关系数据库SQL复习

SQL（Structured Query Language）结构化查询语言，是关系数据库的标准语言 
SQL是一个通用的、功能极强的关系数据库语言

1.2 SQL的特点

1. 综合统一

集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。
可以独立完成数据库生命周期中的全部活动
用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。
数据操作符统一
2. 高度非过程化

非关系数据模型的数据操纵语言?面向过程?，必须制定存取路径
 SQL只要提出?做什么?，无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。
3. 面向集合的操作方式

非关系数据模型采用面向记录的操作方式，操作对象是一条记录
SQL采用集合操作方式
4. 以同一种语法结构提供两种使用方法

SQL是独立的语言 能够独立地用于联机交互的使用方式
SQL又是嵌入式语言 SQL能够嵌入到高级语言（例如C， C++， Java）
5. 语言简洁，易学易用

1.3 SQL的基本概念

基本表
? 本身独立存在的表
? SQL中一个关系就对应一个基本表
? 一个(或多个)基本表对应一个存储文件
? 一个表可以带若干索引
存储文件
? 逻辑结构组成了关系数据库的内模式
? 物理结构是任意的，对用户透明
视图
? 从一个或几个基本表导出的表
? 数据库中只存放视图的定义而不存放视图对应的数据
? 视图是一个虚表
? 用户可以在视图上再定义视图

数据定义

模式定义、表定义、视图和索引的定义

2.1 模式的定义与删除

定义模式实际上定义了一个命名空间。

在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。

在CREATE SCHEMA中可以接受CREATE TABLE， CREATE VIEW和GRANT子句。 

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

eg:CREATE SCHEMA ?S-T? AUTHORIZATION WANG;为用户WANG 定义了一个模式S-T 

    CREATE SCHEMA AUTHORIZATION WANG；<模式名>隐含为用户名WANG

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

DROP SCHEMA <模式名> <CASCADE|RESTRICT>
CASCADE(级联) :删除模式的同时把该模式中所有的数据库对象全部删除
RESTRICT(限制):如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。
　　　　　　　　当该模式中没有任何下属的对象时才能执行。

eg:删除模式ZHANG
DROP SCHEMA ZHANG CASCADE；

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

CREATE TABLE <表名>
（

<列名> <数据类型>[ <列级完整性约束条件> ]
...

[， <表级完整性约束条件> ]

） ；

<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

/*建立?学生?表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。学号是主码，姓名取值唯一。
*/
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY， /* 列级完整性约束条件*/
Sname CHAR(20) UNIQUE， /* Sname取唯一值*/
Ssex CHAR(2)，
Sage SMALLINT，
Sdept CHAR(20)
)；

//建立一个?课程?表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*/
);

//建立一个?学生选课?表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*/
);

SQL中域的概念用数据类型来实现定义表的属性时需要指明其数据类型及长度

模式与表的关系

每一个基本表都属于某一个模式
一个模式包含多个基本表

定义基本表所属模式

在表名中明显地给出模式名 Create table ?S-T?.SC（......） ; 

在创建模式语句中同时创建表

设置所属的模式 

创建基本表（其他数据库对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式 
显示当前的搜索路径： SHOW search_path;
搜索路径的当前默认值是： $user， PUBLIC

DBA用户可以设置搜索路径，然后定义基本表

SET search_path TO ?S-T? ， PUBLIC；

Create table Student（......） ;

结果建立了S-T.Student基本表。

修改基本表

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

eg：

向Student表增加?入学时间?列，其数据类型为日期型。

ALTER TABLE Student ADD S_entrance DATE；

将年龄的数据类型由字符型改为整数。

ALTER TABLE Student ALTER COLUMN Sage INT；

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

ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表
DROP TABLE <表名>［RESTRICT|CASCADE］；
RESTRICT：删除表是有限制的 欲删除的基本表不能被其他表的约束所引用，如果存在依赖该表的对象，则此表不能被删除
CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除

若表上建有视图，选择RESTRICT时基本表不能删除。如果选择CASCADE时可以删除表，视图也自动被删除 

2.3 索引的建立与删除

建立索引目的：加快查询速度

建立索引权限

DBA 或表的属主（即建立表的人）可以建立索引 。

DBMS一般会自动建立以下列上的索引 PRIMARY KEY  UNIQUE 。

维护索引 DBMS自动完成。

使用索引DBMS自动选择是否使用索引以及使用哪些索引。

索引是关系数据库的内部实现技术，属于内模式的索引是关系数据库的内部实现技术，属于内模式的范畴。 

RDBMS中索引一般采用B+树、 HASH索引来实现。采用B+树，还是HASH索引则由具体的RDBMS来决定 

B+树索引具有动态平衡的优点

HASH索引具有查找速度快的特点

CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引 

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

eg：在Student表的Sname（姓名）列上建立一个聚簇索引 

CREATE CLUSTER INDEX Stusname ON Student(Sname)；
在最经常查询的列上建立聚簇索引以提高查询效率，一个基本表上最多只能建立一个聚簇索引，经常更新的列不宜建立聚簇索引

/*
为学生-课程数据库中的Student， Course， SC三个表建立索引。
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)；

删除索引

DROP INDEX <索引名>；

eg:删除Student表的Stusname索引

DROP INDEX Stusname；

数据查询

3.1 单表查询

3.1.1选择表中的若干列

查询指定列
查询经过计算的值
SELECT子句的<目标列表达式>可以为算术表达式 字符串常量  函数 列别名

/*查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名
*/
SELECT Sname，‘Year of Birth: ‘， 2004-Sage， ISLOWER(Sdept) FROM Student；

//使用列别名改变查询结果的列标题:
SELECT Sname NAME， ‘Year of Birth:’ BIRTH，
2000-Sage BIRTHDAY， ISLOWER(Sdept) DEPARTMENT
FROM Student；

3.1.2选择表中的若干元组

1)消除取值重复的行
如果没有指定DISTINCT关键词，则缺省为ALL
指定DISTINCT关键词，去掉表中重复的行
2)查询满足条件的元组

(1) 比较大小

eg:查询所有年龄在20岁以下的学生姓名及其年龄  

SELECT Sname FROM Student WHERE Sdept=‘CS’ ；

（2）确定范围 

谓词: BETWEEN … AND …; NOT BETWEEN … AND …

(3) 确定集合

谓词： IN <值表>, NOT IN <值表>

eg:查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。 

SELECT Sname， Ssex FROM Student WHERE Sdept IN ( ‘IS‘， ‘MA‘， ‘CS‘ ); 

(4)字符匹配

谓词： [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]

//匹配串为固定字符串

SELECT *
FROM Student
WHERE Sno LIKE ‘200215121‘；
//等价于

SELECT *
FROM Student
WHERE Sno = ‘200215121‘；

//匹配串为含通配符的字符串
//查询所有姓刘学生的姓名、学号和性别。

SELECT Sname， Sno， Ssex
FROM Student
WHERE Sname LIKE ‘刘%’ ；

//查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECT Sname
FROM Student
WHERE Sname LIKE ‘欧阳__‘；

//查询名字中第2个字为"阳"字的学生的姓名和学号

SELECT Sname， Sno
FROM Student
WHERE Sname LIKE ‘__阳%’ ；

//查询所有不姓刘的学生姓名。

SELECT Sname， Sno， Ssex
FROM Student
WHERE Sname NOT LIKE ‘刘%‘；

//使用换码字符将通配符转义为普通字符
//查询DB_Design课程的课程号和学分。

SELECT Cno， Ccredit
FROM Course
WHERE Cname LIKE ‘DB\_Design‘ ESCAPE ‘\’ ；

//查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。
//ESCAPE ‘＼‘ 表示?＼? 为换码字符
SELECT *
FROM Course
WHERE Cname LIKE ‘DB\_%i__‘ ESCAPE ‘\’ ；

(5) 涉及空值的查询

谓词： IS NULL 或IS NOT NULL  ? IS不能用 = 代替

(6) 多重条件查询
逻辑运算符： AND和OR来联结多个查询条件 ND的优先级高于OR 可以用括号改变优先级

3.1.3 ORDER BY子句

ORDER BY子句 可以按一个或多个属性列排序 升序： ASC；降序： DESC；缺省值为升序
当排序列含空值时 ASC：排序列为空值的元组最后显示 DESC：排序列为空值的元组最先显示

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

SELECT Sno， Grade
FROM SC
WHERE Cno= ‘3‘
ORDER BY Grade DESC；

//查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT *
FROM Student
ORDER BY Sdept， Sage DESC；

3.1.4 聚集函数 

? 计数
COUNT（[DISTINCT|ALL] *）
COUNT（[DISTINCT|ALL] <列名>）
? 计算总和
SUM（[DISTINCT|ALL] <列名>）
? 计算平均值
AVG（[DISTINCT|ALL] <列名>）
? 最大最小值
MAX（[DISTINCT|ALL] <列名>）
MIN（[DISTINCT|ALL] <列名>）

eg：查询学生200215012选修课程的总学分数。

SELECT SUM(Ccredit)
FROM SC， Course
WHER Sno=‘200215012‘ AND SC.Cno=Course.Cno; 

3.1.5 GROUP BY子句

GROUP BY子句分组：

? 未对查询结果分组，聚集函数将作用于整个查询结果
? 对查询结果分组后，聚集函数将分别作用于每个组
? 按指定的一列或多列值分组，值相等的为一组

//求各个课程号及相应的选课人数。
SELECT Cno， COUNT(Sno)
FROM SC
GROUP BY Cno；

//查询选修了3门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3；

HAVING短语与WHERE子句的区别：

WHERE子句作用于基表或视图，从中选择满足条件的元组
HAVING短语作用于组，从中选择满足条件的组。

3.2 连接查询

3.2.1连接查询：同时涉及多个表的查询
一般格式：
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

连接字段：连接谓词中的列名称 连接条件中的各连接字段类型必须是可比的， 但名字不必是相同的
 

连接操作的执行过程
嵌套循环法(NESTED-LOOP)
1）首先在表1中找到第一个元组， 然后从头开始扫描表2，逐一查找满足连接条件的元组， 找到后就将表1中的第一个元组与该元组拼接起来， 形成结果表中一个元组。

2）表2全部查找完后， 再找表1中第二个元组， 然后再从头开始扫描表2， 逐一查找满足连接条件的元组， 找到后就将表1中的第二个元组与该元组拼接起来， 形成结果表中一个元组 

3）重复上述操作， 直到表1中的全部元组都处理完毕 

排序合并法(SORT-MERGE)
1）首先按连接属性对表1和表2排序
2）对表1的第一个元组， 从头开始扫描表2， 顺序查找满足连接条件的元组， 找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。 当遇到表2中第一条大于表1连接字段值的元组时， 对表2的查询不再继续
3）找到表1的第二条元组， 然后从刚才的中断点处继续顺序扫描表2， 查找满足连接条件的元组， 找到后就将表1中的第一个元组与该元组拼接起来， 形成结果表中一个元组。 直接遇到表2中大于表1连接字段值的元组时， 对表2的查询不再继续
4）重复上述操作， 直到表1或表2中的全部元组都处理完毕为止

索引连接(INDEX-JOIN)
1）对表2按连接字段建立索引
2）对表1中的每个元组， 依次根据其连接字段值查询表2的索引， 从中找到满足条件的元组， 找到后就将表1中的第一个元组与该元组拼接起来， 形成结果表中一个元组

3.2.2 等值与非等值连接查询

//等值连接：连接运算符为=
//查询每个学生及其选修课程的情况

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；

3.2.3 自身连接

自身连接：一个表与其自己进行连接
需要给表起别名以示区别
由于所有属性名都是同名属性，因此必须使用别名前缀

//查询每一门课的间接先修课（即先修课的先修课）

SELECT FIRST.Cno， SECOND.Cpno
FROM Course FIRST， Course SECOND
WHERE FIRST.Cpno = SECOND.Cno；

3.2.4 外连接

外连接与普通连接的区别
? 普通连接操作只输出满足连接条件的元组
? 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出 

//查询每个学生及其选修课程的情况

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

左外连接：列出左边关系（如本例Student） 中所有的元组
右外连接：列出右边关系中所有的元组

3.2.5 复合条件连接

复合条件连接： WHERE子句中含多个连接条件

//查