（三）数据库笔记：SQL

本笔记是我在学习《数据库系统概论（第5版）》所记，提取课本重点，加上我自己的理解

---

---

1.SQL概述(P₇₅)

1.1发展

标准	大致页数	发布日期
SQL/86		1986年10月
SQL 89 (FIPS 127-1)	120页	1989年
SQL/92	622页	1992年
SQL 99 (SQL 3)	1700页	1999年
SQL 2003	3600页	2003年
SQL 2008	3777页	2006年
SQL 2011		2010年

目前，没有一个数据库系统能支持SQL标准的所有概念和特性

1.2基本概念和特点

SQL是一个综合的、功能极强同时又简洁易学的语言。

SQL同样支持三级模式结构，其中，外模式包括若干视图和部分基本表，模式包括若干基本表，内模式包含若干存储文件

SQL相关动词：

SQL功能	动词
数据查询	SELECT（选择）
数据定义（对于表）	CREATE（创建）,DROP（删除）, ALTER（修改）
数据操纵（对于表中数据）	INSERT（插入）, UPDATE（更新）, DELETE（删除）
数据控制	GRANT, REVOKE

特点包括：

1. 综合统一

   SQL集数据定义语言、数据操纵语言、数据控制语言的功能于一体
   非关系模型(层次模型、网状模型)的数据语言一般都分为:

   1. 模式数据定义语言( Schema Data Definition Language，模式DDL)
   2. 外模式数据定义语言( Subschema Data Definition Language，外模式DDL或子模式DDL)。
   3. 数据存储有关的描述语言( Data Storage Description Language，DsDL)。
   4. 数据操纵语言( Data Manipulation Language，DML)

2. 高度非过程化

   使用SQL语言时，只需要提出“做什么”，不需要了解内部操作即可得到结果，自动完成

3. 面向集合的操作方式

4. 以同一种语法结构提供多种使用方式

5. 语言简洁，易学易用

---

2.数据的定义(P₈₀)

共四个部分如下：

重点是表的相关操作

2.1模式

2.1.1创建

创建语句：

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

如果没有指定<模式名>，那么<模式名>隐含为<用户名>

定义一个模式，相当于C++中的命名空间，即可以在后面进一步创建基本表等

语句：
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];
例：

为用户 ZHANG创建一个模式"TEST"，并且在其中定义一个表"TAB1"。
CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1(COLI SMALLINT,
COL2 INT,
COL3 CHAR(20),
COL4 NUMERIC(10, 3),
COL5 DECIMAL(5, 2)

​ );

2.1.2删除

删除语句：

DROP SCHEMA <模式名> <CASCADE| RESTRICT> ;

两个删除模式：

CASCADE：级联，表示删除模式时，将旗下的数据库对象（表等）一同删除

RESTRICT：限制，表示删除时，若旗下有数据库对象（表等），则拒绝删除

---

2.2基本表(P₈₂)

2.2.1创建

创建语句：

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

​ ····
​ [ ,<表级完整性约束条件>]);

• 逗号是用来区分不同的列，最后一个后面不用逗号
• 结束后加分号;

列级完整性约束条件（5个）：

名称	关键字	说明
主键	PRIMARY KEY	直接跟在后面即可
唯一	UNIQUE	直接跟在后面即可，表示取唯一值
检查	CHECK
默认	DEFAULT	跟在后面，后面在加上默认的值
非空/空值	NOT NULL / NULL	表名不能取空值/只能取空值

例如：

建立一个“学生”表 Student：
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2) NOT NULL,
Sage SMALLINT DEFAULT 18,
Sdept CHAR(20)
);

表级完整性约束条件（4个）：

名称	关键字	说明
主键	PRIMARY KEY	直接跟在后面即可
唯一	UNIQUE	直接跟在后面即可，表示取唯一值
检查	CHECK
外键	FOREIGN KEY	见下例

建立学生选课表SC
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4).
Grade Smallint
PRIMARY KEY(Sno,Cno), //主码由两个属性构成,必须作为表级完整性进行定义
FOREIGN KEY (Sno) REFERENCES Student(Sno),
//伸表级完整性约束条件，Sno是外码，被参照表是 Student
FOREIGN KEY(Cno) REFERENCES Course(Cno)
//表级完整性约束条件，Cno是外码，被参照表是 Course
);

数据类型：

数据类型	含义
CHAR(n), CHARACTER(n)	长度为n的定长字符串
VARChAR(n), CHARACTERVARYING(n)	最大长度为n的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT, INTEGER	长整数(4字节)
SMALLINT	短整数(2字节)
BIGINT	大整数(8字节)
NUMERIC(p,d)	定点数，由p位数字(不包括符号、小数点)组成，小数点后面有d位数字
DECIMAL(p,d), DEC(p,d)	同NUMERIC
DOUBLE PRECISION	取决于机器精度的双精度浮点数
REAL	取决于机器精度的单精度浮点数
FLOAT(n)	可选精度的浮点数，精度至少为n位数字
BOOLEAN	逻辑布尔量
DATE	日期，包含年、月、日,格式为 YYYY-MM-DD
TIME	时间，包含一日的时、分、秒,格式为 HH: MM: SS
TIMESTAMP	时间戳类型
INTERVAL	时间间隔类型

---

2.2.2修改

修改语句：

ALTER TABLE<表名>
ADD [ COLUMN]<新列名><数据类型>[完整性约束]]
ADD <表级完整性约束>]
DROP [ COLUMN] <列名> [ CASCADE|RESTRICT]]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT| CASCADE]]
[ ALTER COLUMN<列名><数据类型>];

• ADD添加新列、新的列级完整性约束条件、新的表级完整性约束条件
• DROP COLUMN：删除表中的列，也有两种删除模式同上
• DROP CONSTRAINT：删除指定完整性约束性条件，也有两种删除模式同上
• ALTER COLUMN：修改原有列定义，包括列名、数据类型

---

2.2.3删除

删除语句：

DROP TABLE<表名> [ CASCADE|RESTRICT]];

同样有两种删除模式，CASCADE：级联，RESTRICT：限制

---

2.3视图(略)

2.4索引的创建、删除、修改(P₈₇)

当表的数据量比较大时，查询操作会变得比较耗时，建立索引能够加快查询速度。

数据库索引包括很多种：顺序文件上的索引、B+树索引、散列(hash)索引、位图索引等。

• 顺序文件上的索引是针对按指定属性值升序或降序存储的关系，在该属性上建立一个顺序索引文件，索引文件由属性值和相应的元组指针组成。

• B+树索引是将索引属性组织成B+树形式，B+树的叶结点为属性值和相应的元组指针。B+树索引具有动态平衡的优点。

• 散列索引是建立若干个桶，将索引属性按照其散列函数值映射到相应桶中，桶中存放索引属性值和相应的元组指针。散列索引具有查找速度快的特点。

• 位图索引是用位向量记录索引属性中可能出现的值，每个位向量对应一个可能值。

索引虽然能够加速数据库查询，但需要占用一定的存储空间，所以要适当建立索引。
不同的关系数据库管理系统支持的索引类型不尽相同

2.4.1创建

创建语句：
CREATE [UNIQUE] [CLUSTER] INDEX<索引名>
ON <表名> (<列名> [<次序>] [ ,<列名> [<次序>] ]…) ;

• <表名>是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。
• 用<次序>指定索引值的排列次序，可选ASC(升序)或DESC(降序)，默认值为ASC
• UNIQUE：表明此索引的每一个索引值只对应唯一的数据记录
• CLUSTER：表示要建立的索引是聚簇索引。

例如：

CREATE UNIQUE INDEX Stusno ON Student(Sno);

给表Student建立索引Stusno，且按列Sno的升序排列

2.4.2修改

修改语句：

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

进行改名操作

2.4.3删除

删除语句：

DROP INDEX <索引名>;

---

3.数据查询(见笔记（四）)

---

4.数据的更新

4.1插入数据

插入格式为：
INSERT
INTO <表名> [(<属性列1>[,<属性列2>]…)]
VALUES (<常量1> [,<常量2>]…);

values 语句是对表中属性列进行赋值，不能赋值需要跳过的，使用NULL

<表名>后面可以给出属性列，也可以不给出，则指全部的值

比如：

插入一条选课记录(201215128,1)：
INSERT
INTO SC(Sno, Cno)
VALUES (‘201215128’,‘1’);

插入子查询结果

语句格式为：
INSERT
INTO<表名>[(<属性列1>[,<属性列2>…])
子查询;

同前面，子查询得出的属性个数必须与属性列个数相同，否则报错

附：表的复制

1. SELECT

   SELECT * INTO FROM [WHERE <条件>]

   • 目标表不存在时，创建一个
   • 这样复制是包括模式等完全复制过去

2. INSERT

   INSERT INTO SELECT * FROM

   • 只是复制数据
   • 只能对已存在表(existed-table)操作，否则报错

---

4.2修改数据

修改操作又称为更新操作,其语句格式为
UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>]…
[WHERE <条件>];

• 注意SET语句中可以使用表达式
• WHERE省略后表示要修改表中全部元组
• WHERE语句可以使用子查询

---

4.3删除数据

删除格式为
DELETE
FROM <表名>
[WHERE<条件>]

• DELETE只删除数据，不同于DROP
• WHERE省略后表示要删除表中全部元组
• WHERE语句可以使用子查询