数据库系统原理实验报告

1、数据库系统原理实验报告班级_姓名_学号_信息与电子工程学院实验1 sql server 熟悉和数据库创建1、 实验目的熟悉sql server 2005提供的服务管理器、企业管理器、查询分析器、客户端和服务器端网络实用工具等常用管理工具的使用。理解客户/服务器模式，理解面向连接与非面向连接的差别。理解交互式sql的工作机制。能够理解命名管道协议与tcp/ip协议的差别。能够登陆上sql server数据库服务器。2、 实验内容1、启动sql server 服务。2、打开sql server的企业管理器，连接上sql server服务器。展开左边树状窗口的各级结点，观察右边内容窗口的变化。3、打

2、开sql server的查询分析器，用use命令打开样例数据库pubs。4、在查询窗口输入exec sp_help，运行后察看结果。5、在查询窗口输入select * from authors ，运行后察看结果。三、实验结果3、当不确定当前所操作的是哪个数据库，可使用use来定位到某数据库。4、5、 查询某张表的所有列。实验2 简单查询1、 实验目的： 熟悉sql server的企业管理器和查询分析器的用户界面，掌握用企业管理器和查询分析器创建数据库，修改数据库和删除数据库的方法。2、 实验内容分别使用sql server 2005企业管理器和tsql语句，按下列要求创建、修改和删除用户数据库

3、。 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_

4、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 companyon ( name=company_data, filename=d:microsoft

5、 sql servermssql10_50.sqlexpressmssqldatacompany_data.mdf,size=5mb,maxsize=15mb,filegrowth=1mb )log on (name=company_log, filename=d:microsoft sql servermssql10_50.sqlexpressmssqldatacompany_log.ldf,size=5mb,maxsize=10mb,filegrowth=1mb );2、alter database companyadd file (name=company2_data,filename=

6、d:microsoft sql servermssql10_50.sqlexpressmssqldatacompany2_data.mdf,size=1mb,maxsize=5mb,filegrowth=1mb)alter database companymodify file (name=company_log,maxsize=15mb,filegrowth=2mb);3、alter database companyadd filegroup tempgroupalter database companyadd file(name=company3_data, filename=d:micr

7、osoft sql servermssql10_50.sqlexpressmssqldatacompany3_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 创建和修改数据表一、实验目的： 熟悉有关数据表的创建和修改等工作，理解数据库模式的概念，了解主键约束、外键约束、unique约束和c

8、heck约束的创建和应用。要求学生熟练掌握使用企业管理器和tsql语句create table、alter table及drop table语句对数据表进行管理。2、 实验内容分别在sql server 2005企业管理器和在查询分析分析器中使用tsql语句完成以下操作：员工人事表employeeemp_nochar(5)not nullprimary key员工编号emp_namevarchar(10)not null员工姓名sexchar(2)not null性别deptvarchar(10)not null所属部门titlevarchar(10)not null职称date_hiredd

9、atetimenot null雇佣日birthdaydatetimenull生日salaryintnot null薪水telephonevarchar(20)null电话addrvarchar(50)null住址客户表customercust_idchar(5)not nullprimary key客户号cust_namevarchar(20)not null客户名称addrvarchar(40)not null客户住址tel_novarchar(20)not null客户电话zipchar(6)null邮政编码销售主表salesorder_nointnot nullprimary key订单

10、编号cust_idchar(5)not null客户号sale_idchar(5)not null业务员编号tot_amtnumeric(9,2)not null订单金额order_datedatetimenot null订货日期销货明细表sale_itemorder_nointnot nullprimary key订单编号prod_idchar(5)not nullprimary key产品编号qtyintnot null销售数量unit_pricenumeric(7,2)not null单价order_datedatetimenull订单日期产品名称表productprod_idchar(

11、5)not nullprimary key产品编号prod_namevarchar(20)not null产品名称 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，

12、约束名为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”字母开头,

13、 后面跟5位数的编号，约束名为ck_emp_no。c. 将员工表employee中的性别字段设定这取值只能是“男”和“女”。约束名为ck_sex。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,sexchar(2)not nu

14、ll,dept varchar(10) not null,title varchar(10) not null,date_hired datetime not null,birthday datetime null,salary intnot 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_nov

15、archar(20)not null,zipchar(6)null);create table sales(order_no intnot null primary key,cust_idchar(5)not null,sale_idchar(5)not null,tot_amtnumeric(9,2) not null,order_date datetime not null);create table sale_item(order_no int not null,prod_idchar(5)not null,qtyintnot null,unit_price numeric(7,2) n

16、ot 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 salesadd constraint fk_sale_id foreign key (sale_id) references employee(emp

17、_no);b、alter table salesadd constraint fk_cust_idforeign key (cust_id) references customer(cust_id);c、alter table sale_itemadd constraint fk_order_noforeign key (order_no) references sales(order_no);d、alter table sale_itemadd constraint fk_prod_id foreign key (prod_id) references product(prod_id);4、

18、a、alter table employeeadd constraint ck_salary check (salary between 1000 and 10000);b、alter table employee add constraint ck_emp_no check(emp_no like e0-90-90-90-90-9);c、alter table employeeadd constraint ck_sex check(sex like(男女);d、alter table sales add constraint ck_inno check(invoice_no like i0-

19、90-90-90-90-90-90-90-90-9);5、alter table sales add constraint un_inno unique(invoice_no);实验4 简单的单表查询一、实验目的： 熟练掌握用select语句实现简单的单表查询。掌握select子句、from子句、where子句及order by 子句的用法。2、 实验内容运行查询文件company.sql，生成上机必要的数据，然后完成以下操作。 1、查找所有经理的姓名、职称、薪水。 2、在销售主表sales中查找销售金额大于等于10000元的订单。 3、在员工表employee中查找薪水在4000至8000元

20、之间的员工。 4、在员工表employee中查找住址为上海、北京、天津这三个城市的员工。5、 在客户表customer中查找住址不在上海、北京、天津这三个城市的客户。6、 在员工表employee中查找姓“王”用姓名最后一个字为“功”的员工。7、 在客户表customer中查找姓“刘”的客户名称、电话。8、 查找出职称为“经理”或“职员”的女工的信息。9、 查找薪水最高的前三条员工记录。10、 查找订单金额最高的前10%的订单记录。11、 查找员工表中所属部门（去掉重复记录）。12、 查找员工表中的所有记录，并按薪水由低到高进行排序。三、实验结果1、select emp_name,title,

21、salary from employee where title =经理;2、select * from sales where tot_amt10000;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 cu

22、st_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 sala

23、ry;实验5 复杂的单表查询一、实验目的熟练掌握select查询语句中的group by 子句、having子句的用法，以及汇总函数的使用。二、实验内容1、在员工表employee中统计员工人数。2、统计各部门员工的员工人数及平均薪水。3、查询销售业绩超过10000元的员工编号。4、计算每一产品销售数量总和与平均销售单价。5、统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水（在group by 子句中使用cube关键字）。6、统计各部门不同性别、或各部门或所有员工的平均薪水（在group by 子句中使用rollup关键字）。7、计算出一共销售了几种产品。8、显示sale_item

24、表中每种产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。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_amt10000;4、sel

25、ect 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 allelse isnull(sex,unknown)end as sex,case when(grouping(title)=1) then allelse isnull(t

26、itle,unknown)end as title,avg(salary) as 平均薪水from employeegroup by sex,title with rollup;7、select distinct count(prod_id) 种类数 from sale_item;8、select prod_id,sum(qty * unit_price) 总额from sale_itemgroup by prod_idorder by sum(unit_price) desc;9、 select prod_id,month(order_date),sum(unit_price)from sa

27、le_itemgroup by prod_id,month(order_date)order by prod_id,month(order_date) desc;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 连接查询一、实验目的掌握使用连接的方法从多个表中查询数据。理解内连接、外连接（包括左外连接、右外连接和全外连接）、自身连接的概念和使用。要求学生熟练掌握在fr

28、om子句和在where子句中指定连接条件的这两种方法。二、实验内容1、查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。2、检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。3、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。4、查询在每张订单中订购金额超过24000元的客户名及其地址。5、查找有销售记录的客户编号、名称和订单总额。6、每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列。7、查找在1997年中有销售记录的客户编号、名称和订单总

29、额。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 d_id,prod_name,qty,unit_pricefrom sale_item,product whe

30、re sale_d_id=d_id;3、 select d_id,prod_name,qty,unit_price from sale_item,product where unit_price2400;4、 select cust_name,addr from customer where cust_id in (select cust_id from sales where tot_amt24000);5、select a.cust_id,cust_name,tot_amt from customer a,sales b wher

31、e 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_amtfrom customer a,sales b where a.cust_id=b.cust_id and convert(char(4),order_date,120)=1997;8、 左外连接 select d_id,d_name,b.qty,b.unit_price from prod

32、uct a left outer join sale_item b on(d_id=d_id) and b.unit_price2400; 右外连接 select d_id,d_name,b.qty,b.unit_price from product a right outer join sale_item b on d_id=d_id and b.unit_price2400; 完整外部连接 select d_id,d_name,b.qty,b.unit_price from product a full join sale_item b on(d_id=d_id)and b.unit_price2400;实验7 嵌套查询3、 实验目的掌握select语句的嵌套使用，实现多表的复杂查询，进一步理解select语句的高级使用方法。4、 实验内容1、由sales表中查找出销售金额最高的订单。2、由sales表中查找