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

---------------------------不準用組函數(即MAX()),求薪水的最高值(面試題)--第一種解決辦法:--1,先把所有薪水按照倒序排列--2,再取第一行 select * from(select sal from emp order by sal desc)t where rownum=1

--第二種解決辦法:--1,先跨表查詢自己,先求出的結果中,e1.sal不可能出現最大數--2,然后再not in select e2.sal from emp e1,emp e2 where e1.sal>e2.sal select sal from emp where sal not in(select e2.sal from emp e1,emp e2 where e1.sal>e2.sal)

-----求平均薪水最高的部門的部門編號--第一種解決辦法:--1,先求出每個部門的平均薪水, select deptno,avg(sal)avg_sal from emp group by deptno--2,再求每個部門的平均薪水的最高值, select max(avg_sal)from(***1111111111)--3,最后再求第一步結果中avg_sal = 最高薪水的記錄.select deptno from(111111111111)where avg_sal =(22222222)

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 deptno from(select deptno,avg(sal)avg_sal from emp group by deptno order by avg(sal)desc)

where rownum<=1

--第二種解決辦法:--1,將上面的第一步第二步合并,先求最高平均薪水,用max(avg(sal))的辦法--不能寫成select deptno,max(avg(sal))from emp group by deptno select max(avg(sal))from emp group by deptno--2,求出每個部門的平均薪水

select deptno,avg(sal)avg_sal from emp group by deptno--3,最后再求第二步結果中(即每個部門的平均薪水),avg_sal =(第一步結果)的記錄.即avg_sal =最高薪水的記錄.select deptno from(select deptno,avg(sal)avg_sal from emp group by deptno)where avg_sal =(select max(avg(sal))from emp group by deptno)

--第三種解決辦法:--1,先求出每個部門的平均薪水, select avg(sal)avg_sal from emp group by deptno--2,求最高平均薪水,用max(avg(sal))的辦法

select max(avg(sal))from emp group by deptno--3,再使用having語句, avg(sal)= 第二步的結果 注意：為組函數起的別名在having中不能用

select deptno from emp group by deptno having avg(sal)=(select max(avg(sal))from emp group by deptno)

-----求平均薪水最高的部門的部門名稱--1,部門平均最高薪水

--2,得到部門編號列表,注意用group by deptno--3,再應用having子句, having avg(sal)=(第一步的結果)--4,得到平均最高薪水的那個部門的編號--5,再得到部門名稱

select dname from dept where deptno in(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 * from(select deptno,avg(sal)avg_sal from emp group by deptno)t join salgrade on avg_sal between losal and hisal

--第二步：最低的等級值

select min(grade)from(***1111111111)

--第三步：等于最低值的部門編號------------有錯誤,應該是grade= select deptno from(111111111111)where grade =(22222222222222)--第四步：求名稱

select dname from dept where deptno in(33333333333)select dname from dept where deptno in（select deptno from(select * from(select deptno, avg(sal)avg_sal from emp group by deptno)t join salgrade on avg_sal between losal and hisal)where grade =(select min(grade)from(select * from(select deptno, avg(sal)avg_sal from emp group by deptno)t join salgrade on avg_sal between losal and hisal)))

--也可以用視圖的方式來解決--conn sys/bjsxt as sysdba--grant create table, create view, create sequence to scott--根據第一步的結果,建立一個view

create or replace view v1 as--必須明確定義列

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

--查看一下

select * from v1--查詢一下

--帶入view

select dname from dept where deptno in(select deptno from(v1)where grade =(select min(grade)from v1))

第二篇：黑馬程序員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指令(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