东方学习SQL-task03 复杂一点的查询

shishenshashen

已于 2022-09-19 01:12:51 修改

阅读量217

点赞数 1

分类专栏：东方学习记录文章标签： sql 学习数据库

于 2022-09-18 23:18:44 首次发布

本文链接：https://blog.csdn.net/shishenshashen/article/details/126925044

版权

东方学习记录专栏收录该内容

5 篇文章 0 订阅

订阅专栏

东方学习SQL-task03 复杂一点的查询

一、视图

1.视图的定义

视图是一个虚拟的表，没有保存数据
依据SELECT语句来创建，其实是sql的一段逻辑映射
可以在这个虚拟表上做SQL操作
数据来源在真实表上

2.视图与表的区别

视图不是表，视图是虚表，视图依赖于表
视图没有保存实际的数据，而表保存了真实数据

3.视图的作用

数据保密性：不直接提供表的全部字段，给出一部分字段做视图对外提供有限的可查询部分
数据的冗余：创建视图来降低数据的冗余，只存一份全部数据，其他冗余用视图，相当于引用这个表的数据，而不再做存储
高频优化：把频繁使用的SELECT语句保存以提高执行效率
数据清晰：用户看到的数据更加清晰，可以理解为特定的筛选而不是全部放出来，分不清重点

4.视图的集中操作（增删改查）

4.1创建视图（增）

基本语法

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


#举例说明
CREATE VIEW productsum
(
    product_type,
    cnt_product
)AS 
SELECT product_type,cnt_procuct
FROM product
;

注意事项
- 视图名与表名一样，在数据库中是唯一的，不能与其他视图和表重名，不然逻辑不好确定，不知道这个视图到底是哪个来源
- 视图中的列需要与select子句中的字段顺序一一对应，数量一致。把对应位置的列传给视图的列，顺序不一致则视图中字段展示的值会有影响，字段个数不一致会报错
- 视图不仅可以根据表来建，还可以根据视图来建
- 不过多重视图会降低sql性能，尽量避免这个操作

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nkHtHnNf-1663514217065)(C:\Users\WDF\AppData\Roaming\Typora\typora-user-images\1663467381997.png)]$

有些数据库建视图不支持order by 语句，因为order by 字段，是根据字段的值来排序的，而视图和表严格来说不按照值来排序，按照索引来。所以最好不要用

#基于单表的视图
#1.在product上建立一个视图
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;
 #2.查询一下这个视图
 select * from productsum;

在这里插入图片描述

#多表视图

#1.新建一个表来学习多表视图
CREATE TABLE shop_product
(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));

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0001',	30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0002',	50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0003',	15);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0002',	30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0003',	120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0004',	20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0006',	10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0007',	40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0003',	20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0004',	50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0006',	90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0007',	70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D',	'福冈',		'0001',	100);

#2.在proudct表和 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;

#3.看一下这个视图
select * from view_shop_product;

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5nbpy2t9-1663514217066)(C:\Users\WDF\AppData\Roaming\Typora\typora-user-images\1663469725501.png)]$

#根据视图做sql操作-查询
SELECT sale_price, shop_name
  FROM view_shop_product
 WHERE product_type = '衣服';

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r2WBseAg-1663514217067)(C:\Users\WDF\AppData\Roaming\Typora\typora-user-images\1663469826923.png)]$

4.2修改视图结构（改）

#修改视图语句
1.直接修改
ALTER VIEW <视图名> AS <select 语句>;
2.创建新视图替换
create or replace view <视图名>(列1，列2，列3) AS <SELECT 语句>
这个语句会把新的逻辑替换旧逻辑，查询内容是新逻辑的

#案例
#原视图
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;
 
 #替换视图
 create or replace view productsum(product_type,cnt_product,product_tpyes)
 as
 select product_type,count(*),product_type
 from product
 group by product_type;
 
 #名字是一样的，关键词 replace 替换
 替换后视图结构是第二个视图的样子

替换视图结果
在这里插入图片描述

#修改视图更加方便 alter

ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';
#第三次修改，现在视图结构如下图所示

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XGLUBM8P-1663514217069)(C:\Users\WDF\AppData\Roaming\Typora\typora-user-images\1663470672510.png)]$

4.3 视图更新理解（对数据操作）-不建议操作，数据会造成不一致，引发质量问题

视图是虚拟表，对视图操作其实是对底层基础表的数据操作
在修改时候只有满足底层基本表的定义才能成功
因为视图只是源表的一个窗口，即使修改成功呢可能只有一部分数据完成修改，其他值可能不变，因此风险很大会造成问题
生产上（数据质量问题）是严格保证数据安全的，禁用视图修改

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

只有一部分数据更新，为了数据质量，禁用视图更新

4.4删除视图

删除一般需要权限（删表，删视图都需要权限）
删成功了，再删会报错，就跟创建是一个原理

#语法：
DROP view <视图名>;
drop view productsum;

二、子查询

1.子查询定义

select 语句嵌套就是子查询
select 子句先计算，子查询结果给到外层做查询用，可以附带过滤条件，可以多个表
一般用括号把子查询包裹起来，加上别名，相当于临时表
不同于视图，视图是可以存储的（结构和逻辑），而子查询的临时结果只能用一次，没有进行存储
再用再写，或者用with 表达式固定下来

#结构
select * from 
（	select 
		xx 
	from b [可以做关联 join c on ...]
）as a
;

#案例
SELECT stu_name
FROM (
         SELECT stu_name, COUNT(*) AS stu_cnt --括号里面就是子查询
          FROM students_info
          GROUP BY stu_age
	) AS studentSum  --表的别名
          ;

2.嵌套子查询

#嵌套子查询--多重子查询

判断句子范围：几个from ，from后面就是

SELECT product_type, cnt_product
FROM (SELECT *
        FROM (SELECT product_type, 
                      COUNT(*) AS cnt_product
                FROM product 
               GROUP BY product_type
             ) AS productsum  --1层
       WHERE cnt_product = 4	
     ) AS productsum2;		  
     
     
其中
最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，
第二层查询中将个数为4的商品查询出来，
最外层查询product_type和cnt_product两列。
虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。
      
其实工作中不必太在乎有多少层，能够识别范围就行

子查询嵌套的话，太多影响阅读，看不懂
嵌套太多执行效率变差

2.标量子查询（返回单一值的子查询，用的还比较少）

标量就是单一的意思，那么标量子查询也就是单一的子查询
单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。交叉定位就是一个值。

product_id | product_name | sale_price 
------------+-------------+----------
0003       | 运动T恤       | 4000 
0004       | 菜刀          | 3000 
0005       | 高压锅        | 6800

那么我们执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果。

标量子查询的作用
- 因为是单一值，可以用在where做过滤条件
```
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;
```
- 单一值，可以理解为就是一个字段，值是计算的结果，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

3.关联子查询（有点复杂）

通过一些标志把内层和外层两个查询连接起来达到过滤数据目的
可以理解select 两个子句是join 关系 on条件

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);
   
# where 后面跟了一个子查询充当过滤条件，但是子查询里面还有where把外面的表和子查询里面的表的一些条件做了关联
总的来说是根据 product_type 关联 并过滤出 sale_price 满足大于 AVG(sale_price) 的所有情况

执行结果

#案例
#查询出销售单价高于平均销售单价的商品
SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);
 
过滤条件是所有类别的商品价格，大于总的平均价格

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z5ERFuZd-1663514217071)(C:\Users\WDF\AppData\Roaming\Typora\typora-user-images\1663512319416.png)]$

#选取出各商品种类中高于该商品种类的平均销售单价的商品
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);
   
   过滤条件是 各个商品类型的单价高于对应product_type均价的 是分组之后各自均价

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c0iNTIxJ-1663514217071)(C:\Users\WDF\AppData\Roaming\Typora\typora-user-images\1663512505382.png)]$

在第二条SQL语句也就是关联子查询中我们将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询。

但是如果刚接触的话一定会比较疑惑关联查询的执行过程，这里有一个博客讲的比较清楚。在这里我们简要的概括为：

首先执行不带WHERE的主查询
根据主查询讯结果匹配product_type，获取子查询结果
将子查询结果再与主查询结合执行完整的SQL语句
在子查询中像标量子查询，嵌套子查询或者关联子查询可以看作是子查询的一种操作方式即可。

关联子查询详细介绍 https://zhuanlan.zhihu.com/p/41844742

三、常见函数

1.函数的定义及分类

sql自带函数，类似一个黑盒子，输入值，给出返回的值。
函数分类
- 算数函数（用来进行数值计算的函数）
- 字符串函数（用来进行字符串操作的函数）
- 日期函数（用来进行日期操作的函数）
- 转换函数（用来转换数据类型和值的函数）
- 聚合函数（用来进行数据聚合的函数）
总共有超过200个，常用的30-50个需要记住，其余的查文档即可

2.算数函数

为了演示其他的几个算数函数，在此构造samplemath表
-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m NUMERIC(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; -- 提交事务
-- 查询表内容
SELECT * FROM samplemath;
+----------+------+------+
| 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 |
+----------+------+------+
11 rows in set (0.00 sec)

函数详解

为了演示其他的几个算数函数，在此构造samplemath表
-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m NUMERIC(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; -- 提交事务
-- 查询表内容
SELECT * FROM samplemath;
+----------+------+------+
| 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 |
+----------+------+------+
11 rows in set (0.00 sec)

函数详解

1.ABS() --绝对值
语法：ABS( 数值 )
ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。
当 ABS 函数的参数为NULL时，返回值也是NULL。

2.MOD（） -- 求余数
语法：MOD( 被除数，除数 )
MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。

3.ROUND（） -- 四舍五入
语法：ROUND( 对象数值，保留小数的位数 )
ROUND 函数用来进行四舍五入操作。
注意：当参数 `保留小数的位数` 为变量时，可能会遇到错误，请谨慎使用变量。

SELECT m,
    ABS(m)ASabs_col ,
    n, p,
    MOD(n, p) AS mod_col,
    ROUND(m,1) AS round_col
FROM samplemath;

在这里插入图片描述

3.字符串函数

字符串函数也经常被使用，为了学习字符串函数，在此我们构造samplestr表。
-- DDL ：创建表
USE  shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
(str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
-- DML：插入数据
START TRANSACTION;
INSERT INTO samplestr (str1, str2, str3) VALUES ('opx',	'rt', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('太阳',	'月亮', '火星');
INSERT INTO samplestr (str1, str2, str3) VALUES ('aaa',	NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc哈哈',  'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');
COMMIT;
-- 确认表中的内容
SELECT * FROM samplestr;
+-----------+------+------+
| str1      | str2 | str3 |
+-----------+------+------+
| opx       | rt   | NULL |
| abc       | def  | NULL |
| 太阳      | 月亮 | 火星 |
| aaa       | NULL | NULL |
| NULL      | xyz  | NULL |
| @!#$%     | NULL | NULL |
| ABC       | NULL | NULL |
| aBC       | NULL | NULL |
| abc哈哈   | abc  | ABC  |
| abcdefabc | abc  | ABC  |
| micmic    | i    | I    |
+-----------+------+------+
11 rows in set (0.00 sec)

函数详解

1.CONCAT() -- 拼接
语法：CONCAT(str1, str2, str3)
MySQL中使用 CONCAT 函数进行拼接

2.LENGTH() -- 字符串长度
语法：LENGTH( 字符串 )

3.LOWER() -- 小写转换
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。

4.upper()
类似的， UPPER 函数用于大写转换。

5.REPLACE() -- 字符串的替换
语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

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

扩展
REPEAT -- 字符串按需重复多次
语法：REPEAT(string, number)
该函数用来对特定字符实现按需重复。
Example：
mysql> SELECT REPEAT('加油！',3);
+-----------------------------+
| REPEAT('加油！',3)          |
+-----------------------------+
| 加油！加油！加油！          |
+-----------------------------+
1 row in set (0.00 sec)

4.日期函数

大部分DBMS都支持的日期函数，特定的查阅文档

1.CURRENT_DATE -- 获取当前日期
SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2020-08-08   |
+--------------+
1 row in set (0.00 sec)

2.CURRENT_TIME -- 当前时间
SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 17:26:09     |
+--------------+
1 row in set (0.00 sec)

3.CURRENT_TIMESTAMP -- 当前日期和时间
SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2020-08-08 17:27:07 |
+---------------------+
1 row in set (0.00 sec)

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

在这里插入图片描述

5.转换函数

“转换”这个词的含义非常广泛，在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为cast；另一层意思是值的转换。

1.CAST -- 类型转换
语法：CAST（转换前的值 AS 想要转换的数据类型）
-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
+---------+
| int_col |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

需要特别注意的是，当要转换为整型时，需要指定为 SIGNED（有符号） 或者 UNSIGNED（无符号）

-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;
+------------+
| date_col   |
+------------+
| 2009-12-14 |
+------------+
1 row in set (0.00 sec)


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

四、谓词

4.1 谓词的定义

返回值为真值的函数。包括TRUE/FALSE/UNKNOWN

常见谓词

LIKE
BETWEEN
IS NULL
IS NOT NULL
IN
EXISTS

谓词详解

LIKE谓词 --用于字符串的部分一致查询
当需要进行字符串的部分一致查询时需要使用该谓词。
部分一致大体可以分为前方一致、中间一致和后方一致三种类型。
首先我们来创建一张表

-- DDL ：创建表
CREATE TABLE samplelike
( strcol VARCHAR(6) NOT NULL,
  PRIMARY KEY (strcol)
);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplelike (strcol) VALUES ('abcddd');
INSERT INTO samplelike (strcol) VALUES ('dddabc');
INSERT INTO samplelike (strcol) VALUES ('abdddc');
INSERT INTO samplelike (strcol) VALUES ('abcdd');
INSERT INTO samplelike (strcol) VALUES ('ddabc');
INSERT INTO samplelike (strcol) VALUES ('abddc');
COMMIT; -- 提交事务
SELECT * FROM samplelike;
+--------+
| strcol |
+--------+
| abcdd  |
| abcddd |
| abddc  |
| abdddc |
| ddabc  |
| dddabc |
+--------+
6 rows in set (0.00 sec)

五、CASE表达式

待补充完善

六、习题练习

#练习第一部分

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 sale_price >=1000 and regist_date='2009-09-20'

select * from viewpractice5_1;

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z44tsqp9-1663514217072)(C:\Users\WDF\AppData\Roaming\Typora\typora-user-images\1663508720151.png)]$

#练习 3.2
向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果？为什么？

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

#作答：会报错，视图不能进行插入，如果想修改的话 只能进行update set
并且只有一部分值变化，不推荐


#练习3.3
请根据如下结果编写 SELECT 语句，其中 sale_price_avg 列为全部商品的平均销售单价。

3.3作答 --标量子查询常量做字段
select 
product_id,
product_name,
product_type,
sale_price,
(select avg(sale_price) as sale_price_avg from product ) sale_price_avg
from product

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lm1Gv5QB-1663514217073)(C:\Users\WDF\AppData\Roaming\Typora\typora-user-images\1663510130210.png)]$

#习题3.4
请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。
条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。

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

#3.4作答
create view AvgPriceByType (
 product_id,proudct_name,product_type,sale_price,sale_price_avg_type)
as 
select
product_id,product_name,p1.product_type,sale_price,sale_price_avg_type
from product p1 
join 
(select product_type,avg(sale_price) as sale_price_avg_type from product 
group by product_type) p2
on  p1.product_type=p2.product_type


#方法二
select 
product_id,
product_name,
product_type,
sale_price,
 (select avg(sale_price) from product as p2
 where p1.product_type=p2.product_type
 group by product_type)as sale_price_avg_type
from product p1


select * from avgpricebytype;