开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 东方学习SQL-task03 复杂一点的查询 -> 正文阅读

[大数据]东方学习SQL-task03 复杂一点的查询

东方学习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;