数据库系统概论(第4版)书上实例

1、3.3数据库的定义例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表增加“入学时间”列，其数据类型为日期型ALTER TABLE Student ADD S_entrance DATE例9.将年龄的数据类型由字符型改为整型。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数据查询一、选择表中的若干列1.查询指定列例1.查询全体学生的学

4、号与姓名SELECT Sno,SnameFROM Student例2.查询全体学生的姓名、学号、所在系。SELECT Sname,Sno,SdeptFROM Student2.查询全部列例3查询全体学生的详细记录。SELECT *FROM Student3.查询经过计算的值例4.查询全体学生的姓名及其出生年份。SELECT Sname,2014-SageFROM Student例5.查询全体学生的姓名、出生年月和所在院系，要求用小写字母表示所有的系名。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）、数学系（MA）和信息系（IS）学生的姓名和性别SELECT Sname,Ssex FROM StudentWHERE Sdept IN (CS,MA,IS)例13.查询既不是计算机科学系（CS）、数学系（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_开头，且倒数第3个字符为i的课程的详细情况。SELECT *FROM Course WHERE Cname LIKE DB_%i_ESCAPE例21.某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。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.查询选修了课程名为“信息系统”的学生号及姓名。SELECT Sno,SnameFROM Student WHERE Sno IN (SELECT SnoFROM SC WHERE Cno IN(SELECT CnoFROM Course WHERE Cname=信息系统)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 数据更新一、插入数据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.对每一个系，求学生的平均年龄，并把结果存入数据库。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二、修改数据1.修改某一个元组的值例5.将学生200215121的年龄改为22岁。UPDATE StudentSET Sage=22WHERE Sno=2002151512.修改多个元组的值例6.将所有学生的年龄增加1岁。UPDATE StudentSET Sage=Sage+13.带子查询的修改语句例7.将计算机科学系全体学生的成绩置零。UPDA