SQL 进阶
-------------------------SQL 进阶-------------------------
1.case 语句的用法:
a) 用法一 :-->直接对列进行值判断(类似于C#中的Switch case)
注意: then后返回的值的类型必须一致(因为列的类型只有一种)
Case 列名
When 条件1 then '自定义1';
When 条件2 then '自定义2';
When 条件3 then '自定义3';
Else ' 默认'
End
b) 用法二:是用来对表达式 case 输出结果,一般是用来为 列的取值范围(但不一定非得用 列)
Case
When 表达式1 then ' 类型名1'
When 表达式2 then ' 类型名2'
When 表达式3 then ' 类型名3'
Else ''类型名4'
End
2.子查询 : 在已经搜索的结果集上 再 进行查询操作.(将一个查询语句 作为一个结果集)
a) 注意: 一定要给结果集 取别名.
b) 查询关键字 in, not in ,exists,not exists
c) Exists用法: --->类似 in 关键字
i. In关键字 先查询子查询 再进行 主查询
ii. Exists关键字 先查询 主查询,再进行 子查询 ,将匹配的数据显示出来 举例:
select * from PhoneNum where exists(select * from PhoneType where PhoneNum.pTypeId=PhoneType.ptId)
iii. 判断结果集是否为null
3.----any all---- 用法: 匹配结果集使用
4.注意 : in/ all/ any/ 匹配结果集使用时 , 结果集只能有一个列
a) Any : where typeId = any ( 1,2,3 )
b) All : where typeId = all ( 1,2,3 )
5.只有返回 且 仅返回一行,一列数据的子查询 才能当成单值子查询
6.子查询在以下条件时只能是一个列: 子查询跟随在 =,!=,<,>,之后,或者子查询作为表达式.
7.------------------------分页查询语句----------------------------
Row_number() over(order by id) 根据id的升序生成了一列新的连续的 序号
--1.使用排序分页缺点:查询次数多,查询数据量大
select * from (
select top 5 * from (select top 10 * from area order by ar_id ASC) as t order by ar_id desc
) as c order by ar_id asc
--2.not in 缺点:查询数据量大
--第n页
select top 5 * from Area where ar_id not in(select top 5 ar_id from Area)
--3.between and 缺点:如果id不连续(1,3,4,5),则会出现查出来的页里不满行的情况
select top 5 * from Area--第一页
select * from Area where ar_id between 0 and 4
select * from Area where ar_id between 5 and 9
select * from Area where ar_id between 10 and 14
--4.ROW_NUMBER 结合between and
--ROW_NUMBER() over (order by ar_id) 根据ar_id的升序生成了一列新的连续的序号
select * from(
select ROW_NUMBER() over (order by ar_id) as rnum,* from Area
) as t
where rnum between 10 and 14--按照ROW_NUMBER()生成的序号来分页
-----------------------------表连接--------------------------------
8.内连接: inner join on
a) Inner Join on --- >From T1 join T2 on T1.id=T2.cid where 条件
b) Join必须跟在 from 表名之后,内连接,只会将两张表中,符合 on 条件的行 拼接显示出来
c) 需要多表连接时,只需 进行 多个inner join on 连接即可
9.左外连接 left outer join on(以左表为主) :
a) 显示join左边表的所有记录,右侧表中 符合条件的显示,不符合条件的显示NULL.
10.右连接 right outer join on (以右表为主)
b) 显示join右边表的所有记录,左侧表中 符合条件的显示,不符合条件的显示NULL.
11. 交叉连接 cross join (两张表的乘积)
12. 全连接 Full join on (不管两边是否满足条件,全都显示,对方如果没有对应的数据,则显示NULL)
--------------------------------视 图---------------------------
1.视图是一张虚拟表, 表示一张表的部分数据 或 多张表的综合数据,其结构和数据时建立在对表的查询基础上
2.对视图做 新增 修改 删除 操作的时候,只能针对一个 基表的列,不能同时更新或新增多个基表的列, 不提倡对视图进行新增修改操作,因为视图存在的目的就是 方便查询.
3.视图在操作上和数据表没有什么区别,但两者的差异是其本质不同 : 数据表是实际存储记录的地方,然而视图并不保存热河记录
4.视图的目的:方便查询,所以一般情况下 不能删除
5.创建视图 : create view 视图名 as (连接查询)
5.视图优点:
a) 筛选表中的行
b) 防止未经许可的用户访问敏感数据
c) 降低数据库的复杂程度
6.使用视图 注意事项:
a) 视图中的查询不能使用order by, 除非指定了top语句
i. 视图被认为是一个虚拟表,表示一个集合,是不能有顺序的,而by则返回的是一个有顺序的,是一个游标.
ii. 在视图中使用select top 10 percent +order by 问题
7.创建视图的sql语句:
create view vw_StusInfo
as
select top 50 percent s.id, s.name as stuName ,c.Name as className,s.age
from Student as s
inner join Class as c
on s.cid = c .Id
order by stuName asc
8.-查询视图
select * from vw_StusInfo where id>10
--于视图等价的子查询
select * from
(
select s.id,s.name as stuName ,c.Name as className,s.age
from Student as s
inner join Class as c
on s.cid = c .Id
) as t
where id>10
