山西农业大学20240909

DQL语句

1. 子查询

1.1. 概述

嵌套在一个SQL语句中的DQL语句, 该DQL语句被称为子查询

1.2 应用场景

DQL中使用子查询

• 在SELECT子句中.将当前子查询结果作为一个字段展示
• 在WHERE子句中, 将当前查询结果作为过滤条件使用(最常用的场景)

DML(增INSERT删DELETE改UPDATE)中使用: 将一个查询结果集用于增删改操作

1.3 子查询分类

• 单行单列子查询 : 该查询的结果集只有一个值
• 多行单列子查询 : 该查询的结果集是多个值
• 多行多列子查询 : 将该子查询当做一张表来使用

1.3 子查询分类

1.3.1 单行单列子查询

# 单行单列子查询
# 查看比范传奇工资的高的老师都有谁?
# 1. 未知条件: 范传奇工资是多少?
SELECT name,salary FROM teacher WHERE name='范传奇'; # ==>3000
# 2. 未知条件: 谁的工资高于范传奇的工资?
SELECT name,salary FROM teacher WHERE salary>3000;

# 合并: 在数据库中, 被嵌套的SQL语句要用()括起来
SELECT name,salary FROM teacher
WHERE salary> (SELECT salary FROM teacher WHERE name='范传奇');
# 查看哪些老师的工资是高于平均工资的?
# 1. 未知条件: 先查询平均薪资是多少?
SELECT AVG(salary) FROM teacher;
# 2. 查看高于平均薪资
SELECT name,salary FROM teacher
WHERE salary > (SELECT AVG(salary) FROM teacher);

# 查看和'李费水'在同一个班级的学生都有谁?
SELECT name,class_id FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name='李费水');
# 查看工资最高的老师的工资和奖金是多少?
SELECT MAX(salary) FROM teacher;

SELECT name,salary,comm FROM teacher
WHERE salary=(SELECT MAX(salary) FROM teacher);

1.3.2 多行单列子查询

多行单列子查询可以检索出若干个值, 因此作为过滤条件使用时, 一般配置 IN,NOT IN使用
因为等于是不能同时等于好几个值的,只能等于其中一个

# 多行单列子查询
# 查看与"祝雷"和"李费水"在同一个班级的学生都有谁?
# 1. 未知条件: '祝雷'和'李费水' 的班级号是多少?
SELECT class_id FROM student WHERE name IN('祝雷','李费水');
# 2. 查看与他们同班级号的学生
SELECT name,class_id FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name IN('祝雷','李费水'));

# 修改:
SELECT name,class_id FROM student
WHERE class_id IN (SELECT class_id FROM student WHERE name IN('祝雷','李费水'));

# 查看比科目2和科目4工资都高的老师都有谁?
SELECT MAX(salary) FROM teacher WHERE subject_id IN (2,4);

SELECT name,salary,subject_id FROM teacher
WHERE salary > (SELECT MAX(salary) FROM teacher WHERE subject_id IN (2,4));

1.3.3 多行多列子查询

将一个结果集当做一张表创建出来

# 多行多列子查询
# 创建一张表, 该表中记录了每个科目老师的工资情况, 要求展示
# 最高, 最低, 综合,平均薪资以及科目id
CREATE TABLE teacher_salary_info
AS
SELECT MAX(salary) max_sal,MIN(salary) min_sal,SUM(salary) sum_sal,
       AVG(salary) avg_sal, subject_id
FROM teacher
GROUP BY subject_id;
# 当子查询的SELECT子句中包含函数或表达式, 应当为其取别名, 此时创建的表中该字段名会使用指定的别名
SHOW TABLES ;
SELECT * FROM teacher_salary_info;

2. 关联查询

联合多张表查询数据, 查询结果集中的字段来自多张表
表与表中的记录会产生对应关系, 用于我们联合查询

2.1 关联关系分类

• 一对一 : A表中的记录仅对应B表中的唯一一条记录, 反过来B表中的一条记录也仅对应A表中的唯一一条记录
• 一对多 : A表中记录对应B表中的多条记录, 反过来B表中的一条记录仅能唯一对应A表中的一条记录
• 多对多 : A表中记录对应B表中多条记录, 反过来B表中的记录也能对应A表中的多条记录

2.2 关联连接条件

在DQL语句中, 会指定连接条件, 用来让数据库在查询中知道两张表中记录与记录对应关系, 从而查询出对应的记录
注意: 关联查询中, 连接条件通常不可以忽略, 否则会产生"笛卡尔积"

2.3 语法

SELECT 字段,字段,字段…
FROM 表A,表B,[表C…]
WHERE A表与B表的连接条件
AND [其他的连接条件]
AND 过滤条件
注意: 连接条件要同时满足,并且如果有过滤条件的话, 也要同时满足过滤条件

• 如果不指定连接条件, 会产生 笛卡尔积

当不指定里连接条件时,数据库在关联查询时, 仍会用A的一条记录与B表每条记录连接一次, 并将产生的结果集中的记录, 此时数据量为A表的记录数与B表记录数的乘积

# 连接查询
# 查询每个老师以及他负责的课程科目名称
SELECT t.name,sj.name,subject_id
FROM teacher t,subject sj
WHERE t.subject_id = sj.id;
# 原理
SELECT name,age,subject_id FROM teacher;
SELECT id,name FROM subject;

# 1.查看班级的名称(class)和对应的班主任(teacher)是谁?
SELECT c.name,t.name
FROM class c,teacher t
WHERE c.teacher_id = t.id;
# 2.查看每个学生的姓名, 年龄, 以及其所在班级名称和所在楼层
SELECT s.name,s.age,c.name,c.floor
FROM student s,class c
WHERE s.class_id = c.id;
# 3.王克晶是哪个班的班主任?列出 班级的名称,楼层,老师名称, 工资
SELECT c.name,c.floor,t.name,t.salary
FROM teacher t , class c
WHERE c.teacher_id = t.id
AND t.name='王克晶';
# 4.查看三年级的班级班主任都是谁?要列出班级名称, 所在楼层,班主任和工资
SELECT c.name,c.floor,t.name,t.salary
FROM teacher t , class c
WHERE c.teacher_id = t.id
AND c.name LIKE '3年级%';
# 5.查看来自南京的学生学生都有哪些?要列出城市的名字, 学生名字, 年龄, 性别
SELECT l.name,s.name,s.age,s.gender
FROM student s ,location l
WHERE s.location_id = l.id
AND l.name='南京';
# 6.查看5年级的中队长都有谁?要列出学生名字, 年龄, 性别,职位和所在班级以及楼层
SELECT s.name,s.age,s.gender,s.job,c.name,c.floor
FROM student s,class c
WHERE s.class_id = c.id
AND c.name LIKE '5年级%'
AND s.job = '中队长';