Microsoft SQL SERVER 2008技术内幕 T-SQL语言基础（上）（学习笔记）

男娘i 2021-06-24 16:11 462阅读 0赞

入门心法：要练此功，先废其功。（先忘记已学的其他语言，用T-SQL来思考。）  
所需代码：[https://github.com/956159241/Microsoft-SQL-Server-2008-T-SQL-Fundamentals][https_github.com_956159241_Microsoft-SQL-Server-2008-T-SQL-Fundamentals]

# 目录 #

## 第一章 T-SQL查询和编程基础 ##

#### 1.1 理论背景 ####

###### 1.1.1 SQL ######

###### 1.1.2 集合论 ######

###### 1.1.3 谓词逻辑（Predicate Logic） ######

###### 1.1.4 关系模型（Relational Model） ######

###### 1.1.5 数据生命周期 ######

#### 1.2 SQL Server 体系结构 ####

###### 1.2.1 SQL Server实例 ######

###### 1.2.2 数据库 ######

###### 1.2.3 架构（Schema）和对象 ######

#### 1.3 创建表和定义数据完整性 ####

###### 1.3.1 创建表 ######

###### 1.3.2 定义数据完整性 ######

## 第二章 单表查询 ##

#### 2.1 SELECT 语句的元素 ####

###### 2.1.1 FROM 子句 ######

###### 2.1.2 WHERE 子句 ######

###### 2.1.3 GROUP BY 子句 ######

###### 2.1.4 HAVING 子句 ######

###### 2.1.5 SELECT 子句 ######

###### 2.1.6 ORDER BY 子句 ######

###### 2.1.7 TOP 选项 ######

###### 2.1.8 OVER子句 ######

#### 2.2 谓词和运算符 ####

#### 2.3 CASE 表达式 ####

#### 2.4 NULL值 ####

#### 2.5 同时操作（All-At-Once Operation） ####

#### 2.6 处理字符数据 ####

###### 2.6.1 数据类型 ######

###### 2.6.2 排序规则（Collation） ######

###### 2.6.3 运算符和函数 ######

#### 2.7处理日期和时间数据 ####

## 第三章 联接查询 ##

#### 3.1 交叉联接 ####

###### 3.1.1 ANSI SQL-92语法 ######

###### 3.1.2 ANSI SQL-89语法（不支持使用） ######

###### 3.1.3 自交叉联接 ######

###### 3.1.4 生成数字表 ######

#### 3.2 内联接 ####

#### 3.3 特殊的联接实例 ####

###### 3.3.1 组合联接 ######

###### 3.3.2 不等联接 ######

###### 3.3.3 多表联接 ######

#### 3.4 外联接 ####

###### 3.4.1 外联接基础 ######

--------------------

## 第一章 T-SQL查询和编程基础 ##

#### 1.1 理论背景 ####

SQL——Structured Query Language,它是为查询和管理关系型数据库管理系统（RDMS）中的数据而专门设计的一种标准语言。  
  语言的独立性——关系模型是独立于语言的，例如C\#的类模型。

###### 1.1.1 SQL ######

SQL 是基于关系模型的ANSI和ISO标准语言，专门为查询和管理RDMS中的数据而专门设计的一种标准语言。

名词区分：

*  **ISO** —— 国际标准化组织(International Organization for Standardization)简称ISO，是一个全球性的非政府组织，是国际标准化领域中一个十分重要的组织。（.iso系统镜像文件后缀）。
 *  **IOS** —— 系统。
 *  **OSI** —— 是Open System Interconnect的缩写（七层）。

理解：  
  SQL类似英语，告诉它想要得到什么，然后由DBMS对语言进行处理得出结果。

###### 1.1.2 集合论 ######

关系模型的数学基础——集合论。  
  所谓“集合”是把我们直观或思维中确定的、相互间有明确却别的那些对象m视为一个整体M，这一整体M就称为集合（称m为集合M的元素）。

###### 1.1.3 谓词逻辑（Predicate Logic） ######

关系模型的数学基础——谓词逻辑。  
  不严格的说，**谓词就是用来刻画事物是否具有某种性质或满足某种表达式条件的一个词项**用于维护数据的逻辑完整性和定义它的结构，谓词也可以用于对数据进行过滤以定义其子集等多种应用场合。

###### 1.1.4 关系模型（Relational Model） ######

关系模型是一个用于表示数据的语义模型，其理论基础是集合论和谓词逻辑。  
  关系模型的目标是要用最少的或完全无冗余地支持完整数据的持久化表示，而且还要将数据完整性（强制的数据一致性）定义为模型的一部分。

关系模型几个相关的概念：

*  命题 —— proposition
 *  域 —— domain
 *  n元关系 —— n-ary relaiton
 *  n重元组 —— n-tuple
 *  序偶 —— ordered pair

在关系模型中，**关系**在数据库的实现中就表现为**数据表**。对关系进行操作的结果得到的还是一个关系。  
  一个域就是一个属性可能的（或有效的）\*\* *一组取值* **的集合。  
约束（Constraint）  
  关系模型最大的优点之一就是将数据完整性定义为模型的一部分，完整性是通过规则（或约束）来实施的。  
约束的例子：  
  提供实体完整性（entity integrity）的**候选键（candidate key）\*\*——指在关系中能够防止同一元组（数据行）多次出现的属性集（一个或多个属性）。  
  外键（foreign key）——用于实施引用完整性，外键是在关系（称为引用关系，referencing relation）中的一个或多个属性上定义，通过它来应用另一个（或者，也可能是同一个）关系中的候选键。  
**规范化（Normalization）**  
  关系模型同时也定义了规范化规则（也称为范式）。规范化是一种形式化的数学处理过程，以确保每一个实体只由一个关系来表示。

![2789632-5db95ddf58c787f8.png][]

**第一范式（1NF）无重复的列：**  
  第一范式要求表中的行必须是唯一的属性应该是原子的（atomic）——即列不能再拆分。即一个表表示一个关系。

![2789632-5016cc1608c44381.png][]

数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。很显然，在当前的任何关系数据库管理系统（DBMS）中， **傻瓜也不可能做出不符合第一范式的数据库**，因为这些 **DBMS不允许你把数据库表的一列再分成二列或多列**。因此，你想在现有的DBMS中设计出不符合第一范式的数据库都是不可能的。

**第二范式（2NF）属性完全依赖于主键 \[ 消除部分子函数依赖 \]：**  
  第二范式（2NF）是在第一范式（1NF）的基础上建立起来的,第二范式包括两条规则，首先数据必须满足第一范式，其次要求非键属性（nonkey attribute）和候选键属性之间必须满足一定的条件。非正式地说，如果要获得任何非键属性值，就必须提供同一行中某个候选键的所有属性值。  
  例如员工信息表中加上了员工编号（emp\_id）列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。

![2789632-463270c3b2cce7e5.png][]

不符合第二范式

![2789632-526b00eefbb36f26.png][]

符合第二范式

另外，所有单关键字的数据库表都符合第二范式，因为不可能存在组合关键字。

**第三范式（3NF）属性不依赖于其它非主属性 \[ 消除传递依赖 \]：**  
  **第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息**。要求数据必须满足第二范式。其次，所有非键属性必须非传递依赖于候选键。所有非键属性都必须互相独立。换句话说，一个非键属性不能依赖于其他非键属性。  
参考书本和[http://www.open-open.com/lib/view/open1404791721950.html][http_www.open-open.com_lib_view_open1404791721950.html]

###### 1.1.5 数据生命周期 ######

数据首先进入联机事务处理（OLTP，Online Transactional Processing ）然后进入数据仓库（Data Warehouse）再进入联机分析处理（OLAP，OnLine Analytical Processing）最后进入DM（Data mining ）。

#### 1.2 SQL Server 体系结构 ####

###### 1.2.1 SQL Server实例 ######

SQL Server实例是指安装的一个SQL Server数据库引擎/服务。在同一台计算机上可以安装多个实例(补充：但是需要不同的实例名)。

###### 1.2.2 数据库 ######

可以将数据库认为是各种对象的容器，这些对象可以是表（table）、视图（view）、存储过程（stored procedure）等等。每个实例包含多个数据库。数据库在物理上由数据文件和事物日志文件组成。

###### 1.2.3 架构（Schema）和对象 ######

一个数据库包含多个架构，而每个架构则又包含多个对象。

## 实例 包含 数据库 包含 架构 包含 多个对象 ##

#### 1.3 创建表和定义数据完整性 ####

使用的架构名为dbo，数据库名testdb  
创建数据库语句：

IF DB_ID('testdb') IS NULL
        CREATE DATABASE testdb;

执行之后刷新也不出现数据库名，然后我重启了下SQL Server就出现了……  
  DB\_ID函数接受一个数据库名称作为输入，返回它的内部数据库ID

###### 1.3.1 创建表 ######

创建表语句：

--创建表,表名：Employees(empid:雇员ID mgrid:经理ID ssn:社会保险号(social securuty number))
    USE testdb;
    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
    );

这里，类型‘U’代表用户表。

###### 1.3.2 定义数据完整性 ######

作为模型的一部分而实施的数据完整性（也就是说，作为定义的一部分）称为**声明式（declarative）数据完整性**。用代码来实施的数据完整性（例如，用存储过程或触发器）称为**过程式（procedural）数据完整性**。  
  本节主要介绍声明式约束的一些例子，包括主键、唯一约束（UNIQUE）、外键、检查约束（CHECK）以及DEFAULT约束。  
在添加约束之前，先了解一下如何解除约束：

alter table 表名 drop contrainst 约束键名

**主键约束（Primary Key Constraints）**  
  每个表只能定义一个主键。  
添加主键约束：

--为表添加主键约束Primary Key Constraints
    ALTER TABLE dbo.Employees
        ADD CONSTRAINT PK_Employees
        PRIMARY KEY(empid);

![2789632-9fc8ff453574bec2.png][]

**唯一约束（Unique Constraints）**

--为表添加唯一约束Unique Constraints 添加在ssn列上
    ALTER TABLE dbo.Employees
        ADD CONSTRAINT UNQ_Employees_ssn
        UNIQUE(ssn);

这地地方在管理器上貌似并不能直观的看到，或许只有插入数据的时候才能知道吧，试试看：

![2789632-37fe93946568b256.png][]

**外键约束**  
  先创建一个Orders表，然后设置自己的empid为外键指向被引用表（referenced table）中的一组**候选键（主键或唯一约束）**。注意：引用表和被引用表可能是同一个表。

--添加一个Orders表
    IF OBJECT_ID('dbo.Orders','U') IS NOT NULL
        DROP TABLE dbo.Orders;
    CREATE TABLE dbo.Orders
    (
        orderid INT         NOT NULL,
        empid   INT         NOT NULL,
        custid  VARCHAR(10) NOT NULL,
        orderts DATETIME    NOT NULL,
        qty     INT         NOT NULL,
        CONSTRAINT PK_Orders
            PRIMARY KEY(orderid)
    );
    --添加外键约束
    ALTER TABLE dbo.Orders
        ADD CONSTRAINT FK_Orders_Employees
        FOREIGN KEY(empid)
        REFERENCES dbo.Employees(empid);
    
    --自己给自己添加外键约束
    ALTER TABLE dbo.Employees
        ADD CONSTRAINT FK_Employees_Employees
        FOREIGN KEY(mgrid)
        REFERENCES Employees(empid);

**检查约束（Check）**  
  检查约束用于定义在表中输入或修改一行数据之前必须满足的一个谓词。

--添加检查约束(check)
    ALTER TABLE dbo.Employees
        ADD CONSTRAINT CHK_Employees_salary
        CHECK(salary > 0);

![2789632-68ba86fbedf5f0fd.png][]

**默认约束（Default）**

--默认设置，由它返回当前的日期和时间值
    ALTER TABLE dbo.Orders
        ADD CONSTRAINT DFT_Orders_orderts
        DEFAULT(CURRENT_TIMESTAMP) FOR orderts;

![2789632-20debfb160e7a2f6.gif][]

默认添加orderts

## 第二章 单表查询 ##

#### 2.1 SELECT 语句的元素 ####

SELECT 语句的目的是对表进行查询、应用一定的逻辑处理，并返回结果。  
  Microsoft SQL Server引擎并不教条地严格遵守逻辑查询处理；相反，在物理地处理一个查询时，它可以自由地调整处理阶段的顺序，只要最终的结果能够和逻辑查询处理的规定保持一致。SQL SERVER 可以（事实上经常）在查询的物理处理中采用很多快捷方式。  
创建一条查询语句：

--开始用一个USE语句来设置会话(session)的数据库上下文,如果会话已经位于需要查询的数据库上下文中,则
    --不需要再使用USE语句
    USE InsideTSQL2008;
    
    SELECT empid,YEAR(orderdate) AS orderyear,COUNT(*) AS numorders
        FROM Sales.Orders
        WHERE custid = 71
        GROUP BY empid,YEAR(orderdate)
        HAVING COUNT(*) > 1
        ORDER BY empid,orderyear;

解释（从select开始）：

*  1.从Sales.Orders表中查询数据行
 *  2.对订单数据进行过滤，值保留客户ID等于71的记录
 *  3.按雇员ID和订单年份对订单数据进行分组
 *  4.对分组数据（雇员ID和订单年份）进行过滤，只保留具有多个订单的分组
 *  5.选择（返回）每个分组的雇员ID、订单年份，以及订单数量。
 *  6.按照雇员ID和订单年份对输出结果进行排序。

在逻辑上，应该是按以下顺序来处理它的各个子句：

FROM Sales.Orders
        WHERE custid = 71
        GROUP BY empid,YEAR(orderdate)
        HAVING COUNT(*) > 1
          SELECT empid,YEAR(orderdate) AS orderyear,COUNT(*) AS numorders
        ORDER BY empid,orderyear;

我们看看C\#里提供的LINQ语法:

//创建查询
                var query = from CustomerInfo ci in customers
                            where ci.Age >= 80
                            select ci;

###### 2.1.1 FROM 子句 ######

1.如何修改架构名

![2789632-37017912247033d9.png][]

修改架构名

首先确定该架构已经存在。  
架构名更改方式：  
批量修改：  
`EXEC sp_MSforeachtable 'exec sp_changeobjectowner ''?'',''dbo'' '`  
单个修改：  
`EXEC sp_changeobjectowner '要改的表名','dbo'`  
如果想修改存储过程Owner:  
先把--and xtype='p' 反注释，再创建sp\_changeobjectowner 存储过程。  
然后执行： `EXEC sp_changeobjectowner 'CurrentOwner','dbo'`

2.创建一个简单的查询

--简单的查询
    SELECT orderid,custid,empid,orderdate,freight 
        FROM Sales.Orders;

查询结果：

![2789632-5acf038e963491e3.png][]

看起来似乎是以特定的顺序返回（以orderid的升序排列）的，但不能保证绝对这样。

如果名称中嵌入了空格或其他特殊字符，就必须分隔这样的标识符。

*  ANSI SQL标准：“Order Details”
 *  SQL Server的特殊格式：\[Order Details\]

###### 2.1.2 WHERE 子句 ######

在WHERE子句中，可以指定一个谓词或逻辑表达式，从而过滤由FROM阶段返回的行。  
练习：

--WHERE 子句练习
    SELECT orderid,empid,orderdate,freight
        FROM Sales.Orders
        WHERE custid = 71;

### 要时刻记住T-SQL使用的是三值谓词逻辑，所以逻辑表达式的结果可以为TRUE、FALSE、或者UNKNOW。 ###

###### 2.1.3 GROUP BY 子句 ######

GROUP BY 阶段可以将前面逻辑谓词处理阶段返回的行按“组”进行组合。  
练习：

--GROUP BY 子句练习
    SELECT empid,YEAR(orderdate) AS orderyear 
       FROM Sales.Orders
       WHERE custid = 71
       GROUP BY empid,YEAR(orderdate);

因为聚合函数只为每个组返回一个值，所以一个元素如果不在GROUP BY列表中出现，就只能作为聚合函数（COUNT、SUM、AVG、MIN、MAX）的输入。  
例如：

![2789632-848d171edd962c15.png][]

错误操作，加入了freight

正确的操作:

--GROUP BY 的正确操作
    SELECT
        empid,
        YEAR(orderdate) AS orderyear,
        SUM(freight) AS totalfreight,
        COUNT (*) AS numorders
    FROM Sales.Orders
    WHERE custid = 71
    GROUP BY empid,YEAR(orderdate);

表达式SUM（freight）返回每组中所有运费值的总和，而函数COUNT(\*)则返回每组中行的个数。

### 所有的聚合函数都会忽略NULL，只有一个例外——COUNT（\*）。 ###

如果只想处理不重复的已知值，可以在聚合函数的圆括号中指定DISTINCT关键字。  
例如：

--DISTINCT的使用
    SELECT
        empid,
        YEAR(orderdate) AS numcusts,
        COUNT(DISTINCT custid) AS numcusts
    FROM Sales.Orders
    GROUP BY empid,YEAR(orderdate)

###### 2.1.4 HAVING 子句 ######

HAVING 子句用于指定对组进行过滤的谓词或逻辑表达式，这与WHERE阶段对单独的行进行过滤相对应。  
  因为HAVING子句是**对行进行分组后处理的**,所以可以在逻辑表达式中引用聚合函数。

--HAVING 子句练习
    SELECT
       empid,
      YEAR(orderdate) AS orderyear
    FROM Sales.Orders
    WHERE custid = 71
    GROUP BY empid,YEAR(orderdate)
    HAVING COUNT(*) > 1

###### 2.1.5 SELECT 子句 ######

T-SQL允许查询返回没有名称的结果集列，但关系模型不允许这样。  
**注意：**选择Sales.Orders表的orderid和orderdate列，结果不小心，忘记了在两个列名之间加一个逗号，如下：

select orderid orderdate
    from Sales.Orders;

这一查询在语法上是有效的，意思是说你想将orderid列的别名定义为orderdate，得到输出只有一列包含订单ID的列，其别名为orderdate。**要查出这样的bug可能是很难的**  
  SELECT子句是在FROM、WHERE、GROUP BY以及HAVING子句后处理的。这意味着对于SELECT子句之前处理的那些子句，在SELECT子句中为表达式分配的别名并不存在。  
  为了确保select语句执行的结果中行的唯一性，SQL提供的方法就是使用DISDINCT子句来删除重复的行。

###### 2.1.6 ORDER BY 子句 ######

ORDER BY 子句用于展示数据时对输出结果中的行进行排序。从逻辑查询处理来看，ORDER BY是最后处理的一个子句。

--ORDER BY 子句练习
    SELECT empid,YEAR(orderdate) AS orderyear,COUNT(*) AS numorders
        FROM Sales.Orders
        WHERE custid = 71
        GROUP BY empid,YEAR(orderdate)
        HAVING COUNT(*) > 1
        ORDER BY empid,orderyear;

理解SQL最重要的一点就是要明白表不保证是有序的，因为表示为了代表一个集合（如果有重复项，则是多集），而集合是无序的。  
  事实上，ORDER BY 是唯一能够引用SELECT 处理阶段创建的别名列的阶段，因为它是唯一一个在SELECT阶段之后被处理的阶段。

T-SQL支持在ORDER BY子句中指定没有在SELECT子句中出现过得元素，也就是说，排序依据的列并不一定必须要在输出返回的列中选取。  
  但是，当指定了DISTINCT以后，ORDER BY 子句就被限制为只能选取在SELECT列表中出现的那些元素。

###### 2.1.7 TOP 选项 ######

TOP选项是T-SQL特有的，用于限制查询返回的行数或百分比。

--Top选项练习
    SELECT TOP(5) orderid,orderdate,custid,empid
        FROM Sales.Orders
        ORDER BY orderdate DESC;

**注意：**当在查询中指定了TOP以后，ORDER BY子句就会起到双重作用。首先，作为select处理阶段一部分的top选项要依靠order by子句先为各个行定义他们的逻辑优先顺序，在这种优先顺序的基础上再去过滤其他请求。其次，作为select处理阶段之后的order by阶段，与为了展示数据而对行进行排序的order by子句完全一样。

### 个人理解： ###

由于表是无序的，所以前 n 条是不确定的，用order by 先根据某一列进行排序，然后 top n 选出 n 行，最后order by再执行一次功能，展示数据而对行进行排序。

在TOP选项中可以使用PERCENT关键字。例如：TOP （1）PERCENT----1% 。

再来看看一个功能：WITH TIES

![2789632-b5be2aaedc0b8877.png][]

**注意：**即便指定了TOP（5），输出的还是包含了8行。SQL SERVER 先按照orderdate desc的顺序，返回top（5）行；再从表中返回orderdate值和已经访问过得前5行中的最后一行相同的其他所有行。

###### 2.1.8 OVER子句 ######

在group by子句中，在对数据进行分组以后，查询为每个组只返回一行；因此，也就要限制所有的表达式为每个组只能返回一个值。  
  聚合开窗函数使用OVER子句提供窗口作为上下文，对窗口中的一组值进行操作，而不是使用group by子句提供上下文。  
  注意：只有在select和order by处理阶段才允许使用over子句。  
例如：如果在对OrderValues视图进行查询的SELECT子句中指定了SUM（val） OVER表达式，这个函数就会对SELECT阶段操作的所有行计算其总价格。  
  如果想对行限制或分区，则可以使用PARTITION BY子句。

![2789632-7a3a03540689765f.png][]

OVER 子句的一个优点就是能够在返回基本列的同时，在同一行对它们进行聚合，也可以在表达式中混合使用基本列和聚合值列。例如，以下查询为OrderValues的每一行计算当前价格占总价格的百分比，以及当前价格占客户总价格的百分比：

--over()练习，所占百分比
    SELECT orderid,custid,val,
      100.*val / SUM(val) OVER() AS pctall,
      100.*val / SUM(val) OVER(PARTITION BY custid) AS pctcust
      FROM Sales.OrderValues;

**注意：**在表达式中使用的是十进制数100.（100后面加个点），而不是直接使用证书100，因为这样可以隐式地将整数值val和SUM（val）转换成十进制实数值。否则，表达式的除法将是“整数除法”，会截取小数部分。  
OVER 子句也支持四种排名函数：ROW\_NUMBER(行号)、RANK（排名）、DENSE\_RANK(密集排名)以及NTILE。

![2789632-5dee3480d389e297.png][]

再看一个在内部为子集分配行号：

![2789632-9b2827f8a24cfe88.png][]

#### 2.2 谓词和运算符 ####

T-SQL 有几种不同的语言元素可以指定逻辑表达式，例如，查询过滤器（WHERE 和 HAVING）、CHECK约束，等等。在逻辑表达式中可以使用各种谓词，T-SQL支持的谓词包括IN、BETWEEN以及LIKE等。

扩展：

*  执行where子句查找符合条件的数据；
 *  使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后having 子句去掉不符合条件的组。
 *  having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.
 *  having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。
 *  having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。

**IN这个谓词用于检查一个值（或标量表达式）是否与一组元素中的至少一个相等。**

--IN谓词
    SELECT orderid,empid,orderdate 
        FROM Sales.Orders
        WHERE orderid IN (10248,10249,10250);

**BETWEEN 这个谓词用于检查一个值是否在指定的范围内，包括两个指定的边界值。**

--BETWEEN谓词
    SELECT empid,firstname,lastname
        FROM HR.Employees
        WHERE lastname LIKE N'%D%';

**LIKE这个谓词用于检查一个字符串值是否与指定的模式匹配（如上代码）**  
  在'%D%'前加N，代表国际化（National）用于表示字符串是Unicode数据类型（NCHAR或NVARCHAR）

**运算符**

![2789632-99c33d7ad5f515bc.png][]

优先级

![2789632-2e536cd0aa2bb5ac.png][]

![2789632-bbd7f0453fd7d177.png][]

由上两图很明显看出AND的优先级高于OR。

#### 2.3 CASE 表达式 ####

CASE表达式是一个标量表达式，它基于条件逻辑来返回一个值。  
CASE表达式有两种格式：简单表达式和所搜表达式。CASE简单格式将一个值（或一个标量表达式）与一组可能的取值进行比较，并返回第一个匹配的结果。  
例一：

--CASE表达式
    SELECT productid,productname,categoryid,
        CASE categoryid
            WHEN 1 THEN '1Bevagrages'
            WHEN 2 THEN '2COmmo'
            WHEN 3 THEN '3HHHH'
            WHEN 4 THEN '4Bagrages'
            WHEN 5 THEN '5Bevagages'
            WHEN 6 THEN '6agrages'
            WHEN 7 THEN '7grages'
            WHEN 8 THEN '8ages'
            ELSE    'unkown'
        END AS categoryname
    FROM Production.Products;

![2789632-47ead50fe1a83bfa.png][]

根据categoryid来定义名字

例二：

--CASE表达式2
    SELECT orderid,custid,val,
       CASE NTILE(3) OVER(ORDER BY val)
       WHEN 1 THEN 'LOW'
       WHEN 2 THEN 'Medium'
       WHEN 3 THEN 'High'
       END AS titledesc 
    FROM Sales.OrderValues
    ORDER BY val;

NTILE（）函数把记录结果集分成N部分  
NTILE（3）将查询结果分成三部分。

CASE搜索表达式返回结果为TRUE的第一个WHEN逻辑表达式所关联的THEN子句中指定的值：

SELECT orderid,custid,val,
        CASE
            WHEN val < 1000.00  THEN 'Less then 1000'
            WHEN val BETWEEN 1000.00 AND 3000.00    THEN 'Between 1000 and 3000'
            WHEN val > 3000.00  THEN 'More Than 3000'
            ELSE 'unknown'
        END AS valuecategory
    FROM Sales.OrderValues;

#### 2.4 NULL值 ####

SQL支持用NULL符号来表示缺少的值，它使用的三值谓词逻辑。这意味着谓词的计算结果可以是TRUE、FALSE或UNKNOWN。  
  UNKNOWN的一个微妙之处是当对它取反（negate）时，结果仍然是UNKNOWN。  
  对两个NULL值进行比较的表达式（NULL = NULL），其计算结果竟然也为UNKNOWN。  
  **查询过滤条件“接受TRUE”意味着它既会拒绝让逻辑表达式计算结果为FALSE的行，也会拒绝让表达式计算结果为UNKNOWN的那些行。对于FALSE也是如此。**如若需要查询NULL值：

--查询NULL值
    SELECT custid,country,region,city
    FROM Sales.Customers
    WHERE region IS NULL

![2789632-12b0d77bef03f4f0.png][]

在用于比较和排序目的的不同语言元素中，SQL处理NULL的方式也有所不同。一些元素认为两个NULL值彼此相等，而另一些则认为它们不相等。  
  当进行分组和排序时，认为两个NULL值是相等的。也就是说，GROUP BY子句会在每个组中重新组织所有的NULL值就像有具体值得列一样。  
  ORDER BY子句也会对所有的NULL值进行排序。  
   **ANSI SQL 有两种UNIQUE约束：一种将多个NULL值视为相等的（只允许有一个NULL值），另一种则将多个NULL值视为不同的（允许有多个NULL值）。SQL Server只实现了前者。**

#### 2.5 同时操作（All-At-Once Operation） ####

SQL支持一种所谓的同时操作的概念，其含义是认为在同一逻辑查询处理阶段中出现的所有表达式都是同时进行计算的。  
  从逻辑上来说，SELECT列表中各表达式的计算是没有顺序的——它们只是一组表达式。在逻辑上SELECT列表中的所有表达式都是在同一时刻进行计算的。

SELECT col1,col2
    FROM dbo.T1
    WHERE col1<>0 AND col2/col1>2;

如果表达式col1<>0的结果为FALSE，SQL Server将会按照“短路（short-circuit）求值”的原则，停止计算这个表达式。  
  在ANSI SQL 中有“同时操作的这么个概念，所以SQL Server可以按它喜欢的任意顺序来自由地处理WHERE子句的表达式。

#### 2.6 处理字符数据 ####

###### 2.6.1 数据类型 ######

SQL Server支持两种字符数据类型——普通字符和Unicode字符。普通字符数据类型包括CHAR和VARCHAR，Unicode字符数据类型包括NCHAR和NVARCHAR。

###### 2.6.2 排序规则（Collation） ######

排序规则是字符数据的一个属性，封装了几个方面的特征，包括多语言支持（和Unicode类型有关，因为它支持所有语言）、排序规则、区分大小写、区分重音，等等。

![2789632-7c1cd0e9be4c87ed.png][]

此时，大小写并不匹配。

![2789632-e1f3104dd04b0b9e.png][]

--排序规则,区分大小写
    SELECT empid,firstname,lastname
    FROM HR.Employees
    WHERE lastname COLLATE Latin1_General_CS_AS = N'davis';

当区分大小写时，没有匹配的数据，故没有数据被查出来。

###### 2.6.3 运算符和函数 ######

注意：

![2789632-f27f92fce06a077b.png][]

使用+运算符：

--运算符和函数
    SELECT empid,firstname + N' ' + lastname AS fullname
    FROM HR.Employees;

通过将一个名为CONCAT\_NULL\_YIELDS\_NULL的会话选项设置为OFF，就可以改变SQL Server处理串联的方式。这时，SQL Server将把NULL值作为空字符串来进行串联。

SET CONCAT_NULL_YIELDS_NULL OFF;

### 常用函数 ###

###### 2.6.3.1 SUBSTRING函数 ######

SUBSTRING函数用于从字符串中提取子串。  
语法：

SUBSTRING(string,start,length)

###### 2.6.3.2 LEFT 和 RIGHT函数 ######

LEFT和RIGHT函数是SUBSTRING函数的简略形式，它们分别返回输入字符串中从左边或右边开始制定个数的字符。

LEFT（string，n），RIGHT（string,n）

###### 2.6.3.3 LEN和DATALENGTH函数 ######

LEN函数返回输入字符串中的字符数。  
语法：

LEN（string）；

普通字符字节数与字符数是相同的；而对于Unicode字符，每个字符需要两个字节的存储空间，因此，字符串的字符数是字节数的一半。如果要得到字节数，则应该使用**DATALENGTH**函数；  
  **LEN和DATALENGTH函数的另一个区别是：前者不包含尾随空格，而后者包含尾随空格**。

###### 2.6.3.4 CHARINDEX函数 ######

CHARINDEX函数返回字符串中某个子串第一次出现的起始位置。  
语法：

CHARINDEX（substring，string[,start_pos]）

例子：

SELECT CHARINDEX(' ','IT BEN');

###### 2.6.3.5 PATINDEX函数 ######

PATINDEX函数返回字符串中某个模式第一次出现的起始位置。  
语法：

PATINDEX（patten,string）

例子：

SELECT  PATINDEX('%[0-9]%','afd123afadsf')

###### 2.6.3.6 REPLACE函数 ######

REPLACE 函数将字符串中出现的所有某个子串替换为另一个子串。  
语法：

REPLACE（string,substring1,substring2）

###### 2.6.3.7 REPLICATE函数 ######

REPLICATE函数以指定的次数复制字符串值。  
语法：

REPLICATE（string，n）

###### 2.6.3.8 STUFF函数 ######

STUFF函数可以删除字符串中的一个子串，再插入一个新的字符串作为替换。  
语法：

STUFF（string,pos,delete_length,insertstring）

###### 2.6.3.9 UPPER和LOWER函数 ######

UPPER和LOWER函数将输入字符串中的所有字符都转换为大写或小写字符。  
语法：

UPPER（string），LOWER（string）

###### 2.6.3.10 RTRIM和LTRIM函数 ######

RTRIM和LTRIM函数用于删除输入字符串中的尾随空格或前导空格。  
语法：

RTRIM（string），LTRIM（string）

#### 2.7 处理日期和时间数据 ####

###### 2.7.1 日期和时间数据类型 ######

![2789632-094434cd4c1d9bfb.png][]

日期和时间数据类型

具体使用方式可参照： [https://msdn.microsoft.com/zh-cn/library/ms186724(v=sql.120).aspx][https_msdn.microsoft.com_zh-cn_library_ms186724_v_sql.120_.aspx]

## 第三章 联接查询 ##

#### 3.1 交叉联接 ####

在逻辑上，交叉联接是一种最简单的联接。交叉联接只实现一个逻辑查询步骤（笛卡尔积）。  
例如：

###### 3.1.1 ANSI SQL-92语法 ######

SELECT C.custid,E.empid
    FROM Sales.Customers AS C
        CROSS JOIN HR.Employees AS E;

执行结果：

![2789632-10394967a2f05af0.png][]

###### 3.1.2 ANSI SQL-89语法（不支持使用） ######

跟上面的代码相似，就是把Cross Join改成了逗号联接。结果是一样的。

![2789632-bd78c36afb4519fa.png][]

![2789632-ea5fd42ff0587fa7.png][]

###### 3.1.3 自交叉联接 ######

对同一个表的多个实例也可以进行联接，这种功能就是所谓的自联接（self-join），所有基本联接类型（交叉联接、内联接，以及外联接）都支持自联接。

--3.1.3 自交叉联接
    SELECT
        E1.empid,E1.firstname,E1.lastname,
        E2.empid,E2.firstname,E2.lastname
    FROM HR.Employees AS E1
        CROSS JOIN HR.Employees AS E2;

执行结果：

![2789632-bd7408167487e75d.png][]

###### 3.1.4 生成数字表 ######

--3.1.4 生成数字表，先创建一个数据库TestDB，然后创建一张表
    USE tempdb;
    IF OBJECT_ID('dbo.Digits','U') IS NOT NULL DROP TABLE dbo.Digits;
    CREATE TABLE dbo.Digits(digit INT NOT NULL PRIMARY KEY);
    INSERT INTO dbo.Digits(digit)
        VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
    --显示tempdb里的Digits
    SELECT * FROM Digits;

![2789632-30f92f1d7fdcd8da.png][]

相比于以前的插入这种数字表就要简单的多……

INSERT INTO dbo.Digits(digit) VALUES(0);
    INSERT INTO dbo.Digits(digit) VALUES(1);
    INSERT INTO dbo.Digits(digit) VALUES(2);
    INSERT INTO dbo.Digits(digit) VALUES(3);
    ……

#### 3.2 内联接 ####

两个输入表进行笛卡尔积运算，然后根据用户指定的谓词对结果进行过滤。

###### 注意： ######

> INNER JOIN 等价于 JOIN  
> 你可以理解为  
> JOIN 是 INNER JOIN 的缩写。  
> LEFT JOIN 等价于 LEFT OUTER JOIN  
> RIGHT JOIN 等价于 RIGHT OUTER JOIN

因为ANSI SQL-92语法相对更加安全，且值得推荐使用的语法，故此不再介绍——89语法。

USE InsideTSQL2008;
    SELECT E.empid,E.firstname,E.lastname,O.orderid
    FROM HR.Employees AS E
        JOIN Sales.Orders AS O
            ON E.empid = O.empid;

#### 3.3 特殊的联接实例 ####

###### 3.3.1 组合联接 ######

组合查询跟内联接相似，不同之处是加了多条件。

ON
    AND 
    WHERE

###### 3.3.2 不等联接 ######

SELECT E1.firstname,E1.lastname,E2.firstname,E2.lastname
    FROM HR.Employees AS E1
        JOIN HR.Employees AS E2
        ON E1.empid < E2.empid;

###### 3.3.3 多表联接 ######

SELECT C.custid,C.companyname,O.orderid,OD.productid,OD.qty
    FROM Sales.Customers AS C
        JOIN Sales.Orders AS O
        ON C.custid = O.custid
        JOIN Sales.OrderDetails AS OD
        ON O.orderid = OD.orderid;

![2789632-2426a70881b26cde.png][]

关于on 与where的使用，个人理解，on的一次作用在两张表，where的一次作用在一张表。  
**百度理解**：  
1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

#### 3.4 外联接 ####

外联接会应用内联接所应用的两个逻辑处理步骤（笛卡尔积和ON过滤），此外还多加一个外联接特有的第三步：添加外部行。  
  外联接的第三个逻辑查询处理步骤就是要识别保留表中按照ON条件在另一个表找不到与之匹配的那些行，再把这些行添加到联接的前两个步骤生成的结果表中；对于来自联接的非保留表的那些列，追加的外部行中的这些列用NULL作为占字符。

###### 3.4.1 外联接基础 ######

SELECT C.custid,C.companyname
    FROM Sales.Customers AS C
        LEFT OUTER JOIN Sales.Orders AS O
        ON C.custid = O.custid
    WHERE O.custid IS NULL;

![2789632-a32c14adfffeb3e8.png][]

--------------------

![2789632-3b18269684ea9294.png][]

公众号.png

[https_github.com_956159241_Microsoft-SQL-Server-2008-T-SQL-Fundamentals]: https://github.com/956159241/Microsoft-SQL-Server-2008-T-SQL-Fundamentals
[2789632-5db95ddf58c787f8.png]: /images/20210615/11b5aaa793e047ee86b84cc43b888689.png
[2789632-5016cc1608c44381.png]: /images/20210615/cd201ed5936e4f0ea3154e8c959b0969.png
[2789632-463270c3b2cce7e5.png]: /images/20210615/a09f7ed85d6b4af0959b1452d5e78182.png
[2789632-526b00eefbb36f26.png]: /images/20210615/4d078fc44892449f8592f8c20eb0ebfc.png
[http_www.open-open.com_lib_view_open1404791721950.html]: http://www.open-open.com/lib/view/open1404791721950.html
[2789632-9fc8ff453574bec2.png]: /images/20210615/65e3f7a9658b4bd4addefa1ada9963f7.png
[2789632-37fe93946568b256.png]: /images/20210615/3cbdea2447fc47c887181594e28138de.png
[2789632-68ba86fbedf5f0fd.png]: /images/20210615/2ad9cbbd95164d10865645376cf1ba10.png
[2789632-20debfb160e7a2f6.gif]: /images/20210615/9cd4518efaf44c10bb2a192aa1bd2564.png
[2789632-37017912247033d9.png]: /images/20210615/29d98f8857874c7784c8fb150c2e2fe6.png
[2789632-5acf038e963491e3.png]: /images/20210615/57ee0a2acb86409b8aca8d48ec42d98b.png
[2789632-848d171edd962c15.png]: /images/20210615/ef48a2291e13424b81cde037a732ab20.png
[2789632-b5be2aaedc0b8877.png]: /images/20210615/b7b3de1cbf624f9bad6f48ba2f709bd0.png
[2789632-7a3a03540689765f.png]: /images/20210615/f1e609d14d3e4d959dfb17e51e1b56c8.png
[2789632-5dee3480d389e297.png]: /images/20210615/0e15c6e0869a48a8a21a7bd8680fdd60.png
[2789632-9b2827f8a24cfe88.png]: /images/20210615/638599ecd4a640e1bad6b682bd4fb1ba.png
[2789632-99c33d7ad5f515bc.png]: /images/20210615/867477cf06a84daa814001caa2a8ac45.png
[2789632-2e536cd0aa2bb5ac.png]: /images/20210615/f8440bb13cf24dba92851aa21dccdd49.png
[2789632-bbd7f0453fd7d177.png]: /images/20210615/436c576b4c324c06a14ce7a70e41f344.png
[2789632-47ead50fe1a83bfa.png]: /images/20210615/823d450d38964cd482b74ad3b2145e2e.png
[2789632-12b0d77bef03f4f0.png]: /images/20210615/ce0df721cf49405ea2151a619dc27bec.png
[2789632-7c1cd0e9be4c87ed.png]: /images/20210615/0758bf2cf88244cc83dcc4939141f6ef.png
[2789632-e1f3104dd04b0b9e.png]: /images/20210615/032acfb7276a4b8f835954efb2b6678b.png
[2789632-f27f92fce06a077b.png]: /images/20210615/68e5d7b3dc9b49c392aa58cf922adde5.png
[2789632-094434cd4c1d9bfb.png]: /images/20210615/7e95f6e61ee5414aa78cdcd9a086c5e8.png
[https_msdn.microsoft.com_zh-cn_library_ms186724_v_sql.120_.aspx]: https://msdn.microsoft.com/zh-cn/library/ms186724%28v=sql.120%29.aspx
[2789632-10394967a2f05af0.png]: /images/20210615/7e92477c816e4433938a6c3c537be7db.png
[2789632-bd78c36afb4519fa.png]: /images/20210615/4492581d9239489a9bfddb20b6fb662b.png
[2789632-ea5fd42ff0587fa7.png]: /images/20210615/7b56ed6e12a74e3bbac05512f9ff1292.png
[2789632-bd7408167487e75d.png]: /images/20210615/1050828989584e3a904dd5c45af6a4ce.png
[2789632-30f92f1d7fdcd8da.png]: /images/20210615/c7870b11b9e544d1a695dea75ae3cfd8.png
[2789632-2426a70881b26cde.png]: /images/20210615/2a71bd4dfc7b4d36af732bdc78439218.png
[2789632-a32c14adfffeb3e8.png]: /images/20210615/120ccdcd17a947c9a003cb530669ccae.png
[2789632-3b18269684ea9294.png]: /images/20210615/7f6bc99f4db4403faeedbef538442fe2.png