数据库第三章例题电子版.docx
- 文档编号:16592806
- 上传时间:2023-07-15
- 格式:DOCX
- 页数:25
- 大小:22.20KB
数据库第三章例题电子版.docx
《数据库第三章例题电子版.docx》由会员分享,可在线阅读,更多相关《数据库第三章例题电子版.docx(25页珍藏版)》请在冰点文库上搜索。
数据库第三章例题电子版
3.3数据定义
[例1]定义一个学生-课程模式S-T
CREATESCHEMA“S-T”AUTHORIZATIONWANG;
为用户WANG定义了一个模式S-T
[例2]CREATESCHEMAAUTHORIZATIONWANG;
<模式名>隐含为用户名WANG
[例3]
CREATESCHEMATESTAUTHORIZATIONZHANG
CREATETABLETAB1(COL1SMALLINT,
COL2INT,
COL3CHAR(20),
COL4NUMERIC(10,3),
COL5DECIMAL(5,2)
);
为用户ZHANG创建了一个模式TEST,并在其中定义了一个表TAB1。
[例4]DROPSCHEMAZHANGCASCADE;
删除模式ZHANG。
同时该模式中定义的表TAB1也被删除
[例5]建立“学生”表Student,学号是主码,姓名取值唯一。
CREATETABLEStudent
(SnoCHAR(9)PRIMARYKEY,/*列级完整性约束条件*/
SnameCHAR(20)UNIQUE,/*Sname取唯一值*/
SsexCHAR
(2),
SageSMALLINT,
SdeptCHAR(20)
);
[例6]建立一个“课程”表Course
CREATETABLECourse
(CnoCHAR(4)PRIMARYKEY,
CnameCHAR(40),
CpnoCHAR(4),
CcreditSMALLINT,
FOREIGNKEY(Cpno)REFERENCESCourse(Cno)
);
[例7]建立一个“学生选课”表SC
CREATETABLESC
(SnoCHAR(9),
CnoCHAR(4),
GradeSMALLINT,
PRIMARYKEY(Sno,Cno),
/*主码由两个属性构成,必须作为表级完整性进行定义*/
FOREIGNKEY(Sno)REFERENCESStudent(Sno),
/*表级完整性约束条件,Sno是外码,被参照表是Student*/
FOREIGNKEY(Cno)REFERENCESCourse(Cno)
/*表级完整性约束条件,Cno是外码,被参照表是Course*/
);
[例8]向Student表增加“入学时间”列,其数据类型为日期型。
ALTERTABLEStudentADDS_entranceDATE;
▪不论基本表中原来是否已有数据,新增加的列一律为空值。
[例9]将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。
ALTERTABLEStudentALTERCOLUMNSageINT;
[例10]增加课程名称必须取唯一值的约束条件。
ALTERTABLECourseADDUNIQUE(Cname);
[例11]删除Student表
DROPTABLEStudentCASCADE;
[例12]若表上建有视图,选择RESTRICT时表不能删除
CREATEVIEWIS_Student
AS
SELECTSno,Sname,Sage
FROMStudent
WHERESdept='IS';
DROPTABLEStudentRESTRICT;
--ERROR:
cannotdroptableStudentbecauseother
objectsdependonit
如果选择CASCADE时可以删除表,视图也自动被删除
DROPTABLEStudentCASCADE;
--NOTICE:
dropcascadestoviewIS_Student
SELECT*FROMIS_Student;
--ERROR:
relation"IS_Student"doesnotexist
[例13]CREATECLUSTERINDEXStusname
ONStudent(Sname);
[例14]为学生-课程数据库中的Student,Course,SC三个表建立索引。
CREATEUNIQUEINDEXStusnoONStudent(Sno);
CREATEUNIQUEINDEXCoucnoONCourse(Cno);
CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);
[例15]删除Student表的Stusname索引
DROPINDEXStusname;
3.4数据查询
[例1]查询全体学生的学号与姓名。
SELECTSno,Sname
FROMStudent;
[例2]查询全体学生的姓名、学号、所在系。
SELECTSname,Sno,Sdept
FROMStudent;
[例3]查询全体学生的详细记录。
SELECTSno,Sname,Ssex,Sage,Sdept
FROMStudent;
或
SELECT*
FROMStudent;
[例4]查全体学生的姓名及其出生年份。
SELECTSname,2004-Sage/*假定当年的年份为2004年*/
FROMStudent;
输出结果:
Sname2004-Sage
李勇1984
刘晨1985
王敏1986
张立1985
[例5]查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名
SELECTSname,‘YearofBirth:
',2004-Sage,
ISLOWER(Sdept)
FROMStudent;
输出结果:
Sname'YearofBirth:
'2004-SageISLOWER(Sdept)
李勇YearofBirth:
1984cs
刘晨YearofBirth:
1985is
王敏YearofBirth:
1986ma
张立YearofBirth:
1985is
[例6]查询选修了课程的学生学号。
SELECTSnoFROMSC;
等价于:
SELECTALLSnoFROMSC;
执行上面的SELECT语句后,结果为:
Sno
———————
200215121
200215121
200215121
200215122
200215122
[例7]查询计算机科学系全体学生的名单。
SELECTSname
FROMStudent
WHERESdept=‘CS’;
[例8]查询所有年龄在20岁以下的学生姓名及其年龄。
SELECTSname,Sage
FROMStudent
WHERESage<20;
[例9]查询考试成绩有不及格的学生的学号。
SELECTDISTINCTSno
FROMSC
WHEREGrade<60;
[例10]查询年龄在20~23岁(包括20岁和23岁)之间的学生的
姓名、系别和年龄
SELECTSname,Sdept,Sage
FROMStudent
WHERESageBETWEEN20AND23;
[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECTSname,Sdept,Sage
FROMStudent
WHERESageNOTBETWEEN20AND23;
[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdeptIN('IS','MA','CS');
[例13]查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdeptNOTIN('IS','MA','CS');
[例14]查询学号为200215121的学生的详细情况。
SELECT*
FROMStudent
WHERESnoLIKE‘200215121';
等价于:
SELECT*
FROMStudent
WHERESno='200215121';
[例15]查询所有姓刘学生的姓名、学号和性别。
SELECTSname,Sno,Ssex
FROMStudent
WHERESnameLIKE‘刘%’;
[例16]查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECTSname
FROMStudent
WHERESnameLIKE'欧阳__';
[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECTSname,Sno
FROMStudent
WHERESnameLIKE‘__阳%’;
[例18]查询所有不姓刘的学生姓名。
SELECTSname,Sno,Ssex
FROMStudent
WHERESnameNOTLIKE'刘%';
[例19]查询DB_Design课程的课程号和学分。
SELECTCno,Ccredit
FROMCourse
WHERECnameLIKE'DB\_Design'ESCAPE'\‘;
[例20]查询以"DB_"开头,且倒数第3个字符为i的课程的详细情况。
SELECT*
FROMCourse
WHERECnameLIKE'DB\_%i__'ESCAPE'\‘;
[例21]某些学生选修课程后没有参加考试,所以有选课记录,但没
有考试成绩。
查询缺少成绩的学生的学号和相应的课程号。
SELECTSno,Cno
FROMSC
WHEREGradeISNULL
[例22]查所有有成绩的学生学号和课程号。
SELECTSno,Cno
FROMSC
WHEREGradeISNOTNULL;
[例23]查询计算机系年龄在20岁以下的学生姓名。
SELECTSname
FROMStudent
WHERESdept='CS'ANDSage<20;
改写[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdeptIN('IS','MA','CS')
可改写为:
SELECTSname,Ssex
FROMStudent
WHERESdept='IS'ORSdept='MA'ORSdept='CS';
[例24]查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECTSno,Grade
FROMSC
WHERECno='3'
ORDERBYGradeDESC;
[例25]查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT*
FROMStudent
ORDERBYSdept,SageDESC;
[例26]查询学生总人数。
SELECTCOUNT(*)
FROMStudent;
[例27]查询选修了课程的学生人数。
SELECTCOUNT(DISTINCTSno)
FROMSC;
[例28]计算1号课程的学生平均成绩。
SELECTAVG(Grade)
FROMSC
WHERECno='1';
[例29]查询选修1号课程的学生最高分数。
SELECTMAX(Grade)
FROMSC
WHERCno=‘1’;
[例30]查询学生200215012选修课程的总学分数。
SELECTSUM(Ccredit)
FROMSC,Course
WHERSno='200215012'ANDSC.Cno=Course.Cno;
[例31]求各个课程号及相应的选课人数。
SELECTCno,COUNT(Sno)
FROMSC
GROUPBYCno;
查询结果:
CnoCOUNT(Sno)
122
234
344
433
548
[例32]查询选修了3门以上课程的学生学号。
SELECTSno
FROMSC
GROUPBYSno
HAVINGCOUNT(*)>3;
[例33]查询每个学生及其选修课程的情况
SELECTStudent.*,SC.*
FROMStudent,SC
WHEREStudent.Sno=SC.Sno;
查询结果:
Student.Sno
Sname
Ssex
Sage
Sdept
SC.Sno
Cno
Grade
200215121
李勇
男
20
CS
200215121
1
92
200215121
李勇
男
20
CS
200215121
2
85
200215121
李勇
男
20
CS
200215121
3
88
200215122
刘晨
女
19
CS
200215122
2
90
200215122
刘晨
女
19
CS
200215122
3
80
[例34]对[例33]用自然连接完成。
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.Sno;
[例35]查询每一门课的间接先修课(即先修课的先修课)
SELECTFIRST.Cno,SECOND.Cpno
FROMCourseFIRST,CourseSECOND
WHEREFIRST.Cpno=SECOND.Cno;
[例36]改写[例33]
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno);
执行结果:
Student.Sno
Sname
Ssex
Sage
Sdept
Cno
Grade
200215121
李勇
男
20
CS
1
92
200215121
李勇
男
20
CS
2
85
200215121
李勇
男
20
CS
3
88
200215122
刘晨
女
19
CS
2
90
200215122
刘晨
女
19
CS
3
80
200215123
王敏
女
18
MA
NULL
NULL
200215125
张立
男
19
IS
NULL
NULL
[例37]查询选修2号课程且成绩在90分以上的所有学生
SELECTStudent.Sno,Sname
FROMStudent,SC
WHEREStudent.Sno=SC.SnoAND
/*连接谓词*/
SC.Cno=‘2’ANDSC.Grade>90;
/*其他限定条件*/
[例38]查询每个学生的学号、姓名、选修的课程名及成绩
SELECTStudent.Sno,Sname,Cname,Grade
FROMStudent,SC,Course/*多表连接*/
WHEREStudent.Sno=SC.Sno
andSC.Cno=Course.Cno;
[例39]查询与“刘晨”在同一个系学习的学生。
此查询要求可以分步来完成
①确定“刘晨”所在系名
SELECTSdept
FROMStudent
WHERESname='刘晨';
结果为:
CS
②查找所有在IS系学习的学生。
SELECTSno,Sname,Sdept
FROMStudent
WHERESdept='CS';
结果为:
Sno
Sname
Sdept
200215121
李勇
CS
200215122
刘晨
CS
[例40]查询选修了课程名为“信息系统”的学生学号和姓名
SELECTSno,Sname③最后在Student关系中
FROMStudent取出Sno和Sname
WHERESnoIN
(SELECTSno②然后在SC关系中找出选
FROMSC修了3号课程的学生学号
WHERECnoIN
(SELECTCno①首先在Course关系中找出
FROMCourse“信息系统”的课程号,为3号
WHERECname=‘信息系统’
)
);
[例41]找出每个学生超过他选修课程平均成绩的课程号。
SELECTSno,Cno
FROMSCx
WHEREGrade>=(SELECTAVG(Grade)
FROMSCy
WHEREy.Sno=x.Sno);
[例42]查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
SELECTSname,Sage
FROMStudent
WHERESage FROMStudent WHERESdept='CS') ANDSdept<>‘CS'; [例43]查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。 方法一: 用ALL谓词 SELECTSname,Sage FROMStudent WHERESage (SELECTSage FROMStudent WHERESdept='CS') ANDSdept<>'CS’; 方法二: 用聚集函数 SELECTSname,Sage FROMStudent WHERESage< (SELECTMIN(Sage) FROMStudent WHERESdept='CS') ANDSdept<>'CS’; [例44]查询所有选修了1号课程的学生姓名。 SELECTSname FROMStudent WHEREEXISTS (SELECT* FROMSC WHERESno=Student.SnoANDCno='1'); [例45]查询没有选修1号课程的学生姓名。 SELECTSname FROMStudent WHERENOTEXISTS (SELECT* FROMSC WHERESno=Student.SnoANDCno='1'); [例46]查询选修了全部课程的学生姓名。 SELECTSname FROMStudent WHERENOTEXISTS (SELECT* FROMCourse WHERENOTEXISTS (SELECT* FROMSC WHERESno=Student.Sno ANDCno=Course.Cno)); [例47]查询至少选修了学生200215122选修的全部课程的学生号码。 SELECTDISTINCTSno FROMSCSCX WHERENOTEXISTS (SELECT* FROMSCSCY WHERESCY.Sno='200215122'AND NOTEXISTS (SELECT* FROMSCSCZ WHERESCZ.Sno=SCX.SnoAND SCZ.Cno=SCY.Cno)); [例48]查询计算机科学系的学生及年龄不大于19岁的学生。 方法一: SELECT* FROMStudent WHERESdept='CS' UNION SELECT* FROMStudent WHERESage<=19; 方法二: SELECTDISTINCT* FROMStudent WHERESdept='CS'ORSage<=19; [例49]查询选修了课程1或者选修了课程2的学生。 SELECTSno FROMSC WHERECno='1' UNION SELECTSno FROMSC WHERECno='2'; [例50]查询计算机科学系的学生与年龄不大于19岁的学生的交集 SELECT* FROMStudent WHERESdept='CS' INTERSECT SELECT* FROMStudent WHERESage<=19 v实际上就是查询计算机科学系中年龄不大于19岁的学生 SELECT* FROMStudent WHERESdept='CS'ANDSage<=19; [例51]查询选修课程1的学生集合与选修课程2的学生集合的交集 SELECTSno FROMSC WHERECno='1' INTERSECT SELECTSno FROMSC WHERECno='2'; 实际上是查询既选修了课程1又选修了课程2的学生 SELECTSno FROMSC WHERECno='1'ANDSnoIN (SELECTSno FROMSC WHERECno='2'); [例52]查询计算机科学系的学生与年龄不大于19岁的学生的差集。 SELECT* FROMStudent WHERESdept='CS' EXCEPT SELECT* FROMStudent W
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 第三 例题 电子版