1 定义
：select查询语句中嵌套了select语句，嵌套的那个select查询语句就叫子查询
1.1 从employee表中查询出工资最高员工的信息
mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
| 11 | Jobs | NULL | 3600 | 19283 | dpt2 |
| 12 | Tony | NULL | 3400 | 102938 | dpt3 |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)
mysql> select MAX(salary) from employee;
+-------------+
| MAX(salary) |
+-------------+
| 3600 |
+-------------+
1 row in set (0.00 sec)
mysql> select * from employee where salary =(select MAX(salary) from employee);
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 11 | Jobs | NULL | 3600 | 19283 | dpt2 |
+----+------+------+--------+--------+--------+
2 rows in set (0.00 sec)
mysql>
2 子查询的分类
2.1 子查询结果为单行单列，可以作为WHERE的条件使用运算符去判断，运算符包括 > ,<, = ,>=,<=
查询工资小于平均工资的员工信息
mysql> select avg(salary) from employee;
+-------------+
| avg(salary) |
+-------------+
| 3150.0000 |
+-------------+
1 row in set (0.00 sec)
mysql> select * from employee where salary < (select avg(salary) from employee);
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
+----+------+------+--------+--------+--------+
6 rows in set (0.00 sec)
mysql>
2.2 子查询结果是多行单列，以作为WHERE的条件使用运算符IN去判断
查询部门人数为11和15的员工信息
mysql> select * from department;
+----------+------------+
| dpt_name | people_num |
+----------+------------+
| dpt1 | 11 |
| dpt2 | 12 |
| dpt3 | 10 |
| dpt4 | 15 |
+----------+------------+
4 rows in set (0.01 sec)
mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
| 11 | Jobs | NULL | 3600 | 19283 | dpt2 |
| 12 | Tony | NULL | 3400 | 102938 | dpt3 |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)
mysql> select dpt_name from department where people_num = 11 or people_num = 15;
+----------+
| dpt_name |
+----------+
| dpt1 |
| dpt4 |
+----------+
2 rows in set (0.00 sec)
mysql> select * from employee where in_dpt in(select dpt_name from department where people_num in(11,15));
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
+----+------+------+--------+--------+--------+
5 rows in set (0.01 sec)
2.3 子查询结果是多行多列，可以作为一张虚拟表，和其他表连接查询。
查询工资大于平均工资的员工和其部门的信息
mysql> select * from employee where salary >(select avg(salary) from employee);
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
| 11 | Jobs | NULL | 3600 | 19283 | dpt2 |
| 12 | Tony | NULL | 3400 | 102938 | dpt3 |
+----+------+------+--------+--------+--------+
6 rows in set (0.00 sec)
mysql> select * from department t1,(select * from employee where salary >(select avg(salary) from employee)) t2 where t1.dpt_name
-> =t2.in_dpt;
+----------+------------+----+------+------+--------+--------+--------+
| dpt_name | people_num | id | name | age | salary | phone | in_dpt |
+----------+------------+----+------+------+--------+--------+--------+
| dpt1 | 11 | 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| dpt3 | 10 | 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| dpt2 | 12 | 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| dpt4 | 15 | 10 | Mike | 23 | 3400 | 110110 | dpt4 |
| dpt2 | 12 | 11 | Jobs | NULL | 3600 | 19283 | dpt2 |
| dpt3 | 10 | 12 | Tony | NULL | 3400 | 102938 | dpt3 |
+----------+------------+----+------+------+--------+--------+--------+
6 rows in set (0.00 sec)
mysql>
网友评论