使用MySQL进行应用系统开发 -实验

一、实验目的

使学生具备使用MySQL技术进行应用系统开发的能力。

二、实验内容

（一）：2学时

1、员工管理系统的业务逻辑如下：

实体“员工”的属性有员工编号、姓名、学历、出生日期、性别、工作年限、地址、电话和所属部门。

实体“部门”的属性有部门编号、名称、备注。

实体“员工薪水”的属性有员工编号、收入、支出。

按照下列表格的要求完成该系统的表设计：（2分）

（1）employees（员工信息表）

列名	数据类型	长度	约束	说明
eid	varchar	20	Primary key	员工编号
ename	varchar	20	NOT NULL	姓名
educate	varchar	20	NOT NULL	学历
birthday	date		NOT NULL	出生日期
sex	char	2	NOT NULL	性别
yearday	tinyint		NOT NULL	工作年限
address	varchar	50	NOT NULL	地址
tel	varchar	11	NOT NULL	电话
did	varchar	20	Foreign key	所属部门

（2）departments（部门信息表）

列名	数据类型	长度	约束	说明
did	varchar	20	Primary key	部门编号
dname	varchar	20	NOT NULL	名称
dcontent	text			备注

（3）salary（员工薪水情况表）

列名	数据类型	长度	约束	说明
eid	varchar	20	Foreign key	员工编号
income	decimal	(10,2)	NOT NULL	收入
output	decimal	(10,2)	NOT NULL	支出

2、创建员工管理数据库yggl。（1分）

3、在员工管理数据库yggl中创建员工信息表employees、部门信息表departments和员工薪水情况表salary。（2分）

4、对员工管理数据库yggl创建视图emp_view1，包含所有男员工的员工号码、姓名、工作年限和学历。（2分）

5、对employees表中的部门编号列创建普通索引depart_ind。（1分）

6、创建存储过程，比较两个员工的实际收入，如前者比后者高就输出0，否则输出1，并调用该存储过程比较000001和010008两个员工的收入。（2分）

（二）：2学时

1、创建一个存储函数em_num，返回员工的总人数。（1分）

2、创建触发器，在employees表中删除员工信息的同时将salary表中与该员工相关的数据全部删除。（2分）

3、创建事件，在该事件中创建表temp用来保存员工的总人数，每年统计一次，从现在开始。（2分）

4、创建数据库用户user1，密码为1234。（1分）

5、授予用户user1对yggl库中employees表的select操作权限。（1分）

6、收回用户user1对yggl库中employees表的select操作权限。（1分）

7、备份yggl数据库中departments表的数据到D盘。要求字段值如果是字符就用双引号标注，字段值之间用逗号隔开，每行以“？”为结束标志。（1分）

8、将上题中的备份文件数据导入bk_depart表中。（1分）

三、实验代码及注释

（一）
2.create database if not exists yggl;
3.

create table employees(
eid varchar(20) not null primary key,
ename varchar(20) not null,
educate varchar(20) not null,
birthday date  not null,
sex char(2)  not null,
yearday tinyint  not null,
address varchar(50) not null,
tel varchar(11) not null,
did varchar(20) not null,
foreign key(did) references departments(did)
);

create table departments(
did varchar(20) not null primary key,
dname varchar(20) not null,
dcontent text 
);


create table salary (
eid  varchar(20) not null,
income decimal(10,2) not null,
output  decimal(10,2) not null,
foreign key (eid) references employees(eid)
);

4.create view emp_view1
as select eid,ename,yearday,educate from employees where sex='男';
5.create index depart_ind on employees(did);
6.
delimiter //
create procedure cp(in  eid1 varchar(20),in eid2 varchar(20), out result int)
begin
declare in1  decimal(10,2);
declare out1  decimal(10,2);
declare in2  decimal(10,2);
declare out2  decimal(10,2);
select income into in1 from salary where eid=eid1;
select output into out1 from salary where eid=eid1;
select income into in2 from salary where eid=eid2;
select output into out2 from salary where eid=eid2;
if in1-out1 > in2-out2 then set result=0;
else set result=1;
end if;
end //
delimiter ;



（二）
1.
 delimiter //
create function em_num()
returns int
reads sql data
begin
return (select count(*)  from employees);
end //
delimiter ;

select em_num();
2.
select * from employees;select * from salary;
 delimiter //
create trigger tri_es
after delete on employees for each row
begin
delete from salary where eid=old.eid;
end //
delimiter ;


delete from employees where eid='000001';
select * from employees;
select * from salary;
3.
set @@global.event_scheduler=true;
create table temp(num int );
delimiter //
create event if not exists event_count on schedule every 1 year starts now()
do begin
insert into temp(num) select count(*) from employees;
end //
delimiter ;

select * from temp;
4.
create user 'user1'@'localhost'  identified by '1234';
select user from mysql.user;
5.
grant select  on employees to 'user1'@'localhost' ;
Show grants for user1@localhost;
6.
 revoke select on employees  from 'user1'@'localhost';
Show grants for user1@localhost;
7.
select * from departments into outfile 'D:\departments.txt' 
fields terminated by ','
optionally enclosed by '"'
lines terminated by '?';
8.
create table bk_depart like departments;

load data infile 'D:/departments.txt' into table bk_depart
fields terminated by ','
optionally enclosed by '"'
lines terminated by '?';

Select * from bk_depart;

四、运行结果截图

（一）

5.