《sql語句練習1》

Oracle系列《一》：簡單SQL與單行函數??

?

使用scott/tiger用戶下的emp表和dept表完成下列練習，表的結構說明如下?

emp員工表(empno員工號/ename員工姓名/job工作/mgr上級編號/hiredate受雇日期/sal薪金/comm傭金/deptno部門編號)?

dept部門表(deptno部門編號/dname部門名稱/loc地點)?

工資?＝?薪金?＋?傭金

登錄Oracle數據庫

1、sqlplus?scott/tiger

2、sqlplus?/nolog

SQL>conn?scott/tiger
若是使用SYS的賬號進行登錄的話，則使用以下語句

SQL>conn?/?as?sysdba

?

?

【1】EMP表內容查詢

SQL>?SELECT?*?FROM?emp;?

出錯，原因是沒有找到該表，因為該表時SCOTT用戶的表，所以查詢時應該加上scott.emp就可以了

?

【2】顯示當前用戶

SQL>?show?user

?

【3】查看當前用戶的所有表

SQL>?SELECT?*?FROM?tab;

?

【4】若想重復執行上一條SQL語句，則在sqlplus命令行下輸入"/"即可

?

【5】查詢一張表的結構，例如dept表

SQL>?desc?dept

?

【6】在雇員表中查詢雇員的編號、姓名、工作

SQL>?SELECT?empno,ename,job?FROM?emp;

?

【7】可以為列名取別名，在Linux下Oracle如果英文別名不加上雙引號則會變成大寫

SQL>?SELECT?empno?編號,ename?姓名,job?工作?FROM?emp;

?

【8】查詢所有的工作

SQL>?SELECT?DISTINCT?job?FROM?emp;?

工作可能會重復，加上DISTINCT關鍵字???去重復值

?

【9】若要求按照以下的格式進行結果輸出，如?NO:7469,Name:SMITH,Job:CLERK

SQL>?SELECT?'NO:'||empno||',Name:'||ename||',Job:'||job?FROM?emp;

?

【10】要求列出每個雇員的姓名及年薪

SQL>?SELECT?ename,sal*12?income?FROM?emp;

這里年薪最好用別名進行標識，可以一眼就能明白

?

【11】查看每月可以得到獎金的雇員信息

SQL>?SELECT?*?FROM?emp?WHERE?comm?is?NOT?NULL;

?

?

【12】要求基本工資大于1500，同時可以領取獎金的雇員信息

SQL>?SELECT?*?FROM?emp?WHERE?sal>1500?AND?comm?is?NOT?NULL;

如果是或的是關系，則使用?OR

?

【13】查詢基本工資不大于1500，同時不可以領取獎金的雇員信息

SQL>?SELECT?*?FROM?emp?WHERE?NOT(sal>1500?AND?comm?is?NOT?NULL);

?

【14】查詢在1981年雇傭的全部雇員信息，BETWEEN?..?AND?包含等于的情況

SQL>?SELECT?*?FROM?emp

WHERE?hiredate?BETWEEN?'01-JAN-81'?AND?'31-DEC-81';

?

【15】Oracle對大小敏感，所以查詢時名字要區分大小寫

?

【16】要求查詢出雇員編號不是?7369、7499的雇員信息

SQL>?SELECT?*?FROM?emp

WHERE?empno?NOT?IN(7369,7499);

?

【17】SQL中LIKE語句要注意通配符?%?和?_

SQL>?SELECT?*?FROM?emp

WHERE?hiredate?LIKE?'%81%';

?

【18】查看雇員編號不是7369的雇員信息，使用<>或!=

SQL>?SELECT?*?FROM?emp

WHERE?empno<>7369;

?

【19】要求對雇員的工資由低到高進行排序，升序為默認(ASC)，降序(DESC)

SQL>?SELECT?*?FROM?emp

GROUP?BY?sal?desc;

?

【20】查看出部門號為10的雇員信息，查詢的信息按照工資從高到低，若工資相等則按雇用日期從早到晚排列

SQL>?SELECT?*?FROM?emp

WHERE?deptno=10

ORDER?BY?sal?DESC,hiredate?ASC;

?

數據庫系統中，每個數據庫之間區別最大的就是在函數的支持上，單行函數是最簡單的函數，單行函數分為

1、字符函數：接受字符輸入并且返回字符或數值

2、數值函數：接受數值輸入并返回數值

3、日期函數：對日期型數據進行操作

4、轉換函數：將一種數據類型轉換為另一種數據類型

5、通用函數：NVL、DECODE?函數

?

字符函數：

【1】大小寫轉換?UPPER?和?LOWER

SQL>?SELECT?UPPER('smith')?FROM?dual;

【2】將雇員姓名變為開頭字母大寫，INITCAP

SQL>?SELECT?INITCAP(ename)?FROM?emp;

?

字符函數中有連接函數CONCAT，但不如?||?好用，還有字符串處理的一些函數

字符串截取：substr()

字符串長度：length()

內容替換：replace()

SQL>?SELECT?substr('hello',1,3),length('hello'),replace('hello','l','x')?FROM?dual;

這里注意的是Oracle中字符串截取從0和從1開始都是一樣的，謹防面試提問

?

【3】要求顯示所有雇員的姓名及姓名的后3個字符

SQL>?SELECT?ename,SUBSTR(ename,LENGTH(ename)-2)?FROM?emp;

以上操作顯得較為麻煩，substr()函數是可以倒著截取

SQL>?SELECT?ename,SUBSTR(ename,-3,3)?FROM?emp;

?

數值函數：

1、四舍五入：ROUND()

2、截斷小數位：TRUNC()

3、取余(取模)：MOD

SQL>?SELECT?ROUND(789.536)?FROM?dual;

【1】保留2位小數,(如果是-2則對整數進行四舍五入，變為800了)

SQL>?SELECT?ROUND(783.56,2)?FROM?dual;

【2】使用MOD()函數進行取余操作

SQL>?SELECT?MOD(10,3)?FROM?dual;

?

日期函數：

1、日期?-?數字?=?日期

2、日期?+?數字?=?日期

3、日期?-?日期?=?數字(天數)

【1】求出當前日期

SQL>?SELECT?SYSDATE?FROM?dual;

?

Oracle提供【2】求出從雇用日期到今天所有雇員的雇員編號、姓名和月數

SQL>?SELECT?了以下的日期函數支持：

MONTHS_BETWEEN():求出給定日期范圍的月數

ADD_MONTHS():在指定日期上加上指定的月數，求出之后的日期

NEXT_DAY():下一個的今天的日期

LAST_DAY():求出給定日期的最后一天日期

?

empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate))?FROM?emp;

?

【3】驗證?ADD_MONTHS()、NEXT_DAY()、LAST_DAY()

SQL>?SELECT?ADD_MONTHS(SYSDATE,4)?FROM?DUAL;

SQL>?SELECT?NEXT_DAY(SYSDATE,'MON')?FROM?DUAL;

SQL>?SELECT?LAST_DAY(SYSDATE)?FROM?DUAL;

?

轉換函數：

1、TO_CHAR()：??將日期或數值轉換成字符串

2、TO_NUMBER()：將字符串轉換成數字

3、TO_DATE()：??將字符串轉換成日期

?

【1】將年月日進行分開，要指定拆分的通配符,yyyy-mm-dd

SQL>?SELECT?empno,ename,TO_CHAR(hiredate,'yyyy')?datetime?FROM?emp;

?

【2】將薪水的數字進行格式化，'$99,999'表示美元，'L99,999'表示當地貨幣

SQL>?SELECT?empno,ename,TO_CHAR(sal,'99,999)?salary?FROM?emp;

?

【3】TO_NUMBER()驗證

SQL>?SELECT?TO_NUMBER('123')+TO_NUMBER('123')?FROM?DUAL;

?

【4】TO_DATE()驗證，如下例子執行后顯示為?11-JUL-11

SQL>?SELECT?TO_DATE('2011-7-11','yyyy-mm-dd')?FROM?DUAL;

?

通用函數：

【1】求出每個雇員的年薪(應算上獎金)

SQL>?SELECT?empno,ename,(sal+comm)*12?FROM?emp;

由于comm中有NULL,NULL值計算后還是NULL,正確如下：

SQL>?SELECT?empno,ename,NVL(comm,0),(sal+NVL(comm,0))*12?income?FROM?emp;

NVL可以理解為將NULL值轉換為具體的內容，這里是0

?

【2】DECODE()函數，該函數類似于?IF?...?ELSEIF...ELSE

語法如下：

DECODE(col/expression,選擇1,結果1[，選擇2，結果2，...,默認])

驗證DECODE()函數

SQL>?SELECT?empno,ename,hiredate,

DECODE(job,'CLERK','業務員','SALESMAN','銷售人員',’MANAGER','經理','ANALYST','分析員','PRESIDENT','總裁')?職位

FROM?emp;

?

SQL簡單語句練習：

?

【1】找出傭金高于薪金的60%的員工

SQL>?SELECT?*?FROM?emp?WHERE?comm>sal*0.6

?

【2】找出部門10中所有經理(MANAGER)和部門20中所有辦事員(CLERK)的詳細資料

SQL>?SELECT?*?FROM?emp

WHERE?(deptno=20?AND?job='MANAGER')

OR?(deptno=10?AND?job='CLERK');

?

?

【3】找出既不是經理又不是辦事員但其薪金大于或等于2000的所有員工的資料

SQL>?SELECT?*?FROM?emp

WHERE?job?NOT?IN('MANAGER','CLERK')?AND?sal?>=?2000;

?

【4】找出有獎金的員工的不同國祚

SQL>?SELECT?DISTINCT?job?FROM?emp

WHERE?comm?IS?NOT?NULL;

?

【5】找出各月倒數第3天受雇的所有員工

SQL>?SELECT?*?FROM?emp

WHERE?LAST_DAY(hiredate)-2=hiredate;

?

【6】找出早于12年前受雇的員工

SQL>?SELECT?*?FROM?emp

WHERE?MONTHS_BETWEEN(sysdate,hiredate)/12?>?12;

?

【7】顯示剛好為5個字符的員工的姓名

SQL>?SELECT?ename?FROM?emp

WHERE?length(ename)=5;

?

【8】顯示不帶有"R"的員工的姓名

SQL>?SELECT?ename?FROM?emp

WHERE?ename?NOT?LIKE?'%R%';

?

【9】顯示員工的姓名和受雇日期，將最老的員工排在最前

SQL>?SELECT?*?FROM?emp

Order?BY?hiredate;

?

【10】顯示所有員工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同則按年份排序

SQL>?SELECT?ename,TO_CHAR(hiredate,'yyyy')?year,TO_CHAR(hiredate,'mm')?month?FROM?emp

ORDER?BY?month,year;

?

【11】找出在2月受聘的員工

SQL>?SELECT?*?FROM?emp

WHERE?TO_CHAR(hiredate,'mm')=2;

?

【12】以年月日方式顯示所有員工服務年限

SQL>?SELECT?ename,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12)?year,

TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12))?month,

TRUNC(MOD(sysdate-hiredate,30))?day

FROM?emp;

Oracle系列《二》：多表復雜查詢和事務處理??

多表查詢應該注意去除笛卡爾積，一般多個表時會為表起個別名

?

【1】要求查詢雇員的編號、姓名、部門編號、部門名稱及部門位置

SQL>?SELECT?e.empno,e.ename,d.deptno,d.dname,d.loc

FROM?emp?e,dept?d

WHERE?e.deptno?=?d.deptno;

?

【2】要求查詢每個雇員的姓名、工作、雇員的直接上級領導的姓名(表自關聯)

SQL>?SELECT?e.ename,e.job,m.ename

FROM?emp?e,emp?m

WHERE?e.mgr?=?m.empno;

?

【3】對【2】進行擴充，將雇員所在部門名稱同時列出

SQL>?SELECT?e.ename,e.job,m.ename,d.dname

FROM?emp?e,emp?m,dept?d

WHERE?e.mgr?=?m.empno?AND?e.deptno=d.deptno;

?

【4】查詢每個雇員的姓名、工資、部門名稱，工資在公司的等級(salgrade)，及其領導的姓名所在公司的等級

<1>先確定工資等級表的內容

SQL>?SELECT?*?FROM?salgrade;

?

<2>查詢每個雇員的姓名、工資、部門名稱和工資在公司的等級

SQL>?SELECT?e.ename,e.sal,d.dname,s.grade

FROM?emp?e,dept?d,salgrade?s

WHERE?e.deptno=d.deptno?AND?e.sal?BETWEEN?s.losal?AND?s.hisal;

?

<3>查詢其領導姓名及工資所在公司的等級

SQL>?SELECT?e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade

FROM?emp?e,dept?d,salgrade?s,emp?m,salgrade?ms

WHERE?e.deptno?=?d.deptno?AND?e.sal?BETWEEN?s.losal?AND?s.hisal

AND?e.mgr?=?m.empno?AND?m.sal?BETWEEN?ms.losal?AND?ms.hisal;

?

【5】左連接與右連接的概念，"+"在等號左邊表示右連接，反之，左連接

查詢雇員的編號、姓名及其領導的編號、姓名

SQL>?SELECT?e.empno,e.ename,m.empno,m.ename

FROM?emp?e,emp?m

WHERE?e.mgr?=?m.empno(+);

就發現將KING的那條記錄也連過來了

?

SQL1999語法中有如下幾種連接(了解)

1、交叉連接CROSS?JOIN，產生笛卡爾積

SQL>?SELECT?*?FROM?emp?CROSS?JOIN?dept;

2、自然連接NATURAL?JOIN,自動進行關聯字段的匹配

SQL>?SELECT?*?FROM?emp?NATURAL?JOIN?dept;

3、使用USING子句，直接關聯操作列

SQL>?SELECT?*?FROM?emp?JOIN?dept?USING(deptno)

WHERE?deptno=30;

4、使用ON子句，用戶自己編寫的條件

SQL>?SELECT?*?FORM?emp?JOIN?dept?ON(emp.deptno?=?dept.deptno)

WHERE?deptno=30;

5、左連接(左外連接、LEFT?(OUTER)?JOIN)、右連接(右外連接、RIGHT?(OUTER)?JOIN)

?

組函數及分組統計

1、COUNT():求出全部記錄數

2、MAX()：求出一組中最大值

3、MIN():求出最小值

4、AVG()：求出平均值

5、SUM()：求和

【1】求出每個部門的雇員數量

SQL>?SELECT?deptno,count(empno)

FROM?emp

GROUP?BY?deptno;

?

【2】按部門分組，并顯示部門的名稱，及每個部門的員工數

SQL>?SELECT?d.dname,COUNT(e.empno)

FROM?emp?e,dept?d

WHERE?e.deptno=d.deptno

GROUP?BY?d.dname;

?

【3】要求顯示平均工資大于2000的部門編號和平均工資

SQL>?SELECT?deptno,AVG(sal)

FROM?emp

WHERE?AVG(sal)>2000

GROUP?BY?deptno;

出錯，WHERE子句中不能出現分組函數的條件，要使用HAVING子句

上述語句應該改為如下

SQL>?SELECT?deptno,AVG(sal)

FROM?emp

GROUP?BY?deptno

HAVING?AVG(sal)>2000

?

【4】顯示非銷售人員工作名稱以及從事同一工作雇員的月工資總和，并且要求從事同一工作的雇員月工資合計大于$5000,

輸出結果按月工資的合計升序排序

<1>按工作分組，求出非銷售人員的月工資總和

SQL>?SELECT?job,SUM(sal)

FROM?emp

WHERE?job<>'SALESMAN'

GROUP?BY?job;

<2>對分組條件進行限制，然后進行排序,HAVING子句不能使用別名

SQL>?SELECT?job,SUM(sal)?totalSal

FROM?emp

WHERE?job<>'SALESMAN'

GROUP?BY?job

HAVING?SUM(sal)?>?5000

ORDER?BY?totalSal;

?

【3】分組函數可以嵌套使用，但是在SELECT列中就不能再出現該分組條件的列名了

SQL>?SELECT?deptno,MAX(AVG(sal))

FROM?emp

GROUP?BY?deptno;

出錯！修改如下

SQL>?SELECT?MAX(AVG(sal))

FROM?emp

GROUP?BY?deptno;

?

【4】查詢出比7654工資要高的全部雇員的信息

<1>首先要查詢雇員編號7654的工資

SQL>?SELECT?sal?FROM?emp?WHERE?empno=7654;

<2>以上述條件的結果最后后續查詢的依據

SQL>?SELECT?*?FROM?emp

WHERE?sal>(SELECT?sal?FROM?emp?WHERE?empno=7654);

?

子查詢在操作中分為以下三類：

1、單列子查詢：返回的結果是一列的內容

2、單行子查詢：返回多個列，也可能是一條記錄

3、多行子查詢：返回多個記錄

?

【1】要求查詢工資比7654高，同時與7788從事相同工作的全部雇員

SQL>?SELECT?*?FROM?emp

WHERE?sal>(SELECT?sal?FROM?emp?WHERE?empno=7654)

AND?job=(SELECT?job?FROM?emp?WHERE?empno=7788);

?

【2】要求查詢?部門名稱、部門員工數、部門平均工資，部門的最低收入雇員的姓名

<1>查詢部門員工數、部門平均工資

SQL>?SELECT?deptno,COUNT(empno),AVG(sal)

FROM?emp

GROUP?BY?deptno;

<2>查詢部門的名稱，及最低收入雇員姓名，要進行表關聯(子查詢)

SQL>?SELECT?d.dname,ed.c,ed.a,e.ename

FROM?dept?d,(

?SELECT?deptno,COUNT(empno)?c,AVG(sal)?a,MIN(sal)?min

?FROM?emp

?GROUP?BY?deptno)?ed,?emp?e

WHERE?d.deptno=ed.deptno?AND?e.sal?=?ed.min;

?

若上述存在兩個最低工資的情況，則會出錯，在子查詢中存在以下3種查詢的操作符號

IN：指定一個查詢范圍，例如查詢每個部門的最低工資(返回值有多個)

SQL>?SELECT?*?FROM?emp

WHERE?sal?IN(SELECT?MIN(sal)?FROM?emp?GROUP?BY?deptno);

?

ANY：=ANY(與IN操作一樣)、>ANY(比最小大)、<ANY(比最大小)

SQL>?SELECT?*?FROM?emp

WHERE?sal?<ANY(SELECT?MIN(sal)?FROM?emp?GROUP?BY?deptno);

?

ALL:?>ALL(比最大要大)、<ALL(比最小的小),SQL語句類似上面

?

SQL多列子查詢示例如下

SQL>?SELECT?*?FROM?emp

WHERE?(sal,NVL(comm,-1))?IN

?(SELECT?sal,NVL(comm,-1)?FROM?emp?WHERE?deptno=20);

?

?

數據庫更新操作INSERT、UPDATE、DELETE

?

【1】復制一張表，例如復制EMP表為MYEMP

SQL>?CREATE?TABLE?MYTEMP?AS?SELECT?*?FROM?emp;

?

【2】將編號為7899的雇員的領導取消

SQL>?UPDATE?myemp?SET?mgr=null?WHERE?empno=7899;

?

【3】更新時，一定要注意不能批量更新(加上WHERE子句)，多列更新例子如下

SQL>?UPDATE?myemp?SET?mgr=null,comm=null?WHERE?empno?IN(7369,8899);

?

【4】刪除掉全部領取獎金的雇員

SQL>?DELECT?FROM?emp?WHERE?comm?is?NOT?NULL;

?

事務處理?ACID

A：Atomicity???原子性：事務中的操作或者都完成，或者都取消

C：Consistency?一致性：事務中的操作保證數據庫中的數據不會出現邏輯上不一致的情況

I：Isolation???隔離性：當前的事務與其他未完成的事務是隔離的

D：Durability??持久性：在COMMIT之后，數據永久保存在數據庫中，在此之前，事務的操作都可以回滾

?

驗證事務過程：

<1>創建一張臨時表，只包含部門10

SQL>?CREATE?TABLE?emp10?AS?SELECT?*?FROM?emp?WHERE?empno=10;

<2>刪除emp10中的7782雇員

SQL>?DELETE?FROM?emp10?WHERE?empno=7782;

再打開另一個窗口，發現數據還存在，此時如果可以使用以下的兩種命令進行事務處理

COMMIT?和?ROLLBACK?提交事務和回滾事務

?

SQL查詢練習

【1】列出至少一個員工的所有部門

SQL>?SELECT?d.*,ed.cou?FROM?dept?d,(

?SELECT?deptno,COUNT(empno)?cou?FROM?emp

?GROUP?BY?deptno

?HAVING?COUNT(empno)?>?1)?ed

WHERE?d.deptno=ed.deptno;

?

【2】列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門

SQL>?SELECT?d.deptno,d.dname,e.empno,e.ename

FROM?dept?d,emp?e

WHERE?d.deptno?=?e.deptno(+);

?

【3】列出所有"CLERK"(辦事員)的姓名及其部門名稱，部門的人數

<1>關聯dept表

SQL>?SELECT?e.ename,d.dname

FROM?emp?e,dept?d

WHERE?e.deptno=d.deptno?and?e.job='CLERK';

<2>使用GROUP?BY?完成部門分組人數

SQL>?SELECT?e.ename,d.dname,ed.cou?FROM?emp?e,dept?d,(

?SELECT?deptno,COUNT(empno)?cou?FROM?emp

?GROUP?BY?deptno)?ed

WHERE?job='CLERK'?AND?e.deptno=d.deptno?AND?ed.deptno=e.deptno;

?