开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> DataWhale SQL TASK3 -> 正文阅读

[大数据]DataWhale SQL TASK3

第3章复杂一点的查询

摘要:主要讲解了视图、子查询、函数、谓词、CASE等。CASE注意与C语言等区分，CASE WHEN开头，第二个开始就不需要CASE只写WHEN 即可，有ELSE 有END。EXISTS有S，关联查询、子查询还不是很熟悉。

3.1 视图

“视图不是表，视图是虚表，视图依赖于表”
视图的作用
1. 保存频繁使用的SELECT语句，来提高效率
2. 可视化
3. 保密性
4. 降低数据的冗余
视图跟表一样的操作都是SELECT语句，可以在视图上面创建视图，但是应该避免，会降低SQL的效率
需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。下面这样定义视图是错误的。

/*
创建视图的语法
CREATE VIEW view_name(column_name1,column_name2……)
AS
SELECT 语句

*/

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
 ORDER BY product_type;
 
 /*
 查看视图的字段信息：DESCRIBE view_name
 查看视图的详细信息：SHOW CREATE VIEW view_name;
 查看视图的具体信息:SELECT * FROM view_name
 */

为什么不能使用 ORDER BY 子句呢？这是因为视图和表一样，数据行都是没有顺序的。
在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。
我们在product表和shop_product表的基础上创建视图。

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;

我们可以在这个视图的基础上进行查询

SELECT sale_price, shop_name
  FROM view_shop_product
 WHERE product_type = '衣服';

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

ALTER VIEW <视图名> AS <SELECT语句> -- 我感觉修改视图跟新建差不多，只是CREATE与ALTER的区别

其中视图名在数据库中需要是唯一的，不能与其他视图和表重名。
当然也可以通过将当前视图删除然后重新创建的方式达到修改的效果。（对于数据库底层是不是也是这样操作的呢，你可以自己探索一下。）
修改视图

我们修改上方的productSum视图为

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

更新视图
1. 包含下列结构的视图是不可以被更新的：
  1. 聚合函数，SUM等
  2. DISTINCT
  3. GROUP BY
  4. HAVING
  5. UNION 、UNION ALL
  6. FROM子句包含多个表
2. 更新视图
  1. ```
  UPDATE productsum 
  /*
  新视图的关键字是UPDATE view_name只有一个关键字，
  更新表格一个只有一个关键字UPDATE table_name
  */
  	SET sale_price="5000"
  WHERE product_type="办公用品";
```
2. 视图只是表的一个窗口，所以修改视图与只能修改透过窗口能看到的内容，我们在创建视图时也尽量使用限制不允许通过视图来修改表
删除视图
1. DROP VIEW productSum;关键字是两个DROP VIEW

3.2 子查询

子查询就是查询嵌套，这个语句看起来很好理解，其中使用括号括起来的sql语句首先执行，执行成功后再执行外面的sql语句

SELECT stu_name
FROM (
         SELECT stu_name, COUNT(*) AS stu_cnt 
           FROM students_info
          GROUP BY stu_age) AS studentSum;-- AS studentSum可以看做子查询的名称

标量子查询，就是某个单元格查询
关联子查询

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);
 /*
 在第二条SQL语句也就是关联子查询中我们将外面的product表标记为p1，
 将内部的product设置为p2，而且通过WHERE语句连接了两个查询。
 */

SQL执行顺序
1. FROM->WHERE-> GROUP BY-> HAVING ->SELECT ->ORDER BY
关联子查询的执行顺序不同于正常SQL的执行顺序，是先执行主查询，从主查询选一个数据，送入子查询，再回到主查询。选取的数据是子查询WHERE的条件
总结，待更新……
1. 如果是多了一列一个聚合函数就能搞定的数据
  1. 那么，在原来属于SELECT 列的位置写上一个完整的SLECT FROM 语句并且用小括号括起来，而后加上AS 新列名即可
2. 如果是多了一列明显需要GROUP BY 的数据
  1. 那么，关联子查询
3. 如果是选取部分数据，高于聚合函数的结果
  1. 那么，子查询写在WHERE语句中，小括号括起来
4. 关联子查询，数据是全部，嵌套子查询数据是不完整的

练习题

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 AND regist_date="2009-09-20";
  SELECT * FROM ViewPractice5_1;

3.2

向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果？为什么？

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

答:报错，有聚合函数的视图，不能通过试图修改原表格

3.3

请根据如下结果编写 SELECT 语句，其中 sale_price_avg 列为全部商品的平均销售单价。

答:

SELECT product_id
		,product_name
		,product_type
        ,sale_price
        ,(SELECT AVG(sale_price) FROM product) AS sale_price_avg
FROM product

3.4

请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。

提示：其中的关键是 sale_price_avg_type 列。与习题三不同，这里需要计算出的是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

答：不会

3.3 各种各样的函数

算术函数
1. 加减乘除
2. ABS – 绝对值
  1. ? 语法：ABS( 数值 )
  2. ? ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离
  3. 当 ABS 函数的参数为NULL时，返回值也是NULL。
3. MOD – 求余数
  1. 语法：MOD( 被除数，除数 )
  2. MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
  3. 注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。
4. ROUND – 四舍五入
  1. 语法：ROUND( 对象数值，保留小数的位数 )
  2. ROUND 函数用来进行四舍五入操作。
  3. 注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。
字符串函数
1. CONCAT – 拼接
  1. 语法：CONCAT(str1, str2, str3)
  2. MySQL中使用 CONCAT 函数进行拼接
2. LENGTH – 字符串长度
  1. 语法：LENGTH( 字符串 )
3. LOWER – 小写转换
  1. LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。
4. UPPER 函数用于大写转换
5. REPLACE – 字符串的替换
  1. 语法：REPLACE( 对象字符串，替换字符串，替换为的字符串 )
6. SUBSTRING – 字符串的截取
  1. 语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
    1. 从1开始计数
  2. 使用 SUBSTRING 函数可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。
7. 语法：SUBSTRING_INDEX (原始字符串，分隔符，n)
  1. 该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1
8. REPEAT
  1. 语法：REPEAT(string ,number)
    1. 字符串重复输出
日期函数
1. CURRENT_DATE 获取当前日期
  1. SELECT CURRENT_DATE;
2. CURRENT_TIME 当前时间
3. CURRENT_TIMESTAMP 当前日期和时间
4. EXTRACT 截取日期元素
  1. EXTRACT(日期元素 FROM 日期)
  2. EXTRACT(YEAR FROM CURRENT_TIMESTAMP ) AS year
转换函数，转换数据类型(CAST)和值
1. CAST类型转换
  1. CAST(转换前 AS 想要转换的类型)
  2. SELECT CAST(“001” AS SIGNED INTEGER) AS int_col;
  3. SELECT CAST(“2009-12-14” AS DATE) AS date_col;
2. COALESCE 将NULL转换为其他值
  1. COALESCE(参数1，参数2)，当参数1为NULL时，返回参数2，参数2为NULL时，返回参数3……
聚合函数

3.4 谓词

LIKE，字符串模糊查找
1. WHERE strcol LIKE “ddd%”
2. %表示0个或者任意多个字符串，可以放在字符串开头、结尾
3. 下划线_表示一个任意字符
BETWEEN
1. WHERE sale_price BETWEEN 10 AND 100
2. BETWEEN 是闭区间，如果想要不包含边界，使用<和>
IS NULL 和IS NOT NULL，判断是否为NULL，不能使用=
IN、NOT IN
1. IN 可以简化OR的使用
2. NOT IN (300)，IN(300,200,100)
  1. 但是貌似只能是等于和不等于两种，不能替代大于小于这种
  2. 不能选取NULL的，只能使用IS NULL ,IS NOT NULL
3. 使用子查询比使用IN好，因为不需要经常更新SQL语句
EXISTS 、NOT EXISTS
1. EXISTS可以用IN 和NOT IN进行替代
2. 只需要一个参数，一般是一个子查询
3. 大家可以把在 EXISTS的子查询中书写 SELECT * 当作 SQL 的一种习惯。

3.5 CASE表达式

CASE WHEN <求值表达式> THEN <表达式>
	 WHEN <求值表达式> THEN <表达式>
	 WHEN <求值表达式> THEN <表达式>
ELSE <表达式> -- 可以不写，不写默认是ELSE NULL，建议写
END -- 不写报错

-- 列方向上聚合
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;

行列互转需要聚合函数+CASE语句
1. 值，值聚合函数
2. 文本，MAX MIN

练习题

判断题

3.5

运算中含有 NULL 时，运算结果是否必然会变为NULL ？

答：不是，看具体情况，函数等等

3.6

对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

①

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);

选出purchase_price不是(500, 2800, 5000)的

②

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

空

3.7

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

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

请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

执行结果

low_price | mid_price | high_price
----------+-----------+------------
        5 |         1 |

 SELECT 
COUNT(CASE WHEN sale_price <= 1000 THEN 1 ELSE NULL END) AS low_price,
COUNT(CASE WHEN sale_price>=1001 AND sale_price <= 3000 THEN 1 ELSE NULL END) AS mid_price ,
COUNT(CASE WHEN sale_price >=3001 THEN 1 ELSE NULL END) AS high_price
 FROM product;