实训项目3-员工管理数据库系统

陕西国防工业职业技术学院

SQL Server 2005实训报告

项目名称： 员工管理数据库系统 班 级： 网络3101 姓 名： 刘泽国 学 号： 23 指导老师： 李老师 宫老师 实训时间： 2011.06.27-----2011.07.1 实训地点： 8-507 成 绩：

计算机教研室制

SQL实训

1.创建数据库

创建一个名称为factory的数据库，脚本文件名：0100.sql，此脚本文件可用SSMS方式生成。

(1)将主数据库文件factory_Data.MDF放置在D:\\DBF文件夹中，其文件大小自动增长为按5MB增长。

(2)将事务日志文件factory_Log.MDF放置在D:\\DBF文件夹中，其文件大小自动增长为按1MB增长。

------------------------------------脚本文件0100.sql-------------- ------------------------------

CREATE DATABASE factory ON

(NAME='D:\\dbf\\factory_data',

FILENAME='D:\\dbf\\factory.mdf', FILEGROWTH=5)

LOG ON

(NAME='D:\\dbf\\factory_log', ) go

USE factory Go

FILENAME='D:\\dbf\\factory.ldf', FILEGROWTH=1

------------------------------------------------------------------------------------------------------- 2.创建和使用表

脚本文件名：0200.sql，此脚本文件可用SSMS方式。 (1)建立职工表worker，其结构为：职工号：int，姓名：char(8)，性别：char(2)，出生日期：datetime，党员否：bit，参加工作：datetime，部门号：int。其中“职工号”为主键。在worker表中输入如下记录： 职工号 姓名 性别 出生日期 党员否 参加工作 部门号 3 陈明 男 05/08/45 否 01/01/65 2 10 陈涛 男 02/10/58 是 07/12/84 2 7 程西 女 06/10/80 否 07/10/02 1 12 李涵 男 04/19/65 是 07/10/89 3 4 李华 男 08/07/56 否 07/20/83 3 14 李艺 女 02/10/63 否 07/20/90 3 9 刘夫文 男 01/11/42 否 08/10/60 2 11 刘欣 男 10/08/52 否 01/07/70 1 6 欧阳少兵 男 12/09/71 是 07/20/92 3 1 孙华 男 0l/03/52 是 10/10/70 1 2 孙天奇 女 03/10/65 是 07/10/87 2 13 王小燕 女 02/10/64 否 07/10/89 1 15 魏君 女 01/10/70 否 07/10/93 3 5 余慧 男 12/04/80 否 07/10/02 3 8 张旗 男 11/10/80 否 07/10/02 2 (2)建立部门表depart，其结构为：部门号：int，部门名：char(10)。其中，“部

- 1 -

SQL实训

门号”为主键。在depart表中输入如下记录： 部门号 部门名 1 财务处 2 人事处 3 市场部 (3)建立职工工资表salary，其结构为：职工号：int，姓名：char(8)，日期：datetime，工资：decimal(6,1)。其中，“职工号”和“日期”为主键。在salary表中输入如下记录

职工号 姓名 日期 工资 3 陈明 01/04/04 1350.6 3 陈明 02/03/04 1355.6 10 陈涛 01/04/04 1245.8 10 陈涛 02/03/04 1250.8 7 程西 01/04/04 750.8 7 程西 02/03/04 755.8 12 李涵 01/04/04 1345.0 12 李涵 02/03/04 1350.0 4 李华 01/04/04 1500.5 4 李华 02/03/04 1505.5 14 李艺 01/04/04 1000.6 14 李艺 02/03/04 1005.6 9 刘夫文 01/04/04 2006.8 9 刘夫文 02/03/04 2011.8 11 刘欣 01/04/04 1250.0 11 刘欣 02/03/04 1255.0 6 欧阳少兵 01/04/04 1085.0 6 欧阳少兵 02/03/04 1085.0 1 孙华 01/04/04 1201.5 1 孙华 02/03/04 1206.5 2 孙天奇 01/04/04 900.0 2 孙天奇 02/03/04 905.0 13 王小燕 01/04/04 1200.0 13 王小燕 02/03/04 1205.0 15 魏君 01/04/04 1100.0 15 魏君 02/03/04 1105.0 5 余慧 01/04/04 725.0 5 余慧 02/03/04 730.0 8 张旗 01/04/04 728.0 8 张旗 02/03/04 733.0 (4)建立worker、depart和salary3个表之间的关系。 注意：

数据的录入可使用导入的方法，可先建立数据表结构，然后将此数据复制到excel，最后将其导入到数据库中，注意此数据中的日期格式。

- 2 -

SQL实训

3个表之间的关系主要指主外键关系。

--------------------------------------脚本文件0200.sql------------ ------------------------------

USE [factory]

CREATE TABLE [dbo].[worker](

[职工号] [int] NOT NULL,

[姓名] [char](8) COLLATE Chinese_PRC_CI_AS NULL, [出生日期] [datetime] NULL, [党员] [bit] NULL,

[参加工作] [datetime] NULL,

[姓别] [char](2) COLLATE Chinese_PRC_CI_AS NULL,

[部门号] [int] NULL,

CONSTRAINT [PK_worker] PRIMARY KEY CLUSTERED ([职工号] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO

SET ANSI_PADDING OFF USE [factory] GO

CREATE TABLE [dbo].[depart](

[部门号] [int] NOT NULL,

[部门名] [char](10) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_depart] PRIMARY KEY CLUSTERED ([部门号] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO

SET ANSI_PADDING OFF USE [factory] GO

CREATE TABLE [dbo].[salary]( [职工号] [int] NOT NULL,

[姓名] [char](8) COLLATE Chinese_PRC_CI_AS NULL, [工资] [datetime] NULL, [日期] [datetime] NOT NULL,

constraint pk_d_o_id primary key(职工号, 日期) ) GO

SET ANSI_PADDING OFF

------------------------------------------------------------------------------------------------------- 3.数据库完整性

脚本文件名：0300.sql，此脚本文件可利用企业管理器自动生成。 (1)实施worker表的“性别”字段默认值为“男”的约束。 (2)实施salary表的“工资”字段值限定在0~9999的约束。

- 3 -

SQL实训

------------------------------------脚本文件0300.sql-------------- ------------------------------

CREATE TABLE [dbo].[worker](

[职工号] [int] NOT NULL,

[姓名] [char](8) COLLATE Chinese_PRC_CI_AS NULL,

[性别] [char](2) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [出生日期] [datetime] NULL,

[党员] [char](2) COLLATE Chinese_PRC_CI_AS NULL, [参加工作] [datetime] NULL,

[DF_worker_性别] DEFAULT ('男'),

[部门号] [int] NULL,

CONSTRAINT [PK_worker] PRIMARY KEY CLUSTERED

ALTER TABLE [dbo].[salary] WITH CHECK ADD CONSTRAINT [CK_salary] CHECK (([工资]>=(0) AND [工资]<=(9999)))

------------------------------------------------------------------------------------------------------- 4.查询数据

(1).查询所有男性职工的职工号、姓名。脚本文件名：0401.sql。

------------------------------------脚本文件0401.sql-------------- ------------------------------

select 职工号,姓名 from worker where 性别='男'

-------------------------------------------------------------------------------------------------------

(2).查询所有2000年以前工作的职工姓名、参加工作时间。脚本文件名：0402.sql。

------------------------------------脚本文件0402.sql-------------- ------------------------------

select 姓名,参加工作 from worker

where 参加工作<='2000'

-------------------------------------------------------------------------------------------------------

(3).查询所有市场部的职工姓名、性别、职工号、是否党员。脚本文件名：0403.sql。

------------------------------------脚本文件0403.sql-------------- ------------------------------

select 姓名,性别,职工号,党员否 from worker join depart

on worker.部门号=depart.部门号 where worker.部门号='3'

-------------------------------------------------------------------------------------------------------

(4).查询所有职工2004年2月的职工工资，显示字段包括职工号、职工姓名、性别、部门名称、工资，并按工资从大到小排序。脚本文件名：0404.sql。

------------------------------------脚本文件0404.sql-------------- ------------------------------

select worker.职工号,worker.姓名,worker.性别,worker.部门号,salary.工资 from worker join salary

on worker.职工号=salary.职工号

- 4 -

SQL实训

join depart

on worker.部门号=depart.部门号 where 工资='2004-02'

-------------------------------------------------------------------------------------------------------

(5).查询所有市场部和人事处职工的职工号、职工姓名。脚本文件名：0405.sql。

------------------------------------脚本文件0405.sql-------------- ------------------------------

select 职工号,姓名 from worker join depart

on worker.部门号=depart.部门号

where worker.部门号='2' or worker.部门号='3'

-------------------------------------------------------------------------------------------------------

(6).查询所有职工的总工资、职工号、职工姓名。脚本文件名：0406.sql。 ------------------------------------脚本文件0406.sql-------------- ------------------------------

select worker.职工号,worker.姓名,sum(工资) 总工资 from worker join salary

on worker.职工号=salary.职工号 group by worker.职工号,worker.姓名 order by sum(工资)

-------------------------------------------------------------------------------------------------------

(7).查询所有职工的年龄，显示字段包括职工号、职工姓名、出生年月、年龄。脚本文件名：0407.sql。

------------------------------------脚本文件0407.sql-------------- ------------------------------

select 职工号,姓名,出生日期,year(getdate())-year(出生日期) 年龄 from worker

-------------------------------------------------------------------------------------------------------

(8).查询各部门的党员人数，显示字段包括部门号、部门名称、党员人数。脚本文件名：0408.sql。

------------------------------------脚本文件0408.sql-------------- ------------------------------

Select worker.部门号,depart.部门名,count(党员否)党员人数 from worker join depart

on worker.部门号=depart.部门号 group by worker.部门号,depart.部门名 order by count(党员否)

-------------------------------------------------------------------------------------------------------

(9).查询所有职工的平均工资，显示字段包括职工号、职工姓名、平均工资。脚本文件名：0409.sql。

------------------------------------脚本文件0409.sql-------------- ------------------------------

Select worker.职工号,worker.姓名,avg(工资)平均工资 from worker join salary

- 5 -

SQL实训

on worker.职工号=salary.职工号 group by worker.职工号,worker.姓名 order by avg(工资)

-------------------------------------------------------------------------------------------------------

(10).查询所有平均工资高于1200的职工信息（职工号、职工姓名、性别）和对应的平均工资。脚本文件名：0410.sql。

------------------------------------脚本文件0410.sql-------------- ------------------------------

Select worker.职工号,worker.姓名,worker.性别,avg(工资)平均工资 from worker join salary

on worker.职工号=salary.职工号

group by worker.职工号,worker.姓名,worker.性别 having avg(工资)>'1200' order by avg(工资) desc

-------------------------------------------------------------------------------------------------------

(11).显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。脚本文件名：0411.sql。

------------------------------------脚本文件0411.sql-------------- ------------------------------

Select worker.职工号,worker.姓名,depart.部门名,salary.日期,工资 from salary join worker

on worker.职工号=salary.职工号 join depart

on depart.部门号=worker.部门号

where salary.职工号=(select top 1 职工号 from salary group by 职工号

order by avg(工资) desc)

-------------------------------------------------------------------------------------------------------

(12)显示最高工资的职工所在的部门名。脚本文件名：0412.sql。

------------------------------------脚本文件0412.sql-------------- ------------------------------

Select depart.部门名 from salary join worker

on worker.职工号=salary.职工号 join depart

on depart.部门号=worker.部门号

where salary.职工号=(select top 1 职工号 from salary group by 职工号

order by avg(工资) desc)

-------------------------------------------------------------------------------------------------------

(13)显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。脚本文件名：0413.sql。

- 6 -

SQL实训

------------------------------------脚本文件0413.sql-------------- ------------------------------

Select worker.职工号,worker.姓名 from worker left join salary

on worker.职工号=salary.职工号 group by worker.职工号,worker.姓名

having avg(工资)<(select avg(工资) from salary

------------------------------------------------------------------------------------------------------- 5.更新数据

(1).在depart表中插入设备处记录，部门编号为4。脚本文件名：0501.sql。 ------------------------------------脚本文件0501.sql-------------- ------------------------------

insert into depart values('4','设备处')

-------------------------------------------------------------------------------------------------------

(2).在depart表中插入资产处记录，部门编号为当前表中的最大编号+1。脚本文件名：0502.sql。

------------------------------------脚本文件0502.sql-------------- ------------------------------

insert into depart values('5','资产处')

-------------------------------------------------------------------------------------------------------

(3).在worker表中插入一条记录，记录内容为职工号20，姓名陈立，性别女，出生日期1955年3月8日，参加工作时间为1975年10月10日，部门号为4。脚本文件名：0503.sql。

------------------------------------脚本文件0503.sql-------------- ------------------------------

insert into worker (职工号,姓名,性别,出生日期,参加工作,部门号) values ('20','陈立','女','1955-3-8','1975-10-10','4')

-------------------------------------------------------------------------------------------------------

(4).在worker表中插入一条记录，记录内容为职工号为最大职工号+1，姓名张三，性别男，出生日期1965年9月9日，参加工作时间为1980年10月1日，部门号为3。脚本文件名：0504.sql。

------------------------------------脚本文件0504.sql-------------- ------------------------------

insert into worker(职工号,姓名,性别,出生日期,参加工作,部门号) values ('21','张三','男','1965-9-9','1980-10-1','3')

-------------------------------------------------------------------------------------------------------

(5).修改职工1月份工资，将每个人的工资上涨10%。脚本文件名：0505.sql。 ------------------------------------脚本文件0505.sql-------------- ------------------------------

update salary set 工资=工资*1.1

where 日期='2004-1-4 0:00:00'

-------------------------------------------------------------------------------------------------------

(6).修改所有女性职工2月份工资，将每人的工资加上100元的医疗补贴。脚本文件名：0506.sql。

------------------------------------脚本文件0506.sql-------------- ------------------------------

update salary set 工资=工资+100 where 性别in (select 性别from worker where 性别='女' and 日期='2004-2-3 0:00:00')

-------------------------------------------------------------------------------------------------------

- 7 -

SQL实训

(7).将所有财务处女性职工的参加工作时间向前推一年。脚本文件名：0507.sql。

------------------------------------脚本文件0507.sql-------------- ------------------------------

update worker set 参加工作=year(参加工作)-1 where 部门号='1' and 性别='女'

-------------------------------------------------------------------------------------------------------

(8).删除工资表中工资最高的记录。脚本文件名：0508.sql。

------------------------------------脚本文件0508.sql-------------- ------------------------------

delete from salary where 职工号='9'and 日期='2004-1-4 0:00:00'

-------------------------------------------------------------------------------------------------------

(9).删除工资表中平均工资最高的职工的工资记录。脚本文件名：0509.sql。 ------------------------------------脚本文件0509.sql-------------- ------------------------------

delete from salary where 职工号='9'

-------------------------------------------------------------------------------------------------------

(10).删除职工为1号的职工的所有信息。脚本文件名：0510.sql。

------------------------------------脚本文件0510.sql-------------- ------------------------------

delete from salary where 职工号='1' delete from worker where 职工号='1'

------------------------------------------------------------------------------------------------------- 6.视图

(1)建立视图职工年龄查询，显示所有职工的年龄。脚本文件名：0601.sql。 ------------------------------------脚本文件0601.sql-------------- ------------------------------

Create view vw_ageworker as

select 姓名,year(getdate())-year(出生日期) 年龄 from worker

-------------------------------------------------------------------------------------------------------

(2)建立视图“党员人数统计”，求出各部门的党员人数。脚本文件名：0602.sql。 ------------------------------------脚本文件0602.sql-------------- ------------------------------

create view vw_dangworker as

select 部门号,count(党员否) 党员人数 from worker where 党员否='是'

-------------------------------------------------------------------------------------------------------

(3)建立视图2004年1月份工资查询，显示所有职工的姓名和2004年1月份工资数。脚本文件名：0603.sql。

------------------------------------脚本文件0603.sql-------------- ------------------------------

create view vw_gongsalary as

select 姓名, 工资 from salary

where 日期='2004-01'

-------------------------------------------------------------------------------------------------------

(4)建立视图职工平均工资查询，显示所有职工的职工号、姓名和平均工资。

- 8 -

SQL实训

脚本文件名：0604.sql。

------------------------------------脚本文件0604.sql-------------- ------------------------------

CREATE VIEW vw_View1 AS

SELECT 职工号, 姓名, avg(工资)平均工资 FROM salary

-------------------------------------------------------------------------------------------------------

(5)建立视图2004年2月份工资查询，显示所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列。脚本文件名：0605.sql。

------------------------------------脚本文件0605.sql-------------- ------------------------------

CREATE VIEW vw_View2 AS

SELECT TOP (100) PERCENT dbo.salary.职工号, dbo.salary.姓名, dbo.salary. 工资, dbo.depart.部门名, dbo.salary.日期 FROM dbo.salary CROSS JOIN dbo.depart

WHERE (dbo.salary.日期= CONVERT(DATETIME, '2004-02 ', 102))

-------------------------------------------------------------------------------------------------------

(6)建立视图部门平均工资查询，显示各部门名和该部门的所有职工平均工资。脚本文件名：0606.sql。

------------------------------------脚本文件0606.sql-------------- ------------------------------

CREATE VIEW vw_View3 AS

SELECT depart.部门名, avg(salary. 工资)平均工资 FROM salary CROSS JOIN depart

------------------------------------------------------------------------------------------------------- 7.索引

(1).在worker表的姓名列上创建名为workername的惟一性索引。脚本文件名：0701.sql。

------------------------------------脚本文件0701.sql-------------- ------------------------------

USE [factory] GO

CREATE UNIQUE NONCLUSTERED INDEX [workername] ON [dbo].[worker] (

[姓名] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

-------------------------------------------------------------------------------------------------------

(2).在worker表的出生日期列上创建名为workerborn的非聚集索引，并按从大到小的次序排列。脚本文件名：0702.sql。

------------------------------------脚本文件0702.sql-------------- ------------------------------

USE [factory] GO

CREATE NONCLUSTERED INDEX [workerborn] ON [dbo].[worker]

- 9 -

SQL实训

(

[出生日期] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

-------------------------------------------------------------------------------------------------------

(3).在salary表的工资列上创建名为money的非聚集索引，并按从小到大的次序排列。脚本文件名：0703.sql。

------------------------------------脚本文件0703.sql-------------- ------------------------------

USE [factory] GO

CREATE NONCLUSTERED INDEX [money] ON [dbo].[salary] (

[工资] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

-------------------------------------------------------------------------------------------------------

8.SQL编程

(1)创建一个存储过程showworker，要求该存储过程能根据提供的职工号返回其工作部门名称；如果不存在此人，则显示提示信息“查无此人”。

执行该存储过程，查询职工号为10的职工信息。 脚本文件名：0801.sql。

---------------------------------------脚本文件0801.sql------------------------------------------

if ( select count(*) from worker where 职工号=10)>0 select depart.部门名,worker.职工号 from worker,depart where

worker.职工号=10 and worker.部门号=depart.部门号 else

print '查无此人'

----------------------------------------------------------------------------------

(2)求出男女职工的平均工资。若男职工平均工资高出女职工平均工资50％，则显示“男职工比女职工的工资高多了”的信息；若男职工平均工资与女职工平均工资比率在1.5～0.8之间，则显示“男职工跟女职工的工资差不多”的信息；否则，显示“女职工比男职工的工资高多了”的信息。脚本文件名：0802.sql。 --------------------------------------脚本文件0802.sql-------------- ---------------------------

declare @男float,@女float,@比较值float

select @男=avg(salary.工资) --计算男职工平均工资 from worker,salary

where worker.职工号=salary.职工号and worker.性别='男' select @女=avg(salary.工资) --计算女职工平均工资 from worker,salary

where worker.职工号=salary.职工号and worker.性别='女'

- 10 -

SQL实训

set @比较值=@男/@女 if @比较值>1.5

print '男职工比女职工的工资高多了' else

if @比较值>=0.8

print '男职工跟女职工的工资差不多' else

print '女职工比男职工的工资高多了'

9.触发器

创建一个触发器Delworker，删除worker表中指定职工号的记录时，也将职工工资表salary中相应的记录删去。脚本文件名：0901.sql。

------------------------------------脚本文件0901.sql-------------- ------------------------------

CREATE TRIGGER Delworker ON worker AFTER delete AS begin

declare @worker varchar(30)

select @worker 职工号from deleted print @worker+'用户已被删除！' end

delete from worker where 职工号='13'--触发器验证

------------------------------------------------------------------------------------------------------- 10.数据转换

(1).新建一个数据库factory1，将前面已建的factory数据库中的所有表导入到factory1数据库中。

(2).将前面已建的factory数据库中的所有表（不包括视图）导出到Factory.mdb(ACCESS数据库)。

(3).将此文件夹下的“订单.mdb”(ACCESS数据库)的数据导入到数据库factory中。

- 11 -

SQL实训

实 训 总 结

这次实训，总的下来，感觉不易。在数据库这门课程一开始，感觉学的云里雾里，觉得记代码很麻烦，也会担心这门课程期末考过不了。许多同学也有这样的感受，但相信通过这次的实训大家应该有了更多的信心。这次实训还综合应用数据库、SQLServer2005、学生管理等知识。使我们所学知识得到延伸与拓展，也提高了我们的综合实践能力。

在这一周的实训当中，我们做了一个员工管理数据库系统，我们实训的内容是从数据库、数据表的创建和修改开始的，表是建立关系数据库的基本结构，用来存储数据具有已定义的属性，在表的操作过程中，有查看表信息、查看表属性、修改表中的数据、删除表中的数据及修改表和删除表的操作。从实训中让我更明白一些知识，表是数据最重要的一个数据对象，表的创建好坏直接关系到数数据库的成败。另一个内容是数据库的约束、视图、查询。从中我们了解到查询语句的基本结构，和简单SELECT语句的使用，多表连接查询。而在视图的操作中，也了解到了视图是常见的数据库对象，是提供查看和存取数据的另一种途径，对查询执行的大部分操作，使用视图一样可以完成。虽然只是一个比较和我们有关的数据库，但是在这一周的实训过程中，做的也不是很顺利，其中也遇到了许多问题，通过反反复复的看书，并且向同学请教，把相关的知识复习了一遍，这也是一种很大的收获。至少我们在做的过程中学会了思考，就像在做这个数据库系统的时候，我们在做之前就要有一个方案，设计一个步骤，先开始进行需求分析，接着进行概念模型设计，其次进行逻辑结构设计，最后我们在创建数据库脚步文件。在创建的过程中，是我们问题最多的步骤，通过做题，我们发现了自己的不足，这也与我们平时上课有关，许多学过的知识，转眼就忘，而且也没有在空余时间去复习，并且对于一些重点的知识，我们也掌握的不好，许多的语法知识和基本语句都会出错。对于这种情况，我们就得好好反省自己，在以后的学习当中，我们就要补其不足，掌握更好的学习方法。

其实，不管我们做什么事情，我们都应该想清楚，首先干什么，其次干什么，有了一个合理的规划，我们做起来也就比较的有了方向，有了目标，再向目标去奋斗，这样我们才能更好的把握自己，使自己更好的发展。

- 12 -

本文来源：https://www.bwwdw.com/article/lvj.html