SQL-＞基础-＞进阶

新增语句

INSERT INTO 表名称(列1,列2) VALUES (值1, 值2,....)

INSERT INTO 表名称(列1,列2)  SELECT 值1，值2

--1、INSERT INTO {表名}  VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...)
--例如：
INSERT INTO Production.UnitMeasure VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923') , (N'Y3', N'Cubic Yards', '20080923');  

--2、INSERT INTO {表名}({列名1，列名2，...}) SELECT {列名1，列名2，...} FROM {表名}
--例如：
insert into tb_aaa(code,name,cla) select code,name,cla from tb_bbb where note='出' and complete='否'

--3、快速插入指定数量测试数据
--insert into 表名({列名1，列名2，...}) values(值1,值2,...)  
--go {循环执行次数}
--例如：
insert into tb_chukubjls(FBjbh) values('a')
go 10000

--4、{表变量}
--INSERT INTO {表名} ({列名1，列名2，...})   OUTPUT {输出内容}   INTO {表变量}  数据源
--例如：
declare @MyTableVar table(code nvarchar(100),name nvarchar(100),cla nvarchar(100));  
insert into tb_aaa(code,name,cla) 
OUTPUT INSERTED.code ,INSERTED.name ,INSERTED.cla INTO @MyTableVar  
select code,name,cla from tb_bbb where note='出' and complete='否'

--5、执行包含大量Insert语句的sql文件：
--sqlcmd -S {服务器地址} -U {用户名} -P {密码} -d {数据库名} -i {sql文件}
--例如：
--打开cmd命令窗口
--sqlcmd -S . -U sa -P xiaomai123 -d TEST_CG -i D:\插入测试数据.sql

删除语句

delete from 表名称  [where <删除条件>]

TRUNCATE TABLE 表名称

【 注意：TRUNCATE TABLE 删除表的所有行，但表的结构、列、约束、索引等不会被删除；不能用于有外建约束引用的表，TRUNCATE TABLE 速度极快】

--1、DELETE TOP ({删除条数}) {表名} WHERE {过滤条件} （不可搭配Order by）
--例如：
DELETE TOP(1) tb_aaa WHERE code='03220624123249' and note='出';

--2、DELETE {表别名} FROM {表名} AS {表别名} WHERE {过滤条件}
--例如：
DELETE l  FROM tb_aaa AS l  JOIN tb_bbb AS k ON l.code = k.code where k.state='占用' and l.note='出'

--3、DELETE {表名} WHERE EXISTS (SELECT TOP 1 1 FROM {表名...} WHERE {过滤条件})
--例如：
DELETE tableA WHERE EXISTS (
SELECT TOP 1 1 FROM tableB tb WHERE tb.col1 = tableA.col1
)

--4、{表变量}
--DELETE {表名} OUTPUT {输出内容} INTO {表变量} WHERE {过滤条件}
--例如：
declare @MyTableVar table(code nvarchar(100),name nvarchar(100),note nvarchar(100));  
DELETE c
OUTPUT DELETED.code,DELETED.name,DELETED.note INTO @MyTableVar  
from tb_aaa c join tb_bbb l on c.code =l.code  where l.note ='出'

更新语句

update 表名称 set [列名]=[更新值] [where <更新条件>]

update 表名A set 列1=表名B.列1 from 表名A join 表名B on 表名A.列2=表名B.列2  [where <更新条件>] update 表名A set 列1=(子查询)  [where <更新条件>]

【 注意：子查询只能返回一个值，若子查询有多个返回值，执行时会报错】

--1、UPDATE TOP ({更新条数}) {表名} SET {更新内容} WHERE {过滤条件}（不可搭配Order by）
--例如：
declare @Id bigint;
UPDATE TOP (1) tb_aaa SET @Id=Id,State ='占用',Note=DEFAULT where Code='A1102';

--2、UPDATE {表别名}  SET {更新内容} FROM {表名} AS {表别名} WHERE{过滤条件} 
--例如：
UPDATE b SET Name=k.Name FROM tb_aaa AS b  JOIN tb_bbb AS k ON b.Code= k.Code WHERE k.State='占用'

--3、UPDATE {表名} SET {更新内容} WHERE EXISTS (SELECT TOP 1 1 FROM {表名...} WHERE {过滤条件})
--例如：
UPDATRE tableA SET col2+='_END' WHERE EXISTS (
SELECT TOP 1 1 FROM tableB tb WHERE tb.col1 = tableA.col1)

--4、{表变量}
--UPDATE {表名} SET {更新内容}  OUTPUT {输出内容}   INTO {表变量}  WHERE {过滤条件}
--例如：
DECLARE @MyTableVar TABLE (Id bigint NOT NULL,OldCategory nvarchar(100),NewCategory nvarchar(100),ModifiedDate datetime);  
UPDATE tb_aaa SET Category= Category +'1', Updateon = GETDATE();   
OUTPUT inserted.Id,deleted.Category,inserted.Category,inserted.Updateon INTO @MyTableVar WHERE Code='201038220902LZG02';

查询语句

 select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]【单表查询】

select <分组列1，分组列2>,<统计函数1，统计函数2>   from 表名 where  [where <查询条件表达试>]  GROUP BY <分组列1，分组列2>    [HAVING <分组查询条件表达试>]【分组查询】

select <列名> from <表名A> [INNER JOIN、LEFT OUTER JOIN 、RIGHT  OUTER  JOIN、FULL OUTER JOIN] <表名B> ON <条件表达试> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]【内连接、左外连接、右外连接、全外连接】

select <列名> from <表名A> CROSS JOIN <表名B> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]【交叉连接】

select <列名> from <表名A> [INNER JOIN、LEFT OUTER JOIN 、RIGHT  OUTER  JOIN、FULL OUTER JOIN] <表名B> ON <条件表达试> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]【内连接、左外连接、右外连接、全外连接】

select <列名> from <表名A> CROSS JOIN <表名B> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]【交叉连接】

查询例子

--1、select {列名1，列名2，...} into {新表名/临时表} from {表名}
--例如： 
SELECT IDENTITY (int, 1, 1) AS new_id,* INTO tb_test_new FROM tb_test;

--2、SELECT TOP {数量} WITH TIES {列名1，列名2，...} FROM {表名} ORDER BY {排序字段} DESC
--例如： 
SELECT TOP 8 WITH TIES * FROM students ORDER BY score DESC

--3、SELECT {列名} FROM {表名} WITH (INDEX({索引名称/索引序号})) WHERE {过滤条件}
--例如： 
SELECT a.* FROM tb_aaa AS a WITH (INDEX([NonClusteredIndex-code])) JOIN tb_bbb AS b WITH (INDEX(7)) on a.code = b.code WHERE b.name = 'B';

--4、SELECT {列名1，列名2，...} FROM {表名} WHERE {过滤条件} FOR XML AUTO;
--例如： 
SELECT code,name FROM tb_test WHERE code in ('01','02') FOR XML AUTO;

--5、SELECT {列名1，列名2，...} FROM {表名} WHERE {过滤条件} FOR JSON AUTO;
--例如： 
SELECT code,name FROM tb_test WHERE code in ('01','02') FOR JSON AUTO;

--执行结果 [{"code":"01","name":"A"},{"code":"02","name":"A"}]

SELECT a.bbh,b.code,B.name FROM tb_bbb a JOIN tb_aaa B ON a.bbh=b.bbh WHERE A.bbh in ('0009341913172623863203') FOR JSON AUTO;

--执行结果 [{"bbh":"0009341913172623863203","B":[{"code":"03220624140564","name":"BP225-196"},{"code":"03220624140570","name":"BP218-196"}]}]

JSON操作函数

ISJSON (Transact-SQL) 测试字符串是否包含有效 JSON。

JSON_VALUE (Transact-SQL) 从 JSON 字符串中提取标量值。

JSON_QUERY (Transact-SQL) 从 JSON 字符串中提取对象或数组。

JSON_MODIFY (Transact-SQL) 更改 JSON 字符串中的值。

例 ：
 

SELECT ISJSON('[{"FBbh":"0009341913172623863203","B":[{"FBjbh":"03220624140564","Fhs":"BP225-196"},{"FBjbh":"03220624140570","Fhs":"BP218-196"}]}]') --结果是1



SELECT JSON_QUERY('{"FBbh":"0009341913172623863203","B":[{"FBjbh":"0326","Fhs":"BP218"}, {"FBjbh":"0322","Fhs":"BP218"}]}','$.B')

--结果：[{"FBjbh":"0326","Fhs":"BP218"},{"FBjbh":"0322","Fhs":"BP218"}]



SELECT JSON_QUERY('{"FBbh":"0009341913172623863203","B":[{"FBjbh":"03220624140564","Fhs":"BP225-196"},{"FBjbh":"03220624140570","Fhs":"BP218-196"}]}','$.FBbh')

--结果：NULL

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT @info
-- Update name  

SET @info=JSON_MODIFY(@info,'$.name','Mike')
PRINT @info  --输出{"name":"Mike","skills":["C#","SQL"]}

-- Insert surname
SET @info=JSON_MODIFY(@info,'$.surname','Smith')
PRINT @info  --输出{"name":"Mike","skills":["C#","SQL"],"surname":"Smith"}
-- Set name NULL 

SET @info=JSON_MODIFY(@info,'strict $.name',NULL)
PRINT @info --{"name":null,"skills":["C#","SQL"],"surname":"Smith"}
-- Delete name 

SET @info=JSON_MODIFY(@info,'$.name',NULL)
PRINT @info --{"skills":["C#","SQL"],"surname":"Smith"}
-- Add skill  

SET @info=JSON_MODIFY(@info,'append $.skills','Azure')
PRINT @info --{"skills":["C#","SQL","Azure"],"surname":"Smith"}

用is null或者is not null来判断是否为空行【非特定情况，少用，有性能缺陷】

查询返回限制行数(关键字：top 行数)(set rowcount 行数)【非特定情况，慎用set rowcount】

使用like和通配符进行模糊查询 使用between在某个范围内进行查询

使用in在列举值内进行查询(in后是多个的数据)

使用UNION 和 UNION ALL 连接多个结果集

使用 DISTINCT 语句过滤重复

Compute 和 Compute By【给查询语句附加统计信息】

Group By All【后期SQL SERVER 会舍弃该语法，可忽略】

SELECT INTO【把查询结果保存到临时表，全局临时表，表】

ISNULL【给值为NULL的变量返回默认值】

COALESCE【返回其参数中第一个非空表达式】

IDENT_CURRENT ,@@identity,SCOPE_IDENTITY()【尽量只使用SCOPE_IDENTITY()获取上一条插入语句返回的自增值，其他两种方式存在并发问题，不安全】

使用SET NOCOUNT ON【阻止在结果集中返回显示受T-SQL语句或则usp影响的行计数信息，即使当SET NOCOUNT ON 时候，也更新@@RowCount】

使用@@RowCount进行逻辑判断

游标【慎用，有性能影响】

WITH AS的高级应用：

递归查询：

索引

微软的SQL SERVER提供了两种索引：聚集索引和非聚集索引：

优点：  

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。   可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

缺点：  

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。  索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

创建方向索引的准则

一般来说，应该在这些列上创建索引

1、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

2、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

3、在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

4、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

1、对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

2、对于那些只有很少不同值的列也不应该增加索引。这是因为，由于这些列的不同值很少，需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

3、对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

4、当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少 索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

创建索引的方法

直接创建索引，例如使用CREATE INDEX语句或者使用创建索引向导

用CREATE INDEX语句或者使用创建索引向导来创建索引，这是最基本的索引创建方式，并且这种方法最具有柔性，可以定制创建出符合自己需要的索引。在使用这种方式 创建索引时，可以使用许多选项，例如指定数据页的充满度、进行排序、整理统计信息等，这样可以优化索引。使用这种方法，可以指定索引的类型、唯一性和复合 性，也就是说，既可以创建聚簇索引，也可以创建非聚簇索引，既可以在一个列上创建索引，也可以在两个或者两个以上的列上创建索引。

间接创建索引，例如在表中定义主键时，同时也创建了索引

通过定义主键，也可以间接创建索引。主键是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建 了一个唯一性的聚簇索引。虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引。换句话说，在物理 实现上，不存在主键约束，而只存在唯一性的聚簇索引。

索引碎片

索引碎片查询：DBCC SHOWCONTIG(表名)

索引重建

方法 1、 重建指定索引，这种方法没有性能可谈。重建时表还不可访问。

方法 2、 在线重建索引，只有SQL Server 企业版才支持。

方法 3、 使用填充因子重建，这样做不一定可以减小查寻时的IO量

方法 4、 启用压缩数据页。这样可以减少查寻的IO量，可是会用更多的CPU 要权衡。

方法 1、

          alter index idx_OrderID
          on dbo.OrderDetail
          rebuild;
          go

方法 2、

          alter index idx_OrderID
          on dbo.OrderDetail
          rebuild
          with (online =on);

方法 3、

          alter index idx_OrderID
          on dbo.OrderDetail
          rebuild
          with(fillfactor = 75 ,sort_in_tempDB = on);
          go

方法 4、        

          alter index idx_OrderID
          on dbo.OrderDetail
          rebuild
          with
          (Data_Compression = Page);
          go

alter index all on 表名 rebuild ;  用 all 可以重建指定表上的所有索引。

索引的维护

为了维护系统性能，索引在创建之后，由于频繁地对数据进行增加、删除、修改等操作使得索引页发生碎块，因此，必须对索引进行维护。

可右键选中对应的索引，然后重新生成。

数据库事务 

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

1. 原子性 （Atomic）（Atomicity)

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。

2. 一致性 （Consistent）(Consistency)

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。

3. 隔离性 （Insulation）(Isolation)

由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为隔离性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。

4. 持久性 （Duration）(Durability）

事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

SQL Server使用事务的语法

begin tran：设置起点

commit tran：使事务成为数据库中永久的、不可逆转的一部分

rollback tran：本质上说想要忘记它曾经发生过

save tran：创建一个特定标记，只允许部分回滚

所谓事物隔离级别，就是并发事务对同一资源的读取深度层次。分为5种。

read uncommitted：这个隔离级别最低啦，可以读取到一个事务正在处理的数据，但事务还未提交，这种级别的读取叫做脏读。

read committed：这个级别是默认选项，不能脏读，不能读取事务正在处理没有提交的数据，但能修改。

repeatable read：不能读取事务正在处理的数据，也不能修改事务处理数据前的数据。

snapshot：指定事务在开始的时候，就获得了已经提交数据的快照，因此当前事务只能看到事务开始之前对数据所做的修改。

serializable：最高事务隔离级别，只能看到事务处理之前的数据。

函数

SQL Server有四种函数类别：表值函数、标量值函数、聚合函数、系统函数。    

其中允许用户直接使用SQL定义的，只有表值函数和标量值函数，顾名思义，表值函数返回的数据表对象，标量值函数返回的是简单数据对象    

值得注意的是，函数只允许查询和计算数据，不允许更新数据库里的数据

存储过程

SQL Server存储过程是预编译的、可加密的，存储过程的参数分为输入参数和输出参数两种，其中输入参数可以是任意数据类型（包含自定义表值类型），输出参数可以是简单的标量值和游标

SQL Server存储过程内可以查询和修改数据库里的数据（这一点有别于函数），存储过程的调用，分按位置传递参数和参数名传递参数(因为个别参数是带默认值的) 

在存储过程中，虽然可以有任意多个select语句，但只有每一个select语句返回的结果集格式一致的时候，才可以通过INSERT INTO接收select查询到的结果。

在存储过程中定义多个select一般都是搭配ADO.NET使用，ADO.NET可以遍历每一个结果集，但在Ｔ-SQL中直接调用这样的存储过程，除非满足select返回的结果集格式一致，否则无法获取select的查询结果 

可以在存储过程中使用RAISERROR抛出自定义异常，以便在某些条件成立时，反馈异常信息给外部程序

触发器

触发器是一种特殊的存储过程，它的执行不是由程序调用，也不是手动执行，而是由事件来触发。例如：update、insert、delete这些操作的时候，系统会自动调用对应的触发器

触发器分类：

1、DML（ 数据操纵语言 Data Manipulation Language）触发器：是指触发器在数据库中发生 DML 事件时将启用。DML事件是指在表或视图中对数据进行的 insert、update、delete 操作的语句。

2、DDL（数据定义语言 Data Definition Language）触发器：是指当服务器或数据库中发生 DDL 事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。

3、登陆触发器：是指当用户登录 SQL SERVER 实例建立会话时触发。如果身份验证失败，登录触发器不会触发。

其中DML触发器比较常用，根据 DML 触发器触发的方式不同又分为以下两种情况：

1、after 触发器（之后触发）：其中 after 触发器要求只有执行 insert、update、delete 某一操作之后触发器才会被触发，且只能定义在表上。

 2、instead of 触发器 （之前触发）：instead of 触发器并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。可以在表或视图上定义 instead of 触发器。

DML 触发器有两个特殊的表：插入表（instered）和删除表（deleted），这两张表是逻辑表。这两个表是建立在数据库服务器的内存中，而且两张表的都是只读的。这两张表的结构和触发器所在的数据表的结构是一样的。当触发器完成工作后，这两张表就会被删除。Inserted 表的数据是插入或是修改后的数据，而 deleted 表的数据是更新前的或是已删除的数据。

视图

视图可以看作定义在SQL Server上的虚拟表。视图正如其名字的含义一样，是另一种查看数据的入口。常规视图本身并不存储实际的数据，而仅仅是由SELECT语句组成的查询定义的虚拟表 。从数据库系统内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图。

例如查询，插入，修改，删除操作等，但插入、修改、删除等的操作其实对于原始数据表的操作。

视图的作用：

1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）

3、从而加强了安全性，使用户只能看到视图所显示的数据。

4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。

其实视图有点类似编程里的接口，只有签名没有实现（只有结构，没有数据），有些大数据优化方案中，如果单表数据量过大，就会把这些表拆成多个，然后使用视图进行整合在一起，这样代码不用修改，数据又被分流到了多个物理表上。

如果要通过视图更新数据则要了解清楚里面的诸多限制，比如单表视图，在向视图进行插入数据时，没有在视图上体现的列必须可为空或者有默认值，如果是多表视图，则要为该视图增加instead of 触发器，在instead of 触发器里编写插入，更新，删除的逻辑

聚合窗口函数

select Code,Name,Qty,
count(*) over ()                             --包装表记录数,
sum(Qty) over ()                             --包装表全部记录板件数累计,
avg(Qty*1.0) over ()                         --包装表板件数平均值, 
count(*) over (PARTITION BY Code)            --订单包数,
sum(Qty) over (PARTITION BY Code)            --订单板件数,
avg(Qty*1.0) over (PARTITION BY Code)        --订单内每包平均的板件数 
from tb_bbb

排名窗口函数

select Code,Bbh,Qty,
ROW_NUMBER() over(order by Code)                          --行号,
NTILE(10) over(order by Code)                             --分块排名,
RANK() over(order by Code)                                --排名,
DENSE_RANK() over(order by Code)                          --密集排名,
ROW_NUMBER() over(PARTITION BY Code order by Bbh)          --单内行号
from tb_bbb order by Codeasc

框架

PARTITION BY {列名...}
ORDER BY {列名...}
ROWS BETWEEN UNBOUNDED PRECEDING | 首行
                           <N> PRECEDING | 前面n行
                           <N> FOLLOWING | 后面n行
                           CURRENT ROW | 当前行
            AND
                           UNBOUNEDE FOLLOWING | 尾行
                           <N> PRECEDING | 前面n行
                           <N> FOLLOWING | 后面n行
                           CURRENT ROW | 当前行

select *,
COUNT(*) OVER(PARTITION BY Pcbh ORDER BY Id asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) --批次累计入库数量 
from tb_aaa;

select *,
COUNT(*) OVER(PARTITION BY Pcbh ORDER BY Id asc ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) --批次累计入库数量 
from tb_aaa;

偏移

select FDdbh,FBbh,FFjQty,
LAG(FBbh,1) OVER(PARTITION BY FDdbh ORDER BY Fbbh asc ),--前偏移
LEAD(FBbh,1) OVER(PARTITION BY FDdbh ORDER BY Fbbh asc),--后偏移
FIRST_VALUE(FBbh) OVER(PARTITION BY FDdbh ORDER BY Fbbh asc),--第一条记录值
LAST_VALUE(FBbh) OVER(PARTITION BY FDdbh ORDER BY Fbbh asc)  --最后一条记录值
from tb_bbb order by FDdbh asc

分布

--1、PERCENT_RANK(百分位排名)、②CUME_DIST(累计分布)

DECLARE @成绩表 table(科目 nvarchar(20),学生 nvarchar(20),成绩 float);
insert into @成绩表 values('语文','张一',60),('语文','张二',70),('语文','张三',80),('语文','张四',80),('语文','张五',90)
insert into @成绩表 values('数学','张一',60),('数学','张二',70),('数学','张三',80),('数学','张四',80),('数学','张五',90)
select 科目,学生,成绩,
PERCENT_RANK() OVER(PARTITION BY 科目 ORDER BY 成绩 ASC) 百分位排名,
CUME_DIST() OVER(PARTITION BY 科目 ORDER BY 成绩 ASC) 累计分布
from @成绩表

 假设rk 为数据行的RANK值；

假设nr为窗口分区内数据行的行数；

假设np为领先或与当前行的排序值相同的行的数目；

①PERCENT_RANK(百分位排名)=(rk-1)/(nr-1)

——表示有百分之多少的学生比当前学生的成绩低；

②CUME_DIST(累计分布)=np/nr

——表示有百分之多少的学生比当前学生的成绩低或一样高；

逆分布

--PERCENTILE_DISC(百分位离散)、PERCENTILE_CONT(百分位连续)

DECLARE @成绩表 table(科目 nvarchar(20),学生 nvarchar(20),成绩 float);
insert into @成绩表 values('语文','张一',60),('语文','张二',70),('语文','张三',82),('语文','张四',80),('语文','张五',90)
insert into @成绩表 values('数学','张一',60),('数学','张二',72),('数学','张三',83),('数学','张四',80),('数学','张五',90)
select 科目,学生,成绩,
PERCENTILE_DISC(0.6) WITHIN GROUP(ORDER BY 成绩 ASC) OVER(PARTITION BY 科目) 百分位离散,
PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY 成绩 ASC) OVER(PARTITION BY 科目) 百分位连续
from @成绩表

①PERCENTILE_DISC(百分位离散)

——返回分区中第一个符合条件的值，条件是：其累计分布大于等于输入值；

②PERCENTILE_CONT(百分位连续)

——会生成一个中间值，（中间值-前一条记录值)/（后一条记录值-中间值)=前一条记录百分位排名/后一条记录百分位排名； 

分库分区

什么时候考虑使用分区？

1、一张表的查询速度已经慢到影响使用的时候。

2、数据量大

3、表中的数据是分段的

4、对数据的操作往往只涉及一部分数据，而不是所有的数据

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

分区解决的问题： 主要可以提升查询效率

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

当一个表数据量很大时候，很自然我们就会想到将表拆分成很多小表，在执行查询时候就到各个小表去查，最后汇总数据集返回给调用者加快查询速度。

比如：电商平台订单表，库存表，由于长年累月读写较多，积累数据都是异常庞大的，这时候，我们可以想到表分区这个做法，降低运维和维护成本，提高读写性能。将前半年订单放一个历史分区表，不活跃库存放一个历史分区表。

截止到SQL Server 2016，一张表或一个索引最多可以有15000个分区。

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

1、分区范围

分区范围是指在要分区的表中，根据业务选择表中的关键字段做为分区边界条件，分区后，数据所在的具体位置至关重要，这样才能在需要时只访问相应的分区。注意分区是指数据的逻辑分离，不是数据在磁盘上的物理位置，数据的位置由文件组来决定，所以一般建议一个分区对应一个文件组。

2、分区键

分区表中的字段可以作为分区键，比如库存表中供应商ID。对表和索引进行分区的第一步就是定义分区的关键数据。

3、索引分区

除了对表的数据集进行分区之外，还可以对索引进行分区，使用相同的函数对表及其索引进行分区通常可以优化性能。

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

创建

1、创建文件组

文件组数量由硬件决定，最好是一个文件组对应一个分区，好维护。而通常文件组都处于不同磁盘上。

2、创建分区函数

如何创建表分区边界值，我们肯定要根据业务场景来决定。比如我测试库库存表有36万左右数据，而有些供应商的库存数据远远比其他供应商大，那么我可以考虑使用供应商ID字段作为边界值分区。例如：根据T-SQL统计，18080供应商库存数据最大，那么我可以根据18080供应商上下分为三个区。

3、创建分区方案

创建分区方案并对数据表进行分区。

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

表分区

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

索引分区

数据归档

方案一

对大数据量的表进行表分区，定时将过期数据(比如两年、三年前的数据)清理或移入新表

清理语句：TRUNCATE TABLE 分区表名  WITH (PARTITIONS (2, 4, 6 TO 8));  

移入新表：ALTER TABLE 分区表名 switch partition 1 to <普通表名>

——备注：①两个表在一个文件组中；②字段数量相同，对应位置的字段相同；③相同位置的字段要有相同的属性，相同的类型；

方案二

创建历史数据表，把数据表过期的数据插入到历史数据表，然后删除数据表过期数据，因为是通过DELETE删除数据，所以较慢，需要避免一次DELETE过多数据，应采用1000条记录一次转移

远程连接

--新建远程连接 exec sp_addlinkedserver 'mytest', ' ', 'SQLOLEDB ', '192.168.0.1' exec sp_addlinkedsrvlogin 'mytest', 'false ',null, 'sa', '123456'

--删除远程连接 --exec sp_droplinkedsrvlogin 'mytest',null

--exec sp_dropserver 'mytest'

【如果要访问远程连接上的数据表，需要通过如下方式：连接名.数据库名.dbo.表名】 如果SQL Server所在的电脑也安装了Oracle客户端，SQL Server也可以直接连接到Oracle上，对Oracle上的数据进行增删改查操作（只支持部分标准的SQL语法）

数据库范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。    

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

第一范式（1NF）

所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。

在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。 说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。

不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。

第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个一员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分，如果在员工关系中，没有对其身份证号进行存储，而姓名可能会在数据库运行的某个时间重复，无法区分出实体时，设计辟如ID等不重复的编号以实现区分，被添加的编号或ID选作主键。（该主键的添加是在ER设计时添加，不是建库时随意添加）。

第三范式（3NF）

第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。

巴斯-科德范式（BCNF）

巴斯-科德范式（BCNF）是第三范式（3NF）的一个子集，即满足巴斯-科德范式（BCNF）必须满足第三范式（3NF）。通常情况下，巴斯-科德范式被认为没有新的设计规范加入，只是对第二范式与第三范式中设计规范要求更强，因而被认为是修正第三范式，也就是说，它事实上是对第三范式的修正，使数据库冗余度更小。

这也是BCNF不被称为第四范式的原因。某些书上，根据范式要求的递增性将其称之为第四范式是不规范，也是更让人不容易理解的地方。而真正的第四范式，则是在设计规范中添加了对多值及依赖的要求。

假设仓库管理关系表(仓库号，存储物品号，管理员号，数量)，满足一个管理员只在一个仓库工作；一个仓库可以存储多种物品，则存在如下关系： (仓库号，存储物品号)——>(管理员号，数量) (管理员号，存储物品号)——>(仓库号，数量) 所以，(仓库号，存储物品号)和(管理员号，存储物品号)都是仓库管理关系表的候选码，表中唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：(仓库号)——>(管理员号) (管理员号)——>(仓库号) 即存在关键字段决定关键字段的情况，因此其不符合BCNF。把仓库管理关系表分解为两个关系表仓库管理表(仓库号，管理员号)和仓库表(仓库号，存储物品号，数量)，这样这个数据库表是符合BCNF的，并消除了删除异常、插入异常和更新异常。

反三范式

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于DML的比例。但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整的。

数据库设计的三个境界

数据库设计应该也是分为三个境界的：

第一个境界，刚入门数据库设计，范式的重要性还未深刻理解。这时候出现的反范式设计，一般会出问题。

第二个境界，随着遇到问题解决问题，渐渐了解到范式的真正好处，从而能快速设计出低冗余、高效率的数据库。

第三个境界，再经过N年的锻炼，是一定会发觉范式的局限性的。此时再去打破范式，设计更合理的反范式部分。

范式就像武侠里面的招数，初学者妄想不按招数来，只能死的很难堪。毕竟招数都是高手总结归纳的精华。而随着武功提高，招数熟练之后，必然是发现招数的局限性，要么忘掉招数，要么自创招数。只要努力，加上多熬几年，总能达到第二个境界，总会觉得范式是经典。此时能不过分依赖范式，快速突破范式局限性的人，自然是高手。