子查询

Zpero
136 阅读6分钟

文末附表 students 和teacher 可以直接复制到mysql中使用

子查询

SQL语句中嵌套select语句 ,称为嵌套查询>,又称子查询.

子查询外部可以是 insert, update, delete ,select

根据子查询结果的不同,可以分为:

  • 标量子查询
  • 列子查询
  • 行子查询
  • 表子查询

可以将子查询放到子句中:

  • where
  • having
  • from

标量子查询

子查询返回的结果是单个值(数字\字符串\日期等),是最简单的形式

例如 , 查询 教授科目为数学的所有老师的名字

select tname from teacher where object = '数学';

image-20250710195310858

select s.* ,t.tname from students s join teacher t on t.id = s.teacherid
where s.teacherid =  (
	select id from teacher t where t.tname = '龙公' 
);

image-20250710204656069

列子查询

子查询返回的结果是一列(多行)

常见操作符: in ,not in ,any ,some ,all

根据查询到教授数学科目tname,查询老师的学生

select s.* ,t.tname from students s join teacher t on t.id = s.teacherid
where s.teacherid in  (
	select id from teacher t where t.object = '数学' 
);

image-20250710204051729

SELECT s.*, t.tname 
FROM (SELECT id FROM teacher WHERE object = '数学') tmp
JOIN teacher t ON tmp.id = t.id
JOIN students s ON s.teacherid = t.id;

三张表最少两个条件 ,使用派生表连接 , 性能比上面略快 因为创建了临时表 , 执行路径明确 ,强制使用 teacher.object索引.

image-20250710202633148

索引介绍详情见专栏文章,很快就会更新

select s.* ,t.tname 
from (select id from teacher where object = '数学' and age < 80 order by id limit 1000) tmp
join teacher t on tmp.id = t.id
join students s on t.id = s.teacherid;

image-20250710204105757

性能优化: 使用 order by 更好的利用上索引(在老师很多的情况下)

查询比 张一鸣 工资高的数学老师教授的学生

首先我们来看老师都有谁 , 供待会儿验证 :

select t.* from teacher t 
join (
	select salary from teacher where tname = '张一鸣'
) tmp
on t.salary > tmp.salary;

image-20250710211753204

select s.*,t.*
from students s join teacher t
on s.teacherid = t.id
where t.salary > all(
    select salary from teacher where tname = '张一鸣');

image-20250710210418626

性能优化:

select s.* ,t.* 
from students s 
join teacher t on t.id = s.teacherid
join (
	select salary from teacher where tname = '张一鸣'
    limit 1
) tmp 
on t.salary > tmp.salary;

image-20250710210424810

可以看到结果相同, 为什么会更高效呢?

​ 因为, 子查询在where从句中,原来的写法students和teacher的每一条匹配记录, 都会进行一次子查询 , 虽然mysql可能会优化

​ 在优化写法后, 创建了一个临时表,只需要进行一次子查询,然后和主查询连接

而且,使用join 语法,比where子查询 :

  • 给优化器更多选择执行路径的自由
  • 更容易利用索引
  • 减少重复计算

行子查询

子查询返回的结果是一行(多列)

行子查询 有一个特征,就是行构造器

(t.salary, t.object) = (...)
子查询返回多列

查询 和 王一博 的薪资相同且同科目老师带的学生

select s.* ,t.tname
from students s
join teacher t on t.id = s.teacherid
where (t.salary , t.object) = (
	select salary , object from teacher where tname = '王一博' limit 1
);

image-20250710214147355

查询 比 王一博 的薪资高且同科目老师带的学生

SELECT DISTINCT s.*
FROM students s
JOIN teacher t ON s.teacherid = t.id
JOIN (
    SELECT salary, object
    FROM teacher
    WHERE tname = '王一博'
    LIMIT 1  
) wyb ON t.salary > wyb.salary AND t.object = wyb.object;

image-20250710214459552

表子查询

表子查询是SQL中一种特殊的子查询形式，它返回的结果是一个多行多列的临时表，可以像普通表一样在外层查询中使用。

找出 那些平均薪资高于全校教师平均薪资的科目的教师所带的学生

select s.* ,t.tname
from students s
join teacher t on s.teacherid = t.id 
where t.object  in (
	select object  from teacher 
    group by object 
    having avg(salary) > (
    	select avg(salary)
        from teacher
    )
);

image-20250710223503962

性能优化:(需要配合索引)

方案一 , 使用派生表用join替代where:

SET @school_avg = (SELECT AVG(salary) FROM teacher);

select distinct s.* ,tname , high_pay_object.avga
from students s
join teacher t on s.teacherid = t.id 
join(
	select object ,avg(salary) as avga
    from teacher
    group by object 
    having avg(salary) > @school_avg
) high_pay_object 
on t.object = high_pay_object.object;

image-20250710223643868

使用变量存储全校老师的平均薪资

set 变量 只需要会用就行

方案二 , 使用with子句:

WITH 
-- 计算全校平均薪资
school_avg AS (
    SELECT AVG(salary) AS avg_salary FROM teacher
),
-- 计算各科目平均薪资
subject_avg AS (
    SELECT object, AVG(salary) AS subject_avg_salary
    FROM teacher
    GROUP BY object
    HAVING AVG(salary) > (SELECT avg_salary FROM school_avg)
)
-- 主查询
SELECT s.*, t.tname, sa.subject_avg_salary
FROM students s
JOIN teacher t ON s.teacherid = t.id
JOIN subject_avg sa ON t.object = sa.object;

image-20250710224335112

最后贴一下两张表供使用

create database mysql_test;
use mysql_test;

-- 创建teacher表（精确匹配原表结构）
CREATE TABLE teacher (
    id TINYINT(3) UNSIGNED ZEROFILL NOT NULL,
    tname VARCHAR(200) COLLATE utf8mb4_0900_ai_ci,
    age INT,
    object VARCHAR(8) COLLATE utf8mb4_0900_ai_ci NOT NULL,
    salary INT,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='教师表';

-- 插入数据（完全匹配原数据）
INSERT INTO teacher (id, tname, age, object, salary) VALUES
(001, '林飘飘', 33, '语文', 5000),
(002, '李空', 22, '数学', 5000),
(003, '迪迦', 22, '政治', 5000),
(004, '孙笑川', 30, '语文', 5000),
(005, '王一博', 28, '体育', 5000),
(006, '张一鸣', 22, '数学', 5000),
(007, '西门吹雪', 16, '英语', 3000),
(008, '薄青', 39, '英语', 8000),
(009, '马鸿运', 80, '数学', 8000),
(010, '龙公', 100, '体育', 8000),
(011, '古月方源', 50, '英语', 8000);

-- 验证数据
SELECT * FROM teacher;


-- 创建students表（精确匹配原表结构）
CREATE TABLE students (
    id TINYINT(3) UNSIGNED ZEROFILL NOT NULL,
    name VARCHAR(10) COLLATE utf8mb4_0900_ai_ci NOT NULL,
    score_rank INT,
    teacherid TINYINT(3) UNSIGNED ZEROFILL ,
    foreign key(teacherid) references teacher(id),
    teamleader VARCHAR(10) COLLATE utf8mb4_0900_ai_ci,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 插入数据（完全匹配原数据）
INSERT INTO students (id, name, score_rank, teacherid, teamleader) VALUES
(001, '张三', 1, 001, '张三'),
(002, '李四', 2, 001, '张三'),
(003, '王五', 3, 001, '张三'),
(004, '赵六', 4, 001, '张三'),
(005, '谢广坤', 5, 002, '基尼太美'),
(006, '王德发', 6, 002, '基尼太美'),
(007, '老太太', 7, 003, '基尼太美'),
(008, '基尼太美', 8, 004, '基尼太美'),
(009, '李宁', 9, 005, '李宁'),
(010, '窦唯', 10, 010, '李宁'),
(011, '李逍遥', 11, 009, '李宁'),
(012, '赵灵儿', 12, 009, '李宁'),
(013, '景天', 13, 006, '景天'),
(014, '何必平', 14, 006, '景天'),
(015, '唐雪见', 15, 009, '景天'),
(016, '白长卿', 16, 006, '景天');

-- 验证数据
SELECT * FROM students;

如果喜欢文章的话请点赞支持一下 , 我会频繁更新专栏 . 文章中提到的 索引 会在之后更新

日拱一卒

2025-07-10 22:55:32

avatar
文章
阅读
粉丝