《SQL Server 二零一零从入门到明白》–20180704

发布时间:2019-02-25  栏目:NoSQL  评论:0 Comments

XML查询技术

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

SELECT操作多表数据

至于连接的难题,在《SQL必知必会》学习笔记中早就讲到过,可是从未明白完全,所以再学一下。

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数据类型的运用范围
唯有ST途睿欧ING数据类型才能转换到XML。
XML列无法运用于GROUP BY语句中
XML数据类型存款和储蓄的数量不能够超过2GB
XML数据类型字段不可能被设置成主键或然外键或称为其一部分。
Sql_variant数据类型字段的使用不可能把XML数据类型作为种子品种。
XML列不能钦赐为唯一的。
COLLATE子句不可能被应用在XML列上。
储存在数据库中的XML仅扶助128级的层次。
表中最对只可以拥有3一个XML列。
XML列不能够参与到规则中。
唯一可利用于XML列的放置标量函数是ISNULL和COALESCE。
具有XML数据类型列的表不可能有一个当先15列的主键。

JOIN连接

第1大家先来看一下最简便易行的接连。Products表和Vendors表的接连
示例1
Products表数据如下
manbet手机客户端3.0 1
manbet手机客户端3.0,Vendors表数据如下
manbet手机客户端3.0 2
将那两张表经过以下语句连接

USE test
SELECT prod_name,Products.vend_id,vend_name
FROM Products,Vendors
WHERE Products.vend_id=Vendors.vend_id;
--连接条件为两张表的vend_id相同

连续结果如下
manbet手机客户端3.0 3
当用户所需数据不在四个表中,而在多少个表中,就要求采取多表连接,将五个表中的数量整合到联合,使数码更直观地展示出来。在上述语句中,通过JOIN大家得以从结果中很强烈地知道产品及其供应商。

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

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

内连接

内连接是一种比较常用的多寡连接查询格局,上述语句便是内接连。上面将提交另一种内接连的语法。两者的询问结果是平等的。

--内连接INNER JOIN
USE test
SELECT prod_name,Products.vend_id,vend_name
FROM Products INNER JOIN Vendors--和上面语句差异之处
ON Products.vend_id=Vendors.vend_id;

注:使用INNE奥迪Q5JOIN时,关键字INNESportage可省略,不过接连条件必须用ON连接,不可能用FROM了。

内接连分为等值连接,非等值连接和自然连接二种。

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>的子元素

询问结果如图所示
manbet手机客户端3.0 4
点击查询结果
manbet手机客户端3.0 5
如想询问标签

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

结果如图所示
manbet手机客户端3.0 6

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

Value():总括三个询问并从XML中回到3个不难的值(只可以回去单个值,且该值为非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

询问结果如图所示
manbet手机客户端3.0 7

注:SQLType不可能是XML数据类型,公共语言运转时(CLOdyssey)用户定义类型,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 '插入节点后信息'

询问结果插入节点后音信如图所示
manbet手机客户端3.0 8

注: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

询问结果如下图所示
manbet手机客户端3.0 9

等值连接

连年条件用等号相连,查询结果中列出被再而三表中的全体列,包含重复列。为了特别形象地反映“列出重复列”那天天性,接下去只怕用Products和Vendors表来比喻。逻辑是:一种产品得以对应多少个供应商,二个供应商能够生育各样产品。
示例2
Products表数据如下
manbet手机客户端3.0 10
Vendors表数据如下
manbet手机客户端3.0 11
然后实施等值连接的代码

SELECT * FROM Vendors,Products
WHERE Vendors.vend_id=Products.vend_id;

结果如图所示
manbet手机客户端3.0 12
能够看出,在结果中vend_id列重复。

注:等值连接要满足1个规格,连接条件用=号,查询结果中包含被接连表中保有列,包蕴重复列。

XQuery简介

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

非等值连接

接连条件中利用除等号以外的相比较运算符来比较连接列的列值。例如利用>,<,>=,<=,<>等,也得以行使范围运算符BETWEEN。上边用stu表和class表来示范非等值连接
示例3
新建stu表并插入数据,stu表用来表示学生和该学生的年级总成绩

    CREATE TABLE stu(
    sto VARCHAR(8) NOT NULL,
    stname VARCHAR(20) NOT NULL,
    stsex CHAR(2),
    stgrade FLOAT,
    CONSTRAINT pk_sto PRIMARY KEY(sto),
    CONSTRAINT ck_stsex CHECK(stsex in('男','女'))
    );
    INSERT INTO stu(sto,stname,stsex,stgrade)
    VALUES('20180101','李华','男','568'),
    ('20180102','张三','男','627'),
    ('20180103','孙丽','女','339'),
    ('20180104','袁康','男','482'),
    ('20180105','王婷','女','761'),
    ('20180106','赵四','男','568'),
    ('20180107','周其','女','348.5'),
    ('20180108','吴伟','男','528.5'),
    ('20180109','甄诚','女','702');
    SELECT * FROM stu;

stu表数据如下
manbet手机客户端3.0 13
新建class表并插入数据,class表用来代表分班意况和年级总分之间的涉及

    CREATE TABLE class(
    cla_no CHAR(2),
    cl CHAR(1),
    flag CHAR(6),
    gradelevel_h INT,
    gradelevel_l INT,
    CONSTRAINT pk_cla_no PRIMARY KEY(cla_no),
    CONSTRAINT ck_cl CHECK(cl in('A','B','C','D'))
    );
    INSERT INTO class(cla_no,cl,flag,gradelevel_l,gradelevel_h)
    VALUES('01','A','创新班','700','750'),
    ('02','B','重点','650','700'),
    ('03','B','重点','600','650'),
    ('04','C','提高','500','600'),
    ('05','C','提高','400','500'),
    ('06','D','补习','300','400'),
    ('07','D','补习','0','300');
    SELECT * FROM class;

Cla表数据如下
manbet手机客户端3.0 14
如今要通过询问直观反映出学生分班景况

    SELECT sto,stname,cla_no,flag,stgrade
    FROM stu INNER JOIN class
    ON stu.stgrade<=class.gradelevel_h AND stu.stgrade>class.gradelevel_l
    ORDER BY sto;

结果如图所示
manbet手机客户端3.0 15

FOR XML子句

经过在SELECT语句中利用FO福睿斯XML子句能够把数据库表中的数据检索出来并生成XML格式。SQL Server
二〇〇九支撑FO路虎极光XML的各类方式,分别是RAW方式,AUTO情势,EXPLICIT情势和PATH方式。

理所当然连接

务求连接条件是同等的属性组,且用等号连接,并在结果中把重复的性质列去掉。自然连接是去掉重复属性列的等值连接。
在SQL Server 二零一零中,不帮助NATURAL
JOIN关键字,在其余数据库中能够使用首要字NATURAL JOIN实行一而再。

SELECT * FROM Vendors NATURAL JOIN Products;

地点的几个三番五次示例中,示例1是当然连接,示例2因为有再一次列vend_id因而不满意自然连接的定义。示例3因为老是条件不是用等号连接,且一连条件属性列差异,因而不满意自然连接的概念。

注:对等值连接和自然连接的定义不是很清晰,后续弄精通了会有补充。

重中之重难点
1.3个连接满意等值连接全部标准,唯独查询结果中不含有被连接表的装有列,而只蕴涵部分列,是还是不是是等值连接。
2.等值连接是或不是必须包蕴重复列,不包罗重复列但满意等值连接其余条件,是不是就不是等值连接?网上说本来连接是很是的等值连接,依照自然连接不分包重复列的概念,那么等值连接也足以不包括重复列。
3.本来连接是还是不是要求连接条件的属性列列名和数据类型都无差异?如若仅列名差别,其余标准都满意自然连接,是还是不是足以断定为本来连接。百度当然连接的定义说列名不必然相同,但网上也有人说列名必须一致。(答案是列名必须一律。自然连接会自动匹配列名相同列,若是列名相同,数据类型差异,能展开隐式转换就能合作成功,无法拓展隐式转换就会报错)
Natural Join: Guidelines

  • The associated tables have one or more pairs of identically named
    columns.
  • The columns must be the same data type.
  • Don’t use ON clause in a natural join.

    注:关于两表中包含多少个相同列,怎样抉择一些列作为延续字段实行自然连接,在任何小说中再详述。

    ##### 外连接

    与内连接的界别在于,借使表中有数据在另一张表中找不到一而再对象,内接连的做法是在结果中忽略这几个多少不打字与印刷,而外连接的做法是打字与印刷这一个数据,而那一个数量在另一张表中的连天对象则打字与印刷成NULL。
    涉足外接连的表有主从之分,依据表的职位和讲话中钦点的显要字,外接连可分为左外连接,右外连接,完全连接。

    ###### 左外连接(LEFT OUTE奥德赛 JOIN)

    重临JOIN左侧表全体行,且重返JOIN右边表中能匹配上的行并对五个表的行开展匹配。

    ###### 右外接连(安德拉IGHT OUTRE JOIN)

    归来JOIN左边表全体行,且重返JOIN左侧表中能匹配上的行并对多少个表的行开始展览匹配。

    ###### 完全连接(FULL OUTE哈弗 JOIN)

    回去JOIN左右两侧表全部能匹配的行和无法合营的行。
    上边依次举例
    示例4:Student表和Stu_Grade表
    Student表数据如下
    manbet手机客户端3.0 16
    Stu_Grade表数据如下
    manbet手机客户端3.0 17

    ###### 对Student表和Stu_Grade表的左外连接

SELECT* FROM Student--左外连接,Student表为主,Stu_Grade表为从
LEFT OUTER JOIN Stu_Grade
ON Student.stu_id=Stu_Grade.stu_id;

查询结果如图所示
manbet手机客户端3.0 18
图中体现了主表(Student)的全部行和从表(Stu_Grade)的一部分行。

FOR XML RAW

将表转换来成分名称是row,属性名称为列名大概列的别名。
示例9:将Student表转换为XML格式(FOLAND XML RAW)
Student表的数据如图所示
manbet手机客户端3.0 19
举办语句:

SELECT * FROM Student FOR XML RAW;

询问结果如图所示
manbet手机客户端3.0 20
manbet手机客户端3.0 21

对Student表和Stu_Grade表的右外接连
SELECT* FROM Student--右外连接,Student表为从,Stu_Grade表为主
RIGHT OUTER JOIN Stu_Grade
ON Student.stu_id=Stu_Grade.stu_id;

查询结果如图所示
manbet手机客户端3.0 22
图中显示了主表(Stu_Grade)的全部行和从表(Student)的一些行。

FOR XML AUTO

利用表名称作为成分名称,使用列名称作为品质名称,SELECT关键字前面列的逐一用于XML文书档案的层次。
示例10:将Student表转换为XML格式(FO福睿斯 XML AUTO)
实施语句:

SELECT * FROM Student FOR XML AUTO;

查询结果如图所示
manbet手机客户端3.0 23
manbet手机客户端3.0 24

对Student表和Stu_Grade表的一心连接
SELECT* FROM Student--完全连接
FULL OUTER JOIN Stu_Grade
ON Student.stu_id=Stu_Grade.stu_id;

询问结果如图所示
manbet手机客户端3.0 25
图中显示了Student表和Stu_Grade表的富有行。

FOR XML EXPLICIT

同意用户显式地定义XML树的形态,不受AUTO形式中的各类限制。不可能将FOMurano XML
EXPLICIT直接用在SELECT子句中。
示例11:将xmlTest表转换为XML格式(FOOdyssey XML EXPLICIT)
XmlTest表的数目如图所示
manbet手机客户端3.0 26

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

查询结果如图所示
manbet手机客户端3.0 27
manbet手机客户端3.0 28
在结果图中大家发现,红框中三个班级音信列在协同,而具备学生都列在高级中学一年级3班下,那不是大家想要的结果,大家期待每一个班级对应本人的上学的小孩子。那么哪些缓解此类难点啊,那涉及到排序。

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

首先删除代码行末的FO科雷傲 XML
EXPLICIT语句,仅仅执行剩下的一部分,使结果以表格情势表现,那么结果如下
manbet手机客户端3.0 29
本条表格每行的逐一也象征了该表格转化为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]排序。让我们看看结果怎么样。
运行方面包车型大巴代码,但不运转FOOdyssey XML
EXPLICIT语句,看看表格中数据内容和行顺序是还是不是变动
manbet手机客户端3.0 30
如图所示,发现行反革命数据和学习者数量的一一展现正确。运转具有代码获得XML文书档案,结果如图所示
manbet手机客户端3.0 31
由于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表,看看每一个班级和它下属学生的层级关系是还是不是有误。

注:写FOLAND XML
EXPLICIT代码要留心,层级1的代码中先安装层级结构,不要先急着赋值。在下属层级的代码中对层级1中的代码进行赋值,最好重复赋值,不然就会并发文中的排序难点。假如有个别层级出现重复数据,在该层级的代码前加DISTINCT关键字。化解排序难题最好的不二法门是对一一层级的质量重复赋值并在终极用OHavalDER
BY按层级属性排序。

全面察看地点的XML文书档案,发现总分属性的值是个float类型,要把它转换到int,只需求把层级3中对总分的赋值代码改成CAST(年级总分
AS int)
manbet手机客户端3.0 32

接力连接

穿插连接将转变来自多个基表全体恐怕的结缘。不行使WHERE子句,交叉连接的结果是五个基表全体行的笛Carl积,使用WHERE子句,交叉连接的结果满意WHERE子句条件的数额行数的笛Carl积
示例5:Student表和Course表演示交叉连接
Student表如图所示,为了更直观地看来数据,小编压缩了一部分Student表的数额
manbet手机客户端3.0 33
Course表如图所示
manbet手机客户端3.0 34

FOR XML PATH

PATH情势提供了一种较简单的章程来混合成分及品质。在PATH形式中,列名或列别名被当作XPATH表明式来拍卖,那些表明式钦赐了什么将值映射到XML中。暗许境况下,PATH格局为每一样自动生成

对Student和Course表举办无WHERE子句的陆续连接
SELECT * FROM Student
CROSS JOIN Course;

询问结果如图所示
manbet手机客户端3.0 35
共24条数据,就是Student表和Course表的笛Carl积。

尚无称谓的列

下边介绍一种简易的FO奥迪Q5 XML PATH应用措施

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

询问结果如图所示
manbet手机客户端3.0 36

注:借使提供了空字符串FOOdyssey XML PATH(‘’)则不会变卦任何因素。

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

查询结果如图所示
manbet手机客户端3.0 37
示例12:利用xmlTest表和mainTeacher表查询出xmlTest表中培养>=700分的上学的小孩子的班主任新闻和学习者新闻,并转载成XML格式
XmlTest表数据如下图所示
manbet手机客户端3.0 38
MainTeacher表数据如下图所示
manbet手机客户端3.0 39
实施下边包车型客车语句

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>
对Student表和Course表举行有WHERE子句的交叉连接
SELECT * FROM Student
CROSS JOIN Course
WHERE Student.stu_id='20180101';

查询结果如图所示
manbet手机客户端3.0 40
Student表中级知识分子足WHERE子句的唯有1条数据,查询结果就是那条数据和Course表的笛卡尔积。

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表

查询结果如图所示
manbet手机客户端3.0 41
双击打开查看XML

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

1个表与自家相连接的查询。首若是用以处理列与列之间的关联。以PCourse表为例,CNumber代表该学科的课程号,PCNumber代表该科目标先修课课程号。
示例6:PCourse表的自连接查询
PCourse表的数量如图所示
manbet手机客户端3.0 42
对PCourse表实行自连接查询

SELECT A.PCNumber,A.PCName,B.CNumber,B.CName
FROM PCourse A,PCourse B
WHERE A.CNumber=B.PCNumber;

询问结果如图所示
manbet手机客户端3.0 43

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方法,查询结果如下图所示
manbet手机客户端3.0 44

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

XML索引

鉴于XML数据类型最大可存款和储蓄2GB的数码,由此必要创制XML索引来优化查询品质。

主XML索引

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

辅助XML索引

为了增强主XML索引的天性,能够创设帮助XML索引。只有成立了主XML索引后才能创造援助XML索引。辅助XML索引分3种:PATH,VALUES和PROPERubiconTY协理XML索引。

创办索引

为表中有个别列创造索引,必要该列是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

注:协理索引的命名无法与主索引相同。

修改和删除索引(ALTE汉兰达 INDEX 和 DROP INDEX)
ALTER INDEX ALL ON Student--重建所有索引
REBUILD WITH(FILLFACTOR=80,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=ON);
--删除索引
DROP INDEX 学生信息表 ON Student
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)
);

结果如图所示
manbet手机客户端3.0 45
在上述语句中,sp_xml_preparedocument存款和储蓄进度语句用了三个参数,当中@Student是三个int型变量,该存款和储蓄进度会将句柄存款和储蓄在@Student变量中作为结果数据,@StudentInfo是3个XML类型的变量,存储了就要进行解析的XML文书档案。
OPENXML函数的语句中,使用了叁个参数,在那之中@Student代表已经通过sp_xml_preparedocument存款和储蓄进程分析的文书档案的句柄,’/row’使用XPath情势提供了八个路子,代表要回来XML文档中该路线下的数据行,2是三个可选数据参数,表示将这几个数据行以元素为主导映射。

留下评论

网站地图xml地图