Task03:复杂一点的查询
1. 视图
SELECT stu_name FROM view_students_info;
单从表面上看起来这个语句是和正常的从数据表中查询数据是完全相同的,但其实我们操作的是一个视图。
所以从SQL的角度来说操作视图与操作表看起来是完全相同的,那么为什么还会有视图的存在呢?视图到底是什么?视图与表有什么不同呢?🙏
1.1 什么是视图
MySQL 5.0 + 开始支持视图。视图是一个虚拟的表,不存放任何数据。
与数据表不同点在于,视图是依据 SELECT 语句来创建的(会在下面具体介绍),所以操作视图时会根据创建视图的 SELECT 语句生成一张虚拟表,然后在这张虚拟表上做SQL操作。就像是站在不同角度去看同一张表。
1.2 视图与表有什么区别
《sql基础教程第2版》用一句话非常凝练的概括了视图与表的区别——“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表,它可以看作是一个窗口,通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质,即**视图是基于真实表的一张虚拟的表,其数据来源均建立在真实表的基础上。**不能对视图创建触发器,也不能使用 DROP TABLE 删除视图。
👻 注意啊:对视图进行增删改查,会影响原表数据。
1.3 为什么使用视图
- 重复利用SQL语句:将频繁使用的 SELECT 语句保存为视图,这能提高效率。
- 简化SQL操作:重用SQL后,不需要知道视图的细节,可以使用户看到的数据更加清晰。
- 保护数据:视图可以不对外公开数据表全部字段,增强数据的保密性。
- 视图可以降低数据的冗余。
1.4 如何创建视图
CREATE
VIEW view_name [(column_list)]
AS select_statement
SELECT 语句中列排列顺序和视图中列排列顺序相同,列名可以不同。
一个视图属于一个数据库的,要在给定的数据库中显式创建视图时,视图名需要这样写 db_name.view_name
视图名在数据库中需要是唯一的,不能与其他视图和表重名。
-
多重视图会降低 SQL 的性能:虽然在视图上继续创建视图的语法没有错误,但是我们还是应该尽量避免这种操作。
在 [1] 的第 22.1.1 节中::由于视图不包含数据,每次使用视图时,DBMS都会处理查询执行时所需的任一个检索。如果有多个联结和过滤创建了复杂的视图或嵌套了视图,可能会性能下降很厉害。有大量视图时,需要做测试。
-
定义视图时不能使用 ORDER BY 语句。下面这样定义视图是错误的,但在MySQL中可以执行。
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM tbl_product
GROUP BY product_type
ORDER BY product_type;
为什么不能使用 ORDER BY 子句呢?这是因为视图和表一样,数据行都是没有顺序的。看不懂什么意思?? 😩
在 [1] 的第 22.1.2 节中:ORDER BY 可以用在视图中,但如果从该视图检索数据SELECT中也含有 ORDER BY,那么该视图中的ORDER BY 将被覆盖。
- 视图可以和表一起使用。例如,编写一条联结表和视图的SELECT语句。
基于单表的视图
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM tbl_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_type | sale_price | shop_name |
+-----------------+------------+----------------+
| clothes | 1000 | Beijing |
| Office supplies | 500 | Shanghai |
| clothes | 4000 | Guangzhou |
| Office supplies | 500 | Hebei |
| clothes | 4000 | Henan |
| kitchenware | 3000 | Szechwan |
| kitchenware | 500 | Inner Mongolia |
| kitchenware | 880 | Shanxi |
| clothes | 4000 | Shandong |
| kitchenware | 3000 | Jiangsu |
| kitchenware | 500 | Jiangxi |
| kitchenware | 880 | Shaanxi |
| clothes | 1000 | Liaoning |
+-----------------+------------+----------------+
/*
查看视图
SHOW CREATE VIEW view_shop_product\G
*************************** 1. row ***************************
View: view_shop_product
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_shop_product` (`product_type`,`sale_price`,`shop_name`) AS select `tbl_product`.`product_type` AS `product_type`,`tbl_produc
character_set_client: latin1
collation_connection: latin1_swedish_ci
试试,在视图上进行DQL。
# 查询 每个商品类别的最大销售价格,按销售价格降序排列。
SELECT product_type, MAX(sale_price) AS max_sale_price
FROM view_shop_product
GROUP BY product_type
ORDER BY MAX(sale_price) DESC;
+-----------------+----------------+
| product_type | max_sale_price |
+-----------------+----------------+
| clothes | 4000 |
| kitchenware | 3000 |
| Office supplies | 500 |
+-----------------+----------------+
1.5 如何修改视图结构
ALTER
VIEW view_name [(column_list)]
AS select_statement
也可以通过将当前视图删除然后重新创建的方式达到修改的效果。(对于数据库底层是不是也是这样操作的呢,你可以自己探索一下。)
ALTER VIEW productsum
AS
SELECT product_type, sale_price
FROM tbl_product
WHERE register_date > '2009-09-11';
SELECT * FROM productsum;
+-----------------+------------+
| product_type | sale_price |
+-----------------+------------+
| clothes | 1000 |
| kitchenware | 3000 |
| kitchenware | 500 |
| Office supplies | 100 |
+-----------------+------------+
1.6 如何更新视图内容
因为视图是一个虚拟表,所以在修改时只有满足底层基本表的定义才能成功修改。对于一个视图来说,如果包含以下结构的任意一种都是不可以被更新的:
- 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
- DISTINCT 关键字。
- GROUP BY 子句。
- HAVING 子句。
- UNION 或 UNION ALL 运算符。
- FROM 子句中包含多个表。
视图归根结底还是从表派生出来的,所以对原表的增删改查,也会影响视图;同样,对视图增删改查,也会影响原表。
# 将 productsum 视图中 clothes 的 sale_price 改为 2222
UPDATE productsum
SET sale_price=2222
WHERE product_type='clothes';
# productsum 视图数据已经更新
mysql> select * from productsum;
+-----------------+------------+
| product_type | sale_price |
+-----------------+------------+
| clothes | 2222 |
| kitchenware | 3000 |
| kitchenware | 500 |
| Office supplies | 100 |
+-----------------+------------+
# 原表也可以发现数据也被更新了
mysql> SELECT * FROM tbl_product;
+------------+-----------------+-----------------+------------+----------------+---------------+
| product_id | product_name | product_type | sale_price | purchase_price | register_date |
+------------+-----------------+-----------------+------------+----------------+---------------+
| 0001 | T-shirt | clothes | 2222 | 500 | 2009-09-20 |
| 0002 | puncher | Office supplies | 500 | 320 | 2009-09-11 |
| 0003 | sports T-shirt | clothes | 4000 | 2800 | NULL |
| 0004 | kitchen knife | kitchenware | 3000 | 2800 | 2009-09-20 |
| 0005 | pressure cooker | kitchenware | 6800 | 5000 | 2009-01-15 |
| 0006 | fork | kitchenware | 500 | NULL | 2009-09-20 |
| 0007 | grater | kitchenware | 880 | 790 | 2008-04-28 |
| 0008 | ballpoint | Office supplies | 100 | NULL | 2009-11-11 |
+------------+-----------------+-----------------+------------+----------------+---------------+
不知道大家看到这个结果会不会有疑问,刚才修改视图的时候是设置 product_type=‘clothes’ 的商品的 sale_price=2222,为什么原表中只有修改了一条记录?因为视图只是原表的一个窗口,所以它只能修改透过窗口看到的内容。
注意:这里虽然修改成功了,但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表
1.7 如何删除视图
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
需要有相应的权限才能成功删除。
# 删除视图 productsum
mysql> DROP VIEW productsum;
Query OK, 0 rows affected (0.01 sec)
2. 子查询
直观上,就是前一个SELECT语句查询的结果,可以当一个临时表,继续嵌套后一个SELECT语句进行查询。这不就是套娃么??[doge]
SELECT stu_name
FROM (
SELECT stu_name, COUNT(*) AS stu_cnt
FROM students_info
GROUP BY stu_age) AS studentSum;
2.1 什么是子查询
MySQL 4.1 + 开始引入子查询。子查询指一个查询语句嵌套在另一个查询语句内部的查询,在 SELECT 子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或多个表。
2.2 子查询和视图的关系
-
子查询中 FROM 子句当中,就是定义视图的 SELECT 语句。其中AS studentSum可以看作是子查询的名称
-
子查询是一次性的,所以子查询不会像视图永久存储, 而是在 SELECT 语句执行之后就消失了。
2.3 嵌套子查询
SELECT product_type
FROM
(SELECT *
FROM
(SELECT product_type, COUNT(*) AS cnt_product
FROM tbl_product
GROUP BY product_type
) AS productsum1
WHERE cnt_product > 3
) AS productsum2;
+--------------+
| product_type |
+--------------+
| kitchenware |
+--------------+
最内层的子查询我们将其命名为 productsum1,根据 product_type 分组并查询个数,结果为:
+-----------------+-------------+
| product_type | cnt_product |
+-----------------+-------------+
| clothes | 2 |
| Office supplies | 2 |
| kitchenware | 4 |
+-----------------+-------------+
第二层查询中 cnt_product 大于 3 的商品查询出来,结果为:
+--------------+-------------+
| product_type | cnt_product |
+--------------+-------------+
| kitchenware | 4 |
+--------------+-------------+
最外层查询 product_type 一列信息。
- 虽然嵌套子查询可以查询出结果,但是随着子查询嵌套的层数的叠加,SQL语句不仅会难以理解而且执行效率也会很差,所以要尽量避免这样的使用。
2.4 标量子查询
标量就是单一的意思,那么标量子查询也就是单一的子查询,那什么叫做单一的子查询呢?
所谓单一就是要求我们执行的SQL语句只能返回一个值,也就是要返回表中具体的某一行的某一列。例如我们有下面这样一张表
+------------+-----------------+--------------+------------+----------------+
| product_id | product_name | product_type | sale_price | purchase_price |
+------------+-----------------+--------------+------------+----------------+
| 0003 | sports T-shirt | clothes | 4000 | 2800 |
| 0004 | kitchen knife | kitchenware | 3000 | 2800 |
| 0005 | pressure cooker | kitchenware | 6800 | 5000 |
+------------+-----------------+--------------+------------+----------------+
那么我们执行一次标量子查询后是要返回类似于,“0004”,“菜刀”这样的结果。
2.5 标量子查询有什么用
直接这样想可能会有些困难,让我们看几个具体的需求:
- 查询出 销售单价 高于 平均销售单价 的商品
- 查询出 注册日期 最晚的那个商品
# 查询出 销售单价 高于 平均销售单价 的商品
SELECT product_id, product_name, product_type, sale_price, purchase_price
FROM tbl_product
WHERE sale_price > (SELECT AVG(sale_price) FROM tbl_product); # 平均销售单价是 2250.2500
+------------+-----------------+--------------+------------+----------------+
| product_id | product_name | product_type | sale_price | purchase_price |
+------------+-----------------+--------------+------------+----------------+
| 0003 | sports T-shirt | clothes | 4000 | 2800 |
| 0004 | kitchen knife | kitchenware | 3000 | 2800 |
| 0005 | pressure cooker | kitchenware | 6800 | 5000 |
+------------+-----------------+--------------+------------+----------------+
# 查询出 注册日期 最晚的那个商品
SELECT product_id, product_name, product_type, sale_price, purchase_price, register_date
FROM tbl_product
WHERE register_date = (SELECT MAX(register_date) FROM tbl_product);
+------------+--------------+-----------------+------------+----------------+---------------+
| product_id | product_name | product_type | sale_price | purchase_price | register_date |
+------------+--------------+-----------------+------------+----------------+---------------+
| 0008 | ballpoint | Office supplies | 100 | NULL | 2009-11-11 |
+------------+--------------+-----------------+------------+----------------+---------------+
标量子查询只查出来一个值,这样标量子查询不仅仅局限于 WHERE 子句中,通常任何可以使用单一值的位置都可以使用。
也就是说, 能够使用常数或者列名的地方,无论是 SELECT 子句、GROUP BY 子句、HAVING 子句,还是 ORDER BY 子句,几乎所有的地方都可以使用。
# 在查询结果后面添加一列(计算出 平均销售价格)
SELECT
product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM tbl_product
) AS avg_price
FROM tbl_product;
+------------+-----------------+------------+-----------+
| product_id | product_name | sale_price | avg_price |
+------------+-----------------+------------+-----------+
| 0001 | T-shirt | 2222 | 2250.2500 |
| 0002 | puncher | 500 | 2250.2500 |
| 0003 | sports T-shirt | 4000 | 2250.2500 |
| 0004 | kitchen knife | 3000 | 2250.2500 |
| 0005 | pressure cooker | 6800 | 2250.2500 |
| 0006 | fork | 500 | 2250.2500 |
| 0007 | grater | 880 | 2250.2500 |
| 0008 | ballpoint | 100 | 2250.2500 |
+------------+-----------------+------------+-----------+
2.6 关联子查询
什么是关联子查询
关联子查询既然包含关联两个字那么一定意味着查询与子查询之间存在着联系。这种联系是如何建立起来的呢?先看一个例子:
# 查询 每个商品类别中大于该类别的 平均销售价格 的所有商品
SELECT
product_type,
product_name,
sale_price,
FROM tbl_product AS p1
WHERE sale_price > (SELECT AVG(sale_price)
FROM tbl_product AS p2
WHERE p1.product_type = p2.product_type
GROUP BY product_type);
+-----------------+-----------------+------------+
| product_type | product_name | sale_price |
+-----------------+-----------------+------------+
| Office supplies | puncher | 500 |
| clothes | sports T-shirt | 4000 |
| kitchenware | kitchen knife | 3000 |
| kitchenware | pressure cooker | 6800 |
+-----------------+-----------------+------------+
通过上面的例子我们大概可以猜到吗,关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的,接下来我们就一起看一下关联子查询的具体内容吧。
关联子查询与子查询的联系
# 标量子查询 查询销售单价高于平均销售单价的商品
SELECT product_id, product_name, sale_price
FROM tbl_product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);
# 关联子查询 选取出各商品种类中高于该商品种类的 平均销售单价 的商品
SELECT product_type, product_name, sale_price
FROM tbl_product AS p1
WHERE sale_price > (SELECT AVG(sale_price)
FROM tbl_product AS p2
WHERE p1.product_type = p2.product_type
GROUP BY product_type);
关联子查询:将外面的 tbl_product 表标记为p1,将内部的 tbl_product 设置为p2,而且通过 WHERE 语句连接了两个查询。
这里有一个博客讲的比较清楚。关联子查询简要的概括为:
- 首先执行不带 WHERE 的主查询
- 根据主查询结果匹配 product_type,获取子查询结果
- 将子查询结果再与主查询结合执行完整的SQL语句
在子查询中像标量子查询,嵌套子查询或者关联子查询可以看作是子查询的一种操作方式即可。
3. 各种各样的函数
函数大致分为如下几类:
- 算术函数 (用来进行数值计算的函数)
- 字符串函数 (用来进行字符串操作的函数)
- 日期函数 (用来进行日期操作的函数)
- 转换函数 (用来转换数据类型和值的函数)
- 聚合函数 (用来进行数据聚合的函数)
3.1 算数函数
+ - * /
-- DDL :创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);
-- DML :插入数据
START TRANSACTION; -- 开始事务
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);
COMMIT; -- 提交事务
-- 查询表内容
+----------+------+------+
| m | n | p |
+----------+------+------+
| 500.000 | 0 | NULL |
| -180.000 | 0 | NULL |
| NULL | NULL | NULL |
| NULL | 7 | 3 |
| NULL | 5 | 2 |
| NULL | 4 | NULL |
| 8.000 | NULL | 3 |
| 2.270 | 1 | NULL |
| 5.555 | 2 | NULL |
| NULL | 1 | NULL |
| 8.760 | NULL | NULL |
+----------+------+------+
-
ABS(x)
:绝对值当 ABS 函数的参数为
NULL
时,返回值也是NULL
。 -
MOD(M, N)
:求余数,等价于M % N
和M MOD N
MOD(N, 0)
返回值是NULL
。 -
ROUND(X), ROUND(X,D)
:四舍五入参数1:对象数值
参数2:保留小数的位数
注意:当参数 保留小数的位数 为变量时,可能会遇到错误,请谨慎使用变量。
SELECT
m,
n,
p,
ABS(m) AS abs_col,
MOD(n, p) AS mod_col,
ROUND(m,1) AS round_cols # 保留一位小数 四舍五入
FROM samplemath;
+----------+------+------+---------+---------+------------+
| m | n | p | abs_col | mod_col | round_cols |
+----------+------+------+---------+---------+------------+
| 500.000 | 0 | NULL | 500.000 | NULL | 500 |
| -180.000 | 0 | NULL | 180.000 | NULL | -180 |
| NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | 7 | 3 | NULL | 1 | NULL |
| NULL | 5 | 2 | NULL | 1 | NULL |
| NULL | 4 | NULL | NULL | NULL | NULL |
| 8.000 | NULL | 3 | 8.000 | NULL | 8 |
| 2.270 | 1 | NULL | 2.270 | NULL | 2.3 |
| 5.555 | 2 | NULL | 5.555 | NULL | 5.6 |
| NULL | 1 | NULL | NULL | NULL | NULL |
| 8.760 | NULL | NULL | 8.760 | NULL | 8.8 |
+----------+------+------+---------+---------+------------+
3.2 字符串函数
-- DDL :创建表
USE db_shop;
DROP TABLE IF EXISTS tbl_tbl_samplestr;
CREATE TABLE tbl_samplestr
(str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
-- DML:插入数据
START TRANSACTION;
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES ('opx', 'rt', NULL);
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES ('太阳', '月亮', '火星');
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES ('aaa', NULL, NULL);
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES ('abc哈哈', 'abc', 'ABC');
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO tbl_samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');
COMMIT;
-- 确认表中的内容
SELECT * FROM tbl_samplestr;
- CONCAT(str1, str2, str3) :拼接
- LENGTH( str ):字符串长度
- LOWER(str):小写转换
- UPPER(str):大写转换
- REPLACE(str,from_str,to_str):字符串的替换
SUBSTRING(str, pos)
,SUBSTRING(str FROM pos),
SUBSTRING(str, pos, len)
,SUBSTRING(str FROM pos FOR len)
:字符串的截取SUBSTRING_INDEX(str, delim, count)
:字符串按索引截取
首先明确,在 MySQL 索引都是从 1 开始的。
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2) AS ret;
+-----------+
| ret |
+-----------+
| www.mysql |
+-----------+
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2) AS ret;
+-----------+
| ret |
+-----------+
| mysql.com |
+-----------+
获取第1个元素比较容易,获取第2个元素/第n个元素可以采用二次拆分的写法。
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1) AS ret;
+------+
| ret |
+------+
| www |
+------+
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) AS ret;
+-------+
| ret |
+-------+
| mysql |
+-------+
3.3 日期函数
CURRENT_DATE()
:获取当前日期
SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2021-08-16 |
+----------------+
CURRENT_TIME()
:获取当前时间
SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 07:34:47 |
+----------------+
CURRENT_TIMESTAMP()
:当前日期和时间
SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2021-08-16 07:35:17 |
+---------------------+
EXTRACT(unit FROM date)
:截取日期元素
SELECT
EXTRACT(YEAR FROM '2022-12-12 12:12:12') AS `Year`,
EXTRACT(MONTH FROM '2022-12-12 12:12:12') AS `Month`,
EXTRACT(DAY FROM '2022-12-12 12:12:12') AS `Day`,
EXTRACT(HOUR FROM '2022-12-12 12:12:12') AS `Hour`,
EXTRACT(MINUTE FROM '2022-12-12 12:12:12') AS `Minute`,
EXTRACT(SECOND FROM '2022-12-12 12:12:12') AS `Second`;
+------+-------+------+------+--------+--------+
| Year | Month | Day | Hour | Minute | Second |
+------+-------+------+------+--------+--------+
| 2022 | 12 | 12 | 12 | 12 | 12 |
+------+-------+------+------+--------+--------+
3.4 转换函数
CAST(转换前的值 AS 想要转换的数据类型)
:类型转换
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
+---------+
| int_col |
+---------+
| 1 |
+---------+
SELECT CAST('2009-12-14' AS DATE) AS date_col;
+------------+
| date_col |
+------------+
| 2009-12-14 |
+------------+
COALESCE
:将NULL转换为其他值
SELECT
COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, 'hello world', NULL) AS col_2,
COALESCE(NULL, NULL, '2022-12-12') AS col_3;
+-------+-------------+------------+
| col_1 | col_2 | col_3 |
+-------+-------------+------------+
| 11 | hello world | 2022-12-12 |
+-------+-------------+------------+
4. 谓词
4.1 什么是谓词
谓词就是返回值为真值的函数。包括TRUE
/ FALSE
/ UNKNOWN
。
谓词主要有以下几个:
LIKE
BETWEEN
IS NULL
、IS NOT NULL
IN
EXISTS
4.2 LIKE谓词
LIKE谓词用于字符串的部分一致查询。部分一致分为前方一致、中间一致和后方一致三种类型。
先来看一张表EMP
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
查询ENAME列中,名字以A开头的员工信息。
# % 下划线匹配任意 多 个字符
SELECT * FROM EMP WHERE ENAME LIKE 'A%';
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
+-------+-------+----------+------+------------+---------+--------+--------+
查询ENAME列中,名字的第二个字母M,第四个字母T的员工信息。
# _ 下划线匹配任意 1 个字符
SELECT * FROM EMP WHERE ENAME LIKE '_M_T%';
+-------+-------+-------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-------+------+------------+--------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
+-------+-------+-------+------+------------+--------+------+--------+
4.3 BETWEEN谓词
用于范围查询。与其他谓词不同在于它使用了 3 个参数。
# 查询 员工工资在 1000~2000 的员工信息
SELECT ENAME, SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 2000;
+--------+---------+
| ENAME | SAL |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| MILLER | 1300.00 |
+--------+---------+
4.4 IS NULL 和 IS NOT NULL
用于判断是否为NULL。可以使用<=>
判断为 NULL 但是因为可读性不高,不推荐使用
# 查询 MGR 为 NULL 的员工信息
SELECT * FROM EMP WHERE MGR IS NULL;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
# 查询 COMM 不为 NULL 的员工信息
SELECT * FROM EMP WHERE COMM IS NOT NULL;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4.5 IN谓词
OR的简便用法
# 查询 员工姓名为 SMITH、 FORD、 JAMES 的员工信息
# SELECT * FROM EMP WHERE ENAME='SMITH' OR ENAME='FORD' OR ENAME='JAMES';
SELECT * FROM EMP WHERE ENAME IN('SMITH', 'FORD', 'JAMES');
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
- 注意:在使用 IN 和 NOT IN 时是无法选取出 NULL 数据的。
# NOT IN(300.00) 无法查出 COMM 列为 NULL 的记录
SELECT * FROM EMP WHERE COMM NOT IN(300.00);
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4.6 使用子查询作为IN谓词的参数
IN和子查询
IN 谓词(NOT IN 谓词)具有其他谓词所没有的用法,那就是可以使用子查询作为其参数。子查询就是 SQL内部生成的表,因此也可以说“能够将表或试图作为 IN 的参数”。
-- DDL :创建表
DROP TABLE IF EXISTS shopproduct;
CREATE TABLE shopproduct
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id) -- 指定主键
);
-- DML :插入数据
START TRANSACTION; -- 开始事务
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0001', '东京', '0001', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0001', '东京', '0002', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0001', '东京', '0003', 15);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0002', '名古屋', '0002', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0002', '名古屋', '0003', 120);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0002', '名古屋', '0004', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0002', '名古屋', '0006', 10);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0002', '名古屋', '0007', 40);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0003', '大阪', '0003', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0003', '大阪', '0004', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0003', '大阪', '0006', 90);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0003', '大阪', '0007', 70);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('0004', '福冈', '0001', 100);
COMMIT; -- 提交事务
+---------+-----------+------------+----------+
| shop_id | shop_name | product_id | quantity |
+---------+-----------+------------+----------+
| 0001 | 东京 | 0001 | 30 |
| 0001 | 东京 | 0002 | 50 |
| 0001 | 东京 | 0003 | 15 |
| 0002 | 名古屋 | 0002 | 30 |
| 0002 | 名古屋 | 0003 | 120 |
| 0002 | 名古屋 | 0004 | 20 |
| 0002 | 名古屋 | 0006 | 10 |
| 0002 | 名古屋 | 0007 | 40 |
| 0003 | 大阪 | 0003 | 20 |
| 0003 | 大阪 | 0004 | 50 |
| 0003 | 大阪 | 0006 | 90 |
| 0003 | 大阪 | 0007 | 70 |
| 0004 | 福冈 | 0001 | 100 |
+---------+-----------+------------+----------+
由于单独使用商店编号(shop_id)或者商品编号(product_id)不能区分表中每一行数据,因此指定了 2 列作为主键(primary key)对商店和商品进行组合,用来唯一确定每一行数据。
假设我么需要取出大阪在售商品的销售单价,该如何实现呢?
第一步,取出大阪门店的在售商品 product_id
;
SELECT * FROM shopproduct WHERE shop_name = '0003';
+---------+-----------+------------+----------+
| shop_id | shop_name | product_id | quantity |
+---------+-----------+------------+----------+
| 0003 | 大阪 | 0003 | 20 |
| 0003 | 大阪 | 0004 | 50 |
| 0003 | 大阪 | 0006 | 90 |
| 0003 | 大阪 | 0007 | 70 |
第二步,取出大阪门店在售商品的销售单价 sale_price
。
SELECT product_name, sale_price
FROM tbl_product
WHERE product_id IN (SELECT product_id
FROM shopproduct
WHERE shop_id = '0003');
+----------------+------------+
| product_name | sale_price |
+----------------+------------+
| sports T-shirt | 4000 |
| kitchen knife | 3000 |
| fork | 500 |
| grater | 880 |
+----------------+------------+
子查询是从最内层开始执行的(由内而外),因此,上述语句的子查询执行之后,sql 展开成下面的语句
-- 子查询展开后的结果
SELECT product_name, sale_price
FROM tbl_product
WHERE product_id IN ('0003', '0004', '0006', '0007');
+----------------+------------+
| product_name | sale_price |
+----------------+------------+
| sports T-shirt | 4000 |
| kitchen knife | 3000 |
| fork | 500 |
| grater | 880 |
+----------------+------------+
可以看到,子查询转换之后变为 IN 谓词用法,你理解了吗?
或者,你会疑惑既然 IN 谓词也能实现,那为什么还要使用子查询呢?这里给出两点原因:
1️⃣:实际生活中,某个门店的在售商品是不断变化的,使用 in 谓词就需要经常更新 sql 语句,降低了效率,提高了维护成本;
2️⃣:实际上,某个门店的在售商品可能有成百上千个,手工维护在售商品编号真是个大工程。
使用子查询即可保持 sql 语句不变,极大提高了程序的可维护性,这是系统开发中需要重点考虑的内容。
NOT IN和子查询
NOT IN 同样支持子查询作为参数,用法和 IN 完全一样。
# NOT IN 使用子查询作为参数,取出未在大阪门店销售的商品的销售单价
SELECT product_name, sale_price
FROM tbl_product
WHERE product_id NOT IN (SELECT product_id
FROM shopproduct
WHERE shop_id = '0003');
+-----------------+------------+
| product_name | sale_price |
+-----------------+------------+
| T-shirt | 1000 |
| puncher | 500 |
| pressure cooker | 6800 |
| ballpoint | 100 |
+-----------------+------------+
4.7 EXIST 谓词
EXIST 谓词的用法理解起来有些难度。实际上即使不使用 EXIST,基本上也都可以使用 IN(或者 NOT IN)来代替。但是如果能够熟练使用 EXIST 谓词,会极大便利。
EXIST谓词的使用方法
谓词的作用就是 “判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真(TRUE),如果不存在就返回假(FALSE)。
EXIST(存在)谓词的主语是“记录”。
继续以 IN 和 子查询 中的示例,使用 EXIST 选取出大阪门店在售商品的销售单价。
SELECT product_name, sale_price
FROM tbl_product AS p
WHERE EXISTS (SELECT *
FROM shopproduct AS sp
WHERE sp.shop_id = '0003'
AND sp.product_id = p.product_id);
+----------------+------------+
| product_name | sale_price |
+----------------+------------+
| sports T-shirt | 4000 |
| kitchen knife | 3000 |
| fork | 500 |
| grater | 880 |
+----------------+------------+
EXIST的参数
与 LIKE和 BETWEEN 不一样, 列名 LIKE 字符串
或者 列名 BETWEEN 值1 AND 值2
这样需要指定 2 个以上的参数。
EXIST 是只有 1 个参数的谓词,通常是一个关联子查询。
EXISTS (SELECT *
FROM shopproduct AS sp
WHERE sp.shop_id = '0003'
AND sp.product_id = p.product_id);
上面这样的子查询就是唯一的参数。确切地说,由于通过条件 sp.product_id = p.product_id
将 tbl_product 表和 shopproduct 表进行了联接,因此作为参数的是关联子查询。 EXIST 通常会使用关联子查询作为参数。
子查询中的 SELECT *
由于 EXIST 只关心记录是否存在,因此返回哪些列都没有关系。
EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件 “shop_id=‘0003’,tbl_product 表与 shopproduct 表中 product_id 相同“ 的记录,只有存在这样的记录时才返回真(TRUE)。
因此,使用下面的查询语句,查询结果也不会发生变化。
SELECT product_name, sale_price
FROM product AS p
WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数
FROM shopproduct AS sp
WHERE sp.shop_id = '0003'
AND sp.product_id = p.product_id);
使用 NOT EXIST 替换 NOT IN
NOT IN 也可以用 NOT EXIST 来替换。
下面的代码示例取出,不在大阪门店销售的商品的销售单价。
SELECT product_name, sale_price
FROM tbl_product AS p
WHERE NOT EXISTS (SELECT *
FROM shopproduct AS sp
WHERE sp.shop_id = '0003'
AND sp.product_id = p.product_id);
+-----------------+------------+
| product_name | sale_price |
+-----------------+------------+
| T-shirt | 1000 |
| puncher | 500 |
| pressure cooker | 6800 |
| ballpoint | 100 |
+-----------------+------------+
NOT EXIST 与 EXIST 相反,当“不存在”满足子查询中指定条件的记录时返回真(TRUE)。
5. CASE 表达式
5.1 什么是 CASE 表达式
CASE 表达式是在区分情况时使用的,这种情况的区分在编程中通常称为(条件)分支。
CASE表达式的语法分为 简单CASE表达式 和 搜索CASE表达式 两种。由于搜索 CASE表达式 包含 简单CASE表达式的全部功能。本课程将重点介绍 搜索CASE表达式。
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
...
ELSE <表达式>
END
上述语句执行时,依次判断 WHEN 表达式是否为真值,是则执行 THEN 后的语句,如果所有的 WHEN 表达式均为假,则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式,最后也只会返回一个值。
5.2 CASE表达式的使用方法
假设现在 要实现如下结果:
A: clothes
B: Office supplies
C: kitchenware
因为表中的记录并不包含 “A : ” 或者 “B : ” 这样的字符串,所以需要在 SQL 中进行添加。并将 “A : ” “B : ” “C : ” 与记录结合起来。
- 应用场景1:根据不同分支得到不同列值
SELECT product_name,
CASE WHEN product_type = 'clothes' THEN CONCAT('A: ', product_type)
WHEN product_type = 'Office supplies' THEN CONCAT('B: ', product_type)
WHEN product_type = 'kitchenware' THEN CONCAT('C: ', product_type)
ELSE NULL
END AS abc_product_type
FROM tbl_product;
+-----------------+--------------------+
| product_name | abc_product_type |
+-----------------+--------------------+
| T-shirt | A: clothes |
| puncher | B: Office supplies |
| sports T-shirt | A: clothes |
| kitchen knife | C: kitchenware |
| pressure cooker | C: kitchenware |
| fork | C: kitchenware |
| grater | C: kitchenware |
| ballpoint | B: Office supplies |
+-----------------+--------------------+
ELSE 子句也可以省略不写,这时会被默认为 ELSE NULL。但为了防止有人漏读,还是希望大家能够显示地写出 ELSE 子句。
此外, CASE 表达式最后的 “END” 是不能省略的,请大家特别注意不要遗漏。忘记书写 END 会发生语法错误,这也是初学时最容易犯的错误。
- 应用场景2:实现列方向上的聚合
通常我们使用如下代码实现行的方向上不同种类的聚合(这里是 sum)
SELECT product_type,
SUM(sale_price) AS sum_price
FROM tbl_product
GROUP BY product_type;
+-----------------+-----------+
| product_type | sum_price |
+-----------------+-----------+
| clothes | 5000 |
| Office supplies | 600 |
| kitchenware | 11180 |
+-----------------+-----------+
假如要在列的方向上展示不同种类额聚合值,该如何写呢?
+-------------------+-------------------+------------------+
| sum_price_clothes | sum_price_kitchen | sum_price_office |
+-------------------+-------------------+------------------+
| 5000 | 11180 | 600 |
+-------------------+-------------------+------------------+
聚合函数 + CASE WHEN 表达式即可实现该效果
# 对按照 商品种类 计算出的 销售单价合计值 进行行列转换
SELECT
SUM(
CASE WHEN product_type = 'clothes' THEN sale_price
ELSE 0
END
) AS sum_price_clothes,
SUM(
CASE WHEN product_type = 'kitchenware' THEN sale_price
ELSE 0
END
) AS sum_price_kitchen,
SUM(
CASE WHEN product_type = 'Office supplies' THEN sale_price
ELSE 0
END
) AS sum_price_office
FROM tbl_product;
+-------------------+-------------------+------------------+
| sum_price_clothes | sum_price_kitchen | sum_price_office |
+-------------------+-------------------+------------------+
| 5000 | 11180 | 600 |
+-------------------+-------------------+------------------+
- (扩展内容)应用场景3:实现行转列
CREATE TABLE IF NOT EXISTS tbl_student(
stu_id integer AUTO_INCREMENT,
name varchar(40) NOT NULL,
subject varchar(40),
score integer,
primary key (stu_id)
);
INSERT INTO tbl_student (stu_id, name, subject, score) VALUES(NULL, 'Zhangsan', 'Yuwen', 93);
INSERT INTO tbl_student (stu_id, name, subject, score) VALUES(NULL, 'Zhangsan', 'Mathematics', 88);
INSERT INTO tbl_student (stu_id, name, subject, score) VALUES(NULL, 'Zhangsan', 'English', 91);
INSERT INTO tbl_student (stu_id, name, subject, score) VALUES(NULL, 'Lisi', 'Yuwen', 97);
INSERT INTO tbl_student (stu_id, name, subject, score) VALUES(NULL, 'Lisi', 'Mathematics', 90);
INSERT INTO tbl_student (stu_id, name, subject, score) VALUES(NULL, 'Lisi', 'English', 77);
select * from tbl_student;
+--------+----------+-------------+-------+
| stu_id | name | subject | score |
+--------+----------+-------------+-------+
| 1 | Zhangsan | Yuwen | 93 |
| 2 | Zhangsan | Mathematics | 88 |
| 3 | Zhangsan | English | 91 |
| 4 | Lisi | Yuwen | 97 |
| 5 | Lisi | Mathematics | 90 |
| 6 | Lisi | English | 77 |
+--------+----------+-------------+-------+
计划得到如下的图表结构
聚合函数 + CASE WHEN 表达式即可实现该转换
SELECT name,
SUM(CASE WHEN subject = 'Yuwen' THEN score ELSE null END) as chinese,
SUM(CASE WHEN subject = 'Mathematics' THEN score ELSE null END) as math,
SUM(CASE WHEN subject = 'English' THEN score ELSE null END) as english
FROM tbl_student
GROUP BY name;
+----------+---------+------+---------+
| name | chinese | math | english |
+----------+---------+------+---------+
| Zhangsan | 93 | 88 | 91 |
| Lisi | 97 | 90 | 77 |
+----------+---------+------+---------+
上述代码实现了数字列 score 的行转列,也可以实现文本列 subject 的行转列
# CASE WHEN 实现文本列 subject 行转列
SELECT name,
MAX(CASE WHEN subject = 'Yuwen' THEN subject ELSE null END) as chinese,
MAX(CASE WHEN subject = 'Mathematics' THEN subject ELSE null END) as math,
MIN(CASE WHEN subject = 'English' THEN subject ELSE null END) as english
FROM tbl_student
GROUP BY name;
+----------+---------+-------------+---------+
| name | chinese | math | english |
+----------+---------+-------------+---------+
| Zhangsan | Yuwen | Mathematics | English |
| Lisi | Yuwen | Mathematics | English |
+----------+---------+-------------+---------+
总结
- 当待转换列为数字时,可以使用
SUM
、AVG
、MAX
、MIN
等聚合函数; - 当待转换列为文本时,可以使用
MAX
、MIN
等聚合函数
练习题-第一部分
- 创建出满足下述三个条件的视图(视图名称为 ViewPractice5_1)。使用 tbl_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, register_date
FROM tbl_product
WHERE sale_price >= 1000 AND register_date = '2009-9-20';
SELECT * FROM ViewPractice5_1;
+---------------+------------+---------------+
| product_name | sale_price | register_date |
+---------------+------------+---------------+
| T-shirt | 2222 | 2009-09-20 |
| kitchen knife | 3000 | 2009-09-20 |
+---------------+------------+---------------+
- 向 习题1 中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果呢?
INSERT INTO ViewPractice5_1 VALUES ('knife', 1, '2009-11-02');
不能运行。。。错误信息是:ERROR 1423 (HY000): Field of view ‘shop.ViewPractice5_1’ underlying table doesn’t have a default value
- 请根据如下结果编写 SELECT 语句,其中 sale_price_all 列为全部商品的平均销售单价。
+------------+-----------------+-----------------+------------+----------------+
| product_id | product_name | product_type | sale_price | sale_price_all |
+------------+-----------------+-----------------+------------+----------------+
| 0001 | T-shirt | clothes | 1000 | 2097.5000 |
| 0002 | puncher | Office supplies | 500 | 2097.5000 |
| 0003 | sports T-shirt | clothes | 4000 | 2097.5000 |
| 0004 | kitchen knife | kitchenware | 3000 | 2097.5000 |
| 0005 | pressure cooker | kitchenware | 6800 | 2097.5000 |
| 0006 | fork | kitchenware | 500 | 2097.5000 |
| 0007 | grater | kitchenware | 880 | 2097.5000 |
| 0008 | ballpoint | Office supplies | 100 | 2097.5000 |
+------------+-----------------+-----------------+------------+----------------+
这个题和前面的例子一样。。使用标量子查询做。
SELECT
product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price) FROM tbl_product) AS sale_price_all
FROM tbl_product;
+------------+-----------------+-----------------+------------+----------------+
| product_id | product_name | product_type | sale_price | sale_price_all |
+------------+-----------------+-----------------+------------+----------------+
| 0001 | T-shirt | clothes | 1000 | 2097.5000 |
| 0002 | puncher | Office supplies | 500 | 2097.5000 |
| 0003 | sports T-shirt | clothes | 4000 | 2097.5000 |
| 0004 | kitchen knife | kitchenware | 3000 | 2097.5000 |
| 0005 | pressure cooker | kitchenware | 6800 | 2097.5000 |
| 0006 | fork | kitchenware | 500 | 2097.5000 |
| 0007 | grater | kitchenware | 880 | 2097.5000 |
| 0008 | ballpoint | Office supplies | 100 | 2097.5000 |
+------------+-----------------+-----------------+------------+----------------+
- 请根据 习题1 中的条件编写一条 SQL 语句,创建一幅包含如下数据的视图(名称为 AvgPriceByType)。
子查询语句可以放在:WHERE/HAVING、SELECT、FROM后面。
先来分析一下,子查询语句放到哪里吧。
-
对比一下 tbl_product 表字段和结果集的字段,发现有新字段
avg_sale_price
。# tbl_product 表字段 product_id | product_name | product_type | sale_price | purchase_price | register_date product_id | product_name | product_type | sale_price | avg_sale_price
WHERE/HAVING后的子查询都是对 tbl_product 表做过滤的,不会产生新的字段,PASS。
-
再仔细看一下结果集中的新字段
avg_sale_price
。发现有2500、300、2795都不一样。而SELECT后面跟的标量子查询出来的字段值都是清一色一样的,PASS。因此,子查询语句只能在 FROM 后面。
来吧兄弟们,开干!🎠
题目要求 计算出的是 各商品种类的平均销售单价。
第一步:查询每个种类的平均销售单价
select product_type, avg(sale_price)
from tbl_product group by product_type;
+-----------------+-----------------+
| product_type | avg(sale_price) |
+-----------------+-----------------+
| clothes | 2500.0000 |
| Office supplies | 300.0000 |
| kitchenware | 2795.0000 |
+-----------------+-----------------+
第二步:计算出的是 各商品种类的平均销售单价
select
product_id,
product_name,
t1.product_type,
sale_price,
t2.avg_sale_price
from
tbl_product as t1,
(select product_type, avg(sale_price) as avg_sale_price
from tbl_product group by product_type) as t2
where t1.product_type = t2.product_type;
+------------+-----------------+-----------------+------------+----------------+
| product_id | product_name | product_type | sale_price | avg_sale_price |
+------------+-----------------+-----------------+------------+----------------+
| 0001 | T-shirt | clothes | 1000 | 2500.0000 |
| 0002 | puncher | Office supplies | 500 | 300.0000 |
| 0003 | sports T-shirt | clothes | 4000 | 2500.0000 |
| 0004 | kitchen knife | kitchenware | 3000 | 2795.0000 |
| 0005 | pressure cooker | kitchenware | 6800 | 2795.0000 |
| 0006 | fork | kitchenware | 500 | 2795.0000 |
| 0007 | grater | kitchenware | 880 | 2795.0000 |
| 0008 | ballpoint | Office supplies | 100 | 300.0000 |
+------------+-----------------+-----------------+------------+----------------+
好了,写出来了 😆
create view AvgPriceByType(product_id, product_name, product_type, sale_price, avg_sale_price)
as
select
product_id,
product_name,
t1.product_type,
sale_price,
t2.avg_sale_price
from
tbl_product as t1,
(select product_type, avg(sale_price) as avg_sale_price
from tbl_product group by product_type) as t2
where t1.product_type = t2.product_type;
mysql> select * from AvgPriceByType;
+------------+-----------------+-----------------+------------+----------------+
| product_id | product_name | product_type | sale_price | avg_sale_price |
+------------+-----------------+-----------------+------------+----------------+
| 0001 | T-shirt | clothes | 1000 | 2500.0000 |
| 0002 | puncher | Office supplies | 500 | 300.0000 |
| 0003 | sports T-shirt | clothes | 4000 | 2500.0000 |
| 0004 | kitchen knife | kitchenware | 3000 | 2795.0000 |
| 0005 | pressure cooker | kitchenware | 6800 | 2795.0000 |
| 0006 | fork | kitchenware | 500 | 2795.0000 |
| 0007 | grater | kitchenware | 880 | 2795.0000 |
| 0008 | ballpoint | Office supplies | 100 | 300.0000 |
+------------+-----------------+-----------------+------------+----------------+
练习题-第二部分
-
判断题:运算或者函数中含有 NULL 时,结果是否都会变为NULL ?
任何数据和 NULL 运算时,结果会返回 NULL。
聚合函数有IF NULL判断,会忽略NULL。
-
对本章中使用的
tbl_product
表执行如下 2 条SELECT
语句,能够得到什么样的结果呢?
# 1. 查询 purchase_price 等于 500、2800、5000 的 product_name 和 purchase_price。
SELECT product_name, purchase_price
FROM tbl_product
WHERE purchase_price NOT IN (500, 2800, 5000);
# 2.查询 purchase_price 不等于 500、2800、5000、NULL 的 product_name 和 purchase_price。
SELECT product_name, purchase_price
FROM tbl_product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
- 按照销售单价(
sale_price
)对 练习 6 中的tbl_product
表中的商品进行如下分类。
- 低档商品:销售单价在1000日元以下(T恤衫、办公用品、叉子、擦菜板、 圆珠笔)
- 中档商品:销售单价在1001日元以上3000日元以下(菜刀)
- 高档商品:销售单价在3001日元以上(运动T恤、高压锅)
请编写出 统计上述商品种类中所包含的商品数量的 SELECT 语句,结果如下所示。
low_price | mid_price | high_price
----------+-----------+------------
5 | 1 | 2
这个考察的case表达式使用。先来复习一下case表达式语法
case when <求值表达式> then 表达式
when <求值表达式> then 表达式
...
else null
end
下面开始做题
# 第1步 先将价格分类,在区间里,就返回1,否则返回0
SELECT
CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END AS low_price,
CASE WHEN sale_price > 1000 AND sale_price <= 3000 THEN 1 ELSE 0 END AS mid_price,
CASE WHEN sale_price > 3001 THEN 1 ELSE 0 END AS high_price
FROM tbl_product;
+-----------+-----------+------------+
| low_price | mid_price | high_price |
+-----------+-----------+------------+
| 1 | 0 | 0 |
| 1 | 0 | 0 |
| 0 | 0 | 1 |
| 0 | 1 | 0 |
| 0 | 0 | 1 |
| 1 | 0 | 0 |
| 1 | 0 | 0 |
| 1 | 0 | 0 |
+-----------+-----------+------------+
# 第2步 将 第1步查询的结果,都求和。
SELECT
SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price > 1000 AND sale_price <= 3000 THEN 1 ELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price > 3001 THEN 1 ELSE 0 END) AS high_price
FROM tbl_product;
+-----------+-----------+------------+
| low_price | mid_price | high_price |
+-----------+-----------+------------+
| 5 | 1 | 2 |
+-----------+-----------+------------+
另一种的做法是不用 sum 函数,使用 count 函数,然后将case表达式中的else后面改为 NULL 。
SELECT
COUNT(CASE WHEN sale_price <= 1000 THEN 1 ELSE NULL END) AS low_price,
COUNT(CASE WHEN sale_price > 1000 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 tbl_product;
+-----------+-----------+------------+
| low_price | mid_price | high_price |
+-----------+-----------+------------+
| 5 | 1 | 2 |
+-----------+-----------+------------+
参考文献
[1] (英)BENFORTA著. MySQL必知必会[M]. 北京:人民邮电出版社, 2009.01.
[2] https://github.com/datawhalechina/wonderful-sql