1.含义
出现在其他语句中的select语句，成为子查询或内查询，外部的查询语句，成为主查询或外查询
2.分类：
（1）按子查询出现的位置：
select 后面 （仅支持标量子查询）
from 后面 （支持表子查询）
（√√）where 或having后面（支持标量子查询，列子查询，行子查询）
exists 后面 表子查询
（2）按结果集的行数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一行多列）
行子查询（结果集一行多列）
表子查询（结果集一般是多行多列）
3 √ where 或 having后面
标量子查询（一行一列）
列子查询（一列多行）
行子查询（一行多列）
特点：1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询。一般搭配着单行操作符使用 < , > ,<= , >= , <> ,=.
4.列子查询，一般搭配着多行操作符使用 in，any/some，all；
5.子查询的执行优先于主查询执行。
√3-1 标量子查询（一行一列）
案例一：查询工资比Abel高的员工的名字？
mysql> #查询工资比Abel高的员工的名字？
mysql> select last_name from employees where salary >(select salary from employees where last_name = 'Abel');
+-----------+
| last_name |
+-----------+
| K_ing |
| Kochhar |
| De Haan |
| Greenberg |
| Russell |
| Partners |
| Errazuriz |
| Ozer |
| Hartstein |
| Higgins |
+-----------+
10 rows in set (0.09 sec)
案例二：返回job_id 与141号员工相同的，salary比143号员工高的员工的姓名，job_id 和工资
mysql> 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);
+-------------+----------+---------+
| last_name | job_id | salary |
+-------------+----------+---------+
| Nayer | ST_CLERK | 3200.00 |
| Mikkilineni | ST_CLERK | 2700.00 |
| Bissot | ST_CLERK | 3300.00 |
| Atkinson | ST_CLERK | 2800.00 |
| Mallin | ST_CLERK | 3300.00 |
| Rogers | ST_CLERK | 2900.00 |
| Ladwig | ST_CLERK | 3600.00 |
| Stiles | ST_CLERK | 3200.00 |
| Seo | ST_CLERK | 2700.00 |
| Rajs | ST_CLERK | 3500.00 |
| Davies | ST_CLERK | 3100.00 |
+-------------+----------+---------+
11 rows in set (0.30 sec)
案例三：返回公司工资最少的员工的姓名，工种号，工资。
mysql> #返回公司工资最少的员工的姓名，工种号，工资。
mysql> select last_name ,job_id,salary from employees where salary =(select min(salary) from employees);
+-----------+----------+---------+
| last_name | job_id | salary |
+-----------+----------+---------+
| Olson | ST_CLERK | 2100.00 |
+-----------+----------+---------+
1 row in set (0.35 sec)
案例四：查询最低工资大于50号部门最低工资的部门id 和其最低工资。
mysql> select department_id,min(salary) from employees group by department_id having min(salary) >(select min(salary) from employees where department_id=50);
+---------------+-------------+
| department_id | min(salary) |
+---------------+-------------+
| NULL | 7000.00 |
| 10 | 4400.00 |
| 20 | 6000.00 |
| 30 | 2500.00 |
| 40 | 6500.00 |
| 60 | 4200.00 |
| 70 | 10000.00 |
| 80 | 6100.00 |
| 90 | 17000.00 |
| 100 | 6900.00 |
| 110 | 8300.00 |
+---------------+-------------+
11 rows in set (0.35 sec)
√3-2 列子查询（一列多行）
案例一：返回location_id是1400或1700的部门中的所有员工的姓名
mysql> select last_name from employees where department_id in(select distinct department_id from departments where location_id in(1400, 1700));
+------------+
| last_name |
+------------+
| Hunold |
| Ernst |
| Austin |
| Pataballa |
| Lorentz |
| Whalen |
| Raphaely |
| Khoo |
| Baida |
| Tobias |
| Himuro |
| Colmenares |
| K_ing |
| Kochhar |
| De Haan |
| Greenberg |
| Faviet |
| Chen |
| Sciarra |
| Urman |
| Popp |
| Higgins |
| Gietz |
+------------+
23 rows in set (0.13 sec)
案例二：返回其他部门中比 job_id 为 'it _ prog'部门所有工资都低的员工 的员工号，姓名，jop_id 以及salary。
mysql> select employee_id ,last_name,salary from employees where salary < all(select salary from employees where job_id ='it_prog');
+-------------+-------------+---------+
| employee_id | last_name | salary |
+-------------+-------------+---------+
| 115 | Khoo | 3100.00 |
| 116 | Baida | 2900.00 |
| 117 | Tobias | 2800.00 |
| 118 | Himuro | 2600.00 |
| 119 | Colmenares | 2500.00 |
| 125 | Nayer | 3200.00 |
| 126 | Mikkilineni | 2700.00 |
| 127 | Landry | 2400.00 |
| 128 | Markle | 2200.00 |
| 129 | Bissot | 3300.00 |
| 130 | Atkinson | 2800.00 |
| 131 | Marlow | 2500.00 |
| 132 | Olson | 2100.00 |
| 133 | Mallin | 3300.00 |
| 134 | Rogers | 2900.00 |
| 135 | Gee | 2400.00 |
| 136 | Philtanker | 2200.00 |
| 137 | Ladwig | 3600.00 |
| 138 | Stiles | 3200.00 |
| 139 | Seo | 2700.00 |
| 140 | Patel | 2500.00 |
| 141 | Rajs | 3500.00 |
| 142 | Davies | 3100.00 |
| 143 | Matos | 2600.00 |
| 144 | Vargas | 2500.00 |
| 180 | Taylor | 3200.00 |
| 181 | Fleaur | 3100.00 |
| 182 | Sullivan | 2500.00 |
| 183 | Geoni | 2800.00 |
| 185 | Bull | 4100.00 |
| 186 | Dellinger | 3400.00 |
| 187 | Cabrio | 3000.00 |
| 188 | Chung | 3800.00 |
| 189 | Dilly | 3600.00 |
| 190 | Gates | 2900.00 |
| 191 | Perkins | 2500.00 |
| 192 | Bell | 4000.00 |
| 193 | Everett | 3900.00 |
| 194 | McCain | 3200.00 |
| 195 | Jones | 2800.00 |
| 196 | Walsh | 3100.00 |
| 197 | Feeney | 3000.00 |
| 198 | OConnell | 2600.00 |
| 199 | Grant | 2600.00 |
+-------------+-------------+---------+
44 rows in set (0.00 sec)
解法2:
mysql> select employee_id ,last_name,salary from employees where salary <(select min(salary) from employees where job_id ='it_prog');
√3-3 行子查询（一行多列或多行多I列）使用频率不高
案例一：查询员工编号最小并且工资最高的员工信息。
mysql> select * from employees where employee_id =(select min(employee_id) from employees) and salary = (select max(salary) from employees);
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
1 row in set (0.08 sec)
mysql>
4 √ select 后面 （不常用）
案例一：查询每个部门的员工个数
mysql> select d.*,(select count(*) from employees e where e.department_id = d.department_id) 员工个数 from departments d;
+---------------+-----------------+------------+-------------+--------+
| department_id | department_name | manager_id | location_id | 员工个数 |
+---------------+-----------------+------------+-------------+--------+
| 10 | Adm | 200 | 1700 | 1 |
| 20 | Mar | 201 | 1800 | 2 |
| 30 | Pur | 114 | 1700 | 6 |
| 40 | Hum | 203 | 2400 | 1 |
| 50 | Shi | 121 | 1500 | 45 |
| 60 | IT | 103 | 1400 | 5 |
| 70 | Pub | 204 | 2700 | 1 |
| 80 | Sal | 145 | 2500 | 34 |
| 90 | Exe | 100 | 1700 | 3 |
| 100 | Fin | 108 | 1700 | 6 |
| 110 | Acc | 205 | 1700 | 2 |
| 120 | Tre | NULL | 1700 | 0 |
| 130 | Cor | NULL | 1700 | 0 |
| 140 | Con | NULL | 1700 | 0 |
| 150 | Sha | NULL | 1700 | 0 |
| 160 | Ben | NULL | 1700 | 0 |
| 170 | Man | NULL | 1700 | 0 |
| 180 | Con | NULL | 1700 | 0 |
| 190 | Con | NULL | 1700 | 0 |
| 200 | Ope | NULL | 1700 | 0 |
| 210 | IT | NULL | 1700 | 0 |
| 220 | NOC | NULL | 1700 | 0 |
| 230 | IT | NULL | 1700 | 0 |
| 240 | Gov | NULL | 1700 | 0 |
| 250 | Ret | NULL | 1700 | 0 |
| 260 | Rec | NULL | 1700 | 0 |
| 270 | Pay | NULL | 1700 | 0 |
+---------------+-----------------+------------+-------------+--------+
27 rows in set (0.00 sec)
4 √ from 后面 （不常用）
将子查询充当一张表，要求必须起别名。
案例1:查询每个部门的平均工资的工资等级
mysql> select ag_dep.* ,g.grade_level from (select avg(e.salary) ag, department_id from employees e group by department_id) ag_dep join job_grades g on ag_dep.ag between lowest_sal and highest_sal;
+--------------+---------------+-------------+
| ag | department_id | grade_level |
+--------------+---------------+-------------+
| 7000.000000 | NULL | C |
| 4400.000000 | 10 | B |
| 9500.000000 | 20 | C |
| 4150.000000 | 30 | B |
| 6500.000000 | 40 | C |
| 3475.555556 | 50 | B |
| 5760.000000 | 60 | B |
| 10000.000000 | 70 | D |
| 8955.882353 | 80 | C |
| 19333.333333 | 90 | E |
| 8600.000000 | 100 | C |
| 10150.000000 | 110 | D |
+--------------+---------------+-------------+
12 rows in set (0.40 sec)
5 √ exists 后面（相关子查询）（不常用）
语法：
select（完整的查询语句）
结果：（0/1）
案例一：查询有员工的部门名
mysql> select department_name from departments d where exists (select * from employees e where d.department_id = e.department_id);
+-----------------+
| department_name |
+-----------------+
| Adm |
| Mar |
| Pur |
| Hum |
| Shi |
| IT |
| Pub |
| Sal |
| Exe |
| Fin |
| Acc |
+-----------------+
11 rows in set (0.00 sec)
注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！
网友评论