Oracle数据库sql语句

1.创建用户、赋权限、删除用户

drop tablespace test_tbs including contents cascade constraints;    --删除表空间
create tablespace test_tbs datafile 'test_pdb.dbf' size 1024m autoextend on next 50m maxsize 20480m extent management local;   --创建表空间
create user test_name identified by test_password default tablespace test_tbs;    --创建用户
grant connect to test ; 
grant resource to test ;
grant dba to test ;
drop user test cascade;

2.修改用户密码&解除锁定（需要DBA权限）

alter user SCOTT identified by tiger;
alter user SCOTT account unlock;

 3.查询锁表

SELECT'alter system kill session '''|| c.sid ||''||','|| c.serial# ||''';', a.object_id, a.session_id, b.object_name, c.*
     FROM v$locked_object a, dba_objects b, v$session c
    WHERE a.object_id = b.object_id
      AND a.SESSION_ID = c.sid(+)
      AND schemaname ='SCOTT'
     ORDER BY logon_time

4.左右连接去除笛卡尔积

关于左连接和右连接总结性的一句话：
左连接where只影向右表，右连接where只影响左表。
Left Join
select * from tbl1 Left Join tbl2 where tbl1.ID = tbl2.ID
左连接后的检索结果是显示tbl1的所有数据和tbl2中满足where 条件的数据。

 5.增加新的主键约束（约束无法直接修改，只能先删后插）

alter table 表名 add constraint 主键名 primary key(字段名);

alter table DEPT  drop constraint PK_DEPT ;
alter table DEPT add constraint PK_DEPT primary key(DEPTNO, DNAME);

6.级联删除外键（删除父表记录时，同时删除子表记录）

ALTER TABLE 子表 ADD CONSTRAINT FK_ACTIVITY_ID FOREIGN KEY (ID) REFERENCES 父表 (ID) ON DELETE CASCADE;

7.修改表名（表名大小写问题）

ALTER TABLE "DEPT" RENAME TO DEPT;

8.将本用户下全部sequence查询出来，并拼成创建语句

select 'create sequence '||sequence_name||   
       ' minvalue '||min_value||   
       ' maxvalue '||max_value||   
       ' start with '||last_number||   
       ' increment by '||increment_by||   
       (case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';' 
from user_sequences

 9.将本用户下全部表名与表名的注释查询出来：

select t.TABLE_NAME,c.comments from user_tables t,user_tab_comments c where t.TABLE_NAME=c.table_name order by t.TABLE_NAME asc

 10.分组函数

select a.count,a.code,a.date,a.status,b.sum from (SELECT count(code) count,code,date,status FROM test_table WHERE date = '20190815' GROUP BY code,date,status) a,(SELECT sum(count(code)) sum FROM test_table WHERE date = '20190815' GROUP BY code) b;

 11.删除表的comments，拼成sql语句然后复制粘贴后执行就把数据库表列的comments置为空；

select 'comment on column '||t.table_name||'.'||t.column_name||' is '''';' from user_col_comments t;

 12.给某个用户某张表的权限设置 

grant select,update,delete,insert on DataTable to OracleUser;

 13.将一个表中的某一列数据更新到另一个表中

UPDATE T_CONDETAILED SET latesprice=t_temp.amount FROM t_temp WHERE T_CONDETAILED.conNumber=t_temp.conNo

 14.在oracle中，查询字段中非纯数字值

正则判断,适用于10g以上版本
--非正整数 
select 字段 from 表 where regexp_replace(字段,'\d','') is not null;
--非数值类型
select 字段 from 表 where regexp_replace(字段,'^[-\+]?\d+(\.\d+)?$','') is not null;

 

 

 

 

常用sql待续，欢迎留言补充