------------------------------------Oracle 常用命令------------------------------------
-------------一般命令---------------- 1、清屏 clear screen ; 2、导入sql脚本 @d:/sqlCreate.sql 3、设置回滚点: savepoint aa; 4、回滚数据: rollback to aa; 5、提交事务: commit; 6、设置只读事务: --设置只读后,之后其他用户加入的数据不会影响到本用户的查询结果。 set transaction read only; 6、修改数据库的默认时间类型(默认为:"dd-mm-yyyy"): alter session set nls_date_format='yyyy-mm-dd'; 7、打开操作时间开关: set timing on ; 8、控制台中输入语句: dbms_output.put_line('雇员名:'||v_ename); --dbms_output :是包名 9、声明一个变量 declare v_ename varchar2 ; 10、给变量赋值 v_ename:='赋值' ; -----------用户管理------------ 1、创建一个用户: create user zou identified by mima; 2、修改用户的密码: password newMima; 3、连接一个新用户: conn sys/accpsys as sysdba; 4、删除用户: drop user zou; 5、查询所有用户: select * from dba_users; desc dba_users; 6、给用户赋角色权限: grant connect to zou ; 7、给某个用户授予某张表的访问权限: grant select on emp to zou ; 或者 grant add on emp to zou; 8、同时允许该用户继续授权给其他用户方式: grant add on emp to zou with grant option; 9、回收权限: revoke select on emp from zou; 10、查询用户具有权限: select * from dba_tab_privs ; 11、查询用户具有的角色: select * from dba_role_privs ; 12、查询Oracle中所有角色: select * from dba_roles ; -----------权限表------------ connect:一般开发人员就足够了 alter session create cluster create database link create sequence create session create table create view resource :具有应用开发人员所需要的其他权限,比如存储过程、触发器等。 create cluster create indextype create table create sequence create type create procedure create trigger。 dba角色:具有所有的系统权限,不具有启动和关闭数据库的权限。 -------------表的基本操作---------------- 1、新建一张表: create table users ( userId int not null primary key, userName varchar2(20), birthday date ); ----添 2、添加一条数据方法: insert into users(1,'aaa',to_date('2009-10-13','yyyy-mm-dd')); 3、一次性插入多条数据: insert into kkk(myid,myname,mydept) select empno,ename,deptno from emp where deptno=10 ----修 3、修改一条数据: update users set userName='ccc' where userId=1; 4、一次性修改多条数据: update emp set(job,sal,comm)==(select job,sal,comm from emp where ename='smtth') where ename='scott' ; ----查 4、查询一条数据: select * from users; 5、查询某列是空值的方式: select * from student where birthday is null; 6、查询表结构: desc users; 7、多表连接查询: select a1.ename,a1.sal,s2.dname from emp a1,poed a2 where a1.ename=a2.pname ; 8、用查询结果建立一张新表: create table mytable(id,name,sal,job,deptno) as select empno,ename,sal from emp ; 9、查询当前用户可以访问的所有表: select * from all_tables; --all_tables :能访问到的表 。user_tables :查询该用户的所有表。 --dba_tables :查询所有解决方案的表。 9、表的联合查询: select ~~~~~ union select ~~~~~ --关键字:union :两张表中的数据,并保证无重复数据。 -- union all :不保证无重复数据。 -- intersect :取出两张表中公共部分。 -- minus a表中已涵盖了b表,取出来的就是a表减b表的数据。 8、子查询语句: select * from emp where sal in(select sal from emp where sal=30) ; --in 是指所有的意思,=指任何一个 或者 select * from emp where (deptno,job)=(select deptno,job from emp where lie=30) ; 9、Oracle的分页写法: select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6 ; ----删 7、删除表中的数据: delete from users; 8、删除表结构,即,表。 drop table users; 或者 truncate table student; --此语句更快 ----修改列、及按照固定的格式显示数据。 9、按照固定的时间格式显示时间: select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp; 10、薪水显示方式: to_char(sal,'L99.999.99') 11、添加一个字段: alter table student add(classId number(2)); 12、删除字段(慎用) alter table student drop column sal; 13、修改表字段的长度、类型: alter table student modify(xm varchar2(30)); 14、给列起别名: select sal*12 '年薪' from emp ; -------------Oracle存储过程、视图---------------- 1、创建存储过程
create or replace procedure sp_proc1 is begin insert into my tes values('zou','aaa') ; end ; / -----显示错误信息 show error ; 2、调用存储过程: exec 过程名(参数值1,参数值2...) 或者 call 过程名(参数值1,参数值2...) 3、带输出、输入参数的存储过程: create or replace procedure sp_pro(spNo in number,spName out varchar2,spSal out varchar2) is begin select ename,sal into spName,spSal from emp where empno=spno; end; / 在Java中调用: CallableStatement cs=ct.prepareCall("{call sp_pro(?,?,?)}") ; cs.setInt(1,7788) ; cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); cs.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR); cs.execute(); String name=cs.getString(2); String sal=cs.getString(3); 4、返回集合列表: 1)、创建一个包 create or replace package testpackage as type test_cursor is ref cursor ; end testpackage ; 2)、创建存储过程: create or replace procedure sp_pro(spNo in numbar,p_cursor out tespackage.test_cursor) is begin open p_cursor for select * from emp where deptno=spNo ; end ; / 3)、Java中读取集合: ...... cs.execute(); ResultSet rs=(ResultSet)cs.getObject(2); While(rs.next()){ System.out.println(rs.getInt(1)+" "+rs.getString(2)); } --关闭连接 .... 5、分页存储过程: --开发一个包 create or replace package testpackage AS type test_cursor is ref cursor; end testpackage; --存储过程 create or replace procedure fenye (tableName in varchar2, Pagesize in number, --一页显示数量 pageNo in number, --页码 myrows out number, --总记录数 myPageCount out number,--总页数 P_cursor out tespackage.test_cursor --返回的记录集 ) is v_sql varchar2(1000) ; v_begin number:=(pageNow-1)*Pagesize+1; v_end number:=pageNow*Pagesize; begin --执行部分 v_sql:= ' select * from (select t1.* rownum rn from (select * from '|| tableName | |') t1 where rownum<='|| v_end ||') where rn>='|| begin ; --把游标和sql语句关联起来 open p_cursor for v_sql; --要计算myrows和myPageCount --组织一个sql语句 v_sql:=select count(*) from '|| tableName; --执行sql,并把返回的值,赋给myrows execute immediate v_sql into myrows; --计算my if mod(myrows,Pagesize)=0 then myPageCount:=myrows/Pagesize; else myPageCount:=myrows/Pagesize+1; end if ; --关闭游标 close p_cursor; end ; / 6、创建Oracle视图 create or replace view myView as select * from emp where no<1000 -------------Oracle的异常处理----------------
1、异常的定义: 1)、case_not_found : 2)、cursor_already_open :游标已经打开,再次打开时则会抛出异常。 3)、dup_val_on_index :在唯一索引添加重复值时,抛出的异常。 4)、invaild_cursor :当试图在不合法的游标上执行操作时,会触发该例外。 5)、invalid_number :比如应该输入数字输入的是字符串。 6)、too_many_rows :返回多行时,用一个接收就会抛出该异常。 7)、zero_divide :当执行2/0语句时,则会出触发异常。 8)、value_error :当得到的数据比变量大,则会抛出该异常。 9)、login_denide :用户登陆异常则是该异常。 10)、not_logged_on :如果用户没登陆就执行dml操作,就会触发。 11)、storage_error :如果超出了内存空间或是内存被损坏,就触发该例外。 12)、timeout_on_resource :如果oracle在等待资源时,出现了超时就触发该例外。 create or replace procedure sp_pro6(spno number) is v_sal emp.sal%type; begin select sal into v_sal from emp where empno=spno; case when v_sal<1000 then update emp set sal=sal+100 where empno=spno; when v_sal<2000 then update emp set sal=sal+200 where empno=spno end case; exception when case_not_found then dbms_output.put_line('case语句没有与'|| v_sal ||'相匹配的条件'); end ; / -------------数据备份操作操作----------------
----前提条件在Oracle的主目录下找到/bin/exp.exe,并运行 1、导出自己的表: exp userid=system/accpsystem@accp tables=(users,table2) file=d:/users.dmp 2、导出其他用户的表: exp userid=system/accpsystem@accp tables=(scott.emp) file=d:/emp.dmp 3、导出表结构: exp userid=system/accpsystem@accp tables=(users) file=d:/users.dmp rows=n 4、直接导出方式: exp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp direct=y 5、导出自己的方案: exp scott/accpscott@accp owner=scott file=d:/scott.dmp 6、导出数据库: exp userid=system/accpsystem@accp full=y inctype=complete file=d:/accp.dmp 7、导入自己的表: imp userid=scott/accpscott@accp tables=(users,emp) file=d:/users.dmp ----导入时该表不能有主外键关系。 8、导入表到其他用户内: imp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp touser=system 9、导入表结构(导入表结构而不导入数据): imp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp rows=n 10、导入表结构(导入数据而不导入表结构): imp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp ignore=y 11、导入方案: imp userid=scott/accpscott file=d:/scoot.dmp 12、导入其他解决方案(必须具有dba的权限): imp userid=system/accpsystem file=d:/system.dmp fromuser=system touser=scott 13、导入数据库: imp userid=system/accpsystem full=y file=d:/accp.dmp -----------------表空间的管理----------------- 1、创建表空间: create tablespace spoo1 datafile 'd:/test/data01.dbf' size 20m uniform size 128k; 2、使用表空间: create table myPart(deptno number(4),dname varchar2(10),loc varchar2(13)) tablespace sp001 ; 3、使表空间脱机: alter tablespace 表空间名 offline; 4、使表空间联机: alter tablespace 表空间名 online ; 5、只读表空间: alter tablespace 表空间名 read only ; 6、可读写表空间: alter tablespace 表空间名 read write; 7、查询表空间中的表: select * from all_tables where tablespace_name='表空间名' ; 8、知道表名,查询所属表空间: select tablespace_name,table_name from user_tables where table_name='emp' ; 9、删除表空间: drop tablespace '表空间' including contents and datafiles ; 10、增加数据文件: alter tablespace sp001 add datafile 'd:/test/sp001.dbf' size 20m 11、增加数据文件的大小: alter tablespace sp001 'd:/test/sp001.dbf' resize 200m ; 12、设置文件的自动增长: alter tablespace sp001 'd:/test/sp001.dbf' autoextend on next 10m maxsize 500m ; ----故障处理,将表移动到其他表空间:
1)、确定数据文件所在的表空间: select tablespace_name from dba_data_files where file_name='d:/sp001.dbf' ; 2)、使表空间脱机: alter tablespace sp001 offline ; 3)、使用命令移动数据文件到指定的目标位置: host move d:/sp001.dbf c:/sp001.dbf ; 4)、执行alter tablespace 命令: alter tablespace sp001 rename datafile 'd:/sp001.dbf' to 'c:/sp001.dbf' ; 5)、使表空间联机: alter tablespace sp001 online ; 6)、显示表空间信息: select tablespace_name from dba_tablespaces ; 7、显示表空间所有包含的数据文件: select file_name,bytes from dba_data_files where tablespace_name='表空间名' ; -------------Oracle基本类型----------------
number(5,2) :表示一个小数有5位有效数,2位小数范围:-999,99-999,99. number(5) :表示一个5位的整数 -99999-99999 number范围 -10的38次方-10的38次方可以表示整数,也可以表示小数。 char(10) :字符型,使用它比varchar的读取效益要强很多,常用属性要是用char类型,最大2000. varchar :最大4000 clob : 字符型的大对象4G。 date :精确到秒钟。比较常用。 timestamp :精度很高,精确到毫秒。 blob :二进制数据,可以存放图片/声音 4G。(很少使用,如果安全性很高的话可以使用)。
-------------Oracle约束、角色---------------- 1、添加约束: alter table customer add constraint card_uniquer unique(cardId) ; 2、删除约束: alter table customer drop constraint 约束名 ; 3、删除主键: alter table 表名 drop primary key cascade ; 4、查询约束信息: select * from user_constraints where table_name='约束名' ;
5、创建索引: create index on '索引名' on '表名'(列名,列名....) ; -------------Oracle的内置函数----------------
lower() :将字符串转换成小写。 upper() :转换大写。 length(char) :返回字符的长达。 substr(char,m,n) :截取字符串。 --第一个参数列名,第二参数是从几个开始取,第三个参数是取几个。 --示例:select upper(substr(ename,1,1)) || lower(substr(ename,2, length(ename)-1)) from emp; replace(char1,search_string,replace_string) :替换方法。 instr(char1,char2,[,n[,m]]) :查找位置。 round(n,[m]) :该函数用于执行四舍五入,如果省掉m,则四舍五入到整数;如果m是正数,则四舍五入到小数点的m位后, 如果m是负数,则四舍五入到小数点的m位前。 trunc(n,[m]) :该函数用于截取数字,如果省掉m,就截去小数部分,如果m是正数就截取 到小数点的m位后,如果m是负数,则截取到小数点的前m位。 mod(m,n) : 取余数。例:select mod(elieming,2) from emp; floor(n) :返回小于或是等于n的最大整数。 ceil(n) :返回大于或是等于n的最小整数。 abs(n) :返回数字n的绝对值。 acos(n) :返回数字的反余弦值。 asin(n) :返回数字的反正旋值。 atan(n) :返回数字的反正切。 cos(n) : exp(n) :返回e的n次幂。 log(m,n) :返回对数值。 power(m,n) :返回m的n次幂。 ----日期函数: sysdate :获取当前时间。 ADD_MONTHS(日期,月数) 返回日期 MONTHS_BETWEEN(日期,日期) 返回月数 LAST_DAY(日期) 当前日期月份最后一天 如倒数第几天,可以使用减。 ROUND(日期,模式) 当前日期舍入日期 如round(sysdate,’year’) NEXT_DAY(日期,星期) 如next_day(sysdate,'星期二’) TRUNC(日期,模式) 截断日期 EXTRACT(模式 from 日期) 提取时间部分 Oracle可以进行隐形转换数据类型。例: create table t1(id int); insert into t1 values('10') —>这样oracle会自动的将’10’转换成10。 日期显示时/分/秒。例: yy :两位数字的年份2004—>04。 yyyy :四位数字的年份 2004年。 mm :两位数字的月份8月—>08 dd :2位数字的天30号—>30 hh24 :8点—>20 hh12 :8点—>08 mi、ss —>显示分钟/秒 select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) from emp;
|