黑马程序员—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] ASSELECT ProductID,ProductNameFROM ProductsWHERE Discontinued=No

更新视图

--向 "Current Product List" 视图添加 "Category" 列CREATE VIEW [Current Product List] ASSELECT ProductID,ProductName,CategoryFROM ProductsWHERE 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  '服务器名称: '+@@SERVERNAMEprint ‘最后一次放生的错误号'+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 moneyselect @money=AVG(b_money) from Bookif @money >50beginselect 'A'select top 2 * from Book order by b_money descendelsebeginselect 'B'select top 2 * from Book order by b_money ascend

WHILE循环

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

例子：

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

综合练习：

--问题：书籍价格上涨，确保每本书价格最少50元。--提价：先每本都加2元，看是否都价格达标，如果没有全部达标，每本再加2元，--再看是否都达标，如此反复提价，直到所有书都达标为止 。--分析：--第一步，统计没达标的本数 ；--第二步，如果有书没通过，提价；--第三步，循环判断。DECLARE @num intWHILE(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='家宝‘ @@errorUPDATE 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+@@errorUPDATE bank SET currentMoney=currentMoney+1000   WHERE customerName='李四'SET @errorSum=@errorSum+@@error  --累计是否有错误If @errorSum>0Beginrollback transaction select ‘失败’EndElseBegincommit transactionselect ‘成功’End

2)存储过程

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

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

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

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

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

例子：

EXEC sp_databasesEXEC  sp_renamedb 'Northwind','Northwind1'EXEC sp_tablesEXEC sp_columns stuInfo  EXEC sp_help stuInfoEXEC sp_helpconstraint stuInfoEXEC sp_helpindex stuMarksEXEC sp_helptext 'view_stuInfo_stuMarks' EXEC sp_stored_procedures  

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

创建存储过程

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

例子1：

--编写存储过程usp_GetBookByCid，要求传入参数:@categoryId intcreate proc usp_GetBookByCid@cateid intasselect * from book where b_cid =@cateid--掉用存储过程，传入分类ID，查处属于该分类的书籍列表exec usp_GetBookByCid 1

例子2：

--编写分页存储过程create procedure proGetPageData@pageIndex int,@pageSize intasdeclare @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 @sqlStrEXEC(@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 outputasselect * 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 * @pagesizeselect @rowCount=count(*) from Categorydeclare @pi int,@ps int,@rc int,@pc intset @pi =1set @ps =5 exec usp_Getpagebooks @pi,@ps,@rc output,@pc outputselect @pi,@ps,@rc,@pc

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

create PROCEDURE GetPageData(@TableName varchar(30),--表名称@IDName varchar(20),--表主键名称@PageIndex int,--当前页数 @PageSize int--每页大小 )ASIF @PageIndex > 0BEGINset 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 offEND

3.触发器

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

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

常用语法：

CREATE TRIGGER triggerName ON Tablefor UPDATE|INSERT|DELETEASbegin…end

触发器-更新

CREATE TRIGGER testForFun ON dbo.Category for UPDATEASbeginselect * from bookendupdate Category set c_name = 'Android2' where c_id=3

触发器-删除

CREATE TRIGGER testForDel ON dbo.Category for delete ASbeginselect * from bookenddelete Category set c_name = 'Android2' where c_id=3

--创建一个带事务回滚的触发器create trigger testForInsert on Categoryfor insertasbeginbegin transactionrollback transactionendinsert 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 FORSELECT LastName, FirstName FROM Northwind.dbo.EmployeesOPEN Employee_CursorFETCH NEXT FROM Employee_CursorWHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM Employee_CursorENDCLOSE Employee_CursorDEALLOCATE 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_cursorFetch Next from s_cursor into @Id,@evnamewhile @@fetch_status=0begin   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   elseprint 'old:'+@MenuId+' new:'+@NewMenuId   Fetch Next from s_cursor into @Id,@evnameendclose s_cursorDEALLOCATE s_cursorGO

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

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