DataSet的内容介绍，两种单例模式（并发量的考虑），SQL高级中的case语句、连接操作、子查询、派生表
————————————————————————————————
1.Case的用法
使用方法一：（类似C#中的case的用法）
->语法：
Case 表达式
when 值1 then 返回值
when 值2 then 返回值
…
End
->案例：把用户表中数据，全部查询出来，要求把DelFlag=0的显示成未删除
select *,case DelFlag
when 0 then N'未删除'
when 1 then N'删除'
end as 删除状态
,UserName
from
UserInfo
使用方法二：（类似C#中的多个if else）
->语法
Case
when 表达式 then 返回值
when 表达式 then 返回值
else 值
->案例：把用户表中数据全部查询出来，要求把DelFlag=0的显示成删除
select *,case
when DelFlag=0 then N'未删除'
when DelFlag=1 then N'删除'
else N'未知'
end as 删除状态
,UserName
from
UserInfo
2.补充：ISNULL（表达式，替换的值）
select CreateDate,ISNULL(CreateDate,getdate()) from UserInfo
3.SQL控制语句
->SQL中的变量
->定义变量：
->declare @变量名 变量类型
->给变量赋值：
->set @参数名=值
->select @参数名=值
select @i=count(1) from UserInfo;
->打印 ： Print @变量名
->IF ELSE
->语法格式：
if(表达式)
begin
SQL语句
end
else
begin
语句
end
->WHILE
->语法：
While(表达式)
begin
SQL语句
end
4.数据的连接
主键不参与业务逻辑
->交叉连接
一张表中有M条数据，另一张表中有N条记录，那么交叉连接后就是M*N
->内连接
一张表中有M条数据，另一张表中有N条记录，那么内连接后的数据不确定条数，要根据外键去讨论
->外连接
select * from 表1 as t1 right jion 表2 as t2 on t1.列名=t2.列名;
表示将表1和表2通过“列名”作为对应关系进行连接，如果有数据没有相关对应，就看中间的连接方式right/left/full，如果是right就以右侧的数据为主，如果是left就以左侧的数据为主，如果是full就全部都显示，没有对应就补null；
5.子查询
需要将一个一个查询的结果作为另一个查询的条件
通常，将里面的查询叫做子查询，外面的查询叫做外部查询
注意：子查询内部 必须查询一个数据
返回结果
单值（一个单元格） 标量子查询（常常放在where子句中作为条件，或者在select中作为一个值）
多值 多值子查询
行（略）
列 一般放在in中作为批量删除、修改等操作或查询
表 一般作为数据源进行再一次检索
6.表表达式
->派生表
select top 30
t1.stuId as 编号
, t1.stuName as 姓名
, case t1.stuSex when 'm' then '男' else '女' end as 性别
, t3.final as 期末总评
, t2.className as 课程
, t1.stuPhone as 电话
, t1.stuAddress as 家庭住址
from
TestDataBase..Student as t1
inner join
TestDataBase..Course as t2
on t1.classId = t2.classId
inner join
(select
stuId
, avg(testBase+testBeyond+testPro) as final
from
TestDataBase..Score
group by
stuId) as t3
on t1.stuId = t3.stuId
order by
t3.final desc;
其中，将结果集作为一张表来使用的应用就叫做派生表
实际上这张表是不存在的，
汇编语言：机器型语言
高级语言：描述性语言
第三种语言：任务型语言
表表达式的意思是表参与运算得到的结果还是表，常用的表表达式：
->派生表
->分页（重要）
引出：淘宝京东购物网站，第一页显示不了 ，会分页显示
select * from Student;
一共有6万条数据，一次显示会崩溃，所以分页显示
问题：每页有M条记录，要显示第N页，如何实现？
其实不好写，凡是遇到这种有规律性问题的时候，你需要给自己设置具体的数字，按照数字的规律先写个三遍、四遍的结果，从中找到结果；
每页10条，第1页
select top 10 * from TestDabase..Student;
每页10条，第2页
select * from TestDatabase..Student where stuId between 11 and 20;
每页10条，第3页
select * from TestDatabase..Student where stuId between 21 and 30;
……
规律：
每页M条，第N页
select * from TestDatabase..Student
where stuId between M*(N-1) and M*N;
如果不连续，那么可以考虑加一个列，让其连续
--row_number() over(order by 字段)
->公用表表达式（CTE）
-- 公用表表达式(CTE， common table expression)
-- 语法
/*
with 别名
as
(
结果集
)
紧跟查询
*/
with t
as
(
select
row_number() over(order by stuid) as num
, stuId
, stuName
, stuSex
, stuBirthdate
, stuStudydate
, stuAddress
, stuEmail
, stuPhone
, stuIsDel
, stuInputtime
, classId
from
TestDataBase..Student
where
stuIsDel = 0
)
select -- 紧跟查询
num
, stuId
, stuName
, stuSex
, stuBirthdate
, stuStudydate
, stuAddress
, stuEmail
, stuPhone
, stuIsDel
, stuInputtime
, classId
from t
where t.num between (10-1)*9+1 and 9*10;
->视图（View）
可持久化的派生表
create view vw_FenYeable --视图名需要以vw开头
因为必须是批处理执行的语句 ，所以需要在首尾加go
go
create view vw_FenYeable
as
select
row_number() over(order by stuid) as num
, stuId
, stuName
, stuSex
, stuBirthdate
, stuStudydate
, stuAddress
, stuEmail
, stuPhone
, stuIsDel
, stuInputtime
, classId
from
TestDataBase..Student
where
stuIsDel = 0
go
select * from vw_FenYeable where vw_FenYeable.num between 9 * 10 + 1 and 10 * 10;
--视图无法存储数据，本质上还是查询，目的是更好的帮助我们进行查询。如果每次查询一个复杂操作的时候，选择使用视图
->内联表值函数（*不做要求）
-- 就是带有参数的“视图”
-- 语法
/*
create function fn_函数名
(@参数名 as 类型, @参数名 as 类型, ...)
returns table
as
return
结果集
*/
定义时：
go
create function fn_FenYe
(@pageCount as int, @pageIndex as int)
returns table
as
return
select
num
, stuId
, stuName
, stuSex
, stuBirthdate
, stuStudydate
, stuAddress
, stuEmail
, stuPhone
, stuIsDel
, stuInputtime
, classId
from vw_FenYeable
where
vw_FenYeable.num
between (@pageIndex - 1) * @pageCount + 1 and @pageCount * @pageIndex;
go
使用时：
select * from fn_FenYe(20, 9);
-- 将结果集进行运算，得到一个新的结果集，并将其作为数据源进行查询
7.表的透视变换
将原始给的横表显示成竖排方式：
select
学号
, sum(case when 课程='语文' then 成绩 else 0 end) as '语文'
, sum(case when 课程='数学' then 成绩 else 0 end) as '数学'
, sum(case when 课程='英语' then 成绩 else 0 end) as '英语'
from
Score
group by 学号
8.事务
在SQL Server中，默认的每一条语句都是一个事务 。
begin transaction
事务内容
->生效
commit transaction
->不生效
rollback transaction
如何知道事务是否生效？
@@rowcount 记录上条语句受影响的行数
@@error 如果有错记录错误编码
事务：一个整体，要么全部执行成功，要么全部执行失败
严格的定义：如果一个操作满足原子性、持久性、隔离性与一致性，那么这个操作称为一个事务
原子性：不可划分，要么全部成功要么全部失败
持久性：一旦事务执行完成，不可销毁
隔离性：如果事务在执行操作时还未完成，另外一个事务需要执行这个操作相关时需要等待（阻塞）
一致性：一旦事务完成，不管在文件中、数据文件中、内存中的所有数据都是一样的
如果严格遵循事务的操作，会很影响性能但是提高安全性
->隔离性的实现机制：锁
数据文件分块存储 文件存储模型：文件块
颗粒度：是锁定一行数据还是锁定一页数据就叫做颗粒度
共享锁（读）、排他锁（增删改）
->事务隔离级别
未提交读、已提交读、可重复读、序列化读、快照
9.存储过程 procedure
将SQL语句封装起来，就像C#中的方法一样
意义：将一个执行的过程(可能会很复杂)封装成一个名字，然后使用这个名字就可以执行这个过程
语法：
create peoc[edure] 存储过程名字
参数 as 类型 [默认值|output] --此处的as可以省略
as
begin
代码
end
e.g.
go
create proc usp_test1
as
begin
begin transaction
declare @myError int;
update bank set balance=balance - 900 where cid='0002'
set @myError = (select @@ERROR);
update bank set balance=balance + 900 where cid='0001'
set @myError += (select @@ERROR);
if(@myError = 0)
begin
commit
end
else
begin
rollback
end
end
go
->执行存储过程
exec usp_test1;
->带参数的存储过程
go
create proc usp_test2
@from as char(4)
, @to as char(4)
, @money as money
as
begin
begin transaction
declare @myError int;
update bank set balance=balance - @money where cid=@from
set @myError = (select @@ERROR);
update bank set balance=balance + @money where cid=@to
set @myError += (select @@ERROR);
if(@myError = 0)
begin
commit
end
else
begin
rollback
end
end
go
->执行
exec usp_test2 '0001', '0002', -900;
exec usp_test2 @to='0002', @money=100, @from='0001';
->上述执行是否成功不能看到，需要有返回值的存储过程
go
create proc usp_test3
@from as char(4)
, @to as char(4)
, @money as money
, @isSuccess int output -- 与C#一模一样，在存储过程内部赋值即可
as
begin
begin transaction
declare @myError int;
update bank set balance=balance - @money where cid=@from
set @myError = (select @@ERROR);
update bank set balance=balance + @money where cid=@to
set @myError += (select @@ERROR);
if(@myError = 0)
begin
commit
set @isSuccess = 1;
end
else
begin
rollback
set @isSuccess = 0;
end
end
go
->执行
但是这里虽然能显示我们的结果，但是如果出错结果为0但是还是会报错，怎么才能让它不报错呢？
->在SQL Server中的Try-catch
go
create proc usp_test4
@from as char(4)
, @to as char(4)
, @money as money
, @isSuccess int outputas
begin
begin transaction
begin try
update bank set balance=balance - @money where cid=@from
update bank set balance=balance + @money where cid=@to
commit
set @isSuccess = 1;
end try
begin catch
rollback
set @isSuccess = 0;
end catch
end
go
存储过程是为了用一个名字代替一串Sql语句，传输会快，解析不需要也会快
所以不管是大公司还是小公司都会用存储过程，但是也有的公司会要求全部用C#的逻辑来写
->系统存储过程
exec sp_help;
->默认参数的存储过程
go
create proc usp_FenYe1
@pageIndex int = 1 -- 第几页
,@pageCount int = 10-- 每页条数
as
begin
select * from fn_FenYe(@pageCount, @pageIndex);
end
go
10.触发器
现在来讲，用触发器的机会还不多
触发器是什么？
是一个特殊的存储过程，如果用C#来比喻的话本质还是方法
不是自己调用，而是因为执行某一个错做二自动的触发，就像事件
使用
->监视操作
（可以记录操作的时间、人物……）
->补充约束
（约束只能在一张表里，如果跨越多张表还要对字段进行约束的话可以考虑进行约束）
->语法
create trigger tr_类型_触发器的名字 on 表名
触发类型:after(之前)|instead of(代替,一般不用)
操作类型:insert|delete|update
as
begin
代码
end
两张临时表
inserted和deleted
11.逻辑与流程控制
-- 逻辑处理与流程控制
-- 选择结构
/*
if 表达式
begin
end
else if 表达式
begin
end
else
begin
end
*/
-- 循环结构
/*
while 表达式
begin
end
*/
-- 定义变量
declare @num int;
-- 为变量赋值
set @num = 123;
-- 使用
select @num;
-- 求1到100的和
declare @sum int;
declare @i int;
set @sum=0;
set @i = 0;
while @i <= 100
begin
set @sum = @sum + @i;
set @i = @i + 1;
end
select @sum;
go
declare @sum int;
declare @i int;
set @sum=0;
set @i = 0;
while @i <= 100
begin
if @i % 2 = 0
begin
set @sum = @sum + @i;
end
set @i = @i + 1;
end
select @sum;
go
-- 系统变量常常使用@@来引导
select @@VERSION
select @@ERROR;
