SQL - 学习笔记4
文章目录
14、多表查询
(1) 多表查询 - 标量子查询
子查询:嵌套在其他查询中的查询
标量子查询:只返回一行一列(即一个单元格数据)的子查询。相当于是一个常数。
(2) 多表查询 - 关联子查询
嵌套在其他查询中的查询。适用于组内比较
子查询返回一列数据:子查询的结果,与主查询的目标列存在一定关联。
(3) 多表查询 - 普通子查询
① 嵌套在其他查询中的查询。
子查询返回一列数据:将子查询的结果列,作为主查询的取值范围。
② 嵌套在其他查询中的查询。
子查询返回二维表:将二维表作为主查询新的检索表。
(4) 多表查询 - 表联结
关系型数据库是将数据保存在不同的表中
原则:一类数据一个表;表之间相互联系
分解数据为多个表能更有效方便地处理数据,但是检索数据时,则必须使用 - 联结
联结:一种机制,关联多个表,返回一组输出。在一条SELECT语句中实现。
主键:唯一标识,可以是ID或任何其他唯一值。
SELECT [列名] -> 指定检索列,可以出自不同表
FROM 表1,表2 -> 从一个表拓展为两个表或更多表
WHERE [表1.列a] = [表2.列b] -> 过滤条件外多了一个联结条件,逐行匹配
① 多表查询 - 内部联结(等值联结)
INNER JOIN … ON …
联结方式 -> 内部联结 ->
联结前提:必须有共同列
联结结果:所有表的所有列、共同列重复的行
创建联结规则:列出所有表、定义所有表关系
② 多表查询 - 自联结(特殊内联结)
联结方式 -> 自联结 ->
联结前提:联结表为同一个
联结结果:返回所有数据
创建联结规则:列出所有表、定义所有表关系
③ 多表查询 - 外部联结(特殊内联结)
联结方式 -> 自联结 ->
联结前提:必须有共同列
联结结果:所有表的所有列、包含相关表中没有关联行的行
创建联结规则:列出所有表、定义所有表关系
左外部联结 - 包含左边表的所有行 LEFT OUTER JOIN
右外部联结 - 包含右边表的所有行 RIGHT OUTER JOIN
全外部联结 - 包含两边表的所有行 FULL OUTER JOIN
④ 多表查询 - 表联结
(5) 组合查询 - UNION
1)自动过滤重复行;两条及两条以上SELECT语句,每两条之间UNION一次;UNION ALL关键字(不删重复的记录)。
2)相同的列、表达式或聚集函数;数据类型必须兼容(允许隐式转换);最终字段名由第一条SELECT语句决定。
3)ORDER BY放在最后一条SELECT语句中, 作用整个检索结果;字段名由SELICT展示结果决定。
相关练习:
Q1:总结一下子查询的几种类别,及分别的用法和特征(包括子查询返回值的样式、子查询在主查询语句中的位置)。
(1)标量子查询:子查询返回一行一列的一个值,即一个单元格数据;子查询结果可作为主查询语句的常数列或用于WHERE子句的过滤条件和分组的筛选条件HAVING子句中。
(2)关联子查询:子查询返回一列数据,子查询的结果与主查询的字段存在一定关联。用于WHERE子句的过滤条件和分组的筛选条件HAVING子句中;关联条件需要写在子查询部分。
(3)普通子查询:子查询返回一列数据,将子查询的结果列作为主查询不连续的取值范围;或者子查询返回二维表,将二维表作为主查询的新的检索表。
Q2:具体说明内联结和外联结的异同,以及联结的具体写法。
同:都必须有共同列,创建联结规则都需要列出所有表并定义所有表之间的关系。
异:内部联结只保留存在相等关系的行,外部联结哪边外,则保留其所有行,另一边只保留存在向党关系的行。
SELECT … FROM … INNER JOIN … ON …
SELECT … FROM … LEFT/RIGHT/FULL OUTER JOIN … ON …
Q3:按要求写出以下语句。
1、找出所有洁云牌抽纸的销售流水(使用标量子查询)。
SELECT o.*
FROM order_list o
WHERE o.prod_id = (SELECT prod_id FROM prod_info p WHERE p.prod_name = ‘抽纸’ AND p.brand = ‘洁柔’);
2、找出各类商品中,价格高于该类商品均价的部分(使用关联子查询)。
SELECT p.*
FROM prod_info p
WHERE p.sale_price > (
SELECT AVG(p2.sale_price)
FROM prod_info p2
WHERE p.class = p2.class
GROUP BY p2.class
)
3、用联结的方法改写第一题。
SELECT o.*
FROM prod_info p INNER JOIN order_list o
ON p.prod_id = o.prod_id AND p.brand = ‘洁柔’ AND p.prod_name = ‘抽纸’;
4、归总下所有商品的销量。
SELECT p.prod_id, SUM(o.quantity)
FROM prod_info p LEFT OUTER JOIN order_list o
ON p.prod_id = o.prod_id
GROUP BY p.prod_id