概述

SQL的基本组成：

• 数据定义语言
• 数据操纵语言
• 事务控制
• 嵌入式和动态sql
• 完整性
• 权限管理

表操作

表创建

1、实体完整性约束

• 列后面PRIMARY KEY
• 最后加PRIMARY KEY(属性名1，属性名2)

2、参照完整性约束

• 列后面加 References 表名（属性名）
• 在最后加，有几个外码，就写几行： foreign key(属性名) references 表名（属性名）
• 另外，可选参数：[on delete[cascade|set null]]] 。表示删除被参照的元组时，前者会删除参照元组；后者会将对应属性设为空

3、属性值约束

• null / not null
• unique
• not null unique 与primary key可互换（not null unique 和 primary key指定一个就可以了，不能同时指定）
• check 限制列中的取值范围，如check (jyje >=0), check(col1 > col2)

表修改/删除

alter table xxx add/drop/modify

drop table xxx

索引

索引位于内模式（物理层面）。

create [unique][cluster] index xxx
on table(col1[次序(默认asc｜desc)],col2[次序])

cluster：聚簇索引，索引项的顺序与表中记录的物理顺序一致的索引组织

视图

create view 视图名(列表名)
as select 查询子句
[with check option]

规则：

• 子查询可以是复杂的select语句，但不允许含有order by和distinct
• with check option 增删更新时保证操作行满足视图定义的条件

数据操作

select

select [all|distinct] 目标列表达式
from 表名或视图名,表名或视图名
where 条件表达式
group by col1 [having 条件表达式]
order by col2 [asc|desc]

注意：as可以给列和表名重命名，as可省略

子查询（嵌套查询）

• in / not in （前面加属性名）
• exists / not exists （前面不加属性名）(关注的是子查询是否成立，而不是子查询的查询结果)

例如：

select cno, cname from c
where not exists (select * from sc where sc.cno = c.cno)

聚集函数

count([distinct|all] *)
min
max
avg
any 任何一个即可
all
...

分组查询 group by

如查询每个学生的平均成绩：

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

分组查询的条件：having 子句

字符串操作

like进行匹配，%匹配任意长度字符串，_匹配任意一个字符

注意：如果模式中包含特殊字符（如%, _, \），使用escape进行转义： like 'ab\%cd%' secape'\'

集合操作

• union or union all (不去重复行)
• intersect 交
• except 差

外连接

• left join
• right join
• full join 全外连接（左和右连接做并集）

insert into

insert into table values (...)
insert into table (col1,col2) values (val, val2)

delete

delete vs. drop : 前者只删除数据，关系模式仍然存在

update

update table set col=new_val where ...

授权与触发器

授权grant

grant 权限 on table/database/view 表名/数据库名/视图名
to user1, user2 / public(所有人)
[with grant option] （表示权限给A后，A可以再把这个权限给其他人）

所有权限：all privileges

收回权限revoke

revoke 权限 on ...
from user1, ... / public
[restrict | cascade]

restrict: 只收回指定的用户的权限； cascade：还收回该用户赋予的其他用户的权限

触发器

特点：

• 当指定的触发条件满足时（比如插入删除更新），则激活触发器。
• 触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器
• 不能在临时表或系统表上创建触发器，但触发器可以引用临时表

需要指定：

• 触发器名称
• 表名
• 触发事件（不能是查询 ）
• 触发条件
• 触发动作

语句：

create trigger trigger_name [before|after]
[delete | insert | update of col_name] -- 触发事件
on table_name
[referencing 临时视图名]
[for each row | for each statement（默认方式）]
[when <触发条件>]
begin
	触发动作
end [触发器名称]

规则：触发事件发生，触发器被激活。检查触发条件，若满足，则执行触发动作。否则，啥都不做。

嵌入式sql与存储过程

嵌入式sql

定义：将sql语句嵌入到某种高级语言中的方式，通常采用预编译的方法 EXEC SQL <SQL语言>

向主语言传递SQL语句执行状态信息的方式： SQLCA，即SQL通信区，系统默认的全局变量

主变量（共享变量）：主语言通过主变量向sql语句提供参数，用DECLARE说明。在sql中，为了和属性名区分，需要在前面加: (有的可能是@)

游标

定义游标：

EXEC SQL DECLARE name CURSOR FOR
<select语句>

这只是说明性语句，此时select并不执行

打开游标：

exec sql open name;

游标可以理解为指针

推进游标：

exec sql fetch name into value_name;

游标推进一行，将游标指向的行的值取出，送到共享变量中

关闭游标：

exec sql close name;

存储过程（procedure）

定义：是一组为了完成特定功能的sql语句集合，经编译后存储在数据库中，用户指定存储过程的名称并给出参数来执行。类似函数。可以接受输入和输出参数。执行比单个的sql语句块更快。需要注意：没有共享变量的概念。

create procedure procedure_name (in|out|in out para1 type1, ...)
[as]
begin
	<SQL语句>
end

参数类型：

• in 输入型
• out 输出型
• in out 既可作为输入，也可作为输出

补充知识

• 触发器内部不能以事务程序方式编写，存储过程可以
• 针对复杂的约束，应采用触发器来实现
• 嵌入式SQL，引入指示变量来解决主语言无空值的问题