数据库原理实验指导书.doc

数据库原理实验手册实验一实验名称：熟悉SQL SERVER 2000环境一、实验目的熟悉SQL SERVER 2000提供的服务管理器、企业管理器、查询分析器、客户端和服务器端网络实用工具等常用管理工具的使用。了解Windows身份验证和SQL Server身份验证这两种连接数据库服务器方式的不同，并能够使用某种连接方式登陆上SQL SERVER数据库服务器。学会使用SQL SERVER的联机丛中寻求帮助。二、实验原理使用SQL SERVER 2000提供的各种常用管理工具进行有关的操作。三、实验设备安装有SQL SERVER 2000的计算机。四、实验内容1 启动SQL SERVER 服务。2 打开SQL SERVER的企业管理器，连接上SQL SERVER服务器。展开左边树状窗口的各级结点，观察右边内容窗口的变化。3 在SQL SERVER的企业管理器的树状窗口或内容窗口中选择某一项，点击鼠标右键，了解快捷菜单中的有关功能。4 使用Windows身份验证的连接方式打开SQL SERVER的查询分析器，在查询窗口用USE命令打开样例数据库pubs（也可以从工具栏中的数据库下拉列表中选择pubs数据库）。5 在查询窗口输入exec sp_help，运行后察看结果。6 在查询窗口输入select * from authors ，运行后察看结果。7 在SQL SERVER的查询分析器的“帮助”菜单中点击“目录与索引”，然后在SQL SERVER的联机丛中选“索引”，输入要查找的关键字“sp_help”、“exec”、“select”，了解它们的含义。8 在SQL SERVER的企业管理器中创建数据库S_T，并在该数据库中创建表student、course和sc，在创建表的同时可以创建各种约束。这三个表的结构可以参考书上85页的例5、例6和例7。9 向各个表输入数据，各个表的数据可参考书56页的图2.4。10 在SQL Server的查询分析器中打开S_T数据库，输入书中91页开始的例题，实现对数据库的查询。实验二实验名称：创建和修改数据库一、实验目的熟悉SQL SERVER的企业管理器和查询分析器的用户界面，掌握用企业管理器和查询分析器创建数据库，修改数据库和删除数据库的方法。了解数据库的三种文件类型：主数据文件（.mdf）、次要数据文件（.ndf）和事务日志文件（.ldf）。二、实验原理在企业管理器中实现对数据库的管理及使用TSQL语句CREATE DATABASE、ALTER DATABASE及DROP DATABASE来实现数据库的创建、修改及删除。三、实验设备安装有SQL SERVER 2000的计算机。四、实验示例1 创建名称为testdb的数据库，数据库中包含一个数据文件，逻辑文件名为testdb_data，磁盘文件名为testdb.mdf，文件初始容量为2MB，最大容量为8MB，文件容量递增值为1MB；事务日志文件的逻辑文件名为testdb_log，磁盘文件名为testdb_log.ldf，文件初始容量为1MB，最大容量为5MB，文件容量递增值为1MB。CREATE DATABASE testdbON( NAME=testdb.data, FILENAME=d:DATAtestdb.mdf,SIZE=5MB,MAXSIZE=8MB,FILEGROWTH=1MB )LOG ON(NAME=testdb_log, FILENAME=d:DATAtestdb_log.ldf,SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=1MB )2 对testdb数据库进行修改：添加一个数据文件，逻辑文件名为testdb2_data，磁盘文件名为testdb2_data.ndf，文件初始容量为1MB，最大容量为5MB，文件容量递增值为1MB。ALTER DATABASE testdbADD FILE (NAME=testdb2_data, FILENAME=d:DATAtestdb2.ndf,SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=1MB )五、实验内容分别使用SQL SERVER 2000企业管理器和TSQL语句，按下列要求创建、修改和删除用户数据库。1 删除数据库S_T，在查询分析器中用命令采用默认设置创建数据库S_T（只需给出数据库名）。2 创建名称为company的数据库，数据库中包含一个数据文件，逻辑文件名为company_data，磁盘文件名为company_data.mdf，文件初始容量为5MB，最大容量为15MB，文件容量递增值为1MB；事务日志文件的逻辑文件名为company_log，磁盘文件名为company_log.ldf，文件初始容量为5MB，最大容量为10MB，文件容量递增值为1MB。create database companyon(name=company.data,filename=d:DATAcompany.mdf,size=5MB,maxsize=15MB,filegrowth=1MB)log on(name=company_log,filename=d:DATAcompany.ldf,size=2MB,maxsize=5MB,filegrowth=1MB)3 对该数据库进行修改：添加一个数据文件，逻辑文件名为company2_data，磁盘文件名为company2_data.ndf，文件初始容量为1MB，最大容量为5MB，文件容量递增值为1MB；将日志文件company_log的最大容量增加为15MB，文件容量递增值为2MB。alter database companyadd file(name=company2,filename=d:DATAcompany2.ndf,size=2MB,maxsize=4MB,filegrowth=1MB)4 在company数据库中添加一个文件组TempGroup，并向该文件组中添加一个容量为3MB，最大容量为10MB，递增量为1MB的数据文件，该数据文件的逻辑文件名为company3_data，磁盘文件名为company3_data.ndf。alter database companyadd filegroup Tempgroupgoalter database companyadd file(name=company3_data,filename=d:DATAcompany3_data.ndf,size=3MB,maxsize=10MB,filegrowth=1MB)to filegroup Tempgroup;5 在company数据库中删除数据文件company2_data。use companygoalter database companyremove file company2_data;6 删除数据库company。drop database company7 采用默认设置创建数据库company。create database company实验三实验名称：创建和修改数据表一、实验目的熟悉有关数据表的创建和修改等工作，理解数据库模式的概念，了解主键约束、外键约束、UNIQUE约束和CHECK约束的创建和应用。要求学生熟练掌握使用企业管理器和TSQL语句CREATE TABLE、ALTER TABLE及DROP TABLE语句对数据表进行管理。二、实验原理在企业管理器中实现表的创建和修改等工作及使用TSQL语句CREATE TABLE、ALTER TABLE及DROP TABLE来实现数据表的创建、修改及删除。三、实验设备安装有SQL SERVER 2000的计算机。四、实验示例两个示例：l 一个是书上的学生选课数据库S_T，S_T数据库中有学生表student，课程表course和学生选课表sc。各表的结构如下：学生表studentSnochar(5)Not nullprimary key学号Snamechar(20)Not null姓名Ssexchar(2)Not null性别SagesmallintNot null年龄Sdeptchar(20)Not null系别课程表courseCnochar(4)Not nullprimary key课程号Cnamechar(40)Not null课程名Cpnochar(4)先行课Ccreditsmallint Not null学分学生选课表scSnochar(5)Not nullprimary key学号Cnochar(4)Not nullprimary key课程号Gradesmallint成绩l 另一个示例是某公司的产品销售数据库company，company数据库中存在人事表employee、客户表customer、销售表sales、销售明细表sale_item、产品表product。各表的结构如下：员工人事表employee emp_nochar(5)Not nullprimary key员工编号emp_namevarchar(10)Not null员工姓名sexchar(2)Not null性别deptvarchar(10)Not null所属部门titlevarchar(10)Not null职称date_hireddatetimeNot 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邮政编码销售主表sales order_nointNot nullprimary key订单编号cust_idchar(5)Not null客户号sale_idchar(5)Not null业务员编号tot_amtnumeric(9,2)Not null订单金额order_datedatetimeNot null订货日期销货明细表sale_item order_nointNot nullprimary key订单编号prod_idchar(5)Not nullprimary key产品编号qtyintNot null销售数量unit_pricenumeric(7,2)Not null单价order_datedatetimenull订单日期产品名称表product prod_idchar(5)Not nullprimary key产品编号prod_namevarchar(20)Not null产品名称1创建表及相应的约束create table employee1(emp_no char(5) not null check(emp_no like E-F0-90-90-90-9),emp_name char(10) not null,emp_sex char(1) not null check(emp_sex in (m,f),emp_phone char (13) not null,emp_add varchar(60) not null,emp_salary char(5) not null check (emp_salary between 60000 and 80000)go2修改表alter table employeeadd constraint ck_phone check (phone like 0-90-90-90-90-90-90-90-90-90-90-90-90-9)五、实验内容分别在SQL SERVER 2000企业管理器和在查询分析分析器中使用TSQL语句完成以下操作：l 针对S_T数据库：1 创建student表，设置Sno为主键；在Sno列上创建核查约束，要求学号各个位上只能取数字字符；在Ssex列上创建核查约束，要求性别只能取“男”或“女”；在Sage 列上创建核查约束，要求学生的年龄只能在14至38之间。create table student(sno char(5) primary key check(sno like0-90-90-90-90-9),sname char(20) not null,ssex char(2) check (ssex in(男,女),sage smallint not null check(sage between 14 and 38),sdept char(20) not null)2 创建course表，设置列Cno为主键；在Cno列上创建核查约束，要求课程号只能取数字字符。create table course(cno char(4) primary key check(cno like0-90-90-90-9),cname char(40) not null,cpno char(4),ccredit smallint not null)3 创建sc表，设置主键为Sno与Cno的组合；在Grade列上创建核查约束，限定成绩只能在0-100。create table sc(sno char(5),cno char(4),grade smallint check(grade between 0 and 100),primary key (sno,cno),/*主键由两个属性组成*/foreign key(sno) references student(sno),foreign key (cno) references course(cno),)4 修改course表，在表中为Cpno列添加一外键约束Fk_Cpno，它参照了course表的主键Cno。alter table courseadd constraint FK_cpno foreign key(cpno)references course(cno);5 修改sc表，在表中为Sno添加一外键约束Fk_Sno，它参照了student表中的主键Sno；为Cno添加外键约束Fk_Cno， 它参照了course表中的主键Cno。alter table scadd constraint FK_sno foreign key(sno)references student (sno);alter table scadd constraint FK_cno foreign key(cno) references course (cno);l 针对company数据库：1 在数据库company中创建以上五张表，并设置各表的主键。create table employee(empno char(5) not null primary key,empname varchar(10) not null,sex char(2) not null check(sex in(男,女),dept varchar(10) not null,title varchar(10) not null,datehired datetime not null,birthday datetime not null,salary int not null,telephone varchar(20) null,addr varchar(50) null)create table customer(custid char(5) not null primary key,custname varchar(20) not null,addr varchar(40) not null,telno varchar(20) not null,zip char(6) null)create table sales(orderno int not null primary key,custid char(5) not null,saleid char(5) not null,totamt numeric(9,2) not null,orderdate datetime not null)create table saleitm(orderno int not null,prodid char(5) not null,qty int not null,unitprice numeric(7,2) not null,orderdate datetime null,primary key(orderno,prodid)create table product(prodid char(5) not null primary key,prodname varchar(20) not null)2 在销售主表sales中添加字段“发票号码” invoice_no，char(10)，NOT NULL。alter table salesadd invoiceno char(10) not null;3 添加外键约束：a) 在销售主表sales的业务员编号字段sale_id上添加外键约束，参照字段为员工表employee中的字段员工编号emp_no，约束名为FK_sale_id。alter table salesadd constraint FK_sale_id foreign key(saleid) references employee(empno);b) 在销售主表sales的客户号字段cust_id上添加外键约束，参照字段为客户表customer中的字段客户号cust_id，约束名为FK_cust_id。alter table salesadd constraint FK_cust_id foreign key(custid)references customer(custid);c) 在销售明细表sale_item的订单编号字段order_no上添加外键约束，参照字段为销售主表sales中的字段订单编号order_no，约束名为FK_order_no。alter table saleitmadd constraint FK_order_no foreign key(orderno)references sales(orderno);d) 在销售明细表sale_item的产品编号字段prod_id上添加外键约束，参照字段为产品名称表product中的产品编号字段prod_id，约束名为FK_prod_id。alter table saleitmadd constraint FK_prod_id foreign key(prodid)references product(prodid);4 添加核查约束：a) 将员工表employee中的薪水字段salary的值限定在1000至10000间，约束名为CK_salary。alter table employeeadd constraint CK_salary check(salary between 1000 and 10000);b) 将员工表employee中的员工编号字段emp_no设定为以“E”字母开头, 后面跟5位数的编号，约束名为CK_emp_no。alter table employeeadd constraint CK_emp_no check(empno likeE0-90-90-90-9);c) 将员工表employee中的性别字段设定这取值只能是“男”和“女”。约束名为CK_sex。alter table employeeadd constraint CK_sex check(sex in(男,女);d) 将销售主表sales中的发票号码字段invoice_no设定为以“I”字母开头，后面跟9位数的编号，约束名为CK_inno。alter table salesadd constraint CK_inno check(invoiceno likeI0-90-90-90-90-90-90-90-90-9);5 为销售主表sales中的字段发票号码invoice_no设置为唯一约束，约束名为UN_inno。alter table salesadd constraint UN_inno unique(invoiceno);或：alter table sales add unique(invoiceno);实验四实验名称：简单的单表查询一、实验目的熟练掌握用SELECT语句实现简单的单表查询。掌握SELECT子句、FROM子句、WHERE子句及ORDER BY 子句的用法。二、实验原理用SELECT语句实现简单的单表查询。在SELECT子句中用TOP关键字来限制返回到结果集中的记录数目，用DISTINCT关键字从结果集中去掉重复的记录。WHERE子句中用关系比较符、NOT BETWEEN、NOT IN、LIKE、 IS NOTNULL及逻辑运算符构成查询条件，对结果集中的记录进行筛选。ORDER BY子句将根据查询结果集中一个或多个字段对查询结果进行排序。三、实验设备安装有SQL SERVER 2000的计算机。四、实验示例1查找表中所有姓刘的职工的工号，姓名，部门，薪水select emp_no,emp_name,dept,salaryfrom employeewhere emp_name like 刘%2查找所有定单金额高于20000的所有客户编号select cust_idfrom saleswhere tot_amt200003查找业务部或会计部的女员工的基本信息。select emp_no,emp_name,deptfrom employee where sex=女 and dept in (业务,会计)4选取编号界于C0001和C0004的客户编号、客户名称、客户地址。select cust_id,cust_name,addrfrom customer where cust_id between C0001 AND C00045选取销售数量最多的前5条订单订单号、数量。select top 5 order_no,qtyfrom sale_itemorder by qty desc6计算出sale_item表中每一笔销售数据的销售金额，并按照销售金额的大小排序。select prod_id,qty,unit_price,(qty*unit_price)tot_amtfrom sale_itemorder by tot_amt desc五、实验内容运行查询文件company.sql，生成上机必要的数据，然后完成以下操作。1、 查找所有经理的姓名、职称、薪水。select empname,title,salaryfrom employeewhere title=经理;2、 在销售主表sales中查找销售金额大于等于90400元的订单。select *from saleswhere totamt=90400;3、 在员工表employee中查找薪水在4000至8000元之间的员工。select *from employeewhere salary between 4000 and 8000;4、 在员工表employee中查找住址为上海、北京、天津这三个城市的员工。select *from employeewhere addr=上海or addr=北京 or addr=天津;或select *from employeewhere addr in(上海,北京 ,天津);5、在客户表customer中查找住址不在上海、北京、天津这三个城市的客户。select *from employeewhere addr!=上海and addr!=北京 and addr!=天津;或select *from employeewhere addr not in(上海,北京 ,天津);6、在员工表employee中查找姓“郭”用姓名最后一个字为“亮”的员工。select *from employeewhere empname like 郭%亮;7、在客户表customer中查找姓“刘”的客户名称、电话。select custname,telnofrom customerwhere custname like 郭%;8、查找出职称为“经理”或“职员”的女工的信息。select *from employeewhere title in (经理,职工)；9、查找薪水最高的前三条员工记录。select top 3 *from employeeorder by salary desc;10、查找订单金额最高的前10%的订单记录。select top 10 percent *from salesorder by tot_amt desc11、查找员工表中所属部门。（去掉重复记录）select count (distinct dept)from employee12、查找员工表中的所有记录，并按薪水由低到高进行排序。select *from employeeorder by salary desc实验五实验名称：复杂的单表查询 一、实验目的熟练掌握SELECT查询语句中的Group by 子句、Having子句的用法，以及汇总函数的使用。二、实验原理在查询语句中用Group by子句进行分组；用Having子句对分组进行筛选。使用MAX（），MIN（），COUNT（），SUM（），AVG（）等函数在查询结果集中生成汇总值。三、实验设备安装有SQL SERVER 2000的计算机。四、实验示例1、统计表中员工的薪水在40000-60000之间的人数select count(*) as 人数from employeewhere salary between 40000 and 600002、计算P0001产品的平均销售单价select avg(unit_price)from sale_itemwhere prod_id=P00013、 找出目前销售业绩超过40000元的业务员编号及销售业绩，并按销售业绩从大到小排序。Select sale_id ,sum(tot_amt)from sales group by sale_id having sum(tot_amt)40000order by sum(tot_amt) desc4、 查询订购了三种以上产品的订单号。Select order_no from sale_itemGroup by order_noHaving count(*)3五、实验内容1、在员工表employee中统计员工人数。select count(*) from employee;2、统计各部门员工的员工人数及平均薪水。select count(empno),avg(salary)from employeegroup by dept;3、查询销售业绩超过10000元的员工编号。select saleidfrom saleswhere totamt=10000;4、计算每一产品销售数量总和与平均销售单价。select count(qty) 产品销售数量总和,avg(unitprice) 平均销售单价from saleitmgroup by prodid;5、 统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水。（在GROUP BY 子句中使用CUBE关键字）select case when(grouping(sex)=1)then allelse isnull(sex,unknown)end as sex,case when(grouping(title)=1) then allelse isnull(title,unknown)end as title,avg(salary) as 平均薪水from employeegroup by sex,title with cube6、 统计各部门不同性别、或各部门或所有员工的平均薪水。（在GROUP BY 子句中使用ROLLUP关键字）select case when(grouping(sex)=1)then allelse isnull(sex,unknown)end as sex,case when(grouping(title)=1) then allelse isnull(title,unknown)end as title,avg(salary) as 平均薪水from employeegroup by sex,title with rollup;7、 计算出一共销售了几种产品。select count(distinct prodid) 销售产品数量from saleitm;8、 显示sale_item表中每种产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。select prodid,count(distinct prodid) as 订单数量,sum(qty*unitprice) as 销售总额from saleitmgroup by prodidorder by sum(qty*unitprice) desc;9、 计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。select prodid,month(orderdate)as 年月,sum(qty*unitprice)as销售金额from saleitmgroup by month(orderdate),prodidorder by month(orderdate),prodid desc;10、 查询每位业务员各个月的业绩，并按业务员编号、月份降序排序。select saleid,month(orderdate) 年月,sum(totamt) 业绩from salesgroup by saleid,month(orderdate)order by saleid,month(orderdate) desc;实验六实验名称：连接查询一、实验目的掌握使用连接的方法从多个表中查询数据。理解内连接、外连接（包括左外连接、右外连接和全外连接）、自身连接的概念和使用。要求学生熟练掌握在FROM子句和在WHERE子句中指定连接条件的这两种方法。二、实验原理在查询语句的FROM子句中用以下形式实现各种连接操作：l FROM 表1 INNER JOIN 表2 ON 表1列名=表2列名 （实现内连接）l FROM 表1 LEFT OUTER JOIN 表2 ON 表1列名=表2列名 （实现左外连接）l FROM 表1 RIGHT OUTER JOIN 表2 ON 表1列名=表2列名 （实现右外连接）l FROM 表1 FULL OUTER JOIN 表2 ON 表1列名=表2列名 （实现全外连接）l FROM 表1 AS 别名1 JOIN 表1 AS 别名2 ON 别名1列名=别名2列名 （实现自身连接）在查询语句的WHERE子句中用以下形式实现各种连接操作：l FROM 表1，表2 WHERE 表1列名=表2列名 （实现内连接）l FROM 表1，表2 WHERE 表1列名*=表2列名 （实现左外连接）l FROM 表1，表2 WHERE 表1列名=*表2列名 （实现右外连接）l FROM 表1 AS 别名1 ，表1 AS 别名2 WHERE 别名1列名=别名2列名 （实现自身连接）三、实验设备安装有SQL SERVER 2000的计算机。四、实验示例1、 检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、单价。select d_id,a.qty,a.unit_price,d_namefrom sale_item as a inner join product as b /*如果改成left join/right join 试分析结果*/on (d_id=_id) and a.qty2order by d_id2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addrfrom employee as a inner join employee as bon (a.emp_no!=b.emp_no) and (a.emp_nameb.emp_name) and (a.addr=b.addr)3、查找商品名称为14寸显示器商品的销售情况，显示该商品的编号、销售数量、单价和金额select d_id,qty,unit_price,unit_price*qty totpricefrom sale_item a,product bwhere d_id=d_id and prod_name=14寸显示器四、实验内容与步骤1、 查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。select a1.empname,a1.sex,a1.title,a1.salary,a1.addrfrom employee as a1,employee as a2where a1.dept=a2.dept and a1.addr=a2.addr and a1.sex=女 and a1.empno=a2.empno;2、 检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。select did,prodname,qty,unitpricefrom product,saleitmwhere did=did;3、 检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。select did,prodname,qty,unitpricefrom product,saleitmwhere did=did and saleitm.unitprice=24;4、 查询在每张订单中订购金额超过24000元的客户名及其地址。select custname,addrfrom customer,saleswhere customer.custid=sales.custid and totamt=90250;5、 查找有销售记录的客户编号、名称和订单总额select sales.custid,custname,totamtfrom customer,saleswhere customer.custid=sales.custid;6、 每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列。select custid,prodid,sum(qty),avg(unitprice)from saleitm,saleswhere sales.orderno=saleitm.ordernogroup by custid,prodidorder by custid,prodid;7、 查找在1997年中有销售记录的客户编号、名称和订单总额select customer.custid,custname,sum(totamt)from sales,customerwhere sales.custid=customer.custid and convert(char(4),sales.orderdate,120)=1997group by customer.custid,custname;8、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。 select did,prodname,qty,unitpricefrom saleitm left outer join product on (did=did)where saleitm.unitprice24;select did,prodname,qty,unitpricefrom saleitm right outer join product on (did=did)where saleitm.unitprice24;select did,prodname,qty,unitpricefrom saleitm full outer join product on (did=did)where saleitm.unitprice24;实验七实验名称：嵌套查询一、实验目的掌握SELECT语句的嵌套使用，实现多表的复杂查询，进一步理解SELECT语句的高级使用方法。二、实验原理使用嵌套查询时，先用内查询（子查询）挑选出部分数据，以作为外查询（主查询）的数据来源或搜索条件。包含子查询的语句通常采用以下格式：WHERE 表达式 NOT IN （子查询）WHERE 表达式 比较运算符 ANY|ALL （子查询）WHERE NOT EXISTS （子查询）其中前两种又称为不相关子查询，子查询的查询