[toc]

MySQL快速入门（二）

• 接上篇MySQL快速入门（一）

约束条件

在上一篇说到建表语句的完整结构

create table t(字段 类型 约束条件);约束条件：unsigned		让数字没有正负号zerofill		多余的使用数字0填充not null		非空default			默认值unique			唯一值primary key		主键foreign key		外键auto_increment	 自增

# 无符号：id int unsigned新增表数据的方式方式1:  按照字段顺序一一传值insert into t1 values(1,\'Hammer\');方式2:  自定义传值顺序 甚至不传insert into t1(name,id) values(\'Hammer\',1);insert into t1(id) values(1);  # name没有传就为null在MySQL中不传数据 会使用关键字NULL填充意思就是空 类似于python的None# 非空create table t2(id int,name varchar(32) not null);# 默认值：所有的字段都可以设置默认值，用户不给该字段传值则使用默认的，否则使用传了的create table t3(id int default 911,name varchar(16) default \'Hammer\');# 唯一值：unique单列唯一   ->create table t4(id int,name varchar(32) unique);联合唯一create table t5(id int,host varchar(32),port int,unique(host,port));

自增

由于主键类似于数据的唯一标识，并且主键一般都是数字类型，一般将自增设置给序号类型的字段，比如id（主键），····

• auto_increment

create table t6(id int primary key auto_increment,name varchar(32));

自增的特性

通俗的理解，序号删除了就会接着以前的序号往下排，或者插入了序号失败的就不计入，那么在mysql中，序号插入失败后台也会记录，比如插入id=1失败了，那么下次就会从2开始记··

自增不会因为删除操作而回退delete from 无法影响自增如果想要重置需需要使用truncate关键字truncate 表名  # 清空表数据并且重置主键值

主键

主键通俗的理解为是

not null

+

unique

就是主键，由此可见主键的特征就是非空且唯一，必须有唯一性，比如id，每个人的id号唯一，那么就可以给它设置一个主键作为唯一标识；

主键的存在也加快了数据的查询速度；

\'\'\'InnoDB存储引擎规定了一张表必须有且只有一个主键，因为InnoDB是通过主键的方式来构造表的\'\'\'如果没有设置主键,InnoDB内部会如何变化？情况一：没有主键和其他约束条件# InnoDB会采用隐藏的字段作为主键 不能加快数据的查询情况二：没有主键但是有非空且唯一的字段# InnoDB会自动将该字段升级为主键create table t7(id int not null unique,name varchar(32));

外键

外键的存在解决了表内容重点不清晰，重复字段浪费空间，扩展性差等问题；

比如一个员工表内有员工的基本信息，也统计了员工所在部门的相关信息，这样表的重点就会不清晰，到底是统计员工信息还是部门信息，并且一个部门会对应多个员工，会导致字段重复浪费空间等问题····

解决方式就是，员工表写员工信息，部门表写部门信息，多表存在，使用外键来使得表与表之间确立对应关系，员工所在的部门有该部门的编号，例如dep_id对应了部门表的id；从而确立了表与表之间的数据关系！

级联更新/删除

用外键做了表与表的对应关系后，那么操作（增删改）一个表数据，是不允许的；

那么可以通过添加级联更新和删除来同步数据，添加在设置外键下边···

• on update cascade

  ：级联更新

• on delete cascade

  ：级联删除

表与表之间的关系

• 一对多
• 多对多
• 一对一
• 没有对应关系

ps：表关系没有

\'多对一\'

如何判断两个表或者多个表之间存在关系？用

“换位思考”

的方法

一对多

# 判断是否为一对多关系：-> 员工表和部门表举例--->员工表的角度：一个员工可以对应多个部门吗？不可以！--->部门表的角度：一个部门可以对应多个员工吗？可以！\'\'\'类似上面的这种关系，一个可以一个不可以，那么表关系就是\'一对多\'！\'\'\'

SQL语句实现

# 使用sql语句实现，先建立不含外键的表create table dep(id int primary key auto_increment,dep_name varchar(32),dep_desc varchar(254));create table emp(id int primary key auto_increment,name varchar(11),age int,dep_id int,foreign key(dep_id) references dep(id));insert into dep(dep_name,dep_desc) values(\'财务部\',\'财政大权\'),(\'人事部\',\'人才引进\'),(\'销售部\',\'商品出售\');mysql> select *from dep;+----+-----------+--------------+| id | dep_name  | dep_desc     |+----+-----------+--------------+|  1 | 财务部    | 财政大权     ||  2 | 人事部    | 人才引进     ||  3 | 销售部    | 商品出售     |+----+-----------+--------------+insert into emp(name,age,dep_id) values(\'Hammer\',18,1),(\'Hans\',28,2),(\'jason\',38,3),(\'he\',18,1);mysql> select * from emp;+----+--------+------+--------+| id | name   | age  | dep_id |+----+--------+------+--------+|  1 | Hammer |   18 |      1 ||  2 | Hans   |   28 |      2 ||  3 | jason  |   38 |      3 ||  4 | he     |   18 |      1 |+----+--------+------+--------+# 这样插入表数据的时候，需要依据外键规定的部门编号来插入数据，部门编号是固定的，插入没有的部门编号报错mysql> insert into emp(name,age,dep_id) values(\'Hammer\',18,5);ERROR 1452 (23000): Cannot add or update a child row: a foreign key

多对多关系

# 同样使用换位思考的方法书籍表和作者表举例->书籍表的角度---> 一本书可以对应多个作者吗?可以---> 一个作者可以写多本书吗?可以\'\'\'类似这种双方都可以的情况，就是多对多的关系！\'\'\'# 注意！！！！1、多对多关系创建表的时候，不能像"一对多"关系那样创建，因为两边有对应关系，需要都写入外键，那么创建一个表另外一个表没有创建，写入外键就会报错2、此时，需要第三张表来存储对应关系

SQL语句实现

create table book(id int primary key auto_increment,book_name varchar(32),price float(6,2));create author(id int primary key auto_increment,name varchar(11),age int);create table bookandauthor(id int primary key auto_increment,author_id int,book_id int,foreign key(author_id) references author(id)on update cascadeon delete cascade,foreign key(book_id) references book(id)on update cascadeon delete cascade,);

一对一关系

# 换位思考的方法游戏人物表和任人物详细信息表->游戏人物表角度---> 一个人物可以对应多个详细信息吗？不可以--->一个详细信息可以对应多个人物吗？不可以# 通俗理解为一个人一个身份证这种关系，就类似"9527"是你的编号一样~~~\'\'\'类似这种关系，双向不可以（×），就是一对一的关系或者没关系！\'\'\'

注意

• 外键字段建在任何一方都可以，但是推荐建在查询频率较高的表中

SQL语句实现

create table peo(id int primary key auto_increment,name varchar(11),height int,info_id int unique,foreign key(info_id) references det_info(id)on update cascadeon delete cascade);create table det_info(id int primary key auto_increment,color varchar(32),skill varchar(32));

外键约束

• 在创建表的时候，需要注意先创建没有设置外键的表
• 在插入数据的时候，要确保被关联表中有数据
• 在插入新数据的时候，外键字段只能填写被关联表中已经存在的数据
• 在修改和删除被关联表中的数据的时候，无法直接操作，需要添加级联更新/删除

操作表方法

方法如下

ps：modify只能改字段数据类型完整约束，不能改字段名，但是change可以！

查询关键字

练习数据

# 数据准备create table emp(id int primary key auto_increment,name varchar(20) not null,sex enum(\'male\',\'female\') not null default \'male\', #大部分是男的age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int, #一个部门一个屋子depart_id int);#插入记录#三个部门：教学，销售，运营insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values(\'Hammer\',\'male\',18,\'20170301\',\'CEO\',7300.33,401,1), #以下是教学部(\'tom\',\'male\',78,\'20150302\',\'teacher\',1000000.31,401,1),(\'kevin\',\'male\',81,\'20130305\',\'teacher\',8300,401,1),(\'tony\',\'male\',73,\'20140701\',\'teacher\',3500,401,1),(\'owen\',\'male\',28,\'20121101\',\'teacher\',2100,401,1),(\'jack\',\'female\',18,\'20110211\',\'teacher\',9000,401,1),(\'jenny\',\'male\',18,\'19000301\',\'teacher\',30000,401,1),(\'sank\',\'male\',48,\'20101111\',\'teacher\',10000,401,1),(\'哈哈\',\'female\',48,\'20150311\',\'sale\',3000.13,402,2),#以下是销售部门(\'呵呵\',\'female\',38,\'20101101\',\'sale\',2000.35,402,2),(\'西西\',\'female\',18,\'20110312\',\'sale\',1000.37,402,2),(\'乐乐\',\'female\',18,\'20160513\',\'sale\',3000.29,402,2),(\'拉拉\',\'female\',28,\'20170127\',\'sale\',4000.33,402,2),(\'僧龙\',\'male\',28,\'20160311\',\'operation\',10000.13,403,3), #以下是运营部门(\'程咬金\',\'male\',18,\'19970312\',\'operation\',20000,403,3),(\'程咬银\',\'female\',18,\'20130311\',\'operation\',19000,403,3),(\'程咬铜\',\'male\',18,\'20150411\',\'operation\',18000,403,3),(\'程咬铁\',\'female\',18,\'20140512\',\'operation\',17000,403,3);

select··from

• select * from 表名

  ：查询该所有数据

• select 字段名1,字段2 from 表名

  ：查询该表指定字段

select * from emp;select id,name from emp;

where 筛选

where功能主要是针对查询出来的数据在分组前进行筛选

示例

1.查询id大于等于3小于等于6的数据select * from emp where id>=3 and id<=6;select * from emp where id between 3 and 6;

2.查询薪资是20000或者18000或者17000的数据select * from emp where salary in (20000,18000,17000);select * from emp where salary=18000 or salary=17000 or salary=20000;

3.查询员工姓名中包含o字母的员工姓名和薪资select name,salary from emp where name like \'%o%\';

4.查询员工姓名是由四个字符组成的员工姓名与其薪资select name,salary from emp where name like \'____\';select name salary from emp where char_length(name)=4;

5.查询id小于3或者大于6的数据select * from emp where id<3 or id>6;select * from emp where id not between 3 and 6;

6.查询薪资不在20000，18000，17000范围的数据select * from emp where salary not in (20000,18000,17000);

7.查询岗位描述为空的员工名与岗位名 (针对null不能用等号，只能用is)select name,post  from emp where post_comment is null;

group by 分组

分组是按照指定的条件，将个体归为一个个整体

需要注意的是，如果在不适用函数的情况下，按哪个字段分组， 那么查询的时候只能操作该字段相关的数据，比如按post分组，那么name，id等字段不可直接获取

针对5.6需要自己设置sql_modeset global sql_mode = \'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH\';# 重新连接客户端

聚合函数

聚合函数主要就是配合分组一起使用

• max：最大
• min：最小
• sum：总和
• count：个数
• avg：平均

1.按部门分组select * from emp group by post;# 分组后取出的是每个组的第一条数据mysql> select id,name,sex from emp group by post;+----+--------+--------+| id | name   | sex    |+----+--------+--------+|  1 | Hammer | male   || 14 | 僧龙   | male   ||  9 | 哈哈   | female ||  2 | tom    | male   |+----+--------+--------+"""设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取"""# 设置了之后再取数据，就得取和分组相关的数据，不然会报错select * from emp group by post;  # 报错select id,name,sex from emp group by post;  # 报错# 只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名select post from emp group by post;  # 获取部门信+-----------+| post      |+-----------+| CEO       || operation || sale      || teacher   |+-----------+

分组只能获取组名，那么想获取到其它信息可以通过聚合函数来获取

1.获取每个部门的最高工资mysql> select post,max(salary) from emp group by post;+-----------+-------------+| post      | max(salary) |+-----------+-------------+| CEO       |     7300.33 || operation |    20000.00 || sale      |     4000.33 || teacher   |  1000000.31 |+-----------+-------------+mysql> select post as \'部门\',max(salary) as \'工资\' from emp group by post;+-----------+------------+| 部门      | 工资       |+-----------+------------+| CEO       |    7300.33 || operation |   20000.00 || sale      |    4000.33 || teacher   | 1000000.31 |+-----------+------------+# as 可以给查询结果起别名，来指定更明确的意思2、每个部门的最低工资mysql> select post as \'部门\',min(salary) as \'工资\' from emp group by post;+-----------+----------+| 部门      | 工资     |+-----------+----------+| CEO       |  7300.33 || operation | 10000.13 || sale      |  1000.37 || teacher   |  2100.00 |+-----------+----------+

3、每个部门的平均工资mysql> select post,avg(salary) from emp group by post;+-----------+---------------+| post      | avg(salary)   |+-----------+---------------+| CEO       |   7300.330000 || operation |  16800.026000 || sale      |   2600.294000 || teacher   | 151842.901429 |+-----------+---------------+

4、每个部门的工资总和mysql> select post,sum(salary) from emp group by post;+-----------+-------------+| post      | sum(salary) |+-----------+-------------+| CEO       |     7300.33 || operation |    84000.13 || sale      |    13001.47 || teacher   |  1062900.31 |+-----------+-------------+

5、每个部门的人数mysql> select post,count(id) from emp group by post;+-----------+-----------+| post      | count(id) |+-----------+-----------+| CEO       |         1 || operation |         5 || sale      |         5 || teacher   |         7 |+-----------+-----------+

group_concat 和 concat函数

group_concat函数用于分组后，获取该字段的数据

mysql> select post,age,name from emp group by post; # 不使用group_concat函数报错ERROR 1055 (42000): \'oldboy.emp.age\' isn\'t in GROUP BY\'# 使用了group_concat函数mysql> select post,group_concat(name,age) from emp group by post;+-----------+----------------------------------------------------------+| post      | group_concat(name,age)                                   |+-----------+----------------------------------------------------------+| CEO       | Hammer18                                                 || operation | 程咬铜18,程咬银18,程咬金18,僧龙28,程咬铁18               || sale      | 呵呵38,拉拉28,乐乐18,西西18,哈哈48                       || teacher   | jack18,sank48,owen28,tony73,kevin81,tom78,jenny18        |+-----------+----------------------------------------------------------+# group_concat函数还可以美化mysql> select post,group_concat(\'{\',name,\':\',age,\'}\') from emp group by post;+-----------+-------------------------------------------------------------------------+| post      | group_concat(\'{\',name,\':\',age,\'}\')                                      |+-----------+-------------------------------------------------------------------------+| CEO       | {Hammer:18}                                                             || operation | {程咬铜:18},{程咬银:18},{程咬金:18},{僧龙:28},{程咬铁:18}               || sale      | {呵呵:38},{拉拉:28},{乐乐:18},{西西:18},{哈哈:48}                       || teacher   | {jack:18},{sank:48},{owen:28},{tony:73},{kevin:81},{tom:78},{jenny:18}  |+-----------+-------------------------------------------------------------------------+

concat函数 不分组就可以使用

mysql> select concat(\'{\',name,\':\',age,\'}\') from emp;

Having 过滤

where与having都是筛选功能区别在于：

where在分组前使用，having在分组后使用

# 获取部门所有年龄大于30平均薪资大于10000的员工名字mysql> select post,group_concat(name) from emp where age>30 group by post  having avg(salary>30);+---------+---------------------+| post    | group_concat(name)  |+---------+---------------------+| sale    | 哈哈,呵呵           || teacher | tom,kevin,tony,sank |+---------+---------------------+

Distinct 去重

去重操作，一定有重复数据，如果一列有重复数据，另一列没有，同时操作这两列也是不去重的

select distinct id,age from emp;  # NOselect distinct post from emp;   # YES

ORDER BY 排序

单列排序

按照单个字段或者表达式的值进行排序称为单列排序

SELECT col1, col2, ...FROM tORDER BY col1 [ASC | DESC];# ORDER BY 用于指定排序的字段；ASC升序排序，DESC 降序排序

# 获取部门编号为3的员工信息，并将工资从高到低排序SELECT emp_name, salaryFROM employeeWHERE dept_id = 3ORDER BY salary DESC;# 统计各部门年龄在20岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc;

多列排序

多列排序是指基于多个字段或表达式的排序，使用逗号进行分隔

SELECT col1, col2, ...FROM tORDER BY col1 ASC, col2 DESC, ...;# 首先基于第一个字段进行排序；对于第一个字段排序相同的数据，再基于第二个字段进行排序；依此类推

# 获取部门编号为3的员工信息，并且按照月薪从高到低排序，月薪相同时再按照入职先后进行排序SELECT emp_name, salary, hire_dateFROM employeeWHERE dept_id = 4ORDER BY salary DESC, hire_date;

空值排序

空值（NULL）在 SQL 中表示未知或者缺失的值，MySQL认为空值最小，升序时空值排在最前，降序时空值排在最后

insert into empinfo values(3,\'hans\',\'\',\'2022-2-1\');select salary from empinfo order by salary;+--------+| salary |+--------+|        || 10000  || 20000  |+--------+

limit 分页

MySQL支持使用 LIMIT 实现分页的效果

SELECT emp_name, salaryFROM empORDER BY salary DESCLIMIT 5 OFFSET 0;# ORDER BY 按照月薪从高到低进行排序；OFFSET 表示跳过 0 行，LIMIT 返回前 5 条数据，也就是月薪 Top-5 的员工。另外，OFFSET 可以省略，

MySQL支持两种写法：

• LIMIT 5 OFFSET 10
• LIMIT 10, 5
• 都表示跳过 10 行，返回随后的 5 行数据