10--MySQL数据查询应用（实战）（一）

迈不过友情╰ 2022-06-15 03:21 237阅读 0赞

**现有如下两个数据表emp和dept，数据如下：**

**员工信息表：emp**  
![这里写图片描述][SouthEast]

**部门表：dept**  
![这里写图片描述][SouthEast 1]

**表格字段说明：**  
![这里写图片描述][SouthEast 2]

**两个表需要插入的数据**

> **emp表：**  
> INSERT INTO emp VALUES(7369,’SMITH’,’CLERK’,7902,’1980-12-17’,800,NULL,20);  
> INSERT INTO emp VALUES(7499,’ALLEN’,’SALESMAN’,7698,’1981-02-20’,1600,300,30);  
> INSERT INTO emp VALUES(7521,’WARD’,’SALESMAN’,7698,’1981-02-22’,1250,500,30);  
> INSERT INTO emp VALUES(7566,’JONES’,’MANAGER’,7839,’1981-04-02’,2975,NULL,20);  
> INSERT INTO emp VALUES(7654,’MARTIN’,’SALESMAN’,7698,’1981-09-28’,1250,1400,30);  
> INSERT INTO emp VALUES(7698,’BLAKE’,’MANAGER’,7839,’1981-05-01’,2850,NULL,30);  
> INSERT INTO emp VALUES(7782,’CLARK’,’MANAGER’,7839,’1981-06-09’,2450,NULL,10);  
> INSERT INTO emp VALUES(7788,’SCOTT’,’ANALYST’,7566,’1987-04-19’,3000,NULL,20);  
> INSERT INTO emp VALUES(7839,’KING’,’PRESIDENT’,NULL,’1981-11-17’,5000,NULL,10);  
> INSERT INTO emp VALUES(7844,’TURNER’,’SALESMAN’,7698,’1981-09-08’,1500,0,30);  
> INSERT INTO emp VALUES(7876,’ADAMS’,’CLERK’,7788,’1987-05-23’,1100,NULL,20);  
> INSERT INTO emp VALUES(7900,’JAMES’,’CLERK’,7698,’1981-12-03’,950,NULL,30);  
> INSERT INTO emp VALUES(7902,’FORD’,’ANALYST’,7566,’1981-12-03’,3000,NULL,20);  
> INSERT INTO emp VALUES(7934,’MILLER’,’CLERK’,7782,’1982-01-23’,1300,NULL,10);
> 
> **dept表：**  
> INSERT INTO dept VALUES(10, ‘ACCOUNTING’, ‘NEW YORK’);  
> INSERT INTO dept VALUES(20, ‘RESEARCH’, ‘DALLAS’);  
> INSERT INTO dept VALUES(30, ‘SALES’, ‘CHICAGO’);  
> INSERT INTO dept VALUES(40, ‘OPERATIONS’, ‘BOSTON’);

--------------------

**根据提供的emp和dept表查询下列信息：**  
工资=月薪+奖金  
1、工资高于JONES的员工  
2、查询与SCOTT同一个部门的员工  
3、工资高于30号部门所有人工资的员工信息  
4、查询工作和工资与MARTIN（马丁）完全相同的员工信息  
5、有2个以上直接下属的员工信息  
6、查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

--------------------

**查询sql语句以及查询结果：**

**– 1、工资高于JONES的员工。**  
– 1.1第一步：先找出JONES员工的工资  
SELECT sal FROM emp WHERE ename=’JONES’;  
– 1.2 工资高于JONES的员工。  
SELECT \* FROM emp WHERE sal>( SELECT sal FROM emp WHERE ename=’JONES’);

![这里写图片描述][SouthEast 3]

**– 2、查询与SCOTT同一个部门的员工。**  
– 2.1 第一步：现查找SCOTT所在部门  
SELECT deptno FROM emp WHERE ename=’SCOTT’;  
– 2.2 查找步骤1得到的部门号里的所有员工  
SELECT \* FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename=’SCOTT’);

![这里写图片描述][SouthEast 4]

**– 3、工资高于30号部门所有人工资的员工信息**

> **– 第一种方案：max**  
> – 3.1 先找出30号部门工资最高的人:2850(一个)  
> SELECT MAX(sal) FROM emp WHERE deptno=30;  
> – 3.2 查找出工资大于2850的人  
> SELECT \* FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);

![这里写图片描述][SouthEast 5]

> **– 第二种方案：all 所有**  
> – 3.11 先查出部门的所有人的工资集合  
> SELECT sal FROM emp WHERE deptno=30;  
> – 3.12 查出工资高于30号部门所有人的员工信息  
> SELECT \* FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

**– 4、查询工作和工资与MARTIN（马丁）完全相同的员工信息**  
– 4.1 先查出MARTIN（马丁）的工作和工资  
SELECT job,sal FROM emp WHERE ename=’MARTIN’;  
– 4.2 查询工作和工资与MARTIN（马丁）完全相同的员工信息 (job,sal)表示一个集合； in (集合) 包含  
SELECT \* FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename=’MARTIN’);

![这里写图片描述][SouthEast 6]

**– 5、有2个以上直接下属的员工信息**  
– 思考过程 按照mgr分组，查找每个mgr的个数：SELECT mgr,COUNT(mgr) FROM emp GROUP BY mgr;  
– 5.11 条件：只要大于2的mgr  
SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>2;  
– 5.12 查找符合条件的mgr下的员工信息  
SELECT \* FROM emp WHERE mgr IN(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>2);

![这里写图片描述][SouthEast 7]

**– 6、查询员工编号为7788的员工名称、员工工资、部门名称、部门地址**  
– 6.1 查询员工编号为7788的员工名称、员工工资  
SELECT deptno,ename,sal FROM emp WHERE empno=7788;  
– 6.2 查询指定部门编号的部门名称，部门地址  
SELECT dname,loc FROM dept;  
– 多表查询:使用主外键关系去除重复信息  
SELECT e.empno,e.ename,e.sal,d.dname,d.loc FROM emp e,dept d WHERE empno=7788 AND e.deptno= d.deptno;

![这里写图片描述][SouthEast 8]

[SouthEast]: /images/20220615/2d18a71ebb0e4c7caf49ac9ecdbeaaaa.png
[SouthEast 1]: /images/20220615/d8bed8133b0d41019fb67783208456d3.png
[SouthEast 2]: /images/20220615/15229f311ffc4d2cab4327832604df6f.png
[SouthEast 3]: /images/20220615/7bf5ddc54bf94f0cbe3cbe090c6ac0b7.png
[SouthEast 4]: /images/20220615/c99b8713f4244e98b75bc997c9e63c93.png
[SouthEast 5]: /images/20220615/7739cecd142a446c91c553da4d849906.png
[SouthEast 6]: /images/20220615/0110264f3f174af59202152e6343a819.png
[SouthEast 7]: /images/20220615/69baf341865b4092bf13b452d5c94623.png
[SouthEast 8]: /images/20220615/734f565dfebc41bb9a87a1f56dac304e.png