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

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

---求部門(mén)經(jīng)理人中平均薪水最低的部門(mén)名稱(chēng)(思考題)第一步,求部門(mén)經(jīng)理的雇員編號(hào)

select distinct mgr from emp where mgr is not null 第二步，按部門(mén)統(tǒng)計(jì)，求部門(mén)經(jīng)理的平均薪水

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 第四步，求部門(mén)經(jīng)理人中平均薪水最低的部門(mén)名稱(chēng)

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))----------------求比普通員工的最高薪水還要高的經(jīng)理人名稱(chēng)--1,求所有經(jīng)理的編號(hào)

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,不是我們想要的結(jié)果

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名雇員(重點(diǎn)掌握)--這種沒(méi)法實(shí)現(xiàn),oracle下rownum只能使用 <<=，不能使用 = >>= 等比較操作符--注意里面的rownum和外面的rownum的區(qū)別，外面要想訪問(wèn)里面的rownum，必須取得一個(gè)別名。

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--------練習(xí): 求最后入職的5名員工--1,每個(gè)人的入職時(shí)間

--2,取前5行

-----求每個(gè)部門(mén)中薪水最高的前兩名雇員

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

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

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

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

--兩個(gè)view跨表連接,大于薪水最高的行數(shù),小于最高的行數(shù)+1,并且部門(mén)編號(hào)要匹配 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可以用來(lái)實(shí)現(xiàn)結(jié)果集的合并和去除（可以理解為加和減），例如： select * from emp where deptno=10 union select * from emp where deptno=20;

--相當(dāng)于

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

--而下面的語(yǔ)句

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

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

--求分段顯示薪水的個(gè)數(shù)

如：

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..的時(shí)候，包含了最大和最小值。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語(yǔ)言教程：Oracle指令

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

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

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

select * from emp order by ename desc;--也可以是多個(gè)字段組合排列。例如：?jiǎn)T工信息按照部門(mén)正序排列，并且按照姓名倒敘排列

select * from emp order by deptno asc,ename desc

---------------------------function的用法--把所有姓名變成小寫(xiě)

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

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

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

--求ascii碼對(duì)應(yīng)的字符 select chr(65)from dual--求字符對(duì)應(yīng)的ascii碼

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

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

--四舍五入小數(shù)點(diǎn)前面多少位

---------------------------important!日期轉(zhuǎn)換函數(shù)

---------------------------將當(dāng)前日期轉(zhuǎn)換成1981-03-12 12:00:00這種形式的字符串

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

--將1981-03-12 12:00:00字符串轉(zhuǎn)換成日期

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

dual;

--將每個(gè)人的薪水轉(zhuǎn)換成固定格式的字符串

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

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

--當(dāng)null參與計(jì)算時(shí)候，可以用nvl這個(gè)函數(shù)。比如求每個(gè)人一年總共的收入 select ename,sal*12+comm from emp

---------------------------group function組函數(shù)

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

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

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

--也可以過(guò)濾掉重復(fù)的行之后統(tǒng)計(jì)行數(shù)

select count(distinct deptno)from emp--可以指明按照哪個(gè)字段進(jìn)行分組.比如；分部門(mén)統(tǒng)計(jì)最高薪水

select deptno,max(sal)from emp where deptno is not null group by deptno--也可以按照多個(gè)字段來(lái)分組統(tǒng)計(jì)，比如：分部門(mén)和崗位，統(tǒng)計(jì)最高薪水和行數(shù) select deptno,job,max(sal),count(*)from emp group by deptno,job

---------------------------重要：出現(xiàn)在select列表中的字段，如果沒(méi)有在組函數(shù)中，那么必須出現(xiàn)在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以上的部門(mén)

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

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

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

--sql structured query language

--DML--Data Manipulation Language--數(shù)據(jù)操作語(yǔ)言

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--數(shù)據(jù)定義語(yǔ)言

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事務(wù)控制語(yǔ)句 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--與開(kāi)發(fā)關(guān)系不是很密切,用于權(quán)限的分配與回收 grant,revoke,data control

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

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

--每個(gè)員工的所有信息

select * from emp--每個(gè)人的部門(mén)編號(hào)，姓名，薪水

select deptno,ename,sal from emp;--每個(gè)人的年薪

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

select 2*3 from emp;--計(jì)算2*3的值(dual)select 2*3 from dual;select * from dual;--得到當(dāng)前時(shí)間

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

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

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

---------------------------distinct 關(guān)鍵詞的用法--求有哪些個(gè)部門(mén)

select distinct deptno from emp--可以用來(lái)修飾多個(gè)字段。比如：求有哪些個(gè)部門(mén)和job的組合 select distinct deptno,job from emp

-------------------------where關(guān)鍵詞的用法

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

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

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

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

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

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

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')--求入職時(shí)間在20-2月-81之后的員工信息

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

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

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

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

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

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

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

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

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

--求沒(méi)有年終獎(jiǎng)的員工

select * from emp where comm is null--求有年終獎(jiǎng)的員工

select * from emp where comm is not null

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

--什么時(shí)候用外連接呢?比如領(lǐng)導(dǎo)向你要所有學(xué)生的列表,順便把所屬的班級(jí)也列出來(lái),就需要外連接

--在Where語(yǔ)句中使用子查詢(xún)

-----雇員中最高薪水的人員名稱(chēng)--1,先求出最高薪水

--2,再求雇員中最高薪水的人員名稱(chēng)

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

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

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

-----雇員中哪些人是經(jīng)理人

--1,首先查詢(xún)mgr中有哪些號(hào)碼--2,再看有哪些人員的號(hào)碼在此出現(xiàn)

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 中不讓寫(xiě)orderby select ename from emp where empno in(select distinct mgr from emp where mgr is not null order by mgr)

-----在From子句中使用子查詢(xún)

----

--部門(mén)平均薪水的等級(jí)

--1,首先將每個(gè)部門(mén)的平均薪水求出來(lái)

--2,然后把結(jié)果當(dāng)成一張表，再用這張結(jié)果表和salgrade表做連接，以此求得薪水等級(jí)

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;

-----每個(gè)部門(mén)最高薪水的人員名稱(chēng)

--1,首先將每個(gè)部門(mén)的最高薪水求出來(lái)

--2,然后把結(jié)果當(dāng)成一張表，再用emp和這張結(jié)果表做連接，以此求得每個(gè)部門(mén)最高薪水的人員名稱(chēng)

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

-----哪些人的薪水在部門(mén)的平均薪水之上--1,首先將每個(gè)部門(mén)的平均薪水求出來(lái)

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

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)-----求部門(mén)中(所有人的)平均的薪水等級(jí),形式如：--deptno avg_grade--10 3.67--20 2.8--30 2.5--1,先求每個(gè)人的薪水等級(jí)--2,再按照部門(mén)分組,求平均數(shù)

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，----------------------------------------------------用來(lái)標(biāo)識(shí)每條記錄的行號(hào)，行號(hào)從1開(kāi)始，每次遞增1

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

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

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

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

--總結(jié)一下select語(yǔ)法 select from where group by having order by---------------------------執(zhí)行順序very important!--首先執(zhí)行where語(yǔ)句將原有記錄過(guò)濾；--第二執(zhí)行g(shù)roup by 進(jìn)行分組；--第三執(zhí)行having過(guò)濾分組；

--然后將select 中的字段值選出來(lái)；--最后執(zhí)行order by 進(jìn)行排序；

-------------------------/* 按照部門(mén)分組統(tǒng)計(jì)，求最高薪水，平均薪水 只有薪水是1200以上的才參與統(tǒng)計(jì)

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

-------------------------/* 把雇員按部門(mén)分組，求最高薪水，部門(mén)號(hào)，過(guò)濾掉名字中第二個(gè)字母是'A'的，要求分組后的平均薪水>1500，按照部門(mén)編號(hào)倒序排列 */ 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;

--------------------------老語(yǔ)法：----------------------------------------等值連接：求員工姓名以及員工所在部門(mén)的名字同時(shí)顯示出來(lái)

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

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

--也可以同一個(gè)表做跨表連接：求每位員工的姓名，及其上級(jí)經(jīng)理的姓名

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

--------------------------新語(yǔ)法------------------在SQL1992的語(yǔ)法規(guī)則中，語(yǔ)句過(guò)濾的條件和表連接的條件都被放在了where子句中，當(dāng)條件過(guò)多時(shí)，容易造成混淆，--SQL1999修正了這個(gè)缺點(diǎn)，將連接條件和數(shù)據(jù)過(guò)濾條件區(qū)分開(kāi)來(lái)，------------------------

--交叉連接

--結(jié)果會(huì)產(chǎn)生這兩張表的笛卡爾乘積

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

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

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

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

--多個(gè)join,where組合使用

--(求工作職位是’PRESIDENT’的雇員姓名，部門(mén)名稱(chēng)和薪水等級(jí)時(shí))

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

--外連接--取出表中連接不到一起的多余的數(shù)據(jù)--沒(méi)有全內(nèi)連接，沒(méi)有右內(nèi)連接

--其中outer也可以省略，簡(jiǎn)寫(xiě)為left join , right join , full join--left inner join可以縮寫(xiě)成inner join 也可以縮寫(xiě)成join，意思是左內(nèi)。--update emp set deptno=20 where ename='SMITH';--commit;select * from emp;select * from dept;delete from dept where deptno=99;--左內(nèi),從左往右找，匹配不上的記錄不顯示 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;--沒(méi)有這種語(yǔ)法：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;--沒(méi)有右內(nèi)連接：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;

--左外,右外的區(qū)別