出现在select语句中的select语句,称为子查询或内查询。
外部的select查询语句,称为主查询或外查询。
按结果集的行列数不同分为4种
按子查询出现在主查询中的不同位置分
测试数据比较多,放在我的个人博客上了。
浏览器中打开链接:http://www.itsoku.com/article/209
mysql中执行里面的javacode2018_employees库部分的脚本。
成功创建javacode2018_employees库及5张表,如下:
子查询位于select后面的,仅仅支持标量子查询。
查询每个部门员工个数
SELECT
a.*,
(SELECT count(*)
FROM employees b
WHERE b.department_id = a.department_id) AS 员工个数
FROM departments a;
查询员工号=102的部门名称
SELECT (SELECT a.department_name
FROM departments a, employees b
WHERE a.department_id = b.department_id
AND b.employee_id = 102) AS 部门名;
将子查询的结果集充当一张表,要求必须起别名,否者这个表找不到。
然后将真实的表和子查询结果表进行连接查询。
查询每个部门平均工资的工资等级
-- 查询每个部门平均工资
SELECT
department_id,
avg(a.salary)
FROM employees a
GROUP BY a.department_id;
-- 薪资等级表
SELECT *
FROM job_grades;
-- 将上面2个结果连接查询,筛选条件:平均工资 between lowest_sal and highest_sal;
SELECT
t1.department_id,
sa AS '平均工资',
t2.grade_level
FROM (SELECT
department_id,
avg(a.salary) sa
FROM employees a
GROUP BY a.department_id) t1, job_grades t2
WHERE
t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
运行最后一条结果如下:
mysql> SELECT
t1.department_id,
sa AS '平均工资',
t2.grade_level
FROM (SELECT
department_id,
avg(a.salary) sa
FROM employees a
GROUP BY a.department_id) t1, job_grades t2
WHERE
t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
+---------------+--------------+-------------+
| department_id | 平均工资 | grade_level |
+---------------+--------------+-------------+
| NULL | 7000.000000 | C |
| 10 | 4400.000000 | B |
| 20 | 9500.000000 | C |
| 30 | 4150.000000 | B |
| 40 | 6500.000000 | C |
| 50 | 3475.555556 | B |
| 60 | 5760.000000 | B |
| 70 | 10000.000000 | D |
| 80 | 8955.882353 | C |
| 90 | 19333.333333 | E |
| 100 | 8600.000000 | C |
| 110 | 10150.000000 | D |
+---------------+--------------+-------------+
12 rows in set (0.00 sec)
where或having后面,可以使用
特点
in,any,some,all分别是子查询关键词之一。
下文中会经常用到这些关键字。
一般标量子查询,示例
查询谁的工资比Abel的高?
/*①查询abel的工资【改查询是标量子查询】*/
SELECT salary
FROM employees
WHERE last_name = 'Abel';
/*②查询员工信息,满足salary>①的结果*/
SELECT *
FROM employees a
WHERE a.salary > (SELECT salary
FROM employees
WHERE last_name = 'Abel');
返回job_id与141号员工相同,salary比143号员工多的员工、姓名、job_id和工资
/*返回job_id与141号员工相同,salary比143号员工多的员工、姓名、job_id和工资*/
/*①查询141号员工的job_id*/
SELECT job_id
FROM employees
WHERE employee_id = 141;
/*②查询143好员工的salary*/
SELECT salary
FROM employees
WHERE employee_id = 143;
/*③查询员工的姓名、job_id、工资,要求job_id=① and salary>②*/
SELECT
a.last_name 姓名,
a.job_id,
a.salary 工资
FROM employees a
WHERE a.job_id = (SELECT job_id
FROM employees
WHERE employee_id = 141)
AND
a.salary > (SELECT salary
FROM employees
WHERE employee_id = 143);
查询最低工资大于50号部门最低工资的部门id和其最低工资【having】
/*查询最低工资大于50号部门最低工资的部门id和其最低工资【having】*/
/*①查询50号部门的最低工资*/
SELECT min(salary)
FROM employees
WHERE department_id = 50;
/*②查询每个部门的最低工资*/
SELECT
min(salary),
department_id
FROM employees
GROUP BY department_id;
/*③在②的基础上筛选,满足min(salary)>①*/
SELECT
min(a.salary) minsalary,
department_id
FROM employees a
GROUP BY a.department_id
HAVING min(a.salary) > (SELECT min(salary)
FROM employees
WHERE department_id = 50);
将上面的示例③中子查询语句中的min(salary)改为salary,执行效果如下:
mysql> SELECT
min(a.salary) minsalary,
department_id
FROM employees a
GROUP BY a.department_id
HAVING min(a.salary) > (SELECT salary
FROM employees
WHERE department_id = 500000);
ERROR 1242 (21000): Subquery returns more than 1 row
错误提示:子查询返回的结果超过了1行记录。
说明:上面的子查询只支持最多一列一行记录。
列子查询需要搭配多行操作符使用:in(not in)、any/some、all。
为了提升效率,最好去重一下distinct关键字。
示例1
返回location_id是1400或1700的部门中的所有员工姓名
/*返回location_id是1400或1700的部门中的所有员工姓名*/
/*方式1*/
/*①查询location_id是1400或1700的部门编号*/
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700);
/*②查询员工姓名,要求部门是①列表中的某一个*/
SELECT a.last_name
FROM employees a
WHERE a.department_id IN (SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700));
/*方式2:使用any实现*/
SELECT a.last_name
FROM employees a
WHERE a.department_id = ANY (SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700));
/*拓展,下面与not in等价*/
SELECT a.last_name
FROM employees a
WHERE a.department_id <> ALL (SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700));
示例2
返回其他工种中比job_id为’IT_PROG’工种任意工资低的员工的员工号、姓名、job_id、salary
/*返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id、salary*/
/*①查询job_id为'IT_PROG'部门任-工资*/
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
/*②查询员工号、姓名、job_id、salary,slary<①的任意一个*/
SELECT
last_name,
employee_id,
job_id,
salary
FROM employees
WHERE salary < ANY (SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
/*或者*/
SELECT
last_name,
employee_id,
job_id,
salary
FROM employees
WHERE salary < (SELECT max(salary)
FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
示例3
返回其他工种中比job_id为’IT_PROG’部门所有工资低的员工的员工号、姓名、job_id、salary
/*返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id、salary*/
SELECT
last_name,
employee_id,
job_id,
salary
FROM employees
WHERE salary < ALL (SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
/*或者*/
SELECT
last_name,
employee_id,
job_id,
salary
FROM employees
WHERE salary < (SELECT min(salary)
FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
示例
查询员工编号最小并且工资最高的员工信息,3种方式。
/*查询员工编号最小并且工资最高的员工信息*/
/*①查询最小的员工编号*/
SELECT min(employee_id)
FROM employees;
/*②查询最高工资*/
SELECT max(salary)
FROM employees;
/*③方式1:查询员工信息*/
SELECT *
FROM employees a
WHERE a.employee_id = (SELECT min(employee_id)
FROM employees)
AND salary = (SELECT max(salary)
FROM employees);
/*方式2*/
SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) = (SELECT
min(employee_id),
max(salary)
FROM employees);
/*方式3*/
SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) in (SELECT
min(employee_id),
max(salary)
FROM employees);
方式1比较常见,方式2、3更简洁。
语法:exists(玩转的查询语句)。
示例1
简单示例
mysql> SELECT exists(SELECT employee_id
FROM employees
WHERE salary = 300000) AS 'exists返回1或者0';
+----------------------+
| exists返回1或者0 |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)
示例2
查询所有员工的部门名称
/*exists入门案例*/
SELECT exists(SELECT employee_id
FROM employees
WHERE salary = 300000) AS 'exists返回1或者0';
/*查询所有员工部门名*/
SELECT department_name
FROM departments a
WHERE exists(SELECT 1
FROM employees b
WHERE a.department_id = b.department_id);
/*使用in实现*/
SELECT department_name
FROM departments a
WHERE a.department_id IN (SELECT department_id
FROM employees);
示例3
查询没有员工的部门
/*查询没有员工的部门*/
/*exists实现*/
SELECT *
FROM departments a
WHERE NOT exists(SELECT 1
FROM employees b
WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL);
/*in的方式*/
SELECT *
FROM departments a
WHERE a.department_id NOT IN (SELECT department_id
FROM employees b
WHERE b.department_id IS NOT NULL);
上面脚本中有b.department_id IS NOT NULL,为什么,有大坑,向下看。
使用in的方式查询没有员工的部门,如下:
SELECT *
FROM departments a
WHERE a.department_id NOT IN (SELECT department_id
FROM employees b);
运行结果:
mysql> SELECT *
-> FROM departments a
-> WHERE a.department_id NOT IN (SELECT department_id
-> FROM employees b);
Empty set (0.00 sec)
in的情况下,子查询中列的值为NULL的时候,外查询的结果为空。
页面更新:2024-05-29
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2020-2024 All Rights Reserved. Powered By 71396.com 闽ICP备11008920号-4
闽公网安备35020302034903号