• 概要
• 1、外键
• 2、表与表之间建立关系
• 3、外键约束
• 4、操作表的SQL语句补充
• 5、MySQL查询关键字

概要

1、外键

2、SQL查询关键字

3、多表查询思路

​ 子查询

​ 连表操作

1、外键

# 什么是外键？
	这里先下个结论：外键的主要作用是保持数据的一致性、完整性
从下面的例子一步一步推导：
1、首先先创建一张员工信息表
    	例如：
        create table staff(
            id int primary key auto_increment,
            name varchar(32) not null,
            age int,
            salary int,
            department	varchar(32),
        );

'''
问题来了:
问题1：上述表里包括了员工的基本信息以及部门名称，那么,这张表到底是员工信息表，
	还是部门信息表呐？这就造成了表名有歧义的情况。
问题2：我们都知道一个部门可以有多名员工，那么员工的所在部门是不
	是会出现重复存储的情况，这样就浪费了存储空间
问题3：当我们需要对表里的员工信息进行修改时，就会出现牵一发动全身的情况，
	而且这一点会很大程度上影响我们的查询速度（当存储的数据相当多的时候）
'''
# 关于以上问题，有没有解决方式？
# 解决方式1
	将两张表拆分开，拆成员工表和部门表
    	staff与department
'''
将表拆分为两张表以后，在对表里的信息进行修改时，上述三个问题是得到了解决，
但是紧接着又来了一个问题
问题:当我们想要在查看某个员工的信息时，同时能够查看该员工所属部门时，
该操作就不行了，简单来说，表与表之间数据对应关系没了，那么如何解决这个问题？
'''
# 解决方式2
	这里就需要用到我们上面说的外键.
    	这里我们可以给员工表增加一个外键字段：部门编号
    从而达到标识员工表和部门表之间的数据关系的作用
    # 简单的可以理解为，外键字段可以让你去其他表中查找数据。

2、表与表之间建立关系

1、在学习MySQL时，我们说过，MySQL为关系型数据库
，关系型数据库的特性就是拥有固定的表结构，且表与表之间可以建立关系，
表结构在创建表的时候已经说过了，那么如何实现表与表之间建立关系？
2、表与表之间的关系分类
	表与表之间的关系分为：
    	一对多
        多对多
        一对一
        无关系
3、如何判断表与表之间的关系？
	1、一对多的情况
    	以员工表和部门表为例
        先站在员工表的基础之上问：一个员工是否可以对应多个部门
        	答案：不可以
	再站在部门表的基础之上问：一个部门是否可以对应多个员工
        	答案：可以
     # 结论：出现一个可以 一个不可以，称这两个表之间为'一对多'的关系
    '''
    	针对一对多的关系，外键字段建在多的一方（员工表）
    '''
    建立表之间一对多关系命令
    # 使用SQL语句创建真正意义上的表关系，可以先创建不含外键的基本表，之后再添加外键字段
    1、创建两张基本表
	create table staff(
	    id int primary key auto_incremrnt,
	    name varchar(32),
	    age int,
	    dep_id int,  # 外键字段
	);
    	create table department(
            id int prinmary key auto_increment,
            department_name varchar(32),
            department_desc varchar(254)
        );
     2、添加外键字段
    alter table 表名（从表） add foreign key(外键字段) references 主表（建立关系的字段）;

    '''
    	解释：主表和从表
    		简单的可以理解为，含有外键字段的称为从表，以外键字段作为关键字段的称为主表
    		上面的员工表里，将部门编号作为外键字段，以该字段来建立两个表之间的关系。
    '''

实验如下（一对多）

注意：如果Key是MUL, 则表示该列的数据是可以重复的，这也就是一对多的关系的特征，即：设置为外键的当前列，当前列的值可以重复出现

# 多对多的情况如何进行判断
	以书籍表和作者表为例
    	先站在书籍表的基础上问：一本书籍是否可以对应多个作者
        答案：可以
        再站在作者表的基础上问：一个作者是否可以对应多本书籍
        答案：可以
    # 结论:出现两者都可以的情况，则两表的关系为多对多
	'''
	多对多的表关系：
		需要单独开设第三张表存储（并且第三张表可以不绑定）
	'''
    1、创建两张基本表
    	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
        );
     2、创建第三张表存储
        create table book_author(
            id int primary key auto_increment,
            author_id int,
            book_id int,
            foreign key(author_id) references author(id),
            foreign key(book_id) references book(id)
        );

多对多实验结果

# 一对一的关系判断
	以作者与作者详情表为例
    	先站在作者表的基础上问：一个作者是否能对应多条作者详情信息？
        答案：不可以
        再站在作者详情表的基础上问：一条作者详情信息能否对应多个作者？
        答案：不可以
    # 结论：出现两者都不可以时，两个表的关系有两种，一对一或者没有关系，
    # 但是一般没有关系我们不会去讨论，这里只讨论有关系的表与表之间
    '''
    一对一的表关系：
    	外键字段建在任何一方都可以，但是推荐建在查询频率较高的表中，下面我们以建在作者表中为例
    '''
    1、创建两个基础表
    	create table author1(
            id int primary key auto_increment,
            name varchar(32),
            age int,
            author_id int unique  # 注意这个unique
        );

        create auth_detail(
            id int prinmary key auto_increment,
            phone varchar(32),
            address varchar(32)
        );

     2、添加外键字段
    alter table author1add foreign key(author_id) references auth_detail(id);

一对一表关系实验结果

结论：在创建一对一表关系的外键时，外键必须要加上unique唯一值

的约束条件，若不加，就会和上面的一对多关系一样，因此，

一对一的表关系，设置为外键的列，里面的值不允许重复出现，

所以，要区分开一对多和一对一的时候，一对一的关系外键的

约束条件一定要加上unique唯一属性。

3、外键约束

# 在创建外键约束时需要注意的点
1、在创建表的时候，需要先创建被关联表（没有外键字段的表）
2、在插入新数据的时候，应该先确保被关联的表中有数据
3、在插入新数据时，外键字段只能填写被关联表中已经有的数据
4、在修改和删除被关联的表中的数据的时候，无法直接操作，
若想要表与表之间的数据可以实现同步修改和同步删除，需要添加额外的约束条件
# 创建外键字段的两种方式（以一对多为例）
	方式1：先创建出两张空表，再给从表（即含有外键字段的表）添加外键字段
    	例如：
            create table staff(
            	id int primary key auto_increment,
                age int
            );
            create table department(
            	id int primary key auto_increment,
                name varchar(32) unique，
            );
			alter table 从表（staff）add foreign key(staff_id) references 主表（department）（id）;
    方式2：先创建好主表（被关联表），再创建从表，并直接添加外键字段
    	例如：
	create table department(
            id int primary key auto_increment,
            name varchar(32) unique,
       );
	create table staff(
            id int primary key auto_increment,
            age int,
            staff_id int,
            foreign key(staff_id) references department(dep_id)
       );

# 实现数据之间同步修改和删除
    create table department1(
    	id int primary key auto_increment,
        dep_name varchar(32),
        dep_id int
    );
    create table staff1(
    	id int primary key auto_increment,
        age int,
        staff_id int,
        foreign key(staff_id) references department1(dep_id) 
        on update cascade  # 级联更新
        on delete cascade  # 级联删除
    );
    # 注：级联更新和级联删除是跟在外键字段后面，切记不要当成是新的字段名添加。            

级联更新删除实验如下

​ 修改删除前：

/>

修改删除后：

总结：由于外键有实质性的诸多约束，所以，当我们要操作的表特别多时，

而表与表之间又有实质性的联系时，频繁增加外键反而会增加耦合程度

（即表与表之间都有相应的关系，操作起来反而麻烦），所以，在一般的

实际开发中，有时候并不会去用外键创建表，更多的时候是用SQL语句建

立逻辑意义上的表关系（如操作完员工表以后，再去操作部门表）

4、操作表的SQL语句补充

show tables;  # 查看当前库下所有的表名
desc 表名；  # 查看指定表的相关信息
create table 表名（字段名，字段类型）;  # 创建表
alter table 表名 change 旧的字段名 新的字段名 旧的字段类型;  # 修改某个字段的字段名
drop table 表名;  # 删除指定表

# 其他相关语法
    1、修改表名
    	alter table 表名 rename 新表名；

    2、增加字段
    	alter table 表名 add 字段名 字段类型 完整约束条件；
        alter table 表名 add 字段名 字段类型 完整约束条件 first;  # 将新字段添加到表的第一列
        alter table 表名 addd 字段名 字段类型 完整约束条件 after 字段名；  # 将新字段添加到指定的字段名后一列（指定的字段必须存在）

     3、删除字段
        alter table 表名 drop 字段名；

     4、修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以
        alter table 表名 modify 字段名 新的字段类型 新的完整约束条件
        alter table 表名 change 旧字段名 新的字段名 旧的字段类型 完整约束条件;

5、MySQL查询关键字

• 数据准备

# 建立新的员工表
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);

• 查询关键字之select与from

基本语法：
	select 表中的某些字段名 from 表名
    例如：select * from emp;  # 查询表中的所有数据
    	 select id，name from emp;  # 查询表中的id和name字段，多个字段名之间用逗号隔开

• 查询关键字之where

where筛选功能
MySQL查询之精准查询与模糊查询
	精准查询：即查询条件和符合查询条件的必须完全一致
    	例：查询名字为jason的员工薪资
        select salary from emp where name='jason';
	模糊查询：即查询的结果中只要是符合查询条件的都会显示出来，也就是没有明确的筛选条件
    模糊查询关键字：like
    关键符号：%  匹配任意个数的任意字符
    		 — 匹配单个个数的任意字符
        例1：查询员工表中所有名字带'程'的员工姓名
        select name from emp where name like'程%';
        例2：查看当前MySQL环境变量中含'mode'的环境变量
        show variables like '%mode%';
        
'''
在执行查询的时候，建议先搞明白查询的优先级顺序再执行SQL语句
1、先确定是在哪张表里查询 from 表名
2、再确定查询的条件是什么 where 字段名 like 条件
3、再对查询出来的数据筛选出我们想要看的部分 select name,id
'''
        
# 以上面的员工表为基准进行练习
1、查询id大于等于3小于等于6的数据
    方式1：select id,name from emp where id>=3 and id<=6;
    方式2：select id,name from emp where id between 3 and 6;
2、查询薪资是20000或者18000或者17000的数据
    方式1：select * from emp where salary=20000 or salary=18000 or salary=17000;
    方式2：select * from emp where salary in (20000,18000,17000);
3、查询员工姓名中包含0字母的员工姓名和薪资
    select name,salary from emp where name like '%o%';
4、查询员工姓名是由四个字符组成的员工姓名与其薪资
    方式1：select name,salary from emp where name like '____';  # 使用四个下划线匹配
    方式2：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、查询薪资不再2000,18000,17000范围的数据
    select * from emp where salary not in （20000,18000,17000）；
7、查询岗位描述为空的员工名与岗位空
    select name,post from emp where post_comment is null;
    select name,post from emp where post_commment is not null;
    # 针对null的查询不能用等号，只能用is

• 查询关键字之group by

group by 分组
'''
按照某个指定的条件将单个单个的个体分成一个整体
	eg：按照男女将人分组
		按照肤色分组
		按照年龄分组
注：分组之后只能直接获取到分组的依据，其他数据不能直接获取
'''
# 针对MySQL5.6版本需要设置一下环境变量sql_mode
    1、设置环境变量sql_mode
    	set global sql_mode='only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
    2、重启客户端

# 聚合函数
	聚合函数的作用主要就是配合分组一起使用
    	max 最大值
        min 最小值
        sum 求和
        count 计数
        avg 求平均数

# 数据分组的应用场景：每个部门的平均薪资，男女比例等
1、按部门分组
    select * from emp group by post;  # 分组后取出的是每个组的第一条数据
    select id,name,sex from emp gropu by post;  # 验证
'''
	我在执行上述语句会出现‘isn't in GROUP BY’的错误，意思是分组的列不在selec的字段中，
	这里需要注意的是，上面我们说了，严格意义上来说，分组的目的是为了以后能够按照所分的组
	去研究相关数据，所以为了更加严谨一下，我是事先将我的MySQL的环境便令sql_mode设置成了
	ONLY_FULL_GROUP_BY，意思就是说对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY
	中出现，那么这个SQL是不合法的，所以才会出现报错的情况，而在设置完sql_mode以后，我们只能查
	看分组的依据，也就是，只能查看group by后面的字段名，因此，针对MySQL5.6版本来说，一定要提前
	设置好环境变量，不然我们的分组将没有任何意义。
'''
# 正确的执行语句应是如下格式
	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可以省略，但是不推荐省，省略以后所表达的意思就会比较模糊
3、每个部门的最低工资
 	select post as '部门',min(salary) as '最低工资' from emp group by post;
4、每个部门的平均工资
	select post as '部门',avg(salary) as '平均工资' from emp group by post;
5、每个部门的工资总和
	select post as '部门',sum(salary) as '工资总和' from emp group by post;
 6、每个部门的总人数
	select post as '部门',count(id) as '总人数' from emp group by post;

• group by 分组查询补充说明

# group_concat  分组后的情况使用
如果遇到了真的需要获取分组以外的数据字段，可以使用group_concat（字段名）
1、获取每个部门的员工姓名
	select post，group_concat(name) from emp group by post;  
2、同时也可以获取多个非分组依据的数据字段，并且可以使用符号进行拼接
	select post,group_concat(name,':',sex) from emp group by post;
# concat	不分组的情况使用
1、获取单个字段数据
	select concat(name) from emp;
2、获取多个字段数据并使用特殊符号拼接
	select concat(name,'|',sex) from emp;

• 关键字之having过滤

having  过滤
'''
having和where都是筛选功能，但是有区别
	where 在分组之前对数据进行筛选
	having在分组之后对数据进行筛选
'''
例：统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
'''
针对上述问题，基本的解题思路
	1、先筛选出年龄大于30岁的
		select * from emp where age>30;
	2、上述的执行结果，可以简单的看成是一个全新的表，那么对于执行完成之后的表再进行分组如何解决？
	在筛选出的结果基础之上再根据部门进行分组
		select post,avg(salary) from emp where age>30 group by post
	# 这里需要注意，我们这里是用group by对where筛选之后的结果进行分组，所以，筛选的字段应是group by的分组依据，且还需要平均工资
	3、保留平均工资大于10000的部门
	上面说过，SQL语句的执行结果可以看成是一张新的表，所以我们只需要在group by后面加上having过滤即可
		select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
'''
上述例子的完整语法为：
    	select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
  

# 总结：针对SQL语句的执行结果，可以看成是一个全新的表

• 关键字distinct去重

distinct  去重
# 注意：distinct是对有重复的展示数据进行去重操作，一定要是重复的数据
例：展示去重后的年龄
    select distinct age from emp;
例：展示去重后的部门信息
    select distinct post from emp；

• 关键字之order by排序

order by 排序
1、升序  # 默认情况下是升序
	例：按照工资升序排
    select * from emp order by salary asc;  # 不加asc默认为升序
2、降序
	例：按照年龄降序
    select * from emp order by age desc;  # 降序排序
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
	select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc;

• 关键字之limit分页

'''
当我们遇到了数据量非常庞大的情况，如果直接查询所有的数据，会出现展示不全或者错乱的情况，
在这种情况下，我们可以使用limit来限制展示的数据多少
'''
# 限制展示条数
select * from emp limit 5;  # 只展示前五条的数据
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;

# 分页显示
select * from emp limit 5,5;  # 第一个参数代表从第几个位置开始，第二个参数代表显示多少条

• 关键字之regexp正则

regexp正则查询，和MySQL自带的模糊查询效果类似
例：查询名字以'j'开头以'n'或者'y'结尾的员工信息
	select * from emp where name regexp '^j.*(n|y)$';