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

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

3.1 SQL概述

3.1.1 SQL的产生与发展

SQL（Structrued Query Language）结构化查询语言

3.1.2 SQL的特点

1. 综合统一（集DDL、DML、DCL功能于一体）
2. 高度非过程化
3. 面向集合的操作方式
4. 以同一种语法结构提供多种使用方式
5. 语言简洁，易学易用

SQL功能	动词
数据查询	select
数据定义	create\drop\alert
数据操纵	insert\update\delete
数据控制	grant\revoke

3.1.3 SQL的基本概念

• SQL支持关系数据库三级模式结构

1. 基本表（<->模式）：本身独立存在的表
2. 存储文件（<->内模式）
3. 视图（<->外模式）：虚表，从一个或几个基本表导出的表

3.2 学生－课程数据库

3.3 数据定义

	创建	删除	修改
模式	create schema	drop schema
表	create table	drop table	alert table
视图	create view	drop view
索引	create index	drop index	alert index

3.3.1 模式的定义与删除

1. 定义模式：

create schema <模式名> authorization <用户名>

• 模式名可省略，默认为用户名
• 在create schema中可以接受create table, create view和create grant子句
• 执行创建模式语句必须具有DBA权限，或者DBA授予在create schema的权限

2. 删除模式：

drop scema <模式名> <cascade|restrict>

• cascade：级联，restrict：限制（拒绝执行）

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

1. 定义基本表：

create table <表名>(
    <列名><数据类型>[<列级完整性约束条件>]
    [,<列名><数据类型>[<列级完整性约束条件>]]...
    [,<表级完整性约束条件>]
);
create table student(
    Sno char(9) primary key,
    Sname char(20) unique,
    Ssex char(2),
    Sage smallint,
    Sdept char(20),
);

create table course(
    Cno char(4) primary key,
    Cname char(40),
    Cpno char(4),
    Ccredit smallint,
    foreign key(Cpno) references course(Cno);
);

create table SC(
    Sno char(9),
    Cno char(4),
    Grade smallint,
    primary key(Sno,Cno),/*表级完整性*/
    foreign key(Sno) references student(Sno),
    foreign key(Cno) references course(Cno)
)

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

3. 模式与表
   每一个基本表都属于某一个模式，一个模式包含多个基本表。若没有指定模式，系统会根据搜索路径来确定该对象所属的模式
   ①显示当前的搜索路径：show search_path
   ②搜索路径的当前默认值：$user, public
   ③DBA用户可以设置搜索路径：set search_path to "S-T", public;
   1. 创建基本表：

/*1. 创建表时给出模式名*/
create table "S-T" .student(...);
create table "S-T" .course(...);
create table "S-T" .SC(...);
/*2. 在创建模式语句中同时创建表*/
create schema test authorization zhang
create table tab1(col1 smallint,
                 col2 int,
                 col3 char(20),
                 col4 numeric(10,3),
                 col5 decimal(5,2)
                 );
/*3. 设置所属模式，在创建表名中不必给出模式名*/

－
2. 修改基本表

alter table <表名>
[add [column]<新列名><数据类型>[完整性约束]]
[add <表级完整性约束>]
[drop [column]<列名>[cascade|restrict]]
[drop constraint<完整性约束名>[restrict|cascade]]  /*删除完整性约束*/
[alter column<列名>(type)<数据类型>]

－
3. 删除基本表

drop table<表名>[restrict|cascade]

删除student表，若表上建有视图，选择restrict时表不能删除，选择cascade时可以删除表，视图也自动被删除。

3.3.3 索引的建立与删除

建立索引的目的：加快查询速度
谁可以建立索引：DBA或建表人
RDBMS中索引一般采用B+树、hash索引来实现
索引是关系数据库内部实现技术，属于内模式的范畴
create index语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引

1. 建立索引
   语句格式：
   create [unique][cluster] index<索引名>on<表名>(<列名>[<次序>][,<列名>[<次序>]]...)

• ASC：升序，DESC：降序
• cluster表示要建立的索引是聚簇索引。聚簇索引是指索引顺序与表中记录的物理顺序一致的索引组织。
• 在最经常查询的列上建立聚簇索引以提高查询效率
• 一个基本表上最多只能建立一个聚簇索引
• 经常更新的列不宜建立聚簇索引

2. 删除索引
   语句格式：
   drop index <索引名>

• 删除索引时，系统会从数据字典中删去有关该索引的描述

3.3.4数据字典

数据字典是关系数据库管理系统内部的一组系统表，记录了所有定义信息、各类用户对数据库的权限、统计信息等。
RDBMS执行SQL数据定义时，实际就是更新数据字典。

3.4 数据查询

语句格式：

select [all|distinct]<目标列表达式>[,<目标列表达式>]...
from<表名或视图名>[,<表名或视图名>]...
[where<条件表达式>]
[group by<列名1>[having<条件表达式>]]
[order by<列名2>[asc|desc]];

3.4.1 单表查询

1. 选择表中的若干列

• 查询指定列
  select sno,sname from student;
• 查询全部列
  select * from student;
• 查询经过计算的值

select sname, 2004-sage from student;
select sname name, 'year of birth' birth, 2004-sage birthday, lower(sdept) department from student;    /*含列别名*/

2. 选择表中的若干元组

• 消除取值重复的行
  select distinct sno from sc
• 查询满足条件的元组（在where 子句中）

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<;not+上述比较运算符
确定范围	between and, not between and
确定集合	in, not in
字符匹配	like, not like
空值	is null, is not null
多重条件（逻辑运算）	and, or, not
①比较大小

select sname from student where sdept=='cs';
select sname,sage from student where sage<20;
select distinct sno from sc where grade<60;

②确定范围

select sname, sdept, sage from student where sage between 20 and 23;
/*注：where sage between 20 and 23 等同于 where sage >=20 and sage<=23*/

③确定集合

select sname, ssex from student where in('is','ma','cs');

④字符匹配

select * from student where sno like '200215121';
/*含通配符的字符串*/
select sname, sno, ssex from student where sname like '刘%';
select sname, sno from student where sname like '欧阳_';
select snmae, sno from student where sname like '_阳%';
select sname, sno, ssex from student where sname not like '刘%';
/*使用换码字符将通配符转移为普通字符*/
select cno,ccredit from course where cname like 'DB\_Design' escape '\' ;
select * from course where cname like 'DB\_%i__' escape '\';

⑤涉及空值的查询

/*is不能用=代替*/
select sco, cno from sc where grade is null;

⑥多重条件查询
用and和or联结多个查询条件
3. order by子句
升序：ASC（默认）
降序：DESC
当排序列含空值时：空值默认为最大值

select sno, grade from sc where cno=3 order by grade desc;
select * from student order by sdept asc, sage desc;

4. 聚集函数

count([distinct|all] *)        /*统计元组个数*/
count([distinct|all] <列名>)   /*统计一列中值的个数*/
sum([distinct|all] <列名>)     /*计算一列值的总和，必须为数值型*/
avg([distinct|all] <列名>)     /*计算一列值的平均值，必须为数值型*/
max([distinct|all] <列名>)     /*求一列值的最大值*/
min([distinct|all] <列名>)     /*求一列值的最小值*/

select count(distinct sno) from sc;
select sum(ccredict) from sc,course where sno='200215012' and sc.cno==course.cno;
select avg(grade) from sc where cno=1;
select max(grade) from sc where cno=1;

5. group by子句
   作用：按指定的一列或多列值分组，值相等的为一组，来细化聚集函数的作用对象。
   对查询结果分组后，聚集函数将分别作用于每个组
   select cno,count(sno) from sc group by cno;

• group by子句分组后，可以使用having短语指定筛选条件

select sno,count(cno) from sc group by sno having count(cno)>3;

• having短语与where子句的区别：
  ①作用对象不同
  ②where子句中不能用聚集函数作为条件表达式

3.4.2 连接查询

1. 等值与非等值连接查询
   连接查询的where子句中用来连接两个表的条件称为连接条件或连接谓词。
   当连接运算符为‘=’称为等值连接，其他运算符为非等值连接。

select student.*, sc.* from student,sc where student.sno=sc.sno;

2. 自身连接

• 需要给表、属性起别名以示区别

select first.cno, second.cpno from course first, course second where first.cpno=second.cno;

3. 外连接
   以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。

• 左外连接：列出左边关系中所有的元组left out join sc on
• 右外连接：列出右边关系中所有的元组right out join sc on

select student.sno,sname,ssex,cno,grade from student left out join sc on(student.sno=sc.sno);

4. 多表连接
   连接操作是两个以上的表进行连接

select student.sno,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno;

3.4.3 嵌套查询

一个select-from-where语句称为一个查询块。
定义：将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询。

• 外层查询（父查询）、内层查询（子查询）
• 子查询中不能使用order by子句

select sname from student where sno in(
    select sno from sc where cno='2'
);

1. 带有in谓词的子查询
   在嵌套查询中，子查询的结果往往是个集合，用in谓词表示父查询的条件在子查询结果的集合中。

• 例：查询与刘晨在同一个系学习的学生

/*法一*/
select sno,sname,sdept from student where sdept in(
    select sdept from student where sname='刘晨'
);
/*法二*/
select second.sno,second.sname,second.sdept from student first, student second where first.sname='刘晨' and first.sedept=second.sdept;
/*其他例子*/
select sno,sname from student where sno in(
    select sno,cno from sc where cno in(
        select cno,cname from course where cname='信息系统'
    ) 
);
select sno,sname from student,sc,course where course='信息系统' and sc.cno=course.cno and student.sno=sc.sno;

说明：

• 不相关子查询：子查询的查询条件不依赖于父查询
• 相关子查询：子查询的查询条件依赖于父查询整个查询语句称为嵌套查询

2. 带有比较运算符的子查询
   当能确切知道内层查询返回单值时，可用比较运算符。

select sno,snamae,sdept from student where sdept=(
    select sdept from student where sname='刘晨'
);
select sno,cno from sc x where grade >= (
    select avg(grade) from sc y where x.sno=y.sno;
)

3. 带有any(some)或all谓词的子查询
   谓词语义：any——任意一个值，all——所有值

• !=(或<>)any：不等于子查询结果中的某个值
• 例：查询非计算机科学系中比计算机科学任意一个学生年龄小的学生姓名和年龄

select sname,sage from student where sage < any(
    select sage from student where sdept='cs'
) and sdept !='cs';

/*使用聚集函数*/
select sname,sage from student where sage<(
    select max(sage) from student where sdept='cs'
) and sdept!='cs';

• 用聚集函数实现子查询要比直接用any\all效率更高
  等价关系：

	=	<>或!=	<	<=	>	>=
any	in		<max	<=max	>min	>=min
all		not in	<min	<=min	>max	>=max

4. 带有exists谓词的子查询
   （1）exists谓词
   代表存在量词$\exists$，带有exists谓词的子查询值返回逻辑真值true或逻辑假值false。

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

/*连接查询*/
select sname from student,sc where cno='1' and sc.sno=student.sno;
/*嵌套查询*/
select sname from student where exists(
    select * from sc where cno='1',student.sno=sc.sno
)

（2）说明

• 使用存在量词exists后，若内层查询结果非空，则外层的where子句返回真值，否则返回假值
• 由exists引出的子查询目标列表都用*，因为带exists的子查询只返回真值或假值，给出列名无实际意义
  （3）不同形式的查询间的替换
• 一些带exists或not exists谓词的子查询不能被其他形式的子查询等价替换
• 所有带in谓词、比较运算符、any和all谓词的子查询都能用带exists谓词的子查询等价替换。

select sname,sno,sdept from student s1 where exists(
    select * from student s2 where s1.sdept=s2.sdept and s2.sname='刘晨'
);

（4）用exists/not exists实现全称量词
sql中没有全称量词$\forall$，可以将带有全称量词的谓词转换为等价的带有存在量词的谓词。

• 例：查询选修了全部课程的学生姓名

select sname from student where not exists(
    select * from course where not exists(
        select * from sc where student.sno=sc.sno and cno=course.cno
    )
);

（5）用exists/not exists实现逻辑蕴涵
sql中没有全称蕴涵逻辑运算(implication)，可以将谓词演算转换为等价的逻辑蕴涵谓词。

• 例：查询至少选修了学生20125122选修的全部课程的学生学号
  等价于：不存在这样的课程y，学生201215122选修了y，而学生x没有选。

select sno from student st1 where not exists(
    select * from sc sc1 where sc1.sno='201215122' and not exists(
        select * from sc sc2 where sc2.cno=st1.cno and sc2.sno=st1.sno
    )   
);

3.4.4 集合查询

集合操作的种类：并操作、交操作、差操作

1. 并操作union
   union：将多个查询结果合并起来，系统自动去掉重复元素
   union all：将多个查询结果合并起来时，保留重复元素
   例：查询计算机科学系的学生及年龄不大于19岁的学生

/*法一*/
select * from Student where Sdept='cs'
union select * from Student where Sage<=19;
/*法二*/
select distinct * from Student where Sdept='cs' or Sage<=19;

2. 交操作intersect
   例：查询计算机科学系的学生与年龄不大于19岁的学生的交集

/*法一*/
select * from Student where Sdept='cs'
intersect select * from Student where Sage<=19;
/*法二*/
select * from Student where Sdept='cs' and Sage<=19;

例：查询选修课程1的学生集合与选修课程2的学生集合的交集

/*法一*/
select * from SC where cno=1
intersect select * from SC where cno=2;
/*法二*/
select * from SC where cno=1 and sno in(
    select sno from SC where sno=2
);

3. 差操作except
   例：查询计算机科学系的学生与年龄不大于19岁的学生的差集

/*法一*/
select * from Student where Sdept='cs'
except select * from Student where Sage<=19;
/*法二*/
select * from Student where Sdept='cs' and Sage>19;

• 说明：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

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.sno and sc.grade>=avg_sc.grade;

• 如果子查询中没有聚集函数，派生表可以不指定属性列，子查询select子句后面的列名为其默认属性。
• 通过from子句生成派生表时，as关键字可以省略，但必须为派生关系指定一个别名。而对于基本表，别名是可选项。

3.4.6 select 语句的一般格式

select [all|distinct] <目标列表达式>[别名][,<目标列表达式>[别名]...]
from <表名或视图名>[别名][,<表名或视图名>[别名]...]
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [asc|desc]]

3.5 数据更新

3.5.1 插入数据

1. 插入元组
   语句格式：
   insert into <表名>[(<属性列1>[,<属性列2>]...)] values (<常量1>[,<常量2>...]);
   功能：将新元组插入指定表中。

• into子句：顺序可以和表中顺序不一样，没有指定属性列的默认插入全部。
• values子句：提供的值必须与into子句匹配。

/*RDBMS在grade列上自动赋空值*/
insert into sc values('200215128','1',null);

2. 插入子查询结果
   语句格式：
   insert into <表名> [(<属性列1>[,<属性列2>]...)] 子查询；

• select子句目标列必须与into子句匹配，值的个数、类型都要一致
  例：对每一个系，求学生的平均年龄，并把结果存入数据库。

insert into Dept_age(Sdept,svg_age) select sdept,avg(age) from Student group by Sdept;

3.5.2 修改数据

语句格式：
update <表名> set <列名>=<表达式>[,<列名>=<表达式>]...[where <条件>];
功能：修改指定表中满足where子句条件的元组

• set子句：指定修改方式，修改的列，修改后取值
• where子句：指定要修改的元组，缺省表示修改所有元组
• 在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则

1. 修改某一个元组
   例：将学生202215121的年龄改为22岁
   update Student set sage=22 where sno='200215121;
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 <条件>];
功能：删除指定表中满足where子句条件的元组

• where子句：指定要删除的元组，缺省表示要删除表中的全部元组，表的定义仍在。

1. 删除某一个元组的值
   delete from Student where Sno='200215128'
2. 删除多个元组的值
   delete from sc;
3. 带子查询的删除语句

delete from sc where sno in(
    select sno from Student where Sdept='cs'
);

3.6 空值的处理

空值的存在是因为取值有不确定性，对关系运算带来特殊的问题，所以需要做特殊的处理。
SQL语言中允许某些元组的某些属性取空值，一般有以下几种情况：
①该属性有值，但当前不知道他的具体值
②该属性不应该有值
③由于某种原因不便于填写

1. 空值的产生
   insert into sc(sno,cno,grade) values ('200215128','1',null);
2. 空值的判断
   用 is null或is notnull来表示
   select * from Student where Sname is null or Ssez is null or Sage is null;
3. 空值的约束条件
   ①属性定义（或者域定义）中有not null约束条件的不能取空值
   ②加了unique限制的属性不能取空值
   ③码属性不能取空值
4. 空值的算术运算、比较运算符和逻辑运算
   算术运算：空值与另一个值（包括另一个空值）的算术运算的结果为空值；
   比较运算：空值与另一个值（包括另一个空值）的比较运算的结果为unknown；
   逻辑运算：

XY	X and Y	X or Y	not X
TT	T	T	F
TU	U	T	F
TF	F	T	F
UT	U	T	U
UU	U	U	U
UF	F	U	U
FT	F	T	T
FU	F	U	T
FF	F	F	T
例：查询选修1号课程的不及格的学生以及缺考的学生

select sno from SC where grade<60 and Cno='1'
union select sno from SC where grade is null and Cno='1';
/*或*/
select sno from SC where Cno='1' and (grade is null or grade<60);

3.7 视图

1. 试图的特点
   1. 视图是虚表，是从一个或几个基本表（或视图）导出的
   2. 只存放视图的定义，不存放视图对应的数据
   3. 表中的数据发生变化，视图中查询出的数据也随之发生变化
2. 基于视图的操作：查询、删除、受限更新、定义基于该视图的新视图

3.7.1 定义视图

1. 建立视图
   （1）语句格式：
   create view<视图名>[(<列名>[, <列名>]...)] as <子查询> [with check option];
   说明：
   1. 组成视图的属性列名：全部省略或全部指定
   2. 子查询不允许有order by子句和distinct短语
   3. RDBMS执行create view语句时只是把视图定义存入数据字典，并不执行其中的select语句
   4. 在对视图查询时，按视图的定义从基本表中将数据查出
      例：建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生

create view is_Student as select Sno,Sname, Sage from Student where Sdept='is' with check option;

加入with check option时，is_Student视图的自动更新：

• 修改操作：自动加上Sdept='is'的条件
• 删除操作：自动加上Sdept='is'的条件
• 插入操作：自动检查Sdept属性值是否为’is’，如果不是，则拒绝该插入操作，如果没有提供Sdept值，则自动定义Sdept为’is’
  （2）基于多个基表的视图
  例：建立信息系选修了1号课程的学生视图

create view is_s1(sno,sname,grade) as select Student.sno,Student.sname,SC.grade from Student,SC where cno='1' and Sdept='is' and Student.Sno=SC.Sno;

（3）基于视图的视图
例：建立信息系选修了1号课程且成绩在90分以上的学生的视图。

create view is_s2 as select sno,sname,grade from is_s1 where grade>90;

（4）带表达式的视图
例：定义一个反映学生出生年份的视图

create view bt_s(Sno,Sname,Sbirth) as select Sno,Sname,2000-Sage from Student;

（5）分组视图
例：将学生的学号及他的平均成绩定义为一个视图

create view S_G(Sno,grade) as select Sno, AVG(grade) from SC group by Sno;

（6）不指定属性列
例：将Student表中所有女生记录定义为一个视图

create view F_Student(F_sno,name,sex,age,dept) as select * from Student where Ssex='female';

• F_Student和Student的属性列需要一一对应，修改基表Student的结构后，Student与F_Student 视图的映象关系被破坏，导致该视图不能正确工作。

2. 删除视图
   语句格式：
   drop view<视图名>[cascade];

• 该语句从数据字典中删除指定的视图定义
• 如果该视图上还导出了其他视图，使用cascade级联删除语句，把该视图和由它导出的所有视图一起删除。
• 删除基表时，由该基表导出的所有视图定义都必须显式地使用drop view语句删除。

3.7.2 查询视图

视图定义后，用户可以像基本表一样对视图进行查询。

1. RDBMS实现视图查询的方法——视图消解法
   第一步：进行有效性检查
   第二步：转换成等价的对基本表的查询
   第三步：执行修正后的查询
2. 视图消解法的局限
   例：在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

select * from S_G where grade>90;
/*转换后：where子句中不能有聚集函数*/
select Sno,avg(grade) from SC where avg(grade)>=90 group by Sno;
/*正确的转换*/
select Sno,avg(grade) from SC group by Sno having avg(grade)>=90;

3.7.3 更新视图

更新视图是指通过视图来插入、删除数据，因为视图不适宜存储数据，因此对视图的更新操作将通过视图消解，转化为对实际表的更新操作。

• 为防止在更新视图时出错，定义视图时要加上with check option子句
  例：将信息系学生视图Is_Student中学号200215122的学生姓名改为“刘辰”

update IS_Student set Sname='刘辰' where sno='200215122;

• 更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换为对相应基本表的更新。
  例：对视图S_G中学号为200215121学生的平均成绩改为90分

create S_G(Sno,Gavg) as select Sno,avg(grade) from SC group by Sno;
update S_G Gavg=90 where Sno='200215122';
/*平均成绩是计算得出的，系统无法修改各科成绩，是平均成绩到90*/

3.7.4 视图的作用

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