SQL語句學(xué)習(xí)手冊(cè)實(shí)例精華版

1、SQL語句學(xué)習(xí)手冊(cè)實(shí)例版(本人整理，用來查詢) 表操作 例 1對(duì)于表的教學(xué)管理數(shù)據(jù)庫中的表 STUDENTS ，可以定義如下： CREATETABLESTUDENTS (SNO NUMERIC (6, 0) NOT NULL SNAME CHAR (8) NOT NULL AGE NUMERIC(3,0) SEX CHAR(2) BPLACECHAR(20) PRIMARY KEY(SNO) 例 2對(duì)于表的教學(xué)管理數(shù)據(jù)庫中的表 ENROLLS ，可以定義如下： CREATETABLEENROLLS (SNO NUMERIC(6,0)NOT NULL CNO CHAR(4)NOT NULL GR

2、ADE INT PRIMARY KEY(SNO,CNO) FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO) FOREIGN KEY(CNO) REFERENCES COURSES(CNO) CHECK (GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100) 例 3根據(jù)表的 STUDENTS 表，建立一個(gè)只包含學(xué)號(hào)、姓名、年齡的女學(xué)生表。 CREATE TABLE GIRL AS SELECT SNO, SNAME, AGE FROM STUDENTS WHERE SEX= 女 ; 例 4刪除教師表 TEACHER 。 DROP

3、TABLETEACHER 例 5在教師表中增加住址列。 ALTER TABLE TEACHERS ADD (ADDR CHAR(50) 例 6把 STUDENTS 表中的 BPLACE 列刪除，并且把引用 BPLACE 列的所有視圖和約束也一起刪除。 ALTER TABLE STUDENTS DROP BPLACE CASCADE 例 7補(bǔ)充定義 ENROLLS 表的主關(guān)鍵字。 ALTER TABLE ENROLLS ADD PRIMARY KEY (SNO,CNO) ； 視圖操作（虛表） 例 9建立一個(gè)只包括教師號(hào)、姓名和年齡的視圖 FACULTY 。 ( 在視圖定義中不能包含 ORDER

4、BY 子句 ) CREATE VIEW FACULTY AS SELECT TNO, TNAME, AGE FROM TEACHERS 例 10從學(xué)生表、課程表和選課表中產(chǎn)生一個(gè)視圖 GRADE_TABLE ， 它包括學(xué)生姓名、課程名和成績。 CREATE VIEW GRADE_TABLE AS SELECTSNAME,CNAME,GRADE FROMSTUDENTS,COURSES,ENROLLS WHERESTUDENTS.SNO ENROLLS.SNO AND COURSES.CNO=ENROLLS.CNO 例 11刪除視圖 GRADE_TABLE DROP VIEW GRADE_TAB

5、LE RESTRICT 索引操作 例 12在學(xué)生表中按學(xué)號(hào)建立索引。 CREATE UNIQUE INDEX ST ON STUDENTS (SNO,ASC) 例 13刪除按學(xué)號(hào)所建立的索引。 DROP INDEX ST 數(shù)據(jù)庫模式操作 例 14創(chuàng)建一個(gè)簡易教學(xué)數(shù)據(jù)庫的數(shù)據(jù)庫模式 TEACHING_DB ，屬主為 ZHANG 。 CREATE SCHEMA TEACHING_DB AUTHRIZATION ZHANG 例 15刪除簡易教學(xué)數(shù)據(jù)庫模式 TEACHING_DB 。（ 1 ）選用 CASCADE ，即當(dāng)刪除數(shù)據(jù)庫模式時(shí)，則本數(shù)據(jù)庫模式和其下屬的基本表、視圖、索引等全部被刪除。（ 2

6、）選用 RESTRICT ，即本數(shù)據(jù)庫模式下屬的基本表、視圖、索引等事先已清除，才能刪除本數(shù)據(jù)庫模式，否則拒絕刪除。） DROP SCHEMA TEACHING_DB CASCADE 單表操作 例 16找出 3 個(gè)學(xué)分的課程號(hào)和課程名。 SELECT CNO, CNAME FROM COURSES WHERE CREDIT 3 例 17查詢年齡大于 22 歲的學(xué)生情況。 SELECT* FROM STUDENTS WHEREAGE 22 例 18 找出籍貫為河北的男生的姓名和年齡。 SELECT SNAME, AGE FROM STUDENTS WHERE BPLACE 河北 ANDSEX 男

7、 例 19找出年齡在 20 23 歲之間的學(xué)生的學(xué)號(hào)、姓名和年齡，并按年齡升序排序。 (ASC （升序）或 DESC （降序）聲明排序的方式，缺省為升序。 ) SELECT SNO, SNAME, AGE FROM STUDENTS WHEREAGE BETWEEN 20 AND 23 ORDERBY AGE 例 20找出年齡小于 23 歲、籍貫是湖南或湖北的學(xué)生的姓名和性別。（條件比較運(yùn)算符、 和邏輯運(yùn)算符 AND （與），此外還可以使用的運(yùn)算符有：（大于）、（大于等于）、（小于等于）、（不等于）、 NOT （非）、 OR （或）等。 謂詞 LIKE 只能與字符串聯(lián)用，常常是 “ 列名 LI

8、KEpattern” 的格式。特殊字符 “_” 和 “%” 作為通配符。 謂詞 IN 表示指定的屬性應(yīng)與后面的集合（括號(hào)中的值集或某個(gè)查詢子句的結(jié)果）中的某個(gè)值相匹配，實(shí)際上是一系列的 OR （或）的縮寫。謂詞 NOT IN 表示指定的屬性不與后面的集合中的某個(gè)值相匹配。 謂詞 BETWEEN 是 “ 包含于 之中 ” 的意思。） SELECT SNAME, SEX FROM STUDENTS WHEREAGE 23ANDBPLACELIKE 湖 或 SELECT SNAME, SEX FROM STUDENTS WHEREAGE 23ANDBPLACEIN（ 湖南 ， 湖北 ） 例 22找出

9、學(xué)生表中籍貫是空值的學(xué)生的姓名和性別。（在 SQL 中不能使用條件：列名 NULL 。在 SQL 中只有一個(gè)特殊的查詢條件允許查詢 NULL 值：） SELECT SNAME, SEX FROM STUDENTS WHEREBPLACE IS NULL 多表操作 例 23找出成績?yōu)?95 分的學(xué)生的姓名。（子查詢） SELECT SNAME FROM STUDENTS WHERE SNO (SELECT SNO FROM ENROLLS WHEREGRADE 95) 例 24找出成績?cè)?90 分以上的學(xué)生的姓名。 SELECTSNAME FROM STUDENTS WHERESNO IN (S

10、ELECT SNO FROM ENROLLS WHERE GRADE 90) 或 SELECTSNAME FROM STUDENTS WHERESNO ANY (SELECT SNO FROM ENROLLS WHERE GRADE 90) 例 25查詢?nèi)繉W(xué)生的學(xué)生名和所學(xué)課程號(hào)及成績。（連接查詢） SELECTSNAME, CNO, GRADE FROM STUDENTS, ENROLLS WHERESTUDENTS.SNO ENROLLS.SNO 例 26找出籍貫為山西或河北，成績?yōu)?90 分以上的學(xué)生的姓名、籍貫和成績。（當(dāng)構(gòu)造多表連接查詢命令時(shí)，必須遵循兩條規(guī)則。第一，連接條件數(shù)正好

11、比表數(shù)少 1 （若有三個(gè)表，就有兩個(gè)連接條件 ) ；第二，若一個(gè)表中的主關(guān)鍵字是由多個(gè)列組成，則對(duì)此主關(guān)鍵字中的每一個(gè)列都要有一個(gè)連接條件（也有少數(shù)例外情況） SELECTSNAME, BPLACE, GRADE FROM STUDENTS, ENROLLS WHEREBPLACE IN ( 山西 ， 河北 )ANDGRADE 90AND STUDENTS.SNO=ENROLLS.SNO 例 28查出課程成績?cè)?80 分以上的女學(xué)生的姓名、課程名和成績。（ FROM 子句中的子查詢） SELECTSNAME,CNAME, GRADE FROM (SELECT SNAME, CNAME , GR

12、ADE FROMSTUDENTS, ENROLLS,COURSES WHERESEX 女 ) AS TEMP (SNAME, CNAME,GRADE) WHEREGRADE 80 表達(dá)式與函數(shù)的使用 例 29查詢各課程的學(xué)時(shí)數(shù)。（算術(shù)表達(dá)式由算術(shù)運(yùn)算符、 * 、與列名或數(shù)值常量所組成。） SELECTCNAME,COURSE_TIME CREDIT*16 FROM COURSES 例 30找出教師的最小年齡。（內(nèi)部函數(shù)： SQL 標(biāo)準(zhǔn)中只使用 COUNT 、 SUM 、 AVG 、 MAX 、 MIN 函數(shù)，稱之為聚集函數(shù)（ Set Function ）。 COUNT 函數(shù)的結(jié)果是該列統(tǒng)計(jì)值的

13、總數(shù)目， SUM 函數(shù)求該列統(tǒng)計(jì)值之和， AVG 函數(shù)求該列統(tǒng)計(jì)值之平均值， MAX 函數(shù)求該列最大值， MIN 函數(shù)求該列最小值。） SELECTMIN(AGE) FROM TEACHERS 例 31統(tǒng)計(jì)年齡小于等于 22 歲的學(xué)生人數(shù)。（統(tǒng)計(jì)） SELECTCOUNT(*) FROM STUDENTS WHEREAGE = 3 相關(guān)子查詢 例 37查詢沒有選任何課程的學(xué)生的學(xué)號(hào)和姓名。（當(dāng)一個(gè)子查詢涉及到一個(gè)來自外部查詢的列時(shí)，稱為相關(guān)子查詢（ Correlated Subquery) 。相關(guān)子查詢要用到存在測(cè)試謂詞 EXISTS 和 NOT EXISTS ，以及 ALL 、 ANY （

14、SOME ）等。） SELECT SNO, SNAME FROM STUDENTS WHERENOT EXISTS (SELECT * FROM ENROLLS WHERE ENROLLS.SNO=STUDENTS.SNO) 例 38 查詢哪些課程只有男生選讀。 SELECT DISTINCT CNAME FROM COURSES C WHERE 男 ALL (SELECT SEX FROMENROLLS ， STUDENTS WHERE ENROLLS.SNO=STUDENTS.SNO AND ENROLLS.CNO=C.CNO) 例 39要求給出一張學(xué)生、籍貫列表，該表中的學(xué)生的籍貫省份，

15、也是其他一些學(xué)生的籍貫省份。 SELECTSNAME, BPLACE FROM STUDENTS A WHEREEXISTS (SELECT * FROM STUDENTS B WHERE A.BPLACE=B.BPLACE AND A.SNO B.SNO) 例 40找出選修了全部課程的學(xué)生的姓名。 本查詢可以改為：查詢這樣一些學(xué)生，沒有一門課程是他不選修的。 SELECTSNAME FROM STUDENTS WHERENOT EXISTS (SELECT * FROM COURSES WHERENOT EXISTS (SELECT * FROM ENROLLS WHERE ENROLLS.

16、SNO STUDENTS.SNO AND ENROLLS.CNO COURSES.CNO) 關(guān)系代數(shù)運(yùn)算 例 41設(shè)有某商場(chǎng)工作人員的兩張表：營業(yè)員表 SP_SUBORD 和營銷經(jīng)理表 SP_MGR ，其關(guān)系數(shù)據(jù)模式如下： SP_SUBORD (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE) SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE) 其中，屬性 SALPERS_ID 為工作人員的編號(hào) , SALPERS_NAME 為工作人員的姓名 , MANAGER_ID 為所在部門經(jīng)理的編號(hào) , O

17、FFICE 為工作地點(diǎn)。 若查詢?nèi)可虉?chǎng)工作人員，可以用下面的 SQL 語句： (SELECT * FROM SP_SUBORD) UNION (SELECT * FROM SP_MGR) 或等價(jià)地用下面的 SQL 語句： SELECT * FROM (TABLE SP_SUBORD UNION TABLE SP_MGR) （ 2 ） INTERSECT (SELECT * FROM SP_SUBORD) INTERSECT (SELECT * FROM SP_MGR) 或等價(jià)地用下面的 SQL 語句： SELECT * FROM (TABLE SP_SUBORD INTERSECT TABL

18、E SP_MGR) 或用帶 ALL 的 SQL 語句： (SELECT * FROM SP_SUBORD) INTERSECT ALL (SELECT * FROM SP_MGR) 或 SELECT * FROM (TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR) （ 3 ） EXCEPT (SELECT * FROM SP_MGR) EXCEPT (SELECT * FROM SP_SUBORD) 或等價(jià)地用下面的 SQL 語句： SELECT * FROM (TABLE SP_MGR EXCEPT TABLE SP_ SUBORD) 或用帶 ALL 的

19、 SQL 語句： (SELECT * FROM SP_MGR) EXCEPTALL (SELECT * FROM SP_SUBORD) 例 42查詢籍貫為四川、課程成績?cè)?80 分以上的學(xué)生信息及其成績。（自然連接） (SELECT * FROM STUDENTS WHERE BPLACE= 四川 ) NATURAL JOIN (SELECT * FROM ENROLLS WHERE GRADE =80) 例3.43 列出全部教師的姓名及其任課的課程號(hào)、班級(jí)。 （外連接與外部并外連接允許在結(jié)果表中保留非匹配元組，空缺部分填以 NULL 。外連接的作用是在做連接操作時(shí)避免丟失信息。 外連接有 3

20、 類： （ 1 ）左外連接（ Left Outer Join ）。連接運(yùn)算謂詞為 LEFT OUTER JOIN ，其結(jié)果表中保留左關(guān)系的所有元組。 （ 2 ）右外連接（ Right Outer Join ）。連接運(yùn)算謂詞為 RIGHT OUTER JOIN ，其結(jié)果表中保留右關(guān)系的所有元組。 （ 3 ）全外連接（ Full Outer Join ）。連接運(yùn)算謂詞為 FULL OUTER JOIN ，其結(jié)果表中保留左右兩關(guān)系的所有元組。） SELECT TNAME, CNO, CLASS FROM TEACHERS LEFT OUTER JOIN TEACHING USING (TNO) SQ

21、L 的數(shù)據(jù)操縱 例 44把教師李映雪的記錄加入到教師表 TEACHERS 中。（插入） INSERT INTO TEACHERS VALUES(1476 ， 李映雪 ， 44 ， 副教授 ) 例 45成績優(yōu)秀的學(xué)生將留下當(dāng)教師。 INSERT INTO TEACHERS (TNO ， TNAME) SELECT DISTINCT SNO ， SNAME FROM STUDENTS ， ENROLLS WHERE STUDENTS.SNO ENROLLS.SNO AND GRADE 90 例 47把所有學(xué)生的年齡增加一歲。（修改） UPDATE STUDENTS SET AGE AGE+1 例

22、48學(xué)生張春明在數(shù)據(jù)庫課考試中作弊，該課成績應(yīng)作零分計(jì)。 UPDATE ENROLLS SET GRADE 0 WHERE CNO C1 AND 張春明 (SELECT SNAME FROM STUDENTS WHERE STUDENTS.SNO=ENROLLS.SNO) 例 49從教師表中刪除年齡已到 60 歲的退休教師的數(shù)據(jù)。（刪除） DELETE FROM TEACHERS WHEREAGE 60 SQL 的數(shù)據(jù)控制 例 50授予 LILI 有對(duì)表 STUDENTS 的查詢權(quán)。（表視圖特權(quán)的授予 一個(gè) SQL 特權(quán)允許一個(gè)被授權(quán)者在給定的數(shù)據(jù)庫對(duì)象上進(jìn)行特定的操作。授權(quán)操作的數(shù)據(jù)庫對(duì)象包

23、括：表 / 視圖、列、域等。授權(quán)的操作包括： INSERT 、 UPDATE 、 DELETE 、 SELECT 、 REFERENCES 、 TRIGGER 、 UNDER 、 USAGE 、 EXECUTE 等。其中 INSERT 、 UPDATE 、 DELETE 、 SELECT 、 REFERENCES 、 TRIGGER 有對(duì)表做相應(yīng)操作的權(quán)限，故稱為表特權(quán)。） GRANT SELECT ON STUDENTS TO LILI WITH GRANT OPTION 例 51取消 LILI 的存取 STUDENTS 表的特權(quán)。 REVOKE ALL ON STUDENTS FROM L

24、ILI CASCADE 不斷補(bǔ)充中：1. 模糊查找:它判斷列值是否與指定的字符串格式相匹配。可用于char、varchar、text、ntext、datetime和smalldatetime等類型查詢。 可使用以下通配字符： 百分號(hào)%：可匹配任意類型和長度的字符，如果是中文，請(qǐng)使用兩個(gè)百分號(hào)即%。 下劃線_：匹配單個(gè)任意字符，它常用來限制表達(dá)式的字符長度。 方括號(hào)：指定一個(gè)字符、字符串或范圍，要求所匹配對(duì)象為它們中的任一個(gè)。：其取值也 相同，但它要求所匹配對(duì)象為指定字符以外的任一個(gè)字符。 例如： 限制以Publishing結(jié)尾，使用LIKE %Publishing 限制以A開頭：LIKE A%

25、 限制以A開頭外：LIKE A%2.更改表格 ALTERTABLEtable_name ADDCOLUMNcolumn_nameDATATYPE 說明：增加一個(gè)欄位（沒有刪除某個(gè)欄位的語法。) ALTERTABLEtable_name ADDPRIMARYKEY(column_name) 說明：更改表得的定義把某個(gè)欄位設(shè)為主鍵。 ALTERTABLEtable_name DROPPRIMARYKEY(column_name) 說明：把主鍵的定義刪除。 3.group by 在select 語句中可以使用group by 子句將行劃分成較小的組，然后，使用聚組函數(shù)返回每一個(gè)組的匯總信息，另外，可

26、以使用having子句限制返回的結(jié)果集。group by 子句可以將查詢結(jié)果分組，并返回行的匯總信息Oracle 按照group by 子句中指定的表達(dá)式的值分組查詢結(jié)果。在帶有g(shù)roup by 子句的查詢語句中，在select 列表中指定的列要么是group by 子句中指定的列，要么包含聚組函數(shù)select max(sal),job emp group by job;(注意max(sal),job的job并非一定要出現(xiàn)，但有意義)查詢語句的select 和group by ,having 子句是聚組函數(shù)唯一出現(xiàn)的地方，在where 子句中不能使用聚組函數(shù)。select deptno,sum

27、(sal) from emp where sal1200 group by deptno having sum(sal)8500 order by deptno;當(dāng)在gropu by 子句中使用having 子句時(shí)，查詢結(jié)果中只返回滿足having條件的組。在一個(gè)sql語句中可以有where子句和having子句。having 與where 子句類似，均用于設(shè)置限定條件where 子句的作用是在對(duì)查詢結(jié)果進(jìn)行分組前，將不符合where條件的行去掉，即在分組之前過濾數(shù)據(jù)，條件中不能包含聚組函數(shù)，使用where條件顯示特定的行。having 子句的作用是篩選滿足條件的組，即在分組之后過濾數(shù)據(jù)，條件

28、中經(jīng)常包含聚組函數(shù)，使用having 條件顯示特定的組，也可以使用多個(gè)分組標(biāo)準(zhǔn)進(jìn)行分組。查詢每個(gè)部門的每種職位的雇員數(shù)select deptno,job,count(*) from emp group by deptno,job;4.外連接與內(nèi)連接 有時(shí)候，即使在連接的表中沒有相應(yīng)的行，用戶可能想從一張表中看數(shù)據(jù)，Oracle提供了外連接實(shí)現(xiàn)該功能。內(nèi)連接是指連接查詢只顯示完全滿足連接條件的記錄，即等值連接，外連接的查詢結(jié)果是內(nèi)連接查詢結(jié)果的擴(kuò)展。外連接不僅返回滿足連接條件的所有記錄而且也返回了一個(gè)表中那些在另一個(gè)表中沒有匹配行的記錄。外連接的操作符是“+”?！?”號(hào)放在連接條件中信息不完全的

29、那一邊（即沒有相應(yīng)行的那一邊）。運(yùn)算符“+”影響NULL行的建立。建一行或多行NULL來匹配連接的表中信息完全的行。外連接運(yùn)算符“+”只能出現(xiàn)在where子句中表達(dá)式的一邊。假如在多張表之間有多個(gè)連接條件，外連接運(yùn)算符不能使用or,in邏輯運(yùn)算符與其它條件組合。假如emp表中deptno=10的ename為空值，dept表中deptno=20的loc為空值：1.select ename,dept.deptno,loc from emp,dept where emp.depno(+)=dept.deptno;如果在dept.deptno中有的數(shù)值在emp.deptno中沒有值，則在做外連接時(shí)，結(jié)果中ename會(huì)產(chǎn)生一個(gè)空值。(emp.deptno=10)2.select ename,dept.deptno,loc from emp,dept where emp.depno=dept.deptno(+);如果在emp.deptno中有的數(shù)值在dept.deptno中沒有值，則在做外連接時(shí)，