本文详细介绍了SQL中的触发器、存储过程、游标、索引、视图等关键概念，包括它们的用途、语法和注意事项，以及如何管理和操作数据库中的数据和权限。
温馨提示：点击右侧目录即可快速跳转对应章节→→→→→→
前言：What is SQL? (帮助理解)
如果把database数据库比作盘子(想象一下你家有不同尺寸的盘子，因此有很多数据库产品)，那data数据就是盘子里的菜，结构化查询语言Structured Query Language，简称SQL，则是吃饭时用来夹菜的筷子。
一个适合小白学SQL的网站：xuesql.cn
http://www.xuesql.cn/
【1、创建表create table】
create table 表名(属性1 数据类型 primary key,--主键= not null unique
属性2 数据类型 check(限制条件),--例：CHECK (Sex='男' OR Sex='女')
属性3 数据类型 references 被参照的表名（被参照的属性名）--外键来自哪个表哪个属性
on delete cascade--表示删除被参照关系的元组时，同时删除参照关系的元组
on delete set null--表示删除被参照关系的元组时，参照关系的元组取null值
);
1.（主键）实体完整性约束（从多个候选码中通过 最小化规则 选择出一个作为主码也就是主键）
*若主键只是表中的某一个字段，则在定义主键属性的最后加primary key = not null unique
*【复合/联合主键】若主键是由表中多个字段组成，则在表级完整性约束加primary key(属性1，属性2)。(在一个数据表中通过多个字段作为主键来确定一条记录，那么，这多个字段组成的就是复合/联合主键 ）
①数据库中每张表只能有一个主键，不可能有多个主键；
②主键的作用：保证数据库的唯一性和完整性。主键的值也称为键值，必须能够唯一标识表中的每一条记录，且不能为null。也就是说：一个表只要定义了主键约束，就不可能有相同的两行记录（因为由主键唯一标识开了）。
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 ('男','女'))
--例：
create table item_stop202410
(
bm varchar2(50),
mc varchar2(100)
);
create table item_changecode202410
(
newcode varchar2(50),
mc varchar2(100),
oldcode varchar2(50)
);
create table item_changename202410
(
gjbm varchar2(50),
oldname varchar2(100),
oldyb varchar2(4),
newyb varchar2(4),
newname varchar2(100),
changetxt varchar2(50)
);
create table item_changefylb202401
(
gjbm varchar2(50),
ypmc varchar2(100),
fylb1 varchar2(2),
fylb1mc varchar2(10),
oldfylb1mc varchar2(20)
);
【2、修改表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 表名;--删除表
【3、查询语句select】
select TOP | DISTINCT 选择列表 | * --distinct去重
from 表名 --表若重命名，引用时一定要用新的名称
where 查询条件--条件范围对应整个表，结果返回之前执行，不能使用聚合函数
group by 分组条件
having 过滤条件--条件范围对应分组后的数据，必须跟在group by后，对返回结果过滤，可以使用聚合函数
order by 排序字段 asc | desc nulls first | last;
① 使用了聚集函数时，一般都要group by分组：group by 后面跟select之后除了聚集函数之外的所有属性列，若select之后无聚集函数，则group by之后跟select之后的所有属性列。②group by之后跟的字段名必须是重命名之前的(不能跟重命名之后的新名称)。
② having限制条件，去掉不满足having后条件的分组。
③ asc 升序 (默认) ，desc 降序
④ 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 后跟select之后未使用聚集函数的列名
--例：
select *
from KA0203
where aka090 in (select oldcode from item_changecode202410);
select *
from ybdzb
where center_code in (select oldcode from item_changecode202410);
select *
from medi000
where gjbm in (select oldcode from item_changecode202410);
select * from medi000 where gjbm not in (select center_code from ybdzb);
select t.*, t.rowid
from medi000 t
where ypbm not in (select his_code from ybdzb)
and dzbz = '1';【4、集合操作：并union、交intersect、差minus、（except只能用于SQLserver）】
集合操作要求两个关系模式必须一致（属性名称、个数、字段类型等）。
① union (无重并集) ： 当执行 UNION时，自动去掉结果集中的重复行，并以第 一列的结果进行升序排序。
② union all (有重并集) ： 不去掉重复行，并且不对结果集进行排序。
③ intersect (交集)：取两个结果集的交集，并且以第一列的结果进行升序排列。
④ minus (差集)： 只显示在第一个集合中存在， 在第二个集合中不存在的数据（ A减去B，在查询结果1中，不在查询结果2中）。
⑤ except (差集) 注：except只能用于SQLserver。只显示在第一个集合中存在， 在第二个集合中不存在的数据（ A减去B，在查询结果1中，不在查询结果2中）。
差集举例：
table1:
f_name f_date
name1 2009-6-1
name2 2009-6-2
table2:
f_name f_date
name3 2009-6-2
name4 2009-6-3
查询f_date差集：
1、在SQLserver中用except（ 注：except只能用于SQLserver）：
select f_date from table1
except
select f_date from table2;
except查询结果：
2009-6-1
2、在oracle中用minus：
select f_date from table1
minus
select f_date from table2
minus查询结果：
2009-6-1【5、内、外连接join...on...】
跟在from后，连接两个表(SQL99标准)。
① 左外连接：...from 主表A left (outer) join 副表B on 条件;--查询结果以左表A为准，左表A在右表B中没有的部分填充null
select * from m_main A left outer join z_main B ON A.DAH=B.DAH;② 右外连接：...from 副表A right (outer) join 主表B on 条件;--查询结果以右表B为准，右表B在左表A中没有的部分填充null
select * from m_main A right outer join z_main B ON A.DAH=B.DAH;③全外连接：...from A full (outer) join B on 条件;--查询结果把两个表在对方表中没有的部分都填充null
select * from m_main A full outer join z_main B ON A.DAH=B.DAH;④ 内连接： ...from A (inner) join B on 条件;--自连接，一个表自己连接自己
显示内连接：select * from m_main A inner join z_main B ON A.DAH=B.DAH;
隐式内连接：select * from m_main A, z_main B where A.DAH=B.DAH;【6、插入insert into】
方法一： insert into 表名 values（值1，值2，值3...）;--insert into不指名哪列，所以需values要把所有列的值都加进去
方法二：insert into 表名（列名1，列名2...） values (列名1的值,列名2的值...);--指名了要插入的列，values跟对应列的值
【7、删除delete】
delete from 表名;--删除表中所有内容=清除内容，不删除表的关系模式结构存储结构等
delete from 表名 where ...;--删除符合where后条件的行
truncate table 表名;--等价于不带where的delete语句，优点是truncate一次性删除数据，效率比delete更高，可以释放空间，设置成初始大小；缺点是truncate是一个DDL语句，不能被撤销rollback
drop table 表名;--删除所有内容，包括表的关系模式结构等
删除详见：Oracle删除数据的三种方式http://t.csdnimg.cn/TGkzV
【8、更新update】
update 表名 set 列名='要修改为的新值' where 条件;
--把表中符合where条件的行对应的列update为要修改为的值
update更新实例:
--例1：项目名称不变，收费项目类别“诊查费”要改为“诊察费”
update T001
set mc = '诊察费'
where dl = '0111'
and bm = 05
and mc = '诊查费';
update medi000
set fylb1mc = '诊察费', fylb2mc = '诊察费', fylb3mc = '诊察费'
where fylb1 = '05'
and fylb2 = '05'
and fylb1mc = '诊查费'
and fylb2mc = '诊查费';
--例2：20241101医疗项目相关变更工作
update KA0203 a
set AKA090 =
(select newcode
from item_changecode202410 t
where t.oldcode = a.aka090)
where aka090 in (select oldcode from item_changecode202410);
update medi000 a
set gjbm =
(select newcode from item_changecode202410 t where t.oldcode = a.gjbm)
where gjbm in (select oldcode from item_changecode202410);
update medi000 a
set gjbm =
(select center_code from ybdzb t where t.his_code = a.ypbm)
where a.ypbm in (select his_code from ybdzb);补充：
Tips:假设A，B两个表，表A中有属性参照了表B的属性。
1、先更新/删除参照的表A，再更新/删除被参照的表B
2、插入先插入被参照的表B，再插入参照的表A
【9、授予权限grant privilege】
grant select/insert/update/delete/alter/index/createtab/all privileges
on table/database
to user/public
with grant option;--all privileges 所有权限
--public 将权限授予所有人
--表示获得授权的人还可以把权限赋予其他用户
【10、收回权限revoke privilege】
revoke select/insert/update/delete/alter/index/createtab/all privileges
on table/database
from user/public
restrict/cascade;--restrict 只收回指定的用户的权限
--cascade 收回指定用户的权限以及该用户赋予的其他用户的权限（级联收回）
【11、视图view】
是从一个或几个基本表（或视图）导出的'虚表'，数据是随着基表的更新而更新。
数据库只存放view的定义，而不存放view对应的数据，这些数据仍存放在原来的基本表中。
view作用：
①简化了操作，把经常使用的数据定义为视图
②安全性，用户只能查询和修改能看到的数据
③逻辑上的独立性，屏蔽了真实表的结构带来的影响
***更新view
①从多个表得出的视图不允许更新
②使用了分组、聚集函数的视图不允许更新
③从单个表通过投影选择操作得出的视图允许更新
create view 视图名(列表名)--组成视图的属性列名全部省略或者全部指定。如果省略属性列名，则隐含该视图由select子查询目标列的主属性组成
as select 查询语句--子查询可以是任意复杂的select语句，但通常不允许含有order by子句和distinct短语
with check option;--表示对update,insert,delete操作时保证更新、插入或删除的行必须满足视图定义中的谓词条件（即子查询中的条件表达式）
drop view 视图名; --删除视图
【12、索引index】
一个表中一列或多列的值的集合，以及指向表中物理标识这些值的数据页的逻辑指针清单。
index作用：减少'查询'时间，提高'查询'效率和系统性能
一个表只能有一个cluster index，可以有多个非聚簇索引
create unique/cluster index 索引名
on 表名(属性名1 asc/desc,属性名2 asc/desc);
--unique 此索引的每一个索引项只对应唯一的一条数据记录。
--cluster 聚簇索引：索引项的顺序与表中记录的物理顺序一致的索引。
drop index 索引名; --删除索引
【13、触发器trigger】
数据库中事务的概念和作用https://blog.csdn.net/z646721826/article/details/79412459用于复杂的业务规则或要求，预编译效率高，不能包含事务，可以调用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 触发器名;--删除触发器
【14、存储过程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，参数2...）;--调用存储过程
Drop 存储过程名称;--删除存储过程
【15、游标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,该语句关闭游标，使它不再和查询结果相联系。游标关闭后，后面还可以再打开。
【16、常见时间格式转换】
常见1： t.lrsj >=TO_DATE('2024-01-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss') --lrsj数据类型转为date（用在条件）
示例：
select t.hj,t.*,t.rowid
from m_detail t
where
t.lrsj >=TO_DATE('2024-01-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and
t.lrsj <=TO_DATE('2024-01-29 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and t.ksbm='1050';常见2： replace(substr(cyrq,1,10),'-') cysj--cyrq数据类型为varchar2（用在查询后）
示例：
select distinct zyh,
replace(substr(cyrq,1,10),'-') cysj,
to_number(to_char(lrsj,'yyyymmdd')) jssj
from z_jzjl
where zhbj='0';常见3： to_char(lrsj,'yyyymmdd')--lrsj 数据类型是date（用在查询后）
示例：
select t.hj,
to_char(lrsj,'yyyymmdd') lrsj,
t.*,t.rowid
from m_detail t
where
t.lrsj >=TO_DATE('2024-01-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and
t.lrsj <=TO_DATE('2024-01-29 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and t.ksbm='1050';【17、查询表中某列字段相同的重复数据】
常用方法：使用 COUNT() 函数：下述查询语句中，我们通过 GROUP BY ZYH对表中的数据进行分组，并使用 COUNT(*) 函数计算每个分组中的记录数。然后使用 HAVING 条件筛选出记录数大于 1 的分组，即表示重复数据。
例：查询表中某字段相同的重复数据：
--查询9月份出院列表中住院号相同的重复记录。
select zyh,count(*)
from z_out_list t
where t.lrrq >= '20240901'
and t.lrrq <= '20240930'
group by zyh
having count(*)>1;
扫一扫
979
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
