MySQL之多表查询

多表查询

 

数据准备

#建表
create table dep(
id int primary key auto_increment,
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);

 

多表查询的思路

# 查询jason所在的部门名称
    涉及到SQL查询题目 一定要先明确到底需要几张表
    1.先查询jason所在的部门编号
        select dep_id from emp where name='jason';
    2.根据部门编号查询部门名称
        select name from dep where id=(select dep_id from emp where name='jason'); 
    '''一条SQL语句的查询结果既可以看成是一张表也可以看成是查询条件'''
    
'''
多表查询的思路
    1.子查询
        将SQL语句查询的结果括号括起来当做另外一条SQL语句的条件
            大白话:就是我们日常生活中解决问题的方式>>>:分步操作
    2.连表操作(重要)
        先将需要使用到的表拼接成一张大表 之后基于单表查询完成
            inner join    内连接
            left join     左连接
            right join    右连接
            union         全连接
'''

# 涉及到多表查询的时候 字段名称容易冲突 需要使用表名点字段的方式区分

# inner join:只拼接两张表中共有的部分
select * from emp inner join dep on emp.dep_id = dep.id;

# left join:以左表为基准展示所有的内容 没有的NULL填充
select * from emp left join dep on emp.dep_id = dep.id;

# right join:以右表为基准展示所有的内容 没有的NULL填充
select * from emp right join dep on emp.dep_id = dep.id;

# union:左右表所有的数据都在 没有的NULL填充
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;

'''
疑问:上述操作一次只能连接两张表 如何做到多张表?
    将两张表的拼接结果当成一张表与跟另外一张表做拼接
    依次往复 即可拼接多张表
'''

 

多表查询练习题

　　编写SQL不要想着一次性写完 可以边写边看

　　1、查询所有的课程的名称以及对应的任课老师姓名

SELECT
    teacher.tname,
    course.cname
FROM
    teacher
    INNER JOIN course ON teacher.tid = course.teacher_id;

　　2、查询平均成绩大于八十分的同学的姓名和平均成绩

# 1.先确定需要使用到的表 
# 2.在思考多表查询的方式
# 第一步先查询成绩表中 平均成绩大于80的学生编号
# 1.1 按照学生id分组并获取平均成绩
-- select student_id,avg(num) from score group by student_id;
# 1.2 筛选出平均成绩大于80的数据  (针对聚合函数的字段结果 最好起别名防止冲突)
-- select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80;
# 1.3 将上述SQL的结果与student表拼接
SELECT
    student.sname,
    t1.avg_num 
FROM
    student
    INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON student.sid = t1.student_id;

　　3、查询没有报李平老师课的学生姓名

# 1.先查询李平老师教授的课程编号
-- select course.cid from course where teacher_id = 
-- (select tid from teacher where tname ='李平老师');
# 2.根据课程id号筛选出所有报了的学生id号
-- select distinct score.student_id from score where course_id in (select course.cid from course where teacher_id = 
-- (select tid from teacher where tname ='李平老师'));
# 3.去学生表中根据id号取反筛选学生姓名
SELECT
    student.sname 
FROM
    student 
WHERE
    sid NOT IN (
SELECT DISTINCT
    score.student_id 
FROM
    score 
WHERE
    course_id IN ( SELECT course.cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) 
--     );

　　4、查询没有同时选修物理课程和体育课程的学生姓名(只要了报了一门的  两门和一门没报的都不要)

# 1.先获取两门课程的id号
-- select course.cid from course where cname in ('物理','体育');
# 2.再去分数表中先筛选出所有报了物理和体育的学生id(两门 一门)
-- select * from score where course_id in (select course.cid from course where cname in ('物理','体育'));
# 3.如何筛选出只报了一门的学生id  按照学生id分组 然后计数 并过滤出计数结果为1的数据
-- select score.student_id from score where course_id in (select course.cid from course where cname in ('物理','体育'))
-- group by score.student_id
-- having count(score.course_id) = 1;
# 4.根据学生id号去student表中筛选学生姓名
SELECT
    student.sname 
FROM
    student 
WHERE
    sid IN (
SELECT
    score.student_id 
FROM
    score 
WHERE
    course_id IN ( SELECT course.cid FROM course WHERE cname IN ( '物理', '体育' ) ) 
GROUP BY
    score.student_id 
HAVING
    count( score.course_id ) = 1);

　　5、查询挂科超过两门(包括两门)的学生姓名和班级

# 1.先筛选出小于60分的数据
-- select * from score where num < 60;
# 2.按照学生id分组 然后统计挂科数量
-- select student_id,count(course_id) from score where num < 60 group by student_id;
# 3.筛选出挂科超过两门的学生id
-- select student_id from score where num < 60 group by student_id
-- having count(course_id) >=2;
# 4.先将上述结果放在一边 去连接student和class表
SELECT
    student.sname,
    class.caption 
FROM
    class
    INNER JOIN student ON class.cid = student.class_id 
WHERE
    student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );

 

更多练习
https://www.cnblogs.com/Dominic-Ji/p/10875493.html

 

END