第一篇：黑馬程序員c語言教程：Oracle指令大全(6)

--------------------------------為什么in的后面不能order by ？

---求部門經理人中平均薪水最低的部門名稱(思考題)第一步,求部門經理的雇員編號

select distinct mgr from emp where mgr is not null 第二步，按部門統計，求部門經理的平均薪水

select deptno,avg(sal)avg_sal from emp where empno in(select distinct mgr from emp where mgr is not null)group by deptno 第三步，求最低值

select min(avg(sal))from emp where empno in(select distinct mgr from emp where mgr is not null)group by deptno 第四步，求部門經理人中平均薪水最低的部門名稱

select deptno from(2222222222222)where avg_sal =(***333333333)

select dname from dept where deptno in(select deptno from(select deptno,avg(sal)avg_sal from emp where empno in(select distinct mgr from emp where mgr is not null)group by deptno)where avg_sal =(select min(avg(sal))from emp where empno in(select distinct mgr from emp where mgr is not null)group by deptno))----------------求比普通員工的最高薪水還要高的經理人名稱--1,求所有經理的編號

create or replace view v1 as select distinct mgr from emp where mgr is not null select * from v1--2,普通員工的最高薪水

select max(sal)from emp where empno not in(select distinct mgr from emp where mgr is not null)--3，select ename from emp where empno in(select * from v1)and sal >(select max(sal)from emp where empno not in(select distinct

mgr from emp where mgr is not null))

--即:

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))

------------------求薪水最高的前5名雇員--1,先觀察一下

--2,看看rownum的作用

--3,不是我們想要的結果

select ename,sal from emp where rownum<=5 order by sal desc--4,先order by,再rownum select * from(select ename,sal from emp order by sal desc)t where rownum<=5

--------------------求薪水最高的第6到第10名雇員(重點掌握)--這種沒法實現,oracle下rownum只能使用 <<=，不能使用 = >>= 等比較操作符--注意里面的rownum和外面的rownum的區別，外面要想訪問里面的rownum，必須取得一個別名。

select * from(select ename,sal from emp order by sal desc)t where rownum>=5 and rownum<=10

--所以再套一層select select * from(select t.*,rownum r from(select ename,sal from emp order by sal desc)t)where r>=5 and r<=10

--還有一種排序方式 select * from(select * from emp order by sal desc)where rownum<=10 minus select * from(select * from emp order by sal desc)where rownum<=5--------練習: 求最后入職的5名員工--1,每個人的入職時間

--2,取前5行

-----求每個部門中薪水最高的前兩名雇員

--1,每個員工的姓名，部門，工資,按部門和工資(倒序)排列

select ename,deptno,sal from emp order by deptno,sal desc--2,套一層,加上個r select ename,deptno,sal,rownum r from(select ename,deptno,sal from emp order by deptno,sal desc)t--3,創建試圖

create or replace view v1 as select ename,deptno,sal,rownum r from(select ename,deptno,sal from emp order by deptno,sal desc)t--觀察一下

select * from v1

--每個部門中,薪水最高的第一行,并創建試圖 create or replace view v2 as select deptno,min(r)min_r from v1 group by deptno

--兩個view跨表連接,大于薪水最高的行數,小于最高的行數+1,并且部門編號要匹配 select ename from v1 join v2 on(v1.deptno = v2.deptno and v1.r >=v2.min_r and v1.r<=v2.min_r+1)

-------------------面試題: 比較效率

select * from emp where deptno = 10 and ename like '%A%';select * from emp where ename like '%A%' and deptno = 10;

----------------------------使用union、minus

--使用union、minus可以用來實現結果集的合并和去除（可以理解為加和減），例如： select * from emp where deptno=10 union select * from emp where deptno=20;

--相當于

select * from emp where deptno=10 or deptno=20

--而下面的語句

select * from emp where deptno in(10,20)minus select * from emp where sal < 1500;--相當于

select * from emp where deptno in(10,20)and sal>=1500

--求分段顯示薪水的個數

如：

scale total <800 0 801-1000 2 1001-2000 3 2001-5000 6 >5000 8

select '<800' as scale ,count(*)as total from emp where sal<800

union select '<801-1000' as scale ,count(*)as total from emp where sal<=1000 and sal>=801

--或者顯示成為

--注意：使用between..and..的時候，包含了最大和最小值。800-1000 1001-2000 2001-5000 2 3 6 select * from(select count(*)as “800-1000” from emp where sal >=800 and sal <= 1000),(select count(*)as “1001-2000” from emp where sal >=1001 and sal <= 2000),(select count(*)as “2001-5000” from emp where sal >=2001 and sal <= 5000)

--或顯示成為

DEPTNO 800-2000 2001-5000--------------------------30 5 1 20 2 3 10 1 2 select t.deptno,“800-2000”,“2001-5000” from(select deptno,count(*)as “800-2000” from emp where sal between 800 and 2000 group by deptno)t join(select deptno,count(*)as “2001-5000” from emp where sal between 2001 and 5000 group by deptno)t1 on t.deptno = t1.deptno

第二篇：黑馬程序員c語言教程：Oracle指令

---------------------------order by的用法

--員工信息按照姓名正序排列

select * from emp order by ename asc;--員工信息按照倒敘排列

select * from emp order by ename desc;--也可以是多個字段組合排列。例如：員工信息按照部門正序排列，并且按照姓名倒敘排列

select * from emp order by deptno asc,ename desc

---------------------------function的用法--把所有姓名變成小寫

select lower(ename)from emp;--把所有姓名變成大寫

select upper(ename)from emp;--求所有人名中包含'a'的員工信息不區分大小寫

select * from emp where lower(ename)like '%a%'--截取子字符串，比如求Hello的一部分 select substr('hello',2,2)from dual;select substr(ename,2,2)from emp;--求Hello的一部分，并指明長度

--求ascii碼對應的字符 select chr(65)from dual--求字符對應的ascii碼

select ascii('中')from dual--四舍五入

select round(12.456,2)from dual select round(12.456,-1)from dual--四舍五入小數點后面多少位

--四舍五入小數點前面多少位

---------------------------important!日期轉換函數

---------------------------將當前日期轉換成1981-03-12 12:00:00這種形式的字符串

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')from dual;

--將1981-03-12 12:00:00字符串轉換成日期

select to_date('1981-03-12 12:00:00','YYYY-MM-DD HH24:MI:SS')from

dual;

--將每個人的薪水轉換成固定格式的字符串

select to_char(sal,'$999,999,999.99')from emp;--將固定格式的字符串轉換成數值

select to_number('$8,000.00','$999,999,999.99')from dual;

--當null參與計算時候，可以用nvl這個函數。比如求每個人一年總共的收入 select ename,sal*12+comm from emp

---------------------------group function組函數

--求所有人的薪水的總和，平均值，最大值，最小值

select sum(sal),avg(sal),max(sal),min(sal)from emp;--求總的行數

select count(*)from emp;--求總的行樹，（可以指定具體的字段）但如果字段有null值的時候需要小心使用 select count(comm)from emp;

--也可以過濾掉重復的行之后統計行數

select count(distinct deptno)from emp--可以指明按照哪個字段進行分組.比如；分部門統計最高薪水

select deptno,max(sal)from emp where deptno is not null group by deptno--也可以按照多個字段來分組統計，比如：分部門和崗位，統計最高薪水和行數 select deptno,job,max(sal),count(*)from emp group by deptno,job

---------------------------重要：出現在select列表中的字段，如果沒有在組函數中，那么必須出現在group by 子句中。

-------------------------select ename,deptno,job,max(sal),count(*)from emp group by deptno,job

--求薪水最高的員工姓名

select * from emp where sal=(select max(sal)from emp);delete from emp where ename='TEST2' update emp set deptno=10 where deptno=99 select * from dept insert into dept(deptno,dname,loc)values('10','ACCOUNTING','NEW YORK');--having從句的用法

--求平均薪水是2000以上的部門

select deptno,avg(sal)as avg_sal from emp group by deptno

having avg(sal)>2000-------------------------

第三篇：黑馬程序員c語言教程：Oracle指令

--sql structured query language

--DML--Data Manipulation Language--數據操作語言

query information(SELECT), add new rows(INSERT), modify existing rows(UPDATE), delete existing rows(DELETE), perform a conditional update or insert operation(MERGE), see an execution plan of SQL(EXPLAIN PLAN), and lock a table to restrict access(LOCK TABLE).--DDL--Data Definition Language--數據定義語言

create, modify,drop, or rename objects(CREATE,ALTER,DROP,RENAME), remove all rows from a database object without dropping the structure(TRUNCATE), manage access privileges(GRANT,REVOKE), audit database use(AUDIT,NOAUDIT)and add a description about an object to the dictionary(COMMENT).--Transaction Control事務控制語句 save the changes(COMMIT)or discard the changes(ROLLBACK)made by DML statements.Also included in the transaction-control statements are statements to set a point or marker in the transaction for possible rollback(SAVEPOINT)and to define the properties for the transaction(SET TRANSACTION).Used to manage the properties of the database.There isonly one statement in this category(ALTER SYSTEM).--DCL--Data Control Language--與開發關系不是很密切,用于權限的分配與回收 grant,revoke,data control

--Session Control control the session properties(ALTER SESSION)and to enable/disable roles(SET ROLE).--System Control

-------------------------select的用法

--每個員工的所有信息

select * from emp--每個人的部門編號，姓名，薪水

select deptno,ename,sal from emp;--每個人的年薪

select ename,sal*12 from emp;--計算2*3的值

select 2*3 from emp;--計算2*3的值(dual)select 2*3 from dual;select * from dual;--得到當前時間

select sysdate from dual--可以給列起別名,比如求每個人的年薪

select ename,sal*12 salperyear from emp;--如果別名中有空格，需要用雙引號

select ename,sal*12 “sal per year” from emp;--如果沒有內容，則為空 select comm from emp;--當空字段參與計算，則結果是null--例如：計算每個人的全年的收入包括月薪和年終獎 select ename,sal*12+comm from emp;--可以將多個字符串拼在一起。比如：求每個人的薪水，格式為smith-sal-123 select ename||'-sal-'||sal from emp;--如果字符串中有單引號，需要用另外一個單引號轉義，比如：這樣一個字符串： he's friend select ename||'''s sal is'||sal from emp;

---------------------------distinct 關鍵詞的用法--求有哪些個部門

select distinct deptno from emp--可以用來修飾多個字段。比如：求有哪些個部門和job的組合 select distinct deptno,job from emp

-------------------------where關鍵詞的用法

--可以是數值類型的等值判斷。比如：求10這個部門的所有員工 select * from emp where deptno=20--可以是字符串類型的等值判斷。比如：求叫KING的這個人的信息 select * from emp where ename = 'KING'--也可以是不等值判斷。比如：求薪水小于2000的員工信息 select * from emp where sal<2000;

--字符串也可以做不等值判斷，比如：求所有ename大于'CBA'的員工信息。select * from emp where ename>'CBA';--求部門不是10的員工

select * from emp where deptno <> 10;--求薪水在800和1500之間的員工信息

select * from emp where sal >=800 and sal <=1500;--也可以寫成

select * from emp where sal between 800 and 1500--這樣寫則不可以

-----------------------------select * from emp where 800<=sal<=1500--where...in..的用法。比如：求薪水是800或者1500或正2000的員工信息 select * from emp where sal=800 or sal=1500 or sal=2000--相當于寫成這樣

select * from emp where sal in(1500,800,2000,1500,1500,1500,1500);--再比如求姓名是KING,SMITH,AA的員工信息

select * from emp where ename in('KING','SMITH','AA')--求入職時間在20-2月-81之后的員工信息

select * from emp where hiredate < '23-5月-87';

---------------------------and or not的用法

--求薪水大于1000或者部門在20這個部門的員工信息 select * from emp where sal>1000 and deptno=20--求薪水不是800或者不是1500或者不是3000的員工信息 select * from emp where sal not in(800,1500,3000)--也可以這樣來寫

select * from emp where sal <>800 and sal <> 1500 and sal<>3000

---------------------------like的用法

--求名字中包含ALL這三個字符的員工信息

select * from emp where ename like '%E%';--求名字中的第二個字母是A的員工

select * from emp where ename like '_A%';--特殊字符需要轉義。比如：求員工中包含特殊字符%的員工信息 select * from emp where ename like '%%%' escape ''

---------------------------null的用法

--求沒有年終獎的員工

select * from emp where comm is null--求有年終獎的員工

select * from emp where comm is not null

第四篇：黑馬程序員c語言教程：Oracle指令

--什么時候用外連接呢?比如領導向你要所有學生的列表,順便把所屬的班級也列出來,就需要外連接

--在Where語句中使用子查詢

-----雇員中最高薪水的人員名稱--1,先求出最高薪水

--2,再求雇員中最高薪水的人員名稱

select ename from emp where sal=(select max(sal)from emp)

--有哪些人的薪水是在整個雇員的平均薪水之上的

select ename,sal from emp where sal >(select avg(sal)from emp)

-----雇員中哪些人是經理人

--1,首先查詢mgr中有哪些號碼--2,再看有哪些人員的號碼在此出現

select distinct mgr from emp where mgr is not null order by mgr

select ename from emp where empno in(select distinct mgr from emp where mgr is not null)--where in 中不讓寫orderby select ename from emp where empno in(select distinct mgr from emp where mgr is not null order by mgr)

-----在From子句中使用子查詢

----

--部門平均薪水的等級

--1,首先將每個部門的平均薪水求出來

--2,然后把結果當成一張表，再用這張結果表和salgrade表做連接，以此求得薪水等級

select deptno,avg(sal)from emp group by deptno select * from(select deptno,avg(sal)avg_sal from emp group by deptno)t join salgrade on avg_sal between losal and hisal;

-----每個部門最高薪水的人員名稱

--1,首先將每個部門的最高薪水求出來

--2,然后把結果當成一張表，再用emp和這張結果表做連接，以此求得每個部門最高薪水的人員名稱

select deptno,max(sal)from emp where deptno is not null group by deptno

select ename from emp e join(select deptno,max(sal)max_sal from emp where deptno is not null group by deptno)t on sal = max_sal and e.deptno = t.deptno

-----哪些人的薪水在部門的平均薪水之上--1,首先將每個部門的平均薪水求出來

--2,然后把結果當成一張表，再用emp和這張結果表做連接，以此求得哪些人的薪水在部門的平均薪水之上

select deptno,avg(sal)avg_sal from emp group by deptno

select * from emp join(select deptno,avg(sal)avg_sal from emp group by deptno)t on(sal>avg_sal and emp.deptno=t.deptno)-----求部門中(所有人的)平均的薪水等級,形式如：--deptno avg_grade--10 3.67--20 2.8--30 2.5--1,先求每個人的薪水等級--2,再按照部門分組,求平均數

select deptno,sal,grade from emp join salgrade on sal between losal and hisal select deptno,avg(grade)from(select deptno,sal,grade from emp join salgrade on sal between losal and hisal)t group by deptno

------------------------------使用偽字段:rownum，----------------------------------------------------用來標識每條記錄的行號，行號從1開始，每次遞增1

select rownum,emp.* from emp;--oracle下rownum只能使用 <<=，不能使用 = >>= 等比較操作符，select rownum,emp.* from emp where rownum<5;--當rownum和order by 一起使用時，會首先選出符合rownum條件的記錄，然后再排序

--(錯誤的寫法)例如，當我們要求薪水最高的前5個人時，最直接的想法可以這樣寫： select * from emp where rownum<5 order by sal desc--(正確的寫法)可以這樣寫

select * from(select * from emp order by sal desc)t where rownum<=5

第五篇：黑馬程序員c語言教程：Oracle指令

--總結一下select語法 select from where group by having order by---------------------------執行順序very important!--首先執行where語句將原有記錄過濾；--第二執行group by 進行分組；--第三執行having過濾分組；

--然后將select 中的字段值選出來；--最后執行order by 進行排序；

-------------------------/* 按照部門分組統計，求最高薪水，平均薪水 只有薪水是1200以上的才參與統計

并且分組結果中只包括平均薪水在1500以上的部門 而且按照平均薪水倒敘排列 */ select max(sal),avg(sal)from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal)desc

-------------------------/* 把雇員按部門分組，求最高薪水，部門號，過濾掉名字中第二個字母是'A'的，要求分組后的平均薪水>1500，按照部門編號倒序排列 */ select max(sal),deptno from emp where ename not like '_A%'group by deptno having avg(sal)>1500 order by deptno desc

/* very very important!*/ select ename, deptno from emp;select deptno, dname from dept;

--------------------------老語法：----------------------------------------等值連接：求員工姓名以及員工所在部門的名字同時顯示出來

select ename,emp.deptno,dname,dept.deptno from emp,dept where emp.deptno = dept.deptno

select ename,e.deptno,dname,d.deptno from emp e,dept d where e.deptno = d.deptno

--非等值連接：要求每位雇員的薪水等級 select * from salgrade select ename,sal,grade,losal,hisal from emp,salgrade where sal >=losal and sal <=hisal--跨3個表：求工作職位是’PRESIDENT’的雇員姓名，部門名稱和薪水等級時 select ename,dname,grade from emp,dept,salgrade where emp.deptno = dept.deptno and sal >=losal and sal <=hisal and job ='PRESIDENT'

--也可以同一個表做跨表連接：求每位員工的姓名，及其上級經理的姓名

select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno

--------------------------新語法------------------在SQL1992的語法規則中，語句過濾的條件和表連接的條件都被放在了where子句中，當條件過多時，容易造成混淆，--SQL1999修正了這個缺點，將連接條件和數據過濾條件區分開來，------------------------

--交叉連接

--結果會產生這兩張表的笛卡爾乘積

select * from emp cross join dept--要用deptno作為等值連接條件，我們可以這樣寫 select * from emp join dept using(deptno)select ename, dname from emp join dept using(deptno);--相當于

select ename, dname from emp join dept on emp.deptno = dept.deptno--也可以寫成這樣

--也可以用于非等值連接--求每位雇員的薪水等級

select * from emp join salgrade on(sal >=losal and sal<= hisal)

--多個join,where組合使用

--(求工作職位是’PRESIDENT’的雇員姓名，部門名稱和薪水等級時)

select * from emp join dept on emp.deptno = dept.deptno join salgrade on(sal >=losal and sal<= hisal)where job = 'PRESIDENT'

--外連接--取出表中連接不到一起的多余的數據--沒有全內連接，沒有右內連接

--其中outer也可以省略，簡寫為left join , right join , full join--left inner join可以縮寫成inner join 也可以縮寫成join，意思是左內。--update emp set deptno=20 where ename='SMITH';--commit;select * from emp;select * from dept;delete from dept where deptno=99;--左內,從左往右找，匹配不上的記錄不顯示 select ename,emp.deptno from emp join dept on emp.deptno = dept.deptno;select ename,emp.deptno from emp inner join dept on emp.deptno = dept.deptno;--沒有這種語法：select ename,emp.deptno from emp left inner join dept on emp.deptno = dept.deptno;--左外連接,從左往右找，匹配不上的記錄也顯示一行 select ename,dept.deptno from emp left /*outer*/ join dept on emp.deptno = dept.deptno;

--右外連接，從右往左找，匹配不上的記錄，也顯示一行 select ename,dept.deptno from emp right /*outer*/ join dept on emp.deptno = dept.deptno;--沒有右內連接：select ename,dept.deptno from emp right inner join dept on emp.deptno = dept.deptno;

--全外連接 select ename,dept.deptno from emp full /*outer*/ join dept on emp.deptno = dept.deptno;

--左外,右外的區別