習(xí)題課 - SQL結(jié)構(gòu)化查詢語言資料

1、SQL主要分成(fn chn)四個(gè)部分： 數(shù)據(jù)定義； 數(shù)據(jù)操縱； 數(shù)據(jù)控制； 嵌入式SQL的使用。共五十三頁 SQL的主要(zhyo)特點(diǎn) 1. 一體化； 2. 兩種使用方式，統(tǒng)一的語法結(jié)構(gòu)； 3. 高度非過程化； 4. 語言簡潔，易學(xué)易用。共五十三頁 1、基本表的創(chuàng)建(chungjin) CREATE TABLE SQL 模式名.基本表名 （列名 類型， 完整性約束， ） 完整性規(guī)則主要有三種： 主鍵子句(z j)（PRIMARY KEY）； 外鍵子句（FOREIGN KEY）； 檢查子句（CHECK）。共五十三頁可用下列(xili)語句創(chuàng)建表SC:CREATE TABLE SC (SNO

2、CHAR(4) NOT NULL, CNO CHAR(4) NOT NULL, GRADE NUMERIC(7,2), PRIMARY KEY(SNO,CNO), FOREIGN KEY(SNO)REFERENCES S(SNO), FOREIGN KEY(CNO)REFERENCES P(CNO), CHECK(GRADE BETWEEN 0 AND 100);主鍵子句外鍵子句檢查子句共五十三頁2. 視圖的創(chuàng)建CREATE VIEW （列名表） AS 例：在student數(shù)據(jù)庫，建立一個(gè)各學(xué)院學(xué)生情況視圖： CREATE VIEW D_STUDENT(DNAME,SNO,SNAME) AS

3、(SELECT DNAME,SNO,SNAME FROM DEPT d,STUDENT S WHERE D.DNO=S.DNO)共五十三頁 3. 索引(suyn)的創(chuàng)建 CREATE UNIQUE INDEX 索引名 ON 基本表名 （列名，列名 ）例： CREATE UNIQUE INDEX DN_IND ON DEPT(DNAME); CREATE INDEX S_IND2 ON STUDENT(SNAME DESC); CREATE INDEX C_IND ON C(HOURS, CREDIT); 共五十三頁 4. SQL的數(shù)據(jù)查詢 SELECT DISTINCT 目標(biāo)(mbio)表的列

4、名或列表達(dá)式序列 FROM 基本表名(或)視圖名序列|表引用 WHERE 行條件表達(dá)式 GROUP BY 列名1序列 HAVING 組條件表達(dá)式 ORDER BY 列名2 ASC|DESC序列；共五十三頁 在WHERE子句的行條件表達(dá)式中可使用(shyng)下列運(yùn)算符： 算術(shù)比較運(yùn)算符：，或！； 邏輯運(yùn)算符：AND，OR，NOT 集合成員資格運(yùn)算符：IN，NOT IN 謂詞：EXISTS，ALL，SOME，UNIQUE 聚合函數(shù)：AVG，MIN，MAX，SUM，COUNT 集合運(yùn)算符：UNION，INTERSECT，EXCEPT 空值邏輯判斷符：NULL, IS NOT NULL 共五十三頁集

5、合操作并 UNION交 INTERSECT差 MINUS 或 except 例:查詢?nèi)?qun xio)的所有學(xué)生（包括本科和研究生） select sno,sname,age from sudent union select g_sno,g_sname,age from graduates并相容：要求參加運(yùn)算的雙方列數(shù)相同而且(r qi)對(duì)應(yīng)的列數(shù)據(jù)類型一致共五十三頁5. SQL的數(shù)據(jù)(shj)更新1. 數(shù)據(jù)插入單個(gè)元組： INSERT INTO 基本表名（列名表） VALUES （元組值）2. 數(shù)據(jù)刪除 DELETE FROM WHERE 3. 數(shù)據(jù)修改 UPDATE 基本表名 SET

6、列名=值表達(dá)式，列名=值表達(dá)式 WHERE 條件表達(dá)式共五十三頁目標(biāo)程序宿主語言十嵌入式SQL 預(yù) 處 理 程 序 宿主語言十函數(shù)調(diào)用宿主語言編譯程 序6. 嵌入式SQL的使用(shyng)技術(shù)共五十三頁例1：一個(gè)表當(dāng)做多個(gè)表使用(shyng) 查詢家族之間的祖孫關(guān)系 Family(parent, child) Select x.Parent Grand_Parent, y.Child Grand_Child From Family x, Family y where x.child = y. parent注：同一個(gè)表可以當(dāng)作多個(gè)表使用共五十三頁查詢課程選修(xunxi)之間的先選后選關(guān)系 r

7、equirement(pre_cno,post_cno) course(cno,cname) Select ame Pre_course , ame Post_course from course x, requiement r, course y where o=r.pre_cno and r.post_cno=o共五十三頁例2：集合運(yùn)算一個(gè)要注意的例子 查詢沒有(mi yu)選修 DB 課程的學(xué)生？Select distinct sno,sname from student s,s_c,course c where s.sno=s_c.sno and s_o=no and ame!=DB共

8、五十三頁查詢沒有選修Oracle數(shù)據(jù)庫的學(xué)生(xu sheng) select sno,sname from student except （有些系統(tǒng)用minus） select distinct sno,sname from student s,s_c,course c where s.sno=s_c.sno and s_o=no and ame = DB共五十三頁例2：數(shù)據(jù)分組（聚合函數(shù)(hnsh)又叫分組函數(shù)(hnsh)或組函數(shù)(hnsh)） select dno, avg(age) 統(tǒng)計(jì)各學(xué)院學(xué)生 from student 的平均年齡 group by dno; select dno,

9、 avg(age) 分組運(yùn)算時(shí)where from student 和having的使用 where sex=male group by dno having avg(age) 19; 共五十三頁例3： 子查詢 SQL語言中需要值的地方(dfng)用一條查詢語句代替，稱為子查詢。子查詢分嵌套子查詢和相關(guān)子查詢兩種。1）嵌套子查詢：例：查詢與“張三”同學(xué)在同一個(gè)系的所有學(xué)生。 解1：select dno from student where sname = 張三； 得： 012 select sno, sname from student where sno=012 共五十三頁解2： selec

10、t sno, sname from student where dno= ( select dno from student where sname=張三）； 子查詢能夠(nnggu)獨(dú)立于主查詢執(zhí)行的查詢?yōu)榍短鬃硬樵儭?共五十三頁2）相關(guān)子查詢(chxn)： 查詢比本學(xué)院平均年齡大的學(xué)生學(xué)號(hào)和姓名 select sno, sname from student x where age ( select avg（age） from student y where y.dno=x.dno）； 子查詢不能夠單獨(dú)執(zhí)行，需要主查詢中的值才可以執(zhí)行。 共五十三頁例4： 用子查詢解決商（除法）運(yùn)算問題： 查

11、詢至少選修了C中全部課程(kchng)的學(xué)生。 S_C(SNO, CNO) C(CNO) S_C C(SNO) s001 c001 c001 s001 s001 c002 c005 s001 c005 s002 c005 即：S_C C s002 c006 一般情況問題中帶有 至少查詢時(shí)都會(huì)涉及 到除法共五十三頁解：Select distinct sno from s_c x where not exists (select distinct cno from c y where not exits (select * from s_c z 這可以(ky)作為 where x.sno=z.sn

12、o 一個(gè)模版用 and o=o)共五十三頁作為一個(gè)一般性問題： 從如下(rxi)數(shù)據(jù)庫中查詢至少選修了課程名（cname）為“DB”和“OS”的學(xué)生的學(xué)號(hào)和姓名。 STUDENT(SNO,SNAME,AGE,SEX,DNO) COURSE(CNO,CNAME,HOURS,CREDIT) S_C(SNO, CNO)共五十三頁解： select x.sno,x.sname from student x where not exists (select o from course y where ame in (DB, OS) and not exists (select * from studen

13、t s_c z where z.sno =x.sno and o=o)共五十三頁選課表E： 主鍵：（學(xué)號(hào)、學(xué)期(xuq)、課號(hào)、工號(hào)） 外鍵：學(xué)號(hào)，（學(xué)期、課號(hào)、工號(hào)），課號(hào)，工號(hào) 1. 請(qǐng)說明school數(shù)據(jù)庫中各表的主鍵和外鍵。如果(rgu)O表加一個(gè)個(gè)開課編號(hào)(標(biāo)識(shí)碼)會(huì)帶來哪些影響(優(yōu)缺點(diǎn))作業(yè)和實(shí)驗(yàn)解答：第1周研討課共五十三頁2.檢索(jin su)有學(xué)生重修的教師編號(hào)和姓名。select gh,xm where gh in (select a.gh from e a,e b where a.xh=b.xh and a.kh=b.kh and a.xq b.xq)作業(yè)和實(shí)驗(yàn)(shyn

14、)：第2周研討課共五十三頁6. 查詢年齡小于本學(xué)院平均年齡，所有課程總評(píng)成績(chngj)都高于所選課程平均總評(píng)成績(chngj)的學(xué)生學(xué)號(hào)、姓名和平均總評(píng)成績(chngj)，按年齡排序。 select s1.xh, xm, avg(e1.zpcj) from s s1, e e1 where s1.xh=e1.xh group by s1.xh,xm,year(getdate()-year(s1.csrq) having s1.xh in (select s2.xh from s s2 where year(getdate()-year(s2.csrq) (select avg(year(g

15、etdate()-year(s3.csrq) from s s3 where s2.yxh=s3.yxh) and s1.xh not in(select e2.xh from e e2 where e2.zpcj23 and sex=M;共五十三頁 習(xí)題(xt) 4.2: （略）3.檢索學(xué)號(hào)為S3學(xué)生(xu sheng)所學(xué)課程的課程號(hào)與任課老師名；Select cno, tanme from c,sc where o=o and sno=S3共五十三頁 習(xí)題(xt) 4.2: （略）4.檢索至少(zhsho)選修LIU老師所授課程中一門課的女學(xué)生姓名；Select distinct sna

16、me from s,c,sc where s.sno=sc.sno and o=o and tname=LIU and sex=F;共五十三頁 習(xí)題(xt) 4.2:5.檢索WAN同學(xué)不學(xué)(b xu)的課程的課程號(hào)；Select cno from cExceptSelect cno from s,sc where s.sno=sc.sno and sname=WAN;？Select distinct cno from sc共五十三頁 習(xí)題(xt) 4.2:6.檢索(jin su)至少選修兩門課程的學(xué)生學(xué)號(hào)； Select distinct x.sno from sc x,sc y where

17、x.sno=y.sno and o o 1（1 =4 2!=5（scXsc）共五十三頁 習(xí)題(xt) 4.2:7.檢索全部(qunb)學(xué)生選修的課程課程號(hào)與課程名；Select cno,cname from c where not exists (select sno from s where not exists (select * from sc o=o and sc.sno=s.sno) 共五十三頁 習(xí)題(xt) 4.2:8.檢索選修(xunxi)課程包含LIU老師所授課程的學(xué)生學(xué)號(hào)。Select sno from s where not exists (select cno from

18、c where tname=LIU and not exists (select * from sc where sc.sno=s.sno and o=o) 共五十三頁SELECT DISTINCT x.jno FROMspj AS x WHERENOT EXISTS (SELECT y.pno FROMspj AS yWHEREy.sno = s1 AND NOT EXISTS （SELECT* FROMspj AS z WHERE(z.jno =x.jno) AND (z.pno =y.pno) AND (z.sno = s1) （參考(cnko)文本文件 p82-spj_ex.txt）*

19、P.82 第6小題說明(shumng)共五十三頁 習(xí)題(xt) 4.6:1.統(tǒng)計(jì)有學(xué)生(xu sheng)選修的課程門數(shù)；2.求選修C4課程的學(xué)生的平均年齡；3.求LIU老師所授課程的每門課的學(xué)生平均成績；4.統(tǒng)計(jì)每門課程的學(xué)生選課人數(shù)（超過10人的才統(tǒng)計(jì)），要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)排列；5.檢索學(xué)號(hào)比WANG同學(xué)大，但年齡比他小的學(xué)生姓名；6.檢索姓名以WANG打頭的所有學(xué)生的姓名和年齡；7.在SC表中檢索成績?yōu)榭罩档膶W(xué)生學(xué)號(hào)和課號(hào)；8.求年齡大于女同學(xué)平均年齡的男同學(xué)姓名和年齡；9.求年齡大于所有女同學(xué)年齡的男同學(xué)姓名和年齡共五十三頁 習(xí)題(x

20、t) 4.6:（略） 1.統(tǒng)計(jì)(tngj)有學(xué)生選修的課程門數(shù)；Select count(distinct cno) from sc;共五十三頁 習(xí)題(xt) 4.6: 2.求選修(xunxi)C4課程的學(xué)生的平均年齡；解2：Select avg(age) from s where s.sno in (select distinct sno from sc where cno=C4);解1：Select avg(age) from s,sc where s.sno=sc.sno and o=C4;如果 C4，C5,C6 時(shí)呢？要不要？共五十三頁 習(xí)題(xt) 4.6:（略） 3.求LIU老師所

21、授課程的每門課的學(xué)生平均(pngjn)成績；Select o,avg(grade) from c,sc where o=o and tname=LIU group by o;共五十三頁 習(xí)題(xt) 4.6:（略） 4.統(tǒng)計(jì)每門課程的學(xué)生選課人數(shù)（超過10人的才統(tǒng)計(jì)），要求輸出(shch)課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)排列；Select cno,count(sno) from sc group by cno having count(sno)10 order by count(sno) desc, cno;共五十三頁 習(xí)題(xt) 4.6:（略） 5.檢索學(xué)號(hào)比

22、WANG同學(xué)大，但年齡(ninlng)比他小的學(xué)生姓名；Select s.sname from s where s.sno all (select x.sno from s x where sname=WANG) and s.age (select avg(x.age) from s x where x.sex=F);共五十三頁習(xí)題(xt) 4.6: 9.求年齡大于所有(suyu)女同學(xué)年齡的男同學(xué)姓名和年齡Select s.sname,s.age from s where s.sex=M and s.age all (select x.age from s x where x.sex=F);共五十三頁習(xí)題(xt) 4.7: 2.在基本表S中檢索每一門課程成績都大于等于80分的學(xué)生的學(xué)號(hào)、姓名和性別(xngbi)，并將結(jié)果保存到另一個(gè)已存在的基本表STUDENT（SNO，SNAME，SEX）中Insert into student(sno,sname,sex)select s.sno,sname,sex from s where not exists (select * from sc where sc.sno=s.sno and grade80)共五十三頁習(xí)題(xt) 4.7: 2.在基本表S中檢索每一門課程成績都大于等于80分的學(xué)生(xu sheng)的學(xué)號(hào)、姓名和