SQL进阶之一:复杂查询

熟悉SQL的CRUD后,是时候来提高SQL能力了。
本文重点是学习视图和子查询,这两者可以跟表一样进行使用,因此恰当地运用他们可以写出更灵活的SQL语句。

1. 视图

1.1 视图和表

  • 视图 VIEW:保存SELECT语句的虚拟表。

Views are stored queries that when invoked produce a
result set. A view acts as a virtual table.
视图保存了查询语句,当调用视图时会产生一个结果集。视图充当了虚拟表的角色。
——来自MySQL官方文档Chapter24.5 Using Views

  • 视图和表的区别

    • 表含有真实数据,这些数据存储了硬盘中
    • 视图真正保存的是SELECT语句,当调用视图时,就相当于执行该SELECT语句并创建一张临时表。
  • 视图的优点:

    • 节省存储设备容量:视图保存的是SELECT语句而不用保存数据
    • 重用SELECT语句:将频繁使用的SELECT保存成视图,这样就不用每次重新书写了
    • 简化复杂的SQL操作:不用了解基本查询细节直接使用视图即可
    • 保护数据:可以授予用户访问表的特定部分的权限,而不是整个表的访问权限
    • 更改数据格式和表示:视图可返回与底层表的表示和格式不同的数据。

!注意:性能问题
每次调用视图时它实际上会执行查询操作,如果使用多个连接和过滤创建了复杂的视图或者嵌套了视图,性能可能会下降得很厉害。因此,在部署使用了大量视图的应用前,应该进行性能上的测试。

1.2 创建视图

CREATE
 [OR REPLACE]
 [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
 [DEFINER = user]
 [SQL SECURITY { DEFINER | INVOKER }]
 VIEW view_name [(column_list)]
 AS select_statement
 [WITH [CASCADED | LOCAL] CHECK OPTION]

创建测试表product

CREATE TABLE product(
	product_id CHAR(4) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    product_type VARCHAR(32) NOT NULL,
    sale_price INTEGER,
    purchase_price INTEGER,
    regist_date DATE,
    PRIMARY KEY(product_id)
);

START TRANSACTION;
INSERT INTO product VALUES('0001', 'T恤衫',   '衣服',     1000, 50,  '2020-09-20');
INSERT INTO product VALUES('0002', '打孔器',  '办公用品', 1000, 32,  '2020-09-20');
INSERT INTO product VALUES('0003', '运动T恤', '衣服',     1000, 280, '2020-09-20');
INSERT INTO product VALUES('0004', '菜刀',    '厨房用具', 1000, 500, '2020-09-20');
INSERT INTO product VALUES('0005', '高压锅',  '厨房用具', 1000, 680, '2020-09-20');
INSERT INTO product VALUES('0006', '叉子',    '厨房用具', 1000, 5,   '2020-09-20');
INSERT INTO product VALUES('0007', '擦菜板',  '厨房用具', 1000, 88,  '2020-09-20');
INSERT INTO product VALUES('0008', '圆珠笔',  '衣服',     1000, 10,  '2020-09-20');
COMMIT;

创建视图

CREATE VIEW productsum(product_type, cnt_product)
AS 
SELECT product_type,COUNT(*)
FROM product
GROUP BY product_type;

1.3 查看视图

查看某个具体的视图

SELECT * FROM productsum;

在这里插入图片描述
查看某个数据库下的所有视图:
方法一:通过状态的COMMENT列来查找:

说明:Mysql5.1支持视图,视图被看作一种抽象表,因此显示视图状态的语句与显示表状态的语句相同,只是在COMMENT列中以view区分

SHOW TABLE STATUS WHERE COMMENT='VIEW';

方法二:通过系统表information_schema.tables查询

SELECT * FROM information_schema.tables 
WHERE table_schema='yourDatabaseName' AND table_type='VIEW';

1.4 删除视图

DROP VIEW view_name;

1.5 视图限制条件

  1. 定义视图时不要使用ORDER BY子句
  2. 更新视图需要满足几个条件

标准SQL中有这样的规定:如果定义视图的SELECT语句能够满足某些条件,那么这个视图就可以被更新,下面列举一些比较具有代表性的条件;

  • SELECT子句中未使用DESINCT
  • FROM子句中只有一张表
  • 未使用GROUP BY子句
  • 未使用HAVING子句

视图归根结底还是从表派生出来的,如果原表可以更新,那么视图中的数据也可以更新,反之亦然,视图中的数据可以更新,那么原表的数据也可以更新。如果视图发生了改变,而原表没有进行相应更新的话,就无法保证数据的一致性了。

创建一视图,这个视图可以满足更新的条件

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,'2020-11-30');

查询该视图

SELECT * FROM productjim;

在这里插入图片描述
查询product原表,可以看到在原表中也插入成功了
在这里插入图片描述

1.6 好的视图编程习惯

  1. 将频繁使用的SELECT语句做成视图
  2. 尽量避免多重视图——即在某一视图的基础上创建视图

2. 子查询

2.1 子查询

  • 子查询 Subquery 即嵌套在其他查询中的查询。

子查询也可以看做是一张一次性的视图

2.2 标量子查询

  • 标量子查询:返回单一值(即一行一列)的子查询。
    由于标量子查询返回值是单一的,因此可以使用=><这样的比较运算符中,这也正是标量子查询的优势所在。

假设我们需要在product表中查询这样的表:
“查询出购买高于平均购买价的商品”
我们一开始可能会考虑这样的SQL语句

SELECT *
FROM product
WHERE purchase_price > AVG(purchase_price);

很可惜,WHERE子句不能使用聚合函数,所以该语句会报错,这时我们可以使用子查询:

SELECT *
FROM product
WHERE purchase_price >( 
	SELECT AVG(purchase_price)
	FROM product
	);
  • 标量子查询的特性决定了它的巨大优势,它可以用在任何使用常数或者列名的地方,无论是SELECT子句、GROUP BY子句、HAVING子句,还是ORDER BY子句,几乎所有的地方都可以使用。

2.3 关联子查询

关联子查询比较诡异,知乎上有一篇讲得很好的文章,在此贴上简单易懂教你学会SQL关联子查询

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值