SQLServer与ADO.Net（六）
---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流！ ----------------------
1、CASE函数用法
计算条件列表并返回多个可能结果表达式之一。
CASE具有两种格式：
1）简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
2）CASE 搜索函数计算一组布尔表达式以确定结果。
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
注：使用类似switch-case；表达式逻辑类似与if-else if；then 后数据类型要一致。
示例:
select
SNameas 姓名,
case
whenSAge < 10 then '儿童'
whenSAge < 18 then '少年'
whenSAge < 45 then '青年'
whenSAge < 60 then '中年'
else'老年'
endas 年龄,
caseSGender
when1 then '男'
else'女'
endas 性别,
SRemarkas 备注
from
T_PersonInfo
2、索引Index(*)
l 全表扫描：对数据进行检索（select）效率最差的是全表扫描，就是一条条的找。
l 如果没有目录，查汉语字典就要一页页的翻，而有了目录只要查询目录即可。为了提高检索的速度，可以为经常进行检索的列添加索引，相当于创建目录。
l 创建索引的方式，在表设计器中点击右键，选择“索引/键”→添加→在列中选择索引包含的列。
l 使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、Update、Delete的速度。只在经常检索的字段上(Where)创建索引。
l （*）即使创建了索引，仍然有可能全表扫描，比如like、函数、类型转换等。
3、子查询
将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。
l -> Select出来的结果是一个结果集
l -> Select后面一定需要一个数据源
l -> 凡是需要数据源的地方就可使用结果集
l ->只有返回且仅返回一行、一列数据的子查询才能当成单值子查询
示例：
1）select Fname, Fage, Fgender, Fclass
from tStudent2012
where Fclass=(select Cid from tClass2012 where Cname='.Net')
2）select * from (select * from tStudent2012 where Fgender=1) as tbl
where tbl.Fage>60
l -> 如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合。可以使用in关键字代替=号
示例：
1）select sname, sage,sgender, sclass from T_Student201217 where sclass in
(select cid from T_Class201217 wherecname='.Net' or cname='Java' or cname='Php')
4、分页
l 数据库中分页的实现
• 每页5条数据
• select top 5 * from student where sId not in
(select top(5*(2-1)) sId from student order by sId desc)
order by sIddesc
l 上面是sql 2000以前的实现方式。SQLServer2005后增加了Row_Number函数简化实现。
• select * from
(select row_number() over (order by sId desc) as num,* fromstudent) as s
where s.num between 5*(3-1)+1 and 5*3
l 限制结果集。（ ROW_NUMBER不能用在where子句中，所以将带行号的执行结果作为子查询，就可以将结果当成表一样用了）：
• select * from
(selectrow_number() over (order by sId asc) as num,* from student) as s
where s.numbetween 6 and 10 order by sId asc
5、表连接Join
l Inner Join、Left Join、Right Join
l (*)Cross Join 交叉连接 笛卡尔积 其它连接的基础
select 表1.字段,表2.字段from 表1 inner join 表2 on 表1.外键=表2.主键
6、视图概述
l 回顾数据怎么存储的
l 视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
l 视图在操作上和数据表没有什么区别，但两者的差异是其本质是不同:数据表是实际存储记录的地方，然而视图并不保存任何记录，它存储的实际上是查询语句
l 相同的数据表，根据不同用户的不同需求，可以创建不同的视图（不同的查询语句）
l 视图的目的是方便查询，所以一般情况下不能对视图进行增删改
l 优点：
• 筛选表中的行
• 防止未经许可的用户访问敏感数据
• 降低数据库的复杂程度
7、局部变量_先声明再赋值
l 声明局部变量
DECLARE @变量名 数据类型
DECLARE @name varchar(20)
DECLARE @id int
l 赋值
SET @变量名 =值 --set用于普通的赋值
SELECT @变量名 = 值 --用于从表中查询数据并赋值
l 例如：
SET @name=‘张三’
SET @id = 1
SELECT @name = sName FROM student WHERE sId=@id
8、输出变量的值
l SELECT 以表格的方式输出,可以同时输出多个变量
l PRINT 以文本的方式输出，一次只能输出一个变量的值
• SELECT @name,@id
• PRINT @name
• PRINT @id
9、变量种类
l 局部变量：
• 局部变量必须以标记@作为前缀 ，如@Age int
• 局部变量：先声明，再赋值
l 全局变量（系统变量）：
• 全局变量必须以标记@@作为前缀，如@@version
• 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值
10、常用的全局变量
l @@error变量，在每次执行完SQL语句后，都会为@@error变量赋值，如果上次执行的SQL语句有错，则将@@errro赋值为一个不为0的值，否则（执行没错），则将@@error赋值为0.
• print 'SQLServer的版本'+@@VERSION
• print '服务器名称: '+@@SERVERNAME
• print ‘最后一次放生的错误号'+convert(varchar(5),@@ERROR)
• print @@identity
11、IF ELSE
IF(条件表达式)
BEGIN --相当于C#里的{
语句1
……
END --相当于C#里的}
ELSE
BEGIN
语句1
……
END
12、WHILE循环
WHILE(条件表达式)
BEGIN --相当于C#里的{
语句
……
continue
BREAK
END --相当于C#里的}
13、事务-为什么需要事务
如，转账问题：
假定钱从A转到B，至少需要两步：
• A的资金减少
• 然后B的资金相应增加
• update bank set balance=balance-1000 where cid='0001'
• update bank set balance=balance + 1000 where cid='0002'
--查看结果。
SELECT * FROMbank
注意约束：金额不能小于10 会出问题？ 银行不乐意吧
14、什么是事务(Transaction)
l 事务：同生共死
l 指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成，必须作为一个整体执行
l 这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行
l 语法步骤：
• 开始事务：BEGINTRANSACTION
• 事务提交：COMMITTRANSACTION
• 事务回滚：ROLLBACKTRANSACTION
l 判断某条语句执行是否出错：
• 全局变量@@ERROR；
• @@ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；
例：SET @errorSum=@errorSum+@@error
15、存储过程
l 存储过程---就像数据库中运行方法(函数)
l 和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。
l 前面学的ifelse/while/变量/insert/select 等，都可以在存储过程中使用
l 优点：
执行速度更快 – 在数据库中保存的存储过程语句都是编译过的
允许模块化程序设计– 类似方法的复用
提高系统安全性– 防止SQL注入
减少网络流通量– 只要传输 存储过程的名称
l 系统存储过程
• 由系统定义，存放在master数据库中
• 名称以“sp_”开头或”xp_”开头,自定义的存储过程可以以usp_开头。
l 自定义存储过程
• 由用户在自己的数据库中创建的存储过程
16、系统存储过程
17、创建存储过程
l 定义存储过程的语法
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 [= 默认值] [OUTPUT],
@参数n 数据类型 [= 默认值] [OUTPUT]
AS
SQL语句
l 参数说明：
• 参数可选
• 参数分为输入参数、输出参数
• 输入参数允许有默认值
l EXEC 过程名 [参数]
18、调用带参数的存储过程
l 无参数的存储过程调用：
Exec usp_upGrade
l 有参数的存储过程两种调用法：
EXEC usp_upGrade2 60,55 ---按次序
EXEC usp_upGrade2 @english=55,@math=60 --参数名
l 参数有默认值时：
EXEC usp_upGrade2 --都用默认值
EXEC usp_upGrade2 1 --第一个用默认值
EXEC usp_upGrade2 1,5 --不用默认值
19、存储过程中使用输出参数
l 输出参数关键字：OUTPUT
l declare @a int
exec usp_pp@canshu= @a output
print @a
20、通过ado.net执行存储过程
l 设置执行类型CommandType
l 设置参数类型Direction
System.Data.SqlClient.SqlCommandmyCommand = newSystem.Data.SqlClient.SqlCommand("P_Test",myConnection);
myCommand.CommandType=CommandType.StoredProcedure;
//添加输入查询参数、赋予值
myCommand.Parameters.Add("@Name",SqlDbType.VarChar);
myCommand.Parameters["@Name"].Value="A";
//添加输出参数
myCommand.Parameters.Add("@Rowcount",SqlDbType.Int);
myCommand.Parameters["@Rowcount"].Direction=ParameterDirection.Output;
myCommand.ExecuteNonQuery();
21、触发器
l 触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。
l 一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。
l 触发器是一个功能强大的工具，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。
l 那究竟何为触发器？在SQLServer里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。
l 常见的触发器有2种：after(for)、instead of,分别应用于Insert ,Update , Delete 事件(DML触发器)
(1）deleted表存放由于执行delete或update语句而要从表中删除的所有行。
在执行delete或update操作时，被删除的行从激活触发器的表中被移动（move）到deleted
表，这两个表不会有共同的行。
(2）inserted表存放由于执行insert或update语句而要向表中插入的所有行。
在执行insert或update事物时，新的行同时添加到激活触发器的表中和inserted表中，
inserted表的内容是激活触发器的表中新行的拷贝。
说明：update事务可以看作是先执行一个delete操作，再执行一个insert操作，旧的行首先
被移动到deleted表，让后新行同时添加到激活触发器的表中和inserted表中。
不能对视图定义 AFTER 触发器。
22、常用语法
CREATE TRIGGER triggerName ON 表名
For | after（只指定了for,默认为after触发器） | instead of
UPDATE|INSERT|DELETE（insert,update,delete）
AS
begin
…
end
23、触发器-插入
CREATE TRIGGER tr_updateStudent ON score
for INSERT
AS
Begin
declare @sidint,@scoreid int
select @sid =studentId,@ scoreid=sid from inserted
if exists(select *from student where sid=@sid)
print ‘插入成功’
else
delete fromscore where sid = @scoreId
End
Insert into score (studentId,english) values(100,100)
24、触发器-删除
CREATE TRIGGER tr_deleteStudent ON student
for delete
AS
begin
insert into backupStudent select * from deleted
End
Delete from student where sId=1
附：随机练习代码
---======子查询===========
select sname, sage, sgender, sclass from T_Student201217 where sclass in
(select cid fromT_Class201217 wherecname='.Net' or cname='Java' or cname='Php')
--=========ROW_NUMBER=============
select ROW_NUMBER() over(order by sname),* from T_Student201217
select * from (select ROW_NUMBER() over(order by sname) as num,* from T_Student201217) as tbl
where tbl.num between 10*(2-1) and 10*(2)
--============分页=================
select top 5 * from student where sId not in
(select top (5*(2-1)) sId from student order by sId desc)
order by sId desc
select * from
(select row_number() over (order by sId desc) as num,* from student) as s
where s.num between5*(3-1)+1 and 5*3
--==========表连接===============
--学生姓名 性别年龄班级英语成绩数学成绩
--将多张表格合并到“一张表格” 结果集
--将两张表做为一个数据源，查询出来的字段来源于两张表
--select 表.字段,表.字段from表innerjoin 表on 表.外键=表.主键
select -- inner join
T_Student201217.sname as 学生姓名,
case T_Student201217.sgender
when 0 then '女'
else '男'
end as性别,
T_Student201217.sage as 年龄,
(select cname from T_Class201217 where cid=sclass) as 班级,
--英语成绩
T_Score201217.scEnglish as 英语成绩,
--数学成绩
T_Score201217.scMath as 数学成绩
from T_Student201217 inner join T_Score201217
on T_Student201217.[sid]=T_Score201217.scStudent
--由于缺考，所以没有将缺考的学生查出来
--因为的学生在分数表里面没有数据
--left join ，完全保留join左边表的数据
select
T_Student201217.sname as 学生姓名,
case T_Student201217.sgender
when 0 then '女'
else '男'
end as性别,
T_Student201217.sage as 年龄,
(select cname from T_Class201217 where cid=sclass) as 班级,
--英语成绩
T_Score201217.scEnglish as 英语成绩,
--数学成绩
T_Score201217.scMath as 数学成绩
from T_Student201217 left join T_Score201217
on T_Student201217.[sid]=T_Score201217.scStudent
--right join ,完全保留join右边表的数据
select
T_Student201217.sname as 学生姓名,
case T_Student201217.sgender
when 0 then '女'
else '男'
end as性别,
T_Student201217.sage as 年龄,
(select cname from T_Class201217 where cid=sclass) as 班级,
--英语成绩
T_Score201217.scEnglish as 英语成绩,
--数学成绩
T_Score201217.scMath as 数学成绩
from T_Student201217 right join T_Score201217
on T_Student201217.[sid]=T_Score201217.scStudent
--Cross Join 交叉连接笛卡尔积
select * from T_Student201217 , T_Score201217
select * from T_Student201217 Cross Join T_Score201217
--==========视图============
--create viewvw_名字
--as
--SQL语句
create view vw_Select_Student_Score
as
select
T_Student201217.sname as 学生姓名,
case T_Student201217.sgender
when 0 then '女'
else '男'
end as性别,
T_Student201217.sage as 年龄,
(select cname from T_Class201217 where cid=sclass) as 班级,
--英语成绩
T_Score201217.scEnglish as 英语成绩,
--数学成绩
T_Score201217.scMath as 数学成绩
from T_Student201217 left join T_Score201217
on T_Student201217.[sid]=T_Score201217.scStudent
--执行视图
select * from vw_Select_Student_Score
--==变量===
--声明：declare@变量名类型
--赋值：set@变量名=值或是一个单值的子查询
-- select@变量名= 字段from ... --多半用于聚合函数
--统计学生成绩及格的人数（数学与英语都及格）
declare @count int
set @count=(select COUNT(*) from T_Score201217 where scEnglish>=60 and scMath>=60)
print @count
select '好一朵美丽的茉莉花'
print '好一朵美丽的茉莉花'
select @count= COUNT(*) from T_Score201217 where scEnglish>=60 and scMath>=60
print @count
--全局变量，一般系统定义和维护，我们只能读取，不能修改全局变量的值，自定义的不会当做全局变量使用
--declare@@name int
--set@@name=12345
--print @@name
select * from abc
print @@error
insert into T_Class201217(cname) values('嵌入式')
print @@IDENTITY
print @@LANGUAGE
print 'SQLServer的版本'+@@VERSION
print @@max_connections
--if else 和while=====
--在C#中什么结构，SQL中几乎就是什么结构，只是没有花括号，全部使用begin-end
--if 条件
-- begin SQL语句end
--else
-- begin SQL语句 end
--查询一个名叫'梦小琪'的人
declare @count int
select @count=COUNT(*) from MyStudent where FName='梦小琪'
if @count>0
begin
select * from MyStudentwhere FName='梦小琪'
end
else
begin
print '查无此人'
end
--1~100的和while循环
declare @index int
declare @sum int
set @index=0
set @sum=0
while(@index<=100)
begin
set @sum=@sum+@index
set @index=@index+1
end
print @sum
--==事务==
create table bank
(
cid char(4) primary key,
balance money, --余额
)
alter table bank
add constraint Ch_balance check(balance>=10)
insert into bank values('0001',1000)
insert into bank values('0002',10)
select * from bank
--有此时候做一件事情可能联动其他事情
--但是又不能保证每次绝对正确执行
--那么，按照正常的逻辑应该执行下去，如果成功，就执行完成
--如果不成功，则将所有联动的事情一起恢复到初始状态
update bank set balance=balance-1000 where cid='0001'
update bank set balance=balance+1000 where cid='0002'
--开始事务：BEGINTRANSACTION
--事务提交：COMMITTRANSACTION
--事务回滚：ROLLBACKTRANSACTION
begin transaction
begin try
declare @MyError int
set @MyError=0
update bank set balance=balance-1000 where cid='0001'
set @MyError=@MyError+@@ERROR
update bank set balance=balance+1000 where cid='0002'
set @MyError=@MyError+@@ERROR
end try
begin catch
print '有错误!!!'
end catch
--由于是第一句话出现的错误，当执行第二句话以后，@@ERROR将变成
--所以需要定义一个临时变量来记录，只要没错，累加就是，不用担心
if(@MyError=0)
commit
else
rollback
--==try-catch==
begin try
select 1
end try
begin catch
select 2
end catch
--在SQLServer中尽量少用try-catch,实际上可以使用if来避免出现异常
--====存储过程=====
sp_help bank
use master
go
sp_databases
exec sp_tables
--定一个存储过程
--定义存储过程的语法
-- CREATE PROC[EDURE] 存储过程名
-- @参数数据类型[=默认值][OUTPUT],
-- @参数n 数据类型[= 默认值][OUTPUT]
-- AS
-- SQL语句
--查询学生表
create proc usp_select_student
as
select * from MyStudent
exec usp_select_student
--根据性别查询学生
create proc usp_SelectGender_Student
@gender nvarchar(10)
as
select * from MyStudent where FGender=@gender
exec usp_SelectGender_Student'男'
--加一个年龄
alter proc usp_SelectGender_Student
@gender nvarchar(10),
@age int
as
select * from MyStudent where FGender=@gender and Fage=@age
exec usp_SelectGender_Student'男',50
exec usp_SelectGender_Student @age=50,@gender='男'
--默认就处理男的，可以指定女的
alter proc usp_SelectGender_Student
@gender nvarchar(10)='男',
@age int =50
as
select * from MyStudent where FGender=@gender and Fage=@age
exec usp_SelectGender_Student
exec usp_SelectGender_Student'女',20
--将银行转帐变成一个存储过程
alter proc usp_transMoney
@money money,
@isRight bit output
as
begin
begin transaction
begin try
declare @MyError int
set @MyError=0
update bank set balance=balance-@money where cid='0001'
set @MyError=@MyError+@@ERROR
update bank set balance=balance+@money where cid='0002'
set @MyError=@MyError+@@ERROR
commit
set @isRight=1
end try
begin catch
print '有错误!!!'
rollback
set @isRight=0
end catch
end
declare @check bit
exec usp_transMoney-200,@isRight= @check output
if(@check=1)
begin
select *from bank
end
else
begin
print '余额不足，转帐失败'
end
--===触发器===
create table Records
(
rId intidentity(1,1) primary key,
rType int, -- 1存钱 -1 取钱
rMoney money,
userId char(4)
)
select * from bank
--创建触发器
create trigger tr_Records
on Records
for insert
as
declare @typeint,@money money,@id char(4)
select @type= rType,@money=rMoney,@id=userId from inserted
update bankset balance = balance + @money*@type
where cId = @id
--当插入数据的时候就会引发触发器
insert into Records values(-1,10,'0002')
select * from Records
create trigger tr_del
on student
for delete
as
delete fromscore where studentId in(select sid from deleted)
---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流！ ----------------------