部分Oracle命令（随时更新）

如有错误 请多多指教

/*
--通过SQLPLUS登录数据库
1  如果只是单一数据库
   运行cmd  回车
   然后运行 sqlplus  回车
   根据提示输入用户名和密码登录
2  运行cmd   回车
   然后运行 sqlplus username/password 回车
   或者
   运行cmd   回车
   然后运行 sqlplus /nolog
   然后  connect  username/password
3  如果有多个数据库实例
   则可以先设置自己需要用的数据库实例为当前实力
   运行cmd  
   然后运行 set oracle_sid=DBname  回车
   然后可根据上边的1 , 2 登录
*/

CREATE TABLE student
(
  SID   NUMBER(2,0) NOT NULL ,
  SNAME VARCHAR2(12) NOT NULL
)

--插入数据
INSERT INTO student VALUES
(2,'s2',22)


--更新数据
--利用子查询来更新数据
UPDATE STUDENT
SET
  (
    SID  ,
    SNAME,
    SAGE
  )=( SELECT * FROM student WHERE sid=2 ) WHERE SID=4;
  
--删除数据
DELETE FROM student WHERE SID=

--提交数据
COMMIT ;

--查询
SELECT * FROM student;
--查询中使用运算表达式
SELECT SID,SNAME,SAGE+10 FROM student;
--使用列别名
SELECT SID AS "编号", SNAME AS "姓名", SAGE AS "年龄" FROM student;
--连接字符串（在sqlplus中可以看到结果）
SELECT SNAME||'的年龄是'|| SAGE AS "学生信息" FROM student WHERE SID =2 ；


--修改表结构  
ALTER TABLE student RENAME 
COLUMN StudentID TO SID ;

ALTER TABLE student RENAME
COLUMN StudentName TO SNAME;

ALTER TABLE student RENAME
COLUMN StudentAge TO SAGE;

--查询表结构
DESC student;

--修改表结构 增加列
ALTER TABLE student ADD( SAGE NUMBER(2));

--查询前N行 相当于SQL Srever中的 top
--sql: select top 4  from tablename
SELECT * FROM student WHERE ROWNUM<=1;
-- like 查询
SELECT SNAME FROM student WHERE sname LIKE '%1%';
--IN 子句
SELECT * FROM student WHERE SID IN(1,3);
--COUNT函数 统计行数
SELECT COUNT(*) FROM student ;
--avg函数 平均值
SELECT AVG(SID) FROM student;
--sum 函数 求和
SELECT SUM(SID) FROM student;
--MAX MIN 函数
SELECT MAX(SID),MIN(SID) FROM student;



--PL/SQL块
DECLARE
  v_age NUMBER(2);
  --以上为定义一个整形变量
BEGIN
  v_age := 60;                          -- 给整形变量赋值
  dbms_output.put_line('测试输出：'||v_age); --输出变量的值
END;
--SQL/PL 语句块在 sql plus中的编写
/*VAR AGE NUMBER
BEGIN
SELECT SGAE INTO :AGE FRON STUDENT WHERE SID=2;
END;
/
PRINT AGE ;
*/
--if else 语句
DECLARE
  v_x NUMBER(2);
  v_y NUMBER(2);
BEGIN
  v_x   :=3;
  IF v_x <2 THEN
    v_y :=1;
  ELSE
    v_y := 0;
  END IF;
  DBMS_OUTPUT.put_line('V_X:'||v_x);
  DBMS_OUTPUT.put_line('V_Y:'||v_y);
END;

--创建和被复制的表结构相同的表 但是不会想新表中写入数据  where 1=2
create table table_name(新表) as select * from table_name(被复制的表) where 1=2
--修改用户密码
ALTER USER "UserName" IDENTIFIED BY "NewPwd"

 

SELECT A.*, CASE   
 WHEN sage= 12 THEN '等于12'
 WHEN  sage>12  THEN '大于12'
  ELSE 'UNKNOW' END 
FROM testtable  A

SELECT A.*, CASE  sage
 WHEN  12  THEN '等于12'
 WHEN  24  THEN '等于24'
  ELSE 'UNKNOW' END 
FROM testtable  A
--注意二者的区别 一个需要在case后边加上字段名 另一个不需要 

 

 
--取两日期之间的差(天数)
select  to_date('2012-05-04','YYYY-MM-DD')-trunc(sysdate) from dual

--trunc 的用法 
 
--一、用于date类型 

SELECT trunc(sysdate,'mm')FROM dual --返回当月的第一天 
SELECT trunc(sysdate,'yyyy')FROM dual --返回当年第一天 
SELECT trunc(sysdate,'day')FROM dual --返回当周第一天 
SELECT trunc(sysdate,'dd')FROM dual --返回当天 
SELECT trunc(sysdate)FROM dual --返回当天 


--二、用于number类型 

select trunc(1.1415926) from dual --截掉小数部分 
select trunc(1.1415926,2) from dual --保留两位小数（不做四舍五入处理） 
select trunc(1.1415926,-1) from dual --返回零 截取整数部分第一位，并以零代替 

 

--修改时间的显示格式

select  sysdate from dual;

ALTER SESSION SET NLS_language=american;

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-mm-DD';

 

--修改列名 但是已经有数据 不能直接修改

alter table table_name add  temp varchar2(300) ;

update table_name set  temp = old_cloumn;

alter table table_name rename column old_cloumn to temp2; 

alter table table_name rename column temp to old_cloumn ; 

--删除之前注意数据是否已经复制成功 temp2保存原始数据 

alter table table_name drop column temp2;

 

关于数据的导入导出

---------------------------------
--创建虚拟目录  该命令并不会在物理磁盘上创建该目录  需要手动创建  
--BACK_DIR 目录名称  as 后的为目录路径
create directory BACK_DIR as 'E:\Work\Backoracle_dump';

--查询创建了那些目录
select * from dba_directories

--删除目录
DROP directory  BACK_DIR ;

--导出数据
--BU_USER/bu13991@bu13991   uid/pwd@dSID  
--DIRECTORY=EXPDP_DIR       如上创建的目录名
--DUMPFILE=S_SPXX_73.dump   导出之后的文件名
--VERSION=10.2.0.1.0        版本    
--TABLES=S_SPXX_73          从哪个表导出
Expdp BU_USER/bu13991@bu13991 DIRECTORY=EXPDP_DIR DUMPFILE=S_SPXX_73.dump VERSION=10.2.0.1.0    TABLES=S_SPXX_73

--  参数基本与导出数据相同

impdp bu_user/bu13991@bu13991  directory=EXPDP_DIR  dumpfile= S_UNION_COUNT_LOG_73.dump table_exists_action = replace 

---------------------------------------------------------------------------------

ExpDB_Impdb导入导出数据表

-- 一  创建要导出的目录

CREATE DIRECTORY DRI_Backup AS 'E:\Work\Backoracle_dump';
-- E:\Work\Backoracle_dump 该目录需要手动创建 

-- 二  在dos中执行以下命令
    
--导入表和库
--表:
  Impdp USER/PWD@SID DIRECTORY=DRI_Backup  DUMPFILE=S_SPXX_73.dump  VERSION=10.2.0.1.0 TABLE_EXISTS_ACTION = replace   LOGFILE=ImpdbTableLog.log
    
--Impdp bu_user/bu13991@bu13991 DIRECTORY=DRI_Backup  DUMPFILE=S_CONFIG_73.dump    table_exists_action = replace   LOGFILE=ImpdbTableLog.log
--S_SPXX_73.dump<导出后文件名>    VERSION=10.2.0.1.0<版本>  TABLES=S_SPXX_73<要导出的表名>
--库:
  Impdp USER/PWD@SID DIRECTORY=DRI_Backup  DUMPFILE= BU13991_20120418.DUMP  TABLE_EXISTS_ACTION  = replace  LOGFILE=ImpdbDBLog.log
--Impdp bu_user/bu13991@bu13991 DIRECTORY=DRI_Backup  DUMPFILE= BU13991-20120418.DUMP  table_exists_action = replace  LOGFILE=ImpdbDBLog.log

--导出表和库
--表:
  Expdp USER/PWD@SID DIRECTORY=DRI_Backup DUMPFILE=S_SPXX_73.dump    TABLES=S_SPXX_73   LOGFILE=ExpdbTableLog.log
--Expdp bu_user/bu13991@bu13991 DIRECTORY=DRI_Backup  DUMPFILE=S_CONFIG_73.dump     TABLES=S_CONFIG_73    LOGFILE=ExpdbTableLog.log
--S_SPXX_73.dump<导出后文件名>    VERSION=10.2.0.1.0<版本>  TABLES=TABLE_NAME<要导出的表名>
--库:
  Expdp USER/PWD@SID DIRECTORY=DRI_Backup  DUMPFILE= BU13991_20120218.DUMP  FULL=Y    LOGFILE=ExpdbDBLog.log --全库导出
--Expdp bu_user/bu13991@bu13991 DIRECTORY=DRI_Backup   schemas=BU_USER DUMPFILE= BU13991_20120418.DUMP   LOGFILE=ExpdbDBLog.log -- 只导出该用户

 

以下是来自网络的更详细的解释

/*
一、创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。
create directory dpdata1 as 'd:\test\dump';

二、查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）
select * from dba_directories;

三、给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予。
grant read,write on directory dpdata1 to scott;

四、导出数据
1)按用户导
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
2)并行进程parallel
expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3)按表名导
expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
4)按查询条件导
expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';
5)按表空间导
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6)导整个数据库
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;

五、还原数据
1)导到指定用户下
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;
2)改变表的owner
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;
3)导入表空间
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;
4)导入数据库
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
5)追加数据
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append;


使用exclude,include导出数据
1、Include导出用户中指定类型的指定对象
--仅导出lttfm用户下以B开头的所有表,包含与表相关的索引，备注等。不包含过程等其它对象类型：
expdp lttfm/lttfm@fgisdb dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=TABLE:\"LIKE \'B%\'\"

--导出lttfm用户下排除B$开头的所有表：
expdp lttfm/lttfm@fgisdb schemas=lttfm dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=TABLE:\"NOT LIKE \'B$%\'\"

--仅导出lttfm用户下的所有存储过程：
expdp lttfm/lttfm@fgisdb schemas=lttfm dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=PROCEDURE;   

2、Exclude导出用户中指定类型的指定对象
--导出lttfm用户下除TABLE类型以外的所有对象，如果表不导出那么与表相关的索引，约束等与表有关联的对象类型也不会被导出:
expdp lttfm/lttfm@fgisdb schemas=lttfm dumpfile=exclude_1.dmp logfile=exclude_1.log directory=dir_dp job_name=my_job exclude=TABLE;

--导出lttfm用户下排除B$开头的所有表：
expdp lttfm/lttfm@fgisdb dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job exclude=TABLE:\"LIKE\'b$%\'\";

--导出lttfm用户下的所有对象，但是对于表类型只导出以b$开头的表：
expdp lttfm/lttfm@fgisdb dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job exclude=TABLE:\"NOT LIKE \'b$%\'\";


*/