实验二交互式SQL语句的使用Word文档格式.docx
- 文档编号:7834869
- 上传时间:2023-05-09
- 格式:DOCX
- 页数:28
- 大小:788.34KB
实验二交互式SQL语句的使用Word文档格式.docx
《实验二交互式SQL语句的使用Word文档格式.docx》由会员分享,可在线阅读,更多相关《实验二交互式SQL语句的使用Word文档格式.docx(28页珍藏版)》请在冰点文库上搜索。
_集合查询
④视图操作
_创建视图
_视图查询
(3)实验记录、结果
一、数据定义
创建学生选课数据库ST,包括三个基本表,其中Student表保存学生基本信息,Course表保存课程信息,SC表保存学生选课信息,其结构如下表所示。
表2-1Student表结构
列名称
用途
类型
长度
约束
备注
Sno
学号
字符
8
主键
Sname
姓名
Ssex
性别
2
Sage
年龄
整型
Sdept
所在系
20
Sclass
班级
4
表2-2Course表结构
Cno
课程号
Cname
课程名
40
Cpno
先修课程号
Ccredit
学分
表2-3SC表结构
外键
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'
)
20100002'
刘晨'
女'
19,'
INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,Sclass)
VALUES('
20100021'
王敏'
18,'
MA'
1002'
20100031'
张立'
1003'
INSERTINTOStudent(Sno,Sname,Ssex,Sclass)
20100003'
刘洋'
▼INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,Sclass)
VALUES('
20100010'
赵斌'
19'
1005'
)
▼INSERTINTOStudent
20100022'
张明明'
男’,'
(2)插入到Course表中:
INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)
1'
数据库系统原理'
5'
4)
2'
高等数学'
null,2)
3'
管理信息系统'
▼INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)
4'
操作系统系统原理'
6'
3)
数据结构'
7'
数据处理'
null,2)
C语言'
null,4)
(3)插入到SC表中:
INSERTINTOSCVALUES('
92)
▼INSERTINTOSCVALUES('
85)
88)
90)
80)
▼INSERTINTOSC(Sno,Cno)VALUES('
INSERTINTOSC(Sno,Cno,Grade)VALUES('
null)
(4)多行插入到表中
创建存一个表,保存学生的学号、姓名和年龄:
CREATETABLEcs_Student
(学号char(8),
姓名char(8),
年龄smallint
);
插入数据行:
INSERTINTOcs_Student
SELECTSno,Sname,Sage
FROMstudentWhereSdept='
(5)检查插入到表中的数据
SELECT*FROMStudent
SELECT*FROMCourse
SELECT*FROMSC
2.修改数据
(1)将学生20100001的年龄改为22岁。
UPDATEstudentSETSage=22WHERESno='
(2)将所有学生的年龄增加一岁:
UPDATEStudentSETSage=Sage+1
(3)填写赵斌同学的管理信息系统课程的成绩:
UPDATESCSETGrade=85
WHERESno='
ANDCno='
(4)将计算机科学系全体学生的成绩加5分:
UPDATEscSETGrade=Grade+5
WHERE'
=(selectSdept
fromstudent
wherestudent.Sno=sc.Sno);
(5)▼将刘晨同学的2号课程成绩修改为80:
UPDATESCSETGrade=80
WHERECno='
ANDSno=(SELECTSnoFROMStudent
WHERESC.Sno=Student.SnoANDSname='
);
▼将“20100021”同学的学号修改为“20100025”:
UPDATEStudentSETSno='
20100025'
WHERESno='
3.删除数据
(1)删除学号为201000022的学生记录:
DELETEFROMStudentWHERESno='
(2)删除学号20100001学生的1号课程选课记录
将选课信息复制到一个临时表tmpSC中:
SELECT*INTOtmpSCFROMSC
在tmpSC中执行删除操作:
DELETEFROMtmpSCWHERESno='
andCno='
(3)▼删除临时表中20100002学生的全部选课记录
(4)删除计算机科学系所有学生的选课记录
DELETEFROMtmpSCWHERE'
fromstudentwhere
student.Sno=tmpSC.Sno);
(5)删除全部选课记录:
DELETEFROMtmpSC
三、数据查询操作
1.单表查询
(1)按指定目标列查询
查询学生的详细记录:
SELECT*FROMStudent;
查询学生的学号、姓名和年龄
SELECTSno,Sname,SageFROMStudent;
(2)目标列包含表达式的查询
查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。
SELECTSname,'
YearofBirth:
'
2004-Sage,LOWER(Sdept)
FROMStudent;
(3)查询结果集中修改列名称
查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名:
SELECTSname,'
'
asBIRTH,2000-SageBIRTHDAY,
DEPARTMENT=LOWER(Sdept)
(4)取消重复行
查询选修了课程的学生学号:
比较ALL和DISTINCT的区别
SELECTSnoFROMSC;
SELECTDISTINCTSnoFROMSC;
(5)简单条件查询
查询计算机科学系全体学生的名单
SELECTSnameFROMStudentWHERESdept='
(6)按范围查询
查询年龄在20~23岁之间的学生的姓名、系别和年龄
SELECTSname,Sdept,Sage
FROMStudent
WHERESageBETWEEN20AND23
(7)查询属性值属于指定集合的行
查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别:
SELECTSname,Ssex
WHERESdeptIN('
(8)模糊查询
查询所有姓刘学生的姓名、学号和性别
SELECTSname,Sno,Ssex
WHERESnameLIKE'
刘%'
(9)查询空值
查询缺少成绩的学生的学号和相应的课程号
SELECTSno,Cno
FROMsc
WHEREGradeisnull;
(10)多重条件查询
查询计算机科学系年龄在20岁以下的学生姓名:
SELECTSname
FROMstudent
WHERESdept='
andSage<
20;
(11)结果集排序
查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT*FROMStudentORDERBYSdept,SageDESC;
(12)▼查询学生基本信息,结果集属性名使用汉字:
SELECTSno学号,Sname姓名,Ssex性别,Sage年龄,Sdept系所,Sclass班级FROMStudent;
▼查询信息系且年龄大于23岁同学的学号和姓名:
SELECTSno,Sname
andSage>
23;
▼查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系:
SELECTSno,Sname,Sage,Sdept
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='
(3)分组统计
统计各门课程的选课人数、均分和最高分:
selectcno课程号,count(*)人数,AVG(grade)均分,MAX(grade)最高分
fromscgroupbyCno
统计均分大于90的课程
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)最高成绩
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
(2)在FROM中指定连接条件
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudentJOINSCON(Student.Sno=SC.Sno)
(3)使用外连接查询
查询每个学生信息及其选修课程的情况:
FROMStudentLEFTOUTERJOINSCON(Student.Sno=SC.Sno)
(4)复合条件连接查询
查询选修2号课程且成绩在90分以上的所有学生:
SELECTStudent.Sno,Sname
FROMStudentjoinSCON(Student.Sno=SC.Sno)
WHERESC.Cno='
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='
▼查询各门课程的课程号、课程名称以及选课学生的学号
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
WHERESage<
ALL(SELECTSageFROMStudent
WHERESdept='
ANDSdept<
>
;
(4)由EXISTS引出的子查询:
查询所有选修了1号课程的学生姓名
SELECTSnameFROMStudent
WHEREEXISTS(SELECT*
FROMSC
WHERESno=Student.SnoANDCno='
5.集合查询
(1)集合并
查询计算机科学系的学生及年龄不大于19岁的学生:
SELECT*FROMStudentWHERESdept='
UNION
SELEC
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验二 交互式SQL语句的使用 实验 交互式 SQL 语句 使用