Oracle数据库常用SQL

-- Oracle ORA-00984: column not allowed here/ORA-00984错误: 列在此处不允许
-- 当数据以char的形式存在时,应加单引号,则插入数据库就不会出现类似错误.

-- Oracle实现select的结果集随机展示
select * from tablename order by dbms_random.value();

-- oracle四舍五入保留两位小数
select TO_CHAR('变量','FM99999999999.00') from dual;

-- 强制使用索引
SELECT  /*+ROWID(tablename)*/   count(1)  FROM tablename;

-- oracle日期查询
select to_date('2020-01-01 10:22:18','yyyy-MM-dd HH:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
-- DDD 一年中的第几天
select sysdate,to_char(sysdate,'yyyy-ddd hh24:mi:ss') from dual;-- 2020-167 14:59:16
-- IW ISO标准的年中的第几周，D 一周中的第几天，周日算第一天
select sysdate,to_char(sysdate,'yyyy-mm iw-d hh24:mi:ss') from dual;-- 2020-09 39-2 14:59:16
-- 当前时间减去7分钟的时间 
select sysdate,sysdate - interval '7' MINUTE from dual;
-- 当前时间减去7小时的时间 
select sysdate,sysdate - interval '7' hour from dual;
-- 当前时间减去7天的时间 
select sysdate,sysdate - interval '7' day from dual;
-- 当前时间减去7月的时间 
select sysdate,sysdate - interval '7' month from dual;
-- 当前时间减去7年的时间 
select sysdate,sysdate - interval '7' year from dual;
-- 时间间隔乘以一个数字，减去7小时的时间
select sysdate,sysdate - 3.5 *interval '2' hour from dual;

-- trunc()用于截取时间或者数值，返回指定的值
select trunc(sysdate ,'YEAR') from dual;-- 2020-01-01 00:00:00
select trunc(sysdate) from dual;-- 2020-09-23 00:00:00
select to_char(trunc(sysdate ,'YYYY'),'YYYY') from dual;-- 2020
select trunc(12.345,2) from dual;-- 12.34;
select trunc(12.3,-2) from dual;-- 0
select trunc(123.4,-2) from dual;-- 100
select trunc(12.3) from dual;-- 12

--返回当前时间的日月年小时分秒毫秒，可以指定秒后面的精度(最大=9)
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;-- 11-SEP-2020 14:55:23.29600

-- 行转列函数listagg
SELECT LISTAGG(to_char(columns1),',') WITHIN GROUP(ORDER BY columns2) AS columns1 from tablename;-- 分组和排序关键字是必须的
-- 上面的sql可能会导致ORA-01489: result of string concatenation is too long，可使用下面的方式
select xmlagg(xmlparse(content columns1||',' wellformed) order by columns2).getclobval() as columns1 from tablename;


-- 获取用户下所有表名
select table_name from user_tables where TABLESPACE_NAME is not null and  user='USER_NAME';
-- 获取表的列名和数据类型
SELECT table_name, column_name, data_type FROM all_tab_cols WHERE table_name = 'tablename ';
-- 查看所有用户对象，包含了表，视图，索引，函数等
select uo.object_name,uo.object_type from user_objects uo where uo.object_type<>'LOB' order by uo.object_type desc;
-- 查询没有索引的表				
select table_name from user_tables where table_name not in(select distinct table_name from user_indexes);

-- 删除用户下的所有表，DML,可恢复，不会自动提交事务，只删除内容。速度drop>truncate>delete
SELECT 'DELETE FROM '|| table_name || ';' FROM USER_TABLES ORDER BY TABLE_NAME;
-- 删除用户下的所有表，DDL,不可恢复，会自动提交事务，只删除内容
SELECT 'TRUNCATE TABLE '|| table_name || ';' FROM USER_TABLES ORDER BY TABLE_NAME;
-- 彻底删除，下面包含了删除后残留的BIN$开头的表，DDL,不可恢复，会自动提交事务，删除表结构和内容
SELECT 'DROP TABLE '||table_name||';' FROM CAT WHERE TABLE_TYPE='TABLE' ORDER BY TABLE_NAME;
-- 如果有删除用户的权限，使用下面的语句，删除后再创建该用户。加了cascade就可以把用户和数据全部删掉
drop user user_name cascade;

-- 禁止所有的外键约束
SELECT 'ALTER TABLE ' || table_name || ' disable CONSTRAINT ' || constraint_name || ';' FROM user_constraints where CONSTRAINT_TYPE = 'R';	
-- 打开已经禁止的外键
SELECT 'ALTER TABLE ' || table_name || ' enable CONSTRAINT ' || constraint_name || ';' FROM user_constraints where CONSTRAINT_TYPE = 'R';

-- 如何删除拥有1亿条数据的表，只保留其中的5条（为什么不把5条数据放到临时表后，把原表drop掉，把临时表改名）
into #临时表 select 5条数据 from 1亿条数据的表;
truncate table 1亿条数据的表;
insert 1亿条数据的表 select * from #临时表;
drop table #临时表;

-- 查看索引状态//ORA-01502: index 'index_name' or partition of such index is in unusable state，一般是因为索引状态为UNUSABLE引起的
SELECT OWNER, INDEX_NAME,STATUS FROM ALL_INDEXES WHERE INDEX_NAME='indexname';
SELECT INDEX_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME='indexname';
-- 重建索引
ALTER INDEX indexname REBUILD;