oracle常用语句-DCL

表空间

创建表空间

create tablespace test01 datafile 'test01.dbf' size 10m;
create temporary tablespace temp01 tempfile 'temp01.dbf' size 10m;

查看表空间

select file_name from dba_data_files where tablespace_name = 'TEST01';
select file_name from dba_temp_files where tablespace_name = 'TEMP01';

修改表空间状态（联机【只读、可读可写】、脱机）

select status from dba_tablespaces where tablespace_name = 'TEST01';
alter tablespace test01 offline;

创建用户

输入 sqlplus 用户名/密码 as sysdba 以管理员身份连接数据库。

如： sqlplus scott/123456 as sysdba;

--创建用户
create user 用户名 identified by 密码;
--授予所有权限，一次性授权多个用户用逗号隔开即可。
grant all privileges to 用户名;
grant create table, create view to 用户名;

更改初始密码

alter user test01 identified by j2yd;

锁定用户

alter user test01 account lock;

删除用户

drop user test01 cascade;

添加索引

-- 格式
create index 索引名 on 表名(列名);

-- 声明表空间
create index 索引名 on 表名(列名) online tablespace 空间名;
create index 索引名 on 表名(列名) tablespace 表空间名;

-- 组合索引
create index 索引名 on 表名(列名1,,列名2);
-- 唯一索引
create unique index 索引名 on 表名(列名);

备份表：

create table 表名 as select * from t1; 

创建序列：

CREATE sequence 序列名 increment by 每次加多少 start with 从多少开始 maxvalue 99999999999 nocycle nocache;

--CREATE sequence SEQ_DECLARE_INFO increment by 1 start with 1000 maxvalue 99999999999 nocycle nocache;

-- 使用序列
select SEQ_DECLARE_INFO.nextval from dual;
select SEQ_DECLARE_INFO.currval from dual;

创建同义词

语法结构：

CREATE [OR REPLACE] [PUBLIC] SYSNONYM [当前用户.]synonym_nameFOR [其他用户.]object_name;

create synonym scms.tb_user for mobuser.tb_user;

授权

-- 其中TABLExxx是需要授权的表，userxxx是被授权者
GRANT SELECT,UPDATE,INSERT,DELETE ON TABLExxx TO userxxx;
-- 或者
GRANT ALL ON TABLExxx TO userxxx;

decode()函数

decode(字段表达式,'条件1','满足条件1的返回值','不满足的返回值');

case when

case when '条件表达式' then '满足条件时返回的值' else '不满足条件时返回的值' end

SELECT 
	tu.DEPT_ID ,
	tu.WORK_NO ,
	TU .NAME ,
	tu.ID_CARD ,
	(SELECT pm.MAPPING_POST FROM PCH_MAPPING pm WHERE pm.MAPPING_POST_ID = tu.POST) post_name,
	(CASE WHEN tbu.LOGIC_SYS_NO IS NULL THEN '0' ELSE to_char(to_char(SYSDATE,'yyyy')-substr(LOGIC_SYS_NO,1,4)) end) work_year,
	eval."RESULT",
	eval.SCORE,
	eval.ADD_TIME
FROM 
	TB_USER tu 
left join 
	tb_base_user tbu on tu.work_no = tbu.work_no

START WITH... CONNECT BY PRIOR...

用来遍历含有父子关系的表结构

select
	*
from
	tb_dept
start with
	dept_id = '44001469'
connect by 
	prior dept_id = upper_dept_id

start with 后面是根节点的限定语句。

connect by 是连接条件

prior 是用来表示上一条记录，connect by prior dept_id = upper_dept_id 表示上一条记录的 dept_id 是本条记录的 upper_dept_id ，即本记录的父亲是上一条记录。

后面还可以跟 where 条件

exists()函数

SELECT * FROM tb_user tu WHERE not EXISTS (SELECT 1 FROM TB_DEPT td WHERE tu.DEPT_ID =td.DEPT_ID)

使用 in() 函数查询，效率太慢，可用 exists() 代替

exists() 的子查询实际上并不返回任何数据，而是返回true和false

regexp_like() 正则匹配

regexp_like就是使用正则表达式校验字符串是否符合规则

例如：判断‘123456as’是否是小数或者整数

 SELECT '123' FROM dual where regexp_like('123456as','^[0-9\.]+$')

有时候判断条件中需要使用两个或者多个like操作的时候，也可以使用regexp_like来操作，例如下面的

SELECT '123' FROM dual where regexp_like('123456as','(12|49)')

就是判断字符串'123456as'中是否存在12或者49，若存在，则返回123，否则返回空

同理：使用not like不存在的时候，也可以使用not regexp_like来校验。例如下面

SELECT '123' FROM dual where not regexp_like('123456as','(12|49)')

就是判断字符串'123456as'中是否不存在12或者49，若存在，则返回123，否则返回空