MySQL数据库实验九 MySQL应用系统开发

一、实验项目：

使用MySQL进行应用系统开发。

二、实验目的

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

三、实验内容

（一）：

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

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

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

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

2、创建员工管理数据库yggl。

create database yggl;
Show databases;

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

use yggl;
CREATE TABLE departments (
deptid char(3) NOT NULL,
deptname char(20) NOT NULL,
memo text,
PRIMARY KEY  (deptid));

CREATE TABLE employees (
empid char(6) NOT NULL,
empname char(10) NOT NULL,
edu char(4) NOT NULL,
birth date NOT NULL,
sex char(2) NOT NULL,
jobyear tinyint,
address varchar(20),
phone char(12),
deptid char(3) NOT NULL,
PRIMARY KEY  (empid),
foreign key(deptid)
references departments(deptid));

CREATE TABLE salary (
empid char(6) NOT NULL,
inmoney float(8,2) NOT NULL,
outmoney float(8,2) NOT NULL,
PRIMARY KEY(empid),
foreign key(empid)
references employees(empid));
show tables;

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

create or replace view emp_view1(员工编号,姓名,工作年限,学历) as
select empid,empname,jobyear,edu from employees
where sex='男';
select * from emp_view1;

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

create index depart_ind on employees(deptid);
Show index from employees\G

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

delimiter $$
create procedure cp(in id1 char(6),in id2 char(6),out bj int)
begin
declare sr1,sr2 float(8,2);
select inmoney-outmoney into sr1 from salary where empid=id1;
select  inmoney-outmoney into sr2 from salary where empid=id2;
if sr1> sr2 then set bj=0;
else set bj=1;
end if;
end $$
delimiter ;
call cp('000001','010008',@bj);
select @bj;

（二）：

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

delimiter $$
create function em_num() returns integer 
begin
return ( select count( * ) from employees);
end $$
delimiter ;
select em_num();

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

DELIMITER $$
CREATE TRIGGER EM_DELETE AFTER DELETE
ON Employees FOR EACH ROW
BEGIN
DELETE FROM salary WHERE empid=OLD.empid;
END$$
DELIMITER ;
delete from employees where empid='000001';

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

set @@global.event_scheduler=true;
create table temp(counts int not null primary key);
create event tjemp  on schedule  every 1 year starts now()
do insert into temp select count(*) from employees;
select * from temp;

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

create user user1@localhost identified by '1234';
select user from mysql.user;

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

grant select on yggl.employees to user1@localhost;
Show grants for user1@localhost;

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

revoke select on yggl.employees from user1@localhost;
Show grants for user1@localhost;

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

SELECT * FROM  departments INTO OUTFILE 'D:/myfile.txt'
FIELDS  TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '?';

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

CREATE TABLE bk_depart LIKE departments;
LOAD DATA INFILE  'D:/myfile.txt' INTO TABLE bk_depart
FIELDS  TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '?';
Select * from bk_depart;