数据库系统概论笔记（第三章）

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

---

1.SQL结构化查询语言：是非过程化语言，是关系数据库的标准语言

• 功能：

  • 数据定义：定义数据对象的组成与结构，如模式、表、视图、索引
  • 数据操纵：增删查改
  • 数据控制：安全性授权

• SQL具有两种使用方式：交互式SQL、嵌入式SQL

• SQL支持数据库三级模式结构：外模式对应视图和部分基本表、模式对应基本表、内模式对应存储文件（如索引）

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

3. 模式的定义 注：<>内为必填项，[]内为选填项。

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

例子：为用户wang定义一个“S-T”模式

create schema S-T authorization wang;

4. 模式的删除

drop schema <模式名> <cascade|restrict>;

• cascade：级联删除，会删除所有
• restrict：如果模式里定义了下属的数据库对象，则拒绝

5. 基本表的定义

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

例子：建立course表，cpno（先修课）属性外键，参照表course的cno属性

create table course (
    cno CHAR(4) primary key,
    cname CHAR(40) not null,
    cpno CHAR(4),
    foreign key(cpno) references course(cno) );

6. 修改基本表

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

7. 删除基本表

drop table <表名> [restrict|cascade];

默认：restrict

8. 索引

索引包括：

• B+树索引：B+树叶子节点为属性值和相应的元素指针
• 位图索引：二进制状态压缩
• 散列索引：哈希

索引的作用：

• 唯一索引保证每一行数据的唯一性
• 加快检索速度（主要原因）
• 加速表和表之间的连接
• 减少分组、排序的时间
• 查询过程中，使用优化隐藏器，提高系统性能

9. 建立索引

create [unique] [cluster] index <索引名>
on <表名> ( <列名> [<ASC|DESC>]  [, <列名> [<ASC|DESC>] ...);

ASC（默认）:升序**；DESC**：降序
unique：每一个索引值对应唯一的数据记录
cluster：建立的索引是簇集索引（相近的物理块，加快i/o）

10.删除索引加粗样式

drop index <索引名>;

11. 数据查询一般格式

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

12. 指定别名改变查询结果列标题

select sname NAME, 'a string' STRING, 2014-Sage BIRTHDAY, LOWER(sdept) DEPARTMENT
from student;

出现一个属性：STRING，每个查询结果在该分量的投影为‘a string’
LOWER：变小写

13. 消除取值重复的行
例子：查询所有学号

select distinct sno
from SC;

没有distinct则默认为all

14. between and
例子：查询年龄在20到23岁的学生姓名，年龄。包括20和23

select sname,sage
from Student
where sage between 20 and 23;

15. in
例子：查询系别为cs，se的学生姓名，性别

select sname,ssex
from Student
where sdept in ('cs', 'se');

16. 字符匹配：like
基本格式

[not] like '<匹配串>' [escape '<换码字符>' ]

匹配串没有通配符，和‘=’一样
通配符%：代表任意长度字符串
通配符_：代表任意单个字符
escape：将通配符转换为普通字符

例子：查询以‘DB_’开头，且倒数第三个字符为i的课程

select *
from course
where cname like 'DB\_%i__' escape '\' ;

17.order by 排序（默认为升序）
例子：查询选修3号课程的学生的学号和成绩，结果按照分数降序排列

select sno, grade
from sc
where cno='3'
group by grade DESC;

对于空值，排序现实的次序由具体系统实现决定。SQLSERVER为空值最小。

18. 聚集函数

count(*)
count( [distinct|all] <列名> )
sum( [distinct|all] <列名> )
avg( [distinct|all] <列名> )
max( [distinct|all] <列名> )
min( [distinct|all] <列名> )

除了count(*),其他都不处理空值
聚集函数只能用在select子句和group by中的having子句，where子句中不能用！

19. group by
分组后聚集函数将作用于每一个组

例子：求各课程选课人数

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

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

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

20. 外连接

select student.sno, sname, cno
from Student left outer join sc on (student.sno=sc.sno);

21. 带有in谓词的子查询
例子：查询与“刘晨”在同一个系的学生

select sno, sname, sdept
from student
where sdept in (
    select sdept
    from student
    where sname="刘晨" );

22. 带有比较运算符的子查询
例子：找出每个学生超过他自己选修课程平均成绩的课程好2

select sno, cno
from sc X
where grade > (
    select avg(grade)
    from sc Y
    where X.sno=Y.sno );

注意该查询的子查询和父查询是相关的，因此这个查询是相关子查询

23. 带有any或all谓词的子查询
any代表全部；all代表任意

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

select sname, sage
from student
where sdept<>'cs' AND
      sage<ANY (
            select sage
            from studen
            where sdept='cs' ) ;

24. 带有exists谓词的子查询
EXISTS代表存在量词∃，带有exists谓词的子查询不返回任何数据，只产生true/false

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

select sname
from student
where exists (
    select *
    from sc
    where sc.sno=student.sno AND
          sc.cno='1' );

25. 集合查询：并操作union、交操作intersect、差操作except
例子：查询cs系学生与年龄不大于19岁的学生的差集（也就是大于19岁的）

select * 
from student
where sdept='cs'
except
select * 
from student
where sageM<=19;

26. 基于派生表的查询
子查询可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象

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

select sno, cno
from sc, ( select sno, avg(grade)
           from sc
           group by sno
         ) as avg_sc(sno, avg_grade)
where sc.sno=avg_sc.sno AND
      sc.grade>avg_sc.abg_grade;

注意通过FROM子句生成派生表的时候，as可以省略，但是必须指定别名
子查询没有聚集函数的时候，派生表可以不指定属性列名

27. 插入数据
格式：

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

如果into子句没有指定任何的属性列名，则必须在表的每一个属性列都有值
指定了部分属性列，其他属性默认会为空值（定义没有NOT NULL约束）
常量与属性列的顺序一一对应，顺序可以不和表的定义顺序一样
字符串常数要用单引号括起来

28. 插入子查询
例子：对student表按系分组求平均年龄，再把系名和平均年龄存入dept_age表

insert 
into dept_age(sdept, avg_age)
select sdept, avg(sage)
from student
group by sdept;

29. 修改数据
格式：

update <表名>
set <列名>=<表达式> [, <列名>=<表达式> ] ...
[where <条件> ];

省略where子句，则表示修改所有元组

30. 带子查询的修改语句
例子：将cs系全体学生成绩置为0

update sc
set grade=0
where sno in (
    select sno
    from student
    where sdept='cs' );

31. 删除数据
格式：

delete
from <表名>
[where <条件> ];

删除的是表中的数据，而不是表的定义

32. 带子查询的删除语句
例子：将cs系全体学生选课记录删除

delete 
from sc
where sno in (
    select sno
    from student
    where sdept='cs' );

33.空值的处理
空值就是“不知道”，“无意义”的值
空值的约束条件：属性定义中有NOT NULL，则不能取空值，加了unique的属性不能取空值，码属性不能取空值
空值与另一个值的算术运算为空值；与另一个值的比较运算的结果为UNKNOWN。
有了UNKOWN后，逻辑运算变成了三值逻辑

34. 视图
视图是从一个或几个基本表（或视图）导出的表。是一个虚表
数据库只存放视图的定义，而不存放视图对应的数据。

作用：

• 简化用户操作
• 使用户以多角度看同一数据
• 对重构数据提供一定逻辑独立性
• 对机密数据提供安全保护
• 更清晰的表达查询

35. 定义视图
格式：

create view <视图名> [ (<列名> [,<列名>]... ) ]
as <子查询>
[with check option];

子查询可以是任意select语句
with check option 表示对视图进行update，insert，delete时要满足视图定义时的条件（子查询的条件）

36. 删除视图
格式：

drop view <视图名> [cascade]

cascade:把该视图和由它导出的视图一起删除

36. 查询，更新视图
语法同基本表。
由于视图不存放实际的数据，会转换成对基本表的查询、更新—>转换过程称为视图消解