MySQL 数据库 之 外键 & SQL查询语句详解

内容概要

• 外键
• 表与表之间关系
  • 一对多
  • 多对多
  • 一对一
  • 没有关系
• 操作表的SQL语句（ALTER）
• SQL 查询关键字
  • 查询语句之 where
  • 查询语句之分组 group by
  • 分组后筛选 having
  • 去重 distinct
  • 排序 order by
  • 限制查询数据条数 limit
  • 正则查询 regexp
  • 聚合函数函数 max min sum avg count
  • 连接字段 concat

内容详细

外键

什么是外键？

用于建立表与表之间联系的字段，也是一种约束条件，

在 SQL 语句中，建立外键的关键字是 ：

foreign key(本表字段名) references 被关联的表名(id)

foreign key(dep_id) references dep(id)

为什么要有外键？

假设有这样一张员工信息数据表：

![image-20220221151359232]

上面这张表有以下缺陷：

• 1、表的重点不突出，到底是员工表还是部门表
• 2、表中部门和部门信息两个字段的数据很多在重复
• 3、表的拓展性极差，只要教学部改一个名字，整张表都要跟着改掉，牵一发而动全身

如何建立外键？

解决以上表的缺陷：

将上述一张表拆分成两张表，然后通过给一个表添加一个外键字段，将两张表联系起来

外键字段>>>:部门编号

其实就是用来标识表与表之间的数据关系

简单的理解为该字段可以让你去到其他表中查找数据

表与表之间关系

表关系总共就四种：

一对多、多对多、一对一、没有关系

判断表关系的方式:换位思考

补充：级联更新，级联删除，也就是两表之间的数据改动可以进行同步

    on update cascade  # 级联更新
    on delete cascade,  # 级联删除

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

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  # 级联删除
);

3.一对一表关系

一对一与一对多的区别是，一对一外键字段必须唯一

	作者表与作者详情表
    	先站在作者表的基础之上
        	问:一个作者信息能否对应多个作者详情信息
            答:不可以
        再站在作者详情表的基础之上
        	问:一个作者详情信息能否对应多个作者信息
            答:不可以
        结论:两个都不可以 
            那么表关系可能是"一对一"或者"没有关系"
            # 外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中

SQL 语句创建：

# 表一
create table author(
    id int primary 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)
);

**补充： **

1.在创建表的时候 需要先创建被关联表(没有外键字段的表)
2.在插入新数据的时候 应该先确保被关联表中有数据
3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据
4.在修改和删除被关联表中的数据的时候 无法直接操作
如果想要数据之间自动修改和删除需要添加额外的配置

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

操作表的SQL语句（ALTER）

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 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

SQL 查询关键字

数据准备：

# 数据准备
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

from控制的是查询哪张表
select控制的是查询表里面的哪些字段
	select * from emp;
    select id,name from emp;

查询关键字之where筛选

where筛选功能 

"""
模糊查询:没有明确的筛选条件
	关键字:like
	关键符号:
		%:匹配任意个数任意字符
		_:匹配单个个数任意字符
show variables like '%mode%se';
"""
# 1.查询id大于等于3小于等于6的数据
select id,name 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字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
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 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 = NULL;  # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

聚合函数

聚合函数主要就是配合分组一起使用
max min sum count avg

查询关键字之group by分组

有些查询条件，是要对表的某一些字段进行分组查询的

比如： 查询每个部门的平均薪资，需要对部门字段进行分组

按照某个指定的条件将单个单个的个体分成一个个整体
	eg:  按照男女将人分组
		按照肤色分组
		按照年龄分组

分组后只能获取分组的那个字段的数据，其他数据都不能直接获取

但是在5.6版本中，获取了也不会报错，需要手动设置严格模式：

set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

# 数据分组应用场景：每个部门的平均薪资，男女比例等

# 1.按部门分组
select * from emp group by post;  # 分组后取出的是每个组的第一条数据
select id,name,sex from emp group by post;  # 验证
"""
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取
"""
set global sql_mode="strict_trans_tables,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;
统计的时候只要是非空字段 效果都是一致的 
这里显示age，salary，id最后演示特殊情况post_comment

**补充： **

# 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;

having过滤

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

我们一定要有一个简单的认识 一条SQL语句的结果也可以看成是一张全新的表
"""
select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;

关键字之distinct去重

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

关键字之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;

关键字之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;

关键字之regexp正则

select * from emp where name regexp '^j.*(n|y)$';

判断表关系

	# 班级表
    cid	caption
    # 学生表
    sid sname gender class_id
    # 老师表
    tid	tname
    # 课程表
    cid	cname	teacher_id
    # 成绩表
    sid	student_id course_id number

1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

1. 查询岗位名以及岗位包含的所有员工名字

select post as '岗位',group_concat(name) as '员工' from emp group by post;

2. 查询岗位名以及各岗位内包含的员工个数

 select post as '岗位',count(post) from emp group by post;

3. 查询公司内男员工和女员工的个数

select sex as '性别',count(sex) as '人数' from emp group by sex;

4. 查询岗位名以及各岗位的平均薪资

select post as '岗位',avg(salary) as '平均薪资' from emp group by post;

5. 查询岗位名以及各岗位的最高薪资

select post as '岗位',max(salary) as '最高薪资' from emp group by post;

6. 查询岗位名以及各岗位的最低薪资

select post as '岗位',min(salary) as '最低薪资' from emp group by post;

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

 select sex as '性别',group_concat(name) as '员工',avg(salary) as '平均薪资' from emp group by sex;