黑马程序员—SQL系列 (四)

---------------------- ASP.Net+Android+IO开发S、 .Net培训、期待与您交流！ ----------------------

前言：可通过目录快速查阅

SQL语句提高

1.视图

参考资料（深入浅出视图）： http://www.cnblogs.com/CareySon/archive/2011/12/07/2279522.html

1) 视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
2) 视图在操作上和数据表没有什么区别，但两者的差异是其本质不同：

数据表是实际存储记录的地方，然而视图并不保存任何记录，它存储的实际上是查询语句
相同的数据表，根据不同用户的不同需求，可以创建不同的视图（不同的查询语句）
3) 优点：筛选表中的行；防止未经许可的用户访问敏感数据；降低数据库的复杂程度

创建视图

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

更新视图

--向 "Current Product List" 视图添加 "Category" 列
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

撤销视图

DROP VIEW view_name

2.存储过程

1)一些概念

局部变量_先声明再赋值

声明局部变量

    DECLARE @变量名  数据类型
    DECLARE @bookName varchar(20)
    DECLARE @bId int

赋值

SET @变量名 =值      --set用于普通的赋值
SET @ bookName =‘家宝’
SELECT @变量名 = 值  --用于从表中查询数据并赋值
SELECT @ bookName=b_title FROM Book WHERE b_id=2

例子：

--从Book表中找出比书名为.NETMVC3价格小的书籍信息
- -声明变量
declare @mo money 
- -赋值
select @mo=b_money from Book where b_title=‘.NETMVC3‘ 
- -使用变量
select * from Book where b_money<@mo 

变量种类

局部变量：
局部变量必须以标记@作为前缀 ，如@Age int
局部变量：先声明，再赋值 
全局变量（系统变量）：
全局变量必须以标记@@作为前缀，如@@version
全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值 

全局变量例子：

print  'SQLServer的版本'+@@VERSION 
print  '服务器名称: '+@@SERVERNAME
print ‘最后一次放生的错误号'+convert(varchar(5),@@ERROR)

IF ELSE

IF(条件表达式)
  BEGIN --相当于C#里的{
    语句1  ……
  END --相当于C#里的}
ELSE
 BEGIN
    语句1
    ……
  END

例子：

--统计并显示所有书籍价格(b_money) 的平均值，如果平均价格在50以上，显示“A“，并显示平均价格最高的2本书
--如果在50以下，显示“B“，并显示平均价格最少的2本书
declare @money money
select @money=AVG(b_money) from Book
if @money >50
begin
	select 'A'
	select top 2 * from Book order by b_money desc
end
else
begin
	select 'B'
	select top 2 * from Book order by b_money asc
end

WHILE循环

WHILE(条件表达式)
  BEGIN --相当于C#里的{
    语句
    ……
    BREAK
  END --相当于C#里的}

例子：

--打印1-50
declare @a int
set @a=1
while (@a<50)
begin
    print str(@a)
    set @a=@a+1
end

综合练习：

--问题：书籍价格上涨，确保每本书价格最少50元。
--提价：先每本都加2元，看是否都价格达标，如果没有全部达标，每本再加2元，
--再看是否都达标，如此反复提价，直到所有书都达标为止 。
--分析：
--第一步，统计没达标的本数 ；
--第二步，如果有书没通过，提价；
--第三步，循环判断。

DECLARE @num int
WHILE(1=1) --条件永远成立
  BEGIN
    SELECT @num=COUNT(*) FROM Book WHERE b_money<50    --统计不达标本数
    IF (@num>0)
       UPDATE Book  SET b_money=b_money+2                                    --每本加2元
   ELSE
       BREAK    --退出循环(只有一行语句可省begin-end)
  END

事务
为什么需要事务
银行转账问题：
假定钱从A转到B，至少需要两步：
A的资金减少
然后B的资金相应增加

UPDATE bank SET uMoney=uMoney-1000 WHERE uName='家宝‘ 
@@error
UPDATE bank SET uMoney=uMoney+1000 WHERE uName='奥巴马‘
@@error

如果其中一条sql语句出错的话，银行账目就会出问题

事务

指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成，必须作为一个整体执行
这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行 
语法步骤：
开始事务：BEGIN TRANSACTION
事务提交：COMMIT TRANSACTION
事务回滚：ROLLBACK TRANSACTION
判断某条语句执行是否出错：
全局变量@@ERROR：只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计
例：

SET @errorSum=@errorSum+@@error

利用事务解决转账问题：

BEGIN TRANSACTION 
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT 
SET @errorSum=0  --初始化为0，即无错误
/*--转账：张三的账户少1000元，李四的账户多1000元*/
UPDATE bank SET currentMoney=currentMoney-1000
   WHERE customerName='张三'
SET @errorSum=@errorSum+@@error
UPDATE bank SET currentMoney=currentMoney+1000
   WHERE customerName='李四'
SET @errorSum=@errorSum+@@error  --累计是否有错误
If @errorSum>0
Begin
	rollback transaction
	 select ‘失败’
End
Else
Begin
	commit transaction
	select ‘成功’
End

2)存储过程

存储过程---就像数据库中运行方法(函数)
和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果
前面学的if else/while/变量 等，都可以在存储过程中使用
优点：

执行速度更快  – 在数据库中保存的存储过程语句都是编译过的

允许模块化程序设计  – 类似方法的复用

提高系统安全性          –  防止SQL注入

减少网络流通量          – 只要传输 存储过程的名称
系统存储过程：由系统定义，存放在master数据库中，名称以“sp_”开头或”xp_”开头

例子：

EXEC sp_databases
EXEC  sp_renamedb 'Northwind','Northwind1'
EXEC sp_tables
EXEC sp_columns stuInfo  
EXEC sp_help stuInfo
EXEC sp_helpconstraint stuInfo
EXEC sp_helpindex stuMarks
EXEC sp_helptext 'view_stuInfo_stuMarks' 
EXEC sp_stored_procedures  

自定义存储过程：由用户在自己的数据库中创建的存储过程

创建存储过程

定义存储过程的语法
    CREATE  PROC[EDURE]  存储过程名 
    @参数1  数据类型 = 默认值 OUTPUT,
    @参数n  数据类型 = 默认值 OUTPUT
    AS
      SQL语句
参数说明：
参数可选
参数分为输入参数、输出参数 
输入参数允许有默认值
执行存储过程：EXEC  过程名  [参数]

例子1：

--编写存储过程usp_GetBookByCid，要求传入参数:@categoryId int
create proc usp_GetBookByCid
@cateid int
as
select * from book where b_cid =@cateid

--掉用存储过程，传入分类ID，查处属于该分类的书籍列表
exec usp_GetBookByCid 1

例子2：

--编写分页存储过程
create procedure proGetPageData
@pageIndex int,
@pageSize int
as
declare @sqlStr varchar(300)
set @sqlStr='select top '+str(@pageSize)+' * from Category where c_id not in(select top '+str((@pageIndex-1)*@pageSize)+' c_id from Category order by c_addtime)order by c_addtime'
print @sqlStr
EXEC(@sqlStr)

调用存储过程

--无参数的存储过程调用
Exec pro_GetAge
--有参数的存储过程两种调用法
EXEC proGetPageData 60,55 ---按次序
EXEC proGetPageData @labPass=55,@writtenPass=60 --参数名
--参数有默认值时
EXEC proGetPageData --都用默认值 
EXEC proGetPageData 1  --页容量(@pageSize)默认值 
EXEC proGetPageData 1,5   --不用默认值

存储过程中使用输出参数

create proc [dbo].[usp_Getpagebooks]
@pageIndex int = 1,
@pagesize int = 5,
@rowCount int output
as
select * from
(
select row_number() over(order by c_id) as num,* from Category) as a 
where a.num>=(@pageIndex-1) * @pagesize and a.num <=@pageIndex * @pagesize
select @rowCount=count(*) from Category

declare @pi int,@ps int,@rc int,@pc int
set @pi =1
set @ps =5 
exec usp_Getpagebooks @pi,@ps,@rc output,@pc output
select @pi,@ps,@rc,@pc

最后使用的存储过程(推荐)

create PROCEDURE GetPageData
(
@TableName varchar(30),--表名称
@IDName varchar(20),--表主键名称
@PageIndex int,--当前页数 
@PageSize int--每页大小 
)
AS
IF @PageIndex > 0
BEGIN
set nocount on
   DECLARE @PageLowerBound int,@StartID int,@sql nvarchar(225)
   SET @PageLowerBound = @PageSize * (@PageIndex-1)
   IF @PageLowerBound<1
    SET @PageLowerBound=1
   SET ROWCOUNT @PageLowerBound
   SET @sql=N'SELECT @StartID = ['+@IDName+'] FROM '+@TableName+' ORDER BY '+@IDName
     exec sp_executesql @sql,N'@StartID int output',@StartID output
   SET ROWCOUNT 0
   SET @sql='select top '+str(@PageSize) +' * from '+@TableName+' where ['+@IDName+']>='+ str(@StartID) +' ORDER BY ['+@IDName+'] '
   EXEC(@sql)
set nocount off
END

3.触发器

触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。
一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。
触发器是一个功能强大的工具，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。

常见的触发器有三种：分别应用于Insert , Update , Delete 事件 

常用语法：

CREATE TRIGGER triggerName ON Table
for UPDATE|INSERT|DELETE
AS
begin
…
end

触发器-更新

CREATE TRIGGER testForFun ON dbo.Category 
for UPDATE
AS
begin
select * from book
end
update Category set c_name = 'Android2' where c_id=3

触发器-删除

CREATE TRIGGER testForDel ON dbo.Category 
for delete 
AS
begin
select * from book
end
delete Category set c_name = 'Android2' where c_id=3

--创建一个带事务回滚的触发器
create trigger testForInsert on Category
for insert
as
begin
	begin transaction
	rollback transaction
end
insert into Category (c_name)values('110')
--消息 3609，级别 16，状态 1，第 1 行
--事务在触发器中结束。批处理已中止

更多详细介绍： http://wenku.baidu.com/view/1b5612fbaef8941ea76e0556.html

4.游标

游标其实可以理解成一个定义在特定数据集上的指针，我们可以控制这个指针遍历数据集，或者仅仅是指向特定的行，所以游标是定义在以Select开始的数据集上的
1.声明游标

DECLARE 游标名 CURSOR FOR SELECT语句(注：此处一定是SELECT语句)

2.打开游标

OPEN 游标名

3.读取游标数据

Fetch [Next | Prior | First | Last | Absoluten| Relativen] From 游标名 INTO @name1,@name2...
WHILE(@@FETCH_STATUS = 0)
BEGIN
--要执行的SQL语句
FETCH NEXT FROM 游标名
END

开启游标之后，默认是位于第一行的前面，因此需要FETCH NEXT(即第一行)
WHILE循环是判断是否有数据，如果有，则执行BEGIN中的语句。
注意在执行语句后面添加FETCH NEXT..，使游标跳至下一条数据，否则会不停的循环执行第一条语句。
其中：

Next表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。默认的读取选项为Next
Prior表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回，并且把游标置于第一行之前。
First表示返回结果集中的第一行，并且将其作为当前行。
Last表示返回结果集中的最后一行，并且将其作为当前行。
Absoluten如果n为正数，则返回从游标头开始的第n行，并且返回行变成新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的当前行，如果n为0，则返回当前行。
Relativen如果n为正数，则返回从当前行开始的第n行，如果n为负,则返回从当前行之前的第n行，如果为0，则返回当前行。
4.关闭游标

CLOSE 游标名

关闭后不能对游标进行读取等操作，但可以使用OPEN语句再次打开
5.释放游标

DEALLOCATE 游标名

即删除游标，不可再使用
6.注意问题
尽管使用游标比较灵活，可以实现对数据集中单行数据的直接操作，但游标会在下面几个方面影响系统的性能：
使用游标会导致页锁与表锁的增加
导致网络通信量的增加
增加了服务器处理相应指令的额外开销

例子1：

--用 @@FETCH_STATUS 控制在一个 WHILE 循环中的游标活动。
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

例子2：

--自己工作中写的游标
declare @NewMenuId varchar(30)
declare @MenuId varchar(30)
declare @Id varchar(32)
declare @evname varchar(500)

Declare s_cursor Cursor for select id,evname from C_StandKeyElementList WHERE ISNULL(evname, '') <> '' and evname like '%MenuRedirect.aspx?MenuId=%'

open s_cursor

Fetch Next from s_cursor into @Id,@evname
while @@fetch_status=0
begin
   select @MenuId=dbo.F_Get_No(@evname)
   set @NewMenuId=''
   select @NewMenuId=Id from S_Menus where state=1 and  MyFlag=@MenuId
   if (@NewMenuId='')
      select top 1 @NewMenuId=Id from S_Menus where MyFlag=@MenuId
   if (@NewMenuId<>'') 
       --print ''
       update C_StandKeyElementList set evname=replace(evname,@MenuId,@NewMenuId) where id=@id
   else
	print 'old:'+@MenuId+' new:'+@NewMenuId

   Fetch Next from s_cursor into @Id,@evname
end
close s_cursor
DEALLOCATE s_cursor
GO

注：SQL系列到这里就结束了，睡觉去了

---------------------- ASP.Net+Android+IO开发S、 .Net培训、期待与您交流！ ----------------------