数据库原理及应用实验报告
学号: 姓名:
实验1
一、实验目的:
熟悉SQL Server使用界面,掌握使用SSMS和SQL语句创建并设置数据库。
二、实验原理:
1.使用SSMS创建数据库
2.创建数据库语句:
CREATE DATABASE 数据库名
[ON [PRIMARY] //指定一个文件为主要文件
[<文件格式>[,…n]]
[,<文件组格式>[,…n]] ]
[LOG ON {<文件格式>[,…n]}]
<文件格式>::=
([NAME=逻辑文件名,]
FILENAME=‘操作系统下的文件名和路径’
[,SIZE=文件初始容量]
[,MAXSIZE={文件最大容量|UNLIMITED}]
[,FILEGROWTH=递增值])[,…n]
<文件组格式>::=FILEGROUP 文件组名<文件格式>[,…n]
3.修改数据库语句:
ALTER DATABASE 数据库名
{ADD FILE <文件格式>[,…n] [TO FILEGROUP 文件组名]
|ADD LOG FILE<文件格式>[,…n]
|REMOVE FILE 逻辑文件名
|ADD FILEGROUP 文件组名
|REMOVE FILEGROUP 文件组名
|MODIFY FILE<文件格式>
|MODIFY FILEGROUP 文件组名 文件组属性}
<filespec>::=
(NAME=逻辑文件名
[,FILENAME=‘实际文件名和路径’]
[,SIZE=容量]
[,MAXSIZE={最大容量|UNLIMITED}]
[,FILEGROWTH=递增量])
三、实验内容:
1. 使用SQL语句完成下题
(1) 使用CREATE DATABASE语句创建一个名为“st”数据库,要求①主要数据文件:逻辑文件名为“stdata1”,实际文件名为“stdata1.mdf”②事务日志文件:逻辑文件名为“stlog1”,实际文件名为“stlog1.ldf”③初始容量均为3MB,最大容量均为10MB,递增量均为1MB。
(2) 使用CREATE DATABASE语句创建一个名为“student”数据库,要求①主要数据文件:逻辑文件名为“studentdata1”,实际文件名为“studentdata1.mdf”②非主要数据文件:逻辑文件名为“studentdata2”,实际文件名为“studentdata2.ndf”③事务日志文件:逻辑文件名为“studentlog1”,实际文件名为“studentlog1.ldf”④初始容量均为3MB,最大容量均为10MB,递增量均为1MB。
(3) 使用ALTER DATABASE语句在“st” 数据库中添加一个数据文件和一个事务日志文件,其逻辑文件名分别为“studentdata3”和“studentlog2”,其实际文件名分别为“studentdata3.ndf”和“studentlog2.ldf”,初始容量均为1MB,最大容量均为10MB,递增量均为1MB;然后将数据文件studentdata3的初始容量增加到5MB,上限增加到20MB。
解:(1)create database st on primary
(name=stdata1,
filename='d:\xs\stdata1.mdf',
size=3mb, maxsize=10mb, filegrowth=1mb)
log on
(name='stlog1',
filename='d:\xs\stlog1.ldf',
size=1mb, maxsize=10mb, filegrowth=1mb)
go
(2)create database student on primary
(name=studentdata1,
filename='d:\xs\studentdata1.mdf',
size=3mb, maxsize=10mb, filegrowth=1mb),
(name=studentdata3,
filename='d:\xs\studentdata2.ndf',
size=1mb, maxsize=10mb, filegrowth=1mb)
log on
(name='studentlog1',
filename='d:\xs\studentlog1.ldf',
size=1mb, maxsize=10mb, filegrowth=1mb)
go
(3)alter database st
add file
(name=studentdata3,
filename='d:\xs\studentdata3.ndf',
size=1mb, maxsize=10mb, filegrowth=1mb),
(name='studentlog2',
filename='d:\xs\studentlog2.ldf',
size=1mb, maxsize=10mb, filegrowth=1mb)
alter database st
modify file
(name=studentdata3,
size=5mb,maxsize=20mb)
实验2
一、实验目的:掌握使用SSMS和SQL语句创建并管理数据库表.
二、实验原理:
1.在SSMS上创建数据库表
2.创建数据库表语句:
CREATE TABLE[数据库名.拥有者]表名
(<字段定义>)[,…n]
<字段定义>::={字段名 数据类型}
[[DEFAULT 常量表达式]|[IDENITY[(种子,增量)]]]
[<字段定义>][,…n]
3.创建约束
[CONSTRAINT 约束名] NULL|NOT NULL
[CONSTRAINT 约束名] PRIMARY EY[CLUSTERED|NONCLUSTERED]
[(字段名[,…16])]]
[CONSTRAINT 约束名]DEFAULT 默认值[FOR字段名]
[CONSTRAINT 约束名] UNIQUE[CLUSTERED|NONCLUSTERED] [(字段名[,…16])]]
[CONSTRAINT 约束名] FOREIGN KEY[(字段名[,…16])] REFERENTCES 引用表名(引用字段名[,…16])
[CONSTRAINT 约束名]CHECK(条件表达式)
4.修改数据库表
ALTER TABLE
ALTER COLUMN 字段名 新数据类型[(精度[,小数位数])] [NULL|NOT NULL]
ALTER TABLE 表名
ADD{字段定义|字段名AS计算机字段表达式}[,…n]
ALTER TABLE 表名
DROP COLUMN 字段名[,…n]
ALTER TABLE 表名
ADD CONSTRAINT 约束定义[,…n]
ALTER TABLE 表名
DROP CONSTRAINT 约束名[,…n]
三、实验内容
1.使用SQL语句完成下题:
(1) 使用CREATE TABLE语句创建一个学生表,其中包含以下字段:学号(int,自动编号,种子为200001,增量为1,定义为主键);姓名(varchar(8),not null);专业(varchar(20),null,)。
(2) 使用CREATE TABLE语句创建一个成绩表其中包含以下字段:学号(int,not null);课程号(int,not null);成绩(real,null,)。
(3) 使用alter table语句新增学生表和成绩表之间的外键联系,并新增对成绩的检查性约束。
解:(1) create table 学生表
(学号 int identity (200001,1) primary key ,
姓名 varchar(8) not null,
专业 varchar(20) null )
(2) create table 成绩表
(学号 int not null,
课程号 int not null,
成绩 real null)
(3) alter table 成绩表
add
constraint gjmg foreign key(学号) references 学生表(学号),
constraint fhjg check (成绩>=0 and 成绩<=100)
实验3
一、实验目的:熟练掌握SELECT语句对数据库表的各项操作.
二、实验原理:
1.选择查询语句:
SELECT 字段列表 (指定输出字段)
[INTO 新表] (将检索结果存储到一张新表)
From 数据源 (指定检索的数据来源)
[WHERE 搜索条件](指定对记录的过滤条件
[GROUP BY 分组表达式](对检索到的记录分组)
[HAVING 搜索表达式](指定对记录的过滤条件)
[ORDER BY 排序表达式](对检索到的记录进行排序处理)
三、实验内容:
1. 完成课后题3、5
7.3(1) SELECT 业务员姓名,年龄 from 业务员
where 年龄=
(select max (年龄) from 业务员)
(2) select 业务员姓名,月薪from 业务员
order by 月薪 desc
(3) select * from 业务员
where 月薪 between 1000 and 2000
(4) select 客户编号, count(订单号)as 订单数,sum(金额)as 总金额 from 订单
group by 客户编号
(5) select 业务员姓名,count(订单号)as 订单数from 业务员 full outer join 订单
on 业务员.业务员编号=订单.业务员编号
group by 业务员姓名
having count(订单号) >= 2
order by 订单数
(6) update 业务员
set 月薪= 1.1* 月薪
(7) select 业务员姓名,年龄 from 业务员
where 月薪> all (select avg (月薪) from 业务员)
(8) update 业务员
set 月薪=1.05*月薪
where 业务员编号 in
(select 业务员编号 from 订单
group by 业务员编号 having sum(金额)>20000 )
(9) select 业务员姓名,订单号,z.客户编号,金额 from 业务员x,订单y,客户z
where x.业务员编号= y.业务员编号and y.客户编号=z.客户编号and 金额>=2000
7.5(1)SELECT ENO,ENAME FROM EMP
WHERE ENO IN (SELECT ENO FROM WORKS WHERE CNO ='C4' OR CNO='C8'
GROUP BY ENO
HAVING COUNT(CNO)>1)
(2)SELECT ENO,ENAME FROM EMP
WHERE ECITY IN(SELECT CITY FROM COMP WHERE CNO IN (SELECT CNO FROM WORKS))
(3)SELECT ENO ,COUNT (CNO) AS NUM,SUM(SALARY)AS SUM_SALARY FROM WORKS
WHERE ENO IN (SELECT ENO FROM EMP)
GROUP BY ENO
(4)SELECT DISTINCT ENO FROM WORKS
WHERE CNO IN (SELECT CNO FROM WORKS WHERE ENO='E6')
(5)UPDATE WORKS
SET SALARY =100+SALARY
WHERE ENO IN (SELECT ENO FROM EMP WHERE AGE>50)
(6)DELETE
FROM WORKS
WHERE ENO IN ( SELECT ENO FROM EMP WHERE AGE >60)
(7)CREATE VIEW EMP_WOMAN
AS SELECT EMP.ENO,ENAME,WORKS.CNO,CNAME,SALARY FROM (EMP JOIN WORKS ON EMP.ENO = WORKS.ENO) JOIN COMP
ON WORKS.CNO = COMP.CNO WHERE SEX='女'
(8)SELECT CNO,ENO FROM WORKS
WHERE CNO IN(select CNO FROM COMP WHERE CITY='上海')
(9)select ENAME from EMP
WHERE ENO NOT IN (SELECT ENO FROM WORKS WHERE CNO IN (SELECT CNO FROM COMP WHERE CITY='上海'))
(10)SELECT ENO ,ENAME FROM EMP
WHERE ENO IN (SELECT ENO FROM WORKS WHERE SALARY >1000 AND CNO IN (select CNO from COMP where CNAME='IBM')) AND SEX='男'
7.3 订单
客户
业务员
7.5 COMP
EMP
WORKS
实验四、设计性实验:建立小型数据库管理系统
一、实验目的
熟悉数据库建立及应用的方法,系统的应用数据库。
二、实验原理
1.创建数据库语句:
CREATE DATABASE 数据库名
[ON [PRIMARY] //指定一个文件为主要文件
[<文件格式>[,…n]]
[,<文件组格式>[,…n]] ]
[LOG ON {<文件格式>[,…n]}]
[FOR LOAD//提供与SQL Server早期版本的兼容性 | FOR ATTACH//将已经存在的数据库文件连接进来]
<文件格式>::=
([NAME=逻辑文件名,]
FILENAME=‘操作系统下的文件名和路径’
[,SIZE=文件初始容量]
[,MAXSIZE={文件最大容量|UNLIMITED}]
[,FILEGROWTH=递增值])[,…n]
<文件组格式>::=FILEGROUP 文件组名<文件格式>[,…n]
2.修改数据库语句:
ALTER DATABASE 数据库名
{ADD FILE <文件格式>[,…n] [TO FILEGROUP 文件组名]
|ADD LOG FILE<文件格式>[,…n]
|REMOVE FILE 逻辑文件名
|ADD FILEGROUP 文件组名
|REMOVE FILEGROUP 文件组名
|MODIFY FILE<文件格式>
|MODIFY FILEGROUP 文件组名 文件组属性}
<filespec>::=
(NAME=逻辑文件名
[,FILENAME=‘实际文件名和路径’]
[,SIZE=容量]
[,MAXSIZE={最大容量|UNLIMITED}]
[,FILEGROWTH=递增量])
3.备份,恢复数据库原理语句:
RESTORE DATABASE 数据库 FROM 备份设备名
EXEC sp_attach_db ‘数据库名’,‘文件名’ [,…16]
4.创建数据库表语句:
CREATE TABLE[数据库名.拥有者]表名
(<字段定义>)[,…n]
<字段定义>::={字段名 数据类型}
[[DEFAULT 常量表达式]|[IDENITY[(种子,增量)]]]
[<字段定义>][,…n]
5.选择查询语句:
SELECT 字段列表 (指定输出字段)
[INTO 新表] (将检索结果存储到一张新表)
From 数据源 (指定检索的数据来源)
[WHERE 搜索条件](指定对记录的过滤条件
[GROUP BY 分组表达式](对检索到的记录分组)
[HAVING 搜索表达式](指定对记录的过滤条件)
[ORDER BY 排序表达式](对检索到的记录进行排序处理)
三、实验内容
1.数据库的设计
根据实际情况建立E-R数据模型,需要考虑的实体有学生、班级、课程。其中E-R简图如下:
其中班级实体的属性为班级名称、班长学号等;学生属性为学号、姓名等;课程属性为课程号、课程名称;学习联系的属性为学生号、课程号、成绩。其他属性自行设计。
2.数据库的建立
建立数据库及相应的数据库表。
3.数据库的使用
a. 在数据库表中插入数据
b. 设计SQL语言使其分别满足下列要求:
查找学号为20070101的学生姓名;
查找“计算机基础”课程的课程编号;
查找20070102号同学学习的课程名;
查找学习“C语言”课程的学生学号;
查找200701班班长的姓名;
查找成绩高于80分的学生学号;
查找“C语言课程”的平均成绩;
所有设计语句请在sql server中检验。
4.实验报告要求
画出你所设计的E-R数据模型,并给出你所建立的数据库表结构,写出你的数据库及数据库表的建立的语句,给出符合上述条件的select语句
E-R数据模型:
数据库表的结构:
班级
学生
课程
学习联系
语句:
create database st
on primary
(name='st',
filename='c:\sql\st.mdf',
size=3mb,maxsize=10mb,filegrowth=10mb)
go
log on
(name='st',
filename='c:\sql\st.ldf',
size=3mb,maxsize=10mb,filegrowth=10mb)
go
use student
create table 班级 (
班级名称 nchar(10) primary key,
班长学号 nchar(10) not null)
go
create table 学生 (
学号 nchar(10) primary key,
姓名 nchar(10) not null)
go
create table 课程 (
课程号 nchar(10) primary key,
课程名称 nchar(10) not null)
go
create table 学习联系(
学号 nchar(10) primary key,
课程号 nchar(10) not null,
成绩 int null)
go
Select语句:
(1)select 姓名 from 学生
where 学号 =20070101
(2)select 课程号 from 课程
where 课程名称='计算机基础'
(3)select 课程名称 from 课程,学习联系
where 学习联系.学号='20070102' and 学习联系.课程号 = 课程.课程号
(4)select 学号 from 学习联系 ,课程
where 课程.课程名称='C语言' and 课程.课程号=学习联系.课程号
(5)select 姓名 from 班级,学生
where 班级名称=200701 and 学号=班长学号
(6)select 学号 from 学习联系
where 成绩>80
(7)select avg(成绩) as '平均成绩' from 课程,学习联系
where 课程名称='C语言' and 课程.课程号=学习联系.课程号