SQL 语言：基本概述和数据定义

文章目录

- 1. 数据库语言
- 2. SQL 概述
- - 2.1 SQL 的特点
  - 2.2 SQL 语言支持三级模式结构
  - 2.3 SQL 的基本组成
- 3. 数据定义
- - 3.1 数据类型
  - 3.2 创建表
  - 3.3 修改和删除表
  - 3.4 创建和删除索引
  - 3.5 创建和删除视图

1. 数据库语言

数据结构化语言 (Structured Query Language，SQL)，在关系数据库中最普遍使用的语言，是一种通用的、功能强大的关系数据库标准语言。

主要功能：数据查询 (Data Query)、数据操纵 (Data Manipulation)、数据定义 (Data Definition)、数据控制 (Data Control)

基本表：实际存储在数据库中的表；

视图：由若干个基本表或其它视图导出的表，是一个虚表；

SQL 用户：可以是应用程序，也可以是终端用户；

任何一个数据库系统都应向用户提供一种数据库语言，其中包括数据定义语言和数据操纵语言；SQL 语言是集数据定义和数据操纵为一体的典型数据库语言。数据库语言与数据模型密切相关，基于不同的数据模型，数据库语言也不同。

数据定义语言 (Data Definition Language，DDL) ：用来定义数据库模式，包括数据库模式定义、数据存储结构和存取方法定义，数据库模式的修改和删除功能。

数据操纵语言 (Data Manipulation Language，DML) ：用来表示用户对数据库的操作请求，通常数据操纵语言 DML 能进行的操纵有插入、删除、修改、查询数据库中的信息，简称增删改查。

SQL 可以作为独立语言在终端以交互的方式使用，也可以作为程序设计的子语言使用，即嵌入到高级语言中使用，这种方式下使用的 SQL 称为嵌入式 SQL ，嵌入式 SQL 的高级语言称为宿主语言。

在 DBMS 中，对宿主型数据库语言 SQL 采用两种方法处理：第一种方法是采用预编译；第二种方法是修改和扩充主语言，使之能处理 SQL 语句。

2. SQL 概述

2.1 SQL 的特点

综合统一。非关系模型的数据语言分为模式定义语言和数据操纵语言，其缺点是当要修改模式时，必须停止现有数据库的运行，转储数据，修改模式编译后再重装数据库。SQL 集数据定义、数据操纵和数据控制功能于一体，语言风格统一，可独立完成数据库生命周期的所有活动。
高度非过程化。当进行数据操作时，只要指出“做什么”，无须指出“怎么做”，存储路径对用户来说是透明的，提高了数据的独立性。
面向集合的操纵方式。非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。而 SQL 语言采用面向集合的操作方式，其操作对象、查找结果可以是元组的集合。
两种使用方式。第一种方式，用户可以在终端输入 SQL 命令，对数据库进行运行操作，称为自含式语言；第二种方式，将 SQL 语言嵌入到高级程序中 (Python等)，称为嵌入式语言。
语言简洁、易学易用。
- 数据查询：SELECT ，该动词是 SQL 中用得最多的动词。
- 数据定义：CREATE、DROP、ALTER，用于创建新表、修改表和删除表。
- 数据操作：INSERT、UODATE、DELETE，用于数据的插入、修改和删除。
- 数据控制：GRANT、REVORK，用于数据库对象访问的权限授予和回收。

2.2 SQL 语言支持三级模式结构

SQL 语言支持关系数据库的三级模式结构 (外模式、模式、内模式)，其中，视图对应外模式、基本表对应模式、存储文件对应内模式。

2.3 SQL 的基本组成

数据定义语言。SQL DDL 提供定义关系模式和视图、删除关系和视图、修改关系模式的命令。
交互式数据操作语言。SQL DML 提供查询、插入、删除和修改的命令。
事务控制 (Transaction Control)。SQL 提供定义事务开始和结束的命令。
嵌入式 SQL 和动态 SQL ( Embedded SQL and Dynamic SQL )。用于嵌入到某种通用的高级语言 (C、C++、Java、Python、PL/I、COBOL 和 VB 等) 中混合编程。其中，SQL 负责操作数据库，高级语言负责控制程序流程。
完整性 ( Integrity )。SQL DDL 包括定义数据库中的数据必须满足的完整性约束条件的命令，对于破坏完整性约束条件的更新将被禁止。
权限管理 ( Authorization )。SQL DDL 中包括说明关系和视图的访问权限。

3. 数据定义

3.1 数据类型

SQL 支持的内部基本类型如下：

3.2 创建表

语句格式：

CREATE TABLE <表名>
(
	<列名> <数据类型> [列级完整性约束条件]
	[,<列名> <数据类型> [列级完整性约束条件]
	...
	[,<表级完整性约束条件>]
);

列级完整性约束有 NULL (空) 和 UNIQUE (取值唯一) ，如 NOT NULL UNIQUE 表示取值唯一，不能取空值。

示例1. 建立一个供应商、零件数据库。其中关系供应商 S(Sno,Sname,Status,City) 属性名分别表示供应商代码、供应商名、供应商状态和供应商所在城市；关系零件 P(Pno,Pname,Color,Weight,City) 属性名分别表示零件号、零件名、颜色、重量及产地。该数据库要满足如下要求：

（1）供应商代码不能为空，且值是唯一的，供应商的名也是唯一的。

（2）零件号不能为空，且值是唯一的；零件名不能为空。

（3）一个供应商可以供应多个零件，而一个零件可以由多个供应商供应。

分析：（1）表示 Sno 非空唯一约束，Sname 唯一约束；（2）表示 Pno 非空唯一约束，Pname 非空约束；（3）表示供应商和零件之间是多对多的联系，在关系数据库中，多对多联系必须生成一个关系模式，而该模式的码是该联系两端实体的码加上联系的属性构成的，若该联系名为SP，那么关系模式为SP(Sno, Pno,Qty)，另外供应商和零件分别要建立一个关系模式。

CREATE TABLE S
(
	Sno CHAR(5) NOT NULL UNIQUE
	,Sname CHAR(30) UNIQUE
	,Status CHAR(8)
	,City CHAR(20)
	,PRIMARY KEY(Sno)
);
CREATE TABLE P
(
	Pno CHAR(6)
	,Pname CHAR(30) NOT NULL 
	,Color CHAR(8)
	,Weight NUMERIC(6,2)
	,City CHAR(20)
	,PRIMARY KEY(Pno)
);
CREATE TABLE SP
(
	Sno CHAR(5) NOT NULL UNIQUE
	,Pno CHAR(6) NOT NULL UNIQUE
	,Qty NUMERIC(9)
	,PRIMARY KEY(Sno,Pno)
	,FOREIGN KEY(Sno) REFERENCES S(Sno)
	,FOREIGN KEY(Pno) REFERENCES P(Pno)
);

PRIMARY KEY(Pno) 已经定义了 Pno 为主码，所以 Pno CHAR(6) NOT NULL UNIQUE 语句中的 NOT NULL UNIQUE 可以省略。

3.3 修改和删除表

修改表语法格式：

ALTER TABLE <表名>
	[ADD COLUMN <新列名> <数据类型> [完整性约束条件] 
	[DROP COLUMN <列名>]
	[DROP [CONSTRAINT]<完整性约束名>]
	[MODIFY <列名> <数据类型>];

删除表语法格式：

DROP TABLE <表名>;

示例2. 向“ 供应商” 表S 增加Zap “ 邮政编码”；将 Status 字段改为整型；删除供应商表。

# 向“ 供应商” 表S 增加Zap “ 邮政编码”
ALTER TABLE S ADD Zap CHAR(6);
# 将 Status 字段改为整型
ALTER TABLE S MODIFY Status INT;
# 删除供应商表
DROP TABLE S;

3.4 创建和删除索引

数据库中的索引与书籍中的目录页面类似，利用目录页面可以快速查找信息，在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。数据库中的索引是某个表中一列或者若干列的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引作用如下：

通过创建唯一索引，可以保证数据记录的唯一性。
可以大大加快数据检索速度。
可以加快表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。
在使用 ORDER BY 和 GROUP BY 子句中进行检索数据时，可以显著减少查询中分组和排序的时间。
使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。

索引分为聚集索引和非聚集索引。聚集索引时指索引项的顺序与表中记录的物理顺序一致的索引。

创建索引语法格式：

CREATE [UNIQUE] [CLUSTER|NONCLUSTER] 
  INDEX <索引名>
	ON <表名>(<列名>[<次序>][,<列名>[<次序>]);

参数说明如下：

次序：可选择 ASC (升序) 或 DESC (降序) ，默认值为 ASC。
UNIQUE：表明此索引的每一个索引值只对应唯一的数据记录。
CLUSTER：表明要建立的索引是聚集索引，意为索引项的顺序是与表中记录的物理顺序一致的索引组织，默认。
NONCLUSTER：表示建立的索引是非聚集索引。

删除索引语法格式：

DROP INDEX <索引名> ON <表名>;

示例3. 假设供应销售数据库中有供应商S、零件P、工程项目J、供销情况SPJ 关系，希望建立4个索引。其中，供应商S中Sno按升序建立索引；零件P中Pno按升序建立索引；工程项目J中Jno按升序建立索引；供销情况SPJ中Sno按升序，Pno按降序，Jno按升序建立索引。删除其中一个索引。

CREATE UNIQUE INDEX SNS-O ON S(Sno);
CREATE UNIQUE INDEX P-PNO NO P(Pno);
CREATE UNIQUE INDEX J-JNO ON J(Jno);
CREATE UNIQUE INDEX SPJ-NO ON SPJ(Sno ASC, Pno DESC, JNO ASC);
DROP INDEX SNS-O ON SNS-O;

3.5 创建和删除视图

视图是从一个或者多个基本表或视图中导出的表，其结构和数据是建立在对表的查询基础上的；视图不是真实存在的基本表，而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。

视图的优点和作用如下：

可以使视图集中数据、简化和定制不同用户对数据库的不同数据要求。
可以屏蔽数据的复杂性，用户不必了解数据库的结构，就可以方便地使用和管理数据，简化数据权限管理和重新组织数据以便输出到其他应用程序中。
可以使用户只关心他感兴趣的某些特定数据和所负责的特定任务，而那些不需要的或者无用的数据则不在视图中显示。
大大地简化了用户对数据的操作。
可以让不同的用户以不同的方式看到不同或者相同的数据集。
在某些情况下，由于表中数据量太大，因此在表的设计时常将表进行水平或者垂直分割，但表的结构的变化对应用程序产生不良的影响。
提供了一个简单而有效的安全机制。

创建视图语法格式：

CREATE VIEW 视图名(列表名)
AS SELECT 查询子句
[WITH CHECK OPTION];

视图的创建，必须遵循如下规定：

（1）子查询可以是任意复杂的 SELECT 语句，但通常不允许含有 ORDER BY 子句和 DISTINCT 短语。

（2）WITH CHECK OPTION 表示对 UPDATE，INSTER，DELETE 操作时保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

（3）组成视图的属性列名或者全部省略或者全部指定。如果省略属性列名，则隐含该视图由 SELECT 子查询目标列的主属性组成。

删除视图语法格式：

DROP VIEW 视图名;

示例4. 若学生关系模式为 Student(Sno,Sname,Sage,Sex,SD,Email,Tel)，建立“计算机系”（CS表示计算机系）学生的视图，并要求进行修改、插入操作时保证该视图只有计算机系的学生。

CREATE VIEW CS_STUDENT
AS SELECT Sno,Sname,Sage,Sex
FROM Student
WHERE SD = 'CS'
WITH CHECK OPTION;

DROP VIEW CS_STUDENT;