菜单

《SQL Server 二〇〇九从入门到精晓》–20180704

2019年3月1日 - MySQL

XML查询技术

XML文书档案以一个纯文本的样式存在,首要用于数据存款和储蓄。不但方便用户读取和动用,而且使修改和保险变得更便于。

约束

XML数据类型

XML是SQL
Server中置放的数据类型,可用以SQL语句恐怕当做存款和储蓄进程的参数。用户能够从来在数据库中蕴藏、查询和治本XML文件。XML数据类型还是可以够保存整个XML文书档案。XML数据类型和其它数据类型不设有根本上的差距,能够把它用在任何一般SQL数据类型能够利用的地点。
示例1:创立3个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数据类型的选取范围
只有ST汉兰达ING数据类型才能转换来XML。
XML列无法利用于GROUP BY语句中
XML数据类型存款和储蓄的多少不可能跨越2GB
XML数据类型字段无法被设置成主键只怕外键或称为其一部分。
Sql_variant数据类型字段的利用不能够把XML数据类型作为种子品种。
XML列不能够钦定为唯一的。
COLLATE子句不可能被使用在XML列上。
仓储在数据库中的XML仅援救128级的层系。
表中最对只可以拥有三11个XML列。
XML列不可能加盟到规则中。
唯一可利用于XML列的内置标量函数是ISNULL和COALESCE。
装有XML数据类型列的表不可能有叁个超过15列的主键。

主关键字约束(Primary Key Constraint)

用来钦命表中的一列或几列组合的值在表中拥有唯一性。建立主键的目标是让外键来引用。

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

能够成立xml类型的变量,参数和列,恐怕将XML架构集合和xml类型的变量、参数或列关联,那种情景下,xml数据类型实例称之为类型化xml实例。不然XML实例称为非类型化的实例。

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。

XML数据类型方法

XML数据类型共有5种方法
query():执行贰个XML查询并重临查询结果(重返2个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():计算3个询问并从XML中回到贰个简练的值(只好回到单个值,且该值为非XML数据类型)。
Value()方法有二个参数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数据类型,公共语言运营时(CLXC90)用户定义类型,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

删除Primary Key
ALTER TABLE table1
DROP CONSTRAINT t_idss;

封锁名与列名不均等,此处填写约束名

XQuery简介

XQuery是一种查询语言,能够查询结构化恐怕半结构化的多少。SQL Server
二零零六中对XML数据类型提供了支持,能够存款和储蓄XML文书档案,然后利用XQuery语言实行询问。

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

FOR XML子句

由此在SELECT语句中运用FOTiguanXML子句能够把数据库表中的数据检索出来并生成XML格式。SQL Server
二零零六支撑FO奥德赛XML的三种格局,分别是RAW方式,AUTO方式,EXPLICIT方式和PATH情势。

累加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;
FOR XML RAW

将表转换来成分名称是row,属性名称为列名可能列的外号。
示例9:将Student表转换为XML格式(FORAV4 XML RAW)
Student表的数目如图所示
图片 7
履行语句:

SELECT * FROM Student FOR XML RAW;

查询结果如图所示
图片 8
图片 9

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

选拔表名称作为成分名称,使用列名称作为品质名称,SELECT关键字前边列的一一用于XML文书档案的层次。
示例10:将Student表转换为XML格式(FO卡宴 XML AUTO)
履行语句:

SELECT * FROM Student FOR XML AUTO;

询问结果如图所示
图片 10
图片 11

外关键字约束(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走,不能够反着来。

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

FOR XML EXPLICIT

同意用户显式地定义XML树的样子,不受AUTO形式中的各种限制。不能够将FO奥迪Q3 XML
EXPLICIT直接用在SELECT子句中。
示例11:将xmlTest表转换为XML格式(FO汉兰达 XML EXPLICIT)
XmlTest表的数目如图所示
图片 12

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,不能漏掉,否则结果会以表格形式显示

询问结果如图所示
图片 13
图片 14
在结果图中大家发现,红框中二个班级新闻列在一块儿,而具有学员都列在高级中学一年级3班下,那不是大家想要的结果,大家盼望每一种班级对应自个儿的上学的小孩子。那么哪些缓解此类题材啊,那事关到排序。

注:假使层级中有五个数据完全重复,能够在该层级对应的代码前加DISTINCT关键字去除重复成分。

首先删除代码行末的FO牧马人 XML
EXPLICIT语句,仅仅执行剩下的有些,使结果以表格情势突显,那么结果如下
图片 15
那几个表格每行的次第也象征了该表格转化为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]排序。让我们看看结果什么。
运维方面包车型客车代码,但不运营FO奇骏 XML
EXPLICIT语句,看看表格中多少内容和行顺序是还是不是改变
图片 16
如图所示,发现行反革命数据和学习者数量的一一显示正确。运营具有代码获得XML文书档案,结果如图所示
图片 17
鉴于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表,看看每个班级和它下属学生的层级关系是或不是有误。

注:写FO奥德赛 XML
EXPLICIT代码要小心,层级1的代码中先安装层级结构,不要先急着赋值。在部属层级的代码中对层级1中的代码举办赋值,最好重复赋值,不然就会冒出文中的排序难点。假如有个别层级出现重复数据,在该层级的代码前加DISTINCT关键字。化解排序难点最好的办法是对种种层级的质量重复赋值并在结尾用OOdysseyDER
BY按层级属性排序。

细心察看地点的XML文书档案,发现总分属性的值是个float类型,要把它转换到int,只供给把层级3中对总分的赋值代码改成CAST(年级总分
AS int)
图片 18

UNIQUE约束

除主键外另一种能够定义唯一约束的项目,允许空值。添加UNIQUE的章程同上,那里只简单举例。

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

PATH情势提供了一种较简单的法子来混合成分及品质。在PATH方式中,列名或列别称被当作XPATH表明式来拍卖,那几个表达式钦点了怎么样将值映射到XML中。默认意况下,PATH方式为每一样自动生成

CHECK约束

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

没知名称的列

上边介绍一种简单的FO路虎极光 XML PATH应用措施

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

查询结果如图所示
图片 19

注:要是提供了空字符串FOKoleos XML PATH(‘’)则不会转变任何因素。

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

查询结果如图所示
图片 20
示例12:利用xmlTest表和mainTeacher表查询出xmlTest表中战表>=700分的学生的班老板音信和学生音讯,并转化成XML格式
XmlTest表数据如下图所示
图片 21
MainTeacher表数据如下图所示
图片 22
推行下边包车型大巴言辞

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>
添加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约束限制结果为男和女就会报错。

TYPE命令

SQL Server援助TYPE命令将FO兰德酷路泽 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表

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

<result>
  <学生信息 学号="20180105" 姓名="王婷" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>
<result>
  <学生信息 学号="20180109" 姓名="甄诚" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>
在成立表的时候添加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’。

讲话已告一段落。

FO景逸SUV 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方法,查询结果如下图所示
图片 24

<联系电话>15963002120</联系电话>
<联系电话>15963002120</联系电话>
删除CHECK约束
ALTER TABLE table3
DROP CONSTRAINT ck_t3_type;

XML索引

是因为XML数据类型最大可存款和储蓄2GB的数据,因而要求创设XML索引来优化查询品质。

DEFAULT约束

由此定义列的暗中同意值或选取数据库的私下认可值对象绑定表列,来内定列的暗许值。

主XML索引

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

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

为了拉长主XML索引的性情,能够成立协助XML索引。唯有创立了主XML索引后才能制造帮助XML索引。帮忙XML索引分3种:PATH,VALUES和PROPE揽胜极光TY扶助XML索引。

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

为表中有些列创立索引,要求该列是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

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

添加封锁不点名约束名
ALTER TABLE table3
ADD DEFAULT '类型2' FOR t3_type;
GO
修改和删除索引(ALTELacrosse INDEX 和 DROP INDEX)
ALTER INDEX ALL ON Student--重建所有索引
REBUILD WITH(FILLFACTOR=80,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=ON);
--删除索引
DROP INDEX 学生信息表 ON Student
GO

注:删除主索引,与其连带的装有协理索引也会被删去。因而地点语句中剔除学生新闻表索引后,帮忙学生音讯表索引也被剔除了。

增加封锁钦赐约束名
ALTER TABLE table3
ADD CONSTRAINT df_t3_type
DEFAULT '类型2' FOR t3_type;
GO
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)
);

结果如图所示
图片 25
在上述语句中,sp_xml_preparedocument存款和储蓄进度语句用了3个参数,当中@Student是三个int型变量,该存款和储蓄进程会将句柄存款和储蓄在@Student变量中作为结果数据,@StudentInfo是2个XML类型的变量,存款和储蓄了就要实行分析的XML文书档案。
OPENXML函数的言语中,使用了三个参数,个中@Student代表曾经由此sp_xml_preparedocument存款和储蓄进程分析的文书档案的句柄,’/row’使用XPath形式提供了多少个路线,代表要回去XML文书档案中该路线下的数目行,2是三个可选数据参数,表示将这么些多少行以成分为宗旨映射。

NOT NULL约束

封锁字段值不为空。

建表时设置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 
)
为已存在的列添加NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NOT NULL;
删除NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NULL;
GO

自定义暗许值对象保证数据完整性

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是否有默认值为当前日期

查询结果如下
图片 26

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

查询结果如下
图片 27

储存进程查询暗中认可值对象的全体者
USE test
EXEC sp_help date_today;
GO

结果如图所示
图片 28

去除私下认可值对象
DROP DEFAULT date_today;

删去不成事,指示以下音信:
消息3716,级别16,状态3,第1 行
心慌意乱删除暗中同意值’date_today’,因为它已绑定到贰个或七个列。
那就是说我们清楚,当1个暗中认可值对象绑定了列其后,就不恐怕删除它,如若想要删除,就必须先解绑。在地方的操作中,大家的暗中认可值对象date_today绑定了test数据库table3表的t3_date1字段。

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

此时大家再一次尝试删除暗中同意值对象,发现照旧可怜,此时要专注,在上面的操作中,暗许值对象date_today不止绑定了t3_date1列,还绑定了自定义数据类型date_time,并将该数据类型定义给了t3_date列,大家还要求对date_time解绑默许值对象。

EXEC sp_unbindefault 'date_time';

那时候再也删除暗许值对象,就能够成功删除。删除默许值对象后,原先绑定的字段不会再有默许值。

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

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

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 行
在应接纳标准的上下文(在’;’ 附近)中钦点了非布尔类型的表明式。

去除自定义规则

和自定义暗中同意值对象一样,删除自定义规则供给该规则先与字段和自定义数据类型解绑。在地点的操作中,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条规则,要是对3个列绑定2条规则,前一条规则会被后一条规则顶替。

翻开自定义规则
EXEC sp_help 'score_rule';

结果如图所示
图片 29

查看自定义规则的定义新闻
EXEC sp_helptext 'score_rule';
GO

结果如图所示
图片 30

相关文章

发表评论

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

网站地图xml地图