MYSQL学习视图与函数(三)

最新推荐文章于 2022-10-28 13:11:30 发布

shangdixhlz

最新推荐文章于 2022-10-28 13:11:30 发布

阅读量203

点赞数

分类专栏： SQL 文章标签： mysql sql

本文链接：https://blog.csdn.net/shangdixhlz/article/details/115832656

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SQL学习Task03

任务：
- 了解什么是视图,子查询,应用场景分别是哪些
- 怎么做视图的增删改
- 怎么创建标量,嵌套和关联子查询
- 了解算数函数、字符串函数、日期函数、转换函数的基本用法
- 了解LIKE,BETWEEN,IS NULL、IS NOT NULL、IN、EXIST的用法
- 了解CASE表达式的基本用法和应用场景
本次任务涉及的表

product表

CREATE TABLE product( product_id CHAR(4) NOT NULL,product_name VARCHAR(100) NOT NULL,product_type VARCHAR(32) NOT NULL,sale_price INTEGER,purchase_price INTEGER,regist_date DATE,PRIMARY KEY(product_id) ) ;
insert into product values('0001','T-shirt','cloth',1000,500,'2009-09-20'); 
insert into product values('0002','punch','office',500,320,'2009-09-11'); 
insert into product values('0003','sport t-shirt','cloth',4000,2800,NULL); 
insert into product values('0004','knife','cook',3000,2800,'2009-09-20'); 
insert into product values('0005','pressure cooker','cook',6800,5000,'2009-01-15'); 
insert into product values('0006','fork','cook',500,NULL,'2009-09-20'); 
insert into product values('0007','chopping board','cook',880,790,'2008-04-28'); 
insert into product values('0008','ballpen','office',100,NULL,'2009-11-11'); 
COMMIT;

shop_product表

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', 'tokyo', '0001', 30); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', 'tokyo', '0002', 50); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', 'tokyo', '0003', 15); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', 'nagoya', '0002', 30); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', 'nagoya', '0003', 120); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', 'nagoya', '0004', 20); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', 'nagoya', '0006', 10); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', 'nagoya', '0007', 40); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', 'osaka', '0003', 20); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', 'osaka', '0004', 50); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', 'osaka', '0006', 90); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', 'osaka', '0007', 70); 
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D', 'fukuoka', '0001', 100);

samplemath表

Use shop;
CREATE TABLE samplemath (m float(10,3), n INT, p INT);
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;

samplestr表

USE shop; 
DROP TABLE IF EXISTS samplestr; 
CREATE TABLE samplestr (
str1 VARCHAR (40), 
str2 VARCHAR (40), 
str3 VARCHAR (40) 
);
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 ('sum', 'moon', 'mars'); 
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 ('abchaha', 'abc', 'ABC'); 
INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC'); 
INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I'); 
COMMIT;

shopproduct表

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) -- 指定主键 
);
START TRANSACTION; -- 开始事务 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', 'tokyo', '0001', 30); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', 'tokyo', '0002', 50); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', 'tokyo', '0003', 15); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', 'nagoya', '0002', 30); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', 'nagoya', '0003', 120); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', 'nagoya', '0004', 20); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', 'nagoya', '0006', 10); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', 'nagoya', '0007', 40); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', 'Osaka', '0003', 20); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', 'Osaka', '0004', 50); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', 'Osaka', '0006', 90); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', 'Osaka', '0007', 70); 
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', 'fukuoka', '0001', 100); 
COMMIT; -- 提交事务

1 了解什么是视图,子查询,应用场景分别是哪些

1.1 简介

视图：通过select语句从真实的表中提取数据并按照select语句呈现的一张虚拟的表,在视图中的操作不会影响真实的表。
视图的应用场景：

定义视图可以避免频繁使用同一个select语句,从而提高效率。
定义视图可以让用户看到有针对性的数据。
定义视图不必将所有字段呈现,可以增强数据保密性。
定义视图因为是虚拟的表,不对真实的表产生影响,避免数据冗余。
子查询：即包含在select查询语句中的select查询。先执行子查询再将子查询的结果作为外部select查询的条件。
子查询的应用场景：
通常跟在外部select语句的where后充当条件。
where条件比较的对象是公式。

1.2 怎么做视图的增删改查

创建视图（基于单表,基于多表）
# create view <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
其中select后查询的列名排列顺序要和视图后面的列名排列顺序一致。且视图名称在数据库中是唯一的,不能和其他视图或表重名。

创建基于单表的视图

CREATE VIEW productsum (product_type, cnt_product) 
AS 
SELECT product_type, COUNT(*) FROM product 
GROUP BY product_type ;

单表视图

创建基于多表的视图:product表与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;

多表视图

查询视图

基于view_shop_product查询。

# select sale_price,shop_name from view_shop_product where product_type=’cloth’;
// 查询产品类型为衣服的商品的价格、名称,查询的视图表名是view_shop_product

查询视图

修改视图结构

类似于删除视图重建。
# alter view <视图名> AS <SELECT语句>

修改视图：productsum表

ALTER VIEW productsum 
AS 
SELECT product_type, sale_price FROM product 
WHERE regist_date > '2009-09-11';

修改视图
- 更改结构前
修改结构前
- 更改结构后
修改结构后

更新视图内容

当视图包含以下任何一种都不能被更新：
- 聚合函数 sum(),min(),max(),count()等。
- DISTINCT 关键字
- GROUP BY
- HAVING
- UNION 或者 UNION ALL运算符
- FROM多个表

更新视图：productsum

# UPDATE productsum SET sale_price='5000' 
WHERE product_type='office';

更新视图

查看基本表的变化

注意:因为视图的数据来自于基本表,所以这里修改视图意味着原表的数据也可以改变,但不建议通过视图修改原表的数据。

1.3 怎么创建标量,嵌套和关联子查询

标量子查询

标量就是单一的意思,也就是要求select查询只能返回表中具体的某一个单元格中数据。

查询售价高于平均售价的商品

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

标量子查询

查询注册日期最晚的商品

 SELECT product_id, product_name, regist_date 
FROM product 
WHERE regist_date=(SELECT max(regist_date) FROM product);

最晚日期标量子查询

在select子句中添加标量子查询：
由于标量子查询返回单一值的特性,使得标量子查询语句可以放在select子句,group by子句,having子句和order by子句等可以使用常数和列名的地方

# SELECT product_id, product_name, sale_price, 
(SELECT AVG(sale_price) FROM product) AS avg_price 
FROM product;

标量子查询3

嵌套子查询

多层嵌套

# SELECT product_type, cnt_product 
FROM (SELECT * 
FROM (SELECT product_type, COUNT(*) AS cnt_product 
FROM product 
GROUP BY product_type) AS productsum 
WHERE cnt_product=4) AS productsum2;

*查询由内到外：
先根据product_type分组计算商品个数,将计数后的列名叫做cnt_product,将查询结果命名为productsum表(product_type,cnt_product)。
外层select查询productsum表中个数(cnt_product)=4的商品信息
最外层select查询查询productsum表中个数(cnt_product)=4的商品的类型和个数形成productsum2表。

嵌套子查询随着层数的叠加,其可理解度和执行效率都会大大降低,不建议多层嵌套。

关联子查询

即子查询的条件中包含外部查询的字段,子查询表和外部查询表的某个字段进行比较

选取每个商品种类中,高于自己商品种类的平均销售单价的商品
首先我们的第一想法是用子查询,先查询销售平均值,再利用外部查询提取大于平均值的商品：

# select product_type,product_name,sale_price
From product
where sale_price >(select avg(sale_price) as avg_sale_price 
from product);

关联子查询1

不过上述的方法都只是查询单价高于所有商品的平均价的商品,怎么才能筛选每个种类内单价高于同类的平均价的商品呢？那就需要一种方法去阐述条件：我只要OFFICE种类中的单价大于OFFICE种类均值的商品,where product_type=’office’:

# select product_name,product_type,sale_price
From product
where sale_price >(select avg(sale_price) as avg_sale_price 
from product
where product_type=’office’);

使用这种方法代表有几种类型就要执行几次命令,太麻烦了,有没有一种方法能让他自动对应各个类型？让product_type=product_type,用关联子查询试试看吧

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

上述关联子查询的语句查询流程是：
（1）外部查询不带where条件：
SELECT product_type, product_name, sale_price FROM product;
关联子查询2

（2）# SELECT product_type,AVG(sale_price) FROM product AS p2 GROUP BY product_type
关联子查询3

（3）# SELECT product_type,AVG(sale_price) FROM product AS p2 WHERE p1.product_type=p2.product_type GROUP BY product_type;得出商品类型为cloth,平均值为2500的结果
关联子查询4

（4）# SELECT product_type,product_name,sale_price FROM product where sale_price>2500 and product_type=’cloth’;
关联子查询5

（5）之后重复（2）,（3）,（4）的操作
关联子查询6

2 了解算数函数、字符串函数、日期函数、转换函数的基本用法

2.1 算数函数

+ - * /
ABS绝对值
ABS函数用来计算一个数字的绝对值,当ABS函数的参数为NULL值时,返回值也是NULL。
MOD求余数
MOD(被除数,除数)用于求余数,只能对整数列求余。
主流DBMS都支持MOD函数,只有sql server不支持该函数,其使用%符号来计算余数。
ROUND四舍五入
ROUND(对象数值,保留小数的位数)用来进行四舍五入操作。
示范：

# SELECT m,ABS(m) AS abs_col,n,p,
MOD(n,p) AS mod_col,
ROUND(m,1) AS round_colS 
FROM samplemath;

函数2.1

2.2 字符串函数

CONCAT函数
CONCAT(str1,str2,str3)用于拼接字符串。
LENGTH函数
LENGTH(字符串) 用于测量字符串的长度。
LOWER
LOWER函数只能针对英文字母使用,将大写字母转换成小写。
REPLACE
REPLACE(对象字符串,替换前的字符串,替换后的字符串)用于替换某个字符串。
SUBSTRING
SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)用于截取某一段字符串。
SUBSTRING_INDEX – 字符串按索引截取
SUBSTRING_INDEX(原始字符串,分隔符,计数)如果计数是正数,那么就是从左往右数,第N个分隔符的左边的全部内容,相反,如果是负数,那么就是从右边开始数,第N个分隔符右边的所有内容,例如：
substring_index(www.wikibt.com,'.',2);
结果：www.wikibt
substring_index(www.wikibt.com,'.',2);
结果：wikibt.com
如果我要中间的字符串如何？那么从两个方向截取：
先截取从右到左第二个小数点以右的字符串：wikibt.com,再截取从左往右小数点以左的字符串：wikibt
substring_index(substring_index(www.wikibt.com,'.',-2),'.',1);

2.3 日期函数

CURRENT_DATE函数
select CURRENT_DATE;获取当前日期,格式为YYYY-MM-DD
CURRENT_TIME函数
select CURRENT_TIME;获取当前时间,格式为hh:mm:ss
CURRENT_TIMESTAMP函数
select CURRENT_TIMESTAMP;获取当前日期和时间。
EXTRACT 函数
EXTRACT(日期元素 FROM 日期)可以截取出日期数据的一部分,例如“年”“月”,“小时”等,其返回值是数值类型,非日期类型：

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;

2.4 转换函数-涉及表：samplemath,product

CAST（转换前的值 AS 想要转换的数据类型）
示范：
基本表：

将M列数值转换为整型：
select cast(m as integer) from samplemath;
转换函数1

COALESCE( expression,value1,value2……,valuen)
判断查询的值是否为空,如果为空值则指定返回的值
示范：
查询 product_name,sale_price,purchase_prise和regist_date,如果purchase_price不为空则返回purchase_price的值,如果为空,则返回sale_price的值,如果sale_price的值为空则返回100；

Select product_name,sale_price,purchase_prise,regist_date,
COALESCE(purchase_price,sale_price,’100’) as purchaseprice
from product;

转换函数2

3 了解LIKE,BETWEEN,IS NULL、IS NOT NULL、IN、EXIST的用法

3.1 LIKE 模糊匹配

'%'匹配任意一段连续的字符
'_'匹配某一个字符
示范：

查询注册时间为2008年的商品
select * from product where regist_date LIKE ‘2008%’;
查询注册时间为2009年10月之前的20号的数据
select * from product where regist_date LIKE ‘2009-0_-20’;

3.2 BETWEEN 描述某个范围之间的值

示范：

查询销售价格介于500至900之间(包含500和900)的商品信息
select * from product where sale_price LIKE ‘500’ and ‘900’;

模糊匹配3

3.3 IS NULL、IS NOT NULL 空值/非空值

示范：

查询注册日期为空的商品
select * from product where regist_date IS NULL;
查询注册日期不为空的商品
select * from product where regist_date IS NOT NULL;

3.4 IN 取某集合中的值

IN相当于OR的简便用法,不用重复执行OR函数。
示范：

查询商品类型为cloth或office的商品
select * from product where product_type IN (‘cloth’,’office’);
IN与子查询语句结合
查询shopproduct表shop_id为000c的商品名称和销售价格。这个查询涉及两个表shopproduct表和product表,用两个表都有的共同字段product_id 进行关联,先查询shopproduct表shop_id为000c的product_id,再查询product表中对应product_id的商品信息。

SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
  	FROM shopproduct
   	WHERE shop_id='000C');

集合2

3.5 NOT IN 表示某个集合以外的值

查询方式和IN一样。

3.6 EXISTS 判断是否存在满足某条件的记录

示范：
EXISTS通常用关联子查询作为参数。在这个案例中是以product_id作两个表之间的关联。

用 exists选取在osaka门店在售商品的销售单价。

SELECT product_name, sale_price FROM product AS p 
WHERE EXISTS (SELECT * FROM shopproduct AS sp 
WHERE sp.shop_id='000C' AND sp.product_id=p.product_id);

存在1

也可以这样查询

SELECT product_name, sale_price FROM product AS p,shopproduct AS sp 
WHERE sp.shop_id='000C' 
AND sp.product_id=p.product_id);

存在2

4 了解CASE表达式的基本用法和应用场景-product表

4.1 根据不同分支得到不同列值

CASE实现效果,添加表中记录不存在的A:,B:等字符串并将A:、B: 和C:与记录结合起来

A ：cloth 
B ：office 
C ：cook

注：END一定不能漏掉。

SELECT product_name, 
CASE WHEN product_type='cloth' THEN CONCAT('A:',product_type) 
WHEN product_type='office' THEN CONCAT('B:',product_type) 
WHEN product_type='cook' THEN CONCAT('C:',product_type) 
ELSE NULL 
END AS abc_product_type 
FROM product;

case函数

4.2 实现列方向上的聚合

聚合函数+CASE WHEN表达式可呈现行列转换效果。

正常聚合函数实现行方向上不同种类的聚合。

SELECT product_type, SUM(sale_price) AS sum_price FROM product 
GROUP BY product_type;

case函数2

实现列方向上展示不同种类的聚合值

select  SUM(CASE WHEN product_type=’cloth’ THEN sale_price else 0 end) as sum_price_cloths,
 SUM(CASE WHEN product_type=’cook’ THEN sale_price else 0 end) as sum_price_cook,
 SUM(CASE WHEN product_type=’office’ THEN sale_price else 0 end) as sum_price_office
from product;

case函数3

实现行转列
基本表：

行转列：

select product_name,SUM(CASE WHEN product_type=’cloth’ THEN sale_price else 0 end) as sum_price_cloths,
 SUM(CASE WHEN product_type=’cook’ THEN sale_price else 0 end) as sum_price_cook,
 SUM(CASE WHEN product_type=’office’ THEN sale_price else 0 end) as sum_price_office
from product
Group by product_name;

case函数行转列

TASK 3练习题

对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
①
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);
②
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

答：
①查询成本不是500、2800、5000的商品。
②查询成本不是500、2800、5000和空值的商品。

按照销售单价（ sale_price）对product（商品）表中的商品进行如下分类。
- 低档商品：销售单价在1000日元(包含1000)以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
- 中档商品：销售单价在1000日元以上3000日元以下（菜刀）
- 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
  答：

select (case when sale_price <1001 then product_name else null end) as low_price,
(case when sale_price between 1001 and 3000 then product_name else null end) as low_price,
(case when sale_price >3000 then product_name else null end) as low_price
from product;

练习2.1

请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

答：

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

练习2.2