SQL 语法整理

最新推荐文章于 2021-07-26 13:04:57 发布

雨点ing

最新推荐文章于 2021-07-26 13:04:57 发布

阅读量1.6k

点赞数

分类专栏：数据库

数据库专栏收录该内容

22 篇文章 0 订阅

订阅专栏

SQL 语法整理

一、创建数据库

数据库在SQL Server2005的组成

SQL Server2005中的数据库由包含数据库表的集合,以及其他对象(视图、索引、存储过程、函数、触发器等)组成。

a.数据库组成实例

一个实例包含有4个系统数据库和若干个用户自定义数据库。

b.逻辑上文件组组成数据库

SQL Server2005中文件组分为两类：一类是主文件组，另一类是用户自定义文件组。

主文件组主要存储的是数据库的系统信息。

用户自定义文件组则存储着用户的数据信息。

逻辑组成从上到下可理解为:实例-数据库-文件组-对象

c.物理上看数据库的组成

数据库有两种文件组成：数据文件和日志文件。

主数据文件，扩展名是.mdf

二级数据文件，扩展名是.ndf

日志文件，扩展名是.ldf

d.系统数据库

master

model

tempdb

msdb

master

master数据库记录着SQL Server 2005系统中的所有系统级别信息，包括所有的登录账户和系统配置以及所包含的数据库、数据文件的位置。master数据库是SQL Server启动的第一个入口，是所有系统数据库中的重中之重，一旦master数据库损坏，那么SQL Server将无法启动。

model

model数据库是新建数据库的模板。当发出创建数据库的命令时，新的数据库的第一部分将通过复制model数据库的内容创建，剩余部分由空数据页填充。由于tempdb数据库在SQL Server每次启动时都要重新创建，所以model数据库在SQL Server 2005中也是必需的，不能缺少的。

tempdb

tempdb数据库保存了所有的临时表和临时存储过程。默认情况下，SQL Server在运行时，tempdb数据库会根据需要自动增长。每次启动SQL Server时都要重新创建tempdb以便系统启动时，该数据库总是空的。在断开连接时会自动删除临时表和存储过程，并且在系统关闭后没有活动连接。因此tempdb中不会有什么内容从一个SQL Server会话保存到另一个会话。

msdb

msdb数据库提供SQL Server 2005代理程序调度、警报、作业以及记录操作员等活动。

（SQL Server是遵循先写日志，再进行数据库修改的数据库系统。

每个数据库都必须包含一个也只能包含一个主数据文件.

数据库可以没有二级数据文件,也可以拥有很多二级数据文件,二级数据文件在实际工作中主要是存储用户自定义的信息。

每个数据库必须至少有一个日志文件.每个数据库可以有一个日志文件,也可以有多个日志文件。

注意：在创建数据库的时候，每个数据库文件之间都用逗号隔开，在LOG ON关键字之前的那个数据文件结束时，就没有逗号了。）

表概述

表:Table,列:Column,单元:Field

表分为两类：系统表和用户自定义表

临时表

临时表与永久表相似，但临时表存储在tempdb数据库中，但会话断掉之后会自动删除。临时表是在SQL Server2005启动后，用户在操作数据库的时候创建生成的，在断开与SQL Server2005服务连接或者关闭服务以后，临时表将自动删除

列的IDENTITY属性

为表列产生一个自动生成的序列,语法：IDENTITY[(seed,increment)]

命名规则

尽量不要用中文对数据库对象命名

数据库对象命名是不能够出现空格的,但可以用界定符号[]来加以限制去使用.

系统保留的关键字,也需要用界定符号[]来加以限制.

对表的基本操作

1.SQL

Structured Query Language

a.DDL(Data Define Language)

b.DML(Data Manufacturing Language)

c.DCL(Data Control Languange)

SELECT select_list

FROM table_name

[WHERE search_condition]

[ORDER BY order_expression[ASC|DESC]]

TOP字句

select top 2 name,age from student

DISTINCT \ BETWEEN \ IN \ NOT IN\COMPUTE \ COMPUTE BY

select * from parents where f_position in('worker','engineer')

select * from parents where f_position not in('worker','engineer')

COMPUTE BY与GROUP BY的比较

select f_position,count(f_telephone),count(m_telephone) from parents group by f_position

select f_position,f_telephone,m_telephone from parents order by f_position compute count(f_telephone),count(m_telephone) by f_position

(COMPUTE不仅将汇总的数据显示出来，同时还能将详细信息显示出来。

GROUP BY只能显示汇总的信息.)

模糊查询LIKE字句

通配符如下:

%(百分号):表示从0~n个任意字符.

_(下划线):表示单个任意字符.

[](方括号):表示方括号里列出的任意一个字符。

[^]:任意一个没有在方括号里列出的字符。

外联接

LEFT OUTER JOIN或LEFT JOIN \ RIGHT OUTER JOIN或RIGHT JOIN \ FULL OUTER JOIN或FULL JOIN

(左外联接的结果集中除了包括满足条件的行外，还包括左表所有的行(左表中没有满足条件的以空值的形式出现))

INSERT [INTO]

Table_name(column_name)[,…n]

VALUES

(expression)[,…n]

a.使用列清单

例如:向学生表中插入一条学生信息.

INSETR INTO Student

(id ,[name], gender ,age , grade ,class , teacher_id, parents_id)

VALUES(2006008,'Songdan','女',22,1,4,101,2005)

b.省略列清单

例如:向学生表中插入一条学生信息.

INSETR INTO Student VALUES (2006009,'SD','女',23,1,1,101,2008)

UPDATE table_name

SET

{column_name={expression|DEFAULT|NULL}}

[WHERE {<search_condition>}]

修改多行数据

UPDATE Student

SET

teacher_id=103

WHERE

id IN

(

2006001,

2006002,

2006003

)

DELETE [FROM] table_name

WHERE search_condition

TRUNCATE清空表

TRUNCATE TABLE table_name

DROP删除表

DROP TABLE table_name

用主外键实现表关联

主键

主键是表中的一列或者多列数据,用来惟一标识一行数据.在表中,不允许有主键相同的行存在.在受主键约束的列中,不能有空值(NULL)出现。为了有效实现数据的管理，每一张表有应该有一个主键,而且只能有一个。主键可能是列的组合。

创建表时创建主键

id INT CONSTRAINT pk_test1_id PRIMARY KEY

查看表格属性

EXECUTE SP_HELP [表名]

有时候需要有多个列的联合,才能惟一标识一条信息

CREATE TABLE book

(

isbn INT, ---图书编号

copy_no INT, ---图书副本号

book_name CHAR(20), ---图书名称

CONSTRAINT pk_book_isbn_copy_no PRIMARY KEY(isbn,copy_no) ---定义表级主键约束

)

创建表后添加主键

ALTER TABLE table

ADD CONSTRAINT constraint_name PRIMARY KEY(column[,…n])

修改列的值，用update。修改列的属性，用alter table…alter column [列名]

外键

外键约束主要是为了维护两个表之间的一致性关系,是建立在主键的基础之上的,其值是参考主键的内容。主键的值是惟一的,不能为空,而外键的值可以是重复的、为空的，外键的数值必须在主键中出现。在同一张表中可以有多个外键，每一个外键的创建都必须有一个主键相对应，外键可以是一个列，也可以是多个列的组合。

在创建表时，定义外键

CREATE TABLE table_name

(

column_name datatype

[CONSTRAINT constraint_name]

{

[FOREIGN KEY]

REFERENCES referenced_table_name[(ref_column)]

}

)

e.g=〉

CREATE TABLE junior_member

(

member_id INT,

adult_member_id INT,

member_name CHAR(10),

member_gender CHAR(2),

CONSTRAINT pk_junior_member_id PRIMARY KEY(member_id),

CONSTRAINT fk_junior_ adult_member_id FOREIGN KEY (adult_member_id)

REFERENCES adult_member(member_id)

)

/*为表格adult_member创建一个外键约束*/

ALTER TABLE adult_member

ADD CONSTRAINT fk_adult_member_id_member_id

FOREIGN KEY(member_id) REFERENCES member(member_id)

表关联种类

表之间(也就是实体之间)有一定的关联种类，实体之间的不同种类，决定着实体或表格之间的对应关系。有的可以拆成1对1的关系，有的可以拆成1对n的关系，有的可以拆成n对n的关系，不同的关系拆成不同数量的表

在关系数据库中有3个要素：

实体(Entity):客观存在的事物。例如：学生，老师，家长。

属性(Attribute):实体所具有的特性。例如：学生的姓名，年龄，性别。

关系(Relation):实体与实体之间的关系。例如：学生和老师，学生与家长。

a.一对一的关系

两个实体中，如果有一个实体的数值确定了，则另一个实体的数值也就惟一确定了，反之也是如此,那么这种对应关系就是一对一(1:1).

一对一的关系实体在数据库中能够拆分为两张表,为了描述表之间的关联关系，通过主外键的方式联系起来.

b.一对多的关系

实体A与B是一对多(1:n)的关系,是指当A中的一个值确定以后,可以对应到B中的多个值;反过来，当B中的一个值确定以后,惟一地能够对应A中的一个值.

一对多的关系可以拆分成两张表，通过主外键的方式联系起来。

c.多对多的关系

实体A与B是多对多(m:n)的关系,是指当选中A的一个数值确定后，可以对应到B 中的多个值;反过来，当B中一个值确定以后也可以对应A中的多个值.

多对多的关系可以拆分成为3张表，中间的关系表与其他两张表建立起主外键的关系,通过这张关系表来体现这两个实体之间的关系。

删除主外键

删除是有一定的顺序的。

要先删除外键的信息，然后再删除主键，否则就会报错。

如果要删除主键，要先看是否被外键引用，如果被引用那么就会报错。

数据库的管理

查看数据库属性

EXECUTE SP_HELPDB [数据库名]

数据库更名

EXECUTE SP_RENAMEDB ‘newdemo’,’demo’

（将数据库newdemo变更名称为demo）

文件组更名

USE master

ALTER DATABASE school

MODIFY FILEGROUP teachers

NAME=teacher

数据文件更名

/*将school数据库中数据文件逻辑名称为school2变更为school3*/

USE school

ALTER DATABASE school

MODIFY FILE

(

NAME=school2,

NEWNAME=school3,

)

修改数据文件大小

/*修改数据库school中逻辑名称为school2的大小，将其变为23MB*/

USE school

ALTER DATABASE school

MODIFY FILE

(

NAME=school2,

SIZE=23

)

/*为数据库school添加一个叫linshi2的文件组*/

ALTER DATABASE school

ADD FILEGROUP linshi1

/*为数据库school中文件组linshi1添加一个数据文件叫linshi2*/

ALTER DATABASE school

ADD FILE

(

NAME=linshi2,

FILENAME=’C:\DATA\linshi2.ndf’,

SIZE=3,

MAXSIZE=20,

FILEGROWTH=1

)

TO FILEGROUP linshi1

/*为数据库school添加一个日志文件，逻辑名称为school_log4*/

ALTER DATABASE school

ADD LOG FILE

(

NAME=school_log4,

FILENAME=’C:\DATA\school_log4.ldf’,

SIZE=3,

MAXISZE=20,

FILEGROWTH=1

)

删除文件组/数据文件/日志文件

如果要删除文件组,必须要先删除文件组内的数据文件,否则不可能删除文件组.

如果要删除数据文件,数据文件不能为空。如果数据文件内容不为空也不能删除数据文件,日志文件也是如此,如果不为空的情况下是不能删除的，只有为空的情况下才能删除.

如果数据文件有内容，应先执行TRUNCATE TABLE table_name(删除表中的所有数据，只留下一个表的定义),然后再删除数据文件，最后删除文件组。

/*先删除数据文件*/

ALTER DATABASE school

REMOVE FILE linshi2

/*再删除文件组*/

ALTER DATABASE school

REMOVE FILEGROUP linshi1

/*最后删除日志文件*/

ALTER DATABASE school

REMOVE FILE school_log4

分离附加用户数据库

分离、附加用户数据库是一种很好的转移数据库、备份数据库的方法。

/*分离数据库demo*/

USE master

EXECUTE SP_DETACH_DB ‘demo’

/*将分离的demo数据库附加到SQL Server实例上*/

USE master

EXECUTE SP_ATTACH_DB ‘demo’,

‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\demo.mdf’

（如果数据库正在应用，或者在这个数据库操作环境下，是不可以分离该数据库的，只有在其他数据库的操作环境下，而且要分离的数据库不被使用的前提下，才能够使用SP_DETACH_DB命令分离数据库。）

理解master数据库以及系统表在系统中的作用

由master数据库中的系统表识别出用户自定义数据库以后，SQL Server实例将读取用户自定义数据库中的系统表。用户自定义数据库中的系统表将识别出其数据库中的文件组名称，数据文件名称与数据文件物理位置以及数据库对象等。

master数据库中的sysdatabases表中的信息，存储着整个SQL Server实例的数据库信息，由这个表识别了其他数据库，再由用户数据库中类似于sys.filegroups以及sys.database_files的表来识别用户自定义数据库中的内容。就这样一级一级地识别出数据库中的所有对象。

表的管理

USE school

/*查看student表的属性*/

EXECUTE SP_HELP student

修改表格名称

/*students为现在表格名称，student为变更后表格名称*/

EXECUTE SP_RENAME ‘students’,’student’

/*将student表格中的“学生名”列的列名变更为“name”*/

EXECUTE SP_RENAME ‘student.学生名’,’name’

ALTER TABLE修改表结构

（修改,增加,删除列或约束;

使约束和触发器有效或无效等。）

添加列

ALTER TABLE student

ADD city VARCHAR(10)

删除列

ALTER TABLE student

DROP COLUMN city

将学生表年龄字段变更为字符串U

ALTER TABLE student

ALTER COLUMN age CHAR(10)

将列变为NOT NULL

ALTER TABLE student

ALTER COLUMN graduation CHAR(20) NOT NULL

实现数据的完整性

数据的正确,信息的完整性具有3个特点:

数据的值正确

数据的和谐性

数据之间保持不同表之间的和谐关系

数据完整性概念

数据完整性是指存放在数据库中,数据要满足业务规则.

在数据库中数据的完整性类型共有3种:

a.实体完整性

b.域完整性

c.引用完整性

a.实体完整性

也称为行完整性,是指表中的每一行都必须能够惟一标识,且不存在重复的数据行.

实现实体完整性的方法:

(1)强制表的标识规范;

(2)列或主键的完整性(通过索引、UNIQUE约束、PRIMARY KEY约束或IDENTITY属性实现).

b.域完整性

也称为列完整性,是指限制用户往列中输入内容，保证给定列的输入数据的有效性。

实现实体完整性的方法:

(1)限制列值的数据类型;

(2)限制列值的格式(通过设置CHECK约束与规则来实现)；

(3)限制列值的取值范围(通过FOREIGN KEY约束、CHECK约束、DEFAULT定义、NOT NULL 定义和规则)

（CHECK约束和规则都是实现域完整性的方法。CHECK约束与表存储在一起，当表被删除时，CHECK约束也将同时被删除；规则独立于表存在于数据库中，可以被多个表操作引用，只有当数据库被删除，规则才被删除。）

c. 引用完整性

也称为参照完整性、关联完整性，是指当一个表引用了另一个表中的某些数据时，要防止非法的数据更新，如插入、修改或删除，以保证表之间已定义的关系。实现引用完整性主要通过主键与外键的联系来实现。外键的取值参照主键的取值，即外键的取值要不取自主键，要不为空。设置引用完整性可以确保键值在所有的关联表中都一致，这种一致性不允许引用参照表中不存在的值，如果主键值更改了，那么在整个数据库中对该键值的引用也要进行一致的更改。

约束

在SQL Server中用于实现数据完整性的途径，主要是约束(Constraint)、标识列(Identity Column) 、默认值(Default) 、规则(Rule) 、触发器(Trigger) 、数据类型(Data Type) 、索引(Index)和存储过程(Stored Procedure)等。

PRIMARY KEY主键约束

FOREIGN KEY外键约束

UNIQUE惟一约束

CHECK检查约束

DEFAULT默认值约束

CASCADE级联引用一致性约束

（PRIMARY KEY主键约束：当某一（组）列被设置为主关键字，该一（组）列的取值不能为空或有重复值出现。

UNIQUE惟一约束：当某一（组）列被设置了惟一性约束，该一（组）列的取值不能有重复值出现，可以取空值，但最多只能有一个数据为空。

既然有了主键,为什么还需要UNIQUE约束呢?每个实体,都应该有一个惟一的标识来表示自己,这类属性值可以作为表的主键.另一方面,实体可能还有其他的属性值,也是需要惟一的,比如驾照,军人证,暂住证等,这类属性值就不能作为表的主键存在,但是还需要对存储这类值的列进行限制,则出现了UNIQUE约束.）

Check约束

在parents表格中添加salary列，数据类型为money，并为其创建约束

ALTER TABLE parents

ADD salary MONEY CHECK (salary>0)

/*先在student表格中添加一列名为city数据类型为char(10)*/

/*为学生表格添加限制生源地约束*/

USE school

ALTER TABLE student

ADD city CHAR(10)

ALTER TABLE student

ADD CONSTRAINT ck_student_city CHECK(city in('北京','天津','上海','广州'))

可以使用SP_HELP以及SP_HELPCONSTRAINT两个命令来查看当前表中所有的约束。

SQL Server是不会检验出约束的限制是否正确的。约束的作用是叠加的，如果一个列中有多个约束，则录入的数值就要满足所在列上的所有约束

DEFAULT约束

/*为表格student的city列添加一个默认值约束*/

USE school

ALTER TABLE student

ADD CONSTRAINT df_student_city

DEFAULT(‘北京’) FOR city

/*输入带默认值的一条学生记录*/

USE school

INSERT INTO student

(id,name,gender,age,grade,class,teacher_id,parents_id,graduation,city)

VALUES

(2006008,’MR.wang’,’男’,24,1,1,101,2003,’2006年毕业’,default)

USE school

INSERT INTO student

(id,name,gender,age,grade,class,teacher_id,parents_id,graduation)

VALUES

(2006008,’MR.wang’,’男’,24,1,1,101,2003,’2006年毕业’)

<两种方法都对，都可以插入，其中city列的值为北京。>

如果该列允许为空，可以将该行赋予为空。由于有时不希望可能有空的列，可为该列定义一个默认值。

在创建表时，可以创建 DEFAULT 定义作为表定义的一部分。如果某个表已经存在，则可以为其添加 DEFAULT 定义。表中的每一列都可以包含一个 DEFAULT 定义。

若要修改 DEFAULT 定义，必须首先删除现有的 DEFAULT 定义，然后用新定义重新创建它。

UNIQUE约束

/*在student表格中增加名为uk_student_cardno惟一性约束*/

ALTER TABLE student

ADD CONSTRAINT uk_student_cardno UNIQUE(cardno)

对于默认值约束和检查约束,如果一开始创建表时,列允许空,并且表中已经存有数据了即空因为允许空,如果不添加数据的话),那么后来添加约束时,对空值不起作用.

禁用/启用约束

ALTER TABLE table_name

CHECK|NOCHECK CONSTRAINT constraint_name

对于新约束，假定为检验表原始数据(原始数据为NULL的不参加检验,不为NULL的原始数据参加检验)，对于重新启用的约束，假定为不检验原始数据.

{ CHECK | NOCHECK} CONSTRAINT

指定启用或禁用 constraint_name。此选项只能与 FOREIGN KEY 和 CHECK 约束一起使用。如果指定了 NOCHECK，则将禁用约束，从而在将来插入或更新列时，不根据约束条件进行验证。无法禁用 DEFAULT、PRIMARY KEY 和 UNIQUE 约束。

WITH CHECK | WITH NOCHECK

指定表中的数据是否用新添加的或重新启用的 FOREIGN KEY 或 CHECK 约束进行验证。如果未指定，对于新约束，假定为 WITH CHECK，对于重新启用的约束，假定为 WITH NOCHECK。

如果不想根据现有数据验证新的 CHECK 或 FOREIGN KEY 约束，请使用 WITH NOCHECK。除极个别的情况外，建议不要进行这样的操作。在以后所有数据更新中，都将计算该新约束。如果添加约束时用 WITH NOCHECK 禁止了约束冲突，则将来使用不符合该约束的数据来更新行时，可能导致更新失败。

查询优化器不考虑使用 WITH NOCHECK 定义的约束。在使用 ALTER TABLE table CHECK CONSTRAINT ALL 语句重新启用这些约束之前，将忽略这些约束。

对表原始数据不校验添加CHECK约束

默认情况下，新添加的约束是检验表原始数据的.

如果数据库中的表需要创建约束,而原始数据或者说老系统的数据不满足约束的要求但想保留,而未来数据都满足约束的要求，这个时候只能通过不检验原始数据的方式来添加约束.

此时如果还想录入不满足约束的信息,只能通过约束的禁用|启用来实现.

对表中原始数据不检验添加约束的语法如下:

ALTER TABLE table_name

WITH CHECK|NOCHECK ADD CONSTRAINT constraint_name constraint_content

一般情况下是由于老的系统的数据需要继续保留,而新的数据却满足关系数据库的法则,在这种情况下只能通过对原始数据不检验添加约束来解决。

使用级联CASCADE

如果关联表中在主表中的主键信息进行了修改,关联表中的存储与其相对应的外键信息也应该同步的修改.但是由于主表被关联表所引用,所以无论删除或者修改主表中主键,都会报错,因为违反了信息的完整性.用户可以在先删除或者更改外键信息的前提下，再删除或者修改主键信息,这样就不会报错.但是如果表的主键信息,被多个表所引用,那么其操作将是非常繁重的,这个时候，就不能通过这个方法来实现数据的完整性.为此SQL Server提供了一种叫级联的方式，当主键的信息删除或者修改时,引用的外键信息就会相应地删除或者修改。

使用级联的语法如下:

FOREIGN KEY (column[,…n])

REFERENCES referenced_table_name[(ref_column[,…n])]

[ON DELETE CASCADE]

[ON UPDATE CASCADE]

ON DELETE CASCADE :

删除级联,如果主表的主键被删除,则从属表中引用主键相关键值的信息也删除.

ON UPDATE CASCADE:

更改级联,如果主表的主键被修改，则从属表引用主键相关键值的信息也被更改。

在学生表格上创建外键约束

ALTER TABLE student

ADD CONSTRAINT fk_student_teacher_id_teachers_id

FOREIGN KEY(teacher_id) REFERENCES teachers(id)

ON DELETE CASCADE

ON UPDATE CASCADE

使用级联的情况下, 删除编号为101的老师, 修改编号为101的老师信息,结果是成功删除或更新.同时学生表中的tercher_id也被删除或更新

不使用级联的情况下,报错: 因为与学生表的外键约束冲突.学生表的外键tercher_id引用老师表的主键id.

创建/使用规则

规则类似于CHECK约束,是一种独立的对象,是与表同级别的对象.

CHECK约束只能约束表中的列,没有可复用性.而规则却是可复用的。

创建规则的语法如下:

CREATE RULE rule_name

AS condition_expression

绑定规则的语法如下:

SP_BINDRULE [@rulename=]’rule’,[@objname=]’object_name’

创建规则/规则的绑定

规则由于是与表级别相同的对象,如果要将规则应用,必须与要使用这个规则的列进行绑定,这样的列才能被规则所限定.

/*在school数据库中创建年龄规则*/

USE school

CREATE RULE age_rule

@age>0

/*将规则与学生表格列绑定*/

USE school

EXECUTE SP_BINDRULE age_rule,’student.age’

将规则从列上解除绑定/删除规则

当规则还与多个表的列有绑定的情况下是不能够随便删除的,需要先将规则从表的列上解除才能够删除规则。

/*解除表格student上规则与age列的绑定*/

USE school

EXECUTE SP_UNBINDRULE ‘student.age’

/*删除规则age_rule*/

USE school

DROP RULE age_rule

通过SP_CONSTRAINT或SP_HELP命令查看表上的规则。

创建/使用DEFAULT对象

DEFAULT对象与规则类似,都是与表同一个级别的对象，同规则的创建方式、使用方法是一样的。

DEFAULT对象是为特定数据库定义的，通过将其绑定到默认值要应用的每个列上，与规则类似，都可以被不同的列共享。

如果DEFAULT对象绑定到某一列上，该表已经为列指定了不同的默认值。在向列绑定新的默认值时，原来绑定的默认值对象就会自动地从该表中释放出来。

创建DEFAULT对象/绑定DEFAULT对象

/*创建一个名称为df_age的默认值*/

USE school

CREATE DEFAULT df_age

/*在学生表格年龄列绑定df_age*/

USE school

EXECUTE SP_BINDEFAULT df_age,’student.age’

DEFAULT从列上解除绑定/删除DEFAULT对象

与解除规则绑定/删除规则的操作一样，如果要删除DEFAULT对象，就先要将绑定在表列上的DEFAULT解除，然后删除DEFAULT对象。

/*使用SP_UNBINDEFAULT命令解除绑定*/

USE school

EXECUTE SP_UNBINDEFAULT ‘student.age’

/*删除df_age对象*/

USE school

DROP DEFAULT df_age

DEFAULT与RULE的共同点如下：

a.独立于表的数据库对象。

b.定义后需要BIND列。

c.要删除必须要解除绑定。

视图

视图是一个逻辑的概念,也称之为虚拟的表，对于外部的用户而言,视图与表的结构一样被使用.使用视图主要是为用户提供一个快捷的访问方法,或者屏蔽用户对真实数据源直接访问.视图中实际上封装的仅仅是一些SELECT查询语句,而并非真实的数据,所以被称之为虚拟的表。作为虚拟的表仍然有表的一些属性,对表中数据的添加、修改、删除一样可以作用于虚拟表，即视图上。

视图也被称为虚拟的表，其内容由SELECT查询语句定义。同真实的表一样，视图包含了一系列带有名称的列和行的数据。但是，视图并不在数据库中以存储的数据集合形式存在。用行和列的数据，来自由定义视图的查询所引用的表，并且在引用视图时动态生成。视图一经定义，便存储在数据库中，与其相对应的数据并没有像表那样又在数据库中再存储一份。通过视图看到的数据只是存放在基表中的数据。对视图的操作与对表的操作一样，可以查询、修改、删除。通过对视图看到的数据进行修改时，相应的基表的数据也要发生变化，同时，若基表的数据发生变化，这种变化也可以自动地反映到视图中。

视图和查询最主要的差别是：视图的存储是作为数据库开发者设计数据库的一部分；而查询仅仅是对表的查询并非数据库设计的一部分。

创建一个能同时反映学生与老师信息的视图。

/*创建名称为v_student_teacher的视图*/

USE school

CREATE VIEW v_student_teacher

SELECT s.id AS 'student_id',

s.name AS 'student_name', ----学生姓名

s.gender AS 'student_gender', ----学生性别

s.age AS 'student_age', ----学生年龄

s.grade, ----学生年级

s.class, ----学生班级

s.teacher_id AS 's_teacher_id', ----老师编号

s.parents_id, ----父母编号

t.id AS 'teacher_id', ----老师编号

t.name AS 'teacher_name', ----老师姓名

t.gender AS 'teacher_gender', ----老师性别

t.age AS 'teacher_age', ----老师年龄

t. subject, ----所教的学科

t. teaching_age, ----教学年龄

t. position, ----工作岗位

t. salary ----岗位工资

FROM student AS s

INNER JOIN

teachers AS t ON s.teacher_id = t.id

/*使用视图*/

USE school

SELECt * FROM v_student_teacher

创建视图

CREATE VIEW view_name

select_statement

/*建立简单视图v_student*/

USE school

---创建视图v_student

CREATE VIEW v_student AS

SELECT * FROM student

<基表变化则试图将发生相应的变化>

通过视图修改数据

通过更新视图数据(包括添加、修改和删除)可以修改基本表数据。但并不是所有的视图都可以更新，只有对满足可更新条件的视图才能进行更新。

任何修改(包括UPDATE、INSERT和DELETE语句)都只能引用一个基本表的列(要修改的列必须属于视图定义中的同一个基表)。

即使是可更新视图，也不能随意更新数据。如果视图所依赖的基本表有多个时，不能向该视图添加数据，因为这将影响多个基本表。

向可更新视图中添加数据时，系统会按照添加记录的键值所属的范围，将数据添加到其键值所属的基本表中；修改数据时，若视图依赖于多个基本表，那么一次只能修改一个基本表中的数据；删除数据时，若视图依赖于多个基本表，就不能通过视图删除数据。

通过视图添加数据

INSERT INTO view_name(column_name…)

VALUES(value1,value2,…)///基本表也发生了相应的变化

INSERT INTO v_student_teacher

(student_id,student_name,student_age,graduation,cardno)

VALUES ///未指定的列都赋值为NULL。。

(2006013,'小明',24,'已毕业','220200030304304042')

通过视图更改数据

在视图使用了多个底层基表联接的情况下，每次更新操作只能来自某一个基表中的数据列的值。

语法：

UPDATE view_name

SET column_name1=<values>

WHERE column_name2=<values>

通过视图删除数据

当一个视图由两个或两个以上基表构成时,不允许通过视图删除基表中的数据,即只能通过单基表视图删除数据。

语法:

DELETE FROM view_name

WHERE column_name=<values>

修改视图

ALTER VIEW view_name

AS select_statement

/*重命名视图student_teachers为v_student_teachers */

EXEC sp_rename 'student_teachers','v_student_teachers'

/*删除视图v_student_teachers*/

DROP VIEW v_student_teachers

存储过程

创建一个快速输入学生信息的存储过程

/*创建存储过程i_student*/

USE school

CREATE PROCEDURE i_student

@id INT,@name CHAR(30),@gender CHAR(2),@age INT,@grade SMALLINT,

@class SMALLINT,@teacher_id INT,@parents_id INT,@graduation CHAR(20),

@city CHAR(10),@cardno CHAR(20)

INSERT INTO student

(id,name,gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)

VALUES

(@id,@name,@gender,@age,@grade,@class,@teacher_id,@parents_id,

@graduation,@city,@cardno)

/*使用存储过程i_student录入信息*/

EXECUTE i_student

2006012,'Lily','女',23,1,1,103,2003,'尚未毕业','天津','220200030304304043'

什么是存储过程

存储过程是一套已经预先编译好的SQL代码,是SQL语句和可选控制语句的集合及一个独立的数据库对象.存储过程在数据库内可以由应用程序调用执行,而且允许用户声明变量、有条件执行以及其他强大的编程工程。由于存储过程是已经编译好的代码，所以执行的时候不需要分析也不需要再次编译，能够提高程序的运行效率。

存储过程可以包含程序流、逻辑以及对数据库的查询。可以接受参数、输出参数、返回单个或者多个结果集以及返回值。

存储过程的类型：

a.系统存储过程：存储在master数据库(以SP_为前缀)，提供了有效的查询系统表的方法，可以实现许多系统管理功能。

b.本地存储过程：在每个数据库中用户创建的存储过程是通过SQL语句创建的。

c.临时存储过程

d.远程存储过程

e.扩展存储过程

用户自定义存储过程

CREATE {PROC|PROCEDURE} procedure_name @parameter data_type

[WITH ENCRYPTION]

AS <sql_statement>[;][…n]

不带参数的存储过程

例如：现在每天学校需要将学生表中的数据，添加时间戳以后备份到学生备份表中。

/*创建备份学生表格信息的存储过程*/

/*创建一个名称为p_bak_student存储过程*/

USE school

CREATE PROCEDURE p_bak_student

INSERT INTO student_bak

(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno,[sysdate])

SELECT

id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno,getdate()

FROM student

使用存储过程来实现查询

/*创建名为s的存储过程来存储查询学生表格信息*/

USE school

CREATE PROCEDURE s

SELECT * FROM student

/*不是用EXECUTE运行名称为s的存储过程*/

便于用户每次都不使用复杂的SELECT语句来进行查询。

可以直接运行存储过程名称。

带简单参数的存储过程

/*带学号参数的存储过程*/

CREATE PROCEDURE s

@id int/*参数*/

SELECT * FROM student

WHERE id=@id

/*输入参数2001002的学生号，查询学号2001002学生的信息*/

s 2001002/调用形式/

带有通配符参数的存储过程

例如：通过输入学生的模糊名称，查找其相应的班主任。

/*创建名称为query_student的存储过程*/

CREATE PROCEDURE query_student

@student_name varchar(20)=’%’

SELECT s.id AS ‘学生号’,

s.name AS ‘学生名’,

s.teacher_id AS ‘老师编号’,

t.name AS ‘老师姓名’

FROM

student s

INNER JOIN

teachers t

s.teacher_id=t.id

WHERE s.name LIKE @student_name

/*执行query_student存储过程*/

EXECUTE query_student ‘小%’

/*执行query_student存储过程*/

EXECUTE query_student

GO/*返回所有行

@student_name varchar(20)=’%’是参数默认值的写法,因此执行EXECUTE query_student返回所有行.

查看存储过程源代码

EXECUTE SP_HELPTEXT i_student

为存储过程加密

/*修改存储过程，将存储过程i_student加密*/

ALTER PROCEDURE i_student

@id INT,@name CHAR(30),@gender CHAR(2),@age INT,@grade SMALLINT,

@class SMALLINT,@teacher_id INT,@parents_id INT,@graduation CHAR(20),

@city CHAR(10),@cardno CHAR(20)

WITH ENCRYPTION

INSERT INTO student

(id,name,gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)

VALUES

(@id,@name,@gender,@age,@grade,@class,@teacher_id,@parents_id,

@graduation,@city,@cardno)

通过存储过程传递参数

CREATE {PROC|PROCEDURE} [schema_name.] procedure_name[;number]

[{@parameter[type_schema_name.]data_type}

[VARYING][=default][ [OUT[PUT]

default:定义参数的默认值.如果定义了参数的默认值,则在调用存储过程时可以不指定该值.默认值必须是一个常量或者是NULL.如果存储过程在LIKE字句中使用参数，则默认值中可以包括通配符(%,_,[],[^]).

OUTPUT:指出该参数是一个输出类型,是一个返回调用过程。

例如：输入老师编号参数，返回这个老师管理下的所有学生数.

/*返回老师管理下所有学生数*/

USE school

CREATE PROCEDURE sum_student

@teacher_id INT,@sum_student INT OUTPUT

SELECT @sum_student=COUNT(ID) ---COUNT是统计函数

FROM student

WHERE

teacher_id=@teacher_id

/*调用存储过程sum_student,输入老师编号102*/

USE school

DECLARE @sum_student INT ---@sum_student为声明的变量

EXECUTE sum_student 102,@sum_student OUTPUT

修改存储过程

ALTER {PROC|PROCEDURE} [schema_name.] procedure_name[;number]

[{@parameter[type_schema_name.]data_type}

[VARYING][=default][ [OUT[PUT]

[WITH ENCRYPTION]

<sql_statement>[…n]

重新命名以及删除存储过程

重新命名存储过程的方法是使用系统存储过程SP_RENAME来实现的。

删除存储过程的语法如下：

DROP PROCEDURE procedure_name

函数

系统函数

(1)配置函数

@@VERSION

/*使用@@VERSION查看当前SQL Server版本*/

SELECT @@VERSION AS ‘SQL Server版本’

@@SERVERNAME

/*查看SQL Server的当前实例名称*/

SELECT @@SERVERNAME AS ‘服务名称’

(2)日期与时间函数

GETDATE()

/*查看当前系统时间*/

SELECT GETDATE() AS ‘当前系统时间’

YEAR(date)/ MONTH/DAY

/*查看给定时间的年份*/

SELECT YEAR(‘03/12/2005’) AS ‘年’

DATEADD(datepart,number,date)

/*查看20天后的系统时间*/

SELECT DATEADD(day, 20, GETDATE())

AS '二十天后的时间'

DATEDIFF(datepart,startdate,enddate)

/*查看系统当前与北京奥运会相差的天数*/

SELECT DATEDIFF(day, GETDATE(), ‘08/8/2008')

AS ‘今天与北京奥运会还差多少天'

DATENAME(datepart,date)

/*查看当前系统时间的月份值*/

SELECT DATENAME(month,getdate()) as '本月'

(3)数学函数

COUNT()

返回的是INT数据类型的数组项数，主要用于统计当前表中有多少数据量，其中空值的行数不记录在内。

/*统计所有学生信息*/

SELECT COUNT(*) as ‘学生总数’ FROM student

AVG()/ABS()

ROUND(numeric_expression,length[,function])

/*计算学生的平均成绩(保留两位小数)*/

SELECT ROUND(AVG(result),2) as ‘主科平均分’

(4)字符串函数

SUBSTRING(expression,start,length)

LOWER(character_expression)

LTRIM(character_expression)---去除字符串两端的空格

d.ASCII(character_expression)

e.REVERSE(character_expression)

f.REPLACE(string_expression1,string_expression2,string_expression3)

SELECT REPLACE(‘ABCDECCC’,’C’,’X’) AS ‘使用REPLACE函数运行结果’

g.LEFT(charater_expression,integer_expression)

SELECT LEFT(‘ADBADSS’,5) AS ‘返回前5位字母’

(5)其他常用函数

a.ISDATE(expression1)

用于判断输入的表达式是否为有效日期，返回的数值为1或者0。

b.ISNULL(expression1,expression2)

此函数判断expression1的值是否为NULL，如果expression1为空，则返回expression2；如果不为空，则返回expression1的原值。在使用该函数时，expression1与expression2的类型必须是相同的。

c.ISNUMERIC(expression)---判断是否是数字

SELECT ISNUMERIC(23432.2345), ISNUMERIC(23),

ISNUMERIC('ADG')

d.PRINT(expression)

PRINT('HELLO WORLD')

e.CAST(expression AS datatype)

将expression转换为另一种数据类型

SELECT name AS ‘学生姓名’,

CAST(sysdate AS char(20)) AS ‘数据备份时间’

FROM student_bak

f.CONVERT(data_type[(length)],expression[,style])

把表达式的值从一种数据类型转换为另一种。但比CAST函数更加细致。

SELECT CONVERT(CHAR,GETDATE(),101) AS ‘转换成字符串的时间’

用户自定义函数

(1)标量函数

(2)内嵌表值函数

(3)多语句表值函数

标量函数是一个简单的数值,例如INT、CHAR、DECIMAL(十进制)等。标量函数的函数体要以BEGIN开始，并且用END来标识结束。

内嵌与多语句表值函数，返回的都是一个TABLE的数据类型，即一个表。其中，内嵌表值函数返回的是一个用SELECT语句的查询值，而多语句则返回的是用户自定的TABLE变量，是包含在BEGIN与END之间的函数体。

(1)标量函数

CREATE FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type

[ = default ] }

[ ,...n ]

]

)

RETURNS return_data_type

[ WITH <function_option> [ ,...n ] ]

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

[ ; ]

例如：创建函数f(x)=x*x+2X+1

/*创建一个名称为f()的函数*/

CREATE FUNCTION f(@x INT) RETURNS INT

BEGIN

RETURN @X*@X+2*@X+1

END

/*将@x=3带入函数*/

SELECT dbo.f(3) as ‘参数为3的函数计算结果’

（使用用户自定义函数时，都必须加上前面的所有者，前面涉及到dbo就是一个架构，当前创建的用户自定义函数属于这个架构的。）

(2)内嵌表值函数

CREATE FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

[ = default ] }

[ ,...n ]

]

)

RETURNS TABLE

[ WITH <function_option> [ ,...n ] ]

[ AS ]

RETURN [ ( ] select_stmt [ ) ]

[ ; ]

例如：创建一个函数，在输入学生编号以后，输出学生、老师的姓名。

/*在school数据库中创建带学生编号参数的函数*/

CREATE FUNCTION select_student(@id INT)

RETURNS TABLE

RETURN

SELECT s.name AS ‘学生姓名’,

t.name AS ‘老师姓名’

FROM student s INNER JOIN teachers t

ON s.teacher_id=t.id

WHERE s.id=@id

/*在函数中输入学生编号*/

SELECT * FROM dbo.select_student(2006005)

(3)多语句表值函数

CREATE FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

[ = default ] }

[ ,...n ]

]

)

RETURNS @return_variable TABLE < table_type_definition >

[ WITH <function_option> [ ,...n ] ]

[ AS ]

BEGIN

function_body

RETURN

END

[ ; ]

@return_variable就是要返回的表，这里用到了一个之前没有用过的数据类型TABLE ，

< table_type_definition >表示对表限制

例如：创建一个函数，输入学生的编号，输出学生、老师及家长姓名。

/*创建一个带学生编号参数的多语句表值函数*/

CREATE FUNCTION select_info(@id INT)

RETURNS @information TABLE

(

学生姓名 CHAR(20),

老师姓名 CHAR(20),

父亲姓名 CHAR(20),

母亲姓名 CHAR(20)

)

BEGIN

INSERT INTO @information

SELECT s.name, t.name,p.father,p.mother

FROM student s INNER JOIN teachers t

ON t.id=s.teacher_id INNER JOIN parents p

ON s.parents_id=p.id WHERE s.id=@id

RETURN

END;

SELECT * FROM dbo.select_info(2006005)

修改/重新命名/删除函数

修改：

ALTER FUNCTION function_name

重命名：

SP_RENAME

删除：

DROP FUNCTION {[owner.]function_name}[,…n]

触发器

对数据库的数据操作,可能自动地触发一些其他的操作或者事件的发生，这就是触发器.触发器不同于存储过程,存储过程定义在数据库上,并且可以携带输入、输出参数，同时需要用户使用EXECUTE来进行调用。而触发器大多是定义在表上，没有任何参数，不需要用户调用，而是自动执行的。也就是说当用户操作某表的时候，系统就会自动调用定义上的操作事件。

触发器是一种特殊的存储过程，也是提前编译好的SQL语句，它不同于前面介绍的存储过程，它与表紧密相连，可以看作表定义的一部分，当用户修改表的时候，触发器将会自动执行。

SQL Server2005包括两大类触发器：DML触发器和DDL触发器。

DML触发器

DML触发器是基于表而创建的，可以在一张表创建多个DML触发器。其特点是定义在表或者视图上、自动触发、不能被直接调用。用户可以针对INSERT、UPDATE、DELETE语句分别设置触发器，也可以针对一张表上的特定操作设置。触发器可以容纳非常复杂的SQL语句，但不管操作多么复杂，也只能作为一个独立的单元被执行、看作一个事务。如果在执行触发器的过程中发生了错误，则整个事务都会回滚。

DDL触发器

DDL触发器是一种特殊的触发器，它在响应数据定义语言(DDL)语句时触发。可以用于在数据库中执行管理任务，例如审核以及规范数据库操作。

实现DML触发器

(1)DML触发器

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name . ]trigger_name

ON { table | view }

[ WITH <dml_trigger_option> [ ,...n ] ]

{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

{ sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <method specifier [ ; ] > }

（当INSERT、UPDATE、DELETE语句引起表中的数据变化时，将会自动激活触发器。例如，校验输入的数据是否合法、删除的是否合理等。

AFTER

指定 DML 触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被激发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。

如果仅指定 FOR 关键字，则 AFTER 为默认值。

不能对视图定义 AFTER 触发器。

INSTEAD OF

指定 DML 触发器是“代替”SQL 语句执行的，因此其优先级高于触发语句的操作。不能为 DDL 触发器指定 INSTEAD OF。

对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。但是，可以为具有自己的 INSTEAD OF 触发器的多个视图定义视图。

INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图。如果将 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中，则 SQL Server 将引发错误。用户须用ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。）

(2)AFTER触发器

AFTER触发器是在DML语句执行以后才触发的。该类型要求只有在执行某一操作，如INSERT、UPDATE、DELETE之后才能被触发，而且只能在表上定义。

a.UPDATE操作

/*在学生表格上创建一个UPDATE类型的触发器*/

/*创建一个触发器基于学生表格的学生编号列，年龄列*/

CREATE TRIGGER tg_student_id_age

ON student

AFTER UPDATE

IF UPDATE(id)

BEGIN

INSERT INTO student_update_log(sysdate,log_info,dml_user)

VALUES(GETDATE(),’修改了学生编号’,USER_NAME())

PRINT(‘您正在更改学生表格中的学生编号关键字’)

END

IF UPDATE(age)

BEGIN

INSERT INTO student_update_log(sysdate,log_info,dml_user)

VALUES(GETDATE(),’修改了学生年龄’,USER_NAME())

PRINT(‘您正在更改学生表格中的学生年龄关键字’)

END

/*在表格student上创建一个有关删除操作的触发器*/

CREATE TRIGGER tg_std_del

ON student

AFTER DELETE

BEGIN

INSERT INTO student_del_log(sysdate,del_info,dml_user)

VALUES

(GETDATE(),’删除了学生表格信息，将要影响表格teachers与parents’,USER_NAME())

PRINT(‘您正在删除学生表格中的信息’)

END

/*在表格student上创建一个有关插入操作的触发器*/

CREATE TRIGGER tg_student_insert

ON student

AFTER INSERT

DECLARE @phone char(20)

SELECT @phone=p.phone

FROM inserted as i INNER JOIN parents as p

ON i.parents_id=p.id

IF @phone IS NULL

BEGIN

RAISERROR('父母没有填家庭电话，请填写',16,1)

ROLLBACK TRANSACTION

END

（由于 CHECK 约束只能引用定义了列级或表级约束的列，表间的任何约束（在本例中如果插入的学生数据的父母对应的phone字段为空，则不允许插入该记录）都必须定义为触发器。）

(3)INSTEAD OF触发器

INSTEAD OF触发器是代替在表上的DML操作，执行了SQL语句。

AFTER触发器:在执行了INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。该类型触发器要求只有执行某一操作（如INSERT、UPDATE或DELETE）之后，触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。

INSTEAD OF触发器：使用INSTEAD OF触发器可以代替通常的触发动作。还可为带有一个或多个基表的视图定义INSTEAD OF触发器，而这些触发器能够扩展视图可支持的更新类型。INSTEAD OF触发器执行时并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。

/*在老师表格上创建INSTEAD OF触发器*/

/*创建名称为tg_teachers_del触发器*/

CREATE TRIGGER tg_del_teachers

ON teachers

INSTEAD OF DELETE

BEGIN

INSERT INTO teachers_del_log(sysdate,del_info,dml_user)

VALUES(GETDATE(),’老师表格执行了删除信息的操作’,USER_NAME())

PRINT(‘老师表格的信息现在不允许删除，如果删除请删除或者禁用名为tg_del_teachers的触发器’)

END

老师表中的信息是不能够删除的，如果对信息进行删除操作，一方面需要报警，另一方面要记录下删除操作的时间、使用的用户。

用左边的语句验证上边创建的触发器，右边的语句查看触发器执行的结果。

删除信息时系统作出了警报——“老师表格的信息现在不允许删除，如果删除请删除或者禁用名为tg_del_teachers的触发器”，DELETE操作被代替为记录日志中信息。

INSTEAD OF触发器，还可以创建在UPDATE以及INSERT关键字，其创建方法与上面的DELETE触发器是一致。

(4)inserted与deleted表

在使用触发器的时候，SQL Server在系统中保存了两张临时表，分别为inserted与deleted表。这两张表都存储在高速缓存中，实际上就是事务日志的视图，它们与创建触发器的表拥有同样的结构。

inserted与deleted表中的数据是不能修改的，在inserted中存储着被INSERT和UPDATE语句影响的新数据行。当用户执行INSERT以及UPDATE的时候，将会把要INSERT以及UPDATE的数据保存在inserted表中。

例如：创建一触发器，要求在删除学生信息的时候相应地删除记录学生成绩的信息。

/*在表格student上创建名为tg_student_del的触发器*/

CREATE TRIGGER tg_student_del

ON student

AFTER DELETE

BEGIN

DELETE FROM result

WHERE student_id IN

(

SELECT id FROM deleted

)

INSERT INTO student_del_log

(sysdate,del_info,dml_user,student_id)

SELECT GETDATE(),’用户运行了删除操作’,USER_NAME(),id FROM deleted

PRINT(‘用户运行了删除操作，将学生表格中的学生信息删除了’)

END

实现DDL触发器

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)

CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[ WITH <ddl_trigger_option> [ ,...n ] ]

{ FOR | AFTER } { event_type | event_group } [ ,...n ]

AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }

<ddl_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]

<method_specifier> ::= assembly_name.class_name.method_name

DDL触发器是一种特殊的触发器，它在响应数据定义语言(DDL)语句时触发。其可以用于在数据库中执行管理任务。

从语法上看，DDL与DML触发器不同，DML是创建表上的，而DDL则是创建在实例或者数据库上的。对于DDL是不能够使用INSTEAD OF关键字的，只能用AFTER类型的触发器。

例如，创建一个作用在数据库上的触发器，不允许删除school数据库中的任何表。

/*禁止在school数据库上删除表格或修改表的结构*/

CREATE TRIGGER forb_drop_table

ON DATABASE

FOR DROP_TABLE,ALTER_TABLE

BEGIN

PRINT’对不起，这个数据库不允许删除表格或修改表的结构，如果要删除表格，请先删除触发器forb_drop_table’

ROLLBACK

END

/*删除school数据库中的student表格*/

DROP TABLE student

管理触发器

触发器是特殊的存储过程，所有适用于存储过程的管理方式都适合于触发器。用户可以使用SP_HELPTEXT、SP_HELP、SP_DEPENDS、SP_HELPTRIGGER等系统存储过程以及使用企业管理器来浏览触发器的有关信息，也可以使用SP_RENAME系统存储过程来为触发器重新命名。

触发器的管理

使用SP_HELPTRIGGER查看当前创建的触发器的信息。语法如下：

SP_HELPTRIGGER [@tablename=]’table’[,[@triggertype=]’type’]

其中type是触发器类型的取值范围，包括INSERT、UPDATE，DELETE。如果不指定type的值，那么返回定义在该表上的所有触发器的信息。type可以是下表中的任一值。

值	说明
DELETE	返回DELETE触发器信息
INSERT	返回INSERT触发器信息
UPDATE	返回UPDATE触发器信息

触发器与其他维护数据一致性方法的比较

数据完整性方法	影响	功能	系统开销	事务前或后
约束	定义在表上，在数据库操作之前作检查	中	中	前
DEFAULT/RULE	独立于表，在数据操作之前检查	弱	弱	前
TRIGGER	DML触发器定义在表上，可以实现复杂的商业逻辑。DDL触发器是定义在数据库作用域或者服务器作用域上。	弱	高	后

如果定义了外键，同时也定义了AFTER触发的触发器，如果后者违反了外键的约束，则触发器不工作，因为外键是前触发，TRIGGER是后触发，如果TRIGGER不符合要求，还需要回滚。

修改触发器

ALTER TRIGGER

删除触发器

DROP TRIGGER trigger_name

SQL语言编程

/*使用变量查询学生成绩信息，以学生号为变量查询*/

DELCARE @id INT /*使用DECLARE来定义变量*/

SET @id=(SELECT TOP 1 id FROM student) /*为变量赋值*/

SELECT * FROM result WHERE student_id=@id

注意：每一行的结尾都没有逗号。

SQL语言详述
(1)数据定义(DDL---Data Definition Language)，如CREATE TABLE命令。

(2)数据操作(DML---Data Manipulation Language)，如SELECT命令。

(3)数据控制(DCL---Data Control Language)，如GRANT命令。

GRANT CREATE TABLE TO user_one//授予用户user_one创建表权限。

REVOKE CREATE TABLE FROM user_one//撤消用户user_one创建表权限。

DENY SELECT ON student user_one//拒绝用户user_one在表student上的查询权限。

变量的使用

变量是可以赋值的SQL语句元素，在SQL Server中有局部与系统变量两种。

(1)局部变量

局部变量的声明需要使用DECLARE语句。语法如下：

DECLARE {@variable_name datatype[,…n]}

局部变量必须以@开头，例如：

DECLARE @age INT --声明一个整数型的变量

SET @age=23 --为变量赋值

--或者使用 SELECT @age=23

SELECT * FROM student WHERE age=@age

（局部变量首先需要声明，一方面要声明其变量名称，另一方面要声明变量的数据类型。变量声明以后需要赋值，这样才能使用。赋值的方法有两种：SET和SELECT关键字。局部变量仅仅作用在语句块中，跨出了这个范围就失效了。）

系统变量

系统变量是由SQL Server系统提供的预先声明好的变量，通过在名称前保留两个(@@)符号区别于局部变量。在SQL Server中系统变量以函数的形式出现。

SELECT @@VERSION AS ‘SQL Server版本’

变量的使用

变量的范围是非常大的，在SQL语句块、视图、存储过程、函数中都可以使用。

例如：创建一函数，要求在输入学生编号时输出年龄，如果年龄为0或者负值则警报。

CREATE FUNCTION fun_age(@id INT)

RETURNS VARCHAR(20)

BEGIN

DECLARE @age INT,@result VARCHAR(20)

SET @age=(SELECT age FROM student WHERE id=@id)

IF(@age<0 OR @age=0)

SET @result='错误的年龄'

IF(@age>0)

SET @result='正确年龄'

RETURN @result

END

运算符

运算符是进行数据计算、字符操作、常量和变量比较的符号。SQL Server主要使用的运算符包括：算术运算符、比较运算符、逻辑运算符和字符串串联运算符(+)。

控制流程语句

(1)IF ELSE

IF ELSE条件判断

IF logical_expression

expressions1

[ELSE expressions2]

(2)BEGIN END

使用BEGIN END封装的是SQL语句块,其每个语句块都是完整的单元,在SQL Server中允许使用嵌套的BEGIN END语句块.

(3)WHILE

WHILE logical_expression

BEGIN

Expression

[BREAK]

[CONTINUE]

END

(4)CASE

CASE

[WHEN logical_expression THEN result_expression][…n]

ELSE result_expression

END

/*对grade字段使用CASE关键字*/

SELECT id AS ‘学生编号’,

name AS ‘学生姓名’,

CASE grade

WHEN 1 THEN ‘一年级’

WHEN 2 THEN ‘二年级’

WHEN 3 THEN ‘三年级’

END

FROM student

以上语句改为如下语句也对.但第三列没列名

SELECT id AS 学生编号,

name AS 学生姓名,

CASE

WHEN grade=1 THEN '一年级'

WHEN grade=2 THEN '二年级'

WHEN grade=3 THEN '三年级'

END

FROM student

以上语句改为如下语句也对.但第三列有列名

SELECT id AS 学生编号,

name AS 学生姓名,

年级=

CASE

WHEN grade=1 THEN '一年级'

WHEN grade=2 THEN '二年级'

WHEN grade=3 THEN '三年级'

END

FROM student

(5)GO

(6)RETURN

其他SQL

(1)EXISTS

EXISTS使用

存在判断函数，如果表达式返回至少一行记录，则判断为存在,返回1;如果返回的结果是空集，则判断为不存在，返回0.

IF EXISTS(SELECT COUNT(*) FROM result)

BEGIN

PRINT(‘学生成绩表格中存储了数据’)

END

ELSE

BEGIN

PRINT(‘学生成绩表格中没有存储数据’)

END

(2)INSERT INTO

使用INSERT INTO语句一次性插入多条数据

前提数据库中,必须有表存储了要插入的数据,或者可以通过系统函数来提供(比如使用GETDATE()来提供当前系统时间).表数据类型以及长度必须满足要插入的数据.

例如:将学生表中的数据插入到一张新的表中。

1. 创建名称为student_demo表

2. 将student表中的数据插入到student_demo中

INSERT INTO student_demo

(学生编号,姓名,性别,年龄,年级,班级,老师编号,家长编号,毕业时间,生源地,身份证号)

SELECT id,name,age,gender,grade,class,teacher_id,parents_id,graduation,city,cardno FROM student

(3)SELECT INTO

能够实现快速地转移数据,或者以现有的数据快速地创建一张表,起到一个快速备份的作用。

a.使用单表数据创建表

/*使用SELECT INTO关键字创建teacher_bak的表格*/

SELECT * INTO teachers_bak

FROM teachers

b.使用多表数据创建表

例如:创建一张同时有学生表与家长表数据的表。

/*创建表格family*/

SELECT s.id AS ‘学生编号’,

s.name AS ‘学生姓名’,

s.age AS ‘学生年龄’,

s.grade AS ‘年级’,

s.class AS ‘班级’,

p.id AS ‘家长编号’,

p.father AS ‘父亲名称’,

p.mother AS ‘母亲名称’

INTO family

FROM student s

INNER JOIN

parents p

s.parents_id=p.id

(4)TRUNCATE(截断)和DELETE

TRUNCATE和DELETE命令都是删除数据,但TRUNCATE将表中所有数据都删除,而DELETE则可以有选择地删除,也同样可以将表中所有数据清除.

DELETE删除数据是记录在日志中的,可以通过其将数据恢复,而TRUNCATE命令则是将整个表截断,其操作是不记录在日志中的.所以，TRUNCATE命令清空表数据更加快捷.

表在录入数据以后,占用了一定的系统磁盘空间,当使用DELETE命令以后，并不是将数据原来占有的空间收回,而是继续保留一段时间.而通过TRUNCATE命令则是将表所有的空间收回到系统中,这是TRUNCATE与DELETE命令最根本的区别。

动态SQL语句执行

/*在school数据库中创建create_table存储过程*/

CREATE PROCEDURE create_table @table_name VARCHAR(30)

BEGIN

DECLARE @v_sql VARCHAR(200)

SET @v_sql=’CREATE TABLE ’+@table_name+’(

id INT,

[name] CHAR(20),

city CHAR(20),

age INT)’

EXEC(@v_sql) /* 将字符串转化为语句并执行*/

END

注意:不要漏掉空格,否则会报错.

/*使用存储过程create_table创建linshi表*/

EXEUTE create_table ‘linshi’

（动态的SQL语句，主要是在存储过程与函数中调用了DDL语言，由于通过正常的方式无法实现这些功能，只有通过动态的SQL来实现.动态SQL语句,在所有的大型数据库中都有,通过这种方式，能够实现一般SQL语句无法实现的功能.原理是将普通的SQL语句(主要是DDL语句)封装在一个长的字符串中，通过EXEC命令来调用。）

嵌套查询

基于列的查询

例如:要查找在学生表中,出现的所有老师的信息。

/*以teacher_id为嵌套查询的内容*/

SELECT id AS ‘老师编号’,

name AS ‘老师姓名’,

age AS ‘老师年龄’,

subject AS ‘老师所教的科目’,

teaching_age AS ‘教学年龄’

FROM teachers

WHERE id IN(

SELECT teacher_id FROM student

)

基于比较的查询

例如:在成绩表中找出大于平均成绩的信息.

/*通过对AVG()函数求得学生的平均年龄来对比*/

SELECT * FROM result

WHERE

result>(

SELECT AVG(result) FROM result

)

游标的使用

数据库中的游标类似于C语言的指针.特别是交互式联机应用程序,其并不总能将整个结果集作为一个单元来有效地处理.这些应用程序需要一种机制,以便每次处理一行或部分.游标就是提供这种机制的对结果集的一种扩展。

(1)声明游标

DECLARE cursor_name [INSENSITIVE][SCROLL] CURSOR

FOR

select_statement

[FOR {READ ONLY|UPDATE[OF column_name[,…n]]}]

例如:

DECLARE cursor_student CURSOR

FOR select * from student

(2)使用游标

a.打开/关闭/释放游标

使用游标之前必须要能够打开.语法:OPEN cursor_name

游标在使用完毕以后要关闭.语法:CLOSE cursor_name.关闭游标后,可以再次打开,在一个批处理过程中,也可以多次打开和关闭。

游标本身会占用一定的计算机资源，在使用完后,为了能回收占有的系统资源,应该将其释放。语法:DEALLOCATE cursor_name.当游标释放完毕，如果要重新使用那么就需要重新声明.

b.使用游标取数

在打开游标以后，就可以使用游标取数.

FETCH

[[NEXT|PRIOR|FIRST|LAST

|ABSOLUTE{n|@nvar}

|RELATIVE{n|@nvar}

]

FROM

]

{{[GLOBAL] cursor_name}|@cursor_variable_name}

[INTO @variable_name[,…n]]

c.使用游标

例如:使用游标来将家长表中的信息输出。

/*以表parents为游标的基准*/

USE school

/*声明游标*/

DECLARE cursor_parents CURSOR FOR

SELECT id,father,mother,f_telephone,m_telephone

FROM parents

/**打开游标/

OPEN cursor_parents

/*第一次使用游标取数*/

FETCH NEXT FROM cursor_parents

/*检查@@FETCH_STATUS以确定是否还可以继续取数*/

WHILE @@FETCH_STATUS=0

BEGIN

FETCH NEXT FROM cursor_parents

END

/*关闭游标*/

CLOSE cursor_parents

索引

什么是索引

数据库中表的索引与日常生活中所使用的书或者字典的索引是相似的。索引可以极大地提高查询的速度。

索引的分类

索引包含由表或视图中的一列或多列生成的键.

根据索引的使用方式、数据的存储方式可以将索引分为4类：

惟一性索引、

主键索引、

聚集索引、

非聚集索引。

(1)索引列

索引是创建在表的列上，可以在一个列上，也可以在多个列上，当某一个列数值具有相同的数值，多列的索引就能将其区分开。如果经常搜索两个列或者多个列的时候，索引也是有帮助的。

确定索引的有效性包括以下内容：

a.检查查询中的WHERE和JOIN字句，在任何一个字句中，包含每一列都是索引可以选择的。

b.试验新的索引，检查其对运行查询性能的影响。

c.考虑表中已经创建的索引数据量，不要在一张表上创建大量的索引。索引是一个比较大的对象，会降低查询的速度。

d.检查表中创建的索引，尽量避免包含重复的列，尤其是在多列中。

e.尽量在数值型的列上创建索引，数值的比较、查询速度都要高于字符串。

<经常在student表上，基于学号与姓名作为查询条件查询信息，则在这两列上创建多列的索引，将能大大提高访问的速度。>

(2)聚集索引

聚集索引的含义是：表中的各行的物理顺序与索引的逻辑顺序是相同的，每张表中只有一个，因为一旦创建成功，其数据的物理顺序就固定了。

聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。

只有当表包含聚集索引时，数据行才按排序顺序存储。如果表具有聚集索引，则称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

创建聚集索引的语法：

CREATE CLUSTERED(集群) INDEX index_name

ON {table_name|view_name} (column[ASC|DESC][,…n])

/*在result表id列上创建聚集索引*/

CREATE CLUSTERED INDEX idx_result_id

ON result(student_id ASC)

（result只能创建一个聚集索引，表中的物理顺序与索引中的物理顺序都已固定，当再次创建一个聚集索引时就会报错。如果要在另一个列上创建聚集索引，那么只能先将前面创建的聚集索引删除。）

<大家都使用过字典，排序方法是按着拼音，所以当查询索引（目录）一旦定下来，整个字典中所有字所在的页数就确定了。这种拼音查字方法决定的索引就是聚集索引。>

(3)非聚集索引

非聚集索引不影响表中数据的物理位置，是建立在表中聚集索引的基础之上的。一张表上只能创建一个聚集索引，但是现在却可以创建多个非聚集索引。

/*在result表subject列上创建非聚集索引*/

/*创建名称为idx_result_subject索引*/

CREATE NONCLUSTERED INDEX idx_result_subject

ON result(subject ASC)

/*使用SP_HELPINDEX命令查看在表result上创建的索引*/

EXECUTE SP_HELPINDEX result

<最普通的，也是最重要的查字法就是：拼音查字法。大多数字典都是以拼音查字法为基准，这就对应着数据库中的聚集索引。除了拼音查字法以外，还有一些特殊的，例如：部首查字法、笔画查字法等。这些查字法，是建立在字典中字所在位置都确定的基础上的，是建立在拼音查字法的基础上的。这些查字法在数据库中就对应着非聚集索引。

一张表上只能创建一个聚集索引，但是现在却可以创建多个非聚集索引。就象一个字典，在拼音查字法定下了字所在的位置后，可以制定笔画、部首查字法来适应不同的查询需要。>

(4)惟一性索引

惟一性索引类似于UNIQUE约束，不允许两行具有相同的索引值。如果现有的数据中存在重复的数值，则数据库是不允许创建惟一性索引的。当更新数据的时候，使得表中的数值重复的话，数据引擎也会拒绝这样的修改。

在创建惟一性约束(UNIQUE)的时候，实际上系统也为这张表列创建了一个相应的惟一性索引,其作用与主键约束相同,既起到约束的作用又同时起到索引的作用。

/*在student表cardno列上创建惟一性索引*/

CREATE UNIQUE NONCLUSTERED INDEX idx_student_cardno

ON student(cardno ASC)

ON students ---将索引存储在students文件组上

创建成功！

/*创建名称为idx_student_teacher_id索引*/

CREATE UNIQUE NONCLUSTERED INDEX idx_student_teacher_id

ON student(teacher_id ASC)

ON students --将索引存储在students文件组上

创建失败！

<因为cardno列上的数值是惟一的，所以创建成功。但teacher-id列上的数值不是惟一的，所以无法创建惟一性的索引。>

(5)主键索引

主键,通常是一列或者多个列的组合,惟一地标识表中的数据行.在关系数据库中为表定义了一个主键以后，相应地就会创建一个索引,是跟随主键而创建的,主键索引实际上是惟一性索引的特殊例子,要求被创建索引的列满足主键的要求—— 不能为空、数值惟一。当在查询中使用了索引，允许快速地访问数据。

当创建主键约束的时候，系统会自动创建名称相同的聚集索引，一旦创建了主键，就不能再在这张表上创建聚集索引了。

总结：

CREATE [UNIQUE][CLUSTERED][NONCLUSTERED] INDEX index_name

ON <object>(column [ASC|DESC][,…n])

UNIQUE:代表的是惟一索引。

CLUSTERED：代表的是聚集索引。

NONCLUSTERED：代表的是非聚集索引。

index_name：代表的是索引的名称。

column：是要创建的索引引用的列。

ASC代表索引是升序的，DESC代表索引是降序的。

索引管理

索引创建以后，需要数据库管理员经常维护、管理。由于表在创建索引以后，会影响数据的插入、修改、删除的速度，一般的情况下，会牺牲一点消耗，以换取高速的查询。但是，当大批量数据导入到数据库的时候，就要考虑性能问题，这个时候为了能够节约时间，数据库管理员会把索引删除或禁用，当数据导入完毕，再重新创建索引或者将索引启用。

创建索引要慎重，因其是一个非常大的对象，只有在经常查询的列上，创建索引才能够发挥作用，不要创建在数据量比较小的表上，也不要创建在很少访问的表上。创建索引有利有弊，利是通过索引访问数据，其速度非常快，能够大大节约用户访问系统的速度；弊是创建索引会影响数据的插入、删除、修改的操作，尤其是在表的数据量非常大的时候，利弊的权衡需要仔细考虑。不过在多数情况下，索引带来的数据检索速度的好处要远远大于弊端。

1)修改索引

关键字是ALTER INDEX

a.禁用索引

当进行数据的大批量导入或者修改的时候，可以先将索引禁用，这样可以提高对表数据的DML操作。

/*禁用在学生表格上的idx_student_cardno索引*/

ALTER INDEX idx_student_cardno

ON student

DISABLE

b.启用索引

当大批量数据修改或导入以后，索引需要重新启用。

/*启用在学生表格上的idx_student_cardno索引*/

ALTER INDEX idx_student_cardno

ON student

REBUILD

(2)索引碎片

就像操作系统经常需要进行碎片的整理一样，数据库的操作也会出现很多碎片，尤其是对表中的数据进行频繁添加、修改、删除的动作，更会大大增加碎片的数量。索引是建立在表的数据基础上，由于频繁地对表数据的修改，必然会造成索引碎片的产生。

/*使用DBCC语句来整理碎片*/

DBCC SHOWCONTIG (stduent)

如果索引碎片非常多，可选择以下方法来减少

a.删除然后重新创建聚集索引：创建聚集索引将重新组织，使数据页填满。这种方法的缺点是，索引在删除/重新创建周期内，为脱机状态，并且该操作是一个整体，不可中断。如果中断，则不能重新创建索引。

b.对索引的页级按逻辑顺序重新排序：使用ALTER INDEX…REORGANIZE，对索引的页逻辑顺序重新排序。由于此操作是联机的，因此语句运行时索引可用。此外，中断该操作不会丢失已完成的工作。这种方法的缺点是在重新组织数据方面，没有聚集索引的删除/重新创建操作有效。

c.联机重新生成索引：使用REBUILD和ALTER INDEX。

全文索引

前面介绍的索引，都是建立在数字字段或者字符串类型(一般选择长度比较短的)上的，一般的，是不会在存储地址信息或者人员简介等类字段上，因为长度比较大。而全文索引，就是针对这种大文本建立的索引解决方法，可以快速地定位，提取数据。

对大量非结构化的文本数据进行查询时，使用全文搜索获得的性能优势，会得到充分的表现。对数百万行文本数据执行的LIKE查询，可能需要花费几分钟才能返回结果；但对同样的数据，全文查询只需更小的时间，具体取决于返回的行数。

创建全文索引需要执行两个步骤：

(1)创建全文目录来存储全文索引

(2)创建全文索引

/*创建名为school_cagalog的目录*/

USE school

--在当前数据库中启用全文索引，如果不起用这个属性，则不能创建全文目录

EXECUTE SP_FULLTEXT_DATABASE enable

CREATE FULLTEXT CATALOG(目录) school_catalog

ON FILEGROUP students

/*在表student上添加address列，全文索引用在存储大的信息上*/

USE school

ALTER TABLE student

ADD address varchar(200)

为表格中的数据输入信息……

/*在数据库中使用全文索引*/

SELECT id AS ‘学生编号’,

name AS ‘学生姓名’

address AS ‘学生地址’

FROM student

WHERE FREETEXT(address,’西城区’)

慎重使用/删除索引

索引本身是比较大的对象，在表列上创建索引以后，将大大影响表的数据添加、删除、修改速度。在表上创建索引要仔细考虑。

a.对数据量小的表不要创建索引，这样非但不会提高，反而会降低查询访问的速度。

b.创建索引的表列，经常要被访问，不能在不经常访问的表上创建索引。

c.不要过多地创建非聚集索引，这样会影响访问的速度。

d.要经常地对索引进行重建。

e.在大批量数据导入或数据修改的时候，要先将索引禁用，操作完后，再将索引重新启用。

f.如果表的数据量非常巨大(GB以上的数据量)，这个时候即使创建了索引，进行了最好的优化，也不会将查询速度提高多少。

删除索引语法：

DROP INDEX index_name

ON <object>

实际上，在大批量数据操作的时候，数据库管理员通常会将索引删除，然后重新建立。

事务与锁

什么是事务

事务是单个的逻辑工作单元

如果某一事务成功，则在该事务中进行的所有数据更改提交，成为数据库中的永久组成部分。如果事务运行的过程中出现了错误，则数据修改就会被全部取消(在数据库中称之为ROLLBACK)。

事务用通俗的话来解释就是要么全部执行，要么全部不执行，这就是原子性。

事务分为以下几种：

a.自动提交事务：每条单独的语句都是事务。每一条语句实际上都是事务，对于INSERT 来说，输入的这些数值，如果有一个数据类型不符或者数值的长度过大，就会报错，也就是说，要么所有的数据都插入到一行中，要么所有的数据都没有插入到数据库中，这就是一个自动提交事务。

b.显示事务：每个事务均以BEGIN TRANSACTION语句开始，以COMMIT或ROLLBACK结束。

c.隐性事务：在前一个事务完成时，新事务启动，但仍以COMMIT或ROLLBACK语句显示完成。

d.批处理级事务：只能应用于多个活动结果集(MARS)，在MARS会话中，启动的Transact—SQL显示或隐性变为批处理级事务。当批处理完成时，没有提交或回滚的事务将自动由SQL Server进行。

并发性问题

例如：现有两家小的银行需要帐户之间转帐，通过两个存储过程来描述这次转帐。

(1)不使用事务造成的数据不一致性

/*创建两个存储用户储蓄的账户*/

/*创建两张结构相同的表格，表格名称分别为bank1与bank2*/

CREATE TABLE bank1

(

deposit_id INT, ---储蓄账户编号

name CHAR(20), ---储蓄账户的名称

deposit MONEY --储蓄额

)

CREATE TABLE bank2

(

deposit_id INT, ---储蓄账户编号

name CHAR(20), ---储蓄账户的名称

deposit MONEY --储蓄额

)

/*在表格bank1以及bank2中录入信息*/

---在bank1中录入两条信息

INSERT INTO bank1(deposit_id,name,deposit)

VALUES(1001,’王云’,10000)

INSERT INTO bank1(deposit_id,name,deposit)

VALUES(1002,’李治’,20000)

---在bank2中录入两条信息

INSERT INTO bank2(deposit_id,name,deposit)

VALUES(1003,’张冶’,9000)

INSERT INTO bank2(deposit_id,name,deposit)

VALUES(1004,’李婷’,30000)

/*创建两个转账的存储过程*/

/*创建名称为transfer_bank1与transfer_bank2的存储过程*/

CREATE PROCEDURE transfer_bank1 @deposit_id INT ,@deposit MONEY

UPDATE bank1

SET deposit=deposit+@deposit

WHERE deposit_id =@deposit_id

CREATE PROCEDURE transfer_bank2 @deposit_id INT ,@deposit MONEY

UPDATE bank2

SET deposit=deposit+@deposit

WHERE deposit_id =@deposit_id

/*使用存储过程实现转账功能*/

/*从账户编号为1001的储蓄用户转移5000到账户编号为1003的储蓄用户*/

EXECUTE transfer_bank1 1001,-5000

EXECUTE transfer_bank2 1003,5000

/*使用WAITFOR DELAY来模拟两个账户之间转账的时间延迟*/

EXECUTE transfer_bank1 1001,-5000

WAITFOR DELAY ’20:00’ ---代表延时20分钟以后执行以下语句

EXECUTE transfer_bank2 1003,5000

选中上述代码运行，然后打开SQL Server Configuration Manager管理器，将SQL Server(MSSQLSERVER)服务停止；

然后将SQL Server(MSSQLSERVER)服务重新启动。

<当服务器再重新启动时，就会发现bank1中的王云帐户，已经将5000元转移出去了，但是在bank2中，张治帐户并没有接收到。这就是在没有维护事务一致性的情况下，造成的结果。>

(2)通过事务维护原子性

/*使用显示事务来将转帐标识*/

/*从账户编号1002的储蓄用户转移给账户编号为1004的储蓄用户5000*/

--使用WAITFOR DELAY模拟数据转移过程中的时间延迟

USE school

BEGIN TRANSACTION T1

EXECUTE transfer_bank1 1002,-10000

WAITFOR DELAY ’20:00’ ---代表延时20分钟以后执行以下语句

EXECUTE transfer_bank2 1004,10000

COMMIT TRANSACTION T1

选中上述代码运行，然后打开SQL Server Configuration Manager管理器，将SQL Server(MSSQLSERVER)服务停止；

然后将SQL Server(MSSQLSERVER)服务重新启动查看结果

<从运行来看，当转帐的操作被显示的事务标识后，如果在执行的过程中服务器出现异常，则当数据库重新启动以后就会发现数据没有被改变，这就实现了通过显示事务来维护数据的一致性。>

锁的介绍

锁是Microsoft SQL Server Database Engine用于同步多个用户同时对一个数据块的访问的一种机制。为了解决并发访问问题，SQL Server提供了锁的机制，通过加锁来限定用户访问资源的并发问题。

死锁及处理：

在事务和锁的使用过程中，死锁是一个不可避免的现象。在下列两种情况下，可以发生死锁。

第一种情况是，当两个事务分别锁定了两个单独的对象，这时每一个事务都要求在另外一个事务锁定的对象上获得一个锁，因此每一个事务都必须等待另外一个事务释放占有的锁，这时，就发生了死锁。这种死锁是最典型的死锁形式。

第二种情况是，当在一个数据库中，有若干个长时间运行的事务执行并行的操作，当查询分析器处理一种非常复杂的查询例如连接查询时，那么由于不能控制处理的顺序，有可能发生死锁现象。

当发生死锁现象时，除非某个外部进程断开死锁，否则死锁中的两个事务都将无限期等待下去。SQL Server2005的SQL Server Database Engine自动检测SQL Server中的死锁循环。数据库引擎选择一个会话作为死锁牺牲品，然后终止当前事务(出现错误)来打断死锁。如果监视器检测到循环依赖关系，通过自动取消其中一个事务来结束死锁。在发生死锁的两个事务中，根据事务处理时间的长短作为规则来确定其优先级。处理时间长的事务具有较高的优先级，处理时间较短的事务具有较低的优先级。在发生冲突时，保留优先级高的事务，取消优先级低的事务。

SQL Server2005的安全机制

SQL Server2005是一种大型的关系数据库，安全机制分为5个级别：客户机、网络传输、服务器、数据库以及数据库对象。

a.客户机安全机制

b.网络传输的安全机制

c.实例级别安全机制 (分Windows验证模式和混合验证模式(SQL Server与Windows验证模式))

d.数据库级别安全机制

e.对象级别安全机制

通过命令行的方式创建Windows帐户登录

a.通过”控制面板”|”管理工具”|”计算机管理”,创建一个Windows用户,名称为”study1”,密码自定.

b.以Windows管理员或sa用户登录到Management Studio管理界面。

c.在查询界面输入如下代码:

/*使用CREATE LOGIN命令创建Windwos身份验证模式的SQL Server登录账户*/

USE master

CREATE LOGIN [WANGHUI\study1] FROM WINDOWS

/*使用CREATE USER命令赋予Windows用户study1访问数据库school权限*/

USE school----注意:访问那个数据库就要进入那个数据库创建一个用户

CREATE USER study1

FOR LOGIN [WANGHUI\study1]

通过以上操作

以”CREATE LOGIN”命令赋予windows用户study1访问SQL Server实例的权限

又以CREATE USER命令赋予windows用户study1访问school数据库的权限。

(4)通过命令行的方式创建SQL Server帐户登录

/*创建名为study2的SQL Server身份验证的登录*/

USE master

CREATE LOGIN study2 ---这里的study2为登录名

WITH

PASSWORD=’study2’, ---这里的study2为登录的密码

DEFAULT_DATABASE=school

c.在查询界面输入如下代码:

/*使用CREATE USER命令创建数据库用户*/

/*在school数据库中*/

USE school

CREATE USER study2

FOR LOGIN study2

<只做完步骤b就重新以”study2”与”SQL Server身份验证”模式登录SQL Server,这样做是登录不进去的,必须作完c步骤之后才能登录.即当数据库中不存在与登录相对应的用户时,SQL Server的实例是无法登录的。一个没有映射到用户的登录名是无法登录到数据库的。>

创建架构与删除架构

架构是一种允许用户对数据库进行分组的容器对象。在SQL Server2005中，一个数据库对象通过由4个命名部分所组成的结构来引用.

<服务器>.<数据库>.<架构>.<对象>

使用架构的一个好处是它可以将数据库对象与数据库用户分离。在SQL Server2005中，所有的数据库对象都隶属于架构.因此,在数据库对象属于架构的情况下,可以更改并删除数据库用户.这种抽象的方法允许用户创建一个由数据库角色拥有的架构,以使多个数据库用户拥有相同的对象。

使用SQL命令创建架构

a.以Windows管理员或sa用户登录到Management Studio管理界面。

b.在查询界面输入如下代码:

/*在school数据库中创建名称为teachers的架构*/

USE school

CREATE SCHEMA teachers

AUTHORIZATION dbo

创建了一个名为teachers的架构，其所有者为dbo.

删除用户、架构与登录

由于访问SQL Server的数据库既需要创建登录SQL Server实例的帐户，又需要创建在数据库中的用户。所以要删除帐户时，应该先将此帐户在各个数据库中映射的用户删除，然后再删除这个帐户。

一个登录帐户可以在多个数据库中映射数据库用户。

删除数据库用户

如果删除了数据库中的用户，那么与其对应的登录帐户就无法登录到该数据库中。

以Windows管理员或sa用户登录到Management Studio管理界面;

右键单击用户数据库节点，选择“属性”命令，单击“权限”选项；

选择要删除的用户，单击“删除”按钮。

通过上述操作，删除了在某个用户数据库中的某个用户。一般情况下，一个登录帐户可能在多个数据库中有相对应的数据库用户，删除一个数据库中的用户并不影响此登录帐户访问其他数据库。

通过命令行的方式删除用户、架构与登录

a.删除用户与登录

以Windows管理员或sa用户登录到Management Studio管理界面;

在查询界面中输入如下代码：

/*删除school数据库中的teacher2用户*/
/*使用DROP USER命令删除teacher2用户*/

USE school

DROP USER teacher2 ---删除teacher2用户

/*使用DROP LOGIN命令删除teacher2登录帐户*/

DROP LOGIN teacher2

上述代码首先将登录帐户teacher2在school数据库中映射的用户teacher2删除，然后又将登录帐户teacher2删除。

删除架构

以Windows管理员或sa用户登录到Management Studio管理界面;

在查询界面中输入如下代码：

/*使用DROP SCHEMA命令删除名为teachers的架构*/

USE school

DROP SCHEMA teachers

权限

在数据库中，为了能够让每个登录到数据库的用户正确地行使其权限,并不影响数据库数据的安全，SQL Server提供了一套权限管理的体制。

当用户第一次登录数据库时,没有任何权限来操作数据库中的数据，必须由数据库管理员来赋予相应的权限.例如用户要访问student表,那么必须先赋予查询该表的权限，如果需要修改student的数据，就需要赋予修改该表的权限.

在SQL Server数据库中，权限分为对象权限和语句权限两种.

(1)对象权限

对象权限就是用户在已经创建的对象上行使的权限.具体权限包括:SELECT,

UPDATE,DELETE,INSERT,EXECUTE,ALTER,REFERENCES等。

通过SQL命令赋予对象权限

以teacher4用户身份登录到SQL Server Management Studio,执行以下代码会提示没有权限;

/*使用UPDATE更改学生表格数据信息*/

UPDATE student

SET age=30

WHERE

ID=2006010

以Windows管理员或sa用户登录到Management Studio管理界面;

在查询界面输入如下代码：
/*使用GRANT命令赋予teacher4修改表格student的权限*/

USE school

GRANT UPDATE ON dbo.student

TO teacher4

再以teacher4用户身份登录到SQL Server Management Studio，此时就有了修改权限。

语句权限

除了提供对象的操作权限外，还提供了创建对象的权限。创建数据库或者数据库中的对象所涉及的活动同样需要一定的权限。例如，用户需要在数据库中创建表、视图，那么就需要赋予创建这些对象的权限。包括：CREATE TABLE,CREATE VIEW,CREATE RULE,CREATE PROCEDURE,CREATE DEFAULT,CREATE DATABASE,BACKUP DATABASE,BACKUP LOG.

在赋予用户权限之前，首先要指定用户使用架构，在创建时如果没有，则系统通常提供为默认架构dbo。

通过SQL命令赋予语句权限

以Windows管理员或sa用户登录到Management Studio管理界面;

在查询界面输入如下代码并执行：

/*使用CREATE LOGIN与CREATE USER命令创建新的登录与数据库用户*/

CREATE LOGIN teacher5

WITH

PASSWORD=’teacher5’,

DEFAULT_DATABASE=school

USE school

CREATE USER teacher5

FOR LOGIN teacher5

WITH DEFAULT_SCHEMA=students

在查询界面输入如下代码：

/*使用GRANT命令赋予数据库school中的teacher5用户CREATE TABLE的权限*/

GRANT CREATE TABLE TO teacher5

通过以上操作，登录名为“teacher5”的帐户，就能够在数据库school中创建在students架构下的表。

删除权限

删除权限在图形化界面下的操作与赋予权限的操作基本上是一致的。

通过命令行语句删除权限如下:

以Windows管理员或sa用户登录到Management Studio管理界面;

在查询界面输入如下代码并执行：

USE school

REVOKE SELECT ON dbo.student

FROM teacher4

角色

在SQL Server安全体系中，还提供了一种强大的工具角色.角色就是权限的集合,类似于Windows操作系统安全体系中组的概念.在实际工作中,有大量用户的权限是一样的,如果让数据库管理员在每次创建完帐户以后再赋予权限,是一件非常繁重的体力劳动.而如果把权限相同的用户,集中在一个对象中管理,则要方便得多。

角色正好提供了这样的功能,对一个角色授予、撤消权限将适应于角色中所有成员。可以建立一个角色，来代表一类用户所要执行的工作，然后将授予适当的权限。当工作人员开始工作时，只须将他们添加到该角色成员，当离开时，从该角色中删除即可。

(1)系统角色、角色级别

在SQL Server2005安全体系中，预先提供了一些系统角色。角色根据SQL Server的安全体系，提供了服务器级别的角色和数据库级别的角色。

a.固定服务器角色

固定服务器角色在其作用域内属于服务器范围。固定服务器角色的每个成员，都可以向其所属角色添加其他登录名。

固定服务器角色有：bulkadmin,dbcreator,diskadmin,processadmin,securityadmin,

serveradmin,setupadmin,sysadmin.

b.固定数据库角色

固定数据库角色是在数据库级别定义的，并且存在于每个数据库中。

固定数据库角色包括：

db_accessadmin, db_backupoperator,

db_datareader, db_datawriter,