獲取oracle數(shù)據(jù)庫當(dāng)前用戶下所有表名和表名的注釋

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 用戶對(duì)象的導(dǎo)導(dǎo)出

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 創(chuàng)建用戶

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

oracle 創(chuàng)建表空間

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導(dǎo)出時(shí)，空表不能導(dǎo)出。11G中有個(gè)新特性，當(dāng)表無數(shù)據(jù)時(shí)，不分配segment，以節(jié)省空間

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
SQL Select語句完整的執(zhí)行順序：
1、from子句組裝來自不同數(shù)據(jù)源的數(shù)據(jù)；
2、where子句基于指定的條件對(duì)記錄行進(jìn)行篩選；
3、group by子句將數(shù)據(jù)劃分為多個(gè)分組；
4、使用聚集函數(shù)進(jìn)行計(jì)算；
5、使用having子句篩選分組；
6、計(jì)算所有的表達(dá)式；
7、使用order by對(duì)結(jié)果集進(jìn)行排序。
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;

獲取隨機(jī)數(shù)

select dbms_random.value() from dual;

獲取隨機(jī)字符串

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

獲取任意五行

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

將空值轉(zhuǎn)換成實(shí)際值

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

將字符替換成指定字符

select translate(name,'bl','BX') from userinfo; 將b替換為B，將l替換為X；

將字符中所有數(shù)字消除掉

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

空值排序問題

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

條件排序，商品表中當(dāng)前銷售的商品價(jià)錢 促銷的時(shí)候?yàn)榇黉N價(jià)，平時(shí)為正常價(jià)，按照當(dāng)前銷售價(jià)來排序

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;

查詢沒有員工的部門信息 使用外聯(lián)結(jié)

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

三張表聯(lián)合查詢 兩張表內(nèi)連接 然后和另外一個(gè)外聯(lián)結(jié) 比如查詢所有員工的姓名，部門名稱，第二職業(yè)，有的沒有第二職業(yè)，所以用外聯(lián)結(jié)

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;

復(fù)制表數(shù)據(jù)

insert into bonus2 select * from bonus;

將元數(shù)據(jù)按條件分配到不同備份表中 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所有視圖的一個(gè)視圖

select * from dictionary

分組和窗口函數(shù)的使用 關(guān)于窗口函數(shù) over() 的具體使用規(guī)則 另行百度。

查詢每個(gè)部門的員工數(shù)
select deptno 部門,count(ename) 部門人數(shù) from emp group by deptno;
在此基礎(chǔ)上增加一列 顯示公司總?cè)藬?shù)
select deptno 部門,count(ename) 部門人數(shù),(select count(ename) from emp) 公司總?cè)藬?shù) from emp group by deptno;
使用窗口函數(shù)，查詢公司員工姓名，部門編號(hào)，公司總?cè)藬?shù) 三列數(shù)據(jù)
select ename,deptno,count(ename)over() 公司總?cè)藬?shù) from emp order by 2;
查詢公司員工姓名，部門編號(hào)，所在部門總?cè)藬?shù) 三列數(shù)據(jù)
select ename,deptno,count(ename)over(partition by deptno) 所在部門總?cè)藬?shù) from emp order by 2;
over內(nèi)部可以使用order by 不僅表示排序 而且表示按照該排序進(jìn)行范圍依次擴(kuò)大 來進(jìn)行統(tǒng)計(jì)分析，如下
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) 按日期依次增加范圍統(tǒng)計(jì) from emp;