数据库系统概论第三章关系数据库标准语言SQL

最新推荐文章于 2022-07-19 20:32:40 发布

黄连福

最新推荐文章于 2022-07-19 20:32:40 发布

阅读量715

点赞数

分类专栏：数据库复习

本文链接：https://blog.csdn.net/AFu_xmu/article/details/115729946

版权

数据库复习专栏收录该内容

6 篇文章 4 订阅

订阅专栏

1.数据定义

关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有表、视图和索引。因此SQL的数据定义功能包括模式定义、表定义、视图和索引定义。

1.1 模式的定义与删除

1.1.1 定义模式

在SQL中，模式定义语句如下

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

如果没有指定<模式名>，那么<模式名>隐含为<用户名>
【例1】 定义一个学生-课程模式S-T

CREATE SCHEMA "S-T" AUTHORIZATION WANG;

为用户WANG定义了一个模式S-T。
【例2】

CREATE SCHEMA AUTHORIZATION WANG;

该语句没有指定<模式名>，所有<模式名>隐含为用户名WANG。
定义模式实际上定义了一个命名空间。在这个空间中可以进一步第一该模式包含的数据库对象，例如基本表、视图、索引等。定义语句如下

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

【例3】 为用户ZHANG创建一个模式TEST，并且在其中定义一个表TAB1。

CREATE SCHEMA TEST AUTHORIZATION ZHANG
	CREATE TABLE TAB1(
	COL1 SMALLINT,
	COL2 INT,
	COL3 CHAR(20),
	COL4 NUMERIC(10,3),
	COL5 DECIMAL(5,2)
	);

1.1.2 删除模式

在SQL中，删除模式语句如下

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

其中CASCADE和RESTRICT两者必选其一。
选择了CASCADE（级联），表示在删除模式的同时把该模式中所有的数据库对象全部一起删除。
选择了RESTRICT（限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属对象时才能执行DROP SCHEMA语句。
【例4】 删除例三创建的TEST模式与其中的表TAB1。

DROP SCHEMA TEST CASCADE;

1.2 基本表的定义、删除与修改

1.2.1 定义基本表

SQL语言使用CREATE TABLE 语句定义基本表，格式如下：

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

【例5】 建立一个学生表Student

CREATE TABLE Student(
	Sno CHAR(9) PRIMARY KEY, /*列级完整性约束条件，Sno是主码*/
	Sname CHAR(2) UNIQUE, /*Sname取唯一值*/
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20)
	);

【例6】 建立一个课程表Course

CREATE TABLE Course(
	Cno CHAR(4) PRIMARY KEY,
	Cname CHAR(40),
	Cpno CHAR(4),
	Ccredit SMALLINT,
	FOREIGN KEY (Cpno) REFERENCES Course(Cno)
	/*表级完整性约束条件，Cpno是外码，背参照表是Course，背参照列是Cno*/
	);

本例说明参照表和被参照表可以是同一个表
【例7】 建立学生选课表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*/
	);

1.2.2 数据类型

数据类型	含义
CHAR(n)	长度为n的定长字符串
VARCHAR(n)	最大长度为n的变长字符串
INT	长整数（也可以写作INTEGER)
SMALLINT	短整数
NUMERIC(p,d)	定点数，由p位数字（不包含符号、小数点）组成，小数后面有d位数字
REAL	取决于机器精度的浮点数
Double Precision	取决于机器精度的双精度浮点数
FLOAT(n)	浮点数，精度至少为n位数字
DATE	日期，包含年、月、日，格式为YYYY-MM-DD
TIME	时间，包含一日的时、分、秒，格式为HH:MM:SS

1.2.3 模式与表

每一个基本表都属于某一个模式，一个模式可以包含多个基本表。定义基本表所属的模式有三种方法。

方法一，在表名中明显地给出模式名

CREATE TABLE "S-T".Student(...);	/*Student所属的模式是S-T*/
CREATE TABLE "S-T".Course(...);		/*Course所属的模式是S-T*/
CREATE TABLE "S-T".SC(...);			/*SC所属的模式是S-T*/

方法二，在创建模式语句中同时创建表，如【例3】所示。
方法三，设置所属的模式，这样在创建表时表明中不必给出模式名。
当用户在创建基本表（或其他数据库对象）时没有指定模式，系统根据搜索路径（search path）来确定该对象所属的模式。
搜索路径包含一组模式列表，RDBMS会使用模式列表中第一个存在的模式作为数据库对象的模式名。若搜索路径中的模式名都不存在，系统将给出错误。
使用下面的语句可以显示当前的搜索路径：
```
 SHOW search_path;
```
搜索路径的当前默认值是：$user,PUBLIC。其含义是首先搜索与用户名相同的模式名，如果该模式名不存在，则使用PUBLIC模式。
DBA用户也可以设置搜索路径，比如：
```
SET search_path TO "S-T",PUBLIC;
```
然后，定义基本表：
```
CREATE TABLE Student(......);
```
实际结果是建立了S-T.Student基本表。因为RDBMS发现搜索路径中的第一个模式名S-T存在，就把该模式作为基本表Student所属的模式。

1.2.4 修改基本表

SQL语言用ALTER TABLE语句修改基本表，其一般格式为

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

其中<表名>是要修改的基本表，ADD子句用于增加新列或新的完整新约束条件，DROP子句用于删除指定的完整性约束条件，ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。
【例8】 向Student表增加“入学时间”列，其数据类型为日期型。

ALTER TABLE Student ADD S_entrance DATE;

【例9】 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

ALTER TABLE Student ALTER COLUMN Sage INT

【例10】 增加课程名称必须取唯一值的约束条件。

ALTER TABLE Course ADD UNIQUE(Cname);

1.2.5 删除基本表

当某个基本表不再需要时，可以使用DROP TABLE语句删除它，其一般格式为：

DROP TABLE <表名> [RESTRICT|CASCADE]
/*选择RESTRICT:则删除是有条件的，要删除的表不能被其他表的约束所引用（如CHECK,FOREIGN KEY 等约束），不能有视图，不能有触发器，不能有储存过程或函数等。否则不能被删除*/
/*若选择CASCADE:删除是无条件的，在删除该表的同时，相关的依赖对象，比如视图，都将一起被删除。*/
/*缺省状态是RESTRICT*/

【例11】 删除Student表。

DROP TABLE Student CASCADE;

1.3 索引的建立与删除

建立索引是加快查询速度的有效手段。用户可以根据应用环境的需求，在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。
一般说来，建立与删除索引由DBA或表的owner负责完成。系统在存取数据时自动选择何时的索引作为存取路径，用户不必也不能显示地选择索引。

1.3.1 建立索引

在SQL中，建立索引的一般格式为：

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

其中，表名是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名中间用逗号分隔，每个列名后面还可以用次序指定索引值的排列次序，可选ASC(升序)和DESC(降序)，缺省值为ASC。UNIQUE表明此索引的每一个索引值只对应唯一的数据记录，CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。
【例12】 在Student表的Sname列上建立一个聚簇索引，而且Student表中的记录按照Sname值升序存放。

CREATE CLUSTER INDEX Stusname ON Student(Sname);

【例13】 为学生-课程数据库中的Student，Course，SC3个表建立索引，其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC)

1.3.2 删除索引

删除索引的SQL格式为：

DROP INDEX <索引名>;

2.数据查询

数据库查询是数据库的核心操作。SQL提供了SELECT语句进行数据库的查询，其一般格式如下

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

2.1 单表查询

2.1.1 选择表中若干列

1.查询指定列

SELECT Sno,Sname
FROM Student;

2.查询全部列

SELECT * FROM Student

3.查询经过计算的值
【例】 查询全体学生的姓名及出生年份

SELECT Sname,2021-Sage
FROM Student

【例】 查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有系名。

SELECT Sname,'Year of Birth:',2021-Sage,LOWER(Sdept)
FROM Student;

2.1.2 选择表中若干行

1.消除取值重复的行（Distinct）

SELECT Sno FROM Student
/*等价于*/
SELECT ALL Sno FROM Student
/*去除学号相同的重复行*/
SELECT DISTINCT Sno FROM Student

2.查询满足条件的元组（Where)
（1）比较大小
运算符包括 =,>,<,<=,>=,!=或<>,!>（不大于）,!<（不小于）
【例】 查询计算机科学系全体学生的名单
```
SELECT Sname FROM Student
WHERE Sdept = 'CS';
```
（2）查询范围
使用BETWEEN…AND… 和NOT BETWEEN…AND… 来查找属性值在（或不在）指定范围内的元组。前部分是下限，后部分是上限。
【例】 查询年龄在20~23岁之间的学生的姓名，系别和年龄。
```
  SELECT Sname,Sdept,Sage FROM Student
  WHERE Sage BETWEEN 20 AND 23;
```
（3）确定集合
使用IN或者NOT IN 来查找属性值属于指定集合的元组。
【例】 查询CS系和IS系学生的信息
```
SELECT * FROM Student
WHERE Sdept IN ('CS','IS');
```
（4）字符匹配
LIKE 可以用来进行字符串的匹配。其中通配符%代表任意长度的字符串，_代表任意单个字符串。
【例】 查询姓刘学生的信息
```
SELECT * FROM Student
WHERE Sname LIKE "刘%"；
```
（5）多重条件
使用AND或者OR关键词，AND优先级大于OR。

2.1.3 ORDER BY子句

用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，缺省为升序。
【例】 查询选了3号课程学生的学号及成绩，结果按分数降序排列。

	SELECT Sno,Grade
	FROM SC
	WHERE Cno = '3'
	ORDER BY Grade DESC;

2.1.4 聚集函数

SQL聚集函数主要有：

	COUNT([DISTINCT|ALL] *) /*统计元组个数*/
	COUNT([DISTINCT|ALL] <列名>) /*统计一列中值的个数*/
	SUM([DISTINCT|ALL] <列名>) /*计算一列值的总和，必须是数值类型*/
	AVG([DISTINCT|ALL] <列名>) /*计算一列值的平均值，必须是数值类型*/
	MAX([DISTINCT|ALL] <列名>) /*求一列值中的最大值*/
	MIN([DISTINCT|ALL] <列名>) /*求一列值中的最小值*/

【例】 查询学生总人数

	SELECT COUNT(*)
	FROM Student;

2.1.5 GROUP BY子句

GROUP BY 子句将查询结果按某一列或多列的值分组，值相等的为一组。
如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足条件的组，则可以使用HAVING短语指定筛选条件。
【例】 查询选修了3门以上课程的学生学号。

	SELECT Sno
	FROM SC
	GROUP BY Sno
	HAVING COUNT(*) > 3;

2.2 连接查询

2.2.1 等值与非等值连接查询

当连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。
若在等值连接中吧目标列重复的属性列去掉则为自然连接
【例】 查询每个学生及其学修课程的情况

/*等值连接*/
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno = SC.Sno;

/*自然连接*/
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno;

2.2.2 自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。
【例】 查询每一门课的间接先修课（即先修课的先修课）

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpnp = SECOND.Cno;

2.2.3 外连接

在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，对于两个表的连接，有时候一个表中的表项对于另一张表并没有满足条件的元组，但是，我们仍然想将其保存，并用null值填充剩下字段，这时候就要使用外连接。
【例】 以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，仍把舍弃的Student元组保存在结果关系中，在SC表的属性上填空值。

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUT JOIN SC ON USING (Sno);

左外连接列出左边关系中所有元组，右外连接 列出右边关系中所有元组。

2.2.4 复合条件连接

使用WHERE和AND实现多表复合连接

2.3 嵌套查询

在SQL中，一个SELECT-FROM-WHERE语句称作一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
上层的查询块称为外层查询或者父查询，下层查询块称为内层查询或子查询。

2.3.1 带有IN谓词的子查询

【例】 查询选修了课程名为“信息系统”的学生学号和姓名。

SELECT Sno,Sname
FROM Student
WHERE Sno IN
	(SELECT Sno 
	FROM SC
	WHERE Cno IN
		(SELECT Cno
		 FROM Course
		 WHERE Cname = '信息系统'
		 )
	);
/*也可以使用连接查询实现*/
SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno 
AND SC.Cno = Course.Cno
AND Course.Cname = '信息系统';

2.3.2 带有比较符号的子查询

如果内查询的结果是一个值，可以用=代替IN。
子查询内值与父查询相关的话，则称为相关子查询。如下例。
【例】 找出每个学生超过他选秀课程平均成绩的课程号

SELECT Sno,Cno
FROM SC x
WHERE Grade >= (SELECT AVG(Grade)
				FROM SC y
				WHERE y.Sno = x.Sno);

2.3.3 带有ANY(SOME)或ALL谓词的子查询

子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的系统用SOME）或ALL谓词修饰符。而使用ANY或ALL谓词时则必须同时使用比较运算符，语义如下：

>ANY	/*大于子查询结果中的某个值*/
>ALL	/*大于子查询结果中的所有值*/
<ANY	/*小于子查询结果中的某个值*/
<ALL	/*小于子查询结果中的所有值*/
>=ANY	/*大于等于子查询结果中的某个值*/
>=ALL	/*大于等于子查询结果中的所有值*/
<=ANY	/*小于等于子查询结果中的某个值*/
<=ALL	/*小于等于子查询结果中的所有值*/
=ANY	/*等于子查询结果中的某个值*/
=ALL	/*等于子查询结果中所有值（通常没意义）*/
!=(或<>) ANY	/*不等于子查询中的某个值*/
!=(或<>) ALL	/*不等于子查询中的任何一个值*/

【例】 查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
				FROM Student
				WHERE Sdept = 'CS')
AND Sdept <> 'CS';

2.3.4 带有EXISTS谓词的子查询

【例】 查询所有选修了1号课程的学生姓名

SELECT Sname
FROM Student
WHERE EXISTS
	(SELECT * FROM SC
	WHERE Sno = Student.Sno
	AND Cno = '1');

使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。由EXISTS引出的子查询，目标列表达式通常使用*，因为其只返回true或false，所以给出列名无实际意义。

2.4 集合查询

SELECT语句的查询结果是元组的集合，所以多个SELECT语句结果可以进行集合操作，包括：并（UNION)、交（INTERSECT）、差（EXCEPT）。参加集合操作的各查询结果列数必须相同，对应数据类型也必须相同。
【例】 查询计算机科学系的学生及年龄不大于19岁的学生的并集。

SELECT * FROM Student
WHERE Sdept='CS'
UNION
SELECT * FROM Student
WHERE Sage <= 19;

【例】 查询既选修了课程1又选修了课程2的学生。

SELECT Sno FROM SC
WHERE Cno = '1'
INTERSECT
SELECT Sno FROM SC
WHERE Cno = '2';

【例】 查询计算机科学系的学生与年龄不大于19岁的学生的差集

SELECT * FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT * FROM Student
WHERE Sage <= 19;
/*等同于*/
SELECT * FROM Student
WHERE Sdept='CS' AND Sage>19;

3.数据更新

3.1 插入数据

SQL的数据插入语句INSERT通常有两种形式，一种是插入一个元组，一种是插入子查询结果（即可以一次插入多个元组）。

3.1.1 插入元组

插入元组的INSERT语句格式为

INSERT INTO <表名> [(<属性列1>[,<属性列2>...])]
VALUES (<常量1>[,<常量2>...]);

INTO子句中没有出现的属性列，新元组在这些列上将去空值。但是需要注意的是，在表定义时说明了NOT NULL的属性列不能取空值，否则会出错。
【例】 插入数据

INSERT 
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('200215128','陈冬‘,'男','IS',18);

3.1.2 插入子查询结果

插入子查询结果的INSERT语句的格式为

INSERT 
INTO <表名> [(<属性列1>[,<属性列2>]...)
子查询;

【例】 对每一个系，求学生的平均年龄，并把结果存入数据库。

/*1.首先创建一个新表*/
CREATE TABLE Dept_age(
	Sdept CHAR(15),
	Avg_age SMALLINT
);
/*2.对Student表按系分组求平均年龄，插入到新表*/
INSERT INTO Dept_age(Sdept,Avg_age)
	SELECT Sdept,AVG(Sage)
	FROM Student
	GROUP BY Sdept;

3.2 修改数据

修改操作又称作更新操作，其语句的一般格式为

UPDATE <表名>
SET <列名> = <表达式>[,<列名> = <表达式>]...
[WHERE <条件>];

【例】 将学生200215121的年龄改为22岁

UPDATE Student
SET Sage = 22
WHERE Sno = '200215121';

3.3 删除数据

删除语句的一般格式为

DELETE FROM <表名>
[WHERE <条件>];

【例】

/*删除学号为200215128的学生记录*/
DELETE FROM Student
WHERE Sno = '200215128';
/*删除所有学生的选课记录*/
DELETE FROM SC;

4.视图

视图像一个窗户，限定用户可以看到和修改的数据。视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新操作则有一定的限制。

4.1 定义视图

4.1.1 建立视图

建立视图的SQL语句一般格式如下

CREATE VIEW <视图名> [(<列名>[,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION];

其中，子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。
WITH CHECK OPOTION表示对视图进行UPDATE,INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义的条件（即子查询表达式）。
【例】 建立信息系选修了1号课程的学生的视图。

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept='IS'
AND Student.Sno = SC.Sno
AND SC.Cno='1';

4.1.2 删除视图

删除视图的SQL语句一般格式如下

DROP VIEW <视图名> [CASCADE];

使用CASCADE，表名级联删除，即将该视图和由它导出的所有视图一起删除
【例】

DROP VIEW IS_S1 CASCADE;

4.2 查询视图

视图定义后，用户就可以像对基本表一样对视图进行查询了。
【例】 再信息系学生的视图中找出年龄小于20岁的学生。

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

4.3 更新视图

更新视图是指通过视图来插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。
对视图的更新，最终都要通过视图消解转化为对基本表的更新操作。
如果定义视图时使用了WITH CHECK OPTION子句，在进行数据更新时，RDBMS会检查视图中定义的条件，若不满足条件则拒绝该操作。
【例】 将信息系学生视图IS_Student中学号为200215122的学生姓名改为“刘晨”。

UPDATE IS_Student
SET Sname = '刘晨'
WHERE Sno = '200215122';
/*视图消解后的代码*/
UPADATE Student
SET Sname = '刘晨'
WHERE Sno = '200215122'
AND Sdept = 'IS';

【例】 向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为200215129，姓名为赵新，年龄为20岁。

INSERT INTO IS_Student
VALUES('200215129','赵新',20);
/*视图消解等价于*/
INSERT INTO Student(Sno,Sname,Sage,Sdept)
VALUES('200215129','赵新',20,'IS');

【例】 删除信息系学生视图IS_Student中学号为200215129的记录。

DELETE FROM IS_Student
WHERE Sno = '200215129';
/*视图消解后等价于*/
DELETE FROM Student
WHERE Sno = '200215129'
AND Sdept = 'IS';

黄连福

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录