目录
3.1SQL概述
3.1.1 SQL的产生于发展
目前,没有一个数据库系统能够支持SQL标准的所有概念和特性。同时,许多软件厂商对SQL基本命令集还进行了不同程度的扩充和修改,又可以支持标准以外的一些功能特性。
3.1.2 SQL的特性
1.综合同一
2.高度非过程化
3.面向集合的操作方式
4.以同一种语法结构提供多种使用方式
5.语言简介,易学易用
3.1.3 SQL的基本概念
支持SQL的干系数据库管理系统同样支持关系数据库三级模式结构。其中外模式包括若干视图和部分基本表,模式包括若干基本表,内模式包括若干存储文件。
基本表和视图一样,都是关系。
基本表使本身独立存在的表,在关系数据库管理系统中一个关系就对应一个基本表。一个或多个基本表对应一个存储文件,一个表可以带若干索引,索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构对最终用户使隐蔽的。
视图使从一个或几个基本表导出的表。数据库中只存放视图的定义而不存放视图对应的数据。这些数据仍存放在导出视图的基本表中,因此视图使一个虚表。视图在概念上与基本表等同,用户课可以在视图上再定义视图。
3.2学生-课程数据库
以学生-课程数据库为例
首先要定义一个学生-课程模式S-T。其包括学生表:Student(Sno,Sname,Ssex,Sage,Sdept)、课程表:Course(Cno,Cname,Cpno,Ccredit)和学生选课表:SC(Sno,Cnon,Grade)
3.3数据定义
SQL的数据定义功能包括模式定义、表定义、视图和索引的定义。
一个关系数据库管理系统的实例中可以建立多个数据库,一个数据库中可以建立多个模式,一个模式下通常包括多个表、视图和索引等数据库对象。
3.3.1 模式的定义与删除
1.定义模式
SQL中模式定义语句如下:
CREATE SCHEMA<模式名>AUTHORIZATION<用户名>;
如果没有指定<模式名>,那么<模式名>隐含为<用户名>。
定义模式实际上定义了一个命名空间,在这个空间中可以进一步定义该模式包含的数据库对象,例如基本表、视图、索引等。用户可以在创建模式的同时在这个模式定义中进一步创建基本表、视图,定义授权,即:
CREATE SCHEMA<模式名>AUTHORIZATION<用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>];
2.删除模式
其语句为:
DROP SCHEMA<模式名><CASCADE|RESTRICT>;
其中CASCADE和RESTRICT两者必选其一。选择了CASCADE(级联),表示在删模式的同时把该模式中所有的数据库对象全部删除;选择了RESTRICT(限制),表示如果该模式中已经定义了下属的数据库对象(如表、视图等),则拒绝该删除语句的执行。
3.3.2 基本表的定义、删除与修改
1.定义基本表
其定义语句如下:
CREATE TABLE<表名> (<列名><数据类型>[列级完整性约束条件]
[,<列名><数据类型>[列级完整性约束条件]]
...[,<表级完整性约束条件>]);
例如,建立一个“学生”表Student:
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
2.数据类型
定义表的各个属性时需要指明其数据类型及长度。
数据类型 | 含义 |
CHAR(n),CHARACTER(n) | 长度为n的定长字符串 |
VARCHAR(n),CHARACTERVARYING(n) | 最大长度为n的变长字符串 |
CLOB | 字符串最大对象 |
INT,INTEGER | 长整数(4字节) |
等等 |
3.模式与表
每一个基本表都属于某一个模式,一个模式包含多个基本表。定义基本表有三种方式:
1)在表中明显地给出模式名:
CREATE TABLE "S-T".Student(...); /*Student所属的模式是S-T*/
2)在创建模式语句中同时创建表
3)设置所属的模式,这样在创建表时表名中不必给出模式名
当用户创建基本表时若没有指定模式,系统根据搜索路径来确定该对象所属的模式。
使用下面的语句可以显示当前的搜索路径:
SHOW search_path;
搜索路径的当前默认值时$user,PUBLIC。其含义时首先搜索与用户相同的模式名,如果该模式名不存在,则使用PUBLIC模式。
4.修改基本表
SQL用ALTER TABLE语句修改基本表,其一般格式为:
ALTER TABLE<表名>
[ADD[COLUMN]<新列名><数据类型>[完整性约束]]
[ADD<表级完整性约束>]
[DROP[COLUMN]<列名>[CASCADE|RESTRICT]]
[DROP CONSTRAINT<完整性约束名>[RESTRICT|CASCADE]]
[ALTER COLUMN<列名><数据类型>];
不论基本表中原来是否已有数据,新增加的列一律为空值。
5.删除基本表
当某个基本表不再需要时,可以使用DROP TABLE语句删除它。其一般格式为:
DROP TABLE<表名> [RESTRICT|CASCADE];
基本表定义一旦被删除,不仅表中的数据和此表的定义将被删除。而且此表上建立的索引、触发器等对象一般也都将被删除。
3.3.3 索引的建立与删除
当表的数据量比较大时,查询操作会比较耗时。建立索引是加快查询速度的有效手段。
常见的索引包括顺序文件上的索引、B+树索引、散列、位图索引等。索引虽然能够加速数据库查询,但需要占用一定的存储空间,当基本表更新时,索引要进行相应的维护,这些都会增加数据库的负担。
索引时关系数据库管理系统的内部实现技术,属于内模式的范畴。
1.建立索引
其一般格式为:
CREATE [UNIQUE][CLUSTER] INDEDX<索引名>
ON<表名>(<列名>[<次序>] [,<列名>[<次序>]]...);
UNIQUE表名此索引的每一个索引值只对应唯一的数据记录。
CLUSTER表示要建立的索引时聚簇索引。
例如:
CREATE UNIQUE INDEX Stusno ON Student(Sno);
2.修改索引
其一般格式为:
ALTER INDEX<旧索引名>RENAME TO<新索引名>;
3.删除索引
其一般格式为:
DROP INDEX<索引名>;
删除索引时,系统会同时从数据字典中删去有关该索引的描述。
3.3.4 数据字典
数据字典时关系数据库管理系统内部的一组系统表,它记录了数据库中所有的定义信息。关系数据库感力系统再执行SQL的数据定义语句时,实际上就是再更新数据字典表中的相应信息。
3.4 数据查询
其一般格式为:
SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]...
FROM<表名或视图名>[,<表名或视图名>...]|(<SELECT 语句>)[AS]<别名>
[WHERE<条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]];
整个SELECT语句的含义是,根据WHERE子句的条件表达式从FROM子句指定的基本表、视图或派生表中找出满足条件的元组,再按SELECT子句中的目标列表达式选出元组中的属性值形成结果表。
如果有GROUP BY子句,则将结果按<列名1>的值进行分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带HAVING短语,则只有满足指定条件的组才予以输出。
如果有ORDER BY子句,则结果表还要按<列名2>的值的升序或降序排序。
3.4.1 单表查询
单表查询是指仅涉及一个表的查询。
1.选择表中的若干列(即关系代数的投影运算)
1)查询指定列
SELECT Sno,Sname
FROM Student;
2)查询全部列
a.在SELECT关键字后面列出所有列名
b.如果列的显示顺序与其在基本表中的顺序相同,也可以简单地将<目标列表达式>指定为*。
例如,查询全体学生的详细记录:
SELECT*
FROM Student;
等价于
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
3)查询经过计算的值
SELECT子句的<目标列表达式>不仅可以是表中的属性列,也可以是表达式、字符串常量和函数等。
查询全体学生的姓名及其出生年份
SELECT Sname,2014-Sage
FROM Student;
2.选择表中的若干列
1)消除重复的行
两个本来并不完全相同的元组在投影到指定的某些列上后,可能会变成相同的行。可以用DISINCT来消除他们。
SELECT DISTINCT Sno
FROM SC;
等价于
SELECT ALL Sno
FROM SC;
2)查询满足条件的元组
查询条件 | 谓词 |
比较 | =,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符 |
确定范围 | BETWEEN AND,NOT BETWEEN AND |
确定集合 | IN ,NOT IN |
字符匹配 | LIKE,NOT LIKE |
空值 | IS NULL,IS NOT NULL |
多重条件 | AND.OR,NOT |
a.BETWEEN后是范围的下线(最小值),AND后是范围的上线(最大值)
查询年龄在20~23(包括20,23)之间的学生的姓名、系别和年龄:
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
b.字符匹配
其语法格式为:
[NOT] LIKE'<匹配串>' [ESCAPE'<换码字符>']
其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串,也可以含有通配符%和_。
%(百分号):代表任意长度的字符串。例如a%b表示以a开头,以b结尾的任意长度的字符串。如acb、addgb、ab等都满足该匹配串。
_(下划线):代表任意单个字符。例如a_b表示以a开头,以b结尾的长度为3的任意字符串。如acb、afb等都满足该匹配串。
如果LIKE后面的匹配串中不含通配符,则可以用=运算符取代LIKE谓词,用!=或<>运算符取代NOT LIKE谓词。
例如,查询以“DB_”开头,且倒数第三个字符为i的课程的详细情况:
SELECT*
FROM Course
WHERE Cname LIKE 'DB\_%i__'ESCAPE'\';
其中ESCAPE用来说明换码字符。
c.逻辑运算符AND和OR可用来链接多个查询条件。ADN的优先级高于OR。
3.ORDER BY 子句
例如,查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列:
SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;
4.聚集函数
为了进一步方便用户,增强检索功能,SQL提供了许多聚集函数,主要有:
COUNT(*) 统计元组个数
COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数
SUM([DISTINCT|ALL <列名>) 计算一列值的总和(此列必须是数值型)
AVG([DISTINCT|ALL <列名>) 计算一列值的平均值(此列必须是数值型)
MAX([DISTINCT|ALL <列名>) 求一列值中的最大值
MIN([DISTINCT|ALL <列名>) 求一列值中的最小值
当聚集函数遇到空值时,除COUNT(*)外,都跳过空值而只处理非空值。注意,WHERE子句中时不能用聚集函数作为条件表达式的。聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。
5.GROUP BY 子句
GROUP BY子句将查询结果按某一列或多列的值分组,值相等的一组。分组后聚集函数将作用于每一个组,即每一组都有一个函数值。如果分组后还要求按一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则可以使用HAVING短语指定筛选条件。
例如,查询选修了三门以上课程的学生学号:
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;
这里先用GROUP BY子句按Sno进行分组,再用聚集函数COUNT对每一组计数;HAVING短语给出了选择组的条件,只有满足条件(即元组个数>3,表示此学生选修的课超过3门)的组才会被选出来。
WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图,从中选择满足条件的元组。HAVING短语作用于组,从中选择满足条件的组。
3.4.2连接查询
(不小心没保存,暂时不想写了,后面再补)
3.4.3嵌套查询
3.4.4集合查询
3.4.5基于派生表的查询
子查询不仅可以出现在WHERE子句中,还可以出现FROM子句中,这时子查询生成的临时派生表称为主查询的查询对象。
例如,找出每个学生超过他自己选修课程平均成绩的课程号:
SELECT Sno,Cno
FROM SC,(SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno) AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno AND SC.Grade>=Avg_sc.avg_grade;
这里FROM子句中的子查询将生成一个派生表Avg_sc。该表由avg_sno和avg_grade两个属性组成,记录了每个学生的学号及平均成绩。主查询将SC表与Avg_sc按学号相等进行连接,选出选修课成绩大于其平均成绩的课程号。
如果子查询中没有聚集函数,派生表可以不指定属性列,子查询SELECT子句后面的列名为其默认属性。
通过FROM子句生成派生表时,AS关键字可以省略,但必须为派生表关系指定一个别名。
3.5数据更新
数据更新操作由三种:向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。
3.5.1插入数据
SQL的数据插入语句INSERT通常有两种形式,一种时插入一个元组,另一种是插入子查询结果。
1.插入元组
其一般格式为:
INSERT
INTO<表名>[(<属性列1> [,<属性列2>]...)]
VALUES(<常量1> [,<常量2>]...);
其功能是将新元组插入指定表中。其中新元组的属性列1的值为常量1,属性列2的值为常量2,···。INTO子句中没有出现的属性列,新元组在这些列上将取空值。但必须注意的是,在表定义时说明了NOT NULL的属性列不能取空值,否则会出错。
如果INTO子句中没有指明任何属性列名,则新插入的元组必须在每个属性列上均有值。
例如,将一个新学生元组(学号:201215128,姓名:陈冬,性别:男,所在系:IS,年龄:18岁)插入到Student表中。
INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215128','陈冬','男','IS',18);
在INTO子句中指出了表名Student,并指出了新增加的元组在哪些属性上要赋值,属性的顺序可以与CREATE TABLE中的顺序不一样。VALUES子句对新元组的各属性赋值,字符串常数要用单引号括起来。
只指出表名,没有指出属性名表示新元组要在表的所有属性列上都指定值,属性列的次序与CREATE TABLE中的次序相同。
2.插入子查询结果
其格式为:
INSERT
INTO<表名>[(<属性列1> [,<属性列2>...])
子查询;
3.5.2修改数据
修改数据又称为更新操作,其一般格式为:
UPDATE<表名>
SET<列名>=<表达式>[,<列名>=<表达式>]...
[WHERE<条件>];
其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句给出<表达式>的值用于取代相应的属性列值。如果省略WHERE子句,则表示要修改表中的所有元组。
1.修改某一个元组的值
例如,将学生201215121的年龄改为22岁:
UPDATE Student
SET Sage=22
WHERE Sno='201215121';
2.修改多个元组的值
例如,将所有学生的年龄增加1岁:
UPDATE Student
SET Sage=Sage+1;
3.带子查询的修改语句
例如,将计算机科学系全体学生的成绩置零:
UPDATE SC
SET Grade=0
WHERE Sno IN
( SELECT Sno
FROM Student
WHERE Sdept='CS');
3.5.3删除数据
其一般格式为:
DELETE
FROM<表名>
[WHERE<条件>];
DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句则表示删除表中全部元组,但表的定义仍在字典中。
其例子与修改数据一样。
3.6空值的处理
取空值一般有以下集中情况:
1)该属性应该有一个值,但目前不知道它的具体值。
2)该属性不应该有值。例如,缺考学生的成绩。
3)由于某种原因不便于填写。
1.空值的产生
例如:
INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215126','1',NULL);
2.空值的判断
判断一个属性的值是否为空值,用IS NULL或IS NOT NULL来表示。
例如:
SELECT*
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;
3.空值的约束条件
属性定义中有NOT NULL约束条件的不能取空值,码属性不能取空值。
4.空值的算术运算、比较运算和逻辑运算
空值与另一个值(包括另一个空值)的算术运算的结果为空值,空值与另外一个值(包括另一个空值)的比较运算的结果为UNKNOWN。有了UNKNOWN后,传统的逻辑运算中二值逻辑就扩展成了三值逻辑。
例如,找出选修1号课程的不及格的学生:
SELECT Sno
FROM SC
WHERE Grade<60 AND Cno='1';
选出的学生是那些参加了考试(Grade属性为非空值)而不及格的学生,不包括缺考的学生。因为前者使条件Grade<60的值为TRUE,后者使条件的值为UNKNOWN。
3.7视图
3.7.1定义视图
1.建立视图
其一般格式为:
CREATE VIEW<视图名>[(<列名>[,<列名>]...)]
AS<子查询>
[WITH CHECK OPTION];
其中。子查询可以是任意的SELECT语句,是否可以含有ORDER BY子句和DISTINCT短语,则取决于具体系统的实现。
WITH CHECK OPTION表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入和删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。
组成视图的属性列名或者全部省略或者全部指定。如果省略了视图的各个属性名,则隐含该视图由子查询中SELECT子句目标中的诸字段组成。但在下列三种情况下必须明确指定组成视图的所有列名:
1)某个目标不是单纯的属性名,而是聚集函数或列表达式。
2)多表连接时选出了几个同名列作为视图的字段。
3)需要在视图中为某个列启用新的更合适的名字。
关系数据库管理系统执行CREATE VIEW语句的结果只是把视图的定义存入数据字典,并补执行其中的SELECT语句。只是在对视图查询时,才按视图的定义从基本表中将数据查出。
例如,建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
由于在定义视图时加入了WITH CHECK OPTION子句,以后对该视图进行插入、修改和删除操作时,关系数据库管理系统会自动加上Sdept=‘IS’的条件。
若一个视图时从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,则称这类视图为行列子集视图。
视图不仅可以建立在一个或多个基本表上,也可以建立在一个或多个已定义好的视图上,或建立在基本表与视图上,例如:
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=60;
这里的视图IS_S2就是建立在视图IS_S1之上的。
定义基本表时,为了减少数据库中的冗余数据,表中只存放基本数据,由基本数据经过各种计算派生出的数据一般是不存储的。由于视图中的数据并不实际存储,所以定义视图时可以根据应用的需要设置一些派生属性列。这些派生属性列由于在基本表中并不实际存在,也称它们为虚拟列。带虚拟列的视图也称为带表达式的视图。
例如,定义一个反映学生出生年份的视图:
CREATE VIEW BT_S(Sno,Sname,Sage)
AS
SELECT Sno,Sname,2014-Sage
FROM Student;
这里视图BT_S是一个带表达式的视图。视图中出生年份值是通过计算得到的。
好可以用带有聚集函数和GROUP BY子句的查询来定义视图,这种视图称为分组视图。
若从一个表导出的视图的属性列与表的属性列一一对应,但修改了基本表的结构,则其视图与表的映像关系就会被破坏,该视图就不能正常工作了。为避免此类问题,最好在修改基本表之后删除由该基本表导出的视图,然后重建这个视图。
2.删除视图
其一般格式为:
DROP VIEW<视图名>[CASCADE];
视图删除后视图的定义将从数据字典中删除。基本表删除后,由该基本表导出的所有视图均无法使用了,但是视图的定义没有从字典中清楚。删除这些视图定义需要显式地使用DROP VIEW语句。
3.7.2查询视图
关系数据库管理系统执行对视图的查询时,首先进行有效性检查,检查查询中涉及的表、视图等是否存在。如果存在,则从数据字典中取出视图的定义,把定义中的子查询和用户的查询结合起来,转换成等价的对基本表的查询,然后再执行修正了的查询。这一转换过程称为视图消解。
例如:
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
可转换为
SELECT Sno,Sage
FROM Student
WHERE Sdept='IS' AND Sage<20;
但WHERE子句中是不能用聚集函数作为条件表达式的,因此执行此修正后的查询将会出现语法错误。
目前多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询就不一定能作转换了,因此这类查询应该直接对基本表进行。
定义视图并查询视图与基于派生表的查询是有区别的。视图一旦定义,其定义将永久地保存在数据字典中,之后地所有查询都可以直接引用该视图。而派生表只是在语句执行时临时定义,语句执行后该定义即被删除。
3.7.3更新视图
更新视图是指通过视图来插入、删除和修改数据。由于视图是指不实际存储数据的虚表,因此对视图的更新最终要转换为对基本表的更新。对视图的更新操作也是像查询视图那样通过视图消解,转换为对基本表的更新操作。
为防止用户通过视图对数据进行更新操作时,有意无意地对不属于视图范围内地基本表数据进行操作,可在定义视图时加上WITH CHECK OPTION子句。这样在视图上增、删、改数据时,关系数据库管理系统会检查视图定义中的条件,若不满足条件则拒绝执行该操作。
一般来说,并不是所有视图都是可更新的。一般地,行列子集视图时可更新的。
3.7.4视图的作用
1.视图能够简化用户的操作。
2.视图使用户能以多种角度看待同一数据。
3.视图对重构数据库提供了一定程度的逻辑独立性。
4.视图能够对机密数据提供安全保护。
5.适当利用视图可以更清晰地表达查询。