613
技術社區[雲棲]
Oracle SQL經典薈萃
將自己學習，工作中整理的一些經典SQL和大家分享一下。注意，在Oracle 9i中tiger賬戶默認是開啟的，後續版本則改變了策略~同時，關於sql優化尤其是hint的使用，這裏不做分享，具體可以查閱民間流傳的50大招~
-- toy sql
alter user scott account unlock;
select ename,sal*12 as "annual sal" from emp;
--注意：""保持了字段名的小寫狀態
select ename,sal*12 + comm from emp;
select job||'KaK''a' from emp;
select ename,sal*12 + nvl(comm,0) from emp;
--注意：任何含有空值的數學表達式，計算結果都是空值;字符串連接過程中，單引號使用'轉義
select distinct deptno,job from emp;
--distinct deptno,job組合唯一
select ename,sal from emp where deptno <>10;
--Oracle中日期型處理技巧。1.1-1月-1982 2。date'1982-01-01'
select ename,hiredate from emp where hiredate>'1-1月-1982';
select ename,hiredate from emp where hiredate>to_date('1982-1-1','yyyy-mm-dd');
--SQL中不等於的方法
select ename,sal from emp where sal between 800 and 1500;
select ename,sal from emp where sal >=800 and sal<=1500;
--指定$為轉義字符
select ename from emp where ename like '%$%%' escape '$';
select lower(ename) from emp;
select upper(ename) from emp;
select substr(ename,1,3) from emp;
select * from emp where length(ename)=5;
select upper(substr(ename,1,1)) from emp || select lower(substr(ename,2,length(ename)-1))from emp;
select chr(65) from dual;
select ascii('A') from dual;
select round(25.656,-1) from dual;
--L,本地貨幣符號，C，國際貨幣符號
select to_char(sal,'$999,999.999')from emp;
select to_char(sal,'L999,999.999')from emp;
select to_char(sal,'L000,000.000')from emp;
select to_char(hiredate,'YYYY-MM-MM HH24:MI:SS') from emp;
select ename,hiredate from emp where hiredate > to_date('1981-02-20 12:00:00','YYYY-MM-DD HH24:MI:SS');
select sal from emp where sal>to_number('$1,250.00','$99,999.99');
select replace(ename,'a','A') from emp;
select sys_context('USERENV','db_name') from dual;
select sys_context('USERENV','current_schema') from dual;
--用戶和方案的關係:一一對應,Oracle是以方案的方式管理數據對象的，用戶名和方案名相同
select deptno,job,max(sal) as max_sal from emp group by deptno,job;
--按照兩個字段進行分組
select deptno,round(avg(sal),2) from emp group by deptno;
--注意：出現在select列表中的字段，如果沒有出現在組函數裏，那麼必需出現在group by語句裏！
--分組函數隻能出現在選擇列表，having，order by子句中！
select ename,deptno from emp where sal in (select max(sal)from emp group by deptno);
select ename from emp where sal in --= (select max(sal) from emp);
select deptno,max(sal) from emp group by deptno;
select deptno,round(avg(sal)) from emp group by deptno having avg(sal)>2000 order by deptno;
--注意：where是對單條語句過濾，而having是對分組進行過濾！
select ename,deptno from emp order by deptno desc;
select avg(sal) from emp where sal>1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;
select * from product_component_version;
select ename,sal from emp where sal > (select avg(sal) from emp);
--每個部門中薪水最高的人
select ename,deptno,sal from emp where sal in (select max(sal) from emp group by deptno);
--仔細分析上述寫法，在大數據量條件下，會出現偏差
select ename,sal,emp.deptno from emp join (select max(sal) as max_sal,deptno from emp group by deptno) t on(emp.sal = t.max_sal and emp.deptno = t.deptno);
select t1.ename as clerk,t2.ename as boss from emp t1 ,emp t2 where t1.mgr = t2.empno;
select t1.ename as clerk,t2.ename as boss from emp t1 join emp t2 on (t1.mgr = t2.empno);
select t1.ename as clerk,t2.ename as boss from emp t1 left outer join emp t2 on (t1.mgr = t2.empno);
select ename,dname from emp right outer join dept on (emp.deptno = dept.deptno);
select ename,dname,grade from emp e,dept d,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal and job <> 'CLERK';
--超強震撼，99語法將連接條件與數據過濾條件分離，仔細品味吧！
select ename,dname,grade from emp join dept on ( emp.deptno = dept.deptno ) join salgrade on(emp.sal between salgrade.losal and salgrade.hisal) where ename not like '_A%';
--SQL 99與SQL 92的區別 : Where 語句後隻跟數據過濾條件 ！
select ename,dname from emp,dept;
select ename,dname from emp cross join dept;
-- 等值連接
select ename,dname from emp,dept where emp.deptno = dept.deptno;
select ename,dname from emp join dept on ( emp.deptno = dept.deptno );
select ename,dname from emp join dept using (deptno);
--部門平均薪水的等級
select deptno,avg_sal,grade from(select deptno,avg(sal) as avg_sal from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal);
--部門平均的薪水等級
select deptno,avg(grade) from(select deptno,ename,grade from emp join salgrade on(emp.sal between salgrade.losal and salgrade.hisal)) group by deptno;
--雇員中誰是經理人
select ename from emp where empno in (select distinct mgr from emp);
--不用聚集函數求最高薪水
select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal));
--平均薪水最高的部門的部門編號
select deptno,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal =(select max(avg_sal) from (select deptno,avg(sal) avg_sal from emp group by deptno));
--同樣的例子，使用聚集函數嵌套！
select deptno,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal =(select max(avg(sal)) from emp group by deptno);
--平均薪水最高的部門的部門名稱
select dname from dept where deptno = (select deptno from (select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal =(select max(avg_sal)
from (select deptno,avg(sal) avg_sal from emp group by deptno)));
--平均薪水等級最低的部門的部門名稱
select t1.deptno,dname,avg_sal,grade from
(
select deptno,grade,avg_sal from
(
select deptno,avg(sal) as avg_sal from emp group by deptno
) t join salgrade s on
(
t.avg_sal between s.losal and s.hisal
)
) t1 join dept on
(
t1.deptno = dept.deptno
) where t1.grade =
(
select min(grade) from
(
select deptno,avg_sal,grade from
(
select deptno,avg(sal) avg_sal from emp group by deptno
) t join salgrade s on
(
t.avg_sal between s.losal and s.hisal
)
)
);
--顯示高於自己部門平均工資的員工信息
select t1.ename,t1.sal,t1.deptno,t2.avg_sal from emp t1,(select deptno,avg(sal) avg_sal from emp group by deptno) t2 where t1.deptno = t2.deptno and t1.sal > t2.avg_sal;
--另類做法
--1.創建視圖
create view v$dept_avg_sal_info as
select deptno,grade,avg_sal from
(
select deptno,avg(sal) as avg_sal from emp group by deptno
) t join salgrade s on
(
t.avg_sal between s.losal and s.hisal
);
--2.使用剛創建的視圖進行查詢
select t1.deptno,dname,avg_sal,grade from
(
select deptno,grade,avg_sal from
(
select deptno,avg(sal) as avg_sal from emp group by deptno
) t join salgrade s on
(
t.avg_sal between s.losal and s.hisal
)
) t1 join dept on
(
t1.deptno = dept.deptno
) where t1.grade =
(
select min(grade) from v$dept_avg_sal_info
);
--比普通員工的最高薪水還高的經理人名稱
select ename from emp where empno in
(
select distinct mgr from emp where mgr is not null
)
and sal >
(
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null)
);
--備份表
create table emp2 as select * from emp;
grant dba to scott;
grant create table,create view to scott;
--邏輯備份
--1. exp
--2. create user ** identified by ** default tablespace users quota 10M on users
--3. grant create session,create table,create view to **
--4. imp
--另類insert
insert into emp2 select * from emp;
select ename from (select rownum r ,ename from emp) where r > 10;
--薪水最高的前5名雇員
--錯誤的寫法！（錯誤原因：先取出前5個，才排序）
select ename,sal from emp where rownum <=5 order by sal desc;
--正確的寫法！
select ename,sal from (select ename,sal from emp order by sal desc) where rownum <=5;
--薪水最高的第6個到第10個人
select ename,sal,rownum r from (select ename,sal,rownum r from(select ename,sal from emp order by sal desc))where r<=10 and r>=6;
--hibernate 寫法
select * from ( select row_.*, rownum rownum_ from ( select ename,sal from emp order by sal desc) row_ where rownum <= 10) where rownum_ >=6;
--顯示比部門30的所有員工的工資都高的員工姓名，工資和部門號
select ename,sal,deptno from emp where sal> all--any (select sal from emp where deptno=30);
select ename,sal,deptno from emp where sal >(select max(sal) from emp where deptno =30);--效率高
--查詢與SMITH的部門和崗位完全相同的所有員工
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
--希望SCOTT員工的崗位，工資，津貼與SMITH員工一樣
update emp set(job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
--查詢8個月前入職的員工
select * from emp where sysdate>add_months(hiredate,8);
--顯示滿10個月服務年限的員工姓名和受雇日期
select ename,hiredate from emp where sysdate>=add_months(hiredate,12*10);
--顯示每個雇員入職天數
select ename,floor(sysdate-hiredate) as '入職天數' from emp;
--各月倒數第3天受雇的員工
select * from emp where last_day(hiredate)-2=hiredate;
--SQL注入漏洞
select * from emp where empno='7369' and ename='SMITH' or 1='1';
--MS SQL中起作用
select * from emp where empno='7369';delete from emp;--'and ename='SMITH';
--查詢員工信息（包括其直接上司）
select e1.empno,e1.ename,e1.mgr,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno
select e1.empno,e1.ename,e1.mgr,(select e2.ename from emp e2 where e2.empno = e1.mgr) as mgrname from emp e1
--查詢員工7902的所有上級
select empno,ename,level from emp connect by empno = PRIOR mgr start with empno = 7902;
--查詢員工7902的所有下級
select empno,ename,level from emp connect by PRIOR empno = mgr start with empno = 7902;
--使用rowid刪除重複項，保留rowid最大一項，模式如：
select rowid,t.product_type,t.order_datetime from tb_order_view t where rowid != (select max(rowid)from tb_order_view b where b.product_type = t.product_type and b.order_datetime = t.order_datetime)
--case...when...then
select e1.empno,
e1.ename,
e1.job,
e1.hiredate,
e1.sal,
case
when e1.sal < 1000 then
'低'
else
(case
when e1.sal <= 3000 then
'中'
else
'高'
end)
end as grade
from emp e1
select count(case when sal<1000 then 1 else null end)low,
count(case when sal between 1000 and 3000 then 1 else null end)mid,
count(case when sal >3000 then 1 else null end) high from emp
--數據庫完整性實現方式之-約束（5種）,包括列級定義，表級定義
--1.非空 2.唯一 3.主鍵 4.外鍵 5.Check
create table stu(
id number(6),
name varchar2(20) constraint stu_name_nn--自定義約束名字 not null,
sex char(2) default '男' check(sex in('男','女')),
age number(3) check(age>0),
sdate date,
grade number(2) default 1,
class number(4) --references class(id),
email varchar2(50),
constraint stu_fk foreign key (class) references class(id),
constraint stu_id_pk primary key(id),
constraint stu_unique unique(email,name)
);
create table class(
id number(4) primary key,
name varchar2(20)
);
alter table class modify name not null;
alter table class add constraint nameUnique unique(name);
select constraint_name,constraint_type,status,validated from user_constraints where table_name='emp';
select column_name,position from user_cons_columns where constraint_name='nameUnique';
--null值，數據庫不認為是重複值
--表修改
alter table stu add(addr varchar2(100));
alter table stu drop(addr);
alter table stu drop column addr;
rename stu to student;--修改表名字
alter table stu modify(addr varchar2(50));--修改字段長度或類型（空表）
--約束條件操作
alter table stu drop constraint stu_fk;
alter table stu add constraint stu_fk foreign key (class) references class(id);
--數據字典表
desc user_tables;
select table_name from user_tables;
select view_name from user_views;
select constraint_name from user_constraints;
select constraint_name,table_name from user_constraints;
desc dictionary;
select table_name from dictionary;
--索引與視圖(主鍵約束和唯一約束)
create index index_stu_email on stu(email);--也可以設置複合索引，注意順序不同（區分度高的字段放後麵），索引不同，索引的層次不要超過4層。
drop index index_stu_email;
select index_name from user_indexes;
select view_name from user_views;
--索引的缺點。1.係統需要為索引開辟大約為表存儲容量1.2倍的空間（硬盤空間和內存空間）。2.更新數據時，係統需要額外的時間來對索引進行同步更新。
select index_name,index_type from user_indexes where table_name='emp';
select table_name,column_name from user_id_columns where index_name='emp';
create sequence seq;
select seq.nextval from dual;
create view v$view_table as select M.column_name FieldName from (select * from user_tab_columns where upper(Table_name) = upper('BLB')) M left join user_col_comments A ON M.COLUMN_NAME=A.COLUMN_NAME and M.Table_Name = A.Table_Name
left join (select a.table_name,b.column_name from user_constraints a, user_cons_columns b where a.constraint_name=b.constraint_name and upper(a.table_name)=upper('BLB') and a.constraint_type='P') B ON M.Table_Name = B.TABLE_NAME and M.COLUMN_NAME=B.COLUMN_NAME order by M.column_id;
insert into [用戶表](id) select * from v$view;
create or replace procedure insert_ID is
begin
for i in 1..196 loop
insert into lcqkbt(id) values(seq.nextval);
end loop;
commit;
end;
alter database datafile '' resize 100M;
--dba daily work
create table errorlog(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
create sequence seq_errorlog_id start with 1 increment by 1;
declare
v_deptno dept.deptno%type :=10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errcode :=SQLCODE;
v_errmsg:=SQLERRM;
insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
commit;
end;
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when(c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found) loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
update dept set deptno = 99 where deptno = 10;
create or replace trigger cascadeUpdate
after update on dept
for each row
begin
update emp set deptno = :NEW.deptno where deptno=:OLD.deptno;
end;
create or replace procedure bbs(v_pid article.pid%type,v_level binary_integer) is
cursor c is select * from article where pid= v_pid;
v_preStr varchar2(1024):='';
begin
for i in 1..v_level loop
v_preStr := v_preStr ||'****';
end loop;
for v_article in c loop
dbms_output.put_line(v_preStr||v_article.cont);
if(v_article.isleaf = 0) then
p(v_article.id,v_level+1);
end if;
end loop;
end;
--Oracle date默認格式:11-12月-1997 or 11-12月-97
--更改輸入格式
alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';
1.insert into...
1.savepoint a;
3.delete from stu;--刪除所有記錄,表結構還在,寫日誌，可以恢複，速度慢
4.rollback to a;
truncate table stu；--刪除所有記錄，表結構還在，不寫日誌，無法恢複，速度快
set timing on;
--union all和union的區別在於後者會去掉重複行
--minus，intersect
--創建數據庫
--使用 Configuration and Migration Tools->Database Configuration Assistant 8步，默認20進程
--隻讀事務
set transaction read only;
--預定義角色是在數據庫安裝後，係統自動創建的一些常用的角色
--1．CONNECT,RESOURCE,DBA ---這些預定義角色主要是為了向後兼容。其主要是用於數據庫管理。oracle建議用戶自己設計數據庫管理和安全的權限規劃，而不要簡單的使用這些預定角色。將來的版本中這些角色可能不會作為預定義角色。
--2．DELETE_CATALOG_ROLE,EXECUTE_CATALOG_ROLE,SELECT_CATALOG_ROLE ---這些角色主要用於訪問數據字典視圖和包。
--3. EXP_FULL_DATABASE,IMP_FULL_DATABASE ---這兩個角色用於數據導入導出工具的使用。
select * from role_sys_privs where role=upper('connect');
--對象權限就是指在表、視圖、序列、過程、函數或包等對象上執行特殊動作的權利。有九種不同類型的權限可以授予給用戶或角色
--係統權限需要授予者有進行係統級活動的能力,如連接數據庫,更改用戶會話、建立表或建立用戶等等。你可以在數據字典視圖SYSTEM_PRIVILEGE_MAP上獲得完整的係統權限。對象權限和係統權限都通過GRANT語句授予用戶或角色。需要注意的是在授予對象權限時語句應該是WITH GRANT OPTION子句，但在授予係統權象時語句是WITH ADMIN OPTION;
--查看死鎖信息&解除死鎖
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo, dba_objects ao, v$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
SELECT a.sid, b.owner, object_name, object_type
FROM v$lock a, all_objects b
WHERE TYPE = 'TM'
and a.id1 = b.object_id;
alter system kill session '3267,62017'
--查看死鎖機器
select username, lockwait, status, machine, program
from v$session
where sid in (select session_id from v$locked_object)
--查看死鎖字段
select sql_text
from v$sql
where hash_value in
(select sql_hash_value
from v$session
where sid in (select session_id from v$locked_object));
最後更新：2017-04-02 06:52:22