SQL Server 2016 Learning Notes

• SQL Server 2016
• • 一、概述
  • 二、运行环境
  • 三、 创建与管理数据库
  • 四、表和数据的完整性
  • 五、Transact-SQL基础及高级应用
  • 六、索引和视图
  • 七、存储过程和触发器
  • 八、事务和锁
  • 九、安全机制
  • 其他

SQL Server 2016

一、概述

1. 基本概念

   • 数据（Data）和信息（Information）
   • 数据库（Database, DB）
   • 结构化查询语言（Structured Query Language, SQL）：
     • 特点
     • 一体化
     • 使用方式灵活
     • 非过程化
     • 语言简洁，语法简单
     • 组成
       • 数据定义语言（Data Definition Language, DDL）：Create、ALter、Drop
       • 数据操作语言（Data Manipulation Language, DML）: select、insert、update、delete
         • 数据查询语言（Data Query Language, DQL）：select、where、order by、 group by、 having
       • 数据控制语言（Data Control Language, DCL）：grant、revoke
       • 事务处理语言（Transaction Processing Language, TPL）：begin transaction、Commit、roolback
       • 指针控制语言（Pointer Control Language, CCL）：declare cursor、fetch into、update where current
   • 数据库管理系统（Database Management System, DBMS）
   • 数据库系统的体系
     • 集中式：单台主机，与其他计算机交互
     • C/S：客户端
     • B/S：浏览器
     • 分布式结构

2. 关系数据库（Relational Database, RDB）

   • 概念

     实体、属性、主键、实体型、实体集、联系

   • 实体之间的联系

     一对一、一对多、多对多

   • 概念模型的表示方法

     实体-联系模型（E-R模型）：实体用长方框、属性用椭圆框、联系用菱形框

   • 数据模型三要素

     • 数据结构
     • 数据操作
     • 完整性约束

   • 常用数据模型

     • 层析模型：不能表示两个以上实体之间的复杂联系和实体之间的多对多关系
     • 网状模型
     • 关系模型
       • 基本运算：选择、投影、连接
       • 数据完整性：实体、域、参照、事务完整性、用户定义完整性
         • 事务完整性：原子性、一致性、隔离性、持久性
     • 面向对象模型

3. 设计数据库

   • 数据库设计的范式
     • 1NF、2NF、3NF…
   • 方法
     • 实体关系、视图概念、分布设计法
   • 过程
     • 需求分析、概念设计、逻辑设计、物理设计

二、运行环境

1. 硬件需求
   • CPU: 64位，不低于1.4GHz (最好高于2.0GHz) ，X86处理器不支持安装
   • 内存：4GB以上，精简版1GB以上
   • 硬盘：6GB以上
   • 显示器：1024 x 768或更高
2. 软件需求
   • 操作系统：Windows Server 2012或Win8 及以上
   • Web环境：IE8.0及以上
   • NET Framework：4.0及以上
   • 网络软件
3. 服务项目
   • 集成服务（IS）
   • 分析服务（AS)
   • 报表服务（RS）
   • SQL Server代理（Agent)
   • 复制服务
   • 全文搜索
   • 主数据服务
   • 服务中介
   • 开发工具
4. 系统数据库
   • master：记录SQL Server的所有信息，应定期备份
   • model：用于在SQL Server实例上创建所有数据库的存储新数据库结构特性的模板
   • msdb：代理服务数据库，为报警、任务调度、记录操作员的操作空间
   • resource：隐形只读数据库，包含SQL中的所有系统对象
   • tempdb：为所有的临时表、临时存储过程及其他临时操作提供存储空间
5. 数据库设置
   • 默认索引填充因子
   • 硬盘充足时，可考虑将文件和日志文件分别存储到不同的物理硬盘上，可增加安全性

三、 创建与管理数据库

1. 数据库的结构层析：物理、概念、用户数据库
2. 常用对象
   • 表
   • 索引
   • 视图
   • 数据库关系图、默认值、规则、存储过程、触发器、用户

四、表和数据的完整性

1. 数据完整性

   • 域完整性：默认值、检查、外键、数据类型和规则
   • 实体完整性：主键、唯一码、唯一索引、标识列
   • 引用完整性：外键、检查、触发器和存储过程
   • 用户定义完整性：规则、触发器、存储过程以及约束

2. 约束类型

   • Not Null、PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK

3. 约束

   --创建约束
   --1. Not Null
   ALTER TABLE class
   ALTER COLUMN classname nvchar(12) NOT NULL
   GO
   
   --2.CHECK
   ALTER CONSTRAINT CK_name CHECK(column >= 0 and column <= 100),
         CONSTRAINT CK_name2 CHECK(...)
   Go
   
   --3.UNIQUE
   ALTER TABLE tablename
   ADD CONSTRAINT u_Email UNIQUE NONCLUSTERED(Email)
   GO
   
   --4.FOREIGN KEY:仅能引用位于同一服务器上的同一数据库中的表
   ALTER TABLE tablename
   WITH CHECK
   ADD CONSTRAINT FK_sc_student FOREIGN KEY(studentno)
   References student(studentno)
   GO
   
   --禁用检查
   ALTER TABLE score
   With NoCHECK
   Add CONSTRAINT CK_final12 CHECK((final >= 0 AND final <= 100))
   GO
   

4. 规则

   --只能通过Transact-SQL语句创建
   CREATE RULE score_rule
   AS
   @score BETWEEN 0 and 100
   GO
   
   --绑定规则
   EXEC sp_dindrule 'score_rule','tabel.column'
   
   --解除绑定
   EXEC sp_unbindrule 'table.column'
   
   DROP RULE rule
   

5. 默认值

   CREATE DEFAULT
   EXEC sp_bindefault
   --同规则
   

五、Transact-SQL基础及高级应用

1. 架构
   • 是单个用户所拥有的数据库对象的合集，这些对象形成单个命名空间
   • 推荐使用架构，可以简化DBO和开发人员的工作
2. 常用编码
   • ASCII
   • Unicode
   • UTF-8：通用格式转换，对英文使用8位（1个字节）、中文使用24位（3个字节）
   • GB 2312：简体中文字符集，GBK是对GB 2312的扩展
3. 运算符及优先级

优先级	运算符
1	() 圆括号
2	+（正）、-（负）、~（位非）
3	* 、/ 、%
4	+、-、+（连接）、&（位与）
5	比较运算符
6	^（位异或）、|（位或）
7	NOT
8	AND
9	ALL、ANY、SOME、BETWEEN、IN、LIKE、OR
10	=（赋值）

4. 函数

   • 分类：

     • 字符串、文本/图像管理、日期/时间类、数学计算、安全管理、SQL Server系统配置、系统统计、系统、游标、元数据

   • 数学函数

   • ABS、ASIN、ACOS、CEILING(返回大于或等于指定数值表达式的最小整数)、DEGREE（弧度转角度）、EXP、FLOOR（返回小于或等于指定数值表达式的最大整数）、LOG、PI、POWER、RADIANS、RAND、ROUND、SIN、SQUARE、SQRT（平方根）

   • 聚合函数

     • AVG、COUNT、MAX、MIN、SUM、STDEV（标准偏差）、VAR（方差）

   • 日期和时间

     函数	功能描述
     GETDATE	获取系统当前的日期和时间
     DATEADD(unit,n,date)	n天/小时/年
     DATEDIFF(unit,date1,date2)	计算日期1与日期2之间的差值
     DATENAME(part,date)	返回日期的指定部分（如年/月/日）的字符串形式
     DATEPART(part,date)	返回日期的指定部分（如年/月/日）的整数形式
     DAY	获取日期部分日的整数
     MONTH	获取日期部分月的整数
     YEAR	获取日期部分年的整数
     GETUTCDATE	获取格林尼治的标准时间datetime值

     日期	缩写	参数范围
     Year	Yy	1753~9999
     Quarter	Qq	1~4
     Month	Mm	1~12
     Day of Year	Dy	1~366
     Day	Dd	1~31
     Week	Wk	1~53
     Weekday	Dw	1~7
     Hour	Hh	0~23
     Minute	Mi	0~59
     Second	SS	0~59
     Millisecond	MS	0~999

   • 转换函数

     • CAST（expression AS data_type）：把一个数据类型强制转换为另一种数据类型

     • CONVERT（data_type[(length)]）,expression[,style])：把表达式从一种数据类型转换成领一种数据类型， 还允许把日期转换成不同的样式

     • 转换类型

       • 显示转换

       • 隐式转换

   • 字符串函数

     函数	功能描述
     CHARINDEX	返回字符串指定表达式的起始位置
     DIFFERENCE	以整数返回两个字符表达式的SOUNDEX值之差
     LEFT / RIGHT	返回字符串左 / 右边开始指定个数的字符
     LEN	返回给定字符串表达式的字符个数，其中不包含尾随空格
     LOWER / UPPER	大写转换为小写
     LTRIM / RTRIM	删除起始空格后返回字符表达式
     PATINDEX	表达式中某模式第一次出现的起始位置，如果在全部有效的文本和字符数据类型中没有找到，则返回0
     QUOTENAME	返回带有分隔符的Unicode串
     REPLACE	用第三个表达式替换第一个字符串中出现的所有第二个给定字符串表达式
     REPLICATE	以指定的次数重复字符表达式
     REVERSE	字符串反转
     STUFF	删除指定长度的字符并在指定的起始点插入另一组字符
     SUBSTRING	求子串函数

     • STUFF合并查询结果为一行：

       SELECT FoodId,MaterialName=STUFF(
       (SELECT',' +MaterialName
        FROM    FoodMaterial            
        WHERE FoodId=A.FoodId
        FOR XML path('')),1,1,''
       )
       FROM FoodMaterial A
       GROUP BY FoodId
       

   • 自定义函数

5. 控制流语句

   • IF…ELSE: 超过一行是需使用BEGIN…END

   • BEGIN…END : 组合成语句块，主要用于WHILE、CASE、IF…ELSE子句

   • WHILE…CONTINUE…BREAK

   • CASE…WHEN…THEN… [WHEN…THEN…] (…n) ELSE…END

   • 其他语句

     • GO：批处理的结束语句

     • PRINT：向客户端返回用户定义消息

     • GOTO：程序跳到指定的标有标识符的位置处继续执行，而位于GOTO语句和标识符之间的程序将不会被执行

       label :
       statement | statement_block
       GOTO label
       
       --不推荐使用GOTO，不利于结构化程序设计
       

     • WAITFOR：暂时停止执行SQL、语句块、存储过程等，直到所设定的时间已到才继续执行。

       WAITFOR
       {
       DELAT 'time_to_pass'| TIME 'time_to_execute'
       }
       
       --e.g.
       BEGIN
         WIATFOR TIME '22:20:17'
         EXECUTE sp_help
       END
       

     • RETURN：无条件终止，可返回整型值

     • TRY…CATCH

       BEGIN TRY
         { sql_statement | statement_block}
       END TRY
       BEGIN CATCH
         { sql_statement | statement_block}
       END CATCH [;]
       

     • EXECUTE：EXEC

6. WITH ROLLUP

   • 在GROUP BY后再进行相同的总体计算

     SELECT courseno,ROUND(avg(final),2) FROM score GROUP BY courseno WITH ROLLUP
     

7. 聚合函数

   • 不能被用于SELECT语句的WHERE子句中
     • 便利聚合函数
     • 统计聚合函数
     • 超聚合

8. 游标(CURSOR)

   • 概念

     • 全局游标
     • 局部游标：存储过程、触发器、用户自定义的函数中声明
     • 使用步骤如下：
       1. 声明游标：DECLARE CURSOR
       2. 打开游标：OPEN
       3. 获取记录信息：一条Fetch会执行两步操作：首先将游标当前指向的记录保存到一个局部变量中；然后游标将自动移动向下一条记录。可重复执行。
       4. 关闭游标：CLOSE
       5. 释放游标：Deallocate

   • 运用

     • 声明游标

       DECLARE cursor_name CURSON [ LOCAL|GLOBAL]
       [FROM ARD_ONLY|SCROLL|] [STATIC|DYNAMIC][READ_ONLY]
       FOR select_statement[;]
       

     • 打开游标

       OPEN cursor_name
       

     • 使用FETCH获取记录信息

       FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE {n|@nvar}|RELATIVE {n|@nvar}]
       FROM cursor_name 
       [INTO @variable_name[,...n]]
       

     • 关闭游标

       CLOSE cursor_name
       

     • 释放游标

       DEALLOCATE cursor_name
       

       @@FETCH_STATUS = 0 表示游标指向合法行记录

       CURSOR_STATUS

       @@CURSOR_ROWS

   • 大对象（Large Object, LOB）

     • 包含数字化信息的数据字段，可以是音频、视频、图像及文档等
     • 种类
       • BLOB（二进制大对象数据）：字符串最长可达2GB，用于保存图像、剩余、Word文档等
       • CLOB（字符巨型对象数据）：最大4GB的字符串文本
       • DBCLOB（双字节巨型大对象数据）：最大4GB的字符串文本

六、索引和视图

1. 索引

   • 分类

     • 聚集索引：索引值的顺序与数据库表中记录的物理顺序相同，即聚集索引决定了数据库表中记录行的存储顺序，每个表只能创建一个。
     • 非聚集索引：顺序不要求一致
     • 唯一索引：可确保所有表中的任意两行的索引列值（不包括NULL）不重复，可在多列创建。

   • 用途

     • 加速数据检索
     • 优化查询
     • 强制实施行的唯一性

   • 设计的基本原则

     • 经常更新（INSERT、UPDATE、DELETE）的话避免使用过多索引

     • 数据量大、更新少的可创建多个索引来提高性能

     • 在视图上创建

     • 若查询语句中存在计算列，则可考虑对计算列值创建索引

     • 索引最大键列数为16，索引最值键值为900B

       --创建索引
       CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name
       ON
       {table_or_view_name}(column [ASC|DESC][,...n])
       [INCLUDE(column_name[...n])]
       [ON { filegroup_name | defualt }]
       
       --修改索引
       ALTER INDEX {index_name | ALL} On <object>
       {REBUID [WITH...] | DISABLE | REOGRANIZE}
       
       --索引碎片的检测
       SELECT avg_fragmentation_in_precent FROM sys.dm_db_index_physical_status(DB_ID(),OBJECT_id('table_name'),null.null,null)
       
       --碎片较少可以ALTER INDEX重组(REOGRANIZE)
       --碎片较多可以ALTER INDEX重建(REBUILD WITH(PAD_INDEX = ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON)
       --或使用CREATE INDEX重建(WITH(PAD_INDEX = ON, FILLFACTOR = 70,DROP_EXISTING = ON))
       
       --对语句进行分析索引
       SET SHOWPLAN_ALL ON 
       GO
       SELECT ...
       GO
       SET SHOWPLAN_ALL IO OFF
       GO
       
       --分析磁盘活动量
       SET STATISTICS IO ON
       GO
       SELECT ...
       GO
       SET STATISTICS IO OFF
       GO
       

七、存储过程和触发器

1. 存储过程

   • 目的

     • 提高了处理复杂任务的能力
     • 增强了代码的复用率和共享性
     • 减少了网络中数据的流量
     • 存储过程在服务器注册，加快了过程的运行速度
     • 加强了系统的安全性：参数化存储过程可以保护应用程序不受SQL注入式攻击

   • 类型

     • 系统、用户定义、临时（已#或##为前缀）、扩展

   • 设计原则

     • 不能超过128M

     • 只能在当前数据库中创建，如更改了远程其他实例的数据，则不能回滚，也不参与事务处理

     • 主要为了处理那些需要被多次运行的语句集

     • 可以嵌套存储过程，最大层析可以用@@

     • 可引用临时表

       --常用的sql系统存储过程
       sp_helpdb: 用于查看数据库名称及大小
       sp_helptext
       sp_renamedb
       sp_rename
       sp_helplogins
       sp_helpsrvrolemember
       

       --创建存储过程
       CREATE PROC[EDURE] procedure_name [;number]
       [ { @parameter date_type} [ = default] --设置默认值，不必指定参数也可执行
        [OUTPUT]][,...n]
       [WITH {RECOMPILE | ENCRYPTION}]  -- 每次重新编译 / 加密
       AS sql_statement [,...n]
       

2. 触发器

   • 分类
     • DML触发器：实现表格联动更改、防止恶意或错误的INSERT、UPDATE、DELETE
     • DDL触发器

八、事务和锁

1. 事务

   • 特点
     • 原子性（Atomicity）：全部执行或全部不执行
     • 一致性（Consistency）：由隔离性来表示，将数据库从一个一致状态转变到另一个一致状态
     • 隔离性（Isolation）：一个事务的内部操作及使用的数据对并发的其他事务是隔离的
     • 持久性（Durability）：事务提交，对数据库所做的修改将是持久的
   • 分类
     • 自动提交事务
     • 显示事务（BEGIN TRANSACTION和COMMIT TRANSACTION或ROLLBACK TRANSACTION）
     • 隐式事务（SET IMPLICIT_TRANSACTIONS ON/OFF)
     • 分布式事务
   • @@TRANCOUNT：查看当前事务的个数

2. 并发

   • 影响

     • 更新丢失
     • 不可重复读
     • 幻读
     • 脏读

   • 事务的隔离级别

     SET TRANSACTION ISOLATION LEVEL
     { READ UNCOMMITTED --未提交可读
     | READ COMMITTED   --已提交可读，可避免脏读
     | REPEATABLE READ  --可重复读
     | SNAOSHOT		   --快照
     | SERIALIZABLE	   --可串行化，等同于HOLDLOCK
     }
     

3. 管理锁

   • 锁类型

     锁类型	说明
     共享（S）	保护资源，以便只能对其进行读取访问
     排他（X）	确保不能同时对同一资源进行多个更新
     更新（U）	防止常见形式的死锁
     架构
     意向	建立锁层次结构，常见IS、IU、IX
     大容量更新（BU）
     键范围

   • 死锁

     • 处理时间长的实物具有较高的优先级

     • 为防止死锁，应遵守以下原则

       • 事务需按同一顺序访问数据库对象
       • 保持事务简短并处于同一个批处理中
       • 设置和使用较低的隔离级别

       select * from sys.dm_tran_locks where  resource_database_id = DB_ID('table_name')
       

九、安全机制

1. 查询权限

   • select * from fn_my_permissions(NULL / table_name , ‘SERVER’/‘DATABASE’/‘OBJECT’)

2. 服务器角色

   角色	权限
   sysadmin	拥有所有权限
   serveradmin	管理SQL Server服务器的配置选项，关闭服务器
   diskadmin	管理磁盘文件
   processadmin	管理运行的进程
   public	可查看任何数据库
   securityadmin	审核SQL Server系统登录，管理CREATE DATABASE权限、读取错误日志和修改日志
   setupadmin	管理链接服务器和启动过程
   dbcreator	创建、修改和删除数据库
   bulkadmin	可以执行BULK INSERT语句进行大容量操作

其他

sp_rename：表、列、索引、视图或用户定义数据类型