SQL学习笔记_Aliyun3

SQL学习笔记_Aliyun3

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

视图

视图是一张虚拟的表，而是一个窗口，数据来源建立在真实的表的基础上，而他的作用主要有：

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

创建视图

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

Tips：定义视图时不能使用ORDER BY子句因为视图和表一样，数据行都是没有顺序的

Example

• 单表视图

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;  #链接product和shop_product表

修改视图

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

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

更新视图

对于更新视图有很严格的限制：

• SELECT 子句中未使用DISTINCT
• FROM 子句中只有一张表
• 未使用GROUP BY 子句
• 未使用HAVING子句
• 未使用聚合函数 SUM()、MIN()、MAX()、COUNT() 等。

-- 该视图就可以更新，因为在SELECT子句中既没有聚合也没有结合
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, '2009-11-30');

删除视图

DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

DROP VIEW ProductSum;

子查询

即嵌套查询，一般的运算顺序为，先计算子查询，子查询作为结果作为外层另一个查询的限制条件。然而随着子查询嵌套的层数叠加，执行效率会降低

子查询与视图的关联

子查询相当于创造了一个不储存的视图，该视图仅用于下一级的查询，在SELECT语句执行之后就会消失

-- 两层嵌套查询
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;

标量子查询

必须而且只能返回1行1列的结果（返回单一值得子查询）

标量子查询的书写位置在任何可以使用单一值得位置都可以使用，不论是SELECT子句，GROUP BY 子句，HAVING子句，还是ORDER BY 子句

Example

SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;

SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) > (SELECT AVG(sale_price)FROM Product);

Example

product_id | product_name | sale_price 
------------+-------------+----------
0003       | 运动T恤       | 4000 
0004       | 菜刀          | 3000 
0005       | 高压锅        | 6800

-- 查询出销售单价高于平均销售单价的商品（也就意味第一步得SELECT出平均销售单价，第二步再SELECT出高于平均值的商品）
SELECT product_id,product_name,sale_price
FROM Product
WHERE sale_price>(SELECT AVG(sale_price) FROM Product);

关联子查询

-- 查询出各个商品种类中高于该商品种类的平均销售单价的商品
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的书写顺序和执行顺序

书写顺序SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY

执行顺序 FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY

而在上面的关联查询的例子中，执行的顺序并不是先从子查询开始的（不妨想，如果先从子查询开始的话那么子查询返回了三行平均值，但是并没有一个可以关联的值用来挑选哪个一个是主查询所对应的值），而是先从主查询的Product表中需拿出一个Product_type进入子查询中得到子查询中的平均值结果（这也可以解释为什么要新另一个Product as P2），然后返回父查询，用于判断父查询的WHERE，然后重复上述操作，直到Product_type全部被匹配完。

练习3.1

-- 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。
#条件 1：销售单价大于等于 1000 日元。
#条件 2：登记日期是 2009 年 9 月 20 日。
#条件 3：包含商品名称、销售单价和登记日期三列。
CREATE VIEW ViewPractise5_1 (product_name,sale_price,regist_date)
AS
SELECT product_name,sale_price,regist_date
FROM product
WHERE regist_date='2009-09-20'
AND sale_price>1000;

练习3.2

会发生错误，因为对视图插入相当于对视图对应的表插入，而视图对应的表Product中，Product_id,product_type都是NOT NULL，因而插入中有NULL的attributes自然会报错

练习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
#为什么要用子查询，因为如果单纯是AVG是返回一个查询值，而这里是每个对应的行都要返回AVG所以用子查询重复AVG

SQL附带函数

函数大致分为如下几类：

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

算数函数

ABS – 绝对值

语法：ABS( 数值 )

ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。

当 ABS 函数的参数为NULL时，返回值也是NULL。

MOD – 求余数

语法：MOD( 被除数，除数 )

MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。

注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。

ROUND – 四舍五入

语法：ROUND( 对象数值，保留小数的位数 )

ROUND 函数用来进行四舍五入操作。

Example

CREATE TABLE SampleMath(m NUMERIC (10,3),
n INTEGER,
p INTEGER);
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);

-- ABS
SELECT m,ABS(m) AS abs_col
FROM SampleMath;

-- MOD
SELECT n, p,
MOD(n, p) AS mod_col #n为被除数，p为除数
FROM SampleMath;

-- ROUND
SELECT m, n,
ROUND(m, n) AS round_col
FROM SampleMath

字符串函数

拼接

-- 在Oracle或者DB2或者PostgreSQL中
SELECT str1, str2,
str1 | | str2 AS str_concat
FROM SampleStr;

-- 在SQL SEVER
SELECT str1, str2, str3,
str1 + str2 + str3 AS str_concat
FROM SampleStr;

-- MySQL SQL Server 2012 及之后
SELECT str1, str2, str3,
CONCAT(str1, str2, str3) AS str_concat
FROM SampleStr;

替换

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

SELECT str1, str2, str3,
REPLACE(str1, str2, str3) AS rep_str
FROM SampleStr;
-- 替换对象为“micmic”,替换前字符串为“i”，替换后的字符串为“I”,最终返回结构为“mIcmIc”

日期函数

-- 返回SQL执行的日期
CURRENT_DATE

-- 返回SQL执行的时间
CURRENT_TIME

-- 返回SQL执行的时间和日期
CURRENT_TIMESTAMP

-- 截取出日期数据中的一部分
#EXAMPLE
SELECT CURRENT_TIMESTAMP,
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;

谓词

● LIKE
● BETWEEN
● IS NULL、IS NOT NULL
● IN
● EXISTS

谓词的用法在于判断是否存在满足某种条件的记录，如果存在这样的记录就返回TRUE， 如果不存在就返回FALSE

LIKE

用于字符串的部分一致查询

%用于模式匹配 %string->搜索所有string前面包含的；%string%搜索无论前后只要包含string的；string%搜索后面包含string的

Example

SELECT *
FROM SampleLike
WHERE strcol LIKE 'ddd%';

BETWEEN

用于范围查询

SELECT product_name, sale_price
FROM Product
WHERE sale_price BETWEEN 100 AND 1000;

IN

or的简便用法；“从里面挑值”

SELECT product_name, purchase_price
FROM Product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

IS NULL | IS NOT NULL

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL

EXIST

EXIST通常都会使用关联子查询作为参数

类似于IN

-- 选取大阪店在售商品的销售单价
SELECT product_name, sale_price
FROM Product AS P 
WHERE EXISTS (SELECT *
FROM ShopProduct AS SP 
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id);

CASE表达式（条件分支）

CASE分支是一个表达式，意味着表达式可以书写在任意位置

也可以用于讲SELECT语句进行行列互换（行列转换）

CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.. .
ELSE <表达式>
END #CASE表达式中的END不能省略
#else语句可以省略

用GROUP BY进行分类加和

SELECT product_type,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type;

Result：

product_type | sum_price
--------------+----------
衣服 | 5000
办公用品 | 600
厨房用具 | 11180

转换成一行显示三列总值

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

练习3.5

NO

练习3.6

NOT IN的参数中包含NULL时结果通常会为空，也就是无法选取出任何记录。因为null不能参与比较运算符，即与任何数据比较结果都为NULL

如果null参与算术运算，则该算术表达式的值为null。（例如：+，-，，/ 加减乘除）
如果null参与比较运算，则结果可视为false。（例如：>=,<=,<> 大于，小于，不等于）
如果null参与聚集运算，则聚集函数都置为null。除count()之外。
如果在not in子查询中有null值的时候,则不会返回数据。
此时要么在子句中加入NOT NULL要么用IN语句执行

练习3.7

SELECT COUNT(CASE WHEN sale_price<1000 THEN 1 ELSE 0 ) AS low_price
COUNT(CASE WHEN sale_price>1000 AND sale_price<3000 THEN 1 ELSE 0 ) AS mid_price
COUNT(CASE WHEN sale_price>=3001 THEN 1 ELSE 0 ) AS high_price
FROM Product;

References

https://zhuanlan.zhihu.com/p/41844742?spm=5176.21852664.0.0.6ee111b7qoSYRI

https://blog.youkuaiyun.com/chinese_coder/article/details/111798707