Oracle常用sql语句整理

本文章，有参考别的参考文献，参考别的博客，谢谢

--创建一个永久性表空间，路径可选

create tablespace Demo datafile 'f:\Demo01.dbf' size 10M;
--创建一个自动增长的表空间
create tablespace Demo datafile 'f:\Demo02.dbf' size 10M autoextend on;
--创建临时表空间
create temporary tablespace tempDemo03 tempfile 'f:\Demo03.dbf' size 10M;
--删除表空间（同时删除文件）
drop tablespace tempDemo03 including contents and datafiles;
--删除表空间（不删除文件）
drop tablespace Demo;
--查询表空间基本信息
select * from dba_tablespaces;
--查询正在运行的后台进程
select * from v$bgprocess where paddr <> '00';
--创建新用户并指定表空间
create user hk identified by hk default tablespace Demo temporary tablespace temp;
--查看用户hk的信息
select * from dba_users where username='hk';
--修改用户名密码
alter user hk identified by YJ;
--删除用户
drop user hk;
--删除用户（用户中有信息时，用cascade）
drop user hk cascade;
--连接权限
grant connect to hk;
--使用表空间权限
grant resource to hk;
--授权创建表权限
grant create table to hk;
--dba系统权限
grant dba to hk;
--授权用户使用表空间Demo的权限
alter user hk quota unlimited on Demo;
--授予对象权限  
--注意做上面这些事情时，你必须使用emp表的所有者scott登陆
grant select on emp to hk; 
--使用管理员sys或者system授权，那么管理员登陆后就处于管理员所对应  
--的模式下面，所以就必须加上模式名.  
grant select on scott.emp to hk; 
--在做其他事情也是同上。  
grant delete|update|all on scott.emp to hk;
--锁定用户
alter user hk account lock;
--解锁用户
alter user hk account unlock;
--连接
Connnect hk/hk;
--查询用户创建的表信息
select * from user_tables;
--创建表userinfo  
CREATE TABLE userinfo(  
   stu_id varchar2(10) not null,  
   stu_name varchar(20) not null  
);
--添加记录（指定添加字段）  
insert into userinfo (stu_id,stu_name)values(1,'1');  
commit;  
--添加记录（默认全部字段）  
INSERT INTO userinfo  VALUES (2,'alex','1');  
commit;  
--如果表中有记录，删除所有记录，以便于修改表中字段类型  
delete   userinfo ;
--将需要更改类型字段，全部赋值为空  
update userinfo t set t.stu_sex='';  
--修改userinfo表中字段类型为number        
ALTER TABLE userinfo MODIFY (stu_id NUMBER);  
ALTER TABLE userinfo MODIFY (stu_name varchar(20));  
ALTER TABLE userinfo MODIFY (stu_sex NUMBER);  
--添加一个字段属性(注意两种创建方式)    
ALTER TABLE userinfo ADD("upwd" varchar(50) not null);  
ALTER TABLE userinfo ADD(stu_sex varchar(50) );
--删除一个字段属性  
ALTER TABLE userinfo DROP COLUMN "upwd";  
ALTER TABLE userinfo DROP COLUMN upwd;  
    
--删除表信息及结构   
DROP TABLE userinfo; 
--GRANT 操作授权  
--赋权限，让其他用户查询dept表  
GRANT SELECT ON scott.emp TO hk  
--赋角色  
GRANT RESOURCE TO hk  
--REVOKE –解除权限  
--在scott下，解除其他用户查询dept表的权力  
revoke select on scott.emp from hk  
  
--解除alex用户的RESOURCE权限  
REVOKE "RESOURCE" FROM hk  
  
--表示把创建表的权限赋予所有人  
grant create session to public;  
--返回当前用户所有的对象权限  
select * from user_tab_privs  
  
--对象权限可以控制到列 ,注意:查询和删除不能控制到列      
grant update(name) on mytab to hk;   
grant insert(id) on mytab to hk;   
--权限的传递   
--系统权限的传递：   
grant alter table to A with admin option;   
--那么A可以通过把该权限传递给B，如果想B也可以传递下去那么可以也带上with admin option   
grant alter table to B;   
--对象权限的传递:   
grant select on mytab to A with grant option;   
--那么A可以把在表mytab的select权限赋予给B，如果B想也能传递该select权限也可以带上with grant option   
grant select on mytab to B;   
 
----------------------------------------------------------------------------------------------------  
--创建表user1  
CREATE TABLE user1(  
   stu_id number not null,  
   stu_name varchar(20) not null,  
   stu_add varchar(50)   
)  
  
--插入值  
insert into user1 values (5,'用户5','四川路5号');  
commit;  
  
--创建表user2  
CREATE TABLE user2(  
   stu_id number not null,  
   stu_name varchar(20) not null,  
   stu_add varchar(50)   
)  
  
select * from user1;  
select * from user2;  
  
--批量插值  
INSERT INTO user2(stu_id,stu_name,stu_add) SELECT stu_id,stu_name,stu_add FROM user1;  
  
--复制一个表（带结构带值）  
CREATE TABLE users7 AS (SELECT * FROM user1);  
  
--复制一个表（只复制表结构）  
CREATE TABLE users6 AS (SELECT * FROM user1 where 1<0);  
  
--复制一个表（选择字段，带值）  
CREATE TABLE users5 AS SELECT stu_id,stu_name FROM user1 WHERE stu_id=5  
  
--TCL - 事务控制语言------------------------------------------------------------------------------------------------  
--创建表 test  
create table test  
(  
       testid number not null,  
       testname varchar2(20) not null,  
       testvalue varchar2(20),  
       testdate date,  
       testcontent varchar(1000)  
)  
  
--删除表 test 内的信息  
delete from test  
  
--查询表 test  
select * from test  
  
  
--插入信息  
insert into test values (01,'测试1','测试1',to_date('2008-8-14','YYYY-MM-DD'),'');  
--设置保存点 one  
savepoint one;  
--插入信息  
insert into test values (02,'test3','测试2','','');  
--设置保存点 two  
savepoint two;  
--回滚到保存点 one  
rollback to savepoint one;  
  
--oracle函数 - 单行函数（注：函数可嵌套使用）---------------------------------------------------------------  
  
--当前字符长度（6）  
select length('abcdef') from dual  
--当前字符字节（10）  
SELECT LENGTHB('abc你好def') FROM dual   
--ltrim：左边空格字符去掉 rtrim：右边空格字符去掉  trim：去掉两边的空格  
select ltrim(' 123 ') from dual;  
select rtrim(' 123 ') from dual;  
select trim(' 123 ') from dual;  
  
--截取字符串 前3个字符  
SELECT SUBSTR('abcdefg',1,3) FROM dual;  
  
--截取字符串 后3个字符   
SELECT SUBSTR('abcdefg',LENGTH('abcdefg')-3+1,3) FROM dual;  
  
--testname字段为从第2位开始的3个字符  
select testid,substr(substr(testname,1,4),length(substr(testname,1,4))-3+1,3) as test from test;  
  
--获取当前时间  
SELECT sysdate FROM dual;  
select current_date from dual ;  
--ALTER SESSION SET NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss';  
--next_date：给定一个时间，查询下一个日期时间  
SELECT NEXT_DAY(sysdate,'星期三') FROM dual;  
  
--to_char：转换成为char类型  
--to_date：转换成日期类型，和to_date类型相反  
--to_number：转换整型，非数字类型不能转换  
SELECT TO_CHAR(sysdate,'yyyy-mm-dd') FROM dual;  
SELECT TO_Date('2060-09-12','yyyy-mm-dd') FROM dual;  
  
--排序(倒排)  
SELECT * FROM user1 ORDER BY stu_id DESC  
  
--查询当前用户  
SELECT user FROM dual;  
  
  
--decode：语句判断函数（和switch 类似）  
--语法：SELECT SUM (DECODE(字段名,'条件',为true,为false)) FROM 表名;  
--查询一个表中男的数量  
SELECT SUM (DECODE(sex,'男',1,0)) 男人数 FROM userinfo;  
  
--多条件判断，0为女，1为男，其他为'未知'  
select stu_name,decode(stu_sex,'0','女','1','男','未知') from userinfo  
  
--nvl空值判断函数，为空显示'未输入'  
SELECT nvl(stu_sex,'未输入') FROM userinfo  
  
--nvl2 ,类似if-else 。  
SELECT stu_id,stu_sex, NVL2(stu_sex,'不为空','空') FROM userinfo;  
  
--NULLIF， stu_sex是0则为null，不是则返回原值  
SELECT stu_id,stu_sex, NULLIF(stu_sex,'0') FROM userinfo;  
  
--oracle函数 - 分组函数 ---------------------------------------------------------------  
--聚合函数：sum，avg，max，min，count（注：聚合函数不能作为条件出现在where条件的后面）  
  
--删除scoreinfo表  
drop table scoreinfo;  
commit;  
  
--创建scoreinfo表  
create table scoreinfo  
(  
  
       stu_id number primary key,  
       stu_name varchar2(20) not null,  
       stu_sex varchar2(2),  
       stu_chinese number(10,2) default 0,  
       stu_math number(10,2) default 0,  
       stu_english number(10,2) default 0,  
       stu_ext1 varchar2(200),  
       stu_ext2 varchar2(200),  
       stu_ext3 varchar2(200)  
)  
--插入数据  
insert into scoreinfo  values (1,'jack','1',89,77.5,99,'','','');  
insert into scoreinfo  values (2,'lily','2',47,56,91,'','','');  
insert into scoreinfo  values (3,'jim','1',90,92,96,'','','');  
insert into scoreinfo  values (4,'linda','2',98,68,88,'','','');  
commit;  
  
--利用聚合函数查询  
select * from scoreinfo  
select sum(stu_chinese) from scoreinfo  
select avg(stu_chinese) from scoreinfo  
select max(stu_chinese) from scoreinfo  
select min(stu_chinese) from scoreinfo  
select count(stu_id) from scoreinfo  
  
--GROUP BY，查询各个班级chinese总成绩  
SELECT stu_ext1,sum(stu_chinese) from scoreinfo group by stu_ext1;  
  
--HAVING子句，查询chinese总成绩大于150的班级  
SELECT stu_ext1,sum(stu_chinese) from scoreinfo group by stu_ext1 having sum(stu_chinese)>150;  
  
--oracle函数 - 分析函数-----------------------------------------------------------------------------------------  
  
--用管理员权限,赋予用户查询 scott.emp 表权限  
GRANT SELECT ON scott.emp TO hk  
  
--根据sal高低排序,排名没有并列  
SELECT empno, deptno, ename,sal,ROW_NUMBER() OVER (ORDER BY sal DESC) AS rank FROM scott.emp  
         
--分组排名,排名没有并列  
select empno,deptno,ename,sal,row_number()over(partition by deptno order by sal desc ) as rank from scott.emp   
  
--排名有并列,下一名不顺排  
select empno,deptno,ename,sal,rank() over(order by sal desc) as rank from scott.emp  
  
--分组排名,排名有并列,下一名不顺排  
select empno,deptno,ename,sal,rank() over(partition by deptno order by sal desc) as rank from scott.emp  
  
--排名有并列,下一名顺排  
select empno,deptno,ename,sal,dense_rank() over (order by sal desc) as rank from scott.emp  
  
--分组排名,排名有并列,下一名顺排  
select empno,deptno,ename,sal,dense_rank() over (partition by deptno order by sal desc )as rank from scott.emp