3.1 视图
视图查询语句
SELECT stu_name FROM view_students_info;
3.1.1 什么是视图
视图是一个虚拟的表,不同于直接操作数据表,视图是依据SELECT语句来创建的(会在下面具体介绍),所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表,然后在这张虚拟表上做SQL操作。
3.1.2 视图与表有什么区别
视图与表的区别—“是否保存了实际的数据”。
3.1.3 视图的作用
-
通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
-
通过定义视图可以使用户看到的数据更加清晰。
-
通过定义视图可以不对外公开数据表全部字段,增强数据的保密性。
-
通过定义视图可以降低数据的冗余。
3.1.4 如何创建视图
创建视图的基本语法如下:
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
- 基于单表的视图
我们在product表的基础上创建一个视图,如下:
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type ;
创建的视图如下图所示:
- 基于多表的视图
我们在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 = '衣服';
查询结果为:
3.1.5 如何修改视图结构
修改视图结构的基本语法如下:
ALTER VIEW <视图名> AS <SELECT语句>
修改productSum视图
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 子句中包含多个表。
更新视图
更新productSum视图
UPDATE productsum
SET sale_price = '5000'
WHERE product_type = '办公用品';
此时我们再查看productSum视图,可以发现数据已经更新了
注意:虽然可以修改,但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表
3.1.7 如何删除视图
删除视图的基本语法如下:
DROP VIEW <视图名1> [ , <视图名2> …]
注意:需要有相应的权限才能成功删除。
我们删除刚才创建的productSum视图:
DROP VIEW productSum;
3.2 子查询
3.2.1 什么是子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。
3.2.2 子查询和视图的关系
子查询就是将用来定义视图的 SELECT
语句直接用于 FROM
子句当中。其中AS studentSum
可以看作是子查询的名称,而且由于子查询是一次性的,所以子查询不会像视图那样保存在存储介质中, 而是在 SELECT
语句执行之后就消失了。
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;
虽然嵌套子查询可以查询出结果,但是随着子查询嵌套的层数的叠加,SQL语句不仅会难以理解而且执行效率也会很差,所以要尽量避免这样的使用。
3.2.4 标量子查询
所谓单一就是要求我们执行的SQL语句只能返回一个值,也就是要返回表中具体的某一行的某一列。例如我们有下面这样一张表
product_id | product_name | sale_price |
---|---|---|
0003 | 运动T恤 | 4000 |
0004 | 菜刀 | 3000 |
0005 | 高压锅 | 6800 |
那么我们执行一次标量子查询后是要返回类似于,“0004”,“菜刀”这样的结果。
3.2.5 标量子查询的作用
让我们看几个具体的需求:
- 查询出销售单价高于平均销售单价的商品
- 查询出注册日期最晚的那个商品
让我们看如何通过标量子查询语句查询出销售单价高于平均销售单价的商品。
SELECT product_id, product_name, sale_price
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);
由于标量子查询的特性, 能够使用常数或者列名的地方,无论是 SELECT 子句、GROUP BY 子句、HAVING 子句,还是 ORDER BY 子句,几乎所有的地方都可以使用。
我们还可以这样使用标量子查询:
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM product) AS avg_price
FROM product;
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);
在第二条SQL语句也就是关联子查询中我们将外面的product表标记为p1,将内部的product设置为p2,而且通过WHERE语句连接了两个查询。
3.3 各种各样的函数
函数大致分为如下几类:
- 算术函数 (用来进行数值计算的函数)
- 字符串函数 (用来进行字符串操作的函数)
- 日期函数 (用来进行日期操作的函数)
- 转换函数 (用来转换数据类型和值的函数)
- 聚合函数 (用来进行数据聚合的函数)
3.3.1 算数函数
-
ABS – 绝对值
语法:ABS( 数值 )
-
MOD – 求余数
语法:MOD( 被除数,除数 )
-
ROUND – 四舍五入
语法:ROUND( 对象数值,保留小数的位数 )
3.3.2 字符串函数
-
CONCAT – 拼接
语法:CONCAT(str1, str2, str3)
MySQL中使用 CONCAT 函数进行拼接。 -
LENGTH – 字符串长度
语法:LENGTH( 字符串 )
-
LOWER – 小写转换
类似的, UPPER 函数用于大写转换。 -
REPLACE – 字符串的替换
语法:REPLACE( 对象字符串,替换前的字符串,替换后的字符串 )
-
SUBSTRING – 字符串的截取
语法:SUBSTRING (对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
实例:
3.3.3 日期函数
- CURRENT_DATE – 获取当前日期
- CURRENT_TIME – 当前时间
- 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;
+---------------------+------+-------+------+------+--------+--------+
| 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.3.4 转换函数
- CAST – 类型转换
语法:CAST(转换前的值 AS 想要转换的数据类型)
-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
+---------+
| int_col |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;
+------------+
| date_col |
+------------+
| 2009-12-14 |
+------------+
1 row in set (0.00 sec)
- COALESCE – 将NULL转换为其他值
语法:COALESCE(数据1,数据2,数据3……)
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)
3.4 谓词
3.4.1 什么是谓词
谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
谓词主要有以下几个:
- LIKE
- BETWEEN
- IS NULL、IS NOT NULL
- IN
- EXISTS
3.4.2 LIKE谓词 – 用于查询有相同内容的字符串
部分一致大体可以分为前方一致、中间一致和后方一致三种类型。
- 前方一致:选取出“dddabc”
SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
+--------+
| strcol |
+--------+
| dddabc |
+--------+
1 row in set (0.00 sec)
- 中间一致:选取出“abcddd”“dddabc”“abdddc”
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
+--------+
| strcol |
+--------+
| abcddd |
| abdddc |
| dddabc |
+--------+
3 rows in set (0.00 sec)
- 后方一致:选取出“abcddd“
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';
+--------+
| strcol |
+--------+
| abcddd |
+--------+
1 row in set (0.00 sec)
- _下划线匹配任意 1 个字符
使用 _(下划线)来代替 %,与 % 不同的是,它代表了“任意 1 个字符”。
SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';
+--------+
| strcol |
+--------+
| abcdd |
+--------+
1 row in set (0.00 sec)
3.4.3 BETWEEN谓词 – 用于范围查询
使用 BETWEEN
可以进行范围查询。
-- 选取销售单价为100~ 1000元的商品
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| T恤 | 1000 |
| 打孔器 | 500 |
| 叉子 | 500 |
| 擦菜板 | 880 |
| 圆珠笔 | 100 |
+--------------+------------+
5 rows in set (0.00 sec)
3.4.4 IS NULL、 IS NOT NULL – 用于判断是否为NULL
为了选取出某些值为 NULL 的列的数据,不能使用 =,而只能使用特定的谓词IS NULL
。
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 叉子 | NULL |
| 圆珠笔 | NULL |
+--------------+----------------+
2 rows in set (0.00 sec)
与此相反,想要选取 NULL 以外的数据时,需要使用IS NOT NULL。
3.4.5 IN谓词 – OR的简便用法
- 多个查询条件取并集时可以选择使用or语句。
-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤 | 500 |
| 打孔器 | 320 |
| 高压锅 | 5000 |
+--------------+----------------+
3 rows in set (0.00 sec)
- 也可以使用IN 谓词 `IN(值1, 值2, 值3, …)来替换上述 SQL 语句。
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤 | 500 |
| 打孔器 | 320 |
| 高压锅 | 5000 |
+--------------+----------------+
3 rows in set (0.00 sec)
上述语句简洁了很多,可读性大幅提高。
- 反之,希望选取出“进货单价不是 320 元、 500 元、 5000 元”的商品时,可以使用否定形式NOT IN来实现。
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 运动T恤 | 2800 |
| 菜刀 | 2800 |
| 擦菜板 | 790 |
+--------------+----------------+
3 rows in set (0.00 sec)
3.4.6 使用子查询作为IN谓词的参数
- IN和子查询
-- step2:取出大阪门店在售商品的销售单价 `sale_price`
SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
FROM shopproduct
WHERE shop_id = '000C');
- NOT IN和子查询
-- NOT IN 使用子查询作为参数,取出未在大阪门店销售的商品的销售单价
SELECT product_name, sale_price
FROM product
WHERE product_id NOT IN (SELECT product_id
FROM shopproduct
WHERE shop_id = '000A');
3.4.7 EXIST 谓词
EXIST
:当“存在”满足子查询中指定条件的记录时返回真(TRUE)
-- 使用 EXIST 选取出大阪门店在售商品的销售单价。
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);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤 | 4000 |
| 菜刀 | 3000 |
| 叉子 | 500 |
| 擦菜板 | 880 |
+--------------+------------+
4 rows in set (0.00 sec)
NOT EXIST
:当“不存在”满足子查询中指定条件的记录时返回真(TRUE)。
-- 不在大阪门店销售的商品的销售单价。
SELECT product_name, sale_price
FROM product AS p
WHERE NOT EXISTS (SELECT *
FROM shopproduct AS sp
WHERE sp.shop_id = '000A'
AND sp.product_id = p.product_id);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 菜刀 | 3000 |
| 高压锅 | 6800 |
| 叉子 | 500 |
| 擦菜板 | 880 |
| 圆珠笔 | 100 |
+--------------+------------+
5 rows in set (0.00 sec)
3.5 CASE 表达式
3.5.1 CASE 表达式语法
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
3.5.2 CASE表达式的使用方法
- 应用场景1:根据不同分支得到不同列值
假设现在 要实现如下结果:
A :衣服
B :办公用品
C :厨房用具
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;
+--------------+------------------+
| product_name | abc_product_type |
+--------------+------------------+
| T恤 | A : 衣服 |
| 打孔器 | B : 办公用品 |
| 运动T恤 | A : 衣服 |
| 菜刀 | C : 厨房用具 |
| 高压锅 | C : 厨房用具 |
| 叉子 | C : 厨房用具 |
| 擦菜板 | C : 厨房用具 |
| 圆珠笔 | B : 办公用品 |
+--------------+------------------+
8 rows in set (0.00 sec)
- 应用场景2:实现列方向上的聚合
通常我们使用如下代码实现行的方向上不同种类的聚合(这里是 sum)
SELECT product_type,
SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type;
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| 衣服 | 5000 |
| 办公用品 | 600 |
| 厨房用具 | 11180 |
+--------------+-----------+
3 rows in set (0.00 sec)
假如要在列的方向上展示不同种类额聚合值,该如何写呢?
sum_price_clothes | sum_price_kitchen | sum_price_office
------------------+-------------------+-----------------
5000 | 11180 | 600
聚合函数 + CASE WHEN 表达式即可实现该效果
-- 对按照商品种类计算出的销售单价合计值进行行列转换
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)