SQL語句大全實例

1、TABLE STUDENTSNUMERIC (6, 0) NOT NULLCHAR (8) NOT NULL NUMERIC(3,0) CHAR(2)CHAR(20)P RIMARY KEY(SNO)例2對于表得教學管理數(shù)據(jù)庫中得表學號3KO3KO姓名SHANESHANE鋅齡AGEAGE性別SEXSEX黠貴B B PLACEPLACE9900219&0S52gsoseaG90S74090573如刖991091張舂明22242224332433SQL語句實例何）學生 STUDENTS課程號課程名字分CNOCNAMECKEB1TC1教培庫3C2數(shù)據(jù)皓構3C3C34 4C4戟件工程3(b)(

2、b)課程C0W5ESC0W5ES斂師號TNOTKAKE年齡AGE職稱FS14201420周毎華3S3S副教授劉逹平3030講師14332020講師數(shù)師1EACHERS1EACHERS課程號飼0 0班級CLASSCLASS教師號THOTHO學生數(shù)SNUMSNUMC18/51142030C2U2033C3囲為143130C3ESJ214S120C4ES51143234C1ES 咒U303g任課TEACHINGTEACHING表操作學號 諜程號成贛SNOCHOCLCL990027C33S0027C4990653CL990652C4C430060C3500674C2900676C3990990閔4 4

3、 C3C3aGoe34CLCL901091C3怨）選課ENROLLSENROLLS例1對于表得教學管理數(shù)據(jù)庫中得表STUDENTS，可以定義如下:CREATE(SNOSNAMEAGESEXBP LACEENROLLS，可以定義如下:TABLE ENROLLSNUMERIC(6,0) NOT NULLCHAR(4) NOT NULLINTP RIMARY KEY(SNO,CNO)FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO)FOREIGN KEY(CNO) REFERENCES COURSES(CNO)CHECK (GRADE IS NULL) OR (GRA

4、DE BETWEEN 0 AND 100)例3根據(jù)表得STUDENTS表，建立一個只包含學號、姓名、年齡得女學生表。CREATE TABLE GIRLCREATE(SNOCNOGRADEAS SELECT SNO, SNAME, AGEFROM STUDENTS女；WHERE SEX=4刪除教師表TEACHER。DROP TABLE TEACHER5在教師表中增加住址列。ALTER TABLE TEACHERSADD (ADDR CHAR(50)6把STUDENTS表中得BP LACE列刪除, 約束也一起刪除。并且把引用BP LACE列得所有視圖與ALTER TABLE STUDENTSDRO

5、P BP LACE CASCADE例7補充定義ENROLLS表得主關鍵字。ALTER TABLE ENROLLSADD P RIMARY KEY (SNO,CNO)視圖操作(虛表)例9建立一個只包括教師號、姓名與年齡得視圖ORDER BY子句)CREATE VIEW FACULTYFACULTY。(在視圖定義中不能包含AS SELECT TNO, TNAME, AGEFROM TEACHERS例10從學生表、課程表與選課表中產(chǎn)生一個視圖 課程名與成績。GRADE_TABLE，它包括學生姓名、CREATE VIEW GRADE TABLEAS SELECT SNAME,CNAME,GRADEFR

6、OM STUDENTS,COURSES,ENROLLSWHERE STUDENTS、SNO=ENROLLS、SNO ANDCOURSES、CNO=ENROLLS、CNO例11刪除視圖GRADE_TABLEDROP VIEW GRADE_TABLE RESTRICT索引操作例12在學生表中按學號建立索引。CREATE UNIQUE INDEX STON STUDENTS (SNO,ASC)例13刪除按學號所建立得索引。DROP INDEX ST數(shù)據(jù)庫模式操作例14創(chuàng)建一個簡易教學數(shù)據(jù)庫得數(shù)據(jù)庫模式TEACHING_DB，屬主為ZHANG。CREATE SCHEMA TEACHING_DB AUT

7、HRIZATION ZHANG例15刪除簡易教學數(shù)據(jù)庫模式TEACHING_DB。（1）選用CASCADE，即當刪除數(shù)據(jù)庫模式時，則本數(shù)據(jù)庫模式與其下屬得基本表、視圖、索引等全部被刪除。（2）選用RESTRICT，即本數(shù)據(jù)庫模式下屬得基本表、視圖、索引等事先已清除，才能刪除 本數(shù)據(jù)庫模式，否則拒絕刪除。）DROP SCHEMA TEACHING_DB CASCADE單表操作16找出3個學分得課程號與課程名。SELECT CNO, CNAMEFROM COURSESWHERE CREDIT=317查詢年齡大于22歲得學生情況。SELECT *FROM STUDENTSWHERE AGE2218找

8、出籍貫為河北得男生得姓名與年齡。SELECT SNAME, AGEFROM STUDENTSWHEREBP LACE=河北AND SEX=男19找出年齡在2023歲之間得學生得學號、姓名與年齡，并按年齡升序排序。（ASC（升序）或DESC（降序）聲明排序得方式，缺省為升序。）SELECT SNO, SNAME, AGEFROM STUDENTSWHERE AGE BETWEEN 20 AND 23ORDER BY AGE例20找出年齡小于23歲、籍貫就是湖南或湖北得學生得姓名與性別。（條件比較運算 符=、 與邏輯運算符AND（與），此外還可以使用得運算符有：于等于）、=（小于等于）、 （不等于

9、）、NOT（非）、OR謂詞LIKE只能與字符串聯(lián)用，常常就是“ 列名LIKE pattern符“_”與“作為通配符。謂詞IN表示指定得屬性應與后面得集合（括號中得值集或某個查詢子句得結果） 個值相匹配，實際上就是一系列得OR（或）后面得集合中得某個值相匹配。謂詞BETWEEN就是 “包含于 之中”SELECT SNAME, SEXFROM STUDENTSWHERE AGE23 AND BP LACE或SELECT SNAME, SEX FROM STUDENTS（大于）、=（大（或）等。得格式。特殊字中得某得縮寫。謂詞NOT IN表示指定得屬性不與得意思。）LIKE湖WHERE AGE=NU

10、LL。在SQL中只有一個特殊得查詢條件允許查詢NULL值：）SELECT SNAME, SEXFROM STUDENTSWHERE BP LACE IS NULL多表操作23找出成績?yōu)?5分得學生得姓名。（子查詢）SELECT SNAME(SELECT SNOFROM ENROLLSWHERE GRADE=95）24找出成績在90分以上得學生得姓名。SELECT SNAMEFROM STUDENTSWHERE SNO IN(SELECT SNOFROM ENROLLSWHERE GRADE90)(SELECT SNOFROM ENROLLSWHERE GRADE90）25查詢?nèi)繉W生得學生名與

11、所學課程號及成績。（連接查詢）SELECT SNAME, CNO, GRADEFROM STUDENTS, ENROLLSWHERE STUDENTS、SNO=26找出籍貫為山西或河北，成績?yōu)?0分以上得學生得姓名、籍貫與成績。（當構造 多表連接查詢命令時，必須遵循兩條規(guī)則。第一，連接條件數(shù)正好比表數(shù)少 表，就有兩個連接條件）；第二，若一個表中得主關鍵字就是由多個列組成，鍵字中得每一個列都要有一個連接條件（也有少數(shù)例外情況）FROMSTUDENTSWHERESNO=SELECTSNAMEFROMSTUDENTSWHERESNO=ANYENROLLS、SNO1（若有三個則對此主關子查詢）SELE

12、CTSNAME, BP LACE, GRADEFROMSTUDENTS, ENROLLSWHEREBP LACE IN ( 山西， 河北)AND GRADE =SNO=ENROLLS、SNOSTUDENTS、例28查出課程成績在80分以上得女學生得姓名、課程名與成績。（90 ANDFROM子句中得SELECT SNAME,CNAME, GRADEFROM (SELECT SNAME, CNAME , GRADEFROM STUDENTS, ENROLLS,COURSES WHERE SEX=女)AS TEMP (SNAME, CNAME,GRADE)WHERE GRADE80表達式與函數(shù)得使用

13、32找出學生得平均成績與所學課程門數(shù)。SELECT SNO, AVG(GRADE), COURSESFROM ENROLLSGRO UP BY SNO34找出年齡超過平均年齡得學生姓名。SELECT SNAMEFROM STUDENTSWHERE AGE(SELECT AVG(AGE)FROM STUDENTS)例35找出各課程得平均成績，按課程號分組，且只選擇學生超過（GROUP BY與HAVINGGRO UP BY子句把一個表按某一指定列（或一些列）上得值相等得原則分組，然后 再對每組數(shù)據(jù)進行規(guī)定得操作。例29查詢各課程得學時數(shù)。（算術表達式由算術運算符+、-量所組成。）SELECT CN

14、AME,COURSE_TIME FROM COURSES例30找出教師得最小年齡。（內(nèi)部函數(shù)：MIN函數(shù)，稱之為聚集函數(shù)（*、/與列名或數(shù)值常CREDIT*16MAX、列統(tǒng)計值得總數(shù)目，SUMMAX函數(shù)求該列最大值，SELECT MIN(AGE)FROM TEACHERS31統(tǒng)計年齡小于等于SELECT COUNT(*)FROM STUDENTSWHERE AGE =SQL標準中只使用Set Function函數(shù)求該列統(tǒng)計值之與，MIN函數(shù)求該列最小值。）22歲得學生人數(shù)。（統(tǒng)計）22COUNT、SUM、AVG、COUNT函數(shù)得結果就是該AVG函數(shù)求該列統(tǒng)計值之平均值，）。COUNT(*)3人

15、得課程得成績。(SELECT *FROM STUDENTS BA、SNO B、SNO)例40找出選修了全部課程得學生得姓名。本查詢可以改為：查詢這樣一些學生，沒有一門課程就是她不選修得。SELECTGROUP BY子句總就是跟在WHERE子句后面，當WHERE子句缺省時，它跟在FROM子句后面。HAVING子句常用于在計算出聚集之后對行得查詢進行控制。)SELECT CNO, AVG(GRADE), STUDENTSFROM ENROLLS=COUNT(*)GRO UP BY CNOHAVING COUNT(*) = 3相關子查詢例37查詢沒有選任何課程得學生得學號與姓名。(當一個子查詢涉及到

16、一個來自外部查 詢得列時，稱為相關子查詢(Correlated Subquery)。相關子查詢要用到存在測試謂詞EXISTS與NOT EXISTS，以及ALL、ANY(SOME)等。)SELECT SNO, SNAMEFROM STUDENTSWHERE NOT EXISTS(SELECT *FROM ENROLLSWHERE ENROLLS、SNO=STUDENTS例38查詢哪些課程只有男生選讀。、SNO)SELECT DISTINCT CNAMEFROM COURSESCWHERE 男=ALL(SELECT SEXFROM ENROLLSSTUDENTSWHERE ENROLLS、SNO=

17、STUDENTS、SNO ANDENROLLS、CNO=C例39要求給出一張學生、籍貫列表, 籍貫省份。、CNO)該表中得學生得籍貫省份，也就是其她一些學生得SELECTSNAME, BP LACEFROMSTUDENTS AWHEREEXISTSWHERE A、BPLACE=B、BP LACE ANDSNAME(SELECT *FROM COURSESWHERE NOT EXISTS(SELECT *FROM ENROLLSWHERE ENROLLS、SNO=STUDENTS、SNOAND ENROLLS、CNO=COURSES、CNO)關系代數(shù)運算例41設有某商場工作人員得兩張表：營業(yè)員表

18、SP_MGR，其關系數(shù)據(jù)模式如下：SP_SUBORD (SAL PERS_ID, SAL PERS_NAME, MANAGER_ID, OFFICE)SP_MGR (SAL PERS_ID, SAL PERS_NAME, MANAGER_ID, OFFICE)其中，屬性SAL PERS_ID為工作人員得編號，SAL PERS_NAME為工作人員得姓名，MANAGER_ID為所在部門經(jīng)理得編號，OFFICE為工作地點。若查詢?nèi)可虉龉ぷ魅藛T，可以用下面得SQL語句：(SELECT * FROM SP_SUBORD)UNION(SELECT * FROM SP_MGR)或等價地用下面得SQL語句：

19、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(SELECT * FROM SP_MGR)或SELECT *FROM (TABLE SP_SUBORD INTERSECT AL

20、L TABLE SP_MGR)(3)EXCEPT(SELECT * FROM SP_MGR)EXCEPT(SELECT * FROM SP_SUBORD)FROMSTUDENTSWHERENOT EXISTSSP SUBORD與營銷經(jīng)理表或等價地用下面得SQL語句：SELECT *FROM (TABLE SP_MGR EXCE PT TABLE SP_ SUBORD)或用帶ALL得SQL語句：(SELECT * FROM SP_MGR)EXCEPT ALL(SELECT * FROM SP_SUBORD)例42查詢籍貫為四川、課程成績在80分以上得學生信息及其成績。(自然連接)(SELECT

21、* FROM STUDENTSWHERE BPLACE=四川)SQL得數(shù)據(jù)操縱UP DATE STUDENTSSET AGE=AGE+148學生張春明在數(shù)據(jù)庫課考試中作弊，該課成績應作零分計。UP DATE ENROLLSSET GRADE=0WHERE CNO=C1 AND張春明=（SELECT SNAMENATURAL JOIN（SELECT * FROM ENROLLS WHERE GRADE =80）例3、43列出全部教師得姓名及其任課得課程號、班級。（外連接與外部并外連接允許在結果表中保留非匹配元組， 得作用就是在做連接操作時避免丟失信息。外連接有3類：（1）左外連接（Left Ou

22、ter Join果表中保留左關系得所有元組。（2）右外連接（Right Outer Join結果表中保留右關系得所有元組。（3）全外連接（Full Outer Join果表中保留左右兩關系得所有元組。）SELECT TNAME, CNO, CLASSFROM TEACHERS LEFT OUTER JOIN TEACHING USING （TNO）。連接運算謂詞為）。連接運算謂詞為）。連接運算謂詞為空缺部分填以NULL。外連接LEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOIN，其結，其，其結44把教師李映雪得記錄加入到教師表INSERT INTO TE

23、ACHERS VALUES（1476，李映雪，45成績優(yōu)秀得學生將留下當教師。INSERT INTO TEACHERS （TNOSELECT DISTINCT SNO，SNAME FROMSTUDENTS，ENROLLS WHERE STUDENTS、SNO47把所有學生得年齡增加一歲。TEACHERS中。（插入）44副教授）TNAME)=ENROLLS、SNO AND GRADE=90(修改)FROM STUDENTSWHERE STUDENTS、SNO=ENROLLS、SNO）49從教師表中刪除年齡已到60歲得退休教師得數(shù)據(jù)。（刪除）DELETE FROM TEACHERSWHERE AG

24、E=60SQL得數(shù)據(jù)控制WITH GRANT OP TION例51取消LILI得存取STUDENTS表得特權。REVOKE ALLON STUDENTSFROM LILI CASCADE不斷補充中：1、 模糊查找：它判斷列值就是否與指定得字符串格式相匹配。與smalldatetime等類型查詢?？墒褂靡韵峦ㄅ渥址喊俜痔?:可匹配任意類型與長度得字符，如果就是中文，請使用兩個百分號即 下劃線_：匹配單個任意字符，它常用來限制表達式得字符長度。方括號:指定一個字符、字符串或范圍，要求所匹配對象為它們中得任一個。 取值也相同，但它要求所匹配對象為指定字符以外得任一個字符。例如：限制以限制以限制以2

25、、更改表格ALTER TABLE table nameADD COLUMN column_ name DATAT YPE說明：增加一個欄位（沒有刪除某個欄位得語法。ALTER TABLE table nameADD P RIMARY KEY （column_ name）說明：更改表得得定義把某個欄位設為主鍵。ALTER TABLE table name例50授予LILI有對表STUDENTS得查詢權。（表/視圖特權得授予 一個SQL特權允許一個被授權者在給定得數(shù)據(jù)庫對象上進行特定得操作。INSERT得數(shù)據(jù)庫對象包括： 表/視圖、 列、 域等。 授權得操作包括:DELETE、SELECT、REF

26、ERENCES、TRIGGER EXECUTE等。 其中INSERT、UP DATE、DELETE TRIGGER有對表做相應操作得權限，故稱為表特權。）GRANT SELECT ON STUDENTSTO LILI授權操作UPDATEUNDER、SELECT、REFERENCESUSAGE可用于char、varchar、text、ntext、datetime%。F:其Publishing結尾，使用LIKE%PublishingA開頭：LIKE A%A開頭外：LIKEFA%DROP P RIMARY KEY (column_ name)說明：把主鍵得定義刪除。3、group by在select

27、語句中可以使用group by一個組得匯總信息，另外，可以使用 查詢結果分組，并返回行得匯總信息 查詢結果。在帶有group by子句得查詢語句中，指定得列，要么包含聚組函數(shù)selectmax(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 hav ing sum(sal)8500

28、order by deptno;當在gropu by子句中使用having子句時，查詢結果中只返回滿足個sql語句中可以有where子句與having子句。having與where限定條件where子句得作用就是在對查詢結果進行分組前，將不符合where組之前過濾數(shù)據(jù)，條件中不能包含聚組函數(shù)，使用where條件顯示特定得行。having子句得作用就是篩選滿足條件得組，即在分組之后過濾數(shù)據(jù)，條件中經(jīng)常包含聚組 函數(shù)，使用having條件顯示特定得組，也可以使用多個分組標準進行分組。 查詢每個部門得每種職位得雇員數(shù)select deptno ,job,co un t(*) from emp gro

29、up by deptno job;4、外連接與內(nèi)連接有時候，即使在連接得表中沒有相應得行，用戶可能想從一張表中瞧數(shù)據(jù)， 外連接實現(xiàn)該功能。內(nèi)連接就是指連接查詢只顯示完全滿足連接條件得記錄，即等值連接，外連接得查詢結果就是內(nèi)連接查詢結果得擴展。外連接不僅返回滿足連接條件得所有記錄而且也返回了一個表中那些在另一個表中沒有匹配行得記錄。外連接得操作符就是“+。 “+號放在連接條件中信息不完全得那一邊(即沒有相應行得那一邊)。運算符“+影響NULL行得建立。建一行或多行NULL來匹配連接得表中信息完全得行。外連接運算符 “+只能出現(xiàn)在where子句中表達式得一邊。假如在多張表之間有多個連接條件，外連接

30、運算符不能使用or,in邏輯運算符與其它條件組合。假如emp表中deptno=10得en ame為空值，de pt表中deptno=20得loc為空值:1、selectename,dept、deptno,locfromemp,deptwhere子句將行劃分成較小得組， 然后，使用聚組函數(shù)返回每having子句限制返回得結果集。group by子句可以將Oracle按照group by子句中指定得表達式得值分組在select列表中指定得列要么就是group by子句中having條件得組。 在一 子句類似，均用于設置條件得行去掉，即在分Oracle提供了emp、depno什）=dept、deptno;如果在dept、deptno中有得數(shù)值在emp、deptno中沒有值，則在做外連接時,結果中ename會產(chǎn)生一個空值。（emp、deptno=10）2、select ename,dept、deptno,locfromemp,deptwhereemp、depno=dept、deptno什）；如果在emp、deptno中有得數(shù)值在dept、