高级查询

背景

如何使用sql查询两张及以上表格的数据
开端的语法是连接查询（又称多表查询，当查询的字段来自于多个表时，就会用到连接查询）
- 其意义在于，避免了无效连接后出现的笛卡尔乘积现象（表1 有m行，表2有n行，结果=m*n行）
接着是联合查询、子查询

案例代码（student、subject、score三张表）

score.png

subject.png

student.png

有关于笛卡尔查询的代码
查询score中每行数据的老师姓名
笛卡尔现象代码：

建立有效连接后的代码

分类：

按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

按功能分类：

内连接：
- 等值连接
- 非等值连接
- 自连接
外连接：
- 左外连接
- 右外连接
- 全外连接
交叉连接

一、sql92标准

1、等值连接

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

之前的案例，就是一个等值连接

为表起别名

①提高语句的简洁度
②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

可以使用AND关键字增加筛选

查询score表中的学生姓名，并且学生年龄要大于18

可以使用分组查询、排序

查询每个科目保留两位小数的平均分，按照降序排列

可以三表连接

查询每个科目保留两位小数的平均分（要求：学生成绩及格的），按照降序排列

2、非等值连接

创建level表

查询每个学生具体成绩的等级

自连接

挑选出语文成绩高于数学成绩的学生

二、sql99语法

语法：

select 查询列表
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

连接类型分类：

内连接（★）：inner
外连接
左外(★):left 【outer】
右外(★)：right 【outer】
交叉连接：cross

一）内连接

语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

1、等值连接

查询score中的老师

可以使用AND关键字增加筛选

查询score表中的学生姓名，并且学生年龄要大于18

可以使用分组查询、排序

查询每个科目保留两位小数的平均分，按照降序排列

可以三表连接

查询每个科目保留两位小数的平均分（要求：学生成绩及格的），按照降序排列

2、非等值连接

查询每个学生具体成绩的等级

自连接

挑选出语文成绩高于数学成绩的学生

二、外连接

应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果

修改表数据使连接存在null值

左外连接 score主表

右外连接 score主表

左外连接 student主表

右外连接 student主表

交叉连接

sql92和 sql99pk

功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高

子查询

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：

select后面：
仅仅支持标量子查询
from后面：
- 支持表子查询
where或having后面：★
- 标量子查询（单行） √
- 列子查询（多行） √
- 行子查询

exists后面（相关子查询）
- 表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

一、where或having后面

标量子查询（单行子查询）
列子查询（多行子查询）
行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用

列子查询，一般搭配着多行操作符使用
in、any/some、all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1.标量子查询★

查询语文成绩比春喜高的学生清单
先找春喜的语文成绩

再找成绩比春喜高的学生

支持分组及排序查询

查询学生两科目平均分低于整体平均分的人
先查询平均分

再查询小于平均分的学生

2.列子查询（多行子查询）★

查询编号为学生为李四和王五的数学成绩

3、行子查询（结果集一行多列或多行多列）

查询编号最大的最高成绩

二、select后面

仅仅支持标量子查询

查询及格的学生总个数

三、from后面

将子查询结果充当一张表，要求必须起别名

查询每个老师下的及格学生个数

四、exists后面（相关子查询）

语法：
exists(完整的查询语句)
结果：
1或0

查询是否存在不及格的学生

联合查询

union 联合合并：将多条查询语句的结果合并成一个结果

语法：
查询语句1
union
查询语句2
union
...

应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：★
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项

查询编号为001的学生姓名和编号为1001的科目

关于三者的异同

子查询：

子查询是多表查询的一种形式，是一种次要查询，返回的结果数据被用于主查询的条件中，它返回的结果可以是一行或多行
查询效率较低，性能差，因为底层数据是笛卡儿积，
可读性相对较高
代码较麻烦
当只需要唯一的列，用子查询

连接查询

连接查询是对两个以上表逐记录（一行一行地）进行相关查询
主要使用join关键字，配合inner （内连接）left outer （左外连接）right outer（右外连接）等使用
性能高
代码简洁
可读性较差
当需要连接多个表时，用联结查询更好，效率高

组合查询

union关键字
要求返回的列结构类似，列类型必须兼容
union默认去重，使用union all可以返回所有结果
用union查询时，只能使用一条ORDER BY 语句，且只能出现在最后一条SELECT语句之后

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。