多表查询案例

数据环境准备:

sql

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薪资等级表。

查询员工的姓名、年龄、职位、部门信息（隐式内连接）
表: emp , dept
连接条件: emp.dept_id = dept.id
sql
```
select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;
```
查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
表: emp , dept
连接条件: emp.dept_id = dept.id
sql
```
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;
```
查询拥有员工的部门ID、部门名称
表: emp , dept
连接条件: emp.dept_id = dept.id
sql
```
select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;
```
查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)
表: emp , dept
连接条件: emp.dept_id = dept.id
sql
```
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ;
```

查询所有员工的工资等级

表: emp , salgrade

连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal

sql

-- 方式一
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;

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

表: emp , salgrade , dept

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

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

sql

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

查询 "研发部" 员工的平均工资

 表: `emp` , `dept` 

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

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

查询工资比 "灭绝" 高的员工信息。

查询 "灭绝" 的薪资

sql

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

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

sql

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

查询比平均薪资高的员工信息
- 查询员工的平均薪资
  sql
```
select avg(salary) from emp;
```
- 查询比平均薪资高的员工信息
  sql
```
select * from emp where salary > ( select avg(salary) from emp );
```

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

查询指定部门平均薪资

sql

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

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

sql

select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );

查询所有的部门信息, 并统计部门的员工人数

sql

select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数' from dept d;

查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
表: student , course , student_course
连接条件: student.id = student_course.studentid , course.id = student_course.courseid
sql
```
select s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;
```

多表查询案例 ​

多表查询案例