sql实验报告

SQL实验报告

软件一班

范章鹏

201322450332

实验一 创建和删除数据库数据表

实验目的

1． 熟悉SQL Server 2008 中SQL Server Management Studio的环境 2． 了解SQL Server 2008数据库的逻辑结构和物理结构

3． 掌握使用向导方式和SQL方式来创建和删除数据库及数据表的方法

实验准备

1．装有SQL Server 2008的PC机。

2. 明确能够创建数据库的用户必须是系统管理员。

实验要求

1.熟练使用SQL Server Management Studio（简称SSMS）中的进行数据库的创建 和删除操作，以及数据表的创建和删除操作。 2.熟练使用SQL语句创建和删除数据库数据表。 3.学会分离和附加数据库。

4.完成建立和删除数据库数据表的实验报告。

实验内容

设有一学籍管理系统，其数据库名为“EDUC”，初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按5％比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为“student_data”（如不修改则默认为数据库名EDUC_data）,存放路径为“E:\\sql_data”（可自己选择存放路径）, 物理文件名为“student_data.mdf（自动生成，无需设置），创建完成后可在存放路径下查看物理文件。日志文件的逻辑文件名为“student_log”,存放路径为“E:\\sql_data” （可自己选择存放路径），物理文件名为“student_log.ldf”，（自动生成，无需设置），创建完成后可在存放路径下查看物理文件。

(1) 分别使用向导和SQL语句两种方式创建上述描述的数据库EDUC。 使用SQL Server Management Studio（简称SSMS）创建数据库。

1）启动SSMS

在开始菜单中：所有程序－SQL Server 2008 －SQL Server Management Studio

单击“连接”按钮，便可以进入【SQL Server Management Studio】窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码。

2）建立数据库

在“对象资源管理器”窗口，建立上述数据库EDUC。在数据库节点上右击选择新建。在下图“新建数据库”窗口中，完成实验内容中EDUC数据库的各项要求。

以上设置完成后，点击“确定”。完成数据库创建。即可在“对象资源管理器”中查看到EDUC。如下图所示。

（3）使用向导创建EDUC数据库中的三个基本表，并录入数据。以student表为例：

1）打开EDUC前的“+”，右击下层的“表”选项，右侧弹出表设计窗口，先完成student的创建，如下：

2）使用同样方法继续在EDUC数据库中创建另外两个表course和sc表。表结构如下：

（3）使用向导删除上面建立的SC表。

右击“dbo.sc”选择“删除”，确认删除此表。 （4）使用向导删除上面建立的数据库EDUC。 右击“EDUC”选择“删除”，确认删除此数据库。

实验二 交互式SQL

实验目的

1．观察查询结果, 体会SELECT语句实际应用； 2．要求学生能够使用SELECT语句进行数据库查询。 3. 熟练掌握各种查询的操作方法。

实验准备

1. 完成实验一所要求的EDUC数据库，成功建立了基本表，并录入相应数据。 2. 了解SELECT语句的用法。

3. 熟悉查询分析器中的SQL脚本运行环境。

实验要求

完成实验内容中的查询操作，并查看运行结果提交实验报告。

实验内容

1．查询选修了2号课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列；

SELECT Sno,Grade FROM SC

WHERE Cno='2'

ORDER BY Grade,Cno DESC;

2．查询选修了3号课程且成绩在80－90 之间(包括80和90)的学生学号和成绩；SELECT Sno,Grade

FROM SC

WHERE Cno='3'AND Grade>=80 AND Grade<=90;

3． 查询计算机系和数学系的姓张的学生的信息；

SELECT *

FROM Student,SC

WHERE Sname LIKE '张%' AND Sdept IN('MA','CS')

4． 查询缺少了成绩的学生的学号和课程号。

SELECT Sno,Cno FROM SC

WHERE Grade=NULL

5． 查询每个学生的情况以及他（她）所选修的课程；

SELECT Student.*,Cno FROM Student,SC

WHERE Student.Sno=SC.Sno

6． 查询选修了“数据库”课程的学生的学号，姓名，成绩；

SELECT Student.Sno Sno,Sname,Grade FROM Student,Course,SC

WHERE Student.Sno=SC.Sno and SC.Cno=Course.Cno AND Cname='数据库'

7． 查询每一门课的间接先行课（即先行课的先行课）。

SELECT first.Cno,second.Cpno FROM Course first,Course second WHERE first.Cpno=second.Cno

8． 查询学生201215121选修课程的总学分；

SELECT SUM(Grade)SumGrade FROM SC

WHERE Sno='201215121'

9． 查询每门课程号和选修该课程的人数；

SELECT Cno,COUNT(*) count FROM sc

GROUP by Cno

10．查询选修课程超过3 门课的学生学号。

SELECT Sno FROM sc

GROUP by Sno

HAVING COUNT(*)>3;

实验三 视图与数据更新

实验目的

1．掌握创建视图的SQL语句，数据更新的SQL语句。 2．了解使用创建视图向导创建视图的方法。

3．掌握使用SQL创建视图的方法，使用SQL更新数据的方法。

实验准备

1．了解创建视图与数据更新的方法。

2．掌握创建视图与数据更新的SQL语句的语法格式。 4．了解删除视图与数据更新的SQL语句的用法。

实验要求

1. 用不同的方法创建视图，练习数据更新。 2. 提交实验报告，并验收实验结果。

实验内容

1. 创建视图

（1） 使用企业管理器创建视图

1）在EDUC库中以“student”表为基础，建立一个名为“V_SC”的视图，该视图是所有计算机系学生；

右击EDUC下“视图”，选择“新建”，在弹出设计窗口中，通过添加表，选择字段等操作完成。

(2) 使用SQL语句创建视图

在EDUC数据库中，以Student Course 和SC表为基础完成以下视图定义 1） 定义计算机系学生基本情况视图V_Computer；

create view v_Computer(Sno,Sname,Ssex,Sage,Sdept) as

select * from Student where Sdept='CS'

2） 将Student Course 和SC表中学生的学号，姓名，课程号，课程名，成绩定义为视

图V_S_C_G；

create view V_S_C_G as

select Student.Sno Sno,Sname,Course.Cno Cno,Cname,Grade from Student,Course,SC

where Student.Sno=Sc.Sno and Course.Cno=SC.Cno

3） 将各系学生人数，平均年龄定义为视图V_NUM_AVG create view V_NUM_AVG(Sdept,SdeptNum,AvgSage) as

select Sdept,count(Student.Sno),AVG(Sage) from Student

group by Student.Sdept;

4）定义一个反映学生出生年份的视图V_YEAR create view V_YEAR(Sno,Sname,birthday) as

select Sno,Sname,2015-Sage

from Student

group by Sno,Sname,Sage;

5）将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G create view V_AVG_S_G as

select SC.Sno,Sname ,count(SC.Cno) SdeptNum,AVG(Grade)AvgGrade from Student,Course,SC

where Student.Sno=SC.Sno and Course.Cno=SC.Cno Group by

SC.Sno,Student.Sname;

6）将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G create view V_AVG_C_G(Cno,Number,AvgGrade) as

select SC.Cno,count(SC.Sno),AVG(Grade) from Course,SC

where Course.Cno=SC.Cno group by SC.Cno;

建好的视图如下：

1． 查询视图

1） 查询平均成绩为90分以上的学生学号、姓名和成绩；（使用V_AVG_S_G视图） select sno,sname,AvgGrade from V_AVG_S_G where AvgGrade>90

2） 查询人数超过10人，且平均年龄在18岁以上的院系；（使用V_NUM_AVG视图）

select Sdept from V_NUM_AVG where AvgSage>18

3. 删除视图

（1） 使用企业管理器删除视图 用企业管理器删除视图“V_SC” 在视图上 右键--->删除 （2） 使用SQL语句删除视图

用SQL语句删除视图 V_YEAR; DROP VIEW V_YEAR

综合训练——河北经贸大学运动会管理系统数据库设计

（实验四、五、六皆根据此题目）

实验四 ER图

参照河北经贸大学运动会管理过程，系统主要包括以下功能： （1）学生可通过学号登录系统报名，每人限报3项。 （2）教师可通过教师号登录系统报名，每人限报3项。

（3）管理员后台维护运动会项目管理，各比赛项目按不同人数要求分组。教师项目按年龄分为青年组（45岁以下含45），中年组（45岁-60岁）。

（4）记录各项目比赛成绩，并按照预赛成绩决定决赛名单，并记录决赛成绩、排名。

设计要求

请根据以上业务需求，画出E-R图，设计关系模式，并在SQL Server2008中实现该数据库（数据自拟），并用SQL语言完成下列操作。

1） 某同学报名男子5000米长跑项目；

insert

into 学生参赛表

values('20130304','12','98','85');

2） 查询教师青年组全部比赛项目；

select 项目名

from 教师信息表,教师参赛表,比赛项目表

where 教师信息表.教师号=教师参赛表.教师号 and 教师参赛表.ID=比赛项目表.ID and 年龄<45;

3）查询女生100米决赛成绩及排名；

select 姓名,决赛成绩

from 学生参赛表,学生信息表

where 学生参赛表.学号=学生信息表.学号 and ID='02' order by 决赛成绩 desc

4） 查询男生100米决赛名单；

select 姓名

from 学生参赛表,学生信息表

where 学生参赛表.学号=学生信息表.学号 and ID='01'

5） 统计运动会各项目报名人数。

select 比赛项目表.ID,count(比赛项目表.ID)人数 from 学生参赛表,教师参赛表,比赛项目表 group by 比赛项目表.ID ;

ER图如下：

学生（姓名，性别，学院）；

教师（姓名，性别，年龄，教师号，学院）； 比赛项目（ID，项目名，时间）；

实验五 逻辑结构图

本文来源：https://www.bwwdw.com/article/6h0v.html