《SQL Server 二零零六从入门到领悟》–20180710

发布时间:2019-02-28  栏目:MyBatis  评论:0 Comments

目录

XML查询技术

XML文书档案以3个纯文本的款式存在,主要用来数据存款和储蓄。不但有利于用户读取和平运动用,而且使修改和珍贵变得更便于。

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级的层系。
表中最对只可以拥有三贰13个XML列。
XML列不可能加盟到规则中。
唯一可利用于XML列的放手标量函数是ISNULL和COALESCE。
享有XML数据类型列的表不可能有1个超过15列的主键。

1.运用Transact-SQL语言编程

即使SQL Server
二零零六提供了图形化界面,但唯有一种Transact-SQL语言能够直接与数据库引擎进行交互。依照实施效果特色能够将Transact-SQL语言分成3大类:数据定义语言DDL,数据操纵语言DML,数据控制语言DCL。

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

能够创造xml类型的变量,参数和列,大概将XML架构集合和xml类型的变量、参数或列关联,那种气象下,xml数据类型实例称之为类型化xml实例。不然XML实例称为非类型化的实例。

1.1.多少定义语言DDL

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

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

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()方法有三个参数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数据类型,公共语言运营时(CL帕杰罗)用户定义类型,image,text,ntext或sql_variant数据类型,但足以是用户自定义数据类型SQL。

Modify():在XML文书档案的稳妥地点执行1个改动操作。它的参数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.2.多少操纵语言DML

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

XQuery简介

XQuery是一种查询语言,能够查询结构化或然半结构化的数目。SQL Server
2010中对XML数据类型提供了支撑,能够存款和储蓄XML文书档案,然后采纳XQuery语言实行查询。

1.3.数码控制语言DCL

提到到权力管理的语言称为数据控制语言,首要用来实施有关安全管理的操作。如授予权限(GRANT),收回权限(REVOKE),拒绝授予主体权限,并幸免主体通过组或剧中人物成员持续权限(DENY

FOR XML子句

通过在SELECT语句中央银行使FO兰德酷路泽XML子句可以把数据库表中的数据检索出来并生成XML格式。SQL Server
二〇〇八支撑FO中华VXML的四种模式,分别是RAW情势,AUTO方式,EXPLICIT情势和PATH格局。

1.4.Transact-SQL语言功底

FOR XML RAW

将表转换来成分名称是row,属性名称为列名或许列的别名。
示例9:将Student表转换为XML格式(FO奥德赛 XML RAW)
Student表的数据如图所示
图片 7
履行语句:

SELECT * FROM Student FOR XML RAW;

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

1.4.1.常量与变量

常量不多说。在SQL Server
二〇〇八中,存在三种变量。一种是系统定义和保卫安全的全局变量,一种是用户定义用来保存中间结果的一部分变量。

FOR XML AUTO

接纳表名称作为成分名称,使用列名称作为质量名称,SELECT关键字前边列的逐条用于XML文书档案的层系。
示例10:将Student表转换为XML格式(FO陆风X8 XML AUTO)
实践语句:

SELECT * FROM Student FOR XML AUTO;

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

1.4.1.1.种类全局变量

系统全局变量分为两大类,一类是与自然SQL
Server连接或与最近处理有关的全局变量,如@@Rowcount意味着方今3个讲话影响的行数。@@error代表保留近期实践操作的错误状态。一类是与成套SQL
Server系统有关的全局变量,如@@Version代表如今SQL Server的版本新闻。

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

结果如图所示
图片 12

FOR XML EXPLICIT

同意用户显式地定义XML树的形象,不受AUTO方式中的各种限制。不可能将FO锐界 XML
EXPLICIT直接用在SELECT子句中。
示例11:将xmlTest表转换为XML格式(FOPRADO XML EXPLICIT)
XmlTest表的数码如图所示
图片 13

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

询问结果如图所示
图片 14
图片 15
在结果图中大家发现,红框中三个班级信息列在一齐,而享有学生都列在高级中学一年级3班下,那不是大家想要的结果,大家期望各样班级对应本人的学员。那么怎么样解决此类难点吧,那提到到排序。

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

先是删除代码行末的FOLX570 XML
EXPLICIT语句,仅仅执行剩下的部分,使结果以表格格局展现,那么结果如下
图片 16
以此表格每行的逐条也意味了该表格转化为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语句,看看表格中数量内容和行顺序是或不是改变
图片 17
如图所示,发现行数据和学员数量的逐条展现正确。运转具有代码获得XML文书档案,结果如图所示
图片 18
是因为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的代码中先安装层级结构,不要先急着赋值。在部属层级的代码中对层级第11中学的代码进行赋值,最好重复赋值,不然就会并发文中的排序难题。假若有些层级出现重复数据,在该层级的代码前加DISTINCT关键字。解决排序难点最好的法子是对一一层级的习性重复赋值并在最终用O奥迪Q7DER
BY按层级属性排序。

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

1.4.1.2.局部变量

部分变量能够享有一定数据类型,有自然的功效域,一般用于充当计数器计算或决定循环执行次数,只怕用于保存数据值。局地变量前唯有一个@符,用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

结果如图所示
图片 20

FOR XML PATH

PATH方式提供了一种较简单的主意来混合成分及品质。在PATH情势中,列名或列别名被用作XPATH表明式来拍卖,那几个表明式钦点了哪些将值映射到XML中。暗许意况下,PATH格局为每一样自动生成

2.运算符

从没称谓的列

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

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

询问结果如图所示
图片 21

注:假设提供了空字符串FO库罗德 XML PATH(‘’)则不会转变任何因素。

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

查询结果如图所示
图片 22
示例12:利用xmlTest表和mainTeacher表查询出xmlTest表中战表>=700分的学生的班老板消息和学生新闻,并转化成XML格式
XmlTest表数据如下图所示
图片 23
MainTeacher表数据如下图所示
图片 24
实行上边包车型地铁言语

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.1.算数运算符

在SQL Server
2009中,算数运算包蕴加(+)减(-)乘(*)除(/)取模(%)。举一个粗略的事例。
示例1:在Student表中添加一列,列名为stu_age,根据Student表的stu_birthday列计算stu_age列并插入数据。(演示插入整列数据的格局)
Student表数据如图所示
图片 25
执行上面包车型地铁讲话

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

结果如图所示
图片 26

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表

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

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

2.2.赋值运算符

即等号(=),将表明式的值赋予另一个变量。举三个简单易行的事例。
示例2:总计Student表中学生的平均入学成绩并打字与印刷。
Student表的数码如图所示,stu_enter_score列存放了学员的入学成绩
图片 28
实践上面包车型地铁口舌

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

结果如图所示
图片 29

FOLAND 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方法,查询结果如下图所示
图片 30

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

2.3.位运算符

位运算符包含与运算(&),或运算(|)和异或运算(^),能够对五个说明式举办位操作,那五个表明式能够是整型数据或二进制数据。Transact-SQL首先把整型数据转换为二进制数据,然后按位运算。举个简单的事例。
示例3:证明三个int型变量@num1,@num2,对那四个赋值且做与或异或运算。
执行下边的讲话

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

结果如图所示
图片 31
推而广之示例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,函数定义正确。

XML索引

由于XML数据类型最大可存款和储蓄2GB的数码,由此需求成立XML索引来优化查询品质。

2.4.比较运算符

也称关系运算符,用于比较八个值的涉及,常见的有等于(=),大于(>),小于(<),大于等于(>=),小于等于(<=),不对等(<>或!=)
示例5:从Student表中查询入学成绩在平均分以上的学习者消息
Student表的多少如图所示
图片 32
实施下列语句

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

结果如下图所示
图片 33

注:不能够直接把代码写成上面的情势

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

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

因为AVG是聚合函数。

主XML索引

主XML索引对XML列中XML实例内的有着标记,值和路径进行索引。创设主XML索引时,相应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列(入学战绩)数据如图所示
图片 34
实践上边包车型大巴言语

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

结果如图所示
图片 35

辅助XML索引

为了提升主XML索引的属性,能够创制援救XML索引。唯有创立了主XML索引后才能创造辅助XML索引。帮助XML索引分3种:PATH,VALUES和PROPE瑞鹰TY协助XML索引。

2.6.连接运算符

加号(+)是字符串连接运算符,能够用它把字符串串连起来,在示例4的十进制转二进制函数中,就用上了加号。
示例7:将Student表的stu_name列和stu_enter_score列放在同样列展现,列名为score
Student表的数额如图所示
图片 36
施行下列语句

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

实践结果如图所示
图片 37

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

始建索引

为表中某些列成立索引,必要该列是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.7.一元运算符

一元运算符只对贰个表达式执行操作,该表达式能够是数字数据类型中的任何一种数据类型。SQL
Server 二零零六提供的一元运算符包涵正(+),负(-),位反(~)。
示例8:声爱他美(Aptamil)(Karicare)个int数据类型变量@num并赋值,对该变量做正负位反操作。
施行下列语句

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

结果如图所示
图片 38

注:位反操作符用于取二个数的补数,只好用来整数。

修改和删除索引(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

注:删除主索引,与其连带的具有扶助索引也会被删去。由此地点语句中剔除学生音讯表索引后,协助学生音讯表索引也被剔除了。

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

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

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)
);

结果如图所示
图片 40
在上述语句中,sp_xml_preparedocument存储进度语句用了一个参数,当中@Student是3个int型变量,该存款和储蓄进程会将句柄存款和储蓄在@Student变量中作为结果数据,@StudentInfo是三个XML类型的变量,存款和储蓄了就要实行解析的XML文档。
OPENXML函数的话语中,使用了一个参数,个中@Student代表曾经因此sp_xml_preparedocument存储进程分析的文书档案的句柄,’/row’使用XPath方式提供了3个途径,代表要回来XML文书档案中该路线下的多少行,2是二个可选数据参数,表示将那个数量行以成分为着力映射。

3.控制语句

3.1.BEGIN END语句块

BEGIN END能够定义SQL
Server语句块,使那么些话语作为一组语句执行,允许语句嵌套。举例请见示例4

3.2.IF ELSE语句块

用来钦命T-SQL语句的履行基准,若条件为真,则执行规则表明式前边的话语,条件为假时,能够试用ELSE关键字钦定要实施的T-SQL语句。举例请见示例4

3.3.CASE分支语句

示例10:将Student表的学员,性别和原籍打字与印刷出来,须要籍贯只可以展现本省,省里或自治区。
Student表的数量如图所示
图片 41
推行下列语句

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

结果如图所示
图片 42

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

结果如图所示
图片 43

3.5.WAITFO奥迪Q5延迟语句

WAITFO酷路泽延迟语句能够让在它之后的话语在二个内定的每一日恐怕时间距离后执行,能够悬挂起批处理,存款和储蓄进度或工作的履行。
示例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

图片 44

3.6.RETU奇骏N无条件退出语句

该语句表示无条件结束查询,批处理或存款和储蓄进程的实施。存款和储蓄进程和批处理RETU哈弗N语句后边的口舌都不再履行。当在存款和储蓄进度中动用该语句时,能够钦点再次来到给调用应用程序、批处理或进度的整数值。假若RETUKugaN未钦命重返值,则存款和储蓄进程的重返值是0

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.';

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

注:在WHILE循环中接纳GOTO会打破循环。

示例14:用GOTO语句完毕示例1第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

结果如图所示
图片 46

3.8.TRAV4Y CATCH错误处理语句

假如T奥迪Q3Y块内部产生错误,会将决定传递给CATCH块内的语句组。T奥迪Q5Y
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

施行结果如图所示
图片 47
语句中二个select语句全体都实施了。即使把报错的select语句放到经常的select语句后边,正常的select语句还是能无法实施呢?执行下列语句

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

结果如图所示
图片 48
不奇怪select语句不可能履行。T汉兰达Y
CATCH语句的逻辑是,一旦TRAV4Y语句块中出现难点讲话,立即跳转到CATCH语句块,T大切诺基Y语句块接下去的语句不再实施。

4.常用函数

4.1.数据类型转换函数

私下认可景况下SQL
Server会对有些数据类型实行机动转换,这种转移称为隐式转换。蒙受不只怕自行转换,则必要用CAST()函数和CONVERT()函数转换,那种转移称为显式转换。CAST()函数和CONVERT()函数的意义是一致的,CAST函数更便于选用,CONVECRUISERT函数的帮助和益处是能够内定日期和数值格式。
示例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

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

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

结果如图所示
图片 50

注:在上述代码中,CONVE索罗德T(DATE,stu_birthday,101)这么写是没用的。101格式码只对日期格式转化为字符串有效,其余格式转化为日期格式是不行的。

其余常用函数太不难了此地不写了,略。

留下评论

网站地图xml地图