数据库例题doc.docx

数据库例题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