Oracle-个人总结
文章目录
#.1.1 基本SQL-SELECT语句
desc dual; --查看表结构
SQL> ed
SQL> /
--空值是无效的,未指定的,未知的或不可预知的值
select sysdate date1 from dual;--as可以省略
select a.name as "test" from my_name a where name = 400 --双引号固定大小写
-- ||表示连接符
select 123 || 'hello' || 123 from dual;
select distinct * from my_name a where name = 400; -- distinct去重
--查询中间结果(1)
select b.* from (select a.*, rownum as num from my_name a) b where num between 2 and 8;
--查询中间结果(2) (使用集合减运算符minus)
select * from my_name where rownum <= 8 minus select * from my_name where rownum < 2;
--查询中间结果(3) (取交集)
select b.* from (select *,rownum as num from my_name a) b where num >=2
intersect
select * from my_name where rownum <= 8;
--top-N
select organ_code, organ_name, tree_level
from (select rownum as rn, organ_code, organ_name, tree_level
from (select organ_code, organ_name, tree_level
from my_company
order by tree_level)
)
where rn <= 10
and rn >= 2;
select *
from (select organ_code, organ_name, tree_level
from my_company
order by tree_level)
where rownum < 10;
#.1.2 过滤和排序数据
select * from my_name a where name <> 400; --不等于
select * from my_name where name between '10004041' and '10004043' ; --包含边界值
select * from my_name where name in ('10004041', '10004042', '10004043') ;
select * from my_name where name like '%404_'; --%可能有0个或多个字符,_表示有一个字符
select * from my_name where name like '%a%b%' or name like '%b%a%'; --有a和b的情况
select * from my_name where name like '%\_%' escape '\'; --查看有下划线的情况
select * from my_name where name is null; --对应name为空,不是空格
select * from my_name where name is not null;
逻辑运算
select * from my_name where type = '1' and name = 'pipe';
select * from my_name where type = '1' or name = 'pipe';
select * from my_name where type = '1' and name not in ('pipe');
排序
select * from my_name where type = '1' order by name desc; --逆序
select * from my_name where type = '1' order by name asc, age desc; --asc可不写,正序
#.1.3 单行函数
大小写
select lower('Xiao Li'), upper('Xiao Li'), initcap('xiao li') from dual;
--lower小写, upper大写, initcap首字母大写
字符串操作
select concat('hello', 'world'), substr('helloworld', 2, 4), length('hello') from dual;
--concat拼接,SUBSTR(str,pos,len)
--INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
--C1 被搜索的字符串
--C2 希望搜索的字符串
--I 搜索的开始位置,默认为1,(负值倒数第几个,往回查找)
--J 第几次出现,默认为1
select instr('hello, world', 'w') from dual;
--索引,oracle索引默认从1开始
select instr('hello, world', 'x') from dual; --0
--索引,不存在为0
select lpad(first_name, 15, '*') from my_name;
--汉字占2位,字符占1位,左填充,rpad右填充
select trim('h' from 'hhelllleoddhh') from dual; --去除首尾h字符 elllleodd
select trim(' hhelllleoddhh ') from dual; --去除空格
select replace('abcdea', 'a', 'm') from dual; --全部替换
select left('123456789', 6) from dual; --123456
select right('123456789', 6) from dual; --456789
数字函数
select round(435.145, 2), round(435.144, 2), round(435.152, -1) from dual;
--四舍五入 435.15 435.14 440
select trunc(435.145, 2), trunc(435.144, 2), trunc(435.152, -1) from dual;
--截断 435.14 435.14 430
select mod(1100, 200) from dual;
--求余 100
日期函数
select sysdate, sysdate+1, sysdate-1 from dual; --日期加减法
select months_between(sysdate, sysdate + 365) from dual; --相差的月数
select add_months(sysdate, -2) from dual; --加月份
select next_day(sysdate, '星期一') from dual; --下个星期一
select last_day(sysdate) from dual; --本月最后一天
select sysdate, round(sysdate, 'month') from dual;
--20230117 20230201
select sysdate, trunc(sysdate, 'month') from dual;
--20230117 20230101
数据隐式转换
date <> varchar2 <> number 自动转换
select '2' + 14 from dual;
select sysdate + 2 from dual;
date和char转换
select to_char(sysdate, 'yyyy-mm-dd') from dual; --date转char
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual; --加汉字
select to_date('1994-06-07', 'yyyy-mm-dd') from dual; --char转date
select to_number(to_char(to_timestamp('140000000','hh24missff3') - numtodsinterval(1/1000,'second'), 'hh24missff3')) from dual; --减1毫秒
char和number转换
--9代表:如果存在数字则显示数字,不存在则显示空格。
--0代表:如果存在数字则显示数字,不存在则显示0,即占位符。
--FM代表:删除如果是因9带来的空格,则删除之。
select to_char(1234567.89, 'FM999,999,999.99') from dual;
--没有为空格,FM删除空格 1,234,567.89
select to_char(1234567.89, '000,000,999.99') from dual;
--没有用0替换 001,234,567.89
select to_char(1234567.89, '$000,000,999.99') from dual;
--美元 $001,234,567.89
select to_char(1234567.89, 'L000,000,999.99') from dual;
--人民币 ¥001,234,567.89
select to_number('¥001,234,567.89', 'L000,000,999.99') from dual;
--char转number 1234567.89
nvl相关函数
select nvl(trim(name), '100') from my_name where id = 100;
--nvl 前面值为null,使用后面字符替换
--情形一:name为空,结果100
--情形二:name为空格,结果100
--情形三:根据id查询无结果,无记录
select nvl(trim(name), to_char(100)) from my_name; --类型不一致使用转换函数
select nvl2(trim(name), first_name, '200') from my_name; --nvl2不为空,输出前面,为空输出后面
select nullif('12', '12'), nullif('12', '13') from dual; --相等返回null,不相等返回前者
条件表达式
select name,
case first_name
when '123' then first_name || '**'
when '456' then first_name|| '!!'
else first_name || '##'
end as first_name_cur
from my_name; --case when then else then end
select name,
case
when first_name in ('123') then first_name || '**'
when first_name in ('456') then first_name || '!!'
else first_name || '##'
end as first_name_cur
from my_name; --case when then when then else end
select name,
decode(first_name ,
'123',
first_name || '**',
'456',
first_name || '!!',
first_name || '##') as first_name_cur
from my_name; --decode
#.1.4 多表查询
select a.first_name, a.last_name, b.class_name
from my_name a, my_class b
where a.name = b.name; --where防止笛卡尔积,内连接,不包含不匹配行
select a.first_name, a.last_name, b.class_name
from my_name a, my_class b
where a.name = b.name(+); --左外连接
select a.first_name, a.last_name, b.class_name
from my_name a, my_class b
where a.name(+) = b.name; --右外连接
select a.first_name, a.last_name, b.class_name
from my_name a, my_class b
where a.name(+) = b.name(+); --满外连接
select b.*
from my_object a, my_object b
where a.id = b.parent_id
and b.parent_id = '12'; --自连接
另一种写法
select a.first_name, a.last_name, b.class_name, c.object_name
from my_name a join my_class b
on a.name = b.name
join my_object c
on b.name = c.name --多表连接
select a.first_name, a.last_name, b.class_name
from my_name a left outer join my_class b
on a.name = b.name; --左外连接
select a.first_name, a.last_name, b.class_name
from my_name a right outer join my_class b
on a.name = b.name; --右外连接
select a.first_name, a.last_name, b.class_name
from my_name a full outer join my_class b
on a.name = b.name; --满外连接,左右都可以为空
#.1.5 分组函数(多行函数)
select avg(tree_level), max(tree_level), min(tree_level), sum(tree_level)
from my_object a;
--min、max都任何类型都适用 avg、sum只能是number型,而且只计算非空
select count(tree_level), count(1), count(2), count(*)
from my_object a; --count中expr不为空记录
--count(1), count(2), count(*)结果相同
select count(distinct tree_level)
from my_object a; --count中expr非空不重复记录
select count(*) "total",
count(decode(modify_date, 20220915, 1, null)) "20220915",
count(decode(modify_date, 20211124, 1, null)) "20211124",
count(decode(modify_date, 20210805, 1, null)) "20210805",
count(decode(modify_date, 20210315, 1, null)) "20210315",
count(decode(modify_date, 20210208, 1, null)) "20210208"
from my_object a; --统计不同条件下数量
select a.parent_id, a.code, avg(a.tree_level)
from my_object a
group by a.parent_id, a.code
having avg(a.tree_level) > 0
--group by分组,查询未包含在组函数的列都需要包含在group by中
--组函数不能放在where,必须放在having
#.1.6 子查询
单行子查询
select *
from my_object a
where modify_date > (select modify_date
from my_object
where code = '37547'); --子查询,单行子查询(返回单行,可能空值)
select a.code, a.name, min(a.modify_date)
from my_object a
group by a.code, a.name
having min(a.modify_date) > (select modify_date
from my_object a
where organ_code = '37547'); --子查询
多行子查询
select *
from my_object a
where parent_id <> '-1'
and modify_date > any (select modify_date
from my_object
where parent_id = '-1'); --any,任何一个
select *
from my_object a
where parent_id <> '-1'
and modify_date > all(select modify_date
from my_object
where parent_id = '-1'); --all,所有
select *
from my_object a
where modify_date not in (select modify_date
from my_object
where parent_id = '-1'); --空值问题
其他
exists
not exists
select *
from my_object a
where exists (select modify_date
from my_object
where code = '37547'); --子查询,单行子查询(返回单行,可能空值)
#.1.7 创建和管理表 DDL语句
查看用户定义表
-- 表、视图、同义词、序列、索引
select * from user_tables; --查看用户定义的表
select * from user_objects; --查看用户定义的数据库对象
select * from user_catalog; --查看用户定义的表、视图、同义词、序列
创建表
CREATE TABLE my_fare( --1、直接创建
fare_type char(1) DEFAULT ' ' NOT NULL,
fare_name varchar2(4) DEFAULT ' ' NOT NULL,
cur_value number(10,2) DEFAULT 0 NOT NULL --0位小数
);
CREATE TABLE my_fare2 --2、依托
as
select * from my_fare
where 1 = 2; --不复制数据
drop table my_fare; --删除表
--truncate table my_fare; --清空表,表还在,不可回滚
--delete from my_fare; --清空表,表还在,可回滚
rename my_fare to my_fare3; --改表名
增加删除列
alter table my_fare
add (email varchar2(20)); --增加列
alter table my_fare
modify (email varchar2(22) default ' '); --改数据类型,需要列为空
alter table my_fare
drop column email; --删除列
alter table my_fare
rename column email to email2; --改列名
#.1.8 数据处理 DML语句
--增加
insert into my_code
values ('11', 'test', 'test_name', '-1', 1, 0, 20220922, 'test'); --1、全部插入,某些列不能为空
insert into my_code (organ_code, parent_id)
values ('11', '-1'); --指定列
insert into my_code (organ_code, parent_id)
select (company_no, company_name) from my_company; --2、有原有插入
insert into my_code (organ_code, parent_id)
values ('&code', &id); --3、由输入插入
数据库事务
--一个或多个DML语句(commit提交、rollback回滚)
--一个DDL语句(自动提交)
--一个DCL语句(数据控制语句)
select * from my_code;
update my_code set organ_code = '11' where remark = '初始化';
commit; --rollback;回滚或者递交
#.1.9 约束
–not null
–unique
–primary key
–check
–创建和修改约束
– 建表的同时
– 建表之后
–作用范围
– 列级约束只能作用在一个列上
– 表级约束可以作用在多个列上
创建表时添加约束
create table emp2(
--constraint检验名称,否则默认名称SYS_**
id number(10) constraint emp2_id not null, --不能为空
name varchar2(20) not null,
salary number(10, 2)
);
create table emp3(
--列级约束
id number(10) constraint emp3_id not null,
name varchar2(20) constraint emp3_name unique, --不能重复,相同null不违反unique
salary number(10, 2),
email varchar2(20),
--表级约束
constraint emp3_email unique(email)
);
create table emp4(
id number(10) constraint emp4_id_pk primary key, --主键,不能为空,列级约束
name varchar2(20) constraint emp4_name unique,
salary number(10, 2),
email varchar2(20),
constraint emp4_email unique(email)
);
create table emp5(
id number(10),
name varchar2(20) constraint emp5_name unique,
salary number(10, 2),
email varchar2(20),
constraint emp5_email unique(email),
constraint emp5_id_pk primary key(id) --主键,表级约束
);
create table emp6(
id number(10),
name varchar2(20) constraint emp6_name unique,
salary number(10, 2),
email varchar2(20),
depart_id number(10),
constraint emp6_email unique(email),
constraint emp6_id_pk primary key(id),
constraint emp6_dept_id_fk foreign key(depart_id) reference emp5(id) --外键,和其他表约束
--插入数据外键,emp5表必须存在对应值
);
create table emp7(
id number(10),
name varchar2(20) constraint emp6_name unique,
depart_id number(10),
constraint emp7_dept_id_fk foreign key(depart_id) reference emp5(id) on delete set null
--子表相应列置空
--constraint emp7_dept_id_fk foreign key(depart_id) reference emp5(id) on delete cascade
--父表中列被删除,子表相应列也被删除
);
create table emp8(
id number(10),
name varchar2(20) constraint emp8_name unique,
salary number(10) check(salary > 1500 and salary < 30000) --check约束
);
建表后添加约束
--添加或删除约束,不能修改约束
alter table emp5
modify(salary number(10, 2) not null);
alter table emp5
drop constraint emp5_name; --删除约束
alter table emp5
add constraint emp5_name_uk unique(name); --增加约束
alter table emp5
disable constraint emp5_name_uk; --让约束失效
alter table emp5
enable constraint emp5_name_uk; --让约束起效(数据可能报错)
#.1.10 视图
视图建立在已有表的基础上,存储的select语句
视图的目的:控制数据访问、简化查询、避免重复访问相同的数据
create view my_organ_view--视图
as
select organ_code, organ_name, remark
from my_organ
where parent_id = '-1';
select * from my_organ_view;
update my_organ_view set remark = '初始化' where organ_code = '11';
--直接影响原表
create view my_organ_view2--多表视图
as
select a.system_name, b.organ_code, b.organ_name, b.remark
from my_company a, my_organ b
where a.company_name = b.organ_name;
create or replace view my_organ_view2 --修改视图
as
select a.system_name, b.organ_code, b.organ_name
from my_company a, my_organ b
where a.company_name = b.organ_name
with read only; --不允许修改
create or replace view my_organ_view3 --复杂视图
as
select system_name, avg(tree_level) avg_level
from my_company a, my_organ b
where a.company_name = b.organ_name
group by system_name;
drop view futuorgancodeinfo_view; --删除视图
#.1.11 其他数据库对象【序列、索引、同义词】
序列:提供有规律的数值
索引:提高查询的效率
同义词:给对象起别名
序列
--序列
create sequence empseq
increment by 10 --每次增长10
start with 10 --从10开始增长
maxvalue 100 --提供的最大值
cycle --需要循环
nocache; --不需要缓存登录
select empseq.nextval from dual; --下个值
select empseq.currval from dual; --当前值
alter sequence empseq
increment by 1
nocycle; --修改序列,增长1,不循环
--查看序列
select sequence_name, min_value, max_value, increment_by, last_number from user_sequences;
drop sequence empseq;
索引
--索引
create index emp01_id_ix
on emp01(emp_id);
create unique index emp01_id_ix
on emp01(emp_id); --唯一索引
drop index emp01_id_ix; --删除索引
同义词
create synonym e for employees;
select * from e;
#.1.12 集合
select *
from my_name
where name in ('400', '700001')
union
select *
from my_name
where name in ('402', '700001'); --并集去重
select *
from my_name
where name in ('400', '700001')
union all
select *
from my_name
where name in ('402', '700001'); --并集不去重
select *
from my_name
where name in ('400', '700001')
intersect
select *
from my_name
where name in ('402', '700001'); --交集
select *
from my_name
where name in ('400', '700001')
minus
select *
from my_name
where name in ('402', '700001'); --A集合减去B集合
select emp_id, dept_id, to_char(null)
from emp01
union
select to_number(null), dept_id, dept_name
from emp02; --不对应情况
column a_dummy noprint;
select 'study at' as "Pipe calendar", 2 a_dummy
from dual
union
select 'I want to', 1
from dual
union
select 'book store', 3
from dual
order by 2 asc; --按第二例排序,第二列不显示
#.1.13 其他
多条数据整合成一条
select to_char(wmsys.wm_concat(distinct a.company_name))
from my_company a,my_project b
where a.company_no = b.company_no
#.1.14 控制用户权限
--DBA权限
create user superman
identified by 123456; --创建用户,设置密码
grant create session --赋予创建会话权限
to superman;
grant create table --赋予创建表权限
to superman;
alter user superman quota unlimited
on users; --在users角色创建表空间,无限制,也可以限制5M
--角色
create role my_role; --创建角色
grant create session, create table, create view to my_role; --赋予权限
grant my_role to superman; --赋予superman用户my_role角色
--分配权限
grant select, update
on employees
to superman;
--分配权限
grant select, update
on employees
to superman
with grant option; --给用户授权权限,用户同样具有分配权限
grant select, update
on employees
to public; --给所有用户授权
--收回权限
revoke select, update
on employees
from superman;
select * from USER_SYS_PRIVS; --查询权限
#.1.15 PLSQL
简单语法
set serveroutput on --打印
--declare
--声明
begin
--执行
dbms_output.put_line('Hello world');
--exception
--异常
end;
/
类型
常规数据类型
set serveroutput on --打印
declare
v_account varchar2(10);
v_number number(5);
begin
select account, number into v_account, v_number
from my_account where account = '400';
dbms_output.put_line(v_account || '***' || v_number);
end;
/
动态获取类型
set serveroutput on --打印
declare
v_account my_account.account%type;
v_number my_account.number%type;
begin
select account, number into v_account, v_number
from my_account where account = '400';
dbms_output.put_line(v_account || '***' || v_number);
end;
/
定义record类型
set serveroutput on --打印
declare
type record_type is record(
v_account my_account.account%type, --逗号
v_number my_account.number%type --没有逗号
);
my_record record_type;
begin
select account, number into v_account, v_number
from my_account where account = '400';
dbms_output.put_line(my_record.v_account || '***' || my_record.v_number);
end;
/
定义record类型,由已有表数据类型
set serveroutput on --打印
declare
my_record my_account%rowtype;
begin
select * into v_account, v_number
from my_account where account = '400';
dbms_output.put_line(my_record.account || '***' || my_record.number);
end;
/
异常错误处理
declare
v_cur number(5) := 100;
begin
v_cur := v_cur / 0;
exception
when too_many_rows then dbms_output.put_line('输入行太多');
when others then dbms_output.put_line('其他错误');
end;
/
流程控制
if语句
set serveroutput on --打印
declare
v_cur number(5);
v_res varchar(10);
begin
v_cur := 100;
if v_cur > 150 then
v_res := '大于150';
elsif v_cur > 100 then
v_res := '大于100';
else
v_res := '小于100';
end if;
dbms_output.put_line(v_res || '***' || v_res);
end;
/
case when
declare
v_cur number(5);
v_res varchar(10);
begin
v_cur := 100;
v_res := case when v_cur = 100 then 'A'
when v_cur = 200 then 'B'
when v_cur = 300 then 'C'
else 'D'
end;
dbms_output.put_line(v_res || '***' || v_res);
end;
/
loop循环
declare
v_cur number(5) := 13;
begin
loop
dbms_output.put_line(v_cur || '***' || v_cur);
exit when v_cur > 15;
v_cur := v_cur + 1; --迭代条件
end loop;
end;
/
while循环
declare
v_cur number(5) := 100;
begin
while v_cur <= 105 loop
dbms_output.put_line(v_cur || '***' || v_cur);
v_cur := v_cur + 1; --迭代条件
end loop;
end;
/
for…loop循环
declare
v_cur number(5) := 100;
begin
for c in 95..v_cur loop --包括上下限
dbms_output.put_line(c || '***' || c);
if c = 98 then --中间跳出
goto a;
end if;
end loop;
<<a>> --标签
dbms_output.put_line('end');
end;
/
游标
for循环游标
declare
cursor tmp_cursor is select a.* from my_project a where init_date = 20230110;
begin
for c in tmp_cursor loop
dbms_output.put_line(c.remark);
end loop;
end;
/
存储函数和存储过程
存储函数
create or repalce function get_sum(num1 number, num2 number)
return number
is
v_sum number(10) := 100;
begin
return num1 + num2 + v_sum
end;
select get_sum(10, 20) from dual;
存储过程
create or repalce procedure get_sum(num1 number, num2 number)
is
v_sum number(10) := 100;
begin
dbms_output.put_line(num1 + num2 + v_sum);
end;
begin
get_sum(100, 200);
end;