代码改变世界

《SQL Server 2008 从入门到精通》 学习笔记 第三天

2012-02-02 15:43  CodeCy  阅读(554)  评论(0)  编辑  收藏  举报

分区

分区函数

use SQL2008SBS

go

create partition function

mypartfunction (int)

as range left

for values (10,20,30,40,50,60)

 

对新表进行分区

clip_image002

View Code 
USE SQL2008SBS

GO

CREATE SCHEMA Archive AUTHORIZATION dbo

GO

ALTER DATABASE SQL2008SBS

ADD FILEGROUP FG2

ALTER DATABASE SQL2008SBS

ADD FILEGROUP FG3

GO

ALTER DATABASE SQL2008SBS

ADD FILE

(NAME = FG2_dat, FILENAME = 'D:\Documents\SQL Server\Data\SQL2008SBS_4.ndf', SIZE = 2MB)

TO FILEGROUP FG2

ALTER DATABASE SQL2008SBS

ADD FILE

(NAME = FG3_dat, FILENAME = 'D:\Documents\SQL Server\Data\SQL2008SBS_5.ndf', SIZE = 2MB)

TO FILEGROUP FG3

GO

CREATE PARTITION FUNCTION shipdatepartfunc (date)

AS

RANGE RIGHT

FOR VALUES ('1/1/2007','1/1/2008')

GO

CREATE PARTITION SCHEME shipdatepartscheme

AS

PARTITION shipdatepartfunc

TO

(FG1, FG2, FG3)

GO

--We want the primary key to be nonclustered so that

-- we can create a clustered index to partition the table

CREATE TABLE Archive.OrderDetail(

OrderDetailID INT IDENTITY(1,1) NOT NULL,

OrderID INT NOT NULL,

SKU CHAR(10) NOT NULL,

Quantity INT NOT NULL,

UnitPrice MONEY NOT NULL,

ShipDate DATE NOT NULL)

ON shipdatepartscheme(ShipDate)

GO

CREATE CLUSTERED INDEX icx_shipdate

ON Archive.OrderDetail(ShipDate)

ON shipdatepartscheme(ShipDate)

GO

ALTER TABLE Archive.OrderDetail

ADD CONSTRAINT pk_orderdetail PRIMARY KEY NONCLUSTERED (OrderDetailID, ShipDate)

ON shipdatepartscheme(ShipDate)

GO

CREATE TABLE Archive.OrderHeader(

OrderID INT IDENTITY(1,1) NOT NULL,

CustomerID INT NOT NULL,

OrderDate DATE NOT NULL,

SubTotal MONEY NOT NULL,

TaxAmount MONEY NOT NULL,

ShippingAmount MONEY NOT NULL,

GrandTotal AS ((SubTotal+TaxAmount)+ShippingAmount),

FinalShipDate DATE NOT NULL)

ON shipdatepartscheme(FinalShipDate)

GO

ALTER TABLE Archive.OrderHeader

ADD CONSTRAINT pk_orderheader PRIMARY KEY NONCLUSTERED(OrderID, FinalShipDate)

ON shipdatepartscheme(FinalShipDate)

GO

CREATE CLUSTERED INDEX icx_finalshipdate

ON Archive.OrderHeader(FinalShipDate)

ON shipdatepartscheme(FinalShipDate)

GO

 

管理分区

clip_image004

View Code 
USE SQL2008SBS

GO

CREATE PARTITION FUNCTION partfunc (datetime)

AS

RANGE RIGHT

FOR VALUES ('1/1/2005','1/1/2006')

GO

CREATE PARTITION SCHEME partscheme

AS

PARTITION partfunc

TO

(FG1, FG2, FG3)

GO

CREATE TABLE dbo.orders (

OrderID int identity(1,1),

OrderDate datetime NOT NULL,

OrderAmount money NOT NULL

CONSTRAINT pk_orders PRIMARY KEY CLUSTERED (OrderDate,OrderID))

ON partscheme(OrderDate)

GO

SET NOCOUNT ON

DECLARE @month int = 1,

@day int = 1,

@year int = 2005

WHILE @year < 2007

BEGIN

WHILE @month <= 12

BEGIN

WHILE @day <= 28

BEGIN

INSERT dbo.orders (OrderDate, OrderAmount)

SELECT CAST(@month AS VARCHAR(2)) + '/' + CAST(@day AS VARCHAR(2)) + '/' + CAST(@year AS VARCHAR(4)), @day * 20

SET @day = @day + 1

END

SET @day = 1

SET @month = @month + 1

END

SET @day = 1

SET @month = 1

SET @year = @year + 1

END

GO

--查看单个分区中的数据

SELECT * FROM dbo.orders

WHERE $partition.partfunc(OrderDate)=3

GO

--更改分区函数，引入新的范围并且设置下一个被使用的文件组

ALTER PARTITION SCHEME partscheme

NEXT USED FG1;

GO

ALTER PARTITION FUNCTION partfunc()

SPLIT RANGE ('1/1/2007');

GO

--创建一张切换年数据的表，并且查看两表的数据

CREATE TABLE dbo.ordersarchive (

OrderID int NOT NULL,

OrderDate datetime NOT NULL

CONSTRAINT ck_orderdate CHECK (OrderDate<'1/1/2006'),

OrderAmount money NOT NULL

CONSTRAINT pk_ordersarchive PRIMARY KEY CLUSTERED (OrderDate,OrderID)

)

ON FG2

GO

SELECT * FROM dbo.orders

SELECT * FROM dbo.ordersarchive

GO

--将年的数据切换到归档表，并查看结果

ALTER TABLE dbo.orders

SWITCH PARTITION 2 TO dbo.ordersarchive

GO

SELECT * FROM dbo.orders

SELECT * FROM dbo.ordersarchive

GO

--删除为指定的边界点

ALTER PARTITION FUNCTION partfunc()

MERGE RANGE ('1/1/2005');

GO

--删除相关测试对象，对数据库进行清理

DROP TABLE dbo.orders

DROP TABLE dbo.ordersarchive

DROP PARTITION SCHEME partscheme

DROP PARTITION FUNCTION partfunc

GO

 

数据检索与操作

数据的检索

通用select语句

select 1

select 'select 1'

select ' this is a character constant', ' this is another character constant', 1, '7/27/2008'

select * from Person.Address

select AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode from Address

 

--COUNT函数

select COUNT(*) from Address

 

--CASE 函数

select ShipMethodID,case ShipMethodID

when 1 then 'A.Datum'

when 2 then 'Contoso'

when 3 then 'Consolidated Messenger'

else 'Unknow'

end

from Purchasing.PurchaseOrderHeader r

 

结果排序

--order by

select addressID, AddressLine1 + '' + ISNULL(AddressLine2,''),City,StateProvinceID

from Address

order by addressID

 

多个表中索引数据

clip_image006

USE AdventureWorks

GO

--数据筛选

select productID, Name , ProductNumber, ListPrice, DaysToManufacture

from Production.Product

where productSubCategoryID in (1,2,2)

select productID, Name , ProductNumber, ListPrice, DaysToManufacture

from Production.Product

where name like 's_M%'

 

--NULL

select productID, Name , ProductNumber, ListPrice, DaysToManufacture

from Production.Product

where Color is null

select productID, Name , ProductNumber, ListPrice, DaysToManufacture

from Production.Product

where Color is not null

 

--inner join 内联排除两个表中不满足条件的任意数据

select firstName ,LastName , b.Title

from Person.Contact a inner join HumanResources.Employee b on a.ContactID = b.ContactID

 

--outer join 外联接联合两个表同时保留来自于其中一方的所有数据行left outer join 保存左侧表中所有数据，只追加右侧表中存在的值，反之right outer join.....

select a.ProductID, a.Name , b.SalesOrderID

from Production.Product a Left outer join sales.SalesOrderDetail b

on a.ProductId = b.ProductID

where a.Color = 'Black'

Order by 1

 

检索唯一结果

clip_image008

use AdventureWorks

go

--检索唯一结果

select b.ProductID,b.Name,b.ListPrice,a.UnitPrice as 'Sales Price'

from Sales.SalesOrderDetail as a

Join Production.Product as b

on a.ProductID=b.ProductID and a.UnitPrice<b.ListPrice

where b.ProductID = 718

select COUNT(*) from HumanResources.Employee

select COUNT(ManagerID) from HumanResources.Employee

--DISTINCT

select COUNT(DISTINCT ManagerID) from HumanResources.Employee

 

从表返回数据 利用带有from子句的select 语句

基于sarg筛选结果 包含where 子句

多个表返回结 在from 子句中使用inner join ,,left outer join ,right outer join 和 full outer join

结构集排序 包含一个order by 子句

返回唯一行集 在select子句中加入distinct关键字

返回结果集的初始子集 在select 子句中加入top 和 top percent 关键字

高级数据检索

聚合函数

clip_image010

group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面

--聚合数据

use AdventureWorks

go

select Title,COUNT(*)

from HumanResources.Employee

group by Title

 

--派生表

select Color,COUNT(*)

from Production.Product

Group by Color

 

聚合多个序列

clip_image012

use AdventureWorks

go

select CustomerID,SalesPersonID,TerritoryID,YEAR(OrderDate) OrderYear, MONTH(OrderDate) OrderMonth, SUM(TotalDue) Total

from Sales.SalesOrderHeader

group by CustomerID,SalesPersonID,TerritoryID,YEAR(OrderDate),MONTH(OrderDate)

with CUBE

 

grouping sets 新特性

clip_image014

--Grouping sets 新特性

SELECT CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate) OrderYear, MONTH(OrderDate) OrderMonth, SUM(TotalDue) Total,

GROUPING(SalesPersonID) SalesPersonGroup

FROM Sales.SalesOrderHeader

GROUP BY CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate), MONTH(OrderDate)

WITH CUBE

GO

SELECT S.Name StoreName, ST.Name TerritoryName,

ST.CountryRegionCode, ST.[Group],

P.FirstName + ' ' + P.LastName SalesPerson, SUM(SH.TotalDue) SalesTotal

FROM Sales.Customer C

INNER JOIN Sales.Store S

ON C.StoreID = S.BusinessEntityID

INNER JOIN Sales.SalesTerritory ST ON C.TerritoryID = ST.TerritoryID

INNER JOIN Sales.SalesPerson SP ON S.SalesPersonID = SP.BusinessEntityID

INNER JOIN Person.Person P ON S.SalesPersonID = P.BusinessEntityID

INNER JOIN Sales.SalesOrderHeader SH ON C.CustomerID = SH.CustomerID

GROUP BY GROUPING SETS((P.FirstName + ' ' + P.LastName),(S.Name),(ST.Name), (ST.Name,ST.CountryRegionCode), (ST.[Group]))

GO

 

聚合筛选

clip_image016

use AdventureWorks

go

--错误做法

select SalesOrderID,SUM(LineTotal) as SubTotal

from Sales.SalesOrderDetail

where LineTotal > 30000

group by SalesOrderID

order by SalesOrderID

 

--Having

select SalesOrderID,SUM(LineTotal) as SubTotal

from Sales.SalesOrderDetail

group by SalesOrderID

having SUM(LineTotal) > 30000

 

--更高效

select SalesOrderID,SUM(LineTotal) as SubTotal

from Sales.SalesOrderDetail

where SalesOrderID > 45000

group by SalesOrderID

having SUM(LineTotal) > 45000

order by SalesOrderID

 

运行聚合

clip_image018

USE AdventureWorks

GO

SELECT SH3.SalesPersonID, SH3.OrderDate, SH3.DailyTotal, SUM(SH4.DailyTotal) RunningTotal

FROM (SELECT SH1.SalesPersonID, SH1.OrderDate, SUM(SH1.TotalDue) DailyTotal

FROM Sales.SalesOrderHeader SH1

WHERE SH1.SalesPersonID IS NOT NULL

GROUP BY SH1.SalesPersonID, SH1.OrderDate) SH3

INNER JOIN (SELECT SH2.SalesPersonID, SH2.OrderDate, SUM(SH2.TotalDue) DailyTotal

FROM Sales.SalesOrderHeader SH2

WHERE SH2.SalesPersonID IS NOT NULL

GROUP BY SH2.SalesPersonID, SH2.OrderDate) SH4

ON SH3.SalesPersonID = SH4.SalesPersonID

AND SH3.OrderDate >= SH4.OrderDate

GROUP BY SH3.SalesPersonID, SH3.OrderDate, SH3.DailyTotal

ORDER BY SH3.SalesPersonID, SH3.OrderDate

GO

 

计算透视表

clip_image020

USE AdventureWorks

GO

--行数据转换为列数据PIVOT

SELECT VendorID, [2001], [2002], [2003], [2004]

FROM (SELECT VendorID, PurchaseOrderID, YEAR(OrderDate) ChangeYear

FROM Purchasing.PurchaseOrderHeader) r

PIVOT

(COUNT(r.PurchaseOrderID)

FOR ChangeYear

IN ([2001],[2002],[2003],[2004]))

AS Results

ORDER BY VendorID

 

--IN 指定参与聚合的列

SELECT VendorID, [2003]

FROM (SELECT VendorID, PurchaseOrderID, YEAR(OrderDate) ChangeYear

FROM Purchasing.PurchaseOrderHeader) r

PIVOT

(COUNT(r.PurchaseOrderID)

FOR ChangeYear

IN ([2003]))

AS Results

ORDER BY VendorID

GO

 

数据的排名

USE AdventureWorks

Go

--ROW_NUMBER() 函数对结果集进行连续的编号（从到n）

View Code 
SELECT p.FirstName, p.LastName,

ROW_NUMBER() OVER(ORDER BY s.SalesYTD DESC) AS 'RowNumber',

s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson s INNER JOIN Person.Person p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress ba ON p.BusinessEntityID = ba.BusinessEntityID

INNER JOIN Person.Address a ON a.AddressID = ba.AddressID

WHERE s.TerritoryID IS NOT NULL

GO

 

--PARTITION BY 子句，将结果分组后对组内的数据进行编号

View Code 
SELECT p.FirstName, p.LastName

,ROW_NUMBER() OVER (PARTITION BY s.TerritoryID ORDER BY SalesYTD DESC) AS 'RowNumber',

s.SalesYTD, s.TerritoryID

FROM Sales.SalesPerson s INNER JOIN Person.Person p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress ba ON p.BusinessEntityID = ba.BusinessEntityID

INNER JOIN Person.Address a ON a.AddressID = ba.AddressID

WHERE s.TerritoryID IS NOT NULL

GO

 

--RANK函数处理ORder by 子句指定值有重复结果集

View Code 
SELECT a.ProductID, b.Name, a.LocationID, a.Quantity

,RANK() OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) AS 'Rank'

FROM Production.ProductInventory a INNER JOIN Production.Product b

ON a.ProductID = b.ProductID

ORDER BY b.Name;

SELECT a.ProductID, b.Name, a.LocationID, a.Quantity

,RANK() OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) AS 'Rank'

FROM Production.ProductInventory a INNER JOIN Production.Product b

ON a.ProductID = b.ProductID

ORDER BY 'Rank';

GO

 

--DENSE_RANK 函数对结果集进行编号为重复值指定相同的排名会消除排序列中的间断

View Code 
SELECT a.ProductID, b.Name, a.LocationID, a.Quantity

,DENSE_RANK() OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) AS 'DenseRank'

FROM Production.ProductInventory a INNER JOIN Production.Product b

ON a.ProductID = b.ProductID

ORDER BY b.Name;

SELECT a.ProductID, b.Name, a.LocationID, a.Quantity

,DENSE_RANK() OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) AS DenseRank

FROM Production.ProductInventory a INNER JOIN Production.Product b

ON a.ProductID = b.ProductID

ORDER BY DenseRank;

GO

 

聚合结果集

View Code 
USE AdventureWorks

GO

SELECT p.FirstName, p.LastName,

NTILE(4) OVER(ORDER BY s.SalesYTD DESC) AS QuarterGroup,

s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson s INNER JOIN Person.Person p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress ba ON p.BusinessEntityID = ba.BusinessEntityID

INNER JOIN Person.Address a ON a.AddressID = ba.AddressID

WHERE s.TerritoryID IS NOT NULL

SELECT p.FirstName, p.LastName,

NTILE(2) OVER(PARTITION BY s.TerritoryID ORDER BY s.SalesYTD DESC) AS QuarterGroup,

s.SalesYTD, s.TerritoryID

FROM Sales.SalesPerson s

INNER JOIN Person.Person p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress ba ON p.BusinessEntityID = ba.BusinessEntityID

INNER JOIN Person.Address a ON a.AddressID = ba.AddressID

WHERE s.TerritoryID IS NOT NULL

GO

SELECT p.LastName, p.FirstName

FROM Person.Person p JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

WHERE e.BusinessEntityID = 2

UNION

SELECT p.LastName, p.FirstName

FROM Person.Person p JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

WHERE e.BusinessEntityID = 2

UNION

SELECT p.LastName, p.FirstName

FROM Person.Person p JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

WHERE e.BusinessEntityID = 2

GO

SELECT p.LastName, p.FirstName

FROM Person.Person p JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

WHERE e.BusinessEntityID = 2

UNION ALL

SELECT p.LastName, p.FirstName

FROM Person.Person p JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

WHERE e.BusinessEntityID = 2

UNION ALL

SELECT p.LastName, p.FirstName

FROM Person.Person p JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

WHERE e.BusinessEntityID = 2

GO

SELECT p.LastName, p.FirstName

FROM Person.Person p JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

WHERE e.BusinessEntityID = 2

UNION

SELECT p.LastName, p.FirstName

FROM Person.Person p JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

WHERE e.BusinessEntityID = 2

UNION ALL

SELECT p.LastName, p.FirstName

FROM Person.Person p JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

WHERE e.BusinessEntityID = 2

GO

 

--INTERSECT 比较不同的结果集

View Code 
SELECT ProductID

FROM Production.Product

INTERSECT

SELECT ProductID

FROM Production.ProductDocument

GO

 

--等同于下面的联合查询

View Code 
SELECT a.ProductID

FROM Production.Product a INNER JOIN Production.ProductDocument b

ON a.ProductID = b.ProductID

GO

 

--EXCEPT 从左查询中返回右查询没有找到的所有非重复值

--以下查询将找出所有没有文档的产品

View Code 
select *

from Production.Product

EXCEPT

select ProductID

from Production.ProductDocument

公用表表达式

USE AdventureWorks

GO

WITH EMP_cte(BusinessEntityID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel)

AS (SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0

FROM HumanResources.Employee e INNER JOIN Person.Person as p

ON p.BusinessEntityID = e.BusinessEntityID

UNION ALL

SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1

FROM HumanResources.Employee e

INNER JOIN EMP_cte ON e.OrganizationNode = EMP_cte.OrganizationNode.GetAncestor(1)

INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID)

SELECT EMP_cte.RecursionLevel, EMP_cte.BusinessEntityID, EMP_cte.FirstName, EMP_cte.LastName,

EMP_cte.OrganizationNode.ToString() AS OrganizationNode, p.FirstName AS 'ManagerFirstName', p.LastName AS 'ManagerLastName'

FROM EMP_cte INNER JOIN HumanResources.Employee e

ON EMP_cte.OrganizationNode.GetAncestor(1) = e.OrganizationNode

INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID

ORDER BY RecursionLevel, EMP_cte.OrganizationNode.ToString()

OPTION (MAXRECURSION 25)

GO

 

查询XML数据

clip_image022

USE AdventureWorks

GO

SELECT a.Name,a.ProductNumber,b.Instructions

FROM Production.Product a INNER JOIN Production.ProductModel b

ON a.ProductModelID = b.ProductModelID

WHERE b.Instructions IS NOT null

 

--使用auery()方法获得标准行

View Code 
SELECT a.Name,a.ProductNumber,

b.Instructions.query('declare

namespace AW="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";

AW:root/AW:Location[@MachineHours>2.5]') Locations

FROM Production.Product a INNER JOIN Production.ProductModel b

ON a.ProductModelID = b.ProductModelID

WHERE b.Instructions IS NOT null

 

--不考虑校对设置

View Code 
SELECT a.Name, a.ProductNumber,

b.Instructions.query('declare

namespace AW="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";

AW:root/AW:Location[@machineHours>2.5]') Locations

FROM Production.Product a INNER JOIN Production.ProductModel b

ON a.ProductModelID = b.ProductModelID

WHERE b.Instructions IS NOT NULL

GO

 

--用于精确地格式化xml数据

View Code 
SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID IN (119,122)

FOR XML RAW;

GO

 

--ELEMENTS 获得以元素为中心的xml数据

View Code 
SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID IN (119,122)

FOR XML RAW, ELEMENTS;

GO

 

--使用TYPE 返回XML数据类型而不是字符串格式

View Code 
SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID IN (119,122)

FOR XML RAW, ELEMENTS, TYPE;

GO

 

--获取xml结果集时，null值自动丢弃

View Code 
SELECT ProductID, Name, Color

FROM Production.Product

FOR XML RAW, ELEMENTS;

GO

 

--通过xsnil指令解决上面问题

View Code 
SELECT ProductID, Name, Color

FROM Production.Product

FOR XML RAW, ELEMENTS XSINIL

GO

 

--FOR XML 子句可以返回一个XML构架

View Code 
SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID IN (119,122)

FOR XML RAW, XMLSCHEMA

GO

 

--AUTO指令提供简单而直接的方法从属性为中心的xml中返回数据

View Code 
SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID IN (119,122)

FOR XML AUTO

GO

 

对结果集进行简单的聚合

使用GROUP BY 子句

对结果集进行多级别的聚合

在group by 子句中添加Grouping sets,cube/rollup操作符

筛选聚合

用Having 子句指定聚合的筛选标准

计算透视表

使用PIVOT操作符

将多个结果结合在一起

使用UNION操作符将两个或多个重复结果合并为单个结果集，删除重复行

使用INTERSECT 操作符返回其左边两边的两个查询都返回所有非重复值

使用EXCEPT 操作符从左边查询中返回右查询没有找到的所有非重复值

执行优化的递归查询

指定公用表表达式(CET)

查询一个XML文档

使用query()方法

返回XML格式的结果

根据用户需求使用AUTO,RAW,PATH或EXPLICIT操作符指定FOR XML子句