Mysql入门详解

Mysql入门详解
数据库之Mysql

本篇文章为观看某教学视频后所作个人总结
目录
- 数据库之Mysql
  一、简单了解数据库
  1.1常见关系型数据库
  1.2非关系型数据库
  1.3 mysql语句的构成
  二、Mysql的使用
  2.1mysql数据库的创建、修改，查询
  2.2数据库表的创建、修改，查询
  2.2.1 数据类型
  2.2.2列的约束
  2.2.3创建表
  2.2.4删除表
  2.2.4查看表
  2.2.5修改已经创建的表
  2.3数据库表的CRUD（增删改查）
  2.3.1插入数据
  2.3.2删除数据
  2.3.3更新数据
  2.2.4数据查询
  三、多表查询
  3.1 简单了解与分析
  3.2 建表的种类
  3.3 商品表进阶设计（多表）【本实例引自黑马教程】
  3.4 商品表查询实例(多表)
一、简单了解数据库

1.1常见关系型数据库
- mysql：开源免费，中小型企业；sun公司被oracle收购后开始收费。
- mariadb：由mysql创始人创建，由mysql延伸出来的分支，命令基本通用。
- Oracle：商业收费，适用于大型电商网站。
- db2：IBM公司，大多用于银行系统。
- sqlserver：Windows专用，政府网站多采用此数据库，教学使用。
  
  用于描述实体与实体之间的的关系
1.2非关系型数据库

Nosql 、mongodb、redis

键值对(key-value)

1.3 mysql语句的构成
- SQL:结构化查询语言
- DDL:数据定义语言，定义数据库、数据表的结构：create(创建)、drop(删除)、alter(修改)
- DML:数据操纵语言，用来操作数据，包括insert(插入)、update(修改)、delete(删除)
- DCL:数据控制语言，定义或取消访问权限，安全设置（grant）
- DQL:数据查询语言，select(查询) from子句 where子句等
二、Mysql的使用

2.1mysql数据库的创建、修改，查询
- 登录数据库服务器
  
  mysql -uroot -p1234 (我的密码是1234)
  
  通用格式为mysql -u[用户名] -p[用户的密码]
  
  退出数据库使用exit
- 创建一个数据库
  
  create database mybase character set utf8 collate utf8_bin/utf_general_ci;
  
  通用规则：create database 数据库名 [设置字符集] [校验规则]
  
  为了避免中文乱码问题建议在创建表的时候直接指定编码为utf8(国际通用编码)
  
  如果还有中文乱码，则可能是dos下的编码问题chcp查看编码
  
  chcp 935 表示gbk; chcp 65001表示utf8
- 查看所有创建的数据库
  
  show databases;
  
  注意：1.不要漏掉databases后面的s。
  
  2.三个数据库的原表不要动:information_schema;
  
  performance_schema；mysql
- 查看数据库的定义
  show create database 数据库名;
```
mysql> show create database day02;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| day02    | CREATE DATABASE `day02` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
```
- 查看当前正在使用的数据库
```
mysql> select database();
+------------+
| database() |
+------------+
| day02      |
+------------+
1 row in set (0.00 sec)
```
- 选中数据库
```
use 数据库名;
mysql> use day02;
Database changed
```
- 修改数据库
```
#修改数据库的字符集
alter database 数据库名 character set 字符集；
```
- 删除数据库
```
drop database 数据库名；
drop database test;
```
2.2数据库表的创建、修改，查询

2.2.1 数据类型

char varchar double float boolean

date: YYYY-MM-DD

time: hh:mm:ss

datetime: YYYY-MM-DD hh:mm:ss 默认值为none

timesstrap: YYYY-MM-DD hh:mm:ss 默认值是当前时间

text: 存放文本 blob: 存放二进制

对于单个字符，char的长度固定，varchar长度可变

2.2.2列的约束

主键约束：primary key

唯一约束：unique

非空约束：not null

2.2.3创建表
```
#切换到指定的数据库
use 数据库名；
#开始创建表
create table 表名(
列名 类型[长度] 约束，
列名2 类型[长度] 约束
)
#具体例子：
mysql> use day02;
Database changed
mysql> create table student(
    -> sid int primary key,
    -> sname varchar(31),
    -> sex int,
    -> age int
    -> );
Query OK, 0 rows affected (0.10 sec)
```
2.2.4删除表
```
use 数据库名;
drop table 表名;
```
2.2.4查看表
```
#确保已经切换到指定的数据库
#查看表的结构
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(11)     | NO   | PRI | NULL    |     
| sname | varchar(31) | YES  |     | NULL    |     
| sex   | int(11)     | YES  |     | NULL    |     
| age   | int(11)     | YES  |     | NULL    |     
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
#查看表的定义
show create table 表的名字;
show create table stuinfo;
```
2.2.5修改已经创建的表

包括的操作有:添加列(add)、删除列(drop)、修改列(modify)、修改列名(change)、修改表名(rename)、修改表的字符集
```
#添加列(add)
alter table 表名 add 列名  列的类型 列的约束
alter table student add hobby varchar(10);
#删除列(drop)
alter table student drop hobby ;
#修改列(modify)
alter table student modify sex varchar(2);
#修改列名(change)
alter table student change age year int(5);
#修改表名(rename)
rename table student to employeer;
#修改表的字符集
alter table employeer character set gbk;
```
开发时一般不会修改表名，影响较大。

2.3数据库表的CRUD（增删改查）

2.3.1插入数据
```
-- 单条插入
#标准格式
insert into 表名(key1,key2,key3...)  values(value1,value2,value3...);
#举个栗子
insert into student(sid,sname,sex,age)  values(1,'lusi',1,22);
#当插入的数据为全列时，可以省略"key"值,如下：
insert into student values(1,'lusi',1,22);
#若插入的数据不是全列，则需要写全"key"值，如下：
insert insto student (sid,sname)  values(2,'ziyu');

-- 批量插入
#以逗号作为分隔符
insert into student values(3,'xm',1,21),(4,'xh',2,18),(5,'xg',1,23);
```
2.3.2删除数据
```
delete from 表名 [where条件];
#举个栗子
delete from student where sid=1;
#未指明条件，则删除全部数据记录
delete from student; 

-- 比较delete与truncate的效率
delete:DML  一条一条删除表中的数据
truncate:DDL 先删除表再重建表
倘若表中数据较多，truncate效率更高，否则delete效率高
```
2.3.3更新数据
```
update 表名 set 列名1=value1，列名2=value2...;
update student set sname=yamgmi,age=30 where sid=4;
#倘若未指明where条件，则更新到所有数据项
```
2.2.4数据查询

语法格式：select [distinct] [ * ] [列名1，列名2.....] from 表名 [where 条件]

-- 为了方便实验，我们单独创建两张表
```
create database goods character set utf8;
use goods;
#商品的分类：商品分类的ID、商品分类的名称、商品分类的描述
create table category(
 cid int primary key auto_increment,
 cname varchar(10),
 cdesc varchar(31)
);
#所有的商品：商品ID、商品名称，价格，生产日期，分类ID
create table product (
 pid int primary key auto_increment, #自增长
 pname varchar(10),
 price double,
 pdate timestamp,
 cno  int
);
#插入数据
insert into category values(null,'休闲食品','瓜子,辣条'),
                         (null,'饮料','营养快线,安慕希'),
                          (null,'箱包','召唤师峡谷制造'),
                         (null,'烟酒','茅台,红南京,拉菲');
insert into product  values(null,'瓜子',12,null,1),(null,'卫龙辣条',4,null,1),
                           (null,'哇哈哈',8,null,2),(null,'安慕希',68,null,2),
                           (null,'莫斯利安',58,null,2),(null,'京东大包',165,null,3),
                           (null,'阿迪王行李箱',256,null,3),(null,'茅台',1000,null,4),
                           (null,'小苏',100,null,4);
```
开始查询

1.简单查询

select * from product
别名 as、select运算查询（对结果运算，不改变数据库中的值）
select pname as "商品名称",price as "商品价格",price*0.8 as "折后价" from product;

as是mysql为列起别称的关键字，使用时可以省略

2.条件查询[where 关键字]
where后面的条件：> >= < <= = != <>
<>:不等于，标准sql !=:不等于，非标准sql
判断某一列是否为空：is null, is not null
逻辑运算and or not between..and..
select * from product where price between 10 and 100 and cno <> 4;

between..and..在使用时，前面的数必须要小于后面的数

3.模糊查询
_ 代表单个字符
% 代表多个字符
select * from product where pname like "_斯%";
in 设定范围
select * from product where price in (12,8,165);

4.排序查询：order by 关键字
asc: ascend 升序（默认）
desc: descend 降序
select * from product where cno=2 order by price asc;

5.聚合函数
- 常见聚合函数
  
  函数名描述
  
  sum() 求和
  
  avg 求平均值
  
  count() 统计数量
  
  min() 最小值
  
  max 最大值
- 使用实例
  1. 获得所有商品价格总和
    
    select sum(price) from product;
  2. 获得所有商品平均价格
    
    select avg(price) from product;
  3. 获取商品个数
    
    select count(*) from product;
where 条件后面不可以接聚合函数

6.子查询（嵌套查询）

查询商品价格大于平均价格的商品

select * from product where price > (select avg(price) from product);

子查询中的嵌套子句后面不需要分号

7.分组查询：group by
1. 根据cno分组，统计分组后商品的个数
  
  select cno,count(*) from product group by cno;
2. 根据cno分组，统计分组后商品的平均价格，并且平均价格大于100
  
  select cno,avg(price) from product group by cno having avg(price)>100;
```
#总结一下
聚合函数常与分组搭配使用
where条件过滤的是分组之前，不可接聚合函数
having 条件过滤的是分组之后，可以接聚合函数
#编写顺序
select .. from where .. group by .. having .. order by ..
#执行顺序
from .. where .. group by .. having .. select .. order by ..
```
三、多表查询

这一部分很重要，所以单独分出来，尽量细节化。

3.1 简单了解与分析

多表查询中，多张表靠什么来维持多表之间的关系？ 外键约束。

引入科学百科对外键的定义：如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

分析：以上面的商品数据库(goods)为例，商品分类(category)的中的主键(cid)与所有商品表(product)的非主键(cno)之间可以建立联系，因为他们都表示商品分类的ID编号。故可以为从表product添加外键，如下:

alter table product add foreign key(cno) references category(cid);

注意：1.在添加外键之前须确保，从表中外键的值必须在主表中存在，否则无法建立外键。上面category表中我们的cid有1、2、3、4四个值(自增长),如果我们事先执行如下操作insert into product values(null,"洋娃娃",20,null,5);则会因为主表中没有cid=5这个值而无法添加外键。2. 在添加外键之后，如果想要删除某个分类，需要先删除对应分类号的所有商品。（即若要删除主表中某行的值，必须先删除从表中对应外键值的所有条目）

3.2 建表的种类
- 一对多 : 商品和分类
  - 建表原则: 在多的一方添加一个外键,指向一少的一方的主键
- 多对多: 老师和学生, 学生和课程
  
  建表原则: 建立一张中间表,将多对多的关系,拆分成一对多的关系,中间表至少要有两个外键,分别指向原来的那两张表
- 一对一: 班级和班长, 公民和身份证, 国家和国旗
  - 建表原则:
    
    将一对一的情况,当作是一对多情况处理,在任意一张表添加一个外键,并且这个外键要唯一,指向另外一张表
    
    直接将两张表合并成一张表
    
    将两张表的主键建立起连接,让两张表里面主键相等
  - 实际用途: 用的不是很多. (拆表操作 )
    
    相亲网站:
    
    个人信息 : 姓名,性别,年龄,身高,体重,三围,兴趣爱好,(年收入, 特长,学历, 职业, 择偶目标,要求)
    
    拆表操作 : 将个人的常用信息和不常用信息,减少表的臃肿。
建表约束问题：

主键约束: 默认就是不能为空, 唯一
- 外键都是指向另外一张表的主键
- 主键一张表只能有一个
唯一约束: 列面的内容, 必须是唯一, 不能出现重复情况, 为空
- 唯一约束不可以作为其它表的外键
- 可以有多个唯一约束
3.3 商品表进阶设计（多表）【本实例引自黑马教程】

假设以上面的商品数据库为基础，设计一个简单的网上商城数据库，应该怎么做？或者说我们还缺哪些数据库，数据库之间的关系又如何约束。

分析：首先作为一个商城，我们得有用户表(user)吧，然后每个用户还得有自己的购物清单(orders)。一个用户可以有多个购物清单，而同一个购物清单只能属于同一个用户(毕竟购物清单的id编号是固定的)，所以用户与清单的关系是一对多(1:n)；同一个分类可以有多个商品，同一个商品只属于一个分类(别钻牛角尖，说猪既是肉类又是宠物类，开个玩笑)，所以商品与商品分类的关系是一对多；一个清单中可以包含多件商品，而同一件商品也可以存在于不同的购物清单中，所以是多对多关系，多对多这种互相包含的关系会使得数据库很难管理，因此引入一张中间表(orderitem)，专门负责管理他们之间的关系，具体如下：

给出相关表的创建
- 用户表 (用户的ID,用户名,密码,手机)
```
create table user(
	uid int primary key auto_increment,
  	username varchar(31),
  	password varchar(31),
  	phone  varchar(11)
);

insert into user values(1,'lisi','123','18811118888');
```
- 订单表 (订单编号,总价,订单时间 ,地址,外键用户的ID)
```
  create table orders(
  	oid int primary key auto_increment,
    	sum int not null,
      otime timestamp,
    	address varchar(100),
    	uno int,
    	foreign key(uno) references user(uid)
  );
  insert into orders values(1,200,null,'江宁大学城旁边小黑屋',1);
  insert into orders values(2,250,null,'江宁大学城旁边最豪华房子',1);
```
- 订单项: 中间表(订单ID,商品ID,商品数量,订单项总价)
```
create table orderitem(
	ono int,
  	pno int,
  	foreign key(ono) references orders(oid),
  	foreign key(pno) references product(pid),
  	ocount int,
  	subsum double
);
--给1号订单添加商品 200块钱的商品
insert into orderitem values(1,7,100,100);
insert into orderitem values(1,8,101,100);

--给2号订单添加商品 250块钱的商品 ()
insert into orderitem values(2,5,1,35);
insert into orderitem values(2,3,3,99);
```
分类表(category)与商品表(product)延用之前的，不再列出。

3.4 商品表查询实例(多表)
- 交叉连接查询笛卡尔积
  SELECT * FROM product;
  SELECT * FROM category;
笛卡尔积 ,查出来是两张表的乘积 ,查出来的结果没有意义
SELECT * FROM product,category;

--过滤出有意义的数据
SELECT * FROM product,category WHERE cno=cid;

SELECT * FROM product AS p,category AS c WHERE p.cno=c.cid;
SELECT * FROM product p,category c WHERE p.cno=c.cid;

--数据准备
INSERT INTO product VALUES(NULL,'耐克帝',10,NULL);
- 内连接查询
  -- 隐式内链接
  SELECT * FROM product p,category c WHERE p.cno=c.cid;
  -- 显示内链接
  SELECT * FROM product p INNER JOIN category c ON p.cno=c.cid;
  -- 区别:
  隐式内链接: 在查询出结果的基础上去做的WHERE条件过滤
  显示内链接: 带着条件去查询结果, 执行效率要高
- 左外连接
  左外连接,会将左表中的所有数据都查询出来, 如果右表中没有对应的数据,用NULL代替
  SELECT * FROM product p LEFT OUTER JOIN category c ON p.cno=c.cid;
- 准备工作
  INSERT INTO category VALUES(100,'电脑办公','电脑叉叉差');
- 右外连接: 会将右表所有数据都查询出来, 如果左表没有对应数据的话, 用NULL代替
  
  SELECT * FROM product p RIGHT OUTER JOIN category c ON p.cno=c.cid;
-- 查询分类名称为手机数码的所有商品
1.查询分类名为手机数码的ID
SELECT cid FROM category WHERE cname='手机数码';
2.得出ID为1的结果
SELECT * FROM product WHERE cno = (SELECT cid FROM category WHERE cname='手机数码');

-- 查询出(商品名称,商品分类名称)信息
--左连接
SELECT p.pname,c.cname FROM product p LEFT OUTER JOIN category c ON p.cno = c.cid;

--子查询
SELECT pname ,(SELECT cname FROM category c WHERE p.cno=c.cid ) AS 商品分类名称 FROM product p;

若觉得掌握的还不够，点此下载基础sql练习题以及多表查询练习，配有答案^_
相关阅读:
socket communication between a Java and/or C++ programs (现成的程序)
java和asp.net之间web Service的创建和调用(2)
linux下自动启动tomcat
什么是CPU架构
 Eclipse for Linux on POWER 的安装和使用
 linux开机启动tomcat6
揭开Socket编程的面纱
 认识错误武胜
 MySqlCommand, MySqlParameter and "LIKE" with percent symbol zt 武胜
 设置zedgraph鼠标拖拽和局部放大属性转武胜
原文地址：https://www.cnblogs.com/randolf/p/10347400.html

函数名	描述
sum()	求和
avg	求平均值
count()	统计数量
min()	最小值
max	最大值

数据库之Mysql

一 、简单了解数据库

1.1常见关系型数据库

1.2非关系型数据库

1.3 mysql语句的构成

二、Mysql的使用

2.1mysql数据库的创建、修改，查询

2.2数据库表的创建、修改，查询

2.2.1 数据类型

2.2.2列的约束

2.2.3创建表

2.2.4删除表

2.2.4查看表

2.2.5修改已经创建的表

2.3数据库表的CRUD（增删改查）

2.3.1插入数据

2.3.2删除数据

2.3.3更新数据

2.2.4数据查询

三、多表查询

3.1 简单了解与分析

3.2 建表的种类

3.3 商品表进阶设计（多表）【本实例引自黑马教程】

3.4 商品表查询实例(多表)

一、简单了解数据库