第01章 查询和编程基础（理论背景、SQLServer体系结构、创建表和定义数据完整性）

第1章 查询和编程基础 Background to T-SQL Queying and Programming

 

1.1 理论背景

1.1.1 SQL
SQL（Structured Query Language）是基于关系模型的ANSI和ISO标准语言，专门设计用于查询和管理RDBMS中的数据。T-SQL是标准ANSI-SQL在Microsoft SQL Sever中的独特实现（也称为方言）。
关系模型基于两种数学理论：集合论和谓词逻辑。既然SQL是以关系模型作为它的基础，从一定程度上说，它就有了坚实的数学基础。

1.1.2 集合论
集合论的创始人是数学家格奥.康托（Georg Cantor），他对集合的定义如下：
把我们直观或思维中确定的、相互间具有明显区别的那些事物m视为一个整体M。称M为集合，m为M的元素。

1.1.3 谓词逻辑
谓词就是用来刻画事物是否具有某种性质或满足某种表达式条件的一个词项，换句话说也就是true和false

1.1.4 关系模型
规范化（Normalization）
第一范式（1NF）：表中的行必须是唯一的，属性应该是原子的。
第二范式（2NF）：满足1NF，且每个非键属性完全函数依赖于整个候选键。换句话说，每个非键属性不能只函数依赖于候选键的一部分。
第三范式（3NF）：满足2NF，所有非键属性必须非传递依赖于候选键。

1.1.5 数据生命周期

 

 

（1）联机事务处理（OLTP，OnLine Transaction Processing）
OLTP系统的重点是数据输入，而不是生成报表，主要处理的是插入、更新和删除数据。
关系模型的目标主要定位于OLTP系统，一个规范化的模型可以为数据输入和数据一致性提供更好的性能，并将数据冗余保持咋最低限度。
缺点：不适合生成报表的使用目的，因为会涉及大量多表联结运算，导致查询复杂、性能低下。
可以在SQL Server中实现OLTP数据库，并用T-SQL对它进行管理和查询。
（2）数据仓库（Data Warehouse）
DW是专门针对数据检索和生成报表而设计的环境。
有意保持了一定的冗余，允许通过更少的表和更简单的关系，最终得到比OLTP环境更加简单和有效的查询。
DW中的数据通常会预先聚合到某个特定级别的粒度（如日期），而在OLTP环境中的数据则通常按照事务级别来记录。
SQL Server早期版本主要定位于OLTP环境。现在也可以将数据仓库实现为一个SQL Server数据库，并用T-SQL对它进行管理和查询。
从源系统（OLTP，以及其他系统）抽取数据，对数据进行处理，并将数据加载到数据仓库的工具称为ETL（Extract Transform and Load）。SQL Server提供一个称为Miscrosoft SQL Server Integration Service（SSIS）的工具来处理ETL需求。
（3）联机分析处理（OLAP，Online Analytical Processing）
对聚合后的数据进行动态的在线分析处理。实现这一想法有两种解决方案：
①方案一：在关系数据仓库中计算和存储不同级别的聚合。这种方案需要编写一套复杂的过程来处理聚合的初始化和增量更新。
②方案二：使用专门为OLAP需求而设计的特殊产品——Miscrosoft SQL Server Analysis Services（SSAS，或AS）。它可计算不同级别的聚合，并将结果保存在一种经过优化的多位结构（多维数据集，cube）中。
SSAS多维数据集的数据源可以是（通常也是）关系数据仓库。除了支持大量的聚合数据，SSAS也提供了许多丰富而复杂的数据分析功能。
用于管理和查询SSAS数据方块的语言称为多维表达式（MDX，Multidimensional Expressions）。
（4）数据挖掘（DM，Data mining）
不是让用户自己在数据海洋中查找有用信息，数据挖掘模型可以为用户做这些。
SSAS支持用数据挖掘算法（包括聚类分析、决策树等）来解决这些需求。用于管理和查询数据挖掘模型的语言是数据挖掘扩展插件（DMX，Data Mining Extensions）。

 

1.2 SQL Server 体系结构

 

SQL server体系结构涉及的实体包括：SQL Server实例、数据库、模式，以及数据库对象。

1.2.1 SQL Server 实例
SQL Server实例是指安装的一个SQL Server数据库引擎/服务。
在同一台计算机上可以安装多个实例，它们彼此逻辑上是完全独立的，共享服务器的物理资源。
（1）默认实例
只能安装一个。要连接到一个默认实例，只需指明实例所在的计算机的名称或IP地址。
如：Server1
服务器名称也可以是 (local) 、 . 、 loacalhost ，当本机未安装网卡（驱动）时使用 (local)
（2）命名实例
可安装多个。要联结到一个命名实例，客户端要指明计算机的名称或IP地址，接着再写一个反斜杠（“\”），后面指明实例名称（在安装期间提供的）。如：Server1\Inst1
1.2.2 数据库
一个实例可以安装多个数据库。安装SQL Server时，安装程序会创建几个系统数据库，安装好后，用户可以创建自己的用户数据库。
可以在数据库级上定义一个称为collation（排序规则）的属性，由它确定数据库中字符数据使用的排序规则信息（包括支持的语言、区分大小写和排序规则）。如果在创建数据库时不为其指定collation属性，将使用实例默认的排序规则设置。
（1）逻辑层面

①master
保存实例范围内的元数据信息、服务器配置、实例中所有数据库的信息，以及初始化信息。
②Resource
保存所有系统对象。当查询数据库中的元数据信息时，这种信息表面上是位于数据库中，但实际上是保存在Resouce数据库中的。
③model
是新数据库的模板。每个新创建的数据库最初都是model的一个副本（copy）。对model数据库的修改不会影响现有的数据库，只影响此后新创建的数据库。
④tempdb
是保存临时数据的地方。每次重启SQL Server实例时，会删除这个数据库的内容，并将其创建为model的一个副本。
⑤msdb
是称为SQL Server Agent的一种服务保存其数据的地方。SQL Server Agent负责自动化处理。
（2）物理层面

每个数据库至少要有一个数据文件和一个日志文件。
多个数据文件可在逻辑上按照文件组（filegroup）的形式进行分组管理。通过文件组可以控制数据库对象的存放位置。数据库至少要有一个主文件组（PRIMARY），而用户定义的文件组是可选的。
1.2.3 架构（Schema）和对象
架构是各种对象的容器

（1）可以在架构级别上控制对象的访问权限。
（2）架构也是一个命名空间。建议引用对象时，写明架构名称，避免解析对象名称时付出额外代价。

 

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

1.3.1 创建表

-- Create a database called testdb
IF DB_ID('testdb') IS NULL
  CREATE DATABASE testdb;
GO

-- Create table Employees
USE testdb;

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
  DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees
(
  empid     INT         NOT NULL,
  firstname VARCHAR(30) NOT NULL,
  lastname  VARCHAR(30) NOT NULL,
  hiredate  DATE        NOT NULL,
  mgrid     INT         NULL,
  ssn       VARCHAR(20) NOT NULL,
  salary    MONEY       NOT NULL
);

ANSI规定：如果不指定一个列是否允许空值，则假设应该是NULL（允许NULL）。SQL Server提供了一些设置可以改变这一默认行为。强烈建议显式的写明是否允许为空。

1.3.2 定义数据完整性
关系模型带来的最大优点之一就是模型本身集成了数据完整性。
作为模型的一部分而实施的数据完整性（也就是说，作为表定义的一部分）称为声明式（declarative）数据完整性；用代码来实施的数据完整性（例如，用存储过程或触发器）称为过程式（procedural）数据完整性。
（1）主键约束（Primary Key Constraints）

-- Primary key
ALTER TABLE dbo.Employees
  ADD CONSTRAINT PK_Employees
  PRIMARY KEY(empid);

（2）唯一约束（Unique Constraints）
ANSI支持两种类型的唯一性约束，一种是只允许在唯一约束列中有一个值可以为NULL，另一种是允许多个NULL值。SQL Server只实现了前者。

-- Unique
ALTER TABLE dbo.Employees
  ADD CONSTRAINT UNQ_Employees_ssn
  UNIQUE(ssn);

（3）外键约束

-- Foreign key
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
  DROP TABLE dbo.Orders;

CREATE TABLE dbo.Orders
(
  orderid   INT         NOT NULL,
  empid     INT         NOT NULL,
  custid    VARCHAR(10) NOT NULL,
  orderts   DATETIME    NOT NULL,
  qty       INT         NOT NULL,
  CONSTRAINT PK_Orders
    PRIMARY KEY(OrderID)
);

ALTER TABLE dbo.Orders
  ADD CONSTRAINT FK_Orders_Employees
  FOREIGN KEY(empid)
  REFERENCES dbo.Employees(empid);

ALTER TABLE dbo.Employees
  ADD CONSTRAINT FK_Employees_Employees
  FOREIGN KEY(mgrid)
  REFERENCES Employees(empid);　

默认为“禁止操作（no action）”，含义为：当试图删除被引用表中的行，或更新被引用的候选键时，如果在引用表中存在相关的行，则此操作不能执行。
可以定义级联操作的外键，当ON DELETE或ON UPDATE时：
①CASCADE：从被引用表中删除一行时，RDBMS也将从引用表中删除相关行
②SET DEFAULT
③SET NULL
（4）检查约束（Check）
检查约束用于定义在表中输入或修改一行数据之前必须满足的一个谓词。例如，以下的检查约束可以保证Employees表的salary列只支持正数：
只有当谓词计算结果为TRUE或UNKNOWN时，RDBMS才会接受对数据行的修改。

-- Check
ALTER TABLE dbo.Employees
  ADD CONSTRAINT CHK_Employees_salary
  CHECK(salary > 0);

（5）默认约束（Default）
默认约束与特定的属性关联。当插入一行数据时，如果没有为属性显式指定明确的值，就可以用一个表达式作为其默认值。

-- Default
ALTER TABLE dbo.Orders
  ADD CONSTRAINT DFT_Orders_orderts
  DEFAULT(CURRENT_TIMESTAMP) FOR orderts;