数据库系统原理实验报告

《数据库系统原理》

实验报告

班级________ 姓名________________ 学号___________

信息与电子工程学院

《数据库原理》实验报告 信息与电子工程学院

实验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、 5、查询某张表的所有列。 1

《数据库原理》实验报告 信息与电子工程学院

实验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。 5、删除数据库company。 6、采用默认设置创建数据库company。 三、实验结果 1、create database company on ( name=company_data, filename='d:\\microsoft sql server\\mssql10_50.sqlexpress\\mssql\\data\\company_data.mdf', size=5mb, maxsize=15mb, filegrowth=1mb ) log on (name=company_log,

2

《数据库原理》实验报告 信息与电子工程学院

filename='d:\\microsoft sql server\\mssql10_50.sqlexpress\\mssql\\data\\company_log.ldf', size=5mb, maxsize=10mb, filegrowth=1mb ); 2、alter database company add file (name=company2_data, filename='d:\\microsoft sql server\\mssql10_50.sqlexpress\\mssql\\data\\company2_data.mdf', size=1mb, maxsize=5mb, filegrowth=1mb) alter database company modify file (name='company_log',maxsize=15mb,filegrowth=2mb); 3、alter database company add filegroup tempgroup alter database company add file (name='company3_data', filename='d:\\microsoft sql server\\mssql10_50.sqlexpress\\mssql\\data\\company3_data.ndf', size=3mb, maxsize=10mb, filegrowth=1mb) to filegroup tempgroup; 4、alter database company remove file company2_data; 5、drop database company; 6、create database company; 3

《数据库原理》实验报告 信息与电子工程学院

实验3 创建和修改数据表

一、实验目的： 熟悉有关数据表的创建和修改等工作，理解数据库模式的概念，了解主键约束、外键约束、unique约束和check约束的创建和应用。要求学生熟练掌握使用企业管理器和t—sql语句create table、alter table及drop table语句对数据表进行管理。 二、实验内容 分别在sql server 2005企业管理器和在查询分析分析器中使用t—sql语句完成以下操作： 员工人事表employee emp_no emp_name sex dept title date_hired birthday salary telephone addr 客户表customer cust_id cust_name addr tel_no zip 销售主表sales order_no cust_id sale_id int char(5) char(5) not null not null not null primary key 订单编号 客户号 业务员编号 char(5) varchar(20) varchar(40) varchar(20) char(6) not null not null not null not null null primary key 客户号 客户名称 客户住址 客户电话 邮政编码 char(5) varchar(10) char(2) varchar(10) varchar(10) datetime datetime int varchar(20) varchar(50) not null not null not null not null not null not null null not null null null primary key 员工编号 员工姓名 性别 所属部门 职称 雇佣日 生日 薪水 电话 住址 4

《数据库原理》实验报告 信息与电子工程学院

tot_amt order_date numeric(9,2) datetime not null not null 订单金额 订货日期 销货明细表sale_item order_no prod_id qty unit_price order_date int char(5) int numeric(7,2) datetime not null not null not null not null null primary key primary key 订单编号 产品编号 销售数量 单价 订单日期 产品名称表product prod_id char(5) not null not null primary key 产品编号 产品名称 prod_name varchar(20) 1、在数据库company中创建以上五张表，并设置各表的主键。 2、在销售主表sales中添加字段“号码” invoice_no，char(10)，not null。 3、 添加外键约束： a.在销售主表sales的业务员编号字段sale_id上添加外键约束，参照字段为员工表employee中的字段员工编号emp_no，约束名为fk_sale_id。 b.在销售主表sales的客户号字段cust_id上添加外键约束，参照字段为客户表customer中的字段客户号cust_id，约束名为fk_cust_id。 c.在销售明细表sale_item的订单编号字段order_no上添加外键约束，参照字段为销售主表sales中的字段订单编号order_no，约束名为fk_order_no。 d.在销售明细表sale_item的产品编号字段prod_id上添加外键约束，参照字段为产品名称表product中的产品编号字段prod_id，约束名为fk_prod_id。 4、添加核查约束： a.将员工表employee中的薪水字段salary的值限定在1000至10000间，约束名为ck_salary。 b.将员工表employee中的员工编号字段emp_no设定为以“e”字母开头, 后面跟5位数的编号，约束名为ck_emp_no。 c.将员工表employee中的性别字段设定这取值只能是“男”和“女”。约束名为ck_sex。 5

《数据库原理》实验报告 信息与电子工程学院

d.将销售主表sales中的号码字段invoice_no设定为以“i”字母开头，后面跟9位数的编号，约束名为ck_inno。 5、为销售主表sales中的字段号码invoice_no设置为唯一约束，约束名为un_inno。 三、实验结果 1、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, date_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 6

《数据库原理》实验报告 信息与电子工程学院

); create table sale_item (order_no int not null, prod_id char(5) not null, qty int not null, unit_price numeric(7,2) not null, order_date datetime null, primary key(order_no,prod_id) ); create table product (prod_id char(5) not null primary key, prod_name varchar(20) not null ); 2、alter table sales add invoice_no char(10) not null; 3、a、alter table sales add constraint fk_sale_id foreign key (sale_id) references employee(emp_no); b、alter table sales add constraint fk_cust_id foreign key (cust_id) references customer(cust_id); c、alter table sale_item add constraint fk_order_no foreign key (order_no) references sales(order_no); d、alter table sale_item add constraint fk_prod_id foreign key (prod_id) references product(prod_id); 4、a、alter table employee add constraint ck_salary check (salary between 1000 and 10000); b、alter table employee 7

《数据库原理》实验报告 信息与电子工程学院

add constraint ck_emp_no check(emp_no like 'e[0-9][0-9][0-9][0-9][0-9]'); c、alter table employee add constraint ck_sex check(sex like('男''女')); d、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中查找住址为上海、北京、天津这三个城市的员工。 5、在客户表customer中查找住址不在上海、北京、天津这三个城市的客户。 6、在员工表employee中查找姓“王”用姓名最后一个字为“功”的员工。 7、在客户表customer中查找姓“刘”的客户名称、电话。 8、查找出职称为“经理”或“职员”的女工的信息。 9、查找薪水最高的前三条员工记录。 10、查找订单金额最高的前10%的订单记录。 11、查找员工表中所属部门（去掉重复记录）。 12、查找员工表中的所有记录，并按薪水由低到高进行排序。 8

《数据库原理》实验报告 信息与电子工程学院

三、实验结果 1、select emp_name,title,salary from employee where title ='经理'; 2、select * from sales where tot_amt>10000; 3、select * from employee where salary between 4000 and 8000; 4、select * from employee where addr in('上海','北京','天津'); 5、select * from customer where addr not in('上海','北京','天津'); 6、select * from employee where emp_name like '王%五'; 7、select cust_name,tel_no from customer where cust_name like '刘%'; 8、select * from employee where title in ('经理','职员') and sex='女'; 9、select top 3 * from employee order by salary desc; 10、select top 10 percent * from sales order by tot_amt desc; 11、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、计算出一共销售了几种产品。 9

《数据库原理》实验报告 信息与电子工程学院

8、显示sale_item表中每种产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。 9、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。 10、查询每位业务员各个月的业绩，并按业务员编号、月份降序排序。 三、实验结果 1、select count(emp_no) 总人数 from employee; 2、select dept,count(emp_no) 员工人数,avg(salary) 平均薪水 from employee group by dept; 3、select sale_id,tot_amt from dbo.sales where tot_amt>10000; 4、select prod_id,count(prod_id) 销售总量,avg(unit_price) 平均单价 from dbo.sale_item group by prod_id; 5、select sex,title,avg(salary) 平均薪水 from dbo.employee group by sex,title with cube; 6、select case when(grouping(sex)=1)then 'all' else isnull(sex,'unknown') end as sex, case when(grouping(title)=1) then 'all' else isnull(title,'unknown') end as title, avg(salary) as 平均薪水 from employee group by sex,title with rollup; 7、select distinct count(prod_id) 种类数 from sale_item; 8、select prod_id,sum(qty * unit_price) 总额 from sale_item group by prod_id order by sum(unit_price) desc; 9、select prod_id,month(order_date),sum(unit_price) from sale_item group by prod_id,month(order_date) order by prod_id,month(order_date) desc; 10

《数据库原理》实验报告 信息与电子工程学院

10、select sale_id,month(order_date), sum(tot_amt) from sales group by sale_id,month(order_date) order by sale_id,month(order_date) desc;

实验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、select a.emp_name,a.sex,a.title,a.salary,a.addr from employee a,employee b where a.dept=b.dept and a.addr=b.addr and a.sex='女'; 2、select product.prod_id,prod_name,qty,unit_price from sale_item,product where sale_item.prod_id=product.prod_id; 3、select product.prod_id,prod_name,qty,unit_price

11

《数据库原理》实验报告 信息与电子工程学院

from sale_item,product where unit_price>2400; 4、select cust_name,addr from customer where cust_id in (select cust_id from sales where tot_amt>24000); 5、select a.cust_id,cust_name,tot_amt from customer a,sales b where a.cust_id=b.cust_id; 6、select prod_id,cust_id,qty,unit_price from sale_item,sales order by prod_id,cust_id; 7、select a.cust_id,cust_name,tot_amt from customer a,sales b where a.cust_id=b.cust_id and convert(char(4),order_date,120)='1997'; 8、①左外连接 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 a.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 a.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元的员工。 12

《数据库原理》实验报告 信息与电子工程学院

5、在销售主表sales中查询销售业绩最高的业务员编号及销售业绩。 6、找出目前业绩超过232000元的员工编号和姓名。 7、查询订购的产品至少包含了订单10003中所订购产品的订单。 8、查询末承接业务的员工的信息。 三、实验结果 1、select order_no,tot_amt from sales where tot_amt=(select max(tot_amt )from sales); 2、select order_no,tot_amt,sale_id from sales where tot_amt>all (select tot_amt from sales where sale_id='e0013'and order_date='1996/10/15') order by tot_amt; 3、select emp_no from employee where sex='女'; 4、select emp_no,emp_name from employee where salary<'200000'; 5、select top 1 sale_id, tot_amt from sales; 6、select emp_no,emp_name from employee where emo_no in (select sale_id from sales group by sale_id having sun(tot_amt)>23200); 7、select distinct order_no from sale_item a where order_no<>'10003'and not exists ( select * from sale_item b where order_no ='10003' and not exists (select * from sale_item c where c.order_no=a.order_no and c.prod_id=b.prod_id)); 8、select * from employee a where not exists (select * from sales b where a.emp_no=b.sale_id) 13

《数据库原理》实验报告 信息与电子工程学院

实验8 数据更新

五、实验目的 熟练使用insert/delete/update语句进行表的更新操作。 六、实验内容 1、为各表添加若干条记录，必须符合实验二中设定的各种约束。 2、将每个员工的薪水上调10%。 3、删除sales表中作废的订单（其号码为‘i000000004’），其订货明细表中的数据也一并删除。 4、删除所有没有销售业绩的员工记录。 5、对那些只要有一笔销售业绩超过20000元的员工的薪水增加500元。 三、实验结果 1、 2、 3、 4、 5、

14