实验1:创建数据库并实现简单查询 实验报告

GDOU-B-11-112

广东海洋大学学生实验报告书

实验一：建数据库，实现简单实验名称 课程名称 数据库原理与应用

查询

学院（系）

学生姓名

成绩

学号

专业

实验地点

班级

实验日期

一、实验目的

实验的主要目的是要求同学们熟练掌握SQL Server的使用，主要是建立数据库和实现简单查询

二、实验内容

1.创建学生Student数据库（包含学生、课程和选修三个表），写出创建三个基本表的CREATE语句，列名、数据类型和约束如下：

列名

cStudentNo

vStudentName

iSage

nSgender

cClass

vDepartment

描述

学号

姓名

年龄

性别

班级

院系

数据类型

长度为12的定长字符串

变长字符串，长度自定

整型

Unicode编码方式的字符串型

定长字符串，长度10

变长字符串，长度自定

约束

主键

非空

年龄在0到100岁之间

性别只能取‘男’或‘女’

取默认值‘计算机系’

列名

cCourseNo

vCourseName

vTeacher

iCredit

描述

课程号

课程名

教师名

学分

数据类型

长度为8的定长字符串

变长字符串，长度自定

变长字符串

带一位小数的数值型

约束

主键

无

无

取值0到5之间

列名

cStudentNo

cCourseNo

iGrade

描述

学号

课程号

成绩

数据类型

和学生表学号一致

和课程表的课程号一致

整型

约束

外键，引用学生表学号

外键，引用课程表课程号

0到100分之间

在Student数据库中，写出完成如下操作的T-SQL语句：

(1) 修改学生表，添加一个新的属性列‘籍贯’。

(2) 修改课程表，删除学分列上的取值0-5的约束。

(3) 修改选修表，将成绩列的数据类型修改了浮点型。(修改类型前先删除此列上的约束)

(4) 修改课程表，给课程名添加一个取值唯一的约束。

2.执行脚本，生成Recruitment和Globaltoyz数据库，并完成下列查询。

注：首先开启数据库服务，启动Microsoft SQL SERVER Management Studio；

解压缩SQL SCRIPT文件到SQL SCRIPT文件夹，进入‘Guided Practice’目录，点击打开CREATERECRUITMENTDB文件，将该脚本文件在SQL SERVER查询编辑器中打开，点击工具栏上的‘执行’按钮执行该脚本。执行成功后刷新数据库，观察是否创建了一个名为‘Recuitment’的数据库。接下来，同样点击打开该目录下的另一文件‘CREATERECRUITMENTOBJECTS’，按同样的方法执行脚本，执行成功后会生成Recuitment数据库中的一系列基本表。

（GlobalToyz数据库的安装脚本在‘UnGuided Practice’目录下，安装过程和Recuitment类似，安装的脚本文件为‘CREATEGLOBALTOYZDB’和‘CREATEGLOBALTOYZOBJECTS’，按先后次序安装）

安装后请熟悉一下两个数据库的基本表，了解一下该数据库主要描述的数据应用环境。

安装后写出在GlobalToyz中的查询语句，（只要求写出SELECT 语句，不需要写出查询结果）

(1) 查询日期为2001年5月22日的订单详情。

(2) 查询订单总额超过75的订单详情。

(3) 订单延误将按照总价值mTotalCost的5倍赔偿，查询每份订单的订单号和需要赔偿的金额

(4) 查询以‘I’开头，国家名称由五个字母构成的国家ID和名称。

(5) 查询Shipment表中dActualDeliveryDate为空的记录。

(6) 对于PickofMonth表，查询月销售量最高的玩具ID和销售的月份和年份。

(7) 对于PickofMonth表，统计2000年销售的玩具的总数量。

(8) 对于Toyz表，查询玩具的最高售价，最低售价，和平均售价。

(9) 对于Shopper表，统计‘California’州的购买者人数。

(10) 对于PickofMonth表，查询2001的销售记录，要求按照月销售额按照从小到大的顺序显示。

(11) 对于ShippingRate表，计算每个国家的每磅的平均运费。

三、实验过程

1.1

CREATE TABLE Student

(cStudentNo CHAR(12),

vStudentName varchar(12) NOT NULL,

iSage int,

nSgender nchar(12),

cClass char(10),

vDepartment varchar(20)

CONSTRAINT DF_vDepartment DEFAULT '计算机系',

CONSTRAINT PK_cStudentNo PRIMARY KEY (cStudentNo),

CONSTRAINT CK_iSage CHECK(iSage<=100 and iSage>=0),

CONSTRAINT CK_nSgender CHECK (nSgender='男' OR nSgender='女'))

1.2

CREATE TABLE Course

(cCourseNo char(8),

vCourseName varchar(8),

vTeacher varchar(8),

iCredit FLOAT(1),

CONSTRAINT PK_cCourseNo PRIMARY KEY (cCourseNo),

CONSTRAINT CK_iCredit CHECK (iCredit<=5 AND iCredit>=0))

1.3

CREATE TABLE SC

(cStudentNo CHAR(12),

cCourseNo CHAR(8),

iGrade int,

CONSTRAINT CK_iGrade CHECK(iGrade<=100 AND iGrade>=0),

CONSTRAINT FK_cStudentNo FOREIGN KEY (cStudentNo) REFERENCES Student

(cStudentNo),

CONSTRAINT FK_cCourseNo FOREIGN KEY (cCourseNo) REFERENCES Course

(cCourseNo))

1.4.1

ALTER TABLE Student ADD S_Place VARCHAR(20)

1.4.2

ALTER TABLE Course DROP CK_iCredit

1.4.3

ALTER TABLE SC DROP CK_iGrade

ALTER TABLE SC ALTER COLUMN iGrade CHAR

1.4.4

ALTER TABLE Course ADD UNIQUE(vCourseName)

2.1

涉及表格Orders和OrderDetail,列cOrderNo和dOrderDate

SELECT *

FROM Orders

WHERE dOrderDate=('2001-05-20 00:00:00.000')

2.2

涉及表格OrderDetail,列mToyCost

SELECT *

FROM OrderDetail

WHERE mToyCost>75

2.3

涉及表格Orders,列cOrderNo和mTotalCost，

SELECT cOrderNo,mTotalCost*5 AS '赔偿总额'

FROM Orders

2.4

涉及表格Country,列cCountryId和cCountry，涉及字符匹配查询。

SELECT *

FROM Country

WHERE cCountry LIKE 'I____'

2.5

涉及表格Shipment,列dActualDeliveryDate,涉及空值的查询。

SELECT *

FROM Shipment

WHERE dActualDeliveryDate IS NULL

2.6

涉及表格PickofMonth,列iTotalSold,涉及聚集函数的使用。

SELECT cToyId,siMonth,iYear

FROM PickofMonth

WHERE iTotalSold=(

SELECT Max(iTotalSold)

FROM PickofMonth)

2.7

涉及表格PickofMonth,列iTotalSold,涉及聚集函数的使用。

SELECT SUM(iTotalSold) AS '2000年销售的玩具的总数量'

FROM PickofMonth

WHERE iYear='2000'

2.8

涉及表格Toyz,列mToyRate,涉及聚集函数的使用。

SELECT MAX(mToyRate) AS '最高售价',

MIN(mToyRate) AS '最低售价',

AVG(mToyRate) AS '平均售价'

FROM Toys

2.9

涉及表格Shopper,列cState,涉及聚集函数的使用。

SELECT COUNT(*) AS 'California州的购买者人数'

FROM Shopper

WHERE cState='California'

2.10

涉及表格PickofMonth,列iYear、cToyId和iTotalSold,涉及聚集函数的使用。

SELECT *

FROM PickofMonth

WHERE iYear='2000'

ORDER BY iTotalSold

2.11

涉及表格ShippingRate,列mRatePerPound,涉及聚集函数的使用。

SELECT AVG(mRatePerPound) AS '每个国家的每磅的平均运费'

FROM ShippingRate

GROUP BY cCountryId

四、实验总结

通过这次试验,我对SQL Sever的建立数据库和实现简单查询的能力都有了很大的提升，对数据库的认识有了更深刻的理解，对以后的学习提供了很大的帮助，熟悉了建立数据库和实现简单查询。

指导教师

日期

注：请用A4纸书写，不够另附纸。

第 页，共 页