oracle學習總結2

標籤：

1:常用的函數

to_date()函數，將字串轉換為日期格式
select to_date(‘2015-09-12‘,‘yyyy-MM-dd‘) from dual; --其中後面的日期格式要和前面要轉化的匹配

to_number()函數，將字串轉換為數字格式
select ename,sal from emp where sal>to_number(‘$5000.00‘,‘$9999.99‘);

清屏命令：clear screen;

2:常用的組函數 max() min() avg() sum() count()

select max(sal) from emp; --最高薪水是多少

select min(sal) from emp; --最低薪水是多少

select avg(sal) from emp; --平均薪水是多少

select sum(sal) from emp; --薪水總和是多少

select count(ename) from emp; --總共有多少員工

3:group by 求部門的平均薪水
select deptno,avg(sal) from emp group by deptno;

4：執行select一般順序
select deptno,avg(sal) from emp(表名) where sal>5000(where限定條件) group by deptno(按照部門分組) having avg(sal)>5000
order by avg(sal) desc;

5：處理重複記錄的問題
select empno from emp group by empno having count(empno)>1; --查詢出重複的empno
select * from emp where empno in(select empno from emp group by empno having count(empno)>1); --查詢出重複的記錄
delete from emp where empno in (select empno from emp group by empno having count(empno)>1) and rowid not in (select min
(rowid) from emp group by empno having count(empno)>1); --重複資料刪除記錄，保留第一條

 

6:子查詢
select ename,sal from emp where sal=(select max(sal) from emp); --查詢公司中薪水最高的員工姓名
select ename,sal from emp where sal in(select max(sal) from emp group by deptno);--查詢每個部門薪水最高的員工姓名 --錯誤的
（所有員工只要和其中一個匹配就會被查出）
select ename,sal from emp e join (select max(sal) max_sal,deptno from emp group by deptno) t on (e.sal=t.max_sal and
e.deptno=t.deptno);
或者select ename,sal from emp e,(select max(sal) max_sal, deptno from emp group by deptno) t where e.sal=t.max_sal and
e.deptno=t.deptno;
select empno,count(1) from emp group by empno having count(1)>1; --查詢員工號重複的員工

92年標準：
select ename,sal,grade from emp e,grade g where e.sal between g.lowsal and g.higsal; --求所有員工薪水等級
select ename,sal,grade from emp e join (select max(sal) max_sal,deptno from emp group by deptno) t on (e.sal=t.max_sal and
e.deptno=t.deptno) join grade g on (t.max_sal bwtween g.lowsal and g.higsal);--求部門中薪水最高員工姓名、等級

--求各部門平均工資等級
select deptno ,avg_sal,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t join grade g on (t.avg_sal
between g.lowsal and g.higsal);

--求各部門的平均薪水等級
select ename,deptno,sal,grade from emp e join grade g on(e.sal between g.lowsal and g.higsal); --每個員工的薪水等級
select deptno,avg(grade) from (select ename,deptno,sal,grade from emp e join grade g on(e.sal between g.lowsal and
g.higsal)) t group by deptno;

99年sql標準：
select ename,sal,grade from emp e join grade g on (e.sal between g.lowsal and g.higsal);

自串連：在同一張表進行操作
select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr=e2.empno);或者select e1.ename,e2.ename from emp e1,emp e2 where
e1.mgr=e2.empno;
左外串連：
select e.ename,d.loc from emp e left join dept d on (e.deptno=d.deptno);--左外串連會把左邊那張表中沒有關聯到的欄位也給查詢
出來
右外串連：
select e.ename,d.loc from emp e right join dept d on(e.deptno=d.deptno);--右外串連會把右邊那張表中沒有關聯到的欄位也給查詢
出來
全外串連：
select e.ename,d.loc from emp e full join dept d on(e.deptno=d.deptno); --全串連會把左右兩邊表中沒有關聯到的欄位都給查詢出
來

7:--查詢所有的經理人
select ename from emp where empno in(select distinct mgr from emp);

8:--不使用組函數，查詢薪水的最高值(先按照薪水排序，然後查詢第一條)
select sal from (select sal from emp order by sal desc) where rownum=1;
或者
select e1.sal from emp where sal not in (select e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal)); --自串連表，那麼表e1中
只有最大值不會關聯到

9：--求平均薪水最高的部門的名稱
1：--求各部門平均薪水
select deptno,avg(sal) from emp group by deptno;
2: --求平均薪水最高為多少
select max(avg_sal) from (select deptno,avg(sal) avg_sal from emp group by deptno);
3: --求平均薪水最高部門的編號
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));
4:--求平均薪水最高部門的名稱
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)));

10: --求平均薪水等級最低的部門名稱
1:--求各部門平均薪水
select avg(sal),deptno from emp group by deptno;
2:--求各部門平均薪水等級
select deptno,avg_sal,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t join grade g on(t.avg_sal
between g.lowsal and g.higsal);
3:--求平均薪水等級最低
select min(grade) from (select grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t join grade g on
(t.avg_sal between g.lowsal and g.higsal));
4:--求平均薪水等級最低的部門編號
select deptno from (select deptno,avg_sal,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t join grade
g on(t.avg_sal between g.lowsal and g.higsal)) where grade =(select min(grade) from (select grade from (select avg(sal)
avg_sal,deptno from emp group by deptno) t join grade g on(t.avg_sal between g.lowsal and g.higsal)));

5:--求平均薪水等級最低的部門編號
select dname from dept where deptno in (select deptno from (select deptno,avg_sal,grade from (select avg(sal)
avg_sal,deptno from emp group by deptno) t join grade g on(t.avg_sal between g.lowsal and g.higsal)) where grade =(select
min(grade) from (select grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t join grade g on(t.avg_sal
between g.lowsal and g.higsal))));

 

11:oracle資料庫使用者切換
conn sys/root(使用者名稱/密碼) as sysdba;

授權語句：
grant create table,create view to system(使用者名稱);
grant select,update,insert on emp to hr; --一次可以授權多種操作，但是只能針對一張表

12：--求比普通員工最高工資還要高的經理人
1：求普通員工的最高工資
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null)
2：求還要高的經理人
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));

13:--求薪水最高的前2名員工
select ename,sal from (select ename,sal from emp order by sal desc) where rownum <3;

14:rownum 子查詢，實現分頁，首先是排序，然後是查詢rownum，第三步限定條件
select ename,sal from
(select ename,sal,rownum r from
(select ename,sal from emp order by sal desc)) where r>=2 and r<=3

對比：
mysql：select ename,sal from (select ename,sal from emp order by sal desc) t where id limit 2,2;--從2開始取，取2條
db2： 使用fetch

13：dml語句(資料操縱語句)
update emp set ename=‘tom‘ where empno=1001;
delete from emp where empno=1002;
insert into emp (empno,deptno) values(1005,20);

14:事務控制（dcl語句）
a：commit提交 rollback復原 transcation事務結束
b：當執行ddl語句之後，之前的事務也會被提交
c：正常的退出串連，exit，也會自動認可事務

oracle學習總結2