约束条件、表查询关键字
学习内容总结
今日学习内容总结
约束条件
自增特性
之前有提到，在数据库应用，我们经常要用到唯一编号，以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。
Myql的auto_increment属性具有以下特性：
1. 具有auto_increment属性的数据列应该是一个正数序列，如果把该数据列声明为UNSIGNED，这样序列的编号个数可增加一倍。比如tinyint数据列的最大编号是127，如果加上UNSIGNED，那么最大编号变为255。
2. auto_increment数据列必须有唯一索引，以避免序号重复；必须具备NOT NULL属性。
3. 自增不会随着数据的删除而回退。
外键
外键简介
什么是外键，外键是用来建立两张表之间的关系的。那么为什么要有外键呢，我们可以举个例子：
| id | name | gender | dep_name | dep_desc |
|---|---|---|---|---|
| 1 | tank | male | 技术部 | 技术游戏开发 |
| 2 | sean | female | 销售部 | 销售游戏 |
| 3 | egon | female | 销售部 | 销售游戏 |
这个时候我们可以发现将所有数据存放在一张表中的弊端:
1. 结构不清晰
2. 浪费空间
3. 可扩展性极差 ---> 不可忽视的弊端
类似于将所有python代码存放在一个py文件中，强耦合到一起了,代码解耦合就相当于拆分表，拆分表解决以上问题，因此需要给两张表之间，建立一种强有力的关系, 通过使用 “外键”。
外键关系
1. 一对多
2. 多对多
3. 一对一
4. 没有关系
怎么使用外键
对于关系的判断核心就四个字，换位思考。
一对多
先创建两张表(将一张表拆分成两张),确认两张表的关系，以员工表与部门表为例：
站在员工表的位置考虑
一个员工能对应多个部门吗？ 不可以
站在部门表的位置考虑
一个部门能对应多个员工吗？ 可以
所以这两个表之间的关系是'一对多的关系'，其中部门是一，员工是多。而在一对多关系中，外键字段是建在字段'多'的一方(例如案例中的员工表)。
外键SQL语句
# 被关联表 部门表
create table dep(
id int primary key auto_increment comment '编号',
dep_name varchar(30) comment '部门名称',
dep_decs varchar(30) comment '部门描述'
);
# 关联表 员工表
create table emp(
id int primary key auto_increment comment '编号',
name varchar(16) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号' not null,
foreign key (dep_id) references dep(id)
);
注意：创建表的时候一定要先创建被关联表。
建立好表格和再插入数据，先插入被关联表(dep)的数据,再插入关联表(emp)的数据。
foreign key的约束效果
1.创建表的时候 应该先创建被关联表(没有外键字段的表)
2.插入数据的时候 应该先插入被关联表(没有外键字段的表)
外键字段填入的值只能是被关联表中已经存在的值
3.修改、删除被关联表数据都会出现障碍
update dep set id=200 where id=2;
delete from dep where id=2;
解决修改，删除被关联表数据都会出现障碍的问题：级联更新级联删除
create table emp1(
id int primary key auto_increment comment '编号',
name varchar(32) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号',
foreign key(dep_id) references dep1(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table dep1(
id int primary key auto_increment comment '编号',
dep_name varchar(32) comment '部门名称',
dep_desc varchar(32) comment '部门描述'
);
其实在实际工作中外键也可能不会使用。因为外键会消耗额外的资源，并且会增加表的复杂度。表很多的情况下，我们也可以通过SQL语句的形式建立逻辑意义上的表关系。
多对多
以图书与作者为例
1.先站在图书表的角度
一本书籍能否对应多名作者，能
2.再站在作者表的角度
一名作者能否对应多本书籍， 也能
换位思考后两边都可以对应多个，所以这就是'多对多'关系。这样的关系如何建表呢：
create table book(
id int primary key auto_increment,
title varchar(32),
price float(10,2),
author_id int,
foreign key(author_id) references author(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table author(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
book_id int,
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
这样建表会发现，无法知道哪张表是被关联表。所以针对多对多关系，需要单独开设第三张表专门存储关系。利用第三张表，为两张表建立“多对多外键关系”。
create table book(
id int primary key auto_increment,
title varchar(32),
price float(10,2)
);
create table author(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others')
);
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade # 级联更新
on delete cascade, # 级联删除
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
在多对多关系中，两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除。
一对一关系
两张表之间的关系 一一对应，将一张数据量比较大的表，拆分成两张表。比如用户与用户详情为例,通过换位思考会发现他们是一对一的关系。针对'一对一关系'外键字段建在任意一方都可以,但是推荐建在查询频率较高的较好的一方。
create table User(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
user_detail_id int unique, # 外键必须设置为唯一的
foreign key(user_detail_id) references UserDetail(id)
on update cascade # 级联更新
on delete cascade, # 级联删除
);
create table UserDetail(
id int primary key auto_increment,
phone bigint,
age int
);
针对一对一，在添加数据时，关系必须一一对应。否则报错。
表查询关键字
数据准备
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
('jason','male',18,'20170301','张江第一帅形象代言',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),
# 销售部
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
# 运营部
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3);
查询关键字之select与from
select用于指定查询的字段
from用于指定查询的表
select id,name from mysql.user;
查询关键字其实有先后执行顺序，但是无需过多在意。
查询关键字之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=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);
# 3.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
# 4.查询薪资不在20000,18000,17000的数据
select * from emp where salary not in (20000,18000,17000);
# 5.查询岗位描述为空的数据
select * from emp where post_comment=null; # 不可以
# 针对null只能用is不能用等号
select * from emp where post_comment is null; # 可以
模糊查询
查询条件如果不是很明确的情况下，我们统一称之为'模糊查询'。
关键字
like:开启模糊查询的关键字
关键符号
%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
# 6.查询员工姓名中包含字母o的员工姓名和薪资
select name,salary from emp where name like '%o%';
# 7.查询员工姓名是由四个字符组成的数据
select * from emp where name like '____';
select * from emp where char_length(name)=4;
查询关键字之group by分组
mysql分组查询
在 MySQL SELECT 语句中，允许使用 GROUP BY 子句，将结果集中的数据行根据选择列的值进行逻辑分组，以便能汇总表内容的子集，实现对每个组而不是对整个结果集进行整合。
什么是分组：按照指定的条件将单个单个的个体组织成一个个整体的就是组。
为什么需要分组：分组的好处在于可以快速统计出某些数据。
语法格式：
GROUP BY { <列名> | <表达式> | <位置> } [ASC | DESC]
# 语法说明
<列名>：指定用于分组的列。可以指定多个列，彼此间用逗号分隔。
<表达式>：指定用于分组的表达式。通常与聚合函数一块使用，例如可将表达式 COUNT(*)AS' 人数 ' 作为 SELECT 选择列表清单的一项。
<位置>：指定用于分组的选择列在 SELECT 语句结果集中的位置，通常是一个正整数。例如，GROUP BY 2 表示根据 SELECT 语句列清单上的第 2 列的值进行逻辑分组。
ASC|DESC：关键字 ASC 表示按升序分组，关键字 DESC 表示按降序分组，其中 ASC 为默认值，注意这两个关键字必须位于对应的列名、表达式、列的位置之后。
# GROUP BY 子句中的各选择列必须也是 SELECT 语句的选择列清单中的一项。比如按部分分组
select * from emp group by post;
在mysql5.7及以上版本默认自带sql_mode=only_full_group_by 。该模式要求分组之后默认只可以直接获取分组的依据不能直接获取其他字段，原因是分组的目的就是按照分组的条件来管理诸多数据，最小单位应该是分组的依据而不是单个单个的数据。如果是MySQL5.6及以下版本，需要自己手动添加。
使用GROUP BY 子句需要注意的点
1. GROUP BY 子句可以包含任意数目的列，使其可以对分组进行嵌套，为数据分组提供更加细致的控制。
2. GROUP BY 子句列出的每个列都必须是检索列或有效的表达式，但不能是聚合函数。若在 SELECT 语句中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。
3. 除聚合函数之外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
4. 若用于分组的列中包含有 NULL 值，则 NULL 将作为一个单独的分组返回；若该列中存在多个 NULL 值，则将这些 NULL 值所在的行分为一组。
聚合函数
SQL 允许对表中的数据进行计算。对此，SQL 有 5 个内置函数，这些函数都对 SELECT 的结果进行操作。
| 函数名 | 作用 |
|---|---|
| max | 统计最大值 |
| min | 统计最小值 |
| sum | 统计求和 |
| count | 统计计数 |
| avg | 统计平均值 |
其中 COUNT 函数可用于任何数据类型 (因为它只是计数)，而 SUM 、AVG 函数都只能对数字类数据类型做计算，MAX 和 MIN 可用于数值、字符串或是日期时间数据类型。
这里有一些题目，能够帮助我们判断是否分组。
# 1.统计每个部门的最高薪资
select post,max(salary) from emp group by post;
# 2.统计每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 3.统计每个部门的员工人数
select post,count(id) from emp group by post;
# 4.统计每个部门的月工资开销
select post,sum(salary) from emp group by post;
# 5.统计每个部门最小的年龄数
select post,min(age) from emp group by post;
# 间接获取分组以外其他字段的数据
# 1.统计每个部门下所有员工的姓名
select post,group_concat(name) from emp group by post;
# 2.统计每个部门下所有员工的姓名和年龄
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;
select post,group_concat(name) as '姓名' from emp group by post;
select id as '序号',name as '姓名' from emp;
# 字段起别名 as关键字也可以不写 但是语义不明确 建议加上
# 不写as
select id '序号',name '姓名' from emp;
作业
分组练习题
1. 查询岗位名以及岗位包含的所有员工名字
mysql> select post,group_concat(name) from emp group by post;
+--------------------+-------------------------------------+
| post | group_concat(name) |
+--------------------+-------------------------------------+
| operation | 僧龙,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 哈哈,呵呵,西西,乐乐,拉拉 |
| teacher | tom,kevin,tony,owen,jack,jenny,sank |
| 张江第一帅形象代言 | jason |
+--------------------+-------------------------------------+
4 rows in set (0.01 sec)
2. 查询岗位名以及各岗位内包含的员工个数
mysql> select post,count(id) from emp group by post;
+--------------------+-----------+
| post | count(id) |
+--------------------+-----------+
| 张江第一帅形象代言 | 1 |
| teacher | 7 |
| sale | 5 |
| operation | 5 |
+--------------------+-----------+
4 rows in set (0.00 sec)
3. 查询公司内男员工和女员工的个数
mysql> select sex,count(id) from emp group by sex;
+--------+-----------+
| sex | count(id) |
+--------+-----------+
| male | 10 |
| female | 8 |
+--------+-----------+
2 rows in set (0.00 sec)
4. 查询岗位名以及各岗位的平均薪资
mysql> select post,avg(salary) from emp group by post;
+--------------------+---------------+
| post | avg(salary) |
+--------------------+---------------+
| 张江第一帅形象代言 | 7300.330000 |
| teacher | 151842.901429 |
| sale | 2600.294000 |
| operation | 16800.026000 |
+--------------------+---------------+
4 rows in set (0.00 sec)
5. 查询岗位名以及各岗位的最高薪资
mysql> select post,max(salary) from emp group by post;
+--------------------+-------------+
| post | max(salary) |
+--------------------+-------------+
| 张江第一帅形象代言 | 7300.33 |
| teacher | 1000000.31 |
| sale | 4000.33 |
| operation | 20000.00 |
+--------------------+-------------+
4 rows in set (0.00 sec)
6. 查询岗位名以及各岗位的最低薪资
mysql> select post,min(salary) from emp group by post;
+--------------------+-------------+
| post | min(salary) |
+--------------------+-------------+
| 张江第一帅形象代言 | 7300.33 |
| teacher | 2100.00 |
| sale | 1000.37 |
| operation | 10000.13 |
+--------------------+-------------+
4 rows in set (0.01 sec)
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
mysql> select sex,avg(salary) from emp group by sex;
+--------+---------------+
| sex | avg(salary) |
+--------+---------------+
| male | 110920.077000 |
| female | 7250.183750 |
+--------+---------------+
2 rows in set (0.00 sec)
浙公网安备 33010602011771号