04_ 关系数据库标准语言SQL

1. SQL 概述

1.1 基本概念

结构化查询语言是关系数据库的基本的语言, 也是一个通用的, 功能性极强的关系数据库语言.

支持 SQL 的关系数据库管理系统也支持数据库三级模式结构, 外模式包括视图和部分基本表, 模式包括若干基本表, 内模式包括若干存储文件.

2. 数据定义

2.1 模式的定义与删除

一个关系数据库管理系统实例可以建立多个数据库, 一个数据库中可以建立多个模式, 一个模式下通常包括多张表, 视图和索引等数据对象.

2.1.1 模式的定义

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
如果没有指定<模式名>, 则<模式名>隐含为<用户名>

例如 : 为用户 LI 定义一个学生-课程模式 S-T

create SCHEMA S-T AUTHORIZATION LI

定义模式实际上是定义了一个命名空间, 在这个空间中可以进一步定义该模式包含的数据库对象, 例如基本表, 视图, 索引等.

而且在定义模式的同时可以进一步创建基本表, 视图, 定义授权 .

create schema <模式名> authorization <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]

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

create schema TEST authorization ZHANG 
create table TAB1 (col1 smallint,
                   col2 int,
                   col3 char(20),
                   col4 numberic(10,3),
                   col5 decimal(5,2)
                  );

2.1.2 删除模式

drop schema <模式名> <CASCADE|RESTRICT>;

其中 cascade 与 restrict 二者必选其一, cascade 是级联, 删除的时候级联删除, restrict(限制), 表示如果在该模式中已经定义了下属对象, 则拒绝该删除语句的执行.

例如级联删除模式 ZHANG

drop schema ZHANG cascade 该语句在删除模式 ZHANG 的同是也删除了其中的 TAB1 表.

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

2.2.1 模式与表

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

定义表及其所属的模式的三种方式 :

• 在表名中显式给出模式名 : create table 'S-T'.Student(...);

• 在创建模式的语句中直接创建表(详见定义模式)

• 设置所属模式, 在创建表时不需要指定模式名 :

  如果没有直接指定模式, 系统会根据搜索路径来确定该对象所属的模式, 搜索路径为一组模式列表, 关系数据库管理系统会使用模式列表中的第一个存在的模式作为数据库对象的模式名, 如果搜索路径中的模式都不存在,系统将给出错误.

  可以使用下面语句显示当前的搜索路径 : show search_path

  搜索路径的默认值为 $User, PUBLIC, 含义是首先搜索与用户名相同的模式名, 如果模式名不存在, 则使用 PUBLIC 模式.

  数据库管理员也可以设置搜索路径 : set search_path TO 'S-T',PUBLIC;,然后定义基本表, 关系数据库管理系统就会首先搜索是否存在该模式名对应的模式.

2.2.2 数据类型

数据模型中有一个很重要的概念域, 每个属性一个域, 它的取值必须是域中的值.定义表中的各个属性时需要指明其数据类型及其长度.

数据类型	含义
char(n) character(n)	长度为 n 的定长字符串
varchar(n) charactervarying(n)	最大长度为 n 的变长字符串
clob	字符串大对象
blob	二进制大对象
smallin	短整型(2字节)
tint Integer	长整型(4字节)
bigint	大整形(8字节)
numeric(p,d)	定点数 由p位数字(不包括符号,小数点)组成,小数点后有d位数字
decimal(p,d) dec(p,d)	同 numeric
real	取决于机器精度的单精度浮点数
double precision	取决于机器精度的双精度浮点数
float(n)	可选精度的浮点数 精度至少为 n 位数字
Boolean	逻辑布尔量
date	日期包含年月日 YYYY-MM-DD
TIME	时间 时分秒 HH:MM:SS
timestamp	时间戳类型
interval	时间间隔类型

2.2.3 定义基本表

基本格式 :

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

建表的同时还可以定义与该表有关的完整性约束条件, 这些约束条件被存在于系统的数据字典中, 当用户操作表中的数据时, 系统会自动检测该操作是否违背这些完整性约束条件, 如果完整性约束涉及到该表的多个属性列, 则必须在表级上, 否则即可以在表级也可以在列级.

2.2.4 修改基本表

一般格式 :

alter table <表名> 
[add [column] <新列名><数据类型> [完整性约束]]
[add <表级完整性约束>]
[drop [column] <列名> [cascade|restrict]]
[drop constraint <完整性约束名> [cascade|restrict]]
[alter column <列名><数据类型>];

2.2.5 删除基本表

drop table <表名> [cascade|restrict]

2.3 索引的建立与删除

建立索引是加快查询速度的有效手段.

2.3.1 建立索引

格式 :

create [unique] [cluster] index <索引名>
	on <表名>(<列名> [<次序>] [,<列名> [<次序>]] ...);

例如 : 在 SC 表中按学号升序和课程号降序唯一索引

create unique index scno on SC(Sno ASC, Cno DESC);

2.3.3 删除索引

格式 : drop index <索引名>

例如 : 删除上面建立的索引

drop index scno;

2.4 数据字典

数据字典是关系数据库系统内部的一组系统表, 他记录了数据库中所有的定义信息, 包括关系模式定义, 视图定义, 索引定义以及完整性约束定义和各类用户对数据库的操作权限, 统计信息等, 关系数据库系统在执行SQL的数据定义语句时, 实际上就是在更新数据字典表中的相应信息.

3. 数据查询

一般格式 :

select [all|distinct] <目标表达式> [,<目标表达式>] ...
from <表名|视图名> [,<表名|视图名> ...] | (<select 语句>)[AS]<别名>
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [ASC|DESC]];

3.1 单表查询

3.1.1 选择表中的若干列

3.1.2 选择若干元组

eg :

3.1.3 group By 子句

group by 子句将查询结果按照某一列或多列的值进行分组, 值相等的为一组.

分组后聚集函数将作用于每个组.

如果分组后有条件, 则在group by 后必须使用 having 进行条件的设置.

例子 : 求出平均分大于90分的学生的学号与平均分.

select sno,avg(grade)
from sc
group by sno
having avg(grade) > 90;

3.1.4 order by 子句

用户可以使用 order by 子句对查询结果按照一个属性或者多个属性进行升序或者降序, 默认为升序.

对于空值, 排序时显示的次序由具体系统来决定.

eg :

查询学号和成绩并按照成绩降序排序.

select sno, grade
from sc
where cno = '3'
order by grade desc;

3.2 连接查询

3.2.1 等值/非等值连接

连接查询的 where 子句中用来连接两个表的条件称为 连接条件或者连接谓词, 连接谓词还可以使 between … and …

当连接运算符为 = 时称为等值连接, 其他连接称为非等值连接.

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

eg :

等值连接 :

select Student.*, SC.* from Student, SC where Student.Sno = SC.Sno

非等值连接 :

select Student.no, Sname from Student, SC where Student.sno = SC.sno and SC.Grade > 90

上例中, select 子句与 where 子句中的属性名前都加了表名前缀, 这是为了避免混淆, 如果属性名在连接的各个表中是唯一的, 可以省略表名前缀.

等值连接中把目标列中重复的属性列去掉则为自然连接.

3.2.2自连接

连接的操作不仅可以在两个表中进行, 也可以在一个表中进行, 这时称为表的自连接.

使用自连接时, 要为这个表起两个别名, 以便区分.

eg :

select FIRST.Cno , Second.Cpno from Course FIRST, Course Second where FIRST.Cpno = SECOND.Cpno

3.2.3 外连接

在通常的连接操作中, 只有满足条件的元组才能被选出来, 如果要把未选中的元组(悬浮元组)也保存在结果关系中, 这时就需要外连接.

左外连接列出左边的关系中的所有的元组, 右外连接列出右边的所有的元组.

eg : 查询每个学生及其选修课程的信息(含有未选课的学生)

左外连接 :

select student.sno, sname, ssex, sage, sdept, cno, grade from Student LEFT OUTER JOIN ON (Student.Sno = SC.Sno)

3.2.4 多表连接

连接操作中两个以上的表进行连接称为多表连接.

eg : 查询每个学生的学号, 姓名, 选修的课程名 以及 成绩

select Student.Sno, Sname, Cname, grade from Student, SC, Course where Student.Sno = SC.Sno and Course.Cno = SC.Cno

3.3 嵌套查询

Sql 语言中, 一个 SELECT-FROM-WHERE 称为一个语句块, 将一个语句块嵌套在另一个查询块的 where 子句或者 having 短语的条件中的查询称为嵌套查询.

eg :

select Sname
from Student
where SNo in
(
	select Sno from SC where Cno = '2'
);

其中上层的循环块称为外层查询或者父查询, 下层的循环快称为内层查询或者子查询.

子查询的 select 语句不能使用 ORDER BY, ORDER BY 只能对最终结果排序.

3.3.1 带有 IN 谓词的子查询

在嵌套查询中, 子查询的结果往往是一个集合, 所以谓词 IN 是嵌套查询中最经常使用的谓词.

子查询的查询条件不依赖与复查询, 称为不相关子查询.

eg :

select Sname
from Student
where SNo in
(
	select Sno from SC where Cno = '2'
);

如果子查询的查询条件依赖于父查询, 这类子查询称为相关子查询, 整个查询语句称为相关查询语句.

select sno cno
from sc x
where grade >= (select avg(grade) from sc y where y.sno = x.sno);

3.3.2 带有比较运算符的子查询

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接.

当用户能够确切的知道内层返回的是单个值时, 可以用 > >= < <= != = 或者 <> 比较运算符比较结果

找出每个学生超过他自己选修课程平均成绩的课程号 :

eg :

select sno cno
from sc x
where grade >= (select avg(grade) from sc y where y.sno = x.sno);

上述的第一步 : 先从 Sc 表中取出一个 元组, 然后将 sno 传给内层的 sno

第二步 : 内层执行查询从外层传来的学号的学生的平均成绩

第三步 : 比较外层的 grade 与 内层的平均成绩

3.3.3 带有 ANY SOME 或者 ALL 谓词的子查询

子查询返回的单个值使用比较运算符, 但是当返回多个值时就要用到 ANY SOME 或者 ALL 修饰符, 而使用 ANY ALL 谓词时必须同时使用比较运算符.

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wypn1pGa-1577523463317)(…/images/20191023171635.png)]

eg :

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

select sname, sage
from Student
where sage < any(select sage from student where sdept = 'CS')
and dept <> 'CS';

3.3.4 带有 EXIST 谓词的子查询

exists 代表存在, 带有 exists 的子查询不返回任何数据, 只返回逻辑真(true)与逻辑假(false).

使用存在量词 exists 后, 如果内层结果为非空, 则外层的 where 子句返回真值, 否则返回假值.

还有 not exists 如果内层为空则返回真值, 否则返回假值.

eg :

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

select sname
from student
where exists
(select * from SC where sno = student.sno and cno = '1')

执行流程是 : 先从 student 表中取出一个元组, 然后取出 sno, 并传递给内层循环, 内层循环执行从 SC 表中查询该学号的学生是否选修了一号课程. 如果选修了则返回 true, 否则返回 false.

3.4 集合查询

集合操作主要包括并操作 UNION , 交操作 INTERSECT 和 差操作 EXCEPT

1. 问题 : 查询计算机科学系学生以及年龄不大于19岁的学生.

   分析 : 求计算机科学系学生以及年龄不大于19岁学生的并集.

   SQL : select * from student where sdept='cs' union select * from student where Sage < 19

   注意 : 使用 union 是把多个查询结果合并起来, 系统会自动去掉重复元组, 如果要保留重复元组, 则可以使用 union all 操作符.

2. 问题 : 查询计算机科学系学生与年龄不大于19岁学生发生的交集.

   SQL : select * from Student where Sdept='CS' intersect select * from student where Sage <= 19

3. 问题 : 查询计算机科学系的学生与年龄不大于19岁的学生的差集.

   SQL : select * from student where Sdept = 'cs' except select * from student where sage <= 19

3.5 基于派生类的查询

子查询不仅仅可以出现在 where 语句中, 还可以出现在 from 语句中, 这时子查询生成的临时派生表称为主查询的查询对象.

问题 : 查询每个学生超过自己选修课程平均成绩的课程号.

SQL : select sno, cno from sc, (select sno, avg(score) from sc group by sno) as AVG_sc(avg_sno,avg_grade) where sc.sno = AVG_sc.sno and sc.grade >= avg_sc.avg_grade

3.6 SELECT语句的一般格式

一般格式 :

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V8iokUdr-1577523463318)(…/images/1572141198970.png)]

目标表达式可选格式 :

* , <表名>.*, count([distinct|all] *), [<表名.>[属性列名表达式][,<表名.>[属性列名表达式]…]

其中, 属性列名表达式可以是由属性列, 作用于属性列的聚集函数和常量的任意算术运算, (±*/)组成的运算公式.

聚集函数的一般形式 :

where子句条件表达式的可选格式 :

4. 数据修改

4.1 插入数据

Sql 的数据插入语句主要有两种形式, 一种是插入一个元组, 另一种是插入一个查询结果, 后者可以插入一条或者多条元组.

普通插入数据 :

格式 : insert into <表名> [(<属性列1>[,[<属性列2>]...)] values (<常量1>[,<常量2>]...)

例如 : insert into student (Sno, Sname, Ssex, Sdept, Sage) values ('20120111','陈东','男','IS',18)

注意 :

​ value中的字符串字段要使用单引号引起来.

​ 如果插入所有字段, 则属性列可以不写, 但是属性的值要与属性列对应, 即 ``insert into student values (‘20120111’,‘陈东’,‘男’,‘IS’,18`

​ 如果插入的字段值少于表中的属性列, 则默认不指定的属性列为空值.

插入子查询的结果 :

格式 : insert into <表名> [(<属性列1>[,[<属性列2>]...)] 子查询

例如 : insert into dept(sdept,avg_age) select sdept, avg(sage) from student group by sdept

4.2 修改数据

格式 : update <表名> set <列名1> = <表达式> [, <列名1> = <表达式>] ...

修改某一个元组的数据 :

例如 : update student set sage = 22 where sno = '23023

修改多个元组的数据 :

例如 : update student set sage = sage + 1

带子查询的数据修改 :

例如 : update sc set grade = 0 where sno in (select sno from Student where sdept = 'CS')

4.3 删除数据

格式 : delete from <表名> [where <条件>]

删除一个元组的值 :

例如 : delete from student where sno = '2012333'

删除多个元组的值 :

例如 : delete from student

带子查询的删除语句 :

例如 : delete from sc where sno in (select sno from student where sdept = 'CS')

5. 空值处理

空值 : 空值就是不知道或者不存在或者无意义的值.

SQL 语言中允许某些元组的某些元素在特定情况下取空值 :

• 该属性应该有一个值, 但是具体不知道他的具体的值. 例如某学生的年龄在学生登记表漏填, 在录入时不知道他的年龄, 这时可以取空值.
• 该属性不应该有值 : 例如缺考学生的成绩为空, 因为他没有参加考试.
• 由于某种原因不便于填写, 例如一个人的电话号码不想让大家知道.

5.1 空值的判断

判断一个属性是否为空值, 用 IS NULL 或者 IS NOT NULL 来判断.

例如 : 从 student 表找出漏填数据的学生信息

select * from student where sname is null or ssex is null or sage is null or sdept is null

5.2 空值的约束条件

属性定义中有 not null 约束条件的不能取空值, 加了 unique 限制的属性不能取空值, 码属性不能取空值.

5.3 空值的算术运算比较运算逻辑运算

空值与另一个值(包括另一个空值)的算术运算的结果为空值, 空值与另一个值(包括另一个空值)的比较运算为 UNKNOWN.

有了 unknown 之后, 传统的逻辑运算中二值(true, false)逻辑就扩展为了三值(true, false, unknown)

x y	x AND y	x OR y	NOT x
T T	T	T	F
T U	U	T	F
T F	F	T	F
U T	U	T	U
U U	U	U	U
U F	F	U	U
F T	F	T	T
F U	F	U	T
F F	F	F	T

6. 视图

视图是从一个或者几个基本表导出的表, 他与基本表不同, 是一个虚表, 数据库中只存放视图的定义, 而不存放视图对应的数据, 这些数据仍然存放在基本表中.

基本表中的数据一旦发生变化, 从视图中查询出来的数据也就随之更改了.

视图一旦定义, 就可以和基本表一样被查询, 被删除, 也可以在视图之上再创建视图, 但是对视图的插入,更新和删除操作会受限制.

6.1 定义视图

SQL 语言用 CREATE VIEW 命令创建视图, 格式为 :

create view <视图名> [(<列名>[,<列名>]...)] as <子查询> [WITH CHECK OPTION]

子查询可以使任意的 select 语句, 是否可以含有 order by 子句和 distinct 短语, 取决于具体的系统实现.

with check option 表示对视图进行 update, insert, delete 操作时要保证更新, 插入, 删除的行满足视图定义中的谓词条件(即子查询中的条件表达式).

组成视图的属性列名或者全部省略或者全部指定, 没有第三种选择 :

• 如果省略了属性列名, 则隐含该视图由子查询中的 select 子句目标列中的诸字段组成.

但是在下面的条件下, 必须指定视图的所有列名 :

• 某个目标列不是单纯的属性名, 而是聚集函数或列表达式.
• 多表连接时选出了几个同名的列作为视图的字段.
• 需要在视图中为某个列启用新的更合适的名字.

例如 : 建立信息系学生的视图 :

create view IS_Student as select Sno, Sname, Sage from Student where Sdept = 'IS'

关系数据库管理系统执行 crate view 语句的结果只是把视图的定义存入数据字典, 并不执行其中的select语句, 只是在对视图进行查询时, 才按照视图定义从基本表中将数据查出.

若一个视图是从单个基本表中导出的, 并且只是去掉了部分的基本表的某些行和列, 但保留了主码, 则称这类视图为行列子集视图, 上面的 IS_Student 就是一个行列子集视图

例如 : 建立信息系学生的视图, 并要求进行修改和插入操作时扔需要保证该视图只有信息系的学生.

create view IS_Student as select Sno, Sname, Sage from Student where Sdept = 'IS' with check option

上面由于加了 with check option 以后对该视图进行插入修改删除的时候关系数据库管理系统会自动的加上Sdept='IS’的条件.

视图不仅可以建立在一个或者多个基本表上, 还可以建立在一个或者多个已经导出的视图上 .

建立信息系选修了1号课程且成绩在90分以上的学生的视图 :

• 信息系选修了1号课程的学生视图 :

  create view IS_S1(Sno, Sname, Grade) AS select Student.Sno, Student.Sname, Grade from Student, SC where Sdept = 'IS' and Student.Sno = SC.Sno and SC.cno = '1'

• 建立信息系选修了1号课程且成绩在90分以上的学生的视图

  create view IS_S2 as select Sno, Sname, Grade from IS_S1 where grade > 90

这里的 IS _ S2 就是建立在视图 IS _ S1 上的.

定义基本表时, 为了减少数据库中的冗余数据, 表中只存放基本数据, 由基本数据经过各种计算派生出的数据一般是不存储的, 由于视图中的数据并不实际存储, 所以定义视图时可以根据应用的需要设置一些派生属性列, 这些派生属性由于在基本表中不实际存在, 也称为虚拟列, 带有虚拟列的视图也称为带表达式的视图.

例如 : 定义一个反映学生出生年份的视图 :

create view BT_S(Sno, Sname, Sbirth) as select Sno, Sname, 2019-Sage from Student

这种视图就是带有表达式的视图, 因为视图中的出生年份是通过计算得到的.

还有带有聚集函数和 group by 子句的查询来定义视图, 这种视图称为分组视图.

例如 : 将学生的学号和平均成绩定义一个视图 :

create view S_G (Sno, Gavg) as select Sno, AVG(Grade) from SC group by sno;

6.2 删除视图

格式 : drop view <视图名> [CASCADE]

视图删除后视图的定义从数据字典中删除, 如果该视图上还导出了其他视图, 则使用 cascade 级联删除语句把视图和由他导出的所有视图都删除.

基本表删除后, 由该基本表导出的视图均无法使用了, 但是视图的定义没有从数据字典中删除, 删除这些视图需要显式的使用 drop view 语句.

例如 : 删除视图 BT_S

drop view BT_S

6.3 查询视图

关系数据库管理系统执行对视图的查询时, 首先进行有效性检查, 检查查询中的涉及的表, 视图是否存在, 如果存在, 则从数据字典中取出视图的定义, 把定义中的子查询和用户的查询结合起来, 转换成等价的对基本表的查询, 然后再执行修正了的查询, 这一个转换过程称为视图消解.

例如 : 在信息系学生的视图中找出年龄小于20岁的学生, 其中 IS_Student 为信息系学生的视图, Student 为基本表.

select Sno , Sage from IS_Student where Sage < 20;

进行视图消解后转换成语句为 :

select Sno, Sage from Student where Sage < 20 and Sdept = ‘IS’;

6.4 更新视图

更新视图使指通过视图来插入 更新 删除数据.

由于视图是不实际的存储数据的虚表, 因此对视图的更新会最终转换为对基本表的更新.

为了防止用户通过视图对数据进行增加删除修改时, 有意无意的堆不属于视图范围内的基本表进行操作, 可以在定义视图时加入 with check option 子句.

更新 : 对视图 IS_Student 中学号为 ‘201333’ 的学生姓名改为 ‘刘晨’

update IS_Student set sname = '刘晨' where sno = '201333'

插入 : 向 IS_Student 表中插入一个新的学生记录, 学号为 ‘201988’ 姓名为 ‘李四’ 年龄为 20

insert into IS_Student values ('201988','李四',20)

删除 : 删除 IS_Student 中学号为 ‘201977’ 的学生记录

delete from IS_Student where sno = '201977'

注意 : 并不是所有的视图都可以更新, 例如 :

设 S_G 视图是由学号和平均成绩两个属性列组成, 其中平均成绩是通过 Student 表中对元组分组后计算出来的结果, 因此平均成绩是无法进行修改的.

6.5 视图的作用

• 视图能够简化用户的操作.
• 视图使用户能够以多种角度看待同一数据.
• 视图对重构数据库提供了一定的逻辑独立性.
• 视图能够对级秘书局提供安全保护.
• 适当利用视图可以更清晰的表达查询.