本文详细介绍了SQL中的触发器、存储过程、游标、索引、视图等关键概念，包括它们的用途、语法和注意事项，以及如何管理和操作数据库中的数据和权限。
温馨提示：点击右侧目录即可快速跳转对应章节→→→→→→
前言：What is SQL? (帮助理解)
如果把database数据库比作盘子(想象一下你家有不同尺寸的盘子，因此有很多数据库产品)，那data数据就是盘子里的菜，结构化查询语言Structured Query Language，简称SQL，则是吃饭时用来夹菜的筷子。
一个适合小白学SQL的网站：xuesql.cn
http://www.xuesql.cn/
1 创建表空间create tablespace
创建表空间：
create tablespace first_tablespace --first_tablespace表空间名称，自定义
datafile 'D:\first_tablespace' --表空间irst_tablespace对应的数据文件，放在哪个路径，自定义
size 100M --设置该表空间默认最大容量为100M（举例），根据需求自定义默认大小
autoextend on --自动扩展（默认最大容量100M满了之后打开自动扩展开关）
next 10M;--每次满了之后自动增长10M（举例），根据需求自定义默认大小
--执行之后D:\下会多一个'first_tablespace.dbf'的文件
2 创建用户create user
注意：
① 用户是创建在表空间上的
② 一个表空间上可以创建多个用户 （表空间：用户=1：N）
创建用户
create user user1 --用户名，自定义
identified by 123456 --密码，自定义
default tablespace first_tablespace;--指定用户是属于'哪一个表空间'
3 授予用户权限grant privileg
授予用户权限。详细的授权语句如下，'/'代表或者
grant select/insert/update/delete/alter/index/createtab/all privileges
--all privileges表示所有权限
on database/table/view --具体是哪一个数据库或者表的权限
to user/public --给具体哪一个用户或者给所有用户
with grant option; --表示获得授权的用户还可以把权限赋予其他用户
--举例：给user1赋予dba权限
grant dba to user1;--dba代表最高权限4 收回用户权限revoke privilege
收回权限：
revoke select/insert/update/delete/alter/index/createtab/all privileges
--all privileges代表所有权限
on table/database--从哪个数据库或者表收回
from user/public--收回具体哪个用户的权限，public收回所有用户的权限
restrict/cascade;
--restrict 只收回指定的用户的权限
--cascade（级联收回） 收回指定用户的权限以及该用户赋予的其他用户的权限5 创建表create table
创建表方法一：用SQL语句创建新表（属性=字段，属性和字段一个意思）
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
create table item_changename202410
(
ID number primary,
gjbm varchar2(50),
oldname varchar2(100),
oldyb varchar2(4),
newyb varchar2(4),
newname varchar2(100),
changetxt varchar2(50)
);
--例2
create table item_changefylb202401
(
ID number primary,
gjbm varchar2(50),
ypmc varchar2(100),
fylb1 varchar2(2),
fylb1mc varchar2(10),
oldfylb1mc varchar2(20)
);
5.1 创建表方法一：利用SQL语句创建新表
create table 表名称(
字段1 数据类型(长度) primary key,--主键= not null unique
字段2 数据类型(长度) check(限制条件),--例：CHECK (Sex='男' OR Sex='女')
字段3 数据类型(长度) references 被参照的表名（被参照的属性名）--外键来自哪个表哪个属性
on delete cascade--表示删除被参照关系的元组时，同时删除参照关系的元组
on delete set null--表示删除被参照关系的元组时，参照关系的元组取null值
);
5.1 创建表方法二：利用PL/SQL图形化界面创建新表
5.2 主键（实体完整性约束）
从多个候选码中通过 最小化规则 选择出一个作为主码也就是主键。
① 数据库中每张表只能有一个主键，不可能有多个主键；
② 主键的作用：保证数据库的唯一性和完整性。主键的值也称为键值，必须能够唯一标识表中的每一条记录，且不能为null。也就是说：一个表只要定义了主键约束，就不可能有相同的两行记录（因为由主键唯一标识开了）。
③ 若主键只是表中的某一个字段，则在定义主键属性的最后加primary key = not null unique
④【复合/联合主键】若主键是由表中多个字段组成，则在表级完整性约束加primary key(属性1，属性2)。(在一个数据表中通过多个字段作为主键来确定一条记录，那么，这多个字段组成的就是复合/联合主键
5.3 外键（参照完整性约束）
该字段在当前正在创建的表中属于非主属性，但是在其他表中作为主键，因此在本表中称为外键。
① 若在列级表示外键： 参照的属性后面加 references 被参照的表名（被参照的属性名）
② 若在表级表示多个外键，在创建表的最后加foreign key（当前创建的属性） references 被参照的表名（被参照的属性名） 有几个外键写几行
5.4 用户自定义约束（属性值上的约束）
① null 为空;not null 非空
② unique 唯一，可以在多个属性上用unique，可写在属性后，也可以写在表级约束
③ not null unique 取值唯一且不为空=primary key
④check 限制列中的取值范围。
例：CHECK (Sex='男' OR Sex='女')，CHECK (余额>=0)，CHECK (年龄>=18 AND 年龄<=60)，check(sex in ('男','女'))
6 修改表alter table
6.1 修改表方法一：利用SQL语句进行修改
修改表方法一：利用SQL语句进行修改
1.增加字段（根据新需求需要新增字段）
alter table 表名 add
(新增字段1 数据类型(长度),
新增字段2 数据类型(长度)
);
增加字段例子：
alter table t_owners add
(remark varchar2(20),
outdate date
);
2.修改字段（修改字段的数据类型，扩充字段默认长度）
alter table 表名称 modify
(要修改的字段1 要修改成什么数据类型(修改后的长度),
要修改的字段2 要修改成什么数据类型(修改后的长度)
);
修改字段例子：
alter table t_owners modify
(remark char(30)
outdate timestamp
);
3.修改字段名
alter table 表名称 rename column 原字段名 to 修改后的新字段名;
修改字段名例子：
alter table t_owners rename column outdate to exitdate;
4.删除字段
alter table 表名称 drop column 要删除的字段1,要删除的字段2;
删除字段例子：
alter table t_owners drop column remark,exitdate;
5.删除表
drop table 要删除的表名称;
删除表例子：
drop table t_owners;
6.新增check约束
alter table S add constraint c_cno check(条件);6.2 修改表方法二：利用PL/SQL图形化界面进行修改
7 增删查改之增：插入insert into
方法一：
insert into 表名 values（值1，值2，值3...）;
--没有指名要插入的列，所以values后需要把所有列的值都写进去
方法二：
insert into 表名（列名1，列名2...） values (列名1的值,列名2的值...);
--指名了要插入的列，values跟对应指名列的值，未指名的字段默认为空方法一： insert into 表名 values（值1，值2，值3...）;
--没有指名要插入的列，所以values后需要把所有列的值都写进去
方法二：insert into 表名（列名1，列名2...） values (列名1的值,列名2的值...);
--指名了要插入的列，values跟对应指名列的值，未指名的字段默认为空
8 增删查改之删：删除delete from
delete from 表名;
--删除表中所有内容=清除内容，不删除表的关系模式结构存储结构等
delete from 表名 where ...;
--删除符合where后条件的行
truncate table 表名;
--等价于不带where的delete语句，
优点是truncate一次性删除数据，效率比delete更高，可以释放空间，设置成初始大小；
缺点是truncate是一个DDL语句，不能被撤销rollback
drop table 表名;
--删除所有内容，包括表的关系模式结构等delete from 表名;--删除表中所有内容=清除内容，不删除表的关系模式结构存储结构等
delete from 表名 where ...;--删除符合where后条件的行
truncate table 表名;--等价于不带where的delete语句，
优点是truncate一次性删除数据，效率比delete更高，可以释放空间，设置成初始大小；
缺点是truncate是一个DDL语句，不能被撤销rollback
drop table 表名;--删除所有内容，包括表的关系模式结构等
具体详见：Oracle删除数据的三种方式http://t.csdnimg.cn/TGkzV
9 增删查改之查：查询语句select
SQL99标准查询：
SELECT
查询列表
FROM 表1 别名1
【连接类型】 JOIN 表2 别名2 ON 连接条件
【where 分组前筛选条件】
【group BY 分组列表】
【having 分组后筛选条件】
【order BY 排序列表】;
--例：
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';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之后未使用聚集函数的列名
10 增删查改之改：更新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);update 表名 set 列名='要修改为的新值' where 条件;
--把表中符合where条件的行对应的列update为要修改为的值
补充：
Tips:假设A，B两个表，表A中有属性参照了表B的属性。
1、先更新/删除参照的表A，再更新/删除被参照的表B
2、插入先插入被参照的表B，再插入参照的表A
11 集合操作：并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-112 内外连接
- left ioin (左连接)：返回包括左表中的所有记录和右表中连接条件相等的记录。
- right join (右连接)：返回包括右表中的所有记录和左表中连接条件相等的记录。
- inner join(等值连接或者叫内连接)：只返回两个表中连接条件相等的记录。
- full join (全外连接)：返回左右表中所有的记录和左右表中连接条件相等的记录。
- 跟在from后，连接两个表(SQL99标准)。
SQL99标准查询： SELECT 查询列表 FROM 表1 别名1 【连接类型】 JOIN 表2 别名2 ON 连接条件 【where 分组前筛选条件】 【group BY 分组列表】 【having 分组后筛选条件】 【order BY 排序列表】;
① 左外连接：...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;12 视图view
是从一个或几个基本表（或视图）导出的'虚表'，数据是随着基表的更新而更新。
数据库只存放view的定义，而不存放view对应的数据，这些数据仍存放在原来的基本表中。
view作用：
①简化了操作，把经常使用的数据定义为视图
②安全性，用户只能查询和修改能看到的数据
③逻辑上的独立性，屏蔽了真实表的结构带来的影响
***更新view
①从多个表得出的视图不允许更新
②使用了分组、聚集函数的视图不允许更新
③从单个表通过投影选择操作得出的视图允许更新
create view 视图名(列表名)--组成视图的属性列名全部省略或者全部指定。如果省略属性列名，则隐含该视图由select子查询目标列的主属性组成
as select 查询语句--子查询可以是任意复杂的select语句，但通常不允许含有order by子句和distinct短语
with check option;--表示对update,insert,delete操作时保证更新、插入或删除的行必须满足视图定义中的谓词条件（即子查询中的条件表达式）
drop view 视图名; --删除视图
13 索引index
一个表中一列或多列的值的集合，以及指向表中物理标识这些值的数据页的逻辑指针清单。
index作用：减少'查询'时间，提高'查询'效率和系统性能
一个表只能有一个cluster index，可以有多个非聚簇索引
create unique/cluster index 索引名
on 表名(属性名1 asc/desc,属性名2 asc/desc);
--unique 此索引的每一个索引项只对应唯一的一条数据记录。
--cluster 聚簇索引：索引项的顺序与表中记录的物理顺序一致的索引。
drop index 索引名; --删除索引
14 触发器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 触发器名;--删除触发器
15 存储过程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 存储过程名称;--删除存储过程
16 游标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,该语句关闭游标，使它不再和查询结果相联系。游标关闭后，后面还可以再打开。
17 常见时间格式转换
常见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';18 查询表中某列字段相同的重复数据
常用方法：使用 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;
扫一扫
4142
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
