数据库实验

实验一 创建数据库和表

一、目的和要求`

    1. 熟悉SQL Server 环境。
    2. 掌握企业管理器的基本使用方法
    3. 掌握查询分析器的基本使用方法。
    4. 了解表的结构特点，了解SQL Server的基本数据类型。
    5. 学会在企业管理器中创建数据库和表。
    6. 学会使用T-SQL 语句创建数据库和表。
    7. 掌握SQLServer中数据完整性的实现方法

二、实验内容

1.创建学生管理数据库，数据库名为XSGL，包含学院信息、学生信息、课程信息和学生选课信息，并插入原始数据。数据库XSGL包含4个表即学院信息表dept、学生信息表student、课程信息表course及学生选课表sc。

2.各表的结构分别如表1， 2， 3， 4所示。

表1  学院信息表：dept

列名

数据类型

长度

是否允许NULL

说明

DNO

Char

4

×

学院编号，唯一性

DNAME

Char

30

×

学院名

DEAN

Char

20

√

院长姓名

表2 学生信息表：student

列名

数据类型

长度

是否允许NULL

说明

SNO

Char

8

×

学号，唯一性，前4位表示入学年份，第5位表示学院编号，第6位表示专业编号

SNAME

Char

20

×

姓名

AGE

smallint

2

√

年龄

SEX

Char

2

√

性别，只能是男'或'女

DNO

Char

4

×

学院编号

BIRTHDAY

datetime

8

√

出生日期

表3 课程信息表： course

列名

数据类型

长度

是否允许NULL

说明

CNO

Char

8

×

课程编号, 主码

CNAME

Char

30

×

课程名称

TNAME

Char

20

√

教师姓名

CREDIT

float

4

√

课程学分

ROOM

Char

30

√

上课教室

表4   学生选课表：sc

列名

数据类型

长度

是否允许NULL

说明

SNO

Char

8

×

学号，

CNO

Char

8

×

课程编号

GRADE

float

2

√

课程成绩

3. 分别使用企业管理器和查询分析器创建数据库XSGL，并建立完整性约束。

4.在所建表中添加测试数据

三、实验步骤

1. 在企业管理器中创建数据库XSGL

方法：企业管理器->右击数据库->新建数据库

注意：在“数据文件”选项卡和“事务日志”选项卡中，可以分别指定数据库文件和日志文件的物理路径等特性。

2.在企业管理器中分别创建表dept、student、course和sc。

在企业管理器中选择数据库 XSGL->在XSGL上单击鼠标右键->新建->表->输入dept表各字段信息->单击保存图标->输入表名dept，即创建了表dept。按同样的操作过程创建表student、course和sc。

3.在企业管理器中删除创建的dept、student、course和sc表

在企业管理器中选择数据库XSGL的表student>在student上单击鼠标右键->删除，即删除了表student。按同样的操作过程删除表dept,course和sc。

4.使用T-SQL语句创建表dept、student、course和sc表并建立相关约束

单击工具栏的“新建查询”按钮，启动SQL编辑器，在光标处输入T-SQL语句，单击“执行”按钮。

实验一

CREATE DATABASE XSGL

CREATE TABLE dept

(DNO CHAR(4) UNIQUE NOT NULL,

DNAME CHAR(30) NOT NULL,

DEAN CHAR(20)

);

CREATE TABLE student

(SNO CHAR(8) UNIQUE NOT NULL,

SNAME CHAR(20) NOT NULL,

AGE SMALLINT ,

SEX CHAR(2) CHECK(SEX IN('男','女')) ,

DNO CHAR(4) NOT NULL,

BIRTHDAY DATETIME

);

CREATE TABLE course

(CNO CHAR(8) PRIMARY KEY NOT NULL,

CNAME CHAR(30) NOT NULL,

TNAME CHAR(20) ,

CREDIT FLOAT(4) ,

ROOM CHAR(30)

);

CREATE TABLE sc

(SNO CHAR(8) NOT NULL,

CNO CHAR(8) NOT NULL,

GRADE FLOAT(2)

);

5.将XSGL数据库从服务器分离

步骤：

（1）在企业管理器中，右击“XSGL”数据库，在弹出的快捷菜单上，选择“所有任务”，在其及联菜单上，选择“分离数据库”。

（2）在“分离数据库”对话框中，单击“确定”按钮。

（3）在随后弹出的对话框中，单击“确定”按钮。

说明：XSGL数据库从SQLSERVER中分离后，在企业管理器中，就看不见XSGL数据库了。建议学生每次在机房练习下课时，将数据库分离出来，以免数据丢失。下次上机，再附加数据库。

6.将XSGL数据库附加到服务器中

步骤：

（1）在企业管理器中，右击“数据库”，在弹出的快捷菜单上，选择“所有任务”，在其及联菜单上，选择“附加数据库”。将带有原始数据的数据库文件XSGL_Data.MDF和XSGL_log.LDF,为附加数据库作准备。

（2）在“附加数据库”对话框中，单击“文件查找”按钮。找出XSGL_Data.MDF文件，再单击“确定”按钮。

（3）在随后弹出的对话框中，单击“确定”按钮。

XSGL数据库附加到SQLSERVER中，在企业管理器中，就能看见XSGL数据库，并对其进行操作了。

实验二 表的查询

一、目的和要求

  1. 掌握简单查询的操作，如单表查询，模糊查询，分组查询，重新设置输出列的名称，输出列的取值与原字段的关系，简单统计的意义，对查询结果进行排序等.

（2）掌握分组查询的使用, 理解分组查询的目的  

（3）掌握简单的多表连接查询，了解多表查询的目的

（4）掌握嵌套查询的用法

（5）掌握带有IN谓词、ANY、SOME、ALL谓词、EXISTS谓词实现嵌套查询的区别

（6）掌握外连接的使用方法和目的

（7）掌握利用AS给表重新命名的方法和目的

二、实验内容

1. 单表查询

在XSGL数据库中，完成以下查询：

  1. 查询分数在70和90之间的学生学号

SELECT distinct SNO

FROM sc

where GRADE between 70 AND 90

  1. 查询少于10名同学选修的授课班号

SELECT CNO

FROM sc

GROUP BY CNO

HAVING COUNT(SNO)<10

  1. 查询选课表中的最高分

SELECT MAX(GRADE)

AS 最高分

FROM sc

  1. 查询授课编号为‘153701’的课程的平均分

 

SELECT AVG(GRADE)

AS '课程平均分'

FROM sc

WHERE CNO='153701'

  1. 查询课程平均分超过85的授课班号，输出结果按课程平均分升序排列

SELECT CNO,AVG(GRADE)

AS '课程平均分' 

FROM sc

GROUP BY CNO

HAVING AVG(GRADE)>85

ORDER BY AVG(GRADE) ASC

 

  1. 查询课程名称为’线性代数’的排课情况

 

SELECT *

FROM course

WHERE CNAME='线性代数'

  1. 查询选修授课班号为‘218801’’的学生学号

 

SELECT SNO

FROM sc

WHERE CNO='218801' ORDER BY SNO

  1. 按授课班号查询课程的平均分，输出授课班号和平均成绩

SELECT CNO,AVG(GRADE)

AS '课程平均分' 

FROM sc

GROUP BY CNO

  1. 查询姓‘周’的教师的排课情况

 

SELECT *

FROM course

WHERE TNAME LIKE '周%'

 

 

2.多表查询

（1）查询所有选课学生的姓名

 

select distinct SNAME

from student,sc

where student.SNO=sc.SNO

  1. 查询所有未选课的学生的姓名

select  SNAME

from student

where SNO not IN(

select  SNO

from sc)

（3）按学生分类查询其选修课程的平均分，输出学号、姓名和平均成绩

select  student.SNO,SNAME,AVG(GRADE) '课程平均分'

from student,sc

where  sc.SNO=student.SNO

group by student.SNO,sname

order by AVG(GRADE)ASC

（4）查询所有课程的平均分，输出课程名和平均成绩，并按平均成绩递增

select  CNAME,AVG(GRADE)'平均成绩'

from course,sc

where  sc.CNO=course.CNO

group by sc.CNO,CNAME

order by AVG(GRADE)ASC

（5）查询少于10名同学选修的课程名称，授课班号，教师名，选课人数

 

select  CNAME'选修的课程名称',sc.CNO'授课班号(CNO)',TNAME AS'教师名(TNAME)',COUNT(SNO)'选课人数'

from course,sc

where  sc.CNO=course.CNO

group by sc.CNO,CNAME,TNAME

having COUNT(SNO)<10

（6）按学号显示信息学院的每个学生的每门课程的成绩明细，

并统计每个学生的总成绩，平均成绩

select DNAME,student.SNO,course.CNAME, sc.GRADE

from course,sc,student,dept

where  sc.CNO=course.CNO and  student.DNO=dept.DNO and student.SNO=sc.SNO

and DNAME='信息学院';

 

select DNAME,student.SNO,sum(GRADE)'总成绩',AVG(GRADE)'平均成绩'

from sc,student,dept

where   student.DNO=dept.DNO and student.SNO=sc.SNO

and DNAME='信息学院'

GROUP BY  DNAME,student.SNO

 

（7）统计每门课的不及格人数，列出课程名和不及格人数

 

select  CNAME'课程名',COUNT(SNO)'不及格人数'

from course,sc

where  sc.CNO=course.CNO and GRADE<60

group by sc.CNO,CNAME

（8）查询同时选修了‘203402’和‘244501’课程的同学名称。

 

 

select  SNAME '学生名'

from student,sc

where  sc.SNO=student.SNO and CNO='203402'and sc.SNO IN(

select SNO

from sc

where  CNO='244501')

 

 

 

 

实验三 数据库的更新操作

一、目的和要求

(1) 学会在企业管理器中对数据库表进行插入、修改和删除数据操作。

(2) 学会使用T-SQL语句对数据库表进行单个数据插入、成批数据插入、修改和删除数据操作，掌握删除表的方法。

(3) 掌握在子查询的基础上创建新表。

(4) 了解数据更新操作时要注意数据完整性。

二、实验内容

通过企业管理器或查询分析器，对XSGL数据库，利用增删改语句，对数据库进行数据维护。

三、实验步骤

1.在企业管理器中向数据库XSGL的student表加入下表所示的数据

                 表 5-1  student表记录

学号

姓名

年龄

性别

部门编号

20069011

李一

20

男

0001

20069012

李二

19

女

0002

方法：

在企业管理器中，展开“XSGL”数据库，单击“表”目录，在右面的详细窗格中，右击表“student”，在快捷菜单中选择“打开表”，并在其及联菜单上，单击“返回所有行”，将鼠标移动到空白记录上，逐字段输入各记录值，输入完后，关闭表窗口。

2．将一个新学生(学号：‘20067027’，姓名：‘张三’，年龄：20，所在系编号：‘0002’ )插入到学生表中。

INSERT

INTO student (SNO,SNAME,AGE,DNO)

VALUES ('20067027','张三','20','2')

3．依据学生的年龄，计算出该学生的出生年

 

SELECT  2018-AGE BIRTHDAY

FROM student

4．将所有安排在A209的课程调整到D109

UPDATE course

set ROOM='D109'

where ROOM='A209'

修改后：

5．将选课表中的‘线性代数’课程的成绩减去4分

UPDATE sc

SET GRADE=GRADE-4

WHERE cno IN(

SELECT cno

FROM course

where CNAME='线性代数'

)

修改前：

修改后

6．删除学院编号为空的学生记录及选课记录

说明：为了满足数据的完整性约束要求，必须先在选课表中删除该生的选课纪录，再在学生表中删除该名学生

DELETE

FROM sc

where SNO IN(

SELECT SNO

FROM student

WHERE DNO='0'

)

 

/*说明：为了满足数据的完整性约束要求，必须先在选课表中删除该生的选课纪录，再在学生表中删除该名学生*/

DELETE

FROM student

WHERE DNO='0'

删除后