T-SQL基础教程Day1

 

 

1.1 理论背景
SQL代表结构化查询语言（Structured Query Language），是一种在关系数据库系统（Relational Database Management Systems,RDBMS）中查询和管理数据的标准语言。
RDBMS是基于关系模型的数据库管理系统，
它基于两个数学分支：集合理论和谓词逻辑。

1.1.1 SQL
SQL是一种基于关系模型的ANSI和ISO标准语言，用于查询和管理RDBMS中的数据。
SQL语句包括
数据定义语言Data Definition Language, DDL
数据操作语言Data Manipulation Language, DML
数据控制语言Data Control Language, DCL

1.1.2 集合理论
集合，我们的意思是：任意集合体M使我们感知或想到的，能够确定的、互异对象m（称为M的元素）的整体。
——Georg Cantor
一个“集合”应将其视为单个实体。
“互异”指集合中的每个元素必须是唯一的。
“我们感知或想到的”意味着集合的定义是主观的。
“对象”集合的定义不限于物理对象，更与抽血的对象相关。
请注意，定义中没有提到集合元素间的任何顺序，集合元素的列出顺序并不重要。

1.1.3 谓词逻辑
谓词是一个属性或是一个表示“持有”或“不持有”的表达式，
换句话说，也就是“真”或“假”。
关系模型就是依靠谓词来维护数据的逻辑完整性并定义其结构的。
谓词用于实施完整性的一个例子就是：在一个名为Employees的表中定义了约束，仅允许工资（Salary）大于0的雇员存储在表中。
谓词是“工资大于0”（T-SQL表达式：salary>0）。
通过筛选数据来界定子集等操作时，也可以使用谓词。

1.1.4 关系模型
关系模型的目标是确保数据的一致性表示，最小化或是没有冗余且不牺牲完整性，并将定义数据的完整性作为模型的一部分。
1.关系
普通的想法是，“关系”术语源于表之间的关系，这是不正确的。
“关系”实际上是数学术语的关系。
在集合理论中，关系是集合的表现形式。在关系模型中，关系是相关的信息的集合，与SQL中相对应的就是表——尽管不能完全对应。
2. 缺失值
三值谓词逻辑是指来自谓词结果的3中可能逻辑值
——“真”、“假”、“未知”
3. 约束
数据完整性通过在数据模型中定义的约束规则实现，并由RDBMS实施。
4. 规范化
关系模型还定义了规范化规则，也称范式。
1NF，2NF，3NF（具体上网查阅定义）

1.1.5 数据生命周期
1 联机事务处理
OLTP系统的重点是数据输入，而不是数据报告。
2 数据仓库
DW是一个以检索数据和报告为目的设计的环境。
3 商业智能语义模型
BISM是微软支持完整BI堆栈应用的最新模型，目的是提供丰富、灵活、高效、可扩展的分析和报告功能。
4 数据挖掘
数据挖掘模型可以替代用户在数据海洋中查找有用的信息。

1.2 SQL Server体系结构
1.2.1 SQL Server的ABC特色
A是应用（Appliance）
“应用”特色背后的理念是提供包括硬件、软件和服务在内的完整解决方案，应用是在客户端本地托管的。
B是盒子（Box）
“盒子”是企业内部部署的SQL Server，这是传统的部署方式。
C是云（Cloud）
微软支持两种SQL Server云特色：私有云和公共云。

1.2.2 SQL Server实例
可以在同一台计算机上安装基于企业内部部署的SQL Server的多个实例。每个实例完全独立于其他实例。
默认实例和命名实例

1.2.3 数据库
系统数据库介绍如下：
master:存储实例范围的元数据信息、服务器配置、实例中的所有数据库信息和初始化信息。
resource:Resource数据库是一个隐藏、制度数据库，存储所有系统对象的定义。当查询数据库中的系统对象时，似乎位于当前数据库的sys架构中，但实际上他们的定义位于Resource数据库中。
model:用作创建新数据库的模板，创建的每个新数据库是有model的副本初始化创建的。
tempdb：是SQL Server存储临时数据的地方，如工作表、排序空间、行版本控制信息等。
msdb：是一个成为“SQL Server代理”的服务存储数据的地方。还存储其他SQL Server功能的相关信息，如Database Mail，Service Broker、备份等等。

1.3.1 创建表

-- Create table Employees
USE TSQL2012;

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
);

1.3.2 定义数据完整性

--------------------------------------------------------
-- Data Integrity
--------------------------------------------------------
-- Primary key
ALTER TABLE dbo.Employees
  ADD CONSTRAINT PK_Employees
  PRIMARY KEY (empid);
-- Unique
ALTER TABLE dbo.Employees
  ADD CONSTRAINT UNQ_Employees_ssn
  UNIQUE (ssn);
-- Table used in foreign key example
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   DATETIME2    NOT NULL ,
  qty        INT NOT NULL ,
  CONSTRAINT PK_Orders
    PRIMARY KEY (orderid)
);

-- Foreign keys
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 dbo.Employees(empid);

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

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

 

附第一章配套代码：　　

---------------------------------------------------------------------
-- Microsoft SQL Server T-SQL Fundamentals
-- Chapter 1 - Background to T-SQL Querying and Programming
--2008 Itzik Ben-Gan 
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Creating Tables
---------------------------------------------------------------------

-- 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
);

---------------------------------------------------------------------
-- Data Integrity
---------------------------------------------------------------------

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

-- 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);

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

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