星星博客 »  > 

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

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

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中创建以上五张表,并设置各表的主键。

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)

相关文章