首页软考

# SQL 概述与数据库定义

# SQL 的基本组成

  1. 数据定义语言。SQL DDL 提供定义关系模式和视图、删除关系和视图、修改关系模式的命令。
  2. 交互式数据操纵语言。SQL DML 提供查询、插入、删除和修改的命令。
  3. 事务控制。SQL 提供定义事务开始和结束的命令。
  4. 嵌入式 SQL 和动态 SQL。用于嵌入到某种通用的高级语言中混合编程。其中，SQL 负责操纵数据库，高级语言负责控制程序流程。
  5. 完整性。SQL DDL 包括定义数据库中的数据必须满足的完整性约束条件的命令，对于破坏完整性约束条件的更新将被禁止。
  6. 权限管理。SQL DDL 中包括说明对关系和视图的访问权限。
  7. SQL 语言中完成核心功能的 9 个动词：
    1. 数据查询： Select
    2. 数据定义： Create , Drop , Alter
    3. 数据操纵： Insert , Update , Delete
    4. 数据控制： Grant , Revoke

# SQL 的数据类型

类型 说明
char(n) 固定长度字符串，表示 n 个字符的固定长度字符串
varchar(n) 可变长度字符串，表示最多可以有 n 个字符的字符串
int 整型，也可以用 integer
smallint 短整型
numeric(p, d) 定点数， p 为整数位， n 为小数位
real 浮点型
double precision 双精度浮点型
float(n) n 为浮点型
boolean 布尔型
date 日期型
time 时间型

# 表的创建、修改和删除

# 创建表

语句格式：

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

注：[] 表示可选，<> 表示必填。

# 实体完整性约束

  1. 在列后面加 PRIMARY KEY
  2. 在最后加 PRIMARY KEY(属性名1, 属性名2) ，主码为属性组（两个或以上属性的组合）只能用这种方法

# 参照完整性约束

  1. 在列后面加 References 表名（属性名）

  2. 在最后面加，有几个外码，就写几行。

    Foreign Key (属性名) References 表名 (属性名) [ON DELETE [CASCADE|SET NULL]

    ON DELETE CASCADE 表示删除被参照关系的元组时，同时删除参照关系中的元组；

    ON DELETE SET NULL 表示删除被参照关系的元组时，将参照关系的相应属性值置为空值。

# 属性值上的约束

  1. NOT NULL ：表示不允许取空值；
  2. UNIQUE ：表示取值唯一；
  3. NOT NULL UNIQUE ：表示取值唯一且不为空；
  4. CHECK ：限制列中值的取值范围。
CHECK (
	Sex = '男'
	OR Sex = '女'
),
CHECK (余额 >= 0),
CHECK (
	年龄 >= 18
	AND 年龄 <= 60
)

# 全局约束

# 基于元组的检查子句

这种约束是对单个关系的元组值加以约束。

入职日期小于等于离职日期，可以用

CREATE TABLE E (
	Eno CHAR(8) PRIMARY KEY,
	入职日期 DATE,
	离职日期 DATE,
	CHECK (入职日期 <= 离职日期)
);
# 基于断言的语法格式

CREATE ASSERTION <断言名> CHECK (<条件>)

教学数据库的模式 Students、SC、C\text{Students、SC、C} 中创建一个约束 ASSE_SC1 ：不允许男同学选修 “张勇” 老师的课。

CREATE ASSERTION ASSE_SC1 CHECK (
	NOT EXISTS (
		SELECT *
		FROM SC
		WHERE Cno IN (
				SELECT Cno
				FROM C
				WHERE TEACHER = '张勇'
			)
			AND Sno IN (
				SELECT Sno
				FROM Students
				WHERE SEX = 'M'
			)
	)
);

建立一个供应商、零件数据库。其中关系供应商 S(Sno, Sname, Status,City)\text{S(Sno, Sname, Status,City)}，关系零件 P(Pno,Pname,Color,Weight,City)\text{P(Pno,Pname,Color,Weight,City)}。该数据库要满足如下要求：

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

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

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

CREATE TABLE S (
    Sno CHAR(5) NOT NULL UNIQUE,
    Sname CHAR(30) UNIQUE,
    Status CHAR(8),
    City CHAR(20),
    PRIMARY KEY (Sno)
);
-- 此处隐含 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),
    Pno CHAR(6),
    Status CHAR(8),
    Qty NUMERIC(9),
    PRIMARY KEY (Sno, Pno),
    FOREIGN KEY (Sno) REFERENCES S (Sno),
    FOREIGN KEY (Pno) REFERENCES P (Pno)
);

# 修改表

语句格式：

ALTER TABLE <表名> [ADD <新列名><数据类型>[列级完整性约束条件]]
	[DROP <完整性约束名>]
	[Modify <列名><数据类型>]);
ALTER TABLE S
ADD Zap CHAR(6);
-- 在表 S 中新增一列 ZAP ， 该列的数据为空
ALTER TABLE S
MODIFY Status INT;
-- 将表 S 的 Status 属性的数据类型更改为 INT
ALTER TABLE S
ADD Constraint C_cno CHECK(......) -- 在表 S 中新增 CHECK 约束 ， 取名为 C_cno

# 删除表

语句格式：

DROP TABLE <表名>
DROP TABLE S;
-- 表删除后 ， 不再是数据库模式的一部分

# 索引的创建和删除

# 索引的概念

  • 数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找到所需信息，无须阅读整本书。在数据库中，索引使数据库无须对整个表进行扫描，就可以在其中找到所需数据。
  • 比如在字典中，我们按字母建立索引。在数据库中，索引是某个表中的一列或者若干列的值的集合，和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

# 索引的作用

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

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

# 建立索引

语句格式：

CREATE [UNIQUE] [CLUSTER] INDEX < 索引名 > ON < 表名 >(
	< 列名 > [<次序>] [,<列名>[<次序>]]…);
  • 次序： ASC （升序）或 DESC （降序），默认为升序。
  • UNIQUE ：表明此索引的每一个索引值只对应唯一的数据记录。
  • CLUSTER ：表明要建立的索引是聚簇索引，意为索引项的顺序是与表中记录的物理顺序一致的索引组织
CREATE UNIQUE INDEX S_Sno on S(Sno);
-- 在表 S 的 Sno 列上建立索引 S_Sno ， 默认为升序 
CREATE UNIQUE INDEX P_Pno on P(Pno);
-- / 在表 P 的 Pno 列上建立索引 P_Pno ， 默认为升序 
CREATE UNIQUE INDEX J_Jno on J(Jno);
-- / 在表 J 的 Jno 列上建立索引 J_Jno ， 默认为升序 
CREATE UNIQUE INDEX SPJ_NO on SPJ(Sno ASC, Pno DESC, Jno ASC);
-- 在表 SPJ 上建立索引 SPJ_NO ， 属性 Sno 按升序 ， Pno 按降序 ， Jno 按升序

# 删除索引

语句格式：

DROP INDEX <索引名>
DROP INDEX StudentIndex;
-- 删除索引 StudentIndex

# 视图的创建和删除

# 视图的作用

教材 P300

# 创建视图

语句格式：

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

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

  1. 子查询可以是任意复杂的 SELECT 语句，但通常不允许含有 ORDER BY 子句和 DISTINCT 短语。
  2. WITH CHECK OPTION 表示对 UPDATE, INSERT, DELETE 操作时保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
  3. 组成视图的属性列名或者全部省略或者全部指定。如果省略属性列名，则隐含该视图由 SELECT 子查询目标列的主属性组成。

例：学生关系模式 S(Sno,Sname,Sage,Sex,SD,Email,Tel)\text{S(Sno,Sname,Sage,Sex,SD,Email,Tel)}​，建立计算机系（CS 表示计算机系）学生的视图，并要求进行修改、插入操作时保证该视图只有计算机系的学生。

CREATE VIEW CS_STUDENT -- 创建视图 CS_STUDENT AS
SELECT Sno,
       Sname,
       Sage,
       Sex -- 选择学号 、 姓名 、 年龄 、 性别列
FROM Student -- 从学生表中查询
Where SD = 'CS' -- 选择系名等于 “CS” 的行 WITH CHECK OPTION;
    -- 以后对该视图进行修改 、 插入操作时 DBMS 会自动加上 SD = 'CS' 的条件 ， 保证视图中只有计算机系的学生

# 删除视图

语句格式：

DROP VIEW 视图名
DROP VIEW CS_STUDENT -- 删除视图 CS_STUDENT

某大型集团公司的数据库的部分关系模式如下：
员工表：EMP(Eno,Ename,Age,Sex,Title)\text{EMP(Eno,Ename,Age,Sex,Title)}，各属性分别表示员工工号、姓名、年龄、性别和职称级别，其中性别取值为 “男”“女”；
公司表：COMPANY(Cno,Cname,City)\text{COMPANY(Cno,Cname,City)}，各属性分别表示公司编号、名称和所在城市；
工作表：WORKS(Eno,Cno,Salary)\text{WORKS(Eno,Cno,Salary)}，各属性分别表示职工工号、工作的公司编号和工资。
有关关系模式的属性及相关说明如下：

  1. 允许一个员工在多家公司工作，使用身份证号作为工号值。

  2. 工资不能低于 1500 元。

【问题 1】

请将下面创建工作关系的 SQL 语 句的空缺部分补充完整，要求指定关系的主码、外码，以及工资不能低于 1500 元的约束。

【问题 2】
创建女员工信息的视图 FemaleEMP，属性有 Eno、Ename、Cno、Cname 和 Salary，请将下面 SQL 语句的空缺部分补充完整。

答案
CREATE TABLE WORKS(
    Eno CHAR(10) ____(a)____,
    Cno CHAR(4) ____(b)____,
    Salary int ____(c)____,
    PRIMARY KEY ____(d)____,
);
CREATE ____(e)____ AS
SELECT EMP.Eno,
    Ename,
    COMPANY.Cno,
    Cname,
    Salary
From EMP,
    COMPANY,
    WORKS
WHERE ____(f)____;

# 数据操作

# Select 基本结构

语句格式：

SELECT [ALL | DISTINCT] < 目标列表达式 > [,<目标列表达式>] …
FROM < 表名或视图名 > [,<表名或视图名>] [WHERE <条件表达式>] [GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC | DESC] … ]

# 简单查询

# 最基本的查询语句

Select Sno,
    Sname
from S
where sex = '男';

等价于：πSno,Sname(σsex=′男′(S))π_{Sno,Sname}(σ_{sex='男'}(S))

# 更名操作

使用 AS 可以给列和表重命名。

Select 学生表.sno as 学号,
    Sname as 姓名,
    Grade 成绩 -- AS 是可以省略的
from S 学生表,
    SC 课程表 -- 表也可以重命名
where 学生表.Sno = 课程表.Sno
    and 学生表.SD = '计算机';
-- 表重命名后 ， 引用时一定要用 新名称 ， 这里不能再用 S 和 SC

# 连接查询

如果涉及两个以上的表，则称为连接查询。

  1. 检索选修了课程号为 'C1' 的学生号和姓名

+++ 答案

SELECT S.Sno,
    Sname
FROM S,
    SC
WHERE S.Sno - SC.Sno
    and SC.Cno = 'C1'

等价于 πS.Sno,Sname(σS.Sno=SC.Sno∧SC.Cno=′C1′(S×SC))\pi_{S.Sno,Sname}(\sigma_{S.Sno=SC.Sno\wedge SC.Cno='C1'}(S\times SC))
+++

  1. 检索选修课程名为 “操作系统” 的学生号和学生姓名

+++ 答案

Select S.Sno,
    Sname
from S,
    SC,
    C
where S.Sno = SC.Sno
    and SC.Cno = C.Cno
    and C.Cname = '操作系统'

+++

  1. 检索至少选修了课程号为 'C1' 和 'C3' 的学生号
答案
Select Sno
from SC SC X,
    SC SC Y
where SC X.Sno = SC Y.Sno
    and SC X.Cno = 'C1'
    and SC Y.Cno = 'C3'

# 子查询

子查询也称嵌套查询。嵌套查询是指一个 SELECT-FROM-WHERE 查询块中可以嵌入另一个查询块之中。

在 SQL 中允许多重嵌套。

IN （ 在 … … 集合中 ） | NOT IN （ 不在 … … 集合中 ）
  1. 检索选修了课程号为 'C1' 的学生号和学生姓名
答案
Select Sno,
    Sname
from S
where Sno IN (
        Select Sno
        from SC
        Where Cno = 'C1'
    );
  1. 检索选修课程名为 “操作系统” 的学生号和学生姓名
答案
Select Sno,
    Sname
from S
where Sno IN (
        Select Sno
        from SC
        Where Cno IN (
                Select Cno
                From C
                Where Cname = '操作系统'
            )
    );
EXISTS（存在） | NOT EXISTS（不存在）

查询没有被任何同学选修的课程号和课程名

+++ 答案

Select Cno,
    Cname
from C
where NOT EXISTS (
        select *
        from SC
        where SC.Cno = C.Cno
    )

+++

# 聚集函数

聚集函数是一个值的集合为输入，返回单个值的函数。

SQL 提供了 5 个预定义集函数：平均值 AVG() 、最小值 MIN() 、最大值 MAX() 、求和 SUM() 、计数 COUNT() 。

集函数名 功能
COUNT（[DISTINCT |ALL]*） 统计元组个数
COUNT（[DISTINCT | ALL] <列名>） 统计一列中值的个数
SUM（[DISTINCT |ALL] <列名>） 计算一列中值的总和
AVG（[DISTINCT | ALL] <列名>） 计算一列中值的平均值
MAX（[DISTINCT | ALL] <列名>） 求一列值的最大值
MIN（[DISTINCT | ALL] <列名>） 求一列值的最小值

  1. 查询课程 C1 的最高分和最低分以及高低分之间的差距
    +++ 答案
Select MAX (Grade),
    MIN (Grade),
    MAX (Grade) - MIN (Grade)
From SC
Where Cno = 'C1 '
  1. 查询其他系比计算机系所有学生年龄都要小的学生姓名和年龄

+++ 答案

Select Sname,
    Age
From S
Where Age < ALL(
        Selec tAge
        From S WhereSD = '计算机'
    )
    AND SD <> '计算机';
Select Sname,
    Age
From S
Where Age <(
        SELECT MIN(Age)
        From S
        Where SD = '计算机'
    )
    AND SD <> '计算机';

+++

谓 词 等价的聚集函数 语义
>/>= ANY >/>= MIN 大于 / 大于等于子查询结果中的某个值
>/>= ALL >/>= MAX 大于 / 大于等于子查询结果中的所有值
</<= ANY </<= MAX 小于 / 小于等于子查询结果中的某个值
</<= ALL </<= MIN 小于 / 小于等于子查询结果中的所有值
<>ANY -- 不等于子查询结果中的某个值
<>ALL NOT IN 不等于子查询结果中的任何一个值
=ANY IN 等于子查询结果中的某个值
=ALL -- 等于子查询结果中的所有值

# 分组查询

# GROUP BY 子句

对元组进行分组，此时聚集函数只用于每个分组。

Select AVG(Grade)
from SC;
Select Sno,
    AVG(Grade)
from SC
GROUP BY Sno;

# HAVING 子句

假如元组在分组前按照某种方式加上限制，使得不需要的分组为空。可以在 GROUP BY 子句后面加上 HAVING 子句。

其实就是分组后做一个选择，去掉不满足 HAVING 后面的那个条件的分组。

Select Sno,
    AVG(Grade)
from SC
GROUP BY Sno
HAVING AVG(Grade) > 85;

# 字符串操作

字符串常用 LIKE 来进行匹配操作， % 匹配任意长度字符串； _ 匹配任意一个字符。模式是区分大小写的。

  1. 查询住址包含 “科技路” 的学生姓名

    Select Sname
    from S
    Where Addr LIKE '%科技路%';

  2. 查询名字为‘国庆’的学生姓名、年龄和所在系

    Select Sname,
        Sage,
        SD
    from S
    Where Sname LIKE '__国庆';

为了使模式中包含特殊字符，即 % 和 _ ，允许使用 ESCAPE 关键词来定义转义符， LIKE 语句后面紧跟 ESCAPE '\' 表明转义符 '\' 后面的特殊字符为普通字符

LIKE 'ab\%cd%' escape '\' --匹配所有以ab%cd开头的字符串
LIKE ' ab \ \ cd %' escape ' \ ' -- 匹配所有以 ab\cd 开头的字符串

# 集合操作

  1. UNION （并）、 UNION ALL （不去掉重复行）
  2. INTERSECT （交）
  3. EXCEPT （差）

SumatraPDF_TAT3JRSgoOd43491cd9208e77c.png

贷款 (customer-no, loan-no)
存款 (customer-no, account-no)

  1. UNION （并）、 UNION ALL （不去掉重复行）查询在银行有贷款或者有存款的所有客户身份证号

+++ 答案

select 'customer-no'
from 贷款
union
select 'customer-no'
from 存款;

+++

  1. INTERSECT （交）查询在银行既有贷款又有存款的客户身份证号

+++ 答案

select 'customer-no'
from 贷款
intersect
select 'customer-no'
from 存款;

+++

  1. EXCEPT （差）查询只在银行有贷款而没有存款的客户身份证号

+++ 答案

select 'customer-no'
from 贷款
except
select 'customer-no'
from 存款;

+++

# 外连接

# LEFT JOIN（左外连接）

SumatraPDF_GHugQSpQ8r.png

SELECT S.Sno,
    Sname,
    SC.Cno,
    Grade
FROM S
    LEFT JOIN SC ON S.Sno = SC.Sno;

# RIGHT JOIN（右外连接）

SELECT S.Sno,
    Sname,
    SC.Cno,
    Grade
FROM S
    RIGHT JOIN SC ON S.Sno = SC.Sno;

# FULL JOIN（全外连接）

SELECT S.Sno,
    Sname,
    SC.Cno,
    Grade
FROM S
    FULL JOIN SC ON S.Sno = SC.Sno;

# INSERT INTO 语句

INSERT INTO 语句用于向表中插入新的行。

语句格式：

INSERT INTO 表名
VALUES (值1, 值2 …)
INSERT INTO 表名(列名1 ， 列名2 …)
VALUES (值1, 值2 …)

INSERT INTO SC
VALUES ('3001', 'C1', '84');
INSERT INTO SC(SNO, CNO)
VALUES ('3001', 'C2');

# DELETE 语句

DELETE 语句用于删除表中的行。

语句格式：

DELETE FROM 表名
WHERE 条件表达式

DELETE FROM SC
WHERE Cno = 'C1';

也可以删除表中的所有数据

DELETE FROM 表名
DELETE FROM SC;

注意：不同于 DORP TABLE SC ， DELETE 只是删除表中的数据，但是关系模式仍然存在，表 SC 仍然存在于数据库中。

# UPDATE 语句

UPDATE 语句用于修改表中的数据。

语句格式：

UPDATE 表名
SET 列名 = 新值
WHERE 条件表达式

UPDATE SC
SET Grade = 90
WHERE Sno = '3001'
    AND Cno = 'C2';

# 视图的查询与更新

# 视图的查询

建立计算机系（CS 表示计算机系）学生的视图，并要求进行修改、插入操作时保证该视图只有计算机系的学生。

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

此时要查询计算机系年龄小于 20 岁的学生的学号及年龄的 SQL 语句如下

SELECT Sno,
    Sage
FROM CS_Student
WHERE Sage < 20

系统执行该语句时，通常先将其转换成等价的对基本表的查询，然后执行查询语句。也就是查询视图时，系统先从数据字典中取出视图的定义，然后将定义中的查语和对该视图的查询语句结合起来，形成一个修正的查询语句。对上例修正之后的查询语句为

SELECT Sno,
    Sage
FROM Student
WHERE SD = 'CS'
    AND Sage < 20;

# 视图的更新

SQL 对视图的更新必须遵循以下规则

  1. 从多个基本表通过连接操作导出的视图不允许更新。
  2. 对使用了分组、聚集函数操作的视图不允许进行更新操作。
  3. 如果视图是从单个基本表通过投影、选取操作导出的则允许进行更新操作，且语法同基本表。