《SQL Server 从入门到精通》学习笔记

《SQL Server 从入门到精通》

第1章 数据库基础

1.2 数据库的体系结构

1.2.1 数据库三级模式结构

1. 模式
   模式也称逻辑模式或概念模式，它是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。一个数据库只有一个模式，模式处于三级结构的中间层。
   定义模式时不仅要定义数据的逻辑结构，而且要定义数据之间的联系，定义与数据有关的安全性完整性要求
2. 外模式
   外模式也称用户模式，它是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。外模式是模式的子集。一个数据库可以有多个外模式。
   外模式是保证数据安全性的一个有力措施。
3. 内模式
   内模式也称存储模式，一个数据库只有一个内模式。它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。

1.2.2 三级模式之间的映射

为了能够在内部实现数据库的3个抽象层次的联系和转换，数据库管理系统在三级模式之间提供了两层映射。

1. 外模式/模式映射
   对应于同一个模式可以有任意多个外模式。对于每一个外模式，数据库系统都有一个外模式/模式映射。当模式改变时，由数据库管理员对各个外模式/模式映射作相应的改变，可以使外模式保持不变。依据数据外模式编写的应用程序就不用修改，保证数据与程序的逻辑独立性。
2. 模式/内模式映射
   模式/内模式映射唯一，定义了数据库的全局逻辑结构与存储结构之间对应关系。当数据库的存储结构改变时，由数据库管理员对模式/内模式映射作相应改变，可以使模式保持不变，应用程序相应地也不做变动。保证数据与程序的物理独立性。

1.3 数据模型

数据模型是一种对客观事物抽象化的表现形式。它对客观事物加以抽象，通过计算机来处理现实世界中的具体事物。它客观地反映了现实世界，易于理解，与人们对外部事物描述的认识相一致。

1.3.1 数据模型的概念

数据模型由数据结构、数据操作、完整性约束组成。
数据结构：是对系统静态特征的描述。描述对象包括数据的类型、内容、行政和数据之间的相互关系。
数据操作：是对系统动态特征的描述，是对数据库中各种对象实例的操作。
完整性约束：是完整性规则的集合。他定义了给定数据模型中数据及其联系所具有的制约和依存规则。

1.3.2 常见的数据模型

1. 层次模型：用树形结构表示实体类型及实体间联系的数据模型成为层次模型。

• 每棵树有且仅有一个无双亲节点，称为根。
• 树中除根外所有节点有且仅有一个双亲。

2. 网状模型：用有向图结构表示实体类型及实体间联系的数据模型成为网状模型。用网状模型编写应用程序及其复杂，数据的独立性较差。
3. 关系模型：以二维表来描述数据。在关系模型中，每个表有多个字段列和记录行，每个字段列有固定的属性（数字、字符、日期等）。关系模型数据结构简单、清晰、具有很高的数据独立性，因此是目前主流的数据库数据模型。

• 关系：二维表
• 元组：二维表一行，表中的记录
• 属性：二维表一列，用类型和值表示
• 域：每个属性取值的变化范围，如性别的域为

关系模型中的数据约束：

• 实体完整性约束：约束关系的主键属性值不能为空
• 参照完整性约束：关系之间的基本约束
• 用户定义的完整性约束：反应具体应用中数据的语义要求

1.3.3 关系数据库的规范化

第一范式（1NF）、2NF、3NF、鲍依斯-科得范式（BCNF）、4NF、5NF。其中，NF是Normal Form的缩写。一般情况下，只要把数据规范到第三范式标准即可满足需要。

• 1NF：在一个关系中，消除重复字段，且各字段都是最小的逻辑存储单位。
• 2NF：1NF基础上，消去非主属性对键的部分函数依赖
• 3NF：2NF基础上，消去非主属性对键的传递函数依赖
• BCNF：3NF基础上，消去主属性对键的传递函数依赖
• 4NF：BCNF基础上，消去表中的多值依赖
  详细说明

1.3.4 关系数据库的设计原则

1. 数据库内文件的数据组织应获得最大限度的共享、最小的冗余度，消除数据及数据依赖关系中的冗余部分，使依赖于同一个数据模型的数据达到有效的分离。
2. 保证输入、修改数据时数据的一致性与正确性。
3. 保证数据与使用数据的应用程序之间获得高度独立性。

1.3.5 实体与关系

实体是指客观存在并可相互区别的事物，也可以是抽象的概念或关系。实体之间有三种关系。

1. 一对一关系：表A中的一条记录确实在表B中有且只有一条相匹配的记录。在一对一关系中，大部分相关信息都在一个表中。
   学生与学生的身份证号
2. 一对多关系：表A中的行可以在表B中有许多匹配行，但是表B中的行只能在表A中有一个匹配行。
   一个学生只属于一个班，一个班有多名学生
3. 多对多关系：关系中的每个表的行在相关表中具有多个匹配行。在数据库中，多对多关系的建立是依靠第3个表（称作连接表）实现的，连接表包含相关的两个表的主键列，然后从两个相关表的主键列分别创建与连接表中的匹配列的关系。
   一个学生可以选择多门课，一门课也有多名学生

详细说明

1.4 常见关系数据库

Oracle在并行处理、实时性、数据处理速度方面都有较好的性能。

第4章 创建与管理数据库

4.1 认识数据库

4.1.1 数据库基本概念

关系数据库
关系数据库是支持关系模型的数据库。关系模型由关系数据结构、关系操作集合和完整性约束3部分组成。

1. 关系数据结构：在啊关系模型中数据结构单一，现实世界的实体以及实体间的联系均用关系来表示，实际上关系模型中数据结构就是一张二维表
2. 关系操作集合：关系操作分为关系代数、关系演算、具有关系代数和关系演算双重特点的语言（SQL语言）
3. 完整性约束

4.1.2 数据库常用对象

• 表：表是包含数据库中所有数据的数据库对象，由行和列组成，用于组织和存储数据。
• 字段：表中每列成为一个字段，字段具有自己的属性，如字段类型、字段大小等。其中，字段类型是字段最重要的属性，它决定了字段能够存储哪种数据。
• 索引：索引是一个单独的、无力的数据库结构。它是依赖于表建立的，在数据库中索引是数据库程序无需对整个表进行扫描，就可以在其中找到所需的数据。
• 视图：视图是从一张或多张表中导出的表（也称虚拟表），是用户查看数据表中数据的一种方式。表中包括几个被定义的数据列与数据行，其结构和数据建立在对表的查询基础上。
• 存储过程：存储过程是一组为了完成特定功能的SQL语句集合（包含查询、插入、删除和更新等操作），经编以后以名称的形式储存在SQL Server服务器端的数据库中，由用户通过制定存储过程更的名字来执行。当这个存储过程被调用执行时，这些操作也会被同时执行。

4.2 SQL Server的命名规范

4.2.1 标识符

1. 标识符格式

• 标识符的首字符必须是Unicode所定义字符，'_' '@' '#'。
  '@'开始的标识符表示局部变量或参数
  '#'开始的标识符表示临时表或过程
  '##'开始的标识符表示全局临时对象
• 标识符后的后续字符
  Unicode 2.0 标准中所定义的字母。
  来自拉丁字母或其他国家/地区脚本的十进制数字。
  '@' '$' '#' '_'
• 标识符不允许是Transact-SQL的保留字
  微软官方文档
• 不允许嵌入空格或其他特殊字符

2. 标识符分类

• 常规标识符：符合标识符的格式规则
• 分隔标识符：包含在“”或[]内的标识符。该标识符可以不符合标识符的格式规则。

4.2.2 对象命名规则

在一个数据库中创建了一个数据库对象后，数据库对象的完整名称应该由服务器名、数据库名、拥有者名和对象名4部分组成
[[[server.][database].][owner_name].]object_name
服务器、数据库和所有者的名称即所谓的对象名称限定符。当引用一个对象时，不需要指定服务器、数据库和所有者，可以利用句号标出他们的位置，从而省略限定符。
指定了所有4哥部分的对象名称被称为完全合法名称。

4.3.3 使用管理器删除数据库

删除数据库时必须满足以下条件

• 如果数据库设计日志传输操作，在删除数据库之前必须取消日志传送操作
• 若要删除为事务复制发布的数据库，或删除为合并复制发布或订阅的数据库，必须首先从数据库中删除复制。如果数据库已损坏，不能删除复制，可以先将数据库设置为脱机状态，然后再删除数据库。
• 如果数据库中存在数据库快照，必须首先删除数据库快照。
  系统数据库（msdb、model、master、tempdb）无法删除，删除数据库后应立即备份master数据库，因为删除数据库将更新master数据库中的信息。

第5章 操作数据表与视图

5.1 数据表基础知识

5.1.1 基本数据类型

基本数据类型详情
char、varchar、nchar、nvarchar的区别：

5.1.2 用户定义数据类型

2. 使用T-SQL语句创建用户自定义数据类型

创建用户自定义数据类型
语法：

sp_addtype [ @typename = ] type,   
    [ @phystype = ] system_data_type   
    [ , [ @nulltype = ] 'null_type' ] ;

实例：

USE test
EXEC sp_addtype postcode,'char(8)','not null'

删除用户自定义数据类型
语法：

sp_droptype [ @typename = ] 'type'

实例：

USE test
EXEC sp_droptype postcode

5.1.3 数据表的数据完整性

Microsost SQL Server 2008提供多种强制数据完整性的机制。

1. NULL or NOT NULL
   表的每一列都有一组属性，如名称、数据类型、数据长度和为空性等，列的所有属性即构成列的定义。列可以定义为允许或不允许空值。

• NULL：默认情况下，列允许空值，即允许用户在增加数据是省略该列的值。
• NOT NULL：不允许在没有指定列默认值的情况下该列的值。

2. 默认值
   如果在插入行时没有指定列的值，那么默认值将指定列中所使用的值。默认值可以使任何取值为常量的对象，如内置函数和数学表达式等。

• 在CREATE TABLE中使用DEFAULT关键字创建默认定义，将常量表达式指派为列的默认值，这是标准方法。
  - 使用CREATE DEFAULT语句创建默认对象，然后使用sp_bindefault系统存储过程将它绑定到列上，这个一个向前兼容的功能。[此功能将在Microsoft SQL Server的未来版本中删除]

3. IDENTITY(特征标识属性)
   数据表中如果某列被指派IDENTITY，系统将自动为表中插入的新行生成连续递增的编号。因为标识值通常唯一，所以标识列通常定义为主键。
   IDENTITY属性适用于INT、SALLINT、TINYINT、DECIMAL(P,0)、UMERIC(P,0)数据类型的列。
4. 约束
   约束是用来定义Microsoft SQL Server 2008自动强制数据库完整性的方式。使用约束优先于使用触发器、规则和默认值。SQL Server 2008中共有以下5种约束。

• NOT NULL：使用户必须在表的指定列中输入一个值。每个表中可以有多个非空约束。
• Check（检查）：用来指定一个布尔操作，限制输入到表中的值。
• Unique（唯一性）：建立一列或多列的组合以唯一标识表中的每一行。逐渐可以保证实体完整性，一个表只能有一个主键，同时逐渐中的列不能接受空值。
• Foreign key（外键）：用于建立和加强两个表数据之间的链接的一列或多列。当一个表中作为主键的一列被添加到另一个表中时，链接就建立了，主要目的是控制存储在外键表中的数据。

5.3 创建、删除和修改约束

约束是SQL Server提供的自动强制数据完整性的一种方式，它是通过定义列的取值规则来维护数据的完整性，是强制完整性的标准机制。使用约束优先于使用触发器、规则和默认值。查询分析器也使用约束定义生成高性能的查询执行计划。

5.3.3 UNIQUE约束

当在表中已创建主键，但又要保证其他数据列的值唯一时，可以使用UNIQUE约束。
使用UNIQUE约束可以确保在非主键列中部输入重复的值。UNIQUE约束和PRIMARY KEY约束都具有强制唯一性，但想要强制一列或多列组合（不是PRIMARY KEY）的唯一性时，应使用UNIQUE约束而不是PRIMARY KEY约束。
可以对一个表定义多个UNIQUE约束，但只能定义一个PRIMARY KEY约束。而且UNIQUE约束允许NULL。当与参与UNIQUE约束的任何值一起使用时，每列只允许一个NULL。FOREIGN KEY约束可以引用UNIQUE约束。
操作：

表->设计->索引/键->增加
列：选择列
类型：唯一键

5.3.4 CHECK约束

CHECK约束通过限制输入到列中的值来强制域的完整性。此类约束类似于FOREIGN KEy约束，可以控制放入到列中的值。

• FOREIGN KEY约束：从其他表获得有效值列表
• CHECK约束：通过不急于其他列中的数据的逻辑表达式确定有效值。
  创建CHECK约束可以将某列数据的取值范围限制在指定的范围内，从而防止输入的数据超出指定的范围。
  操作

表->设计->CHECK约束->增加
表达式：old>18 and old<100

5.3.5 默认约束

默认约束使用户能够定义一个值，每当用户没有在某一列中输入值时，则将所定义的值提供给这一列。如果用户对此列没有特定的要求，可以使用默认约束来为此列输入默认值。
即使列为NOT NULL，设置默认约束后，可不进行输入，会使用默认值。
操作

表->设计->选中列->列属性
默认值或绑定：https://image.baidu.com/s/101.jpg

5.3.6 FK（外键）约束

FK适用于建立两个表数据之间连接的一列或多列。通过将保存表中主键值的一列或多列增加到另一个表中，可创建两个表之间的链接，这个列就成为第二个表的外键。
操作

表->设计->关系->增加
表和列规范：选择主键表的列和外键表的列
INSERT和UPDATE规范
删除规则
不执行任何操作：报错不执行
级联：删除主键表列的同时将关联的外键表中的列同时删除
更新规则
级联：更新主键表列的同时将关联的外键表中的列同时更新

SQL的主键和外键的作用：
外键取值规则：空值或参照的主键值。

• 插入非空值时，如果主键表中没有这个值，则不能插入。
• 更新时，不能改为主键表中没有的值。
• 删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。
• 更新主键记录时，同样有级联更新和拒绝执行的选择。

5.4 关系的创建与维护

5.4.1 一对一关系

在一对一关系中，大部分相关信息都在一个表中。特点

• 分割一个含有许多列的表
• 出于安全考虑而隔离表的某一部分
• 存储可以很容易删除的临时数据，只需删除表即可删除这些数据
• 存储值应用于主表子集的信息。
  如果连个相关列都是主键或具有唯一约束关系，创建的就是一对一关系。

5.4.2 一对多关系

一对多关系是最常见的关系类型，是指表A中的行可以在表B中有许多匹配行，但是表B中的行只能在表A中有一个匹配行
如果在相关列中只有一列是主键或具有唯一约束，则创建的是一对多关系。

5.4.3 多对多关系

多对多关系是指关系中每个表的行在相关表中具有多个匹配行。在数据库中，多对多关系的建立是依靠第3哥表即连接表实现的，连接表包含相关的两个表的主键列，然后从两个相关表的主键列分别创建与链接表中匹配列的关系。

5.5 视图概述

视图是一种常用的数据库对象，它将查询的结果以虚拟表的形式存储在数据中。视图并不在数据库中以存储数据集的形式存在。视图的结构和内容是建立在对表的查询基础之上的，和表一样包括行和列，这些行列数据都来源于其所引用的表，并且是在引用视图过程中动态生成的。
视图中的内容是有查询定义来的，并且视图和查询都是通过SQL语句定义的，他们有着许多相同和不同之处。

×	视图	查询
存储	视图存储为数据库设计的一部分，	查询不存储
排序	只有当视图包括TOP子句才能排序视图	可以排序任何查询结果
加密	可以加密视图	不能加密查询
可以禁止所有用户访问数据库的基表，而要求用户只能通过视图操作数据。这种方法可以保护用户和应用程序不受某些数据库修改的影响

5.6 使用企业管理器管理视图

标准视图：保存在数据库中的SELECT查询语句，即通常意义上理解的视图。
索引视图：创建有索引的视图成为索引视图。他经过计算并存储有自己的数据，可以提高某些类型查询的性能，尤其适用于聚合许多行的查询，但不太适合于经常更新的基本数据集。
分区视图：是在一台或多台服务器间水平联结一组表中的分区数据，以使数据看上去来源于一个表。