MySQL查询

子查询

• 实际问题：谁的工资比Abel 高

方式一：
SELECT salary
FROM  employees
WHERE last_name='Abel';

SELECT last_name
FROM employees
WHERE salary>11000;

方式二:自连接
SELECT last_name,salary
FROM employees e1,employees e2
WHERE e2.salary>e1.salary #多表的连接条件
AND e1.last_name='Abel';

方式三:子查询
SELECT last_name,salary
FROM  employees
WHERE salary>(
		SELECT salary
		FROM  employees
		WHERE last_name='Abel'
        );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

• 称谓的规范:外查询（或主查询）、内查询（或子查询）

注意：
子查询（内查询）在主查询之前一次执行完成
子查询的结果被主查询（外查询）使用
子查询要包括在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应子查询

• 子查询的分类

角度1：从内查询返回的结果的条目数
单行子查询（结果一个） vs 多行子查询（结果多条）
角度2：内查询是否被执行多次
相关子查询vs不相关子查询

单行子查询

• 单行操作符：= > != > >= < <=
• 题目：查询工资大于149号员工工资的员工信息

子查询的编写技巧：
1.从里往外写
2.从外往里写

SELECT  last_name
FROM employees
WHERE salary(
	SELECT salary
	FROM employees
	WHERE employee_id=149
     );
1
2
3
4
5
6
7

• 题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资

SELECT  last_name,job_id,salary
FROM employees
WHERE job_id>(
      SELECT job_id
      FROM employees
      WHERE  emplyee_id=141)
AND salary>(
      SELECT salary
      FROM employees
      WHERE employee_id=143
      );
1
2
3
4
5
6
7
8
9
10
11

• 题目：返回公司工资最少的员工的last_name、job_id、salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary(
        SELECT MIN(salary)
        FROM employees
);
1
2
3
4
5
6

• 题目：查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id,department_id

方式1：
SELECT  employee_id，manager_id,department_id
FROM employees
WHERE manager_id=(
       SELECT manager_id
       FROM employees
       WHERE employee_id =141)
AND department_id=(
       SELECT department_id
       FROM employees
       WHERE employee_id =141
)
AND employee_id <> 141;

方式2：了解
SELECT  employee_id，manager_id,department_id
FROM employees
WHERE (manager_id,department_id)=(
      						SELECT manager_id,department_id
      						FROM employees
      						WHERE employee_id=141
      						);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• 题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >(
		SELECT MIN(salary)
		FROM employees
		WHERE department_id=50
);
1
2
3
4
5
6
7
8

• 题目：显示员工的employee_id,last_name和location
• 其中，若员工department_id与location_id为1800的department_id相同
• 则location为’Canada’，其余为‘USA’

SELECT  employee_id,last_name,CASE department_id =WHEN (SELECT department_id
														FROM departments
														WHERE location_id=1800)
												 THEN 'Canada'
												 ELSE 'USA'  END "location"
FROM employees;
1
2
3
4
5
6

• 子查询的空值问题(子查询不返回任何行)

SELECT last_name
FROM employees
WHERE  job_id=(
        SELECT job_id
        FROM employees
        WHERE last_name='sas'
);
1
2
3
4
5
6
7

多行子查询

• 多行操作符

• ANY/ALL
• 题目：返回其他job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary

SELECT employee_id,last_name,job_id,salar
FROM employees
WHERE job_id<> 'IT_PROG'
AND salary <ANY(
    SELECT salary
    FROM  employees
    WHERE job_id='IT_PROG'
    );
1
2
3
4
5
6
7
8

• 题目：返回其他job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id以及salary

SELECT employee_id,last_name,job_id,salar
FROM employees
WHERE job_id<> 'IT_PROG'
AND salary <ALL(
    SELECT salary
    FROM  employees
    WHERE job_id='IT_PROG'
    );
1
2
3
4
5
6
7
8

• 题目：查询平均工资最低的部门id
• MySQL中聚合函数不可以嵌套使用

方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
						SELECT MIN(avg_sal)
						FROM(
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
							) dept_avg_sal
					);
方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
						SELECT AVG(salary) avg_sal
						FROM employees
					);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

• 多行子查询空值问题

子查询中含有NULL，外查询不返回行

相关子查询

关联子查询：每执行一次外部查询，子查询都要重新计算一次。

• 题目：查询员工中工资大于本公司平均工资的员工的last_name、salary和department_id

SELECT  last_name,salary,department_id
FROM employees
WHERE  salary>(
        SELECT AVG(salary)
        FROM employees
        );
1
2
3
4
5
6

• 题目查询员工中工资大于本公司平均工资的员工的last_name、salary和department_id

方式1：相关子查询
SELECT  last_name,salary,department_id
FROM employees e1
WHERE  salary>(
        SELECT AVG(salary)
        FROM employees e2
        WHERE  department_id=e1.department_id
        );
方式2：在FROM中声明子查询
SELECT e.last_name,e.salary.e.department_id
FROM employees e,(SELECT department_id,AVG(salary) avg_sal
				  FROM employees
				  GROUP BY department_id) dept_avg_sal
WHERE  e.`department_id`=dept_avg_sal.`department_id`
AND e.salary>dept_avg_sal.`avg_sal`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-ORDER BY
题目：在查询员工的id、salary，按照department_name进行排序

SELECT employee_id,salary
FROM emplyees e
ORDER BY(
        SELECT department_name
        FROM departments d
        WHERE  e.`department_id`=d.`department_id`     
        );
1
2
3
4
5
6
7

结论：在SELECT 中，除了GROUP BY 和LIMIT，其他都可以声明子查询

• 题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee__id、last_name和job_id

SELECT *
FROM  job_history;

SELECT  employee_id,last_name,job_id
FROM  employees e
WHERE  2<=(
           SELECT COUNT(*)
           FROM  job_history j
           WHERE e.`employee_id`=j.`employee_id`
		   );
1
2
3
4
5
6
7
8
9
10

EXIETS与NOT EXISTS关键字

如果在子查询中不存在满足的条件的行：
条件返回FALSE
继续在子查询中查找

如果在子查询中存在 满足条件的行：
不在子查询中继续查找
条件返回TRUE

• 题目：查询公司管理者的employee_id,last_name,job_id,department_id信息

子查询：
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN(
						SELECT  DISYINCT manager_id
						FROM   employees
					);
使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS(
						SELECT  *
						FROM   employees e2
						WHERE e1.`employee_id`=e2.`manager_id`
					);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 题目：查询departments表中，不存在employees表中的部门的department_id和department_name

SELECT department_id,department_name
FROM departments d
WHERE EXISTS (
				SELECT *00
				FROM  employees e
				WHERE d.`department_id`=e.`department_id`
			 );
1
2
3
4
5
6
7