跳转到内容

数据库设计/SQL 结构化查询语言

Add links
来自维基教科书，开放世界中的开放书籍

结构化查询语言 (SQL) 是一种数据库语言，用于管理关系数据库管理系统中存储的数据。SQL 最初由 IBM 于 1970 年代初期开发（Date 1986）。最初的版本称为SEQUEL（结构化英语查询语言），旨在操作和检索存储在 IBM 的准关系数据库管理系统 System R 中的数据。然后在 1970 年代后期，Relational Software Inc.（现为 Oracle Corporation）推出了第一个商业化的 SQL 实现版本 Oracle V2，适用于 VAX 计算机。

许多目前可用的关系型 DBMS，例如 Oracle Database、Microsoft SQL Server（如图 15.1 所示）、MySQL、IBM DB2、IBM Informix 和 Microsoft Access，都使用 SQL。

图 15.1. Microsoft SQL Server 的示例，由 A. Watt 提供。

在 DBMS 中，SQL 数据库语言用于

  • 创建数据库和表结构
  • 执行基本数据管理任务（添加、删除和修改）
  • 执行复杂的查询，将原始数据转换为有用的信息

本章将重点介绍使用 SQL 创建数据库和表结构，主要使用 SQL 作为数据定义语言（DDL）。在第 16 章中，我们将使用 SQL 作为数据操纵语言（DML）在数据库表中插入、删除、选择和更新数据。

创建数据库

[编辑 | 编辑源代码]

主要的 SQL DDL 语句是 CREATE DATABASE 和 CREATE/DROP/ALTER TABLE。SQL 语句 CREATE 用于创建数据库和表结构。

示例：CREATE DATABASE SW

SQL 语句 CREATE  DATABASE SW 会创建一个名为 SW 的新数据库。创建数据库后，下一步是创建数据库表。

CREATE TABLE 命令的通用格式是

CREATE TABLE <表名>

(

列名，数据类型，可选列约束，

列名，数据类型，可选列约束，

可选表约束

);

表名是数据库表的名称，例如 Employee。CREATE TABLE 中的每个字段都有三个部分（见上文）

  1. 列名
  2. 数据类型
  3. 可选列约束

列名

[编辑 | 编辑源代码]

列名在表中必须唯一。一些列名的例子是 FirstName 和 LastName。

数据类型

[编辑 | 编辑源代码]

数据类型，如下所述，必须是系统数据类型或用户定义数据类型。许多数据类型都有大小，例如 CHAR(35) 或 Numeric(8,2)。

Bit – 整数数据，值为 1 或 0

Int – 整数（整数）数据，从 -2^31 (-2,147,483,648) 到 2^31 – 1  (2,147,483,647)

Smallint – 整数数据，从 2^15 (-32,768) 到 2^15 – 1 (32,767)

Tinyint – 整数数据，从 0 到 255

Decimal – 固定精度和比例的数字数据，从 -10^38 -1 到 10^38

Numeric – decimal 的同义词

Timestamp – 数据库范围内的唯一数字

Uniqueidentifier – 全局唯一标识符 (GUID)

Money – 货币数据值，从 -2^63 (-922,337,203,685,477.5808) 到 2^63 – 1 (+922,337,203,685,477.5807)，精度为货币单位的万分之一

Smallmoney – 货币数据值，从 -214,748.3648 到 +214,748.3647，精度为货币单位的万分之一

Float – 浮点精度数字数据，从 -1.79E + 308 到 1.79E + 308

Real – 浮点精度数字数据，从 -3.40E + 38 到 3.40E + 38

Datetime – 日期和时间数据，从 1753 年 1 月 1 日到 9999 年 12 月 31 日，精度为三分之一秒，或 3.33 毫秒

Smalldatetime – 日期和时间数据，从 1900 年 1 月 1 日到 2079 年 6 月 6 日，精度为一分钟

Char – 固定长度的非 Unicode 字符数据，最大长度为 8,000 个字符

Varchar – 可变长度的非 Unicode 数据，最大长度为 8,000 个字符

Text – 可变长度的非 Unicode 数据，最大长度为 2^31 – 1 (2,147,483,647) 个字符

Binary – 固定长度的二进制数据，最大长度为 8,000 个字节

Varbinary – 可变长度的二进制数据，最大长度为 8,000 个字节

Image – 可变长度的二进制数据，最大长度为 2^31 – 1 (2,147,483,647) 个字节

可选列约束

[编辑 | 编辑源代码]

可选列约束是 NULL、NOT NULL、UNIQUE、PRIMARY KEY 和 DEFAULT，用于初始化新记录的值。列约束 NULL 表示允许空值，这意味着可以在没有此列的值的情况下创建行。列约束 NOT NULL 表示创建新行时必须提供值。

为了说明，我们将使用 SQL 语句 CREATE TABLE EMPLOYEES 创建具有 16 个属性或字段的 employees 表。

USE SW

CREATE TABLE EMPLOYEES

(

EmployeeNo                      CHAR(10)             NOT NULL           UNIQUE,

DepartmentName            CHAR(30)             NOT NULL           DEFAULT “Human Resources”,

FirstName                         CHAR(25)             NOT NULL,

LastName                            CHAR(25)             NOT NULL,

Category                              CHAR(20)             NOT NULL,

HourlyRate                       CURRENCY          NOT NULL,

TimeCard                             LOGICAL              NOT NULL,

HourlySalaried                   CHAR(1)               NOT NULL,

EmpType                             CHAR(1)               NOT NULL,

Terminated                       LOGICAL              NOT NULL,

ExemptCode                     CHAR(2)               NOT NULL,

Supervisor                           LOGICAL              NOT NULL,

SupervisorName                CHAR(50)             NOT NULL,

BirthDate                             DATE                     NOT NULL,

CollegeDegree                     CHAR(5)               NOT NULL,

CONSTRAINT                     Employee_PK    PRIMARY KEY(EmployeeNo

);

第一个字段是 EmployeeNo，字段类型为 CHAR。对于此字段，字段长度为 10 个字符，用户不能将此字段留空 (NOT NULL)。

类似地，第二个字段是 DepartmentName，字段类型为 CHAR，长度为 30。在定义完所有表列后，使用由 CONSTRAINT 标识的表约束来创建主键

CONSTRAINT     EmployeePK      PRIMARY KEY(EmployeeNo)

我们将在本章稍后进一步讨论约束属性。

同样，我们可以使用 CREATE TABLE SQL DDL 命令创建部门表、项目表和分配表，如下例所示。

USE SW

CREATE TABLE DEPARTMENT

(

DepartmentName Char(35) NOT NULL,

BudgetCode Char(30) NOT NULL,

OfficeNumber Char(15) NOT NULL,

Phone Char(15) NOT NULL,

CONSTRAINT DEPARTMENT_PK PRIMARY KEY(DepartmentName)

);

在这个例子中，一个项目表被创建，包含七个字段：ProjectID、ProjectName、Department、MaxHours、StartDate 和 EndDate。

USE SW

CREATE TABLE PROJECT

(

ProjectID Int NOT NULL IDENTITY (1000,100),

ProjectName Char(50) NOT NULL,

Department Char(35) NOT NULL,

MaxHours Numeric(8,2) NOT NULL DEFAULT 100,

StartDate DateTime NULL,

EndDate DateTime NULL,

CONSTRAINT ASSIGNMENT_PK PRIMARY KEY(ProjectID)

);

在最后一个例子中，一个分配表被创建，包含三个字段：ProjectID、EmployeeNumber 和 HoursWorked。分配表用于记录谁（EmployeeNumber）在哪个项目（ProjectID）上工作了多长时间（HoursWorked）。

USE SW

CREATE TABLE ASSIGNMENT

(

ProjectID Int NOT NULL,

EmployeeNumber Int NOT NULL,

HoursWorked Numeric(6,2) NULL,

);

表约束

[编辑 | 编辑源代码]

表约束由 CONSTRAINT 关键字标识，可用于实现下面描述的各种约束。

IDENTITY 约束

[编辑 | 编辑源代码]

我们可以使用可选的列约束 IDENTITY 为该列提供唯一递增的值。标识列通常与 PRIMARY KEY 约束一起使用，作为表的唯一行标识符。IDENTITY 属性可以分配给具有 tinyint、smallint、int、decimal 或 numeric 数据类型的列。此约束

  • 生成序列号
  • 不强制实体完整性
  • 只有一列可以具有 IDENTITY 属性
  • 必须定义为整数、数字或十进制数据类型
  • 不能更新具有 IDENTITY 属性的列
  • 不能包含 NULL 值
  • 不能将默认值和默认约束绑定到列

对于 IDENTITY[(seed, increment)]

  • Seed – 标识列的初始值
  • Increment – 要添加到最后一个增量列的值

我们将使用另一个数据库示例，通过在 HOTEL 数据库中创建 tblHotel 表，进一步说明 SQL DDL 语句。

CREATE TABLE tblHotel

(

HotelNo Int IDENTITY (1,1),

Name Char(50) NOT NULL,

Address Char(50) NULL,

City Char(25) NULL,

)

UNIQUE 约束

UNIQUE 约束防止将重复值输入列中。

  • PK 和 UNIQUE 约束都用于强制实体完整性。
  • 可以在一个表上定义多个 UNIQUE 约束。
  • 当将 UNIQUE 约束添加到现有表时，将始终验证现有数据。
  • UNIQUE 约束可以放在接受空值的列上。只有一行可以是 NULL。
  • UNIQUE 约束会在选定的列上自动创建一个唯一索引。

这是 UNIQUE 约束的通用语法

[CONSTRAINT constraint_name]

UNIQUE [CLUSTERED | NONCLUSTERED]

(col_name [, col_name2 […, col_name16]])

[ON segment_name]

这是一个使用 UNIQUE 约束的示例。

CREATE TABLE EMPLOYEES

(

EmployeeNo                      CHAR(10)             NOT NULL           UNIQUE,

)

FOREIGN KEY 约束

[编辑 | 编辑源代码]

FOREIGN KEY (FK) 约束定义一个列或列的组合，其值与另一个表的 PRIMARY KEY (PK) 匹配。

  • 当关联表中的 PK 值更新/更改时，FK 中的值会自动更新。
  • FK 约束必须引用另一个表的 PK 或 UNIQUE 约束。
  • FK 的列数必须与 PK 或 UNIQUE 约束的列数相同。
  • 如果使用 WITH NOCHECK 选项，则 FK 约束不会验证表中的现有数据。
  • 不会在参与 FK 约束的列上创建索引。

这是 FOREIGN KEY 约束的通用语法

[CONSTRAINT constraint_name]

[FOREIGN KEY (col_name [, col_name2 […, col_name16]])]

REFERENCES [owner.]ref_table [(ref_col [, ref_col2 […, ref_col16]])]

在这个例子中，tblRoom 表中的 HotelNo 字段是 tblHotel 表中 HotelNo 字段的 FK，如前所示。

USE HOTEL

GO

CREATE TABLE tblRoom

(

HotelNo Int NOT NULL ,

RoomNo Int NOT NULL,

Type Char(50) NULL,

Price Money NULL,

PRIMARY KEY (HotelNo, RoomNo),

FOREIGN KEY (HotelNo) REFERENCES tblHotel

)

CHECK 约束

[编辑 | 编辑源代码]

CHECK 约束限制可以输入表的的值。

  • 它可以包含类似于 WHERE 子句的搜索条件。
  • 它可以引用同一表中的列。
  • CHECK 约束的数据验证规则必须评估为布尔表达式。
  • 它可以为绑定了规则的列定义。

这是 CHECK 约束的通用语法

[CONSTRAINT constraint_name]

CHECK [NOT FOR REPLICATION] (expression)

在这个例子中，Type 字段被限制为只能包含 'Single'、'Double'、'Suite' 或 'Executive' 类型。

USE HOTEL

GO

CREATE TABLE tblRoom

(

HotelNo Int NOT NULL,

RoomNo Int NOT NULL,

Type Char(50) NULL,

Price Money NULL,

PRIMARY KEY (HotelNo, RoomNo),

FOREIGN KEY (HotelNo) REFERENCES tblHotel

CONSTRAINT Valid_Type

CHECK (Type IN ('Single', 'Double', 'Suite', 'Executive'))

)

在第二个例子中，员工入职日期应该在 2004 年 1 月 1 日之前，或者工资上限为 300000 美元。

GO

CREATE TABLE SALESREPS

(

Empl_num Int Not Null

CHECK (Empl_num BETWEEN 101 and 199),

Name Char (15),

Age Int CHECK (Age >= 21),

Quota Money CHECK (Quota >= 0.0),

HireDate DateTime,

CONSTRAINT QuotaCap CHECK ((HireDate < "01-01-2004") OR (Quota <=300000))

)

DEFAULT 约束

[编辑 | 编辑源代码]

DEFAULT 约束用于提供一个值，如果用户没有提供值，则会自动添加到列中。

  • 一列只能有一个 DEFAULT。
  • DEFAULT 约束不能用于具有 timestamp 数据类型或标识属性的列。
  • DEFAULT 约束在创建时会自动绑定到列。

DEFAULT 约束的通用语法是

[CONSTRAINT constraint_name]

DEFAULT {constant_expression | niladic-function | NULL}

[FOR col_name]

此示例将 city 字段的默认值设置为 'Vancouver'。

USE HOTEL

ALTER TABLE tblHotel

Add CONSTRAINT df_city DEFAULT 'Vancouver' FOR City

用户定义类型

[编辑 | 编辑源代码]

用户定义类型始终基于系统提供的數據類型。它們可以強制執行數據完整性，並且允許空值。

要在 SQL Server 中创建用户定义的数据类型，请在您的数据库中选择“可编程性”下的类型。接下来，右键单击并选择“新建”->“用户定义数据类型”，或执行 sp_addtype 系统存储过程。然后，键入

sp_addtype ssn, 'varchar(11)', 'NOT NULL'

这将添加一个名为 SIN 的新用户定义数据类型，其中包含九个字符。

在这个例子中，EmployeeSIN 字段使用用户定义的数据类型 SIN。

CREATE TABLE SINTable

(

EmployeeID INT Primary Key,

员工SIN    SIN，

约束 CheckSIN

检查 (EmployeeSIN LIKE

‘ [0-9][0-9][0-9] – [0-9][0-9] [0-9] – [0-9][0-9][0-9] ‘)

)

更改表

[编辑 | 编辑源代码]

可以使用 ALTER TABLE 语句添加和删除约束。

  • ALTER TABLE 允许删除列。
  • 添加约束时，将验证所有现有数据是否存在违规情况。

在此示例中，我们使用 ALTER TABLE 语句将 IDENTITY 属性应用于 ColumnName 字段。

USE HOTEL

GO

ALTER TABLE  tblHotel

添加约束 unqName 唯一 (名称)

使用 ALTER TABLE 语句添加具有 IDENTITY 属性的列，例如 ALTER TABLE 表名。

添加

ColumnName         int    IDENTITY(种子，增量)

删除表

[编辑 | 编辑源代码]

DROP TABLE 将从数据库中删除一个表。请确保选择了正确的数据库。

DROP TABLE tblHotel

执行上述 SQL DROP TABLE 语句将从数据库中删除 tblHotel 表。

关键词

[编辑 | 编辑源代码]

DDL：数据定义语言的缩写

DML：数据操作语言的缩写

SEQUEL：结构化英语查询语言的首字母缩写词；旨在操作和检索存储在 IBM 的准关系型数据库管理系统 System R 中的数据

结构化查询语言 (SQL)：一种为管理关系型数据库管理系统中保存的数据而设计的数据库语言

练习

[编辑 | 编辑源代码]
  1. 使用第 9 章练习的信息，使用 Transact SQL 实现该模式（显示每个表的 SQL 语句）。也实现约束。
  2. 在 SQL Server 中创建此处所示的表，并显示你使用的语句。表：员工
属性（字段）名称 数据声明
EMP_NUM CHAR(3)
EMP_LNAME VARCHAR(15)
EMP_FNAME VARCHAR(15)
EMP_INITIAL CHAR(1)
EMP_HIREDATE 日期
JOB_CODE CHAR(3)

在创建了问题 2 中的表结构后，编写 SQL 代码以输入图 15.1 中所示表的行。

图 15.2。包含问题 4-10 数据的员工表，作者：A. Watt。

使用图 15.2 回答以下问题

  1. 编写 SQL 代码将人员编号为 107 的人的工作代码更改为 501。完成任务后，检查结果，然后将工作代码重置为其原始值。
  2. 假设员工表中显示的数据已输入，请编写 SQL 代码以列出工作代码为 502 的所有属性。
  3. 编写 SQL 代码以删除名为 William Smithfield 的人的行，该人于 2004 年 6 月 22 日被雇用，其工作代码分类为 500。（提示：使用逻辑运算符包含此问题中给出的所有信息。）
  4. 将属性 EMP_PCT 和 PROJ_NUM 添加到员工表中。EMP_PCT 是要支付给每个员工的奖金百分比。
  5. 使用单个命令，编写 SQL 代码，该代码将为所有工作分类 (JOB_CODE) 为 500 的员工输入项目编号 (PROJ_NUM) = 18。
  6. 使用单个命令，编写 SQL 代码，该代码将为所有工作分类 (JOB_CODE) 为 502 或更高的员工输入项目编号 (PROJ_NUM) = 25。
  7. 编写 SQL 代码，该代码将为所有在 1994 年 1 月 1 日之前被雇用且工作代码至少为 501 的员工将 PROJ_NUM 更改为 14。（你可以假设该表将在此问题之前恢复到其原始状态。）

另请参见 附录 C：带解决方案的 SQL 实验室

参考文献

[编辑 | 编辑源代码]
  • Date, C.J. 关系型数据库精选著作。阅读：马萨诸塞州：Addison-Wesley 出版公司， 1986 年，第 269-311 页。
  • BCOpenCampus：数据库设计
检索自 "https://wikibooks.cn/w/index.php?title=Database_Design/SQL_Structured_Query_Language&oldid=3645571"
华夏公益教科书