创建并初始化Student表

创建并初始化Student表
CREATE TABLE Student
(
    StudentID int, --学生id
    ClassID int, --班级编号
    Mark int --成绩
);
GO
--接下来插入示例数据
INSERT INTO Student VALUES(1,1,90);
INSERT INTO Student VALUES(2,1,84);
INSERT INTO Student VALUES(3,1,80);
INSERT INTO Student VALUES(4,1,80);
INSERT INTO Student VALUES(5,1,90);
INSERT INTO Student VALUES(6,1,76);
INSERT INTO Student VALUES(7,1,89);
INSERT INTO Student VALUES(11,2,90);
INSERT INTO Student VALUES(12,2,82);
INSERT INTO Student VALUES(13,2,80);
INSERT INTO Student VALUES(14,2,80);
INSERT INTO Student VALUES(15,2,90);
INSERT INTO Student VALUES(16,2,75);
INSERT INTO Student VALUES(17,2,89);

使用RANK()函数排序
SELECT *
    ,RANK() OVER(  --使用RANK函数进行排名
    PARTITION BY ClassID --使用ClassID进行分组
    ORDER BY Mark DESC) AS [Rank] --使用Mark进行排序
FROM Student

使用DENSE_RANK函数进行排名
SELECT *
    ,DENSE_RANK() --使用DENSE_RANK()函数进行排名
    OVER(PARTITION BY ClassID --使用ClassID进行分组
    ORDER BY Mark DESC) AS [Rank] --依据Mark列排序
FROM Student

使用NTILE()函数进行分组
SELECT *
,NTILE(2) OVER(ORDER BY Mark DESC) AS NewClass --使用NTILE函数对Mark排名
FROM Student

使用ROW_NUMBER()函数获得排名
SELECT *
,ROW_NUMBER ( ) OVER(ORDER BY Mark DESC) AS OrderID--使用函数对Mark进行排名
FROM Student

--
--13.1.2节示例
--

CREATE TABLE t1
(
c1 int PRIMARY KEY,
c2 varchar(50)
)
GO
INSERT INTO t1 VALUES(1,'good'); --插入测试数据
GO
--以下异常处理代码单独执行：
BEGIN TRY
INSERT INTO t1 VALUES(1,'same')
END TRY
BEGIN CATCH --捕捉到异常后进行处理
SELECT ERROR_LINE(),ERROR_SEVERITY(),ERROR_MESSAGE() --输出异常内容
END CATCH

--
--13.1.3节示例
--

使用APPLY
SELECT p.usecounts, p.cacheobjtype, p.objtype, s.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) s --一个表与表值函数进行APPLY操作

--
--13.1.4节示例
--

创建并初始化产品销售表
CREATE TABLE ProductSale --创建测试表和测试数据
(
ID int,
Name varchar(20),
Quarter int,
Sale int
)
insert into ProductSale values(1,'a',1,1000)
insert into ProductSale values(1,'a',2,2000)
insert into ProductSale values(1,'a',3,4000)
insert into ProductSale values(1,'a',4,5000)
insert into ProductSale values(2,'b',1,3000)
insert into ProductSale values(2,'b',2,3500)
insert into ProductSale values(2,'b',3,4200)
insert into ProductSale values(2,'b',4,5500)

使用PIVOT行转列
SELECT ID,Name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
FROM
ProductSale
PIVOT --进行行转列操作
(
sum(Sale)
for Quarter in
([1],[2],[3],[4])
)
as pvt

使用UNPIVOT
SELECT ID,Name,Quarter,Sale
FROM ProductSale2
UNPIVOT --使用UNPIVOT列转行
(
Sale
for Quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt

--
--13.1.5节示例
--

使用OUTPUT显示删除的数据
CREATE TABLE Student  --创建测试表
(
    StudentID int, --学生id
    ClassID int, --班级编号
    Mark int --成绩
);
GO
--创建测试数据
INSERT INTO Student VALUES(1,1,90);
INSERT INTO Student VALUES(2,1,84);
INSERT INTO Student VALUES(3,1,80);
INSERT INTO Student VALUES(4,1,80);
INSERT INTO Student VALUES(5,1,90);
INSERT INTO Student VALUES(6,1,76);
INSERT INTO Student VALUES(7,1,89);
INSERT INTO Student VALUES(11,2,90);
INSERT INTO Student VALUES(12,2,82);
INSERT INTO Student VALUES(13,2,80);
INSERT INTO Student VALUES(14,2,80);
INSERT INTO Student VALUES(15,2,90);
INSERT INTO Student VALUES(16,2,75);
INSERT INTO Student VALUES(17,2,89);
--以上是初始化数据，接下来要删除一些数据
DELETE FROM Student
OUTPUT deleted.* --将删除的数据输出
WHERE StudentID = 1;

OUTPUT输出INSERT结果
INSERT INTO Student
OUTPUT INSERTED.* --输出插入的行
VALUES(1,1,91)

OUTPUT输出UPDATE结果
UPDATE Student
SET Mark=90
OUTPUT
DELETED.StudentID AS OldStudentID, --输出更新操作时原来的数据
DELETED.ClassID AS OldClassID,
DELETED.Mark AS OldMark,
INSERTED.StudentID AS NewStudentID, --输出更新后的数据
INSERTED.ClassID AS NewClassID,
INSERTED.Mark AS NewMark
WHERE StudentID=1

删除数据到备份表
CREATE TABLE StudentDeleted
(
    StudentID int, --学生id
    ClassID int, --班级编号
    Mark int --成绩
);
GO
DELETE FROM Student
OUTPUT deleted.* INTO StudentDeleted
--将数据从Student表删除，删除的数据插入到StudentDeleted表中
WHERE StudentID = 1;

--
--13.1.6节示例
--

使用CTE和ROW_NUMBER()进行数据库分页
WITH c AS --定义CTE
(
SELECT *,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowID
FROM Sales.vIndividualCustomer
WHERE CountryRegionName='United States'
)
SELECT *
FROM c --使用CTE
WHERE RowID>50 AND RowID<=60

使用CTE递归查询
USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level) --定义CTE中的查询和输出列
AS
(
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, --CTE中的查询
        0 AS Level
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL --进行联合操作
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d --这里递归调用
        ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports --查询CTE
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;