sql03--视图，子查询，关联子查询，各种函数，case语句

最新推荐文章于 2024-08-11 02:49:18 发布

原创最新推荐文章于 2024-08-11 02:49:18 发布 · 314 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

部署运行你感兴趣的模型镜像

视图

创建视图

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

需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。下面这样定义视图是错误的。这是因为视图和表一样，数据行都是没有顺序的。

修改视图结构

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

更新视图内容

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

删除视图

DROP VIEW <视图名1> [ , <视图名2> …]

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询；
子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。

标量子查询

要返回表中具体的某一行的某一列。

通过标量子查询语句查询出销售单价高于平均销售单价的商品。

SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

关联子查询

https://zhuanlan.zhihu.com/p/41844742

关联子查询和一般的查询执行顺序不一样
关联子查询先执行外层的select from
然后将结果依次代入子查询中

习题

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

条件 1：销售单价大于等于 1000 日元。

条件 2：登记日期是 2009 年 9 月 20 日。

条件 3：包含商品名称、销售单价和登记日期三列。
```
create view ViewPractice5_1
as (
select product_name, sale_price, regist_date
from product
where sale_price >= 1000
and regist_date = '2009-9-20' 
);
```

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

执行

   INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

会发生错误

请根据如下结果编写 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;

请根据习题一中的条件编写一条 SQL 语句，创建包含各商品种类的平均销售单价一幅包含如下数据的视图（名称为AvgPriceByType）。

select product_id , product_name, product_type , sale_price, 
(
	select avg(sale_price)
	from product as p2
	where p2.product_type = p1.product_type 
) as sale_price_avg_type
from product as p1;

函数

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

算数函数

abs(num)
mod(被除数，除数)
round(num, n)

字符串函数

concat(str1,str2, str3)
length(str)
lower,upper
replace(对象字符串，替换前的字符串，替换后的字符串)
SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
SUBSTRING_INDEX (原始字符串，分隔符，n)

  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)

repeat

日期函数

SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2020-08-08   |
+--------------+
1 row in set (0.00 sec)

SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 17:26:09     |
+--------------+
1 row in set (0.00 sec)

SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2020-08-08 17:27:07 |
+---------------------+
1 row in set (0.00 sec)

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)

转换函数

cast

-- 将字符串类型转换为数值类型
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(数据1，数据2，数据3……)

谓词

谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。

like % _
between and
is null ; is not null
in（代替多个or）; not in
使用子查询作为IN谓词的参数
exists: 仅对（）中的关联子查询，返回真的记录进行外层操作

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
不要忘记end

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

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

当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
当待转换列为文本时，可以使用MAX MIN等聚合函数

习题

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

是的

对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

①

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);
 
 product_name|purchase_price|
------------+--------------+
打孔器         |           320|
擦菜板         |           790|

②

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
 
 product_name|purchase_price|
------------+--------------+

按照销售单价( sale_price )对练习 3.6 中的 product（商品）表中的商品进行如下分类。
- 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
- 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
- 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

执行结果
```
low_price | mid_price | high_price
----------+-----------+------------
        5 |         1 |         2
```

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