数据库上机实验.docx
数据库上机实验 创建数据表与数据输入 Part I.运用SQL Server Management Studio创建数据表和输入数据 1.在SQL Server Management Studio中创建数据表 P69 1.（1）-（6） 2.为数据表输入数据 P76 4.（1）-（4） 3.数据阅读 P77 1.（1）-（2） 2.（1）-（3） Part II.运用SQL语句创建数据表和输入数据 1.运用SQL语句创建数据表 P72.例3-2 2运用SQL语句输入数据 P82.例3.8 习题：P.105 （1）（2）insert 数据操作Insert、Update、Delete P105 3.上机练习题 （2）（4） Insert (100001, 1000, 2002-12-18 0:00:00) (100002, 2000, 2022-3-20 0:00:00) Update (100001, 1500, 2002-12-18 0:00:00) (100002, 2000, 2022-9-25 0:00:00) Delete Transact-SQL语句基础1 1.将teaching数据库中score表的studentno列设置为引用表student的外键。 ALTER TABLE Score ADD CONSTRAINT FK_score_student FOREIGN KEY (studentno) REFERENCES student(studentno) 2.将teaching数据库中cla表的claname创建UNIQUE约束。 ALTER TABLE cla ADD CONSTRAINT UQ_cla UNIQUE(claname) 执行如下插入语句，查看提示信息 INSERT INTO cla VALUES(090602, 计算机0902, 计算机学院, 马文斐) 3.为teaching数据库中student表的birthday列创建CHECK约束，规定学生的年龄在17-25岁之间。 ALTER TABLE student ADD CONSTRAINT CK_birthday CHECK(YEAR(GETDATE()-YEAR(birthday) BETWEEN 17 AND 25 执行如下插入语句，查看提示信息 INSERT INTO student(studentno, sname, sex, birthday, clano) VALUES (0922221328, 张源, 男, 1983-04-05, 090501) 提示：表达式YEAR(GETDATE()-YEAR(birthday) 4.为teaching数据库创建规则prof_rule，规定老师职称取值只能为助教,讲师,副教授,教授，并将其绑定到teacher表的prof列上。 CREATE RULE prof_rule AS prof IN(助教,讲师,副教授,教授) EXEC sp_bindrule prof_rule, teacher.prof 执行如下插入语句，查看提示信息 INSERT INTO teacher VALUES(t05002, 张源, 软件工程, 工程师, 计算机学院) 提示：表达式IN(职称列表) 5.编写程序，输出在1-3000之间能被17整除的最大数值 提示：可使循环限制变量从最大值起先，逐步削减，第一个满意被17整除的数值即为所求解的结果，可通过BREAK语句跳出循环。（假如使循环限制变量从小到大逐步增加，则循环次数将大大增加，程序执行效率将下降。） PRINT 1-3000之间能被17整除的最大数值为： +CAST(i AS CHAR(4) DECLARE s INT, i INT SELECT s=0, i=3000 WHILE i>=1 BEGIN IF i%17=0 BEGIN PRINT 1-3000之间能被17整除的最大数值为： + CAST(i AS CHAR(4) BREAK END i = i-1 END Transact-SQL语句基础2 1.利用Transact-SQL语句声明一个长度为16的nchar型变量bookname，并赋初值为“SQL Server数据库编程”，打印该变量。 2.编程求50-100之间的全部能被3整除的奇数之和。 3.编写程序，声明CHAR型变量ch，并赋初值。推断字符变量ch中存放的是字母、数字字符还是其他字符，并输出相关信息。（提示：UPPER函数可以将小写字母转换为大写字母） 4.编写程序，求解如下分数序列的前20项之和并打印输出结果。 S=2/1+3/2+5/3+8/5+13/8+21/13+ 分析数列的规律：后一项的分子为前一项的分子和分母之和，后一项的分母则为前一项的分子，通过循环即可实现累加。留意：声明分子和分母为浮点型数据NUMERIC(20,7). 5.查询编号为c06108, c08106, c05109课程的学生总评成果等级，平常成果usually*0.2+期末成果final*0.8>=90的为优秀，80-90为良好，70-80为中等，60-70为及格， 3.查询teacher表中每一位教授的老师号、姓名和专业名称。 4.利用现有的表生成新表，新表中包括学号、学生姓名、课程号和总评成果。其中：总评成果=final*0.8+usually*0.2 5.查询student表中全部年龄大于20岁的男生的姓名和年龄。 6.查询计算机学院老师的专业名称。 7.查询Email运用126邮箱的全部学生的学号、姓名和电子邮箱地址。 8.查询score表中选修c05109或c05103课程，并且课程期末成果在90100分之间的学生姓名和期末成果。 9.查询student表中全部学生的基本信息，查询结果按班级号clano升序排列，同一班级中的学生按入学成果point降序排列。 10.查询选修c05109课程，并且期末成果在前5名的学生学号、课程号和期末成果。（提示：TOP 5） 数据检索2 1.按性别分组，求出student表中每组学生的平均年龄。 2.统计每个学生期末成果的平均分。 3.输出student表中没有职称的职工的老师号、姓名、专业和部门。 4.查询选修课程且期末成果不为空的学生人数。 5.查询每名学生的学号、选修课程数目、总成果，并将查询结果存放到生成的“学生选课统计表”中。 6.查询各班学生的人数。 7.查询各课程期末成果的最高分和最低分。 8.查询教两门及以上课程的老师编号、任课班级数。 9.查询课程编号以c05开头、被3名及以上学生选修且期末成果的平均分高于75分的课程号、选修人数和期末成果平均分，并按平均分降序排序。 10.查询全部08级学生的期末成果平均分，要求利用COMPUTE BY方法显示每一名学生的学生编号、课程号、期末成果的明细表，以及期末成果平均分的汇总表。 11.查询全部女生入学成果的最高分，要求利用COMPUTE BY方法既显示明细又显示汇总结果。 SQL语句的高级应用1 1.查询每一位教授的老师号、姓名和讲授的课程名称。 表：teacher,course,teach_cla 2.分别统计每个学生期末成果高于75分的课程门数。表：student,score 3.计算每个学生获得的学分。表：student,score,course 4.获得入学时间在2008年到2009年之间的全部学生中入学年龄小于19岁的学生的学号、姓名及所修课程的课程名称。表：student, stu_course 5.查询09级学生的学号、姓名、课程及学分。表：student,stu_course 6.查询全部班级的期末成果平均分，并根据平均分降序排列。表：score,student 7.查询老师基本信息和教授课程信息，其中包括未安排课程的老师信息。 表：teacher,teach_cla 8.查询090501班级中选修了韩晋升老师讲授的课程的学生的学号、姓名、课程名和期末成果。表：score,student,course,teach_cla,teacher 9.查询每门课程的课程号、课程名和选修该课程的学生人数，并按所选人数升序排序。表：score,course 10.查询两门及以上课程的期末成果超过80分的学生的姓名及其平均成果。 表：student,score SQL语句的高级应用2 运用子查询 1.输出student表中年龄大于女生平均年龄的男生的全部信息。 2.查询入学考试成果最高的学生的学号、姓名和入学成果。 3.查询全部教授c05127号课程的老师信息。 4.查询同时教授c05127和c05109号课程的老师信息。 5.查询至少选修了姓名为韩吟秋的学生所选修课程中的一门课的学生的学号和姓名。 6.查询没有被任何学生选修的课程编号、课程名称和学分。 7.查询C语言课程期末成果比电子技术课程期末成果高的全部学生的学号和姓名。 8.查询全部班级期末平均成果的最高分，并将其赋值给变量，通过PRINT语句输出。 视图与索引 运用SQL语言 1.创建一个视图v_teacher，查询全部“计算机学院”的老师信息。 CREATE VIEW v_teacher AS SELECT * FROM teacher WHERE department='计算机学院' GO SELECT * FROM v_teacher 2.创建一个视图v_avgstu，查询每个学生的学号、姓名及平均分，并且根据平均分降序排列。 CREATE VIEW v_avgstu AS SELECT TOP 100 student.studentno,sname,avg(final) AS '平均分' FROM student JOIN score ON student.studentno=score.studentno GROUP BY student.studentno,sname ORDER BY avg(final) GO SELECT * FROM v_avgstu 3.修改v_teacher的视图定义，添加WITH CHECK OPTION选项。 ALTER VIEW v_teacher AS SELECT * FROM teacher WHERE department='计算机学院' WITH CHECK OPTION 4.通过视图v_teacher向基表teacher中分别插入数据(05039, 张馨月,计算机应用,讲师,计算机学院)和(06018, 李诚,机械制造,副教授,机械学院)，并查看插入数据状况。 INSERT INTO v_teacher VALUES ('05039', '张馨月','计算机应用','讲师','计算机学院') GO SELECT * FROM teacher 消息550，级别16，状态1，第1 行 试图进行的插入或更新已失败，缘由是目标视图或者目标视图所跨越的某一视图指定了WITH CHECK OPTION，而该操作的一个或多个结果行又不符合CHECK OPTION 约束。 语句已终止。 5.通过视图v_teacher将基表teacher中老师编号为05039的老师职称修改为”副教授”。 UPDATE v_teacher SET prof='副教授' WHERE teacherno='05039' GO SELECT * FROM teacher 6.在course表的cname列上创建非聚集索引IDX_name。 CREATE NONCLUSTERED INDEX IDX_name ON course(cname) 7.在student表的studentno和clano列上创建唯一索引UQ_stu，若该索引已经存在，则删除后重建，并输出student表中的记录，查看输出结果的依次。 CREATE UNIQUE INDEX UQ_stu ON student(studentno,clano) 8.教材P.200-204（节7.5.2）运用SQL Server Management Studio创建视图，根据样图进行操作。 数据库上机试验 数据库上机试验(二) 数据库上机试验报告 数据库上机试验一思索 数据库上机试验报告 网络数据库应用上机试验 数据库上机试验8试验报告 数据库上机试验报告材料 数据库上机试验报告+总结 数据库上机试验总结(含代码) 本文来源：网络收集与整理，如有侵权，请联系作者删除，谢谢！第11页 共11页第 11 页 共 11 页第 11 页 共 11 页第 11 页 共 11 页第 11 页 共 11 页第 11 页 共 11 页第 11 页 共 11 页第 11 页 共 11 页第 11 页 共 11 页第 11 页 共 11 页第 11 页 共 11 页
