Oracle操作语句
目录
- Oracle操作语句
- Oracle语句
- Oracle函数
- to_date(str,'yyyy-mm-dd hh24:mi:ss')
- to_char(date,'yyyy-mm-dd hh24:mi:ss')
- ceil(number)
- floor(number)
- round(number[,decimals])
- nvl(arg,value)
- instr( string1, string2 [, start_position [, nth_appearance ] ] )
- decode(value,if1,then1,if2,then2,if3,then3,...,else)
- start with ... connect by prior ...
- level,sys_connect_by_path,connect_by_isleaf
- siblings
创建表空间
create tablespace test datafile 'D:\app\user\oradata\orcl\test.dbf' size 500m autoextend on next 10m;
删除表空间
删除空的表空间不包含物理文件
drop tablespace test;
删除表空间不包含物理文件
drop tablespace test including contents;
删除表空间包含物理文件
drop tablespace test including contents and datafiles cascade constraint;
创建用户
create user test identified by password default tablespace test;
给用户授权
grant create table, create view, create trigger, create sequence, create procedure to test;
grant dba to test;
导出数据库
exp test/password@ip/orcl file=D:\test.dmp owner=test
exp test/password file=D:\test.dmp owner=test
exp test/password file=D:\test.dmp tables=(table_name)
导入数据库
imp test/password file=D:\test.dmp full = y
误删数据库闪回
alter table table_name enable row movement;
flashback table table_name to timestamp to_timestamp('2019-06-19 20:10:00','yyyy-mm-dd hh24:mi:ss');
alter table table_name disable row movement;
Oracle语句
查找用户
select * from dba_users;
查询该用户下的表
select count(*) from user_tables;
select * from user_tables;
with tmp as (…)
相当于给sql语句起了个别名,相当于建立了一个临时表tmp。
with tmp as (select * from table_name)
select * from tmp;
----------------------------------------
with
tmp1 as (select * from table_name),
tmp2 as (select * from table_name),
tmp3 as (select * from table_name),
...
Oracle函数
to_date(str,‘yyyy-mm-dd hh24:mi:ss’)
将字符串(varchar2)转换为date类型
select to_date('2020-05-31 23:23:23','yyyy-mm-dd hh24:mi:ss') from dual;
to_char(date,‘yyyy-mm-dd hh24:mi:ss’)
将date类型转换为字符串(varchar2)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
ceil(number)
向上取整
select ceil(12.3) from dual;
floor(number)
向下取整
select floor(12.3) from dual; // 12
round(number[,decimals])
截取数值进行四舍五入,第二个参数可以为负值超过位数结果为0
select round(12.6,1) from dual;
nvl(arg,value)
当查询的值为’'或者null时,给一个默认值
select nvl(col,'我是默认值') from dual;
instr( string1, string2 [, start_position [, nth_appearance ] ] )
字符串查找函数,两种方式:
- instr( string1, string2 ),instr(源字符串, 目标字符串)
- instr( string1, string2 [, start_position [, nth_appearance ] ] ),instr(源字符串, 目标字符串, 起始位置, 匹配序号)
查询'询'出现的位置
select instr('我是查询字段查询','询',1,1) from dual; // 4
查询'询'从第五的位置开始第一次出现的位置
select instr('我是查询字段查询','询',5,1) from dual; // 8
查询'询'从倒数第二的位置往回查找第一次出现的位置
select instr('我是查询字段查询','询',-2,1) from dual; // 4
可以用来模糊查询,instr() > 0.
decode(value,if1,then1,if2,then2,if3,then3,…,else)
类似于case … when … then … else
select decode(msg,'111','1','222','2','0') from test;
当msg列的值为'111'时给它指定值为'1',当值为'222'时给它指定值为'2',其他情况指定值为0。