实验教学讲稿
2013～2014学年第一学期
分院(系、部)：信息工程学院
教研室：网络工程
课程名称：数据库原理
授课班级：网络工程2011级本科班主讲教师：李娜
职称：讲师
使用教材：《数据库系统概论》
邯郸学院制
实验1 认识DBMS系统、数据库及数据库表的建立实验
一、实验目的
本实验的目的是使学生熟悉SQL Server Management Studio的使用方
法，熟悉SQL SERVER的常用数据类型，加深对SQL语言的数据定义语句的理解。熟练掌握数据库及数据库表的创建、修改和删除。
二、实验时数：2学时
三、实验内容
分别使用SQL语言和对象资源管理器完成以下操作：
1. 创建数据库
创建数据库名为COMPANY1 ，主数据文件的逻辑名称为COMPANY_DATA,操作系统文件的名称为D:\MSSQL\DATA\COMPANY.MDF,大小为20MB,最大为50MB,以25%的速度增长。日志文件的逻辑名称为COMPANY_LOG,操作系统文件的名称为D:\MSSQL\DATA\COMPANY.LDF,大小为3MB,最大为10MB,以1MB的速度增长。
Create database company1
On primary
(name=company1_data,
Filename=’D:\MSSQL\DATA\COMPANY.MDF’,
Size=20mb,
Maxsize=50mb,
Filegrowth=25%)
Log on
(name=company_log,
Filename=’D:\MSSQL\DATA\COMPANY.LDF’,
Size=3mb,
Maxsize=10mb,
Filegrowth=1mb)
2. 修改数据库
(1)将数据库COMPANY1的主数据文件的初始分配空间大小扩充到40MB. use company1
go
alter database company1
modify file
(name='D:\MSSQL\DATA\COMPANY.MDF',
size=40mb)
(2) 将数据库COMPANY1改名为COMPANY
Exec sp_renamedb’company1’,’company’
3. 创建表
在名为COMPANY数据库中创建以下四个表：
(1)部门表，表名DEPA
列名数据类型描述
DNO DECIMAL(2) 部门编号
DNAME VARCHAR(16) 部门名称
ADDR VARCHAR(20) 部门地址
USE COMPANY
GO
CREATE TABLE DEPA
(DNO DECIMAL(2),
DNAME VARCHAR(16),
ADDR VARCHAR(20)
GO)
(2)雇员表，表名EMPL
列名数据类型描述
ENO DECIMAL(4) 员工编号
ENAME CHAR(8) 员工姓名
BIRTHDATE DATETIME 出生日期
SALARY DECIMAL(7,2) 工资
DNO DECIMAL(2) 所在部门编号
USE COMPANY
CREATE TABLE EMPL
(ENO DECIMAL(4),
ENAME CHAR(8),
BIRTHDATE DATETIME,
SALARY DECIMAL(7,2),
DNO DECIMAL(2)
)
(3)项目表，表名PROJ
列名数据类型描述PNO DECIMAL(3) 项目编号PNAME VARCHAR(24) 项目名称USE COMPANY
CREATE TABLE PROJ
(PNO DECIMAL(3),
PNAME VARCHAR(24)
)
(4)工作表，表名JOB
列名数据类型描述ENO DECIMAL(4) 员工编号PNO DECIMAL(3) 项目编号DAYS INT 工作天数
USE COMPANY
CREATE TABLE JOB
(ENO DECIMAL(4),
PNO DECIMAL(3),
DAYS INT
)
4. 修改表结构
1）修改部门表DEPA，添加一列部门电话，列名Telephone，数据类型VARCHAR(15)。use company
go
alter table depa
add Telephone varchar(15)null
go
2）为项目表PROJ添加Begindate和Enddate列，数据类型为DATETIME。
use company
go
alter table proj
add begindate datetime null
Add enddate datetime null
go
3）删除项目表PROJ中的Enddate列。
use company
go
alter table proj
drop column enddate
go
5. 删除表
1）删除项目表PROJ。
drop table proj
6、添加记录：
1）向DEPA表添加14号部门，客户中心，地址为开发区紫光路2号。
use company
insert into depa
values('14','客户中心',‘开发区紫光路2号’，,null)
go
2）向DEPA表添加 15号部门，技术支持部，地址未详。
use company
insert into depa
values('15',技术支持部',null,null)
go
3）向DEPA表添加 13号部门，财务部，地址未详。
use company
insert into depa
values('13','财务部',null,null)
go
……
4）向EMPL表添加1401号员工，张山，出生日期1977年9月1日，工资4050元，14号部门。
use company
insert into empl
values('1401','张山','1977-9-1','4050','14')
go
5）向EMPL表添加1402号员工，何宜，生日不祥，工资不祥，15号部门。……
use company
insert into empl
values('1402','何宜',null,null,'15')
go
6）向PROJ表添加103号项目，历史学院档案库管理系统。
use company
insert into proj
values('103','历史学院档案库管理系统')
go
7）向PROJ表添加104号项目，ATM机管理系统。
use company
insert into proj
values('104','ATM机管理系统')
go
8）向JOB表添加1401号员工，参与了104号项目35天。
use company
insert into job
values('1401','104',‘35')
go
9）向JOB表添加1402号员工，参与了103号项目20天。
use company
insert into job
values('1402','103','20')
go
7、修改记录：
1）把DEPA表中’客户中心’的地址改为’大连甘井子区红岭路’;
use company
update depa
set ADDR='大连甘井子区红岭路'
where DNO=14
2）把DEPA表中15号部门的地址用沈阳东郊路120号填充。
use company
update depa
set ADDR=沈阳东郊路120号'
where DNO=15
3）把EMPL表中何宜的生日、工资用1981年12月20日，3000元填充。use company
update empl
set BIRTHDATE='1981-12-20',SALARY='3000'
where ENAME='何宜'
GO
4）把EMPL表中张山的部门号改为15号。
use company
update empl
set DNO='15'
where ENAME='张山'
GO
5）把JOB表中参与104号项目的每人增加5天。
use company
update job
set DAYS=DAYS+5
where PNO='104'
GO
6）把JOB表中参与103号项目的每人天数乘以系数0.8。
use company
update job
set DAYS=DAYS*0.8
where PNO='103'
GO
8、删除记录：
1）删除地址为空的部门。
delete from depa where ADDR=null
2）删除JOB表中参与104号项目的员工。
delete from job where PNO='104'
3）删除JOB表中天数低于25天的员工。
delete from job where DAYS<25
4）删除生于1980年后，且工资低于4000的员工。
5）删除属于14号部门的员工。
delete from empl where DNO=14
6）删除15号部门的信息
。delete from empl where DNO=15
9．使用对象资源管理器分离和附加数据库COMPANY
实验2 数据库的单表查询、分组查询与聚集函数的使用
一、实验目的
本实验的目的是使学生熟悉SQL Server Management Studio的使用方法。加深对SQL语言的查询语句的理解。熟练掌握数据库的单表查询（包括投影、选择条件表达，数据排序，模糊查询等）, 熟练掌握数据库的分组、统计、计算和集合的操作方法。
二、实验时数2学时
三、实验内容
1.简单的SELECT查询
1）查询雇员表Empl中所有员工的详细信息，并且列名用汉字表示。
select ENO AS员工编号,ENAME AS员工姓名,BIRTHDATE AS生日,SALARY AS薪资, DNO AS部门编号
from empl
2）查询项目表Proj中所有项目的详细信息，并且列名用汉字表示。
select PNAME AS项目名称, PNO AS项目编号
from proj
2.使用WHERE子句进行查询
1）查询雇员表Empl中李强的员工编号。
use COMPANY
select ENO
from EMPL
where ENAME='李强'
2）查询项目表Proj中项目编号为“101”的项目名称。
use COMPANY
select PNAME
from PROJ
where PNO='101'
3.使用DISTINCT关键字去掉重复行
1）查询雇员表Empl中出现的所有部门编号，要求无重复。
use COMPANY
select distinct DNO
from EMPL
2）查询工作表Job中参与过项目开发的所有员工编号，要求无重复。
use COMPANY
select distinct ENO
from JOB
where PNO is not null
4.使用ORDER BY子句对查询结果进行排序
1）查询雇员表Empl中所有员工的详细信息，结果按员工姓名降序排列。
use COMPANY
select ENO AS员工编号,ENAME AS员工姓名, BIRTHDATE AS生日,SALARY AS薪资
from EMPL
order by ENAME desc
2）查询工作表Job中的工作信息，结果按工作天数升序排列。
use COMPANY
select ENO AS员工编号,PNO AS项目名称, DAYS as工作天数
from JOB
order by DAYS ASC
5.使用LIKE子句进行模糊查询
1）查询所有姓王的员工的编号及姓名，并且以员工编号及员工姓名作为列名。use COMPANY
select ENO AS员工编号, ENAME AS员工姓名
from EMPL
where ENAME like'王_'
2）查询雇员表Empl中姓名以“伟”字结尾的员工信息。
use COMPANY
select ENO AS员工编号,ENAME AS员工姓名, BIRTHDATE AS生日,SALARY AS薪资
from EMPL
where ENAME like'%伟'
6.使用聚集函数MIN、MAX进行查询
1）查询雇员表Empl中员工的最低工资，并且列名用“最低工资”表示。
use COMPANY
select min(SALARY)AS最低工资
from EMPL
2）查询雇员表Empl中员工的最高工资，并且列名用“最高工资”表示。
use COMPANY
select max(SALARY)AS最高工资
from EMPL
7.使用聚集函数COUNT进行查询
1）查询雇员表Empl中所有员工的总人数，并且列名用“员工总数”表示。use COMPANY
select count(ENAME)AS员工总人数
from EMPL
2）查询14号部门的人数，并且列名用“客户中心部人数”表示。
use COMPANY
select count(DNO)AS客户中心部人数
from EMPL
where DNO=14
8.使用分组子句GROUP BY与聚集函数MIN、MAX进行查询
1）查询每个部门中年龄最大的员工，显示部门编号和出生日期。
use COMPANY
select min(BIRTHDATE)as出生日期, DNO as部门编号
from EMPL
group by DNO
2）分别统计雇员表Empl中各部门员工的最高工资，并且列名分别用“部门编号”及“最高工资”表示。
use COMPANY
select max(SALARY)as最高工资, DNO as部门编号
from EMPL
group by DNO
9.使用分组子句GROUP BY与聚集函数SUM、AVG进行查询
1）分别统计雇员表Empl中各部门员工的工资总额，并且列名分别用“部门编号”及“各部门工资总额”表示。
use COMPANY
select sum(SALARY)as各部门工资总额, DNO as部门编号
from EMPL
group by DNO
2）分别统计雇员表Empl中各部门员工工资的平均数，并且列名分别用“部门编号”及“各部门工资平均数”表示。
use COMPANY
select avg(SALARY)as各部门工资平均数, DNO as部门编号
from EMPL
group by DNO
10.使用分组子句GROUP BY与聚集函数COUNT进行查询
1）统计员工人数多于4人的部门信息，并且列名分别用“部门编号”及“员工人数”表示。
use COMPANY
select count(ENAME)as员工人数, DNO as部门编号
from EMPL
group by DNO
having count(ENAME)>4
2）分别统计工作表Job中各项目的参与开发的员工人数，并且列名分别用“项目编号”及“员工人数”表示。
use COMPANY
select PNO as项目编号,count(PNO)as员工人数
from JOB
group by PNO
Having PNO is not null
实验3 数据库的多表连接和子查询
一、实验目的
本实验的目的是使学生加深对SQL和SQL语言的查询语句的理解。熟练掌握数据库的多表连接查询和子查询。
二、实验时数2学时
三、实验内容
㈠多表连接查询：
1.对两张数据表使用内连接进行查询
1）查询姓名为“王一伟”的员工所在部门的名称，并且列名用汉字表示。
use company
select DEPA.DNAME as部门名称,EMPL.ENAME as姓名
from EMPL inner join DEPA
on DEPA.DNO=EMPL.DNO
where EMPL.ENAME='王一伟'
2）查询编号为“1002”的员工姓名及该员工所在部门的名称，并且列名用汉字表示。use company
select EMPL.ENO as部门编号,DEPA.DNAME as部门名称,EMPL.ENAME as姓名
from EMPL inner join DEPA
on DEPA.DNO=EMPL.DNO
where EMPL.ENo='1202'
2.对多张数据表使用内连接进行查询
1）查询姓名为“陈晨”的员工参与过的所有项目的名称，并且列名用汉字表示。use company
select EMPL.ENAME as姓名,EMPL.ENO as员工编号,JOB.PNO as项目名称
from EMPL inner join JOB
on EMPL.ENO=JOB.ENO
where EMPL.ENAME='李晨'
2）查询编号为“1202”的员工姓名及该员工参与过的所有项目的名称，并且列名用汉字表示。
use company
select EMPL.ENAME as姓名,EMPL.ENO as员工编号,JOB.PNO as项目名称
from EMPL inner join JOB
on EMPL.ENO=JOB.ENO
where EMPL.ENO='1202'
3.对两张数据表使用左连接进行查询
1）使用左连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该员工所参与的所有项目的编号，否则该项以空值表示。use company
select EMPL.ENO as员工编号,EMPL.ENAME as姓名,EMPL.BIRTHDATE as出生日期,EMPL.SALARY as工资,Empl.DNO as部门编号,JOB.PNO as项目编号
from EMPL left outer join JOB
on EMPL.ENO=JOB.ENO
2）使用左连接查询所有员工的基本信息及所在部门名称，如果某员工尚未分配到任何部门，则该员工的部门名称项以空值表示。
use company
select EMPL.ENO as员工编号,EMPL.ENAME as姓名,EMPL.BIRTHDATE as出生日期,EMPL.SALARY as工资,Empl.DNO as部门编号,DEPA.DNAME as部门名称
from EMPL left outer join DEPA
on EMPL.DNO=DEPA.DNO
4.对两张数据表使用右连接进行查询
1）使用右连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该员工所参与的所有项目的编号，否则该项以空值表示。use company
select EMPL.ENO as员工编号,EMPL.ENAME as姓名,EMPL.BIRTHDATE as出生日期,EMPL.SALARY as工资,Empl.DNO as部门编号,JOB.PNO as项目编号
from EMPL RIGHT outer join JOB
on EMPL.ENO=job.ENO
2）使用右连接查询所有员所在部门名称，如果某员工尚未分配到任何部门，则该员工的部门名称项以空值表示。
use company
select EMPL.ENO as员工编号,EMPL.ENAME as姓名,EMPL.BIRTHDATE as出生日期,EMPL.SALARY as工资,Empl.DNO as部门编号,DEPA.DNAME AS部门名称
from EMPL right outer join DEPA
on EMPL.DNO=DEPA.DNO
㈡子查询：
1.多表单值子查询
1)查询编号为1001的员工的部门名。
use company
select DNAME
from DEPA
where DNO=(select DNO
from EMPL
where ENO='1001')
2)查询‘客户中心’部门的员工数。
use company
select count(DNO)as员工数
from EMPL
where DNO=(select DNO
from DEPA
where DNAME='客户中心')
3)查询陈晨所在的项目编号。
use COMPANY
select PNO as项目编号
from JOB
where ENO=(select ENO
from EMPL
where ENAME='李晨')
2.多表多值子查询
1)查询1985年以后出生的员工在所在项目的工作天数。use COMPANY
select DAYS as工作天数
from JOB
where ENO in (select ENO
from EMPL
where BIRTHDATE>'1985')
2)查询参与项目天数超过40天的员工姓名。
use COMPANY
select ENAME as员工姓名
from EMPL
where ENO in(select ENO
from JOB
where DAYS>'40')
3)查询工资低于2000的员工的所属部门。
use COMPANY
SELECT DNAME AS部门
from DEPA
where DNO in(select DNO
from EMPL
where SALARY<'2000')
3.多层嵌套子查询
1)查询‘客户中心'部门中，收入高于平均工资的员工。
select empl.ename as工资高于平均工资的员工
from empl
where salary >(select avg(empl.salary)
from empl
)
and
empl.dno in(select depa.dno
from depa
where depa.dname='客户中心') 2)查询“开发系统”项目的参与部门。
SELECT T1.DNAME
FROM PROJ S1,JOB S2,EMPL S3,DEPA T1
WHERE T1.DNO = S3.DNO
AND S3.ENO = S2.ENO
AND S2.PNO = S1.PNO
AND S1.PNAME = '开发系统'
3)查询'宣传'部门雇员参加的项目名称。
SELECT T1.PNAME
FROM PROJ T1,JOB S1,EMPL S2,DEPA S3
WHERE T1.PNO = S1.PNO
AND S1.ENO = S2.ENO
AND S2.DNO = S3.PNO
AND S3.PNAME = '宣传'
4．相关子查询
1)查询参与两个项目以上的员工姓名。SELECT T1.ENO,T2.ENAME
FROM (
SELECT ENO,COUNT(PNO) COUNT
FROM JOB
GROUP BY ENO
HAVING COUNT >=2 )T1,EMPL T2
WHERE T1.ENO = T2.ENO
2)查询所有未参与项目的员工姓名。
SELECT T1.ENAME
FROM EMPL T1
WHERE T1.ENO NOT IN(SELECT ENO FROM JOB)
实验4 数据库的视图与索引定义
一、实验目的
本实验的目的是使学生掌握索引与视图的定义与维护操作，加深对索引与视图在关系数据库中的作用的理解。
二、实验时数2学时
[相关知识]
建立索引是加快查询速度的有效手段。用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多个存取路径，加快查找速度。
视图是根据子模式建立的虚拟表。一个视图可以由一个表构造，也可以由多个表构造。查看和修改视图就如同对表的操作一样，非常容易。
三、实验内容
㈠索引
1.创建索引
1）为PROJ表的PNAME列创建非聚集索引Pindex。
use COMPANY
go
create nonclustered index Pindex on PROJ(PNAME)
go
2）为DEPA表的DNAME（降序），ADDR两列创建唯一非聚集索引Dindex。
use COMPANY
go
create unique nonclustered index Dindex on DEPA(DNAME desc,ADDR)
go
3）在PROJ表的PNAME列上创建唯一非聚集索引Pindex，若该索引已存在则删除后重建。
use COMPANY
go
create unique nonclustered index Pindex on PROJ(PNAME)
go
2．查看索引
1）查看DEPA表的索引信息。
use COMPANY
go
exec sp_helpindex DEPA
2）查看数据库COMPANY索引使用的空间信息
use COMPANY
go
exec sp_spaceused
3.索引更名
1）将DEPA表的索引Dindex名更改为Dindex1。
Use COMPANY
Go
Sp_rename ‘DEPA.Dindex’,’Dindex1’
go
4.删除索引
1）删除DEPA表的索引Dindex1。
use COMPANY
go
drop index DEPA.Dindex1
㈡视图
1.创建视图
1）创建14号部门员工的视图EMPL_14（ENO，ENAME，SALARY）。CREATE VIEW EMPL_14(ENO,ENAME,SALARY)
AS
SELECT EN0,ENAME,SALARY
FROM EMPL
WHERE DN0='14'
2) 创建包含项目名与员工名及工作天数的视图Prjemp。
CREATE VIEW Prjemp
AS
SELECT PROJ.PNAME,EMPL.ENAME,J0B.DAYS
FROM PROJ,J0B,EMPL
WHERE EMPL.EN0=J0B.EN0 AND J0B.PN0=PROJ.PNO
3）在视图EMPL_14上创建工资在3000以上的员工的视图EMPL_14H3。CREATE VIEW EMPL_14H3
AS
SELECT ENO,ENAME,SALARY
FROM EMPL_14
WHERE SALARY>3000
4）创建每个部门的平均工资的视图E_AVG（DNO，SAVG）。
CREATE VIEW E_AVG(DNO,SAVG)
AS
SELECT DN0,AVG(SALARY)
FROM EMPL
GROUP BY DN0
2.查询视图
1）根据视图Prjemp查询做'设计系统'项目的所有员工及工作天数。SELECT ENAME,DAYS
FROM Prjemp
WHERE PNAME=N'设计系统'
2）根据视图E_AVG查询‘客户中心’的平均工资。
SELECT DNO,SAVG
FROM E_AVG
WHERE DNO=(SELECT DN0
FROM DEPA
WHERE DNAME=N'客户中心')
3．更新视图
1）在视图EMPL_14中将1042号员工的姓名改为‘李立春’。UPDATE EMPL_14
SET ENAME=N'李立春'
WHERE ENO=1402