第4章T-SQL与可编程对象 SQL语言是结构化查询语言（Structure Query Language）的简称，它是一个通用的、功能强大的关系数据库操作语言，最初由IBM公司在20世纪70年代中期开发成功。并被国际标准化组织采纳为关系数据库语言的国际标准。 Transact-SQL（ 简称T-SQL）是SQL Server中支持的扩展SQL语言，它提供了数据定义、数据操纵、数据控制等语句，支持对数据库的操纵和管理，是开发数据库应用程序的基本语言。 SQL Server支持采用T-SQL语言定义服务器端应用程序，这些应用程序被统称为可编程对象。

面向过程的程序设计语言，必须一步 一步地将解题的全过程描述出来，不 仅要告诉计算机“做什么”，而且还 要告诉计算机“怎么做”。 4.1.1 T-SQL概述 1. T-SQL语言的特点 1）非过程化 ：使用T-SQL时，不必描述解决问题的全过程，只需提出“做什么”，至于“如何做”的细节则由语言系统本身去完成并给出操作的结果。 2）两种不同的使用方式：两种方式下，T-SQL语言的语法结构基本相同。 联机交互方式：SQL语言可独立使用 嵌入程序设计语言中：例如嵌入C#、VB.net中。 3) 高度一体化：集数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言（DCL）和T-SQL增加的语言元素于一体，可独立完成数据库生命周期的所有活动。 4) 语言简洁、易学易用。

2.T-SQL语言的组成元素 DDL(Data Definition Language) 数据库定义语言，用于定义或修改数据库。包括Create、Alter、Drop等语句。 例如：Create DATABASE Student DML(Data Manipulation Language) 数据库操纵语言，实现对数据库基本操作，包括Selete、Insert 、Update、Delete等语句。 DCL(Data Control Language) 数据库控制语言，实现对数据对象的授权、数据完整性规则描述及控制事务等，包括Grant、Revoke、Deny等语句。 T-SQL增加的语言元素 变量、运算符、函数、流程控制语句和注解等。

3．T-SQL语言书写规则 ①在T-SQL语句中，不区分字母大小写，即大小写字母的意义完全相同。 ② 一条语句可以写在一行，也可以写在若干行上。 在本讲义中： 为了阅读方便，用大写字母书写SQL语句的关键词，用小写字母书写标识符、表达式及各种参数； 为了体现语法结构，一条语句写在多行上。 例如： SELECT * FROM Student

为了增强程序代码的可读性，可在适当的地方加上注释。 T-SQL有两种注释方法： ① 单行注释（--）。语法格式如下： -- 注释文本内容 4．注释 为了增强程序代码的可读性，可在适当的地方加上注释。 T-SQL有两种注释方法： ① 单行注释（--）。语法格式如下： -- 注释文本内容 ② 多行注释（/* … */）。语法格式如下： /* 注释文本内容*/ 例如： USE School /*打开School数据库。当第一次访问某数据库时，需要使用USE语句打开数据库，否则其他访问该数据库的SQL语句不能执行*/ SELECT * FROM Student --查询显示学生表中所有学生的信息

4.1.2 数据定义语言DDL 1．DDL概述 数据定义语言主要是定义数据库中的对象，包括数据库、数据表、视图、索引、存储过程、触发器等的建立或修改。常用语句如下： 操作对象 操作方式 创建 删除 修改 使用 数据库 CREATE DATABASE DROP DATABASE ALTER DATABASE USE 基本表 CREATE TABLE DROP TABLE ALTER TABLE 视图 CREATE VIEW DROP VIEW ALTER VIEW 索引 CREATE INDEX DROP INDEX 存储过程 CREATE PROCEDURE DROP PROCEDURE ALTER PROCEDURE 触发器 CREATE TRIGGER DROP TRIGGER ALTER TRIGGER

2．自动生成数据库定义的SQL脚本 前面已经学习过使用SQL Server Management Studio创建各种数据库对象，实质上手工创建的过程就是在构建DDL语句。 如果想查看或保存已存在的数据库对象的DDL语句，可在SQL Server Management Studio中自动生成各种数据库对象的SQL脚本 ，也可将其保存到一个扩展名为.sql的文件中 ，在查询编辑器中可打开.sql文件，执行SQL脚本，重建相应的数据库 。 提示：使用SQL定义脚本重建的数据库不包括表中数据。

SQL脚本的生成方法如下： ① 右击要生成SQL脚本的数据库或数据表，选“编写数据库(或表)脚本为/Create到/新查询编辑窗口”。 ② 在“查询编辑”窗口可查看和复制SQL脚本。选择“文件/保存”或“文件/另存为”菜单可将这些SQL脚本保存到一个扩展名为“.sql”的文本文件。 ③ 使用“文件/打开”菜单可打开脚本文件。执行可创建新的表。

3．几个常用DDL语句 （1）创建数据库 CREATE DATABASE 数据库名 [ON NAME=逻辑文件名， FILENAME=物理文件名] 提示：[ ]表示可选项 【例4.1】创建一个名为SchoolTest的数据库。 答案1 : CREATE DATABASE SchoolTest --数据库名 以系统数据库model作为模板在SQL Server安装文件夹的Data文件夹下（一般为C:\Program File\Microsoft SQL Server\MSSQL\Data）建立数据库文件SchoolTest.mdf（大小为3MB）和日志文件SchoolTest_log.LDF（大小为1MB）。

答案2 : CREATE DATABASE SchoolTest --数据库名 ON (NAME=‘SchoolTest’, --主数据文件逻辑名 FILENAME=‘D:\ SchoolTest.MDF’) --主数据文件物理名 创建了一个数据库SchoolTest，其主数据文件为大小为3MB的D:\SchoolTest.mdf“ ；并且自动创建一个大小为1MB的“D:\SchoolTest_log.LDF”作为日志文件。

USE 数据库名 --其中数据库名是要打开作为当前数据库的数据库例如：USE School --打开数据库School （2）打开和删除数据库语句 当用户登录到SQL Server后，系统指定系统数据库master作为默认当前数据库。所谓当前数据库是指当前可以操作的数据库。 打开数据库： USE 数据库名 --其中数据库名是要打开作为当前数据库的数据库例如：USE School --打开数据库School 删除数据库： DROP DATABASE 数据库名 例如： DROP DATABASE SchoolTest --删除SchoolTest 注意删除数据库时不允许该数据库有对象处于打开状态。

（3）建立数据表语句 CREATE TABLE数据表名 （列名 列数据类型 列约束 …… Constraint 约束说明 ） 【例4.2】在数据库“SchoolTest”中建立学生表StudentTest。 CREATE TABLE StudentTest (StudentCode CHAR(8) NOT NULL UNIQUE, /*不允许空，取值唯一*/ StudentName VARCHAR(16) NOT NULL, Sex CHAR(2) NOT NULL, LiveInDorm BIT DEFAULT 1， /*默认值为1*/ Constraint StudentPK Primary Key(StudentCode),/* StudentCode主键*/ Constraint SexCheck Check(Sex=‘男’ or Sex=‘女’) /* Sex检查约束*/ )

当数据表不再需要时，可以使用DROP TABLE语句将其删除。 （4）删除表语句 当数据表不再需要时，可以使用DROP TABLE语句将其删除。 DROP TABLE 数据表名 【例4.3】删除StudentTest表。 DROP TABLE StudentTest

（5）建立索引语句 （6）删除索引语句 CREATE INDEX 索引名 ON 数据表名(字段 ASC|DESC) 【例4.4】在StudentTest表的姓名（StudentName）和生日（Birthday）字段上建立唯一索引NameIndex, 姓名按升序排列，姓名相同时，按生日降序排列。 CREATE UNIQUE INDEX NameIndex on StudentTest (StudentName, Birthday DESC) （6）删除索引语句 DROP INDEX 数据表名.索引名 【例4.5】删除StudentTest表中名称为NameIndex的索引。 DROP INDEX StudentTest.NameIndex

DML语句实现对数据的查询和更新，主要语句及功能如下： SELECT 语句：从一个表或多个表查询数据 INSERT语句：向一个表中添加一条记录 UPDATE语句：修改表中某一个或几个字段的值 DELETE语句：从一个表中删除记录

1．查询语句SELECT 又称为选择查询语句，用于从数据库的一个或多个表中检索满足一定条件的记录集，并按一定顺序显示指定的字段内容。 FROM 表名 [ WHERE 查询条件 ] [ GROUP BY 分组字段 [ HAVING 分组条件 ] ] [ ORDER BY 字段名 [ ASC/DESC ] ] [ ]表示可选项。 第2行至最后一行称作SQL子句，不同的子句完成不同的功能。 在书写SELECT语句时，字母大小写意义完全相同。 语句可以写在若干行上（如上述语法格式中的描述），也可以不换行。

(1) 字段表 字段表位于关键字SELECT后面，这些字段将作为查询的结果被显示。 ①可选任意多个字段，字段与字段之间用逗号分隔。 【例4.6】查询Student表中的所有学生的姓名和性别。 SELECT StudentName, Sex FROM Student ②可以使用通配符“*”表示表中的所有字段。 【例4.7】查询Student表中所有学生的全部字段信息。 SELECT * FROM Student

③ 若字段名或表名中含有空格，则该字段名或表名必须用方括号括起来 。 ④在字段表中，可以使用如下方式将原字段名以字段别名显示： 字段名 AS 字段别名 【例4.8】查询Class表中班号和班名班号别名为Class Number 。 SELECT ClassCode AS [Class Number], ClassName FROM Class 提示： “Class Number”含空格，要写为[Class Number] SELECT ClassCode AS 班级编号, ClassName AS 班级名称 FROM Class

⑤在字段名前可以加上一些关键字，以便进一步优化查询结果。常用的关键字有： DISTINCT：若多条记录的字段值具有相同数据，只显示一条记录。 【例4.11】显示Course表中开课的学院名称Academy（与所开课程无关）。 增加Distinct限制后，查询结果中相同的值只显示一次。 SELECT DISTINCT Academy FROM Course 由于每个学院都开设了多门课程，因此多条记录在字段Academy具有部分相同的值。

TOP n |m PERCENT：查询前n条记录或前m%的记录。 【例4.9】查询Course表中的前3条记录。 SELECT TOP 3 * FROM Course 【例4.10】查询Course表中前20%的记录，显示课程号和课程名称。 SELECT TOP 20 PERCENT CourseCode, CourseName FROM Course

⑥ 新增字段查询。查询的字段可以是表中字段名，也可以是常量和表达式，表达式中可以使用内部聚合函数进行统计计算。 常用函数表 函数名 函数功能 AVG 计算某一字段的平均值（字段值必须是数值型） COUNT 统计某一字段的个数 MAX 查找某一字段的最大值 MIN 查找某一字段的最小值 SUM 计算某一字段的总和（字段的值必须是数值型）

【例4.12】 查询Grade表，显示学号、所选课程号及加2分调整后的成绩（Grade），并显示一列说明 “情况属实”。 SELECT StudentCode, CourseCode, Grade+2 AS 成绩, '情况属实' AS 说明 FROM Grade 【例4.13】查询Grade表，统计所有学生的分数总和、平均分、最高分、最低分及总选课人次。 SELECT Sum (Grade) AS 总分, Avg(Grade) AS 平均分, Max (Grade) AS 最高分, Min(Grade) AS 最低分, Count(StudentCode) AS 总人次 FROM Grade

提示：语句运行后，数据库School中产生一张表名为NewTable的新表。刷新数据库，即可查看到。 ⑦ 使用查询创建新表。在字段名后加上“INTO 表名”可将查询结果复制到一张新表中。 【例4.14】将Student表中所有记录的学生姓名、出生日期复制到一张新表NewTable。 StudentName, BirthDay INTO NewTable FROM Student 提示：语句运行后，数据库School中产生一张表名为NewTable的新表。刷新数据库，即可查看到。

（2）FROM子句 FROM子句是SELECT语句必不可少的一个子句，指定要查询的数据来自哪个或哪些表或视图。 FROM 表名1 [，表名2，……，表名n] 注意：表的排列顺序不影响执行结果；如果FROM子句中包含多个表名，且不同的表中具有相同的字段，那么SELECT子句的字段名必须表示成“表名.字段名”。 【例4.15】查询Student表和Grade表，显示学生的选课和成绩情况。 SELECT StudentCode, StudentName, CourseCode, Grade FROM Student, Grade () SELECT Grade.StudentCode, StudentName, CourseCode, Grade FROM Student, Grade WHERE Grade.StudentCode=Student.Studentcode

（3）WHERE子句 WHERE 查询条件 WHERE子句说明查询条件，它是一个可选的子句。它必须紧跟在FROM子句的后面。 其中，查询条件是一个关系或逻辑表达式。 T-SQL中常用的关系和逻辑运算 查询条件 谓词 比较 >、>=、<、<=、=、<>(不等于)、!>(不大于)、!<(不小于)、!=(不等于) 确定范围 BETWEEN …AND、NOT BETWEEN… AND 确定集合 IN、NOT IN、EXISTS 字符匹配 LIKE、NOT LIKE 空值 IS NULL、IS NOT NULL 否定 NOT 逻辑运算 AND、OR

① 比较和逻辑运算 【例4.16】查询Student表中所有女学生的姓名。 SELECT StudentName FROM Student WHERE Sex=‘女’ 【例4.17】查询未住校的女学生的情况。 SELECT StudentCode,StudentName, LiveInDorm FROM Student WHERE LiveInDorm=0 AND Sex=‘女’

② BETWEEN…AND…运算 BETWEEN 值1 AND 值2 NOT BETWEEN 值1 AND 值2 查询某字段的值在指定范围内的数据记录（结果包括值1和值2）。 NOT BETWEEN 值1 AND 值2 查询不在指定范围中的数据记录。 【例4.18】在Grade表中查询选修课程号为“101”、成绩在70分到90分之间的所有学生，显示其学号及成绩。 SELECT StudentCode, CourseCode, Grade FROM Grade WHERE CourseCode= '101‘ AND Grade BETWEEN 70 AND 90 此例的WHERE子句也可以改为： WHERE CourseCode=‘101' AND Grade>= 70 AND Grade<=90

【例4.19】查询Student表中不在1990年到1994年中出生的学生的学号和姓名。 SELECT StudentCode, StudentName, Birthday FROM Student WHERE Birthday NOT BETWEEN '1990-01-01' AND '1994-12-30' 在WHERE子句中可 使用Year()函数： SELECT StudentCode, StudentName, Birthday FROM Student WHERE Year(Birthday) NOT BETWEEN 1990 AND 1994

③确定在集合中的IN运算：查询某字段值在或不在某集合的数据记录 【例4.20】查询Student表中班号为“11”、“21”、“31”的学生。 SELECT StudentCode, StudentName, Sex, ClassCode FROM Student WHERE Classcode IN ('11','21','31') 此例的WHERE子句也可以改为： WHERE ClassCode='11' OR ClassCode='21' OR ClassCode='31'

④字符串模式匹配的LIKE运算：确定某字符字段的子串值是否符合指定的模式，可查询满足字符串匹配的数据记录。 例如： 姓“王”的名字： ‘王%’； 第2个字是“小”的姓名： ‘ _小%’ 姓“张”的单名： ‘张_’ 【例4.21】查询Course表中课程名称中包含“化学”两个字的课程号及课程名称。 SELECT CourseCode,CourseName FROM Course WHERE CourseName LIKE '%化学%' 【例4.22】查询Student表中不姓“王”的学生 。 SELECT StudentCode, StudentName FROM Student WHERE StudentName LIKE '[^王]%'

GROUP BY 分组[ HAVING 分组条件 ] HAVING子句：与WHERE子句作用类似，在使用GROUP BY完成分组后，显示满足HAVING子句中分组条件的所有记录。 【例4.30】统计每门课程的选课人数和平均分。 SELECT CourseCode, Count(StudentCode) AS 选课人数, STR(AVG(Grade),5,2) AS 平均分 FROM Grade GROUP BY CourseCode 如只查询平均分大于等于80的记录，则在语句后增加：[HAVING avg(Grade)>=80]

如果GROUP BY后的分组字段有多个，则表示多次分组。 【例4.31】统计各学院男、女教师的人数。 SELECT Academy, Sex, Count(TeacherCode) AS 教师人数 FROM Teacher GROUP BY Sex, Academy 注意：使用GROUP BY子句时，在SELECT后的字段必须出现在分组字段中（除非该字段包含在聚合函数中），否则不允许分组。

× 出错信息： SELECT Academy, Count(TeacherCode) AS 教师人数 FROM Teacher GROUP BY Sex × 出错信息：

ORDER BY 字段名 [ASC/DESC] (5) ORDER BY子句：按指定字段为查询结果排序。通常是 SQL 语句最后一项。 ORDER BY 字段名 [ASC/DESC] 其中：ASC为升序排序，DESC为降序排序。缺省排列次序为升序。 ASC: Ascending，DESC:Descending 【例4.32】按成绩升序显示Grade表中的所有数据。 SELECT * FROM Grade ORDER BY Grade

可以指定多个排序的字段。规则：首先用指定的第一个字段对记录排序，然后对此字段中具有相同值的记录用第二个字段进行排序，依此类推。 若在SELECT语句中无此子句，则按原数据表的次序显示数据。 【例4.33 】按性别升序（若性别相同，则按出生日期降序）显示学生姓名、性别及出生日期。 SELECT StudentName ,Sex,Birthday FROM Student ORDER BY Sex, Birthday DESC Sex 默认为ASC

(6). 联接查询 ：涉及多表的查询称为联接查询，可以用两种方法实现。 ①在FROM子句中指出需查询的表，在WHERE子句中说明两个表相关联字段的联接条件 。 【例4.24】查询优异生的情况。要求显示学生姓名、所在班级、认定时间、学分积点。 注意：指明Student表 和ExcellentStudent表中StudentCode字段值相同的记录相联接。若在查询时无此条件，查询结果错误。 SELECT StudentName, ClassName, GrantTime, GPA FROM ExcellentStudent, Student, Class WHERE ExcellentStudent.StudentCode=Student.StudentCode AND Student.ClassCode=Class.ClassCode

【例4.23】查询未住校学生的选课及成绩的情况。(显示学号、姓名、课程号以及是否住校） SELECT Student.StudentCode, StudentName, CourseCode, Grade, LiveinDorm FROM Grade, Student WHERE Grade.StudentCode=Student.StudentCode AND LiveInDorm=0 注意：Student表和Grade表都有StudentCode字段，所以要在Select后的该字段名前指明其中一个表名Student.StudentCode 。

ON 表1.字段名1 <比较运算符> 表2.字段名2 ②使用联接关键字JOIN … ON说明两个表及相关联字段的联接条件。 FROM 表1 联接关键字 表2 ON 表1.字段名1 <比较运算符> 表2.字段名2 其中： 表1、表2是被联接的表名； 字段名是被联接的字段。必须有相同的数据类型并包含同类数据，但不需要有相同的名称。 比较运算符：=、<、>、<=、>=、<> 常用联接关键字：INNER JOIN （内联接）、LEFT OUTER JOIN（左外联接）、RIGHT OUTER JOIN（右外联接）

INNER JOIN（内联接）：查询结果仅包含两个表中每对联接匹配的行。内联接是系统默认的，可以将关键词INNER省略。 【例4.25】使用内联接查询未住校学生的选课及成绩情况。 SELECT Student.StudentCode, StudentName, CourseCode, Grade, LiveinDorm FROM Student INNER JOIN Grade ON Student.StudentCode = Grade.StudentCode WHERE LiveInDorm=0 本句的查询结果与例4.23完全相同，但语句中表的联接关系表达更为清晰，WHERE子句只有一个条件“LiveInDorm=0”。

【例4.26】使用左外联接查询未住校学生的选课及成绩情况。 LEFT OUTER JOIN（左外联接）：结果除了包含两张表中符合联接条件的记录，还包含左表（写在关键字LEFT OUTER JOIN左边的表）中不符合联接条件、但符合WHERE条件的全部记录。可以将关键字OUTER省略。 【例4.26】使用左外联接查询未住校学生的选课及成绩情况。 SELECT Student.StudentCode, StudentName, CourseCode, Grade, LiveInDorm FROM Student LEFT JOIN Grade ON Student.StudentCode = Grade.StudentCode WHERE LiveInDorm=0 有2条记录的CourseCode和Grade字段值为NULL。这2条记录是左表（Student）中未住校学生的信息，但在Grade表中没有其相关选课记录。

RIGHT OUTER JOIN（右外联接）：结果除了包含两张表中符合联接条件的记录，还包含右表（写在关键字RIGHTT OUTER JOIN右边的表）中不符合联接条件、但符合WHERE条件的全部记录。可以将关键字OUTER省略。 【例4.27】使用右外联接查询未住校学生的选课及成绩情况。 SELECT Student.StudentCode, StudentName, CourseCode, Grade, LiveInDorm FROM Grade RIGHT JOIN Student ON Student.StudentCode = Grade.StudentCode WHERE LiveInDorm=0 结果与例4.25内联接完全相同。由于在表设计时，在表Student和Grade之间通过外键建立了参照完整性约束，表Grade中的所有StudentCode必须是Student中出现的值。

③ 多表联接嵌套：如果查询所涉及的数据表在3个以上，形成联接嵌套 。 例如，查询学生所选课程成绩，显示学生姓名、课程名称，成绩。涉及Student 、Grade 、Course 三张表。 1）使用WHERE子句设置查询条件 SELECT StudentName, CourseName , Grade FROM Student, Grade , Course WHERE Student.StudentCode = Grade.StudentCode AND Grade.CourseCode = Course.CourseCode

2）使用联接关键字的语句 FROM 表1 JOIN表2 ON 表1.字段i <比较运算符> 表2.字段j JOIN 表3 ON 表2.字段k <比较运算符> 表3.字段l … [ JOIN表n ON 表y.字段m<比较运算符> 表n.字段n] x<=2, y<=n-1 SELECT StudentName, CourseName, Grade FROM Student JOIN Grade ON Student.StudentCode =Grade.StudentCode JOIN Course ON Grade.CourseCode = Course.CourseCode 或者写为如下格式： FROM 表1 JOIN表2 JOIN 表3 … [JOIN表n ON 表n.字段i<比较运算符> 表n-1字段j ON 表x.字段k <比较运算符> 表n-2.字段l … ON 表y.字段m<比较运算符> 表1.字段n] x>=n-1, y>=2 SELECT StudentName, CourseName, Grade FROM Student JOIN Grade JOIN Course ON Grade.CourseCode = Course.CourseCode ON Student.StudentCode =Grade.StudentCode

【例4.28】用嵌套联结实现例4.24查询优异生的情况。 SELECT StudentName, ClassName, GrantTime, GPA FROM ExcellentStudent JOIN Student ON ExcellentStudent.StudentCode=Student.StudentCode JOIN Class ON Student.ClassCode=Class.ClassCode 结果与例4.24使用Where子句说明联接条件的结果完全相同。

【例4.29】查询班号是“61”的学生姓名、班号、所选课程名称和成绩。该查询需要用到3张表Student、Course和Grade。 SELECT StudentName, ClassCode, CourseName, Grade FROM Student JOIN Grade JOIN Course ON Grade.CourseCode = Course.CourseCode ON Student.StudentCode = Grade.StudentCode WHERE ClassCode='61'

(7).子查询 : 当一个查询的结果是另一个查询的条件时，称该查询为子查询。也被称为嵌套查询。 【例4.34】查询“林豆豆”同班同学的学号、姓名。 SELECT StudentCode, StudentName, ClassCode FROM Student WHERE ClassCode= (SELECT ClassCode WHERE StudentName='林豆豆‘)

如果子查询结果返回多值则子查询要与下面介绍的IN、EXIST等结合使用。 【例4.35】查询选修了课程代码为“101”的学生的学号、姓名和班号。 SELECT StudentCode,StudentName, ClassCode FROM Student WHERE StudentCode IN (SELECT StudentCode FROM Grade WHERE CourseCode='101') 提示：在执行过程中，子查询得到选修课程“101”的所有学生学号返回给主查询，主查询再查询这些学生的学号、姓名和班号。

该查询也可以利用联接查询完成： SELECT Student.StudentCode,StudentName,ClassCode FROM Student INNER JOIN Grade ON Student.StudentCode=Grade.StudentCode WHERE CourseCode= '101' 提示：多数情况下，包含子查询的语句可以用联接表示。但子查询与联接相比，有一个显著的优点，就是子查询可以计算一个变化的聚集函数值，并返回到主查询进行比较，而联接做不到。

【例4.36】查询年龄最大的学生的学号和姓名。 SELECT StudentCode, StudentName, Birthday FROM Student WHERE Birthday IN (SELECT MIN(Birthday) FROM Student) 提示：子查询获取Student表中最小的出生日期即年龄最大学生的出生日期，主查询再查询这一日期出生的学生的学号、姓名和出生日期。

【例4.35】用EXISTS子查询实现：查询选修了课程代码为“101”的学生的学号、姓名和班级代号。 EXISTS子查询 ：用于判断一个子查询的结果集是否为非空，如果非空则返回TRUE，否则返回FALSE。NOT EXISTS的返回值与EXISTS相反 。 【例4.35】用EXISTS子查询实现：查询选修了课程代码为“101”的学生的学号、姓名和班级代号。 SELECT StudentCode, StudentName, ClassCode FROM Student WHERE EXISTS (SELECT * FROM Grade WHERE Student.StudentCode=Grade.StudentCode AND Grade.CourseCode='101') 提示：本查询的执行过程是：首先查找主查询中Student表的第一行，将该行的学号提供给子查询，然后子查询执行，若结果集不为空，则把该行的StudentCode，StudentName，ClassCode作为结果集的第一行输出；然后再找Student表中的第2、3……行，重复上述处理过程，直到Student中的数据行全部处理完毕。

【例4.37】查询所有未选修任何课程的学生。 SELECT StudentCode, StudentName FROM Student WHERE NOT EXISTS (SELECT * FROM Grade WHERE Student.StudentCode=Grade.StudentCode)

２．数据插入语句INSERT INTO ① 向表中插入一条数据记录。 INSERT INTO 表名[(字段名1，字段名2，……)] VALUES （表达式1，表达式2，……） 其中： （1）字段可以是某几个字段。表达式1、表达式2分别对应字段名1、字段名2，它们是所要添加的记录的值。 （2）如果表中的字段没有在添加记录语句中出现，则对应的值为NULL。 （3）如果在定义表结构时已经说明了某个字段为必填字段（不能是空值）则在语句中必须为该字段添加对应的数值。 （4）当插入一条完整的记录时，可省略字段名，但字段值次序要与表中字段的次序一一对应。

【例4.38】向Grade表插入一条记录。 【例4.39】向Student表插入一条记录。 INSERT INTO Grade VALUES ('2102', '113', 90,'2012-12-20‘) 本例插入完整记录，可省略字段名，但注意值要与表中字段次序一致。 【例4.39】向Student表插入一条记录。 INSERT INTO Student(StudentCode, Sex, StudentName, ClassCode) VALUES ('3105', '女', '张琳','31‘) 本例插入记录的部分字段值，注意非空字段必须有值；字段次序可与表不同，但与值的对应顺序一致。

INSERT INTO 表名 [（字段1，字段2，……）] SELECT 源字段名表 FROM 源表名 [ WHERE 添加条件 ] ② 从其他表提取一组记录插入到目标表中。 INSERT INTO 表名 [（字段1，字段2，……）] SELECT 源字段名表 FROM 源表名 [ WHERE 添加条件 ] 提示：此处数据表必须已经存在，且其结构定义与SELECT语句返回的字段值类型一致。 【例4.40】向StudentTest表插入记录，数据为Student中所有女同学。 INSERT INTO StudentTest SELECT StudentCode, StudentName, Sex, LiveInDorm From Student WHERE Sex='女’

实现对一条或多条符合条件记录中某个或某些字段值的修改 。 3. 修改记录语句UPDATE 实现对一条或多条符合条件记录中某个或某些字段值的修改 。 UPDATE 表名 SET 字段名1= 表达式1 [，字段名2 = 表达式2…… ] WHERE 条件表达式 提示： 一个UPDATE语句可以更新多个字段值；如没有WHERE子句，将更新数据表中所有记录。

【例4.41】更新Student表中学号为“8101”的联系电话为“67792280” UPDATE Student SET Telephone='67792280' WHERE StudentCode='8101' 【例4.42】更新Course表中信息，将所有开课学院为“计算机学院”的课程的实验学时增加10%，上课学时增加10。 UPDATE Course SET LabHour=LabHour*(1+0.1),ClassHour=ClassHour+10 WHERE Academy='计算机学院'

4.删除语句DELETE DELETE FROM 表名[ WHERE 删除条件 ] 【例4.43】删除Student表中学号为“3105”的记录。 DELETE FROM Student WHERE StudentCode=' 3105 ' 【例4.44】删除Student表中平均成绩低于70的学生。 DELETE FROM Student WHERE StudentCode IN (SELECT StudentCode FROM Grade 　　　GROUP BY StudentCode 　　 HAVING AVG(Grade)<70)

4.1.4 T-SQL的运算功能和控制流程 T-SQL虽然和高级语言不同，但它也有运算、控制等功能，以支持复杂的数据检索和集合操纵。 1. 标识符、常量和变量 （1）标识符：由用户定义的名称，用来标识各种对象如服务器、数据库、数据库对象、变量等。第一个字符必须是字母 、下划线(_) 、@或# 。 注意：在SQL Server中，如果标识符中包含空格，要用双引号（“”）或方括号([])扩起来。 如：my table不合法，必须将其表示为[my table]或＂my table＂。

（2）常量：在程序运行过程中值不变的量。 注意：在T-SQL语句中不同类型常量的写法 数值型：直接写。如：23, 45.6 字符串：用西文单引号扩起来。如： ‘男’。 如字符串包含单引号，则可以使用两个单引号表示嵌入的单引号。 ‘’ ’ 211 ’ ’ 工程’ ---‘211’工程 日期时间型：用西文单引号扩起来。如：'2008-08-08'，'2008-0808 20:08:08 ' Bit型：直接写。如：0, 1

（3）变量：在程序运行过程中其值可以被改变的量。 1)变量的分类 局部变量：由用户定义和使用，其名称前有一个@符号。 全局变量：全局变量由系统定义和维护，其名称前有两个@符号。例如：@@ERROR:返回最后执行的 T-SQL 语句的错误代码。 @@ERROR:返回最后执行的 Transact-SQL 语句的错误代码。 2）局部变量的定义和赋值 DECLARE @局部变量名 数据类型 说明：如在一条语句中声明多个变量，各变量之间用“，”分隔。局部变量被定义后其初始值为NULL。 例如：DECLARE @x float,@var char(8) 该语句定义了变量@x是浮点实型，变量@var是长度为8的字符数据类型。

局部变量赋值的基本语法格式如下： SET @局部变量名=表达式 SELECT @局部变量名=表达式 【例4.45】使用课程号变量和成绩变量查询Grade表中课程代码为“105”且成绩低于80分的记录。 提示：变量类型要与Grade表中字段的类型一致，使用前要先赋值。 DECLARE @code char(3), @score Numeric(3,1) --变量@code表示课程号，@score表示成绩 SET @code='105' --给@code赋值图4.23 变量查询示例 SELECT @score=80 --给@score赋值 SELECT * --使用变量@code和@score查询 FROM Grade WHERE CourseCode=@code AND Grade<@score

【例4.46】将学号为“1102”的学生姓名存放到变量@sname中。 SET　@sname DECLARE @sname varchar(16) SELECT @sname= (SELECT StudentName FROM T_Student WHERE StudentCode=‘1102') SELECT @sname as '姓名' 提示：SELECT语句可用于将单个值赋于一个变量。如果子查询返回值有多个，则将最后一个返回值赋给变量；如果子查询没有返回值，变量就保留当前值。

2．运算符和表达式 SELECT * FROM Grade WHERE Grade IS NULL (1) 运算符 1)算术运算符：+、-、*、/、模运算（%） 2) 比较运算符和逻辑运算符 >=、<、<=、=、<> 、!>、!<、!= BETWEEN AND、NOT BETWEEN AND IN、NOT IN LIKE、NOT LIKE IS NULL、IS NOT NULL NOT、AND、OR 3）字符串连接运算符：+ 例如：‘ABCD’+‘1234’结果为：‘ABCD1234’ 例如：查询Grade表中还未输入成绩的学生代号和课程代号。 SELECT * FROM Grade WHERE Grade IS NULL

(2) 表达式 表达式是由运算对象、运算符及圆括号组成。 当一个表达式中有多个运算符时，运算符的优先级决定运算的先后次序。 在SQL查询分析器中可使用SELECT语句查看表达式的结果 。例如：SELECT 'ABCD'+'1234'

（1）数学函数：常用的数学函数及功能如表所示。 3．常用函数 （1）数学函数：常用的数学函数及功能如表所示。 函数名 函数功能 ABS(x) 计算x的绝对值 SQRT(x) 计算x的平方根 ATN(x) 计算x的反正切值 SIGN(x) 返回x的特征符号 COS(x) 计算x的余弦值 TAN(x) 计算x的正切值 EXP(x) 计算ex ([n]) 产生[0,1)之间的随机float值, n为正数 LOG(x) 计算自然对数lnx ROUND（x, n） 将x四舍五入为指定精度，n为小数位数 SIN(x) 计算x的正弦值 ROUND(x,n):当n为正数时，x四舍五入为n所指定的小数位数。当n为负数时，x则按n所指定的在小数点的左边四舍五入。 例如：ROUND(878.586, 2)=878.590 ROUND(878.586895,3)=878.587000 ROUND(878.586,0)=879.000 ROUND(878.586,-1)=880.000 ROUND(878.586,-2)=900.000 ROUND(878.586,-4)=0.000 RAND(n):返回 0 到1 之间的随机float 值。n是种子值，如果没有提供种子值，系统将生成它自己的不定种子值。如果用种子值调用 RAND，则必须使用不定的种子值来生成随机数。如果用同一种子值多次调用 RAND，它将返回相同的生成值。

declare @i smallint set @i=1 while @i<=5 begin select RAND(@i) set @i=@i+1 end

(2) 日期时间函数：常用日期函数如表所示。 函数名 函数功能 DAY(x) 返回指定日期中所表示的日，x是日期，例如：Day(‘2014-5-1’)=1 MONTH(x) 返回指定日期中所表示的月，例如：Month(‘2014-5-1’)=5 YEAR(x) 返回指定日期中所表示的年份，例如：YEAR(‘2014-5-1’)=2014 GETDATE() 返回当前系统日期和时间 SELECT YEAR('1998-3-12') AS 年份 SELECT GETDATE() AS 今天的日期 提示：在数据库设计时，如将日期型字段的默认值设置为GetDate()，则在添加新记录时，如果不赋值，自动用当前时间填入。 SELECT DATEDIFF(year,'2000-01-01',getdate())

使用CONVERT()函数可以用不同的格式显示日期/时间数据 语法格式： CONVERT(data_type(length),data_to_be_converted,style) 其中，data_type(length)表示转换后数据的长度，data_to_be_converted为待转换的字段或常量，style是整数，表示输出格式的编号。 例如: CONVERT(char(10),publishTime,120) 查询结果中的日期只显示年月日，显示格式为yyyy-mm-dd。 CONVERT(char(10),publishTime,108) 查询结果中的日期的显示格式为hh-mm-ss。

(3) 字符串函数：常用字符串函数如表所示。 函数名 函数功能 ASCII(s) 返回字符串s最左端字符的ASCII码 STR(n) 将数字数据n转换为字符串 CHAR(n) 将ASCII转换为字符 SPACE(n) 返回n个空格 LEFT(s, n) 返回字符串s左边的n个字符 SUBSTRING(s, m, n) 返回字符串s起始m长度为n 的子串 RIGHT(s, n) 返回字符串s右边的n个字符 LOWER(s) 将字符串中的字母转换为小写字母 LEN(s) 返回字符串s的长度（字符的个数） UPPER(s) 将字符串中的字母转换为大写字母 LTRIM(s) 删除字符串s开始处的空格 REPLACE(s1, s2, s3) 用s3替换s1中包含的s2 RTRIM(s) 删除字符串s结尾处的空格 SELECT LEN(‘Sun’), LEN(‘中国北京’) --结果分别为数值3和4 SELECT SUBSTRING(‘VB语言程序设计’, 3, 2) --结果为字符串“语言” SELECT REPLACE ('数据库系统','系统','应用') --结果为字符串“数据库应用” 为了以指定的精度显示计算结果，可以使用STR函数说明精度，格式如下： STR(s,m,n) 其中，s表示字符串，m表示s的总长度，n表示小数位数。 SELECT STR(123.4,5) SELECT STR(123.4,5,1) SELECT STR(123.4,2)

聚合函数对一组值进行计算并返回一个结果，常用于对记录的分类汇总，聚合函数经常与SELECT语句的GROUP BY子句一同使用。 (4) 聚合函数 聚合函数对一组值进行计算并返回一个结果，常用于对记录的分类汇总，聚合函数经常与SELECT语句的GROUP BY子句一同使用。 常用聚合函数如下： 函数名 函数功能 AVG( [ ALL | DISTINCT ] expression ) 计算某一字段的平均值（此字段的值必须是数值型） COUNT( [ ALL | DISTINCT ] expression ) 统计某一字段的个数 MAX( [ ALL | DISTINCT ] expression ) 查找某一字段的最大值 MIN( [ ALL | DISTINCT ] expression ) 查找某一字段的最小值 SUM( [ ALL | DISTINCT ] expression ) 计算某一字段的总和（此字段的值必须是数值型）

4．流程控制语句 T-SQL语言也提供了一些流程控制语句，使得对数据库中数据的检索、更新、插入等操作更加方便。 BEGIN…END语句 多条T-SQL语句使用BEGIN…END组合起来形成一个语句块。BEGIN…END可以嵌套使用。 BEGIN SQL语句1 SQL语句2 … END

(2)IF…ELSE语句 通过判定给定的条件来决定执行哪条语句或语句块。 IF 条件表达式 SQL语句1 [ELSE SQL语句1] 说明： 1)首先计算条件表达式的值，如果为TRUE，则执行IF后面的语句块，否则执行ELSE后面的语句块。 2)如果是单分支流程，可不含ELSE。 3)如果条件表达式中包含SELECT语句，则必须用圆括号将SELECT语句括起来。

【例4.47】统计学号为“1102”的学生的选课总数，如果不少于三门课就报告选课门数，否则显示其选修的课程信息，并提示选课太少。 USE School DECLARE @cn smallint,@text varchar(100) SET @cn=(SELECT count(StudentCode) FROM Grade WHERE StudentCode=‘1102’) --查选课门数 IF @cn>=3 SET @text=‘你选了’+ LTRIM(STR(@cn))+’门课，很好！’ ELSE BEGIN SELECT * FROM Grade WHERE StudentCode=‘1102’ --查询报告选课信息 SET @text=‘你选了’+ LTRIM(STR(@cn)) +’门课,选课太少,加油！’ END SELECT @text AS 选课提示 --查询报告选课提示信息

实现一条SQL语句或SQL语句块重复执行。 （3）WHILE语句 实现一条SQL语句或SQL语句块重复执行。 WHILE 条件表达式 SQL语句1 [BREAK] SQL语句2 [CONTINUE] 说明： 计算条件表达式的值，如果为TRUE，则执行WHILE后的语句块。重复上述过程,当条件表达式的值为False时,结束WHILE语句。 2) BREAK为从本层WHILE循环中退出，当存在多层循环嵌套时，使用BREAK语句只能退出其所在的内层循环，然后重新开始外层的循环。 3) CONTINUE为结束本次循环，开始下一次循环的判断。

【例4.48】调整课程号为“105”的课程成绩。当该课平均成绩小于80时，为每个同学的成绩加5分，循环操作直到最高分大于等于95分。 WHILE (SELECT AVG(Grade) FROM Grade Where CourseCode='105') < 80 IF (SELECT MAX(Grade) FROM Grade WHERE CourseCode='105') <95 BEGIN UPDATE Grade SET Grade=Grade+5 WHERE CourseCode='105' CONTINUE END ELSE BREAK

（4）RETURN语句 使程序从一个查询或存储过程中无条件返回，并返回表达式的值，其后的语句不再执行。 RETURN 表达式 （5）CASE语句 CASE语句是多分支的选择语句。 简单 CASE 函数形式：将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数形式：计算一组条件表达式以确定结果。

1）简单CASE函数 CASE 输入表达式 WHEN 情况表达式 THEN 结果表达式 ... [ELSE 结果表达式] END 1)当输入表达式的值与某一个WHEN子句的情况表达式的值相等时，就返回该WHEN子句中结果表达式的值 。 2)如果所有都不相等，则返回 ELSE子句后的结果表达式的值。若没有ELSE子句，则返回NULL值。 【例4.49】查询1994年出生的男同学的住校情况。 SELECT StudentName AS '姓名', CASE LiveInDorm WHEN 0 THEN '未住校' WHEN 1 THEN '住校' END AS '是否住校' FROM Student WHERE Sex='男' AND YEAR(Birthday)=1994

CASE WHEN 条件表达式 THEN 结果表达式 ... [ELSE结果表达式] END 说明：按顺序计算WHEN子句的条件表达式，当布尔表达式的值为TRUE，则返回THEN后面的结果表达式的值，然后跳出CASE语句。 【例4.50】统计每个学生平均成绩并划分等级。 SELECT StudentCode AS '学号', STR(AVG(Grade), 5, 2) AS '平均成绩', CASE WHEN AVG(Grade)>=90 THEN 'A' WHEN AVG(Grade)>=80 THEN 'B' WHEN AVG(Grade)>=70 THEN 'C' WHEN AVG(Grade)>=60 THEN 'D' WHEN AVG(Grade)<60 THEN 'E' END AS '等级' FROM Grade GROUP BY StudentCode

4.2视图 视图是一个虚拟表，其内容来自对数据表的查询结果。和数据表一样，视图也是二维表结构。 视图是数据库的外模式，通常用来为用户集中数据、简化用户的数据查询。 视图也可作为一种安全机制，允许用户通过视图访问数据，而不授予用户直接访问某些基础表的权限。

4.2.1创建视图 在SQL Server 中创建视图主要有两种方式： (1)在Management Studio中使用向导创建视图； (2)通过执行视图定义SQL语句CREATE VIEW创建视图。 ： 创建视图时必须遵循以下原则： 只能在当前数据库中创建视图。 视图在数据库中作为一个对象存储，视图名称不得与数据库中的表重名。

1.在Management Studio中使用向导创建视图 【例4.51】在数据库School中建立视图V_StudentGrade，查询学生姓名、课程名和成绩。 ① 展开“School/视图”，可看到数据库中已有一些系统视图。用鼠标右击“视图”，选择“新建视图”菜单可打开“添加表”对话框 。 ② 选择与视图有关的表、视图或函数（同时按Ctrl或Shift键可选择多项），单击“添加”按钮；或直接双击要添加的表，即可将其添加到视图设计窗口中。本例添加数据表Student、Course和Grade。单击“关闭”按钮，进入视图设计窗口。

提示:可省略第②~④步骤,直接在第三个窗格中输入实现查询的SELECT语句。 上格是表及其关系窗格 中格是为视图选择表中列的网格 SQL语句窗格 下格是结果窗格 ③在上格单击每个表字段前的复选框，可将该字段添加到视图中，也可在第二个窗格中选择视图字段，并可指定列的别名、排序方式和规则等,在选择过程中，第三个窗格中的SELECT语句也会随之自动改变。 提示:可省略第②~④步骤,直接在第三个窗格中输入实现查询的SELECT语句。

④单击工具栏上的“ ”按钮执行，视图的查询结果显示在结果窗格中。 ⑤单击工具栏上的“　 ”按钮，在弹出的“另存为”对话框中为视图命名，本例输入“V_StudentGrade”，单击【确定】按钮保存视图，从而完成视图创建。

【例4.52】在数据库School中，建立视图V_StudentAvgGradeExcellent，选拔优培生（平均成绩大于等于90的学生），并要求按平均成绩从高到低排列。 该例可参照上例方法在基本表Student和Grade上创建。由于视图的内容也可以来自另一个视图，这里基于视图V_StudentGrade创建。 ① 在创建视图的“添加表”对话框中选择“视图”选项卡，添加视图V_StudentGrade到视图设计器中。

② 在“视图设计”窗口的上格，首先为视图选择“姓名”和“成绩”字段。右击第二个窗格的字段“姓名”，选择快捷菜单的“添加分组依据”；在“成绩”行的“分组依据”列选“Avg”，筛选器列输入“>=90”，排序类型列选“降序”。 ③ 单击工具栏上的“ ”按钮可查看结果，保存视图为“V_StudentAvgGradeExcellent”。

2．使用SQL语句创建视图 在查询编辑窗口中执行SQL语句来完成视图创建 CREATE VIEW 视图名称 AS SELECT查询语句 【例4.53】创建一个性别为“男”的学生视图V_StudentSexMale，包括学生的学号、姓名、班号和性别。 CREATE VIEW V_StudentSexMale AS SELECT StudentCode As 学号, StudentName As 姓名, ClassCode As 班号, Sex As 性别 FROM Student WHERE SEX='男'

创建后的视图与表的用法相同，可以通过视图查询和更新数据库。 4.2.2 使用视图 创建后的视图与表的用法相同，可以通过视图查询和更新数据库。 1．查询视图操作 【例4.54】在数据库School中，查询视图V_StudentGrade统计“大学英语”课程的平均分。 SELECT 课程名称, AVG(成绩) AS 平均分 FROM V_StudentGrade GROUP BY 课程名称 HAVING 课程名称='大学英语'

2．更新视图操作 通过视图来插入、删除和修改数据。由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。 注意并不是所有的视图都可以执行各种更新操作。 如果视图所依赖的基本表有多个时，对于更新操作有以下限制： ① 不能向视图添加数据，因为这将影响多个基本表； ② 一次只能修改一个基本表中的数据； ③ 不能通过视图删除数据。

【例4.55】更新V_StudentSexMale。 视图更新方法： 在Management Studio选中要更新的视图，打开即可更新；也可通过执行SQL语句更新视图。但要更新的视图必须符合更新规则，否则系统提示出错。 【例4.55】更新V_StudentSexMale。 1）向视图V_StudentSexMale中插入一个新的学生记录：（'1104','赵谦','11','男'） INSERT INTO V_StudentSexMale VALUES('1104', '赵谦', '11', '男') 说明：向视图V_StudentSexMale添加记录的命令实际上是对数据表Student添加记录。

2）修改该记录：将“赵谦”名字改为“赵廉”。 UPDATE V_StudentSexMale SET StudentName=‘赵廉’ WHERE StudentName=‘赵谦’ 说明：对视图V_StudentSexMale修改，实际是修改Student表中相关的记录。

1．修改视图：修改视图的定义，即修改视图中指定字段的字段名、别名、表名、是否输出等属性。 4.2.3修改和删除视图 1．修改视图：修改视图的定义，即修改视图中指定字段的字段名、别名、表名、是否输出等属性。 在Management Studio中右击要修改的视图，选择“修改”命令，在视图设计窗口进行修改； 直接修改视图定义语句。 2．删除视图 在Management Studio中右击要删除的视图，选择“删除”命令，在“删除对象”对话框中，按“确定”按钮即可删除相应视图。 提示：删除视图对基本表没有任何影响，因为视图只是个虚拟表。

4.3 存储过程 存储过程是T-SQL语句的集合，它作为数据库对象之一被存储在数据库中，用户的应用程序调用存储过程可实现对数据库的访问。 存储过程的作用和使用方式类似于一些编程语言中的过程。由应用程序调用执行，可以接受输入参数，并以输出参数的形式将多个值返回给调用它的程序。

可以在一个存储过程中执行多条SQL语句。 可以通过输入参数的变化调用存储过程进行动态执行 使用存储过程有以下优点： 可以在一个存储过程中执行多条SQL语句。 可以通过输入参数的变化调用存储过程进行动态执行 存储过程在创建时就在服务器端进行了编译，节省SQL语句的运行时间。 提供了安全机制，它限制了用户访问SQL语句的权利，只为特定用户开放存储过程。 例：exec proc_searchstudent ‘1101' 其中，存储过程proc_SearchStudent是按输入学号查询特定学生的信息。 例：exec proc_searchstudent ‘1102'

4.3.1 创建存储过程 1．使用SQL语句创建存储过程 CREATE PROC[EDURE] 存储过程名 {@形式参数 数据类型}[=默认值][OUTPUT] AS SQL语句1 … SQL语句n 说明： 1）“形式参数”名称必须符合标识符规则； 2）OUTPUT表示该参数是可以返回的，可将信息返回调用者； 3）“默认值”表示输入参数的默认值，该值是常量或NULL，如果定义了默认值，不必提供实参，存储过程就可执行； 4）如有多个参数，可依次列出，用逗号“，”隔开。

执行存储过程： 说明： 1)“@形参”是创建存储过程时定义的形参名； 2)“实参值”是输入参数的值； EXEC[UTE] 存储过程名 [[@形参=]实参值| @变量 [OUTPUT]|[DEFAULT]] 说明： 1)“@形参”是创建存储过程时定义的形参名； 2)“实参值”是输入参数的值； 3)“@变量”表示用来保存参数或者返回参数的变量；OUTPUT表示指定参数为返回参数； 4)DEFAULT表示使用该参数的默认值作为实参。 5)如果有多个参数，依次列出，用逗号“，”隔开。

【例4.56】在School数据库中，创建无参数存储过程Proc_Student，查询所有学生信息。 ① 在Management Studio中，打开一个查询编辑窗口，输入代码后执行。在“School/可编程性/存储过程”下可看到Proc_Student。 CREATE PROCEDURE Proc_Student AS SELECT * FROM Student ② 在查询编辑窗口执行以下调用存储过程Proc_Student的语句。 EXEC Proc_Student

2．使用模板创建存储过程 在Management Studio中，提供了存储过程的创建模板，预先存放了主要语句代码结构，方便直接修改和补充模板中的代码来完成SQL语句。 ①可直接从模板资源管理器选中模板拖放到查询编辑窗口。 ②可通过“新建存储过程”命令使用模板。

【例4.57】在School中创建一个带有输入参数的存储过程proc_SearchStudentName，按输入姓名查询特定学生的信息。 ① 在Management Studio的“对象资源管理器”中，展开“School/可编程性/存储过程”，右击“存储过程”，选择“新建存储过程”，打开查询编辑窗口。 ② 在查询编辑窗口中，给出了创建存储过程的模板，修改相应代码后执行即可创建存储过程。该存储过程带有一个输入参数@sname，接受实参传来的学生姓名。

CREATE PROC proc_SearchStudent @sname nvarchar(20) --@sname表示姓名，是输入参数　　　 AS SELECT * FROM Student WHERE StudentName=@sname --按@sname查询学生信息 ③调用Proc_SearchStudentName，查询学生“林豆豆”的信息。 EXEC proc_SearchStudent '林豆豆' 或者： EXEC proc_SearchStudent @sname ='林豆豆

3．存储过程的创建和应用实例 【例4.58】在School中创建一个带有输入和输出参数的存储过程proc_CountStudent，根据课程编号统计选修该课程的学生人数。 ① 创建存储过程proc_CountStudent。 CREATE PROCEDURE proc_CountStudent @ccode char(3), 　 -- @ccode表示课程号，是输入参数 @number int OUTPUT -- @number表示选修总人数，是输出参数 AS --统计选修人数存放到@number SELECT @number =count(StudentCode) FROM Grade WHERE Grade IS NOT NULL AND CourseCode=@ccode ② 调用proc_CountStudent，查询“101”课的选修人数 。 DECLARE @num int EXEC Proc_CountStudent '101', @num OUTPUT PRINT '选修101课程的学生人数：'+str(@num)

【例4.59】在School中创建一个向Class表中插入班级记录的存储过程proc_ClassAdd。 CREATE PROCEDURE proc_ClassAdd @ccode char(2), --@ccode表示班号，是输入参数 @cname nvarchar(10), --@cname表示班名，是输入参数 @mjname nvarchar(10) --@mjname表示@mjname，是输入参数 AS --插入数据记录到Class INSERT INTO Class(ClassCode, ClassName, MajorName) VALUES(@ccode, @cname, @mjname) ② 调用存储过程proc_ClassAdd，完成记录添加。 EXEC proc_ClassAdd '44', '财务02', '财务管理'

【例4.60】在School中创建一个按学号和课程号修改成绩的存储过程proc_UpdateGrade。 CREATE PROC proc_UpdateGrade @stcode char(4), @cscode char(3), @scgrade Numeric(4,1) AS UPDATE Grade --以@scgrade值修改成绩 SET Grade=@scgrade WHERE StudentCode=@stcode AND CourseCode=@cscode ② 调用存储过程proc_UpdateGrade，将‘2202 ’号学生的‘106’课程成绩改为80分。 EXEC proc_UpdateGrade '2202','106', 80

① 创建存储过程proc_StudentPrize 。 【例4.61】在School中创建一个存储过程proc_StudentPrize，查询某个学生所获得的奖学金。奖学金发放的规则为：平均成绩大于90分获奖学金1000元；平均成绩在80到90分之间获奖学金500元；平均成绩小于80分没有奖学金。 ① 创建存储过程proc_StudentPrize 。 本过程一个输入参数，无输出参数。它通过RETURN语句返回奖金额。 CREATE PROC proc_StudentPrize @stcode char(4) -- @stcode表示学号，是输入参数 AS DECLARE @stavg int -- @stavg是存储过程内部使用的变量，而不是参数 SELECT @stavg=AVG(Grade) FROM Grade --查询某生平均成绩存到@stavg WHERE StudentCode=@stcode GROUP BY StudentCode IF @stavg>=90 -- 根据平均成绩@stavg的值返回奖学金数给调用程序 RETURN 1000 --返回1000 ELSE IF (@stavg>=80) RETURN 500 --返回500 RETURN 0 --返回0

② 调用存储过程proc_StudentPrize，查询‘1101’学生的奖学金数额。 以下几种调用语句都可以： DECLARE @prize int EXEC @prize=proc_StudentPrize ‘1101' PRINT ‘1101学生的奖学金是'+STR(@prize,5)+'元' DECLARE @prize int EXEC @prize=proc_StudentPrize @stcode=‘1101' PRINT '05101101学生的奖学金是'+STR(@prize,5)+'元' DECLARE @prize int,@code char(8) set @code=‘1101' EXEC @prize=proc_StudentPrize @code PRINT ‘1101学生的奖学金是'+STR(@prize,5)+'元'

4.4.2 查看、修改和删除存储过程 1．查看存储过程 2．修改存储过程 3．删除存储过程 在Management Studio中，右击一个存储过程，选择“编写存储过程脚本为/CREATE到/新查询编辑窗口”命令，可以查看存储过程的源代码。 2．修改存储过程 右击一个存储过程，选择“修改”命令，可修改存储过程的源代码后执行。 3．删除存储过程 右击一个存储过程，选择“删除”命令，在“删除对象”对话框中按“确定”按钮即可完成。

② 修改后的存储过程proc_SearchStudent可按以下多种组合执行： 【例4.62】修改例4.57所的存储过程proc_SearchStudentName，按输入的学生的姓名模糊查询一些学生的信息，如果没有提供参数，则返回所有学生的信息。 ① 右击proc_SearchStudent，选择“修改”菜单，查询编辑窗口会显示原来的存储过程代码（注意开头是ALTER PROC），修改代码并执行，即可完成修改。 ALTER PROC [dbo].[proc_SearchStudent] @sname nvarchar(20)=‘%’ --定义存姓名的sname变量并赋初值'%' AS SELECT * FROM Student WHERE StudentName LIKE @sname ② 修改后的存储过程proc_SearchStudent可按以下多种组合执行： EXEC proc_SearchStudent --显示所有学生的信息 EXEC proc_SearchStudent '张%' --显示姓张的学生的信息 EXEC proc_SearchStudent '张小贝' --显示学生张小贝的信息

4.4触发器 触发器是一种特殊的存储过程。当对数据库进行更新操作（数据操纵语句INSERT、DELETE、UPDATE，或数据定义语句如CREARTE、ALTER、DROP等）时，触发器被自动执行。 触发器的创建主要用来完成数据完整性维护和其他一些特殊的任务，一个表可以有多个触发器。 注意：触发器不能被程序直接调用，也不能传递参数。

4.4.1 DML触发器 1．DML触发器简介 当数据库中发生DML语言事件时将调用DML触发器。DML语言事件包括对指定表执行更新数据的INSERT、UPDATE或DELETE语句。 按触发器被触发和数据更新的时间先后，可分为： （1）AFTER触发器：是在表中数据被修改之后才被触发，触发器对变动的数据进行检查，如果发现错误，可拒绝或回滚变动的数据。 （2）INSTEAD OF触发器：在数据修改以前被触发，并取代修改数据的操作，转去执行触发器定义的操作。

2．DML触发器的创建 (1)使用 SQL语句创建触发器 CREATE TRIGGER 触发器名 ON 表名|视图 FOR [INSERT][, UPDATE][, DELETE] AS SQL语句段 【例4.63】在Student表上创建触发器tri_StudentInsDel，当用户插入或删除学生记录时，能自动显示表中的内容。 CREATE TRIGGER tri_StudentInsDel ON Student FOR INSERT, DELETE AS SELECT * FROM Student

2．DML触发器的创建 ① 在查询编辑窗口，输入以上代码后执行，在“数据库/School/表/Student/触发器”下可看到tri_StudentInsDel。 ② 执行添加记录的SQL命令，触发器“tri_StudentInsDel”将被触发执行，系统显示学生表Student的所有信息 。 INSERT INTO Student(StudentCode, StudentName, ClassCode, Sex) VALUES('2204', '郑结', '22', '女')

deleted 表和 inserted 表 触发器在执行时，系统会自动创建和管理两个临时触发事件表：deleted 表和 inserted 表，用于存储在数据表中记录改动的信息。 ① 执行INSERT语句时，新记录被添加到数据表，同时添加到 inserted 表。 ② 执行DELETE语句时，删除记录从数据表删除，同时保存到 deleted 表。 ③ 执行UPDATE语句时，删除的记录被保存到 deleted 表，然后新的记录被添加到数据表和 inserted 表。 deleted 表和 inserted 表可被被用来测试某些数据修改的效果及设置触发器操作的条件。当触发器执行结束后，deleted 表和 inserted 表会自动消失。

【例4.64】在Class上创建触发器tri_UpdateClassMonitor，当插入或修改Class表中的班长（Monitor）值时，自动检查该值是否在Student表的StudentCode字段中，如果不在则取消插入或修改，否则显示“更新操作成功！” CREATE TRIGGER tri_UpdateClassMonitor ON Class FOR INSERT, UPDATE AS DECLARE @text varchar(50),@code char(4) --@text存放提示信息 --查看inserted中新的班长号是否在学生表中，若不存在，回滚操作 SELECT @code=monitor FROM inserted IF NOT EXISTS (SELECT * FROM Student WHERE StudentCode=@code) BEGIN SET @text='班长的学号不存在，取消该更新操作！' RAISERROR(@text,16,1) ROLLBACK TRANSACTION --回滚修改操作 END ElSE SET @text='更新操作成功！' SELECT @text END --提示更新操作执行情况

CREATE TRIGGER tri_UpdateClassMonitor ON Class FOR INSERT, UPDATE AS DECLARE @text varchar(50) --@text存放提示信息 --查看inserted中新的班长号是否在学生表中，若不存在，回滚操作 IF EXISTS(SELECT * FROM inserted WHERE inserted.Monitor NOT IN (SELECT StudentCode FROM Student)) BEGIN SET @text='班长的学号不存在，取消该更新操作！' RAISERROR(@text,16,1) ROLLBACK TRANSACTION --回滚修改操作 END ElSE SET @text='更新操作成功！' SELECT @text END --提示更新操作执行情况

执行如下修改命令时，触发器“tri_UpdateClassMonitor”将被触发执行。 SET monitor='5104' WHERE ClassCode='51' 由于在Student表中不存在学号为‘5104’的学生，更新被取消。打开Class表可以检查确认。

2．使用模板创建触发器 在Management Studio中，提供了触发器的创建模板，预先存放了主要语句代码结构，方便直接修改和补充模板中的代码来完成SQL语句。 ①可直接从模板资源管理器选中模板拖放到查询编辑窗口。 ②可通过“新建触发器”命令使用模板。

【例4.65】 在Course上创建触发器tri_UpdateCourse，当Course表中有LeftSeats修改时，该触发器自动检查该剩余名额数是否与实际情况一致，即判断该数字与TotalSeats减去统计Grade表获得的所有该课程选课人数是否相等，如果一致则确认修改，否则取消修改。 ① 展开“School数据库/表/Course”，右击 “触发器”，选择“新建触发器”命令。 ② 在新打开的查询编辑窗口中，给出了创建触发器命令的模板。按照下面的代码修改建立触发器的命令后执行，即创建了触发器tri_UpdateCourse。

创建触发器的代码如下： CREATE TRIGGER tri_UpdateCourse ON Course FOR UPDATE AS --声明变量：ccode课程号，totalnum总名额，leftnum剩余名额，chsnum选课人数 DECLARE @ccode char(4),@totalnum int,@leftnum int,@chsnum int --获得新修改后的记录信息 SELECT @ccode=Coursecode,@leftnum=LeftSeats,@totalnum=TotalSeats FROM inserted --按课程号统计实际选课人数 SELECT @chsnum=count(StudentCode) FROM Grade GROUP BY CourseCode HAVING CourseCode=@ccode --判断要修改的值与实际剩余名额是否一致，如不一致，回滚修改操作，并报告 IF (@leftnum=@totalnum-@chsnum) PRINT '要修改的课程剩余名额与实际符合，修改成功' ELSE BEGIN PRINT '要修改的课程剩余名额与实际不符合，不能修改' --报告提示信息 ROLLBACK --回滚修改操作 END

③ 执行如下修改记录命令时，触发器tri_ UpdateCourse将被触发执行。 从Course表中看到’101’课程可选总人数为120，在Grade表中课程’101’已选人数为4，则该课程可选人数还剩余116，与修改人数符合,报告修改成功。 UPDATE Course SET Leftseats=116 WHERE CourseCode='101' ④若修改上面语句“SET Leftseats=110”，执行后将报告不能修改。

4.5.3查看、修改和删除触发器 在Management Studio中，展开“数据库/数据表/触发器”可看到该数据表的所有触发器列表，右击要维护的触发器 ① 查看触发器代码 选择“编写触发器脚本为/CREATE到/新查询编辑窗口”菜单，则可以查看触发器的源代码。 ②修改触发器代码 选择“修改”菜单，即可对触发器进行修改。 ③ 删除触发器 选择“删除”菜单，在“删除对象”窗口中按“确定”按钮，即可删除触发器。

4.4.2 DDL触发器 当数据库中发生DDL语言事件（如CREATE、ALTER、DROP等）时将触发DDL触发器。DDL触发器可用于审核和控制对数据库的操作。DDL触发器作为对象存储在创建它的数据库中。 DDL触发器只能是AFTER触发器，在DDL语句执行后被触发。DDL触发器主要用途如下： ① 防止对数据库结构和对象进行某些更改。 ② 执行某些操作以响应数据库结构中的更改。 ③ 记录数据库架构中的更改或事件。

2．DDL触发器的创建 CREATE TRIGGER 触发器名 ON {ALL SERVER|DATABASE} {FOR|AFTER} {event_type|event_group}[,...n] AS SQL语句段 【例4.66】在School数据库创建DDL触发器来防止对数据表被修改或删除。 CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT '要把触发器关闭，才能删除和修改数据表' --报告提示 ROLLBACK --回滚修改操作