Oracle基礎(chǔ)學(xué)習(xí)

大型關(guān)系數(shù)據(jù)庫

system

dhee

://IP:5560/isqlplus

導(dǎo)入數(shù)據(jù)庫腳本：@腳本路徑

所有的關(guān)系數(shù)據(jù)對表的操作使用的差不多上SQL語句

杳詢表使用SQL語句中的SELECT語句

差不多SELECT語句：

SELECT列名洌名,……|*|算術(shù)表達(dá)式

FROM表名

-查詢employees表中所有職員的last_name,salary

SELECTLAST_NAME,SALARY

FROMEMPLOYEES;

Selectlast_name,salary

Fromemployees;

一查詢departments表中所有部門的信息

SELECT*

FROMDEPARTMENTS;

--算術(shù)運(yùn)算符：+,*,!

+：只有加法運(yùn)算的功能，沒有連接作用

/：

SELECT5/2

FROMDUAL;

-查詢employees表中所有職員的last_name,job_id,salary,年薪(salary*12)

SELECTLAST_NAMEJOB_ID,SALARY,SALARY*12

FROMEMPLOYEES;

列別名

1.列名列別名

2.列名AS列別名

注意：當(dāng)列別名要區(qū)分大小寫，或者列別名中包含了專門字符，或者列別名為關(guān)鍵時，需要

將別名放在一對雙引號中

雙引號在關(guān)系數(shù)據(jù)庫中表示列別名

*/

SELECTLAST.NAME"USER"J0BJD.SALARY,SALARY*12AS"YEARSAL"

FROMEMPLOYEES;

一字符串：一對單引號

SELECT'DHEE'AS名字;大連，AS"city"

FROMDUAL;

-連接符：||

SELECTFIRST_NAME|r，'||LAST_NAMEAS姓名

FROMEMPLOYEES;

-屏蔽查詢結(jié)果中重復(fù)記錄：DISTINCT

SELECTDISTINCTJOBJD

PROMEMPLOYEES;

SELECTDISTINCTJOB」D,SALARY

FROMEMPLOYEES;

-過濾與排序

-過濾：WHERE子句

SELECT

FROM

[WHERE條件];

一兗詢employees表中50號部門職員的last_name,salary,depar(menl_id

SELECTLAST_NAME,SALARY.DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENT_1D=5O;

--查詢employees表中所有工資大于10000的職員的Iast_name,job_id,salary

SELECTLAST_NAME,JOB」D,SALARY

FROMEMPLOYEES

WHERESALARY>10000;

一查詢employees表中king職員的employee_id,salary,department_id

SELECTEMPLOYEE_ID,SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHERELAST_NAME='King';

-查詢條件為字符串時，查詢的值必須放在一對單引號中，同時查詢的內(nèi)容區(qū)分大小寫

—查詢employees表中哪些職員的職位(job_id)為it_prog,顯示這些職員的

last_namc,job_id,salary

SELECTLAST_NAME,JOBJD,SALARY

FROMEMPLOYEES

WHEREJOB_ID='IT_PROGr;

SELECTLAST_NAME,HIRE_DATE

FROMEMPLOYEES

WHEREHIRE_DATE=，07-6月-94：

-查詢條件為口期類型時，查詢的值必須放在一對單引號中，同時口期格式必須為Oracle默

認(rèn)的日期格式。

-Oracle默認(rèn)的日期格式為：DD-MON-RR

--查詢employees表中97年以后入職的職員的last」iame.hire_date(包含97年)

SELECTLAST_NAME,HiRE_DATE

PROMEMPLOYEES

WHEREHIRE_DATE>='Oi-l月5;

--BETWEEN...AND…:使列大于等于一個值，同時小于等于另一個值1包含邊界值)

一前旬employees表中工資在9000到12000的職員的lasl_name,salary

SELECTLAST_NAME,SALARY

FROMEMPLOYEES

WHERESALARYBETWEEN9000AND12000;

-查詢employees表中95年到97年入職職員的Iasl_name,hire_dale(包含95年與97年)

SELECTLAST_NAME,HIRE_DATE

FROMEMPLOYEES

WHEREHIRE_DATEBETWEEN'01-1月-95,AND31-12月-97，；

-IN(值列表)：使列與列表中任意一個值進(jìn)行匹配

一查詢employees表中在20,50,90部門「作的職員的last_name,salary,department_id

SELECTLAST_NAME.SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENT_1DIN(20,50,90);

-查詢employees表中職位(job_id)為ic_prog或st_clerk或sa_rep的職員的

last_namejob_id,salary

SELECTLAST_NAME,JOBJD,SALARY

FROMEMPLOYEES

WHEREJOB」DIN('IT_PROG',ST_CLERK',SA_REP');

--LIKE:模糊查詢

SELECTLAST_NAME,SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENT_iD=20ORDEPARTMENT_ID=50ORDEPARTMENT」D=90;

一查詢employees表中l(wèi)ast_name中包含A(不區(qū)分大小寫)的職員的last_name

SELECTLAST_NAME

FROMEMPLOYEES

WHERELAST_NAMELIKE'%A%'ORLAST_NAMELIKE'%a%';

一查詢employees表中工資在5000-12000之間，同時在20或50號部門工作的職員的

last_name,salary,department_id

SELECTLAST_NAME,SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHERESALARYBETWEEN5000AND12000ANDDEPARTMENT」DIN(20,50);

SELECTLAST_NAME,SALARY,DEPARTMENT_ID

PROMEMPLOYEES

WHERE(SALARY>=50(i0ANDSALARY<=12000)AND(DEPARTMENT_ID=20OR

DEPARTMENT_ID=50);

一行詢employees表中工資不在9000-12000之間的職員的lasl_name,salary

SELECTLAST_NAME,SALARY

FROMEMPLOYEES

WHERENOTSALARYBETWEEN9000AND12000;

一排序：ORDERBY子句

SELECT3

FROMI

[WHERE]2

(ORDERBY排序列]4

升序:ASC,默認(rèn)為升序

降序:DESC

*/

SELECTLAST_NAME,SALARYAS"sal”

FROMEMPLOYEES

WHERESALARY〉10000

ORDERBY"sal"DESC;

SELECTLAST_NAME,HIRE_DATE

FROMEMPLOYEES

ORDERBYHIRE_DATEDESC;

SELECTLAST_NAME

FROMEMPLOYEES

ORDERBYLAST_NAMEDESC;

1.ORDERBY后面能夠放列名

2.ORDERBY后面能夠放列別名(注意大小寫)

3.ORDERBY后面能夠放查詢結(jié)果中列的序號

*/

一查詢employees表中所有職員的lasl_name,job_id,depar【mem_id,salary,結(jié)果按部門升序，工

資降序排序

SELECTLAST_NAME,JOBJD,DEPARTMENT_ID,SALARY

FROMEMPLOYEES

ORDERBYDEPARTMENT"ASCNULLSFIRST,SALARYDESC;

SELECTLAST.NAME,JOBJD,DEPARTMENT_ID,SALARY

FROMEMPLOYEES

ORDERBYDEPARTMENT_IDDESCNULLSLAST,SALARYDESC;

-NULLSFIRST/NULLSLAST

--單行函數(shù)：

SELECTLAST_NAME,UPPER(LAST_NAME)--將參數(shù)的值變?yōu)榇髮?/p>

FROMEMPLOYEES；

SELECTLAST_NAME,LOWER(LAST_NAME)—將參數(shù)的值變?yōu)樾?/p>

FROMEMPLOYEES;

--查詢employees表中l(wèi)ast_name中包含A(不區(qū)分大小寫)的職員的last_name

SELECTLAST_NAME

FROMEMPLOYEES

WHEREUPPER(LAST_NAME)LIKE'%A%';

SELECTLAST_NAME

FROMEMPLOYEES

WHERELOWER(LAST_NAME)LIKE'%a%';

SELECTINITCAPCabcabc!ABC#ABC$ABC%ABC???AABC)--將單詞的單字母變?yōu)榇髮?/p>

FROMDUAL;

SELECTLAST_NAME,LENGTH(LAST_NAME)

FROMEMPLOYEES;

SELECTLENGTHC中國)-求字符數(shù)

FROMDUAL:

SELECTLENGTHBf中國')一求字節(jié)數(shù)

FROMDUAL;

SELECTLAST_NAME,SUBSTR(LAST_NAME,3)--截取字符串：從第N位開始截取一宜截

取到最后

FROMEMPLOYEES;

SELECTLAST_NAME,SUBSTR(LAST_NAME,3,2)-截取字符串：從第N位開始截取，械取

指定長度的字符串

FROMEMPLOYEES;

SELECTLAST_NAME,SUBSTR(LAST_NAME,-3)-截取字符串：從倒數(shù)第N位開始截取一

直截取到最后

FROMEMPLOYEES;

SELECTLAST_NAME,SUBSTR(LAST_NAME,-3,2)--截取字符串：從倒數(shù)第N位開始截取,

械取指定長度的字符串

FROMEMPLOYEES;

SELECTLAST_NAME,INSTR(LAST_NAME,'a>-在第一個參數(shù)中查找第二個參數(shù)首次顯現(xiàn)

的位置，沒找到返回0

FROMEMPLOYEES；

--顯示第一個參數(shù)，并用第二個參數(shù)指定第一個參數(shù)的長度，假如第一個參數(shù)的長度不夠用

第三個參數(shù)在左/右邊補(bǔ)齊長度

SELECTLAST_NAME,LPAD(LAST_NAME,20,'$'),RPAD(LAST_NAME,20,'$')

FROMEMPLOYEES;

SELECTLAST_NAME.REPLACE(LAST_NAME：a','「)一將第一個參數(shù)中顯現(xiàn)的第二個參數(shù)

用第三個參數(shù)替換掉

FROMEMPLOYEES;

SELECTTRIM('ABCABC')AS”A"—刪除字符串左右顯現(xiàn)的空格

FROMDUAL;

SELECTTRIM('A'FROM，AAABACAAA>-刪除字符串左右顯現(xiàn)的指定的字符

FROMDUAL;

--查詢employees表中所有職員的last_namc,要求顯示的內(nèi)容中首字母為小寫，其它字母均為

大寫

SELECTLOWER(SUBSTR(LAST_NAME.1,1))||UPPER(SUBSTR(LAST_NAME,2))

FROMEMPLOYEES;

-查詢employees表中l(wèi)ast_name中包含a(小寫)的職員的last_name(不能使用LIKE)

SELECTLAST_NAME

FROMEMPLOYEES

WHEREiNSTR(LAST_NAME；a')>0;

-查詢employees表中l(wèi)ast_name中為4個字符的職員的last_name

SELECTLAST_NAME

FROMEMPLOYEES

WHERELAST.NAMELIKE'

SELECTLAST.NAME

FROMEMPLOYEES

WHERELENGTH(LAST_NAME)=4;

一查詢employees表中l(wèi)as5ame中包含a或e的職員的:asl」iame

SELECTLAST_NAME

FROMEMPLOYEES

WHERELAST_NAMELIKE'%a%'ORLAST_NAMELIKE'%e%';

SELECTLAST_NAME

FROMEMPLOYEES

WHEREINSTR(LAST_NAME；a')>0ORINSTR(LAST_NAME,'e')>0;

--四舍五入：

SELECTROUND(256.987),ROUND(256.987,2),ROUND(256,987.0),ROUND(256.987,-1)

FROMDUAL;

--截斷數(shù)字：

SELECTTRUNC(256.987),TRUNC(256.987,2),TRUNC(256.987,0),TRUNC(256.987,-l)

FROMDUAL;

-MOD()：取余數(shù)

SELECTMOD(15,2)

FROMDUAL;

SELECTFLOOR(12.I)

FROMDUAL;

SELECTCEIU12.1)

FROMDUAL;

--獲得數(shù)據(jù)庫服務(wù)器的時刻：SYSDATE,日期+時刻

SELECTSYSDATE

FROMDUAL;

SQLServer:

getDate。:獲得系統(tǒng)時刻

MySQL:

currdateO：獲得系統(tǒng)口期，沒有時刻

now()：獲得系統(tǒng)日期+時刻

*/

日期+天數(shù)=日期

日期-天數(shù)=日期

口期-口期二天數(shù)

日期不能加日期

*/

SELECTSYSDATE-100

FROMDUAL;

-查詢employees表中所有職員的last_name,hire_date,入職年數(shù)

SELECTLAST_NAME,HIRE_DATE,TRUNC((SYSDATE-HIRE_DATE)/365)

FROMEMPLOYEES;

-MONTHS_BETWEEN①1,D2):求兩個參數(shù)相差的月數(shù)

SELECT

LAST_NAME,HIRE_DATE.TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)AS

年數(shù)

FROMEMPLOYEES;

—ADD_MONTHS(D,N):在一個日期上加上指定的月數(shù)

SELECTLAST_NAME,HIRE_DATE,ADD_MONTHS(HIRE_DATE,35)

FROMEMPLOYEES;

-NEXT_DAY():

SELECTNEXT_DAY(SYSDATE,7)

FROMDUAL;

SELECTNEXT_DAY(SYSDATE,'星期四')

FROMDUAL;

—LAST_DAY():求日期所在月份的最后一天

SELECTLAST_DAY(SYSDATE)

FROMDUAL;

-類型轉(zhuǎn)換：隱式轉(zhuǎn)換，顯式轉(zhuǎn)換

-隱式轉(zhuǎn)換：Oracle自動將一個類型轉(zhuǎn)換為另一個類型。

SELECTLAST_NAME,SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENT_1D='5O';

--顯式轉(zhuǎn)換：

/*

數(shù)字與字符進(jìn)行類型轉(zhuǎn)換

日期與字符進(jìn)行類型轉(zhuǎn)換

數(shù)字與日期不能進(jìn)行類型轉(zhuǎn)換

刊

-TO_CHAR(D,M):將口期類型D按模板M轉(zhuǎn)換為字符類型的值。

SELECTLAST_NAME,HIRE_DATE,TO_CHAR(HIRE_DATE,YYYY-MM-DDDY')

FROMEMPLOYEES;

SELECTLAST_NAME,TO_CHAR(HIRE_DATE：YYYY)

FROMEMPLOYEES;

SELECTTO_CHAR(SYSDATE：DDDDDD')

FROMDUAL；

SELECTTO_CHAR(S￥SDATE,'YYYY-MM-DDHH24:MI:SS')

FROMDUAL;

SELECTLAST_NAME,H1RE_DATE,TO_CHAR(HIRE_DATE：FMYYYY"年”MM"月”DD"日“

DY')

FROMEMPLOYEES;

一查詢employees表中所有周三入職的職員的last_name,hire_date(格式為：YYYY-MM-DD

DY)

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,,YYYY-MM-DDDY')

FROMEMPLOYEES

WHERETO_CHAR(HIRE_DATE：DY尸星期三，；

SELECTLAST_NAME,TO_CHAR(HIRE_DATE；YYYY-MM-DDDY')

FROMEMPLOYEES

WHERETO_CHAR(HIRE_DATE.'D')='4';

-TO_CHAR(N,M):將數(shù)字類型N按模板M轉(zhuǎn)換為字符類型的值

SELECTTO_CHAR(256.987),

TO_CHAR(256.897.,FM99999.99'),

TO_CHAR(256.987：99,),

TO_CHAR(256.987：9999999999999,),

TO_CHAR(256.897,,FM()(X)()().()0,),

TO_CHAR(256.897,(FM99999.00,),

TO_CHAR(256456.897；FM9,999,999.(X)X

TO_CHAR(256456.897：FM$9,999,999.00,),

TO_CHAR(256456.897；FML9,999,999.00')

FROMDUAL;

一查詢employees表中所有5月入職職員的last_name,hiie_date(格式為:1999年1月1日星

期五)，結(jié)果按hire_date升序排序。

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,FMYYYY"年"MM"月”DD"曰"DY')AS

IIIRE_DATE

FROMEMPLOYEES

WHERETO_CHAR(HIRE_DATE1MONA5月’

ORDERBY2ASC;

--查詢employees表中所有職員的last_name與職員3倍的工資(格式為：$50,000.00)，結(jié)果

按工資的降序排序。

SELECTLAST_NAME,TO_CHAR(SALARY*3,'FM$999,999.00)

FROMEMPLOYEES

ORDERBYSALARYDESC；

-TO_DATE(C,M)：依照模板M將字符類型C值轉(zhuǎn)換為日期類型的值

SELECTTO_DATE('l999-10-10'/YYYY-MM-DD*)

FROMDUAL;

SELECTTO_DATE('2021-10-1VYYYY-MM-DD')-SYSDATE

FROMDUAL;

—TO_NUMBER(C,M):依照模板M將字符類型C轉(zhuǎn)換為數(shù)字類型的值

SELECT

TO_NUMBERC256.23'),TO_NUMBER('2,256.23','9,999.00'),TO_NUMBER('$2,256.23','$9999.

00')

FROMDUAL;

一當(dāng)NULL參與到算術(shù)運(yùn)算時結(jié)果一定為NULL

—NVL():當(dāng)?shù)谝粋€參數(shù)不為NULL時，返回第一個參數(shù)的值，當(dāng)?shù)谝粋€參數(shù)為NULL時，返

回第二個參數(shù)的值，兩個參數(shù)的類型必須一致。

SELECTLAST_NAMESALARY,NVL(TO_CHAR(COMMISSION_PCT),'沒有傭金')AS傭

金’(SALARY*12)+(SALARY*12*NVL(COMMISSION_PCT.O))AS年收入

FROMEMPLOYEES;

-NVL2():當(dāng)?shù)谝粋€參數(shù)不為NULL,返回第二個參數(shù)，當(dāng)?shù)谝粋€參數(shù)為NULL,返回第三個

參數(shù)

SELECTLAST_NAME,NVL2(COMMISSION_PCT,'有傭金沒有傭金')

FROMEMPLOYEES;

-查詢employees表中所有職員的lasl_name,job_id,salary,以及增加后的工資，假如職員的

job_id為IT_PROG時，增加后的工資為工資為1.2倍，假如job_id為ST_CLERK時，增加

后的工資為工資的1.5倍，假如job_id為SA_REP時，增加后的工資為工資的2倍,其它jub_id

的職員的工資不變。

SELECTLAST_NAME,JOB_ID,SALARY,

CASE

WHENJOB」D=TT_PROG'THENSALARY*1.20

WHENJOB」D二ST_CLERKTHENSALARY*1.50

WHENJOB」D=5A_REPTHENSALARY*2

ELSESALARY

ENDAS增加后工資

FROMEMPLOYEES;

SELECTLAST_NAME,JOB」D,SALARY,

CASEJOB」D

WHEN'IT.PROG'THENSALARY*1.20

WHEN'ST-CLERK'THENSALARY*1.50

WHENSA_REP'THENSALARY*2

ELSESALARY

ENDAS增加后工資

FROMEMPLOYEES;

SELECTLAST_NAMEJOB_ID,SALARY,

DECODE(

JOBJD,

'IT.PROG',

SALARY*1.2,

'ST_CLERK',

SALARY*1.5,

'SA_REP',

SALARY*2,

SALARY

)AS增加后工資

FROMEMPLOYEES;

--多表連接

/*

笛卡爾集：

行數(shù)：表行數(shù)的乘積

產(chǎn)生笛卡爾集的緣故：沒有連接條件或連接條件無效

*/

--等值連接［內(nèi)連接，簡單連接）

--查詢所有部門的department_name,city

SELECTDEPARTMENT_NAME,CITY

FROMDEPARTMENTS,LOCATIONS

WHEREDEPARTMENTS.LOCATION_ID=LOCATIONS.LOCATION_ID;

一查詢所有職員的last_name,department_name

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEES,DEPARTMENTS

WHEREEMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID;

-表別名:表名表別名

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMRDEPARTMENTSDEPT

WHEREEMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID;

--杳詢職員的last_name,department_id.department_name

SELECTEMRLAST_NAME,DEPT.DEPARTMENT_ID,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMP,DEPARTMENTSDEPT

WHEREEMRDEPARTMENT_ID=DEPT.DEPARTMENT_ID;

一兗詢職員的lasl_name,job_id,job_lille（jobs表中）

SELECTEMP.LAST_NAME,EMP.JOB_ID,JOB.JOB_TITLE

FROMEMPLOYEESEMPJOBSJOB

WHEREEMP.JOBJD=JOB.JOB_ID;

查詢employees表中所有職員的last_nanie,hire_date(格式為:1999-10-10),工齡(只保留整數(shù)部

分)，以及獎金，假如職員的工齡在15(不包含15年)年以上，獎金為8倍的工資，假如職

員的工齡大于18(不包含18年)年以上，獎金為10倍的工資，假如職員的工齡在201不

包含20年)年以上，獎金為15倍的工資，假如職員的工齡小于或等于15年，那么沒有獎

金顯示”此職員不在此次活動范疇內(nèi)”.

*/

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD')AS

HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)AS"工齡"，

CASE

WHENTRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>20THEN

TO_CHAR(SALARY*15)

WHENTRUNC(M0NTHS_BETWEEN(SYSDATE.HIRE_DATE)/12)>18THEN

TO_CHAR(SALARY*10)

WHENTRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>15THEN

TO_CHAR(SALARY*8)

ELSE，此職員不在此次活動范疇內(nèi)，

ENDAS獎金

FROMEMPLOYEES;

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD')AS

HIRE_DATE.TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)AS“工齡”，

CASE

WHENTRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>I5AND

TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)<=18THENSALARY*8

WHENTRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>18AND

TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)<=20THENSALARY*10

WHENTRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>20THENSALARY*15

ELSEO

ENDAS獎金

FROMEMPLOYEES;

-節(jié)詢職員的last_name,job_ti(le,deparlmenl_name,cily

SELECTEMP.LAST_NAME,JOB.JOB_TITLE,DEPT.DEPARTMENT_NAME,LOC.CITY

FROMEMPLOYEESEMRDEPARTMENTSDEPT,LOCATIONSLOCJOBSJOB

WHEREEMP.DEPARTMENT_ID=DEPT.DEPARTMENT」D

ANDDEPT.LOCATION」D=LOC.LOCATION」D

ANDEMP.JOB_ID=JOB.JOB_ID；

SQL:1999

一查詢職員的last_name,department_name

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMP,DEPARTMENTSDEPT

WHEREEMP.DEPARTMENT」D二DEPT.DEPARTMENT」D;

SELECTEMP.LAST_NAME,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMPINNERJOINDEPARTMENTSDEPT

ONEMP.DEPARTMENT」D二DEPT.DEPARTMENT」D

WHEREEMP.LAST_NAMELIKE'%a%';

一查詢職員Wlast_name,job_title,department_name,city

SELECTEMP.LAST_NAME,JOB.JOB_TITLE.DEPT.DEPARTMENT_NAME,LOC.CITY

FROMEMPLOYEESEMRDEPARTMENTSDEPTXOCATIONSLOC,JOBSJOB

WHEREEMP.DEPARTMENT」D=DEPT.DEPARTMENT」D

ANDDEPT.LOCATION_ID=LOC.LOCATION_ID

ANDEMRJOB_ID=JOB.JOBJD;

SELECTEMRLAST_NAME,JOBJOB_TITLE,DEPT.DEPARTMENT_NAME.LOC.CITY

FROMEMPLOYEESEMPJOINDEPARTMENTSDEPTON

EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID

JOINJOBSJOBONJOB.JOB_ID=EMP.JOB_ID

JOINLOCATIONSLOCONLOC.LOCATION」D=DEPT.LOCATION」D;

--內(nèi)連接：查詢出滿足連接條件的數(shù)據(jù)

--外連接：查詢出滿足連接條件與不滿足連接條件的數(shù)，左外連接、右外連接

--查詢所有職員的last_name,department_name

SELECTEMP.LAST_NAME,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMPLEFTJOINDEPARTMENTSDEPT

ONEMP.DEPARTMENT」D二DEPT.DEPARTMENT」D;

SELECTEMP.LAST_NAME,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMPRIGHTJOINDEPARTMENTSDEPT

ONEMP.DEPARTMENTJD=DEPT.DEPARTMENT_ID;

SELECTEMP.LAST_NAME,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMPFULLOUTERJOINDEPARTMENTSDEPT

ONEMPDEPARTMENT」D=DEPT.DEPARTMENT」D;

-ORACLE中獨(dú)有的外連接:(+)

-左外連接

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMP.DEPARTMENTSDEPT

WHEREEMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID(+)；

-右外連接

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMP,DEPARTMENTSDEPT

WHEREEMP.DEPARTMENT_ID(+)=DEPT.DEPARTMENT_ID;

-SQLServer:*

-左外連接

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMP.DEPARTMENTSDEPT

WHEREEMP.DEPARTMENT_ID*=DEPT.DEPARTMENT_ID;

-右外連接

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMP.DEPARTMENTSDEPT

WHEREEMP.DEPARTMENT_ID=*DEPT.DEPARTMENT_ID;

-組函數(shù)（聚合函數(shù)）

—SUM()：求總和

SELECTSUM(SALARY)

FROMEMPLOYEES;

-AVG():求平均值

SELECTAVG(SALARY)

FROMEMPLOYEES;

--MAX()/MIN():求最大值限小值

SELECTMAX(SALARY),MIN(SALARY)

FROMEMPLOYEES;

SELECTMAX(HIRE_DATE),MIN(HIRE_DATE)

FROMEMPLOYEES;

SELECTMAX(LAST_NAME),MIN(LAST_NAME)

FROMEMPLOYEES;

-求50號部門的平均工資，總工資，最高工資與最低工資

SELECTAVG(SALARY),SUM(SALARY),MAX(SALARY),MIN(SALARY)

FROMEMPLOYEES

WHEREDEPARTMENT_ID=50;

-COUNTO

SELECTCOUNT(*)-返回結(jié)果的行數(shù)

FROMEMPLOYEES

WHEREDEPARTMENT」D=50；

SELECTCOUNT(COMMISSION_PCT)-返I可指定列中不為NULL的值的個數(shù)

FROMEMPLOYEES;

--查詢employees表中l(wèi)ast_name中包含A(不區(qū)分大小寫)的職員的人數(shù)

SELECTCOUNT(*)

FROMEMPLOYEES

WHEREUPPER(LAST_NAME)LIKE'%A%';

SELECT

EMP.LAST_NAME,EMP.JOB_iD,EMP.DEPARTMENT_ID,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMPJOINDEPARTMENTSDEPTON

EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID

JOINLOCATIONSLOCONLOC.LOCATIONJD=DEPT.LOCATION_ID

WHERELOC.CITY=Toronto';

SELECT

EMP.LAST_NAME,EMRJOB_ID,EMP.DEPARTMENT_ID,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMRDEPARTMENTSDEPT.LOCATIONSLOC

WHEREEMP.DEPARTMENT_ID;DEPT.DEPARTMENT」DAND

LOC.LOCATION_ID=DEPT.LOCATION_IDANDLOC.CITY='Toronto';

-創(chuàng)建一個查詢顯示雇員的last_name并帶星號顯示他們的月薪，每個星號表示1000美圓。

按薪水降序排序數(shù)據(jù)。列標(biāo)簽為EMPLOYEES_AND_THEIR_SALARIESo

--查詢所有90號部門職員的last_name,salary(格式為：Y50,000.00),city

SELECTEMP.LAST_NAME,TO_CHAR(EMP.SALARY,'FML99.999.00'),LOC.CITY

FROMEMPLOYEESEMPJOINDEPARTMENTSDEPTONEMP.DEPARTMENTJD=

DEPT.DEPARTMENT」D

JOINLOCATIONSLOCONLOC.LOCATION」D=DEPT.LOCATIONJD

WHEREDEPT.DEPARTMENT」D二90;

-查詢97年以后入職職員的人數(shù)。

SELECTCOUNT(*)

PROMEMPLOYEES

WHEREHIRE_DATE>TO_DATE('1997-12-31','YYYY-MM-DD');

SELECTCOUNT(*)

FROMEMPLOYEES

WHEREHIRE_DATE>'31-12月-97，；

SELECTCOUNT(*)

FROMEMPLOYEES

WHERETO_CHAR(HIRE_DATE,'YYYY')>'1997'；

—GROUPBY子句:分組。統(tǒng)計，報表。

SELECT4

FROM1

(WHERE]2

[GROUPBY]3

[ORDERBY]5

--查詢每個部門的最高的工資

SELECTDEPARTMENT」D,MAX(SALARY)

FROMEMPLOYEES

GROUPBYDEPARTMENT」D

ORDERBY1ASC;

SELECTDEPARTMENT」D,MAX(SALARY)

FROMEMPLOYEES

GROUPBYDEPARTMENTJD;

--查詢每個職位的平均工資，顯示job_id與平均工資

SELECTJOB」D,AVG(SALARY)

FROMEMPLOYEES

GROUPBYJOBJD;

-查詢每個部門最高的工資

SELECTMAX(SALARY)

FROMEMPLOYEES

GROUPBYDEPARTMENT」D;

一查詢每個部門的人數(shù)，顯示dcpartmcnt_namc與人數(shù)

SELECTDEPT.DEPARTMENT_NAME,COUNT(*)

FROMEMPLOYEESEMPJOINDEPARTMENTSDEPTON

EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID

GROUPBYDEPT.DEPARTMENT_NAME;

--查詢每個部門的人數(shù)，顯示dcpartmcnt_id,dcpartmcnt_namc與人數(shù)

SELECTEMP.DEPARTMENT」D,DEPT.DEPARTMENT_NAME,COUNT(*)

FROMEMPLOYEESEMPJOINDEPARTMENTSDEPTON

EMP.DEPARTMENTJD=DEPT.DEPARTMENT_ID

GROUPBYEMP.DEPARTMENT_ID,DEPT.DEPARTMENT_NAME

ORDERBY1ASC;

--查詢所個部門中每個職位的人數(shù)，顯示depa「tment_id,jub_id,人數(shù)

SELECTDEPARTMENT」DJOB」D,COUNT(*)

FROMEMPLOYEES

GROUPBYDEPARTMENT—ID,JOB」D

ORDERBYIASC,2ASC;

-組函數(shù)與NULL值:所有組函數(shù)在進(jìn)行運(yùn)算時會自動忽略NULL值

SELECTAVG(NVL(COMMISSION_PCT.O))

FROMEMPLOYEES;

-HAVING子句：過濾分組結(jié)果，使用HAVING時必須使用GROUPBY.

SELECT5

FROM1

IWHEREJ2

(GROUPBY]3

[HAVING條件]4

IORDERBY]6

-WHERE子句中不能使用組函數(shù)作為過濾條件。

WHERE與HAVING的區(qū)別:

1.WHERE過濾的是表，HAVING過濾的是分組的結(jié)果

2.WHERE中不能使用組函數(shù)，HAVING能夠使用組函數(shù)

*/

--查詢平均工資大于10000的部門編號與平均工資，結(jié)果按工資降序排序

SELECTDEPARTMENT_ID,AVG(SALARY)

FROMEMPLOYEES

GROUPBYDEPARTMENT—ID

HAVINGAVG(SALARY)>l(X)()0

ORDERBYAVG(SALARY)DESC;

--當(dāng)使用GROUPBY與DISTINCT時ORDERBY中的列必須在SELECT子句中顯現(xiàn)。

--查詢最高工資大于6000的職位與最高工資，同時JOB」D中要包含REP

SELECTJOB」D,MAX(SALARY)

FROMEMPLOYEES

WHEREJOBJDLIKE'%REP%'

GROUPBYJOBJD

HAVINGMAX(SALARY)>=6000;

--查詢每年入職的人數(shù)，顯示年份與人數(shù)，結(jié)果按年份升序排序

SELECTTO_CHAR(HIRE_DATE,'YYYY'),COUNT(*)

FROMEMPLOYEES

GROUPBYTO_CHAR(HIRE_DATE.'YYYY')

ORDERBYTO.CHARCHIRE.DATE/YYYY')ASC;

一查詢?nèi)藬?shù)大于或等于3人的部門的dcpartment_id與人數(shù)

SELECTDEPARTMENTJD,COUNT(*)

FROMEMPLOYEES

GROUPBYDEPARTMENT_ID

HAVINGCOUNT(*)>=3;

一組函數(shù)嵌套：

--查詢所有部門的最高的平均工資

SELECTMAX(AVG(SALARY))

FROMEMPLOYEES

GROUPBYDEPARTMENT_ID;

1.組函數(shù)嵌套時必須使用GROUPBY子句

2.組函數(shù)嵌套時不要在SELECT子句中顯現(xiàn)非組函數(shù)的列

3.組函數(shù)嵌套只能顯現(xiàn)在SELECT子句中

*/

--子查詢（嵌套查詢）:

-查詢與144號職員在同一個部門工作的職員的last_name,deparlmenl_id

SELECTDEPARTMENTJD

FROMEMPLOYEES

WHEREEMPLOYEE」D=I44;

-一般子查詢：先執(zhí)行子查詢，再執(zhí)行主查詢

SELECTLAST_NAME,DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENT_ID=(SELECTDEPARTMENT_ID

FROMEMPLOYEES

WHEREEMPLOYEE」D=144);

-杳詢哪些職員的工資大于104號職員的工資，顯示這些職員的last_name,salary

SELECTLAST_NAME,SALARY

FROMEMPLOYEES

WHERESALARY>(SELECTSALARY

PROMEMPLOYEES

WHEREEMPLOYEE」D=104);

/*

1.從ORACLE8I以后，除了GROUPBY子句外其它子句都能夠使用子查詢

2.子查詢一定要顯現(xiàn)在一對小括號中

3.盡量將子查詢放在運(yùn)算符的右邊

4.除非取前N條數(shù)據(jù)，否則不要在子查詢中使用ORDERBY子句。

5.子查詢中返回列的個數(shù)與類型，必須與主查詢中條件的列的個數(shù)與類型一致

*/

-查詢工資大于公司平均工資的職員的last_name,d叩arlmenl_id,salary

SELECTLAST_NAME,DEPARTMENT」D,SALARY

FROMEMPLOYEES

WHERESALARY>(SELECTAVG(SALARY)

FROMEMPLOYEES);

一查詢出入職最早的職員的last_name,hire_date(格式為：1999?1?1)

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,,FMYYYY-MM-DD,)

FROMEMPLOYEES

WHEREHIRE.DATE=(SELECTMIN(HIRE_DATE)

FROMEMPLOYEES);

-查詢平均工資大于60號部門最高工資的部門的departmenjid,平均工資

SELECTDEPARTMENTJD,AVG(SALARY)

FROMEMPLOYEES

GROUPBYDEPARTMENT_ID

HAVINGAVG(SALARY)>(SELECTMAX(SALARY)

FROMEMPLOYEES

WHEREDEPARTMENT_ID=60);

--查詢?nèi)藬?shù)最多的部門的加partment_id與人數(shù)

SELECTDEPARTMENT」D,COUNT(*)

FROMEMPLOYEES

GROUPBYDEPARTMENT—ID

HAVINGCOUNT(*)=(SELECTMAX(COUNT(*))

FROMEMPLOYEES

GROUPBYDEPARTMENT");

--多行子查詢：IN,ANY,ALL

-查詢工資至少大于60號部門一名職員的工資的職員的last_name,salary

SELECTLAST.NAME,SALARY

FROMEMPLOYEES

WHERESALARYIN(SELECTSALARY

FROMEMPLOYEES

WHEREDEPARTMENT_ID=60);

SELECTLAST_NAME.SALARY

FROMEMPLOYEES

WHERESALARY>ANY(SELECTSALARY

FROMEMPLOYEES

WHEREDEPARTMENT_ID=60)；

SELECTLAST_NAME,SALARY

FROMEMPLOYEES

WHERESALARY>ALL(SELECTSALARY

FROMEMPLOYEES

WHEREDEPARTMENTJD=60);

-在SELECT子句中使用子查詢

SELECTLAST_NAME,(SELECTDEPARTMENT_NAMEFROMDEPARTMENTSWHERE

DEPARTMENT_ID=90)

FROMEMPLOYEES;

-查詢每個職位的人數(shù)以及那個職位人數(shù)占公司總?cè)藬?shù)的百分比，顯示job_id,人數(shù),百分比

SELECTJOB_ID,COUNT(*),TRUNC((COUNT(*)/(SELECTCOUNT(*)FROM

EMPLOYEES))*100)11'%'AS百分比

FROMEMPLOYEES

GROUPBYJOB」D;

--偽列:在建表時ORACLE為表自動添加的列。

--ROWID:數(shù)據(jù)在硬盤或內(nèi)存中的地址

-ROWNUM:行號,只能小于或小于等于或等于1,否則返回0行數(shù)據(jù)

SELECTROWNUM.LASTNAME.SALARY

FROMEMPLOYEES

WHEREROWNUM=IO

ORDERBY2DESC;

-查詢employees收入最低的前5名

SELECTLAST_NAME,SALARY

FROM(SELECTLAST_NAME,SALARY

FROMEMPLOYEES

ORDERBYSALARYASC)

WHERER0WNUM<=5;

-SQLServer

SELECTTOP5LAST_NAME,SALARY

FROMEMPLOYEES

ORDERBYSALARYDESC;

-MySQL

SELECTLAST_NAME.SALARY

FROMEMPLOYEES

ORDERBYSALARYDESC

LIMIT5;

--查詢?nèi)肼氉钔淼?名職員的last_name,hire_date(格式為:1999-10-10),salary(格式為：

50,000.00)

SELECTLAST_NAME.HIRE_DATE,SALARY

FROM(SELECTLAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD')AS

HIRE_DATE,TO_CHAR(SALARY,'FM99,999.00)ASSALARY

FROMEMPLOYEES

ORDERBYHIRE_DATEDESC)

WHEREROWNUM<=5;

--查詢與124號職員在同一年入職的職員的employee_id,las5ame,hire_date(格式為:

1999-10-10)

SELECTEMPLOYEE」D,LAST_NAME,TO_CHAR(HIRE_DATE；YYYY-MM-DD')

FROMEMPLOYEES

WHERETO_CHAR(HIRE_DATE,'YYYY)=(SELECTTO_CHAR(HIRE_DATE；YYYY')

FROMEMPLOYEES

WHEREEMPLOYEE_ID=&INPUT);

-高級子查詢：成對子查詢，相關(guān)子查詢

--查詢工資與所在部門平均工資相等的職員的last_namedepartment_id,sala「y

SELECTLAST_NAME,DEPARTMENT」D,SALARY

FROMEMPLOYEES

WHERE(DEPARTMENT」D.SALARY)IN(SELECTDEPARTMENT」D,AVG(SALARYj

FROMEMPLOYEES

GROUPBYDEPARTMENT_ID);

一查詢各個部門入職最早的職員的last_name,hire_date(格式為：1999-10-10).department_id

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD').DEPARTMENT_ID

FROMEMPLOYEES

WHERE(NVL(DEPARTMENTJD.O),HIRE_DATE)IN(SELECT

NVL(DEPARTMENT_ID,O),M1N(HIRE_DATE)

FROMEMPLOYEES

GROUPBYDEPARTMENT」D);

-相關(guān)子查詢：先執(zhí)行主查詢，再執(zhí)行子查詢

一查詢工資大于所在部門的平均工資的職員的lasl_name,department_id,salary

SELECTLAST_NAME,DEPARTMENT」D,SALARY

FROMEMPLOYEESEMP

WHERESALARY>(SELECTAVG(SALARY)

FROMEMPLOYEES

WHEREDEPARTMENT_ID=EMP.DEPARTMENT_ID);

SELECTEMP.LAST_NAME,EMP.DEPARTMENT_ID,EMP.SALARY

FROMEMPLOYEESEMP,

(SELECTDEPARTMENT_ID,AVG(SALARY)ASSALARY

FROMEMPLOYEES

GROUPBYDEPARTMENTSA

WHEREEMP.DEPARTMENT_ID=A.DEPARTMENTJDANDEMP.SALARY>A.SALARY;

--集合運(yùn)算：井集，交集，補(bǔ)集

-并集：集合+集合

-兗詢收入最高與收入最低的職員的last_name,salary

SELECTLAST_NAME,SALARY

FROMEMPLOYEES

WHERESALARY=(SELECTMAX(SALARY)

FROMEMPLOYEES)

UNION

SELECTLAST.NAME,SALARY

FROMEMPLOYEES

WHERESALARY=(SELECTMIN(SALARY)

FROMEMPLOYEES)

ORDERBY2ASC;

SELECTEMPLOYEE」D,LAST_NAME

FROMEMPLOYEES

UNION

SELECTDEPARTMENT」D,DEPARTMENT_NAME

FROMDEPARTMENTS;

SELECTEMPLOYEEJD.LAST_NAME

FROMEMPLOYEES

UNION-去掉重災(zāi)值

SELECTEMPLOYEE_ID,LAST_NAME

FROMEMPLOYEES;

SELECTEMPLOYEE_ID,LAST_NAME

FROMEMPLOYEES

UNIONALL-不去重復(fù)值

SELECTEMPLOYEE_ID,LAST_NAME

FROMEMPLOYEES;

一查詢50號部門同時工資大于3000的職員的lasl_name,salary,department_id

SELECTLAST_NAME,SALARY.DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENTJD=50

INTERSECT

SELECTLAST_NAME,SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHERESALARY>30(X);

SELECTLAST_NAME

FROMEMPLOYEES

WHERELAST_NAMELIKE'%a%'

INTERSECT

SELECTLAST.NAME

FROMEMPLOYEES

WHERELAST.NAMELIKE'%e%';

-補(bǔ)集:集合?集合

/*

A:12345

B:35

A-B=l24

B-A=NULL

*/

--查詢收入最高的6/0名職員的las5ame,salary

SELECTLAST_NAME,SALARY

FROM(SELECTLAST.NAME,SALARY

FROMEMPLOYEES

ORDERBYSALARYDESC)

WHEREROWNUM<=10

MINUS

SELECTLAST_NAME,SALARY

FROM（SELECTLAST.NAME,SALARY

FROMEMPLOYEES

ORDERBYSALARYDESC）

WHERER0WNUM<=5

ORDERBYSALARYDESC;

--創(chuàng)建表

/*

CREATETABLE表名（

列名數(shù)據(jù)類型［（長度）］［約束］,

列名數(shù)據(jù)類型［（長度）］［約束］,

列名數(shù)據(jù)類型［（長度）］［約束］,

列名數(shù)據(jù)類型［(長度)］［約束］

)

*/

一數(shù)字類型：NUMBER,長度能夠省略

/*

整型：NUMBER(6):整數(shù)，同時長度為6位的整數(shù)。NUMBER:省略長度，默認(rèn)為38位的整

數(shù)

浮點(diǎn)型：NUMBERS.2):整個數(shù)字最大的長度為8位，其中有兩位小數(shù)

*/

―字符類型：長度不能省略

/*

CHAR(10):AB，儲存固定長度的字符串。

VARCHAR2(10):AB,儲存可變長度的字符串。

VARCHAR與VARCHAR2的區(qū)別？

*/

--口期類型：不能寫長度

/*

DATE:口期+時刻

*/

DROPTABLET1;

CREATETABLET1(

T」DNUMBER(5),

T_NAMEVARCHAR2(20),

T_SEXCHAR(ICHAR),

TDATEDATE

);

--復(fù)制表:

/*

CREATETABLE表名

AS

SELECT語句

*/

DROPTABLETl;

CREATETABLET1

AS

SELECTLAST_NAME,SALARY,DEPARTMENT」D

FROMEMPLOYEES

WHEREDEPARTMENT」D=50;

一添加數(shù)據(jù)：INSERTINTO語句

1.向表中添加一行新的數(shù)據(jù)，并表新行中所有的列賦值

INSERTINTO表名VALUES(值，值，值,……);

INSERTINTOT1VALUES(1,'AA','男',TO_DATE('1999-10-10','YYYY-MM-DD'));

2.向表中添加一行新的數(shù)據(jù)，并向指定的列中賦值

INSERTINTO表名(列名，列名，列名,……)VALUES(值，值，值,……);

INSERTINTOT1(T_ID.T_NAME)VALUES(2：BB');

INSERTINTOT1(T_NAME,T_ID)VALUES('CC',3);

-修改數(shù)據(jù)：UPDATE語句

UPDATE表名SET列名=值,列名=值,...(WHERE條件］;

UPDATET1SETT_NAME='ABCWHERET」D=1;

UPDATET1SETT_SEX;女,T_DATE=SYSDATEWHERET_DATEISNULL;

-將employees收入最高的職員的工資改為公司的最低工資

UPDATEEMPLOYEESSETSALARY=(SELECTMIN(SALARY)FROMEMPLOYEES)

WHERESALARY=(SELECTMAX(SALARY)FROMEMPLOYEES);

-刪除數(shù)據(jù)：DELETE語句

DELETE|FROM］表名［WHERE條件］;

DELETET1WHERET_SEX二男；

-刪除T1表中T_NAME列中所有的數(shù)據(jù)

UPDATET1SETT_NAME=NULL;

一約束：

L.主鍵約束：PRIMARYKEY:不能為