子查询

子查询基本语法

 mysql> select * from student;
 +------+-------+--------+------+-------------+
 | id   | name  | gender | age  | phone       |
 +------+-------+--------+------+-------------+
 |    2 | Jack  | 1      |   28 | 17777777777 |
 |    3 | Jerry | 1      |   20 | 12356548    |
 |    4 | Mary  | 2      |   19 | 1235457952  |
 |    5 | Maria | 2      |   19 | 156459628   |
 |    6 | leo   | 1      |   24 | 43212354    |
 +------+-------+--------+------+-------------+
 ​
 ​
 mysql> select * from score;   
 +----+-------+-------+
 | id | stuid | score |
 +----+-------+-------+
 |  1 |     1 | 89.00 |
 |  2 |     3 | 80.00 |
 |  3 |     4 | 70.00 |
 +----+-------+-------+
 ​
 ​
 mysql> select * from student where id in (select stuId from score where score>=60);
 +------+-------+--------+------+------------+
 | id   | name  | gender | age  | phone      |
 +------+-------+--------+------+------------+
 |    3 | Jerry | 1      |   20 | 12356548   |
 |    4 | Mary  | 2      |   19 | 1235457952 |
 +------+-------+--------+------+------------+

 

---

in和not in

 mysql> select * from student where id not in (select stuId from score where score>=75);
 +------+-------+--------+------+-------------+
 | id   | name  | gender | age  | phone       |
 +------+-------+--------+------+-------------+
 |    2 | Jack  | 1      |   28 | 17777777777 |
 |    4 | Mary  | 2      |   19 | 1235457952  |
 |    5 | Maria | 2      |   19 | 156459628   |
 |    6 | leo   | 1      |   24 | 43212354    |
 +------+-------+--------+------+-------------+
 ​
 ​
 mysql> select * from student where not exists (select stuId from score where score>=100);
 +------+-------+--------+------+-------------+
 | id   | name  | gender | age  | phone       |
 +------+-------+--------+------+-------------+
 |    2 | Jack  | 1      |   28 | 17777777777 |
 |    3 | Jerry | 1      |   20 | 12356548    |
 |    4 | Mary  | 2      |   19 | 1235457952  |
 |    5 | Maria | 2      |   19 | 156459628   |
 |    6 | leo   | 1      |   24 | 43212354    |
 +------+-------+--------+------+-------------+

 

---

exists和not exists

 mysql> select * from student where exists (select stuId from score where score>=75);
 +------+-------+--------+------+-------------+
 | id   | name  | gender | age  | phone       |
 +------+-------+--------+------+-------------+
 |    2 | Jack  | 1      |   28 | 17777777777 |
 |    3 | Jerry | 1      |   20 | 12356548    |
 |    4 | Mary  | 2      |   19 | 1235457952  |
 |    5 | Maria | 2      |   19 | 156459628   |
 |    6 | leo   | 1      |   24 | 43212354    |
 +------+-------+--------+------+-------------+
 ​
 ​
 #not exists
 mysql> select * from student where not exists (select stuId from score where score>=75);
 Empty set (0.00 sec)

只要存在成绩大于75的，就返回所有成员的数据

 

---

基础结束