实验二交互式SQL语句的使用.docx
《实验二交互式SQL语句的使用.docx》由会员分享，可在线阅读，更多相关《实验二交互式SQL语句的使用.docx（28页珍藏版）》请在冰豆网上搜索。
实验二交互式SQL语句的使用
本科实验报告
课程名称：
数据库系统原理
实验项目：
交互式SQL语句的使用
实验地点：
4606
专业班级：
学号：
学生姓名：
指导教师：
2012年10月17日
一目的与要求
（1）掌握数据库对象的操作过程，包括创建、修改、删除。
（2）熟悉表的各种操作，包括插入、修改、删除、查询。
（3）熟练掌握常用SQL语句的基本语法。
二实验设备与环境
使用SQLServer数据库管理系统提供的SSMS和查询编辑器。
三实验内容、实验记录及实验结果与分析
（1）实验内容
_学生选课系统
_
要求如下：
_建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。
_要求认真进行实验，记录各实验用例及执行结果。
_深入了解各个操作的功能。
（2）实验的具体要求
①数据定义
_基本表的创建、修改及删除
_索引的创建
_视图的创建
②数据操作
_插入数据
_修改数据
_删除数据
③数据查询
_单表查询
_分组统计
_连接查询
_嵌套查询
_集合查询
④视图操作
_创建视图
_视图查询
（3）实验记录、结果
一、数据定义
创建学生选课数据库ST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表所示。
表2-1Student表结构
列名称
用途
类型
长度
约束
备注
Sno
学号
字符
8
主键
Sname
姓名
字符
8
Ssex
性别
字符
2
Sage
年龄
整型
Sdept
所在系
字符
20
Sclass
班级
字符
4
表2-2Course表结构
列名称
用途
类型
长度
约束
备注
Cno
课程号
字符
4
主键
Cname
课程名
字符
40
Cpno
先修课程号
字符
4
Ccredit
学分
整型
表2-3SC表结构
列名称
用途
类型
长度
约束
备注
Sno
学号
字符
8
外键
Cno
课程号
字符
4
Sage
年龄
整型
1．创建、修改及删除基本表
（1）Student表：
CREATETABLEStudent
（SnoCHAR（8）PRIMARYKEY,
SnameCHAR（8）,
SsexCHAR
（2）NOTNULL,
SageINT,
SdeptCHAR（20）
）;
（2）Course表：
CREATETABLECourse
（CnoCHAR（4）PRIMARYKEY,
CnameCHAR（40）NOTNULL,
CpnoCHAR（4）,
CcreditSMALLINT,
）;
（3）SC表：
CREATETABLESC
（SnoCHAR（8）FOREIGNKEY（Sno）REFERENCESStudent（Sno）,
CnoCHAR（4）,
GradeSMALLINT,
）;
（4）检查表是否创建成功
SELECT*FROMStudent;
SELECT*FROMCourse;
SELECT*FROMSC;
（5）修改表结构及约束
增加班级列
ALTERTABLEStudentADDSclasschar（4）;
修改年龄列
ALTERTABLEStudentALTERCOLUMNSagesmallint;
增加约束
ALTERTABLECourseADDUNIQUE（Cname）;
2.创建、删除索引
（1）为Course表按课程名称创建索引
CREATEINDEXiCnameOnCourse（Cname）;
（2）为Student表按学生姓名创建唯一索引
CREATEUNIQUEINDEXiSnameONStudent（Sname）;
（3）为SC表按学号和课程号创建聚集索引
CREATECLUSTEREDINDEXiSnoCnoONSC（Sno,CnoDESC）;
（4）为Course表按课程号创建唯一索引
▼CREATEUNIQUEINDEXiCnoONCourse（Cno）;
3．创建视图
建立信息系学生的视图：
CREATEVIEWIS_Student
AS
SELECTSno,Sname,SageFROMStudent;
WHERESdept='IS';
二、数据操作
1．插入数据
（1）插入到Student表中：
INSERTINTOStudentVALUES（'20100001','李勇','男',20,'CS','1001'）
INSERTINTOStudentVALUES（'20100002','刘晨','女',19,'CS','1001'）
INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,Sclass）
VALUES（'20100021','王敏','女',18,'MA','1002'）
INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,Sclass）
VALUES（'20100031','张立','男',19,'IS','1003'）
INSERTINTOStudent（Sno,Sname,Ssex,Sclass）
VALUES（'20100003','刘洋','女','1001'）
▼INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,Sclass）
VALUES（'20100010','赵斌','男','19','IS','1005'）
▼INSERTINTOStudent
VALUES（'20100022','张明明','男’,'19','CS','1002'）
（2）插入到Course表中：
INSERTINTOCourse（Cno,Cname,Cpno,Ccredit）
VALUES（'1','数据库系统原理','5',4）
INSERTINTOCourse（Cno,Cname,Cpno,Ccredit）
VALUES（'2','高等数学',null,2）
INSERTINTOCourse（Cno,Cname,Cpno,Ccredit）
VALUES（'3','管理信息系统','1',4）
▼INSERTINTOCourse（Cno,Cname,Cpno,Ccredit）
VALUES（'4','操作系统系统原理','6',3）
▼INSERTINTOCourse（Cno,Cname,Cpno,Ccredit）
VALUES（'5','数据结构','7',4）
▼INSERTINTOCourse（Cno,Cname,Cpno,Ccredit）
VALUES（'6','数据处理',null,2）
▼INSERTINTOCourse（Cno,Cname,Cpno,Ccredit）
VALUES（'7','C语言',null,4）
（3）插入到SC表中：
INSERTINTOSCVALUES（'20100001','1',92）
▼INSERTINTOSCVALUES（'20100001','2',85）
▼INSERTINTOSCVALUES（'20100001','3',88）
▼INSERTINTOSCVALUES（'20100002','1',90）
INSERTINTOSCVALUES（'20100002','2',80）
▼INSERTINTOSC（Sno,Cno）VALUES（'20100003','1'）
INSERTINTOSC（Sno,Cno,Grade）VALUES（'20100010','3',null）
（4）多行插入到表中
创建存一个表，保存学生的学号、姓名和年龄：
CREATETABLEcs_Student
（学号char（8）,
姓名char（8）,
年龄smallint
）;
插入数据行：
INSERTINTOcs_Student
SELECTSno,Sname,Sage
FROMstudentWhereSdept='CS';
（5）检查插入到表中的数据
SELECT*FROMStudent
SELECT*FROMCourse
SELECT*FROMSC
2．修改数据
（1）将学生20100001的年龄改为22岁。
UPDATEstudentSETSage=22WHERESno='20100001';
（2）将所有学生的年龄增加一岁：
UPDATEStudentSETSage=Sage+1
（3）填写赵斌同学的管理信息系统课程的成绩：
UPDATESCSETGrade=85
WHERESno='20100010'ANDCno='3'
（4）将计算机科学系全体学生的成绩加5分：
UPDATEscSETGrade=Grade+5
WHERE'CS'=（selectSdept
fromstudent
wherestudent.Sno=sc.Sno）;
（5）▼将刘晨同学的2号课程成绩修改为80：
UPDATESCSETGrade=80
WHERECno='2'ANDSno=（SELECTSnoFROMStudent
WHERESC.Sno=Student.SnoANDSname='刘晨'）;
▼将“20100021”同学的学号修改为“20100025”：
UPDATEStudentSETSno='20100025'
WHERESno='20100021';
3.删除数据
（1）删除学号为201000022的学生记录：
DELETEFROMStudentWHERESno='20100022'
（2）删除学号20100001学生的1号课程选课记录
将选课信息复制到一个临时表tmpSC中：
SELECT*INTOtmpSCFROMSC
在tmpSC中执行删除操作：
DELETEFROMtmpSCWHERESno='20100001'andCno='1'
（3）▼删除临时表中20100002学生的全部选课记录
SELECT*INTOtmpSCFROMSC
DELETEFROMtmpSCWHERESno='20100002'
（4）删除计算机科学系所有学生的选课记录
DELETEFROMtmpSCWHERE'CS'=（selectSdept
fromstudentwhere
student.Sno=tmpSC.Sno）;
（5）删除全部选课记录：
DELETEFROMtmpSC
三、数据查询操作
1．单表查询
（1）按指定目标列查询
查询学生的详细记录：
SELECT*FROMStudent;
查询学生的学号、姓名和年龄
SELECTSno,Sname,SageFROMStudent;
（2）目标列包含表达式的查询
查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
SELECTSname,'YearofBirth:
',2004-Sage,LOWER（Sdept）
FROMStudent;
（3）查询结果集中修改列名称
查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名：
SELECTSname,'YearofBirth:
'asBIRTH,2000-SageBIRTHDAY,
DEPARTMENT=LOWER（Sdept）
FROMStudent;
（4）取消重复行
查询选修了课程的学生学号：
比较ALL和DISTINCT的区别
SELECTSnoFROMSC;
SELECTDISTINCTSnoFROMSC;
（5）简单条件查询
查询计算机科学系全体学生的名单
SELECTSnameFROMStudentWHERESdept='CS';
（6）按范围查询
查询年龄在20~23岁之间的学生的姓名、系别和年龄
SELECTSname,Sdept,Sage
FROMStudent
WHERESageBETWEEN20AND23
（7）查询属性值属于指定集合的行
查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别：
SELECTSname,Ssex
FROMStudent
WHERESdeptIN（'IS','MA','CS'）;
（8）模糊查询
查询所有姓刘学生的姓名、学号和性别
SELECTSname,Sno,Ssex
FROMStudent
WHERESnameLIKE'刘%'
（9）查询空值
查询缺少成绩的学生的学号和相应的课程号
SELECTSno,Cno
FROMsc
WHEREGradeisnull;
（10）多重条件查询
查询计算机科学系年龄在20岁以下的学生姓名：
SELECTSname
FROMstudent
WHERESdept='CS'andSage<20;
（11）结果集排序
查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
SELECT*FROMStudentORDERBYSdept,SageDESC;
（12）▼查询学生基本信息，结果集属性名使用汉字：
SELECTSno学号,Sname姓名,Ssex性别,Sage年龄,Sdept系所,Sclass班级FROMStudent;
▼查询信息系且年龄大于23岁同学的学号和姓名：
SELECTSno,Sname
FROMstudent
WHERESdept='IS'andSage>23;
▼查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系：
SELECTSno,Sname,Sage,Sdept
FROMstudent
WHERESage=17ORSage=18ORSage=20ORSage=23;
▼查询年龄不在21~24岁之间的学生的姓名、系别和年龄：
SELECTSname,Sdept,Sage
FROMstudent
WHERESageNOTBETWEEN21AND24;
2．分组统计
（1）聚集函数的使用
查询学生总人数：
SELECTCOUNT（*）FROMStudent;
查询选修了课程的学生人数：
SELECTCOUNT（DISTINCTSno）FROMSC
查询最高分：
SELECTMAX（Grade）FROMSC
（2）聚集函数作用于部分行
统计2号课程的总分、均分和最高分：
SELECTSUM（grade）总分,AVG（grade）均分,MAX（grade）最高分
FROMscWHERECno='2'
（3）分组统计
统计各门课程的选课人数、均分和最高分：
selectcno课程号,count（*）人数,AVG（grade）均分,MAX（grade）最高分
fromscgroupbyCno
统计均分大于90的课程
selectcno课程号,count（*）人数,AVG（grade）均分,MAX（grade）最高分
fromscgroupbyCno
havingAVG（grade）>90
（4）▼统计每个同学的学号、选课数、平均成绩和最高成绩
SELECTStudent.Sno学号,COUNT（distinctCourse.Cno）选课数,AVG（Sc.Grade）平均成绩,MAX（Sc.Grade）最高成绩
FROMSC
JOINStudentON（SC.Sno=Student.Sno）
JOINCourseON（SC.Cno=Course.Cno）
GROUPBYStudent.Sno;
▼统计每个班的每门课的选课人数、平均成绩和最高成绩
SELECTStudent.Sclass班级,Course.Cname课程名,COUNT（*）选课人数,AVG（Sc.Grade）平均成绩,MAX（Sc.Grade）最高成绩
FROMSC
JOINStudentON（SC.Sno=Student.Sno）
JOINCourseON（SC.Cno=Course.Cno）
GROUPBYStudent.Sclass,Course.Cname
3.连接查询
（1）在WHERE中指定连接条件
查询每个参加选课的学生信息及其选修课程的情况：
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.Sno
查询每一门课的间接先修课：
SELECT*FROMcoursefirst,coursesecond
WHEREfirst.Cpno=second.Cno;
SELECTfirst.Cno,second.CpnoFROMcoursefirst,coursesecond
WHEREfirst.Cpno=second.Cno;
（2）在FROM中指定连接条件
查询每个参加选课的学生信息及其选修课程的情况：
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudentJOINSCON（Student.Sno=SC.Sno）
（3）使用外连接查询
查询每个学生信息及其选修课程的情况：
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudentLEFTOUTERJOINSCON（Student.Sno=SC.Sno）
（4）复合条件连接查询
查询选修2号课程且成绩在90分以上的所有学生：
SELECTStudent.Sno,Sname
FROMStudentjoinSCON（Student.Sno=SC.Sno）
WHERESC.Cno='2'ANDSC.Grade>90;
（5）多表查询
查询每个学生的学号、姓名、选修的课程名及成绩：
SELECTStudent.Sno,Sname,Cname,Grade
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;
（6）▼查询选修了2号课程的同学的学号和姓名
SELECTSno,Sname
FROMStudent
WHEREEXISTS（SELECT*
FROMSC
WHERESno=Student.SnoANDCno='2'）;
▼查询各门课程的课程号、课程名称以及选课学生的学号
SELECTCourse.Cno,Cname,Student.Sno
FROMSC,Course,Student
WHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.Cno;
▼查询选修了数据库系统原理课程的同学的学号和姓名和成绩
SELECTStudent.Sno,Sname,Grade
FROMStudent,Course,SC
WHEREStudent.Sno=SC.SnoAND
Course.Cno=SC.CnoandCname='数据库系统原理';
4.嵌套查询
（1）由In引出的子查询
查询与“刘晨”在同一个系学习的学生：
SELECTSno,Sname,SdeptFROMStudent
WHERESdeptIN（SELECTSdeptFROMStudentWHERESname='刘晨'）;
（2）由比较运算符引出的子查询
找出每个学生超过他选修课程平均成绩的课程号。
SELECTSno,CnoFROMSCx
WHEREGrade>=（SELECTAVG（Grade）
FROMSCy
WHEREy.Sno=x.Sno）;
（3）带修饰符的比较运算符引出的子查询
查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄：
SELECTSname,SageFROMStudent
WHERESageWHERESdept='CS'）
ANDSdept<>'CS';
（4）由EXISTS引出的子查询：
查询所有选修了1号课程的学生姓名
SELECTSnameFROMStudent
WHEREEXISTS（SELECT*
FROMSC
WHERESno=Student.SnoANDCno='1'）;
5.集合查询
（1）集合并
查询计算机科学系的学生及年龄不大于19岁的学生：
SELECT*FROMStudentWHERESdept='CS'
UNION
SELEC
升级会员 