SQL Server 笔记之八 实现视图和批量

1.创建、修改、撤消和重命名视图

1).视图的定义和优点
视图是从一个或多个基本表中导出的表，视图的特点在于它可以是连接多张表格的虚表，也可以是使用WHERE子句限制返回行的数据查询的结果。
视图保证数据的安全性，通过限制存取对:
表的特定行
表的特定列
表的特定行和列
使用连接提取行
给定表中数据的统计摘要
另一个视图的子集或视图和表的子集

视图有以下优点：
a.分割数据，简化观点
b.简化操作
c.提供自动的安全保护功能

--------------------------基本表
8.D.1创建视图

你需要查看各种职员的技能。一些将要执行的询问如下：
SELECT vFirstName, vSkill FROM Employee
JOIN PositionSkill ON Employee.cCurrentPosition = PositionSkill.cPositionCode
JOIN Skill ON PositionSkill.cSkillCode = Skill.cSkillCode

SELECT vFirstName, vLastName,vSkill FROM Employee
JOIN PositionSkill ON Employee.cCurrentPosition=PositionSkill.cPositionCode
JOIN Skill ON PositionSkill.cSkillCode = Skill.cSkillCode

SELECT vFirstName, vLastName, vSkill FROM Employee
JOIN PositionSkill ON Employee.cCurrentPosition =PositionSkill.cPositionCode
JOIN Skill ON PositionSkill.cSkillCode =Skill.cSkillCode AND vFirstName = 'Angela'

SELECT vFirstName, vLastName, vQualification, vSkill FROM Employee
JOIN PositionSkill ON Employee.cCurrentPosition = PositionSkill.cPositionCode
JOIN SkillON PositionSkill.cSkillCode = Skill.cSkillCode
简化了执行这些查询的任务。

a.确定如何简化询问
结果：
使用视图可简化询问

b.为视图编写询问
动作：
从视图派生出它的数据的表是Employee、 PositionSkill和Skilll
在视图中包含的列有：来自Employee 表的vFirstName、 vLastName和vQualification 和来自Skill 表的 vSkill

c.创建视图
使用CREATE VIEW 语句可创建视图
语法
  CREATE VIEW view_name
        [(column_name [, column_name]...)]
        [WITH ENCRYPTION]
        AS select_statement [WITH CHECK OPTION]

2).创建视图
Create  view  视图名[(视图列名,…)]
[with  encryption]
As
Select 语句
[With  check  option]

动作：
在Query Analyzer窗口，输入：
  CREATE VIEW vwEmpSkill
  AS
  SELECT vFirstName, vLastName, vQualification, vSkill
  FROM Employee JOIN PositionSkill
  ON Employee.cCurrentPosition =  PositionSkill.cPositionCode
  JOIN Skill ON PositionSkill.cSkillCode = Skill.cSkillCode
按F5执行命令

          
d.通过执行视图上所需的询问，验证此询问已被简化
验证通过在视图上执行所需查询已简化了查询
动作：
a.在Query Analyzer 窗口，输入：
 SELECT vFirstName, vSkill FROM vwEmpSkill
按F5执行查询
b.在Query Analyzer 窗口，输入：
 SELECT vFirstName, vLastName, vSkill
 FROM vwEmpSkill
按F5执行查询
c.在Query Analyzer 窗口，输入：
 SELECT vFirstName,vLastName,vSkill
 FROM vwEmpSkill WHERE vFirstName = 'Angela'
按F5执行查询
d.在Query Analyzer 窗口，输入：
 SELECT * FROM vwEmpSkill
按F5执行查询

一些查询将被执行如下：
 SELECT vFirstName, vDepartmentName
 FROM Employee JOIN Department
 ON Employee.cDepartmentCode = Department.cDepartmentCode

 SELECT vFirstName, cDesignation, vDepartmentName
 FROM Employee JOIN Department
 ON Employee.cDepartmentCode = Department.cDepartmentCode

      SELECT vFirstName, vAddress, cCity,cZip,cDesignation,vDepartmentName,vDepartmentHead
      FROM Employee JOIN Department
      ON Employee.cDepartmentCode = Department.cDepartmentCodeWHERE cCity = 'Columbus'

注意：在创建视图时，并非所有的SELECT子查询都是合法的。
有的SQL语句在这时是禁止使用的，例如：
Select into
Compute 或Compute by 子句
Order by子句
但是在对视图进行查询时，依然可以像对表格一样使用Order by等语句

在视图中可以使用的内容包括：
统计函数
Group by子句
JOIN子句
DISTINCT子句
UNION子句

3).修改视图
Alter  view  视图名[(视图列名,…)]
[with  encryption]
As
Select 语句
[With  check  option]

你可以用ALTER VIEW语句修改视图
语法
 ALTER VIEW view_name [(column_name)]
 [WITH ENCRYPTION]
 AS select_statement
 [WITH CHECK OPTION]

改变、撤消和重命名视图
4).删除视图/重命名视图/查看视图属性
Sp_helptext  视图名
Drop   view视图名
Sp_rename 原视图名,新视图名

撤消视图
你可以使用DROP VIEW语句撤消数据库中的视图
语法
  DROP VIEW view_name

查看视图各列信息
Sp_help 视图名
查看视图依赖于各表的信息
Sp_depends 视图名
查看视图源代码
Sp_helptext 视图名

重命名视图
使用sp_rename系统存储过程，可以对视图进行重命名
语法
  sp_rename old_viewname, new_viewname

在视图上使用INSERT语句
注意：在进行数据插入时还是有一定的限制：
如果视图上没有包括基本表中所有属性为NOT　NULL的行，那么插入操作将会失败。
同NOT　NULL值的限制一样，如果某些列因为某些规则或约束的限制而不能接受从视图插入的列时，插入失败。
如果在视图中包含了使用统计函数的结果，插入操作失败
不能在使用了DISTINCT语句的视图中插入值。
不能在使用了GROUP　BY语句的视图中插入值。
对于多表链接而成的视图来说，一个INSERT语句只能对单独某个表中的列进行插值操作。

2.使用视图来更新数据

8.D.2使用视图修改数据
视图定义如下：
CREATE VIEW vwEmployeeCandidate
AS
SELECT Employee.cCandidateCode, vFirstName, vLastName, cPhone, siTestScoreFROM Employee  JOIN  InternalCandidateON Employee.cCandidateCode=InternalCandidate. cCandidateCode

在更新候选人代码为‘000018’职工的测试成绩和电话号码时，下面命令产生一个错误：
UPDATE  vwEmployeeCandidate
SET cPhone = '(614)324-5634', siTestScore=75
WHERE cCandidateCode=‘000018’
确定错误并改正之，这样表中数据将被修改。

a.确定错误和修改数据的方法
b.编写更新表的单独语句
c.更新表
d.验证表已被更新

确定错误和修改数据的方法
通过视图修改数据
此数据出现在基表中。可以通过修改视图中数据来修改基表
在通过视图插入、更新或删除数据时有一定限制。这些限制是：
你不能在视图中修改数据，如果修改影响到一个以上基表的话
你不能改变作为计算结果的列

结果：
你不可更新属性，因为视图只允许一次更新一个基表。你需要通过给出两个单独的UPDATE命令来更新属性

编写更新表的单独语句
动作：
下面语句将更新基表Employee中 cPhone属性：
 
UPDATE vwEmployeeCandidate SET cPhone = '(614)324-5634'WHERE cCandidateCode = '000018'

下面语句将更新InternalCandidate 表中siTestScore属性：

UPDATE vwEmployeeCandidate SET siTestScore=75 WHERE cCandidateCode='000018'

更新表
动作：
在Query Analyzer 窗口，输入：
            UPDATE vwEmployeeCandidate
    SET cPhone = '(614)324-5634'
    WHERE cCandidateCode = '000018'
 
         UPDATE vwEmployeeCandidate
            SET siTestScore = 75
            WHERE cCandidateCode = '000018'
按 F5 执行语句

验证表已被更新
动作：
在Query Analyzer 窗口，输入：
  SELECT * FROM Employee
  WHERE cCandidateCode = '000018'

  SELECT * FROM InternalCandidate
  WHERE cCandidateCode = '000018'

8.P.1使用视图修改数据
视图定义如下：
CREATE VIEW vwNewspaperNewsad
AS
SELECT cNewspaperName,vCity,cZip,dAdStartDate
FROM Newspaper JOIN NewsAd
ON Newspaper.cNewspaperCode =
NewsAd.cNewspaperCode

在要更新表时，下面语句不能对表进行更新：
UPDATE cNewspaperNewsAd
SET cZip='88993-4532',dAdStartDate='01/09/99'
WHERE cNewspaperName='Daily News'
修改基表中的数据.

 

SQL Server 的编程
SQL Server应用的编程有几种方法。下节描述你可以进行编程的不同方法，通过以下术语：

1).什么是批量/批处理
批处理:
批处理是从客户机传递到服务器上的一组完整的数据和SQL指令。在一个批处理中，可以包含一条SQL指令，也可以包含多条SQL指令。
批处理的所有语句被作为一个整体，而被成组的分析、编译和执行，可以想象，如果在一个批处理中存在着一个语法错误，那么所有的语句都无法通过编译。

2).批量中的简单语法
变量：是一个内存单元/空间，变量定义时指定的数据类型决定了变量中存放哪种数据。
*1）变量的声明
Declare  @变量名  数据类型
局部变量（用户定义的变量）@变量名:在批量中定义，使用，批量结束后就消失。
全局变量                 @@变量名
*2）变量的赋值
Set   @变量名=值
Select  @变量名=值(可从表中将查询出的数据赋值给变量)
*3）显示变量
Print  @变量名  :
Print 只能显示字符串  convert函数转换
Select  @变量名
注释：单行注释 --…………………………..
      多行注释   /*…………………………………
……………………………………..
…………………………………….*/
3).控制流语句
*1）顺序结构
*2）选择/判断/分支结构
@变量名 运算符  值
If 判断表达式
begin
 SQL语句
SQL语句
end
Else
begin
 SQL语句
SQL语句
End

case
*3）循环结构

定义循环变量
初始化循环变量
While 判断表达式（判断循环变量）
Begin
 循环体（包含改变循环变量的值）
End

跳转语句(应用于循环语句)
Continue
Break

变量:
在Transact SQL中，有两类变量：局部变量和全局变量
变量@Charge在批量中声明，当批量执行完后它就丢失。这种变量叫做局部变量，由于是我们定义它们的，所以又叫做用户定义的变量。
全局变量是那些由服务器声明的、通常由服务器赋值的变量。

局部变量
局部变量是作用域限在一定范围内的SQL对象。一般来说局部变量在一个批处理（也可以是存储过程或触发器）中被声明或定义，然后这个批处理内的SQL语句就可以设置这个变量的值，或者是引用这个变量已经被赋予的值，当这个批处理结束后，这个局部变量的生命周期也就随之消亡。
语法
DECLARE @variable_name data_type
例子
DECLARE @Charge int

全局变量
@@CONNECTIONS:记录自最后一次服务器启动以来，所有针对此服务器进行的连接数目。
@@ CPU_BUSY:记录自最后一次服务器启动以来，以ms为单位的CPU工作时间。
@@ ERROR:返回执行上一条SQL语句所返回的错误号。
@@IDENTITY:返回最近一次插入的identity列的数值。
@@ ROWCOUNT:返回上一条SQL语句所影响到数据行的数目。
@@ VERSION：返回SQLSERVER服务器的版本信息。

打印消息:
你可以使用PRINT语句在屏幕上显示用户定义的消息或变量的内容
例子
  DECLARE @MyName char (50)
  SELECT @MyName = 'Coomar Chris'
  PRINT @MyName

注解：
注解入口
你可以在批量中使用注解入口以书写代码的描述
注解入口可以用两种方式书写：:
多行注解入口包含在/* 和 */中
单行注解入口以--（两个连字符）开始

控制流语句
控制流语言控制批处理、存储过程、触发器和事务中SQL语句的执行流程
SQL Server提供的用于编程的控制流语句有：
  IF…ELSE语句
  CASE语句
  WHILE语句

？填空：
a.一同提交SQL server执行的一组SQL 语句叫做 ________.
b. _________在批量中声明，当批量执行完后，它将丢失。

3.声明变量
4.打印消息
5.使用注解

6.使用条件语句

8.D.3使用IF 语句
内部候选人被召集参加面试的最低测试成绩是80分。编写一个批量，对于职工号为000008的员工，如果他的测试成绩高于80，则显示“Called for interview”和测试成绩，如果测试成绩低于80，则显示“Rejected - Not called for interview”。

a.确定如何显示所需的消息

The IF…ELSE Statement
对于SQL语句的执行条件，你可以使用此语句
语法
IF…ELSE    语句
IF boolean_expression
   {sql_statement | statement_block}
ELSE boolean_expression 
   {sql_statement | statement_block}

BEGIN…END  语句
如果有多行T-SQL 语句，那么这些语句必须包含在关键字BEGIN 和END中
语法
BEGIN
 {sql_statement | statement_ block}
END

确定如何显示所需的消息(续)
结果：
在批量中使用PRINT 和IF…ELSE语句可以显示消息

b.在纸上编写批量
结果：
batch 语句给出如下:
DECLARE @iTest int
SELECT @iTest = siTestScore FROM InternalCandidate
WHERE cEmployeeCode = '000008'
IF @iTest < 80
PRINT 'Rejected - Not called for interview'
ELSE
BEGIN
   PRINT 'Called for interview'
   PRINT 'your test score ='
   PRINT @iTest
END

c.执行批量

d.验证批量
动作：
执行以下查询验证你的答案：
SELECT siTestScore
FROM InternalCandidate  
WHERE cEmployeeCode =  '000008'

7.使用循环语句
更多的构造
 CASE语句
在有多个条件需要求值的情况下，SQL Server提供了一个叫做CASE语句的编程构造
语法
CASE
    WHEN boolean_expression THEN expression 
    [[WHEN boolean_expression THEN    
    expression] [...]] 
    [ELSE expression]
END

根据作者来自的州的名字，打印出作者的姓名和州的全名：
Select ‘作者’=
Case
 when state=‘CA’ then ‘来自加利福尼亚’
 when state=‘KS’ then ‘来自堪萨斯州’
 when state=‘IN’  then ‘来自印第安纳州’
 else ‘来自其它州’
END
,au_fname+'.'+au_lname
From authors

 WHILE语句
你可以在批量、存储过程、触发器或游标中使用WHILE语句，以允许一组T - SQL语句在给定条件为真的情况下重复地执行
语法
WHILE  boolean_expression
{sql_statement | statement_block}
[BREAK]
{sql_statement | statement_block}
[CONTINUE]

在没有查询到存在书价超过$30的情况下，反复执行BEGIN…END语句块中的内容：首选将所有书的价格提高50%，并在存在最高书超过$50的情况下，将跳出循环。
while  exists(select price from titles where price<30)
begin
     update titles
     set price=price*1.5
     select max(price) from titles
  if (select max(price) from titles)>50
   break
  else
   continue
end

BREAK和CONTINUE语句
你可以用BREAK和CONTINUE语句控制WHILE循环中语句的执行
BREAK语句导致跳出WHILE循环
CONTINUE语句导致WHILE循环的重新开始，跳过循环内CONTINUE后的任何语句。

小结:

视图是一个虚表，是由一个或多个表中的列的子集组成
视图从一个或多个称为基表或基础表中派生出它的数据
视图可作为一种安全机制，保护基表中的数据
视图可以下情形中限制对数据的访问：特定列、特定行、特定行和列、使用连接提取的行、给定表中数据的统计摘要、另一个视图的子集或视图和表的子集
视图可以用CREATE VIEW语句来创建
当使用视图时，尽管这个视图是从多个基础表中派生出来的，SQL Server只允许对其中一个基础表中的数据进行修改。
视图可以用ALTER VIEW语句来修改
视图可以用DROP VIEW语句来撤消
视图可以用sp_rename存储过程来重命名
批量是与服务器执行一起提交的一组SQL语句
你可以用变量存储临时值
你可以使用PRINT语句在屏幕上显示用户定义的消息或变量的内容
你可以在批量中使用注解入口来书写代码描述
对于SQL语句的条件执行，你可以使用IF…ELSE语句
CASE语句对一列条件进行求值，并返回各种可能结果中的一个
你可以在批量中使用WHILE语句，以允许一组T - SQL语句在给定条件为真的情况下重复地执行
BREAK语句导致跳出WHILE循环
CONTINUE语句导致WHILE循环的重新开始，跳过循环内CONTINUE后的任何语句