一、初识SQL Server
1、数据文件与文件组、日志文件
数据文件
SQL Server 数据库具有以下3种类型的文件。
数据文件：用来存放数据，
（1）主数据文件
一个数据库必须有且只有一个主数据文件，其扩展名为.mdf
（2）次数据文件
一个数据库可以没有也可以有多个次数据文件，其扩展名为.ndf
（3）事务日志文件（.ldf ）
日志文件为数据库的恢复提供日志信息。每个数据库至少包含一个日志文件，也可以有多个。日志文件的扩展名建议为.ldf。
数据库文件组
出于分配和管理的目的，可以将数据库文件分成不同的文件组。每个文件组有一个组名。文件组分主文件组和次文件组。
（1）主文件组
主文件组包含主数据文件和任何没有明确指派给其它文件组的其它文件。
（2）用户定义文件组(次文件组)
用户定义文件组是在 create database 或 alter database 语句中，使用 FILEGROUP 关键字指定的文件组。
一个文件只能属于一个文件组，一个文件组也只能被一个数据库使用。
事务日志
事务日志是一个与数据库文件分开的文件。它存储对数据库进行的所有更改，并记录全部插入、更新、删除、提交、回退和数据库模式变化。
事务日志是任何数据库的关键组成部分，是备份和恢复的重要组件，如果系统出现故障，它将成为最新数据的重要来源。
在默认的情况下，所有数据库都使用事务日志。事务日志的使用是可选的，但是，除非您因特殊原因而不使用，否则您应始终使用它。运行带有事务日志的数据库可提供更强的故障保护功能、更好的性能以及数据复制功能。
数据库快照
2、系统数据库
3、掌握建立、修改和删除数据库的方法
建立数据库
（1）使用对象资源管理器建立数据库
（2）使用T-SQL命令建立数据库
建立数据库的最简方法
CREATE DATABASE database_name 建立数据库的完整语法
CREATE DATABASE database_name [ ON [ PRIMARY ] [<filespec> [，…n] ] ] [ LOG ON {
<filespec> [，…n] } ] 其中filespec在实际应用中用下面相应代码替换： <filespec>::= （[ NAME=logical_file_name，] FILENAME=‘os_file_name’ [，SIZE=size] [，MAXSIZE={
max_size|UNLIMITED}] [，FILEGROWTH=growth_increment] ） 例子：
CREATE DATABASE score3 ON PRIMARY ( NAME=score3_data, FILENAME='D:\sql\score3_data.mdf’, SIZE=5MB, MAXSIZE=20MB, FILEGROWTH=10% ), ( NAME=score3_data1, FILENAME=’ D:\sql\score3_data1.ndf’, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB ) LOG ON ( NAME=score3_log, FILENAME=’ D:\sql\score3_log.ldf’, SIZE=2MB, MAXSIZE=UNLIMITED, FILEGROWTH=1MB ) 修改和删除数据库
使用对象资源管理器修改数据库
使用T-SQL命令修改数据库
alter database database_name {
ADD FILE<filespec>[,…n] |ADD LOG FILE <filespec> [,…n] |ADD FILEGROUP group_name |REMOVE FILE logical_file_name |REMOVE FILEGROUP group_name |MODIFY FILE <filespec> |MODIFY NAME=new_databasename |MODIFY FILEGROUP group_name NAME=new_groupname } Database_name:要修改的数据库的名称。 ADD FILE<filespec>[,…n]:添加数据文件。 ADD LOG FILE <filespec> [,…n]：添加日志文件。 ADD FILEGROUP group_name：添加文件组。 REMOVE FILE logical_file_name：删除文件，是物理删除。 REMOVE FILEGROUP group_name:删除文件组。 MODIFY FILE <filespec>：修改数据库文件。 MODIFY NAME=new_databasename：重命名数据库。 注意点：
修改文件属性时,不需指定物理文件名。
文件大小不能小于初始容量。
删除文件组前必须保证该文件组为空，若其中有文件则应先删除 。
二、创建和管理数据表
1、掌握系统数据类型
| 数据类型 | 数据范围 | 占用存储空间 |
|---|---|---|
| real | -3.40E+38~3.40E+38 | 4个字节 |
| float | -1.79E+308~1.79E+308 | 8个字节 |
3）nchar[(n)] ：可存储1~4000个定长Unicode字符串，字符串长度在创建时指定；如未指定，默认为nchar(1)。每个字符占用2bytes存储空间。
4）nvarchar[(n)] ：可存储最大值为4000个字符可变长Unicode字符串。可变长Unicode字符串的最大长度在创建时指定，如nvarchar(50)，每个字符占用2bytes存储空间。
（6）货币型（低版本的类型）
用十进制数来表示货币值。使用货币型数据时必须在数据前加上货币符号（$），当货币值为负数时，在符号和数据之间加负号，如：$123，$-231。
包括money和smallmoney两种类型。
| 数据类型 | 数据范围 | 占用存储空间 |
|---|---|---|
| money | -2E+63~2E+63-1 | 8个字节 |
| smallmoney | -2E+31~2E+31-1 | 4个字节 |
（7）文本型
当存储的字符数目大于8000时使用文本型，文本型包括text和ntext两种。
text：用来存储ASCLL编码字符数据，最多可以存储231-1（约20亿）个字符。在定义Text数据类型时，不需要指定数据长度.
ntext：用来存储Unicode编码字符型数据，最多可能存储230 -1（约10亿）个字符，其存储长度为实际字符个数的两倍，因为Unicode字符用双字节表示。
（8）日期型
旧类型：包括smalldatetime和datetime。
日期型数据以字符的形式表示，即要用单引号括起来。
| 数据类型 | 日期范围 | 占用存储空间 |
|---|---|---|
| smalldatetime | 1900年1月1日—2079年6月6日 | 4个字节 |
| datetime | 1753年1月1日—9999年12月31日 | 8个字节 |
（9）table数据类型
table数据类型是一种特殊的数据类型，用于存储结果集以进行后续处理。
（10）二进制数据类型
binary[(n)]：存放n字节固定长度的二进制数据。1≤n≤8000。
varbinary[(n)]：存放n字节可变长度的二进制数据，
1≤n≤8000。
（11）自定义数据类型
使用sp_addtype创建用户自定义数据类型的语法如下：
sp_addtype[@typename=]type， [@phystype=]system_data_type [，[@nulltype=]'null_type'] 例 在score数据库中，创建名为“sex_type”自定义数据类型，依赖字符型数据，宽度为2，不允许为空。
USE score EXEC sp_addtype sex_type,'char(5)', 'not null' 可以像使用系统数据类型一样使用自定义数据类型。
例 在score数据库的student表中的sex字段的数据类型修改为sex_type。
USE score ALTER TABLE student ALTER COLUMN sex sex_type 删除自定义数据类型
使用sp_droptype删除用户定义数据类型的语法如下：
Sp_droptype {
‘类型名’} 例 把score数据库中student表的sex字段的数据类型修改为char(2)，然后再删除自定义数据类型sex_type。
USE score ALTER TABLE student ALTER COLUMN sex char(2) GO EXEC sp_droptype sex_type 注意：当自定义数据类型被使用状态下不能被删除
2、建立、修改和删除表
（1）建立表
使用对象资源管理器建立表
使用T-SQL语句建立表
CREATE TABLE <table_name> ( 列名 数据类型 ［NOT NULL ／NULL] ［IDENTITY（初始值，步长值）］ [DEFAULT<默认值> ] [，…n] ［，UNIQUE (列名[，…n])］ ［，PRIMARY KEY(列名[，…n])］ ［，FOREIGN KEY(列名) REFERENCES table_name [(列名)] ［，CHECK(条件)］) 例 在score数据库中建立一个名为teach的教师任教表，
并定义主键约束和外键约束。
Use score Go CREATE TABLE teach ( teacher_id char(4) NOT NULL, course_id char(5) NOT NULL, primary key(teacher_id,course_id), FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id), FOREIGN KEY(course_id) REFERENCES course(course_id) ) 例 在score数据库中建立一个名为course的课程信息表，
并定义主键约束和课程名称字段的唯一性约束。
USE score GO CREATE TABLE course ( course_id char(5) PRIMARY KEY, course_name char(20) , period int, UNIQUE(course_name) ) （2）修改表
使用对象资源管理器修改表
使用ALTER TABLE 语句修改表结构
ALTER TABLE table_name {
ALTER COLUMN 字段名 数据类型 [NULL|NOT NULL] |ADD 字段名 数据类型 [NULL|NOT NULL] |ADD CONSTRAINT 约束名 约束类型 |DROP COLUMN 字段名 [，…n] |DROP CONSTRAINT 约束名 } 例 为score数据库中的course表添加课程类型字段，字段名为
c_type。
USE score GO ALTER TABLE course ADD c_type char(12) 例 为score数据库中的class表添加主键约束，指定class_id字段为主键。
USE score GO ALTER TABLE class ADD CONSTRAINT PK_id PRIMARY KEY(class_id) （3）删除表
使用对象资源管理器删除表
使用T-SQL语句删除表
语法格式如下：
DROP TABLE table_name 3、插入、修改和删除数据
（1）插入记录
使用对象资源管理器添加记录
使用INSERT语句添加记录
语法：
INSERT [ INTO] <表名>[(<字段名1>[，<字段名2>…])] VALUES（<常量1>[,<常量2>]… 语法说明： (1 )[(<字段名1>[，<字段名2>…])] ：要插入字段值的字段名 省略即表示所有列都要插入数据。 (2) （<常量1>[,<常量2>]…）：要插入的字段值，与上面字段名一一对应。 例 使用INSERT语句往class表中录入记录。
方法一：
INSERT class(class_id , class_name, dept_id) VALUES(‘', '17计算机软件1班','01') 方法二：
INSERT class VALUES(‘', '17计算机软件2班','01') （2）修改记录
使用对象资源管理器添加记录
使用update语句修改记录
语法：
UPDATE <表名> SET <字段名>=<表达式>[，......n] [WHERE <条件>] 例 将姓名为张丹丹的address修改为广东广州
UPDATE student SET address=’广东广州’ WHERE student_name=’张丹丹’ （3）删除记录
使用对象资源管理器删除记录
使用DELETE 语句删除记录
语法：
DELETE [FROM]<表名>［WHERE<条件>］ 功能：删除表中满足条件的记录，省略WHERE表示删除所有记录。
三、数据查询
1、了解SQL语言的基本功能
| 操 作 对 象 | 操 作 方 式 | ||
|---|---|---|---|
| 命令 | 创 建 | 修改 | 删除 |
| 表 | CREATE TABLE | ALTER TABLE | DROP TABLE |
| 视图 | CREATE VIEW | DROP VIEW | |
| 索引 | CREATE INDEX | DROP INDEX |
（3）数据操纵语言（Data Manipulation Language，DML）
对已经存在的数据库进行记录的插入、删除、修改等操作。命令动词有INSERT、UPADATE、DELETE。
（4）数据控制语言（Data Control Language，DCL）
用来授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视。命令动词有GRANT、REMOVE等。
2、掌握各种查询操作
SELECT <字段列表> FROM 〈表名〉 [WHERE 〈查询条件〉] 含义是：根据WHERE子句的查询条件，从FROM子句指定的表中找出满足条件记录，再按SELECT语句中指定的字段次序，筛选出记录中的指定字段值。若不设置查询条件，则表示被查询的表中所有记录都满足条件。
完整格式是：
SELECT [ALL|DISTINCT]<字段列表> [INTO 新表名] FROM <表名列表> [WHERE <查询条件>] [GROUP BY <字段名>[HAVING <条件表达式>]] [ORDER BY <字段名>[ASC|DESC]] 参数说明：
（1）ALL|DISTINCT
其中ALL表示查询满足条件的所有行；DISTINCT表示在查询的结果集中，消除重复的记录。
（2）
<字段列表>
：由被查询的表中的字段或表达式组成，指明要查询的字段信息。
（3）INTO 新表名
表示在查询的时候同时建立一个新的表，新表中存放的数据来源于查询的结果。
（4）FROM
<表名列表>
指出针对那些表进行查询操作，可以是单个表，也可以是多个表，表名与表名之间用逗号隔开。
（5）WHERE
<查询条件>
用于指定查询的条件。该项是可选项，即可以不设置查询条件，但也可以设置一个或多个查询条件。
（6）GROUP BY
<字段名>
对查询的结果按照指定的字段进行分组。
（7）HAVING
<条件表达式>
：
对分组后的查询结果再次设置筛选条件，最后的结果集中只包含满足条件的分组。必须与GROUP BY子句一起使用。
（8）ORDER BY
<字段名>
[ASC|DESC]
对查询的结果按照指定的字段进行排序，其中[ASC|DESC]用来指明排序的方式。ASC为升序；DESC为降序。
整个SELECT语句的含义：
（2）针对单表的查询
1）查询指定的字段
用户往往需要了解表中部分字段信息或者全部字段信息，通过对SELECT语句中“字段列表”的控制即可满足用户的需求。
SELECT student_id, student_name, address FROM student SELECT student_id, student_name, class_id, sex, born_date, address, tel, resume FROM student 方法二：通配符法
SELECT * FROM student SELECT <字段列表> FROM 〈表名〉 WHERE 〈查询条件〉 查询条件可以是关系表达式、逻辑表达式和特殊表达式。
| 运算符号 | 含义 |
|---|---|
| = | 等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| !=或<> | 不等于 |
例 查询所有男学生的学号、姓名、性别和出生日期。
SELECT student_id, student_name, sex, born_date FROM student WHERE sex=’男’ | 运算符号 | 含义 |
|---|---|
| OR | 逻辑或 |
| AND | 逻辑与 |
| NOT | 逻辑否 |
| 运算符号 | 含义 |
|---|---|
| % | 通配符,包含0个或多个字符的任意字符串 |
| — | 通配符,表示任意单个字符 |
| [ ] | 指定范围或集合中的任意单个字符 |
| BETWEEN…AND | 定义一个区间范围 |
| IS [NOT] NULL | 检测字段值为空或不为空 |
| LIKE | 字符匹配操作符 |
| [NOT ] IN | 检查一个字段值属于或不属于一个集合 |
| EXISTS | 检查某一字段是否存在值 |
SELECT * FROM student WHERE student_name LIKE ‘刘%’ 例 查询所有姓张和姓刘学生的基本情况。
SELECT * FROM student WHERE student_name LIKE ‘[张，刘]%’ SELECT student_id, student_name, born_date FROM student WHERE born_date BETWEEN '1998-1-1' AND '1999-12-31' 例 查询备注内容为空的学生的学号、姓名与备注。
SELECT student_id, student_name,resume FROM student WHERE resume is null SELECT student_name, class_id,address FROM student WHERE address in (‘广东广州’,’湖南长沙’) SELECT '姓名', student_name, '城市', address FROM student SELECT student_name AS 姓名, born_date AS 出生日期 FROM student SELECT student_name , year(getdate())-year(born_date) as 年龄 FROM student WHERE sex='女' SELECT DISTINCT address FROM student SELECT [TOP n] 字段列表 FROM <表名> SELECT TOP 3 student_id, student_name, class_id FROM student | 函数 | 功能 | 含义说明 |
|---|---|---|
| COUNT | 统计 | 统计满足条件的记录数 |
| MAX | 求最大值 | 求某一集合中的最大值 |
| AVG | 求平均值 | 计算某一数值集合中的平均值 |
| SUM | 求和 | 计算某一数值集合中的总和 |
SELECT max(grade) as 最高分 , min(grade) as 最低分 FROM score WHERE course_id='1001' SUM（[ALL | DISTINCT] 表达式） (字段名) 例 计算号学生总成绩。
SELECT sum(grade) as 总分 FROM score WHERE student_id=‘' AVG（[ALL | DISTINCT] 表达式） (字段名) 例 计算号学生平均成绩。
SELECT AVG(grade) as 平均分 FROM score WHERE student_id=‘' COUNT（[ALL | DISTINCT] 表达式） (字段名) SELECT COUNT（student_id） as 学生总数 FROM student SELECT class_id , count(student_id) FROM student GROUP BY class_id SELECT student_id , sum(grade) as 总分, avg(grade) as 平均分 FROM grade GROUP BY student_id ORDER BY 总分 DESC SELECT student_id, student_name, class_id INTO student_class FROM student 说明：新表的所包含的字段与数据类型与SELECT语句的字段列表一致。如果要创建的临时表，则只要在表名前加上“#或”即可
多表连接查询
连接查询分两大类，一是使用连接谓词进行连接；二是使用关键字JOIN进行连接。
连接谓词
使用连接谓词连接表的基本格式为：
SELECT <输出字段列表> FROM 表1，表2 [，...n] WHERE <表1.字段名1> <连接谓词> <表2.字段名2> 连接字段：必须是可比较的
连接谓词包括：=、<、<=、>、>=、!=、<>等，当比较符是=时称为等值连接。
SELECT student.* , score.* FROM student , score WHERE student.student_id=score.student_id SELECT student.student_id, student_name, class_id, sex, born_date, address, tel, resume, course_id, grade FROM student , score WHERE student.student_id=score.student_id SELECT student.student_id,student_name,course_name,grade FROM student, score, course WHERE student.student_id=score.student_id AND score.course_id=course.course_id SELECT a.student_id,b.student_id,a.course_id,a.grade FROM score a, score b WHERE a.grade=b.grade AND a.student_id<>b.student_id AND a.course_id=b.course_id 以JOIN关键字连接
以JOIN关键字来连接表的方式，增强了表的连接能力和连接的灵活性。 使用JOIN关键字连接表的的基本格式为：
SELECT <输出字段列表> FROM 表名1 <连接类型> 表名2 ON <连接条件> [ <连接类型> 表名3 ON <连接条件>]...... SELECT student.* , score.* FROM student INNER JOIN score ON student.student_id=score.student_id SELECT student.student_id, student_name, course_id, grade FROM student LEFT OUTER JOIN score ON student.student_id=score.student_id SELECT student.student_id, class.class_id, class_name FROM student RIGHT OUTER JOIN class ON student.class_id=class.class_id SELECT student_id, course_name, grade FROM score FULL OUTER JOIN course ON score.course_id=course.course_id SELECT * FROM student CROSS JOIN score 子查询可以嵌套，它能将比较复杂的查询分解为几个简单的查询。一个SELECT—FROM—WHERE语句称为一个查询块。将一个查询块嵌套在另一个WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
SELECT class_name 外部查询 FROM class WHERE class_id IN ( SELECT class_id 内部查询 FROM student （子查询） WHERE student_name=‘张小云’ ） 嵌套查询的执行顺序是：首先执行最底层的内部查询（即子查询），它的查询结果并不显示，而是传递给外层查询，用来做外部查询的查询条件。即按照由里向外的顺序执行。
格式：
<表达式>
[NOT] IN （子查询）
例 查找选修了1002号课程的学生的学号、姓名和班级编号。
SELECT student_id, student_name, class_id FROM student WHERE student_id IN (SELECT student_id FROM score WHERE course_id=‘1002’) 格式为：表达式 { 比较运算符 } { ALL|ANY } (子查询)
ALL：表示表达式要与子查询的结果集中的所有值进行比较，当表达式与每个值都满足比较关系时，才返回TRUE，否则返回FALSE。
ANY：表示表达式只要与子查询的结果集中的某个值满足比较关系时，就返回TRUE，否则返回FALSE。
例 查询选修了1001号课程且成绩比0801103号学生1001号课程成绩高的学生的学号、课程编号和成绩。
SELECT student_id, course_id, grade FROM score WHERE course_id='1001' AND grade>(SELECT grade FROM score WHERE student_id='0' AND course_id='1001') INSERT [INTO] <表名>[（<字段1>[，<字段2>…]）] SELECT [（<字段A>[，<字段B>…]）] FROM <表名> [WHERE<条件表达式>] 【例】创建一个新的学生表 st_info，要求：包含学号、姓名和备注三个字段，然后将student表中相应的字段值插入到表st_info中，最后显示st_info表中的记录。
CREATE TABLE st_info ( 学号char(10) PRIMARY KEY, 姓名 char(8), 备注 char(30) ) Go INSERT INTO st_info(学号,姓名,备注) SELECT student_id, student_name,resume FROM student Go SELECT * FROM st_info UPDATE score SET grade=grade+5 WHERE student_id in (SELECT student_id FROM student WHERE class_id=‘') DELETE student WHERE student_id not in (SELECT student_id FROM score WHERE course_id='1001' ) SELECT student_id, grade FROM score a WHERE grade<( SELECT AVG(grade) FROM score b WHERE a.course_id=b.course_id AND course_id= '1001' ) SELECT 输出列表1 FROM 表名1 UNION [ALL] SELECT 输出列表2 FROM 表名2 SELECT student_id, course_id, grade FROM score WHERE course_id='1001' UNION SELECT student_id, course_id, grade FROM score WHERE course_id='1002' ORDER BY grade DESC 四、视图
1、掌握视图的概念
2、了解视图的作用
3、掌握视图的建立和管理
CREATE VIEW < view_name >[(<字段名>[，…n])] [WITH ENCRYPTION] AS SELECT 语句 [WITH CHECK OPTION] WITH ENCRYPTION:对视图的定义语句进行加密。可以保障视图的定义不被他人获得。
SELECT语句：定义视图的SELECT语句。该语句可以使用多个表或其他视图。该SELECT语句查询出来的数据就是视图所能观察到的数据。
USE score GO CREATE VIEW student_view AS SELECT student_id, student_name, sex, class_id FROM student 查看、修改和删除视图
查看视图信息
使用对象资源管理器查看
使用系统存储过程查看
（1）可以使用系统存储过程sp_help查看视图特征。
格式为： sp_help < view_name >
例 使用sp_help查看视图“student _view”的特征。
USE score GO sp_help student_view USE score GO sp_helptext student_view 修改视图信息
使用对象资源管理器修改
使用T-SQL语句修改视图
格式为：
ALTER VIEW < view_name>[(<列名>[，…n])] [WITH ENCRYPTION] AS SELECT 语句 [ WITH CHECK OPTION] USE score GO ALTER VIEW student_view AS SELECT student_id, student_name, sex, class_id FROM student WHERE sex= '男' 删除视图
使用对象资源管理器删除
使用T-SQL语句删除视图
语句格式为：
DROP VIEW < view_name >[，…n] USE score GO DROP VIEW 成绩_view USE score GO INSERT student_view(student_id, student_name,sex,class_id) VALUES(‘’,‘张三丰','男',‘') USE score GO SELECT student_id,student_name, class_id FROM student_view USE score GO UPDATE student_view SET student_name=‘张丰' WHERE student_id=‘' USE score GO DELETE student_view WHERE student_id=‘' 五、索引
1、掌握索引的概念
索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针
2、了解索引的作用
索引的作用：
①通过创建唯一索引，可以保证数据记录的唯一性。
②可以大大加快数据检索速度。
③可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。
④在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。
⑤使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。
3、索引的分类
从索引表的物理顺序、属性列的重复值以及索引列中所包含的列数等不同角度，可以把索引分为聚集索引和非聚集索引、唯一索引和非唯一索引、单列索引和复合索引。
（1）聚集索引
聚集索引是指数据行的物理存储顺序与索引的顺序完全相同，即索引的顺序决定了表中行的存储顺序。
因为记录是经过排序的，所以在每个表上只能创建一个聚集索引。
如果表中没有建立聚集索引，则在对表建立主键约束时会自动创建一个聚集索引。
（2）非聚集索引
非聚集索引具有完全独立于数据行的结构，即索引中的逻辑顺序并不等同于表中行的物理顺序。非聚集索引仅仅记录指向表中行的位置指针，通过指针可以快速在表中定位数据行。
非聚集索引作为与表独立的对象存在，可以为表中每一个常用于查询的列定义非聚集索引。
在一个列上设置唯一性约束时也自动在该列上创建非聚集索引。
（3）唯一索引和非唯一索引
唯一索引确保被索引的列不包含NULL在内的重复值。如果被索引的列是多个列的组合，那么唯一索引也可以确保索引列中的每个组合也是唯一的。因为唯一索引不允许出现唯一值。而非唯一索引没有这个限制。
（4）单列索引和复合索引
单列索引是对表中的单个字段建立的索引。复合索引是对表中的两个或两个以上字段（最多16个字段）的组合建立的索引，并且所有字段在同一个表中。复合索引中的字段顺序可以和表中字段的顺序不同，建议在复合索引中应该首先定义最有可能是唯一值的字段。
（5）视图索引
视图索引将具体化（执行）视图，并将结果集存储在唯一的聚集索引中，而且其存储方法与带聚集索引的表的存储方法相同。创建聚集索引后，可以为视图和唯一性约束的列上会自动创建唯一索引。
（6）全文索引
一种特殊类型的基于标记的功能性索引，由Microsoft SQL Server全文引擎（MSFTESSQL）服务创建和维护，用于帮助在字符串数据中搜索复杂的词。
（7）XML索引
XML数据类型列中XML二进制大型对象（BLOB）的已拆分持久表示形式
4、掌握索引的建立和管理
（1）创建索引
使用对象资源管理器建立索引
使用T-SQL语句建立索引
格式如下：
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED ] INDEX index_name ON {
table | view } ( column [ ASC | DESC ] [ ,...n ] ) } CREATE UNIQUE NONCLUSTERED INDEX c_name_index ON course (course_name) （2）查看、修改和删除索引
查看索引
索引信息包括索引统计信息和索引碎片信息，通过查看索引信息可以分析索引性能，以便更好的维护索引。
DBCC SHOW_STATISTICS ( table_name, index_name) 例 查看student表中的PK_student索引的统计信息。
DBCC SHOW_STATISTICS ('student',pk_student) DBCC SHOWCONTIG (table_name, index_name) 例 查看score表中的PK_grade索引的碎片统计信息。
DBCC SHOWcontig (score,pk_grade) sp_helpindex [@objname =] 'table_name' sp_helpindex student 修改索引
使用系统存储过程修改索引名称，语法如下：
sp_rename ‘table_name.index_name’, ‘new_index_name’ [, ‘object_type’] sp_rename 'student.s_name_index', 's_index', 'index' 删除索引
使用对象资源管理器删除索引
使用T-SQL语句删除索引
格式为：
DROP INDEX 'table.index | view.index' [ ,...n ] 注意：DROP INDEX命令不能删除由CREATE TABLE或者ALTER TABLE命令创建的主键或者唯一性约束索引，即在对表建立主键或唯一性约束时自动建立的聚集索引或唯一非聚集索引；也不能删除系统表中的索引。
例 使用DROP INDEX删除course表中的索引“c_name_index”。
DROP INDEX course.c_name_index
例 修改class表，为class_name列添加唯一性约束，约束名为unique_class_name，这时在class表中自动建立了一个与约束名同名的索引。再使用DROP INDEX删除索引unique_class_name，观察删除结果.
USE score GO ALTER TABLE class ADD CONSTRAINT unique_class_name UNIQUE(class_name) USE score GO DROP INDEX class.unique_class_name 六、Transact—SQL编程（T-SQL编程）
1、了解T—SQL编程
（1）SQL标识符
1）标准标识符
也称为常规标识符。定义标准标识符时应遵守以下规则。
长度：1～128个字符。
第一个字符必须以字母（a～z或A～Z）、下划线（_）、@或#开头
不能是Transact-SQL保留字。
不允许在标识符中嵌入其它特殊字符或空格。
分隔标识符
分隔标识符是包含在双引号（””）或中括号（[]）内的标准标识符或不符合标准标识符规则的标识符。
对于不符合标准标识符规则的，比如对象或对象名称的一部分使用了保留关键字的，或者标识符中包含嵌入空格的，都必须分隔。
2）批处理
批处理是包含一个或多个Transact-SQL语句的的集合，由客户端发送到 SQL Server 实例以完成执行。
GO
注释
SQL Server有以下两种注释字符。
--（双连字符） /*…*/（斜杠—星号字符对） 2、常量与变量
常量
常量是指在程序运行过程中值保持不变的量。
字符串常量
用单引号括起来的如：’china’、’SQL Server2014’。
整型常量
由没有用引号括起来且不包含小数点的数字字符串，为整型常量。如：123、2005。
实型常量
由没有用引号括起来且包含小数点的数字字符串，为实型常量。如：123.01、2008.5E4
日期时间型常量
由单引号括起来日期格式的数据，为日期时间型数据。如’2008-10-1’。
货币型常量
以“$”作为前缀的后接数字字符串，为货币型常量。如$125、$5425.12
二进制常量
二进制常量具有前缀0x并且是十六进制数字字符串。这些常量不使用引号括起来。如：0xabc、0x341DF
变量
变量是指在程序运行过程中可以变化的量.
在定义和使用变量时应注意：
遵循“先定义，再使用”的基本原则。
变量名也是一种标识符，所以变量的命名也应遵守标识符的命名规则。
变量的名字尽量做到见名知意，避免变量名与系统保留关键字同名。
变量分为:用户自定义的局部变量和系统提供的全局变量。
局部变量
局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部.
声明局部变量
语法格式：
DECLARE @variable_name datatype [，@variable_name datatype ] … USE score GO DECLARE @sum int 局部变量的赋值
方法一：通过SET语句赋值，格式为：
USE score GO DECLARE @id char(10) Set @id=‘’ PRINT @id /*输出变量的值*/ 方法二：通过SELECT语句赋值，格式为：
SELECT @变量名=表达式[,…] [from 表名][WHERE 条件表达式] USE score GO DECLARE @id char(10) SELECT @id=‘’ PRINT @id 【例】定义一个长度为8的字符变量STNameVar，并将student表中学号为学生的姓名赋值给变量STNameVar，然后输出该变量的值。
DECLARE @STNameVar CHAR(8) SELECT @STNameVar=student_name FROM student WHERE student_id =‘' SELECT @STNameVar 全局变量
全局变量是SQL Server系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。且全局变量的名字均以@@开头。
用户不能建立全局变量，也不能用SET或SELECT语句对全局变量赋值。
例如：@@rowcount记录前一条SQL语句处理记录的行数；@@version记录SQL Server的版本信息；@@servername记录当前服务器的名称。
3、运算符
比较运算符
比较运算符表
逻辑运算符
逻辑运算符表
字符串联运算符
字符串联运算符只有一个，即加号“+”。利用字符串运算符可以将多个字符串连接起来，构成一个新的字符串。
例如，执行语句SELECT ‘abc’+’def’，其结果为abcdef。
【例】查询student表中学生的姓名和学号，要求两者要作为一列结果输出，学号和姓名间用字符“–”隔开。
USE SCORE GO SELECT student_id +'--'+student_name FROM student 4、流程控制语句
BEGIN SQL语句1 SQL语句2 … END IF 布尔表达式 SQL语句块 若IF后面的布尔表达式为真，则执行SQL语句块，然后执行IF结构后面的语句；否则跳过语句块直接执行IF结构后面的语句。
包含ELSE子句
IF 布尔表达式 SQL 语句块1 ELSE SQL语句块2 USE SCORE GO IF (SELECT grade FROM score WHERE student_id=‘' and course_id='1001')>60 print 'grade above 60' ELSE print 'grade below or equal 60' WHILE <条件表达式> BEGIN < SQL语句块> END 【例】求1到100的和。
Declare @num int, @sum int Select @num=1, @sum=0 While @num<=100 Begin set @sum=@sum+@num set @num=@num+1 End Print @sum 引入CONTINUE与BREAK命令的WHILE循环的语法格式如下：
WHILE <条件表达式> BEGIN < SQL 语句块> [ BREAK ] [ CONTINUE ] [ SQL 语句块] END [例】求1到100之间的偶数和（包括100）。
Declare @num int, @double_sum int Select @num=0, @double_sum=0 While @num<=100 Begin set @num=@num+1 if @num%2!=0 continue else set @double_sum=@double_sum+@num End print '偶数和为:' print @double_sum GOTO label …… label: 形式二：标签在GOTO语句前面定义。
Label: …… GOTO label Declare @num int, @sum int Select @num=1, @sum=0 label: if(@num<=100) begin set @sum=@sum+@num set @num=@num+1 goto label end print @sum RETURN [整形表达式] WAITFOR { DELAY ‘time’ | TIME ‘time’ }
WAITFOR delay ’00:01:03’ select * from student 【例】直到中午11 点零8 分后才执行SELECT 语句。
waitfor time ’11:08:00’ select * from student PRINT <字符串>|<变量名>|<表示式> 5、函数
（1）统计字符串长度函数 LEN(string_expression) （2）字符串截取函数 LEFT(string_expression，n) RIG HT(string_expression,n) SUBSTRING(string_expression,start,n) （3）字符串转换函数 LTRIM（string_expression） RTRIM（string_expression） CHAR（integer_expression） 例】使用LEN函数统计字符串：’SQL Server2014’的长度。
PRINT LEN('SQL Server2014') 数学函数
ROUND(numeric_expression，n) ABS(numeric_expression) SQRT(numeric_expression) 日期和时间函数
GETDATE( ) YEAR(date) MONTH(date) DAY(date) DATEADD(datepart，n，date) DATEDIFF(datepart , date1 , date2 ) CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression] END 【例】在student表中查询班、班、班学生，然后根据班级编号与班级名称的对应关系，输出学号和班级名称，如果不是这个三个班的学生，班级名称输出为NULL。
SELECT student_id, CASE class_id when ‘’ THEN ’17计算机1班' when ‘’ THEN ’17计算机2班' when ‘’ THEN ‘17计算机3班' ELSE 'NULL' END '班级名称' FROM student 搜索 CASE函数的语法形式：
CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression END 【例】编写程序查询学号为的学生各门课程的成绩，并将成绩转换为不同的等级。
SELECT course_id, CASE when grade>89 THEN '优秀' when grade>79 and grade<=89 THEN '良好' when grade>69 and grade<=79 THEN '中等' when grade>59 and grade<=69 THEN '及格' ELSE '不及格' END '等级' FROM score Where student_id=’’ 类型转换函数
CAST (expression AS data_type) SELECT student_id, course_id, CAST(grade as decimal(4,1))’成绩’ FROM score CREATE FUNCTION [owner_name.]函数名称 （[@形式参数名称 [AS] 数据类型[=default] [,……] ]） RETURNS 返回数据类型 [as] BEGIN 函数体 RETURN 表达式 END 【例】编写一个标量函数：以学号和课程编号做为参数，输入学号和课程编号后得到该学生该门课程的成绩。
CREATE FUNCTION F_grade(@s_id char(10), @c_id char(5)) RETURNS INT AS BEGIN DECLARE @G INT SET @G=(SELECT grade FROM score WHERE student.student_id=@s_id and course_id=@c_id) RETURN @G END 标量函数的调用
可以在使用标量表达式的位置调用标量函数，也可以使用EXECUTE语句执行标量函数。执行标量函数有两种方法，
1）owner_name．函数名(实参１,……,实参n) 2)EXEC owner_name.函数名 实参１,……,实参n 或：EXEC owner_name.函数名 形参１＝实参１,…,形参n=实参n USE SCORE GO PRINT dbo.F_grade(‘','1001') 方法二：
USE SCORE GO DECLARE @成绩 int EXEC @成绩=dbo.F_grade ‘','1001' PRINT @成绩 练习：编写一个函数：输入学号后得到该学号对应的姓名。学号作为函数的输入参数。
Create function fn_stuName(@stuId as char(10)) Returns char(10) Begin declare @Name char(10) set @Name= ( select 姓名 from 学生基本信息表 where 学号=@stuId ) return @Name end 内嵌表值型函数
内嵌表值型函数返回值的类型为table，即它返回的是一个表，返回的表中的数据由位于RETURN 子句后的SELECT语句决定。
内嵌表值型函数的定义
语法格式如下：
CREATE FUNCTION [ower_name.]函数名称 （[@形式参数名称 [AS] 数据类型[=default] [,……] ]） RETURNS table RETURN [(select-stmt)] 【例】建立一个函数，以学号和课程编号做为参数，输入学号和课程编号后得到该学生学号、姓名、课程编号和成绩。
USE SCORE GO CREATE FUNCTION F_s_grade(@s_id char(10),@c_id char(5)) RETURNS TABLE RETURN SELECT student.student_id, student_name, course_id, grade FROM student, score WHERE student.student_id=score.student_id and student.student_id=@s_id and course_id=@c_id 内嵌表值型函数的调用
内嵌表值型函数只能通过select语句调用。调用语法格式为：
SELECT * FROM 函数名(实参１,……,实参n) 【例】调用函数F_s_grade，查看号学生1001号课程的成绩。
SELECT * FROM F_s_grade(‘','1001') 多语句表值函数
多语句表值函数可生成记录，并将记录插入返回的表类型的变量中。
(1)多语句表值函数的定义
CREATE FUNCTION [owner_name.]函数名称 （[@形式参数名称 [AS] 数据类型[=default] [,……] ]） RETURNS @return_variable TABLE <table_type_definition> BEGIN 函数体 RETURN END 【例】建立一个函数，查询学生的学号、姓名、课程编号和成绩，并将这些信息插入到一个表类型变量中。
USE SCORE GO CREATE FUNCTION F_student_grade() RETURNS @result TABLE (s_id char(10), name char(8), c_id char(5), result int ) BEGIN INSERT @result SELECT student.student_id, student_name, course_id, grade FROM student, score WHERE student.student_id=score.student_id RETURN END 多语句表值函数的调用
多语句表值函数的调用与内嵌表值型函数的调用方法相同。调用语法格式为：
SELECT * FROM 函数名([实参１,……,实参n]) 【例】调用F_student_grade函数
SELECT * FROM F_student_grade() 删除用户自定义函数
使用 DROP FUNCTION语句可以删除当前数据库中指定的一个或多个函数。其语法如下：
DROP FUNCTION {
[ owner_name. ] 函数名称 } [ ,...n ] 【例】删除数据库score中的用户自定义函数F_student_grade
DROP FUNCTION F_student_grade create function avg2(@xh char(8)) returns decimal(5,2) begin declare @a decimal(5,2) set @a=(select avg(成绩) from 成绩表 where 学号=@xh) return @a End 2、创建一个多语句函数，以课程编号为参数，函数的功能是查找选修 了该门课的学生姓名和成绩。
create function c_grade(@c_n char(8)) returns @cno table (s_no char(8),c_no char(5),cj decimal(5,2)) begin insert @cno select 学号,课程编号,成绩 from 成绩表 where 课程编号=@c_n return End 执行： select * from c_grade(‘1001') 七、存储过程和触发器数据表
1、存储过程
存储过程概述
存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。
一般用来处理需要与数据库进行频繁交互的复杂的业务。因为存储过程具有以下优点：
①存储过程在服务器端运行，执行速度快。
②封装业务逻辑也可以使数据库操作人员与应用系统开发人员的分工更明确，支持模块化设计。
③存储过程执行一次后，其执行计划就驻留在高速缓冲存储器，如果需要再次调用，只需要从高速缓冲存储器中调用，提高系统性能。
④存储过程可以作为安全机制。通过授予用户对存储过程的操作权限来实现安全机制。
⑤使体现企业规则的运算程序放入数据库服务器中，以便集中控制。
存储过程的分类
1．系统存储过程
系统存储过程存储在master数据库中，以sp_开头，可以在其他数据库中进行调用。
如：sp_help就是取得指定对象的相关信息。
2．扩展存储过程
在SQL Server环境之外执行动态链接库称为扩展存储过程，以XP_开头,用来调用操作系统提供的功能。
3．用户自定义的存储过程
由用户创建并能完成某一特定功能的存储过程。包括Transact-SQL和CLR两种类型。
（1）Transact-SQL存储过程是指保存的Transact-SQL语句集合，可以接受和返回用户提供参数。也可以从数据库向客户端应用程序返回数据。
（2）CLR在本书不做详细介绍。
建立存储过程
使用对象资源管理器建立存储过程
使用T-SQL命令建立存储过程
语法形式如下：
CREATE PROC[EDURE] procedure_name [{
@parameter data_type} [VARYING] [=default][OUTPUT] ] [,...n] [WITH {
RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}] [FOR REPLICATION] AS sql_statement [ ...n ] 【例】创建一个不带参数的存储过程，名称为pro_stu，用于检索学生的姓名、性别和家庭住址。
USE score GO --判断pro_stu存储过程是否存在，若存在，则删除 If exists(select name from sysobjects where name='pro_stu' and type='p') DROP PROCEDURE pro_stu Go --建立存储过程 CREATE PROC pro_stu As SELECT student_name, sex, address FROM student 【例】创建一个带参数的存储过程，名称为pro_class，在执行存储过程的时候输入班级编号，然后检索该班级的学生的详细信息。
USE score GO --判断pro_class存储过程是否存在，若存在，则删除 If exists(select name from sysobjects where name='pro_class' and type='p') DROP PROCEDURE pro_class Go --建立存储过程 CREATE PROC pro_class @c_no char(8) As SELECT * FROM student WHERE class_id=@c_no 存储过程的执行
语法形式如下：
[[EXEC[UTE]] {
[@return_status =] {
procedure_name|@procedure_name_var} [[@parameter=]{
value|@variable[OUTPUT]|[DEFAULT] } [,...n] [ WITH RECOMPILE ] CREATE PROC pro_update @s_id char(10), @c_id char(5), @result int --参数 AS UPDATE score SET grade=@result WHERE student_id=@s_id AND course_id=@c_id / *顺序法执行存储过程，把0号学生1001号课程的成绩修改为85分*/ EXEC pro_update '0', '1001', 85 EXEC pro_update @s_id=’0’, @result=85,@c_id=’1001’ 说明：提示法中的参数顺序可以与建立存储过程时的参数顺序不一致，而顺序法必须保持一致。
CREATE PROC pro_insert @c_id char(5), @c_name char(20), @period int=64, @c_type char(10)='NULL' AS INSERT course VALUES( @c_id ,@c_name,@period,@c_type ) GO --执行存储过程pro_insert EXEC pro_insert '1006', 'c++程序设计' GO --查看插入结果 SELECT * FROM course CREATE PROC pro_sum @a int ,@b int, @sum int output AS SET @sum=@a+@b GO --执行存储过程 DECLARE @s int EXEC pro_sum 20 ,15,@s output PRINT @s 存储过程的返回值
存储过程被执行以后可以返回一个整数类型的代码值，该值用来记录存储过程的执行情况。若返回值为0，表示执行成功；否则表示执行不成功。
捕捉存储过程返回值的方法
1）用一个变量来捕捉存储过程执行后返回值。
2）使用RETURN语句为存储过程指定一个确定的返回值，以用来反映存储过程执行的结果情况。
【例】用变量来捕捉存储过程执行后返回值。建立一个存储过程，完成查询指定学生指定科目的成绩。然后执行该存储过程并捕捉执行是否成功。
CREATE PROC pro_grade @s_id char(10), @c_id char(5) AS SELECT grade FROM score WHERE student_id=@s_id AND course_id=@c_id GO DECLARE @status int --声明变量,用来存放执行结果的状态 EXEC @status=pro_grade '0','1001' SELECT @status --输出状态值 【例】使用RETURN语句为存储过程指定一个确定的返回值。建立一个存储过程，完成查询指定学生指定科目的成绩，如果成绩大于60分，返回1；如果等于60分，返回0；小于60分，则返回-1。然后执行该存储过程并输出返回值。
CREATE PROC pro_return @s_id char(10), @c_id char(5) AS DECLARE @result int SELECT @result=grade FROM score WHERE student_id=@s_id AND course_id=@c_id IF @result>60 RETURN 1 IF @result=60 RETURN 0 IF @result<60 RETURN -1 GO DECLARE @status int --声明变量,用来存放执行结果的状态 EXEC @status=pro_return '0','1001' SELECT '状态'=@status --输出状态值 查看、修改和删除存储过程
1.修改存储过程
语法形式如下： ALTER PROC[EDURE] procedure_name [{
@parameter data_type} [VARYING] [=default][OUTPUT] ] [,...n] [WITH {
RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS sql_statement [ ...n ] 【例】修改存储过程pro_sum，使其功能更改为输出两个数相加的和，不使用返回参数。
USE score GO ALTER PROC pro_sum @a int ,@b int AS DECLARE @sum int SET @sum=@a+@b PRINT @sum EXEC sp_helptext pro_return 【例】查看存储过程pro_return的所有者、类型及参数。
EXEC sp_help pro_return 【例】查看存储过程pro_return的相关性。
EXEC sp_depends pro_return DROP PROC[EDURE] {
procedure_name} [,…n] 【例】删除存储过程pro_return。
DROP PROC pro_return 2、触发器
触发器的概述
Microsoft SQL Server 提供了两种主要机制来强制执行业务规则和数据完整性：约束和触发器。
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。具体表现为：
①触发器可以通过数据库中的相关表进行级联更改。
②触发器可以防止恶意或错误的UPDATE、 INSERT、 DELETE 、CREATE和ALTER操作，这些限制比用 CHECK 约束所定义的更复杂。
③触发器可以评估数据修改前后表的状态，并根据该差异采取措施。
④一个表可以有多个同类触发器允许采取多个不同的操作来响应同一个修改语句。
触发器的类型
两大类触发器：DML触发器和DDL触发器。
1.DML触发器(数据操纵insert,update,delete)
DML触发器是当数据库服务器中发生数据操作语言事件时所执行的操作 .
DML触发器有三种类型：AFTER触发器、INSTEAD OF触发器和CLR触发器。
（1）AFTER触发器
表示只有在执行了指定的操作（INSERT、 DELETE、 UPDATE）之后触发器才被激活，执行触发器中的SQL 语句。若只指定FOR， 则默认为AFTER 触发器，且该类型触发器仅能在表上创建。
（2）INSTEAD OF触发器
指定用触发器中的操作代替触发语句的操作，也就是该触发器并不执行所定义的操作（INSERT、 DELETE、 UPDATE），而是执行触发器本身的SQL语句。可以为基于一个或多个表的视图定义INSTEAD OF触发器，而这些触发器可能够扩展视图可支持的数据类型。
CREATE TRIGGER trigger_name ON {
table|view} [WITH ENCRYPTION] {
{
{
FOR | AFTER | INSTEAD OF } {
[ DELETE ] [,] [ INSERT ] [,] [ UPDATE ]} AS sql_statement[...n] } } 【例】在student表中创建了一个触发器，当往该表中成功插入记录时，输出提示信息。然后执行插入记录操作，检验触发器。
--判断insert_stu触发器是否存在，若存在，则删除 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'insert_stu' AND type = 'TR') DROP TRIGGER insert_stu GO --建立触发器 CREATE TRIGGER insert_stu ON student FOR INSERT AS PRINT '插入记录成功' --插入记录，激活触发器 INSERT student(student_id,student_name,sex,class_id) VALUES(‘','吴盼盼','男',‘') --建立触发器 CREATE TRIGGER update_stu ON student FOR insert, update,delete AS SELECT * FROM inserted SELECT * FROM deleted --修改数据，激活触发器 UPDATE student SET sex='男' WHERE student_id=' CREATE TRIGGER check_insert ON score AFTER insert AS DECLARE @成绩 int SELECT @成绩=grade FROM inserted IF @成绩>=0 AND @成绩<=100 PRINT '插入成功' ELSE BEGIN PRINT '成绩值超出范围,不允许插入' ROLLBACK TRANSACTION END --插入记录，检验insert触发器的作用 INSERT grade(student_id,course_id,grade) VALUES(‘','1001',-10) CREATE TRIGGER check_update ON score AFTER update AS IF update(grade) BEGIN PRINT '修改无效,成绩被录入后,不允许修改' ROLLBACK TRANSACTION END --修改成绩值，检查 update触发器的作用 UPDATE score set grade=80 WHERE student_id= ' AND course_id='1001' 【例】在学生表中建立一个触发器，若删除学生记录，则把该学生在成绩表中的成绩记录一并删除。
CREATE TRIGGER check_delete ON student AFTER delete AS DELETE score WHERE student_id in (SELECT student_id FROM deleted) --删除学生记录，检查 delete触发器的作用 DELETE student WHERE student_id=' ' --查看成绩表 grade中‘’号学生的成绩是否被删除 SELECT * FROM grade USE score GO CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN PRINT '禁止修改或删除表' ROLLBACK END --修改表，检验safety触发器的功能 ALTER TABLE student ADD f_name char(8) 【例】建立 DDL 触发器来防止在当前服务器下建立数据库。
CREATE TRIGGER forbid_create ON ALL SERVER FOR CREATE_DATABASE AS BEGIN PRINT '禁止建立数据库' ROLLBACK END --建立数据库，检验forbid_create触发器的功能 CREATE DATABASE book 查看、修改和删除触发器
查看触发器
（１）使用对象资源管理器查看触发器信息
（２）使用系统存储过程查看触发器
（1）sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。
格式：sp_help ‘触发器名称‘
（2） sp_helptext：用于查看触发器的正文信息
格式：sp_helptext ‘触发器名称’
（3） sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。
sp_depends ’触发器名称’
sp_depends ‘表名’
【例】查看check_delete触发器的定义信息。
sp_helptext check_delete
ALTER TRIGGER trigger_name ON {
table|view} [WITH ENCRYPTION] {
{
{
FOR | AFTER | INSTEAD OF } {
[ DELETE ] [,] [ INSERT ] [,] [ UPDATE ]} [NOT FOR REPLICATION] AS sql_statement[...n] } } （2）修改DDL触发器
ALTER TRIGGER trigger_name ON {
ALL SERVER|DATABASE} [WITH ENCRYPTION] {
FOR | AFTER} {
event_type|event_group}[,…n] AS {
sql_statement[...n] } （２）删除DDL触发器
（3）删除触发器所在的表时，SQL Server将会自动删除与该表相关的触发器。
思考：触发器、存储过程和函数三者有何区别？
触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行；函数是根据输入产生输出，自定义函数的输入输出的关系由用户来定义。在什么时候用触发器？要求系统根据某些操作自动完成相关任务，比如，根据买掉的产品的输入数量自动扣除该产品的库存量。什么时候用存储过程？存储过程就是程序，它是经过语法检查和编译的SQL语句，所以运行特别快。
八、备份与恢复
1、备份与恢复概述
备份概述
数据库备份就是创建完整数据库的副本，以便当数据遭到灾难性的破坏时可以用副本恢复数据。数据备份和还原永远都是保护数据的重要手段之一。
执行备份操作对运行中的事务影响很小，因此可以在正常操作过程中执行备份操作。但如果进行备份操作时尝试建立或删除数据库文件，则创建或删除将会失败；如果在创建或删除数据库文件时尝试进行备份，则备份操作将等待，直到创建或删除数据库文件完成。
备份的类型
（1）数据库完整备份：是指对数据库内所有内容都进行备份，所以占用的空间和时间较多。
（2）差异备份：只备份最后一次数据库完整备份以来被修改的数据，占用的时间和空间较少。
（3）事务日志备份：只备份数据库的事务处理记录。备份所用的时间和空间更少。利用事务日志备份进行恢复时，可以指定恢复到某一个事务。
（4）文件和文件组备份：即备份数据库文件或文件组。这种备份应该与事务日志备份结合起来才有意义。
如某数据库中有两个数据文件，一次仅备份一个文件，而且在每个数据文件备份后，都要进行日志备份。在恢复数据时，可使用事务日志使所有的数据文件恢复到同一个时间点。
2、备份
创建备份设备
SQL Server备份是创建在备份设备上的，所以在对数据库进行备份之前，需要先创建一个备份设备，如磁盘、或磁带煤体。
使用对象资源管理器创建备份设备
使用系统存储过程创建备份设备
语法格式如下：
Sp_addumpdevice [@devtype]’device_type’ ,[@logicalname]=’logical_name’ ,[@physicalname]=’physical_name’ 参数说明：
exec sp_addumpdevice 'disk','bk_score',' e:\backupfile\bk_score.bak' 1．完整备份数据库的语法格式如下：
BACKUP DATABASE database_name TO <backup_device>[,…n] USE master GO EXEC sp_addumpdevice 'disk','score_bak','d:\score_bak' GO BACKUP DATABASE score TO score_bak 2.文件和文件组备份的语法格式如下：
BACKUP DATABASE database_name {
FILE=logical_file_name | FILEGROUP = logical_filegroup_name } [ ,...f ] TO backup_device [ ,...n ] USE master GO EXEC sp_addumpdevice 'disk','file_bak','d:\file_bak' GO BACKUP DATABASE score file='score' TO file_bak 3.日志文件备份的语法格式如下：
BACKUP LOG database_name TO backup_device [ ,...n ] 【例】先建立一个名为log_bak的备份设备，然后使用BACKUP LOG对score数据库中的逻辑名为“score_LOG”的日志文件进行备份，将文件备份到备份设备log_bak上。
USE master GO EXEC sp_addumpdevice 'disk','log_bak','d:\log_bak' GO BACKUP LOG score TO log_bak 3、恢复数据库
RESTORE DATABASE database_name [ FROM <backup_device>[,…n] ] [with [ FILE=file_number ] [[,]MOVE ‘logic_file_name’ TO ‘operating_system_file_name’] […n] [[,]{
RECOVERY |NORECOVERY }] [[,] REPLACE ] [[,] RESTART ] ] 参数说明：
USE master GO RESTORE DATABASE score FROM score_bak 【例】从备份设备file_bak中恢复score数据，并覆盖原数据库。
USE master GO RESTORE DATABASE score FROM file_bak with replace 4、数据转换
数据的导出
使用对象资源管理器导入数据的步骤如下:
（1）启动Microsoft SQL Server Management Studio，连接到相应的数据库引擎实例。
（2）在“对象资源管理器”中，单击服务器名称以展开服务器目录。
（3）展开“数据库”目录，在score数据库上单击鼠标右键，弹出快捷菜单，依次选择“任务”、“导出数据”命令。如图10.20所示。
（4）执行“导出数据”命令后，弹出“SQL Server导入和导出向导”窗口，单击“下一步”按钮，进入“选择数据源”窗口，在这里需要导出的是SQL Server数据库中的数据，因此在“数据源”下拉列表框中选择“Sql Native Client” 选项，然后在“服务器名称”下拉列表框中选择数据库所在的服务器；最后在“数据库”下拉列表框中选择数据库，在这里选择score数据库。如图10.21所示
（5）单击“下一步”按钮，弹出“选择目标”窗口，在“目标”下拉列表框中选择“Microsoft Excel ”；然后单击“Excel文件路径”后的“浏览”按钮，选择一个Excel表格文件，最后在“Excel版本”下拉列表框中选择“Microsoft Excel 97-2005”。 弹出如图10.22所示。
（6）单击“下一步”按钮，弹出如图10.23所示的“指定表复制或查询”窗口。选中“复制一个或多个表或视图的数据”单选按钮。
（7）单击“下一步”按钮，弹出如图10.24所示的“选择源表和源视图”窗口，用来选择需要导出的表和视图。在这里选择student表，同时可以通过编辑进行查看和修改。
（8）单击“下一步”按钮，弹出如图10.25所示的“保存并执行包”窗口，可以调度包的执行时间，这里选择“立即执行”复选框。
（9）单击“下一步”按钮，在弹出的窗口中单击“完成”按钮，即可完成将SQL Server数据库中基本表的数据导出到Excel表格中。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/213714.html原文链接：https://javaforall.net
