本笔记为阿里云天池龙珠计划SQL训练营的学习内容,链接为:https://tianchi.aliyun.com/specials/promotion/aicampsql
Task03复杂查询方法-试图、子查询、函数
一、视图
视图并不是数据库真实存储的数据表,它可以看作是一个窗口,通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质,即视图是基于真实表的一张虚拟的表,其数据来源均建立在真实表的基础上。
为什么需要视图:
- 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
- 通过定义视图可以使用户看到的数据更加清晰。
- 通过定义视图可以不对外公开数据表全部字段,增强数据的保密性。
- 通过定义视图可以降低数据的冗余。
创建视图
create view 视图名(列1,列2,...) as (select 语句)
注意:1. 视图名在数据库中需要唯一,不能与其他视图或数据表重名
2. 视图不仅可以基于真实表,还可以在视图的基础上继续创建视图,但我们尽量避免
3. 一般视图中不能使用order by,MySQL允许,但是在自身语句含order by时,视图的order by 被忽略
示例:pandas 是基于NumPy 的一种工具,该工具是为了解决数据分析任务而创建的。
修改视图
alter view 视图名 as select语句
更新视图
因为视图是一个虚拟表,所以对视图的操作就是对底层基础表的操作,在修改时只有满足底层基础表的定义才能修改成功。
对于一个视图来说,如果包含以下结构的任意一种是不可以被更新的:
- 聚合函数
- distinct关键字
- group by子句
- union或者union all
- from 子句中包含多个表
视图的更新有时只会对原表的部分更新,容易引起不一致,一般在创建表时使用限制不允许通过视图更新。
update 视图名
set...;
删除视图
drop view 视图名;
二、子查询
子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。而且由于子查询是一次性的,所以子查询不会像视图那样保存在存储介质中, 而是在 SELECT 语句执行之后就消失了。
1.嵌套子查询
select a,b,c
from (select *
from (select(...)));
虽然嵌套子查询可以查询出结果,但是随着子查询嵌套的层数的叠加,SQL语句不仅会难以理解而且执行效率也会很差,所以要尽量避免这样的使用。
2.标量子查询
标量就是单一的意思,那么标量子查询也就是单一的子查询,那什么叫做单一的子查询呢?
所谓单一就是要求我们执行的SQL语句只能返回一个值,也就是要返回表中具体的某一行的某一列。
关联子查询
where连接两个查询
SELECT product_type, product_name, sale_price
FROM product ASp1
WHERE sale_price > (SELECT AVG(sale_price)
FROM product ASp2
WHERE p1.product_type =p2.product_type
GROUP BY product_type);
三、函数
1. 算术函数
用来计算数值:+,-,*,/
- ABS():绝对值,当参数为null时,返回值也是null
- MOD(被除数,除书):求余数,只能对整数求余,除数不能为0
- ROUND(数值,保留小数位数),四舍五入
2. 字符串函数
- CONCAT(str1,str2),字符串拼接
- CONCAT_ws(str1,‘seperator’,str2)或CONCAT_ws(‘seperator’,str1,str2),指定连接符
- group_cancat(concat_ws()),具体有点忘了
- LENGTH(str):字符串长度
- LOWER(str):小写转化
- UPPER(str):大写转换
- REPLACE(str, ‘str里要替换的部分’ , ‘替换后的部分’)
- SUBSTRING(str from i for j):从第i个字符开始截取j个
- substring_index(str,分隔符,n):第n个分隔符之前或之后的字符串
3. 日期函数
- 当前日期:current_date
- 当前时间:current_time
- 当前日期和时间:current_timestamp
- ectract(year from 日期):截取日期元素,返回的是数值不是日期
4.转换函数
- CAST(转换前的值 as 想要转换的数据类型):类型转换
- COALESCE(数据1,数据2,…),返回从左侧开始第一个不是null的值,用来将null转换为其他值
谓词
谓词就是返回真值的函数
- LIKE,like ‘_ab%’, %代表零个或多个任意字符串,_代表一个字符
- BETWEEN,范围查询,闭区间
- IS NULL, IS NOT NULL
- IN
- EXISTS
case 表达式
case when ... then ...
when ... then ...
else ...
end