MySql----基础入门（七）---多表查询练习案例-优快云博客

本文链接：https://blog.youkuaiyun.com/ABCabc0286/article/details/144492068

7.1，多表查询案例：

数据环境准备:

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 inner join dept d on e.dept_id = d.id where
age <30;

3). 查询拥有员工的部门ID、部门名称

表: emp , dept

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

select distinct e.dept_id,d.name from emp e,dept d where e.dept_id =d.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 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.*,d.name,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 = dept.id

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

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

①. 查询 "灭绝" 的薪资

select name,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(e.salary),d.name from emp e ,dept d where e.dept_id =1 and d.id=1;

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

select e.*,d.name  from emp e,dept d where e.salary <
                                         (select avg(salary)
                                          from emp e1
                                          where e1.dept_id =e.dept_id) and e.dept_id = d.id;

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

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

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

表: student , course , student_course

连接条件: student.id = student_course.studentid , course.id = student_course.courseid

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