外键约束、SQL查询关键字、多表查询

上期内容回顾

• 存储引擎

# 查看
	show engines;
    
# 四个引擎
	myisam 5.5之前的默认引擎
	innodb 5.5之后的默认引擎
	memory 基于内存的
	blackhole 一切事物存储于此都会消失(黑洞)

• 基本数据类型

# 整型
	tinyint smallint int bigint
    
# 浮点型
	float double decimal
    
# 字符型
	char varchar
    
# 日期类型
	date datetime time year
    
# 枚举与集合
	enum set

• 创建表的完整语法

create table 表名(
	字段名 字段类型 约束条件;
	字段名 字段类型 约束条件;
	字段名 字段类型 约束条件
);

• 约束条件

unsigned	让数字没有正负号
zerofill	多余的使用数字0填充
not null	非空约束
default 	默认值
unique  	唯一值
primary key	主键 非空且唯一
auto_increment	自增长(只能给主键用)

今日内容概要

• 外键(约束条件)
• SQL查询关键字
• 多表查询思路

内容详细

1. 外键

# 外键前戏
	以创建一张员工表为例
	表头内容包含：
		id name age	dep_name dep_desc	
"""
缺陷：
	1.表的重点不清晰
		到底是员工表还是部门表 可以忽略
	
	2.表中相关字段一直在重复存储
		浪费存储空间 可以忽略
		
	3.表的扩展性极差,牵一发而动全身  
		不能忽略


解决方式:
	将上述一张表拆分成两张表 
	emp与dep
	上述三个缺陷全部解决
	
	
但是：
	带来了一个小问题 表与表之间的数据没有对应关系了
"""


# 解决：外键
	外键字段>>>:部门编号
"""
外键:
	其实就是用来标识表与表之间的数据关系
	简单的理解为该字段可以让你去到其他表中查找数据
"""

2. 表与表之间建关系

2.1 一对多

# 表关系总共就四种
	一对多
	多对多
	一对一
	没有关系
    
# 判断表关系的方式:换位思考
	# 一对多
		以员工和部门表为例
		先站在员工表的基础之上
			问:一个员工信息能否对应多个部门信息
			答:不可以
		再站在部门表的基础之上
			问:一个部门信息能否对应多个员工信息
			答:可以
		结论:一个可以一个不可以 那么表关系就是"一对多"
			员工表是多 部门表是一
		"""
		针对一对多的表关系 外键字段建在多的一方
		表关系没有'多对一'一说 都是'一对多'
		"""
"""
使用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(32),
	age int,
	dep_id int,
	foreign key(dep_id) references dep(id)  # emp表中的dep_id必须在dep表中包含
); 

insert into dep(dep_name,dep_desc) values('教学部','教书育人'),('安保部','保卫家园'),('人事部','招兵买马'),('财务部','军饷保证');

insert into emp(name,age,dep_id) values('jason',18,1),('tony',28,2),('mary',38,3),('jack',48,4);
"""

2.2 多对多关系

# 多对多关系
	以书籍表与作者表为例
	先站在书籍表的基础之上
		问:一个书籍信息能否对应多个作者信息
			答:可以
	再站在作者表的基础之上
		问:一个作者信息能否对应多个书籍信息
			答:可以
	结论:两个都可以 那么表关系就是"多对多"
	# 多对多表关系 需要单独开设第三张表存储(并且第三张表可以不绑定)

"""
create table book(
	id int primary key auto_increment,
	title varchar(32),
	price float(6,2)
);

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

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
);
"""

2.3 一对一表关系

# 一对一表关系
	以作者表与作者详情表为例
		先站在作者表的基础之上
			问:一个作者信息能否对应多个作者详情信息
				答:不可以
		再站在作者详情表的基础之上
			问:一个作者详情信息能否对应多个作者信息
				答:不可以
		结论:两个都不可以 
            
	那么表关系可能是"一对一"或者"没有关系"
	# 外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中
    
"""
create table author(
	id int primmary key auto_increment,
	name varchar(32),
	age int,
	author_id int unique,
	foreign key(author_id) references author_detail(id)
	on update cascade
	on delete cascade
);

create table author_detail(
	id int primary key auto_increment,
	phone varchar(32),
	address varchar(32)
);
"""

3. 外键约束

# 1.在创建表的时候 需要先创建被关联表(没有外键字段的表)

# 2.在插入新数据的时候 应该先确保被关联表中有数据

# 3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据

# 4.在修改和删除被关联表中的数据的时候 无法直接操作

"""如果想要数据之间自动修改和删除需要添加额外的配置"""
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(32),
	age int,
	dep_id int,
	foreign key(dep_id) references dep(id)  # emp表中的dep_id必须在dep表中包含
	on update cascade  # 级联更新(不加逗号)
	on delete cascade  # 级联删除
);


"""
由于外键有实质性的诸多约束 当表特别多的时候外键的增多反而会增加耦合程度
	所以在实际开发项目中 有时候并不会使用外键创建表关系
	而是通过SQL语句层面 建立逻辑意义上的表关系
	
	eg:操作员工表的sql执行完毕之后 立刻跟着执行操作部门的sql
"""

4. 操作表的SQL语句补充

show tables;  # 展示
desc 表名;  # 查看
create table t1(id int);  # 创建
alter table t1 change id nid int;  # 改变
drop table t1;  # 删除

# 语法：
1. 修改表名  
	alter table 表名 rename 新表名;
                          
2. 增加字段
	alter table 表名 add 字段名 数据类型 [完整性约束条件…],
                          
	alter table 表名 add 字段名 数据类型 [完整性约束条件…] first;  # 增加到首行
                          
	alter table 表名 add 字段名 数据类型 [完整性约束条件…] after 字段名;  # 增加到某行之后      
                                           
3. 删除字段
	alter table 表名 drop 字段名;
                          
4. 修改字段  # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
	alter table 表名 modify 字段名 数据类型 [完整性约束条件…];
                          
	alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];                   

5. 查询关键字

# 数据准备
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),
('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);

5.1 关键字之select与from

# from 控制的是查询哪张表

# select 控制的是查询表里面的哪些字段

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

5.2 关键字之where筛选

# where筛选功能 
	模糊查询:没有明确的筛选条件
	关键字:like
	关键符号:
		%:匹配任意个数任意字符
		_:匹配单个个数任意字符
	show variables like '%mode%se';


# 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.查询员工姓名中包含o字母的员工姓名和薪资
	select * from emp where name like '%o%';
"""
在刚开始接触 mysql查询的时候，建议按照查询的优先级顺序拼写出你的sql语句

先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""

# 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 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;
	select name,post from emp where post_comment is not null;

5.3 关键字之group by分组

# 分组
	按照某个指定的条件将单个单个的个体分成一个个整体
	eg:
		按照男女将人分组
		按照肤色分组
		按照年龄分组
        
# 分组之后默认只能够直接拿到该分组的依据 其他数据都不能直接获取
# 数据分组应用场景：每个部门的平均薪资，男女比例等
        
# 聚合函数
	聚合函数主要就是配合分组一起使用
	max min sum count avg
    
    
# 1.按部门分组
	针对5.6MySQL 需要自己设置sql_mode
	set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
	设置完成后 重新进入MySQL 再执行：
		select post from emp group by post;

"""
设置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;  # 获取部门信息
	# 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名


# 2.获取每个部门的最高工资 
	先以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
	# 再每个部门的最高工资
		select post,max(salary) from emp group by post;
    
	补充:在显示的时候还可以给字段取别名
		select post as '部门',max(salary) as '最高工资' from emp group by post;
	as也可以省略 但是不推荐省 因为寓意不明确
    
	# 每个部门的最低工资
		select post,min(salary) from emp group by post;
        
	# 每个部门的平均工资
		select post,avg(salary) from emp group by post;
        
	# 每个部门的工资总和
		select post,sum(salary) from emp group by post;
        
	# 每个部门的人数
		select post,count(id) from emp group by post;

5.4 分组补充说明

# group_concat 分组之后使用
	如果真的需要获取分组以外的数据字段 可以使用group_concat()
    
# 每个部门的员工姓名
	select post,group_concat(name) from emp group by post;
    
	select post,group_concat(name,'|',sex) from emp group by post;  # 以 | 分隔显示多项数据拼接的结果
    
    
# concat  不分组使用
	select concat(name,sex) from emp;
	select concat(name,'|',sex) from emp;

5.5 关键字之having过滤

"""
where与having都是筛选功能 但是有区别:
	where在分组之前对数据进行筛选
	having在分组之后对数据进行筛选

我们一定要有一个简单的认识：
	一条SQL语句的结果也可以看成是一张全新的表！
"""

# 统计各部门年龄在30岁以上的员工平均工资 并且保留平均工资大于10000的部门
	select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;

5.6 关键字之distinct去重

# 对有重复的展示数据进行去重操作 一定要是重复的数据
	select distinct id,age from emp;
	select distinct post from emp;

5.7 关键字之order by排序

# order by默认就是升序排
	select * from emp order by salary asc; 
    
# 降序排
	select * from emp order by salary desc;

# 先按照age降序排，在年轻相同的情况下再按照薪资升序排
	select * from emp order by age desc,salary asc; 

# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
	select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc;

5.8 关键字之limit分页

# 限制展示条数
	select * from emp limit 3;
    
# 查询工资最高的人的详细信息
	select * from emp order by salary desc limit 1;

# 分页显示
	select * from emp limit 0,5;  # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置

	select * from emp limit 5,5;

5.9 关键字之regexp正则

# 匹配 以j开头 中间任意字符 以 n或者y结尾
	select * from emp where name regexp '^j.*(n|y)$';