实例
SELECT
*
FROM
student
WHERE
class_id IN ( SELECT id FROM class WHERE id = 2 );
+----+--------+------+------+----------+--------+
| id | name | sex | age | class_id | status |
+----+--------+------+------+----------+--------+
| 4 | 小红 | 女 | 23 | 2 | 1 |
| 5 | 小花 | 女 | 19 | 2 | 1 |
+----+--------+------+------+----------+--------+
2 rows in set (0.00 sec)
实例
select * from student where class_id < (select id from class where id = 2);
+----+--------+------+------+----------+--------+
| id | name | sex | age | class_id | status |
+----+--------+------+------+----------+--------+
| 1 | 张三 | 男 | 18 | 1 | 1 |
| 2 | 王五 | 男 | 20 | 1 | 1 |
| 3 | 李四 | 男 | 20 | 1 | 1 |
+----+--------+------+------+----------+--------+
3 rows in set (0.00 sec)
实例
select * from student where class_id = (select id from class where id = 2);
+----+--------+------+------+----------+--------+
| id | name | sex | age | class_id | status |
+----+--------+------+------+----------+--------+
| 4 | 小红 | 女 | 23 | 2 | 1 |
| 5 | 小花 | 女 | 19 | 2 | 1 |
+----+--------+------+------+----------+--------+
2 rows in set (0.00 sec)
实例
SELECT
class.`id`,
class.`name`,
( SELECT count( * ) FROM student WHERE student.`class_id` = class.`id` ) AS 学生数
FROM
class;
+------+---------+-----------+
| id | name | 学生数 |
+------+---------+-----------+
| 1 | 班级1 | 3 |
| 2 | 班级2 | 2 |
| 3 | 班级3 | 2 |
+------+---------+-----------+
3 rows in set (0.00 sec)