上周给大家分享了一部分《SQL基础教程》学习笔记，今天将剩下部分继续分享给大家。
5 复杂查询
5.1 视图
从sql角度，视图和表是相同，区别在表中保存的实际数据，而视图保存是select语句(视图本身不存储数据)
使用视图，可以轻松完成跨多表查询数据等复杂操作
可以将select语句当成视图来使用，创建视图用create view,删除视图用drop
view
视图包含"不能使用order by"和"对其进行有限制的更新"，实际上视图保存的是select语句
视图的优点
无需保存数据，可以节省存储设备的容量
频繁的使用select语句保存成视图，不用每次都重新书写了，将进程使用的select做成视图
--创建视图
create view
as
<select语句>;
--视图可以使用在select语句的from子句中，并且视图会自动更新
--创建视图
create view
as
<select语句>;
--视图可以使用在select语句的from子句中，并且视图会自动更新
多重视图
在视图的基础上创建视图，但尽量避免在视图的基础上创建视图，多重视图会降低SQL的性能
视图的限制
定义视图时不要使用order by子句
因为视图和表一样，数据行都是没有顺序的
对视图进行更新
在select语句中视图可以和表一样使用，但是对于insert、delete、update这类更新语句满足了一定条件可以被更新
由于使用视图来保存原表的汇总数据时无法判断将视图更改反映到原表中的，例如将一行数据插入视图中，但是原表中的编号等信息不清楚，无法保证数据的一致性
视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新
- select子句中未使用distinct
- from 子句中只有一张表
- 未使用group by子句
- 未使用having子句
删除视图
drop view ;
5.2 子查询
- 子查询就是一次性视图(select语句)，与视图不同，子查询在select语句执行完毕就会消失
- 首先执行内层查询，再执行外层查询
- 子查询必须设定名称，尽量从处理内容的角度出发为子查询设定恰当的名称，使用as关键字，也可省略
标量子查询 标量就是单一的意思，在数据库之外的领域也经常使用，标量子查询就是必须且只能返回一行一列的结果，由于只返回单一值，所以标量子查询的返回值可以用在"="或"<>"这样需要单一值比较的运算符之中， 5.3 关联子查询
关联子查询会在细分的组内进行比较时使用
使用关联资产时，通常会使用"限定"或"限制"这样的语言
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
);
6 函数、谓词、case表达式
6.1 各种各样的函数
函数就是输入某一值得相应输出结果的功能，输入值称为参数，输出值称为返回值
算术函数
--算术函数除了常见的四则运算(+-x/)，介绍常见一些函数，numeric是大多数DBMS都支持的一种数据类型，参数是null,返回值也是null
select round(1123.26723,2);
--结果：1123.27 四舍五入
select abs(-1)
--结果：1 绝对值
select mod(7,3)
--结果：1 取余
字符串函数
concat() --拼接字符串
length() --在sql server中无法使用，sql server中使用len()函数，汉子占两个字符，英文字母占一个
--在MySQL中有length()和char_length()
lower() --只对英文字母使用，将参数中的字符串全部转化成小写字母，upper() --将小写转化成大写
replace(对象字符串，替换前的字符串，替换后的字符串)
substring(对象字符串 from 截取的位置 for 截取的字符个数) --可以截取字符串一部分
日期函数
current_date--当前日期
current_time--当前时间
current_timestamp--挡墙的日期和时间
extract()函数--截取日期函数，返回值并不是日期型，而是数值类型
转换函数
在sql中有两层意思：
一是数据类型的转换，简称类型转换，在英语中称为cast；
另一层的意思是值的转换；
- cast函数--类型转换
cast(转换前的值 as 想要转换的数据类型)
select cast("ooo1" as interger)- coalesce函数 --将null转换为其他值
--coalesce是sql特有的函数，返回可变参数中左侧第一个不是null的值
coalesce(数据1，数据2，数据3...)
6.2 谓词
谓词是需要满足特定条件的函数，该条件就是返回值是真值。
返回值可能是数字、字符串或者日期，但是谓词的返回值就是真值(True/False/Unknown)。
这就是谓词和函数的最大区别。
- like --字符串的部分一致查询
-- 前方一致
select * from SampleLike where strcol like "ddd%"; --代表"0个字符以上的字符串"
-- 中间一致
select * from SampleLike where strcol like "%ddd%"; --代表"0个字符以上的字符串"
-- 后方一致
select * from SampleLike where strcol like "%ddd";
--使用%和_(下划线)进行后方一致查询
select * from SampleLike where strcol like "ddd_ _"; --进行ddd+任意两个字符- between --范围查询
- is null 、is not null --判断是否是null
--为了选出某些值为null的列的数据，不能使用=，使用特定的谓词is null，不是null的is not null
- in --or的谓词
--选取出在范围的数据，还有not in不在范围的数据，两者都无法选取出null数据
- exists
-- 谓词的作用就是"判断是否存在满足某种条件的记录"，存在这样的记录返回True，否则返回False 6.3 case表达式
case表达式分为简单case表达式和搜索case表达式，简单表达式包含在搜索表达式内，只介绍搜索case表达式
case表达式是在区分情况时使用的，在编程中称之为(条件)分支，是相当于sql中的if语句
case when then -- 类似于"列=值"
when then
when then
.
.
else --指定了不满足when子句中的条件的记录，null之外其他值或表达式都可写在else中
end --不能省略
搜索case表达式
select product_name,
case when product_type = "衣服"
then "A":'|| product_type'
when product_type = "办公用品"
then "B":'|| product_type'
when product_type = "厨房用品"
then "C":'|| product_type'
else null
end as abc_product_type
from Product;
--
简单case表达式
select product_name,
case product_type --写过一次，不用再写
when "衣服" then "A":'|| product_type'
when "办公用品" then "B":'|| product_type'
when "厨房用品" then "C":'|| product_type'
end as abc_product_type
from Product;
--
经典案例
select product_name,product_price
from Product
where purchase_price not in (500,2800,5000)；
--结果如下，没有含有null的两行，是因为通常的谓词都无法与null比较
product_name purchase_price
打孔器 320
擦菜板 790
select product_name,product_price
from Product
where purchase_price not in (500,2800,5000，null)；--结果是什么都没有，使用子查询作为not in 的参数时，子查询的返回值也不能是null
7 集合运算
7.1 表的加减法
集合在数学领域表示"(各种各样)事物的总和"，在数据库领域表示记录(行)的集合，集合运算是对满足同一规则的记录的进行加减等四则运算。
用来进行集合运算的运算符称为集合运算符。
表的加法--union
select product_id,sale_price from Product
union
select product_id,sale_price from Product2;
注意事项：
作为运算对象的记录的列数必须相同
作为运算对象的记录中列的类型必须一致，如果要使用不同的类型，可以使用类型转换函数cast
可以使用select语句，但是order by子句只能在最后使用一次
包含重复行的集合运算--all选项
包含重复行的只需只需在union后加all即可
select product_id,sale_price from Product
union all
select product_id,sale_price from Product2;
选取表中公共部分--intersect
--intersect应用于两张表中，选取出公共记录
select product_id,sale_price from Product
intersect
select product_id,sale_price from Product2
order by product_id;
记录的减法--except
select product_id,sale_price from Product
except
select product_id,sale_price from Product2
order by product_id;
--减法运算的减数和被减数位置的不同，得到的结果也不同 7.2 联结(以列为单位进行联结)
联结(join)就是将其他表中的列添加过来。 联结大致分为内连接和外联结。 之前的union和intersect都是以行方向为单位进行操作。 union增加行，intersect减少行。 内联结(inner join) from 子句 之前from都是一个表，而这次同时使用两张表，使用inner join将两张表联结过来 on子句 注意on后边的联结条件，指定两张表联结使用的列(联结键)，使用内连接必须使用on子句，书写在from和where之间 外联结(outer join) 外联结包括做联结和右联结，外联结使用left和right来指定主表，使用left时，from子句中left左边的表是主表，使用right时，from子句中right右边的表是主表。 3张表以上的联结 -- 联结的from子句中再次使用使用inner join(outer join) 将其他表也添加进来 交叉联结--cross join(笛卡尔积) -- 进行交叉联结时无法使用内联结和外联结中所使用的on,交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录通常是两张表行数的集合。 8 SQL高级处理
8.1 窗口函数
窗口函数可以进行排序、生成序列号等一系列的聚合函数无法实现的高级操作
窗口函数也称为OLAP函数(online analytical processing 实时分析处理)，例如进行市场分析、创建财务报表、创建计划等日常商务工作。
over ([partition by ]
order by )
--能够作为窗口函数的函数
--聚合函数(sum,avg,count,max,min)
--rank,dense_rank,row_number等专用窗口函数
语法的基本使用方法--使用rank函数
select product_name,product_type,sale_price,
rank() over (partition by product_type
order by sale_price) as ranking
from Product;
-- partition by 能够设定排序的对象范围，本例中设定了商品的种类进行排序
--order by指定了按照哪一列，何种顺序进行排序，与select语句中末尾的order by一样，可以通过关键字asc/desc来指定升序或者降序
partition by在横向上对表进行分组，而order by决定了纵向的排序规则。
通过partition by分组后的记录合称为窗口。
省略了partition by相当于一个大的窗口。
窗口函数兼具group by子句分组和排序的功能，但是不具有group by子句汇总的功能
专用创建函数的种类
rank函数
计算排序时，如果存相同位次的记录，则会跳过之后的位次
dense_rnak函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次
row_number函数
赋予唯一的连续位次(同价也是不同位次)
窗口函数的适用范围
窗口函数只能在select中使用，能在order by中使用是因为order by在select之后执行的
作为窗口函数使用的聚合函数
所有的聚合函数都能用作窗口函数
计算移动平均值
窗口函数就是以窗口为单位进行分割，并在其中进行排序的函数，其实还有包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架
select product_id,product_name,sale_price,
avg(sale_price) over (order by product_id
rows 2 preceding) as moving_avg
from Product;
-- 这里使用row("行")和preceding("之前")两个关键字，将框架指定为"截止~行"，因此rows 2 preceding这就是截止2之前两行，响应的“之后”是following -- 由于框架是根据当前记录来确定，因此和固定的窗口不同，其范围会随着当前的记录的变化而变化 --如果是当前记录的前后作为汇总对象 rows between 1 preceding and 1 following --前中后三行 两个order by 在窗口函数中也有一个order by，但是这个order by只是决定窗口函数按照什么样的顺序进行计算的，对结果的排序没有影响。 要想最后按照顺序排序，需要在select语句最后，使用order by子句进行指定； 8.2 GROUPING运算符
只使用group by子句和聚合函数是无法同时得到小计和合计的，可以用grouping实现
理解grouping运算符中的cube关键在于形成“积木搭建出的立方体”的印象
grouping 运算符包含以下是三种：
rollup(卷起的意思)、cube、grouping sets
同时得到合计行
rollup--同时得到合计和小计
select product_type,sum(sale_price) as sum_price
from Product
group by rollup(product_type);
--使用了rollup时多出了最上方的合计和个分类的小计
grouping--让null更加容易分辨
该函数在其参数列的值为超级分组记录所产生的null时返回1，其他情况返回0
select grouping(product_type) as product_type,
grouping(regist_date) as regist_date,
sum(sale_price) as sum_price
from Product
group by rollup(product_type,regist_date);
--结果 product_type regist_date sum_price 1 1 16780 0 0 11180 0 0 880 cube--用数据来搭积木(立方体的意思) cube语法和rollup相同，只需将rollup换成cube即可，rollup结果一定包含在cube中 所谓cube就是将group by子句中的聚合键的“所有可能的组合”的汇总结果集中到一个结果中 grouping sets--取得期望的积木 该运算符可以用于从rollup或者cube中取出部分记录 ——End——