多表查询案例

数据环境准备

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);

在这个案例中, 我们主要运用上面所讲的多表查询的方法,完成以下 12 个需求即可

而这里主要涉及的表就三张 emp 员工表,dept 部门表,salgrade 薪资等级表

(1) 查询员工的姓名,年龄,职位,部门信息(隐式链接)

表 emp,dept

连接条件: emp.dept_id = dept.id

SELECT e.name , e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;

(2) 查询年龄小于 30 岁的员工姓名,年龄,职位,部门信息(显式链接)

表 emp,dept

连接条件: emp.dept_id = dept.id

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

连接条件 emp.dept_id = dept.id

SELECT DISINCT d.id, d.name from emp e, dept d where e.dept_id = d.id;

(4) 查询所有年龄>40 岁的员工,及其归属的部门名称,如果员工没有分配部门,也需要查询出来(外链)

表 emp,dept

连接条件 emp.dept_id = dept.id

SELECT e.name , e.age, d.name from emp e left join dept d on e.dept_id = d.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;

(6) 查询"研发部"的所有员工信息以及工资等级

表 emp, salgrade ,dept

连接条件: emp.salary between salgrade.losal and salgrade.hisal and emp.dept_id = dept.id

查询条件: dept.name = '研发部'

select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s , dept d 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) 查询工资比"灭绝师太"高的员工信息

• 查询灭绝的薪资

select salary from emp where name = '灭绝师太';

• 查询比她工资高的员工数据

select * from emp where salary > (select salary from emp where name = '灭绝师太');

(9) 查询比平均薪资高的员工信息

• 查询员工的平均薪资

select avg(salary) from emp;

• 查询比平均薪资高的员工信息

select * from emp where salary > (select avg(salary) from emp);

(10)查询低于本部门的平均工资的员工信息

• 查询各部门的平均薪资

select avg(el.salary) from emp e1 where e1.dept_id = 1;
select avg(el.salary) from emp e1 where e1.dept_id = 2;

• 查询低于本部门的平均工资的员工信息

select * from emp e2 where e2.salary < (select avg(el.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;

(12) 查询所有学生的选课情况,展示出学生名称,学号,课程名称

表 student,course,student_course

连接条件: student.id = student_course.student_id and course.id = student_course.course_id

select s.name, s.id, c.name from student s, course c, student_course sc where s.id = sc.student_id and c.id = sc.course_id;