实验三:交互式SQL语句的使用
欧阳光明(2021.03.07)
1、实验目的
(1)掌握数据库对象的操作过程,包括创建、修改、删除 (2)熟悉表的各种操作,包括插入、修改、删除、查询 (3)熟练掌握常用SQL语句的基本语法 2、实验平台
使用SQL Server提供的Microsoft SQL Server Management Studio工具,交互式使用SQL语句。 3 实验内容及要求
选择如下一个应用背景之一:
学生选课系统
习题3、4、和5中使用的数据库 其它你熟悉的应用
(1)建立一个数据库和相关的表、索引、视图等数据库对象,练习对表、索引和视图的各种操作。
(2)要求认真进行实验,记录各实验用例及执行结果。 (3)深入了解各个操作的功能。
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
实验要求包括如下方面的内容: 3.1 数据定义
1. 基本表的创建、修改及删除 2. 索引的创建 3. 视图的创建 3.2 数据操作
完成各类更新操作包括: 1. 插入数据 2. 修改数据 3. 删除数据 3.3 数据查询操作 完成各类查询操作
1. 单表查询 2. 分组统计 3. 连接查询 4. 嵌套查询 5. 集合查询 3.4 数据操作
1. 创建视图
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
2. 视图查询 参考示例:
建立一个学生选课数据库,练习对表、视图和索引等数据库对象的各种操作。 一、数据定义
创建学生选课数据库ST,包括三个基本表,其中Student表
保存学生基本信息,Course表保存课程信息,SC表保存学生选课信息,其结构如下表:
表1. Student表结构
列名称 Sno Sname Ssex Sage Sdept Sclass 用途 学号 姓名 性别 年龄 所在系 班级 类型 字符 字符 字符 整型 字符 字符 8 8 2 20 4 长度 约束 主键 备注 表2. Course表结构
列名称 Cno Cname Cpno Ccredit 用途 课程号 课程名 先修课程号 学分 类型 字符 字符 字符 整型 4 40 4 长度 约束 主键 备注 表3. SC表结构
列名称 Sno Cno Grade 用途 学号 课程号 成绩 类型 字符 字符 整型 长度 8 4 约束 外键 备注 *欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
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)创建员工表Employee
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
CREATETABLEEmployee (
编号CHAR(8)PRIMARYKEY, 姓名VARCHAR(8)notnull 部门CHR(40), 工资numeric(8,2), 生日datetime, 职称char(20),
);
指出该语句中的错误并改正后执行。 (5)检查表是否创建成功 SELECT*FROMStudent SELECT*FROMCourse SELECT*FROMSC SELECT*FROMEmployee (6)修改表结构及约束
增加班级列
ALTERTABLEStudentADDSclasschar(4)
修改年龄列
ALTERTABLEStudentALTERCOLUMNSagesmallint
增加约束
ALTERTABLECourseADDUNIQUE(Cname)
(7)删除表
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
DROPTABLEEmployee
2.创建索引
(1)为Course表按课程名称创建索引
CREATEINDEXiCnameOnCourse(Cname) (2)为Student表按学生姓名创建唯一索引
CREATEUNIQUEINDEXiSnameONStudent(Sname)
(3)为SC表按学号和课程号创建聚集索引
CREATECLUSTEREDINDEXiSnoCnoOnSC(Sno,Cnodesc) (4)为Course表按课程号创建唯一索引
请自己完成该操作
3.创建视图
建立信息系学生的视图: CREATEVIEWIS_Student AS
SELECTSno,Sname,SageFROMStudent WHERESdept='IS'; 3.2 数据操作 1. 插入数据
将如下表格中的数据分别插入到数据库相应的表中:
表4.学生基本信息表
学号 姓名 性别 年龄 所在系 班级 *欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07 20100001 20100002 20100021 20100031 20100003 20100010 20100022 李勇 刘晨 王敏 张立 刘洋 赵斌 张明明 男 女 女 男 女 男 男 20 19 18 19 19 19 CS CS MA IS IS CS 1001 1001 1002 1003 1001 1005 1002 表5.课程信息表
课程号 1 2 3 4 5 6 7 课程名 数据库系统原理 高等数学 管理信息系统 操作系统原理 数据结构 数据处理 C语言 先修课程号 56 1 6 7 学分 4 2 4 3 4 2 4 表6.学生选课信息表
学号 20100001 20100001 20100001 20100002 20100002 20100003 20100010 课程号 1 2 3 1 2 1 3 成绩 92 85 88 90 80 (1)插入到Student表
INSERTINTOStudentVALUES('20100001','李勇','男',20,'CS','1001')
INSERTINTOStudentVALUES('20100002','刘晨','女
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
',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')
检查下列语句中的错误,并改正:
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,sclass) VALUES('20100010',赵斌,'男','19','IS','1005')
INSERT INTO Student 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)
请写出插入其余行的插入语句,并插入数据。 (3)插入到SC表
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
INSERTINTOSCVALUES('20100001','1',92) 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
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
2. 修改数据
(1)将学生20100001的年龄改为22岁。
UPDATEstudentSETSage= 22 WHERESno='20100001'; (2)将所有学生的年龄增加一岁。
UPDATEStudentSETSage=Sage+1
(3)填写赵斌同学的管理信息系统课程的成绩 UPDATESCSETGrade= 85
WHERESno='20100010'ANDCno='3'
(4)将计算机科学系全体学生的成绩加5分 UPDATEscSETGrade=Grade+ 5
WHERE'CS'=(selectSdeptfromstudentwherestudent.Sno=sc.Sno); (5)请自己完成如下操作
将刘晨同学的2号课程成绩修改为80
将“20100021”同学的学号修改为“20100025”
(6)检查数据是否修改 3. 删除数据
(1)删除学号为201000022的学生记录 DELETEFROMStudentWHERESno='20100022' (2)删除学号20100001学生的1号课程选课记录 将选课信息复制到一个临时表tmpSC中:
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
SELECT*INTOtmpSCFROMSC 在tmpSC中执行删除操作:
DELETEFROMtmpSCWHERESno='20100001'andCno='1' (3)删除临时表中20100002学生的全部选课记录 请自己完成该操作。
(4)删除计算机科学系所有学生的选课记录
DELETEFROMtmpSCWHERE'CS'=(selectSdeptfromstudentwherestudent.Sno=tmpSC.Sno); (5)删除全部选课记录 DELETEFROMtmpSC (6)检查数据是否删除 3.3 数据查询操作 完成如下查询操作: 1. 单表查询
(1)按指定目标列查询
查询学生的详细记录:
SELECT*FROMStudent;
查询学生的学号、姓名和年龄
SELECTSno,Sname,SageFROMStudent; (2)目标列包含表达式的查询
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。
SELECTSname,'Year of Birth: ',2004-Sage,LOWER(Sdept)FROMStudent; (3)查询结果集中修改列名称
查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。
SELECTSname,'Year of Birth:'asBIRTH, 2000-SageBIRTHDAY,DEPARTMENT=LOWER(Sdept) FROMStudent; (4)取消重复行
查询选修了课程的学生学号:比较ALL和DISTINCT的区别
SELECTSnoFROMSC;
SELECTDISTINCTSnoFROMSC; (5)简单条件查询
查询计算机科学系全体学生的名单
SELECTSnameFROMStudent WHERESdept='CS'; (6)按范围查询
查询年龄在20~23岁之间的学生的姓名、系别和年龄
SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN 20 AND 23
(7)查询属性值属于指定集合的行
查询信息系(IS)、数学系(MA)和计算机科学系(CS)
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
学生的姓名和性别
SELECTSname,SsexFROMStudentWHERESdeptIN('IS','MA','CS'); (8)模糊查询
查询所有姓刘学生的姓名、学号和性别
SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'刘%' (9)查询空值
查询缺少成绩的学生的学号和相应的课程号
SELECTSno,CnoFROMscWHEREGradeisnull; (10)多重条件查询
查询计算机科学系年龄在岁以下的学生姓名
SELECTSnameFROMstudentWHERESdept='CS'andSage<20; (11)结果集排序
查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列
SELECT*FROMStudent ORDERBYSdept,SageDESC; (12)完成下列查询
查询学生基本信息,结果集属性名使用汉字 查询信息系且年龄大于23岁同学的学号和姓名
查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系
查询年龄不在21~24岁之间的学生的姓名、系别和年龄
2. 分组统计
(1)聚集函数的使用
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
查询学生总人数
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)完成下面的查询
统计每个同学的学号、选课数、平均成绩和最高成绩 统计每个班的每门课的选课人数、平均成绩和最高成绩
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
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)复合条件连接查询
查询选修号课程且成绩在分以上的所有学生
SELECTStudent.Sno,Sname
FROMStudentjoinSCON (Student.Sno=SC.Sno)/* 连接条件*/
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
WHERESC.Cno='2'ANDSC.Grade> 90;/* 过滤条件*/ (5)多表查询
查询每个学生的学号、姓名、选修的课程名及成绩
SELECTStudent.Sno,Sname,Cname,Grade FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno; (6)完成下列查询
查询选修了2号课程的同学的学号和姓名
查询各门课程的课程号、课程名称以及选课学生的学号 查询选修了数据库系统原理课程的同学的学号和姓名和成绩
4. 嵌套查询
(1)由In引出的子查询
查询与“刘晨”在同一个系学习的学生
SELECTSno,Sname,Sdept FROMStudent
WHERESdeptIN(SELECTSdeptFROMStudent WHERESname='刘晨');
(2)由比较运算符引出的子查询
找出每个学生超过他选修课程平均成绩的课程号。
SELECTSno,CnoFROMSCx
WHEREGrade>=(SELECTAVG(Grade)FROMSCy WHEREy.Sno=x.Sno);
(3)带修饰符的比较运算符引出的子查询
查询其他系中比计算机科学系所有学生年龄都小的学生姓名
*欧阳光明*创编 2021.03.07
*欧阳光明*创编 2021.03.07
及年龄。
SELECTSname,SageFROMStudent
WHERESage (4)由EXISTS引出的子查询 查询所有选修了1号课程的学生姓名 SELECTSnameFROMStudent WHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1') 5. 集合查询 (1)集合并 查询计算机科学系的学生及年龄不大于19岁的学生 SELECT*FROMStudentWHERESdept='CS' UNION SELECT*FROMStudentWHERESage<=19 (2)集合交 查询计算机科学系且年龄不大于19岁的学生 SELECT*FROMStudentWHERESdept='CS' INTERSECT SELECT*FROMStudentWHERESage<=19 (3)集合差 *欧阳光明*创编 2021.03.07 *欧阳光明*创编 2021.03.07 查询计算机科学系且年龄大于19岁的学生 SELECT*FROMStudentWHERESdept='CS' EXCEPT SELECT*FROMStudentWHERESage<=19; 3.4 视图操作 建立视图并基于视图进行查询: 1. 创建视图 (1)建立学生基本信息视图 CREATEVIEWStudent_VIEW(学号,姓名,性别,年龄,系,班级) AS SELECTSno,Sname,Ssex,Sage,Sdept,Sclass FROMStudent; (2)建立学生均分视图 CREATEVIEWS_G(Sno,Gavg) AS SELECTSno,avg(Grade) FROMSCGROUPBYSno; (3)建立选课信息视图 CREATEVIEWXK_VIEW AS SELECTStudent.*,Course.*,Grade *欧阳光明*创编 2021.03.07 *欧阳光明*创编 2021.03.07 FROMStudent,SC,Course WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno; 2. 视图查询 (1)查询学生基本信息 SELECT*FROMStudent_VIEW (2)找出每个学生超过他选修课程平均成绩的课程号 SELECTSC.Sno,Cno,grade FROMSC,S_G WHERESC.Sno=S_G.SnoandGrade>=S_G.Gavg (3)查询每个学生的学号、姓名、选修的课程名及成绩 SELECTSno,Sname,Cname,Grade FROMXK_VIEW (4)比较使用视图查询和直接从基表查询的优点 *欧阳光明*创编 2021.03.07 因篇幅问题不能全部显示,请点此查看更多更全内容