mysql-实战案例 （超详细版）

 欢迎您来到我的MySQL基础复习专栏

☆* o(≧▽≦)o *☆哈喽~我是小小恶斯法克

✨博客主页：小小恶斯法克的博客

该系列文章专栏：重拾MySQL

文章作者技术和水平很有限，如果文中出现错误，希望大家能指正

感谢大家的关注！ ❤️

​

目录

​​​​​​多表查询综合案例

✨查询员工的姓名、年龄、职位、部门信息 （隐式内连接，需要消除笛卡尔积）

✨查询年龄小于29岁的员工的姓名、年龄、职位、部门信息（显式内连接）

✨查询拥有员工的部门ID、部门名称 （难点在于有一个部门是没有员工的）

✨查询所有年龄大于41岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(因为没有部门的也要展示，只能使用外连接,外连接大部分采用左外，因为右外也可以通过调换表顺序改为左外)

✨查询所有员工的工资等级 （发现跟部门没有关系，而员工表中只有工资，没有等级，所以涉及新表salgrade）

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

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

✨查询工资比 "n" 高的员工信息。

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

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

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

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

---

​​​​​​多表查询综合案例

数据环境准备:

create table salgrade(grade int, --工资等级lowsal int, --这个等级的最低工资hightsal 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个需求即可，而这里主要涉及到的表就三张：empcp员工表、dept部门表、salgrade工资等级表 。

---

✨查询员工的姓名、年龄、职位、部门信息 （隐式内连接，需要消除笛卡尔积）

需要用的表 : empcp , dept

需要的连接条件: empcp.dept_id = dept.id

这里再重复一下，如果给表起了别名你就必须要用别名去定义字段了

select e.name , e.age , e.job , d.name from empcp e , dept d where e.dept_id = d.id;

 执行：

​

---

✨查询年龄小于29岁的员工的姓名、年龄、职位、部门信息（显式内连接）

需要用的表 : empcp , dept

需要的连接条件: empcp.dept_id = dept.id

在DQL语句中我们的查询条件在哪个关键字之后？是在where之后

select e.name , e.age , e.job , d.name from empcp e inner join dept d on e.dept_id =d.id where e.age   
执行：
 
​
 
✨查询拥有员工的部门ID、部门名称 （难点在于有一个部门是没有员工的）
   
需要用的表 : empcp , dept
  
需要的连接条件: empcp.dept_id = dept.id
    
换个思路：我们要去查拥有员工的部门，其实就是查询部门表和员工表之间的交集，怎么去查询两个表之间的交集，其实实际就是用内连接
  
select  * from empcp e , dept d where e.dept_id = d.id ;
  
而我们只要返回部门id，和部门名称，所以把*改为d.id,d.name
  
​
  
发现重复，去重，用关键字distinct
    
select distinct d.id , d.name from empcp e , dept d where e.dept_id = d.id;
  
执行：
 
​
 
✨查询所有年龄大于41岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
 
(因为没有部门的也要展示，只能使用外连接,外连接大部分采用左外，因为右外也可以通过调换表顺序改为左外)
   
需要用的表 : empcp , dept
  
需要的连接条件: empcp.dept_id = dept.id
    
思路：
  
select * from empcp e left join dept d on e.dept_id = d.id where e.age > 40;
  
然后把*换成员工表的所有字段e.*，和部门名称d.name，如下
    
select e.*, d.name from empcp e left join dept d on e.dept_id = d.id where e.age >41 ;
  
执行：
 
​
 
✨查询所有员工的工资等级 （发现跟部门没有关系，而员工表中只有工资，没有等级，所以涉及新表salgrade）
   
需要用的表 : empcp , salgrade
  
需要的连接条件 : empcp.salary >= salgrade.lowsal and empcp.salary = s.lowsal and e.salary = s.lowsal and e.salary  ( select salary from empcp where name = 'n' );--后面其实就是刚刚查出来的14000，这个14000就是上面那条语句查询返回的结果，所以我们要通过子查询来实现，把上面的语句放到下面，然后再加上括号

执行：

​

执行：

​

---

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

思路：

拆解为两步

1.查询员工的平均薪资

select avg(salary) from empcp;

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

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

执行：

​

执行：

​

---

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

思路：

拆解为两步

1.查询指定部门平均薪资

比如查询1号部门的平均薪资

select * from emp e1 where e1.dept_id = 1

*改avg(e1.salary) 此时只能用别名e1

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

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

思路：

1.首先查询出所有的员工数据

2.select * from empcp；

3.此时所有的员工数据都拿到了，在此基础上增加一个查询条件，salary