MySQL多表查询案例

先看我的表数据

dept表

emp表

salgrade表

student表

course表

student_course表

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

 4.查询所有年龄大于40岁的员工，及归属的部门名称；如果员工没有分配部门， 也需要展示出来

-- emp, dept
-- 连接条件:emp.dept_id = dept.id 
-- 外连接

select e.*, 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.local 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, dept, salgrade
-- 连接条件 1.emp和salgrade 条件emp.salary between salgrade.local and salgrade.hisal  2. emp.dept_id = dept.id
-- 查询条件：研发部 dept.name = '研发部';

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 = '研发部' );

7.查询 “研发部的平均工资”
-- 表emp, dept
-- 连接条件 emp.dept_id = dpet.id 

select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';

8.查询工资比“灭绝”高的员工信息

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

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

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

0 查询低于本部门平均工资的员工信息
-- a. 查询指定部门平均薪资
select avg(e1.salary) from emp e1 where e1.dept_id = 1; 
select avg(e1.salary) from emp e1 where e1.dept_id = 2; 

-- b. 查询低于本部门平均工资的员工信息

select * 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;

12. 查询所有学生的选课情情况，展示出学生名称，学号，课程名称 
-- 表：student, course, student_course
-- 连接条件stduent.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; 

 MySQL多表查询的总结