温馨提示：本文是数据库系统工程师下午案例SQL部分的核心重点。每一个【】是一个知识点。
【触发器trigger】：用于复杂的业务规则或要求，预编译效率高，不能包含事务，可以调用procedure。
触发条件成立的时候，触发动作会'自动执行'，不能被调用;
trigger可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。
不能在临时表或系统表上创建trigger，但trigger可以引用临时表。
补：在一个表上最多只能建立6个触发器，缺点不能返回数据+破坏代码结构，维护困难。
create trigger 触发器名称 before/after --trigger在此操作前before还是后after触发
delete/insert/update of 列名--表中哪些行或列变动后激发trigger,delete删除行,insert插入行,update修改表中的值时激发(也可用update of 列名 指定是哪列的值被修改)
on 表名--哪个表上的行被delete insert或哪些列被update后激发trigger
referencing new row as nrow --referencing：trigger运行过程中，系统会生成两个临时视图，分别存放更新前和更新后的值
referencing old row as orow --对于行级触发器，为old row和new row; 对于语句级触发器，为old table和new table，默认old
for each row--表示为行级触发器，对每一个被影响的元组（即每一行）执行一次触发过程。
for each statement--表示为语句级触发器，对整个事件只执行一次触发过程，为默认方式。
when 触发条件--指明当什么条件满足时，执行下面的触发动作(余额<0,将余额置于0,并写入贷款表)
begin
触发动作--定义触发动作，即当触发条件满足时，需要数据库做什么
end;
alter trigger 触发器名称 before/after--修改触发器
delete/insert/update of 列名
on 表名--哪个表的行或列发生变动后激发触发器
as
begin
SQL语句
end;
drop trigger 触发器名;--删除触发器
【存储过程procedure】:可以包含事务，是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。
procedure中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
提高程序执行的效率:由于procedure在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快，可以直接通过存储过程的名称进行'调用'。
create procedure 存储过程名称 （in 参数1 数据类型，out 参数2 数据类型，in out 参数3 数据类型）--参数的数据类型只需要指明类型名即可，不需要指定'宽度'。具体宽度由外部调用者决定。
declare 局部变量 变量类型
as
begin
sql
Notfound →exit 若没有找到就退出循环
Exception →rollback 若遇异常就回滚
Commit/rollback--显示提交或回滚
Return 数字（默认0：成功 1：失败）--提示成功与否
end;
--in：为默认值，表示该参数为输入型参数，在过程体中值一般不变。
--out：表示该参数为输出参数，可以作为存储过程的输出结果，供外部调用者使用。
--in out：既可作为输入参数，也可作为输出参数。
Call 存储过程名称（参数1，参数3...）;--调用存储过程
Drop 存储过程名称;--调用存储过程
【游标cursor】：SQL面向集合，一条SQL可以处理一条或多条记录。而主语言是面向记录的，一组主变量一次只能放一条记录，所以引入游标cursor，通过移动cursor指针来决定获取哪一条记录。
exec sql declare 游标名 cursor for
select 语句;--定义游标cursor,说明性语句，其中的select语句并不执行
exec sql open 游标名;--打开游标cursor,执行游标定义中的SELECT语句，同时游标处于活动状态。cursor是一个指针，此时指向查询结果第一行之前。
exec sql fetch 游标名 into 变量表;--推进游标cursor,该语句执行时，游标推进一行，并把游标指向的行（称为当前行）中的值取出，送到共享变量中。
exec sql close 游标名;--关闭游标cursor,该语句关闭游标，使它不再和查询结果相联系。游标关闭后，后面还可以再打开。
【索引index】：一个表中一列或多列的值的集合,以及指向表中物理标识这些值的数据页的逻辑指针清单。
index作用：减少'查询'时间，提高'查询'效率和系统性能
一个表只能有一个cluster index,可以有多个非聚簇索引
create unique/cluster index 索引名
on 表名(属性名1 asc/desc,属性名2 asc/desc);
--unique 此索引的每一个索引项只对应唯一的一条数据记录。
--cluster 聚簇索引：索引项的顺序与表中记录的物理顺序一致的索引。
drop index 索引名; --删除索引
【视图view】：是从一个或几个基本表（或视图）导出的'虚表'，数据是随着基表的更新而更新。
数据库只存放view的定义，而不存放view对应的数据，这些数据仍存放在原来的基本表中。
view作用：
①简化了操作，把经常使用的数据定义为视图
②安全性，用户只能查询和修改能看到的数据
③逻辑上的独立性，屏蔽了真实表的结构带来的影响
***更新view
①从多个表得出的视图不允许更新
②使用了分组、聚集函数的视图不允许更新
③从单个表通过投影选择操作得出的视图允许更新
create view 视图名(列表名)--组成视图的属性列名全部省略或者全部指定。如果省略属性列名，则隐含该视图由select子查询目标列的主属性组成
as select 查询语句--子查询可以是任意复杂的select语句，但通常不允许含有order by子句和distinct短语
with check option;--表示对update,insert,delete操作时保证更新、插入或删除的行必须满足视图定义中的谓词条件（即子查询中的条件表达式）
drop view 视图名; --删除视图
【授权grant】:
grant select/insert/update/delete/alter/index/createtab/all privileges--all privileges 所有权限
on table/database
to user/public--public 将权限授予所有人
with grant option;--表示获得授权的人还可以把权限赋予其他用户
【收回权限revoke】
revoke select/insert/update/delete/alter/index/createtab/all privileges
on table/database
from user/public
restrict/cascade;
--restrict 只收回指定的用户的权限
--cascade 收回指定用户的权限以及该用户赋予的其他用户的权限（级联收回）
【创建表create table】
create table 表名(属性1 数据类型 primary key,--主键= not null unique
属性3 数据类型 check(限制条件),--例：CHECK (Sex='男' OR Sex='女')
属性2 数据类型 references 被参照的表名（被参照的属性名）--外键来自哪个表哪个属性
on delete cascade--表示删除被参照关系的元组时，同时删除参照关系的元组
on delete set null--表示删除被参照关系的元组时，参照关系的元组取null值
);
1.（主键）实体完整性约束
*只有一个主键，则在定义主键属性的最后加primary key = not null unique
*有多个主键，在创建表的最后加primary key(属性1，属性2)
2.（外键）参照完整性约束
*若在列级表示外键： 参照的属性后面加 references 被参照的表名（被参照的属性名）
*若在表级表示多个外键，在创建表的最后加foreign key（当前创建的属性） references 被参照的表名（被参照的属性名） 有几个外键写几行
3.用户自定义约束（属性值上的约束）
*null 为空;not null 非空
*unique 唯一，可以在多个属性上用unique，可写在属性后，也可以写在表级约束
*not null unique 取值唯一且不为空=primary key
*check 限制列中的取值范围。例：CHECK (Sex='男' OR Sex='女')，CHECK (余额>=0)，CHECK (年龄>=18 AND 年龄<=60)，check(sex in ('男','女'))
【修改表alter table】
alter table 表名
[add <新列名> <数据类型> [列级完整性约束条件]]--新增一列属性
[drop <完整性约束名>]--删除主键
[modify <列名> <数据类型> ]);--修改某属性的数据类型=change
例：
alter table S zap char(8);--修改s表的zap字段固定长度为8
alter table S modify sta int;--修改sta属性的数据类型为int
alter table S add constraint c_cno check(条件);--新增check约束
drop table 表名;--删除表
【查询语句】
select all/distinct <目标列表达式>
from <表名或视图名> --表若重命名，引用时一定要用新的名称
where 条件 --条件范围对应整个表
group by 列名 having 条件 --条件范围对应分组后的数据
--使用了聚集函数时，一般都要group by分组
--having限制条件，去掉不满足having后条件的分组。
order by 列名 asc/desc;--默认asc 升序 desc 降序
--where 约束声明：结果返回之前执行，不能使用聚合函数
--having 过滤声明：必须跟在group by后，对返回结果过滤，可以使用聚合函数
in 在集合中= or; not in 不在集合中 【where后条件语句中】之前有指定的具体的属性名，之后的子查询结果不是all，是具体的属性名
exists 存在; not exists 不存在 【where后条件语句中】之前没有指定的属性名，之后的子查询结果是可以是all
【字符串操作】like '_database' _代表一个字符 not like
【字符串操作】like '%database' %代表任意多个字符
【转义字符escape】为了使模式中包含特殊字符，即%和_，允许使用escape关键词来定义转义符，like语句后面紧 跟escape '\'表明转义符'\' 后面的特殊字符为普通字符。
like 'ab\%cd%' escape '\';--匹配所有以ab%cd开头的字符串
is null 为空; is not null 非空
Default 默认值：default null默认为空；或者接在not null之后，例：not null default '60'
count ([distinct | all]*) 统计元组个数:有几行
Count (*) 统计行数，null也算在内
Count (列名) 统计某个值的个数，null不算在内
sum ([distinct | all]*) 计算某一列的总和
avg ([distinct | all]*) 计算某一列的平均值
max ([distinct | all]*) 计算某列的最大值
min ([distinct | all]*) 计算某列的最小值
有聚集函数就要用group by，group by 后跟未使用聚集函数的列名
【集合操作】并交叉：要求两个关系模式一致（属性名称、个数等）。
union 合并两个查询结果且去掉重复行
union all 合并两个查询结果不去掉重复行
intersect 既在查询结果1中，也在查询结果2中
except 在查询结果1中，不在查询结果2中
【连接】跟在from后，连接两个表。
左外连接：...from 主表A left (outer) join 副表B on 条件;--查询结果以左表A为准，左表A在右表B中没有的部分填充null
右外连接：...from 副表A right (outer) join 主表B on 条件;--查询结果以右表B为准，右表B在左表A中没有的部分填充null
全连接：...from A full (outer) join B on 条件;--查询结果把两个表在对方表中没有的部分都填充null
内连接： ...from A (inner) join B on 条件;--自连接，自己连接自己
【插入数据insert into】
insert into 表名 values（值1，值2，值3...）;--insert into不指名哪列，所以需values要把所有列的值都加进去
insert into 表名（列名1，列名2...） values (列名1的值,列名2的值...);--指名了要插入的列，values跟对应列的值
【删除delete】
delete from 表名 where ...;--删除符合where后条件的行
delete from 表名;--删除表中所有行，不删除表的关系模式结构等
drop from 表名;--drop删除所有内容，包括表的关系模式结构等
【更新update】
update 表名 set 列名='要修改为的新值' where 条件;
--把表中符合where条件的行对应的列update为要修改为的值
row 行 column 字段（列） 查看所有表：show tables 查看表结构：desc 表名
跟在where后的条件中X Between 1 and 10等价于X>=1 and X<=10
Tips:假设A，B两个表，表A中有属性参照了表B的属性。
1、先更新/删除参照的表A，再更新/删除被参照的表B
2、插入先插入被参照的表B，再插入参照的表A
扫一扫
9
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
