获取oracle数据库当前用户下所有表名和表名的注释

select a.TABLE_NAME,b.COMMENTS
from user_tables a,user_tab_comments b
WHERE a.TABLE_NAME=b.TABLE_NAME
order by TABLE_NAME

oracle 用户对象的导导出

exp devimage/oracle@172.xx.x.xx/TESTDB owner='devimage' file=d:/devimage.dmp log=d:/devimage.log
imp wxtest5star03/123456@localhost/orcl FROMUSER='devimage' TOUSER='wxtest5star03' FILE=D:/devimage.dmp log=d:/wxtest5star03.log IGNORE=Y

oracle 创建用户

create user devtest10 identified by dev10
default tablespace TBS_BCP_DAT
temporary tablespace user_temp;
grant connect,resource,dba to devtest10;

oracle 创建表空间

create tablespace DATA_TESTKIDSWANT
logging
datafile 'E:\app\Administrator\oradata\orcl\ DATA_TESTKIDSWANT.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

Oracle 11G在用EXPORT导出时，空表不能导出。11G中有个新特性，当表无数据时，不分配segment，以节省空间

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
SQL Select语句完整的执行顺序：
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、使用order by对结果集进行排序。
8、select 集合输出。

条件分支

select userid ,loginname ,email ,
case when email is null then 'null' when email is not null then 'not null'
end as status
from t_ac_user;

获取前5行

select * from t_ac_user where rownum =1;

获取随机数

select dbms_random.value() from dual;

获取随机字符串

select dbms_random.string('A',5) from dual;

获取任意五行

select * from (
select * from t_ac_user order by dbms_random.value()) where rownum <5;

将空值转换成实际值

select userid ,loginname ,email ,coalesce(email,'0') from t_ac_user;

将字符替换成指定字符

select translate(name,'bl','BX') from userinfo; 将b替换为B，将l替换为X；

将字符中所有数字消除掉

select replace(translate(name,'0123456789','##########'),'#','') from userinfo;

空值排序问题

select * from userinfo order by age nulls last; 或者nulls first;

条件排序，商品表中当前销售的商品价钱 促销的时候为促销价，平时为正常价，按照当前销售价来排序

select goods_name, case when is_sell ='1' then price when is_sell = '0' then pricecx end as nowprice from t_bd_goods order by nowprice;
或者
select goods_name from t_bd_goods order by case when is_sell ='1' then price
when is_sell = '0' then pricecx
end ;

oracle求交集，并集，差集 分别是 intersect，union all，minus，检索的字段类型一致。

select ename,job from emp
minus
select ename,job from empv;

查询没有员工的部门信息 使用外联结

select d.* from dept d,emp e where d.deptno=e.deptno(+) and e.deptno is null;

三张表联合查询 两张表内连接 然后和另外一个外联结 比如查询所有员工的姓名，部门名称，第二职业，有的没有第二职业，所以用外联结

select e.ename,d.dname,b.job from emp e,dept d,bonus b where e.deptno=d.deptno and e.ename = b.ename(+);

如果有的部门没有员工，有的员工没有部门 这种情况要查询出所有的信息需要使用全连接

select d.deptno,e.ename from dept d full join emp e on d.deptno=e.deptno;

复制表数据

insert into bonus2 select * from bonus;

将元数据按条件分配到不同备份表中 insert all 和 insert first

insert all
when ename ='a' then
into bonus2 values(ename,job,sal,comm )
else
into bonus3 values(ename,job,sal,comm)
select * from bonus;

检索 所有表 表中所有列 所有表中的索引列

select * from all_tables where owner = 'SCOTT';
select * from all_tab_columns where owner = 'SCOTT' and table_name = 'EMP';
select * from all_ind_columns where index_owner = 'SCOTT' and table_name='EMP';

检索oracle所有视图的一个视图

select * from dictionary

分组和窗口函数的使用 关于窗口函数 over() 的具体使用规则 另行百度。

查询每个部门的员工数
select deptno 部门,count(ename) 部门人数 from emp group by deptno;
在此基础上增加一列 显示公司总人数
select deptno 部门,count(ename) 部门人数,(select count(ename) from emp) 公司总人数 from emp group by deptno;
使用窗口函数，查询公司员工姓名，部门编号，公司总人数 三列数据
select ename,deptno,count(ename)over() 公司总人数 from emp order by 2;
查询公司员工姓名，部门编号，所在部门总人数 三列数据
select ename,deptno,count(ename)over(partition by deptno) 所在部门总人数 from emp order by 2;
over内部可以使用order by 不仅表示排序 而且表示按照该排序进行范围依次扩大 来进行统计分析，如下
select ename,sal,hiredate,sum(sal)over() 所有员工工资总额 from emp;
select ename,sal,hiredate,sum(sal)over(partition by deptno) 所在部门工资总额 from emp;
select ename,sal,hiredate,sum(sal)over(order by hiredate) 按日期依次增加范围统计 from emp;