Mysql 基础(十三) 复杂查询

复杂查询

生活的道路一旦选定，就要勇敢地走到底，决不回头。——左拉

视图

• 从 SQL 角度来看,视图和表是相同的,两者的区别是表中保存的是实际的数据,而视图中保存的是 SELECT 语句(视图本身并不存储数据)

• 使用视图,可以轻松完成跨多表查询数据等复杂操作

• 可以将常用的 SELECT 语句作为视图来使用

• 创建视图需要使用 CREATE VIEW 语句

• 视图包含”不能使用 ORDER BY”和”可对其有限制的更新”两项限制

• 删除视图需要使用 DROP VIEW 语句

视图和表

什么是视图

从 SQL 来看视图就是一张表

• 视图和表的区别

是否保存了真实的数据

使用表的时候我们就是从存储设备(硬盘)中读取数据,进行各种计算之后再将结果返回给用户这样一个过程

但是使用视图的时候并不会将数据保存到设备中,而且也不会将数据保存到其他任何地方,所以视图保存的是 SELECT 语句,我们从视图中读取数据时,视图会在内部执行该 SELECT 语句并创建一张临时表

视图的优点

• 由于视图无需保存数据。因此可以节省存储设备的容量。(因为它存储的是 SQL 语句)

• 可以将频繁使用的 SELECT 语句保存成视图,这样就不用每次都重新书写了创建好视图后只需在 SELECT 语句中进行调用就可以方便得到想要的结果。特别是在进行汇总以及复杂的查询条件导致 SELECT 语句非常庞大,使用视图可以大大效率

• 视图中的数据会随着原表的变化自动更新.视图归根到底就是 SELECT 语句,所谓参照视图也就是执行 SELECT 语句的意思,因此可以保证数据的最新状态.

应当将经常使用的 SELECT 语句做成视图

创建视图的方法

• 创建视图需要使用 CREATE VIEW 语句,其语法如下:

CREATE VIEW (<视图列名1>,<视图列名2>,...) AS 

SELECT 语句需要书写在 AS 关键字之后 SELECT 语句中列的排列顺序和视图中列的排列顺序相同.SELECT 语句中的第 1 列就是视图中的第一列,SELECT 语句中的第 2 列就是视图中的第 2 列。以此类推:视图的列名在视图名称之后的列表中定义

• 实例化

CREATE VIEW ProductSum (product_type,cnt_product) AS SELECT product_type,COUNT(*) FROM Product GROUP BY product_type;

这里千万不能省略 AS 关键字

这样一个视图就创立完了

• 使用视图

SELECT product_type,cnt_product FROM ProductSum;

• 结果

product_type	cnt_product
衣服	2
办公用品	2
厨房用具	4

这样下次在查询的时候需要在工作中频繁汇总的时候,就不用每次使用 GROUP BY 和 COUNT 函数 写 SELECT 语句来从 Product 表中取得数据了.创建出视图之后就可以通过非常简单的 SELECT 语句随时取得汇总的结果,这样表中的数据更新后视图也会随之更新

之所以这样是因为视图就是保存好的 SELECT 语句定义视图的时候可以使用处 ORDER BY 以外的任何 SELECT 语句比如 GROUP BY WHERE,HAVING 等等

视图嵌套

我们可以在视图的规模上在创建视图,如下面 ProductSum 就是视图

CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
SELECT product_type, cnt_product
FROM ProductSum
WHERE product_type = '办公用品';

• 执行结果

product_type	cnt_product
办公用品	2

这里特别强调的是一般不推荐这么做,或者避免在视图上创建视图,因为多重视图会降低 SQL 性能.

视图的限制

• 定义视图时候不能使用 ORDER BY 子句

CREATE VIEW ProductSum (product_type,cnt_product) AS
SELECT product_type,COUNT(*) FROM Product GROUP BY product_type
ORDER BY product_type

因为视图和表一样,数据行都是没有顺序的。所以在定义视图时请不要使用 ORDER BY 子句

• 对视图进行更新(一般情况禁止更新)

一句话来说通过汇总得到视图 不允许使用 INSERT UPDATE,DELETE 等等,因为它是汇总的

而不是汇总得到的视图 允许被更新

比如 要是创建汇总

CREATE VIEW ProductSum (product_type,cnt_product) AS SELECT product_type,COUNT(*) FROM Product GROUP BY product_type;

这种情况要是使用 INSERT 之类的会报错,因为它是汇总的

但是如果要是下面这种,没有使用聚合的,则可以修改 INSERT

CREATE VIEW ProductJim (product_id, product_name, product_type, 
sale_price, purchase_price, regist_date)
AS
SELECT *
FROM Product
WHERE product_type = '办公用品';

• 比如添加数据

INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');

删除视图

• 基础语法

DROP VIEW 视图名称(<视图列名1>,<视图列名2>)

• 实例化

DROP VIEW ProductSum;

子查询

• 子查询就是一次性视图(SELECT 语句)与视图不同,子查询在 SELECT 语句执行完毕就会消失

• 由于子查询需要命名,因此需要根据处理内容来指定恰当的名称

• 标量子查询就是只能返回一行一列的子查询

子查询和视图

• 子查询的特点概括起来就是一张一次性视图

• 子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句中

• 简单来说就是查询嵌套,例如下面()里面的就是子查询 AS 不能省

-- 在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
FROM ( SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type ) AS ProductSum;

执行顺序 它会首先执行子查询 然后执行外层的 SELECT 查询

子查询嵌套

• 例如

SELECT product_type, cnt_product
FROM (SELECT *
FROM (SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type) AS ProductSum
WHERE cnt_product = 4) AS ProductSum2;

子查询要是嵌套太多了,可读性会变差,性能也会变差,所以尽量避免

标量子查询

标量子查询有一个特殊的限制,那就是必须而且只能返回 1 行 1 列的结果
也就是返回表中某一行的某一列的值 例如’10’或者’东京都’这样的值

标量子查询实际上就是返回单一值的子查询

例如

-- 在WHERE子句中不能使用聚合函数
SELECT product_id, product＿name, sale_price
FROM Product
WHERE sale_price > AVG(sale_price);

这种写法就是错误的 因为在 WHERE 子句里面不能使用聚合函数

• 但是利用子查询则可以改变规则

SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);

标量子查询的书写位置

• 标量子查询的书写位置并不仅仅局限于 WHERE 子句中,通常任何可以使用单一值的位置可以使用.也就是说 能够使用常数或者列名的地方,无论 SELECT 子句,GROUP BY 子句,HAVING 子句,还是 ORDER BY 子句 几乎所有的地方都可以使用

比如

SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM Product) AS avg_price
FROM Product;

但是标量子查询必须也只能返回一条数据 比如 下面这个就是错误的

因为 GROUP BY xxxx 返回的结果就不止一条

SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM Product
GROUP BY product_type) AS avg_price
FROM Product;

关联子查询

• 关联子查询会在细分的组内进行比较时使用

• 关联子查询和 GROUP BY 子句一样 也可以对表中的数据进行切分

• 关联子查询的结合条件如果未出现在子查询内就会发生错误

SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product AS P2
WHERE P1.product_type = P2.product_type
GROUP BY product_type);

• 这里面 P1,P2 都指向的是 Product 表而不是最后的结果

• 关联的核心就是 P1.product_type = P2.product_type

P2 只能在括号里面使用,括号外面用不了但是 P1 在子查询能用,子查询内部只能子查询使用

关联子查询的执行顺序是不一样的

• (1) 先执行主查询

SELECT product_type, product_name, sale_price
FROM Product AS P1

• (2) 从主查询的 product_type 取得第一个值 比如”衣服”,通过 where p1.product_type = p2.product_type 转入子查询:

SELECT AVG(sale_price)

FROM Product AS P2

WHERE P2.product_type = '衣服'

GROUP BY product_type);

这样获取到的结果就是价格的平均值

AVG(sale_price)
2500.0000

• (3) 把这个数字接着返回主查询

SELECT product_type , product_name, sale_price

FROM Product AS P1

WHERE sale_price > 2500 AND product_type = '衣服'

结果

product_type	product_name	sale_price
衣服	运动 T 恤	4000

然后在以此类推