Oracle數(shù)據(jù)庫(kù)筆記1

1、java企業(yè)級(jí)項(xiàng)目開(kāi)發(fā)班oracle 10g課堂筆記2015年10月30日目 錄第一章：oracle 10g介紹一、oracle 10g安裝二、oracle 10g 設(shè)置命令開(kāi)始運(yùn)行 輸入 sqlplus打開(kāi)oracel操作客戶端登錄用戶： scott 密碼:tiger在輸入用戶名： 用戶名/密碼注銷(xiāo)登錄： quit 可以退出當(dāng)前登錄狀態(tài)從當(dāng)前賬戶切換到另一個(gè)指定賬戶上connect 用戶名/密碼對(duì)一個(gè)賬戶進(jìn)行鎖定和解鎖操作第一步： 以管理員的身份登錄，然后去解鎖普通鎖定的用戶說(shuō)明需要登錄的用戶被鎖定，必須通過(guò)解鎖后才能登錄解鎖命令： alter user 普通賬戶名 account unlo

2、ck|lock;unlock ：解鎖 lock：鎖定注意：必須在管理員身份登錄后才能進(jìn)行解鎖或鎖定操作清理屏幕host cls 將當(dāng)前屏幕清理對(duì)命令窗口進(jìn)行屬性的修改：為了方便顯示數(shù)據(jù)將命令窗口的寬： 300 個(gè)字符， 行：9999設(shè)置查詢(xún)數(shù)據(jù)顯示的一行字符數(shù)：set linesize 300;注意：每一次進(jìn)入客戶端進(jìn)行數(shù)據(jù)顯示的時(shí)候，都必須重新設(shè)定設(shè)置一頁(yè)顯示行數(shù)set pagesize 30; 注意：每一次進(jìn)入客戶端進(jìn)行數(shù)據(jù)顯示的時(shí)候，都必須重新設(shè)定若system用戶被鎖定，怎么樣來(lái)進(jìn)行解鎖鎖定systemSystem/managerAlter user system account lo

3、ck；解鎖systemSystem/manager as sysdbaAlter user system account unlock;登錄 sys賬戶Sys/admin as sysoperSql語(yǔ)言： sql89、sql92、sql99結(jié)構(gòu)化的查詢(xún)語(yǔ)言：定義功能（DDL）: create drop alter 主要對(duì)于結(jié)構(gòu)進(jìn)行修改操作功能（DML）:insert update delete 主要對(duì)記錄進(jìn)行修改查詢(xún)功能（DQL）:select 查詢(xún)控制功能（DCL）:第二章：SQL查詢(xún)語(yǔ)言的語(yǔ)法Scott用戶下表結(jié)構(gòu)：雇員表字段名類(lèi)型字段中文說(shuō)明EmpnoNumber(4) 主鍵雇員編號(hào)En

4、ameVarchar2(10) 雇員姓名JobValurchar2(9)職稱(chēng)MgrNumber(4)領(lǐng)導(dǎo)編號(hào)Hiredatedate入職日期SalNumber(7,2)工資CommNumber(7,2)獎(jiǎng)金DeptnoNumber(2)部門(mén)編號(hào)部門(mén)表字段名類(lèi)型字段中文說(shuō)明DeptnoNumber(2)部門(mén)編號(hào)DnameVarchar2(14)部門(mén)名稱(chēng)LocVarchar2(13)地址工資等級(jí)表字段名類(lèi)型字段中文說(shuō)明GradeNumber工資等級(jí)LosalNumber最低工資HisalNumber最高工資SQL查詢(xún)語(yǔ)言：一、 簡(jiǎn)單查詢(xún)SELECT ALL|DISTINCT<字段名列表>

5、;|常量|表達(dá)式 Select <所求內(nèi)容> ：查詢(xún)結(jié)果中的列Select <所求內(nèi)容> from <來(lái)自的表名列表>查詢(xún)出雇員表的所有信息Select * from emp;* :代表指定表的所有字段名列表select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;Select后面：可以跟指定的字段名列表，每一個(gè)字段之間用逗號(hào)隔開(kāi)查詢(xún)出雇員姓名和工資信息（投影）select ename as 雇員姓名,sal 工資 from emp;Select后面查詢(xún)某一個(gè)字段，給結(jié)果字段重新取名（別名）A)

6、 字段名 as 別名B) 字段名 空格 別名可以一個(gè)表取別名Select a.empno,a.ename from emp a;通常給表名取別名： 表名 空格 別名 給字段取別名 ，字段名 as 別名Ed命令的使用（可以將sql語(yǔ)句寫(xiě)在指定的文本文件中，然后引入執(zhí)行）Ed 回車(chē)會(huì)自動(dòng)修改上一條執(zhí)行過(guò)sql語(yǔ)句，并且打開(kāi)文件后可以對(duì)語(yǔ)句進(jìn)行修改，修改后保存按/回車(chē)就可以執(zhí)行修改好的命令Ed 文件名 回車(chē)文件名：是一指定的文件，若文件存在，直接打開(kāi)，若不存在會(huì)提示創(chuàng)建該文件可以引入外部寫(xiě)好sql語(yǔ)句文件直接執(zhí)行 文件名 回車(chē)查詢(xún)出雇員表的職稱(chēng)，重復(fù)出現(xiàn)只顯示一次Select distinct jo

7、b from emp;Select all|distinct ：all:默認(rèn)情況，將重復(fù)記錄顯示出來(lái) Distinct ：去掉查詢(xún)結(jié)果中的重復(fù)記錄重復(fù)記錄：水平上一橫數(shù)據(jù)都一模一樣將雇員表按下列格式顯示出來(lái)雇員編號(hào)為：7369 雇員姓名：jack 職稱(chēng)是：manager 入職日期：select 'empno: '|empno|' ename:'|ename|' job: '|job|' mgr: '|mgr |' sal: '|sal from emp;Select 后面可以出現(xiàn)常量,和其他不連接用 “|”該常量所

8、在位置，是結(jié)果中一個(gè)列，并且其值是其本身查詢(xún)出雇員的年薪select ename as 雇員姓名,sal*12 as 年薪 from empSelect后面可能出現(xiàn)表達(dá)式，查詢(xún)不會(huì)改變?cè)碇械闹担艚Y(jié)果中的值需要改變時(shí)，必須在select后面跟表達(dá)式二、限定查詢(xún)格式： select <所求內(nèi)容> from <表名列表>where <篩選條件>所求內(nèi)容： distinct <字段名列表><常量><表達(dá)式>查詢(xún)出雇員工資大于或等于3000的所有雇員信息思路：篩選條件：sal >= 3000所求內(nèi)容：empno,ename

9、,job,mgr,hiredate,sal,comm,deptno所用的表：empselect empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal >= 3000;書(shū)寫(xiě)條件：字段名 運(yùn)算符 具體的值查詢(xún)出JONES員工的信息思路：篩選條件：ename = JONES所求內(nèi)容： *所用的表： empSelect * from emp where ename = JONES注意問(wèn)題：1. 查詢(xún)的值是字符型，寫(xiě)值的時(shí)候必須使用定界符單引號(hào)內(nèi)2. 查詢(xún)值必須分大小寫(xiě)，sql語(yǔ)句書(shū)寫(xiě)的其他地方部分大小寫(xiě)查詢(xún)出工資大于等于1

10、000并小于等于2500之間的所有員工信息思路：篩選條件：sal >= 1000 and sal <=2500所求內(nèi)容：*所用的表：empselect * from emp where sal>=1000 and sal <=2500與運(yùn)算：必須兩個(gè)條件都要同時(shí)滿足的記錄才會(huì)顯示在結(jié)果中條件1 and 條件2 and 條件3查詢(xún)出工資在3000以上，或職稱(chēng)為MANAGER的所有職工篩序條件：sal >3000 or job = MANAGER所求內(nèi)容：*所用的表：empselect * from emp where sal > 3000 or job = &

11、#39;MANAGER'或運(yùn)算：表示只要滿足其中一個(gè)條件即可條件1 or 條件2 or條件3查詢(xún)出職稱(chēng)為MANAGER或SALESMAN，并且工資在2000到3000之間（含）的雇員信息。思路：篩選條件：(job=MANAGER OR JOB=SALESMAN) AND (SAL>=2000 AND SAL<=3000)所求內(nèi)容：*所用的表：empselect * from emp where (job='MANAGER' OR JOB='SALESMAN') AND (SAL>=800 AND SAL<=3000);注意：運(yùn)算過(guò)

12、程中and優(yōu)先級(jí)別高于or的，注意加括號(hào)來(lái)改變優(yōu)先級(jí)別設(shè)置日期顯示的格式：alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'查詢(xún)出獎(jiǎng)金為空的所有員工信息篩選條件: comm is null Select * from emp where comm is null;查詢(xún)獎(jiǎng)金不為空的雇員信息Select * from emp where comm is not null;查詢(xún)出職稱(chēng)為 MANAGER,SALESMAN,CLERK的雇員信息做法1： Select * from emp where job='MAN

13、AGER' OR JOB='SALESMAN' OR JOB='CLERK'做法2：Select * from emp where job in('MANAGER', 'SALESMAN', 'CLERK');字段名 in|not in (值的列表)In： 屬于Not in：不屬于查詢(xún)出以雇員姓名中包含有N字符的所有雇員信息select * from emp where ename like '%N%'模糊查詢(xún)條件： 字段名 like 帶有通配符的字段值通配符： %：可以和任意多個(gè)字符相匹配

14、（匹配即相等） _:可以和任意一個(gè)字符相匹配查詢(xún)出雇員姓名中第二個(gè)字符為I的所有雇員信息select * from emp where ename like '_I%'查詢(xún)出工資在1000到3000之間的職工 姓名和職稱(chēng)思路：篩選條件： sal >=1000 and sal <=3000所求內(nèi)容： ename，job所用的表： empSelect ename，job from emp where sal >=1000 and sal <=3000查詢(xún)出工資在1000到2000之間（含）的所有雇員信息字段名 between <下限值> and

15、<上限值> ：寫(xiě)在where后面，表示指定字段的值在上下限值之間并且包含上下限值。select * from emp where sal>=1000 and sal<=2000;select * from emp where sal between 1000 and 2000;Oracle數(shù)據(jù)庫(kù)中的單行函數(shù)單行函數(shù)：只對(duì)一個(gè)值進(jìn)行操作單行函數(shù)的語(yǔ)法單行函數(shù)名(字段名|表達(dá)式,參數(shù)1,參數(shù)2,)參數(shù)說(shuō)明:Fuction_name 函數(shù)名稱(chēng)column 列名expression 表達(dá)式arg1,arg2, 參數(shù)單行函數(shù)分類(lèi):字符函數(shù)：接收字符輸入并返回字符或數(shù)值數(shù)值函數(shù)：接

16、受數(shù)據(jù)輸入并返回?cái)?shù)值日期函數(shù)：對(duì)日期型數(shù)據(jù)進(jìn)行操作轉(zhuǎn)換函數(shù)：從一種數(shù)據(jù)類(lèi)型轉(zhuǎn)換為另外一種數(shù)據(jù)類(lèi)型通用函數(shù)：NVA 函數(shù) DECODE 函數(shù)字符函數(shù)：接收字符輸入并返回字符或數(shù)值1、lower(字符表達(dá)式)函數(shù)原型： lower(字符表達(dá)式) 返回：字符串功能：將指定字符串轉(zhuǎn)化為小寫(xiě) 注意：偽表 dual例題1：select lower('HELLO WORLD!') from dual;例題2：查詢(xún)出job中含有G字母的雇員姓名,職稱(chēng)（g字母大小寫(xiě)不區(qū)分）select ename as 雇員姓名,job as 職稱(chēng) from emp where lower(job) like

17、'%g%'2、upper(字符表達(dá)式) 函數(shù)原型： upper(字符表達(dá)式) 返回值類(lèi)型：字符型功能：將小字母轉(zhuǎn)換為大寫(xiě)字母select upper('hello world!') as result from dual;3、initcap(字符表達(dá)式) 函數(shù)原型：initcap(字符表達(dá)式) 返回值類(lèi)型： 字符型功能：將制定字符串的首字母轉(zhuǎn)換為大寫(xiě)字母，其余字符小寫(xiě)select initcap('hello WORLD!') as result from dual;4、concat(字符串表達(dá)式1,字符串表達(dá)式2)函數(shù)原型： concat(字符

18、串表達(dá)式1,字符串表達(dá)式2) 返回值類(lèi)型： 字符串功能： 將指定兩個(gè)字符串進(jìn)行收尾連接，最終成為一個(gè)字符串和 <字符表達(dá)式1 >| <字符表達(dá)式2> 等價(jià)，字符常量必須寫(xiě)在一對(duì)單引號(hào)之間select concat('hello','world') as result from dual;select 'hello'|'world' as result from dual;select concat(ename,job) as result from emp;5、substr(字符表達(dá)式,起始位置,長(zhǎng)度)函數(shù)

19、原型：substr(字符表達(dá)式,起始位置,長(zhǎng)度)功能：從指定的字符表達(dá)式中取出從起始位置到規(guī)定長(zhǎng)度的子串注意：1） 改函數(shù)若省略,長(zhǎng)度，從起始位置一直取到字符串的尾部2） 起始位置：可以為正式時(shí)，從左邊開(kāi)始計(jì)數(shù)，若為負(fù)數(shù)時(shí)，從右邊開(kāi)始計(jì)數(shù)若起始位置的值為0或1的時(shí)候，從左邊第一個(gè)開(kāi)始取子串（包含起始位置）3）column <字段名> format a<列寬> 功能：設(shè)置指定字段的顯示列寬，一遍將列名顯示完全6、length(字符表達(dá)式)函數(shù)原型：length(字符表達(dá)式) 返回值類(lèi)型： 數(shù)值型功能：返回指定字符串的長(zhǎng)度注意：漢字并不是兩個(gè)寬度查詢(xún)出雇員名字有6個(gè)字符組成

20、的所有雇員信息select * from emp where length(ename) = 6;7、replace(字符表達(dá)式1,字符表達(dá)式2,字符表達(dá)式3)函數(shù)原型： replace(字符表達(dá)式1,字符表達(dá)式2,字符表達(dá)式3) 返回值類(lèi)型：字符型功能：在字符表達(dá)式1中尋找到字符表達(dá)式2的內(nèi)容時(shí)用字符表達(dá)式3來(lái)替換注意：1) 該函數(shù)區(qū)分大小寫(xiě)2) 將字符表達(dá)式3用空串來(lái)代替時(shí)，可以刪除字符表達(dá)式1中的指定字符表達(dá)式28、chr(整數(shù))函數(shù)原型：chr(整數(shù)) 返回值類(lèi)型：字符型 功能：將指定整數(shù)作為ASCII碼返回對(duì)應(yīng)的一個(gè)字符select chr(65) as result from du

21、al;9、ascii(字符)函數(shù)原型：ascii(字符) 返回值類(lèi)型：數(shù)值型 功能：將指定字符的ASCII碼返回select ascii('A') from dual;數(shù)值型函數(shù)函數(shù)說(shuō)明ABS（value）返回指定數(shù)值的絕對(duì)值CEIL（value）返回大于或等于Value的最小整數(shù)值FLOOR（value）返回小于或等于value的最大整數(shù)值COS（value）求余弦值COSH（value）求反余弦值EXP（value）返回以e為底的指數(shù)值LN（value）返回以value為底的自然對(duì)數(shù)LOG（x,y）返回以x為底y的對(duì)數(shù)POWER（value,exponent）求value的

22、exponent的指數(shù)值ROUND（value,precision）按value的precision精度四舍五入MOD（value,divisor）取余SQRT（value）返回value的平方根trunc(value)取整數(shù)部分，不會(huì)進(jìn)行四舍五入操作1、abs(數(shù)值)函數(shù)原型Abs(數(shù)值表達(dá)式)功能返回?cái)?shù)值表達(dá)式的絕對(duì)值例題select abs(-100) as result from dual;2、ceil(數(shù)值表達(dá)式)函數(shù)原型ceil(數(shù)值表達(dá)式)功能大于或等于數(shù)值表達(dá)式的最小整數(shù)例子3、Floot(數(shù)值表達(dá)式)函數(shù)原型Floot(數(shù)值表達(dá)式)功能小于或等于數(shù)值表達(dá)式的最大整數(shù)例題pow

23、er(數(shù)值表達(dá)式1，數(shù)值表達(dá)式2)函數(shù)原型power(數(shù)值表達(dá)式1，數(shù)值表達(dá)式2)功能求數(shù)值表達(dá)式1的數(shù)值表達(dá)式2次方例題Round(數(shù)值表達(dá)式,整數(shù)表達(dá)式)函數(shù)原型Round(數(shù)值表達(dá)式,整數(shù)表達(dá)式)功能將數(shù)值表達(dá)式1，保留整數(shù)個(gè)小數(shù)后得到的結(jié)果整數(shù)表達(dá)式2：個(gè)位為0，往整數(shù)部分走是負(fù)數(shù)，往小數(shù)部分走是正數(shù)表示例子 Mod(value,divisor)函數(shù)原型Mod(被除數(shù),除數(shù))功能返回兩個(gè)數(shù)的余數(shù)例題SQRT（value）函數(shù)原型SQRT（數(shù)值表達(dá)式）功能求出數(shù)值表達(dá)式的算術(shù)平方根例題日期函數(shù)函數(shù)說(shuō)明ADD_MONTHS（date,count）指定日期 date上增加count個(gè)月LAS

24、T_DAY(date)返回日期date所在月的最后一天MONTHS_BETWEEN(date1,date2)返回date1到date2之間相隔的月數(shù)NEW_TIME(date,this,orther)指date由this時(shí)區(qū)轉(zhuǎn)為other時(shí)區(qū)NEXT_DAY(date,day)返回指定日期或最后一個(gè)星期幾的日期.day為星期幾SYSDATE獲取當(dāng)前的系統(tǒng)日期CURRENT_TIMESTAMP()獲取當(dāng)前的系統(tǒng)日期及時(shí)間add_months(日期表達(dá)式1,數(shù)值表達(dá)式)函數(shù)原型1） add_months(日期表達(dá)式1,數(shù)值表達(dá)式)功能在日期表達(dá)式的基礎(chǔ)上加上數(shù)值表達(dá)式個(gè)月份后的那個(gè)日期返回值類(lèi)型日

25、期型注意Select sysdate from dual； 返回當(dāng)前系統(tǒng)的日期例題今天日期基礎(chǔ)上加1個(gè)月后是那個(gè)日期2、Last_day(日期表達(dá)式)函數(shù)原型Last_day(日期表達(dá)式)功能返回日期表達(dá)式當(dāng)前月份最后一天的日期返回值日期型 Months_between(日期表達(dá)式1，日期表達(dá)式2)函數(shù)原型Months_between(日期表達(dá)式1，日期表達(dá)式2)功能：求出日期表達(dá)式1和日期表達(dá)式2之間所相差的月數(shù)返回值類(lèi)型數(shù)值型例題NEXT_DAY(日期表達(dá)式,指定星期幾)函數(shù)原型NEXT_DAY(日期表達(dá)式,指定星期幾)功能指定日期表達(dá)式下一個(gè)星期的指定星期幾的日期 返回值類(lèi)型是，日期例題

26、類(lèi)型轉(zhuǎn)換函數(shù)To_char()將數(shù)字或日期轉(zhuǎn)化為字符串函數(shù)原型To_char(日期表達(dá)式|數(shù)值表達(dá)式,格式)功能將數(shù)字或日期轉(zhuǎn)化為字符串當(dāng)參數(shù)為日期時(shí)to_char()在使用的時(shí)候要用到格式控制的符,格式控制符不區(qū)分大小寫(xiě)字母 年:Y,年份為四位數(shù)，故應(yīng)寫(xiě)成:YYYY或yyyy 月:M,月份為兩位數(shù),故應(yīng)寫(xiě)成:MM或mm 日:D，日為兩位數(shù)，故應(yīng)寫(xiě)成:DD或dd格式前加fm表示去掉前導(dǎo)0日期當(dāng)格式用yyyy單獨(dú)可以將指定日期的年份取出mm取出月份dd 取出日部分當(dāng)參數(shù)為數(shù)值型時(shí)1） 格式可以用9或0來(lái)作為格式的限定2） 若數(shù)值表示的貨幣，可以中格式前加$符號(hào)，顯示值時(shí)，顯示前導(dǎo)$3） 若要顯示

27、￥貨幣符，應(yīng)用本地貨幣符，中前面加前導(dǎo)L4） 可以用 000,000,000來(lái)添加數(shù)值的千位符顯示返回值類(lèi)型字符串類(lèi)型to_char()在使用的時(shí)候要用到格式控制的符,格式控制符不區(qū)分大小寫(xiě)字母 年:Y,年份為四位數(shù)，故應(yīng)寫(xiě)成:YYYY或yyyy 月:M,月份為兩位數(shù),故應(yīng)寫(xiě)成:MM或mm 日:D，日為兩位數(shù)，故應(yīng)寫(xiě)成:DD或ddTo_number()將當(dāng)前的固定格式的字符串轉(zhuǎn)化為數(shù)字能夠轉(zhuǎn)換的字符（09的數(shù)字字符 正負(fù)號(hào)，小數(shù)點(diǎn)，e字母看情況）To_date()將當(dāng)前固定格式的字符串轉(zhuǎn)化為日期To_date(字符表達(dá)式,格式)查詢(xún)結(jié)果的排序：u ORDER BY 排序字段名1 DESC|AS

28、C,排序字段名2 DESC|ASCu 排序?qū)ψ詈蟮慕Y(jié)果進(jìn)行排序，ORDER BY 一般寫(xiě)中SQL語(yǔ)句的最后一個(gè)部分u 若排序字段有多個(gè)時(shí)，從左到右依次，若第一個(gè)排序字段有相同值時(shí)，然后按第二個(gè)排序字段區(qū)進(jìn)行排序。u 默認(rèn)是升序排序（ASC，也可以不寫(xiě)ASC）按工資的降序顯示所有雇員信息select * from emp order by sal desc;先按部門(mén)的升序，部門(mén)相同的按工資的降序排序，若工資相同時(shí)再按入職時(shí)間升序顯示出雇員信息select * from emp order by deptno asc,sal desc,hiredate asc;SQL強(qiáng)化訓(xùn)練選擇部門(mén)30中的所有職

29、工篩選條件：deptno = 30所求內(nèi)容：emp.*來(lái)自的表：empSelect 所求內(nèi)容 from 來(lái)自的表 where 篩選條件select emp.* from emp where deptno = 30;列出所有業(yè)務(wù)員(CLERK)的姓名，編號(hào)，和部門(mén)編號(hào)篩選條件： job = CLERK所求內(nèi)容：ename,empno,deptno所用的表：empselect ename,empno,deptno from emp where job='CLERK'找出獎(jiǎng)金高于薪金的員工篩選條件：comm > sal所求內(nèi)容：emp.*來(lái)自的表：empSelect emp.*

30、 from emp where comm > sal;找出獎(jiǎng)金高于薪金的60%的員工篩選條件：comm > sal*0.6所求內(nèi)容：*來(lái)自的表：empSelect * from emp where comm > sal*0.6;找出部門(mén)10中所有經(jīng)理(MANAGER)和部門(mén)20中所有業(yè)務(wù)員(CLERK)的詳細(xì)資料篩選條件：(deptno = 10 and job= MANAGER) or (deptno = 20 and job = CLERK )所求內(nèi)容： *來(lái)自的表: empselect * from emp where (deptno=10 and job='M

31、ANAGER') or (deptno = 20 and job='CLERK');找出部門(mén)10中所有經(jīng)理(MANAGER),部門(mén)20中所有業(yè)務(wù)員(CLERK),既不是經(jīng)理又不是業(yè)務(wù)員但其薪水大于等于2000的所有員工的詳細(xì)資料篩選條件：(deptno =10 and job =MANAGER) OR (deptno =20 and job=CLERK) OR (job!=MANAGER AND JOB !=CLERK AND SAL >=2000)所求內(nèi)容: *來(lái)自的表： empSELECT * FROM EMP WHERE (DEPTNO=10 AND JOB

32、='MANAGER') OR (DEPTNO = 20 AND JOB='CLERK') OR (JOB != 'MANAGER' AND JOB != 'CLERK' AND SAL >= 2000) ;找出收取傭金的員工的不同工作篩選條件： SAL IS NOT NULL所求內(nèi)容： JOB所用的表： empSELECT DISTINCT JOB FROM EMP WHERE SAL IS NOT NULL;找出不收取傭金或收取的傭金低于1000的員工篩選條件： sal is null or sal <1000所求內(nèi)

33、容： *所用的表： empselect * from emp where sal is null or sal<1000;找出各月倒數(shù)第3天受雇的所有員工篩選條件： hiredate = LAST_DAY(HIREDATE)-2 所求內(nèi)容: *來(lái)自的表：empselect * from emp where hiredate = last_day(hiredate)-2;找出早于12年前受雇的員工篩選條件：trunc(months_between(sysdate,hiredate)/12) > 12select * from emp where trunc(months_betwee

34、n(sysdate,hiredate)/12) > 12;以首字母大寫(xiě)的方式顯示所有員工的姓名select initcap(ename) as ename from emp;顯示正好為5個(gè)字符的員工姓名select ename from emp where length(ename) = 5;顯示不帶有”R”的員工姓名select * from emp where NOT(ename like '%R%');select * from emp where ename not like '%R%'顯示所有員工姓名的前三個(gè)字符select substr(ena

35、me,1,3) as ename from emp;顯示所有員工的姓名，并用a替換所有Aselect replace(ename,'A','a') as ename from emp;顯示滿10年服務(wù)年限的員工姓名和受雇日期select ename,hiredate from emp where trunc(months_between(sysdate,hiredate)/12)>=10;顯示員工的詳細(xì)資料，按姓名由大到小排序select * from emp order by ename desc;顯示員工的姓名和受雇日期，根據(jù)其服務(wù)年限，將最老的員工排

36、在最前面select ename,hiredate from emp order by hiredate asc;select * from emp order by trunc(months_between(sysdate,hiredate)/12) desc;顯示所有員工的姓名，工作和薪金，按工作降序排列，若工作相同則按薪金升序排序select ename,job,sal from emp order by job desc,sal asc;顯示所有員工的姓名，加入公司的年份和月份，按受雇日期所在的月排序，若月份相同，則將最早年份排在最前面select ename,to_char(hire

37、date,'yyyy') as year,trunc(months_between(sysdate,hiredate) as month from emp order by to_char(hiredate,'fmmm'),year asc;select ename,to_char(hiredate,'yyyy') as year,trunc(months_between(sysdate,hiredate) as month from emp order by to_char(hiredate,'fmmm'),2 asc;顯示一個(gè)

38、月為30天的情況所員工的日薪金，忽略余數(shù)select trunc(sal/30) as 日薪金 from emp;找出在(任何年份)2月受聘的所有員工selct * from emp where to_char(hiredate,'fmmm') = '2'對(duì)每個(gè)員工，顯示其加入公司的天數(shù)select trunc(sysdate-hiredate) as 入職天數(shù) from emp;顯示姓名中任意位置包含“A”的所有員工姓名select ename from emp where ename like '%A%'以年月日方式顯示所有員工的服務(wù)年限se

39、lect trunc(months_between(sysdate,hiredate)/12) as year,trunc(mod(months_between(sysdate,hiredate),12) month, trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate) as dayfrom emp;三、 多表查詢(xún)查詢(xún)格式SELECT <所求內(nèi)容> FROM <表1>,<表2> WHERE <連接條件> AND <篩選條件>用到多張表時(shí)，需要重表之間的連

40、接 連接（內(nèi)連接（等值連接和自然連接）和外連接（左連接，右連接，全連接）連接：將多張有聯(lián)系的表組合稱(chēng)為一張例題1：查詢(xún)出查詢(xún)每一個(gè)雇員的雇員信息和工資等級(jí)信息所求內(nèi)容：emp.*,grade所用的表：emp,salgrade連接條件： sal>=losal and sal <= hisalSelect emp.* ,grade from emp,salgrade where sal>=losal and sal <= hisal;例題2：查詢(xún)出雇員姓名以及雇員所做的部門(mén)名篩選條件：所求內(nèi)容: ename,dname所用的表：emp,dept連接條件：emp.deptno

41、 = dept.deptnoselect a.ename,b.dname from emp a,dept b where a.deptno = b.deptno;例題3：查詢(xún)出雇員姓名，所做部門(mén)名，工資等級(jí)篩選條件：所求內(nèi)容：ename,dname,grade所用的表：emp,dept,salgrade連接條件：emp和dept ： emp.deptno = dept.deptno Emp 和grade： sal>=losal and sal <= hisalselect ename,dname,grade from emp,dept,salgrade where (emp.dep

42、tno = dept.deptno) and (sal>=losal and sal<=hisal);例題4：查詢(xún)出工資等級(jí)為5的所有職工所中的部門(mén)信息篩選條件：grade = 5所求內(nèi)容：dept.*所用的表：1）從篩選條件來(lái)自的表 salgrade2） 從所求內(nèi)容中來(lái)自動(dòng)表 dept3） 兩張表若需要連接，就必須有一張中間表emp才能將上面兩張表連接起來(lái)，中間表也要包括進(jìn)來(lái)select a.* from dept a,emp b,salgrade c where a.deptno = b.deptno and (b.sal>=c.losal and b.sal <=

43、 c.hisal) and grade = 5;例題5：查詢(xún)出地址中NEW YORK工作的雇員信息例題6:查詢(xún)出地址中NEW YORK工作的雇員姓名和工資等級(jí)四、左、右連接查詢(xún)左連接查詢(xún)：以左邊表為標(biāo)準(zhǔn)查詢(xún)內(nèi)容，左邊表的所有記錄都會(huì)出現(xiàn)的查詢(xún)結(jié)果中，若中右邊有對(duì)應(yīng)記錄則對(duì)應(yīng)出現(xiàn)，沒(méi)有對(duì)應(yīng)右邊表字段值出用null來(lái)填充補(bǔ)齊。改變：左邊表名.公共字段名 = 右邊表名.公共字段名(+)例題：將dept、emp兩張表進(jìn)行左連接查詢(xún)select * from dept,emp where dept.deptno = emp.deptno(+);右連接：以右邊表為標(biāo)準(zhǔn)，去連接左邊表，右邊表的所有記錄都會(huì)出

44、現(xiàn)在查詢(xún)結(jié)果中，有對(duì)應(yīng)則對(duì)應(yīng)，沒(méi)有對(duì)應(yīng)的左邊表字段值用null值填充。連接條件：左邊表名.公共字段名(+) = 右邊表名.公共字段名例題：將dept、emp兩張表進(jìn)行右連接查詢(xún)select * from dept,emp where dept.deptno(+) = emp.deptno;SQL：1999SQL定義 (了解)SELECT table1.column,table2.columnFROM table1 CROSS JOIN table2|NATURAL JOIN table2|JOIN table2 USING(column_name)|JOIN table2 ON (table1

45、.column_name=table2.column_name)|LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name=table2.column_name)1、交叉連接(CROSS JOIN):迪卡爾積運(yùn)算92標(biāo)準(zhǔn)：select * from emp,dept;99標(biāo)準(zhǔn)：select * from emp cross join dept;2、table1 INNER JOIN table2 ON (table1.column_name=table2.column_name)、內(nèi)連接（實(shí)質(zhì)就是等值連接）例題：Emp表和dept表進(jìn)行內(nèi)

46、連接（列相加，記錄相對(duì)應(yīng)）92標(biāo)準(zhǔn)： select * from emp,dept where emp.deptno = dept.deptno;格式： select <所求內(nèi)容> from 表1,表2 where 表1.公共字段名 = 表2.公共字段名99標(biāo)準(zhǔn)： select * from emp inner join dept on emp.deptno = dept.deptno;格式：select <所求內(nèi)容> from 表1 INNER JOIN表2 ON 表1.公共字段名 = 表2.公共字段名select * from emp join dept on em

47、p.deptno = dept.deptno;格式：select <所求內(nèi)容> from 表1 JOIN表2 ON 表1.公共字段名 = 表2.公共字段名99標(biāo)準(zhǔn)：select * from emp join dept using(deptno);格式： select <所求內(nèi)容> from 表1 Join 表2 USING(公共字段名)用USING(公共字段名) 來(lái)代替連接條件3、自然連接 (NATURAL JOIN)：是等值連接基礎(chǔ)上去掉一個(gè)重復(fù)的列所得到結(jié)果就是自然連接得到結(jié)果例題：寫(xiě)emp和dept兩張進(jìn)行自然連接92標(biāo)準(zhǔn)：select emp.*,dname,

48、loc from emp,dept where emp.deptno = dept.deptno;99標(biāo)準(zhǔn)：select * from emp natural join dept;4、外連接（左連接，右連接，全連接）Table 1 LEFT|RIGHT|FULL OUTER JOIN table2 左連接/右連接/全連接1、左連接92標(biāo)準(zhǔn)：select * from emp,dept where emp.deptno = dept.deptno(+);格式： select <所求內(nèi)容> from 表1，表2 where 表1.公共字段名=表2.公共字段名(+)99標(biāo)準(zhǔn)：select

49、 * from emp left join dept on emp.deptno = dept.deptno;select <所求內(nèi)容> from 表1 left outer join表2 on表1.公共字段名=表2.公共字段名2、右連接92標(biāo)準(zhǔn)：select * from emp,dept where emp.deptno (+)= dept.deptno;格式： select <所求內(nèi)容> from 表1，表2 where 表1.公共字段名(+)=表2.公共字段名99標(biāo)準(zhǔn)：select * from emp right join dept on emp.deptno

50、 = dept.deptno;select <所求內(nèi)容> from 表1 right outer join表2 on表1.公共字段名=表2.公共字段名3、全連接92標(biāo)準(zhǔn)：沒(méi)有表達(dá)方式99標(biāo)準(zhǔn)：select * from emp full join dept on emp.deptno = dept.deptno;select <所求內(nèi)容> from 表1 full outer join表2 on表1.公共字段名=表2.公共字段名四、嵌套查詢(xún)將某一個(gè)查詢(xún)的結(jié)果作為另一查詢(xún)的條件值，查詢(xún)成為嵌套查詢(xún)Select <所求內(nèi)容> from <所求內(nèi)容來(lái)自的表&

51、gt; where 公共字段名 IN|NOT IN (SELECT 公共字段名 from 篩選條件所涉及的表 where 篩選條件 and 其他條件 ) and 其他條件若子查詢(xún)前面：> < >= <= = 時(shí)，子查詢(xún)的結(jié)果只能有一個(gè)容易子查詢(xún)前面： in | not in，子查詢(xún)的結(jié)果又多個(gè)1）例題查詢(xún)出地址在NEW YORK的雇員編號(hào)，雇員姓名 A)先將地址在NEW YORK部門(mén)編號(hào)是多少select deptno from dept where loc = 'NEW YORK' B)在通過(guò)上面的部門(mén)編號(hào)求出所有該部門(mén)的雇員信息select * fr

52、om emp where deptno = 10;select * from emp where deptno = (select deptno from dept where loc = 'NEW YORK');2)查詢(xún)出工資雇員編號(hào)為7788雇員的工資高于的所有雇員信息select * from emp where sal >= (select sal from emp where empno=7788);3）查詢(xún)出和雇員編號(hào)為7788或 7839雇員在同一個(gè)部門(mén)的雇員信息select * from emp where deptno in (select deptno

53、 from emp where empno=7788 or empno = 7839);4） 查詢(xún)出不和雇員編號(hào)為7788或 7839雇員在同一個(gè)部門(mén)的雇員信息select * from emp where deptno not in (select deptno from emp where empno=7788 or empno = 7839);五、組函數(shù) 計(jì)算查詢(xún)組函數(shù)：對(duì)一組數(shù)據(jù)進(jìn)行操作的函數(shù)SUM() 求和函數(shù)AVG() 求平均值MAX() 求最大值MIN() 求最小值COUNT() 統(tǒng)計(jì)應(yīng)用組函數(shù)：例題：計(jì)算出所有雇員的工資總和select sum(sal) as sum_sal

54、from emp;例題：求出所有雇員的平均工資select avg(sal) as avg_sal from emp;例題：求出所有雇員中最低工資和最高工資select max(sal) as max_sal,min(sal) as min_sal from emp;例題：統(tǒng)計(jì)出雇員表中出現(xiàn)的部門(mén)有多少個(gè)（重復(fù)值統(tǒng)計(jì)問(wèn)題）參數(shù)中加： distinct 重復(fù)值只統(tǒng)計(jì)一次select count(distinct deptno) as count_dept from emp;例題：查詢(xún)出工資高于平均工資的所有雇員信息select * from emp where sal > (select

55、avg(sal) from emp);注意：當(dāng)組函數(shù)作為條件時(shí)，不能寫(xiě)中where后面，只能寫(xiě)group by 部分的having后面作為分組所滿足的條件。例題：查詢(xún)出工資都高于20部門(mén)的所有員工的雇員信息。select * from emp where sal > (select max(sal) from emp where deptno = 20);例題：查詢(xún)出工資等級(jí)為5級(jí)的雇員的所在部門(mén)名稱(chēng)內(nèi)容.分析：篩選條件：grade = 5所求內(nèi)容：dname所用的表：所求內(nèi)容涉及的字段來(lái)自的表 dept 篩選條件涉及字段來(lái)自的表 salgrade Dept和salgrade兩張表沒(méi)有直

56、接聯(lián)系，必須使用中間表 emp后，兩張表才能連接，查詢(xún)時(shí)必須包含中間表。方法1：思想：將dept，salgrade，emp三張表等值連接成為一張表后，進(jìn)行條件和所內(nèi)容的篩選select distinct dname from dept,emp,salgrade where dept.deptno = emp.deptno and (sal>=losal and sal <=hisal) and grade = 5;方法2：思想：將dept和emp先連接成為一張表后，再和salgrade進(jìn)行嵌套查詢(xún)select distinct dname from dept,emp where dept.deptno = emp.deptno and sal >= (select losal from salgra