SQL語句大全實例

1、學(xué)號3KO姓名SHANE鋅齡AGE性別 SEX黠貴B PLACE9900219&0S52 gsoseaG90S74090573 如刖 991091張舂明22242224332433SQL語句實例何）學(xué)生STUDENTS課程號課程名字分CNOCNAMECKEB1TC1教培庫3C2數(shù)據(jù)皓構(gòu)3C34C4戟件工程3(b)課程 C0W5ES斂師號TNOTKAKE年齡AGE職稱FS1420周毎華3S副教授劉逹平30講師143320講師數(shù)師1EACHERS課程號飼0班級 CLASS教師號THO學(xué)生數(shù) SNUMC1£8/51142030C2U2033C3囲為143130C3ESJ214S12

2、0C4ES51143234C1ES咒U303g任課TEACHING表操作學(xué)號諜程號成贛SNOCHOCL990027C33S0027C4990653CL990652C430060C3500674C2900676C3990 閔 4C3aGoe34CL901091C3怨）選課ENROLLS例1對于表得教學(xué)管理數(shù)據(jù)庫中得表STUDENTS ，可以定義如下:CREATE(SNOSNAMEAGESEXBP LACETABLE STUDENTSNUMERIC (6, 0) NOT NULLCHAR (8) NOT NULL NUMERIC(3,0) CHAR(2)CHAR(20)P RIMARY KEY(S

3、NO)ENROLLS ，可以定義如下:例2對于表得教學(xué)管理數(shù)據(jù)庫中得表CREATE(SNOCNOGRADETABLE 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 (GRADE BETWEEN 0 AND 100)例3根據(jù)表得STUDENTS 表，建立一個只包含學(xué)號、姓名、年齡得女學(xué)生表。CREA

4、TE TABLE GIRLAS 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 STUDENTSDROP BP LACE CASCADE例7補充定義ENROLLS 表得主關(guān)鍵字。ALTER TABLE ENROLLSADD P R

5、IMARY KEY (SNO,CNO)視圖操作(虛表)例9建立一個只包括教師號、姓名與年齡得視圖ORDER BY 子句)CREATE VIEW FACULTYFACULTY 。(在視圖定義中不能包含AS SELECT TNO, TNAME, AGEFROM TEACHERS例10從學(xué)生表、課程表與選課表中產(chǎn)生一個視圖 課程名與成績。GRADE_TABLE ，它包括學(xué)生姓名、CREATE VIEW GRADE TABLEAS SELECT SNAME,CNAME,GRADEFROM STUDENTS,COURSES,ENROLLSWHERE STUDENTS、SNO = ENROLLS、SNO

6、ANDCOURSES、CNO=ENROLLS 、CNO 例11 刪除視圖 GRADE_TABLEDROP VIEW GRADE_TABLE RESTRICT索引操作例12在學(xué)生表中按學(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 ，屬主為ZHANG 。CREATE SCHEMA TEACHING_DB AUTHRIZATION ZHANG例15刪除簡易教學(xué)數(shù)據(jù)庫模式TEACHING_DB 。（ 1 ）選用CA

7、SCADE ，即當(dāng)刪除數(shù)據(jù)庫模式時，則本數(shù)據(jù)庫模式與其下屬得基本表、視圖、索引等全部被刪除。（2 ）選用RESTRICT ，即本數(shù)據(jù)庫模式下屬得基本表、視圖、索引等事先已清除，才能刪除 本數(shù)據(jù)庫模式，否則拒絕刪除。）DROP SCHEMA TEACHING_DB CASCADE單表操作16找出3個學(xué)分得課程號與課程名。SELECT CNO, CNAMEFROM COURSESWHERE CREDIT = 317查詢年齡大于 22歲得學(xué)生情況。SELECT *FROM STUDENTSWHERE AGE 2218找出籍貫為河北得男生得姓名與年齡。SELECT SNAME, AGEFROM STU

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

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

10、'湖南，湖北）WHERE AGE < 23 AND BP LACE例22找出學(xué)生表中籍貫就是空值得學(xué)生得姓名與性別。（在SQL中不能使用條件：V列名>=NULL 。在SQL中只有一個特殊得查詢條件允許查詢NULL值：）SELECT SNAME, SEXFROM STUDENTSWHERE BP LACE IS NULL多表操作23找出成績?yōu)?95分得學(xué)生得姓名。（子查詢）SELECT SNAMEFROMSTUDENTSWHERESNO =(SELECT SNOFROM ENROLLSWHERE GRADE = 95）24找出成績在 90分以上得學(xué)生得姓名。SELECT SN

11、AMEFROM STUDENTSWHERE SNO IN(SELECT SNOFROM ENROLLSWHERE GRADE > 90)SELECTSNAMEFROMSTUDENTSWHERESNO = ANY(SELECT SNOFROM ENROLLSWHERE GRADE > 90）25查詢?nèi)繉W(xué)生得學(xué)生名與所學(xué)課程號及成績。（連接查詢）SELECT SNAME, CNO, GRADEFROM STUDENTS, ENROLLSENROLLS 、 SNOWHERE STUDENTS 、 SNO =1 （若有三個 則對此主關(guān)26找出籍貫為山西或河北，成績?yōu)?90分以上得學(xué)生得姓

12、名、籍貫與成績。（當(dāng)構(gòu)造 多表連接查詢命令時，必須遵循兩條規(guī)則。第一，連接條件數(shù)正好比表數(shù)少 表，就有兩個連接條件）；第二，若一個表中得主關(guān)鍵字就是由多個列組成，鍵字中得每一個列都要有一個連接條件（也有少數(shù)例外情況）SELECTSNAME, BP LACE, GRADEFROMSTUDENTS, ENROLLSWHEREBP LACE IN ( 山西， 河北)AND GRADE > =SNO=ENROLLS 、SNOSTUDENTS 、例28查出課程成績在 80分以上得女學(xué)生得姓名、課程名與成績。（90 ANDFROM子句中得子查詢）SELECT SNAME,CNAME, GRADEFR

13、OM (SELECT SNAME, CNAME , GRADEFROM STUDENTS, ENROLLS,COURSES WHERE SEX ='女')AS TEMP (SNAME, CNAME,GRADE)WHERE GRADE > 80表達式與函數(shù)得使用例29查詢各課程得學(xué)時數(shù)。（算術(shù)表達式由算術(shù)運算符+、-量所組成。）SELECT CNAME,COURSE_TIME FROM COURSES例30找出教師得最小年齡。（內(nèi)部函數(shù)：MIN函數(shù)，稱之為聚集函數(shù)（*、/與列名或數(shù)值常CREDIT*16MAX、列統(tǒng)計值得總數(shù)目，SUMMAX函數(shù)求該列最大值，SELECT M

14、IN(AGE)FROM TEACHERS31統(tǒng)計年齡小于等于SELECT COUNT(*)FROM STUDENTSWHERE AGE < =SQL標(biāo)準(zhǔn)中只使用Set Function函數(shù)求該列統(tǒng)計值之與，MIN函數(shù)求該列最小值。）22歲得學(xué)生人數(shù)。（統(tǒng)計）22COUNT 、SUM、AVG、COUNT函數(shù)得結(jié)果就是該AVG函數(shù)求該列統(tǒng)計值之平均值，）。32找出學(xué)生得平均成績與所學(xué)課程門數(shù)。COUNT(*)SELECT SNO, AVG(GRADE), COURSESFROM ENROLLSGRO UP BY SNO34找出年齡超過平均年齡得學(xué)生姓名。SELECT SNAMEFROM ST

15、UDENTSWHERE AGE >(SELECT AVG(AGE)FROM STUDENTS)3人得課程得成績。例35找出各課程得平均成績，按課程號分組，且只選擇學(xué)生超過（GROUP BY 與 HAVINGGRO UP BY 子句把一個表按某一指定列（或一些列）上得值相等得原則分組，然后 再對每組數(shù)據(jù)進行規(guī)定得操作。GROUP BY 子句總就是跟在 WHERE子句后面，當(dāng)WHERE子句缺省時，它跟在 FROM子句后面。HAVING子句常用于在計算出聚集之后對行得查詢進行控制。)SELECT CNO, AVG(GRADE), STUDENTSFROM ENROLLS=COUNT(*)GRO

16、 UP BY CNOHAVING COUNT(*) >= 3相關(guān)子查詢例37查詢沒有選任何課程得學(xué)生得學(xué)號與姓名。(當(dāng)一個子查詢涉及到一個來自外部查 詢得列時，稱為相關(guān)子查詢(Correlated Subquery)。相關(guān)子查詢要用到存在測試謂詞EXISTS 與 NOT EXISTS ，以及 ALL 、 ANY(SOME )等。)SELECT SNO, SNAMEFROM STUDENTSWHERE NOT EXISTS(SELECT *FROM ENROLLSWHERE ENROLLS 、SNO=STUDENTS例38 查詢哪些課程只有男生選讀。、SNO)SELECT DISTINCT

17、 CNAMEFROM COURSESCWHERE '男'=ALL(SELECT SEXFROM ENROLLSSTUDENTSWHERE ENROLLS 、SNO=STUDENTS、SNO ANDENROLLS、CNO=C例39要求給出一張學(xué)生、籍貫列表, 籍貫省份。、CNO)該表中得學(xué)生得籍貫省份，也就是其她一些學(xué)生得SELECTSNAME, BP LACEFROMSTUDENTS AWHEREEXISTS(SELECT *FROM STUDENTS BWHERE A、BPLACE=B、BP LACE ANDA、SNO < > B、SNO)例40找出選修了全部課程

18、得學(xué)生得姓名。本查詢可以改為：查詢這樣一些學(xué)生，沒有一門課程就是她不選修得。SNAMESELECTFROMSTUDENTSWHERENOT EXISTS(SELECT *FROM COURSESWHERE NOT EXISTS(SELECT *FROM ENROLLSWHERE ENROLLS 、SNO = STUDENTS 、SNOAND ENROLLS 、CNO = COURSES、CNO)關(guān)系代數(shù)運算SP SUBORD 與營銷經(jīng)理表例41設(shè)有某商場工作人員得兩張表：營業(yè)員表 SP_MGR ，其關(guān)系數(shù)據(jù)模式如下：SP_SUBORD (SAL PERS_ID, SAL PERS_NAME,

19、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語句：SELECT *FROM (TABLE SP_SUBORD UNION TABLE SP_MGR)(

20、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 ALL TABLE SP_MGR)(3 ) EXCEPT(SELECT * FROM SP_MGR

21、)EXCEPT(SELECT * FROM SP_SUBORD)或等價地用下面得 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分以上得學(xué)生信息及其成績。(自然連接)(SELECT * FROM STUDENTSWHERE BPLACE= 四川')NATURAL JOIN（SELECT * FROM ENROLLS WHERE GRADE &g

22、t;=80）例3、43列出全部教師得姓名及其任課得課程號、班級。（外連接與外部并外連接允許在結(jié)果表中保留非匹配元組， 得作用就是在做連接操作時避免丟失信息。外連接有3類：（1 ）左外連接（ Left Outer Join 果表中保留左關(guān)系得所有元組。（2 ）右外連接（ Right Outer Join 結(jié)果表中保留右關(guān)系得所有元組。（3 ）全外連接（ Full Outer Join 果表中保留左右兩關(guān)系得所有元組。）SELECT TNAME, CNO, CLASSFROM TEACHERS LEFT OUTER JOIN TEACHING USING （TNO）。連接運算謂詞為）。連接運算謂詞

23、為）。連接運算謂詞為空缺部分填以 NULL 。外連接LEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOIN，其結(jié)，其，其結(jié)SQL得數(shù)據(jù)操縱44把教師李映雪得記錄加入到教師表INSERT INTO TEACHERS VALUES（1476 ，'李映雪'，45成績優(yōu)秀得學(xué)生將留下當(dāng)教師。INSERT INTO TEACHERS （TNOSELECT DISTINCT SNO ， SNAME FROM STUDENTS ， ENROLLS WHERE STUDENTS 、SNO47把所有學(xué)生得年齡增加一歲。TEACHERS 中。（插入）44

24、9;副教授'）TNAME)=ENROLLS、SNO AND GRADE >= 90(修改)UP DATE STUDENTSSET AGE = AGE+148學(xué)生張春明在數(shù)據(jù)庫課考試中作弊，該課成績應(yīng)作零分計。UP DATE ENROLLSSET GRADE = 0WHERE CNO = 'C1' AND'張春明'=（SELECT SNAMEFROM STUDENTSWHERE STUDENTS 、SNO=ENROLLS、SNO）49從教師表中刪除年齡已到60歲得退休教師得數(shù)據(jù)。（刪除）DELETE FROM TEACHERSWHERE AGE &g

25、t;= 60SQL得數(shù)據(jù)控制例50授予LILI有對表STUDENTS得查詢權(quán)。（表/視圖特權(quán)得授予 一個SQL特權(quán)允許一個被授權(quán)者在給定得數(shù)據(jù)庫對象上進行特定得操作。INSERT得數(shù)據(jù)庫對象包括：表 /視圖、列、域等。授權(quán)得操作包括: DELETE 、 SELECT 、 REFERENCES 、 TRIGGER EXECUTE 等。其中 INSERT、UP DATE 、DELETE TRIGGER有對表做相應(yīng)操作得權(quán)限，故稱為表特權(quán)。）GRANT SELECT ON STUDENTSTO LILI授權(quán)操作UPDATEUNDER 、SELECT 、 REFERENCESUSAGEWITH GRA

26、NT OP TION例51 取消 LILI得存取 STUDENTS 表得特權(quán)。REVOKE ALLON STUDENTSFROM LILI CASCADE不斷補充中：可用于 char、varchar、text、ntext、datetime1、 模糊查找： 它判斷列值就是否與指定得字符串格式相匹配。與smalldatetime等類型查詢?？墒褂靡韵峦ㄅ渥址?。F:其百分號% :可匹配任意類型與長度得字符，如果就是中文，請使用兩個百分號即 下劃線_：匹配單個任意字符，它常用來限制表達式得字符長度。方括號:指定一個字符、字符串或范圍，要求所匹配對象為它們中得任一個。 取值也相同，但它要求所匹配對象

27、為指定字符以外得任一個字符。例如：Publishing 結(jié)尾，使用 LIKE'%Publishing'A 開頭：LIKE 'A%'A 開頭外：LIKE'FA%'限制以限制以限制以2、更改表格ALTER TABLE table nameADD COLUMN column_ name DATAT YPE說明：增加一個欄位（沒有刪除某個欄位得語法。ALTER TABLE table nameADD P RIMARY KEY （column_ name）說明：更改表得得定義把某個欄位設(shè)為主鍵。ALTER TABLE table nameDROP P RI

28、MARY KEY (column_ name)說明：把主鍵得定義刪除。子句將行劃分成較小得組， 然后，使用聚組函數(shù)返回每 having子句限制返回得結(jié)果集。group by子句可以將 Oracle按照group by子句中指定得表達式得值分組3、group by 在select語句中可以使用 group by 一個組得匯總信息，另外，可以使用 查詢結(jié)果分組，并返回行得匯總信息 查詢結(jié)果。在 select列表中指定得列要么就是group by子句中在帶有g(shù)roup by子句得查詢語句中， 指定得列，要么包含聚組函數(shù) select max(sal),job emp group by job;(注意

29、max(sal),job得job并非一定要出現(xiàn)，但有意義)查詢語句得select與group by ,having子句就是聚組函數(shù)唯一出現(xiàn)得地方，在where子句中不能使用聚組函數(shù)。select deptno ,sum(sal) from emp where sal>1200 group by deptno hav ing sum(sal)>8500having條件得組。在一 子句類似，均用于設(shè)置order by deptno;當(dāng)在gropu by子句中使用having子句時，查詢結(jié)果中只返回滿足個 sql語句中可以有 where 子句與 having 子句。having 與 wh

30、ere 限定條件條件得行去掉，即在分where子句得作用就是在對查詢結(jié)果進行分組前，將不符合where組之前過濾數(shù)據(jù)，條件中不能包含聚組函數(shù)，使用where條件顯示特定得行。having子句得作用就是篩選滿足條件得組，即在分組之后過濾數(shù)據(jù)，條件中經(jīng)常包含聚組 函數(shù)，使用having條件顯示特定得組，也可以使用多個分組標(biāo)準(zhǔn)進行分組。 查詢每個部門得每種職位得雇員數(shù)select deptno ,job,co un t(*) from emp group by deptno job;4、外連接與內(nèi)連接Oracle 提供了有時候，即使在連接得表中沒有相應(yīng)得行，用戶可能想從一張表中瞧數(shù)據(jù)， 外連接實現(xiàn)該

31、功能。內(nèi)連接就是指連接查詢只顯示完全滿足連接條件得記錄，即等值連接，外連接得查詢結(jié)果就是內(nèi)連接查詢結(jié)果得擴展。外連接不僅返回滿足連接條件得所有記錄而且也返回了一個表中那些在另一個表中沒有匹配行得記錄。外連接得操作符就是“ +。 “ +號放在連接條件中信息不完全得那一邊(即沒有相應(yīng)行得那一邊)。運算符“ +影響NULL行得建立。建一行或多行NULL來匹配連接得表中信息完全得行。外連接運算符 “ + 只能出現(xiàn)在where子句中表達式得一邊。假如在多張表之間有多個連接條件，外連接運算符不能使用or,in邏輯運算符與其它條件組合。假如emp表中deptno=10 得en ame為空值，de pt表中deptno=20 得loc為空值:1、selectename,dept、deptno,locfromemp,deptwhereemp、depno什）=dept、deptno;如果在dept、deptno中有得數(shù)值在emp、deptno中沒有值，則在做外連接時,結(jié)果中ename 會產(chǎn)生一個空值。（emp、deptno=10）2、select ename,dept、deptno,locfromemp,deptwhereemp、depno=dept、deptno什）；如果在emp、