oracle基本操作语句总结

1.命令行操作oracle(cmd)
1).->sqlplus(进入oracle-sql)
2).->username(输入用户名)
3).->password(输入密码)

2.创建表空间
sql>create tablespace businessplatform datafile 'I:\app\lenovo\oradata\orc\businessplatform.dbf' size 200m autoextend on next 10m maxsize unlimited;   
sql>alter database datafile 'D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf' autoextend on;
1) DATAFILE: 表空间数据文件存放路径
2) SIZE: 起初设置为200M
3) UNIFORM: 指定区尺寸为128k,如不指定，区尺寸默认为64k
4) 空间名称histdb 与 数据文件名称 histdb.dbf 不要求相同,可随意命名.
5) AUTOEXTEND ON/OFF 表示启动/停止自动扩展表空间
6) alter tablespace datafile ' D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf ' resize 500m; //手动修改数据文件大小为500M

3.删除表空间
sql>DROP TABLESPACE histdb INCLUDING CONTENTS AND DATAFILES; 

4.创建用户
sql> create user cocis identified by cocis;

5.创建用户,同时为用户指定缺省的永久表空间和临时表空间
sql> create user cocis identified by cocis    default tablespace cocis  temporary tablespace temp;

6.删除用户
sql> drop user cocis;

7.权限授予
1)如果要授予SYSDBA的权限给用户则用如下命令：
SQL> grant SYSDBA to cocis;
2)授予DBA权限
sql> grant dba to cocis;
3)sql> grant connect,resource to cocis;     
注释：当用户创建之后，一般只需要授予CONNECT和RESOURCE这两个角色即可。若要单独进行授权，则需执行单独的授权命令，如
grant create table to cocis;等。
4)sql>grant create session to cocis(授予用户会话权利)
5)sql>grant unlimited namespace to cocis(授权使用表空间)

8.权限回收
sql> revoke unlimited tablespace from cocis;  
sql> revoke create table from cocis;等

9.命令行导出数据库
1).导出
exp database file=d:\temp.dmp;
exp ip@database file=d:\temp.dmp;
按提示：输入用户名密码即可导出
2).导出整个username用户下所有对象
exp username/password@database file=d:\temp.dmp;
exp username/password@ip/database file=d:\temp.dmp;
3).导出空数据表
exp username/password@database file=d:\temp.dmp rows=n;

10.命令行导入数据库
1).导入username用户对象.
imp 用户名/口令@数据库  回车 
imp username/password@database
或者imp username/password@ip/database
2)填写导入文件路径：EXPDAT.DMP>c:\a.dmp 
3)输入插入缓冲区大小：默认不填 回车 
4)只列出导入文件的内容：   回车 
5)忽略创建错误：yes 
6)导入权限：yes 
7)导入表数据：yes 
8)导入整个导出文件：yes 
9)等待…… 
10)成功终止导入，但出现警告

11.查询系统表
1)ELECT * FROM ALL_TABLES;系统里有权限的表
2)SELECT * FROM DBA_TABLES; 系统表
3)SELECT * FROM USER_TABLES; 当前用户下的表

12.删除数据恢复
select dbms_flashback.get_system_change_number from dual;
select * from tablename as of scn 26900126;

CREATE TABLE QUICK_TABLE AS
SELECT * FROM tablename AS of scn 26902314;

13.system等用户密码忘记
cmd
sqlplus /nolog
conn  / as sysdba
alter user system identified by manager
然后
conn system/manager

14.创建存储过程
CREATE OR REPLACE PROCEDURE pro_bobo (
       resultList OUT SYS_REFCURSOR,
       t_NAME IN VARCHAR2
)
IS
BEGIN
 OPEN resultList FOR SELECT *  FROM t_sys_user WHERE NAME LIKE '%'|| t_NAME || '%';
END pro_bobo;

15.查询当前用户默认表空间
select username,default_tablespace from user_users;

16.查看当前用户的角色
SQL>select * from user_role_privs;

17.查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;

18.查看用户下所有的表
SQL>select * from user_tables;

19.显示当前会话所具有的权限
SQL>select * from session_privs;

20.显示指定用户所具有的系统权限
SQL>select * from dba_sys_privs where grantee='GAME';

21.查看某表的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');

22.查看函数和过程的状态
SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';

23.分组查询，查询最近的一条记录
A表中：
SELECT DISTINCT FIRST_VALUE(a.ID) OVER(PARTITION BY A.NAME ORDER BY A.TIME DESC)
from A a;

24.wm_concat(column)函数实现字段合并
SELECT wmsys.wm_concat(t.a)  FROM A t

25.decode函数(将查询结果翻译成其他值)
SELECT decode(p.status,11,'A',12,'B','C')  FROM A p 
--当ststus=11时输出A,12时输出B,否则C

26.to_char(日期转字符串)
SELECT to_char(SYSDATE,'yyyy-MM-dd hh24:mi:ss') FROM dual t

27.cast(类型转换)
--char类型转varchar类型，并去除左右的空格
SELECT trim(cast(s.status as varchar(2))) FROM s

28.translate ( 'char' , 'from_string' , 'to_string' )
--TRANSLATE返回将from_string中的每个字符替换为to_string中的相应字符
--以后的string。TRANSLATE是REPLACE所提供的功能的一个超集。
--如果from_string比to_string长，那么在from_string中而不在
--to_string中的额外字符将从char中被删除，因为它们没有相应的替换字符
--to_string不能为空。Oracle将空字符串解释为NULL，
--并且如果TRANSLATE中的任何参数为NULL，那么结果也是NULL
select translate('123abc','2dc','4e') from dual;

29.TRUNC
 1).trunc(DATE,format)，截取日期
   当前日期是：2012-08-10 星期五
   format为年时，精确到年
   SELECT trunc(SYSDATE,'yyyy') FROM dual ;
   结果：2012-1-1
   
   format为月时，精确到年-月
   SELECT trunc(SYSDATE,'mm') FROM dual ;
   结果：2012-8-1
   
   format为日时，精确到年-月-日
   SELECT trunc(SYSDATE,'dd') FROM dual ;
   结果：2012-8-10
 
 2).trunc(number[,decimals]) 截取数字
   没有decimals，去除小数点后的数据
   SELECT trunc(1925.0589) FROM dual;
   结果：1925
   
   decimals为正整数,指明需保留小数点后面的位数
   SELECT trunc(124.16666,2) from dual;
   结果：124.16
   
   decimals为负整数,表示为小数点左边指定位数后面的部分截去，即均以0记
   SELECT trunc(124.16666,-2) from dual;
   结果：100
   
 30.Round 函数
	语法为ROUND(number，num_digits)
    其中Number是需要进行四舍五入的数字；Num_digits为指定的位数，按此位数进行四舍五入，
	如果 num_digits 大于 0，则四舍五入到指定的小数位，如果 num_digits 等于 0，
	则四舍五入到最接近的整数，如果 num_digits 小于 0，则在小数点左侧进行四舍五入。

	例如：

	ROUND(21.129,0) 将 21.129 四舍五入到一个整数结果为21。

	ROUND(21.129,2) 将 21.129 四舍五入到两个小数位，结果为21.13。

	ROUND(21.129,1) 将 21.129 四舍五入到一个小数位结果为21.1。

	ROUND(-21.129,2) 将 -21.129 四舍五入到两小数位结果为-21.13。

	ROUND(21.129, -1) 将 21.129 四舍五入到小数点左侧一位结果为20。
	 
	操作演示：
	SELECT ROUND(21.129,0) FROM dual;
	ROUND(21.129,0)
	21

	SELECT ROUND(21.129,2) FROM dual;
	ROUND(21.129,2)
	21.13

	SELECT ROUND(21.129,1) FROM dual;
	ROUND(21.129,1)
	21.1

	SELECT ROUND(-21.129,2) FROM dual;
	ROUND(-21.129,2)
	-21.13

	SELECT ROUND(21.129, -1) FROM dual;
	ROUND(21.129, -1)
	20
	
31.add_months(增加或减去月份 )
select to_char(add_months(to_date('1999-12-01','yyyy-mm-dd'),2),'yyyy-mm-dd') from dual; 
2000-02-01
select to_char(add_months(to_date('1999-12-01','yyyy-mm-dd'),-2),'yyyy-mm-dd') from dual; 
1999-10-01

32.LAST_DAY(返回日期的最后一天) 
select to_char(sysdate,'yyyy-mm-dd'),to_char((sysdate)+1,'yyyy-mm-dd') from dual; 
to_char(sysdate,'yyyy-mm-dd')  to_char((sysdate)+1,'yyyy-mm-dd')
2012-08-10                     2012-08-11
select last_day(sysdate) from dual; 
2012-8-31 17:37:48

SELECT last_day(to_date('2012-08-08','yyyy-mm-dd')) from dual; 
2012-8-31

33.增加字段

alter table A

add column col1 varchar2(100);





34.删除字段

alter table A

drop column col1





35.修改字段类型长度

alter table A

modify col1 varchar2(500);


36.创建索引
create index myindex on chart(year);


37.创建唯一索引
create unique index mm on course(id);


38.删除索引
drop index mm on course;