SQL SERVER数据库实验报告(详细)

2023年6月21日发(作者：)

数据库原理及应用实验报告

学号： 姓名：

实验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 文件组名 文件组属性}

::=

(NAME=逻辑文件名

[，FILENAME=‘实际文件名和路径’]

[，SIZE=容量]

[，MAXSIZE={最大容量|UNLIMITED}]

[，FILEGROWTH=递增量]）

三、实验内容：

1. 使用SQL语句完成下题

（1） 使用CREATE DATABASE语句创建一个名为“st”数据库，要求①主要数据文件：逻辑文件名为“stdata1”,实际文件名为“”②事务日志文件：逻辑文件名为“stlog1”,实际文件名为“”③初始容量均为3MB，最大容量均为10MB，递增量均为1MB。

1 （2） 使用CREATE DATABASE语句创建一个名为“student”数据库，要求①主要数据文件：逻辑文件名为“studentdata1”,实际文件名为“”②非主要数据文件：逻辑文件名为“studentdata2”,实际文件名为“”③事务日志文件：逻辑文件名为“studentlog1”,实际文件名为“”④初始容量均为3MB，最大容量均为10MB，递增量均为1MB。

（3） 使用ALTER DATABASE语句在“st” 数据库中添加一个数据文件和一个事务日志文件，其逻辑文件名分别为“studentdata3”和“studentlog2”，其实际文件名分别为“”和“”，初始容量均为1MB，最大容量均为10MB，递增量均为1MB；然后将数据文件studentdata3的初始容量增加到5MB，上限增加到20MB。

解：(1)create database st on primary

(name=stdata1,

filename='d:',

size=3mb, maxsize=10mb, filegrowth=1mb)

log on

(name='stlog1',

filename='d:',

size=1mb, maxsize=10mb, filegrowth=1mb)

go

(2)create database student on primary

(name=studentdata1,

filename='d:',

size=3mb, maxsize=10mb, filegrowth=1mb),

(name=studentdata3,

filename='d:',

size=1mb, maxsize=10mb, filegrowth=1mb)

log on

(name='studentlog1',

filename='d:',

size=1mb, maxsize=10mb, filegrowth=1mb)

go

(3)alter database st

add file

(name=studentdata3,

filename='d:',

size=1mb, maxsize=10mb, filegrowth=1mb),

(name='studentlog2',

filename='d:',

size=1mb, maxsize=10mb, filegrowth=1mb)

alter database st

modify file

(name=studentdata3,

size=5mb,maxsize=20mb)

2

实验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,

3 专业 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* 月薪

4 （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 ,ENAME,,CNAME,SALARY FROM (EMP JOIN WORKS ON

= ) JOIN COMP

ON = 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='男'

5

7.3 订单

订单号 日期 客户编号 业务员编号 金额

001 2010-09-08 8331 1991 100000

002 2011-05-12 8334 1993 2000000

003 2010-10-11 8332 1992 200000

004 2010-11-12 8333 1993 300000

005 2010-12-13 8334 1994 400000

006 2011-01-01 8335 1995 500000

007 2011-01-23 8333 1992 2500000

008 2011-02-05 8332 1995 320000

009 2011-03-06 8331 1994 3500000

010 2011-04-12 8334 1992 2400000

客户

客户编号 客户姓名 地址 类别

A1 张国荣 江西南昌 xx

A2 李斯 江西上饶 yy

A3 刘德华 浙江杭州 zz

A4 陈凯歌 福建厦门 xy

A5 李连杰 钓鱼岛 yz

业务员

业务员编号 业务员姓名 性别 年龄 月薪

1991 刘欢 男 25 1800

1992 无名 男 24 2000

1993 长风 男 28 2200

1994 飞雪 女 22 1500

1995 残剑 男 21 2800

7.5 COMP

CNO CNAME CITY

C1 国防部 北京

C2 外交部 上海

C3 卫生部 珠海

C4 外汇局 深圳

C5 住建部 南昌

C6 教育部 天津

C7 发改委 南昌

C8 文化部 上饶

C9 全国妇联 纽约

C10 IBM 南昌

6 EMP

ENO ENAME AGE SEX ECITY

E1 海峰 22 女 南昌

E2 佩佩 25 女 纽约

E3 艾艾 26 男 北京

E4 明明 35 女 上海

E5 羽灵 36 男 深圳

E6 莉莉 34 男 杭州

E7 渐渐 45 男 珠海

E8 佳佳 46 女 广州

E9 可可 54 女 香港

E10 瓜瓜 51 男 上饶

WORKS

E1 C1 2500

E2 C2 2000

E3 C3 1500

E4 C4 800

E5 C5 900

E6 C6 1800

E7 C7 3600

E8 C8 4500

E3 C10 1600

E10 C2 4400

E1 C4 3400

E3 C9 2300

E4 C8 1500

实验四、设计性实验：建立小型数据库管理系统

一、实验目的

熟悉数据库建立及应用的方法，系统的应用数据库。

二、实验原理

1.创建数据库语句：

CREATE DATABASE 数据库名

[ON [PRIMARY] //指定一个文件为主要文件

[<文件格式>[，„n]]

[，<文件组格式>[，„n]] ]

[LOG ON {<文件格式>[，„n]}]

7 [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 文件组名 文件组属性}

::=

(NAME=逻辑文件名

[，FILENAME=‘实际文件名和路径’]

[，SIZE=容量]

[，MAXSIZE={最大容量|UNLIMITED}]

[，FILEGROWTH=递增量]）

3.备份，恢复数据库原理语句：

RESTORE DATABASE 数据库 FROM 备份设备名

EXEC sp_attach_db ‘数据库名’,‘文件名’ [,„16]

4.创建数据库表语句：

CREATE TABLE[数据库名.拥有者]表名

（<字段定义>）[,„n]

<字段定义>::={字段名 数据类型}

8 [[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班班长的姓名；

属于

n

n

学生

学习

1

m

课程

9 查找成绩高于80分的学生学号；

查找“C语言课程”的平均成绩；

所有设计语句请在sql server中检验。

４.实验报告要求

画出你所设计的Ｅ－Ｒ数据模型，并给出你所建立的数据库表结构，写出你的数据库及数据库表的建立的语句，给出符合上述条件的select语句

Ｅ－Ｒ数据模型：

学习联系

班级姓名

班长学号

成绩

班级

学号

姓名

学习

学生

数据库表的结构：

班级

班级名称

学生

学号

课程

课程号

学习联系

学号

语句：

create database st

on primary

(name='st',

10

课程号

课程名称

课程

属于

班长姓名

姓名

课程名称

课程号

成绩

filename='c:',

size=3mb,maxsize=10mb,filegrowth=10mb)

go

log on

(name='st',

filename='c:',

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 课程.课程号=学习联系.课程号

11

2023年6月21日发(作者：)

数据库原理及应用实验报告

学号： 姓名：

实验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 文件组名 文件组属性}

::=

(NAME=逻辑文件名

[，FILENAME=‘实际文件名和路径’]

[，SIZE=容量]

[，MAXSIZE={最大容量|UNLIMITED}]

[，FILEGROWTH=递增量]）

三、实验内容：

1. 使用SQL语句完成下题

（1） 使用CREATE DATABASE语句创建一个名为“st”数据库，要求①主要数据文件：逻辑文件名为“stdata1”,实际文件名为“”②事务日志文件：逻辑文件名为“stlog1”,实际文件名为“”③初始容量均为3MB，最大容量均为10MB，递增量均为1MB。

1 （2） 使用CREATE DATABASE语句创建一个名为“student”数据库，要求①主要数据文件：逻辑文件名为“studentdata1”,实际文件名为“”②非主要数据文件：逻辑文件名为“studentdata2”,实际文件名为“”③事务日志文件：逻辑文件名为“studentlog1”,实际文件名为“”④初始容量均为3MB，最大容量均为10MB，递增量均为1MB。

（3） 使用ALTER DATABASE语句在“st” 数据库中添加一个数据文件和一个事务日志文件，其逻辑文件名分别为“studentdata3”和“studentlog2”，其实际文件名分别为“”和“”，初始容量均为1MB，最大容量均为10MB，递增量均为1MB；然后将数据文件studentdata3的初始容量增加到5MB，上限增加到20MB。

解：(1)create database st on primary

(name=stdata1,

filename='d:',

size=3mb, maxsize=10mb, filegrowth=1mb)

log on

(name='stlog1',

filename='d:',

size=1mb, maxsize=10mb, filegrowth=1mb)

go

(2)create database student on primary

(name=studentdata1,

filename='d:',

size=3mb, maxsize=10mb, filegrowth=1mb),

(name=studentdata3,

filename='d:',

size=1mb, maxsize=10mb, filegrowth=1mb)

log on

(name='studentlog1',

filename='d:',

size=1mb, maxsize=10mb, filegrowth=1mb)

go

(3)alter database st

add file

(name=studentdata3,

filename='d:',

size=1mb, maxsize=10mb, filegrowth=1mb),

(name='studentlog2',

filename='d:',

size=1mb, maxsize=10mb, filegrowth=1mb)

alter database st

modify file

(name=studentdata3,

size=5mb,maxsize=20mb)

2

实验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,

3 专业 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* 月薪

4 （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 ,ENAME,,CNAME,SALARY FROM (EMP JOIN WORKS ON

= ) JOIN COMP

ON = 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='男'

5

7.3 订单

订单号 日期 客户编号 业务员编号 金额

001 2010-09-08 8331 1991 100000

002 2011-05-12 8334 1993 2000000

003 2010-10-11 8332 1992 200000

004 2010-11-12 8333 1993 300000

005 2010-12-13 8334 1994 400000

006 2011-01-01 8335 1995 500000

007 2011-01-23 8333 1992 2500000

008 2011-02-05 8332 1995 320000

009 2011-03-06 8331 1994 3500000

010 2011-04-12 8334 1992 2400000

客户

客户编号 客户姓名 地址 类别

A1 张国荣 江西南昌 xx

A2 李斯 江西上饶 yy

A3 刘德华 浙江杭州 zz

A4 陈凯歌 福建厦门 xy

A5 李连杰 钓鱼岛 yz

业务员

业务员编号 业务员姓名 性别 年龄 月薪

1991 刘欢 男 25 1800

1992 无名 男 24 2000

1993 长风 男 28 2200

1994 飞雪 女 22 1500

1995 残剑 男 21 2800

7.5 COMP

CNO CNAME CITY

C1 国防部 北京

C2 外交部 上海

C3 卫生部 珠海

C4 外汇局 深圳

C5 住建部 南昌

C6 教育部 天津

C7 发改委 南昌

C8 文化部 上饶

C9 全国妇联 纽约

C10 IBM 南昌

6 EMP

ENO ENAME AGE SEX ECITY

E1 海峰 22 女 南昌

E2 佩佩 25 女 纽约

E3 艾艾 26 男 北京

E4 明明 35 女 上海

E5 羽灵 36 男 深圳

E6 莉莉 34 男 杭州

E7 渐渐 45 男 珠海

E8 佳佳 46 女 广州

E9 可可 54 女 香港

E10 瓜瓜 51 男 上饶

WORKS

E1 C1 2500

E2 C2 2000

E3 C3 1500

E4 C4 800

E5 C5 900

E6 C6 1800

E7 C7 3600

E8 C8 4500

E3 C10 1600

E10 C2 4400

E1 C4 3400

E3 C9 2300

E4 C8 1500

实验四、设计性实验：建立小型数据库管理系统

一、实验目的

熟悉数据库建立及应用的方法，系统的应用数据库。

二、实验原理

1.创建数据库语句：

CREATE DATABASE 数据库名

[ON [PRIMARY] //指定一个文件为主要文件

[<文件格式>[，„n]]

[，<文件组格式>[，„n]] ]

[LOG ON {<文件格式>[，„n]}]

7 [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 文件组名 文件组属性}

::=

(NAME=逻辑文件名

[，FILENAME=‘实际文件名和路径’]

[，SIZE=容量]

[，MAXSIZE={最大容量|UNLIMITED}]

[，FILEGROWTH=递增量]）

3.备份，恢复数据库原理语句：

RESTORE DATABASE 数据库 FROM 备份设备名

EXEC sp_attach_db ‘数据库名’,‘文件名’ [,„16]

4.创建数据库表语句：

CREATE TABLE[数据库名.拥有者]表名

（<字段定义>）[,„n]

<字段定义>::={字段名 数据类型}

8 [[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班班长的姓名；

属于

n

n

学生

学习

1

m

课程

9 查找成绩高于80分的学生学号；

查找“C语言课程”的平均成绩；

所有设计语句请在sql server中检验。

４.实验报告要求

画出你所设计的Ｅ－Ｒ数据模型，并给出你所建立的数据库表结构，写出你的数据库及数据库表的建立的语句，给出符合上述条件的select语句

Ｅ－Ｒ数据模型：

学习联系

班级姓名

班长学号

成绩

班级

学号

姓名

学习

学生

数据库表的结构：

班级

班级名称

学生

学号

课程

课程号

学习联系

学号

语句：

create database st

on primary

(name='st',

10

课程号

课程名称

课程

属于

班长姓名

姓名

课程名称

课程号

成绩

filename='c:',

size=3mb,maxsize=10mb,filegrowth=10mb)

go

log on

(name='st',

filename='c:',

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 课程.课程号=学习联系.课程号

11