WonderfulSQL-task3

WonderfulSQL-task3

(Datawhale28期组队学习)

基础点

• 视图
  • 与表区别
  • 创建
  • 修改结构
  • 更新内容
  • 删除
• 子查询
  • 与视图关系
  • 嵌套
  • 标量
  • 关联
• 函数
  • 算数函数
  • 字符串函数
  • 日期函数
  • 转换函数
• 谓词
  • like（字符串部分一致查询）
  • between（范围）
  • is null | is not null
  • in
  • exist
• case表达式

知识点

1 视图

虚拟的表，根据Select语句生成一张虚拟表，进行SQL操作。

1.1 与表区别

是否保存了实际的数据

• 视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上
• 视图保存的是select语句，使用的临时表。表保存真实数据
• 视图不是表，视图是虚表，视图依赖于表
  • 可以保存频繁使用的SELECT语句，提高效率
  • 更清晰的看数据
  • 不公开所有字段，保密
  • 降低数据冗余

1.2 创建

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

# 单表
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type;
 
 # 多表
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

#在视图上查询：
SELECT sale_price, shop_name
FROM view_shop_product
WHERE product_type='衣服';

注：

• 尽量避免创建多重视图，会降低SQL性能
• 定义视图时不能使用ORDER BY语句
  • 数据行是没有顺序的
  • MySQL中视图的定义是允许使用 ORDER BY 语句的，若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略

1.3 修改结构

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

• 视图名在数据库中需要是唯一的，不能与其他视图和表重名

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

1.4 更新内容

• 视图是对底层基础表的操作，修改需要满足底层基础表的定义
• 原表可以更新，视图也可。（数据一致性）
• 包含多种结构不可修改：
  • 聚合函数：sum/avg/min/max/count
  • distinct去重关键字
  • group by 分组语句
  • having过滤语句
  • union/union all运算符
  • from子句包含多个表

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

视图

1.5 删除

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

2子查询

一个查询语句嵌套在另一个查询语句内部的查询。子查询作为外层另一个查询的过滤条件，查询可以基于一个表或多个表。

2.1 与视图关系

• 将SELECT语句直接用于FROM子句中，AS xx可以做别称
• 不会想视图那样保存在存储介质，在select语句执行后即消失

2.2 嵌套

• 子查询可以多层嵌套
• 但是子查询嵌套层数过多会影响SQL语句的执行效率，并且难以理解

2.3 标量子查询

单一的子查询：执行的SQL语句只能返回一个值，即表中具体的某一行的某一列。
用处：

• 使用单一值的位置都可以用标量子查询，使用常数或列名的地方均可（select、group by、having、order by）

2.4 关联子查询

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

3 函数

3.1 算数函数

用于数值计算

+ - * /
ABS()
MOD()
ROUND()(当参数 保留小数的位数 为变量时，可能会遇到错误)

3.2 字符串函数

用于字符串操作

concat(str1,str2,str3)
length(str)
lower(str)
upper(str)
replace(tar, str1,str2)
substring()(截取的起始位置从字符串最左侧开始计算，索引值起始为1)
SUBSTRING_INDEX – 字符串按索引截取
SUBSTRING_INDEX (原始字符串， 分隔符，n)
REPEAT – 字符串按需重复多次
REPEAT(string, number)

3.3 日期函数

用来日期操作

CURRENT_DATE – 获取当前日期
CURRENT_TIME – 当前时间
CURRENT_TIMESTAMP – 当前日期和时间
EXTRACT – 截取日期元素

3.4 转换函数

转换数据类型和值

CAST – 类型转换
COALESCE – 将NULL转换为其他值

3.5 聚合函数

用于数据聚合

4谓词

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

4.1 like（字符串部分一致查询）

字符串的部分一致查询

前方：%是代表“零个或多个任意字符串”的特殊符号，LIKE 'ddd%'代表“以ddd开头的所有字符串”
中间：查询对象字符串中含有作为查询条件的字符串，无论该字符串出现在对象字符串的哪个位置，LIKE ‘%ddd%’
后方：即作为查询条件的字符串（这里是“%ddd”）与查询对象字符串的末尾部分相同

_下划线匹配任意 1 个字符（占位置）

4.2 between（范围）

between X and Y（闭区间）
如果不想让结果中包含临界值，那就必须使用 < 和

4.3 is null | is not null (判断是否为null)

选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL| is not null

4.4 in| not in

多个查询条件取并集时可以选择使用or语句
选取的对象多，用IN(值1, 值2, 值3, ......)代替or
not in
使用IN 和 NOT IN 时是无法选取出NULL数据的（null只能用 IS NULL 和 IS NOT NULL 来进行判断）
可以用子查询做其参数
使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，不用一直更新sql语句，并且手工维护工程量过大

4.5 exist | not exist

判断是否存在满足某种条件的记录，存在True，不存在False
EXIST（存在）谓词的主语是“记录”
EXIST 通常会使用关联子查询作为参数
EXIST 可以替换 IN， NOT IN 也可以替换NOT EXIST

5 case表达式

CASE 表达式是函数的一种，条件分支
简单CASE表达式和搜索CASE表达式

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

• 根据不同分支得到不同列值
• 实现列方向上的聚合：聚合函数 + CASE WHEN 表达式
• 实现行转列：聚合函数 + CASE WHEN

练习

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

#3.2
#会报错；视图插入数据，实际上是给原表插入数据，原表要求product_id, product_name, product_type不为空。

#3.3
select product_id, product_name, product_type,sale_price, 
(select avg(sale_price) from product) as sale_price_all
from product
order by product_id;

# 3.4
create view AvgPriceByType
as
select product_id, product_name, product_type,sale_price, 
(select avg(sale_price) from product group by product_type) as avg_sale_price
from product
order by product_id;

#3.5
#不会

#3.6
#(1)找出价格不是500,2800,5000的商品的名称及价格
#(2)取空，not in谓词参数不可以有null，否则查询结果为空

#3.7
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 > 3001 then sale_price else null end) as high_price
from product;

参考

• https://github.com/datawhalechina/wonderful-sql