MySQL--多表查询，案例练习

一、查询中要用到的表

emp

dept

salgrade

student

course

student_course

二、代码练习

-- ------------------------> 多表查询案例 <------------------------
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. 查询员工的姓名、年龄、职位、部门信息 (隐式内连接)
-- 表：emp dept
-- 连接条件: emp.dept_id = dept id
-- 内连接
select e.name,e.age,e.job,d.* from emp e,dept d where e.dept_id = d.id;

-- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息 (显示内连接)
-- 表：emp dept
-- 连接条件: 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、部门名称
-- 表: emp dept
-- 连接条件:d.id = e.dept_id
-- 内连接
select distinct d.id,d.name from dept d join emp e on d.id = e.dept_id;

-- 4. 查询所有年龄大于40岁的员工，及其归属部门名称；如果员工没有分配部门，也需要展示出来
-- 表: emp dept
-- 连接条件:d.id = e.dept_id
-- 外连接
select e.*,d.name from emp e left join dept d on d.id = e.dept_id where e.age > 40;

-- 5. 查询所有员工的工资等级
-- 表:emp salgrade
-- 连接条件: emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
-- 内连接
select e.*,s.grade,s.losal,s.hisal from emp e ,salgrade s where e.salary >= s.losal and e.salary <= s.hisal;

select e.*,s.grade,s.losal,s.hisal from emp e ,salgrade s where e.salary between s.losal and s.hisal;

-- 6. 查询“研发部”所有员工的信息及工资等等级
-- 表:emp salgrade dept
-- 连接条件: emp.salary between salgrade.losal and salgrade.hisal ,emp.dept_id = dept.id
-- 查询条件: dept.name = “研发部”
-- 内连接
select e.*, s.grade
from emp e,
     dept d,
     salgrade s
where e.salary between s.losal and s.hisal
  and e.dept_id = d.id
  and d.name = '研发部';

-- 7. 查询“研发部”员工的平均工资
-- 表:emp dept
-- 连接条件:emp.dept_id = dept.id
-- 聚合函数 内连接
select avg(e.salary) from emp e,dept d where e.dept_id = d.id and d.name = '研发部';

-- 8. 查询工资比“灭绝”高的员工信息
-- 表:emp
-- 标量子查询
select e.* from emp e where salary > (select e.salary from emp e where e.name = '灭绝');

-- 9. 查询比平均工资高的员工信息
-- 表:emp
-- 聚合函数 标量子查询
select e.* from emp e where salary > (select avg(e.salary) from emp e);

-- 10. 查询低于本部门平均工资的员工信息
-- 表:emp dept
-- 自连接
-- a.查询指定部门的平均薪资
select avg(e1.salary) from emp e1 where e1.dept_id = 1;

-- b.查询低于本部门平均薪资的员工信息
select e2.*, (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id) '当前部门平均薪资'
from emp e2
where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);

-- 11. 查询所有的部门信息，并统计部门的员工人数
-- 子查询
select d.id,d.name,(select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;

select count(*) from emp where dept_id = 1;

-- 12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称
-- 表 student,course,student_course
-- 连接条件: student.id = student_course.studentid , course.id = student_course.courseid

select s.name, s.no, c.name
from student s,
     student_course sc,
     course c
where s.id = sc.studentid
  and c.id = sc.courseid;