SQL学习|03 复杂查询方法-视图、子查询、函数等

作者: 8a590e918db0 | 来源:发表于2021-05-18 18:32 被阅读0次

SQL学习|03 复杂查询方法-视图、子查询、函数等
2018-03-20
数据库上基本笔记
mysql操作
搜索
chapter22：使用视图
c#11月13号
SQL干货篇三
Mysql多表查询, 视图,事务,索引，函数，go连接数据库
DDL 数据库视图定义

一、视图

1.1 什么是视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的。所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

1.2 视图与表有什么区别

视图与表的区别：“是否保存了实际的数据”。视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

image.png

“视图不是表，视图是虚表，视图依赖于表”。

1.3 为什么会存在视图

通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
通过定义视图可以使用户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
通过定义视图可以降低数据的冗余。

1.4 如何创建视图

创建视图的语法：

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

视图名在数据库中需要是唯一的，不能与其他视图和表重名。
视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。
虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。
需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。
这是因为视图和表一样，数据行都是没有顺序的。

在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

1.5 如何修改视图的结构

修改视图结构的语法：

ALTER VIEW <视图名> AS <SELECT语句>

其中视图名在数据库中需要是唯一的，不能与其他视图和表重名。

1.6 如何更新视图内容

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。
视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。
更新视图：

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';
-- 更新视图，原表的数据也被更新了
-- 注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表

1.7 如何删除视图

删除视图的语法：

DROP VIEW <视图名1> [ , <视图名2> …]

注意：需要有相应的权限才能成功删除。

二、子查询

2.1 什么是子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

2.2 子查询和视图的关系

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中，而是在 SELECT 语句执行之后就消失了。

2.3 嵌套子查询

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;

虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。

2.4 标量子查询

标量就是单一，所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。

2.5 标量子查询有什么用

由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

2.6 关联子查询

关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的。
关联查询的执行过程：

首先执行不带WHERE的主查询
根据主查询结果匹配product_type，获取子查询结果
将子查询结果再与主查询结合执行完整的SQL语句

练习题

CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
  FROM product
 WHERE sale_price >= 1000 and regist_date = '2009-09-20';

2
视图插⼊数据时，原表也会插⼊数据，⽽原表数据插⼊时不满⾜约束条件，所以会报错。
3
根据如下结果编写SQL语句：

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) sale_price_all
FROM product;

4
编写SQL语句，创建一个包含如下数据的视图。

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

select product_id, product_name, product_type, sale_price, (select avg(sale_price) from product b where a.product_type = b.product_type group by product_type) avg_sale_price
  from product a;

三、各种各样的函数

函数大致分为如下几类：

算术函数（用来进行数值计算的函数）
字符串函数（用来进行字符串操作的函数）
日期函数（用来进行日期操作的函数）
转换函数（用来转换数据类型和值的函数）
聚合函数（用来进行数据聚合的函数）

函数总个数超过200个，不需要完全记住，常用函数有 30~50 个，其他不常用的函数使用时查阅文档即可。

3.1 算数函数

ABS(数值)：计算绝对值
MOD(被除数，除数)：求余数
ROUND(数值，保留位数)：四舍五入

SELECT m,
ABS(m)ASabs_col ,
n, p,
MOD(n, p) AS mod_col,
ROUND(m,1)ASround_colS
FROM samplemath;

3.2 字符串函数

CONCAT(str1, str2, str3)：拼接字符串
LENGTH( 字符串 )：求字符串长度
LOWER()：小写转换
UPPER()：大写转换
REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )：字符串替换
SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）：截取子串
SUBSTRING_INDEX (原始字符串，分隔符，n)：字符串按索引截取

3.3 日期函数

CURRENT_DATE：获取当前日期
CURRENT_TIME：当前时间
CURRENT_TIMESTAMP：当前日期和时间
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;
+---------------------+------+-------+------+------+--------+--------+
| now                 | year | month | day  | hour | MINute | second |
+---------------------+------+-------+------+------+--------+--------+
| 2020-08-08 17:34:38 | 2020 |     8 |    8 |   17 |     34 |     38 |
+---------------------+------+-------+------+------+--------+--------+
1 row in set (0.00 sec)

3.4 转换函数

CAST（转换前的值 AS 想要转换的数据类型）:数据类型转换

SELECT CAST('2009-12-14' AS DATE) AS date_col;
+------------+
| date_col   |
+------------+
| 2009-12-14 |
+------------+
1 row in set (0.00 sec)

COALESCE(数据1，数据2，数据3……):将NULL转换为其他值,该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。

SELECT COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, 'hello world', NULL) AS col_2,
COALESCE(NULL, NULL, '2020-11-01') AS col_3;
+-------+-------------+------------+
| col_1 | col_2       | col_3      |
+-------+-------------+------------+
|    11 | hello world | 2020-11-01 |
+-------+-------------+------------+
1 row in set (0.00 sec)

四、谓词

4.1 什么是谓词

谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
谓词主要有以下几个：

LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

4.2 LIKE谓词 – 用于字符串的部分一致查询

-- 前方一致
SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
-- 中间一致
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
-- 后方一致
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';
-- _下划线匹配任意 1 个字符
SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';

4.3 BETWEEN谓词 – 用于范围查询

-- 选取销售单价为100～ 1000元的商品
-- BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

4.4 IS NULL、 IS NOT NULL – 用于判断是否为NULL

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
-- 
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

4.5 IN谓词 – OR的简便用法

-- 需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);

4.6 使用子查询作为IN谓词的参数

SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
  FROM shopproduct
WHERE shop_id = '000C');

4.7 EXIST 谓词

EXIST 是只有 1 个参数的谓词，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。

-- 由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系
SELECT product_name, sale_price
  FROM product AS p
 WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数
                 FROM shopproduct AS sp
                WHERE sp.shop_id = '000C'
                  AND sp.product_id = p.product_id);

五、CASE 表达式

5.1 什么是 CASE 表达式？

语法：

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END

5.2 CASE表达式的使用方法

应用场景1：根据不同分支得到不同列值

SELECT  product_name,
        CASE WHEN product_type = '衣服' THEN CONCAT('A ： ',product_type)
             WHEN product_type = '办公用品'  THEN CONCAT('B ： ',product_type)
             WHEN product_type = '厨房用具'  THEN CONCAT('C ： ',product_type)
             ELSE NULL
        END AS abc_product_type
  FROM  product;

应用场景2：实现列方向上的聚合

-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
  FROM product;

（扩展内容）应用场景3：实现行转列

SELECT name,
       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
  FROM score
 GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 |      93 |   88 |      91 |
| 李四 |      87 |   90 |      77 |
+------+---------+------+---------+
-- CASE WHEN 实现文本列 subject 行转列
SELECT name,
       MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,
       MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,
       MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as english
  FROM score
 GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 | 语文    | 数学 | 外语    |
| 李四 | 语文    | 数学 | 外语    |
+------+---------+------+---------+

总结：

当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
当待转换列为文本时，可以使用MAX MIN等聚合函数

练习题

按照销售单价（ sale_price）对 product（商品）表中的商品进行如下分类。

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）

select count(case when sale_price <= 1000 then product_name else null end) low_price,
count(case when sale_price between 1001 and 3000 then product_name else null end) mid_price,
count(case when sale_price > 3000 then product_name else null end) high_price
 from product;

SQL学习|03 复杂查询方法-视图、子查询、函数等
一、视图 1.1 什么是视图视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的。所以操...
2018-03-20
MYSQL查询语句 MYSQL复杂操作语句 MYSQL多表查询方法函数部分
数据库上基本笔记
SQl语言的复杂查询与视图主要内容： [not]IN some / all [not]exists 聚集函数 g...
mysql操作
sql一些函数,复杂查询等基本操作 sql脚本函数GROUP_CONCAT -GROUP_CONCAT 这个函数...
搜索
直接sql查询用sql语句like查询，复杂的用sql语句拼接。视图和存储过程查询简化程序，提高执行效率，维...
chapter22：使用视图
视图视图是虚拟的表，只包含使用时动态检索数据的查询。为什么使用视图重用SQL语句；简化复杂的SQL操作； ...
c#11月13号
上午学习使用mySQL进行表格操作，然后是数据库，command对象，SQL查询。下午练习了查询方法的使用。
SQL干货篇三
SQL干货篇三创建视图 create view <视图名>[(列名),(列名)...] as <子查询> [wi...
Mysql多表查询, 视图,事务,索引，函数，go连接数据库
1.1 今日目标理解多表查询理解子查询能够创建视图能够删除视图能够查看创建视图的SQL语句能够理解事...
DDL 数据库视图定义
创建视图根据表的查询结果创建视图创建简单视图，由一张表的查询生成的视图创建复杂视图，由多张表的查询或有函数时...