MySQL 使用篇子查询

本是古典何须时尚 2022-11-30 12:46 233阅读 0赞

### 多表查询的需求 ###

查询`杜子腾`的各科成绩

首先查询 `学生的ID`,再根据id 去查询各科成绩

mysql> SELECT number FROM student_info WHERE name = '杜子腾';
    SELECT * FROM student_score WHERE number = 20200803;

将两句合并

mysql> SELECT * FROM student_score \
    WHERE number = (SELECT number FROM student_info WHERE name = '杜子腾');

> 所有的子查询都必须用小括号扩起来，否则是非法的

### 标量子查询 ###

`标量子查询`单纯的代表一个值，由`标量子查询`作为的操作数组成的搜索条件只要符合表达语法就可以

如上述的例子就是标量子查询

mysql> SELECT * FROM student_score \
    WHERE number = (SELECT number FROM student_info WHERE name = '杜子腾');
    
    mysql> SELECT * FROM student_score \ 
    WHERE number > (SELECT number FROM student_info WHERE name = '杜子腾');

### 列子查询 ###

列子查询，就是我们的子查询语句的结果是一列

mysql> SELECT number FROM student_info WHERE major = '计算机科学与工程';
    
    +----------+
    | number   |
    +----------+
    | 20180101 |
    | 20180102 |
    +----------+

正常查询

mysql> SELECT * FROM student_score WHERE number IN (20180101, 20180102);

使用子查询

SELECT * FROM student_score \
    WHERE number \
    IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');

### 行子查询 ###

子查询结果是一行记录

SELECT * FROM student_score WHERE (number, subject) = (SELECT number, '母猪的产后护理' FROM student_info LIMIT 1);
    
    +----------+-----------------------+-------+
    | number   | subject               | score |
    +----------+-----------------------+-------+
    | 20180104 | 母猪的产后护理           |    55 |
    +----------+-----------------------+-------+

### 表子查询 ###

mysql> SELECT * FROM student_score \
    WHERE (number, subject) IN \
    (SELECT number, '母猪的产后护理' FROM student_info WHERE major = '计算机科学与工程');

### EXISTS和NOT EXISTS子查询 ###

有时候，外层查询并不关心子查询的具体结果，只关心是否查到了数据

SELECT * FROM student_score \
    WHERE EXISTS (SELECT * FROM student_info WHERE number = 20180108);
    
    Empty set (0.00 sec)
    # 显然，并没有20180108学号的学生

### 不相关子查询和相关子查询 ###

# 不相关子查询，子查询可以独立运行
    mysql> SELECT * FROM student_score \
    WHERE number = (SELECT number FROM student_info WHERE name = '杜琦燕');
    
    
    # 相关子查询, 子查询有用到外层查询的数据
    mysql> SELECT number, name, id_number, major FROM student_info \
    WHERE EXISTS \
    (SELECT * FROM student_score WHERE student_score.number = student_info.number);

### 对同一个表的子查询 ###

mysql>  SELECT * FROM student_score \
    WHERE subject = '母猪的产后护理' \
    AND score > (SELECT AVG(score) FROM student_score WHERE subject = '母猪的产后护理');