第4章-數(shù)據(jù)庫的查詢和視圖

1、第4章 數(shù)據(jù)庫的查詢和視圖01 選擇、投影和連接02 數(shù)據(jù)庫的查詢03 數(shù)據(jù)庫視圖1 選擇、投影和連接主要內容 選擇 投影 連接010203選擇學號姓名性別平均成績104215王敏男74104211李曉林女82 104210胡小平男88選擇性別為女且平均成績在80分以上的行學號姓名性別平均成績104211李曉林女82 學生表選擇后的結果查詢滿足條件的行投影學號姓名性別平均成績104215王敏男74104211李曉林女82104210胡小平男88對“學號”和“平均成績”投影學號平均成績104215741042118210421088學生表選擇后的結果查詢滿足條件的列連接T1T21A6F2BT1T

2、2T3T4T51A13M2B20NA表T3T4T513M20NB表T1 = T3等值連接連接后的表等值連接T1T2T310A1B15A1C220D2C2T1T2T3T4T5T65A1C210A2C220D2C20A2D1A表T1T4T5T61100A1D11002B2C1200A2D1510A2C2B表自然連接連接后的表自然連接2 數(shù)據(jù)庫的查詢主要內容 選擇列 選擇行 連接 統(tǒng)計 排序0102030405為列指定別名不使用列別名使用列別名看下面這兩種查詢結果，哪個看著比較順眼呢？看下面這個例子檢索emp表員工的編號、姓名和年基本工資、日基本工資信息。分析：年基本工資查詢語句：select em

3、pno,ename,sal*12,sal/30 from emp;sal*12日基本工資信息sal/30 使用列別名列名 AS 列別名使用列別名列名 AS 列別名列名 列別名ename as 員工姓名empno 員工編號1.檢索emp表員工的編號、姓名和年基本工資、日基本工資信息，并為查詢結果設置列別名select empno as 員工編號,ename as 員工名稱,sal*12 as 年基本工資,sal/30 as 日基本工資 from emp;2.檢索emp表的指定列（empno、ename、job），不使用任何關鍵字而直接為這些列指定中文的別名select empno 員工編號,en

4、ame 員工名稱,job 職務 from emp;如果別名中包含了特殊字符，或想讓別名原樣顯示，就要使用雙引號把別名括起來?！霸囈辉嚒敝械牡谝粋€查詢，如果這樣寫：select empno as 員工編號,ename as 員工名稱,sal*12 as 年-基本工資,sal/30 as 日基本工資 from emp;分析列別名：年-基本工資，包含特殊符號“-”，而且別名沒有用雙引號括起來注意使用雙引號排除重復行查詢結果中包含重復行查詢結果中排除重復行 查詢結果中包含重復行默認情況下，查詢結果會包含所有滿足條件的結果，其中可能會包含重復行。在SCOTT模式下，顯示emp表中的job（職務）列查詢結

5、果中排除重復行為了排除重復行，需要使用DISTINCT關鍵字在SCOTT模式下，顯示emp表中的job（職務）列，要求顯示的“職務”記錄不重復select distinct job from emp;distinct的作用是消除重復內容，但是所謂的消除重復的內容，是指一條完整的數(shù)據(jù)全部是重復的，如果多行記錄只有一列重復而其他列不重復，那么也是無法消除的。查詢emp表的員工編號和職位當查詢比較大的表時應盡可能地避免使用DISTINCT關鍵字帶有表達式的SELECT子句在使用SELECT語句時，對于數(shù)字數(shù)據(jù)和日期數(shù)據(jù)都可以使用算術表達式。在SELECT語句中可以使用算術運算符，包括（+）、減（-）

6、、乘（*）、除（/）和括號。檢索emp表的sal列，把其值調整為原來的1.1倍select ename, sal , sal*(1+0.1) from emp;算術運算符是有優(yōu)先級先乘除后加減。在表達式中同一優(yōu)先級的運算符計算次序是從左到右。如果使用了括號，括號中的運算優(yōu)先。如果有多重括號嵌套，內存括號中的運算優(yōu)先。查詢所有列查詢單個表中所有列查詢多個表中所有列select distinct | * | 列名稱 AS列別名，列名稱AS列別名,from 表名稱 表別名; 查詢單個表中所有列要查詢表中所有列在SELECT子句后面使用星號（*）來實現(xiàn)在SCOTT模式下，在SELECT語句中使用星號（

7、*）來檢索dept表中所有的數(shù)據(jù)第一步：連接SCOTT模式connect scott/tiger第二步：查詢dept表中所有的數(shù)據(jù)select * from dept; 如果在SYSTEM模式中，查詢emp表，要如何進行查詢呢？想一想如果這樣做：select * from emp;應該這樣做：select * from soctt.emp; 查詢多個表中所有列要查詢多個表中所有列在FROM子句后面指定多個數(shù)據(jù)表，用逗號隔開在SCOTT模式下，同時查詢dept和salgrade表中的所有數(shù)據(jù)分析：要想“同時查詢dept和salgrade表中的所有數(shù)據(jù)”就在from子句中指定兩個數(shù)據(jù)表dept和s

8、algrade查詢語句：select * from dept, salgrade;查詢特定列查詢特定列偽列如果想查找單個數(shù)據(jù)怎么辦？買 雪地鞋，手套從 百貨大樓查詢特定列SELECT column_name1,column_name2,column_name3,column_name FROM 表名稱;SELECT子句后面可以加想要查詢的列名，用“，”隔開語法在SCOTT模式下，檢索emp表中指定的列（job、ename、empno）查詢語句select job、ename、empno from emp;分析用SELECT語句來查詢job、ename、empno，只要在SELECT后面寫job

9、、ename、empno就可以啦偽列一種數(shù)據(jù)類型，唯一標識一條記錄，物理位置的一個id，基于64位編碼的18個字符。定義 它并不是真實的存在于數(shù)據(jù)表中的列，所以被稱為偽列。偽列可以從表中查詢，但是不能插入、更新或刪除。 偽列的用途1.能以最快的方式訪問表中的一行2.能顯示表的行是如何存儲的3.作為表中唯一標識 常用的偽列rowid和rownum數(shù)據(jù)庫中的每一行都有一個行地址，rowid偽列返回該行地址?？梢允褂胷owid值來定位表中的一行，通常情況下，rowid值可以唯一地標識數(shù)據(jù)庫的一行。rowid對于一個查詢返回的每一行，rownum偽列返回一個數(shù)值代表的次序。返回第一行的rownum值為

10、1，第二行的rownum值為2，以此類推。通過使用rownum偽列，用戶可以限制查詢返回的行數(shù)rownum1.查詢emp表的rowid列select rowid from emp;2.從emp表中查詢前5條數(shù)據(jù)select * from emp where rownum 6;選擇行1表達式比較比較運算符的格式為：expression = | | | = | | != expression查詢emp表中工資（sal）大于1500的數(shù)據(jù)記錄SQL select empno,ename,sal from emp where sal 1500;選擇行2模式匹配LIKE謂詞表達式的格式為：string_

11、expression NOT LIKE string_expressionESCAPE escape_characterLIKE謂詞 LIKE運算符可以使用以下兩個通配符“%”和“_”。其中：“%”：代表0個或多個字符?！癬”：代表一個且只能是一個字符。選擇行3范圍比較 用于范圍比較的關鍵字BETWEEN關鍵字IN關鍵字選擇行4空值比較空值（NULL）從技術上來說就是未知的、不確定的值，但空值與空字符串不同，因為空值是不存在的值，而空字符串是長度為0的字符串。SQL select empno,ename,sal,comm from emp where comm is null;查詢emp表中沒

12、有獎金的員工信息選擇行5子查詢（1）單行子查詢 單行子查詢是指返回一行數(shù)據(jù)的子查詢語句。當在WHERE子句中引用單行子查詢時，可以使用單行比較運算符（=、=、=和）。SQL select empno,ename,sal from emp where sal (select min(sal) from emp) and sal select empno,ename,job from emp where deptno in (select deptno from dept where dnameSALES);在emp表中，查詢不是銷售部門（SALES）的員工信息選擇行（3）關聯(lián)子查詢 在一些特殊需

13、求的子查詢中，內查詢的執(zhí)行需要借助于外查詢，而外查詢的執(zhí)行又離不開內查詢的執(zhí)行，這時，內查詢和外查詢是相互關聯(lián)的，這種子查詢就被稱為關聯(lián)子查詢。SQL select empno,ename,sal from emp f where sal (select avg(sal) from emp where job = f.job) order by job;在emp表中，使用“關聯(lián)子查詢”檢索工資大于同職位的平均工資的員工信息連接使用簡短的表別名就可以替代原有較長的表名稱，這樣就可以大大縮減語句的長度。SQL select e.empno as 員工編號, e.ename as 員工名稱, d.d

14、name as 部門 from emp e,dept d where e.deptno=d.deptno and e.job=MANAGER;1表別名通過DEPTNO（部門號）列來關聯(lián)emp表和dept表，并檢索這兩個表中相關字段的信息。連接內連接是一種常用的多表關聯(lián)查詢方式，一般使用關鍵字INNER JOIN來實現(xiàn)。其中，INNER關鍵字可以省略，當只使用JOIN關鍵字時，語句只表示內連接操作。SQL select e.empno as 員工編號, e.ename as 員工名稱, d.dname as 部門 from emp e inner join dept d on e.deptno=

15、d.deptno;2內連接通過deptno字段來內連接emp表和dept表，并檢索這兩個表中相關字段的信息。連接3外連接外連接通常有以下三種：左外連接：關鍵字為LEFT OUTER JOIN或LEFT JOIN。右外連接：關鍵字為RIGHT OUTER JOIN 或RIGHT JOIN。完全外連接：關鍵字為FULL OUTER JOIN或FULL JOIN。連接 左外連接的查詢結果中不僅包含了滿足連接條件的數(shù)據(jù)行，而且還包含左表中不滿足連接條件的數(shù)據(jù)行。SQL insert into emp(empno,ename,job) values(9527,EAST,SALESMAN);SQL sel

16、ect e.empno,e.ename,e.job,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno;（1）左外連接首先使用insert語句在emp表中插入新記錄（注意沒有為deptno和dname列插入值，即它們的值為null），然后實現(xiàn)emp表和dept表之間通過deptno列進行左外連接。連接 同樣道理，右外連接的查詢結果中不僅包含了滿足連接條件的數(shù)據(jù)行，而且還包含右表中不滿足連接條件的數(shù)據(jù)行。SQL select e.empno,e.ename,e.job,d.deptno,d.dname from em

17、p e right join dept d on e.deptno=d.deptno;（2）右外連接實現(xiàn)emp表和dept表之間通過deptno列進行右外連接。連接 在執(zhí)行完全外連接時，Oracle會執(zhí)行一個完整的左外連接和右外連接查詢，然后將查詢結果合并，并消除重復的記錄行。SQL select e.empno,e.ename,e.job,d.deptno,d.dname from emp e full join dept d on e.deptno=d.deptno;（3）完全外連接實現(xiàn)emp表和dept表之間通過deptno列進行完全外連接。連接 自然連接和內連接的功能相似，自然連接是指

18、在檢索多個表時，Oracle會將第一個表中的列與第二個表中具有相同名稱的列進行自動連接。在自然連接中，用戶不需要明確指定進行連接的列，這個任務由Oracle系統(tǒng)自動完成，自然連接使用“NATURAL JOIN”關鍵字。SQL select empno,ename,job,dname from emp natural join dept where sal 2000;4自然連接在emp表中檢索工資（sal字段）大于2000的記錄，并實現(xiàn)emp表與dept表的自然連接。連接 自連接主要用在自參照表上顯示上下級關系或者層次關系。自參照表是指在同一張表的不同列之間具有參照關系或主從關系的表。例如，em

19、p表包含empno（雇員號）和mgr（管理員號）列，兩者之間就具有參照關系。這樣用戶就可以通過mgr列與empno列的關系，實現(xiàn)查詢某個管理者所管理的下屬員工信息。5自連接連接SQL select em2.ename 上層管理者,em1.ename as 下屬員工 from emp em1 left join emp em2 on em1.mgr=em2.empno order by em1.mgr;查詢所有管理者所管理的下屬員工信息。連接交叉連接實際上就是不需要任何連接條件的連接，它使用cross join關鍵字來實現(xiàn)。6交叉連接SQL select count(*) from dept c

20、ross join emp;通過交叉連接dept表和emp表，計算出查詢結果的行數(shù)。統(tǒng)計1聚合函數(shù) 函 數(shù)說 明AVG(xDISTINCT|ALL)計算選擇列表項的平均值，列表項目可以是一個列或多個列的表達式COUNT(xDISTINCT|ALL)返回查詢結果中的記錄數(shù)MAX(xDISTINCT|ALL)返回選擇列表項目中的最大數(shù)，列表項目可以是一個列或多個列的表達式MIN(xDISTINCT|ALL)返回選擇列表項目中的最小數(shù)，列表項目可以是一個列或多個列的表達式SUM(xDISTINCT|ALL)返回選擇列表項目的數(shù)值總和，列表項目可以是一個列或多個列的表達式VARIANCE(xDISTI

21、NCT|ALL)返回選擇列表項目的統(tǒng)計方差，列表項目可以是一個列或多個列的表達式STDDEV(xDISTINCT|ALL)返回選擇列表項目的標準偏差，列表項目可以是一個列或多個列的表達式統(tǒng)計2GROUP BY函數(shù)GROUP BY子句經(jīng)常與聚集函數(shù)一起使用。使用GROUP BY子句和聚集函數(shù)，可以實現(xiàn)對查詢結果中每一組數(shù)據(jù)進行分類統(tǒng)計。所以，在結果中每個數(shù)據(jù)都有一個與之對應的統(tǒng)計值。函 數(shù)說 明AVG返回一個數(shù)字列或是計算列的平均值COUNT返回查詢結果中的記錄數(shù)MAX返回一個數(shù)字列或是計算列的最大值MIN返回一個數(shù)字列或是計算列的最小值SUM返回一個數(shù)字列或是計算列的總和統(tǒng)計3HAVING子句

22、HAVING子句通常與GROUP BY子句一起使用，在完成對分組結果統(tǒng)計后，可以使用HAVING子句對分組的結果做進一步的篩選。SQL select deptno as 部門編號,avg(sal) as 平均工資 from emp group by deptno having avg(sal) 2000 ; 在emp表中，首先通過分組的方式計算出每個部門的平均工資，然后再通過having子句過濾出平均工資大于2000的記錄信息。排序在SELECT語句中，可以使用ORDER BY子句對檢索的結果集進行排序，該子句位于FROM子句之后，其語法格式如下：SELECT columns_listFROM

23、 table_nameWHERE conditional_expressionGROUP BY columns_listORDER BY order_by_expression ASC | DESC ,.n 3 數(shù)據(jù)庫視圖主要內容 視圖的概念 創(chuàng)建視圖 查詢視圖 更新視圖 修改視圖的定義0102030405 刪除視圖06視圖的概念 視圖是一個虛擬表，它由存儲的查詢構成，可以將它的輸出看作是一個表。視圖同真的表一樣，也可以包含一系列帶有名稱的列和行數(shù)據(jù)。但是，視圖并不在數(shù)據(jù)庫中存儲數(shù)據(jù)值，其數(shù)據(jù)值來自定義視圖的查詢語句所引用的表，數(shù)據(jù)庫只在數(shù)據(jù)字典中存儲視圖的定義信息。 視圖建立在關系表上，也可

24、以在其它視圖上，或者同時建立在兩者之上。視圖看上去非常像數(shù)據(jù)庫中的表，甚至可以在視圖中進行INSERT、UPDATE和DELETE操作。通過視圖修改數(shù)據(jù)時，實際上就是在修改基本表中的數(shù)據(jù)。與之相對應，改變基本表中的數(shù)據(jù)也會反映到由該表組成的視圖中。創(chuàng)建視圖使用SQL Developer中創(chuàng)建視圖創(chuàng)建視圖使用CREATE VIEW語句創(chuàng)建視圖create or replace view alias,alias) as with check option constraint constraint_namewith read only語法查詢視圖 用戶可以通過SELECT語句像查詢普通的數(shù)據(jù)表一樣

25、查詢視圖的信息。SQL select * from emp_view;在SCOTT模式下，通過select語句查詢視圖emp_view。更新視圖可更新視圖滿足以下條件：沒有使用連接函數(shù)、聚合函數(shù)和組函數(shù)；創(chuàng)建視圖的SELECT語句中沒有聚合函數(shù)且沒有GROUP BY、ONNECT BY、 START WITH子句及DISTINCT關鍵字；創(chuàng)建視圖的SELECT語句中不包括從基表列通過計算所得的列；創(chuàng)建視圖沒有包含只讀屬性。更新視圖使用UPDATE語句可以通過視圖修改基本表的數(shù)據(jù)。 將emp_view_complex視圖中員工編號是7566的員工的工資改為3000試一試修改視圖的定義使用SQL D