數(shù)據(jù)庫系統(tǒng)概論(第4版)書上實例

1、3.3數(shù)據(jù)庫的定義例5.建立一個 學生表 StudentCREATE TABLE Student (Sno CHAR(9) PRIMARY KEY ,Sname CHAR(20) UNIQUE,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20);例6.建立一個課程表CourseCREATE TABLE Course (Cno CHAR(4) PRIMARY KEY ,Cname CHAR(40) ,Cpno CHAR(4),Ccredit SMALLINT,FOREIGN KEY Cpno REFERENCES Course(Cno);例7.建立學生選課表SCC

2、REATE TABLE SC(Sno CHAR(9),Cno CHAR(4) ,Grade SMALLINT,PRIMARY KEY (Sno,Cno),FOREIGN KEY(Sno) REFERENCES Student (Sno),FOREIGN KEY(Sno) REFERENCES Course (Cno);例8.向Student表增加“入學時間”列，其數(shù)據(jù)類型為日期型ALTER TABLE Student ADD S_entrance DATE例9.將年齡的數(shù)據(jù)類型由字符型改為整型。ALTER TABLE Student ALTER COLUMN Sage INT例10.增加課程名

3、稱必須取唯一值的約束條件。ALTER TABLE Course ADD UNIQUE(Cname)11.刪除Student表DROP TABLE Student CASCADE例12.建立視圖CREATE VIEW IS_Student ASSELECT Sno ,Sname,Sage FROM StudentWHERE Sdept=IS13.建立索引CREATE UNIQUE INDEX Studsno ON Student(Sno);例14.刪除Student表的Stusname索引DROP INDEX Stusname3.4數(shù)據(jù)查詢一、選擇表中的若干列1.查詢指定列例1.查詢?nèi)w學生的學

4、號與姓名SELECT Sno,SnameFROM Student例2.查詢?nèi)w學生的姓名、學號、所在系。SELECT Sname,Sno,SdeptFROM Student2.查詢?nèi)苛欣?查詢?nèi)w學生的詳細記錄。SELECT *FROM Student3.查詢經(jīng)過計算的值例4.查詢?nèi)w學生的姓名及其出生年份。SELECT Sname,2014-SageFROM Student例5.查詢?nèi)w學生的姓名、出生年月和所在院系，要求用小寫字母表示所有的系名。SELECT Sname,Year of Birth:2014-Sage,LOWER(Sdept)FROM Student二、選擇表中的若干元組

5、1.取消重復的行例6.查詢選修了課程的學生學號。SELECT DISTINCT SnoFROM SC2.查詢滿足條件的元組例7.查詢計算機科學系全體學生的名單。SELECT SnameFROM Student WHERE Sdept=CS例8.查詢所有年齡在20歲以下的學生姓名及其年齡。SELECT Sname,SageFROM Student WHERE Sage20例9.查詢考試成績有不及格的學生的學號。SELECT DISTINCT SnoFROM SCWHERE Grade60例10.查詢年齡在20-23歲之間的學生姓名、系別和年齡。SELECT Sname ,Sdept,SageFR

6、OM StudentWHERE Sage BETWEEN 20 AND 23例11.查詢年齡不在20-23歲之間的學生姓名、系別和年齡。SELECT Sname ,Sdept,SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23例12.查詢計算機科學系（CS）、數(shù)學系（MA）和信息系（IS）學生的姓名和性別SELECT Sname,Ssex FROM StudentWHERE Sdept IN (CS,MA,IS)例13.查詢既不是計算機科學系（CS）、數(shù)學系（MA）也不是信息系（IS）學生的姓名和性別SELECT Sname,Ssex FROM S

7、tudentWHERE Sdept NOT IN (CS,MA,IS)例14.查詢學號為200215121的學生的詳細情況。SELECT *FROM StudentWHERE Sno=200215121例15.查詢所有姓劉的學生的姓名、學號和性別。SELECT Sname ,Sno,SsexFROM StudentWHERE Sname LIKE 劉%例16.查詢姓“歐陽”且全名為3個漢字的學生姓名。SELECT Sname FROM StudentWHERE Sname LIKE 歐陽_例17.查詢名字中第二個為“陽”字的學生姓名和學號。SELECT Sname ,SnoFROM Stude

8、ntWHERE Sname LIKE _陽%例18.查詢所有不姓劉的學生姓名SELECT Sname WHERE Sname NOT LIKE 劉%例19.查詢DB_Design 課程的課程號和學分。SELECT Cno,CcreditFROM Course WHERE Cname LIKE DB_DesignESCAPE例20.查詢以DB_開頭，且倒數(shù)第3個字符為i的課程的詳細情況。SELECT *FROM Course WHERE Cname LIKE DB_%i_ESCAPE例21.某些學生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W生的學號和相應的課程號。S

9、ELECT Sno,CnoFROM SCWHERE Grade IS NULL例22.查詢所有有成績的學生學號和課程號。SELECT Sno,CnoFROM SCWHERE Grade IS NOT NULL例23.查詢計算機科學系年齡在20歲以下的學生姓名。SELECT Sname FROM Student WHERE Sdept=CS AND Sage3六、連接查詢1.等值與非等值的連接查詢例33.查詢每個學生及其選修課程的情況。SELECT Student .*,SC.*FROM Student ,SCWHERE Student.Sno=SC.Sno例34.對【例33】用自然連接完成。S

10、ELECT Student .Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROM Student ,SCWHERE Student.Sno=SC.Sno2.自身連接例35.查詢每一門課的間接先修課。SELECT FIRST.Cno,SECOND.CpnoFROM Course FIRST,Course SECONDWHERE FIRST.Cpno=SECOND.Cno3.外連接例36.改寫例33SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROM Student LEFT OUT JOIN SC ON (Stu

11、dent.Sno=SC.Sno)4.復合條件查詢例37.查詢選修2號課程且成績在90分以上的所有學生。SELECT Student.Sno,SnameFROM Student,SCWHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90例38.查詢每個學生的學號、姓名、選修的課程及成績。SELECT Student.Sno,Sname，Cname,GradeFROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno七、嵌套查詢1.帶有IN謂詞的子查詢例39.查詢與“劉晨

12、”在同一個系的學生。SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname=劉晨)例40.查詢選修了課程名為“信息系統(tǒng)”的學生號及姓名。SELECT Sno,SnameFROM Student WHERE Sno IN (SELECT SnoFROM SC WHERE Cno IN(SELECT CnoFROM Course WHERE Cname=信息系統(tǒng))2.帶有比較運算符的子查詢例41.找出每個學生超過他選修課程平均成績的課號。SELECT Sno,CnoFROM SC

13、 xWHERE Grade=(SELECT AVG(Grade)FROM SC yWHERE y.Sno=x.Sno)例42.查詢其他系中比計算機科學系某一個學生年齡小的姓名和年齡。SELECT Sname,SageFROM Student WHERE SageANY (SELECT SageFROM StudentWHERE Sdept=CS)AND SdeptCS例43.查詢其他系中比計算機科學系所有學生年齡都小的學生姓名及年齡。SELECT Sname,SageFROM StudentWHERE Sage ALL(SELECT Sage FROM StudentWHERE Sdept=C

14、S)AND SdeptCS4.帶有EXISTS謂詞的子查詢。例44.查詢所有選修了1號課程的學生姓名。SELECT Sname FROM StudentWHERE EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1)例45.查詢沒有選修1號課程的學生姓名。SELECT Sname FROM StudentWHERE NOT EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1)例46.查詢選修了全部課程的學生姓名。SELECT Sname FROM StudentWHERE N

15、OT EXISTS(SELECT * FROM CourseWHERE NOT EXISTS (SELECT *FROM SC WHERE Sno=Student.SnoAND Cno=Course.Cno)例47.查詢至少選修了學生200215122選修的全部課程的學生學號。SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS (SELECT *FROM SC SCYWHERE SCY.Sno=200215122 AND NOT EXISTS (SELECT * FROM SC SCZWHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=

16、SCY.Cno)八、集合查詢例48.查詢計算機科學系的學生機年齡大于19歲的學生。SELECT *FROM StudentWHERE Sdept =CSUNIONSELECT * FROM StudentWHERE Sage=19例49.查詢選修了課程號1或者選修了課程號2的學生。SELECT SnoFROM SC WHERE Cno=1UNIONSELECT SnoFROM SC WHERE Cno=2例50.查詢計算機科學系的學生與年齡不大于19歲的學生的交集。SELECT *FROM StudentWHERE Sdept =CSINTERSECTSELECT * FROM Student

17、WHERE Sage=19例51.查詢既選修了1號課程有選修了2號課程的學生。SELECT SnoFROM SC WHERE Cno=1INTERSECTSELECT SnoFROM SC WHERE Cno=2例52.查詢計算機科學系的學生與年齡不大于19歲的學生的差集。SELECT *FROM StudentWHERE Sdept =CSEXCEPTSELECT * FROM StudentWHERE Sage=193.5 數(shù)據(jù)更新一、插入數(shù)據(jù)1.插入元組例1.將一個新學生元組（學號：200215128；姓名：陳東；性別：男；所在系：IS；年齡：18歲）插入到Student表中。INSER

18、TINTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES(200215128,陳東,男,IS,18)例2.將學生張成民的信息插入到Student表中INSERTINTO StudentVALUES(200215126,張成民,男,18,CS)例3.插入一條選課記錄（200215128，1）。INSERT INTO SC(Sno,Cno)VALUES(200215128,1)例4.對每一個系，求學生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。CREATE TABLE Dept_age(Sdept CHAR(15)Avg_age INT)INSERT INTO Dept_age(Sdept,Avg_age)SELECT Sdept,AVG(Sage)FROM StudentGROUP BY Sdept二、修改數(shù)據(jù)1.修改某一個元組的值例5.將學生200215121的年齡改為22歲。UPDATE StudentSET Sage=22WHERE Sno=2002151512.修改多個元組的值例6.將所有學生的年齡增加1歲。UPDATE StudentSET Sage=Sage+13.帶子查詢的修改語句例7.將計算機科學系全體學生的成績置零。UPDA