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

重点：SQL语言的特点、掌握SQL语言的关系表定义及更新操作、掌握SQL语言强大的查询功能、掌握SQL语言的关系表更新操作

难点：SQL语言正确完成复杂查询

3.1 SQL概述

3.1.1 SQL的特点

综合统一

集DDL、DML、DCL的功能与于一体，可以独立完成数据库生命周期中的全部活动。

高度非过程化

无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。

面向集合的操作方式

操作对象、查询结果、插入、删除、更新操作的对象可以是元组集合。

以同一种语法结构提供两种使用方式

可独立的用于联机交互的使用方式，用户可在终端键入SQL命令对数据库进行操作；SQL语句可嵌入到高级语言程序中，供程序员设计程序时使用。

语言简单，易学易用

核心功能只有9个动词。

3.1.2 SQL语言的基本概念

用户可以用SQL语言对视图和基本表进行查询等操作，在用户观点里，视图和表一样都是关系。

3.2 数据定义

3.2.1 定义、删除与修改基本表

定义基本表

create table table_name
(
<column_name> <datatype> [condition],
<column_name> <datatype> [condition],
<column_name> <datatype> [condition]
);

修改基本表

alter table <table_name>
    [add <new_column_name> <data_type> [condition]]
    [add condition]
    [drop <column_name>]
    [drop <condition>]
    [alter column <column_name> <data_type>]

删除基本表

drop table <talbe_name>
--删除一个表，及与该表相关的索引、视图、码和外部码。

3.2.2 建立与删除索引

建立索引是加快查询速度的有效手段，一个基本表上可建立一个或多个索引，以提供多种存取路径，加快查找速度。

建立索引

create [unique] [clustered] index <index_name>
on <table_name> (<column_name> [<order>]);
--对指定的表的列建立索引。
--unique代表索引值唯一。
--cluster表示索引是聚簇索引，指索引项的顺序与表中记录的物理顺序一致。

一个基本表最多只能建立一个聚簇索引，在更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。

删除索引

drop index <table_name.index_name>

3.3 查询

数据查询是数据库应用的核心功能。

select [all|distinct] <目标列表达式>
from <table_name>,[table_name]
[where <condition>]
[group by <column_name> [having <condition>]]
[order by <column_name> [asc|desc]];

select...from语句执行过程

1. from... table-->内存
2. where... 选取元组
3. group... 分组
4. having... 选择分组
5. select... 投影
6. [{union|...}] 查询结果的集合运算
7. order by... 排序输出

3.3.1 单表查询

选择表中的若干列

查询指定列

select <column_name>
from <table_name>

查询全部列

select * from <table_name>

查询经过计算的值

select <equation>

消除取值重复的行

select distinct <column_name>
from <table_name>

查询满足条件的元组

比较大小

select <column_name>
from <table_name>
where <column_name> {[not]+[<|<=|>|>=|=|<>|!=|!>|!<]};

确定范围

select <column_name>
from <tabel_name>
where <column_name> [not] between <value> to <value>

确定集合

select <column_name>
from <tabel_name>
where <column_name> [not] [in] (Domain)

字符匹配

[not] like '<匹配串>' [escape '<换码字符>']
--'%'表示任意长度字符串
--'_'表示单个任意字符
--'escape'\'' 表示\为换码字符，匹配串中紧跟在\后面的字符'%'或'_'，被定义为普通字符

涉及空值的查询

is [not] null

多重条件查询

<condition1> and <condition2>
<condition1> or <condition2>

对查询结果的排序

order by <column_name> {asc|desc}

使用集函数

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

对查询结果分组

group by <column_name>
[having <condition>]
--having 用于分组后按照一定条件对这些分组进行筛选。

3.3.2 连接查询

等值与非等值连接查询

--连接条件1
[<table_name>.]<column_name> <比较运算符>[<table_name>.]<column_name>
--连接条件2
[<table_name>.]<column_name> between [<table_name>.]<column_name> and [<table_name>.]<column_name>

自身连接

select [a].<column_name> , [b].<column_name>
from <table_name1> a, <table_name1> b
where a.<column_name> <比较运算符> b.<column_name>;

外连接

select [a|b]<column_name>,[a|b]<column_name>,[a|b]<column_name>
from [a|b] [right|left] join [a|b]
--方向介词左侧的表格位于方向介词右侧的方向介词侧。

复合条件连接

3.3.3 嵌套查询

带有in谓词的子查询

不相关子查询

带有比较运算符的子查询

内存查询返回的是单值时，可以用比较运算符；子查询要跟在比较符之后。

带有any或all谓词的子查询

[比较运算符] [all|any]

带有exists谓词的子查询

exists表示存在量词，带有exists的子查询不返回任何记录的数据，只返回逻辑值的‘true’和‘false’。

不相关子查询（Non-correlated Subquery）

子查询的查询条件不依赖于父查询的子查询。

如带in谓词的子查询

相关子查询（Correlated Subquery）

子查询的查询条件依赖于外层父查询的某个属性值的子查询。

带exists谓词的子查询为相关子查询。

执行过程：

在外层查询中取出表1的第一个元组，用内层规定的相关属性值处理内层查询，如果exists返回true值，则将该元组送入结果表中，如此反复，直到外层查询的元组遍历完毕。

示例代码：

select Sname,
from student
where exists(
    select *    /*不关心子查询具体内容，使用select * */
    from sc
    where sno=student.sno and cno='1'
)

3.3.4 集合查询

集合交集Union

--需求：查询选修了课程1或选修了课程2的学生
select sno
from sc
where cno='1'
union
select sno
from sc
where cno='2'

集合交集intersect

集合差集except

3.3.5 select语句的一般格式

SELECT [ALL|DISTINCT]<目标列表达式>[别名][,<目标列表达式>[别名]]……
       FROM <表名或视图名>[别名][,<表名或视图名>[别名]]……
       [WHERE <条件表达式>]
       [GROUP BY<列名1>[HAVING<条件表达式>]]
       [ORDER BY<列名2>[ASC|DESC]];

3.4 数据更新

3.4.1 插入数据

3.4.1.1 插入单个元组

insert into <表名> [(列名)]
value (<常量>)

插入一已知元组的所有列常量

insert into s
values('95005','cd','nan','18','cs');

插入一已知元组的部分列常量

insert into sc(sno,cno)
values('95001','1');

3.4.1.2 插入子查询结果

insert into <表名> [(列名)]
子查询;

3.4.2 修改数据

格式：

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

修改某一个元组的值

UPDATE Student
SET    Sage = 22
WHERE  Sno = '95001';

修改多个元组的值

UPDATE Student
SET    Sage = Sage+1;

带子查询的修改语句

UPDATE SC
SET    Grade = 0
WHERE  Sno IN
       (SELECT  Sno
        FROM    Student
        WHERE   Sdept ='CS');

3.4.3 删除数据

格式：

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

只能对某个元组操作，不能只删除某些属性上的值。

删除一个元组的值

delete 
from s
where sno='95005'

删除多个元组的值

delete from sc

带子查询的删除语句

DELETE 
FROM SC
WHERE  Sno  IN
       (SELECT Sno 
        FROM   Student
        WHERE  Sdept= 'CS');

更新操作与数据库的一致性

保证语句要么都做，要么都不做，以保证关系的完整性。

3.5 视图

视图时从一个或多个表中导出的表，视图是一个虚表，在数据库中只存放视图的定义。视图所对应的数据仍存放在原来的表中，随着表中数据的变化，视图的数据随之改变。对于视图的查询与基本表一致。对视图的更新将受到一定的限制。

3.5.1 定义视图

格式：

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

视图分类

行列子集视图

从单个基本表导出，保留基本表的码，但去掉其他的某些列和部分行的视图。

例子：

create view is_student
as
select sno,sname,ssex,sage
from student
where sdept='is'

建立视图的结果是把视图的定义存入数据字典，并不执行select语句。只在对视图查询时，才按其定义从基本表中将数据查出。

表达式视图

带虚拟列（经过各种计算派生出的数据所设置的派生属性列）的视图。

例子：

create view bt_s(sno,sname,sbirth)--虚拟列sbirth
as
select sno,sname,2006-sage
from student;

分组视图：

子查询目标带由组函数或子查询带有group by 子句的视图。

create view s_g(sno,gavg)
as
select sno,avg(grade)
from sc
group by sno;

删除视图

格式：

drop view <视图名>

3.5.2 查询视图

视图消解（view resolution）

​ 在对视图查询时，DBMS将进行有效性检查（表及视图）。若存在，则从数据字典中取出视图定义，并把定义中的子查询与用户查询结合起来转换为等价的对基本表的查询，然后再执行。

例子：

--在信息系学生的视图中查找年龄小于20的学生。
    SELECT  Sno，Sname
    FROM    IS_Student
    WHERE   Sage < 20;
--视图消解
    SELECT  Sno，Sname
    FROM    Student
    WHERE   Sage<20 AND Sdept= 'IS'; 

3.5.3 更新视图

更新视图即通过视图插入、删除和修改数据，实质上转换为对基本表的更新。

为防止用户对超出视图范围的基本表的数据进行操作，在定义视图时需要使用[with check option]来限制对视图的更新。

3.5.4 视图的作用

视图能够简化用户的操作

视图使用户能以多种角度看待同一数据

视图对重构数据库提供了一定程度的逻辑独立性

视图能够对机密数据提供安全保护

3.6 数据控制

数据控制功能包括了：数据库恢复、并发控制；数据库的安全性控制；数据库的完整性控制。

3.6.1 授权

格式：

grant <权限> [,<权限>]
[On <对象名>]
to <用户> [,<用户>]
[with grant option]--这句允许客户转授权限

数据库授权

grant create table
to <用户>
[with grant option]

建立表的权限属于DBA，可由DBA授权给普通用户，普通用户在拥有此权限后可建立基本表，基本表的属主拥有对该表的一切操作权限。

基本表授权

grant [基本表操作|all privileges]
on <表名>
to <用户> [,<用户>]
[with grant option]

视图授权

grant [视图操作|all privileges]
on <视图名>
to <用户> [,<用户>]
[with grant option]

列授权

grant [select|update] (<列名>)
on <表或视图>
to <用户>
[with grant option]

3.6.2 收回权限

格式：

revoke create table
from <用户>

revoke <权限>
[on <表名|视图名>]
from <用户>