Jimmy那些事儿

SQL Server 2012宝典_读书笔记

发表 | 更新 | 分类 ， ， | 阅读次数

《SQL Server 2012 宝典》读书笔记

第一部分 入门

第2章 数据架构

2.2 数据库目标

OLTP

  • what：联机事务处理过程（On-Line Transaction Processing），也称为面向交易的处理过程
  • why ：最大优点是可以即时地处理输入的数据，及时地回答。
  • how ：基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果

ETL

  • what：数据仓库技术；用来描述Extract(抽取) - Transform(转换)- Load(加载)至目的端的过程。
  • why ：数据仓库利用提取-转换-加载(ETL)过程将各种格式的数据和框架转换为便于数据检索的通用格式，以从整个企业的多个数据存储中收集大量数据。
  • how ：用户从数据源抽取出所需的数据，经过数据清洗，最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去

2.3 智能数据库设计

物理架构

  • what：包括数据库的表、列、主键、外键与约束。

瀑布式开发 vs. 敏捷式开发

瀑布式开发：经历4个项目阶段：需求收集、设计、开发和实现。虽然这种方法可能对一些工作有效，但当创建软件时，用户往往直到看到了才知道他们想要的是什么。这是往往已经进入了开发阶段。

敏捷式开发：用大量短周期或迭代替代单个较长的开发过程，从而解决这个问题。每次迭代都构建了可测试的工作模型，并且用户能够使用软件，从而进一步发现他们的需求。当用户看到进展迅速并相信可以添加新特性时，他们会更愿意在软件的生命周期内引入更多特性，而不是检查在下一个版本中实现。

第二部分 建立数据库和处理数据

第6章 基本的查询流

6.1 查询流

6.1.1 Select 语法

  • Select语句的From部分将所有数据源组装进一个结果集，然后由Select语句的剩余部分对结果集执行操作。
  • Where子句作用于From组装的记录集，根据条件筛选某些行。
  • 聚合函数对数据集执行求和操作。
  • Group by 子句根据在该子句中指定的列将大量数据集分组成较小的数据集。
  • Having 对较小的数据组执行聚合函数。
  • Order by 子句确定结果子的排列顺序。默认为升序；
1
2
3
4
5
6
7
8
Select [Distinct] [Top(n)] *, columns, or expressions
  [From data source(s)]
    [Join data source
	  ON condition] (may include multiple joins)
  [Where conditions]
  [Group by columns]
  [Having conditions]
  [Order by columns];

6.1.3 查询语句的逻辑流

数据源(From) —— 条件(Where) —— 列/表达式 (col/exp) —— Order by — 谓词

  1. From，查询首先组装初始数据集。
  2. Where，筛选；筛选过程实际上是选择符合标准的行的where子句。
  3. Group by，组合数据的子集
  4. 聚合，Aggregations，选择性地对数据进行聚合；如求平均值，按列中的值对数据分组以及筛选组；
  5. Having，筛选数据的子集
  6. 列表达式：处理Select列，并计算任何表达式 [ 这个时候才涉及到列 ]
  7. Order by，排序
  8. Over，窗口函数和排名函数通过与其他聚合函数一起提供结果的单独排序的视图
  9. Distinct，从结果集中删除任何重复的行
  10. Top，选定行后，执行计算，并按所需的顺序排序
  11. Insert,Update,Delete，最后一个逻辑步骤是将数据修改操作应用到查询结果。
  12. Output，选择插入和删除的虚拟表，并返回给客户端
  13. Union，堆叠或合并多个查询的结果

6.2 From子句

6.2.3 表名称

  • 方括号：表名或字段名如果引用了sql server中的关键字，数据库会不识别这到底是关键字还是表名（还是字段名）时就必须要加；
    • 查询语句的表中加上方括号[ ] ， 目的是以声明其不是保留字 ；
    • 如果表名不是关键字，不用加方括号

6.2.4 完全限定的名称

  • Server.Database.Scheme.Table；服务器.数据库.架构.表名称
    • 常使用后两部分来限定即可；

6.3 Where条件

  • 最佳实践：找到事物的最好办法就是查找，而不是先排除不是该事物的所有东西。即where条件，声明肯定的限制条件优于否定的限制条件；
1
where col >= 10 ;  优于 where col !< 9

惊叹号! ，不是ANSI标准的SQL；

6.3.1 Between And

1
2
3
4
5
6
7
8
9
10
11
# 使用带有日期时间值的 BETWEEN 
WHERE RateChangeDate BETWEEN '2001-12-12' AND '2002-01-05';   
----下面的示例检索所在的行【datetime值】可以介于'20011212'和'20020105'(含） 之间;因为在查询中的日期值和datetime值存储在RateChangeDate而无需在日期的时间部分中指定了列。
-- 下面是结果集：
BusinessEntityID RateChangeDate
----------- -----------------------
3 2001-12-12 00:00:00.000
4 2002-01-05 00:00:00.000
----未指定时间部分时，将默认使用 12:00 A.M。 
--请注意，若某行的时间部分晚于 2002-01-05 12:00 A.M.， 则由于它处于范围之外，因此此查询不返回该行。

6.3.2 与列比较

  1. 如果函数用于where子句中的测试列，那SQL Server 在筛选where子句前被迫对每一行计算该函数；
1
2
3
--正确的写法； where col = 130 -30
--避免的情况； where col + 30 =130
  1. 对于NOT IN条件来说，如果列表中有NULL值，每行都别判定为FALSE； 证明否定命题是很难的，尤其是当NULL值也包括在内时；
1
2
3
Select 'IN' where 'A' Not In ('B', 'C');  返回 IN
Select 'IN' where 'A' Not In ('B', 'NULL');  返回空

6.3.3 使用Like搜索条件

运算符 含义 示例
% 任意长度的字符串 Email Like ‘%@%.com’
‘_’ 任意一个字符 AuthorName Like ‘张_’
[ ] 在指定范围内的任意一个字符 A Like ‘A6C8[1-5]’
[^] 不在指定范围内的任意一个字符 A Like ‘A6C8[^1-6]’

查找含通配符的表达式：

  1. 把通配符放入方括号[ ] 内
  2. 在其之前放一个转义符

6.3.4 多个where条件

  • 布尔逻辑运算的优先次序： NOT > AND > OR
1
2
3
4
5
6
7
8
9
10
Where name Like 'Chain%' or ProductID Between 320 And 324 
  And name Like '%s%'  
--1. 先执行And，即找出name中 带有 s 的名字；
--2. 再在其中寻找 name中有Chain 或者 ProduceID在[320,324]
------------------
Where (name Like 'Chain%' or ProductID Between 320 And 324 )
  And name Like '%s%'
--1. 先执行括号，即先找出name中有Chain 或者 ProduceID在[320,324]
--2. 再在其中找出name中 带有 s 的名字；

6.4 列、星号、别名和表达式

6.4.2 别名

  1. 要使用与SQL Server关键字相同的别名或包含空格的别名，可将别名让入方括号[]、单引号’’或双引号””。

  2. 插入某个列均为x的值，只要在Select中添加’x’即可

    1
    Select Name, 'x', Name+sex From table

6.5 结果集排序

6.5.1 通过列名称来指定顺序

  1. Order by 排序列可以不必是Select返回的列
  2. order by子句和选择列表中的列的顺序完全无关

6.5.2 使用表达式来指定顺序

  1. 在Order by子句中，可以使用表达式

    1
    2
    3
    4
    5
    6
    7
    8
    9
    Order by LastName + FirstName;
    ----------------------------------
    Order by 
      Case 
    	When Left(description, 5) = 'This ' Then Stuff(description, 1, 5, '')
        Else description
      End;
    --根据Case的不同情况来进行排序

6.5.3 使用列别名指定顺序

  1. Order by 子句允许别名存在，是因为逻辑上Order by 子句在组合列和别名后执行；
    • Where子句不可使用别名，因为在逻辑上在处理列及表达式前先执行Where子句

6.5.4 使用列的顺序位置来进行排序 （若改变了选择的列，则列的位置顺序会发生更改）

6.6 Select Distinc

  1. 从查询的结果集中删除重复的行

6.7 Top( ) 谓词

6.7.1 With Ties

  1. 允许最后的位置包含多行，但这多行是完全相同的
1
Select top (10) With Ties listprice From ...

6.7.2 随机行选择

  1. 使用Top(1) 返回单行，且用Newid()随机排序结果；每次将返回一个随机值
  2. 涉及到较大的表时，可用Tablesample( n Percent/Rows)选项
  3. 由于是随机选择，可通过Repeatable()来指定 [效果同R语言中的set.seed()]
1
2
3
Select top(1) Lastname From person.person Tablesample(10 Percent) -- 随机选择10%的
  Repeatable(1234)
  Order by Newid();  --随机排序结果集

第7章 关系数据库设计和创建物理数据库架构

7.1 数据库基础知识

7.1.2 表、行和列

  1. 一个行 = 一个项
  2. 一个列 = 该项相关的具体数据片段
  3. 一个单元格 = 单个项的单个数据部分

7.1.3 数据库设计阶段

  1. DDL
    • what：数据库模式定义语言DDL(Data Definition Language)。用于描述数据库中要存储的现实世界实体的语言。
    • how ：DDL数据库模式定义语言是SQL语言（结构化查询语言）的组成部分。
      • SQL语言包括四种主要程序设计语言类别的语句：数据定义语言(DDL)，数据操作语言(DML)，数据控制语言(DCL)和事务控制语言（TCL）。
  1. DML
    • what：数据操作语言(Data Manipulation Language)
      • 查询(Select)、插入(Insert)、修改(Update)、删除(Delete)

7.1.5 三个“一规则”

  1. 一个实体(表)代表一组相似的事情
  2. 一个元组(行)代表一件事情
  3. 一个特性(列)代表关于该事情的一个描述性事实

7.1.8 主键

  1. 主键：一个或一组可用于唯一标识元组(行)的属性；
    • 每个实体必须有一个主键，没有主键就不是有效的实体
  2. 候选键：所有潜在的主键；（身份证号码、雇员编号、驾驶证号码都可以做为主键）
  3. 备选键：没有选中做为主键的候选键；

7.1.9 外键

  1. 外键：主表中复制主键而来的元组(行)，用于连接两个实体(主要实体、次要实体)
    • 次要主体的外键指向主要实体的主键

7.2 数据库设计模式

7.2.3 多对多模式

  1. 在SQL DDL中实现多对多关系，必须要用到第三个表（关联表/联接表)，从而认为地在两个实体之间创建两个一对多的关系

7.3 范式

第一范式（1NF）

  • what：原子性 + 主键；求数据库的每个列的值域都是由原子值组成 + 每个字段的值都只能是单一值
    • 主键：每个数据行必须在独一无二的识别项
    • 原子性：
      • 表中不会有多个存储同类数据的列 （fruit1, fruit2）
      • 列中不会有多个类型相同的值 （apple, orange, banana）

1971年埃德加·科德提出了第一范式

第二范式（2NF）

  • what：第一范式（主键为组合键） + 没有部分依赖（所有非键的字段都一定是候选键全体字段的函数）
    • 组合键
      • 由两列以上组成的主键；
      • 组成后具有唯一性
  • how ：数据表里的所有数据都要和该数据表的键（主键与候选键）有完全依赖关系：每个非键属性必须独立于任意一个候选键的任意一部分属性。如果有哪些数据只和一个键的一部分有关的话，就得把它们独立出来变成另一个数据表。如果一个数据表的键只有单个字段的话，它就一定匹配第二范式。

有一个数据表记录了设备组件的信息，如下所示：

组件 ID (主键) 价格 供应商ID (主键) 供应商名称 供应商住址
65 59.99 1 Stylized Parts VA
73 20.00 1 Stylized Parts VA
65 69.99 2 ACME Industries CA

这个数据表的每个值都是单一值，所以它匹配第一范式。因为同一个组件有可能由不同的供应商提供，所以得把组件 ID 和供应商 ID 合在一起组成一个主键。
组件(关键词)和价格之间的关系很正确：同一个组件在不同供应商有可能会有不同的报价，所以价格确实和主键完全相关(完全依赖)。
另一方面，供应商的名称和住址就只和供应商 ID 有关(部分依赖)，这不匹配第二范式的原则。仔细看就会发现 “Stylized Parts” 这个名称和 “VA” 这个住址重复出现了两次；要是它改名了或是被其他公司并购了怎么办？这时候最好把这些数据存到第二个数据表中：

供应商 ID (主键) 名称 住址
1 Stylized Parts VA
2 ACME Industries CA

这么一来，原本的 “组件来源” 数据表就得要做相对应的改动：

组件 ID (主键主键) 价格 供应商 ID（主键、非关键词）
65 59.99 1
73 20.00 1
65 69.99 2

检查数据表里的每个字段，确认它们是不是都和关键词完全相关， 这样才能知道这个数据表是不是匹配第二范式； 如果不是的话，就把那些不完全相关的字段移到独立的数据表里。 接下来的步骤是要确保所有不是键的字段都和彼此没有相依关系，这就叫做第三范式。

第三范式（3NF）

  • what：要求所有非键属性都只和候选键有相关性，也就是说非键属性之间应该是独立无关的。

    ​

第8章 数据类型、表达式和标量函数

8.1 数据类型

8.1.1 字符数据类型

  • 长度固定：当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充，使长度达到相应的长度
  • 从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点
  • 一般来说，如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar。
类型 特点 容量
char 长度固定，8000个英文，4000个汉字 1字节
varchar 长度可变，8000个英文，4000个汉字 1字节
nchar 长度固定 Unicode编码，存储4000个字符 2个字节 [不论是英文还是汉字]
nvarchar 长度可变 Unicode编码，存储4000个字符 2个字节 [不论是英文还是汉字]
text 长度可变 1字节
ntext 长度可变 Unicode编码 2字节

Unicode或非Unicode
​ 数据库中，英文字符只需要一个字节存储就足够了，但汉字和其他众多非英文字符，则需要两个字节存储。如果英文与汉字同时存在，由于占用空间数不同，容易造成混乱，导致读取出来的字符串是乱码。

​ Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。而前缀n就表示Unicode字符，比如nchar,nvarchar，这两种类型使用了Unicode字符集。

8.1.2 数值数据类型

数据类型 说明 大小
Bit 1或0 1位
Tinyint 0~255整数 1字节
Smallint -32,768~32,767 整数 2字节
Int -2,147,483,648~2,147,483,647 整数 4字节
Bigint -2$^{63}$ ~ 2$^{63}$-1 的整数 8字节
Decimal / Numeric -10$^{38}$ +1 的固定精度数值 根据长度而变化
Money -2$^{63}$ ~ 2$^{63}$-1 的数值，精确到万分之一 8字节
SmallMoney -2,147,483,648~2,147,483,647 的数值，精确到万分之一 4字节
Float -1.79E+308 ~1.79E+308 的浮点数值 4或8字节
Real 24位精度的浮点值 4字节

浮点数：伴随着因为无法精确表示而进行的近似或舍入。

在计算机科学中，浮点（英语：floating point，缩写为FP）是一种对于实数的近似值数值表现法，由一个有效数字（即尾数）加上幂数来表示，通常是乘以某个基数（计算机中通常是2）的整数次指数得到。以这种表示法表示的数值，称为浮点数。这种表示方法类似于基数为10的科学计数法。

浮点指的是带有小数的数值。

大部分计算机采用二进制（b=2）的表示方法。位（bit）是衡量浮点数所需存储空间的单位，通常为32位或64位，分别被叫作单精度和双精度。

8.1.3 日期/时间数据类型

数据类型 说明 字符大小
Datetime 1553年1月1日开始至9999年；Jan 1, 1753，精度3毫秒 8字节
Smalldatetime 1900年至2079年6月6日；精度1分钟 Jun 6, 2079
Date 0001年至9999年； 3字符
Time(2) 时间值，精度可变 3-5字节
Datetime(2) 0001年至9999年，精度可变，从0.01秒到100纳秒 6-8字节
Datetimeoffset

8.2 构建表达式

8.2.1 运算符

  • 加号(+)运算符的两种作用

    • 算术表达式
    • 字符串串联
    1
    2
    3
    Select 'abc'+'da'; / Select col1+col2 From 
    Select 'abc'+Convert(varchar(10),'21');

    如果串联字符串和整数，必须将整数转为字符串

8.2.2 按位运算符

  1. 布尔 exclusive or 运算符 xor ( ^ )

    • 只有一个为真，返回True；两个都为真，返回False
    1
    Select 1^1

  2. 按位 Not ( ~ )

    • 对表达式中每个逻辑求反。
    1
    Select (~)@A

    按位Not 不能和布尔表达式一起使用

8.2.3 Case 表达式

  • 最大的优点是可以与Select语句“内联”
  • 若else缺失，表示其他值返回NULL
  • Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略；
    • 相等判断的话，顺序也要注意
    • 布尔判断的话，例如根据金额大小来判定等级，必须要最高的金额写在最前面；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
## 两者区别
1. 输入表达式，只能用于等同性(=)检查
2. 布尔表达式，不局限于等同行(=)检查
--用输入表达式，将与每个where子句中的值比较
Case <input expression>  --只能用于等同性检查，而不进行其他比较
  when <when exp> then <result exp>
  [n...]
  [else <result exp>]
End
--给每个when子句提供一个布尔表达式，求值为TRUE或FALSE [布尔值是“真” True 或“假” False 中的一个]
Case
  when <Boolean exp> then <result exp>
  [n...]
  [else <result exp>]
End

  1. 简单Case：case 简单表达式只能用于等同性检查，而不进行其他比较

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    # 在SELECT语句中，CASE 简单表达式只能用于等同性检查，而不进行其他比较
    SELECT ProductNumber, Category =  
          CASE ProductLine     --表示如果 ProductLine = R 则返回 Road； Case之后的变量，为比较的变量
             WHEN 'R' THEN 'Road'  
             WHEN 'M' THEN 'Mountain'  
             WHEN 'T' THEN 'Touring'  
             WHEN 'S' THEN 'Other sale items'  
             ELSE 'Not for sale'  
          END,  
       Name  
      FROM Production.Product ； 
    --
    Select top 10 SalesOrderID % 10 As 'OrderLastDigit',ProductID % 10 As 'ProductLastDigit',
    "How Colse ?" =  CASE SalesOrderID % 10 
             WHEN  ProductID % 1  THEN 'Exact Match'   -- 可在When子句中引用第二个列来做判断
             WHEN  ProductID % 1-1 THEN 'Within 1'  
             WHEN  ProductID % 1+1 THEN 'Within 1'  
             ELSE 'More Than One Apart'  
          END  
    FROM Sales.SalesOrderDetail ；
    -- % 表示返回余数； 5%2 返回1
    -- 变量名在Case之前使用双引号（“”）说明，该变量是新创建的变量;但不建议如此；

    ​

  2. 布尔型Case：**不局限于**等同行(=)检查

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    -- 没有输入表达式（即Case关键字与第一个When之间的部分）
    -- When表达式必须求值为一个 布尔值；（Case简单语句中，When的表达式可以为1、3、Price+1[含运算]）
    Select top 10 SalesOrderID % 10 As 'OrderLastDigit',ProductID % 10 As 'ProductLastDigit',
    "How Colse ?" =  CASE   					  -- 没有输入表达式
       WHEN (SalesOrderID % 10) < 3 THEN 'Ends With Less Than Threes' 
       WHEN ProductID =6 THEN 'ProductID is 6'     -- 可在When子句中引用第二个列来做判断
       WHEN ABS(SalesOrderID % 10 - ProductID) <= 1 THEN 'Within 1'  
       ELSE 'More Than One Apart'  
          END  
    FROM Sales.SalesOrderDetail ；
    # 可在条件表达式中混合搭配的字段
    # 可执行为任何表达式，只要最后的结果为布尔值

8.2.4 Iif与Choose函数

  1. iif函数是case语句的简写版本：iif(boolean_exoression, true_value, fasle_value)

  2. Choose函数是一个逻辑运算符；返回数组中的第x列

    Choose(index, var1, var2, var3) --index为索引；若超出数组的边界，会返回空值

    Select Choose(3, 'Linda', 'Tiger', 'Bears') # 返回Bears

8.2.5 使用空值

  • what：空值NULL表示不存在的值，是一个未知值；并不表示0；
  • how ：包含空值的任何表达式结果均是一个未知值
    • Null + 1 = Null

聚合函数中SUM( ) 与AVG( ) 会自动排除NULL进行计算；

Count( * ) 会计算空值；但Count( col )会排除空值

1
# 测试空值 - Is Null
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
## 处理空值 
# Isnull() --将NULL替换为某个值
Select Isnull(col, 0)  -- 对col列进行搜索，并将空值NULL转换为0；也可以是其他任意值/字符串
--isnull(soucre_expression, prlacement_value)  # isnull是T-SQL特有的函数
-----------------------------------------------------------------------------
# Coalesce()
Select Coalesce(Null, Null+1, 1+2, "abc")  返回3
--Coalesce(expression, expression,... ) # 接受一系列表达式或列，返回第一个非空值
-----------------------------------------------------------------------------
# Nullif()
--Nullif()接受两个参数；如果他们相等，则返回空值；否则，返回第一个参数；

8.3 标量函数

  • what：标量函数返回单个值
    • 包括日期时间函数、数值函数、系统函数

8.3.2 日期和时间函数

函数名 描述
Getdate 返回当前服务器的日期和时间
Current_timestamp 除了ANSI标准，等同于Getdate
Getutcdate 返回当前服务器的日期和时间，并转化为格林威治标准时间
Sysdatetime 返回当前服务器的日期和时间
Sysutcdatetime 返回当前服务器日期，并转化为格林威治标准时间
Sysdatetimeoffset 返回Datetimeoffset值
  • 日期处理函数
函数名 描述
Dateadd 在指定的日期上累加数值得到新的日期；dateadd(datepart,number,date) SELECT DATEADD(yyyy, 4, ‘01/09/2003’) 返回：2007-01-09
datepar是参数的格式：datepart=yy(年)，mm(月)，qq(季度)；date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。
DATEDIFF 返回两个日期的差值 ； datediff(datepart,startdate,endate) SELECT DATEDIFF(dd, ‘02/05/2003’, ‘02/09/2005’) 返回：735
DATEPART 返回指定日期部分的整数（整数形式） SELECT DATEPART(dd, ‘01/09/2003’) 返回：9
DATENAME 返回指定日期部分的字符串（字符串形式）；工作日(dw)、周(wk)、日(dd)、月(mm) SELECT DATENAME(dw, ‘02/02/2009’) 返回: 星期一
Eomonth 返回针对指定开始日期的月份的最后一天
YEAR 返回指定日期“年”部分整数 SELECT YEAR(GETDATE()) 返回：当前年份整数
MONTH 返回指定日期“月”部分整数 SELECT MONTH(GETDATE()) 返回：当前月份整数
DAY 返回指定日期“日”部分整数 SELECT DAY(GETDATE()) 返回：当前日期整数
查询：日期的应用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 获取当前时间 - getdate
select getdate();
# 返回指定的时间 - dateadd
select dateadd(dd, 3, '2017-01-31'); 
	# datepart：yy/qq/mm/ww/dd/hh/mi/ss/ms ; 
	# num为正或为负；
--dateadd()仅接受提取日期部分
# 计算两个时间差 - datediff
select datediff(dd,'2016-06-01', '2017-01-31'); # yy/qq/mm/ww/dd/hh/mi/ss/ms
--datediff(date_type,startdate , enddate)
# 取出时间的某一部分 - datename/datepart
select datename(dd, '2017-01-31');  # datename 字符串形式
select datepart(dd, '2017-01-31');  # datepart 整数形式
# 获取日期的年份/季度/月度/日期等 
select year(getdate());  quarter/month/day
# 返回针对指定开始日期的月份的最后一天 - Eomonth
Select Eomonth('2016-02-32',1)  # 返回 2016-03-31
--Eomonth(start_date, month_to_add)
参考：日期缩写参考
  • datediff / datename / datepart /dateadd
日期部分 缩写
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y # 查询date在当年是第多少天. 一年中的第几天；
day dd, d
week wk, ww # 查询date在当年中是第几周 / 以周为单位的间隔数
weekday dw # 一周中的第几天(星期几)
Hour hh
minute mi, n
second ss, s
millisecond ms

8.4 字符串函数

函数名 功能描述 举例
Len 返回指定字符串的字符个数(而不是字节)，其中不包含尾随空格 SELECT LEN(‘李丽然作者’) 返回:5
Datelength 返回指定字符串的字节数 SELECT DATALENGTH(‘中国人’) 返回：6
UPPER 将小写字符转换成大写字符 SELECT UPPER(‘book图书表’) 返回:BOOK图书表
Ltrim 返回去掉左侧空格的字符串 SELECT LTRIM(‘ Authors’) 返回: Authors
CHARINDEX 查找一个指定的字符串在另一个字符串中的起始位置 SELECT CHARINDEX(‘L’, ‘HELLO’, 1) 返回:3
LEFT 返回字符串中从左边开指定个数的字符 SELECT LEFT(‘zhangsan’, 2) 返回:zh
Substring 返回字符串的一部分：从字符串串的起始位置连续取指定个数的子串 SELECT SUBSTRING(‘我爱我的家乡’,3, 2) 返回：我的
Replace 替换一个字符串中的字符 SELECT REPLACE(‘我爱我的家乡家乡’, ‘家乡’, ‘学校’) 返回: 我爱我的学校学校
Stuff 将一个字符中删除指定数量的字符，并插入另一个字符
Concat 将多个字符串组合为单个字符串

使用字符串字面量时，通过输入两个单引号转化为一个单引号

Replace(name, ‘’’’, ‘’) ; Life’’s Great! 被解释为 Life’s Great!

行数&字符数
1
2
3
4
5
6
7
8
9
10
# 返回观测值的行数 - count 
SELECT COUNT(name) FROM my_contacts;
--Count(*) 计算含空值的行数
--Count(col) 计算不含空值的行数
# 返回字符串的字符个数 - len
SELECT LEN('中国人') ;  # 返回:3
# 返回字符串的字节数 - datalength
SELECT DATALENGTH('中国人');  # 返回：6
去空格
1
2
# 去空格 - ltrim / rtrim
select ltrim(rtrim(' "左右都没有空格"  '));  # 左右去空格
1
2
3
4
5
# 生成空格 - space
select space(2);  # 生成2个空格
SELECT RTRIM(LastName) + ',' + SPACE(2) +  LTRIM(FirstName) FROM Person.Person
# 剪裁姓氏，并将逗号、两个空格和 Person 中的 AdventureWorks2012 表列出的人员名字串联起来
取子串
1
2
3
4
5
# 取子串：特定位置 - substring
select substring(name,1,2);  # 返回na;
# 取子串：左/右 - left /right
select left(ltrim( name), 3);  # 返回nam;
返回特定位置
1
2
3
4
5
6
7
# 返回位置：起始位置 - charindex
select charindex('L', 'HELLO', 1);  # 返回:3; 1表示第一次出现
--charindex(serach string, string ,starting position) ；第三个参数默认为1，可不写
# 返回位置：表达式中某模式第一次出现的起始位置 - patindex
patindex('%123%','abc123def');  # 返回4
--允许通配符的使用
替换
1
2
3
4
5
6
# 替换 - replace
select replace('abcdef','cde','xxx'); 返回 abxxxf
# 删除&替换 - stuff()
Select stuff('abcdef', 3, 2, '123')  -- 返回ab123ef；从第三个位置开始删除2个字符，并插入123
--stuff(string, insertion position, delete count, string inserted);
合并
1
2
3
# 合并为一个字符串 - concat()
Select Concat(Null, 'Patrick ', 1, ' LeBlacn')
--隐式地将所有值转换为字符串，将空值转为空字符串
格式转换
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 转换：大小写 - upper/lower
select upper('abc');  # 返回 ABC
# 转换：反转 - reverse
select reverse('abc');  # 返回'cba'
# 转换：字符形式 - char
select char(213);
# 转换：字符串形式 - str
select str(123.45, 6,1);  # 把数值转换成字符串格式
--返回123.5； 将123.45转为6个位置的字符串，数字的小数部分舍入为1为小数；
# 转换：ascii码 - ascii
select ascii(123) as '123'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 格式的转换、显示 - convert : 将第2个参数转换为第1个参数所指定的类型 / 用不同的格式显示日期/时间数据。
-- CONVERT (data_type[(length)], expression [, style]) ;
-- [,style] 日期格式样式
SELECT CONVERT(DateTime, '2020-09-09');  # 返回: 2020-09-09 00:00:00.000
SELECT CONVERT(varchar(5), 92.89);  # 返回：92.89
SELECT CONVERT(varchar(11), GETDATE(), 121);  # 返回：2010-03-24
常用日期格式：
  - 23 ：日期格式 yy-mm-dd
  - 111：日期格式 yy/mm/dd
  - 120：日期格式 yyyy-mm-dd hh:mi:ss(24h)
  - 121：日期格式 yyyy-mm-dd hh:mi:ss.mmm(24h)
  - 105：日期格式 dd-mm-yy
  - 110：日期格式 mm-dd-yy

Data_type : INT / DECIMAL(10,2) / CHAR() / VARCHAR() /

8.6 数据类型转换函数

Cast( )
Convert( )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 格式的转换、显示 - convert : 将第2个参数转换为第1个参数所指定的类型 / 用不同的格式显示日期/时间数据。
-- CONVERT (data_type[(length)], expression [, style]) ;
-- [,style] 日期格式样式
SELECT CONVERT(DateTime, '2020-09-09');  # 返回: 2020-09-09 00:00:00.000
SELECT CONVERT(varchar(5), 92.89);  # 返回：92.89
SELECT CONVERT(varchar(11), GETDATE(), 121);  # 返回：2010-03-24
常用日期格式：
  - 23 ：日期格式 yy-mm-dd
  - 111：日期格式 yy/mm/dd
  - 120：日期格式 yyyy-mm-dd hh:mi:ss(24h)
  - 121：日期格式 yyyy-mm-dd hh:mi:ss.mmm(24h)
  - 105：日期格式 dd-mm-yy
  - 110：日期格式 mm-dd-yy

Data_type : INT / DECIMAL(10,2) / CHAR() / VARCHAR() /

convert - style的参考值

  • 一位或两位数字样式提供两位数的年份；3位数字样式提供4位数字的年份；
代码 Style 格式
100 或者 0 mon dd yyyy hh:miAM （或者 PM）
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
109 或者 9 mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
113 或者 13 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
120 或者 20 yyyy-mm-dd hh:mi:ss(24h)
121 或者 21 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130 dd mon yyyy hh:mi:ss:mmmAM

第9章 联接、子查询和CTE合并数据

9.1 使用联接

  • what：将两个数据集相乘，并对结果进行限制。这样只返回两个数据集的交集。
  • why ：横向合并两个数据集，并通过匹配一个数据源的行与另一个数据源的行，从组合中产生新的数据集。

当涉及多个联接时

一定要用较小规模的数据对查询进行单元测试；

并坚持使用左外联接

9.1.1 内联接

Inner Join

  • 在连接条件中使用等于号（=）运算符，其查询结果中列出被连接表中的所有列，包括其中的重复列。

  • 在连接条件中使用除等于号之外运算符（>、<、<>、>=、<=、!>和!<）

  • 多个数据源的联接（顺序并不重要）

    • 所要获得字段的表要写在最前面；即From之后；
    • 可实现A联接B，B联接C，C联接D；
    1
    2
    3
    4
    5
    Select cst.companyname, prod.name 
      From Customre As cst
    	Inner join salesorderhead As soh On cst.customerid = soh.customerid
    	Inner join salesorderdetail As sod On soh.salesorderid = sod.salesorderid
    	Inner join product As prod On sod.productid = prod.productid

9.1.2 外联接

  • what：以一个表为基准表，进行联接；

    • 若使用外联接，顺序非常重要

  • how ：无论是否匹配，外联接都包含所有数据

  • 外联接中的条件设置（执行逻辑）

    • 当条件位于Join子句中，先包括外表的所有行，然后用条件包括第二个表中的行
    • 当条件位于Where子句中，先执行联接，然后将条件应用于联接行
    1
    2
    3
    4
    5
    6
    7
    # 条件位于Join子句
    Select a.col, b.col 
      From table As a
      Left join table2 As b 
    	On a.id = b.id 
    	And a.Lastname = 'Adams'  --该条件限制的是第二个表中的行
    	[And a.Lastname = b.name]  --也可以再进行限制

    –

1
2
3
4
5
6
# 条件位于Where子句
Select a.col, b.col 
  From table As a
	Left join table2 As b 
	   On a.id = b.id 
  Where a.Lastname = 'Adams'   --该where条件是对联接后的表进行限制

​

9.1.4 交叉联接

Cross Join

  • 不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积
    • （例如：T_student和T_class，返回4*4=16条记录）
    • 如果带where，返回或显示的是匹配的行数。

9.2 差集查询

  • what：分析两个数据集之间相关性的查询；
  • why ：用于查找不存在或不匹配的数据
  • how ：一般需分两步来执行
    1. 联接
    2. 设置第二个数据集的主键为NULL

左差集查询

  • 不在右表中的左表数据集；【可用Inner Join 中不等号为条件进行联接 <>】
1
2
3
4
Select c.customerid, so.ordernumber 
  From customer As c
  Left join salesorder As so On c.customerid = so.customerid
  Where so.ordernumer Is Null;  --设置第二个表的主键为NULL

全差集查询

1
2
3
4
5
6
Select c.customerid, so.ordernumber 
  From customer As c
  Left join salesorder As so On c.customerid = so.customerid
  Where so.customerid Is Null 
	or c.customerid Is Null 
--设置两个表的的主键为NULL

9.3 使用联合

9.3.1 Union All

  1. 返回每个结果集中的所有行，且不考虑是否存在重复行
  2. 每个Select必须具有相同的数量、类型；
  3. 列名或别名由第一个Select确定
  4. Order by子句放在最后，并且对所有结果进行排序，且列名必须是第一个Select语句中存在的
  5. 可用Select Into，但Into必须放在第一个Select语句中

9.4 子查询

9.4.1 简单子查询

  • what：子查询是独立运作的；

  • how ：运行逻辑

    1. 非相关子查询被执行一次
    2. 结果传输到外查询
    3. 外查询被执行一次

    ​

公用表表达式

  • what：CTE (Comman Table Expression )；在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。

  • why ：确定查询结果可当做临时视图来使用；即后续的查询可以引用公共表表达式中的表及字段；

    • CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。
    • 与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。
  • how ：CTE使用With子句，而With子句定义了CTE；在With子句内分别是名称、别名、AS、括号、Select查询语句
    • CTE自身只是一个不完整的SQL语句
    • 一旦With子句中定义了CTE，查询的主要部分就可以使用其名称引用CTE；就像CTE是其他任何表源一样；
    • 若将多个CTE包含在相同的查询中，在主查询之前确定CTE的顺序，并用逗号分隔；并且后面的CTE可以引用在它之前定义的任何CTE
    • CTE不能嵌套

With子句的表达式格式

1
2
3
4
5
6
7
8
9
10
11
With CTEname [Col Aliases]
  As (Select ...From ...
  )
------------------------------------------------------
With 
  CTEname1 (col names) As (Select) , 
  Ctename2 (col names) As (Select)
Select ...
  From CTEname1
	Inner join CTEname2 On
  • 嵌套子查询：子查询可以多个嵌套

9.4.5 相关子查询

  • what：先执行外查询，相关子查询的运行要引用外查询中的列
  • why ：对于复杂的Where条件来说很有用
  • how ：执行逻辑
    1. 先执行一次外查询
    2. 在外查询中对每一行执行一次子查询，把外查询中的值取代为子查询的每一次执行
    3. 子查询的结果要整合到结果集中

子查询可出现在Select子句中，也可以出现在Where 子句中；

判断是否为相关子查询的标准为是否引用了外查询中的列；

没有引用外查询中的列，为非相关子查询；先于外查询运行

引用了外查询中的列，为相关子查询，后于外查询运行

第10章 聚合、窗口化和排名数据

10.1 聚合函数

聚合是在对From和Where子句筛选后的数据集进行聚合计算；即运营逻辑是在其之后执行

10.1.1 基本集合

  • 一旦查询包含了聚合函数，那么每一列必须参与到聚合函数中。
函数名 功能描述
AVG 平均值
SUM 求和
MAX/MIN 求最大值/最小值
COUNT 计算非空单元格（ 返回 int 数据类型值）
count_big 计算非空单元格（返回 bigint 数据类型值）
VAR 方差 # 平方
varp 总体方差
STDEV 标准差
stdevp 总体标准差

Count(*)，计数时也将NULL计入；
其他所有聚合函数，包括Count(col_name)的形式，计算时均已排除了NULL

表a，观测值10行，其中2个NULL；
Count(*) # 返回10；

Count(列名) # 返回8

除非，可用 isnull() 函数进行转换，来计算；

例如，对表a求平均值，若直接用avg() ，其分母为8；

若想使得其分母变成10，应添加 case when isnull(col_name,0) then col_name else end

10.1.2 聚合、平均和空值

  1. 除Count(*)外，所有聚合函数忽略空值；

avg( ) $\ge$ $sum( ) \over count(*)$

  1. Count(*)，计数时也将NULL计入；

    • 其他所有聚合函数，包括Count(col_name)的形式，计算时均已排除了NULL

    表a，观测值10行，其中2个NULL；

    Count(*) # 返回10；

    Count(列名) # 返回8

  2. 除非，可用 isnull() 函数进行转换，来计算；

    例如，对表a求平均值，若直接用avg() ，其分母为8；

    若想使得其分母变成10，应添加 case when isnull(col_name,0) then col_name else end

10.2 集合中的分组

  • Group by 会根据某一列的值将数据集自动分成子集。
  • 数据集被分成子组之后，聚合函数在每一个子组上执行。
  • 对于Group by子句，空值NULL被认为是相等的，并被分组到单个结果行
  • Group by不局限于对列分组，也可以对表达式执行分组（但该表达式必须与Select中的相同）

10.2.2 分组集

  • 对分组集来说，汇总行是每个子集中的每个唯一值组成的行

10.2.3 筛选已经分组的结果

  • 使用Having子句对已分组的结果进行筛选

10.3 窗口化和排名

10.3.2 Over子句

  • 在窗口内分区：Partition by
    • 执行逻辑：先对查询结果进行排序，之后通过Partition by的列进行分区；
1
2
3
4
5
6
7
8
9
10
11
Sum(cola) Over(partition by col1 order by col2)
--其他聚合函数：avg/max/min /count 
# 根据分组求和
Over(Partition by col) --根据col1的分组对cola进行求和； [若同一组别有多个维度，则求和的值是相同的]
# 根据排名求累计和
Over(Order by col2)--根据col2的顺序对cola列累计求和； [若同一组有多个维度，求和的值是累加的]
# 根据分组后的排名，求累积和
Over(Partition by col1 order by col2) --根据Col1的分组进行排名得出Col2的次序，并按col2的次序对cola进行分组求和
1
2
# 移动平均
CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)),1) AS MovingAvg

10.4 排名函数

  • Rank、Row_number、Dense_rank、Ntile
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 排名：无重复排名 - Row_number（组内连续的唯一的)
row_number() over (order by col2);  # 对所有进行排名
row_number() over (partition by col1 order by col2);  # 根据COL1分组，在分组内部根据 COL2排序
-------------------------------------------------
#　排名：有重复排名 - Rank （若出现字段值相同，序号一样，下一个跳过1位[排名是非连续的]）
rank() over([partion by col1] order by col2); 对所有进行排名
ProductID   Name                   LocationID   Quantity Rank  
494         Paint - Silver         3            49       1  
495         Paint - Blue           3            49       1  
493         Paint - Red            3            41       3 
-------------------------------------------------
# 排名：有重复排名 - Dense_rank （若出现字段值相同，序号一样，后一个不跳过[排名是连续的]）
dense_rank() over([partion by col1] order by col2)
ProductID   Name                               LocationID Quantity Rank  
494         Paint - Silver                     3          49       1  
495         Paint - Blue                       3          49       1  
493         Paint - Red                        3          41       2 
-------------------------------------------------
# 排名：对序号进行分组处理 - Ntile
ntile (4) over ([partion by col1] order by col2);  # (4)表示分为4组

ntile函数的分组依据（约定）：

　　首先系统会去检查能不能对所有满足条件的记录进行平均分组，若能则直接平均分配就完成分组了；若不能，则会先分出一个组，这个组分多少条记录呢？就是 （总记录数/总组数）+1 条，之所以分配 （总记录数/总组数）+1 条是因为当不能进行平均分组时，总记录数%总组数肯定是有余的，又因为分组约定1，所以先分出去的组需要+1条。

　　分完之后系统会继续去比较余下的记录数和未分配的组数能不能进行平均分配，若能，则平均分配余下的记录；若不能，则再分出去一组，这个组的记录数也是（总记录数/总组数）+1条。

举个例子，将53条记录分配成5组，53%5 = 3不能平均分配，则将余数3平均分配到前3组 （余数 = 分配的组数），然后比较余下的 53-(11*3)=20 条记录能否平均分配给未分配的2组，能平均分配，则剩下的2组，每组各20/2=10 条记录，分配完成，分配结果为：11，11，11，10，10。

  • Offset & Fetch ：完成分页
1
2
3
4
5
# Offset & Fetch 必须结合使用
Select ... From
  Order by 
  Offset n rows  # 表示跳过n行
  Fetch Next 20 rows only  # 表示返回之后的20行

第11张 通过视图投影数据

11.2 基本视图

1
2
3
Create View schema.table
As
  Select

第12章 在SQL Server中修改数据

12.1 插入数据

12.1.1 插入简单的值行

  1. 把值插入到一个新行时，每个值对应一个插入列（一一对应），但表中列的顺序是无关紧要的；
1
2
3
Insert [Into] schema.table [ (colums, ...) ]
  Values (value,...),
	(value,...);

虽然并不需要列出表中的每一行，但如果一列出现了，那么Insert命令必须有一个值；如果插入时省略了该列的值，则会在该列插入NULL

若该列有一个默认约束，则插入默认值；

若该列没有默认约束，且可以为NULL，则插入NULL

若该列没有默认约束，且不可为NULL，则会报错

  1. 若要在标识列中插入数据，要使用Set Identity_insert on / off关键字
1
2
3
4
Set Identity_insert dbo.address On
  Insert Into dbo.address (AddreddID, Addressl, city, state, county)
  Valuse(999, )
Set Identity_insert dbo.address Off

12.1.2 从Select语句中插入结果集

1
2
Insert [Into] schema.table [ (colums,...)]
  Select columns From data [Where conditions];
  1. schema.table 必须存在
  2. into schema.table中的列必须与之后Select中的列相同
  3. 若schema.table中有5列，只选取其中3列插入，则其他2列的观测值对NULL

12.1.3 从存储过程插入结果集

1
2
Insert [Into] schema.table [ (colums,...)]
  Execute storedprocedure;

12.1.5 在插入数据时创建表

1
2
Select colums 
  Into newtable From data [where conditions];

若Select…Into 命令只从一个表中抽取数据，而Select语句不包含数据类型转换函数，那么表列和null设置保持原状的可能性很大；但键、约束和索引会丢失。

12.2 更新数据

12.2.1 更新单个表

1
2
3
4
5
Update schema.table
  Set column = expression,
	column = value 
  /*[From data] SQL Server 特有的T-SQL扩展，用来进行联接；正常情况下应在where子句中创建子查询*/
  [Where conditions];

Update 只能更新单个表

12.2.2 执行全局搜索与替代

1
2
3
4
5
# replace(data, 'aaa', 'bbb')
Update address
  Set county = Replace(county, 'sun', 'dark')  -- 将county列中所有的sun替换为dark
  Where county Like '%shine' ;

12.2.3 更新数据时引用多个表

12.3 删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
# delete
Delete [From] schema.table
  /*[From data] SQL Server 特有的T-SQL扩展，用来进行联接；正常情况下应在where子句中创建子查询*/
  [Where conditions];
# 删除所有行
Delete From schema.table
--不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
---------------------------------------------------------------------------------
# 删除所有行 - truncate
Truncate Table dbo.address

truncate 、delete与drop区别

  • 相同点
  1. truncate和不带where子句的delete、以及drop都会删除表内的数据drop、truncate都是DDL语句(数据定义语言)，执行后会自动提交。Delete是DML语句(数据库操作语言)
  • 不同点

  1. truncate 和 delete 只删除数据不删除表的结构(定义)；drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

  2. delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

  3. delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动。drop 语句将表所占用的空间全部释放。truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage；truncate 会将高水线复位(回到最开始)。

  4. 速度：drop> truncate > delete

    • TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同：二者均删除表中的全部行。但TRUNCATE TABLE 比DELETE速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

  5. 安全性：

    • 想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。

    • 小心使用 drop 和 truncate，尤其没有备份的时候

    • 使用上，想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大
    • 想删除表,当然用 drop

  6. 对于由FOREIGN KEY 约束引用的表，不能使用TRUNCATE TABLE，而应使用不带WHER 子句的DELETE语句。由于 TRUNCATE TABLE不记录在日志中，所以它不能激活触发器。

  7. TRUNCATE TABLE不能用于参与了索引视图的表。

12.3.2 级联删除

  • 引用完整性（RI）指的是任何辅助行外键都都不能指向主行主键，除非主行缺失存在。
  • 如果一个外键值在某个地方指向了主键，删除主键就会失败（除非先删除指向主键的外键）

12.4 合并数据

  • Meger语句。使用 MERGE 语句在一条语句中执行插入、更新或删除操作。
    • 有条件地在目标表中插入或更新行。
      • 如果目标表中存在相应行，则更新一个或多个列；否则，会将数据插入新行。
    • 同步两个表。根据与源数据的差别在目标表中插入、更新或删除行。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Meger flightpassengers As f         -- MERGE 子句用于指定作为插入、更新或删除操作目标的表或视图
  Using checkin as c                --USING 子句用于指定要与目标联接的数据源
	On c.lastname = f.lastname      --ON 子句用于指定决定目标与源的匹配位置的联接条件
	And c.firstname = f.firstname
	And c.flightcode = f.flightcode
	And c.flightdate = f.flightdate
  When Matched 
	Then Update Set f.seat = c.seat
  When Not Matched By Target        --当Soucre表与Target表（基准表）不匹配时，对Target表进行操作
	Then Insert (Firsrname, Lastname, Flightcode, flightdate, seat)
  When Not Matched By Source        --当Target表与Soucre表（基准表）不匹配时，对Target表进行操作【相同】
	Then Delete;
--WHEN 子句（WHEN MATCHED、WHEN NOT MATCHED BY TARGET 和 WHEN NOT MATCHED BY SOURCE）基于 ON 子句的结果和在 WHEN 子句中指定的任何其他搜索条件指定所要采取的操作。
--OUTPUT 子句针对插入、更新或删除的目标中的每一行返回一行。
--必须由分号进行终止
--必须是一对一匹配；一对多匹配是不允许的
--联接条件必须是确定性的，也就是可重复的

12.5 返回修改后的数据

  • Output子句可以访问插入的和删除的虚拟表，以及任何在From自引用的数据源来选择要返回的数据。

Output子句有一个较为高级的应用，可以把输出数据传输到外查询。

12.5.1 从插入返回数据

1
2
3
Insert Into personlist
  Output Inserted.*    -- Inserted.
  Valuse(7777, 'Jane', 'Doe');

12.5.2 从更新返回数据

  • 可同时返回更新前、更新后的数据
1
2
3
4
5
Update personlist
  Set firstname = 'Jane', Lastname = "Doe"
  Output Deleted.firstname oldfirstname, Deleted.lastname oldflastname,  --Deleted.column oldcolumn
         Inserted.firstname newfirstname, Inserted.lastname newlastname
  Where businessentityID = 7777

12.5.3 从删除返回数据

1
2
3
Delete From personlist
  Output Deleted.*
  Where ...

12.5.4 从合并返回数据

1
2
3
4
...
  Output deleted.column, deleted.column,
         $action,                             -- 显示数据库操作的行为（为Insert、Delete、Update)
		inserted.column, inserted.column ;

12.5.5 把数据库返回到表中

1
2
3
4
5
6
7
8
9
10
Declare @Deletedperson Table(
  businessentityID Int Not Null Primary Key,
  lastname Varchar(50) Not Null,
  firstname Varchar(50) Not Null
)
Delete dbo.personlist
  Output Deleted.colunm, Deleted.column
  Into @Deletedperson
  Where bussinessentityID = 2;

第三部分 高级T-SQL数据类型和查询技术

第四部分 使用T-SQL编程

第16章 使用T-SQL编程

16.1 T-SQL批处理

16.1.1 T-SQL批处理

  1. 终止批处理：批处理分隔关键字是GO。批处理关键字必须是这一行的唯一关键字；

    • 关键字GO之后可添加一个注释

  2. 切换数据库：把USE命令插入到批处理中

    ​

16.1.2 T-SQL格式化

  1. 语句终止：在每个命令的末尾放置一个分号（；）
    • 在CTE前面必须要放一个分号（当CTE不是批处理的第一条语句时）
    • 不要把分号放在If 或者 While 条件之后
    • 不要把分号缝在 End Try 之后
    • 语句终止符必须跟在Meger之后

16.2 操作变量

16.2.1 变量默认值和作用域

  1. T-SQL变量使用 Declare创建；
    • Declare变量跟 变量名 和 数据类型，并用分号结尾；
    • 多个逗号隔开的变量可以用单个Declare 声明
    • 变量赋值用分号隔开；
1
2
3
Declare @test Int, @testtwo Varchar(20) ;
Set @test = 1 ;
Set @testtwo = 'a value' ;

16.2.2 使用Set 和 Select 命令

  1. Set 与 Select 对比
命令 描述
Set 限制在从表达式中检索数据；
一次只能设置一个变量
Select 可从数据源中检所数据，还可包括其他Select子句(From、Where等）；可使用函数
一次课设置多个变量

Set命令可以使用访问数据源的标量子查询（一个变量）

如果你希望在确保没有行的情况下将变量设置为NULL，以及在不止一行的情况下获得一个错误，这是最佳做法；

Select 命令在检索多行时，只会将最后一行的值存储在变量中

1
2
3
4
5
6
7
8
9
10
11
12
Use adventureworks2012
GO
Declare @productID Int, @producename varchar(25);
Set @productID = 782;  --声明一个变量
Select 
	@productID = productID,  -- 声明多个变量；
	@productname = @productID + name --声明变量可叠加使用
  From production.product
  Order by productID;

Select 声明，即将 @var = value 作为一个整体，当做Select查询中的列去看待即可

声明变量可叠加使用

  • 如果Select语句没有返回行，Select语句不会影响变量
1
2
3
4
5
6
7
8
9
10
GO
Declare @productID Int, @producename varchar(25);
Set @productID = 999;
Select 
	@productID = productID,  -- 声明多个变量；
	@productname = @productID + name --声明变量可叠加使用
  From production.product
  Where productID = 1000;  -- 由于ProductID 没有1000，故ProductID仍未NULL

16.2.3 增量变量

  • 声明变量的赋值可以直接用等号（=）联接
  • 可以叠加使用，并且可使用算术运算符
1
2
3
Declare @x int = 1;  -- 直接用等号赋值
Set @x = @x +5 --可进行算术运算

16.2.4 条件选择

  • 在Select语句中运用 Where子句，进行条件选择
1
Select @var = expression Where BooleanExpression;

16.2.5 在SQL查询中使用变量

1
2
3
4
Declare @productID int = 999;
Select name From production.product
  Where productID = @porductID;

16.3 过程流

16.3.1 使用条件T-SQL的If命令

  • 一个if，一个命令的执行；并且没有Then和End来终止if命令；
1
2
3
4
5
6
7
8
9
If condition 
Statement;
----------------------------------
If 1=0
Print 'Line one';
Print 'Line two';
--结果返回Line two；

if语句之后没有分号； if语句实际上是后面语句的提前；

  1. 使用Begin / End 有条件地执行多条语句
1
2
3
4
If condition 
  Begin;
	Multipie Line;
  End;  --每个都有分号
  1. 使用If Exists()作为基于存在性的条件
  • If Exists() 结构使用从SQL Select语句返回的每一行作为条件。
    • 因为If Exists() 结构会查找每一行，所以Select语句应当选择所有的列。一旦一个单行满足了If Exists()，查询就会继续执行
1
2
3
4
5
If Exists
  (Select * From production.product Where quantity = 0)
  Begin;
	Print 'Relpenish Inventory';
  End;
  1. 使用If / Else 执行替换语句
    • 可选的Else定义了if条件为False时的执行代码；Else可控制下一个单个命令，后者Begin/End块
1
2
3
4
If condition
 	 Single line or Begin/End block of code;
  Else
	Single line or Begin/End block of code;

16.3.2 使用While循环

  • what ：当条件为ture时循环代码；

16.5 临时表和表变量

临时表

对象 描述
本地临时表 使用# 进行创建
关闭当前会话窗口时就会消失
全局临时表 使用## 进行创建
关闭SQL Management的最后一个会话窗口(即关闭软件)时就会消失

表变量

  • 使用@进行创建；
  • 当批处理、过程或函数结束后，表变量就不存在了。

临时表与表变量的区别

  1. 两者都存储在tempdb中；只不过表变量存储在内存中的tenpdb页面中
  2. 表变量没有统计函数的功能
  3. 若临时空间 $\ge$ 250行，那就使用临时表

第17章 存储过程的开发

存储过程的优势

  • 一致性。存储过程封装一条/多条T-SQL语句，可在系统的不同区域内一致地执行
  • 可维护性。它们是模块化的，并且独立于调用它们的应用程序。
  • 安全性。在两个前端提供优势：数据库、应用程序。
    • 从数据的角度：存储过程是对象。意味着向特定用户或组授予执行该存储过程的能力。
    • 从应用程序的角度：存储过程让用户有机会再执行T-SQL过程之前验证输入
  • 性能。存错过程执行是服务器端的进程。意味着无论多复杂，在网络上也只会发送一条语句。

17.1 管理存储过程

17.1.1 创建、更改和删除存储过程

  1. Create
    • Create命令必须是批处理语句中的第一条。批处理的终止就结束了存储过程的定义。
      • 在Create Procedure 和下一个批处理终止符(GO)之间的所有内容都被视为存储过程定义的一部分
    • 创建存储过程时始终使用两部分的命名约定：schema.objectname。确保将存储过程添加到适当的框架中
1
2
3
4
5
GO
Create Procedure sales.information
  As
  Select currencycode, name From sales.currency;
GO

其他命令：Alter / Drop

17.1.4 执行存储过程

1
2
Execute sales.information;
Exec sales.information;

17.2 把数据传递到存储过程

  • 存储过程的参数化特征使其成为代码重用的重要特征；
  • 调用存储过程时，必须为每个已定义的参数提供数值。

17.2.1 输入参数

  1. 指定参数值: 通过命名参数值
1
2
3
4
5
6
7
8
Create Proc sales.information 
	@currencycode char(3)
  AS
	Select currencycode, name From sales.currency
	Where currencycode = @currencycode;
GO
Execute sales.information @currencycode = 'USD'
GO
  1. 参数默认值：只需在输入参数信息后面添加 =”default value” 
1
2
3
4
5
6
7
8
9
GO
Alter Procedure sales.information
	@currencycode char(3) = 'USD'
  AS
	Select currencycode, name From sales.currency
	Where currencycode = @currencycode;
GO
Execute sales.information @currencycode = Default;
Execute sales.information Default --效果同上
  1. 通过列表与表作为存储过程的参数输入
1
2
3
4
5
Go
Create Proc sales.rate
  @rateIDlist varchar(50)
AS
Declare @

17.3 从储存过程中返回数据

  • 输出参数：使用输出变量可以从存储过程返回标量函数；
  • Return：使用Return语句可以从存储过程返回单个整数值；
  • 结果集：存储过程可以通过一个或多个Select语句返回数据

17.3.1 输出参数

  • 输出参数：能使存储过程把数据返回到主调应用程序。在创建和调用过程都需要关键字Output。

    • 在存储过程内，输出参数是一个局部变量，就像输入参数一样。
    • 但实际上输出参数是双向参数。当存储过程终止时，其当前值要传输到主调应用程序。

    ​

在主调用程序的过程中，必须创建局部变量来接收输出参数的值。

  1. 创建带有输出参数@currencyname的存储过程sales.currencyname
  2. 声明局部变量@currencynameoutput （输入函数）以接收输出参数
  3. 执行存储过程，为输出参数@currencyname赋予@currencynameoutput （输入函数）的值，该值在局部变量初始化之前为NULL
  4. 在执行存储过程期间，基于出入参数后@currencycode的值，通过Select语句为输出参数赋值。
  5. 存储过程完毕，而且执行要传递回SSMS。局部变量@currencycodeoutput接收输出参数的值
  6. Print命令把该局部变量的值发送到SSMS的message选项卡
1
2
3
GO
Create Proc sales.currencyname
  @currencycode Char(3),#