SQL复杂查询
视图
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
为什么使用视图
- 重用SQL语句
- 简化复杂的SQL操作，编写查询后，可以方便地重用它而不必知道它的基本查询细节
- 使用表的组成部分而不是整张表
- 保护数据，可以给用户授予表的特定部分的访问权限而不是整张表的访问权限
- 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据
性能问题:
因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降的很厉害，所以，在部署使用大量视图的应用前，先进行测试。
视图的规则和限制
- 与表一样，视图必须唯一命名(不能给视图取与其他视图和表相同的名字)
- 对于可以创建的视图数目没有限制
- 为了创建视图，必须具有足够的访问权限
- 视图可以嵌套，可以利用从其他视图中检索数据的查询来构造一个视图
- ORDER BY 可以用在视图中，但如果从该视图中检索数据的SELECT语句中也包含ORDER BY，那么该视图的ORDER BY将被覆盖
- 视图不能索引，也不能有关联的触发器或默认值
- 视图可以和表一起使用
使用视图
- 创建视图
CREATE VIEW 视图名
- 使用SHOW CREATE VIEW viewname;查看创建视图的语句。
- 使用DROP删除视图
DROP VIEW viewname;
- 更新视图时，先使用DROP在使用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，第二条更新语句会创建一个视图，如果要更新视图存在，则第二条更新语句会替换掉原有视图。
更新视图
通常视图是可以更新的（即可以对它们使用INSERT,UPDATE和DELETE）更新一个视图将更新表（视图本身没有数据）如果对视图进行增加删除行，其实是对表进行增加删除行。
但并非所有的视图都可以更新，如果MySQL不能正确的确定更新的基数据，则不能进行更新，实际上，大多数视图都是不可更新的，因为视图主要用于数据检索。如果视图中以下操作，就不可以进行视图的更新：
- 分组(使用GROUP BY和HAVING)
- 联结
- 子查询
- 并
- 聚集函数(Min()，Count()，Sum()等)
- DISTINCT
- 导出(计算)列
使用视图简化复杂联结
视图极大的简化了复杂SQL语句的使用，使用视图，可以一次性编写基础的SQL，然后按需多次使用。
CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
创建了一个名为productcustomers的视图，联结了三个表，返回已订购了任意产品的所有客户的列表。
检索订购了产品TNT2的客户：
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id ='TNT2';
| cust_name | cust_contact |
|---|---|
| Coyote Inc | Y Lee |
| Yosemite Place | Y sam |
查看视图
查看视图和查看表的语句相同
desc 视图名;
select * from 视图名
用视图过滤不想要数据
CREATE VIEW list AS
SELECT id,username,price,date
FROM dataw
WHERE date IS NOT NULL;
将date为null的数据过滤掉，不在视图中显示。
使用视图与计算字段
CREATE VIEW data AS
SELECT
id,
username
date,
price*i AS iprice
FROM dataw;
子查询
任何sql语句都是查询，但是一般指的是SELECT语句。
sql允许创建子查询，即嵌套在其他查询中的查询。
子查询中最常见的使用是在WHERE子句的IN操作符中，以及用来填充计算列。
使用子查询进行过滤
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;
关联子查询
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);
- 关联查询处理过程：
首先执行不带WHERE的主查询
根据主查询讯结果匹配product_type，获取子查询结果
将子查询结果再与主查询结合执行完整的SQL语句
练习题一
- 3.1
创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。
条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。
SELECT * FROM ViewPractice5_1;
执行结果
product_name | sale_price | regist_date
--------------±-----------±-----------
T恤衫 | 1000 | 2009-09-20
菜刀 | 3000 | 2009-09-20
CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
FROM product WHERE sale_price >= 1000 ADN regist_date = "2009-09-20";
- 3.2
向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
对原始数据表不产生影响，只有对原始数据表进行操作，视图数据和原始数据表才会更新。
- 3.3
请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。
product_id | product_name | product_type | sale_price | sale_price_all
------------±------------±-------------±-----------±--------------------
0001 | T恤衫 | 衣服 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 办公用品 | 500 | 2097.5000000000000000
0003 | 运动T恤 | 衣服 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 厨房用具 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 厨房用具 | 6800 | 2097.5000000000000000
0006 | 叉子 | 厨房用具 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 厨房用具 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 2097.5000000000000000
SELECT product_id, product_name, product_type, sale_price,
(SELECT avg(sale_price) FROM product) AS sale_price_all
FROM product;
- 3.4
请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。
product_id | product_name | product_type | sale_price | avg_sale_price
------------±------------±-------------±-----------±--------------------
0001 | T恤衫 | 衣服 | 1000 |2500.0000000000000000
0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000
0003 | 运动T恤 | 衣服 | 4000 |2500.0000000000000000
0004 | 菜刀 | 厨房用具 | 3000 |2795.0000000000000000
0005 | 高压锅 | 厨房用具 | 6800 |2795.0000000000000000
0006 | 叉子 | 厨房用具 | 500 |2795.0000000000000000
0007 | 擦菜板 | 厨房用具 | 880 |2795.0000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000
提示：其中的关键是 avg_sale_price 列。与习题三不同，这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。
CREATE VIEW AvgPriceByType AS
SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price, p2.avg_sale_price
FROM product AS p1,
(SELECT product_type, avg(sale_price) AS avg_sale_price FROM product
GROUP BY product_type) AS p2
WHERE p1.product_type = p2.product_type;
SQL函数
算数函数
创建samplemath表并插入数据。
-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT; -- 提交事务
-- 查询表内容
SELECT * FROM samplemath;
- ABS – 绝对值
语法：ABS( 数值 ) - MOD – 求余数
语法：MOD( 被除数，除数 ) - ROUND – 四舍五入
语法：ROUND( 对象数值，保留小数的位数 )
ROUND 函数用来进行四舍五入操作。
SELECT m,
ABS(m)ASabs_col ,
n, p,
MOD(n, p) AS mod_col,
ROUND(m,1)ASround_colS
FROM samplemath;
注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。
字符串函数
- CONCAT – 拼接
语法：CONCAT(str1, str2, str3) - LENGTH – 字符串长度
语法：LENGTH( 字符串 ) - LOWER – 小写转换
- UPPER 函数用于大写转换。
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。 - REPLACE – 字符串的替换
语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 ) - SUBSTRING – 字符串的截取
语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。
日期函数
- CURRENT_DATE – 获取当前日期
- CURRENT_TIME – 当前时间
SELECT CURRENT_TIME
- CURRENT_TIMESTAMP – 当前日期和时间
SELECT CURRENT_TIMESTAMP
- EXTRACT – 截取日期元素
语法：EXTRACT(日期元素 FROM 日期)
【例子】
SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
谓词
谓词：返回值为真值（TRUE/FALSE/UNKNOWN）的函数。
LIKE - 字符串的部分一致查询
SELECT *FROM dataw WHERE username LIKE 'data%';
%：0 字符以上的任意字符串。
_：任意 1 个字符。
SELECT *FROM dataw WHERE username LIKE 'datawh___';
SELECT *FROM dataw WHERE username LIKE 'datawh_____';
BETWEEN - 范围查询
SELECT * FROM dataw WHERE price BETWEEN 4 AND 9;
re
BETWEEN会包含临界值，不需要临界值可以用>和<来处理
SELECT * FROM dataw WHERE price > 4 AND price< 9;
IS NULL、IS NOT NULL - 判断是否为 NULL
为了选取部分值为 NULL 的列的数据，不能使用 =，只能使用 IS NULL
SELECT * FROM dataw WHERE date IS NULL;
取反（不为空的数据），请使用 IS NOT NULL。
SELECT * FROM dataw WHERE date IS NOT NULL;
IN - OR 的简便用法
用IN替换上面的SQL语句：
否定形式 NOT IN：
- IN 和 NOT IN 是无法选取 NULL 数据的
EXIST
作用：判断是否存在满足某种条件的记录
很多时候基本上可以使用 IN 或 NOT IN 来代替该谓词。
NOT EXIST 与 EXIST 相反，不存在：
使用子查询作为 IN 谓词的参数
IN 和 NOT IN 谓词具有其它谓词没有的用法，它的参数可以是子查询。
SELECT price,username
FROM dataw
WHERE id IN(SELECT id FROM dataw WHERE username="datawhale");
SELECT price,username
FROM dataw
WHERE id NOT IN(SELECT id FROM dataw WHERE username="datawhale");
CASE 表达式
CASE 表达式：（条件）分歧。
语法：
CASE WHEN <判断表达式> THEN <表达式>
WHEN <判断表达式> THEN <表达式>
...
ELSE <表达式>
END
判断表达式类似“键 = 值”的形式，返回值为真值（TRUE/FALSE/UNKNOW）的表达式。如果结果为真，就会返回 THEN 子句中的表达式；如果不为真，就跳转到下一条 WHEN 子句的判断中；如果到最后的 WHEN 子句都不为真，就执行最后一条 ELSE 的表达式。
简化代码：
行转列
练习题二
- 3.5
运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）
会。
- 3.6
对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
①
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);
②
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
- 3.7
按照销售单价（ sale_price）对练习 6.1 中的 product（商品）表中的商品进行如下分类。
低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。
执行结果
low_price | mid_price | high_price
----------+-----------+------------
5 | 1 | 2
SELECT
sum(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,
sum(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END ) AS mid_price,
sum(CASE WHEN sale_price >3000 THEN 1 ELSE 0 END) AShigh_price
FROM product;
文章部分内容引用
SQL基础知识梳理：https://www.cnblogs.com/liqingwen/p/6572284.html
扫一扫
1600
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
