第三章复杂一点的查询

Autumn—snow

已于 2022-05-23 00:31:35 修改

阅读量151

点赞数

于 2022-05-23 00:30:13 首次发布

本文链接：https://blog.csdn.net/Autumn_snow/article/details/124918775

版权

sql

第三章复杂一点的查询

3.1 视图

3.1.1 什么是视图

视图是一个虚拟的表
操作视图时会根据创建视图的SELECT语句生成一张虚拟表

3.1.2 视图与表有什么区别

请添加图片描述

3.1.3 为什么会存在视图

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

3.1.4 如何创建视图

创建视图的基本语法如下：

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

请添加图片描述

注意事项：需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句
基于单表的视图

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

基于多表的视图

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
  FROM product,
       shop_product
 WHERE product.product_id = shop_product.product_id;

3.1.5 如何修改视图结构

修改视图结构的基本语法如下：

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

修改视图

ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';

3.1.6 如何更新视图内容

包含以下结构的任意一种都是不可以被更新的：
- 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
- DISTINCT 关键字。
- GROUP BY 子句。
- HAVING 子句。
- UNION 或 UNION ALL 运算符。
- FROM 子句中包含多个表。
更新视图

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

3.1.7 如何删除视图

删除视图的基本语法如下：

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

3.2 子查询

3.2.1 什么是子查询

3.2.2 子查询和视图的关系

3.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;

3.2.4 标量子查询

3.2.5 标量子查询有什么用

3.2.6 关联子查询

什么是关联子查询

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);

关联子查询与子查询的联系

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);

3.3 各种各样的函数

3.3.1 算数函数（用来进行数值计算的函数）

ABS – 绝对值

ABS( 数值 )

MOD – 求余数

MOD( 被除数，除数 )

ROUND – 四舍五入

ROUND( 对象数值，保留小数的位数 )
注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。

3.3.2 字符串函数（用来进行字符串操作的函数）

CONCAT – 拼接

CONCAT(str1, str2, str3)

LENGTH – 字符串长度

LENGTH( 字符串 )

LOWER – 小写转换
REPLACE – 字符串的替换

REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

SUBSTRING – 字符串的截取

SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

3.3.3 日期函数（用来进行日期操作的函数）

CURRENT_DATE – 获取当前日期
CURRENT_TIME – 当前时间
CURRENT_TIMESTAMP – 当前日期和时间
EXTRACT – 截取日期元素：EXTRACT(日期元素 FROM 日期)

3.3.4 转换函数（用来转换数据类型和值的函数）

CAST – 类型转换：CAST（转换前的值 AS 想要转换的数据类型）
COALESCE – 将NULL转换为其他值：COALESCE(数据1，数据2，数据3……)

3.3.5 聚合函数（用来进行数据聚合的函数）

3.4 谓词

3.4.1 什么是谓词

谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。

谓词主要有以下几个：
- LIKE
- BETWEEN
- IS NULL、IS NOT NULL
- IN
- EXISTS

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

%是代表“零个或多个任意字符串”的特殊符号
_下划线匹配任意 1 个字符

3.4.3 BETWEEN谓词 – 用于范围查询

BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间

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

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。
想要选取 NULL 以外的数据时，需要使用IS NOT NULL。

3.4.5 IN谓词 – OR的简便用法

多个查询条件取并集时可以选择使用or语句。

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

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

# 子查询展开
SELECT product_name, sale_price
FROM product
WHERE product_id IN ('0003', '0004', '0006', '0007');

3.4.7 EXIST 谓词

3.5 CASE 表达式

3.5.1 什么是 CASE 表达式？

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

3.5.2 CASE表达式的使用方法

-- 对按照商品种类计算出的销售单价合计值进行行列转换
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;
+-------------------+-------------------+------------------+
| sum_price_clothes | sum_price_kitchen | sum_price_office |
+-------------------+-------------------+------------------+
|              5000 |             11180 |              600 |
+-------------------+-------------------+------------------+
1 row in set (0.00 sec)

Autumn—snow

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
第三章复杂一点的查询

第三章复杂一点的查询3.1 视图3.1.1 什么是视图视图是一个虚拟的表操作视图时会根据创建视图的SELECT语句生成一张虚拟表3.1.2 视图与表有什么区别3.1.3 为什么会存在视图通过定义视图可以将频繁使用的SELECT语句保存以提高效率。通过定义视图可以使用户看到的数据更加清晰。通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。通过定义视图可以降低数据的冗余。3.1.4 如何创建视图创建视图的基本语法如下：CREATE VIEW <视图名称&
复制链接

扫一扫