1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

/*
	含义：
		出现在其他语句中的select语句，称为子查询或内查询
		外部的查询语句，称为主查询或外查询
	分类：
		1、按子查询出现的位置：
			select后面：
				仅仅支持标量子查询			
			from后面：
				支持表子查询
			where或having后面：★
				标量子查询（单行） √
				列子查询  （多行） √				
				行子查询（用的较少）				
			exists后面（相关子查询）
				表子查询
		
        2、按结果集的行列数不同：
			标量子查询（结果集只有一行一列）
			列子查询（结果集只有一列多行）
			行子查询（结果集有一行多列）
			表子查询（结果集一般为多行多列）
*/

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181

/*
	1、标量子查询（单行子查询）
	2、列子查询（多行子查询）
	3、行子查询（多列多行）

	特点：
		①子查询放在小括号内
		②子查询一般放在条件的右侧
		③标量子查询，一般搭配着单行操作符使用
		> < >= <= = <>
		④列子查询，一般搭配着多行操作符使用
			IN|NOT IN：等于列表中的任意一个★
			ANY|SOME：和子查询返回的某一个值比较（a > ANY可以替换成a > MIN)
			ALL：和子查询返回的所有值比较(a > ALL可以替换成a > MAX)
		④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/

#1.标量子查询★
	#案例1：谁的工资比 Abel 高?
	#①查询Abel的工资
        SELECT last_name, salary
        FROM employees
        WHERE last_name = 'Abel';
	#②查询员工的信息，满足 salary>①结果
		SELECT *
        FROM employees
        WHERE salary>(
			SELECT salary
            FROM employees
            WHERE last_name = 'Abel'
        );
	#案例2：返回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=①并且salary>②
			SELECT last_name, job_id, salary
            FROM employees
            WHERE job_id = (
				SELECT job_id
				FROM employees
				WHERE employee_id = 141
            )
            AND salary > (
				SELECT salary
				FROM employees
				WHERE employee_id = 143
            );
	#案例3：返回公司工资最少的员工的last_name,job_id和salary
		#①查询公司的 最低工资
			SELECT MIN(salary)
            FROM employees;
        #②查询last_name,job_id和salary，要求salary=①
			SELECT last_name, job_id, salary
            FROM employees
            WHERE salary = (
				SELECT MIN(salary)
				FROM employees
            );
	#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
		#①查询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(salary),department_id
			FROM employees
			GROUP BY department_id
			HAVING MIN(salary)>(
				SELECT  MIN(salary)
				FROM employees
				WHERE department_id = 50
			);
	#非法使用标量子查询(>=<条件后的标量子查询必须是单行单列，不能是多列，也不能为空)
		SELECT MIN(salary),department_id
		FROM employees
		GROUP BY department_id
		HAVING MIN(salary)>(
			SELECT  salary
			FROM employees
			WHERE department_id = 250
		);

#2.列子查询（多行子查询）★
	#案例1：返回location_id是1400或1700的部门中的所有员工姓名
		#①查询location_id是1400或1700的部门编号
			SELECT DISTINCT department_id
            FROM departments
            WHERE location_id IN (1400, 1700);
		#②查询员工姓名，要求部门号是①列表中的某一个
			SELECT first_name
            FROM employees
            WHERE department_id IN(
				SELECT DISTINCT department_id
                FROM departments
                WHERE location_id IN (1400, 1700)
            );
            #或
			SELECT first_name
            FROM employees
            WHERE department_id = ANY(
				SELECT DISTINCT department_id
                FROM departments
                WHERE location_id IN (1400, 1700)
            );
	#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
		#①查询job_id为‘IT_PROG’部门任一工资
			SELECT DISTINCT salary
            FROM employees
            WHERE job_id = 'IT_PROG';
		#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
			SELECT employee_id, first_name, 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
		SELECT employee_id, first_name, 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 * 
		FROM employees
		WHERE (employee_id, salary)=(
			SELECT MIN(employee_id), MAX(salary)
			FROM employees
		);
		#之前的做法：
			#①查询最小的员工编号
				SELECT MIN(employee_id)
				FROM employees;
			#②查询最高工资
				SELECT MAX(salary)
				FROM employees;
			#③查询员工信息    
				SELECT *
				FROM employees
				WHERE employee_id = (
					SELECT MIN(employee_id)
					FROM employees
				)
				AND salary = (
					SELECT MAX(salary)
					FROM employees
				);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

/*
	仅仅支持标量子查询
*/

#案例1：查询每个部门的员工个数
	SELECT d.*, (
		SELECT COUNT(*)
		FROM employees e
		WHERE e.department_id = d.department_id
	) AS '个数'
	FROM departments d;
    
#案例2：查询员工号=102的部门名
	SELECT (
		SELECT department_name
        FROM departments d
        INNER JOIN employees e
        ON d.department_id = e.department_id
        WHERE employee_id = 102
    ) AS '部门名';
    #或者
    SELECT department_name
    FROM departments d
    WHERE d.department_id = (
		SELECT e.department_id
        FROM employees e
        WHERE e.employee_id = 102
    );

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

/*
	将子查询结果充当一张表，要求必须起别名
*/
#案例：查询每个部门的平均工资的工资等级
	#①查询每个部门的平均工资
		SELECT AVG(salary), department_id
		FROM employees
		GROUP BY department_id;
    #②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal    
		SELECT  ag_dep.*, g.grade_level
		FROM (
			SELECT AVG(salary) ag, department_id
			FROM employees
			GROUP BY department_id
		) ag_dep
		INNER JOIN job_grades g
		ON ag_dep.ag BETWEEN g.lowest_sal AND g.highest_sal;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

/*
	语法：
	EXISTS(完整的查询语句)
	结果：
	1或0
*/

SELECT EXISTS(
	SELECT employee_id
    FROM employees
    WHERE salary=300000
);		    #查询是否存在工资是30000的，返回0或1		

#案例1：查询有员工的部门名
	#in
		SELECT department_name
		FROM departments d
		WHERE d.`department_id` IN(
			SELECT department_id
			FROM employees
		);		    
    #exists
		SELECT department_name
        FROM departments d
        WHERE EXISTS(
			SELECT *
			FROM employees e
			WHERE e.department_id = d.department_id
        );
#案例2：查询没有女朋友的男神信息
SELECT *FROM beauty;
#in
	SELECT bo.*
    FROM boys bo
    WHERE bo.id NOT IN(
		SELECT boyfriend_id
        FROM beauty b
    );
    
#exists
	SELECT bo.*
	FROM boys bo
	WHERE NOT EXISTS(
		SELECT b.*
		FROM beauty b
		WHERE b.boyfriend_id = bo.id
	);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

#1.	查询和Zlotkey相同部门的员工姓名和工资
	SELECT last_name, salary
	FROM employees
	WHERE department_id = (
		SELECT department_id
		FROM employees
		WHERE last_name = 'Zlotkey'
	);

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
	SELECT employee_id, last_name, salary
    FROM employees
    WHERE salary > (
		SELECT AVG(salary)
		FROM employees
	);

#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
	SELECT employee_id, last_name, salary
    FROM employees e
    INNER JOIN (
		SELECT department_id, AVG(salary) ag
		FROM employees
		GROUP BY department_id
	)avg_dep
    ON e.department_id = avg_dep.department_id
    WHERE salary > avg_dep.ag;

#4.	查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id, last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
);

#5. 查询在部门的location_id为1700的部门工作的员工的员工号
	SELECT employee_id, department_id
    FROM employees
    WHERE department_id IN (
		SELECT department_id
		FROM departments
		WHERE location_id = 1700
	);

#6.查询管理者是K_ing的员工姓名和工资
	SELECT last_name, salary
    FROM employees
    WHERE manager_id IN(
		SELECT employee_id
        FROM employees
        WHERE last_name = 'K_ing'
	);

#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
	SELECT CONCAT(first_name, ' . ', last_name) AS '姓.名' , salary
    FROM employees
    WHERE salary =(
		SELECT MAX(salary)
        FROM employees
	);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

#1. 查询工资最低的员工信息: last_name, salary
	SELECT last_name, salary
    FROM employees
    WHERE salary = (
		SELECT MIN(salary)
		FROM employees
	);

#2. 查询平均工资最低的部门信息
	SELECT *
    FROM departments
    WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
        ORDER BY AVG(salary)
        LIMIT 1
	);
    #或
    SELECT d.*
	FROM departments d
	WHERE d.department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
		HAVING AVG(salary)=(
			SELECT MIN(ag)
			FROM (
				SELECT AVG(salary) ag, department_id
				FROM employees
				GROUP BY department_id
			) ag_dep
		)
	);

#3. 查询平均工资最低的部门信息和该部门的平均工资
	SELECT d.*,ag
	FROM departments d
	INNER JOIN (
		SELECT AVG(salary) ag, department_id
		FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary) 
		LIMIT 1
	) ag_dep
	ON d.department_id = ag_dep.department_id;
    
#4. 查询平均工资最高的 job 信息
	SELECT *
    FROM jobs
    WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id 
		ORDER BY AVG(salary) DESC
		LIMIT 1
	) ;
	 
#5. 查询平均工资高于公司平均工资的部门有哪些?
	SELECT department_id, AVG(salary) AS ag
	FROM employees
	GROUP  BY department_id
	HAVING ag >(
		SELECT AVG(salary)
		FROM employees
	);
 
#6. 查询出公司中所有 manager 的详细信息.
	SELECT *
    FROM employees
    WHERE employee_id IN (
		SELECT DISTINCT manager_id
        FROM employees
	);

#7. 各个部门的最高工资中最低的那个部门的 最低工资是多少
	SELECT department_id, MIN(salary)
    FROM employees
    WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
		ORDER BY MAX(salary)
		LIMIT 1
	) ;

#8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
	SELECT last_name, d.department_id, email, salary 
    FROM employees e 
	INNER JOIN departments d 
	ON d.manager_id = e.employee_id 
    WHERE d.department_id = (
		SELECT department_id 
		FROM employees 
		GROUP BY department_id 
		ORDER BY AVG(salary) DESC 
		LIMIT 1
	);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

/*
	应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
	语法：
		select 查询列表
		from 表
		【join type join 表2
		on 连接条件
		where 筛选条件
		group by 分组字段
		having 分组后的筛选
		order by 排序的字段】
		limit 【offset,】size;		
			offset :要显示条目的起始索引（起始索引从0开始）
			size :要显示的条目个数
	特点：
		①limit语句放在查询语句的最后
		②公式
		要显示的页数 page，每页的条目数size
		
		select 查询列表
		from 表
		limit (page-1)*size,size;
		
		size=10
		page  
		1	0
		2  	10
		3	20	
*/
	#案例1：查询前五条员工信息
		SELECT * FROM  employees LIMIT 0,5;
		SELECT * FROM  employees LIMIT 5;
		
	#案例2：查询第11条——第25条
		SELECT * FROM  employees LIMIT 10, 15;

	#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
		SELECT *
		FROM employees
		WHERE commission_pct IS NOT NULL
		ORDER BY salary DESC
		LIMIT 10;		

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

/*
	union 联合 合并：将多条查询语句的结果合并成一个结果
	语法：
        select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
        select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
        select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
        .....
        select 字段|常量|表达式|函数 【from 表】 【where 条件】
	
	应用场景：
		要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
	特点：★
		1、多条查询语句的查询的列数必须是一致的
        2、多条查询语句的查询的列的类型和顺序几乎相同
        3、union（默认）代表去重，union all代表不去重
*/
#引入的案例：查询部门编号>90或邮箱包含a的员工信息
	SELECT * FROM employees
	WHERE (department_id > 90) OR (email LIKE '%a%');
	#及
	SELECT * FROM employees WHERE email LIKE '%a%'
	UNION 
	SELECT * FROM employees WHERE department_id > 90;
#案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息
	SELECT id, cname FROM t_ca WHERE csex = '男'
	UNION ALL
	SELECT t_id, tname FROM t_ua WHERE tGender = 'male';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

语法：              	 执行顺序
select 查询列表    			⑦
from 表1 别名       		 ①
连接类型 join 表2   		    ②
on 连接条件        			③
where 筛选        		  ④
group by 分组列表  			 ⑤
having 筛选        	 	  ⑥
order by排序列表    		⑧
limit 起始条目索引，条目数;  	⑨

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

USE student;
#一、查询每个专业的学生人数
	SELECT majorid, COUNT(*)
	FROM student
	GROUP BY majorid;
#二、查询参加考试的学生中，每个学生的平均分、最高分
	SELECT studentno, AVG(score), MAX(score)
    FROM result
    GROUP BY studentno;
#三、查询姓张的每个学生的最低分大于60的学号、姓名
	SELECT s.studentno, studentname, MIN(score)
	FROM result r
	INNER JOIN student s
	ON r.studentno = s.studentno
	WHERE studentname LIKE '张%'
	GROUP BY studentno
	HAVING MIN(score) > 60;
#四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称
	SELECT studentname, majorname
    FROM student s
    JOIN major m
    ON s.majorid = m.majorid
	WHERE DATEDIFF(borndate, '1988-1-1') > 0
    GROUP BY m.majorid;
#五、查询每个专业的男生人数和女生人数分别是多少
	SELECT majorid, sex, COUNT(*)
	FROM student
	GROUP BY majorid, sex;
#六、查询专业和张翠山一样的学生的最低分
SELECT studentno, MIN(score)
FROM result
WHERE studentno IN (
	SELECT studentno
	FROM student
	WHERE majorid = (
		SELECT majorid
		FROM student
		WHERE studentname = '张翠山'
	)
);
#七、查询大于60分的学生的姓名、密码、专业名
	SELECT studentname, loginpwd, majorname
	FROM student s
	JOIN major m
	ON s.majorid = m.majorid
	WHERE studentno IN(
		SELECT studentno
		FROM result
		WHERE score > 60
	);
    #或
	SELECT studentname, loginpwd, majorname
	FROM student s
	JOIN major m  ON s.majorid = m.majorid
	JOIN result r ON s.studentno = r.studentno
	WHERE r.score > 60;
#八、按邮箱位数分组，查询每组的学生个数
	SELECT LENGTH(email) AS len_e, COUNT(*)
    FROM student
    GROUP BY len_e;
#九、查询学生名、专业名、分数
	SELECT studentname, majorname, score
    FROM student s
    JOIN major m ON s.majorid = m.majorid
    JOIN result r ON s.studentno = r.studentno;
#十、查询哪个专业没有学生，分别用左连接和右连接实现
	#左连接
		SELECT m.majorid, m.majorname, s.studentno
		FROM major m
		LEFT OUTER JOIN student s ON m.majorid = s.majorid
		WHERE s.studentno IS NULL;
	#右连接
		SELECT m.majorid, m.majorname, s.studentno
		FROM student s
		RIGHT OUTER JOIN  major m ON m.majorid = s.majorid
		WHERE s.studentno IS NULL;

#十一、查询没有成绩的学生人数
	SELECT COUNT(*)
    FROM student s
    LEFT OUTER JOIN result r
    ON s.studentno = r.studentno
    WHERE r.id IS NULL;