数据库例题doc.docx
《数据库例题doc.docx》由会员分享，可在线阅读，更多相关《数据库例题doc.docx（26页珍藏版）》请在冰豆网上搜索。
数据库例题doc
数据库例题
第三章关系数据库标准语言SQL
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，使用cascade参数，同时删除已经定义的表TAB1
如果使用参数restrict，则表示只删除没有实体表的模式，有实体表则拒绝删除
[例题5]建立一个“学生”表Student
CreatetableStudent
（SnoChar（9）Primarykey,/*列级完整性约束条件，Sno是主码*/
SnameChar（20）unique,/*Sname取唯一值*/
Ssexchar
（2）,
Sagesmallint,
Sdeptchar（20）
）;
说明：
创建一个空的“学生”表Student，并加一些限定条件（Sname的唯一性不合理，用于后面例子进行修改）
[例题6]建立一个“课程”表Course
CreatetableCourse
（CnoChar（4）Primarykey,/*列级完整性约束条件，Cno是主码*/
CnameChar（40）,
Cpnochar（4）,/*Cpno的含义是先修课*/
CcreditSmallint,
foreignkeyCpnoreferencesCourse（Cno）
/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
）;
说明：
参照表与被参照表是同一表
SQL2008需要使用：
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）
/*表级完整性约束条件，Cpno是外码，被参照表是Course*/
）;
说明：
两个主码，同时都是外码
[例题8]向Student表增加“入学时间”列，其数据类型为日期型
Step1:
先创建基本表
CreatetableStudent
（SnoChar（9）Primarykey,/*列级完整性约束条件，Sno是主码*/
SnameChar（20）unique,/*Sname取唯一值*/
Ssexchar
（2）,
Sagechar
（2）,
Sdeptchar（20）
）;
Go
Step2:
altertableStudent2AddS_entranceDatetime;
Go
说明：
不论基本表中原来是否已有数据，新增加列一律为空值
[例题9]将年龄的数据类型由字符型改为整数型
altertableStudentaltercolumnSageint;
[例题10]增加课程名称必须取唯一值的约束条件。
先创建基本表
Step1:
CreatetableCourse
（CnoChar（4）Primarykey,/*列级完整性约束条件，Cno是主码*/
CnameChar（40）,
Cpnochar（4）,/*Cpno的含义是先修课*/
CcreditSmallint,
foreignkeyCpnoreferencesCourse（Cno）
/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
）;
Go
Step2:
altertableCourseaddunique（Cname）;
Go
[例题11]删除Student表
droptablestudent2cascade;
droptablestudent2;--cascade;--SQLSever中不支持cascade参数
[例题12]若表上建有视图，选择restrict时表不能删除；cascade时可以删除表，视图也自动被删除。
--本例在SQLServer上不能通过测试
/*Student表上建立视图*/
createviewIS_Student
AS
selectSno,Sname,Sage
FromStudent
WhereSdept='IS';
/*删除Student表*/
droptableStudentrestrict;--errorXXX
/*删除Student表*/
droptableStudentcascade;--successXXX
select*fromIS_Student;--error
[例题13]创建聚簇索引
createclusterindexStusnameONStudent（Sname）;--XXX
createclusteredindexStusnameONStudent（Sname）;
[例题14]创建unique索引
createuniqueindexStusnoONStudent（Sno）;
createuniqueindexCoucnoONCourse（Cno）;
createuniqueindexSCnoONSC（Snoasc,Cnodesc）;
[例题15]删除索引
--dropindexStusname;--XXX
dropindexCourse.Coucno;
在SQLServer中需要指定索引的表名称
3.4数据查询
/*3.4数据查询
*查询示例需预先录入的数据
*/
INSERTINTO[myDB].[dbo].[Student]
（[Sno],[Sname],[Ssex],[Sage],[Sdept]）
VALUES
（200215121,'李勇','男',20,'CS'）;
INSERTINTO[myDB].[dbo].[Student]
（[Sno],[Sname],[Ssex],[Sage],[Sdept]）
VALUES
（200215122,'刘晨','女',19,'CS'）;
INSERTINTO[myDB].[dbo].[Student]
（[Sno],[Sname],[Ssex],[Sage],[Sdept]）
VALUES
（200215123,'王敏','女',19,'MA'）;
INSERTINTO[myDB].[dbo].[Student]
（[Sno],[Sname],[Ssex],[Sage],[Sdept]）
VALUES
（200215125,'张立','男',18,'IS'）;
/*受参照关系影响，不能按课程号顺序插入数据*/
INSERTINTO[myDB].[dbo].[Course]
（[Cno],[Cname],[Ccredit]）
VALUES
（2,'数学',2）;
INSERTINTO[myDB].[dbo].[Course]
（[Cno],[Cname],[Ccredit]）
VALUES
（6,'数据处理',2）;
INSERTINTO[myDB].[dbo].[Course]
（[Cno],[Cname],[Cpno],[Ccredit]）
VALUES
（4,'操作系统',6,3）;
INSERTINTO[myDB].[dbo].[Course]
（[Cno],[Cname],[Cpno],[Ccredit]）
VALUES
（7,'PASCAL语言',6,4）;
INSERTINTO[myDB].[dbo].[Course]
（[Cno],[Cname],[Cpno],[Ccredit]）
VALUES
（5,'数据结构',7,4）;
INSERTINTO[myDB].[dbo].[Course]
（[Cno],[Cname],[Cpno],[Ccredit]）
VALUES
（1,'数据库',5,4）;
INSERTINTO[myDB].[dbo].[Course]
（[Cno],[Cname],[Cpno],[Ccredit]）
VALUES
（3,'信息系统',1,4）;
INSERTINTO[myDB].[dbo].[SC]
（[Sno],[Cno],[Grade]）
VALUES
（200215121,1,92）;
INSERTINTO[myDB].[dbo].[SC]
（[Sno],[Cno],[Grade]）
VALUES
（200215121,2,85）;
INSERTINTO[myDB].[dbo].[SC]
（[Sno],[Cno],[Grade]）
VALUES
（200215121,3,88）;
INSERTINTO[myDB].[dbo].[SC]
（[Sno],[Cno],[Grade]）
VALUES
（200215122,2,90）;
INSERTINTO[myDB].[dbo].[SC]
（[Sno],[Cno],[Grade]）
VALUES
（200215122,3,80）;
[例题16]查询全体学生的学号与姓名
selectSno,Sname
FromStudent;
[例题17]查询全体学生的姓名、学号、所在系
selectSname,Sno,Sdept
FromStudent;
[例题18]查询全体学生的详细记录
select*
FromStudent;
[例题19]查询全体学生的姓名及其出生年份
selectSname,2004-Sage
FromStudent;
[例题20]查询全体学生的姓名、出生年份和所在院系（小写表示）
selectSname,'YearofBirth:
',2004-Sage,lower（Sdept）
FromStudent;
selectSnameName,'YearofBirth:
'Birth,2004-SageBirthDay,lower（Sdept）Deparyment
FromStudent;
[例题21]查询选修了课程的学生学号
selectSno
FromSC;
selectdistinctSno
FromSC;
[例题22]查询计算机系全体学生的名单
selectSname
FromStudent
whereSdept='CS';
[例题23]查询20岁以下的学生姓名及年龄
selectSname,Sage
FromStudent
whereSage<20;
[例题24]查询成绩不及格的学生的学号
selectdistinctSno
FromSC
whereGrade<60;
[例题25]查询年龄在20－23之间
selectSname,Sdept,Sage
FromStudent
whereSagebetween20and23;
[例题26]查询年龄不在20－23之间
selectSname,Sdept,Sage
FromStudent
whereSagenotbetween20and23;
[例题27]查询CS、MA、IS系的学生姓名和性别
selectSname,Sdept,Sage
FromStudent
whereSdeptin（'CS','MA','IS'）;
[例题28]查询非CS、MA系的学生姓名和性别
selectSname,Sdept,Sage
FromStudent
whereSdeptnotin（'CS','MA'）;
[例题29]查询学号为200215121的学生详细情况
select*
FromStudent
whereSnolike'200215121';
select*
FromStudent
whereSno='200215121';
[例题30]查询刘姓学生详细情况
select*
FromStudent
whereSnamelike'刘%';
[例题31]查询姓“欧阳”且命名为3个汉字的学生姓名
INSERTINTO[myDB].[dbo].[Student]
（[Sno],[Sname],[Ssex],[Sage],[Sdept]）
VALUES
（200215126,'欧阳天','男',18,'IS'）;
INSERTINTO[myDB].[dbo].[Student]
（[Sno],[Sname],[Ssex],[Sage],[Sdept]）
VALUES
（200215127,'欧阳天地','男',18,'IS'）;
selectSname
FromStudent
whereSnamelike'欧阳__';
--SQL标准中，1个_代表一个汉字
--2008版本中，'欧阳__'与'欧阳_'有差异，尤其是单个_可以代表一个汉字
[例题32]查询第2个字是“阳”的学生姓名
selectSname,Sno
FromStudent
whereSnamelike'_阳%';--此处的_数量有不同结果
[例题33]查询不姓“刘”的学生姓名
selectSname,Sno,Ssex
FromStudent
whereSnamenotlike'刘%';
[例题34]查询DB_Design课程的课程号和学分
INSERTINTO[myDB].[dbo].[Course]
（[Cno],[Cname],[Ccredit]）
VALUES
（8,'DB_Design',2）;
select*
FromCourse
whereCnamelike'DB\_Design'escape'\';--使用转义符
[例题35]查询“DB_”开头，且倒数第3个字符为i的课程的课程号和学分
INSERTINTO[myDB].[dbo].[Course]
（[Cno],[Cname],[Ccredit]）
VALUES
（9,'DB_Intro',2）;
select*
FromCourse
whereCnamelike'DB\_%i__'escape'\';--使用转义符
[例题36]查没有成绩的学生
INSERTINTO[myDB].[dbo].[SC]
（[Sno],[Cno]）
VALUES
（200215122,1）;
selectSno,Cno
FromSC
whereGradeisnull;
[例题37]查所有有成绩的学生
selectSno,Cno
FromSC
whereGradeisnotnull;
selectdistinctSno
FromSC
whereGradeisnotnull;
[例题38]查计算机科学系年龄在20岁以下的学生
selectSname,Sage
FromStudent
whereSdept='CS'andSage<20;
[例题39]查询选修了3号课程的学生，按分数的降序排列
selectSno,Grade
FromSC
whereCno='3'
orderbygradedesc;--asc/desc
[例题40]查询所有学生，按系号升序，年龄降序排列
select*
FromStudent
orderbySdept,Sagedesc;--asc/desc
[例题41]查询学生总人数
selectCOUNT（*）
FromStudent;
[例题42]查询选修课程的学生总人数
selectCOUNT（distinctSno）
FromSC;
[例题43]查询选修2号课程的学生平均成绩
selectAvg（Grade）
FromSC
whereCno='2';
[例题44]查询选修1号课程的学生最高分
selectMax（Grade）
FromSC;
[例题45]查询200215121学生选修课程平均成绩
selectSum（Grade）
FromSC,Course
whereSno='200215121'andSc.Cno=Course.Cno;
[例题46]查询各个课程号及相应的选课人数
selectCno,Count（Sno）'人数'
FromSC
GroupbyCno;
[例题47]查询选修了3门以上课程的学生学号
selectSno
FromSC
GroupbySno
HavingCOUNT（*）>3;
[例题48]查询每个学生及其选修课程的情况
selectSno
FromSC
GroupbySno
HavingCOUNT（*）>3;
[例题49]自然连接实现查询每个学生及其选修课程的情况
selectStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FromStudent,SC
whereStudent.Sno=Sc.Sno;
[例题50]自身连接查询间接先修课程的情况
selectFirst.Cno,Second.Cpno
FromCourseFirst,CourseSecond
whereFirst.Cpno=Second.Cno;
/*取别名*/
--select*FromStudentasFirst;
--select*FromStudentasSecond;
--go
[例题51]外连接（不兼容）
/*out->outer;leftouterjoin=leftjoin*/
selectStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FromStudentleftoutJoinSCOn（Student.Sno=Sc.Sno）;--outer
/*也可以使用Using来去掉结果中的重复值*/
--FromStudentleftoutJoinSCUsing（SC.Sno）;
selectStudent.Sno,Sname,Ssex,Sage,Sdept,Sc.Cno,Sc.Grade
FromStudentleftJoinSCOn（Student.Sno=Sc.Sno）;
[例题52]查询选修2号课程且成绩在90分以上的所有学生
selectStudent.Sno,Sname
FromStudent,SC
whereStudent.Sno=Sc.Snoand/*连接谓词and*/
SC.Cno='2'andSC.Grade>90;/*其他限定条件*/
[例题53]查询每个学生的学号、姓名、选修的课程名及成绩
selectStudent.Sno,Sname,Cname,Grade
FromStudent,SC,Course
whereStudent.Sno=Sc.SnoandSc.Cno=Course.Cno;
[例题54]查询与“刘晨”在同一个系学习的学生
分步查询：
Step1：
确定“刘晨”所在的系
selectSdept
FromStudent
whereSname='刘晨';
Step2：
查找所有该系的学生
selectSno,Sname,Sdept
FromStudent
whereSdept='CS';
嵌套查询：
（解法1）
selectSno,Sname,Sdept
FromStudent
whereSdeptin
（selectSdept
FromStudent
whereSname='刘晨'）;
自身连接：
（解法2）
selectS1.Sno,S1.Sname,S1.Sdept
FromStudentS1,StudentS2
whereS1.Sdept=S2.SdeptandS1.Sname='刘晨';
解法1、2表明，同一个查询可以有多个查询方法，其执行效率可能会有较大差别----这就是高水平开发人员所关心内容！
[例题55]查询选修了课程为“信息系统”的学生学号和姓名
嵌套查询：
（解法）
selectSno,Sname
FromStudent
whereSnoIn
（selectSno
FromSC
whereCnoin
（selectCno
FromCourse
whereCname='信息系统'））;
连接查询：
（解法）
selectStudent.Sno,Sname
FromStudent,SC,Course
whereStudent.Sno=SC.SnoAnd
SC.Cno=Course.CnoAnd
Course.Cname='信息系统';
[例题56]找出每个学生超过他选修课平均成绩的课程号
selectSno,Cno
Fr
升级会员 