数据库实验4实验报告.docx

1、数据库实验4实验报告淮海工学院计算机工程学院实验报告书课程名： 数据库原理及应用 题 目： T-SQL高级应用 班 级： 软件132 学 号： * * * * 一目的与要求1.掌握复杂查询的使用方法；2.掌握多表连接的方法；3.掌握SELECT语句在多表查询中的应用。4.掌握数据汇总、外连接查询的使用方法；5.掌握事务定义的一般方法二实验内容1给出教材14-16的程序运行结果。2编写程序，查询“1033”班的最高分的学生的学号、姓名、班号、课程号和分数。3编写程序，创建一个新表，包含所有学生的姓名、课程名和分数，并以姓名排序。4编写程序，输出每个班最高分的课程名和分数。5在上次实验建立的fac

2、tory数据库的基础上，完成如下各题：删除factory数据库上各个表之间建立的关系；显示各职工的工资记录和相应的工资小计。按性别和部门名的所有组合方式列出相应的平均工资。在worker表中使用以下语句插入一个职工记录：insert into worker values(20,陈立，女，55/03/08，1，75/10/10，4)，在depart表中使用以下语句插入一个部门记录：insert into depart values(5,设备处)。再对worker和depart表进行全外连接显示职工的职工号，姓名和部门名。然后删除这两个插入的记录。显示最高工资的职工的职工号、姓名、部门名、工资发放

3、日期和工资。显示最高工资的职工所在的部门名。显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。先显示worker表中的职工人数，开始一个事务，插入一个职工记录，再显示worker表中的职工人数，回滚该事务，最后显示worker表中的职工人数。三实验步骤删除factory数据库上各个表之间建立的关系的操作步骤如下： 启动SQL Server管理控制器。 在“对象资源管理器”中展开LCB-PC服务器节点。 展开“数据库”节点。 选中，将其展开。 展开“数据库关系图”节点。 选中dbo.Diagram_1，右击，在出现的快捷菜单中选择“修改”命令，如图5.1所示。 在数据库关系图中，选择表示

4、要从关系图中删除的关系的连接线（对于两条连线均进行的操作）。 右击关系线，从快捷菜单中选择“从数据库中删除关系”命令。 出现一个消息框，提示确认删除。单击“是”按钮。 在出现的对话框中单击“是”按钮保存所做的修改。这样就将worker表和depart表以及worker表和salary表之间的关系删除了。四测试数据与实验结果第1-14题图第1-15题图第1-16题图第2题图第3题图第4题图图5.1第5-2题图第5-3题图第5-4题图第5-5题图第5-6题图第5-7题图第5-8题图第5-9题图第5-10题图五结果分析与实验体会在FROM子句中指定连接条件，有助于将这些连接条件与WHERE子句中的其

5、他搜索条件分开，指定连接时建议使用这种方法。如果某个表只出现在子查询中而不出现在外部查询中，那么该表中的列就无法包含在输出中（外部查询的选择列表）。使用EXISTS引入的子查询在以下几方面与其他子查询略有不同： EXISTS关键字前面没有列名，常量或其他表达式。 由EXISTS引入的子查询的选择列表通常都是由星号（*）组成。由于只是测试是否存在符合子查询中指定条件的行，所以不必列出列名。六源码1use schoolselect 学号,课程号,分数from scorewhere 学号 in(103,105)order by 学号compute avg(分数) by 学号go2use school

6、goselect student.班号,course.课程名,avg(score.分数)as平均分from student,course,scorewhere student.学号=score.学号 and course.课程号=score.课程号group by student.班号,course.课程名 with cubego3use schoolgobegin transaction Mytran insert into teacher values(999,张英,男,1960/03/05,教授,计算机系)save transaction Mytran insert into teach

7、er values(888,胡丽,男,1982/08/04,副教授,电子工程系)rollback transaction Mytran commit transaction goselect * from teachergodelete teacher where 编号=999go4use schoolselect student.学号,student.姓名,student.班号,score.课程号,score.分数from student,scorewhere 分数 in (select max(分数) from score where 班号=1033 and student.学号=scor

8、e.学号)5USE schoolgoSELECT s.姓名,c.课程名,sc.分数INTO studFROM student s,course c,score scWHERE s.学号=sc.学号 and c.课程号=sc.课程号 and sc.分数 is not nullORDER BY s.姓名SELECT * FROM studGo6USE schoolGOSELECT 班号,课程名,MAX(分数) 分数FROM (SELECT s.学号,s.姓名,s.班号,c.课程名,sc.分数 FROM student s,course c,score sc WHERE s.学号=sc.学号 AND

9、 c.课程号=sc.课程号 AND 分数 IS NOT NULL) TGROUP BY 班号,课程名ORDER BY 班号GO7USE factoryGOSELECT worker.职工号,worker.姓名,salary.工资FROM worker,salaryWHERE worker.职工号=salary.职工号ORDER BY worker.职工号,worker.姓名COMPUTE SUM(salary.工资) BY worker.职工号 GO8USE factoryGOSELECT worker.性别,depart.部门名,AVG(salary.工资) AS 平均工资FROM work

10、er,depart,salaryWHERE worker.职工号=salary.职工号 AND worker.部门号=depart.部门号GROUP BY worker.性别,depart.部门名 WITH CUBE GO9USE factoryGOINSERT INTO worker VALUES(20,陈立,女,55/03/08,1,75/10/10,4)GOINSERT INTO depart VALUES(5,设备处)GOSELECT worker.职工号,worker.姓名,depart.部门名FROM worker FULL JOIN departON(worker.部门号=dep

11、art.部门号)ORDER BY worker.职工号GODELETE FROM worker WHERE 职工号=20GODELETE FROM depart WHERE 部门号=5GO10USE factoryGOSELECT worker.职工号,worker.姓名,depart.部门名,salary.日期,salary.工资FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号AND salary.工资= (SELECT MAX(工资) FROM salary)GO11USE fact

12、oryGOSELECT 部门名FROM departWHERE 部门号= (SELECT 部门号 FROM worker WHERE 职工号= (SELECT 职工号 FROM salary WHERE 工资= (SELECT MAX(工资) FROM salary) ) )GO12USE factoryGOSELECT 职工号,姓名FROM workerWHERE 职工号 IN (SELECT 职工号 FROM salary GROUP BY 职工号 HAVING AVG(工资)(SELECT AVG(工资) FROM salary) GO13USE factoryGOSET NOCOUNT

13、 ON-声明变量DECLARE dname char(10)-声明游标DECLARE d_cursor CURSOR FOR SELECT 部门名 FROM depart WHERE 部门号= (SELECT 部门号 FROM worker WHERE 职工号= (SELECT 职工号 FROM salary WHERE 工资= (SELECT MAX(工资) FROM salary) ) )-打开游标OPEN d_cursor-提取第一行数据FETCH NEXT FROM d_cursor INTO dname-打印表标题PRINT 部门名PRINT -WHILE FETCH_STATUS

14、= 0BEGIN -打印一行数据 PRINT dname -提取下一行数据 FETCH NEXT FROM d_cursor INTO dnameEND-关闭游标CLOSE d_cursor-释放游标DEALLOCATE d_cursorGO14USE factoryGOSET NOCOUNT ON-声明变量DECLARE no int,name char(10)-声明游标DECLARE w_cursor CURSOR FOR SELECT 职工号,姓名 FROM worker WHERE 职工号 IN (SELECT 职工号 FROM salary GROUP BY 职工号 HAVING A

15、VG(工资)(SELECT AVG(工资) FROM salary)-打开游标OPEN w_cursor-提取第一行数据FETCH NEXT FROM w_cursor INTO no,name-打印表标题PRINT 职工号 姓名PRINT -WHILE FETCH_STATUS = 0BEGIN -打印一行数据 PRINT CAST(no AS char(8)+name -提取下一行数据 FETCH NEXT FROM w_cursor INTO no,nameEND-关闭游标CLOSE w_cursor-释放游标DEALLOCATE w_cursorGO15USE factoryGODEC

16、LARE num intSELECT num=COUNT(*) FROM workerPRINT 原职工人数:+CAST(num AS CHAR(3) GODECLARE num intBEGIN TRANSACTION -启动事务-插入一个职工记录 INSERT INTO worker VALUES(20,陈立,女,55/03/08,1,75/10/10,4) PRINT 插入一个职工记录SELECT num=COUNT(*) FROM workerPRINT 职工人数:+CAST(num AS CHAR(3) ROLLBACK TRANSACTION -回滚事务GOPRINT 回滚事务DECLARE num intSELECT num=COUNT(*) FROM worker PRINT 职工人数:+CAST(num AS CHAR(3) GO