多表查询

记录相关操作之多表查询

一.多表查询介绍

多表之间查询可以使用 链表 和 子查询 或者搭配查询

二. 准备表

# 建表
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