SQL编程-组队学习打卡task03-复杂一点的查询_sql查看销售表中高于平均单价的商品销售信息-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_39028641/article/details/125812933

本文详细介绍了SQL中的视图创建、修改、更新和删除，以及子查询的使用，包括标量子查询和关联子查询。此外，还涵盖了算术、字符串、日期和转换函数的应用，以及谓词LIKE、BETWEEN、ISNULL、IN和EXIST的用法。最后，通过CASE表达式展示了条件判断和数据转换，并给出了相关习题及其解答。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 视图与表的区别

定义：视图不是表，视图是虚表，视图依赖于表，视图与表的区别-在于“是否保存了实际的数据”。
优点：频繁使用select更高效，增强数据的保密性等。

1.1 创建视图

create view 视图名(列名。。)
as
select语句

视图的列名个数 = select语句中的列名个数；
MySQL中创建视图可以用order by，其他DBMS中不可以。

create view productsum (product_type,cnt_product)
	as
		select product_type,count(*)
		from product
		group by product_type;

1.2 修改视图

alter view 视图名
as
select语句

个人理解：修改视图理解为从原数据表重新换一些数据来观察，本质上没有改变原数据表。

alter view productsum 
	as
		select product_type,sale_price
		from product
		where regist_date > '2009-09-11';

1.3 更新视图

个人理解：把视图看作原表中数据的虚拟映射，则视图不能使用聚合函数、distinct、group by、having等来进行更新，因为这么做视图变了但是原表没变，不能保证数据的一致性。
！尽量避免通过视图来修改表。

update productsum
set sale_price='5000'
where product_type='办公用品';

1.4 删除视图

删除了就没了，跟table一样。

drop view productsum ;

2. 子查询

个人理解为把一个select语句用于另一个select语句中的from子句里面，也就是把一个select语句的查询结果当作一张表，再在上面查询。子查询可以嵌套使用，但是应尽量避免。

2.1 标量子查询

返回一个值。

-- 查询出销售单价高于平均销售单价的商品
select product_id,product_name,sale_price
from product
where sale_price > (select avg(sale_price) from product);

-- 把一个标量作为一列
select product_id,product_name,sale_price,(select avg(sale_price) from product) as avg_price
from product;

product_id|product_name|sale_price|avg_price|
----------+------------+----------+---------+
0001      |T恤          |      1000|2710.0000|
0002      |打孔器         |       500|2710.0000|
0003      |运动T恤        |      4000|2710.0000|
0004      |菜刀          |      3000|2710.0000|
0005      |高压锅         |      6800|2710.0000|
0006      |叉子          |       500|2710.0000|
0007      |擦菜板         |       880|2710.0000|
0008      |圆珠笔         |      5000|2710.0000|

2.2 关联子查询

通过一些标志将内外两层的查询连接起来，达到过滤数据的目的。

书写顺序：
SELECT》FROM 》WHERE》GROUP BY》HAVE》ORDER BY
执行顺序：
FROM 》WHERE》GROUP BY》HAVE》SELECT》ORDER BY

-- 选取出各商品种类中高于该商品种类的平均销售单价的商品
select product_type,product_name,sale_price
from product p1
where sale_price > (select avg(sale_price)
					from product p2
					where p1.product_type = p2.product_type 
					group by product_type 
					);

解析：

而关联子查询的执行顺序和正常的SELECT语句完全不同：

执行主查询
SELECT product_type,product_name,sale_price
FROM product；
从主查询的product_type中取一个值如’衣服’ 传入子查询，使得子查询变为一个标量子查询（在本题中）
SELECT AVG(sale_price)
FROM product p2
WHERE p2.product='衣服';
该查询返回 AVG(sale_price)=2500
将子查询得到的结果返回给主查询，主查询变为
select product_type, product_name, sale_price
from product p1
where sale_price > 2500 and product_type ='衣服';
重复选取主查询中不同的product_type, 执行2、3步，直到主查询中的Product表中product _type列记录取完为止。
https://zhuanlan.zhihu.com/p/41844742

3. 函数

3.1 算术函数（用来进行数值计算的函数）

select m,
	abs(m) as abs_col,		-- 绝对值
	n,p,
	mod(n,p) as mod_col,	-- 求余数，n/p。只有SQL Server 不支持该函数，其使用%符号来计算余数
	round(m,1) as round_col	-- 四舍五入
from samplemath s ;

3.2 字符串函数（用来进行字符串操作的函数）

select 
	str1,
	str2,
	str3,
	concat(str1,str2,str3) as str_concat, 			-- 拼接
	length(str1) as len_str, 						-- 字符串长度
	lower(str1) as low_str,							-- 小写转换，还有UPPER大写转换
	replace(str1,str2,str3) as rep_str, 			-- 字符串的替换REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
	substring(str1 from str2 for str3) as sub_str	-- 字符串的截取SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
from samplestr s ;

str1     |str2|str3|str_concat     |len_str|low_str  |rep_str  |sub_str|
---------+----+----+---------------+-------+---------+---------+-------+
opx      |rt  |    |               |      3|opx      |         |       |
abc      |def |    |               |      3|abc      |         |       |
太阳       |月亮  |火星  |太阳月亮火星         |      6|太阳       |太阳       |       |
aaa      |    |    |               |      3|aaa      |         |       |
         |xyz |    |               |       |         |         |       |
@!#$%    |    |    |               |      5|@!#$%    |         |       |
ABC      |    |    |               |      3|abc      |         |       |
aBC      |    |    |               |      3|abc      |         |       |
abc哈哈    |abc |ABC |abc哈哈abcABC    |      9|abc哈哈    |ABC哈哈    |       |
abcdefabc|abc |ABC |abcdefabcabcABC|      9|abcdefabc|ABCdefABC|       |
micmic   |i   |I   |micmiciI       |      6|micmic   |mIcmIc   |       |

select substring_index('www.mysql.com','.','-2'); -- 字符串按索引截取，SUBSTRING_INDEX (原始字符串， 分隔符，n)
select substring_index(substring_index('www.mysql.com','.','-2'),'.',1) ; # 取出mysql
select repeat('fighting！',3); -- 字符串按需重复多次# fighting！fighting！fighting！

3.3 日期函数（用来进行日期操作的函数）


select current_date; 			-- 获取当前日期  	2022-07-17
select CURRENT_TIME; 			-- 当前时间		17:00:29
select CURRENT_TIMESTAMP 		-- 当前日期和时间 	2022-07-17 17:00:38
SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year, -- 截取日期元素,返回为数值类型。EXTRACT(日期元素 FROM 日期)
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|
-------------------+----+-----+---+----+------+------+
2022-07-17 17:03:16|2022|    7| 17|  17|     3|    16|

3.4 转换函数（用来转换数据类型和值的函数）

-- CAST -- 类型转换
select cast('0001' as signed integer) as int_col;
-- 要转换为整型时，需要指定为 SIGNED（有符号） 或者 UNSIGNED（无符号）


-- COALESCE -- 将NULL转换为其他值.返回可变参数 A 中左侧开始第 1个不是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|

4. 谓词

谓词就是返回值为真值的函数。返回TRUE / FALSE / UNKNOWN。
谓词主要有以下几个：

4.1 谓词 LIKE – 用于字符串的部分一致查询

-- 前方一致，其中的%是代表“零个或多个任意字符串”的特殊符号。以ddd开头的所有字符串
select *
from samplelike s 
where strcol like 'ddd%'; 
-- 中间一致'%ddd%''，末尾一致'%ddd'.
-- 中间一致查询记录最多，同时包含前方一致和后方一致的查询结果。

-- _下划线匹配任意 1 个字符
select *
from samplelike s 
where strcol like 'abc__';

4.2 BETWEEN谓词 – 用于范围查询

-- between 是闭区间。开区间要用< >
select product_name,sale_price
from product p 
where sale_price between 100 and 1000;

4.3 IS NULL、 IS NOT NULL – 用于判断是否为NULL

选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。
与此相反，想要选取 NULL 以外的数据时，需要使用IS NOT NULL。

4.4 IN谓词 – OR的简便用法

多个查询条件取并集时可以选择使用or语句。

-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

等价于

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

4.5 使用子查询作为IN谓词的参数

能够将表、视图作为 IN 的参数


-- 取出大阪在售商品的销售单价
select product_id 
from shopproduct s 
where shop_name = '大阪';

select product_name,sale_price
from product p 
where product_id in 
	(select product_id 
	from shopproduct s 
	where shop_name = '大阪');

4.6 EXIST 谓词

谓词的作用就是 “判断是否存在满足某种条件的记录”。
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
EXIST（存在）谓词的主语是“记录”。

-- 使用 EXIST 选取出大阪门店在售商品的销售单价
select product_id ,sale_price
from product p 
where exists (select *
				from shopproduct s
				where s.shop_name='大阪' and p.product_id=s.product_id);

EXIST 只关心记录是否存在，因此返回哪些列都没有关系

-- 使用 EXIST 选取出大阪门店在售商品的销售单价
select product_id ,sale_price
from product p 
where exists (select 1
				from shopproduct s
				where s.shop_name='大阪' and p.product_id=s.product_id);

5. CASE 表达式

在区分情况时使用。
语法

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END

5.1 应用场景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 ： 办公用品        |

5.2 应用场景2：实现列方向上的聚合

-- 对按照商品种类计算出的销售单价合计值进行行列转换
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|

5.3 应用场景3：实现行转列

在这里插入图片描述

-- 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 |

6. 习题

6.1 创建视图

创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。
在这里插入图片描述

create view ViewPractice5_1
	as
		select product_name,sale_price,regist_date
		from product
		where sale_price >=1000 and regist_date ='2009-09-20';

product_name|sale_price|regist_date|
------------+----------+-----------+
T恤          |      1000| 2009-09-20|
菜刀          |      3000| 2009-09-20|

6.2 向习题一中创建的视图 ViewPractice5_1 中插入如下数据

向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果？为什么？
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

答：
本题会报错，相当于执行
INSERT INTO Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
VALUES (NULL, ' 刀子 ', NULL, 300, NULL, '2009-11-02');
视图不包含的其他列若没有设置default value，则回违反 NOT NULL 约束。

如果想在视图上成功使用insert，那么必须1.视图选取原表的全部列，2.插入数据符合约束。如下所示：

create view ViewP
	as
		select product_id,product_name,product_type,purchase_price,sale_price,regist_date
		from product
		where sale_price >=1000 and regist_date ='2009-09-20';

INSERT INTO ViewP VALUES ('0009',' 刀子 ','厨房用品', 300, 150,'2009-09-20');

6.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;

6.4 编写 SELECT 语句，sale_price_avg_type 列是各商品种类的平均销售单价

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

法一：
Create view avgpricebytype 
	as
		select product_id, product_name, product_type,sale_price,
				(select avg(sale_price) as sale_price_avg_type
				from product P2
				where p1.product_type = p2.product_type) # 使用关联子查询进行结算
		from product p1;

product_id|product_name|product_type|sale_price|Name_exp_5|
----------+------------+------------+----------+----------+
0001      |T恤          |衣服          |      1000| 2500.0000|
0002      |打孔器         |办公用品        |       500|  300.0000|
0003      |运动T恤        |衣服          |      4000| 2500.0000|
0004      |菜刀          |厨房用具        |      3000| 2795.0000|
0005      |高压锅         |厨房用具        |      6800| 2795.0000|
0006      |叉子          |厨房用具        |       500| 2795.0000|
0007      |擦菜板         |厨房用具        |       880| 2795.0000|
0008      |圆珠笔         |办公用品        |       100|  300.0000|

/*分析一下，每一次将p1.product_type传给子查询，如

select avg(sale_price) as sale_price_avg_type
from product P2
where p2.product_type = '衣服'

sale_price_avg_type|
-------------------+
          2500.0000|

再将查询结果返回给主查询，一共执行了8次子查询。*/

6.5 四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL ？

是的。
补充：对于逻辑运算，1 or null = 真；对于聚合函数，count(null)=0。。。

6.7 where 列 not in (xx,xx,null)

在这里插入图片描述
对于1，

对于2，查询为空

sql中null不参与计算， in null 或者 not in null 均返回空。要取出null必须用 is null 或者 is not null。

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

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。
在这里插入图片描述

select count(case when sale_price <= 1000 then sale_price else null end) as low_price,
		count(case when sale_price>1000 and sale_price <=3000 then sale_price else null end) as mid_price,
		count(case when sale_price>3000 then sale_price else null end) as high_price
from product;

或者

select count(case when sale_price <= 1000 then sale_price else null end) as low_price,
		count(case when sale_price between 1001 and 3000 then sale_price else null end) as mid_price,
		count(case when sale_price>3000 then sale_price else null end) as high_price
from product;