Day8 SQL语句尾篇

1、视图

视图：将一个复杂的select语句进行封装，以更方便使用，实现了代码复用。
视图中存储的是select语句，而不是结果集数据；视图可以进行增、删、改，但是尽量不要对视图进行这些操作，视图主要进行查询操作。
语法：create/alter view 名称 as select ...

示例：创建视图，名称为StudentInfo_ClassInfo；使用、删除视图

create view StudentInfo_ClassInfo as select StudentInfo.*,ClassInfo.cTitle from
StudentInfo inner join ClassInfo on StudentInfo.cid=ClassInfo.cId where
StudentInfo.sGender=1
select * from StudentInfo_ClassInfo--使用视图
drop view StudentInfo_ClassInfo--删除视图

2、子查询

将一个查询语句嵌入另一个查询语句中，称这种查询为子查询。
常用运算符：= in exists
in与exists的差别：数据比较大时，exists效率比in高。
示例：查询表ClassInfo中的列cId与表StudentInfo中cid匹配的数据

select * from ClassInfo where cId in (select cid from StudentInfo)
select * from ClassInfo where exists (select * from StudentInfo where
StudentInfo.cid=ClassInfo.cId)

3、分页

已知：页大小、页索引
起始索引：(pageIndex-1) × pageSize+1
结束索引：pageIndex × pageSize
示例：已知页大小：4，页索引：2，则起始页：5，结束页：8

select * from (select *,ROW_NUMBER() over(order by sId desc) as rowindex from 
StudentInfo) as t1 where rowindex between 5 and 8

4、数据透视case

数据透视完成数据的行列转换。
示例：将视图StudentView中列cid对应的男、女列出来，如果值为null，则列出无，并且按照cid从小到大排序

create view StudentView as select cid,sGender,sName from StudentInfo--创建视图
StudentView
select cid,case when sGender=0 then sName else '无' end as 男,case when sGender
=1 then sName else '无' end as 女 from StudentView order by cid
select cid,sGender,sName from StudentInfo

结果：

5、Transact-SQL编程

t-sql编程是只适用于SQL server的语句，即SQL特有语句。

5.1、变量

声明：declare 变量名类型（变量名要求以@开头）
设置：set/select 变量名=值
输出：print/select 变量名
示例：

declare @name nvarchar(10) --声明
set @name='油条' --设置
print @name --输出

内置全局变量：使用双@符号
@@version：数据库版本
@@identity：进行插入后调用，返回最近的insert语句的标识值
@@servername：服务器名称
@@error：返回执行的上一个Transact-SQL语句的错误号，如果没有返回0
@@rowcount：返回受上一句语句影响的行数

select @@VERSION --查看当前数据库版本
insert into ClassInfo values('四不像');select @@IDENTITY --查看最近的insert语句的
标识值
select @@SERVERNAME --查看当前服务器名称
print @@error --查看上一个语句的错误号
print @@rowcount --查看上一个语句的影响行数

5.2、选择语句if

语法：
if 条件
begin
--满足条件执行
end
else begin
--不满足条件执行
end
示例：输出OK

declare @id int
set @id=10
if @id>4
begin 
    print('Ok')
end
else begin
    print('NO')
end

5.3、循环语句while

语法：
while 逻辑表达式
begin
循环体
end
示例：

declare @id int
set @id=1
while @id<10
begin
    print(@id)
    set @id=@id+1
end

5.4、异常处理语句

语法：
begin try
--可能出错的语句
end
begin catch
--捕获后执行的语句
end catch
示例：

begin try
    delete from ClassInfo --出错
end try 
begin catch
    print @@error
end catch

5.5、事务

事物保证了数据的准确性、有效性和可恢复性；当一个事件有多种操作，而其中一个操作出现了问题，那么事务可以让事件回到做之前的状态，保证了事件所有操作正确完成。
只有数据改变（增加、修改、删除）时才会引发事务，查询不会引发事务。

分类：
显式事务（需要手动控制）
隐式事务（不需要手动控制，默认采用此方式）
显式事务语法：
begin transaction --开始事务
commit transaction --提交，没错后执行
rollback transaction --回滚，出错后执行
示例：因为操作2出错，因此回滚事务，所以回到最开始，操作1也不会执行

begin try
    begin transaction --开始事务
    delete from StudentInfo where sId>8  --操作1正确
    delete from ClassInfo --操作2错误
    commit transaction --提交，没错执行
end try 
begin catch
    rollback transaction  --回滚事务，出错执行
end catch

锁模型
锁：当对数据进行修改操作时，将自动添加锁，防止多个人员同时操作数据产生数据错误。