Oracle超全SQL，细节狂魔

1、  导出表
1.1、导出自己的表
exp userid=Scott/targer@wddorcl tables=(imp) file=f:\wdd.dmp;
1.2、导出其他方案的表（DBA权限，如system就可以）
exp userid=system/cfmaster@wddorcl tables=(scott.emp) file=f:\wdd1.dmp;
1.3、导出表的结构
exp userid=Scott/targer@wddorcl tables=(imp) file=f:\wpp.dmp rows=n;
1.4、使用直接导出方式（速度快，数据量大时可以考虑）
exp userid=Scott/targer@wddorcl tables=(imp) file=f:\wpp.dmp direct=y;
2、  导出方案（方案中的所有对象，表、索引、约束等）
2.1、导出自己的方案
exp userid=Scott/targer@wddorcl owner=Scott file=f:\wpp1.dmp;
2.2、导出其他方案（DBA权限，如system就可以）
exp userid=system/cfmaster@wddorcl owner=(system,scott) file=f:\wpp2.dmp;
3、  导出数据库（导出数据库中的对象及数据，需要DBA权限，如system就可以）
exp userid=system/cfmaster@wddorcl full=y inctype=complete file=f:\wdd.dmp;
4、  导入表
4.1、导入自己的表
imp userid=Scott/targer@wddorcl tables=(imp) file=f:\wdd.dmp;
4.2、导入表到其他用户（DBA权限，如system就可以）
imp userid=system/cfmaster@wddorcl tables=(imp) file=f:\wdd.dmp touser=Scott;
4.3、导入表的结构(只导入表的结构，而不导入数据)
imp userid=Scott/targer@wddorcl tables=(imp) file=f:\wpp.dmp rows=n;
4.4、导入数据（如果对象已经存在,可以只导入表的数据）
imp userid=Scott/targer@wddorcl tables=(imp) file=f:\wdd.dmp ignore=y;
5、  导入方案（方案中的所有对象，表、索引、约束等）
5.1、导入自己的方案
imp userid=Scott/targer file=f:\wpp1.dmp;
5.2、导入其他方案（DBA权限，如system就可以）
imp userid=system/cfmaster file=f:\wpp1.dmp formuser=system touser=Scott;
6、  导入数据库（导入数据库中的对象及数据，需要DBA权限，如system就可以）
imp userid=system/cfmaster full=y file=f:\wpp2.dmp;
7、  管理表空间和数据文件
7.1、建立数据表空间
SQL>create tablespace data01 datafile 'f:\data01.dbf' size 20m uniform size 128k;
7.2、使用数据表空间
SQL>create table mypart(deptno number(2),dname varchar2(14),loc varchar2(13)) tablespace data01;
7.3、改变表空间的状态
1）  使表空间脱机
SQL>alter tablespace 表空间名 offine;
2)使表空间连机
SQL>alter tablespace 表空间名 onine;
3)只读表空间
SQL>alter tablespace 表空间名 read only;
4)可读写表空间
SQL>alter tablespace data01 read write;
实例：
1)  知道表空间名，显示该表空间包括的所有表
SQL>select table_name from all_tables where tablespace_name='表空间名';
2)  知道表名，查看该表属于哪个表空间
SQL>select * from all_tables where table_name='表名';
7.4、  删除表空间
SQL>drop tablespace 表空间including contents and datafiles;(彻底删除);
7.5、  扩展表空间
1)  增加数据文件
SQL>alter tablespace data01 add datafile'f:\data02.dbf'size 30m;
2)  增加数据文件的大小
SQL>alter database datafile 'f:\data01.dbf' resize 50m;(大小不能超过500m)
3)  设置文件的自动增长
SQL>alter database datafile 'f:\data01.dbf'autoextend on next 10m maxsize 500m;
7.6、  移动数据文件
1)  确定数据文件所在的表空间
SQL>select tablespace_name from dba_data_files 
where file_name='F:\DATA02.DBF';
2)  使表空间脱机（确保数据文件的一致性，把表空间转变为Offline状态）
SQL>alter tablespace data01 offline;
3)  使用命令移动数据文件到指定的目标位置(或者剪切)
SQL>host move f:\data02.dbf e:\data02.dbf;
4)  执行alter tablespace命令
SQL>alter tablespace data01 rename datafile 'f:\data02.dbf'to'e:\data02.dbf';
5)  使得表空间联机
SQL>alter tablespace data01 online;
补充：
1）  查询表空间使用情况
SQL>SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1;
2）  查询表空间的总容量
SQL>select tablespace_name, sum(bytes) / 1024 / 1024 as MB
　　from dba_data_files
　　group by tablespace_name;
3)显示表空间所包含的数据文件
SQL>select file_name,bytes from dba_data_files
where tablespace_name='表空间名';

1、  显示当前用户约束信息
SQL> select constraint_name,constraint_type,status,validated 
from user_constraints where table_name='EMP';
2、  显示约束所对应的表列信息
SQL> select column_name,position from user_cons_columns 
where constraint_name='约束名';
3、  约束包括：not null、unique、primary key foreign key、check五种
SQL> name varchar2(50) not null
SQL> email varchar2(50) unique
SQL> goodsId char(8) primary key
SQL> cusomerId char(8) references customer(customerId)
SQL> nums number(10) check (nums between 1 and 30)
4、  增加not null约束时需要使用modify选项，而增加其他四种约束使用add选项
SQL> alter table goods modify goodsName not null;
SQL> alter table customer add constraint cardybuque unique(cardId);
--constraint cardybuque即约束及其名字（名字随便取）
5、删除约束
SQL> alter table 表名 drop constraint 约束名称;
在删除主键约束的时候可能有错误，比如说
SQL> alter table 表名 drop primary key;
--这是因为如果在两张表存在主存关系，那么删除主表的主键约束时，必须带上cascade选项
SQL> alter table 表名 drop primary key cascade；

1、  字符函数
1.1、问题：将所有员工的名字按小写的方式显示 
SQL> select lower(ename) from emp; 
1.2、问题：将所有员工的名字按大写的方式显示。 
SQL> select upper(ename) from emp; 
1.3、问题：显示正好为5个字符的员工的信息。 
SQL> select * from emp where length(ename)=5; 
1.4、问题：显示所有员工姓名的前三个字符。 
SQL> select substr(ename,1,3) from emp; 
1.5、问题：以首字母大写,后面小写的方式显示所有员工的姓名。 
SQL>select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))  
from emp; 
1.6、问题：显示所有员工的姓名，用“我是老虎”替换所有“A” 
SQL> select replace(ename,'A', '我是老虎') from emp; 
2、数学函数
2.1、问题：显示在一个月为30天的情况下，所有员工的日薪金，忽略余数。
SQL> select trunc(sal/30), ename from emp; 
or 
SQL> select floor(sal/30), ename from emp;
2.2、在做oracle测试的时候，可以使用dual表 
SQL> select mod(10,2) from dual;结果是0 
SQL> select mod(10,3) from dual;结果是1 
2.3、其它的数学函数
abs(n)： 返回数字n的绝对值 
acos(n)： 返回数字的反余弦值 
exp(n)： 返回e的n次幂 
power(m,n)： 返回m的n次幂 
3、日期函数
默认情况下日期格式是dd-mon-yy 即12-7月-78
3.1、问题：查找已经入职8个月多的员工 
SQL> select * from emp where sysdate>=add_months(hiredate,8); 
//sysdate: 该函数返回系统时间
//add_months(d,n):时间点d再加上n个月
3.2、问题：对于每个员工，显示其加入公司的天数。 
SQL> select floor(sysdate-hiredate) "入职天数",ename from emp; 
or 
SQL> select trunc(sysdate-hiredate) "入职天数",ename from emp; 
3.3、问题：找出各月倒数第3天受雇的所有员工。 
SQL> select hiredate,ename from emp 
where last_day(hiredate)-2=hiredate; 
// last_day(d)：返回指定日期所在月份的最后一天
1、  转换函数 
5.1、问题：日期是否可以显示 时/分/秒
SQL> select ename, to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’)
 from emp;
yy：两位数字的年份 2004-->04 
yyyy：四位数字的年份  2004年 
mm：两位数字的月份 8月-->08 
dd：两位数字的天 30号-->30 
hh24： 8点-->20 
hh12：8点-->08 
mi、ss-->显示分钟\秒 
5.2、问题：显示薪水的时候，把本地货币单位加在前面
SQL>select ename, 
to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'),
 to_char(sal,'L99999.99') 
from emp; 
5.3、问题：显示1980年入职的所有员工 
SQL> select * from emp where to_char(hiredate, 'yyyy')=1980; 
5.4、问题：显示所有12月份入职的员工 
SQL> select * from emp where to_char(hiredate, 'mm')=12; 
5.5、to_date 
//函数to_date用于将字符串转换成date类型的数据
 SQL>insert into emp values  
(9998, to_date('1988-12- 12', 'yyyy-mm-dd')); 
6、系统函数
//sys_context('USERENV','lanuage')
-- USERENV是固定的，不能改的，lanuage可以换成其它
1）terminal：当前会话客户所对应的终端的标示符（本地客户端的计算机名称）
2）lanuage: 语言 
3）db_name： 当前数据库名称 
4）nls_date_format： 当前会话客户所对应的日期格式 
5）current_schema： 当前会话客户所对应的默认方案名 
6）host： 返回数据库所在主机的名称 （本地客户端的工作组/计算机名称）
注：返回oracle服务器的IP地址及服务器名称
SQL>select utl_inaddr.get_host_address,utl_inaddr.get_host_name 
from dual;