oracle常用语句和函数总结

表空间

查询表空间

select * from v$tablespace;

创建表空间

create tablespace j0704_tablespace datafile 'D:\a\a.dbf' size 1000m;

查询表空间对应的数据文件

select tablespace_name,file_name from dba_data_files;

表结构

创建表

create table table_name(
id Number(10),
name Varchar2(20),
sex Char(10),
update  Date
);

修改表名

rename oldname to newname;

删除表结构和数据

drop table table_name;

增加列

alter table table_name add (column datatype );

删除列

alter table table_name drop (column);

修改列

alter table table_name modify (column datatype [default value][null/not null],….);

索引

创建索引

create index 索引名 on 表名 (列名) tablespace 表空间名;

创建唯一索引

create unique index 索引名 on 表名 (列名) tablespace 表空间名;

创建组合索引

create index 索引名 on 表名(列名1,,列名2) tablespace 表空间名;

创建反向键索引

CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空间名;

创建位图索引

CREATE bitmap INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空间名;

删除索引

drop index 索引名;

插入

整行插入

insert into table_name values(val1,val2，val3......);

可部分插入

insert into table_name(col2，col3 )values(val2，val3 );

批量插入

insert into table_name2 select * from table_name1;

删除数据

delete  from  table_name where condition(可删除部分，可回滚，速度慢)

truncate table table_name(删除全部，速度快);

修改数据

update table_name  列名称 = 新值 where 条件

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
	[UPDATE sql]
WHEN NOT MATCHED THEN
	[INSERT sql]

查询数据

Select 查询
SELECT 列名称
FROM 表名称
WHERE condition
AND/OR condition
GROUP BY 列
HAVING 函数（列）
ORDER BY 列 DESC/ASC

序列

创建序列

---------- 初始值 1，最小值 1，增长幅度 1 ，最大值 9999999999999
create sequence table_name_id_SEQ  increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 ;

create sequence table_name_id_seq  start with 1 minvalue 1;

查看序列

---- 下个序列值
SELECT table_name_id_SEQ.Nextval FROM dual;
---- 现在序列值
SELECT table_name_id_SEQ.Currval FROM dual;

删除序列

drop sequence table_name_id_SEQ ;

用户

创建用户

create user 用户名 identified by 密码;
create user 用户名 identified by 密码 default tablespace <表空间名称>;

为用户授权

grant connect,resource to robert;

常用函数

case when

1.简单CASE表达式

SELECT role_type,
CASE
WHEN role_type=1 THEN '普通用户'
WHEN role_type=2 THEN '管理员'
ELSE '不知道'
END role_type
FROM user_table 

2.搜索CASE表达式，使用条件确定返回值

SELECT 
CASE role_type 
WHEN  1 THEN '普通用户'
WHEN  2 THEN '管理员'
ELSE '不知道'
END role_type
FROM user_table

decode

1、翻译值（与case类似，但是比case更强大）

SELECT id, role_type,decode(role_type,1,'普通用户',2, '管理员',null,'无数据','其他') as name

 from user_table  ;

2、分段,连续套用decode

svg(val)函数：val的值为负数，0，正数，分别返回-1，0，1

SELECT t.amout,
       DECODE(sign(t.amout-5000),
       -1,'<=5000',
        0,'<=5000',
        1,decode(sign(t.amout-10000),
                  -1,'5000~10000',
                   0,'5000~10000',
                   1,'>10000')
       )  AS 金额                                   
   FROM test_table t  

nvl

nvl(x,value)将一个NULL转换为另外一个值，如果x为NULL,则返回value,否则返回x值本身

select nvl(address,'北京市') from student;

nvl2(x,value1,value2),如果x不为NULL，返回value1，否则，返回value2

select nvl2(address,'有地址','无地址') from student;

to_char()

转换成字符串，将时间日期按照指定的格式输出，得到是字符串

 select sysdate,to_char(sysdate,'yyyy-mm-dd')from dual;
 select sysdate,to_char(sysdate,'yyyy/mm/dd')from dual;
 select sysdate,to_char(sysdate,'yyyymmdd')from dual;
 select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;

to_date()

将字符串转换为具体指定的时间日期格式

 select sysdate,to_date('20190828','yyyymmdd')from dual;
 select sysdate,to_date('20190828','yyyy-mm-dd')from dual;
 select sysdate,to_date('20190828','yyyy/mm/dd')from dual;
 select sysdate,to_date('20190828','yyyy-mm-dd hh24:mi:ss')from dual;

to_timestamp()

字符型转成timestamp

select to_timestamp('2011-09-14 12:52:42.123456789', 'syyyy-mm-dd hh24:mi:ss.ff') from dual;