记录相关操作之多表查询
一.多表查询介绍
多表之间查询可以使用 链表 和 子查询 或者搭配查询
二. 准备表
# 建表
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
三. 笛卡尔积
交叉连接：不适用任何匹配条件。生成笛卡尔积
在sql中可以通过from 俩张表(之间用,隔开) 形成一个笛卡尔积效应,产生的虚拟表为一个强耦合俩张表的一张新表;
-- 例如:
select * from employee,department;
-- 生成的表的结构为
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
....
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+
-- 加上过滤条件
select * from employee,department where employee.dep_id=department.id;
-- 生成一个有对应关系的虚拟表
+----+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+--------+------+--------+------+--------------+
四.多表连接查询
通过上面的笛卡尔积,就可以衍生到我们的多表连接查询
1.外连接语法
select 字段1,字段2..
from 表1
inner/left/right join 表2
on 表1.字段=表2.字段;
2.四种外连接介绍
mysql知道我们在后面查询数据过程中肯定会经常使用到连表的操作,所以特地给我们开设了对应的方法.
inner join 外连接之内连接(只拼接2张表中共有的数据部分)
-- 内连接
select * from employee inner join department on employee.dep_id=department.id;
left join 外连接之左连接(左表中所有的数据都展示出来,右表中没有对应的项用null来补充)
-- 左连接
select * from employee left join department on employee.dep_id=department.id;
right join 外连接之右连接(右表中所有的数据都展示出来,左表中没有对应的项用null来补充)
-- 右连接
select * from employee right join department on employee.dep_id=department.id;
full join 外连接之全连接(但mysql不支持)
在mysql中要想创建全连接,得使用 union关键字来实现
具体为将左连接和右连接再拼接,成一个左右表中所有的数据
-- 全连接
select * from employee left join department on employee.dep_id=department.id
union
select * from employee right join department on employee.dep_id=department.id;
五. 符合条件连接查询
-- 以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select emp.name,dep.name from employee as emp
inner join department as dep
on emp.dep_id=dep.id
where emp.age>25;
-- 以内连接的方式查询employee和department表，并且以age字段的降序序方式显示(当年龄一样,比员工id,员工id大的在下面)
select * from employee as emp
inner join department as dep
on emp.dep_id=dep.id
order by age desc,emp.id desc;
六. 子查询
1. 介绍
Copy# 1：子查询是将一个查询语句嵌套在另一个查询语句中。
# 2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
# 3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
# 4：还可以包含比较运算符：= 、 !=、> 、<等
2. 带in关键字的子查询
Copy"""
子查询就是我们平时解决问题的思路
分步骤解决问题
第一步
第二步
...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
'''
1. 先获取部门的id号
2. 再去员工表里面筛选出对应的员工
'''
# 第一步:
select id from dep where name in ('技术', '人力资源');
# 第二步:
select * from emp where dep_id in (200, 201);
# 得出结果:
select * from emp where dep_id in (select id from dep where name in ('技术', '人力资源'));
3. 带比较运算符的子查询
Copy# 比较运算符：=、!=、>、>=、<、<=、<>
# 查询大于所有人平均年龄的员工名与年龄
select avg(age) from emp;
select name,age from emp where age > (select avg(age) from emp);
# 查询大于部门内平均年龄的员工名、年龄
select dep_id,avg(age) from emp group by dep_id;
select emp.name,emp.age
from emp inner join (select dep_id,avg(age) as avg_age from emp group by dep_id) as t1
on emp.dep_id=t1.dep_id
where emp.age > t1.avg_age;
4. 带exists关键字的子查询
Copy"""
exists关字键字表示存在。在使用exists关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
"""
# dep表中存在dept_id=203，Ture
select * from emp
where exists (select id from dep where id=203);
# dep表中不存在dept_id=204，False
select * from emp
where exists (select id from dep where id=204);
5. 子查询的实际应用
# 查询平均年龄在25岁以上的部门名
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
select id,name from department where id = any(select dep_id from employee group by dep_id having avg(age) > 25);
# 查看技术部员工姓名
select name from employee where dep_id = (select id from department where name="技术");
# 查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
# not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理
all同any类似，只不过all表示的是所有，any表示任一
# 查询出那些薪资比所有部门的平均薪资都高的员工
select name from employee where salay > all(select avg(salay) from employee group by dep_id);
# 查询出那些薪资比所有部门的平均薪资都低的员工
select name from employee where salay < all(select avg(salay) from employee group by dep_id);
# 查询出那些薪资比任意一个部门的平均薪资低的员工
select name from employee where salay < any(select avg(salay) from employee group by dep_id);
# 查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
select name from employee where salay > any(select avg(salay) from employee group by dep_id);
比较运算符：=、!=、>、>=、<、<=、<>
# 查询 大于所有人平均年龄的员工名与年龄
select name,age from employee where age > all(select avg(age) from employee);
# 查询大于部门内平均年龄的员工名、年龄
select name,age from employee where age > all(select avg(age) from employee group by dep_id);
select t1.name,t1.age from employee as t1
inner join
(select dep_id,avg(age) as avg_age from employee group by dep_id) as t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
# department表中存在dep_id=203，Ture
select * from employee where exists(select id from department where id = 203);
# 返回
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
# department表中存在dept_id=205，False
select * from employee where exists(select id from department where id = 205);
# 返回
Empty set (0.00 sec)
in与exists
！！！！！！当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率！！！！！！
# exists
exists后面一般都是子查询，后面的子查询被称做相关子查询（即与主语句相关），当子查询返回行数时(非0)，exists条件返回true，
否则返回false，exists是不返回列表的值的，exists只在乎括号里的数据能不能查找出来，是否存在这样的记录。
exists 关键字会将外查询的结果放到括号内的子查询的条件,一一进行匹配,有点像for循环嵌套.但是它不管有没有匹配上,都会将子查询的条件全部匹配一遍,匹配了一次或者一次以上就返回true,否则返回false;
而in 是先拿到子查询的结果集,然后再将外查询的结果进行判断.
# 查询出那些班级里有学生的班级
select * from class where exists (select * from stu where stu.cid=class.id);
# exists的执行原理为：
1、依次执行外部查询：即select * from class
2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)
3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录
# in
in后跟的都是子查询，in()后面的子查询 是返回结果集的
# 查询和所有女生年龄相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')
# in的执行原理为：
in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.
not in与 not exists
# not exists 查询的效率远远高于 not in 查询的效率
# not in() 子查询的执行顺序是:
# 为了证明not in 成立,即找不到,需要一条一条的查询表,符合要求才返回子查询的结果集,不符合的就继续查询下一条记录.直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。
not exists;
如果主查询表中记录少,子查询表中记录多,并有索引.
例如:查询那些班级中没有学生的班级
select * from class
where not exists
(select * from student where student.cid = class.cid)
not exists的执行顺序是：
在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。
应用
准备查询数据,即先建表和插入数据.
create databse db13;
use db13;
create table student(
id int primary key auto_increment,
name varchar(16)
);
create table course(
id int primary key auto_increment,
name varchar(16),
comment varchar(20)
);
create table student2course(
id int primary key auto_increment,
sid int,
cid int,
foreign key(sid) references student(id),
foreign key(cid) references course(id)
);
insert into student(name) values("jkey"),("tom"),("liu"),("song");
insert into course(name,comment) values
("数据库","数据库从入门到删库跑路"),
("python","python从入门到放弃"),
("英语","鸟语花香");
insert into student2course(sid,cid) values
(1,1),
(1,2),
(1,3),
(2,1),
(2,2),
(3,2);
查询实例
# 1、查询选修了所有课程的学生id、name:（即该学生根本就不存在一门他没有选的课程。）
# 方式1
select id,name from student where id = (select sid from student2course group by sid having count(cid) = (select count(id) from course));
# 方式2
select id,name from student s where not exists
(select * from course c where not exists
(select * from student2course s2c where s2c.sid=s.id and s2c.cid=c.id));
# 方式3
select s.id,s.name from student as s
inner join student2course as s2c
on s.id =s2c.sid
group by s.name
having count(s2c.id) = (select count(id) from course);
# 2、查询没有选择所有课程的学生，即没有全选的学生。（存在这样的一个学生，他至少有一门课没有选）
# 没有全选的学生,即指的是该学生没有选课,或者选择的课程没有全选
# 先拿到所有的学生,再拿到所以的课程
# 方法1
select id,name from student where id != (select sid from student2course group by sid having count(cid) = (select count(id) from course));
# 方法2
select id,name from student as s where exists
(select * from course as c where not exists
(select * from student2course as s2c where s2c.sid=s.id and s2c.cid=c.id));
# 方法3
select s.id,s.name from student as s
inner join student2course as s2c
on s.id != s2c.sid
group by s.id
having count(s2c.id) != (select count(id) from course);
# 3、查询一门课也没有选的学生。（不存这样的一个学生，他至少选修一门课程）
select id,name from student as s where not exists
(select * from course as c where exists
(select * from student2course as s2c where s2c.sid=s.id and s2c.cid=c.id));
# 4、查询至少选修了一门课程的学生。
select id,name from student as s where exists
(select * from course as c where exists
(select * from student2course as s2c where s2c.sid=s.id and s2c.cid=c.id));
练习：查询每个部门最新入职的那位员工
-- 准备表
create table emp(
id int not null unique 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
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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)
;
查询每个部门最新入职的那位员工
# 方法1
select name from emp where hire_date in (select max(hire_date) from emp group by post);
# 方法2
select name from emp inner join (select max(hire_date) as m from emp group by post) as t1 on emp.hire_date=t1.m;
6.总结
Copy"""
表的查询结果可以作为其他表的查询条件, 也可以通过起别名的方式把它作为一个张虚拟表根其他表关联.
多表查询就两种方式:
先拼接表再查询
子查询 一步一步来
"""
七. 查询语句的优先级
Copyfrom
笛卡尔积 # 把两张表简单粗暴的连接到一起
on
left join 或者 right join
inner join
where
group by
having
distinct
order by
limit