一文搞懂MySQL 多表查询案例详解

create table student(
	id int auto_increment primary key comment '主键id',
	name varchar(10) comment '姓名',
	no varchar(10) comment '学号'
) comment '学生表';
insert into student values(null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');

create table course(
	id int auto_increment primary key comment '主键id',
	name varchar(10) comment '课程名称'
)comment '课程表';
insert into course values(null,'java'),(null,'php'),(null,'mysql'),(null,'hadoop');

create table student_course(
	id int auto_increment comment '主键' primary key,
	studentid int not null comment '学生id',
	courseid int not null comment '课程id',
	constraint fk_courseid foreign key (courseid) references course (id),
	constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';
insert into student_course values(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

create table tb_user(
	id int auto_increment primary key comment '主键id',
	name varchar(10) comment '姓名',
	age int comment '年龄',
	gender char(1) comment '1:男, 2:女',
	phone char(11) comment '手机号'
)comment '用户基本信息表';
insert into tb_user(id, name, age, gender, phone) values
(null, '黄渤', 45, '1', '18800001111'),
(null, '冰冰', 35, '2', '18800002222'),
(null, '码云', 55, '1', '18800008888'),
(null, '李彦宏', 50, '1', '18800009999');

create table tb_user_edu(
	id int auto_increment primary key comment '主键id',
	degree varchar(20) comment '学历',
	major varchar(50) comment '专业',
	primaryschool varchar(50) comment '小学',
	middleschool varchar(50) comment '中学',
	university varchar(50) comment '大学',
	userid int unique comment '用户id',
	constraint fk_userid foreign key (userid) references tb_user(id)
)comment '用户教育信息表';
insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
(null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院',1),
(null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院',2),
(null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学',3),
(null, '本科', '应用数学', '阳泉区第一小学', '阳泉区第一中学', '清华大学',4);

select 字段列表 from 表1, 表2 where 连接条件 and 筛选条件;

select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;

-- 查询每一个员工的姓名及关联部门的名称
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
-- （起别名）
select e.name, de.name from emp e, dept de where e.dept_id = de.id;

-- 显式查询
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

select 字段列表 from 表1 left [outer] join 表2 on 条件...;

select 字段列表 from 表1 right [outer] join 表2 on 条件...;

-- 查询emp表的所有数据，和对应的部门信息(左外连接)
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
-- 查询dept表的所有数据，和对应的员工信息（右外连接）
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

-- 查询员工及其所属领导的名字
select a.name, b.name from emp a, emp b where a.managerid = b.id;
-- 查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id; 

-- 总目标：查询"销售部"的所有员工信息
-- a.查询"销售部"的所有员工信息 (查出来是4)
select id from dept where name = '销售部';
-- b.查询销售部部门id, 查询员工信息
select * from emp where dept_id = 4;
-- 合并：4就是a查出来的结果，直接替换即可
select * from emp where dept_id = (select id from dept where name = '销售部');
-- 总：查询“方东白”之后入职的员工信息
-- a.查询“方东白”的入职时间
select entrydate from emp where name = '方东白';
-- b.查询所有入职时间晚于此时间的员工信息
select * from emp where entrydate > '2009-02-12';
-- 总：
select * from emp where entrydate > (select entrydate from emp where name = '方东白');

-- 查询"销售部"和"市场部"的所有员工信息
select * from emp where dept_id in (select id from dept where dept.name = '销售部' or '市场部');
-- 查询比 财务部 所有人工资都高的员工信息(max 和 any 都可以)
-- 先查财务部的id,再查财务部最高的薪水，然后是大于这个薪水的人员信息
select * from emp where salary > (select max(salary) from emp where dept_id = (select id from dept where name = '财务部'));
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '财务部'));
-- 查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '研发部'));

-- 查询与"张无忌"的薪资及直属领导相同的员工信息
-- 1.先查出来张无忌的薪资和领导
select salary, managerid from emp where name = '张无忌';
-- 查出来薪资和领导一样的员工信息
select * from emp where (salary, managerid) = (12500,1);
-- 总和：
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

-- 查询与"鹿杖客","宋远桥"的职位和薪资相同的员工信息
-- 1.先查询两个人的职位和薪资
select job, salary from emp where name in('鹿杖客','宋远桥');

-- 2.查询职位和薪资在表中有的信息
select * from emp where (job, salary) in (select job, salary from emp where name in('鹿杖客','宋远桥'));

-- 查询入职日期是 2006-01-01 之后的员工信息，及其部门信息
-- 1.入职日期之后的员工信息
select * from emp where entrydate > '2006-01-01';
-- 2.查询这部分员工对应的部门信息
select a.*, b.* from 这部分表 a left join dept b on a.dept_id = b.id;
-- 总：
select a.*, b.* from (select * from emp where entrydate > '2006-01-01') a left join dept b on a.dept_id = b.id;

create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

-- 1. 查询员工的姓名、年龄、职位、部门信息。
select e.name, age, job, d.name from emp e, dept d where e.dept_id = d.id;
-- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
select e.name, age, job, d.name from emp e, dept d where e.dept_id = d.id and age < 30;
select e.name, e.age, e.job, d.name, from emp e join dept d on e.dept_id = d.id where e.age < 30;
-- 3. 查询拥有员工的部门id、部门名称。
select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;
select e.dept_id, d.name from emp e, dept d where e.dept_id = d.id group by e.dept_id, d.name having count(e.dept_id) > 0;
-- 4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
-- 5. 查询所有员工的工资等级。
select e.*, s.grade from emp e, salgrade s where e.salary between s.losal and s.hisal;
select e.*, s.grade from emp e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
-- 6. 查询"研发部"所有员工的信息及工资等级。
-- 先在dept找研发部id，然后在emp筛研发部信息所有员工信息，然后求工资等级
select e.*, s.grade from emp e, dept d, salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal) and d.name = '研发部';
select e.*, s.grade from (select * from emp where dept_id = (select id from dept where name = '研发部')) e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
-- 7. 查询"研发部"员工的平均工资。
-- 先查出来研发部的id, 然后再算所有id一样的人的工资的平均值
select avg(salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';
select avg(salary) from emp where dept_id = (select id from dept where name = '研发部');
-- 8. 查询工资比"灭绝"高的员工信息。
select * from emp where salary > (select salary from emp where name = '灭绝');
-- 9. 查询比平均薪资高的员工信息
select * from emp where salary > (select avg(salary) from emp);
-- 10. 查询低于本部门平均工资的员工信息。
-- 外层查询每一行，内层查询计算该员工所在部门的平均工资，然后比较。
-- 
select * from emp e where e.salary < (select avg(salary) from emp where e.dept_id = dept_id);
-- 11. 查询所有的部门信息，并统计部门的员工人数。
select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;
-- 12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称
select  s.name '学生名称', s.no '学号', c.name '课程名称' from student s, course c, student_course sc where s.id = sc.studentid and c.id = sc.courseid;