SQL Server 技术简明总结

---------------------------第一章 据库的设计---------------------------------------------------------------------------

◆在需求分析阶段，设计数据库的步骤:
。。1收集信息 。。2标识对象（实体－Entity）。。3标识每个实体的属性（Attribute）。。4标识对象之间的关系（Relationship）

◆E-R（Entity－Relationship）实体关系图 ：
。。矩形——实体，一般是名词。。椭圆——属性，一般是名词。。菱形——关系，一般是动词。。直线——连接属性和实体集、实体和关系集

◆在概要设计阶段和详细设计阶段，设计数据库的步骤为：
。。1绘制E-R图。。2将E-R图转换为表格。。3应用三大范式规范化表格

◆如何将E-R图转换为表 ：
。。1将各实体转换为对应的表，将各属性转换为各表对应的列。。2标识每个表的主键列，需要注意的是：没有主键的表添加ID编号列，它没有实际含义，用于做主键或外键，例如用户表中的“UID”列，版块表中添加“SID”列，发贴表和跟贴表中的“TID”列
。。3在表之间建立主外键，体现实体之间的映射关系

◆数据规范化 ：
Dr E.F.codd 最初定义了规范化的三个级别，范式是具有最小冗余的表结构。这些范式是：。。1第一范式(1st NF －First  Normal Fromate)：第一范式的目标是确保每列的原子性. 如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）。。2第二范式(2nd NF－Second Normal Fromate)：如果一个关系满足1NF，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式（2NF）.第二范式要求每个表只描述一件事情。。3第三范式(3rd NF－ Third  Normal Fromate):如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）
。。第一范式（1NF）的目标：确保每列的原子性。
。。第二范式（2NF）的目标：确保表中的每列，都和主键相关
。。第三范式（3NF）的目标：确保每列都和主键列直接相关，而不是间接相关

◆规范化和性能的关系 ：
。1为满足某种商业目标，数据库性能比规范化数据库更重要：
。。通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
。。通过在给定的表中插入计算列（如成绩总分），以方便查询
。2进行规范化的同时，还需要综合考虑数据库的性能

----------------------------第二章 数据库的实现------------------------------------------------------------------------
T-SQL语句回顾：
（1）添加数据: insert into stuInfo (stuName,stuNo,stuAge)velues('张三','s25301',22)
（2）修改数据: update stuInfo set stuAge=25 where stuNmae='张三'
（3）查询数据: select stuName,stuNo from stuInfo where stuAge<25 order by stuNo
（4）删除数据:delect from stuInfo where stuAge<20

◆数据库的物理实现一般包括：
。。创建数据库
。。创建表
。。添加各种约束
。。创建数据库的登录帐户并授权
。。创建数据库或表时一般需要预先检测是否存在该对象
。。数据库从master系统数据库的sysdatabases表中查询
。。表从该数据库的系统表sysobjects表中查询

◆访问SQL Server某个数据库中的某个表，需要三层验证：
。。是否是SQL Server的登录帐户
。。是否是该数据库的用户
。。是否有足够的权限访问该表

◆数据库文件的组成：
。。主数据文件：*.mdf。。次要数据文件：*.ndf。。日志文件：*.ldf。。数据库的其他属性：
。1文件存放位置，分配的初始空间，属于哪个文件组  。2文件增长：可以按百分比或实际大小指定增长速度 。3文件容量设置：可以指定文件增长的最大值或不受限

◆删除、创建 数据库 ：
USE master  --设置当前数据库为master，以便访问sysdatabases表
GO
IF  EXISTS（SELECT * FROM  sysdatabases WHERE  name ='stuDB'）
   DROP DATABASE stuDB

CREATE DATABASE stuDB
   ON  PRIMARY  --默认就属于PRIMARY主文件组，可省略
(
 NAME='stuDB_data',  --主数据文件的逻辑名
 FILENAME='D:/project/stuDB_data.mdf',  --主数据文件的物理名
 SIZE=5mb,  --主数据文件初始大小
 MAXSIZE=100mb,  --主数据文件增长的最大值
 FILEGROWTH=15%   --主数据文件的增长率
)
LOG ON
(
  NAME='stuDB_log',
  FILENAME='D:/project/stuDB_log.ldf',
  SIZE=2mb,
  FILEGROWTH=1MB
)
GO

◆建表的基本步骤：
。。确定表中有哪些列。。确定每列的数据类型。。给表添加各种约束。。创建各表之间的关系

◆SQL Server中的数据类型 ：
整型数据：int smallint   
浮点数据：float numeric 
字符数据：。。固定长度：char(6)。。可变长度: varchar(10) unicode编码：nchar(10)
布尔数据（真/假）bit: 1/ 0
货币数据：money
日期数据：datetime

◆删除、创建 表：
USE stuDB   --将当前数据库设置为stuDB ，以便在stuDB数据库中建表
GO
IF EXISTS（SELECT * FROM  sysobjects  WHERE  name=’stuInfo’ ）
    DROP  TABLE  stuInfo
USE stuDB   --将当前数据库设置为stuDB
GO
CREATE  TABLE  stuInfo    /*-创建学员信息表-*/
(
 stuName  VARCHAR(20)  NOT  NULL ,  --姓名，非空（必填）
 stuNo   CHAR(6)  NOT  NULL,   --学号，非空（必填）
 stuAge  INT  NOT  NULL,  --年龄，INT类型默认为4个字节
 stuID  NUMERIC(18,0),     --身份证号
 stuSeat   SMALLINT  IDENTITY (1,1),   --座位号，自动编号
 stuAddress   TEXT   --住址，允许为空，即可选输入
)
GO

◆约束的目的：确保表中数据的完整型
常用的约束类型：
主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空
唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。
检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束
默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”
外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列

。。添加约束的语法：
ALTER TABLE 表名 
     ADD CONSTRAINT 约束名  约束类型  具体的约束说明

约束名的取名规则：约束类型_约束字段
。。主键（Primary Key）约束：如 PK_stuNo。。唯一（Unique Key）约束：如 UQ_stuID。。默认（Default Key）约束：如 DF_stuAddress。。检查（Check Key）约束：如 CK_stuAge。。外键(Foreign Key)约束：如 FK_stuNo
---主键约束：---
alter table bbsUsers add constraint pk_UID primary key(UID)
---默认约束：---
alter table bbsUsers add constraint df_Upassword default (888888) for Upassword
或
alter table bbsUsers add constraint df_UregDate default (getDate()) for UregDate
---外键约束：---
alter table bbsSetion add constraint fk_SID foreign key(SID) references bbsUsers(UID)
---唯一约束：---
alter table bbsUsers add constraint uq_stuID unique (stuID)
---检查check约束：---
alter table bbsUsers add constraint ck_stuAge check (stuAge between 15 and 40)
或
alter table bbsUsers add constraint ck_Uemail check (Uemail like '%@%')
或
alter table bbsUsers add constraint ck_Upassword check (len(Upassword)>=6)
go

◆删除约束的语法：
ALTER  TABLE  stuInfo 
DROP  CONSTRAINT  DF_stuAddress

◆登录验证有两种方式：
。。SQL身份验证：适合于非windows平台的用户或Internet用户， 需要提供帐户和密码
。。Windows身份验证：适合于windows平台用户，不需要提供密码，和windows集成验证

  登录帐户相应有两种：SQL 帐户和Windows帐户

◆添加 Windows登录帐户方法：
    EXEC sp_grantlogin   ‘jbtraining/S26301‘       
  添加 SQL登录帐户方法：
    EXEC sp_addlogin  ‘zhangsan', ‘1234’

◆创建数据库用户：
创建数据库用户需要调用系统存储过程sp_grantdbaccess，其用法为：
EXEC sp_grantdbaccess ‘登录帐户名’，’数据库用户名’        
（其中，“数据库用户“为可选参数，默认为登录帐户，
即数据库用户默认和登录帐户同名。）
例：
USE stuDB
GO
EXEC sp_grantdbaccess
       'jbtraining/S26301', 'S26301DBUser' 
EXEC sp_grantdbaccess
        'zhangsan', 'zhangsanDBUser'

◆dbo  用户
表示数据库的所有者（DB Owner）
无法删除 dbo 用户，此用户始终出现在每个数据库中

◆guest 用户
适用于没有数据库用户的登录帐号访问
每个数据库可有也可删除

◆向数据库用户授权 ：
语法为：GRANT 权限 [ON  表名 ]  TO  数据库用户

USE  stuDB
GO
/*--为zhangsanDBUser分配对表stuInfo的select, insert, update权限--*/
GRANT select, insert, update ON  stuInfo  TO  zhangsanDBUser 
/*--为S26301DBUser分配建表的权限--*/
GRANT  create  table  TO  S26301DBUser

---------------------------------------第三章 T-SQL编程---------------------------------------------------------------
◆数据库的物理实现一般包括：
创建数据库
创建表
◆添加各种约束
创建数据库的登录帐户并授权
创建数据库或表时一般需要预先检测是否存在该对象
数据库从master系统数据库的sysdatabases表中查询
表从该数据库的系统表sysobjects表中查询
◆访问SQL Server某个数据库中的某个表，需要三层验证：
是否是SQL Server的登录帐户
是否是该数据库的用户
是否有足够的权限访问该表

◆声明局部变量：DECLARE   @变量名  数据类型
例：
DECLARE @name varchar(8)
DECLARE @seat int

◆变量赋值：
1） SET @变量名 =值   
SET @name=‘张三’
2） SELECT  @变量名 = 值----------必须确保筛选出的记录只有1条
SELECT @name=stuName FROM stuInfo
        WHERE stuNo=‘s25302’
例：
DECLARE @name varchar(8)  --学员姓名
SET @name='李文才'        --使用SET赋值
SELECT * FROM stuInfo WHERE stuName = @name
/*--查找李文才的左右同桌--*/
DECLARE @seat int  --座位号
SELECT @seat=stuSeat FROM stuInfo  --使用SELECT赋值
   WHERE stuName=@name
SELECT * FROM stuInfo
   WHERE (stuSeat = @seat+1) OR (stuSeat = @seat-1)
GO
◆全局变量 ：使用两个@标志作为前缀
常用的全局变量 ：
@@ERROR 最后一个T-SQL错误的错误号
@@IDENTITY 最后一次插入的标识值
@@LANGUAGE 当前使用的语言的名称
@@MAX_CONNECTIONS 可以创建的同时连接的最大数目
@@ROWCOUNT  受上一个SQL语句影响的行数
@@SERVERNAME 本地服务器的名称
@@TRANSCOUNT  当前连接打开的事务数
@@VERSION  SQL Server的版本信息
例：
print  'SQL Server的版本'+@@VERSION
print  '服务器的名称: '+@@SERVERNAME
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge)  
     VALUES('武松','s25328','男','23')
--如果大于0表示上一条语句执行有错误
print '当前错误号'+convert(varchar(5),@@ERROR)
 print '刚才报名的学员，座位号为:' 
              +convert(varchar(5),@@IDENTITY )
UPDATE stuinfo SET stuAge=85
       WHERE stuName='李文才'
print '当前错误号'+convert(varchar(5),@@ERROR)
GO
◆逻辑控制语句：
（1）IF－ELSE语句：ELSE是可选部分. 如果有多条语句，才需要BEGIN-END语句块
IF （条件）
  BEGIN
    语句1
    语句2
    ……
  END
ELSE
 BEGIN
    语句1；
    语句2；
    ……
  END
（2）WHILE循环语句：
WHILE （条件）
  BEGIN
    语句1
    语句2
    ……
    BREAK
  END
例：
DECLARE @n int
WHILE(1=1) --条件永远成立
  BEGIN
    SELECT @n=COUNT(*) FROM stuMarks
            WHERE writtenExam<60    --统计不及格人数
    IF (@n>0)
       UPDATE stuMarks     --每人加2分
            SET writtenExam=writtenExam+2
   ELSE
       BREAK    --退出循环
  END
print '加分后的成绩如下：'
SELECT * FROM stuMarks
（3）CASE－END多分支语句：
CASE
  WHEN 条件1 THEN  结果1
  WHEN 条件2 THEN  结果2
  ……
  ELSE 其他结果
END
例：
print  'ABCDE五级显示成绩如下：'
SELECT stuNo,
   成绩= CASE 
                  WHEN writtenExam<60 THEN 'E'
                  WHEN writtenExam BETWEEN 60 AND 69 THEN 'D'
                  WHEN writtenExam BETWEEN 70 AND 79 THEN 'C'
                  WHEN writtenExam BETWEEN 80 AND 89 THEN 'B'
                  ElSE    'A'
              END
      FROM stuMarks
◆批处理语句：
批处理是包含一个或多个 SQL 语句的组，从应用程序一次性地发送到SQL Server执行
SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条

GO是批处理的标志，表示SQL Server将这些T-SQL语句编译为一个执行单元，提高执行效率
一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定

。。SQLServer规定：
如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在语句末尾添加 GO 批处理标志

---------------------------------------第四章 高级查询---------------------------------------------------------------
◆合并多个表中的数据的方法有三种：
联合(Union)－合并多个数据表中的行
子查询－将一个查询包含到另一个查询中
联接－合并多个数据表中的列

◆通过在子查询中使用EXISTS子句，可以对子查询中的行是否存在进行检查

◆IN子查询后面可跟随返回多条记录的子查询，用于检测某列的值是否在某个范围

◆什么是子查询？
子查询在WHERE语句中的一般用法：
    SELECT … FROM 表1 WHERE 字段1 >(子查询)
 外面的查询称为父查询，括号中嵌入的查询称为子查询
UPDATE、INSERT、DELETE一起使用，语法类似于SELECT语句
将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个
例：
问题：问题：
编写T-SQL语句，查看年龄比“李斯文”大的学员，要求显示这些学员的信息 ？
（1）实现方法一：采用T-SQL变量实现
DECLARE @age INT  --定义变量，存放李斯文的年龄
SELECT @age=stuAge FROM stuInfo
     WHERE stuName=‘李斯文’      --求出李斯文的年龄
--筛选比李斯文年龄大的学员
SELECT * FROM stuInfo WHERE stuAge>@age
 GO
（2）采用子查询实现
SELECT * FROM stuInfo
WHERE stuAge>( SELECT stuAge FROM 
                      stuInfo where stuName='李斯文')
GO

◆使用子查询替换表连：
例：
查询笔试刚好通过（60分）的学员。
（1）采用表连接
SELECT stuName FROM stuInfo
    INNER JOIN   stuMarks
       ON  stuInfo.stuNo=stuMarks.stuNo
           WHERE writtenExam=60
GO
（2）采用子查询
SELECT stuName FROM stuInfo
     WHERE stuNo=(SELECT stuNo FROM 
                stuMarks WHERE writtenExam=60)
GO

◆IN子查询 ：
IN后面的子查询可以返回多条记录
常用IN替换等于（＝）的比较子查询
例：
问题：查询笔试刚好通过的学员名单。
SELECT stuName FROM stuInfo
   WHERE stuNo IN
     (SELECT stuNo FROM  stuMarks
             WHERE writtenExam=60)
GO
问题：查询参加考试的学员名单
/*--采用IN子查询参加考试的学员名单--*/
SELECT stuName FROM stuInfo
  WHERE stuNo IN (SELECT stuNo FROM stuMarks)
GO

◆NOT IN子查询
问题：查询未参加考试的学员名单
加上否定的NOT 即可

◆EXISTS子查询
例如：数据库的存在检测
IF EXISTS（SELECT * FROM 
       sysDatabases WHERE name=’stuDB’）
    DROP DATABASE stuDB
CREATE DATABASE stuDB
…….

◆EXISTS子查询的语法：
如果子查询的结果非空，即记录条数1条以上，则EXISTS （子查询）将返回真（true），否则返回假(false)
EXISTS也可以作为WHERE 语句的子查询，但一般都能用IN子查询替换

问题：检查本次考试，本班如果有人笔试成绩达到80分以上，则每人提2分；否则，每人允许提5分
/*--采用EXISTS子查询，进行酌情加分--*/
IF EXISTS (SELECT * FROM stuMarks WHERE writtenExam>80)
  BEGIN
    print '本班有人笔试成绩高于80分，每人加2分，加分后的成绩为：'
    UPDATE stuMarks SET writtenExam=writtenExam+2
    SELECT * FROM stumarks
  END
ELSE
  BEGIN
    print '本班无人笔试成绩高于80分，每人可以加5分，加分后的成绩：'
    UPDATE stuMarks SET writtenExam=writtenExam+5
    SELECT * FROM stumarks
  END
GO

◆NOT EXISTS子查询
问题：
检查本次考试，本班如果没有一人通过考试（笔试和机试成绩都>60分），则试题偏难，每人加3分，否则，每人只加1分
IF NOT EXISTS (SELECT * FROM stuMarks WHERE writtenExam>60 AND labExam>60)
  BEGIN
    print '本班无人通过考试，试题偏难，每人加3分，加分后的成绩为：'
    UPDATE stuMarks
         SET writtenExam=writtenExam+3,labExam=labExam+3
    SELECT * FROM stuMarks
  END
ELSE
  BEGIN
    print '本班考试成绩一般，每人只加1分，加分后的成绩为：'
    UPDATE stuMarks
         SET writtenExam=writtenExam+1,labExam=labExam+1
    SELECT * FROM stuMarks
  END
GO

---------------------------------------第五章 事务、视图、所以、索引--------------------------------------------------------
◆事务(TRANSACTION)
是作为单个逻辑工作单元执行的一系列操作
。。这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行
。。事务是一个不可分割的工作逻辑单元
。。转帐过程就是一个事务。

◆事务的特性
事务必须具备以下四个属性，简称ACID 属性：
原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
一致性（Consistency）：当事务完成时，数据必须处于一致状态
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

◆如何创建事务
T-SQL使用下列语句来管理事务：
开始事务：BEGIN TRANSACTION
提交事务：COMMIT TRANSACTION
回滚（撤销）事务：ROLLBACK TRANSACTION
。。一旦事务提交或回滚，则事务结束。

◆判断某条语句执行是否出错：
使用全局变量@@ERROR；
@@ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；
            如： SET @errorSum=@errorSum+@@error

◆事务的分类：
。。显示事务：用BEGIN TRANSACTION明确指定事务的开始，这是最常用的事务类型
。。隐性事务：通过设置SET IMPLICIT_TRANSACTIONS ON 语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务。当该事务完成时，再下一个 T-SQL 语句又将启动一个新事务
。。自动提交事务：这是 SQL Server 的默认模式，它将每条单独的 T-SQL 语句视为一个事务，如果成功执行，则自动提交；如果错误，则自动回滚

◆使用事务解决银行转帐问题
……关键语句讲解………
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
    print '交易失败，回滚事务'
    ROLLBACK TRANSACTION
  END 
ELSE
  BEGIN
    print '交易成功，提交事务，写入硬盘，永久的保存'
    COMMIT TRANSACTION  
  END
GO
print '查看转帐事务后的余额'
SELECT * FROM bank 
GO

◆什么是索引：
SQL Server中的数据也是按页（ 4KB ）存放
索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 。
索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。
索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。

◆索引类型：
。。唯一索引：唯一索引不允许两行具有相同的索引值
。。主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
。。聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
。。非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

◆使用T-SQL语句创建索引的语法：
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] 
    INDEX   index_name
     ON table_name (column_name…)
      [WITH FILLFACTOR=x]
UNIQUE表示唯一索引，可选
CLUSTERED、NONCLUSTERED表示聚集索引还是
   非聚集索引，可选
FILLFACTOR表示填充因子，指定一个0到100之间的 
   值，该值指示索引页填满的空间所占的百分比

例：在stuMarks表的writtenExam列创建索引
USE stuDB
GO
IF EXISTS (SELECT name FROM sysindexes
          WHERE name = 'IX_writtenExam')
   DROP INDEX stuMarks.IX_writtenExam 
/*--笔试列创建非聚集索引：填充因子为30％--*/
CREATE NONCLUSTERED INDEX IX_writtenExam
     ON stuMarks(writtenExam)
    WITH FILLFACTOR= 30
GO
/*-----指定按索引 IX_writtenExam 查询----*/
SELECT * FROM stuMarks  (INDEX=IX_writtenExam)
    WHERE writtenExam BETWEEN 60 AND 90

◆索引的优缺点：
优点：
加快访问速度
加强行的唯一性
缺点：
带索引的表在数据库中需要更多的存储空间
操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新

◆创建索引的指导原则
。。请按照下列标准选择建立索引的列：
该列用于频繁搜索
该列用于对数据进行排序
。。请不要使用下面的列创建索引：
列中仅包含几个不同的值。
表中仅包含几行。为小型表创建索引可能不太划算，因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

◆什么是视图
。。视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
。。视图中并不存放数据，而是存放在视图所引用的原始表（基表）中
。。同一张原始表，根据不同用户的不同需求，可以创建不同的视图

◆视图的用途
筛选表中的行
防止未经许可的用户访问敏感数据
降低数据库的复杂程度
将多个物理数据库抽象为一个逻辑数据库

◆使用T-SQL语句创建视图的语法
CREATE VIEW view_name 
   AS
    <select语句>
例：方便教员查看视图
IF EXISTS (SELECT * FROM sysobjects WHERE
                         name = 'view_stuInfo_stuMarks')
     DROP VIEW view_stuInfo_stuMarks
GO
CREATE VIEW view_stuInfo_stuMarks
  AS
    SELECT 姓名=stuName,学号=stuInfo.stuNo,
      笔试成绩 =writtenExam,  机试成绩=labExam,
            平均分=(writtenExam+labExam)/2
               FROM stuInfo LEFT JOIN stuMarks
                     ON stuInfo.stuNo=stuMarks.stuNo
GO
SELECT * FROM view_stuInfo_stuMarks

---------------------------------------第六章 存储过程--------------------------------------------------------
◆存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等
◆存储过程允许带参数，参数分为：
输入参数
输出参数
   其中，输入参数可以有默认值。
◆输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值
◆输出参数从存储过程中返回（输出）值，后面跟随OUTPUT关键字
◆RAISERROR语句用来向用户报告错误

◆什么是存储过程
存储过程（procedure）类似于C语言中的函数
用来执行管理任务或应用复杂的业务规则
。。存储过程可以带参数，也可以返回结果
。。存储过程可以包含数据操纵语句、变量、逻辑 控制语句等

◆存储过程的优点
执行速度更快
允许模块化程序设计
提高系统安全性
减少网络流通量

◆存储过程的分类
（1）系统存储过程
由系统定义，存放在master数据库中
类似C语言中的系统函数
系统存储过程的名称都以“sp_”开头或”xp_”开头
（2）用户自定义存储过程
由用户在自己的数据库中创建的存储过程
类似C语言中的用户自定义函数

◆常用的系统存储过程
sp_databases 列出服务器上的所有数据库。
sp_helpdb 报告有关指定数据库或所有数据库的信息
sp_renamedb 更改数据库的名称
sp_tables 返回当前环境下可查询的对象的列表
sp_columns 回某个表列的信息
sp_help 查看某个表的所有信息
sp_helpconstraint 查看某个表的约束
sp_helpindex 查看某个表的索引
sp_stored_procedures 列出当前环境中的所有存储过程。
sp_password 添加或修改登录帐户的密码。
sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。
例：
EXEC sp_databases
EXEC  sp_renamedb 'Northwind','Northwind1'
USE stuDB
GO
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 
。。常用的扩展存储过程：xp_cmdshell
可以执行DOS命令下的一些的操作
以文本行方式返回任何输出
。。调用语法：
  EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

◆定义存储过程的语法
    CREATE  PROC[EDURE]  存储过程名
              @参数1  数据类型 = 默认值 OUTPUT,
               …… ,
              @参数n  数据类型 = 默认值 OUTPUT
            AS
            SQL语句
    GO
和C语言的函数一样，参数可选
参数分为输入参数、输出参数
输入参数允许有默认值

◆调用存储过程：
EXECUTE（执行）语句用来调用存储过程

调用的语法：EXEC  过程名  [参数]
例： 
EXEC proc_stu

◆创建带参数的存储过程
存储过程的参数分两种：
1。输入参数：
    用于向存储过程传入值，类似C语言的按值传递;
2。 输出参数：——其中，输入参数可以有默认值。
    用于在调用存储过程后，
    返回结果，类似C语言的
    按引用传递。
例：
CREATE PROCEDURE proc_stu
  @writtenPass int, 
  @labPass int   
  AS
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,
       labExam  FROM  stuInfo
          INNER JOIN stuMarks ON              
             stuInfo.stuNo=stuMarks.stuNo
                 WHERE writtenExam<@writtenPass
                                                  OR labExam<@labPass
GO

◆调用带参数默认值的存储过程
EXEC proc_stu   --都采用默认值
EXEC proc_stu 64  --机试采用默认值
EXEC proc_stu 60,55   --都不采用默认值
EXEC proc_stu @labPass=55 --第2个参数正确的调用方式：

◆带输出参数的存储过程
如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了
例：
CREATE PROCEDURE proc_stu
  @notpassSum int OUTPUT,
  @writtenPass int=60,  
  @labPass int=60 
  AS
    ……
     SELECT stuName,stuInfo.stuNo,writtenExam,
        labExam FROM  stuInfo   INNER JOIN stuMarks
          ON stuInfo.stuNo=stuMarks.stuNo
            WHERE writtenExam<@writtenPass
              OR labExam<@labPass
    SELECT @notpassSum=COUNT(stuNo)
       FROM stuMarks  WHERE writtenExam<@writtenPass
           OR labExam<@labPass
GO

◆调用带输出参数的存储过程
/*---调用存储过程----*/
DECLARE @sum int  
EXEC proc_stu @sum OUTPUT ,64 
print '--------------------------------------------------'
IF @sum>=3
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,
        超过60%,及格分数线还应下调'
ELSE
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,
        已控制在60%以下，及格分数线适中'
GO

◆处理存储过程中的错误
1.可以使用PRINT语句显示错误信息，但这 些信息是临时的，只能显示给用户
2.RAISERROR 显示用户定义的错误信息时
可指定严重级别，
设置系统变量@@ERROR
记录所发生的错误等

◆RAISERROR语句的用法如下：
RAISERROR (msg_id | msg_str,severity,
   state WITH option[,...n]])
msg_id：在sysmessages系统表中指定用户定义错误信息
msg_str：用户定义的特定信息，最长255个字符
severity：定义严重性级别。用户可使用的级别为0–18级
state：表示错误的状态，1至127之间的值
option：指示是否将错误记录到服务器错误日志中
完善上例：
CREATE PROCEDURE proc_stu
  @notpassSum int OUTPUT, --输出参数
  @writtenPass int=60,  --默认参数放后
  @labPass int=60       --默认参数放后
  AS
    IF (NOT @writtenPass BETWEEN 0 AND 100)
             OR (NOT @labPass BETWEEN 0 AND 100)
       BEGIN
         RAISERROR (‘及格线错误，请指定0－100之间的分
                     数，统计中断退出',16,1)
         RETURN  ---立即返回，退出存储过程
       END
    …..其他语句同上例，略
GO
/*---调用存储过程，测试RAISERROR语句----*/
DECLARE @sum int,  @t int
EXEC proc_stu @sum OUTPUT ,604  
SET @t=@@ERROR
print  '错误号：'+convert(varchar(5),@t )
IF @t<>0 
   RETURN  --退出批处理，后续语句不再执行
print '--------------------------------------------------'
IF @sum>=3
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'
ELSE
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'
GO

----------------------------------------------------------------------------------

◆什么是触发器
银行存取款问题——最优的解决方案就是采用触发器：
。它是一种特殊的存储过程
。也具备事务的功能
。它能在多表之间执行特殊的业务规则

触发器：
是在对表进行插入、更新或删除操作时自动执行的存储过程
。触发器通常用于强制业务规则
。触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束
。可执行复杂的SQL语句（if/while/case）
。可引用其它表中的列
。触发器定义在特定的表上，与表相关
。自动触发执行
。不能直接调用
。是一个事务（可回滚）

◆触发器的类型
DELETE 触发器
INSERT 触发器
UPDATE 触发器

◆inserted 和deleted 表
1）触发器触发时：
系统自动在内存中创建deleted表或inserted表
只读，不允许修改；触发器执行完成后，自动删除
2）inserted 表
临时保存了插入或更新后的记录行
可以从inserted表中检查插入的数据是否满足业务需求
  如果不满足，则向用户报告错误消息，并回滚插入操作
3）deleted 表
临时保存了删除或更新前的记录行
可以从deleted表中检查被删除的数据是否满足业务需求
如果不满足，则向用户报告错误消息，并回滚插入操作

◆inserted表和deleted表存放的信息：
修改操作         inserted表         deleted表
增加(INSERT)记录 存放新增的记录         ------
删除(DELETE)记录 -----                 存放被删除的记录
修改(UPDATE)记录 存放更新后的记录 存放更新前的记录

◆如何创建触发器：
创建的语法：
CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]----------------------WITH ENCRYPTION表示加密触发器定义的SQL文本
  FOR [DELETE, INSERT, UPDATE]----------DELETE, INSERT, UPDATE指定触发器的类型
 AS
  T-SQL语句
GO

◆INSERT触发器的工作原理：
插入记录行——触发insert触发器。向inserted表中插入新行的副本——触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作

INSERT 触发器示例 ：
问题：
解决上述的银行取款问题：当向交易信息表(transInfo)中插入一条交易信息时，我们应自动更新对应帐户的余额。
分析：
在交易信息表上创建INSERT触发器
从inserted临时表中获取插入的数据行
根据交易类型(transType)字段的值是存入/支取，
 增加/减少对应帐户的余额。
-------关键代码------
CREATE TRIGGER trig_transInfo
 ON transInfo
  FOR INSERT
   AS
   DECLARE @type char(4),@outMoney MONEY
   DECLARE @myCardID char(10),@balance MONEY
   SELECT @type=transType,@outMoney=transMoney，
         @myCardID=cardID FROM inserted
     IF (@type='支取')
        UPDATE bank SET currentMoney=currentMoney-@outMoney
             WHERE cardID=@myCardID
    ELSE
        UPDATE bank SET currentMoney=currentMoney+@outMoney
             WHERE cardID=@myCardID
    …..
GO

◆DELETE触发器的工作原理：
删除记录行——触发delete触发器向deleted表中插入被删除的副本——触发器检查deleted表中被删除的数据，决定是否需要回滚或执行其他操作

DELETE触发器示例 ：
问题：
当删除交易信息表时，要求自动备份被删除的数据到表backupTable中 。
分析：
在交易信息表上创建DELETE触发器
被删除的数据可以从deleted表中获取
-------关键代码------
CREATE TRIGGER trig_delete_transInfo
 ON transInfo
  FOR DELETE
   AS
      print '开始备份数据，请稍后......'
      IF NOT EXISTS(SELECT * FROM sysobjects
           WHERE name='backupTable')
         SELECT * INTO backupTable FROM deleted
     ELSE
         INSERT INTO backupTable SELECT * FROM deleted
      print '备份数据成功，备份表中的数据为:'
      SELECT * FROM backupTable
GO

◆UPDATE触发器的工作原理：
插入记录行——向deleted表中插入被删除的副本——向inserted表中插入被添加的副本——检查deleted和inserted表中的数据，确定是否需要回滚或执行其他操作

UPDATE触发器示例 ：
问题：
跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。      
分析：
在bank表上创建UPDATE触发器
修改前的数据可以从deleted表中获取
修改后的数据可以从inserted表中获取
-------关键代码------
CREATE TRIGGER trig_update_bank
 ON bank
  FOR UPDATE
   AS
      DECLARE @beforeMoney MONEY,@afterMoney MONEY 
      SELECT @beforeMoney=currentMoney FROM deleted  
      SELECT @afterMoney=currentMoney FROM inserted   
      IF ABS(@afterMoney-@beforeMoney)>20000
        BEGIN
            print '交易金额:'+convert(varchar(8),
                ABS(@afterMoney-@beforeMoney))
            RAISERROR ('每笔交易不能超过2万元，交易失败',16,1)
            ROLLBACK TRANSACTION
         END
GO
           
◆列级 UPDATE 触发器 ：
。。UPDATE触发器除了跟踪数据的变化（修改）外，还可以检查是否修改了某列的数据
。。使用UPDATE（列）函数检测是否修改了某列
例：
问题：
交易日期一般由系统自动产生，默认为当前日期。为了安全
起见，一般禁止修改，以防舞弊。
分析：
UPDATE（列名）函数可以检测是否修改了某列
-------关键代码------
CREATE TRIGGER trig_update_transInfo
 ON transInfo
  FOR UPDATE
   AS
      IF UPDATE(transDate)
         BEGIN
            print '交易失败.....'
            RAISERROR (‘安全警告：交易日期不能修改，
                           由系统自动产生',16,1)
            ROLLBACK TRANSACTION   
         END
GO