oracle PL-SQL基础知识
目录
脑图
查询数据
选择语句
Select语句结构
子句 |
描述 |
是否必需 |
select |
返回的列或表达式 |
是 |
from |
要检索的表 |
在oracle是,其他不一定 |
where |
行过滤 |
否 |
group by |
分组规范 |
仅当分组计算聚会值时是必需的 |
having |
分组过滤 |
否 |
order by |
排序,desc,asc |
否 |
select 表达式或列名或函数
筛选 distinct;
连接字段 ||
>select [列名1]||[列名2] from [表1] >列名1列名2
>select [列名1]||',('||[列名2]||')'' from [表1] >列名1,(列名2)
数字计算 + - * /
函数
(参考 Oracle学习笔记——常用函数总结)
文本函数 Length() Lower() Upper() LPad() LTrim() Soundex() SubString()
数值函数 Abs Cos Exp Mod Sin Sqrt Tan
日期函数 Add_Month() Last_Day() Months_Between() Next_day() Sysdate() ; To_Date(sysdate,'YYYY-MM-DD HH24:mi:ss');Extact( ? from sysdate) ?=day|month|year
系统函数
聚合函数 AVG() COUNT() MAX() MIN() SUM()
别名 as
行过滤where
过滤条件:
- 常用:值 =;<;>;<=;>=;<>;!=;is null
where [列名1] between [范围开头] and [范围结尾] --值范围
where [列名1] <> [值]; -- 不等于
where [列名1] != [值]; --不等于
where [列名1] IS NULL; --没有值
- 范围 between and;in;not in;
where [列名] IN ( [有效值1],[有效值2]) --IN运算符,清晰,效率高,推荐使用;()可以包含另一个select语句;
where [列名] NOT IN ( [有效值1],[有效值2])
- 模糊 like % _
where [列名] LIKE 'jet%' --LIKE 模糊匹配,%通配符,%匹配n个字符
where [列名] LIKE 'jet_' --LIKE 模糊匹配,_通配符,%匹配单个字符
- 多重条件
where [条件1] AND [条件2]; --多重条件,且,同时
where [条件1] OR [条件2]; --多重条件,或,任一条
where ([条件1] OR [条件2]) AND [条件3]; --多重条件,显性组合();隐形默认处理顺序为and 优于 or;
- 匹配文本的特殊语言--正则表达式
where regexp_like() --返回满足条件的字段
……regexp_instr() --返回满足条件的字符或字符串的位置
……regexp_replace() --返回替换后的字符串
……regexp_substr() --返回满足条件的字符或字符串
/*正则表达式*/
--模糊匹配字符值;.是正则表达式的_运算符,意味匹配任意单个字符
WHERE REGEXP_LIKE( [列名], '值')
input> WHERE REGEXP_LIKE( v_id, '.000') ;
output> 所有含?000 的列,包括1000/2000/;
--匹配多个字符值;|是正则表达式的or运算符
WHERE REGEXP_LIKE( [列名], '值1|值2|值n')
--匹配特殊单一字符;[]内定义一组字符,[123]表示匹配 1ton或2ton或3ton;
WHERE REGEXP_LIKE( [列名], '[值1|值2|值n]ton')
可以写作WHERE REGEXP_LIKE( [列名], '[1|2|3]ton');更容易理解
--匹配特殊单一字符;^表示取反,[^123]定义一组字符,表示匹配除 1ton或2ton或3ton以为的其他字符,并且返回他们;
WHERE REGEXP_LIKE( [列名], '[^值1|值2|值n]ton')
--匹配范围字符;-定义一个范围
WHERE REGEXP_LIKE( [列名], '[值1-值n]')
特殊字符,用\作前缀;即转义字符,"\." 即.;"\_" 即_
匹配字符类别;匹配多个实例; 参考:Oracle 正则表达式 - Oracle - 博客园
分组规范group by
--根据[列1]分组统计[列2]的总数,也可以用其他聚会函数
select [列1],COUNT([列2]) from [表1] GROUP BY [列1];
分组过滤having
--根据[列1]分组统计[列2]的总数,也可以用其他聚会函数
select [列1],COUNT([列2]) from [表1]
GROUP BY [列1]
HAVING COUNT([列2]) >2;
排序order by
desc;asc
子查询
- 子查询过滤,在where子句加入select语句
select [l1]
from [b1]
where [l2] in (select [l2] from [b2] where [l3]=值);
--等于联表查询,查询符合表2中指定列3对应的列2名称,在表1中查询列2名称对应的列1名称;
--可以多重嵌套,最多255个层级,但性能会退化;
- 子查询计算,在select子句加上select语句
select [l1] ,(select count([l2]) from [b2] where [l3]=值) as 别名
from [b1];
- 内联视图,在from中加入select语句
组合查询
1. 组合查询结果
union;union all
--组合SQL查询语句,把他们的结果组合成单个结果集。
select 语句1
union
select 语句2;
--union会自动删除查询结果集中任何重复的行。union all,返回所有行。
select 语句1
union all
select 语句2;
2.组合表查询
外键(Foreign)
一个表中的一列,其中包含另一个表中的主键(Primary Key),因此定义了两个表之间的关系(relation)
表连接方式
等值连接,from A,B where 条件
内连接,from A inner join B on 条件
外连接,from A right/left outer join B on 条件
where做连接条件;
select 列名1,列名2,列名3
from 表名1,表名2
where 表名1.列名1 等于表名2.列名1
内连接,即等值连接
select 列名1,列名2,列名3
from 表名1
inner join 表名2
on 表名1.列名1 等于表名2.列名1;
外连接
select 列名1,列名2,列名3
from 表名1
right/left outer join 表名2
on 表名1.列名1 等于表名2.列名1;
视图
视图,是虚拟表,包含查询(select语句);可以复用,但要考虑性能
create view
create view 视图名 as
select 语句;
之后可以多次引用虚拟表(视图名),就当做是一个表来处理。
操作数据
更新update
update 表名1 set 列名2=‘新值’where 列名1=‘指定行';
删除delect
delect from 表名1 where 列名1=‘指定行';
插入Insert
1.直接插入
insert into 表名(列名1,列名2,列名n)
values(值1,值2,值n);
2.从表导入
insert select
insert into 表名(列名1,列名2,列名n)
select 列名1,列名2,列名n from 表名2; --select 语句也可以加入过滤条件
操作表
创建新表
create tables 表名
(列名 字符类型 not null,
列名 字符类型 null, --允许空值
列名 字符类型 default 1 not null) --空值时,默认1
定义主键
alter table 表名 add constraint pk_表名 Primary Key(列名);
定义外键
alter table 表名1 add constraint fk_表名1_表名2 Foreign Key(列名) references 表名2(列名);
更新表
update tables 表名
add 列名 字符类型;
删除表
drop table 表名;
重命名表
alter table 新表名新 rename to 表名;
管理用户
用户账户
查询
select * from dba_users; create user 用户账户 identified by “密码”; drop user 用户账户
修改
alter user 用户账户 indentified by “新密码”replace“旧密码”
用户权限
查询
select privilege from sys.dba_sys_privs --存储用户权限的表 where grantee='用户账户'
授权
grant select on 表名 to 用户账户 grant select,insert,update on 表名 to 用户账户
撤销
revoke……
进阶
存储过程
创建
create or replace procedure 存储过程名 is\as 可选的变量声明 begin 【过程的主体】 end;
执行
execute 存储过程1;
删除
drop procedure 存储过程1;
存储过程例子
declare
--declare variables定义变量名
v_vend_id vendors.vend_id%type;
v_vend_name vendors.vend_name%type;
--declare cursor定义游标名
cursor c_vendors is
select vend_id,vend_name
from vendors;
begin
open cursor
--loop through cursor遍巡游标,依次把把游标中的记录放入指定变量中,知道when条件满足。
loop
fetch c_vendors into v_vend_id,v_vend_name;
exit when c_vendors%notfound;
end loop;
close c_vendors;
end;
declare
变量名 表名.列名%type;
cursor 游标名 is
select 语句;
begin
open cursor
loop
【循环操作】
exit when 退出条件
end loop
close c_vendors;
游标
定义: 游标cursor是一个存储在oracle服务器上的数据库查询,是由select语句检索的结果集
操作
declare cursor 游标名 is select 语句;
open 游标名; close 游标名;
触发器
定义:用来跟踪表的更改;做记录
操作
create or replace trigger 触发器名 after 操作 on 表名 begin 触发动作 end;
drop trigger 触发器名;
insert触发器--用来跟踪表的更改;
触发器例子:
create or replace trigger order_after_insert
after insert on orders
for each row
begin
insert into orders_log(change_on,change_type,order_num)
values(sysdate,'A',NEW.order_num)
end;
create or replace trigger 触发器名称
after insert on 表名 | before delect on 表名|before update on 表名
for each row
begin
insert into orders_log(change_on,change_type,order_num)
values(sysdate,'A',NEW.order_num)
end;
事务处理
定义: 事务处理transaction processing是一种机制,用于管理必须成批执行的SQL操作集。
操作
savepoint 保存点名;
rollback
rollback to savepoint 保存点名;
Commit
end;