数据库上机实验报告数据库系统设计上机实验报告院系：人文经管学院 专业：信息管理和信息系统 3- 3 -上机实习报告目录一、上机实验报告简介21、上机实验时间安排22、上机实验地点23、上机实验情况介绍2二、上机实验操作内容21、操作对象22、学生数据库的创建33、表的操作54视图的简单创建11一、上机实验报告简介1、上机实验时间安排上机实验从本学期第十周开始，到第十三周结束，历时四周。2、上机实验地点上机实验地点为科研楼1114室。3、上机实验情况介绍数据库上机实验实在对数据库系统设计的学习基础上，以理论结合实战的形式进行。目的是通过学习和动手实践加强自我能力的训练和提升，并且在SQL server数据库的操作学习中加深对数据库的认识和了解，并且进一步掌握SQL server语句（包括数据库、表、视图的创建、修改、删除，学习数据库的查询，修改，还有权限的处理等）。在此基础上有所创造，并尝试对小组作业中的主要组织进行数据库的运用。二、上机实验操作内容1、操作对象自上课开始，我们小组将组织定为学生数据库管理，即实现通过数据库对学生进行合理管理，我们以地大学生数据库为参考对象进行设计，数据库主要内容包括学生学工管理，学生教育管理，此外还有其他辅助性数据库（如网络）管理。这里我们主要针对学工和教育两大数据库进行设计。1) 学工管理：参考地大学工在线，对学生进行学籍管理，其内容包括学生入学开始的基本信息，入学后到毕业这期间的身份变动，重大事项活动（如奖学金，助学金的申请，发放）的管理。2) 教育管理：主要对学生平时上课、考试成绩，以及参加的等级考试，选修课情况进行管理。2、学生数据库的创建（1）创建学生学工管理数据库： Xg，存储在students文件下，代码如下：CREATE DATABASE Xg ON PRIMARY (NAME = Xg_Data , FILENAME = E:students Xg_Data.Mdf, SIZE = 4MB, MAXSIZE = 10MB, FILEGROWTH =1 MB) LOG ON (NAME = Xg_Log , FILENAME = E:studentsXg_Log.Ldf, SIZE = 2MB, MAXSIZE =10MB, FILEGROWTH = 1MB) GO（2）对学生教育数据库进行创建：Jy，存储在students文件下，代码如下：CREATE DATABASE Jy ON PRIMARY (NAME = Jy_Data , FILENAME = E:students Jy_Data.Mdf, SIZE = 4MB, MAXSIZE = 15MB, FILEGROWTH =1 MB) LOG ON (NAME = Jy_Log , FILENAME = E:studentsJy_Log.Ldf, SIZE = 2MB, MAXSIZE =10MB, FILEGROWTH = 1MB) GO接下来对数据库进行查看，在查询分析器进行输入：SP_HELPDB ，如图1图1（3）考虑到未来学生数据库内容的变化和扩充，所以需要对当前数据库容量进行调整。语言如下：Use XGGOALTER DATABASE XGMODIFY FILE (NAME=XG_Data,SIZE=10MB)GOALTER DATABASE XGMODIFY FILE (NAME=XG_Log,SIZE=6MB)GOUse JYGOALTER DATABASE JYMODIFY FILE (NAME=JY_Data,SIZE=8MB)GOALTER DATABASE JYMODIFY FILE (NAME=JY_Log,SIZE=5MB)GO(4)需要对数据进行压缩的话，课进行一下操作，以XG数据库为例，USE XGGODBCC SHRINKFILE (NewSales_Data,5)GO这样就实现了把XG数据库从当前10MB的大小压缩到5MB。3、表的操作（1）这里就以学生学籍信息表为主进行创建：USE XGGOCREATE TABLE xjxx( 学号 Int primary key not null,姓名 Varchar(20) not null,性别 char(2) not null default 男,年龄 Int not null , 学生籍贯 Varchar(20) not null,家庭住址 varchar(50) not null,出生日期 DateTime, 电话 varchar(20),所在院系 varchar(20), not null,政治背景 varchar(20) default 男,奖惩记录 varchar(100) default 无，入学时间 DateTime default)GO 如图2所示图2同样，在JY数据库中主要的基本信息表，课程表，选课表，成绩表：Use JYGoCreate table xsjb(学号 Int primary key not null,姓名 Varchar(20) not null,所在院系 varchar(20) not null,班级 varchar(20) not null,专业 varchar(20) not null,)gouse JYgocreate table kch(课程号 int not null,课程名称 varchar(20) not null)同样用类似的语言创建chj表和xk表，如图3图3在创建好的表里进行数据录入，可以用语言进行,也可直接在表中插入数据如（图4）图4（2）对表的查询和修改对于使用SQL 的数据库，检索数据都要使用SELECT 语句。使用SELECT 语句，既可完成简单的单表查询、联合查询，也可以完成复杂的联接查询、嵌套查询。主要有以下子句的基本语法格式：SELECT 列名1 ,列名2 . INTO 新表名 FROM 表名1 ,表名2 . WHERE 条件 GROUP BY 列名列表 HAVING 条件 ORDER BY 列名列表 ASC | DESC SELECT 语句至少包含两个子句：SELECT 和FROM，SELECT 子句指定要查询的特定表中的列，FROM 子句指定查询的表。WHERE 子句指定查询的条件，GROUP BY 子句用于对查询结果进行分组，HAVING 子句指定分组的条件，ORDER BY 子句用于对查询结果进行排序。（3）接下来以已经创建好的数据库进行实验：1、 查看所有学生的基本信息，在查询分析器中输入Select * from xjxx 结果如下图：2、 相对上述查询，广泛用到的是条件查询，接下来我们一一进行查询where 条件：查询性别为男的同学籍贯：Select 学生籍贯,姓名,所在院系 from xjxx where 性别=男结果如下图like 条件查询姓赵同学的全部信息；Select * from xjxx where 姓名 like赵%接下来，我们通过导入的上机实验数据表student和study，来进行其他查询语句的操作。1、 求所有同学的总成绩和平均成绩：select 学号,总成绩=(cast (专业英语 as int)+高等数学+历史+VB程序设计+管理学 ),平均=(cast (专业英语as int)+ 高等数学+历史+ VB程序设计+管理学)/5 from study 结果如图2、 对男女生人数进行统计：select 男生人数=count(*) from Student where 性别=男select 女生人数=count(*) from Student where 性别=女 3计算Study表中所有同学各门功课的平均成绩； select avg(专业英语) 专业英语 ,avg(高等数学) 高等数学,avg(历史) 历史,avg(VB程序设计) VB程序设计,avg(管理学) 管理学 from Study4 用group by 语句查询男女生的各科平均成绩：Select 性别 性别,avg(专业英语) 专业英语 ,avg(高等数学) 高等数学,avg(历史) 历史,avg(VB程序设计) VB程序设计,avg(管理学) 管理学 from Student,Study where Study.学号=Student.学号 group by 性别5连接查询，对Student表和Study 表中的数据进行连接；Select 姓名,专业英语 ,高等数学,历史, VB程序设计,管理学 from Student,Study where Study.学号=Student.学号6对于有些数据的特殊管理，需要提取一部分数据进行单独使用查询，需要创建新表，辅助以连接查询：创建一个新表Student1，存放Student表中所有性别为“男”的学号、姓名和年龄字段的数据；Select 学号, 姓名 ,年龄 into Student1 from Student where 性别=男结果如图7 平时对数据库的管理中需要对数据进行更新和调整，比如修改Student1表中的数据，使每个人的年龄由出生日期计算得出；UPDATE Student1 SET 年龄=year(GETDATE()-year(s2.出生日期)FROM Student1 s1,Student s2WHERE s1.学号=s2.学号8同样出于对数据优化管理的同时，需要删除冗余失效的数据，如删除Student1表中非“人文经管学院”的学生的数据。delete Student1 where 学号 in (select 学号 from Student where 所在院系人文经管学院)结果提示：（所影响的行数为 9 行）9自连接也能实现数据查询，用自连接查询在Student表中查找系别相同的学生的姓名及其系别；Select A.姓名 姓名1, B.姓名 姓名2 ,A.所在院系 系别 from Student A join Student B on A.所在院系=B.所在院系 WHERE A.姓名B.姓名4视图的简单创建视图也是本次上机实践了解学习的一个内容，下接着以上数据库数据的操作对视图的学习进行简单的试验和汇报。1、创建视图比如：创建视图V_score，显示每个学生的学号、姓名，各门功课的成绩及平均成绩，并要求对视图定义文本进行加密存储；use Studentgocreate v