查找 管理
更新 插入 修改 删除
角色： 管理员 普通用户 限制用户
部门： 人力资源 财务部门 销售部门
员工： 袁丁 荆龙宾 顾泽平
CREATE TABLE Mode (M_Name VARCHAR(20), M_Number VARCHAR(5), M_Description VARCHAR(20), M_UpperMode VARCHAR(20)， M_Level int， M_Address VARCHAR(30), PRIMARY KEY(M_Name), FOREIGN KEY(M_UpperMode) REFERENCES Mode(M_Name) FOREIGN KEY(M_Level) REFERENCES Role(R_Level));
VALUES
INSERT INTO Mode VALUES('upDATE','03','upDATE functions','administer','1','$\>:#$^#e@%^$');
INSERT INTO Mode VALUES('search','04','search TABLEs','NULL','3','$\>:#$^#e(#$');
INSERT INTO Role VALUES('restricted user','3','restricted function');
INSERT INTO Role VALUES('normal user','2','normal function');
CREATE TABLE Department (D_Name VARCHAR(20), D_Description TEXT, PRIMARY KEY(D_Name));
INSERT INTO Mode VALUES('INSERT','01','INSERT
VALUES
INTO
TABLEs','upDATE','1','$\>:#$^#$%@#$');
INSERT INTO Mode VALUES('modify','02','modify TABLEs','upDATE','1','$\>:#$^#e@#$');
zeping','m','12345',2008-11-11,1,'normal
user','financial_department');
CREATE TABLE Log (L_ID VARCHAR(20), L_Time DATE, L_Mode VARCHAR(20), L_IP VARCHAR(20), L_Content TEXT, L_Result VARCHAR(20), L_TABLE VARCHAR(20), L_UserID VARCHAR(20), PRIMARY KEY(L_ID), foregin key(L_UserID), REFERENCES Staff(S_ID));
INSERT INTO log VALUES('00001',2010-11-11,'search','192.168.51.2','search * FROM Staff','succesful','TABLE_1','38071315');
INSERT INTO log VALUES('00002',2010-11-11,'search','192.168.51.3','search * FROM Staff WHERE S_Sex = 'f'','succesful','TABLE_1','38071307');
S_Department VARCHAR(20), PRIMARY KEY(S_ID), foregin key(S_Role), REFERENCES Role(R_Name) foregin key(S_Department), REFERENCES Department(D_Name));
INSERT INTO Staff VALUES('38071319','yuan ding','m','12345',2008-11-11,1,'administrator','human_resource');
INSERT
INTO log
VALUES('00003',2010-11-11,'INSERT','192.168.51.1','upDATE Laborage
set
laborage
=
100
WHERE
L_S_ID
=
'38071307'','succesful','TABLE_2','38071319');
CREATE TABLE Staff (S_ID VARCHAR(20), S_Name VARCHAR(20), S_Sex VARCHAR(1), S_Password VARCHAR(20), S_Time DATE, S_State VARCHAR(10), S_Role VARCHAR(20)
SELECT S_ID,M_Name,M_Number,M_Description,M_Level FROM Staff,Role,Mode WHERE Staff.S_Role = Role.R_Name
and Role.Level <= Mode.M_Level;
SELECT L_ID,L_Time,L_Mode,L_IP,L_Content,L_Result,L_TABLE,L_UserID,CURDAT E()//当前日期 FROM Staff,Log WHERE Staff.S_ID = Log.L_UserID
and (365*YEAR(CURDATE())+30*MONTH(CYRDATE())+DAY(CURDATE()))-30<(3 65* YEAR (L_Time)+30* MONTH (L_Time)+DAY(L_nt,COUNT(*) FROM Staff ORDER BY COUNT(*);
VALUES
FROM FROM
CREATE TABLE Role (R_Name VARCHAR(20), R_Level int,
R_Description TEXT, PRIMARY KEY(R_Name),);
INSERT INTO Role VALUES('administrator','1','full function');
数据库 OA 系统设计
模块：模块名（key），序号，描述，上一级模块（foreign key），功能 项/级别（foreign key），地址 角色：角色名称（key），级别，描述 部门：部门名称（key），描述 员工：用户 id/工号（key），姓名，性别，密码，入职时间，状态， 角色名称（foreign key），部门名称（foreign key） 日志：记录 id（key），时间，类型（增删改查），ip 地址，内容，结 果（成功，失败），记录表，用户 id/工号（foreign key） 模块：
INSERT INTO Staff VALUES('38071307','jing longbin','m','12345',2008-11-11,1,'restricted user','marketing_department');
INSERT
INTO Staff
VALUES('38071315','gu
INSERT INTO Department VALUES('human_resource','hire staff');
INSERT INTO Department VALUES('financial_department','financial affairs');
INSERT INTO Department VALUES('marketing_department','marketing activity');