“数据库应用开发技术”实验报告

2020-2021学年 第二学期

实验名称：

学生姓名：

学 号：

专业班级：

所在学院：

指导教师：

职 称：

完成日期：

实验1 sql server 熟悉和数据库创建

实验目的

熟悉sql server 2005提供的服务管理器、企业管理器、查询分析器、客户端和服务器端网络实用工具等常用管理工具的使用。理解客户/服务器模式，理解面向连接与非面向连接的差别。理解交互式sql的工作机制。能够理解命名管道协议与tcp/ip协议的差别。能够登陆上sql server数据库服务器。

实验内容

1、启动sql server 服务。

2、打开sql server的企业管理器，连接上sql server服务器。展开左边树状窗口的各级结点，观察右边内容窗口的变化。

3、打开sql server的查询分析器，用use命令打开样例数据库pubs。

4、在查询窗口输入exec sp_help，运行后察看结果。

5、在查询窗口输入select * from authors ，运行后察看结果。

三、实验结果

3、当不确定当前所操作的是哪个数据库，可使用use来定位到某数据库。

4、

查询某张表的所有列。

实验2 简单查询

实验目的：

熟悉sql server的企业管理器和查询分析器的用户界面，掌握用企业管理器和查询分析器创建数据库，修改数据库和删除数据库的方法。

实验内容

分别使用sql server 2005企业管理器和t—sql语句，按下列要求创建、修改和删除用户数据库。

1、创建名称为company的数据库，数据库中包含一个数据文件，逻辑文件名为company_data，磁盘文件名为company_data.mdf，文件初始容量为5mb，最大容量为15mb，文件容量递增值为1mb；事务日志文件的逻辑文件名为company_log，磁盘文件名为company_log.ldf，文件初始容量为5mb，最大容量为10mb，文件容量递增值为1mb。

2、对该数据库进行修改：添加一个数据文件，逻辑文件名为company2_data，磁盘文件名为company2_data.ndf，文件初始容量为1mb，最大容量为5mb，文件容量递增值为1mb；将日志文件company_log的最大容量增加为15mb，文件容量递增值为2mb。

3、 在company数据库中添加一个文件组tempgroup，并向该文件组中添加一个容量为3mb，最大容量为10mb，递增量为1mb的数据文件，该数据文件的逻辑文件名为company3_data，磁盘文件名为company3_data.ndf。

4、在company数据库中删除数据文件company2_data。

删除数据库company。

采用默认设置创建数据库company。

实验结果

代码如下：

CREATE DATABASE company

ON PRIMARY

(NAME=company_data,

FILENAME=‘D:\zlj\company_data.mdf’,

SIZE=5,

MAXSIZE=15,

FILEGROWTH=1)

LOG ON

(NAME=company_log,

FILENAME=‘D:\zlj\company_log.ldf’,

SIZE=5,

MAXSIZE=10,

FILEGROWTH=1)

2、代码如下：

ALTER DATABASE company

ADD FILE

(NAME=company2_data,

FILENAME=‘D:\zlj\company2_data.ndf’,

SIZE=1,

MAXSIZE=5,

FILEGROWTH=1)

ALTER DATABASE company

MODIFY FILE

(NAME=company_log,

FILENAME=‘D:\zlj\company_log.ldf’,

MAXSIZE=15,

FILEGROWTH=2)

3、代码如下：

ALTER DATABASE company

ADD FILEGROUP tempgroup

ALTER DATABASE company

ADD FILE

(NAME=company3_data,

FILENAME=‘D:\zlj\company3_data.ndf’,

SIZE=3,

MAXSIZE=10,

FILEGROWTH=1)

4、代码如下：

ALTER DATABASE company

REMOVE FILE company2_data

5、代码如下：

DROP DATABASE company

6、代码如下：

CREATE DATABASE company

实验3 创建和修改数据表

一、实验目的：

熟悉有关数据表的创建和修改等工作，理解数据库模式的概念，了解主键约束、外键约束、unique约束和check约束的创建和应用。要求学生熟练掌握使用企业管理器和t—sql语句create table、alter table及drop table语句对数据表进行管理。

实验内容

分别在sql server 2005企业管理器和在查询分析分析器中使用t—sql语句完成以下操作：

员工人事表employee

emp

no

char(5)

not null

primary key

员工编号

emp_name

varchar(10)

not null

员工姓名

sex

char(2)

not null

性别

dept

varchar(10)

not null

所属部门

title

varchar(10)

not null

职称

date_hired

datetime

not null

雇佣日

birthday

datetime

null

生日

salary

int

not n

ll

薪水

t

lephone

varchar(20)

null

电话

addr

varchar(50)

null

住址

客户表customer

cust_id

char(5)

not null

primary key

客户号

cust_name

varchar(20)

not null

客户名称

addr

varchar(40)

not null

客户住址

tel_no

varchar(20)

not null

客户电话

zip

char(6)

null

邮政编码

销售主表sales

order_no

int

not null

primary key

订单编号

cust_id

char(5)

not null

客户号

sale_id

char(5)

not null

业务员编号

tot_amt

numeric(9,2)

not null

订单金额

order_date

datetime

not null

订货日期

销货明细表sale_item

order_no

int

not null

primary key

订单编号

prod_id

char(5)

not null

pri

ary key

产品编号

qty

int

not null

销售数量

unit_price

numeric(7,2)

not null

单价

order_

ate

datetime

null

订单日期

产品名称表product

prod_id

char(5)

not null

primary key

产品编号

prod_name

varchar(20)

not null

产品名称

1、在数据库company中创建以上五张表，并设置各表的主键。

1

2、在销售主表sales中添加字段“发票号码” invoice_no，char(10)，not null。

3、 添加外键约束：

在销售主表sales的业务员编号字段sale_id上添加外键约束，参照字段为员工表employee中的字段员工编号emp_no，约束名为fk_sale_id。

在销售主表sales的客户号字段cust_id上添加外键约束，参照字段为客户表customer中的字段客户号cust_id，约束名为fk_cust_id。

在销售明细表sale_item的订单编号字段order_no上添加外键约束，参照字段为销售主表sales中的字段订单编号order_no，约束名为fk_order_no。

在销售明细表sale_item的产品编号字段prod_id上添加外键约束，参照字段为产品名称表product中的产品编号字段prod_id，约束名为fk_prod_id。

4、添加核查约束：

将员工表employee中的薪水字段salary的值限定在1000至10000间，约束名为ck_salary。

将员工表employee中的员工编号字段emp_no设定为以“e”字母开头, 后面跟5位数的编号，约束名为ck_emp_no。

将员工表employee中的性别字段设定这取值只能是“男”和“女”。约束名为ck_sex。

将销售主表sales中的发票号码字段invoice_no设定为以“i”字母开头，后面跟9位数的编号，约束名为ck_inno。

5、为销售主表sales中的字段发票号码invoice_no设置为唯一约束，约束名为un_inno。

实验结果

1、代码如下：

CREATE DATABASE company

USE company

CREATE TABLE employee

(emp_no char(5) not null primary key,

emp_name varchar(10) not null,

sex char(2) not null,

dept varchar(10) not null,

title varchar(10) not null,

data_hired datetime not null,

birthday datetime null,

salary int not null,

telephone varchar(20) null,

addr varchar(50) null)

CREATE TABLE customer

(cust_id char(5) not null primary key,

cust_name varchar(20) not null,

addr varchar(40) not null,

tel_no varchar(20) not null,

zip char(6) null)

CREATE TABLE sales

(order_no int not null primary key,

cust_id char(5) not null,

sale_id char(5) not null,

tot_amt numeric(9,2) not null,

order_date datetime not null)

CREATE TABLE sale_item

(order_no int not null primary key,

prod_id char(5) not null,

qty int not null,

unit_price numeric(7,2) not null,

order_ate datetime null)

CREATE TABLE product

(prod_id char(5) not null primary key,

prod_name varchar(20) not null)

2、代码如下：

USE company

ALTER TABLE sales

ADD invoice_no char(10) not null;

3、代码如下：

ALTER TABLE sales

ADD CONSTRAINT FK_sale_id

FOREIGN KEY (sale_id)

REFERENCES employee(emp_no)

ALTER TABLE sales

ADD CONSTRAINT FK_cust_id

FOREIGN KEY (cust_id)

REFERENCES customer(cust_id)

ALTER TABLE sale_item

ADD CONSTRAINT FK_order_no

FOREIGN KEY (order_no)

REFERENCES sales(order_no)

ALTER TABLE sale_item

ADD CONSTRAINT FK_prod_id

FOREIGN KEY (prod_id)

REFERENCES product(prod_id)

4、代码如下：

ALTER TABLE employee

ADD CONSTRAINT ck_salary

CHECK(salary>=1000 AND salary<=10000)

ALTER TABLE employee

ADD CONSTRAINT ck_emp_no

CHECK(emp_no like’[e][0-9][0-9][0-9][0-9][0-9]’);

ALTER TABLE employee

ADD CONSTRAINT ck_sex

CHECK(sex=‘男’OR sex = ‘女’)

ALTER TABLE sales

ADD CONSTRAINT ck_inno

CHECK(invoice_no like’[i][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’);

5、代码如下：

ALTER TABLE sales

ADD CONSTRAINT un_inno UNIQUE (invoice_no)

实验4 简单的单表查询

一、实验目的：

熟练掌握用select语句实现简单的单表查询。掌握select子句、from子句、where子句及order by 子句的用法。

实验内容

运行查询文件company.sql，生成上机必要的数据，然后完成以下操作。

1、查找所有经理的姓名、职称、薪水。

2、在销售主表sales中查找销售金额大于等于10000元的订单。

3、在员工表employee中查找薪水在4000至8000元之间的员工。

4、在员工表employee中查找住址为上海、北京、天津这三个城市的员工。

在客户表customer中查找住址不在上海、北京、天津这三个城市的客户。

在员工表employee中查找姓“王”用姓名最后一个字为“功”的员工。

在客户表customer中查找姓“刘”的客户名称、电话。

查找出职称为“经理”或“职员”的女工的信息。

查找薪水最高的前三条员工记录。

查找订单金额最高的前10%的订单记录。

查找员工表中所属部门（去掉重复记录）。

查找员工表中的所有记录，并按薪水由低到高进行排序。

实验结果

1、查找所有经理的姓名、职称、薪水。

2、在销售主表sales中查找销售金额大于等于10000元的订单。

3、在员工表employee中查找薪水在4000至8000元之间的员工。

SELECT*

from employee

where salary between 4000 and 8000;

4、在员工表employee中查找住址为上海、北京、天津这三个城市的员工。

SELECT *

from employee

where addr in (‘上海市’,‘北京市’,‘天津市’);

5、在客户表customer中查找住址不在上海、北京、天津这三个城市的客户。

SELECT *

from customer

where addr not in (‘上海市’,‘北京市’,‘天津市’);

6、在员工表employee中查找姓“王”用姓名最后一个字为“功”的员工。

SELECT *

from employee

where emp_name like (‘王_功’)

7、在客户表customer中查找姓“刘”的客户名称、电话。

SELECT *

FROM customer

WHERE cust_name like(‘刘%’)

8、查找出职称为“经理”或“职员”的女工的信息。

SELECT emp_name , title , sex

from employee

where title = ’ 经理 ’ or title = ’ 职员 ’ and sex = ‘女’

9、查找薪水最高的前三条员工记录。

SELECT top 3 *

from employee

order by salary desc ;

查找订单金额最高的前10%的订单记录。

SELECT top 10 percent *

from sales

order by tot_amt desc ;

查找员工表中所属部门（去掉重复记录）。

SELECT distinct dept

from employee

12、查找员工表中的所有记录，并按薪水由低到高进行排序。

SELECT *

from employee

order by salary

实验5 复杂的单表查询

一、实验目的

熟练掌握select查询语句中的group by 子句、having子句的用法，以及汇总函数的使用。

二、实验内容

1、在员工表employee中统计员工人数。

2、统计各部门员工的员工人数及平均薪水。

3、查询销售业绩超过10000元的员工编号。

4、计算每一产品销售数量总和与平均销售单价。

5、统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水（在group by 子句中使用cube关键字）。

6、统计各部门不同性别、或各部门或所有员工的平均薪水（在group by 子句中使用rollup关键字）。

7、计算出一共销售了几种产品。

8、显示sale_item表中每种产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。

9、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。

10、查询每位业务员各个月的业绩，并按业务员编号、月份降序排序。

三、实验结果

1、在员工表employee中统计员工人数。

SELECT

COUNT ( emp_no ) AS 员工总人数

from employee

2、统计各部门员工的员工人数及平均薪水。

SELECT

dept as 部门名 , count ( emp_no ) 员工人数 , avg ( salary ) as 平均薪水

from employee

group by dept

go

3、查询销售业绩超过10000元的员工编号。

SELECT a . emp_no as 员工编号 , b . tot_amt as 销售业绩

from employee a , sales b

where tot_amt > 10000

go

计算每一产品销售数量总和与平均销售单价。

SELECT prod_id , sum ( qty ) as 销售数量总和 , avg ( unit_price ) as 平均销售单价

from sale_item

group by prod_id

go

统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水（在group by 子句中使用cube关键字）。

SELECT dept , sex , avg ( salary )

from employee

group by cube ( sex , dept )

go

统计各部门不同性别、或各部门或所有员工的平均薪水（在group by 子句中使用rollup关键字）。

SELECT dept , sex , avg ( salary )

from employee

group by rollup ( sex , dept )

go

计算出一共销售了几种产品。

SELECT count ( prod_id ) as 产品数

from product

显示sale_item表中每种产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。

SELECT prod_id , sum ( qty * unit_price ) as 订购金额总和

from sale_item

group by prod_id

order by 订购金额总和 desc

go

计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。

SELECT prod_id as 产品编号 , month ( order_date ) as 月份 , sum ( qty * unit_price ) as 销售金额总和

from sale_item

group by month ( order_date ), prod_id

order by month ( order_date ), prod_id

go

10、查询每位业务员各个月的业绩，并按业务员编号、月份降序排序。

SELECT sale_id as 业务员编号 , month ( order_date ) as 月份 , sum ( tot_amt ) as 业绩

from sales

group by sale_id , month ( order_date )

order by sale_id , month ( order_date ) desc

go

实验6 连接查询

一、实验目的

掌握使用连接的方法从多个表中查询数据。理解内连接、外连接（包括左外连接、右外连接和全外连接）、自身连接的概念和使用。要求学生熟练掌握在from子句和在where子句中指定连接条件的这两种方法。

二、实验内容

1、查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。

2、检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。

3、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。

4、查询在每张订单中订购金额超过24000元的客户名及其地址。

5、查找有销售记录的客户编号、名称和订单总额。

6、每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列。

7、查找在1997年中有销售记录的客户编号、名称和订单总额。

8、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。

三、实验结果

1、查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。

SELECT a.emp_name, a.sex, a.title, a.salary,a.addr, b.emp_name, b.sex,b.title, b.salary, b.addr

from employee AS a inner join employee AS b

on(a.emp_no!=b.emp_no) and(a.emp_name>b.emp_name) and (a.addr=b.addr)

2、检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。

SELECT a.prod_id, b.prod_name, a.qty, a.unit_price

from sale_item a, product b

where a.prod_id=b.prod_id

3、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。

SELECT product.prod_id,product.prod_name,qty,unit_price

from product,sale_item

where unit_price>2400 and

product.prod_id=sale_item.prod_id

order by sale_item.prod_id

4、查询在每张订单中订购金额超过24000元的客户名及其地址。

SELECT cust_name,addr

from customer a,sales b

where a.cust_id=b.cust_id and tot_amt>24000

5、查找有销售记录的客户编号、名称和订单总额。

SELECT a.cust_id, cust_name, sum(tot_amt) totprice

from customer a, sales b

where a.cust_id=b.cust_id

group by a.cust_id,cust_name

6、每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列。

SELECT cust_id,prod_id,sum(qty),sum(qty*unit_price)/sum(qty)

From sales a, sale_item b

Where a.order_no=b.order_no

Group by cust_id,prod_id

Order by cust_id,prod_id

7、查找在1997年中有销售记录的客户编号、名称和订单总额。

SELECT a.cust_id, cust_name,sum(tot_amt) totprice

from customer a,sales b

where a.cust_id=b.cust_id and convert(char(4),order_date,120)=‘1997’

group by a.cust_id,cust_name

8、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。

左外连接：

SELECT b.prod_id, a.prod_name, b.qty, b.unit_price

from product a LEFT OUTER JOIN sale_item b

on (a.prod_id=b.prod_id) AND b.unit_price>2400;

右外连接：

SELECT b.prod_id, a.prod_name, b.qty, b.unit_price

from product a RIGHT OUTER JOIN sale_item b

on (a.prod_id=b.prod_id) AND b.unit_price>2400;

完整外部连接：

SELECT b.prod_id, a.prod_name, b.qty, b.unit_price

from product a FULL JOIN sale_item b

on (a.prod_id=b.prod_id) AND b.unit_price>2400;

实验7 嵌套查询

一、实验目的

掌握select语句的嵌套使用，实现多表的复杂查询，进一步理解select语句的高级使用方法。

二、实验内容

1、由sales表中查找出销售金额最高的订单。

2、由sales表中查找出订单金额大于“e0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。

3、找出公司女业务员所接的订单。

4、找出目前业绩未超过200000元的员工。

5、在销售主表sales中查询销售业绩最高的业务员编号及销售业绩。

6、找出目前业绩超过232000元的员工编号和姓名。

7、查询订购的产品至少包含了订单10003中所订购产品的订单。

8、查询末承接业务的员工的信息。

三、实验结果

1、由sales表中查找出销售金额最高的订单。

SELECT *

from sales

where tot_amt=(select max(tot_amt)

from sales)

2、由sales表中查找出订单金额大于“e0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。

SELECT sale_id,tot_amt

from sales

where tot_amt> all

(select tot_amt from sales)

3、找出公司女业务员所接的订单。

SELECT sale_id,tot_amt

from sales

where sale_id in

(select sale_id

from employee

where sex=‘女’)

4、找出目前业绩未超过200000元的员工。

SELECT *

from employee

where emp_no in

(select sale_id

from sales

group by sale_id

having sum(tot_amt)>2000)

5、在销售主表sales中查询销售业绩最高的业务员编号及销售业绩。

SELECT sale_id AS ’ 业务员编号 ',tot_amt AS ’ 销售业绩 ’

from sales

where sale_id=( select top 1 sale_id from sales

order by tot_amt desc )

6、找出目前业绩超过232000元的员工编号和姓名。

SELECT emp_name , emp_no

from employee

where emp_no in

( select sale_id

from sales

group by sale_id

having SUM ( tot_amt )> 232000 )

7、查询订购的产品至少包含了订单10003中所订购产品的订单。

SELECT distinct *

from sale_item

where prod_id in

( select prod_id

from sale_item

where order_no = ‘10003’ )

8、查询末承接业务的员工的信息。

SELECT *

from employee a

where not exists

(select *

from sales b

where a.emp_no = b.sale_id )

实验8 数据更新

一、实验目的

熟练使用insert/delete/update语句进行表的更新操作。

二、实验内容

1、为各表添加若干条记录，必须符合实验二中设定的各种约束。

2、将每个员工的薪水上调10%。

3、删除sales表中作废的订单（其发票号码为‘i000000004’），其订货明细表中的数据也一并删除。

4、删除所有没有销售业绩的员工记录。

5、对那些只要有一笔销售业绩超过20000元的员工的薪水增加500元。

三、实验结果

1、为各表添加若干条记录，必须符合实验二中设定的各种约束。

INSERT INTO product VALUES(‘55555’,‘CD’)

2、将每个员工的薪水上调10%。

UPDATE employee

set salary=salary*1.1

where title= ‘员工’;

3、删除sales表中作废的订单（其发票号码为‘i000000004’），其订货明细表中的数据也一并删除。

DELETE

from sales

where invoice_no=‘I000000004’

4、删除所有没有销售业绩的员工记录。

DELETE employee

where dept =‘业务’ and emp_no not in

(select distinct sale_id

from sales )

5、对那些只要有一笔销售业绩超过20000元的员工的薪水增加500元。

UPDATE employee

set salary =salary+500

where emp_no in

(select distinct sale_id

from sales

where tot_amt >20000)