2021年SQL語句大全實例

1、SQL語句實例歐陽光明(2021.03. 07)表操作例1對于表的教學(xué)管理數(shù)據(jù)庫中的表STUDENTS，可以定義如下：CREATE TABLE STUDENTS(SNO NUMERIC (6, 0) NOT NULLSNAME CHAR (8) NOT NULLAGE NUMERIC(3,0)SEX CHAR(2)BPLACE CHAR(20)PRIMARY KEY(SNO)例2對于表的教學(xué)管理數(shù)據(jù)庫中的表ENROLLS ,可以定義如 下：CREATE TABLE ENROLLS(SNO NUMERIC(6,0) NOT NULLCNO CHAR(4) NOT NULLGRADE INTPRI

2、MARY KEY(SNO,CNO)FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO)FOREIGN KEY(CNO) REFERENCES COURSES(CNO)*歐陽光明*創(chuàng)編2021.03.07CHECK (GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100)2021.03.07例3根據(jù)表的STUDENTS表，建立一個只包含學(xué)號.姓名.年齡的女 學(xué)生表。CREATE TABLE GIRLAS SELECT SNO, SNAME, AGEFROM STUDENTSWHERE SEX二女；例4刪除教師表TEACHERoDROP

3、 TABLE TEACHER例5在教師表中增加住址列。ALTER TABLE TEACHERSADD (ADDR CHAR(50)例6把STUDENTS表中的BPLACE列刪除，并且把引用BPLACE列的所有視圖和約束也一起刪除。ALTER TABLE STUDENTSDROP BPLACE CASCADE例7補充定義ENROLLS表的主關(guān)鍵字。ALTER TABLE ENROLLSADD PRIMARY KEY (SNO,CNO);視圖操作（虛表）例9建立一個只包括教師號.姓名和年齡的視圖FACULTYo（在視圖定義中不能包含ORDER BY子句）*歐陽光明*創(chuàng)編2021.03.07CREA

4、TE VIEW FACULTYAS SELECT TNO, TNAME, AGEFROM TEACHERS例10從學(xué)生表.課程表和選課表中產(chǎn)生一個視圖GRADE_TABLE,S 包括學(xué)生姓名.課程名和成績。CREATE VIEW GRADE_TABLEAS SELECT SNAME,CNAME,GRADEFROM STUDENTS,COURSES,ENROLLSWHERE STUDENTS.SNO = ENROLLS.SNO ANDCOURSES.CNO二ENROLLS.CNO例11刪除視圖GRADE_TABLEDROP VIEW GRADE_TABLE RESTRICT 索引操作例12在學(xué)生

5、表中按學(xué)號建立索引。CREATE UNIQUE INDEX STON STUDENTS (SNO,ASC)例13刪除按學(xué)號所建立的索引。DROP INDEX ST數(shù)據(jù)庫模式操作例14創(chuàng)建一個簡易教學(xué)數(shù)據(jù)庫的數(shù)據(jù)庫模式TEACHING.DB ,屬主為ZHANGoCREATE SCHEMATEACHING_DB AUTHRIZATION ZHANG*歐陽光明*創(chuàng)編2021.03.07例15刪除簡易教學(xué)數(shù)據(jù)庫模式TEACHING_DB。（ （1）選用 CASCADE ,即當(dāng)刪除數(shù)據(jù)庫模式時，則本數(shù)據(jù)庫模式和其下屬的基 本表.視圖.索引等全部被刪除。（2）選用RESTRICTED本數(shù)據(jù)庫 模式下屬的基

6、本表.視圖.索引等事先己清除，才能刪除本數(shù)據(jù)庫模 式，否則拒絕刪除。）DROP SCHEMA TEACHING_DB CASCADE單表操作例16找出3個學(xué)分的課程號和課程名。SELECT CNO, CNAMEFROM COURSESWHERE CREDIT = 3例17查詢年齡大于22歲的學(xué)生情況。SELECT *FROM STUDENTSWHERE AGE 22例18找出籍貫為河北的男生的姓名和年齡。SELECT SNAME, AGEFROM STUDENTSWHERE BPLACE =河北AND SEX 工男例19找出年齡在20 23歲之間的學(xué)生的學(xué)號、姓名和年齡，并按年齡升序排序。（A

7、SC （升序）或DESC （降序）聲明排序的方式，缺省為升序。）SELECT SNO, SNAME, AGE*歐陽光明*創(chuàng)編FROM STUDENTSWHERE AGE BETWEEN 20 AND 23ORDER BY AGE例20找出年齡小于23歲.籍貫是湖南或湖北的學(xué)生的姓名和性另I。（條件比較運算符V和邏輯運算符AND （與），此外還 可以使用的 運算符有： （大于）. =（大于等于）. =（小于等于）.（不等于）.NOT （非）.OR （或）等。謂詞LIKE只能與字符串聯(lián)用，常常是“V列名 LIKE pattern-的格式。特殊字符和“”作為通配符。謂詞IN表示指定的屬性應(yīng)與后面的集

8、合（括號中的值集或某個查詢 子句的結(jié)果）中的某個值相匹配，實際上是一系列的OR （或）的縮寫。謂詞NOT IN表示指定的屬性不與后面的集合中的某個勺盲相匹配。謂詞BETWEEN是“包含于之中”的意思。）SELECT SNAME, SEXFROM STUDENTSWHERE AGE 23 AND BPLACE LIKE湖 或SELECT SNAME, SEXFROM STUDENTSWHERE AGE 90)SELECT SNAME2021.03.07*歐陽光明*創(chuàng)編2021.03.07*歐陽光明*創(chuàng)編2021.03.07FROM STUDENTSWHERE SNO = ANY（SELECT S

9、NOFROM ENROLLSWHERE GRADE 90 ）例25查詢?nèi)繉W(xué)生的學(xué)生名和所學(xué)課程號及成績。（連接查詢）SELECT SNAME, CNO, GRADEFROM STUDENTS, ENROLLSWHERE STUDENTS.SNO = ENROLLS.SNO例26找出籍貫為山西或河北，成績?yōu)?0分以上的學(xué)生的姓名.籍貫 和成績。（當(dāng)構(gòu)造多表連接查詢命令時，必須遵循兩條規(guī)則。第 一，連接條件數(shù)正好比表數(shù)少1 （若有三個表，就有兩個連接條 件）；第二，若一個表中的主關(guān)鍵字是由多個列組成，則對此主關(guān) 鍵字中的每一個列都要有一個連接條件（也有少數(shù)例外情況）SELECT SNAME, B

10、PLACE, GRADEFROM STUDENTS, ENROLLSWHERE BPLACE IN C 山西 5 , 4 河北 J AND GRADE =90 AND STUDENTS.SNO二ENROLLS.SNO例28查出課程成績在80分以上的女學(xué)生的姓名.課程名和成績。（FROM子句中的子查詢）SELECT SNAME,CNAME, GRADEFROM (SELECT SNAME, CNAME , GRADEFROM STUDENTS, ENROLLS,COURSES*歐陽光明*創(chuàng)編2021.03.07*歐陽光明*創(chuàng)編WHERE SEX =女)AS TEMP (SNAME, CNAME,

11、GRADE)WHERE GRADE 80表達(dá)式與函數(shù)的使用例29查詢各課程的學(xué)時數(shù)。(算術(shù)表達(dá)式宙算術(shù)運算符+ ./與列名或數(shù)值常量所組成。)SELECT CNAME,COURSE_TIME = CREDIT* 16FROM COURSES例30找出教師的最小年齡。(內(nèi)部函數(shù)：SQL標(biāo)準(zhǔn)中只使用COUNT. SUM、AVG. MAX. MIN函數(shù)，稱之為聚集函數(shù)(Set Function )。COUNT函數(shù)的結(jié)果是該列統(tǒng)計值的總數(shù)目，SUM函數(shù)求該列統(tǒng)計值之和，AVG函數(shù)求該列統(tǒng)計值之平均值,MAX函數(shù)求該列最大值，MIN函數(shù)求該列最小值。)SELECT MIN(AGE)FROM TEACHE

12、RS例31統(tǒng)計年齡小于等于22歲的學(xué)生人數(shù)。(統(tǒng)計)SELECT COUNT(*)FROM STUDENTSWHERE AGE (SELECT AVG(AGE)FROM STUDENTS)例35找出各課程的平均成績，按課程號分組，且只選擇學(xué)生超過3人的課程的成績。(GROUP BY與HAVINGGROUP BY子句把一個表按某一指定列(或一些歹U)上的值 相等 的原則分組，然后再對每組數(shù)據(jù)進(jìn)行規(guī)定的操作。GROUP BY子句總是跟在WHERE子句后面，當(dāng)WHERE子 句缺省時，它跟在FROM子句后面。having子句常用于在計算出聚集之后對行的查詢進(jìn)行控制。)SELECT CNO, AVG(G

13、RADE), STUDENTS = COUNT(*)FROM ENROLLSGROUP BY CNOHAVING COUNTS) = 3相關(guān)子查詢例37查詢沒有選任何課程的學(xué)生的學(xué)號和姓名。(當(dāng)一個子查詢涉及 到一個來自外部查詢的列肘，稱為相關(guān)子查詢(Correlated Subquery)o相關(guān)子查詢要用到存在測試謂詞EXISTS和NOT EXISTS似及ALL . ANY ( SOME )等。)2021.03.07*歐陽光明*創(chuàng)編2021.03.07*歐陽光明*創(chuàng)編SELECT SNO, SNAMEFROM STUDENTSWHERE NOT EXISTS(SELECT *FROM ENR

14、OLLSWHERE ENROLLS.SNO二STUDENTS.SNO)例38查詢哪些課程只有男生選讀。SELECT DISTINCT CNAMEFROM COURSES CWHERE 男=ALL(SELECT SEXFROM ENROLLS , STUDENTSWHERE ENROLLS.SNO 二 STUDENTS.SNO ANDENROLLS. CNO 二 C.CNO)例39要求給出一張學(xué)生、籍貫列表，該表中的學(xué)生的籍貫省份，也是 其他一些學(xué)生的籍貫省份。SELECT SNAME, BPLACEFROM STUDENTS AWHERE EXISTS(SELECT 睿FROM STUDENT

15、S BWHERE A.BPLACE 二 B.BPLACE ANDA.SNOoB.SNO)水歐陽光明*創(chuàng)編2021.03.07*歐陽光明*創(chuàng)編2021.03.07例40找出選修了全部課程的學(xué)生的姓名。本查詢可以改為：查詢這樣一些學(xué)生，沒有一門課程杲他不選 修的。SELECT SNAMEFROM STUDENTSWHERE NOT EXISTS(SELECT 睿FROM COURSESWHERE NOT EXISTS(SELECT *FROM ENROLLSWHERE ENROLLS.SNO = STUDENTS.SNOAND ENROLLS.CNO = COURSES.CNO)關(guān)系代數(shù)運算例41

16、設(shè)有某克場工作人員的兩張表：營業(yè)員表SP.SUBORD和營銷 經(jīng)理表SP_MGR ,其關(guān)系數(shù)據(jù)模式如下：SP_SUBORD (SALPERSJD, SALPERS_NAME, MANAGERJD, OFFICE)SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGERJD, OFFICE)其中，屬性SALPERSJD為工作人員的編號，SALPERS.NAME為工作人員的姓名，MANAGERJD為所在部門 經(jīng)理的編號，OFFICE為工作地點。*歐陽光明*創(chuàng)編2021.03.07*歐陽光明*創(chuàng)編2021.03.07若查詢?nèi)可虉龉ぷ魅藛T，可以用下面的SQL語句：(SELE

17、CT * FROM SP_SUBORD)UNION(SELECT * FROM SP_MGR)或等價地用下賄的SQL語句：SELECT *FROM (TABLE SP_SUBORD UNION TABLE SP_MGR)(2 ) INTERSECT(SELECT * FROM SP_SUBORD)INTERSECT(SELECT * FROM SP_MGR)或等價地用下賄的SQL語句：SELECT *FROM (TABLE SP_SUBORD INTERSECT TABLE SP_MGR)或用帶ALL的SQL語句：(SELECT * FROM SP_SUBORD)INTERSECT ALL(S

18、ELECT * FROM SP_MGR)或SELECT *FROM (TABLE SP_SUBORD INTERSECT ALL TABLESP_MGR)(3) (3) EXCEPT2021.03.07*歐陽光明*創(chuàng)編2021.03.07*歐陽光明*創(chuàng)編(SELECT * FROM SP_MGR)EXCEPT(SELECT * FROM SP.SUBORD)或等價地用下直的SQL語句：SELECT *FROM (TABLE SP_MGR EXCEPT TABLE SP_ SUBORD)或用帶ALL的SQL語句：(SELECT * FROM SP_MGR)EXCEPT ALL(SELECT *

19、FROM SP_SUBORD)例42查詢籍貫為四1 Ik課程成績在80分以上的學(xué)生信息及其成績。(自然連接)(SELECT * FROM STUDENTSWHERE BPLACEJ 四川 JNATURAL JOIN(SELECT * FROM ENROLLSWHERE GRADE =80)例3.43列出全部教師的姓名及其任課的課程號.班級。(外連接與外部并外連接允許在結(jié)果表中保留非匹配元組，空缺部 分填以NULL。外連接的作用是在做連接操作時避免丟失信息。外連接有3類：(1 )左外連接(Left Outer Join ) o連接運算謂詞為LEFT OUTERJOIN，其結(jié)果表中保留左關(guān)系的所有

20、元組。*歐陽光明*創(chuàng)編2021.03.07(2 )右外連接(Right Outer Join ) o連接運算謂詞為RIGHT OUTER JOIN，其結(jié)果表中保留右關(guān)系的所有元組。(3 )全外連接(Full Outer Join ) o連接運算謂詞為FULL OUTERJOIN，其結(jié)果表中保留左右兩關(guān)系的所有元組。)SELECT TNAME, CNO, CLASSFROM TEACHERS LEFT OUTER JOIN TEACHING USING(TNO)SQL的數(shù)據(jù)操縱例44把教師李映雪的記錄加入到教師表TEACHERS中。(插 入)INSERT INTO TEACHERSVALUES(

21、1476,李映雪，44,副教授)例45成績優(yōu)秀的學(xué)生將留下當(dāng)教師。INSERT INTO TEACHERS (TNO , TNAME)SELECT DISTINCT SNO , SNAMEFROM STUDENTS , ENROLLSWHERE STUDENTS.SNO = ENROLLS.SNO AND GRADE 二90例47把所有學(xué)生的年齡增加一歲。(修改)UPDATE STUDENTSSET AGE = AGE+1例48學(xué)生張春明在數(shù)據(jù)庫課考試中作弊，該課成績應(yīng)作零分計。UPDATE ENROLLS*歐陽光明*創(chuàng)編SET GRADE = 0WHERE CNO = *Cr AND張春明1

22、 =（SELECT SNAMEFROM STUDENTSWHERE STUDENTS.SNO 二 ENROLLS.SNO）例49從教師表中刪除年齡已到60歲的退休教師的數(shù)據(jù)。（刪除）DELETE FROM TEACHERSWHERE AGE = 60SQL的數(shù)據(jù)控制例50授予LILI有對表STUDENTS的查詢權(quán)。（表/視圖特權(quán)的 授 予一個SQL特權(quán)允許一個被授權(quán)者在給定的數(shù)據(jù)庫對象上進(jìn)行特定的操作。授權(quán)操作的數(shù)據(jù)庫對象包括：表/視圖、歹U、域等。授權(quán)的操作包括：INSERT、UPDATE、 DELETE、SELECT , REFERENCES . TRIGGER , UNDER ,USAG

23、E , EXECUTE 等。其中 INSERT , UPDATE ,DELETE , SELECT . REFERENCES . TRIGGER 有對表做相 應(yīng)操作的權(quán)限，故稱為表特權(quán)。）GRANT SELECT ON STUDENTSTO LILIWITH GRANT OPTION水歐陽光明*創(chuàng)編 例51取消LILI的存取STUDENTS表的特權(quán)。REVOKE ALLON STUDENTSFROM LILI CASCADE不斷補充中：1.模糊查找:它判斷列值是否與指定的字符串格式相匹 配?？捎糜?char、varchar、text、ntext、datetime A0 smalldatetim

24、e 等類 型查詢?？墒褂靡韵峦ㄅ渥址喊俜痔?可匹配任意類型和長度的字符，如果是中文，請使用兩個百分號即%O下 劃線匹配單個任意字符，它常用來限制表達(dá)式的字符長度。方括號 :指定一個字符、字符串或范圍，要求所匹配對象為它們中的任一 個。F：其取值也門相同，但它要求所匹配對象為指定字符以外的任 一個字符。例如：限制以Publishing結(jié)尾，使用LIKE APublishing限 制以A開頭：LIKE A%,限制以A開頭外：LIKEFA%2更改表 格 ALTER TABLE table_name ADD COLUMN column_n ame DATATYPE說明：增加一個欄位(沒有刪除某個欄位

25、的 語 法。)ALTER TABLE table_name ADD PRIMARY KEY (co lumn_name)說明：更改表得的定義把某個欄位設(shè)為主 鍵。ALTER TABLE DROP PRIMARY KEY (c olumn_name)說明：把主 鍵的定義刪除。3.group by在select語句中可以使用group by子句將行劃分成較小的組，然 后， 使用聚組函數(shù)返回每一個組的匯總信息，號外，可以使用having子句 限制返回的結(jié)果集。group by子句可以將查詢結(jié)果分 組，并返回行的 匯總信息Oracle按照group by子句中指定的表達(dá)式 的值分

26、組查詢結(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(sal) from emp where sal1200 group by deptno having sum(sal)8500 order by dept

27、no;當(dāng)在gropu by子句中使用having子句時，查詢結(jié)果中只返回滿足 having條件的組。在一個sql語句中可以有where子句和having子 句。having與where子句類似，均用于設(shè)置限定條件where子句的 作 用是在對查詢結(jié)果進(jìn)行分組前，將不符合where條件的行去掉，即在 分組之前過濾數(shù)據(jù)，條件中不能包含聚組函數(shù)，使用where條件顯示 特定的行。having子句的作用是篩選滿足條件的組，即在分組之后過 濾數(shù)據(jù)，條件中經(jīng)常包含聚組函數(shù)，使用having條件顯示特定的 組，也可以使用多個分組標(biāo)準(zhǔn)進(jìn)行分組。查詢每個部門的每種職位的雇員數(shù)select deptno,job,

28、count(*) from empgroup by deptno,job;2021.03.07*歐陽光明*創(chuàng)編2021.03.074外連接與內(nèi)連接有時候，即使在連接的表中沒有相應(yīng)的行，用戶可能想從一張表中看 數(shù)據(jù)，Oracle提供了外連接實現(xiàn)該功能。內(nèi)連接是指連接查詢只顯示 完全滿足連接條件的記錄，即等值連接，外連接的查詢結(jié)果是內(nèi)連接 查詢結(jié)果的擴(kuò)展。外連接不僅返回滿足連接條件的所有記錄而且也返 回了一個表中那些在號一個表中沒有匹配行的記錄。外連接的操作符 是“+二“+”號放在連接條件中信息不完全的那一邊(即沒有相應(yīng)行 的那一邊)。運算符“+”影響NULL行的建立。建一行 或多行NULL 來匹

29、配連接的表中信息完全的行。外連接運算符只能出現(xiàn)在where子句中表達(dá)式的一邊。假如在多張表之間有多個連接條件，外連接運算符不能使用or,in邏 輯運算符與其它條件組合。假如 emp 表中 deptno=10 的 ename 為 空值，dept 表中 deptno=20 的 loc 為空值：select ename,dept.deptno,loc from emp,dept where emp.depno(+)=dept.deptno;如果在dept.deptno中有的數(shù)值在emp.deptno中沒有值，則在做外連 接時，結(jié)果中ename會產(chǎn)生一個空值。(emp.deptno=10)select ename,dept.deptno,loc from emp,dept whereemp.depno=dept.deptno(+);如果在emp.deptno中有的數(shù)值在dept.deptno中沒有值，則在做外連 接時，結(jié)果中l(wèi)oc會產(chǎn)生一個空值。（dept.deptno=20） 5