《SQL Server 2010从入门到精通》–20180629

by admin on 2019年10月15日

约束

目录

XML查询技术

XML文档以一个纯文本的形式存在,主要用于数据存储。不但方便用户读取和使用,而且使修改和维护变得更容易。

目录

目录

主关键字约束(Primary Key Constraint)

用来指定表中的一列或几列组合的值在表中具有唯一性。建立主键的目的是让外键来引用。

  • 1.触发器
    • 1.1.DDL触发器
    • 1.2.DML触发器
    • 1.3.创建触发器
      • 1.3.1.创建DML触发器
      • 1.3.2.创建DDL触发器
      • 1.3.3.嵌套触发器
      • 1.3.4.递归触发器
    • 1.4.管理触发器

XML数据类型

XML是SQL
Server中内置的数据类型,可用于SQL语句或者作为存储过程的参数。用户可以直接在数据库中存储、查询和管理XML文件。XML数据类型还能保存整个XML文档。XML数据类型和其他数据类型不存在根本上的差别,可以把它用在任何普通SQL数据类型可以使用的地方。
示例1:创建一个XML变量并用XML填充

DECLARE @doc XML
SELECT @doc='<Team name="Braves" />';

示例2:创建XML数据类型列

CREATE TABLE t1(
column1 INT,
column2 XML,
CONSTRAINT pk_column1 PRIMARY KEY(column1));

在上面的示例中,column2列是XML数据类型列。
示例3:不能将XML数据类型列设置为主键或外键

CREATE TABLE t1(
column1 INT,
column2 XML,
CONSTRAINT pk_column1 PRIMARY KEY(column2));

执行上面的代码,报错如下:
消息1919,级别16,状态1,第1 行
表’t1′ 中的列’column2′ 的类型不能用作索引中的键列。
消息1750,级别16,状态0,第1 行
无法创建约束。请参阅前面的错误消息。
XML数据类型的使用限制
只有STRING数据类型才能转换成XML。
XML列不能应用于GROUP BY语句中
XML数据类型存储的数据不能超过2GB
XML数据类型字段不能被设置成主键或者外键或称为其一部分。
Sql_variant数据类型字段的使用不能把XML数据类型作为种子类型。
XML列不能指定为唯一的。
COLLATE子句不能被使用在XML列上。
存储在数据库中的XML仅支持128级的层次。
表中最对只能拥有32个XML列。
XML列不能加入到规则中。
唯一可应用于XML列的内置标量函数是ISNULL和COALESCE。
具有XML数据类型列的表不能有一个超过15列的主键。

  • 1.使用Transact-SQL语言编程
    • 1.1.数据定义语言DDL
    • 1.2.数据操纵语言DML
    • 1.3.数据控制语言DCL
    • 1.4.Transact-SQL语言基础
  • 2.运算符
    • 2.1.算数运算符
    • 2.2.赋值运算符
    • 2.3.位运算符
    • 2.4.比较运算符
    • 2.5.逻辑运算符
    • 2.6.连接运算符
    • 2.7.一元运算符
    • 2.8.运算符的优先级
  • 3.控制语句
    • 3.1.BEGIN
      END语句块
    • 3.2.IF
      ELSE语句块
    • 3.3.CASE分支语句
    • 3.4.WHILE语句
    • 3.5.WAITFOR延迟语句
    • 3.6.RETURN无条件退出语句
    • 3.7.GOTO跳转语句
    • 3.8.TRY
      CATCH错误处理语句
  • 4.常用函数
    • 4.1.数据类型转换函数
  • 1.架构
    • 1.1.创建架构并在架构中创建表
    • 1.2.删除架构
    • 1.3.修改表的架构
  • 2.视图
    • 2.1.新建视图
    • 2.2.使用视图修改数据
    • 2.3.删除视图
  • 3.索引
    • 3.1.聚集索引
    • 3.2.非聚集索引
    • 3.3.创建索引
    • 3.4.修改索引
    • 3.5.查看索引
    • 3.6.查看索引碎片
    • 3.7.查看统计信息
Primary Key的创建方式

在创建表时创建Primary Key

CREATE TABLE table1(
    t_id VARCHAR(12) ,
    t_name VARCHAR(20),
    t_phone VARCHAR(20),
    CONSTRAINT t_idss PRIMARY KEY(t_id)
);

对t_id列创建主键,约束名为t_idss。

1.触发器

触发器是一种特殊的存储过程,与表紧密关联。

类型化的XML和非类型化的XML

可以创建xml类型的变量,参数和列,或者将XML架构集合和xml类型的变量、参数或列关联,这种情况下,xml数据类型实例称之为类型化xml实例。否则XML实例称为非类型化的实例。

1.使用Transact-SQL语言编程

尽管SQL Server
2008提供了图形化界面,但只有一种Transact-SQL语言能够直接与数据库引擎进行交互。根据执行功能特点可以将Transact-SQL语言分成3大类:数据定义语言DDL,数据操纵语言DML,数据控制语言DCL。

1.架构

架构是一种独立于用户的逻辑分组,组中可以存储表,视图,存储过程等。假如表1在架构1中,表2在架构2中,用架构1的用户名登录时表2不可见。且未添加该架构的数据库不能被该架构的用户访问。

删除Primary Key
ALTER TABLE table1
DROP CONSTRAINT t_idss;

约束名与列名不一致,此处填写约束名

1.1.DDL触发器

当服务器或数据库中发生数据定义语言(DDL)事件时将被调用。如CREATE,ALTERDROP等操作。如果要执行以下操作,可以使用DDL触发器:

  • 防止对数据库架构进行更改
  • 希望数据库中发生某些情况以响应数据库架构中的更改
  • 要记录数据库架构中的更改或事件

XML数据类型方法

XML数据类型共有5种方法
query():执行一个XML查询并返回查询结果(返回一个XML数据类型)。
示例4

DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
SET @xmlDoc='<students>
    <class name="数学" NO="8501">
        <student>
            <name>李林</name>
            <sex>男</sex>
            <age>16</age>
            <address>江苏</address>
        </student>
    </class>
</students>'--将XML实例分配给变量@xmlDoc
SELECT @xmlDoc.query('/students/class/student') AS test
--用query()查询@xmlDoc变量实例中标签<student>的子元素

查询结果如图所示
图片 1
点击查询结果
图片 2
如想查询标签

DECLARE @addr XML--声明一个XML类型变量@addr
SET @addr='/students/class/student'
SELECT @addr.exist('/students/class="江苏"') AS 返回值

结果如图所示
图片 3

注:exsit()方法的参数不必做精确定位

Value():计算一个查询并从XML中返回一个简单的值(只能返回单个值,且该值为非XML数据类型)。
Value()方法有2个参数XQuery和SQLType,XQuery参数表示命令要从XML实例内部查询数据的具体位置,SQLType参数表示value()方法返回的值的首选数据类型。
示例6

DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
DECLARE @classID INT--声明INT类型的变量@classID
SET @xmlDoc='<students>
    <class name="数学" NO="8501">
        <student>
            <name>李林</name>
            <sex>男</sex>
            <age>16</age>
            <address>江苏</address>
        </student>
    </class>
</students>'--将XML实例分配给变量@xmlDoc
SET @classID=@xmlDoc.value('(/students/class/@NO)[1]','INT')
--将value()方法返回值赋值给变量@classID
SELECT @classID AS classID

查询结果如图所示
图片 4

注:SQLType不能是XML数据类型,公共语言运行时(CLR)用户定义类型,image,text,ntext或sql_variant数据类型,但可以是用户自定义数据类型SQL。

Modify():在XML文档的适当位置执行一个修改操作。它的参数XML_DML代表一串字符串,根据此字符串表达式来更新XML文档的内容。
示例7:在@xmlDoc的实例中,元素

DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
SET @xmlDoc='<students>
    <class name="数学" NO="8501">
        <student>
            <name>李林</name>
            <sex>男</sex>
            <age>16</age>
            <address>江苏</address>
        </student>
    </class>
</students>'
SELECT @xmlDoc AS '插入节点前信息'
SET @xmlDoc.modify('insert <学历>本科</学历> after (students/class/student/age)[1]')
SELECT @xmlDoc AS '插入节点后信息'

查询结果插入节点后信息如图所示
图片 5

注:modify()方法的参数中insert和其他关键字必须小写,否则会报错

Nodes():允许把XML分解到一个表结构中。此方法将XML数据类型实例拆分为关系数据,并返回包含原始XML数据的行集。
示例8:依然用@locat参数的实例来示范

DECLARE @locat XML--声明XML变量@locat
SET @locat=
'<root>
    <location locationID="8">
        <step>8的步骤</step>
        <step>8的步骤</step>
        <step>8的步骤</step>
    </location>
    <location locationID="9">
        <step>9的步骤</step>
        <step>9的步骤</step>
        <step>9的步骤</step>
    </location>
    <location locationID="10">
        <step>10的步骤</step>
        <step>10的步骤</step>
        <step>10的步骤</step>
    </location>
    <location locationID="11">
        <step>11的步骤</step>
        <step>11的步骤</step>
        <step>11的步骤</step>
    </location>
</root>'--@locat变量的实例

SELECT T.Loc.query('.') AS result
FROM @locat.nodes('/root/location') T(Loc)
GO

查询结果如下图所示
图片 6

1.1.数据定义语言DDL

是最基础的Transact-SQL语言类型,用来创建数据库和创建,修改,删除数据库中的各种对象,为其他语言的操作提供对象。例如数据库,表,触发器,存储过程,视图,函数,索引,类型及用户等都是数据库中的对象。常见的DDL语句包括

CREATE TABLE--创建表
DROP TABLE--删除表
ALTER TABLE--修改表

1.1.创建架构并在架构中创建表

执行如下语句

CREATE LOGIN hy WITH PASSWORD = '123456'
GO
--新建登录名
CREATE DATABASE schematest
GO
--新建数据库
USE schematest
GO
CREATE USER u_for_test FOR LOGIN hy
GO
CREATE SCHEMA dbo_Schema
go
--在schematest数据库下添加dbo_Schema
CREATE TABLE T1(id INT,NAME VARCHAR(20))
go

CREATE TABLE dbo_Schema.T2(Nid int,DD datetime)
go

GRANT SELECT ON SCHEMA :: dbo_Schema TO u_for_test;
--给u_for_test赋予SELECT权限
--重新使用hy登录即可。

用hy登录,打开未添加dbo_Schema架构的数据库,出现如下提示
图片 7
打开schematest数据库,展开表,dbo_Schema下的T2表可见,非dbo_Schema架构下的T1表不可见。
图片 8

向已有表中添加Primary Key
ALTER TABLE table1
ADD CONSTRAINT t_idss
PRIMARY KEY(t_id);

1.2.DML触发器

当数据库服务器中发生数据操作语言(DML)事件时将被调用。如INSERT,DELETE,UPDATE等操作。将DML触发器和触发语句作为可在触发器内回滚的单个事务对待,如果检测到错误,则整个事务回滚。DML触发器在一下方面非常有用:

  • 可实现数据库相关表之间的级联更改
  • 可以防止恶意或错误的DML语句事件,并强制执行比CHECK约束更为复杂的其他限制
  • 可以评估数据修改前后表的状态,并根据该差异采取措施

一个表中的多个同类DML触发器,允许用多个不同的操作来响应同一个修改语句
SQL Server
2008
为每个触发器创建了2个特殊的表:INSERTED表和DELETED表。这是两个逻辑表,由系统来创建和维护,用户不能对他们进行修改。它们存放在内存中,而不是在数据库中,并且结构与被DML触发器作用的表的结构相同。
INSERTED表中存放了由执行INSERTUPDATE语句而插入的所有行,在执行INSERTUPDATE语句时,新的行将同时被插入到触发器作用的表和INSERTED表中。INSERTED表中的行是触发器作用的表中行的副本。
DELETED表中存放了由执行DELETEUPDATE语句而删除的所有行,在执行DELETEUPDATE语句时,被删除的行将由触发器作用的表中被移动到DELETED表,两个表中不会有重复行。

XQuery简介

XQuery是一种查询语言,可以查询结构化或者半结构化的数据。SQL Server
2008中对XML数据类型提供了支持,可以存储XML文档,然后使用XQuery语言进行查询。

1.2.数据操纵语言DML

是用于操纵表和视图中的数据的语句,例如查询数据(SELECT),插入数据(INSERT),更新数据(UPDATE)和删除数据(DELETE)等。

1.2.删除架构

删除架构前必须删除或者移动该架构的所有对象,不然删除操作将会失败。如执行下列语句

DROP SCHEMA dbo_Schema
GO

结果如图所示
图片 9
此时要将T2表删除或者移动到其他架构才能成功删除dbo_Schema

添加Primary Key的另一种示例
ALTER TABLE Products
ADD PRIMARY KEY(prod_id);

虽然上述代码运行没问题,查看表格设计也可以看到Primary
Key设置成功,但是在删除Primary Key操作时会提示:
消息3728,级别16,状态1,第1 行
‘prod_id’ 不是约束。
消息3727,级别16,状态0,第1 行
未能删除约束。请参阅前面的错误信息。
原因是添加Primary
Key语句中没有用CONSTRAINT指明约束名,系统自动生成了主键名和约束名,要先查看主键名和约束名,删除时填写的也是约束名。
这种情况的正确删除方法

ALTER TABLE Products
DROP CONSTRAINT CK__Products__prod_p__1A14E395;
ALTER TABLE Products
DROP CONSTRAINT PK__Products__56958AB222AA2996;

1.3.创建触发器

FOR XML子句

通过在SELECT语句中使用FOR
XML子句可以把数据库表中的数据检索出来并生成XML格式。SQL Server
2008支持FOR
XML的四种模式,分别是RAW模式,AUTO模式,EXPLICIT模式和PATH模式。

1.3.数据控制语言DCL

涉及到权限管理的语言称为数据控制语言,主要用于执行有关安全管理的操作。如授予权限(GRANT),收回权限(REVOKE),拒绝授予主体权限,并防止主体通过组或角色成员继承权限(DENY

1.3.修改表的架构

如图所示,右键表名——设计——右侧属性栏中修改表的架构
图片 10
如图所示,当把T2表所引用的架构修改为dbo后,可继续删除架构dbo_Schema操作。就能成功删除dbo.Schema
图片 11

多列组合添加主键约束
CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT ts_id PRIMARY KEY(t_id,s_id)
);

1.3.1.创建DML触发器

FOR XML RAW

将表转换成元素名称是row,属性名称为列名或者列的别名。
示例9:将Student表转换为XML格式(FOR XML RAW)
Student表的数据如图所示
图片 12
执行语句:

SELECT * FROM Student FOR XML RAW;

查询结果如图所示
图片 13
图片 14

1.4.Transact-SQL语言基础

2.视图

视图是数据库中原始数据的一种变换,是查看表数据的一种方式,视图是一种逻辑对象,是虚拟的表,是一串SELECT语句,并不是真实的表。

外关键字约束(Foreign Key Constraint)

定义了表之间的关系,用来维护两个表之间的一致性的关系。
在创建表时创建Foreign Key Constraint

CREATE TABLE table2(
    s_id VARCHAR(20),
    s_name VARCHAR(12),
    s_tellphone VARCHAR(11),
    s_address VARCHAR(20),
    CONSTRAINT PK_s_id PRIMARY KEY(s_id),
);--首先新建table2,设置s_id为主键

CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT pk_ts_id PRIMARY KEY(t_id,s_id),--新建table1,对t_id和s_id设置联合主键,键名pk_ts_id
    CONSTRAINT fk_s_id FOREIGN KEY(s_id)--对s_id设置外键fk_s_id
    REFERENCES table2(s_id)--外键fk_s_id外键关联table2的列s_id
    ON DELETE CASCADE--设置在table1的s_id删除时table2的s_id同时删除
    ON UPDATE CASCADE--设置在table1的s_id更新时table2的s_id同时更新
);

注:对table1设置外键关联table2,在插入数据时需要先插入table2的数据,才能成功插入table1的数据。更改table2.s_id数据,table1.s_id数据也会自动改变。但是更改table1.s_id数据,执行时报外键冲突。总之对table1设置外键关联table2后,table1的数据跟着table2走,不能反着来。

添加和删除外键约束同主键。

1.3.1.1.INSERT触发器

示例1:创建一个触发器Automatic_division,当在Student表中插入一条学生信息时,触发器根据入学分数(stu_enter_score)对学生进行自动分班,并在class_student表中插入一条记录。
分班要求:
|Stu_enter_score |Class_id |Class_name|
|——————-|——————|————–|
|stu_enter_score>=700| 01| 创新A班|
|650<=Stu_enter_score<700| 02| 重点B班|
|600<=Stu_enter_score<650| 03| 提高C班|
|550<=Stu_enter_score<600| 04| 普通D班|
|500<=Stu_enter_score<550| 05| 普通E班|
|Stu_enter_score<500| 06| 普通F班|
执行下列语句

CREATE TRIGGER automatic_division
ON student--新建一个检测student表的触发器,命名automatic_division
FOR INSERT--检测到INSERT操作时触发器工作
AS
DECLARE @score INT,@stu_no VARCHAR(8),@class_id CHAR(2)
--声明三个变量
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY--声明一个指向inserted表的局部游标stu_cursor
FOR SELECT stu_no,stu_enter_score FROM inserted
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标指向inserted表的第一个数据并把游标指向的stu_no和stu_enter_score值分别赋值给@stu_no和@score
WHILE @@FETCH_STATUS=0--开始循环
BEGIN
BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
--判断结束
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--将数据插入到class_student表中
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
END--循环结束
CLOSE stu_cursor--关闭游标
DEALLOCATE stu_cursor--释放游标资源
GO

验证代码是否正确
student表中插入数据,并查看class_student表中的数据是否正确

INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
VALUES('20180001','邹莉莉','女','389'),
('20180002','万兴','男','701'),
('20180003','孙伟','男','652'),
('20180004','温佳静','女','676'),
('20180005','姜立夫','男','542')

Class_student表中的数据如图所示
图片 15
游标示例2:对student表中还未分班的学生进行分班
Student表中的数据如图所示
图片 16
其中stu_no20180001~20180005的学生已经在示例1中分班,剩下的学生全都未分班。
执行下列语句

ALTER TABLE student
ADD stu_division_state bit--为student表新建一列记录是否已分班,true表示已分班
GO
DECLARE stu_class_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM class_student
--新建游标stu_class_cursor指向class_student表的所有数据
OPEN stu_class_cursor--打开游标
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_class_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE student
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_class_cursor INTO @stu_no
END
CLOSE stu_class_cursor--关闭游标
DEALLOCATE stu_class_cursor--释放游标资源
---所有学生是否分班已经全部记录在stu_division_state中
GO
DECLARE @stu_no VARCHAR(8),@score INT,@class_id CHAR(2)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no,stu_enter_score FROM student WHERE stu_division_state IS NULL
--新建student表的游标stu_cursor,指向所有未分班学生
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor INTO @stu_no,@score
WHILE @@FETCH_STATUS=0--循环开始
BEGIN
BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--将数据插入到class_student表中
UPDATE student--将student表的stu_division_state改成已分班
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
END--循环结束
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

结果如图所示
Student表的数据
图片 17
Class_student表的数据
图片 18
至此Student表中所有学生都已分班
为了以后方便,可以将游标示例2中的代码稍作修改封装成一个用户自定义存储过程
存储过程示例3
修改后的代码如下

CREATE PROCEDURE student_division
AS
BEGIN
UPDATE student
SET stu_division_state=0--先将student表中所有学生的分班情况都标成未分班

DECLARE stu_class_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM class_student
--新建游标stu_class_cursor指向class_student表的所有数据
OPEN stu_class_cursor--打开游标
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_class_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE student
SET stu_division_state=1
WHERE stu_no=@stu_no--利用游标找出student表中已分班的学生并标记分班状态
FETCH NEXT FROM stu_class_cursor INTO @stu_no
END
CLOSE stu_class_cursor--关闭游标
DEALLOCATE stu_class_cursor--释放游标资源
---所有学生是否分班已经全部记录在stu_division_state中

DECLARE @score INT,@class_id CHAR(2)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no,stu_enter_score FROM student WHERE stu_division_state=0
--新建student表的游标stu_cursor,指向所有未分班学生
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor INTO @stu_no,@score
WHILE @@FETCH_STATUS=0--循环开始
BEGIN
BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--将数据插入到class_student表中
UPDATE student--将student表的stu_division_state改成已分班
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
END--循环结束
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO

注:和游标示例2的代码相比,示例3的代码添加了将所有学生分班状态标记为0的过程,去掉了添加stu_division_state列的过程,但对原来已有的学生的分班状态赋值这个步骤并未删去,而是进行重复校验。并且删除了两段代码中的GO和第二段用于给学生分班的代码中对@stu_no变量的重复声明。

student表插入数据并运行student_division的存储过程

注:对student表插入数据前应先禁用示例1的触发器automatic_division

执行下列语句

ALTER TABLE student DISABLE TRIGGER automatic_division
--禁用automatic_division触发器
INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score,stu_division_state)
VALUES('20180006','王洋','男','724',NULL),
('20180007','易阳','男','713',NULL),
('20180008','孙浩','男','584',NULL),
('20180009','张秋燕','女','420','False'),
('20180010','胡燕','女','527','True')

Student表的数据如图所示,红框内就是我刚刚插入还未分班的数据,其中2018000920180010这两个学生的分班状态被我误标成FalseTrue
图片 19
执行存储过程

EXEC dbo.student_division

结果如图所示
Student表的数据(分班状态都为true了)
图片 20
Class_student表的数据
图片 21

FOR XML AUTO

使用表名称作为元素名称,使用列名称作为属性名称,SELECT关键字后面列的顺序用于XML文档的层次。
示例10:将Student表转换为XML格式(FOR XML AUTO)
执行语句:

SELECT * FROM Student FOR XML AUTO;

查询结果如图所示
图片 22
图片 23

1.4.1.常量与变量

常量不多说。在SQL Server
2008中,存在两种变量。一种是系统定义和维护的全局变量,一种是用户定义用来保存中间结果的局部变量。

2.1.新建视图

示例1:利用student表和class_student表的数据新建视图class_01,记录01班学生详细信息
Student表的数据如图所示
图片 24
Class_student表的数据如图所示
图片 25
执行下列语句新建视图class_01

CREATE VIEW class_01
AS
SELECT class_student.stu_no,class_id,stu_name,stu_sex,stu_age,stu_addr,stu_native_place,stu_birthday,stu_enter_score,stu_phone,stu_father_name,stu_mather_name
FROM class_student INNER JOIN student
ON class_student.stu_no=student.stu_no
WHERE class_id='01'

视图class_01的数据如图所示
图片 26

注:视图只是一个SELECT语句,数据根据基表的数据改变而自动改变。

UNIQUE约束

除主键外另一种可以定义唯一约束的类型,允许空值。添加UNIQUE的方法同上,这里只简单举例。

USE test
GO
ALTER TABLE table2
ADD CONSTRAINT uk_s_tellphone
UNIQUE(s_tellphone);

1.3.1.2.DELETE触发器

当针对目标数据库运行DELETE语句时就会激活DELETE触发器。用户直接运行DELETE语句和使用DELETE触发器又有所不同,当激活DELETE触发器后,从受触发器影响的表中删除的行会被放置在一个特殊的临时表——DELETED表中。DELETED表还允许引用由初始化DELETE语句产生的日志数据。
DELETE触发器被激活时,需要考虑以下几点

  • 当某行被添加到DELETED表中时就不存在于数据库表,因此数据库表和DELETED表不可能存在相同行。
  • 系统自动创建DELETED表时,空间从内存中分配。DELETED表被存储在高速缓存中。
  • DELETE操作定义的触发器并不执行TRUNCATE
    TABLE
    语句,原因在于日志不记录TRUNCATE TABLE语句。

示例4:为student表定义一个DELETE触发器,当删除一条学生信息时,class_student表中该学生的分班信息也会被删除
执行下面的语句

CREATE TRIGGER delete_student
ON student
FOR DELETE
AS
DECLARE @stu_no VARCHAR(8)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM deleted
OPEN stu_cursor
FETCH NEXT FROM stu_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM class_student
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

测试delete_student触发器的正确性
Student表的数据如图所示
图片 27
Class_student表的数据如图所示
图片 28
执行下列语句

DELETE FROM student
WHERE stu_enter_score<=351
--在student表中删除入学成绩小于分的学生

student表来看,只有入学编号为2018001120180012的学生成绩被删除。该操作激活了delete_student触发器
Class_student表的数据如图所示
图片 29
入学编号为2018001120180012的学生分班信息已经从class_student表中自动删除。

FOR XML EXPLICIT

允许用户显式地定义XML树的形状,不受AUTO模式中的种种限制。不能将FOR XML
EXPLICIT直接用在SELECT子句中。
示例11:将xmlTest表转换为XML格式(FOR XML EXPLICIT)
XmlTest表的数据如图所示
图片 30

SELECT DISTINCT 1 AS TAG,--指定顶级层级序号1
NULL AS PARENT,--该层级没有父级
NULL AS '班级信息!1!',
NULL AS '班级信息!2!班级',
NULL AS '班级信息!2!班级类型',
NULL AS '班级信息!2!班主任',
NULL AS '学生信息!3!学号!Element',
NULL AS '学生信息!3!学生姓名!Element',
NULL AS '学生信息!3!性别!Element',
NULL AS '学生信息!3!总分!Element'--设置所有层级元素和属性命名,暂时不对这些元素赋值
--例如在“学生信息!3!总分!Element”格式中,学生信息是元素名,3表示该元素所处层级,总分表示属性名
--Element指出生成以属性单独为一行的XML格式
UNION ALL--层级之间用UNION ALL相连
SELECT DISTINCT 2 AS TAG,--指定二级层级序号2
1 AS PARENT,--父级序号是序号为1的层级
NULL,--在层级的代码中已列出了所有层级元素和属性命名,因此这里给元素和属性做赋值。这句语句对应层级代码中“NULL AS '班级信息!1!'”,说明我希望该元素作为独立成行的标签,没有赋值。
班级,--对层级中的“NULL AS '班级信息!2!班级'”赋值,将xmlTest表中的班级赋值给属性班级
班级类型,--对层级中的“NULL AS '班级信息!2!班级类型'”赋值,将xmlTest表中的班级赋值给属性班级类型
班主任,--同上
NULL,--这句语句开始对应的是层级的属性,因此在层级的代码中不做赋值,在下面层级的代码中做赋值
NULL,
NULL,
NULL
FROM xmlTest--指出上面赋值的数据源来自于xmlTest表
UNION ALL--各个层级之间用UNION ALL连接
SELECT 3 AS TAG,--指定3级层级序号3
2 AS PARENT,--父级是序号为2的层级
NULL,--对应层级的”NULL AS '班级信息!1!'“语句,不希望它有值,所以不做赋值
NULL,--这三个NULL对应层级的各个属性,在层级的代码中已经做过赋值,因此在这里不做赋值
NULL,
NULL,
学号,--对应层级1代码中的层级3属性,在层级代码3中进行赋值
学生姓名,
性别,
年级总分
FROM xmlTest
FOR XML EXPLICIT;--将上述查询转换为XML,不能漏掉,否则结果会以表格形式显示

查询结果如图所示
图片 31
图片 32
在结果图中我们发现,红框中3个班级信息列在一起,而所有学生都列在高一3班下,这不是我们想要的结果,我们希望每个班级对应自己的学生。那么如何解决此类问题呢,这涉及到排序。

注:如果层级中有多个数据完全重复,可以在该层级对应的代码前加DISTINCT关键字去除重复元素。

首先删除代码行末的FOR XML
EXPLICIT语句,仅仅执行剩下的部分,使结果以表格形式呈现,那么结果如下
图片 33
这个表格每行的顺序也代表了该表格转化为XML文档后内容显示顺序。图中层级2(TAG=2)的几行,位置都在一起,这也就是为什么层级3的所有数据都在高一3班下面了。我们需要对表格每行的顺序进行调整,使学生所在行按照xmlTest表中的数据逻辑分散在班级行之下。但是根据上面的表格发现,不管按照什么字段排序,都不可能达到效果。
正确代码如下

SELECT DISTINCT 1 AS TAG,
NULL AS PARENT,
NULL AS '班级信息!1!',
NULL AS '班级信息!2!班级',
NULL AS '班级信息!2!班级类型',
NULL AS '班级信息!2!班主任',
NULL AS '学生信息!3!学号!Element',
NULL AS '学生信息!3!学生姓名!Element',
NULL AS '学生信息!3!性别!Element',
NULL AS '学生信息!3!总分!Element'
UNION ALL
SELECT DISTINCT 2 AS TAG,
1 AS PARENT,
NULL,
班级,
班级类型,
班主任,
NULL,
NULL,
NULL,
NULL
FROM xmlTest
UNION ALL
SELECT 3 AS TAG,
2 AS PARENT,
NULL,
班级,
班级类型,
班主任,
学号,
学生姓名,
性别,
年级总分
FROM xmlTest
ORDER BY [班级信息!2!班级],[学生信息!3!学号!Element]
FOR XML EXPLICIT;

对比第一次代码,我们发现上面的代码不止在行末对数据按元素属性进行了排序,还在赋值的代码中有所改动。在层级1代码中完全没有改动,因为层级1的代码作用是设置XML格式的,对数据排序没有影响。在下面几个层级的赋值部分,每个层级的代码中都对上面几个层级的元素重复赋值,这样做使结果的表格中不再有那么多属性值是NULL,可以方便排序。最后再按照元素[班级信息!2!班级]和[学生信息!3!学号!Element]排序。让我们看看结果如何。
运行上面的代码,但不运行FOR XML
EXPLICIT语句,看看表格中数据内容和行顺序是否改变
图片 34
如图所示,发现行数据和学生数据的顺序显示正确。运行所有代码得到XML文档,结果如图所示
图片 35
由于XML文档内容过长,不贴图了,直接复制所有XML内容展示一下。

<班级信息>
  <班级信息 班级="高一1班" 班级类型="创新班" 班主任="李玉虎">
    <学生信息>
      <学号>20180101</学号>
      <学生姓名>李华</学生姓名>
      <性别>男</性别>
      <总分>5.680000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180103</学号>
      <学生姓名>孙丽</学生姓名>
      <性别>女</性别>
      <总分>3.390000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180108</学号>
      <学生姓名>吴伟</学生姓名>
      <性别>男</性别>
      <总分>5.280000000000000e+002</总分>
    </学生信息>
  </班级信息>
  <班级信息 班级="高一2班" 班级类型="重点班" 班主任="姜杰">
    <学生信息>
      <学号>20180102</学号>
      <学生姓名>张三</学生姓名>
      <性别>男</性别>
      <总分>6.270000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180104</学号>
      <学生姓名>袁康</学生姓名>
      <性别>男</性别>
      <总分>4.820000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180106</学号>
      <学生姓名>赵四</学生姓名>
      <性别>男</性别>
      <总分>5.680000000000000e+002</总分>
    </学生信息>
  </班级信息>
  <班级信息 班级="高一3班" 班级类型="提高班" 班主任="师从光">
    <学生信息>
      <学号>20180105</学号>
      <学生姓名>王婷</学生姓名>
      <性别>女</性别>
      <总分>7.610000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180107</学号>
      <学生姓名>周其</学生姓名>
      <性别>女</性别>
      <总分>3.480000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180109</学号>
      <学生姓名>甄诚</学生姓名>
      <性别>女</性别>
      <总分>7.020000000000000e+002</总分>
    </学生信息>
  </班级信息>
</班级信息>

将上面的结果对比一下原始xmlTest表,看看每个班级和它下属学生的层级关系是否有误。

注:写FOR XML
EXPLICIT代码要注意,层级1的代码中先设置层级结构,不要先急着赋值。在下属层级的代码中对层级1中的代码进行赋值,最好重复赋值,不然就会出现文中的排序问题。如果某个层级出现重复数据,在该层级的代码前加DISTINCT关键字。解决排序问题最好的办法是对各个层级的属性重复赋值并在末尾用ORDER
BY按层级属性排序。

仔细观察上面的XML文档,发现总分属性的值是个float类型,要把它转换成int,只需要把层级3中对总分的赋值代码改成CAST(年级总分
AS int)
图片 36

1.4.1.1.系统全局变量

系统全局变量分为两大类,一类是与当然SQL
Server连接或与当前处理有关的全局变量,如@@Rowcount表示最近一个语句影响的行数。@@error表示保存最近执行操作的错误状态。一类是与整个SQL
Server系统有关的全局变量,如@@Version表示当前SQL Server的版本信息。

SELECT @@VERSION AS 当前版本;--查看当前SQL Server的版本信息

结果如图所示
图片 37

2.2.使用视图修改数据

示例2:有course表数据,基于course表新建视图coursetest,列名为course_id,course_name,credits。
Course表数据如图所示
图片 38
执行下列语句新建coursetest视图

CREATE VIEW coursetest
AS
SELECT course.course_id,course_name,credits FROM course

Coursetest视图数据如图所示
图片 39
在coursetest视图中插入一行course_id为“0013”的数据

INSERT INTO coursetest(course_id,course_name,credits)
VALUES('0013','嵌入式系统开发','5')

Course表数据如图所示
图片 40
这行数据也被插入到course表中,在基于单张表的视图中可以通过增删改视图数据来更新基表数据,对基于多张表的视图不可更新。

CHECK约束

分为表约束和列约束,用于限制字段值在某个范围。

1.3.1.3.UPDATE触发器

当针对目标数据库运行UPDATE语句时就会激活UPDATE触发器。对UPDATE触发器来说,临时表INSERTEDDELETED依然有用。UPDATE触发器被激活时,原始行被移入DELETED表中,更新行被移入到INSERTED表中。触发器检查DELETED表和INSERTED表以及被更新的表,来确定是否更新了多行和如何执行触发器动作。
Student表的数据如图所示
图片 41
Class_student表的数据如图所示
图片 42
示例5:当student表中的stu_no字段更新时,同步更新class_student表中的stu_no字段
执行下列语句新建触发器update_stu_no_single

CREATE TRIGGER update_stu_no_single
ON student
FOR UPDATE
AS
IF UPDATE(stu_no)
BEGIN
UPDATE class_student
SET stu_no=(SELECT stu_no FROM inserted)
WHERE stu_no=(SELECT stu_no FROM deleted)
END
GO

验证update_stu_no_single触发器是否正确,在Student表中执行下列语句,将student表中stu_no为“20180101”的学生的stu_no改成00000000

UPDATE student
SET stu_no='00000000'
WHERE stu_no='20180101'

执行成功后,update_stu_no_single触发器被激活,class_student表的数据如图所示
图片 43

注:update_stu_no_single触发器只能对单行记录的UPDATE操作起效,如果批量UPDATE
stu_no
,执行语句时会提示子查询返回的值不止1个。下面的示例6将提供批量UPDATE
stu_no
的触发器

示例6:实现当student表的stu_no字段批量更新时,class_student表的stu_no也同步批量更新
首先将student表和class_student表的数据修改成原来的样子,并且删除update_stu_no_single触发器
Student表的数据如图所示
图片 44
Class_student表的数据如图所示
图片 45
执行下列语句新建触发器update_stu_no_batch

CREATE TRIGGER update_stu_no_batch
ON student
FOR UPDATE
AS
DECLARE @stu_no_insert VARCHAR(8),@stu_no_delete VARCHAR(8)
DECLARE stu_cursor_insert CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM inserted
OPEN stu_cursor_insert
DECLARE stu_cursor_delete CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM deleted
OPEN stu_cursor_delete
FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE class_student
SET stu_no=@stu_no_insert
WHERE stu_no=@stu_no_delete
FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
END
CLOSE stu_cursor_insert
CLOSE stu_cursor_delete
DEALLOCATE stu_cursor_insert
DEALLOCATE stu_cursor_delete
GO

验证update_stu_no_batch触发器的准确性,对student表执行下列语句,实现批量修改操作

UPDATE student
SET stu_no='00000000'
WHERE stu_no LIKE '201801%'
GO

Student表的数据如图所示
图片 46
Class_student表的数据如图所示
图片 47
我们再来验证update_stu_no_batch触发器对更新单行stu_no数据是否有效。将student表class_student表的数据改回原来的样子,然后执行下列语句

UPDATE student
SET stu_no='00000000'
WHERE stu_no='20180101'

Class_student表的数据如图所示
图片 48

注:在将表数据改成原来的样子时,直接在编辑前200行中操作或者用T-SQL语句操作,对student表数据操作,不成功的话要考虑受键和约束的影响,对class_student表数据操作,不成功的话要考虑受触发器影响。

FOR XML PATH

PATH模式提供了一种较简单的方法来混合元素及属性。在PATH模式中,列名或列别名被作为XPATH表达式来处理,这些表达式指定了如何将值映射到XML中。默认情况下,PATH模式为每一样自动生成

1.4.1.2.局部变量

局部变量能够拥有特定数据类型,有一定的作用域,一般用于充当计数器计算或控制循环执行次数,或者用于保存数据值。局部变量前只有1个@符,用DECLARE语句声明局部变量。

USE test
DECLARE @StudentId varchar(20)
SET @StudentId=(
SELECT Student.stu_no
FROM Student
WHERE stu_enter_score='603')
SELECT @StudentId AS 入学分数为603的学生学号
GO

结果如图所示
图片 49

2.3.删除视图

DROP VIEW coursetest
添加CHECK约束
ALTER TABLE table2
ADD sex CHAR(2);--在table表中添加sex,数据类型为CHAR,用来存放性别
GO
ALTER TABLE table2
ADD CONSTRAINT ck_sex CHECK(sex in('男','女'));

注:此时sex列数据类型不能是bit,如果填写bit,只能存储0和1,用CHECK约束限制结果为男和女就会报错。

1.3.1.4.INSTEAD OF触发器

INSTEAD
OF
触发器可以指定执行触发器,而不是执行触发SQL语句,从而屏蔽原来的SQL语句,而转向执行触发器内部的语句。每个表或者视图只能有1个INSTEAD
OF
触发器。INSTEAD
OF
触发器的特点是,能够使作为触发条件的SQL语句不执行。
Membership表的数据如图所示
图片 50
Call_slip表的数据如图所示
图片 51
示例7:对LibraryManagement数据库里的membership表写一个防删除触发器,尚有借书未还的读者无法被删除
执行下列语句创建member_delete_single触发器

CREATE TRIGGER member_delete_single
ON membership
INSTEAD OF DELETE
AS
BEGIN
IF NOT EXISTS(SELECT * FROM call_slip WHERE member_id=(SELECT member_id FROM deleted) AND borrow_state='未归还')
DELETE FROM membership WHERE member_id=(SELECT member_id FROM deleted)
ELSE
BEGIN
SELECT '该用户尚有图书未还,无法删除'
SELECT * FROM call_slip WHERE member_id=(SELECT member_id FROM deleted) AND borrow_state='未归还'
END
END
GO

验证触发器的正确性,执行下列语句

DELETE FROM membership
WHERE member_id='20060128'

结果如图所示
图片 52
该触发器只针对DELETE一条数据有效
示例8:对LibraryManagement数据库里的membership表写一个防批量删除触发器,尚有借书未还的读者无法被删除
Membership表的数据如图所示
图片 53
Call_slip表的数据如图所示
图片 54
执行下列语句新建触发器(将示例7中的member_delete_single触发器先删除)

CREATE TRIGGER member_delete_batch
ON membership
INSTEAD OF DELETE
AS
BEGIN
DECLARE member_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT member_id FROM deleted
OPEN member_cursor
DECLARE @member_id VARCHAR(8)
FETCH NEXT FROM member_cursor INTO @member_id
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN
IF NOT EXISTS(SELECT* FROM call_slip WHERE member_id=@member_id AND borrow_state='未归还')
DELETE FROM membership WHERE member_id=@member_id
ELSE
PRINT '用户'+@member_id+'无法删除'
END
FETCH NEXT FROM member_cursor INTO @member_id
END
CLOSE member_cursor
DEALLOCATE member_cursor
END
GO

结果如图所示
图片 55
Membership表的数据如图所示
图片 56
示例9:对LibraryManagement数据库里的call_slip表写一个防超借触发器,一个读者的未还图书最多只能有5本,超出不能再借(这里还是针对批量处理数据创建触发器)
Call_slip表的数据如图所示
图片 57
执行下列语句创建provent_overborrowing_batch触发器

CREATE TRIGGER provent_overborrowing_batch
ON call_slip
INSTEAD OF INSERT
AS
BEGIN
DECLARE @member_id VARCHAR(8)
DECLARE borrow_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT member_id FROM inserted
OPEN borrow_cursor
FETCH NEXT FROM borrow_cursor INTO @member_id
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN
IF (SELECT COUNT(*) FROM call_slip WHERE member_id=@member_id AND borrow_state='未归还')<5
INSERT INTO call_slip SELECT * FROM inserted WHERE member_id=@member_id
ELSE
PRINT '用户'+@member_id+'已借阅且未还的图书超过5本,无法再借'
END
FETCH NEXT FROM borrow_cursor INTO @member_id
END
END
GO

执行下列语句测试provent_overborrowing_batch触发器的正确性,其中member_id为“20060128”的用户借书未还超过5本,应该是无法再借的。

--测试数据
INSERT INTO call_slip(book_id,member_id,loan_period,borrow_state)
VALUES('20130002','20060128','30','未归还'),
('20130001','20060128','20','未归还'),
('20130003','20060128','30','未归还'),
('20130004','20062919','30','未归还'),
('20130005','20150821','45','未归还')

结果如图所示
图片 58
Call_slip表的数据如图所示,红框里是新插入的数据
图片 59

没有名称的列

下面介绍一种简单的FOR XML PATH应用方式

SELECT 2+3 FOR XML PATH;--将2+3的值转换成xml格式

查询结果如图所示
图片 60

注:如果提供了空字符串FOR XML PATH(‘’)则不会生成任何元素。

SELECT 2+3 FOR XML PATH('');--将2+3的值转换成xml格式并去掉<row>

查询结果如图所示
图片 61
示例12:利用xmlTest表和mainTeacher表查询出xmlTest表中成绩>=700分的学生的班主任信息和学生信息,并转化成XML格式
XmlTest表数据如下图所示
图片 62
MainTeacher表数据如下图所示
图片 63
执行下面的语句

SELECT xmlTest.学号 AS '学生信息/@学号',--@符号表示该名称为属性名,斜杠表示子层级
xmlTest.学生姓名 AS '学生信息/@姓名',
xmlTest.班级 AS '学生信息/@班级',
mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
mainTeacher.性别 AS '学生信息/班主任信息/性别',
mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
FROM xmlTest,mainTeacher
WHERE xmlTest.年级总分>=700
AND xmlTest.班主任=mainTeacher.姓名
FOR XML PATH('result');--将根目录名改为result

查询结果如下所示

<result>
  <学生信息 学号="20180105" 姓名="王婷" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>
<result>
  <学生信息 学号="20180109" 姓名="甄诚" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>

2.运算符

3.索引

在创建表的时候添加CHECK 约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12),
    CONSTRAINT ck_t3_type CHECK(t3_type in('类型1','类型2','类型3')) 
)

添加了约束后如果插入不符合约束的数据

INSERT INTO table3(
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038219',
'李建',
'社会与科学',
'任何数据'
);

消息547,级别16,状态0,第1 行
INSERT 语句与CHECK
约束”ck_t3_type”冲突。该冲突发生于数据库”test”,表”dbo.table3″, column
‘t3_type’。

语句已终止。

1.3.2.创建DDL触发器

DDL触发器只为了响应CREATEDROPALTER事件而激活,它的作用域是整个数据库或者服务器,而不是作用域某张表或试图。它可以有效控制哪位用户可以修改数据库结构以及如何修改。
示例10:创建一个DDL触发器,控制上班时间(8:00-18:00)不能对LibraryManagement数据库表和试图结构进行新建,修改和删除操作。
执行下列语句创建触发器deny_DDL_table

CREATE TRIGGER deny_DDL_table
ON DATABASE
WITH ENCRYPTION
FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE
AS
DECLARE @eventdata XML
SET @eventdata=EVENTDATA()
IF(DATEPART(HOUR,GETDATE()) BETWEEN 8 AND 17)
BEGIN
SELECT '触发器deny_DDL_table已禁止工作时间8:00-18:00对LibraryManagement数据库的CREATE,ALTER,DROP操作'
SELECT @eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') AS EventType,--事件类型
@eventdata.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(max)') AS PostTime,--时间触发的时间
@eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') AS DatabaseName,--数据库名字
@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') AS ObjectName,--操作的对象名称
@eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') AS ObjectType,--操作的对象类型
@eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS CommandText--操作命令文本
ROLLBACK---对操作进行回滚,也可以不回滚
END
GO

执行以下代码以测试DDL触发器deny_DDL_table的正确性

USE LibraryManagement
CREATE TABLE test(
t_id VARCHAR(2),
t_name VARCHAR(20)
)

结果如图所示
图片 64
图片 65

注:EVENTDATA()可在触发器内部使用,返回有关数据库和服务器事件的信息,以XML格式返回。只有直接在DDL或登录触发器内部引用EVENTDATA时,EVENTDATA才会返回数据。如果EVENTDATA由其他例程调用(即使这些例程由DDL或登录触发器进行调用),将返回
NULL

TYPE命令

SQL Server支持TYPE命令将FOR XML的查询结果作为XML数据类型返回。
示例13:依然是上面的例子,将查询结果作为XML数据类型返回。

CREATE TABLE xmlType(xml_col XML);
--首先创建一个表xmlType,只有一列xml数据类型的xml_col
INSERT INTO xmlType
SELECT(--将上面的查询语句全部复制到括号中,末尾加上TYPE,表示将XML文档作为xml数据类型,并插入到表xmlType中
SELECT xmlTest.学号 AS '学生信息/@学号',
xmlTest.学生姓名 AS '学生信息/@姓名',
xmlTest.班级 AS '学生信息/@班级',
mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
mainTeacher.性别 AS '学生信息/班主任信息/性别',
mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
FROM xmlTest,mainTeacher
WHERE xmlTest.年级总分>=700
AND xmlTest.班主任=mainTeacher.姓名
FOR XML PATH('result'),TYPE
);
SELECT * FROM xmlType;--查询xmlType表

查询结果如图所示
图片 66
双击打开查看XML

<result>
  <学生信息 学号="20180105" 姓名="王婷" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>
<result>
  <学生信息 学号="20180109" 姓名="甄诚" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>

2.1.算数运算符

在SQL Server
2008中,算数运算包括加(+)减(-)乘(*)除(/)取模(%)。举一个简单的例子。
示例1:在Student表中添加一列,列名为stu_age,根据Student表的stu_birthday列计算stu_age列并插入数据。(演示插入整列数据的方法)
Student表数据如图所示
图片 67
执行下面的语句

ALTER TABLE Student
ADD stu_age int;--在Student表中添加stu_age列
CREATE TABLE #agetemp(stu_no varchar(8),age int);--新建一个临时表
INSERT INTO #agetemp(stu_no,age)--在临时表中插入学号和计算出来的年龄
SELECT Student.stu_no,YEAR(GETDATE())-YEAR(stu_birthday)--利用函数和运算符计算年龄
FROM Student;
UPDATE Student
SET Student.stu_age=#agetemp.age--将临时表中的age列数据整个复制到Student表的stu_age列
FROM #agetemp
WHERE Student.stu_no=#agetemp.stu_no--条件是两个表的stu_no列值相等
GO
SELECT * FROM Student

结果如图所示
图片 68

3.1.聚集索引

聚集索引数据按照索引的顺序排序,查询速度比非聚集索引快。当插入数据时,按索引顺序对数据重新排序。打个比方,新华字典中按拼音查字就是聚集索引,找到了矮字就能按顺序查下去找到爱字。一个表只能有1个聚集索引
如果一个表在创建主键时没有聚集索引也没指定唯一非聚集索引,会对PRIMARY
KEY字段自动创建聚集索引

删除CHECK约束
ALTER TABLE table3
DROP CONSTRAINT ck_t3_type;

1.3.3.嵌套触发器

FOR XML的嵌套查询

示例14:在示例12的查询结果中查询班主任联系电话

SELECT (
SELECT xmlTest.学号 AS '学生信息/@学号',
xmlTest.学生姓名 AS '学生信息/@姓名',
xmlTest.班级 AS '学生信息/@班级',
mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
mainTeacher.性别 AS '学生信息/班主任信息/性别',
mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
FROM xmlTest,mainTeacher
WHERE xmlTest.年级总分>=700
AND xmlTest.班主任=mainTeacher.姓名
FOR XML PATH('result'),TYPE).query('result/学生信息/班主任信息/联系电话') AS '优秀教师联系方式';

SELECT里面依然套用了示例13中被套用的代码,外面用了query方法,查询结果如下图所示
图片 69

<联系电话>15963002120</联系电话>
<联系电话>15963002120</联系电话>

2.2.赋值运算符

即等号(=),将表达式的值赋予另一个变量。举一个简单的例子。
示例2:计算Student表中学生的平均入学成绩并打印。
Student表的数据如图所示,stu_enter_score列存放了学生的入学成绩
图片 70
执行下面的语句

DECLARE @average int--声明@average变量
SET @average=(--将计算出的平均值赋值给@average
SELECT AVG(stu_enter_score)
FROM Student)
PRINT @average--打印@average的值

结果如图所示
图片 71

3.2.非聚集索引

非聚集索引不按照索引顺序排序,制定了表中数据的逻辑顺序,采用指针指向数据页的形式。一个表可以拥有多个非聚集索引。打个比方,新华字典中按笔画查字就是非聚集索引,笔画索引顺序和字的顺序不一致,依靠指针来指向数据页。

DEFAULT约束

通过定义列的默认值或使用数据库的默认值对象绑定表列,来指定列的默认值。

1.3.3.1.嵌套触发器

如果一个触发器在执行操作时引发了另一个触发器,而这个触发器又引发了下一个触发器,那么这些触发器就是嵌套触发器。嵌套触发器在安装时就被启用,但是可以使用sp_configure存储过程禁用和重新启用嵌套。
DML触发器和DDL触发器最多可以嵌套32层,可以通过nested
triggers
来配置是否可以嵌套AFTER触发器,但是不管此设置如何都可以嵌套INSTEAD
OF
触发器。如果嵌套触发器进入了无限循环,该触发器将被终止,并且回滚整个事务。嵌套触发器具有多种用途,比如保存前一个触发器所影响的行的副本。
使用嵌套触发器时应该注意以下几点:

  • 默认情况下,嵌套触发器配置选项开启。
  • 在同一个触发器事务中,一个触发器不会被触发两次,触发器不会调用他自己来响应触发器中对同一个表的第二次更新
  • 由于触发器是一个事务,一旦嵌套中任何一层的触发器出现错误,将回滚整个事务。

示例11:有teacher_course表(教师所教课程表),course表(课程表)和course_selection表(学生选课表),写一个嵌套触发器,实现课程取消后,删除教师所教课程表中关于该课程的记录,而教师所教课程表中该课程的记录被取消,导致该课程的学生选课记录也做相应取消。
执行下列语句

--创建course表上的触发器,删除course表中的课程,teacher_course表中的记录做对应删除
CREATE TRIGGER course_delete_batch
ON course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT course_id FROM deleted
OPEN course_cursor
FETCH NEXT FROM course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM teacher_course WHERE course_id=@course_id
FETCH NEXT FROM course_cursor INTO @course_id
END
GO
--创建teacher_course表上的触发器,删除教师课程表的记录,学生选课表的记录也做对应删除
CREATE TRIGGER teacher_course_delete_batch
ON teacher_course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT course_id FROM deleted
OPEN teacher_course_cursor
FETCH NEXT FROM teacher_course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
DELETE FROM course_selection WHERE course_id=@course_id
ELSE
PRINT 'course_id为'+@course_id+'的课程依然正常开课,该课程的学生选课情况不予删除'
FETCH NEXT FROM teacher_course_cursor INTO @course_id
END
GO

course_delete_batch和**
teacher_course_delete_batch就形成了一个嵌套触发器,下面来验证嵌套触发器的正确性。 Course表中的数据如图所示
图片 72
Teacher_course表中的数据如图所示
图片 73
Course_selection**表中的数据如图所示
图片 74
以课程0013为例,执行下列语句

DELETE FROM course WHERE course_id='0013'

Course表的数据如图所示
图片 75
Teacher_course表的数据如图所示
图片 76
Course_selection表的数据如图所示
图片 77
所有关于0013课程的数据都被删除。嵌套触发器有效。

注:在触发器teacher_course_delete_batch中,我额外加入了一个判断,当teacher_course表中还有老师在教授这门课程时,所有关于这门课程的学生选课信息都不予删除。这样做在嵌套触发器里是多余的,删除一门课程,必然会删除teacher_course表中所有与这门课程有关的记录,也必然删除course_selection表中所有与这门课程有关的记录,但是,这样做可以保证该触发器能够独立于嵌套触发器被单独激活。Teacher_course_delete_batch触发器还能用于其他嵌套触发器中,看示例12

示例12:有teacher表(教师信息表),teacher_course(教师所教课程表),和course_selection表(学生选课记录表),写一个嵌套触发器,实现当一个教师离职时,在删除该教师所教课程信息,如果没有教师教这门课程,再删除该课程选课记录。
其中teacher_course表的触发器teacher_course_delete_batch已经在示例11中写完,只需创建teacher表的teacher_delete_batch触发器即可
执行下列代码

CREATE TRIGGER teacher_delete_batch
ON teacher
FOR DELETE
AS
DECLARE @teacher_id CHAR(4)
DECLARE teacher_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT teacher_id FROM deleted
OPEN teacher_cursor
FETCH NEXT FROM teacher_cursor INTO @teacher_id
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM teacher_course WHERE teacher_id=@teacher_id
FETCH NEXT FROM teacher_cursor INTO @teacher_id
END
GO

测试嵌套触发器的正确性
Teacher表的数据如图所示
图片 78
Teacher_course表的数据如图所示
图片 79
Course_selection表的数据如图所示
图片 80
以删除0012号教师路易为例,0012号教师教授0013号课程,且teacher_course表中并无其他教师教授0013号课程,按照逻辑要删除teacher_course表中0012号教师的所教课程记录和course_selection表中所有0013号课程的选课记录。执行下列语句

DELETE FROM teacher WHERE teacher_id='0012'

Teacher表的数据如图所示
图片 81
Teacher_course表的数据如图所示
图片 82
Course_selection表的数据如图所示
图片 83
测试结果正确
参照上面的数据,继续测试另一种情况,以删除0011号教师卢含笑为例,0011号教师教授0012号课程,在teacher_course表中还有其他教师教授该课程,因此嵌套触发器会删除teacher_course表中关于0011号教师教授课程记录,但不会删除course_selection表中关于0012号课程的选课记录。执行下列语句

DELETE FROM teacher WHERE teacher_id='0011'
GO

结果如图所示
图片 84
Teacher表的数据如图所示
图片 85
Teacher_course表的数据如图所示
图片 86
Course_selection表的数据如图所示
图片 87

XML索引

由于XML数据类型最大可存储2GB的数据,因此需要创建XML索引来优化查询性能。

2.3.位运算符

位运算符包括与运算(&),或运算(|)和异或运算(^),可以对两个表达式进行位操作,这两个表达式可以是整型数据或二进制数据。Transact-SQL首先把整型数据转换为二进制数据,然后按位运算。举个简单的例子。
示例3:声明2个int型变量@num1,@num2,对这两个赋值且做与或异或运算。
执行下面的语句

DECLARE @num1 int,@num2 int
SET @num1=5 
SET @num2=6
SELECT @num1&@num2 AS 与,
@num1|@num2 AS 或,
@num1^@num2 AS 异或

结果如图所示
图片 88
扩展示例4:写一个十进制转换为二进制的函数

CREATE FUNCTION Bin_con_dec(@dec int)--定义十进制转换为二进制函数
RETURNS varchar(20)
AS
BEGIN
DECLARE @quo int,@remainder varchar(20),@quo1 int
SET @quo=@dec
SET @remainder=''
WHILE @quo<>0
BEGIN
SET @quo1=@quo/2
SET @remainder=CAST(@quo%2 AS varchar(20))+@remainder
SET @quo=@quo1
END
RETURN @remainder
END

执行上面的函数后,运行下列语句验证函数正确性

PRINT dbo.Bin_con_dec(42)

结果为101010,函数定义正确。

3.3.创建索引

示例3:设置IndexDemo1表的id字段为PRIMARY
KEY,看系统是否自动为该字段创建了聚集索引。执行下列语句

CREATE DATABASE IndexDemo
USE IndexDemo
CREATE TABLE IndexDemo1(
id INT NOT NULL,
A CHAR(10),
B VARCHAR(10),
CONSTRAINT PK_id PRIMARY KEY(id)
)

结果如图所示
图片 89
聚集索引以PRIMARY KEY的键名为索引名。
执行下列语句删除PRIMARY KEY

ALTER TABLE IndexDemo1
DROP CONSTRAINT PK_id

聚集索引PK_id也同时被删除了。
示例4:在示例3的IndexDemo1表中,插入几行数据,添加聚集索引,观察数据顺序,添加非聚集索引,观察数据顺序
IndexDemo1的数据如图所示(未添加索引)
图片 90
执行下列语句,为id列添加聚集索引

CREATE CLUSTERED INDEX clustered_index ON IndexDemo1(id)

添加聚集索引clustered_index后IndexDemo1表的数据如图所示
图片 91
可以发现,表中数据按照id列从小到大进行排序。
此时在表中插入一条数据

INSERT INTO IndexDemo1(id,A,B)VALUES('7','g','f')

表中数据排序如图所示
图片 92
执行下列代码删除聚集索引clustered_index并对id列创建非聚集索引nonclustered_index

DROP INDEX IndexDemo1.clustered_index
GO--删除聚集索引clustered_index
CREATE NONCLUSTERED INDEX nonclustered_index ON IndexDemo1(id)
GO--创建非聚集索引nonclustered_index

表中的数据如图所示
图片 93
此时添加一条记录

INSERT INTO IndexDemo1(id,A,B)VALUES('8','g','f')

表中的数据如图所示
图片 94
在未创建聚集索引,创建了非聚集索引的表中新插入的数据是添加在末行的。

在建表时添加DEFAULT约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12) DEFAULT '类型1' 
)

1.3.3.2.查看触发器嵌套的层数

可以使用@@NESTLEVEL全局变量来查看当前触发器嵌套的层数
示例13:在示例11teacher_course_delete_batch触发器中利用@@NESTLEVEL全局变量查看当前触发器嵌套的层数
执行下列语句修改teacher_course_delete_batch触发器

ALTER TRIGGER teacher_course_delete_batch
ON teacher_course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT course_id FROM deleted
OPEN teacher_course_cursor
FETCH NEXT FROM teacher_course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
DELETE FROM course_selection WHERE course_id=@course_id
ELSE
PRINT 'course_id为'+@course_id+'的课程依然正常开课,该课程的学生选课情况不予删除'
FETCH NEXT FROM teacher_course_cursor INTO @course_id
SELECT @@NESTLEVEL AS NESTLEVEL
END
GO

测试teacher_course_delete_batch触发器(数据就不看了,未影响触发器原来的功能)
执行下列语句

DELETE FROM teacher_course WHERE teacher_id='0009'
--直接在teacher_course表中删除,激活teacher_course_delete_batch触发器

结果如图所示
图片 95
执行下列语句

DELETE FROM teacher WHERE teacher_id='0009'
--在teacher表中删除,触发teacher_delete_batch触发器,进而触发teacher_course_delete_batch触发器

结果如图所示
图片 96

主XML索引

主XML索引对XML列中XML实例内的所有标记,值和路径进行索引。创建主XML索引时,相应XML列所在的表必须对该表的主键创建了聚集索引。

2.4.比较运算符

也称关系运算符,用于比较两个值的关系,常见的有等于(=),大于(>),小于(<),大于等于(>=),小于等于(<=),不等于(<>或!=)
示例5:从Student表中查询入学成绩在平均分以上的学生信息
Student表的数据如图所示
图片 97
执行下列语句

DECLARE @ave int
SET @ave=(SELECT AVG(stu_enter_score) FROM Student)
SELECT *FROM Student
WHERE stu_enter_score>=@ave;

结果如下图所示
图片 98

注:不能直接把代码写成下面的形式

SELECT * FROM Student
WHERE stu_enter_score>=AVG(stu_enter_score)

消息147,级别15,状态1,第2 行
聚合不应出现在WHERE 子句中,除非该聚合位于HAVING
子句或选择列表所包含的子查询中,并且要对其进行聚合的列是外部引用。

因为AVG是聚合函数。

3.4.修改索引

当数据更改时,有必要重新生成索引,重新组织索引或者禁止索引。

  • 重新生成索引表示删除索引,并且重新创建索引。这样可以根据指定的填充度压缩页来删除碎片,回收磁盘空间,重新排序索引。
  • 重新组织索引对索引碎片的整理程度低于重新生成索引。
  • 禁止索引表示禁止用户访问索引。

示例5:对IndexDemo1表中的id列重新生成索引,重新组织索引和禁止索引。
执行下列语句

ALTER INDEX nonclustered_index ON IndexDemo1 REBUILD
--重新生成索引
ALTER INDEX nonclustered_index ON IndexDemo1 REORGANIZE
--重新组织索引
ALTER INDEX nonclustered_index ON IndexDemo1 DISABLE
--禁用索引

注:禁用索引后重新启用索引,只需重新生成索引就可以了。

删除DEFAULT约束
ALTER TABLE table3
DROP CONSTRAINT DF__table3__t3_type__3D5E1FD2;
--DF__table3__t3_type__3D5E1FD2是DEFAULT约束的约束名

1.3.3.3.禁用和启用嵌套触发器

EXEC sp_configure 'nested triggers',0;
GO
--禁用嵌套触发器
EXEC sp_configure 'nested triggers',1;
GO
--启用嵌套触发器
辅助XML索引

为了增强主XML索引的性能,可以创建辅助XML索引。只有创建了主XML索引后才能创建辅助XML索引。辅助XML索引分3种:PATH,VALUES和PROPERTY辅助XML索引。

2.5.逻辑运算符

逻辑运算符的作用是对条件进行测试。ALL,AND,ANY,BETWEEN,EXISTS,IN,LIKE,NOT,ALL,SOME。下面用SOME来举例。SOME的作用是如果在一组比较中,有些为true那就为true。
示例6:查询Student表中是否存在入学成绩高于平均分的学生,如果存在,输出true,不存在输出false。
Student表的stu_enter_score列(入学成绩)数据如图所示
图片 99
执行下面的语句

USE test
IF (SELECT AVG(stu_enter_score) FROM Student)<=SOME(SELECT stu_enter_score FROM Student)
PRINT 'true'
ELSE
PRINT 'false'
GO

结果如图所示
图片 100

3.5.查看索引

可以利用目录视图和系统函数查看索引。这样的函数有很多,不一一列举了。
图片 101

添加约束不指定约束名
ALTER TABLE table3
ADD DEFAULT '类型2' FOR t3_type;
GO

1.3.4.递归触发器

创建索引

为表中某个列创建索引,要求该列是XML数据类型。

ALTER TABLE Student
ADD xml_test XML;--对Student表添加一个XML数据类型字段xml_test
--对Student表的xml_test字段创建主XML索引,命名为学生信息表
CREATE PRIMARY XML INDEX 学生信息表
ON Student(xml_test)
GO
--对Student表的xml_test字段创建PATH辅助XML索引,记得写上主索引名
CREATE XML INDEX 辅助学生信息表
ON Student(xml_test)
USING XML INDEX 学生信息表 FOR PATH
GO

注:辅助索引的命名不能与主索引相同。

2.6.连接运算符

加号(+)是字符串连接运算符,可以用它把字符串串连起来,在示例4的十进制转二进制函数中,就用上了加号。
示例7:将Student表的stu_name列和stu_enter_score列放在同一列显示,列名为score
Student表的数据如图所示
图片 102
执行下列语句

SELECT stu_name+CAST(stu_enter_score AS VARCHAR(3)) AS score FROM Student

执行结果如图所示
图片 103

注:stu_enter_score列数据类型为int,加号只对字符串类型数据有效,因此要用CAST函数将stu_enter_score的数据类型转换为varchar(3),这样才能实现字符串拼接。

3.6.查看索引碎片

右键索引名,在属性——碎片中查看碎片
图片 104

添加约束指定约束名
ALTER TABLE table3
ADD CONSTRAINT df_t3_type
DEFAULT '类型2' FOR t3_type;
GO

1.3.4.1.递归触发器

触发器被激活,更改了表中数据,这种更改又激活了它自己,这种触发器被称为递归触发器。数据库创建时默认递归触发器禁用。但可以使用ALTER
DATABASE
选项来启用它。递归触发器启用的先决条件是嵌套触发器必须是启用状态,如果嵌套触发器禁用,不管递归触发器的配置是什么都将被禁用。而在递归触发器中,inserted表和deleted表都只包含被上一次触发器影响的行数据。
递归触发器有以下两种不同类型(这边没有合适的应用示例可举,先不举例了)

修改和删除索引(ALTER INDEX 和 DROP INDEX)
ALTER INDEX ALL ON Student--重建所有索引
REBUILD WITH(FILLFACTOR=80,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=ON);
--删除索引
DROP INDEX 学生信息表 ON Student
GO

注:删除主索引,与其相关的所有辅助索引也会被删除。因此上面语句中删除学生信息表索引后,辅助学生信息表索引也被删除了。

2.7.一元运算符

一元运算符只对一个表达式执行操作,该表达式可以是数字数据类型中的任何一种数据类型。SQL
Server 2008提供的一元运算符包含正(+),负(-),位反(~)。
示例8:声明一个int数据类型变量@num并赋值,对该变量做正负位反操作。
执行下列语句

DECLARE @num INT
SET @num=45
SELECT +@num AS 正,-@num AS 负,~@num AS 位反
GO

结果如图所示
图片 105

注:位反操作符用于取一个数的补数,只能用于整数。

3.7.查看统计信息

在表下的统计信息中,右键点击要查看统计信息的索引名,点击详细信息
图片 106

NOT NULL约束

约束字段值不为空。

1.3.4.2.直接递归

直接递归触发器是指整个递归过程只有它本身一个触发器的参与。自己激活了自己。

OPENXML函数

OPENXML是一个行集函数,用于检索XML文档。在试用OPENXML函数之前,一定要先用系统存储过程sp_xml_preparedocument分析文档,该存储过程在分析完XML文档后会返回一个句柄,使用OPENXML检索文档时要将该句柄作为参数传给OPENXML。
示例15

--定义两个变量@Student和@StudentInfo
DECLARE @Student int
DECLARE @StudentInfo xml
--使用SET为@StudentInfo赋值
SET @StudentInfo='
<row>
<姓名>祝红涛</姓名>
<班级编号>2019382910</班级编号>
<成绩>89</成绩>
<籍贯>沈阳</籍贯>
</row>
'
--使用系统存储过程sp_xml_preparedocument分析由@Student变量表示的XML文档,将分析得到的句柄赋值给@Student变量
EXEC sp_xml_preparedocument @Student OUTPUT,@StudentInfo
--在SELECT语句中使用OPENXML函数返回行集中的指定数据
SELECT * FROM OPENXML(@Student,'/row',2)
WITH(
姓名 varchar(8),
班级编号 varchar(10),
成绩 int,
籍贯 varchar(20)
);

结果如图所示
图片 107
在上述语句中,sp_xml_preparedocument存储过程语句用了2个参数,其中@Student是一个int型变量,该存储过程会将句柄存储在@Student变量中作为结果数据,@StudentInfo是一个XML类型的变量,存储了将要进行分析的XML文档。
OPENXML函数的语句中,使用了3个参数,其中@Student代表已经经过sp_xml_preparedocument存储过程分析的文档的句柄,’/row’使用XPath模式提供了一个路径,代表要返回XML文档中该路径下的数据行,2是一个可选数据参数,表示将这些数据行以元素为中心映射。

2.8.运算符的优先级

优先级 运算符
1 ~(位反)
2 *(乘),/(除),%(取模)
3 +(正),-(负),+(加),+(连接),-(减),&(位与)
4 =,>,<,>=,<=,<>,!=,!>,!<(比较运算符)
5 ^(位异或),位或(符号打不出来,前面有,自己翻)
6 NOT
7 AND
8 ALL,ANY,BETWEEN,IN,LIKE,ALL,SOME
9 =(赋值)

当表达式中的运算符有相同的优先级时,按照它们在表达式中的位置,一元运算符按从右往左运算,二元运算符(对两个表达式作用的运算符)按从左往右运算。
示例9:验证运算符优先级
执行下列语句

DECLARE @result INT,@num INT
SET @num=45
SET @result=@num+(~@num)*4-@num/(~@num)
SELECT @result AS result
GO

结果如图所示
图片 108
计算代码中的表达式
@result=@num+(~@num)4-@num/(~@num)
=@num+(-46)
4-@num/(-46)
=45+(-46)4-45/(-46)
=45+(-46)
4
=-139

建表时设置NOT NULL约束
CREATE TABLE table3(
    t3_id VARCHAR(12) NOT NULL,
    t3_name VARCHAR(20) NOT NULL,
    t3_class VARCHAR(12) NOT NULL,
    t3_type VARCHAR(12) NOT NULL 
)

1.3.4.3.间接递归

间接递归触发器是指整个递归过程有多个触发器参与,例如A激活B,B激活C,C激活A。可以看成是递归和嵌套的结合。
使用递归触发器时需要注意以下几点:
递归触发器很复杂,需要经过有条理的设计和全面测试
在任意点的数据修改都会激活递归触发器。只能按触发器被激活的特定顺序更新表。
所有触发器一起构成一个大事务,任意触发器的任意位置上的ROLLBACK语句都将取消所有数据的输入,所有数据均被擦除。
触发器最多只能递归16层,一旦有第17个触发器参与进来,结果与ROLLBACK命令一样,所有数据都将被擦除

3.控制语句

为已存在的列添加NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NOT NULL;

1.3.4.4.启用递归触发器

可以使用SQL Server 2008的管理器工具来启用递归触发器。
图片 109

3.1.BEGIN END语句块

BEGIN END可以定义SQL
Server语句块,使这些语句作为一组语句执行,允许语句嵌套。举例请见示例4

删除NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NULL;
GO

1.4.管理触发器

禁用和启用触发器
执行下列语句禁用和启用触发器

ALTER TABLE student DISABLE TRIGGER update_stu_no_single
--禁用update_stu_no_single触发器
GO
ALTER TABLE student ENABLE TRIGGER update_stu_no_single
--启用update_stu_no_single触发器
GO

执行下列语句禁用和启用数据库级别触发器

DISABLE TRIGGER deny_DDL_table ON DATABASE
--禁用数据库级别触发器deny_DDL_table
GO
ENABLE TRIGGER deny_DDL_table ON DATABASE
--启用数据库级别触发器deny_DDL_table
GO

3.2.IF ELSE语句块

用于指定T-SQL语句的执行条件,若条件为真,则执行条件表达式后面的语句,条件为假时,可以试用ELSE关键字指定要执行的T-SQL语句。举例请见示例4

自定义默认值对象维护数据完整性

CREATE DEFAULT date_today AS GETDATE();
--新建默认值对象名date_today,默认值为getdate()函数,获取当前日期
GO
EXEC sp_addtype date_time,'date','NULL';
--利用存储过程新建自定义数据类型date_time,参照系统数据类型date
GO
EXEC sp_bindefault 'date_today','date_time';
--将默认值对象date_today绑定到自定义数据类型date_time上
GO
CREATE TABLE table3(--新建table3,设置字段t3_date的数据类型为date_time
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12),
    t3_date date_time
);
GO
INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date的值,看默认值是否有效
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038220',
'李建',
'社会与科学',
'类型1'
);
GO
SELECT * FROM table3;--查询table3数据,看t3_date是否有默认值为当前日期

查询结果如下
图片 110

ALTER TABLE table3
ADD t3_date1 DATE;--在table3表中新增一列t3_date1,数据类型为DATE
GO
EXEC sp_bindefault 'date_today','table3.t3_date1';
--直接将默认值对象date_today绑定到table3的t3_date1列
GO
INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date和t3_date1的值,看默认值是否有效
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038221',
'李建',
'社会与科学',
'类型'
);
GO
SELECT * FROM table3;
GO

查询结果如下
图片 111

3.3.CASE分支语句

示例10:将Student表的学生,性别和籍贯打印出来,要求籍贯只能显示省内,省外或自治区。
Student表的数据如图所示
图片 112
执行下列语句

SELECT stu_name AS 姓名,stu_sex AS 性别,
(CASE stu_native_place
WHEN '浙江' THEN '省内'
WHEN '内蒙古' THEN '自治区'
WHEN '西藏' THEN '自治区'
WHEN '宁夏' THEN '自治区'
WHEN '新疆' THEN '自治区'
WHEN '广西' THEN '自治区'
ELSE '省外'
END) AS 籍贯 
FROM Student

结果如图所示
图片 113

存储过程查询默认值对象的所有者
USE test
EXEC sp_help date_today;
GO

结果如图所示
图片 114

3.4.WHILE语句

用于设置重复执行T-SQL语句或语句块的条件。
示例11:用“*”在屏幕上输出一个宽度为9的菱形。
执行下列语句

DECLARE @width int,@j int
SET @width=9--@width为菱形的最大宽度
SET @j=1--@j表示每行打印的“*”符号的个数
WHILE @j<=@width
BEGIN
PRINT SPACE((@width-@j)/2)+REPLICATE('*',@j)--SPACE函数打印n个空字符,REPLICATE打印n个特定字符串
SET @j=@j+2
END
SET @j=@width-2
WHILE @j>0
BEGIN
PRINT SPACE((@width-@j)/2)+REPLICATE('*',@j)
SET @j=@j-2
END

结果如图所示
图片 115

删除默认值对象
DROP DEFAULT date_today;

删除不成功,提示以下信息:
消息3716,级别16,状态3,第1 行
无法删除默认值’date_today’,因为它已绑定到一个或多个列。
那么我们知道,当一个默认值对象绑定了列之后,就无法删除它,如果想要删除,就必须先解绑。在上面的操作中,我们的默认值对象date_today绑定了test数据库table3表的t3_date1字段。

3.5.WAITFOR延迟语句

WAITFOR延迟语句可以让在它之后的语句在一个指定的时刻或是时间间隔后执行,可以悬挂起批处理,存储过程或事务的执行。
示例12:在某个时间点查询Student表学号为20180101的学生信息

BEGIN
WAITFOR TIME '15:03'--在15点03分查询
SELECT * FROM Student
WHERE stu_no='20180101'
END

示例13:在3分钟后查询Student表学号为20180102的学生信息

BEGIN
WAITFOR DELAY '00:03'--在3分钟后查询
SELECT * FROM Student
WHERE stu_no='20180102'
END

图片 116

对列解绑默认值对象
USE test
GO
EXEC sp_unbindefault 'table3.t3_date1';

此时我们再次尝试删除默认值对象,发现还是不行,此时要注意,在上面的操作中,默认值对象date_today不止绑定了t3_date1列,还绑定了自定义数据类型date_time,并将该数据类型定义给了t3_date列,我们还需要对date_time解绑默认值对象。

EXEC sp_unbindefault 'date_time';

此时再次删除默认值对象,就可以成功删除。删除默认值对象后,原先绑定的字段不会再有默认值。

3.6.RETURN无条件退出语句

该语句表示无条件终止查询,批处理或存储过程的执行。存储过程和批处理RETURN语句后面的语句都不再执行。当在存储过程中使用该语句时,可以指定返回给调用应用程序、批处理或过程的整数值。如果RETURN未指定返回值,则存储过程的返回值是0

自定义规则维护数据完整性

规则是对列或自定义数据类型的值的规定和限制。自定义规则的表达式一定要返回布尔类型的值,并且表达式中不能包含多个变量。

CREATE RULE score_rule AS @math_score>=0;
GO--新建规则score_rule,参数@math_score
EXEC sp_addtype 'score_type','float','NULL';
GO--新建自定义数据类型score_type
CREATE TABLE table_score(--新建表table_score,预设mt_score和at_score字段用于绑定规则
s_id VARCHAR(4),
s_name VARCHAR(10),
mt_score float,--该字段将用于规则score_rule绑定到列
at_score score_type--该字段将用于规则score_rule绑定到自定义数据类型
);
GO
EXEC sp_bindrule 'score_rule','score_type';
GO--将score_rule规则绑定到自定义数据类型score_type
EXEC sp_bindrule 'score_rule','table_score.mt_score';
GO--将score_rule规则绑定到table_score表的mt_score列

----以下进行规则测试
INSERT INTO table_score(
s_id,
s_name,
mt_score,
at_score
)VALUES(
'0001',
'张华',
'-1',
'-1'
);
GO

进行违反规则的插入后,数据库报错
消息513,级别16,状态0,第1 行
列的插入或更新与先前的CREATE RULE
语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库’test’,表’dbo.table_score’,列’mt_score’。

语句已终止。
很明显,mt_score的插入值为-1,违反了必须大于等于0的规则,数据库报错。将mt_score的插入值改成符合规则的数据,再次运行插入语句,数据库依然会报错,因为at_score字段的插入值也是违反规则的。将两个数据改成符合规则的返回,执行成功。

注:新建规则时表达式一定要是返回布尔类型的值,否则会报错

消息4145,级别15,状态1,过程sum_score,第1 行
在应使用条件的上下文(在’;’ 附近)中指定了非布尔类型的表达式。

3.7.GOTO跳转语句

该语句使T-SQL批处理的执行跳转至指定标签。由于该语句破坏结构化语句的结构,尽量少用
示例13:将GOTO作为分支机制
执行下面语句

DECLARE @Counter int;  
SET @Counter = 1;  
WHILE @Counter < 10  
BEGIN   
    SELECT @Counter  
    SET @Counter = @Counter + 1  
    IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.  
    IF @Counter = 5 GOTO Branch_Two  --This will never execute.  
END  
Branch_One:  
    SELECT 'Jumping To Branch One.'  
    GOTO Branch_Three; --This will prevent Branch_Two from executing.  
Branch_Two:  
    SELECT 'Jumping To Branch Two.'  
Branch_Three:  
SELECT 'Jumping To Branch Three.';

结果如图所示
图片 117
当Counter=4时,执行GOTO语句输出Branch
One,执行完这个语句之后就打破了WHILE循环,接着执行Branch_One语句中的GOTO,输出Branch
Three,结束。

注:在WHILE循环中使用GOTO会打破循环。

示例14:用GOTO语句实现示例11中打印菱形的功能
执行下列语句

DECLARE @width int,@j int,@i int
SET @width=9--@width为菱形的最大宽度
SET @j=1--@j表示每行打印的“*”符号的个数
SET @i=1--@i表示下一行打印第i行
Set3:PRINT SPACE((@width-@j)/2)+REPLICATE('*',@j)--SPACE函数打印n个空字符,REPLICATE打印n个特定字符串
SET @i=@i+1
IF @i<=(@width+1)/2
GOTO Set1
ELSE
GOTO Set2
Set1:
SET @j=@j+2
GOTO Set3
Set2:
SET @j=@j-2
IF @j>=1
GOTO Set3

结果如图所示
图片 118

删除自定义规则

和自定义默认值对象一样,删除自定义规则要求该规则先与字段和自定义数据类型解绑。在上面的操作中,score_rule规则与自定义数据类型score_type以及列mt_score已绑定。因此执行以下语句:

EXEC sp_unbindrule 'score_type';
GO--解除规则score_rule与score_type之间的绑定
EXEC sp_unbindrule 'table_score.mt_score';
GO--解除规则score_rule与表table_score的mt_score列的绑定
DROP RULE score_rule;--删除score_rule规则

注:经过试验,一个列只能绑定1条规则,如果对一个列绑定2条规则,前一条规则会被后一条规则顶替。

3.8.TRY CATCH错误处理语句

如果TRY块内部发生错误,会将控制传递给CATCH块内的语句组。TRY
CATCH构造捕捉所有严重级别大于10但不会终止数据库连接的错误。
示例15:TRY CATCH的示例
执行下列语句

BEGIN TRY
SELECT * FROM Student
SELECT 120/0 FROM Student
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS '错误信息'
END CATCH

执行结果如图所示
图片 119
语句中3个select语句全部都执行了。如果把报错的select语句放到正常的select语句前面,正常的select语句还能不能执行呢?执行下列语句

BEGIN TRY
SELECT 120/0 FROM Student
SELECT * FROM Student
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS '错误信息'
END CATCH

结果如图所示
图片 120
正常select语句无法执行。TRY
CATCH语句的逻辑是,一旦TRY语句块中出现问题语句,立刻跳转到CATCH语句块,TRY语句块接下去的语句不再执行。

查看自定义规则
EXEC sp_help 'score_rule';

结果如图所示
图片 121

4.常用函数

查看自定义规则的定义信息
EXEC sp_helptext 'score_rule';
GO

结果如图所示
图片 122

4.1.数据类型转换函数

默认情况下SQL
Server会对一些数据类型进行自动转换,这种转换称为隐式转换。遇到无法自动转换,则需要用CAST()函数和CONVERT()函数转换,这种转换称为显式转换。CAST()函数和CONVERT()函数的功能是相同的,CAST函数更容易使用,CONVERT函数的优点是可以指定日期和数值格式。
示例16:将Student表中的学号转换为日期格式
下面两句语句的功能是一样的,执行下列语句

SELECT stu_name,CAST(stu_no AS DATE) AS 学号转换成日期,stu_enter_score,stu_birthday FROM Student
SELECT stu_name,CONVERT(DATE,stu_no) AS 学号转换成日期,stu_enter_score,stu_birthday FROM Student

结果如图所示
图片 123
示例17:用CONVERT()函数将stu_birthday转化成指定格式的日期
执行下列语句

SELECT stu_name,CONVERT(VARCHAR(20),stu_birthday,101) FROM Student
--CONVERT函数将DATE类型的stu_birthday字段转化为字符串,并限定了样式,代码101

结果如图所示
图片 124

注:在上述代码中,CONVERT(DATE,stu_birthday,101)这么写是没用的。101格式码只对日期格式转化为字符串有效,其他格式转化为日期格式是无效的。

其他常用函数太简单了这边不写了,略。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图