数据库原理概论Ⅱ-SQL

数据库原理概论Ⅱ-SQL

结构化查询语言（Structured Query Language）是关系数据库的一种通用语言。
SQL是在关系代数更高级的抽象，可以用比较类似英文的语法实现我们要查询的数据等等，所以一条SQL可以翻成不同的关系代数，而功能是相同的，这也就造成了不同SQL的写法效率相差很多。
在本文中我们并不考虑SQL效率的问题，而只总结常用的SQL的用法。

借用书上的例子来讲解，首先先定义一个学生-课程模式，包含以下三个表

• 学生表：Student(Sno, Sname, Ssex, Sage, Sdept)
• 课程表：Course(Cno, Cname, Cpno, Ccredit)
• 学生选课表：SC(Sno, Cno, Grade)

Student:

学号Sno	姓名Sname	性别Ssex	年龄Sage	所在系Sdept
201215121	李勇	男	20	CS
201215122	刘晨	女	19	CS
201215123	王敏	女	18	MA
201215125	张立	男	19	IS

Course:

课程号Cno	课程名Cname	先修课Cpno	学分Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	PASCAL语言	6	4

SC:

学号Sno	课程号Cno	成绩Grade
20125121	1	92
20125121	2	85
20125121	3	88
20125122	2	90
20125122	3	80

一、数据定义

1.1 模式的定义和删除

一个数据库可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库对象
定义一个模式实际上是定义了一个命名空间，在该命名空间下可以定义表，视图等

1.定义模式

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

例 ：为用户ZHANG创建一个模式TEST，并在其中定义一个表TAB1

CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1(	COL1 SMALLINT,
					COL2 INT,
					COL3 CHAR(20),
					)；				

2.删除模式

DROP SCHEMA <模式名>  <CASADE | RESTRICT>；

选择级联（CASADE）在删除模式的同时删除该模式下所有的表、视图等
选择限制（RESTRICT）只有在模式下没有任何对象时才可以删除

例：删除模式TEST，并且同时删除定义的表TAB1

DROP SCHEMA ZHANG CASCADE；

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

1.定义基本表

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

例：定义学生选课表SC，并且设置列级、表级完整性约束

CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno, Cno),	/*主码由两个属性构成， 表级约束*/
FOREIGN KEY(Sno) REFERENCES Stuent(Sno),/*Sno是外码， 被参照表是Student中的Sno属性*/
FOREIGN KEY(Cno) REFERENCES Course(Cno),
);

2.修改基本表

ALTER TABLE <表明>
[ADD [COLUMN] <新列名> <数据类型>[完整性约束]]  // 添加新的列 
[ADD <表级完整性约束>]   						// 添加新的约束 
[DROP [COLUMN] <列名> [CASCADE|RESTRICT]] 	// 删除列
[DROP CONSTRAINT <完整性约束名> [RESTRICT | CASCADE]]	// 删除约束
[ALTER COLUMN <列名><数据类型>]；	// 修改表的定义

3.删除基本表

DROP TABLE <表名> [RESTRICT | CASCADE]

RESTRICT删除的基本表不能被其他表删除
CASCADE删除的表会将相关的视图等一并删除

1.3 索引的建立和删除

当表的数据量比较大的时候，查询会比较耗时，这时建立相应的索引会加快查询速度。
数据库索引包括顺序文件的索引，B+树索引、散列索引、位图索引。
顺序文件索引:按照指定属性值升序或者降序，在该属性上建立顺序索引。
B+树索引：将属性组织成B+树的形式，B+树的叶节点为属性值和相应的指针
散列索引：建立若干个桶，将索引属性按照散列函数值映射到相应的桶中。桶中包括属性值和指针。
位图索引：用位向量记录索引属性中可能出现的值。

用户不能显示地选择索引形式，因为索引结构属于内模式。

1.建立索引

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

<表名> ：要建立索引基本表的名字，索引可以建立在表的多列上
<次序>： ASC升序 或 DESC降序

例：为前面的表按学号和课程号升序建立唯一索引

CREATE UNIQUE INDEX SCno On SC(Sno ASC, Cno DESC);

2.修改索引

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

3.删除索引

DROP INDEX <索引名>

二、数据查询

数据查询是数据库的核心操作，基本形式如下：

SELECT [ALL | DISTINCT] <目标列表达式> [, <目标列表达式>] ...
FROM <表名> [, <表名>...] | (<SELECT语句>) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];

GROUP BY : 结果按<列名1>的值进行分组，属性相同的元组为一个组，HAVING指定条件的组才输出。
ORDER BY：结果按<列名2>进行升序和降序的排列。

2.1 单表查询

1.查询全体学生的姓名、出生年份、所在院系，要求用小写字母表示系名

可以在属性后面跟上别名，使新的关系有新的名字

SELECT Sname NAME, 'Year of Birth:' BIRTH, 2014-Sage BIRTHADAY, LOWER(Sdept) DEPARTMENT FROM Student;

NAME	BIRTH:	BIRTHADAY	DEPARTMENT
李勇	Year of Birth	1994	cs
刘晨	Year of Birth	1995	cs
王敏	Year of Birth	1996	ma
张立	Year of Birth	1995	is

2.查询考试成绩不及格的学生的学号

使用WHERE进行条件限定
使用DISTINCT去除重复的数据

SELECT DISTINCT Sno FROM SC WHERE Grade < 60;

3.查询CS、MA、IS系的学生

使用IN查询指定集合的元组

SELECT Sname, Ssex FROM Student WHERE Sdept IN ('CS','MA','IS');

4.字符匹配查询：查询DB_Design课程的课程号和学分

_ 代表任意单个字符
% 代表任意长度的字符串
ESCAPE ‘’ 表示 \ 是转义字符

SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\';

5.查询选修3号课程的学生的学号及其成绩，查询结果按分数的降序排列

ORDER BY <属性>:按照该属性升序、降序排列

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

6.聚集函数

• COUNT(*)：统计元组个数
• COUNT([DISTING|ALL] <列名>)：统计一列中 值得个数
• SUM([DISTING|ALL] <列名>)： 求该列的总和
• AVG([DISTING|ALL] <列名>) : 求该列的平均值
• MAX([DISTING|ALL] <列名>): 求该列的最大值
• MIN([DISTING|ALL] <列名>)：求该列的最小值

注意：聚集函数只能作用于SELECT子句和GROUP BY的HAVING子句

查询学生201215012选修课程的总学分数

SELECT SUM (Ccredit) FROM SC,Course WHERE Sno = '201215012' AND SC.Cno = Course.Cno;

7.GROUP BY 子句

GROUP BY 子句将查询结果按某一列分组，值相等的分为一组。
分组的目的是为了细化聚集函数的作用范围。分组后聚集函数的作用于每一个分组，即每一组有一个函数值。

例：求各个课程号以及对应的选课人数

SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;

先对Cno分组，相同Cno的为一组，然后对每一组中进行COUNT，算出每一组中的人数

例：查询选修了三门以上课程的学号

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

首先先对Snoopy分组，
然后使用HAVING 对每个分组内的选课数目大于3的进行筛选。

注意：WHERE子句的作用范围是基本表，HAVING作用于组，从中选择满足条件的组

例：查询平均成绩大于等于90分的学生学号和平均成绩

SELECT Sno, AVG(Grade)
FROM SC
GROUP BY(Sno)
HAVING AVG(Grade) >= 90;

注意：WHERE子句不能以聚集函数作为条件，所以不能写为WHERE AVG(Grade>=90)

2.2 连接查询

1.等值与非等值的连接查询

当查询同时涉及两个以上的表称为连接查询。连接查询使用WHERE子句连接连个表的条件。

例：查询每个学生以及选课情况

分析： 学生情况存放在Student表中，学生情况表放在SC表中，二者的连接通过公共属性Sno连接

SELECT Student.* SC.*
FROM Student, SC
WHERE  Student.Sno = SC.Sno;

连接之后的表如下

Student.Sno	Sname	Ssex	Sage	Sdept	SC.Sno	Cno	Grade
201215121	李勇	男	20	CS	201215121	1	92
201215121	李勇	男	20	CS	201215121	2	85
201215121	李勇	男	20	CS	201215121	3	88
201215122	刘晨	女	19	CS	201215122	2	90
201215122	刘晨	女	19	CS	201215122	3	80

2.外连接
在上一个连接中，只有满足条件的元组才能输出，所以没有选课的学生的信息就被丢弃了，所以要保留NULL的数据就要采用上一章讲的关系代数中的外连接，外连接使用OUTER JOIN，分为左外连接和右外连接。

例：显示所有学生的选课信息，包括没有选课的学生

SELECT Student.* SC.*
FROM Student
LEFT OUTER JOIN SC ON (Student.Sno = SC.Sno);

连接之后的表如下

Student.Sno	Sname	Ssex	Sage	Sdept	SC.Sno	Cno	Grade
201215121	李勇	男	20	CS	201215121	1	92
201215121	李勇	男	20	CS	201215121	2	85
201215121	李勇	男	20	CS	201215121	3	88
201215122	刘晨	女	19	CS	201215122	2	90
201215122	刘晨	女	19	CS	201215122	3	80
201215123	王敏	女	18	MA	201215122	NULL	NULL
201215125	张立	男	19	IS	201215122	NULL	NULL

3.多表连接

例：查询每个学生的学号、姓名、选修的课程名及成绩。

多个表连接使用AND

SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

2.3 嵌套查询

1.带有IN谓词的子查询

例：查询与“刘晨”在同一个系的学生
首先先查询刘晨在哪个系(内层查询)，然后再查询在该系的学生(外层查询)，将二者嵌套起来。
使用IN进行集合的查询

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
			(SELECT Sdept 
			FROM Student
			WHERE Sname = '刘晨');

这类查询子查询跟外层查询没有关系，称为不相关子查询。

2.带有比较运算符的子查询

例：查询每个学生超过自己选修课程平均成绩的课程号

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

这种内层查询依赖于外层的结果的查询叫做相关子查询

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

例：查询非计算机系中比计算机系中任意一个学生年龄小的学生姓名和年龄

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

4.带有EXISTS谓词的子查询

EXISTS表示存在，带有EXISTS的子查询不返回任何数据，只产生逻辑true或者false

例：查询所有选择了1号课程的学生姓名

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

使用EXISTS后，若内层查询结果非空，外层的WHERE子句返回真值
EXIXTS之后的子查询一般SELECT后都用*代替，因为没有返回值，只有true 或者false

查询过程：首先先取外层查询Student中的Sno，如果与内层的Sno一样，且内层Cn=1，返回true，外层输出Sname。

三、数据更新

3.1插入数据

1.插入元组

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

例：插入一条选课记录

INSERT INTO S(Sno,Cno) VLALUES('201215128','1',NULL);

2.插入子查询结果

SELECT语句嵌套在INSERT中可以批量插入数据

例：建立一个新表，存在系名和学生平均成绩，然后将每个系的数据插入。

CREATE TABLE Dept_age(Sdept CHAR(15) Avg_age SMALLINT);

INSERT INTO Dept_age(Sdept, Avg_age)
		SELECT Sdept, AVG(Sage)
		FROM Student
		GROUP BY Sdept;

3.2 修改操作

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

例：将计算机专业全体同学的成绩置零

UPDATE SC 
SET GRADE = 0
WHERE Sno IN 
	(SELETE Sno
	FROM Student
	WHERE Sdept = 'CS')；

3.3 删除操作

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

例：删除计算机专业所有学生的选课记录

DELETE 
FROM SC
WHERE Sno IN
	(SELETE Sno
	FROM Student
	WHERE Sdept = 'CS');

四、视图

视图是数据库中表的一个窗口，本身并不是真实存在于数据库中的表，而是提供给用户看的一个虚表，其本身并不存放数据，数据本身仍然存在于原来的表中。

3.1 定义视图

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

子查询可以是任意的SELECT
WITH CHECK OPTION 表示对视图进行UPDATE INSERT DELETE 操作要满足的条件

例：建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）

CRATE 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.Sno = 1;

3.2 删除视图

DROP VIEW <视图名> [CASCADE]；

3.3 查询视图

例：在信息系学生的视图中查找小于20岁的学生

SELECT Sno.Sage
FROM IS_Student
WHERE Sage < 20;

由于视图并不储存数据，在进行查询的时候，取出视图的定义，把视图中的子查询和本次查询的条件结合起来，再对基本表进行查询。也叫作视图消解(view resolution)

所以最终的查询语句是下面这样:

SELECT Sno,Sage
FROM Student 
WHERE Sdept = 'IS' AND Sage < 20;

在视图中，我们一般不做更新和删除，所以在此不再叙述