MySQL的SELECT查询使用

博主：南凝呀
发布时间：2023 年 10 月 19 日
947 次浏览
暂无评论
22777字数
分类：

<div class="panel panel-default  box-shadow-wrap-lg goal-panel">
    <div class="panel-heading">
        进度条：
    </div>
    <div class="list-group">
   <div class="list-group-item"><div class="checkbox">
                <label class="i-checks">
                    <input type="checkbox" checked="" disabled="" value="">
                    <i></i>
                     基本的select语句 
                </label>
</div><div class="checkbox">
                <label class="i-checks">
                    <input type="checkbox" checked="" disabled="" value="">
                    <i></i>
                     运算符 
                </label>
</div><div class="checkbox">
                <label class="i-checks">
                    <input type="checkbox" checked="" disabled="" value="">
                    <i></i>
                     排序与分页 
                </label>
</div><div class="checkbox">
                <label class="i-checks">
                    <input type="checkbox" checked="" disabled="" value="">
                    <i></i>
                     多表查询 
                </label>
</div><div class="checkbox">
                <label class="i-checks">
                    <input type="checkbox"  disabled="" value="">
                    <i></i>
                     多表查询练习 
                </label>
</div><div class="checkbox">
                <label class="i-checks">
                    <input type="checkbox" checked="" disabled="" value="">
                    <i></i>
                     单行函数 
                </label>
</div><div class="checkbox">
                <label class="i-checks">
                    <input type="checkbox" checked="" disabled="" value="">
                    <i></i>
                     聚合函数 
                </label>
</div><div class="checkbox">
                <label class="i-checks">
                    <input type="checkbox" checked="" disabled="" value="">
                    <i></i>
                     子查询 
                </label>
</div>            <p class="goal_name"> 当前进度：</p>
            <div class="progress-striped active m-b-sm progress" value="dynamic" type="danger">
                <div class="progress-bar progress-bar-success" role="progressbar" aria-valuenow="97" aria-valuemin="0"
                 aria-valuemax="100" style="width: 99%;"><span> 99% </span></div>
            </div></div></div></div>

# 数据

> <span style='color:#2F4F4F'>
>
> 所使用的数据表均打包成数据库，下方安装文章内提供。
>
> </span>
>
> ↓↓↓↓↓

<div class="inner-content" >
<p class="inser-title">MySQL手动安装rpm包安装</p>
<div class="inster-summary text-muted">
And 1.获取RPM包自建服务器暂时拆了，不提供自建备份下载，自行前往mysql.com下载压缩包解压，贴个图：...
</div>
</div>
</a>

</div>

</div>

> ↑↑↑↑↑
>
> 以下所有查询操作记得要先进数据库后操作！！

## And 1.基本的select语句

### And 1.1.【题目】

1. 查询员工12个月的工资总和，并起别名为ANNUAL SALARY
2. 查询employees表中去除重复的job_id以后的数据
3. 查询工资大于12000的员工姓名和工资
4. 查询员工号为176的员工的姓名和部门号
5. 显示表 departments 的结构，并查询其中的全部数据

</div>

#### And 1.1.1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY

```mysql
select employee_id,last_name,salary*12 "ANNUAL SALARY" from employees;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3032310783.png)

#### And 1.1.2.查询employees表中去除重复的job_id以后的数据

```mysql
select distinct job_id from employees;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1357916311.png)

#### And 1.1.3.查询工资大于12000的员工姓名和工资

```mysql
select last_name,salary from employees where salary>12000;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2195429631.png)

#### And 1.1.4.查询员工号为176的员工的姓名和部门号

```mysql
select last_name,department_id from employees where employee_id=176;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/350459894.png)

#### And 1.1.5.显示表 departments 的结构，并查询其中的全部数据

```mysql
desc departments;
select * from departments;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3118930042.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3405628445.png)

## And 2.运算符

### And 2.1.【题目】

1. 选择工资不在5000到12000的员工的姓名和工资
2. 选择在20或50号部门工作的员工姓名和部门号
3. 选择公司中没有管理者的员工姓名及job_id
4. 选择公司中有奖金的员工姓名，工资和奖金级别
5. 选择员工姓名的第三个字母是a的员工姓名
6. 选择姓名中有字母a和k的员工姓名
7. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
8. 显示出表 employees 部门编号在 80-100 之间的姓名、工种
9. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

</div>

#### And 2.1.1.选择工资不在5000到12000的员工的姓名和工资

```mysql
select last_name,salary from employees where salary<5000 or salary>12000;
select last_name,salary from employees where salary not between 5000 and 12000;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2899378446.png)

#### And 2.1.2.选择在20或50号部门工作的员工姓名和部门号

```mysql
select last_name,department_id from employees where department_id=20 or department_id=50;
select last_name,department_id from employees where department_id in(20,50);
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3747958796.png)

#### And 2.1.3.选择公司中没有管理者的员工姓名及job_id

```mysql
select last_name,job_id from employees where manager_id is null;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/183040953.png)

#### And 2.1.4.选择公司中有奖金的员工姓名，工资和奖金级别

```mysql
select last_name,salary,commission_pct from employees where commission_pct is not null;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/713569480.png)

#### And 2.1.5.选择员工姓名的第三个字母是a的员工姓名

```mysql
select last_name from employees where last_name like "__a%";
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1206943420.png)

#### And 2.1.6.选择姓名中有字母a和k的员工姓名

```mysql
select last_name from employees where last_name like '%a%k%' or last_name like '%k%a%';
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/907423179.png)

#### And 2.1.7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息

```mysql
select employee_id,first_name,last_name from employees where first_name like "%e";
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3618263521.png)

#### And 2.1.8.显示出表 employees 部门编号在 80-100 之间的姓名、工种

```mysql
select last_name,job_id from employees where department_id between 80 and 100;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/589959446.png)

#### And 2.1.9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

```mysql
select last_name,salary from employees where manager_id in(100,101,110);
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3884173079.png)

## And 3.排序与分页

### And 3.1.【题目】

1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示
2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据
3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

</div>

#### And 3.1.1.查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示

```mysql
select last_name,department_id,salary*12 annual_sal from employees order by annual_sal desc, last_name asc;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/558340776.png)

#### And 3.1.2.选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据

```mysql
select last_name,salary from employees where salary not between 8000 and 17000 order by salary desc limit 20,20;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2088950518.png)

#### And 3.1.3.查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

```mysql
select last_name,email,department_id from employees where email like '%e%' order by LENGTH(email) desc, department_id asc;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/433109866.png)

## And 4.多表查询

### And 4.1.【题目】

1. 显示所有员工的姓名，部门号和部门名称。
2. 查询90号部门员工的job_id和90号部门的location_id
3. 选择所有有奖金的员工的 last_name , department_name , location_id , city
4. 选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
5. 查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
6. 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees	Emp#		manager	Mgr#

kochhar		101	king			100
7. 查询哪些部门没有员工
8. 查询哪个城市没有部门
9. 查询部门名为 Sales 或 IT 的员工信息

</div>

#### And 4.1.1.显示所有员工的姓名，部门号和部门名称。

```mysql
select last_name, e.department_id, department_name from employees e left outer join departments s d ON e.`department_id` = d.`department_id`;
```

![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/770993547.png)

#### And 4.1.2.查询90号部门员工的job_id和90号部门的location_id

```mysql
select job_id,location_id from employees e join departments d ON e.`department_id`=d.`departmentnt_id` where e.`department_id`=90;
select job_id,location_id from employees e,departments d where e.`department_id`=d.`department__id` AND e.`department_id`=90;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1764189965.png)

#### And 4.1.3.选择所有有奖金的员工的 last_name , department_name , location_id , city

```mysql
select last_name,department_name,d.location_id,city from employees e LEFT outer Join departmentts d ON e.`department_id`=d.`department_id` LEFT outer Join locations l ON d.`location_id`=l.`locationn_id` where e.`commission_pct` is not NULL;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/357780860.png)

#### And 4.1.4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

```mysql
select last_name,job_id,e.department_id,d.department_name from employees e LEFT outer Join depaartments d ON e.`department_id`=d.`department_id` LEFT outer Join locations l ON d.`location_id`=l.`loocation_id` where city='Toronto';
select last_name,job_id,e.department_id,department_name from employees e,departments d,locations l where e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id` AND l.city='Torontoo';
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3549763243.png)

#### And 4.1.5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

```mysql
##同上
select department_name,street_address,last_name,job_id,salary from employees e,departments d,locations l where e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id` And d.`departtment_name`='Executive';
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3828861028.png)

#### And 4.1.6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式\employees|Emp#|manager|Mgr#\\kochhar|101|king|100\

| employees | Emp# | manager | Mgr# |
| :-------: | :--: | :-----: | :--: |
|  kochhar  | 101 |  king  | 100 |

```mysql
select emp.last_name employees,emp.employee_id "Emp#",mgr.last_name manager,mgr.employee_id "Mgr#" from employees emp LEFT outer Join employees mgr ON mgr.`employee_id`=emp.`manager_id`;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/670175113.png)

#### And 4.1.7.查询哪些部门没有员工

```mysql
select d.department_id,d.department_name from departments d LEFT outer Join employees e ON e.`departmedepartment_id`=d.`department_id` where e.`department_id` is null;
select d.department_id,d.department_name from departments d where not EXISTS( select * from employees e where e.`department_id`=d.`department_id` );
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2292640495.png)

#### And 4.1.8.查询哪个城市没有部门

```mysql
select l.location_id,l.city from locations l LEFT outer Join departments d ON l.`location_id`=d.`location_id` where d.`location_id` is null;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3392028600.png)

#### And 4.1.9.查询部门名为 Sales 或 IT 的员工信息

```mysql
select employee_id,last_name,department_name from employees e,departments d where e.`department_id`=d.`department_id` AND d.`department_name` in ('Sales','IT');
select employee_id,last_name,department_name from employees e LEFT outer Join departments d ON e.`department_id`=d.`department_id` where d.`department_name` in ('Sales','IT');
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3783370221.png)

### And 4.2.【练习】

暂定。

## And 5.单行函数

### And 5.1.【题目】

1. 显示系统时间(注：日期+时间)
2. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
4. 查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
5. 查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
6. 查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空
7. 查询公司中入职超过10000天的员工姓名、入职时间
8. 做一个查询，产生下面的结果：

`<last_name> earns <salary> monthly but wants <salary*3>`

job					grade
   AD_PRES				A
   ST_MAN				B
   IT_PROG				C
   SA_REP				D
   ST_CLERK			E

产生下面的结果：

| Last_name | job_id | Grade |
   | :-------: | :-----: | :---: |
   |   King   | AD_PRES |   A   |

</div>

#### And 5.1.1.显示系统时间(注：日期+时间)

```mysql
SELECT NOW() FROM DUAL;
```

![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/1849259266.png)

#### And 5.1.2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

```mysql
SELECT employee_id, last_name, salary, salary * 1.2 "new salary" FROM employees;
```

![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/4157876955.png)

#### And 5.1.3.将员工的姓名按首字母排序，并写出姓名的长度（length）

```mysql
SELECT last_name, LENGTH(last_name) FROM employees ORDER BY last_name DESC;
```

![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/2909023181.png)

#### And 5.1.4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT

```mysql
SELECT CONCAT(employee_id, ',' , last_name , ',', salary) OUT_PUT FROM employees;
```

![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/1515430244.png)

#### And 5.1.5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序

```mysql
SELECT DATEDIFF(SYSDATE(), hire_date) / 365 worked_years, DATEDIFF(SYSDATE(),hire_date) worked_days FROM employees ORDER BY worked_years DESC;
```

![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/318968098.png)

#### And 5.1.6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空

```mysql
SELECT last_name, hire_date, department_id FROM employees
#WHERE hire_date >= '1997-01-01'
#WHERE hire_date >= STR_TO_DATE('1997-01-01', '%Y-%m-%d')
WHERE DATE_FORMAT(hire_date,'%Y') >= '1997'
AND department_id IN (80, 90, 110) AND commission_pct IS NOT NULL
```

![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/4111710989.png)

#### And 5.1.7.查询公司中入职超过10000天的员工姓名、入职时间

```mysql
SELECT last_name,hire_date FROM employees
#WHERE TO_DAYS(NOW()) - to_days(hire_date) > 10000;
WHERE DATEDIFF(NOW(),hire_date) > 10000;
```

![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/2080015661.png)

#### And 5.1.8.做一个查询，产生下面的结果：

```mysql
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ', TRUNCATE(salary * 3, 0)) "Dream Salary" FROM employees;
```

![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/1432988400.png)

#### And 5.1.9.使用case-when，按照下面的条件：

```mysql
SELECT last_name Last_name, job_id Job_id, CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE 'F' END "grade"
FROM employees;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1533974532.png)

## And 6.聚合函数

### And 6.1.【题目】

1. where子句可否使用组函数进行过滤?
2. 查询公司员工工资的最大值，最小值，平均值，总和
3. 查询各job_id的员工工资的最大值，最小值，平均值，总和
4. 选择具有各个job_id的员工人数
5. 查询员工最高工资和最低工资的差距（DIFFERENCE）
6. 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
7. 查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
8. 查询每个工种、每个部门的部门名、工种名和最低工资

</div>

#### And 6.1.1.where子句可否使用组函数进行过滤?

不能

#### And 6.1.2.查询公司员工工资的最大值，最小值，平均值，总和

```mysql
select max(salary),min(salary),avg(salary),sum(salary) from employees;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/193891628.png)

#### And 6.1.3.查询各job_id的员工工资的最大值，最小值，平均值，总和

```mysql
select job_id,max(salary),min(salary),avg(salary),sum(salary) from employees grooup by job_id;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2748510669.png)

#### And 6.1.4.选择具有各个job_id的员工人数

```mysql
select job_id,COUNT(*) from employees group by job_id;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3616586354.png)

#### And 6.1.5.查询员工最高工资和最低工资的差距（DIFFERENCE）

```mysql
select max(salary),min(salary),max(salary)-min(salary) DIFFERENCE from employees;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2205988719.png)

#### And 6.1.6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

```mysql
select manager_id,min(salary) from employees where manager_id is not null group by manager_id having min(salary)>6000;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3194676263.png)

#### And 6.1.7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

```mysql
select department_name,location_id,count(employee_id),avg(salary) from employees e right Join departments d ON e.`department_id`=d.`department_id` group by departmentt_name,location_id order by avg(salary) desc;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/588261607.png)

#### And 6.1.8.查询每个工种、每个部门的部门名、工种名和最低工资

```mysql
select department_name,job_id,min(salary) from employees e RIGHT outer Join deparartments d ON e.`department_id`=d.`department_id` group by department_name,job_id;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2375870204.png)

## And 7.子查询

### And 7.1.【题目】

1. 查询和Zlotkey相同部门的员工姓名和工资
2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
3. 选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
5. 查询在部门的location_id为1700的部门工作的员工的员工号
6. 查询管理者是King的员工姓名和工资
7. 查询工资最低的员工信息: last_name, salary
8. 查询平均工资最低的部门信息
9. 查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
10. 查询平均工资最高的 job 信息
11. 查询平均工资高于公司平均工资的部门有哪些?
12. 查询出公司中所有 manager 的详细信息
13. 各个部门中 最高工资中最低的那个部门的 最低工资是多少?
14. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
16. 选择所有没有管理者的员工的last_name
17. 查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
18. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
19. 查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
20. 查询每个国家下的部门个数大于 2 的国家编号（相关子查询）

</div>

#### And 7.1.1.查询和Zlotkey相同部门的员工姓名和工资

```mysql
select last_name,salary from employees where department_id=(
    select department_id from employees where last_name="Zlotkey"
    );
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1524181237.png)

#### And 7.1.2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

```mysql
select employee_id,last_name,salary from employees where salary>(
    select avg(salary) from employees
    );
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2241915199.png)

#### And 7.1.3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

```mysql
select last_name,job_id,salary from employees where salary>ALL(
    select salary from employees where job_id="SA_MAN" 
    );
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2291180080.png)

#### And 7.1.4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

```mysql
select employee_id,last_name from employees where department_id=ANY(
    select DISTINCT department_id from employees where last_name like "%u%"
    );
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3300945559.png)

#### And 7.1.5.查询在部门的location_id为1700的部门工作的员工的员工号

```mysql
select employee_id from employees where department_id in (
    select department_id from departments where location_id=1700
    );
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/704483308.png)

#### And 7.1.6.查询管理者是King的员工姓名和工资

```mysql
select last_name,salary from employees where manager_id in (
    select employee_id from employees where last_name="King"
    );
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1952776253.png)

#### And 7.1.7.查询工资最低的员工信息: last_name, salary

```mysql
select last_name,salary from employees where salary=(
    select min(salary) from employees
    );
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/962196294.png)

#### And 7.1.8.查询平均工资最低的部门信息

```mysql
SELECT d.* FROM departments d,(
		SELECT department_id,AVG(salary) avg_S FROM employees GROUP BY department_id ORDER BY avg_S ASC
	) dd WHERE dd.department_id=d.department_id LIMIT 0,1;
##嵌入语句使用 group by 将平均工资按照的department_id(部门id)聚合分组，以部门的平均工资asc降序排列，使用别名表将departments表依照嵌入查询的别名表dd排序，limit 0,1 取升序中的第一行数据。
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1774716662.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/2799904344.png)

#### And 7.1.9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

```mysql
SELECT d.*,dd.avg_S FROM departments d,(
		SELECT department_id,AVG(salary) avg_S FROM employees GROUP BY department_id ORDER BY avg_S ASC
	) dd WHERE dd.department_id=d.department_id LIMIT 0,1;
##在上方的语句d.*后加上dd别名表的平均工资字段列
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1662963663.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/3625454602.png)

#### And 7.1.10.查询平均工资最高的 job 信息

```mysql
SELECT j.* FROM jobs j,(
		SELECT job_id, AVG(salary) FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC
	) e WHERE j.job_id=e.job_id LIMIT 0,1;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/735596306.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1988763063.png)

#### And 7.1.11.查询平均工资高于公司平均工资的部门有哪些?

```mysql
SELECT department_id FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING AVG(salary)>(
		SELECT AVG(salary) FROM employees
	);
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1032144231.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/3846685696.png)

#### And 7.1.12.查询出公司中所有 manager 的详细信息

```mysql
SELECT employee_id, last_name, salary FROM employees WHERE employee_id IN(SELECT DISTINCT manager_id FROM employees);

SELECT e.employee_id, e.last_name, e.salary FROM employees e JOIN employees ee WHERE e.employee_id=ee.manager_id;

SELECT employee_id, last_name, salary FROM employees e1 WHERE EXISTS ( SELECT * FROM employees e2 WHERE e2.manager_id = e1.employee_id);
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1071161282.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/3128694530.png)

#### And 7.1.13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

```mysql
SELECT MIN(salary) FROM employees WHERE department_id=(
	SELECT department_id FROM employees GROUP BY department_id HAVING MAX(salary)=(
		SELECT MIN(max_S) FROM (
			SELECT MAX(salary) max_S FROM employees GROUP BY department_id ORDER BY department_id
		) tabale_max_S
	)
);
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/866319198.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/130168591.png)

#### And 7.1.14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

```mysql
SELECT last_name, department_id, email, salary FROM employees WHERE employee_id IN(
	SELECT DISTINCT manager_id FROM employees e,(
		SELECT department_id, AVG(salary) avg_S FROM employees GROUP BY department_id ORDER BY avg_S DESC LIMIT 0,1
	) ee WHERE e.department_id=ee.department_id
);
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/2957497255.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1218401464.png)

#### And 7.1.15.查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

```mysql
SELECT department_id FROM departments WHERE department_id NOT IN (
	SELECT department_id FROM employees WHERE job_id="ST_CLERK"
);
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1126238324.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/2195220076.png)

#### And 7.1.16.选择所有没有管理者的员工的last_name

```mysql
SELECT last_name FROM employees e WHERE  NOT EXISTS(
	SELECT * FROM employees ee WHERE e.manager_id=ee.employee_id
);
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/353229241.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1288043202.png)

#### And 7.1.17.查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'

```mysql
SELECT employee_id, last_name, hire_date, salary FROM employees WHERE manager_id = (
	SELECT employee_id FROM employees WHERE last_name="De Haan"
);
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/839109191.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/3068180868.png)

#### And 7.1.18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

```mysql
SELECT employee_id, last_name, salary FROM employees e,(
	SELECT department_id,AVG(salary) avg_S FROM employees GROUP BY department_id
) ee WHERE e.department_id=ee.department_id AND e.salary>ee.avg_S;
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/921863779.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/2412995424.png)

#### And 7.1.19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

```mysql
SELECT department_name, department_id FROM departments d WHERE 5<(
	SELECT COUNT(*) FROM employees e WHERE d.department_id=e.department_id 
);
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1854554448.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1281111363.png)

#### And 7.1.20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）

```mysql
SELECT country_id FROM locations l WHERE 2<(
	SELECT COUNT(*) FROM departments d WHERE l.location_id=d.location_id
)
```

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/2181645789.png)

![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1597291015.png)

最后修改：2024 年 08 月 30 日

觉得文章有用，可以赞赏请我喝瓶冰露

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，将记录你的ip和窗口信息展示，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

MySQL的SELECT查询使用

南凝呀 • 2023 年 10 月 19 日