当前位置：Gxlcms > 数据库问题 > SQL Server2012 T-SQL基础教程--读书笔记

SQL Server2012 T-SQL基础教程--读书笔记

时间：2021-07-01 10:21:17 帮助过：96人阅读

USE TSQL2012;

IF OBJECT_ID(‘dbo.Employees‘, ‘U‘) IS NOT NULL

DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees

(

empid INT NOT NULL,

firstname VARCHAR(30) NOT NULL,

lastname VARCHAR(30) NOT NULL,

hiredate DATE NOT NULL,

mgrid INT NULL,

ssn VARCHAR(20) NOT NULL,

salary MONEY NOT NULL

);

在创建对象脚本中包含USE语句是十分重要，能确保在指定的数据库中创建对象。

1.3.2 定义数据的完整性

1. 主键约束

主键约束强制行的唯一性，在约束的属性中不允许使用NULL标记。约束的属性的值必须是唯一的，每个表只能有一个主键。为了强制逻辑主键约束的一唯一性，SQL Server会在后台创建一个唯一索引。唯一索引是SQL Server为了强制唯一性所使用的一种物理机制。

1ALTER TABLE dbo.Employees
2 ADD CONSTRAINT PK_Employees
3 PRIMARY KEY(empid)

2. 唯一约束

唯一约束强制行的唯一性，允许你在自己的数据库中实现关系模型的备用键概念。与主键不同，可以在同一个表内定义多个唯一约束，此外，唯一约束不限制列必须定义为NOT NULL。根据SQL标准，具有唯一约束的列应该允许重复的NULL值。但是，SQL Server则不允许重复的NULL标记

1ALTER TABLE dbo.Employees
2 ADD CONSTRAINT UNQ_Employees_ssn
3 UNIQUE(ssn);

3. 外键束约

外键用于强制引用的完整性，此约束定义了引用表中的一个或多个属性指向被引用表（父表）中候选键（主键或唯一约束），引用表和被引用表可以是同一个。外键的目的是限制在外键列中允许的值要存在于那些被引用列中。

1IF OBJECT_ID(‘dbo.Orders‘, ‘U‘) IS NOT NULL
2 DROP TABLE dbo.Orders;
3CREATE TABLE dbo.Orders
4(
5 orderid INT NOT NULL,
6 empid INT NOT NULL,
7 custid VARCHAR(10) NOT NULL,
8 orderts DATETIME2 NOT NULL,
9 qty INT NOT NULL,
10 CONSTRAINT PK_Orders
11 PRIMARY KEY(orderid)
12);
13
14ALTER TABLE dbo.Orders
15 ADD CONSTRAINT FK_Orders_Employees
16 FOREIGN KEY(empid)
17 REFERENCES dbo.Employees(empid)

如果Orders表中的订单引用了Employees中的某一个雇员行，当尝试从Employees中删除这一雇员行时，RDBMS会拒绝删除并抛出错误。

4. CHECK约束

CHECK允许定义一个谓词，确保进入到表中的行或是被修改的行必须满足些约束。

1ALTER TABLE dbo.Employees
2 ADD CONSTRAINT CHK_Employees_salary
3 CHECK(salary > 0);

5. 默认约束

如果没有在插入时指定一个显式值时，将会使用该默认值。

1ALTER TABLE dbo.Employees
2 ADD CONSTRAINT DFT_Orders_orders
3 DEFAULT(SYSDATETIME()) FOR orders;

Chapter 02 单表查询

2.1 SELECT 语句元素

SELECT语句的查询顺序：

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

2.1.7 TOP和OFFSET-FETCH

1. TOP筛选

指定10个最近的订单：

1SELECT TOP 10 orderid, orderdate, custid, empid
2FROM Sales.Orders
3ORDER BY orderdate DESC;

可以为TOP选项指定PERCENT关键字，向上舍入。

1SELECT TOP (1) PERCENT orderid, orderdate, custid, empid
2FROM Sales.Orders
3ORDER BY orderdate DESC

Orders中总共有830行，830 * 1%向上舍入则是9行。
我们可以发现，在前4行的数据中，orderdate是相相同的，在这种没有指定决胜属性（tiebreaker）的情况下，对具有相同值的orderdate排序是没有意义的。SQL Server返回的结果是不确定的，即哪行先被物理访问到就先返回哪行。
如果希望查询结果是确定的，则需要ORDER BY列出的数据是唯一的，即要添加一个决胜发展（tiebreaker）。如可在ORDER BY加入orderid作为tiebreaker

1SELECT TOP(5) orderid, orderdate, custid, empid
2FROM Sales.Orders
3ORDER BY orderdate DESC, orderid DESC;

或者使用WITH TIES作为关联行，来实现tiebreaker

1SELECT TOP(5) WITH TIES orderid, orderdate, custid, empid
2FROM Sales.Orders
3ORDER BY orderdate DESC, orderid DESC;

注意，即使指定了TOP 5，但是输出却是8行。SQL Server首先返回基于orderdate DESC排序的TOP 5行，然后是返回与检索到的5行中最后一行orderdate值相同的其他所有行。

2. OFFSET-FETCH

TOP不是标准SQL，并且不支持跳过功能。标准的SQL定义的TOP类似筛选称为为OFFSET-FETCH，支持跳过功能。SQL SERVER 2012中的OFFSET-FETCH被视为ORDER BY子句的一部分，通常用于实现按顺序
显示效果。OFFSET子句指定要跳过的行数，FETCH子句指定在跳过的行数后要筛选的行数。

1SELECT orderid, orderdate, custid, empid
2FROM Sales.Orders
3ORDER BY orderdate, orderid
4OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

基于排序好的结果，OFFSET子句跳过50行，由FETCH子句往后取25行。
注意，使用OFFSET-FETCH的查询必须具有ORDER BY子句。此外FETCH子句不支持没有OFFSET子句。不过，没有FETCH的OFFSET是允许的，这种情况就是跳过多少行，并返回剩余的所有行。而且ROW和ROWS是可以互换的。

2.1.8 开窗函数速览

开窗函数的功能是：对于基本的每一行，按行的窗口（组）进行运算，并计算一个标量（单个）结果值，行的窗口使用OVER子句定义。OVER子句可以使用PARTITION BY子子句约束窗口中的行，并且可以使用ORDER BY子子名为计算结果定义排序。

1SELECT orderid, custid, val
2 , ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val) AS rownum
3FROM Sales.OrderValues
4ORDER BY custid,val;

ROW_NUMBER函数对于查询结果按custid进行分区，并在各个分区内按照指定的val进行排序，分配了唯一、递增、连续的整数。
注意，ROW_NUMBER函数必须在每个分区内生成唯一值。这意味着即使排序值不增加，行号也会增加。
SELECT列表中的表达式是在DISTINCT子句之前计算的，总之，sql语句的执行顺序为：

FROM
WHERE
GROUP BY
HAVING
SELECT
- 表达式
- DISTINCT
ORDER BY
- TOP/OFFSET-FETCH

2.2 谓词和运算符

2.3 CASE表达式

CASE是一个标题表达式，返回一个基于条件置逻辑的值。注意，CASE是表达式而不是语句。所以，它不支持你控制活动流或是做一些基于条件逻辑的损<