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

1.1 理论背景
关系模型两种数学理论：集合论 和 谓词逻辑。

1.1.1 SQL
SQL是基于关系模型的ANSI和ISO标准语言，专门设计用于查询和管理RDBMS中的数据。

SQL几种不同类型的语句：
1.数据定义语言(DDL，Data Definition Language)：用户处理数据对象的定义，包括的语句有Create、Alter和Drop。
2.数据处理语言(DML,Data Manipulation Language):用于查询和修改数据，包含的语句有Select、Insert、Update、Delete和Merge。
3.数据控制语言(DCL,Date Control Language):用于处理权限管理，包括的语句有Grant和Revoke。

 

1.1.2 集合论
集合的非正式定义：集合就是把我们直观或思维中确定的、不同的那些对象作为一个整体来考虑的结果。这些对象就是集合的元素或成员。

“整体”：应该将集合视为一个实体，重点关注一组对象。比如，应该把一组雇员对象看作一个整体，而不是单独的雇员。

“不同的”：集合中的元素必须是唯一的。通过key来约束唯一性。

“直观或思维中”：集合定义有一定的主观性。每个人的看法不一样。

“对象”：不限于物理对象，也适用于抽象对象(如质素或线段)。

另外，集合无关顺序。所以对数据表的查询可以返回任何顺序排列的数据行，除非明确要求进行排序。

1.1.3 谓词逻辑(Predicate Logic)
谓词就是用来刻画事务是否具有某种性质或满足某种表达式条件的词。换句话说，也就是true或false。

在关系模型中，谓词用户维护数据的完整性和定义它的结构。
完整性例子：在雇员表中定义约束，只有薪资大于0的雇员数据才能保存到表中。(表达式：salary>0)
谓词对数据进行过滤：返回来自销售部门的雇员信息。(表达式：department='sales')

可以用谓词来定义集合：
谓词定义无限集合。如：
定义质素集合“X是大于1的正整数，而且只能被1和它本身整除”。
谓词定义有限集合：
集合{0,1,2,3,4,5,6,7,8,9} “X是一个大于或等于0，而小于或等于9的整数”

 

1.1.4 关系模型(Relational Model)
关系模型是一个用于表示数据的语义模型，理论基础是集合论和谓词逻辑。

关系模型的目的：用最少或完全无冗余地支持完整数据的持久化表示，而且要将数据完整性(强制的数据一致性)定义为模型的一部分。

命题、谓词和关系：
关系是集合的一种表示。在关系模型中，关系是相关信息的一个集合，表现为数据表。
关系模型的一个关键要点是：一个关系代表一个集合(例如，Customers)。

为数据库设计数据模型是，所有数据都是用关系(数据表)来表示的。首先要确定一个命题，用户表示数据库中要保存的信息。
如“雇员Ben出生于1971年2月12日，你属于IT部门”就是一个命题。为真就表示Employees表中的一行，假就表示不是Employees表中的一行。

接下来对命题进行形式化处理，从命题中分析出具体的数据(关系本身)，并定义其结构(关系的标题)。
例如，根据命题来创建它的谓词。而关系的标题则组成了属性的一个集合(set)。
在关系模型中，属性是没有顺序的。属性是由一个属性名称和一个域(或类型)名称来标识的。
例如，Employees关系的标题可能由以下属性组成的(用属性名称和相应类型名称表示)：employeeid 整型、firstname 字符串型、birthdate 日期型。
域或类型是最基础的关系型构建模块之一。一个域是一个属性可能的(或有效的)一组取值的集合。如，Int类型范围-2147483648-2147483647之间的所有整数。
域是一种数据库中形式最简单的谓词。
注意：域并不只限于整型或字符串型这样的基本类型，也可以是枚举类型。
域的最佳处理方式是看作一个类(封装了数据和支持它的行为)。
复杂的域的一个例子可以是一个几何域，可以支持多边形。

 

缺少的值：
关系模型中的争议：命题是否只限于使用二值谓词逻辑(要么ture要么false)。
对于缺少值的特殊情况，SQL实现了三值的谓词逻辑，用NULL符号来表示缺少值这种一般概念。

约束：
关系模型最大优点之一就是将数据完整定义为模型的一部分。完整性是通过规则(或约束)来实施的。最简单方法时设置属性的类型和是否允许为空。
约束还有候选键，这些候选键都可以作为主键，被采用的是主键，剩下的称为备用关键字。
外键用于实施引用完整性。

 

规范化：
关系模型同时也定义了规范化规则(也称为范式)。在规范化的数据库中，应该避免对数据的异常修改，而且在不牺牲完整性的前提下，将数据冗余降到最低。

第一范式：
表中的行必须是唯一的，属性应该是原子的(atomic)。行的唯一性通过定义主键实现。
如果表可以表示未一个关系，那么它一定符合第一范式。

第二范式：
首先数据必须满足第一范式，其次要求非键属性和候选键属性之间必须满足一定的条件。
一个非键属性不能只完全函数依赖于候选键的一部分。如果知道了一个候选键的所有属性值，就能够找到任意行的任意属性值。
注释：函数依赖指的是存在在组合候选键字中的某些字段决定非关键字段的情况。

违反第二范式的示例：

图1-1中的数据模型违反第二范式的原因：
其中存在非键属性部分依赖候选键(例子中的主键)的情况。值通过orderid属性就可以找到订单的一行完整属性。

应该改为下图所示：

 

第三范式：
首先，数据必须满足第二范式。
其次，所有非键属性必须非传递依赖于候选键。意味着所有非键属性必须相互独立。一个非键属性不能依赖于其他非键属性。

图1-2中，虽然符合第二范式。但Orders关系中，主键定义在orderid上，customerid和companyname属性都依赖于整个主键(orderid)。例如，需要知道整个整个主键才能找到下单的customerid，同样，需要知道主键才能知道companyname。然而，costomerid和companyname属性也是相互依赖的。

为了满足第三范式 应该改为下图

站内三大范式讲解推荐：数据库表设计总结

 

1.1.5 数据生命周期

联机事务处理(OLTP,OnLine Transactional Processing)
数据首先进入联机事务处理(OLTP)系统，该系统重点是数据输入，处理的事务包括插入、更新和删除数据。

数据仓库(Data Warehouse)
数据仓库是针对数据检索和生成报表而设计的环境。这样的环境服务于整个企业时，称为数据仓库；若只服务于企业的一部分时称为数据集市。

联机分析处理(OLAP,OnLine Analytical Processing)
联机分析处理支持对聚合后的数据进行动态在线分析。

数据挖掘(DM,Data miniing)：
数据挖掘算法梳理数据，从中筛选出有用的信息。SSAS支持用数据挖掘算法(包括聚类分析、决策树等)来解决这些需求。用户管理和查询数据挖掘模型的语言时数据挖掘扩展插件(DMX,Data Mining Extensions)语句。

 

1.2 SQL Server体系结构

1.2.1 SQL Server实例
SQL Server实例是指安装的一个SQL Server数据库引擎/服务。同台计算机上可以安装多个实例。每个实例时完全彼此独立的。位于同一计算机上的两个不同实例和位于两台不同计算机的实例相差无几。它们会共享服务器物理资源(如CPU、内存、磁盘空间)。

可以将计算机上安装的实例之一设置为默认实例，而其他实例则必须为命名实例。
客户端应用程序要联接到默认实例，只需指明实例所在计算机的名称或IP地址。
联接到一个命名实例，要指明计算机的名称或IP地址外，接着加一个反斜杠字符("\")+实例名称。

1.2.2 数据库
可以将数据库认为是各种对象的容器，这些对象可以是表、视图、存储过程等。

安装程序创建的系统数据库包括master、Resource、model、tempdb和msdb。
master：保存SQL Server实例范围内的元数据信息、服务器配置、实例中所有数据库的信息，以及初始化信息。

Resource：是SQL Server2005中增加的，用于保护所有系统对象。当查询数据库中的元数据信息时，这种信息表面上是位于数据库中，但实际是保存在Resource数据库中的。

model：是新数据库的模板。每个新创建的数据库最初都是model的一个副本。model数据库做出的修改不会影响现有数据库，只影响伺候新创建的数据库。

tempdb：SQL Server保存临时数据的地方，这些临时数据包括工作表、排序空间、行版本控制信息等。
注意：每次重新启动SQL Server实例时，会删除这个数据库中的内容，并将其重新创建为model的一个副本。 

 

图1-7演示了数据库的物理布局图

 

数据库在物理上由数据文件和事务日志文件组成。数据文件保存数据库对象数据，日志文件保存SQL Server维护事务需要的信息。

多个数据文件在逻辑上按照文件组(filegroup)的形式进行分组管理。创建数据库对象(如表或索引)时，就会将其保存在目标文件组中。对象数据可能会保存在属于目标文件组的多个文件中。通过文件组可以控制数据库对象的物理存储位置。数据库必须至少要有一个主文件组(Primary)，而用户定义的文件组则是可选的。Primary文件组包含主数据文件(扩展名为.mdf)，以及数据库的系统目录（catalog）。可选择性为Primary增加多个辅助数据文件（secondary data file），扩展名为.ndf。用户定义的文件组只能包含辅助数据文件。可以指定将哪个文件组作为默认文件组。当对象创建语句没有明确指定目标文件组时，就将它创建在默认文件组中。

 

1.2.3 架构(Schema)和对象

图1-8所示，一个数据库包含多个架构，每个架构包含多个对象。可以将架构看作是各个对象的容器，这些对象可以是表、视图、存储过程等。

可以在架构级别上控制对象的访问权限。

架构也是一个命名空间，用作对象名称的前缀。引用对象时推荐总是使用架构+对象名称(Sales.Orders)。

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

表是属于架构，架构数据数据库。
If DB_ID('testdb') Is NULL
  Create Database testdb;
默认架构为dbo。