记录相关操作之多表查询
一. 介绍
本节主题
- 多表连接查询
- 复合条件连接查询
- 子查询
二. 准备表
# 建表
create table dep(
id int,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'爆破');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
drop table emp;
drop table dep;
三. 笛卡尔积
select * from dep,emp; # 结果: 笛卡尔积
select * from emp,dep where emp.dep_id=dep.id;
select emp.id,emp.name,emp.sex,emp.age,dep.name from emp,dep
where emp.dep_id=dep.id;
四. 多表连接查询
1. 外连接语法
select 字段列表
from 表1 inner|left|right join 表2
on 表1.字段 = 表2.字段;
2. 四种外连接介绍
"""
MySQL也知道你在后面查询数据过程中肯定会经常用到拼表操作
所以特地给你开设了对应的方法:
inner join 外连接之内连接(只拼接2张表中共有的数据部分)
1eft join 外连接之左连接(左表所有的数据都展示出来, 没有对应的项就用NULL)
right join 外连接之右连接(右表所有的数据都展示出来, 没有对应的项就用NULL)
union 外连接之全连接(全连接左右两 表所有的数据都展示出来)
注意：mysql不支持全外连接 full join
强调：mysql可以使用下面的方式间接实现全外连接.
"""
# 内连接
select * from emp inner join dep on emp.dep_id=dep.id;
# 左连接
select * from emp left join dep on emp.dep_id=dep.id;
# 右连接
select * from emp right join dep on emp.dep_id=dep.id;
# 全连接
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;
select * from emp union dep on emp.dep_id=dep.id;
五. 符合条件连接查询
# 以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select emp.name,dep.name
from emp inner join dep on emp.dep_id=dep.id
where emp.age>25;
以内连接的方式查询emp和dep表，并且以age字段的升序方式显示
select emp.id, emp.name, emp.sex, emp.age, dep.name
from emp inner join dep on emp.dep_id=dep.id
order by age asc;
六. 子查询
1. 介绍
# 1：子查询是将一个查询语句嵌套在另一个查询语句中。
# 2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
# 3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
# 4：还可以包含比较运算符：= 、 !=、> 、<等
2. 带in关键字的子查询
"""
子查询就是我们平时解决问题的思路
分步骤解决问题
第一步
第二步
...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
'''
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. 带比较运算符的子查询
# 比较运算符：=、!=、>、>=、<、<=、<>
# 查询大于所有人平均年龄的员工名与年龄
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关键字的子查询
"""
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. 练习: 查询每个部门最新入职的那位员工
准备表和记录
company.emp
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
#创建表
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)
;
# ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
外连接查询
select * from emp order by hire_date;
select post,min(hire_date) as min_hire_date from emp group by post;
select *
from emp inner join (select post,min(hire_date) as min_hire_date from emp group by post) t1
on emp.hire_date=t1.min_hire_date;
子查询(难理解)
# 错误写法
select (select * from emp as t2
where t1.post=t2.post
order by hire_date desc
limit 1)
from emp as t1 group by post;
答案一为正确答案，答案二中的limit 1有问题（每个部门可能有>1个为同一时间入职的新员工），我只是想用该例子来说明可以在select后使用子查询
可以基于上述方法解决：比如某网站在全国各个市都有站点，每个站点一条数据，想取每个省下最新的那一条市的网站质量信息
5.总结
"""
表的查询结果可以作为其他表的查询条件, 也可以通过起别名的方式把它作为一个张虚拟表根其他表关联.
多表查询就两种方式:
先拼接表再查询
子查询 一步一步来
"""
七. 查询语句的优先级
from
笛卡尔积 # 把两张表简单粗暴的连接到一起
on
left join 或者 right join
inner join
where
group by
having
distinct
order by
limit
八. 综合练习
1. 查询所有的课程的名称以及对应的任课老师姓名
# 1. 将teacher表与course关联
SELECT
*
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
# 2. 从关联的表中筛选出课程,老师字段
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
2. 查询平均成绩大于八十分的同学的姓名和平均成绩
# 1. 对score表的student_id对象进行分组拿到学生的student_id,再进行having对平均成绩进行过滤, 保留平均成绩作为输出对象
SELECT
student_id,
avg( num ) AS avg_num
FROM
score
GROUP BY
student_id
HAVING
avg( num )> 80;
# 2. 步骤1得到的表结构中的student_id字段与student表的sid进行连表操作
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY score.student_id HAVING avg( num )> 80 ) AS t1 ON student.sid = t1.student_id;
3. 查询没有报李平老师课的学生姓名
# 1. teacher表与course表连表, 再对连表结果中的李平老师的课程过滤出来
SELECT
course.cid
FROM
teacher
INNER JOIN course ON teacher.tid = course.teacher_id where teacher.tname='李平老师';
# 2. 步骤1得到的新表的cid作为score表的条件, 再对student_id进行分组. 作为student表的条件.
# 注意: 在成绩表中. 学生可以报了李平老师的课同时也包了别的老师的课, 如果仅仅是取反, 派出了该学生仅仅之争对与李平老的课的student_Id, 该学生报了别的老师ide课的student_id还存在, 而这两个student_id都是指向的同一个学生, 这样这个学生还是无法排除的. 我们应该先取到报过李平老师的课程的学生, 再去重, 去重以后就是报了李平老师的student_id们, 这样再通过与学生表就可以进行取反筛选.
# SELECT
# student_id
# FROM
# score
# WHERE
# score.course_id NOT IN ( SELECT course.cid FROM course INNER JOIN teacher ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' )
# GROUP BY
# student_id;
SELECT DISTINCT
student_id
FROM
score
WHERE
score.course_id IN ( SELECT course.cid FROM course INNER JOIN teacher ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' );
# 3. 步骤2得到的新表的student_id结果作为student的条件.
SELECT
student.sname
FROM
student
WHERE
student.sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
score.course_id IN ( SELECT course.cid FROM course INNER JOIN teacher ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' )
);
4. 查询没有同时选修物理课程和体育课程的学生姓名(只要选了一门的, 选了2们或者没有选的都不要)
# 1. 通过course表拿到对于物理课程和体育课程的cid, 作为score的条件
SELECT
cid
FROM
course
WHERE
cname IN ( '物理', '体育' );
# 2. 拿到步骤1的查询结果作为score的where条件,再对student_id进行分组, 再使用having过滤出每个组中学生选修课程数量要等于1
SELECT
student_id
FROM
score
WHERE
score.course_id IN (
SELECT
cid
FROM
course
WHERE
cname IN ( '物理', '体育' ))
GROUP BY
score.student_id
HAVING
count( course_id )= 1;
# 3. 拿到步骤2的结果作为student的where条件.
SELECT
student.sname
FROM
student
WHERE
student.sid IN (
SELECT
student_id
FROM
score
WHERE
score.course_id IN (
SELECT
cid
FROM
course
WHERE
cname IN ( '物理', '体育' ))
GROUP BY
score.student_id
HAVING
count( course_id )= 1
);
5. 查询挂科超过两门(包括两门)的学生姓名和班级
# 1. 对score表的num字段进行where筛选, 再对student_id进行分组, 对分组的结果进行having过滤出count(course_id)>=2的student_id组.
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count( course_id )>= 2;
# 2. 连接class表和student表
SELECT
class.caption,
student.sname
FROM
student
INNER JOIN class ON student.class_id = class.cid;
# 3. 步骤1作为步骤2的where条件, 输出学生姓名和班级
SELECT
class.caption,
student.sname
FROM
student
INNER JOIN class ON student.class_id = class.cid
WHERE
student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id )>= 2 );
