數(shù)據(jù)查詢語言

1、數(shù)據(jù)查詢語言相關(guān)的操作：1.sqlplus訪問數(shù)據(jù)庫命令，和數(shù)據(jù)庫建立連接的命令： sqlplus 用戶名/密碼2.顯示當(dāng)前用戶的用戶名： show user3.改變身份： conn（ect） 用戶名/密碼特殊：sys用戶，要指明以什么身份登錄AS SYSDBA/SYSOPR。說明：一旦使用了超級(jí)管理員登錄的話，可以通過命令手工的控制數(shù)據(jù)庫實(shí)例的打開和關(guān)閉：shutdown immediate;一旦關(guān)閉后，用戶無法直接連接sqlplusw命令，此時(shí)可以：A.先使用“/nolog”登錄；B.使用管理員登錄：conn sys/SMITHe_on_install as sysdba;C.然后啟動(dòng)數(shù)據(jù)

2、庫：startup,就正常連接了。4.sqlpus命令窗口也可以調(diào)用本機(jī)的操作系統(tǒng)命令，使用“host”作為前綴。例如：host copy c:test1.sql c:test2.txt;一 單表查詢語句 (1).查詢系統(tǒng)表：（查詢本用戶下所擁有的所有表的表明）select table_name from user_tables;scott用戶下有四張表，使用：select * from tab; 查詢所有的數(shù)據(jù)表的名稱。(2).查看表的結(jié)構(gòu)：desc 表明1查詢某張表的所有數(shù)據(jù)：select distinct /可無 *col_name1,col_name2, from tb_name;一般

3、不要寫*，有多少就具體寫出來。2.對(duì)指定的列的所有值進(jìn)行算術(shù)運(yùn)算：select col_name 運(yùn)算符 數(shù)字 from tb_name;例：select id,last_name,(salary+100)*12 from s_emp;3.給查詢的列取別名： select old_colunm as new_column_name from tb_name; 例：select id,last_name,salary*12 annual from s_emp;4.變換查詢顯示：（盡量別使用中文）select job 工作,sal 工資 from scott.emp;5.使用|可以使得多列的值或者

4、列和特殊的字符串合并到一個(gè)列進(jìn)行顯示： select col_name|spe_char|col_name from tb_name;例：select id,first_name| |last_name|,|title name from s_emp;6.對(duì)null值進(jìn)行替換運(yùn)算（nvl是通用函數(shù)的一種，見單行函數(shù)）： select nvl(col_name,SMITHe_value) from tb_name; 例：select id,last_name,nvl(commission_pct,0) commission_pct from s_emp;7.用distinct關(guān)鍵字，可以將顯示中

5、重復(fù)的記錄只顯示一條：select distinct col_name,col_name from tb_name;例：select distinct title,dept_idfrom s_emp; 注意1：distinct 關(guān)鍵字只能放在select關(guān)鍵詞后面。 如：select id,distinct title from s_emp;錯(cuò)誤！注意2：distinct關(guān)鍵詞后面如果出現(xiàn)多列，表示多列聯(lián)合去重，即多列的值都相同的時(shí)候才會(huì)認(rèn)為是重復(fù)的記錄。1 2 1 21 3 結(jié)果：1 32 4 2 42 47，sqlplus命令 a:在當(dāng)前操作的命令行追加內(nèi)容。 c:在當(dāng)前操作的命令行修改內(nèi)

6、容，只能修改一次，修改第一個(gè)。 clear buffer：清空當(dāng)前緩存del:刪除當(dāng)前操作行。del line_num,刪除指定行。i:當(dāng)前操作命令行的下一行插入內(nèi)容。l:查看緩存命令 l line_num:查看指定的命令行。n text:替換第n行的整行內(nèi)容。?。汉竺娼咏K端命令。 ！clear：清屏8.sqlplus設(shè)置：set pause on回車響應(yīng)，分屏顯示，只在本會(huì)話中有效。 ctrl+c 停止顯示。set pause off 關(guān)閉分屏顯示。set pause “please enter key:”設(shè)置分屏顯示的提示信息。set head off 提頭輸出關(guān)閉。set feed of

7、f 結(jié)尾輸出關(guān)閉。set echo off 回寫關(guān)閉。 set linesize num; 設(shè)置每行顯示的字節(jié)數(shù)。 set pagesize 30; 設(shè)置每頁顯示的行數(shù)。9.文件命令：沒有“；”號(hào)save file_name:將緩存命令（即就上一條）保存到file_name中。get file_name:將文件中的內(nèi)容提取到sqlplus。start file_name:執(zhí)行文件中的sqlp命令。filename:同上。edit file_name:使用終端設(shè)置好的vi編輯器編輯文件。spool file_name:將接下來的sql語句以及sql的運(yùn)行結(jié)果保存到文件中。直到遇到 spool o

8、ff 關(guān)閉spool功能。例：sqll result1exit:退出。10.column命令： column命令 列格式的定義 ·column 目標(biāo)列名 /查看這個(gè)列是否定義了格式 ·column 目標(biāo)列名 format a. /設(shè)置列寬 ·column last_name heading Employee|Name format A15 /設(shè)置題頭 |是換行符。 ·column salary justify left format $99,999.00 /定義數(shù)字顯示格式 注意：如果不滿足顯示的格式，就會(huì)把數(shù)據(jù)顯示為“#”。 ·column

9、salary justify left format $00,000.00會(huì)出現(xiàn)$00,928.00,用0補(bǔ)。 ·column 列名 clear （清除列格式定義） ·$99,999.00中的“$”: 加$；“L”: 使用本地貨幣符號(hào)； “.”: 意思是小數(shù)計(jì)數(shù)顯示； “，”: 表示千分位顯示。查看列是否定義了格式：設(shè)置列寬：取消列的格式定義：設(shè)置題目：column last_name heading Employee|Name format A15。|為換行。設(shè)置后用select empno from scott.emp;查看，如果起了個(gè)別名查看，select empno

10、 Em from scott.emp;此時(shí)看到的是沒有格式的原來的內(nèi)容。注意：這個(gè)很像前面的起別名：select empno Em from scott.emp;但是起別名沒有引號(hào)的效果，也不能換行，僅僅是起了別名。定義數(shù)字顯示格式：-左對(duì)齊（left）設(shè)置格式中不夠的補(bǔ)0：column salary justify left format $00,000.00二選擇操作1. order by排序查詢升序：ASC 降序：DESC語法：order by 目標(biāo)列名（別名）排序順序（不寫則默認(rèn)升序） 注意：有where時(shí)，order by在where后面。其實(shí)order by是在語句的最后的。2wh

11、ere子句（條件查詢） (1)單條件查詢： select job,sal from scott.emp where job=MANAGER;select job,sal from scott.emp where sal<=1600;單條件查詢使用的比較運(yùn)算符表：說明：1. like和not like適合于字符型字段的查詢，%代表任意長(zhǎng)度的字符串，_下劃線代表一個(gè)任意的字符。like M%代表M開頭的任意長(zhǎng)度的字符串，like M_代表M開頭的長(zhǎng)度為3的字符串。 在開發(fā)之中，數(shù)據(jù)可的模糊查詢肯定使用LIKE子句，但是在使用LIKE子句的時(shí)候有一個(gè)最大的注意點(diǎn)：如果在模糊查詢上不設(shè)置任何的查

12、詢關(guān)鍵字的話（%），則表示查詢?nèi)坑涗洝＿@個(gè)特點(diǎn)可以幫助用戶節(jié)約很多的代碼，一定記??！·下面的例子可以實(shí)現(xiàn)：N出現(xiàn)在第三個(gè)位置的長(zhǎng)度不小于5的ename；·下面的可以實(shí)現(xiàn)：ename中含有“_”（下劃線）的名字。用轉(zhuǎn)義字符和escape 。select deptno,job,ename,LENGTH(ename) from scott.emp where job like '%_%' escape ;2.between and不僅能對(duì)數(shù)值型操作，還能對(duì)日期型操作。是閉區(qū)間.3.is (not)null的用法： 4.in（在列表）指定一個(gè)范圍的操作。 如：查詢

13、empno=7369，empno=7566，empno=7799的員工。7799沒有就不顯示了。注意： 如果現(xiàn)在使用了in操作符，查詢的范圍之中存在了null，不影響查詢；如果現(xiàn)在使用的是NOT IN ，查詢的范圍之中存在null（表示查詢?nèi)繑?shù)據(jù)），則不會(huì)有任何的數(shù)據(jù)返回。 (2)組合條件的查詢： select job,sal from scott.emp where job<=MANAGER and sal<=2000; select job,sal from scott.emp where job<=MANAGER or sal<=2000;select job,

14、sal from scott.emp where not job=TOM ; /not job即job<>TOM。3.分組查詢：分組查詢是指將查詢結(jié)果按照某列或者某些列相同的值進(jìn)行分組，然后對(duì)改組的數(shù)據(jù)進(jìn)行組函數(shù)運(yùn)用。語法： selectfromwheregroup by col_name,col_namehaving order by 順序不能改變。group by col_name:即將數(shù)據(jù)按照col_name相同值進(jìn)行分組組函數(shù)常見的5個(gè)：avg:求平均數(shù)count：求總數(shù)max:最大值min:最小值sum:求和1【avg】函數(shù)用法：avg(字段名) 求平均值。要求字段為數(shù)值

15、型。 select dept_id,avg(salary) dept_salary from s_emp group by dept_id order by dept_salary desc;工作原理：先將相同的id分組，然后對(duì)其中的salary分別求和，隨后按avg(salary)降序排列。 2【count】函數(shù)用法：count(字段名) 或 count(*) 統(tǒng)計(jì)總數(shù)。 注意： COUNT()函數(shù)的主要功能是進(jìn)行數(shù)據(jù)的統(tǒng)計(jì)，但是在進(jìn)行數(shù)據(jù)統(tǒng)計(jì)的時(shí)候，如果一張表中沒有統(tǒng)計(jì)記錄，COUNT()也會(huì)返回?cái)?shù)據(jù)，只是這個(gè)數(shù)據(jù)是“0”。 如果使用的是其他函數(shù)，則可能返回null，但是COUNT()永遠(yuǎn)

16、都會(huì)返回一個(gè)具體的數(shù)字，這一點(diǎn)以后在開發(fā)之中都會(huì)使用到。 3【max】函數(shù) 用法：max(字段名) 計(jì)算數(shù)值型字段最大數(shù)。4【min】函數(shù) 用法：min(字段名) 計(jì)算數(shù)值型字段最小數(shù)。 5【sum】函數(shù) 用法：sum(字段名) 計(jì)算數(shù)值型字段的總和。select job,sal from scott.emp group by sal,job having sal<=2000;select job,sal from scott.emp where sal<=2000 group by job,sal;注意： 1.沒有出現(xiàn)在group by子句中和組函數(shù)中的列，不能出現(xiàn)在select

17、子句中：如： select dept_id,last_name,max(salary) from s_empgroup by dept_id;（錯(cuò)誤）并不能找到工資最大的員工名字。last_name不能存在。 2.當(dāng)group by子句中出現(xiàn)多列的時(shí)候，表示按照從左到右的順序進(jìn)行分組，即 按照第一列分組，然后在第一列分號(hào)的組里面，按照第二列進(jìn)行分組，以此類推。 3.如果限制條件中出現(xiàn)了組函數(shù)，該條件必須放到having子句中，不能放在where子句中，此時(shí)其實(shí)where已經(jīng)管不著了。如：avg(salary)>1000select dept_id ,avg(salary) from s_

18、empgroup by dept_id;having avg(salary)>1000;4.where在執(zhí)行g(shù)roup by之前的檢測(cè)每條記錄是否符合條件，having是執(zhí)行g(shù)roup by之后，再次檢查分組后的各組是否滿足條件，having語句只能配合group by 語句使用，沒有g(shù)roup by 時(shí)不能使用having，但可以使用where。 二 多表查詢：盡可能不要用多表查詢，用單表性能很差，因?yàn)閿?shù)據(jù)量大。多表查詢，又稱表聯(lián)合查詢，是相對(duì)于單表而言的，指從多個(gè)數(shù)據(jù)表中查詢數(shù)據(jù)，下面主要是從兩個(gè)表查詢數(shù)據(jù)。多表查詢連接方式：1 等值連接2 不等值連接3 外連接4 自連接：5 集合連

19、接注意：一定要指明查的內(nèi)容是哪張表的，不能直接寫ename,deptno等。除非這個(gè)內(nèi)容在一張唯一的表中。1. 無條件多表查詢：是將各表的記錄以“笛卡爾”積的方式組合起來。例如：scott.emp有14條記錄，scott.dept有4條，那么它們的“笛卡爾”積就是4*14=56條記錄。即使只查一個(gè)字段，也會(huì)顯示56條記錄。2. 等值多表查詢：等值多表查詢將按照等值的條件查詢多個(gè)數(shù)據(jù)表中關(guān)聯(lián)的數(shù)據(jù)。要求關(guān)聯(lián)的多個(gè)數(shù)據(jù)表的某些字段具有相同的屬性，即具有相同的數(shù)據(jù)類型、寬度和取值，要不沒辦法比較是否等值。3. 非等值多表查詢：where，運(yùn)用上面的比較運(yùn)算符表來組合查詢條件。 注意：如果表的名稱很長(zhǎng)

20、，例如：”yinheng_yazhou_zhongguo_shanghai_ren”,所以在進(jìn)行多表查詢的時(shí)候往往都會(huì)先為表起個(gè)別名，然后通過“別名.字段”的方式進(jìn)行查詢。4. 外連接-能顯示的很全 當(dāng)一個(gè)表的記錄在另外一張表中不存在的時(shí)候，我們依舊需要顯示，使用外連接需求：查看s_customer中所有客戶的id和名字以及該客戶所對(duì)應(yīng)的負(fù)責(zé)員工的id，last_name表之間的連接列 s_customer 表中的sales_rep_id s_emp 表中的id但是發(fā)現(xiàn)一張表中多出的內(nèi)容也不顯示了，如果要顯示，則使用左、右連接。左、右連接 關(guān)于左、右連接指的是查詢判斷條件的參考方向，例如： s

21、elect * from emp e,dept d where e.deptno=d.deptno; 查詢結(jié)果少了40這個(gè)部門，因?yàn)樵诠蛦T表之中沒有一條記錄是屬于40部門的，所以現(xiàn)在不會(huì)顯示40部門的信息，即：現(xiàn)在的查詢以emp表為參考，那么如果說現(xiàn)在非要顯示40部門，就必須改變這種參考餓方向，就需要使用左、右連接。select * from emp e,dept d where e.deptno（+）=d.deptno; 就會(huì)出現(xiàn)40部門?！埃?）”就用于左、右連接的更改，這種符號(hào)有以下兩種使用情況： ·（+）=：放在等號(hào)左邊，表示右連接； ·=（+）：放在等號(hào)右邊，表示

22、左連接。意義：右（左）外連接表示右（左）邊表（=號(hào)右（左）邊的表）中的記錄在左（右）邊表中不存在的時(shí)候，右（左）邊表的記錄依舊顯示。不用刻意區(qū)分左還是右，只是根據(jù)查詢結(jié)果而定，如果發(fā)現(xiàn)有些需要的數(shù)據(jù)沒有顯示出來，就使用此符號(hào)更改連接方向。這個(gè)符號(hào)是Oracle數(shù)據(jù)庫獨(dú)有的，其他數(shù)據(jù)庫不能使用。如果想使用，就看下面的5. 自連接：實(shí)質(zhì)就是一張表當(dāng)多張表用，即一張表中的某列的值取決于自己的某一列。語法：給同一個(gè)表取不同的別名就行。需求：查看每一個(gè)員工的經(jīng)理的名字。 6. 集合連接 union：將上下結(jié)果取并集，去除掉重復(fù)的記錄（重復(fù)的只顯示一次）。union all:將上下結(jié)果全部顯示，包括重復(fù)的

23、。minus：取差集intersect：取交集 union： select e.last_name,from s_emp e,s_dept dwhere e.dept_id(+)=d.idunionselect e.last_name,from s_emp e,s_dept dwhere e.dept_id=d.id(+) minus： select last_name,salaryfrom s_empwhere rownum<=10minusselect last_name,salaryfrom s_empwhere rownum<5rownum是記錄行號(hào)

24、。 intersect: select last_name,salaryfrom s_enpwhere rownum<=10intersectselect last_name,salaryfrom s_emp where rownum<57.SQL:1999語法除了以上的表連接操作之外，在SQL語法中，也提供了另外一套用于表連接的操作SQL，格式如下： (1).交叉連接（CROSS JOIN table）:用于產(chǎn)生笛卡爾積。 select * from emp CROSS JOIN dept; 笛卡爾積本身并不是屬于無用大的內(nèi)容，在某些情況下還是需要使用的。(2).自然連接（NAT

25、URAL JOIN）：自動(dòng)找到匹配的關(guān)聯(lián)字段，消除掉笛卡爾積。 select * from emp NATURAL JOIN dept; 但是，并不是所有的字段都是關(guān)鍵字段，設(shè)置關(guān)聯(lián)字段需要通過約束指定。(3).JOINUSING子句：用戶自己指定一個(gè)消除笛卡爾積的關(guān)聯(lián)字段。 select * from emp JOIN dept USING(deptno);(4).JOINON子句：用戶自己指定一個(gè)可以消除笛卡爾積的關(guān)聯(lián)條件。 select * from emp JOIN dept ON(emp.deptno=dept.emptno);(5).連接方向的改變：select * from em

26、p RIGHT OUTER JOIN dept ON(emp.deptno=dept.deptno);在Oracle之外的數(shù)據(jù)庫都使用以上的SQL：1999語法操作，所以這個(gè)語法必須會(huì)一些。再次強(qiáng)調(diào)：多表查詢的性能肯定不高，而且性能一定要在大數(shù)據(jù)量的情況下才能夠發(fā)現(xiàn)。三 用SQL進(jìn)行嵌套查詢?cè)趕elect查詢語句里可以嵌入select查詢語句，稱為嵌套查詢。也稱為“子查詢”，子查詢形成的結(jié)果又成為父查詢的條件。子查詢可以嵌套多層，子查詢操作的數(shù)據(jù)表可以使父查詢不操作的數(shù)據(jù)表。子查詢中不能有order by分組語句。例如：查看工資大于SMITH員工工資的所有員工的empno和ename。分析步驟

27、：第一步：確定需求第二步：寫父句，寫子句 父句：select empno,ename,sal from scott.emp where sal>?; 子句：select sal from emp where ename='WARD'第三步：嵌套 select empno,ename,salfrom scott.empwhere sal>(select sal from emp where ename='WARD');1.簡(jiǎn)單嵌套查詢等價(jià)于： select sal from scott.emp where ename=WARD，得出sal=1250,

28、select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal>=1250;2.帶【in】的嵌套查詢查詢薪水和WARD相等的員工。3.帶【any】的嵌套查詢 等價(jià)于： select sal from scott.emp where job=MANAGER; 4.帶【some】的嵌套查詢 等價(jià)于： select sal from sal from scott.emp where job=MANAGER； any和some的嵌套查詢功能是一樣的，any是早期的。5.帶【all】的嵌套查詢等價(jià)于：select sal fr

29、om scott.emp where job=MANAGER; 6.帶【exists】的嵌套查詢 7.并操作的嵌套查詢-union即并集，A或B元素的總和（相同的元素只顯示一次）。 8.交操作的嵌套查詢-intersect交操作就是集合中交集的概念。屬于A且屬于B元素的總和。 9.差操作的嵌套查詢-minus 就是集合中差集的概念，屬于A且不屬于B的元素總和。 注意：并、交和差操作的嵌套查詢要求屬性具有相同的定義，包括類型和取值范圍。練習(xí)：練習(xí)1：查詢工資大于smith所在部門平均工資的員工的select sal,enamefrom scott.empwhere sal>(select

30、avg(sal) from scott.emp where deptno=(select deptno from emp where ename='SMITH') group by deptno);練習(xí)2：查看部門平均工資大于30號(hào)部門平均工資的部門deptno.select deptno,avg(sal)from scott.empgroup by deptnohaving avg(sal)> (select avg(sal) from scott.empwhere deptno=30);練習(xí)3：查看部門和名字為SMITH的部門相同或者區(qū)域ID為2的部門相同的部門所有員

31、工id和名字父句：select id,last_name,dept_idfrom s_empwhere dept_id = ?or dept_id in ?;子句1：select dept_idfrom s_empwhere last_name = 'SMITH'子句2：select idfrom s_deptwhere region_id = 2;練習(xí)：1.查看薪資大于SMITH員工薪資的員工信息2.查看薪資大于SMITH員工薪資或者所在部門在3號(hào)區(qū)域下 的員工的信息3.查看薪資大于SMITH所在區(qū)域平均工資的員工信息4.查看薪資高于SMITH員工經(jīng)理薪資的員工信息5.查看薪

32、資大于SMITH員工經(jīng)理的經(jīng)理所在區(qū)域的最低工資的員工的信息>24506.查看客戶負(fù)責(zé)員工中工資大于SMITH員工的工資的員工信息練習(xí):1.查看SMITH員工所在部門其他員工薪資總和2.統(tǒng)計(jì)不由11號(hào)和12號(hào)員工負(fù)責(zé)的客戶的人數(shù)3.查看部門平均工資大于SMITH所在部門平均工資的部門信息4.查看員工的id，last_name，salary，部門名字，區(qū)域名字，這些員工有如下條件：薪資大于SMITH所在區(qū)域的平均工資或者跟SMITH員工不在同個(gè)部門1.select ename,empno,sal from scott.emp where sal>(select sal from sc

33、ott.emp where ename='SMITH');2.select ename,empno,sal,deptno from scott.emp where sal>(select sal from scott.emp where ename='SMITH') or deptno=3;3.select ename,sal from scott.emp where sal>(select avg(sal) from scott.emp where deptno=(select deptno from scott.emp where ename=&

34、#39;SMITH') );4.select ename,sal from scott.emp where sal>(select sal from scott.emp where empno=(select mgr from scott.emp where ename='SMITH');5.select sal,ename from scott.emp where sal>(select min(sal) from scott.emp where job=(select job from scott.emp where empno=(select mgr

35、from scott.emp where empno=(select mgr from scott.emp where ename='SMITH') group by job);練習(xí)2：1.select sum(sal) from scott.emp where deptno=(select deptno from scott.emp where ename='SMITH') and ename !='SMITH'2.select sum(num) from scott.emp where empno !=11 and empno !=12 3.select deptno,avg(sal) from scott.emp group by deptno having avg(sal)&