一、视图
1、什么是视图
视图是一个虚拟的表,不同于直接操作数据表,视图是依据SELECT语句来创建的。
视图不是表,视图是虚表,视图依赖于表
2、为什么会存在视图
主要有以下几点原因:
通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
通过定义视图可以使用户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段,增强数据的保密性。
通过定义视图可以降低数据的冗余。
3、如何创建视图
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS
4、如何修改视图结构
ALTER VIEW <视图名> AS
5、如何更新视图内容
对于一个视图来说,如果包含以下结构的任意一种都是不可以被更新的:
聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。
# 创建视图时尽量使用限制不允许通过视图来修改表
6、如何删除视图
DROP VIEW <视图名1> [ , <视图名2> …]
二、子查询
练习题-第一部分
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(product_name,sale_price,regist_date)
AS
SELECT product_name,sale_price,regist_date
FROM product
WHERE regist_date = "2009-9-20"
AND sale_price >= 1000;
3.2
向习题一中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果呢?
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
答:报错
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;
3.4
请根据习题一中的条件编写一条 SQL 语句,创建一幅包含如下数据的视图(名称为AvgPriceByType)。
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
提示:其中的关键是 avg_sale_price 列。与习题三不同,这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。也就是说,该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。
答:
CREATE VIEW AvgPriceByType (product_id,product_name,product_type,sale_price,avg_sale_price)
AS
SELECT product_id,product_name,product_type,sale_price,(SELECT AVG(sale_price) FROM product p2
WHERE p1.product_type = p2.product_type
GROUP BY p1.product_type) AS avg_sale_price FROM product p1;
三、各种各样的函数
函数大致分为如下几类:
算术函数 (用来进行数值计算的函数)
字符串函数 (用来进行字符串操作的函数)
日期函数 (用来进行日期操作的函数)
转换函数 (用来转换数据类型和值的函数)
聚合函数 (用来进行数据聚合的函数)
1、算数函数
ABS – 绝对值
语法:ABS( 数值 )
ABS 函数用于计算一个数字的绝对值,表示一个数到原点的距离。
当 ABS 函数的参数为NULL时,返回值也是NULL。
MOD – 求余数
语法:MOD( 被除数,除数 )
MOD 是计算除法余数(求余)的函数,是 modulo 的缩写。小数没有余数的概念,只能对整数列求余数。
注意:主流的 DBMS 都支持 MOD 函数,只有SQL Server 不支持该函数,其使用%符号来计算余数。
ROUND – 四舍五入
语法:ROUND( 对象数值,保留小数的位数 )
ROUND 函数用来进行四舍五入操作。
注意:当参数 保留小数的位数 为变量时,可能会遇到错误,请谨慎使用变量。
2、字符串函数
CONCAT – 拼接
语法:CONCAT(str1, str2, str3)
MySQL中使用 CONCAT 函数进行拼接。
LENGTH – 字符串长度
语法:LENGTH( 字符串 )
LOWER – 小写转换
LOWER 函数只能针对英文字母使用,它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合,不影响原本就是小写的字符。
类似的, UPPER 函数用于大写转换。
REPLACE – 字符串的替换
语法:REPLACE( 对象字符串,替换前的字符串,替换后的字符串 )
SUBSTRING – 字符串的截取
语法:SUBSTRING (对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算,索引值起始为1。
(扩展内容)SUBSTRING_INDEX – 字符串按索引截取
语法:SUBSTRING_INDEX (原始字符串, 分隔符,n)
该函数用来获取原始字符串按照分隔符分割后,第 n 个分隔符之前(或之后)的子字符串,支持正向和反向索引,索引起始值分别为 1 和 -1。
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 2) |
+------------------------------------------+
| www.mysql |
+------------------------------------------+
1 row in set (0.00 sec)
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', -2) |
+-------------------------------------------+
| mysql.com |
+-------------------------------------------+
1 row in set (0.00 sec)
获取第1个元素比较容易,获取第2个元素/第n个元素可以采用二次拆分的写法。
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1);
+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 1) |
+------------------------------------------+
| www |
+------------------------------------------+
1 row in set (0.00 sec)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
+--------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |
+--------------------------------------------------------------------+
| mysql |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
3、日期函数
CURRENT_DATE – 获取当前日期
CURRENT_TIME – 当前时间
CURRENT_TIMESTAMP – 当前日期和时间
EXTRACT – 截取日期元素
语法:EXTRACT(日期元素 FROM 日期)
使用 EXTRACT 函数可以截取出日期数据中的一部分,例如“年”
“月”,或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型
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)
4、转换函数
CAST – 类型转换
语法:CAST(转换前的值 AS 想要转换的数据类型)
COALESCE – 将NULL转换为其他值
语法:COALESCE(数据1,数据2,数据3……)
COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的,因此可以根据需要无限增加。
在 SQL 语句中将 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)
5、谓词
谓词就是返回值为真值的函数。包括TRUE FALSE UNKNOWN。
谓词主要有以下几个:
LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS
%是代表“零个或多个任意字符串”的特殊符号
_(下划线)来代替 %,与 % 不同的是,它代表了“任意 1 个字符”
BETWEEN谓词 – 用于范围查询
使用 BETWEEN 可以进行范围查询。该谓词与其他谓词或者函数的不同之处在于它使用了 3 个参数。
-- 选取销售单价为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)
BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值,也就是闭区间。如果不想让结果中包含临界值,那就必须使用 < 和 >。
IS NULL、 IS NOT NULL – 用于判断是否为NULL
为了选取出某些值为 NULL 的列的数据,不能使用 =,而只能使用特定的谓词IS NULL。
IN谓词 – OR的简便用法
多个查询条件取并集时可以选择使用or语句。
虽然上述方法没有问题,但还是存在一点不足之处,那就是随着希望选取的对象越来越多, SQL 语句也会越来越长,阅读起来也会越来越困难。
这时, 我们就可以使用IN 谓词`IN(值1, 值2, 值3, …)来替换上述 SQL 语句。
反之,可以使用否定形式NOT IN来实现。
需要注意的是,在使用IN 和 NOT IN 时是无法选取出NULL数据的。
NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。
使用子查询作为IN谓词的参数
IN 谓词(NOT IN 谓词)具有其他谓词所没有的用法,那就是可以使用子查询作为其参数。
EXIST 谓词
EXIST谓词的使用方法
谓词的作用就是 “判断是否存在满足某种条件的记录”。
如果存在这样的记录就返回真(TRUE),如果不存在就返回假(FALSE)。
使用NOT EXIST替换NOT IN
就像 EXIST 可以用来替换 IN 一样, NOT IN 也可以用NOT EXIST来替换。
CASE 表达式
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
上述语句执行时,依次判断 when 表达式是否为真值,是则执行 THEN 后的语句,
如果所有的 when 表达式均为假,则执行 ELSE 后的语句。无论多么庞大的 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;
+--------------+------------------+
| product_name | abc_product_type |
+--------------+------------------+
| T恤 | A :衣服 |
| 打孔器 | B :办公用品 |
| 运动T恤 | A :衣服 |
| 菜刀 | C :厨房用具 |
| 高压锅 | C :厨房用具 |
| 叉子 | C :厨房用具 |
| 擦菜板 | C :厨房用具 |
| 圆珠笔 | B :办公用品 |
+--------------+------------------+
8 rows in set (0.00 sec)
ELSE 子句也可以省略不写,这时会被默认为 ELSE NULL。但为了防止有人漏读,还是希望大家能够显示地写出 ELSE 子句。此外, CASE 表达式最后的“END”是不能省略的,请大家特别注意不要遗漏
假如要在列的方向上展示不同种类额聚合值,该如何写呢?
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)
(扩展内容)应用场景3:实现行转列
假设有如下图表的结构
计划得到如下的图表结构
聚合函数 + CASE WHEN 表达式即可实现该转换
-- CASE WHEN 实现数字列 score 行转列
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 |
+------+---------+------+---------+
2 rows in set (0.00 sec)
上述代码实现了数字列 score 的行转列,也可以实现文本列 subject 的行转列
-- 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 |
+------+---------+------+---------+
| 张三 | 语文 | 数学 | 外语 |
| 李四 | 语文 | 数学 | 外语 |
+------+---------+------+---------+
2 rows in set (0.00 sec
总结:
当待转换列为数字时,可以使用SUM AVG MAX MIN等聚合函数;
当待转换列为文本时,可以使用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);
答:
会得到商品进价不为500,2800,500的商品,但不包含null值商品
②
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
答:
空值
3.7
按照销售单价( sale_price)对练习 6.1 中的 product(商品)表中的商品进行如下分类。
低档商品:销售单价在1000日元以下(T恤衫、办公用品、叉子、擦菜板、 圆珠笔)
中档商品:销售单价在1001日元以上3000日元以下(菜刀)
高档商品:销售单价在3001日元以上(运动T恤、高压锅)
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句,结果如下所示。
执行结果
low_price | mid_price | high_price
----------+-----------+------------
5 | 1 | 2
SELECT SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS '低价格',
SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS '中价格',
SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END) AS '高价格'
FROM product;