use company;
drop table if exists tt_dept;
create table tt_dept (
dept_id int not null auto_increment PRIMARY key,
dept_mgr_id int not null ,
dept_name VARCHAR(30) UNIQUE,
dept_addr VARCHAR(100),
tel VARCHAR(20),
fax VARCHAR(30),
add_time datetime
)auto_increment=100000;
insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)
values(100000,'软件部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());
insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)
values(100001,'学习部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());
insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)
values(100002,'小卖部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());
insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)
values(100003,'中专部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());
insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)
values(100004,'武装部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());
insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)
values(100005,'体育部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());
drop table if exists tt_dept_user;
create table tt_dept_user(
user_id int not null auto_increment PRIMARY key,
user_name VARCHAR(30) UNIQUE,
first_name varchar(10),
last_name varchar(20),
password VARCHAR(30),
dept_id int not null,
add_time datetime,
sex int,
email varchar(50),
phone varchar(11),
tel VARCHAR(20),
birthday date,
address varchar(100),
id_card varchar(18),
image varchar (100),
remark varchar(200),
status int
)auto_increment=100000;
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('admin','周','海波','123456',100000,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('jeremy','张','三','123456',100001,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('john','王','五','123456',100002,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('jelly','赵','六','123456',100003,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('tom','周','三','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('mary','钱','六','123456',100001,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('sam','小','五','123456',100002,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('summer','小','张','123456',100003,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('mine','小','朱','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('sunny','杨','康','123456',100003,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('only','朱','名','123456',100005,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('user','中','国','123456',100003,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('summy','王','强','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('hony','赵','找','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('apple','宋','家','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('sunshine','小','成','123456',100002,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)
VALUES ('tony','小','阳','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);
drop TABLE if EXISTS tt_role;
create table tt_role(
role_id int not null auto_increment primary key,
role_name varchar(20) UNIQUE,
role_desc VARCHAR(200)
)auto_increment=100000;
insert into tt_role(role_name,role_desc) values('部门经理','部门事务管理');
insert into tt_role(role_name,role_desc) values('人事经理','人员事务管理');
insert into tt_role(role_name,role_desc) values('财务经理','财务事务管理');
insert into tt_role(role_name,role_desc) values('市场经理','市场事务管理');
insert into tt_role(role_name,role_desc) values('股东经理','股东事务管理');
insert into tt_role(role_name,role_desc) values('学习部经理','股东事务管理');
drop table if exists tt_menu;
create table tt_menu(
menu_id int not null auto_increment PRIMARY key,
menu_name VARCHAR(30) UNIQUE,
menu_desc varchar(200),
menu_value varchar(100) unique,
father_menu_id int
)auto_increment=100000;
insert into tt_menu(menu_name,menu_desc,menu_value,father_menu_id) values('部门管理','所有部门信息','http://localhost:8080/RBAC/loginConfirm#dept',1);
insert into tt_menu(menu_name,menu_desc,menu_value,father_menu_id) values('用户管理','所有用户信息','http://localhost:8080/RBAC/loginConfirm#user',1);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id)values('角色管理','所有角色信息','http://localhost:8080/RBAC/loginConfirm#role',1);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) values('菜单管理','所有菜单信息','http://localhost:8080/RBAC/loginConfirm#menu',1);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) values('角色人员管理','所有角色人员信息','http://localhost:8080/RBAC/loginConfirm#roleMem',1);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) values('权限管理','所有权限信息','http://localhost:8080/RBAC/loginConfirm#authority',1);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('部门增加','部门信息增加','http://localhost:8080/RBAC/Department/Department_add.jsp',100000);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('部门维护','部门信息维护','http://localhost:8080/RBAC/selectDepartment',100000);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('用户增加','用户信息增加','http://localhost:8080/RBAC/getDeptNameList',100001);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('用户维护','用户信息维护','http://localhost:8080/RBAC/selectUser',100001);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('角色增加','角色信息增加','http://localhost:8080/RBAC/Role/role_add.jsp',100002);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('角色维护','角色信息维护','http://localhost:8080/RBAC/selectRole',100002);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('菜单增加','菜单信息增加','http://localhost:8080/RBAC/selectFatherMenu',100003);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('菜单维护','菜单信息维护','http://localhost:8080/RBAC/selectMenu',100003);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('角色人员维护','角色人员信息维护','http://localhost:8080/RBAC/selectRoleUser',100004);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('用户权限设置','用户菜单信息维护','http://localhost:8080/RBAC/selectUserMenu',100005);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('部门权限设置','部门菜单信息维护','http://localhost:8080/RBAC/selectDeptMenu',100005);
insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('角色权限设置','角色菜单信息维护','http://localhost:8080/RBAC/selectRoleMenu',100005);
select* from tt_menu;
drop table if exists tt_role_user;
create table tt_role_user(
role_id int not null ,
user_id int not null
);
insert into tt_role_user (role_id,user_id) values(100000,100001);
insert into tt_role_user (role_id,user_id) values(100000,100002);
insert into tt_role_user (role_id,user_id) values(100001,100003);
insert into tt_role_user (role_id,user_id) values(100001,100004);
insert into tt_role_user (role_id,user_id) values(100002,100005);
insert into tt_role_user (role_id,user_id) values(100004,100006);
insert into tt_role_user (role_id,user_id) values(100003,100007);
insert into tt_role_user (role_id,user_id) values(100004,100008);
insert into tt_role_user (role_id,user_id) values(100004,100009);
insert into tt_role_user (role_id,user_id) values(100002,100011);
drop table if exists tt_user_menu;
create table tt_user_menu(
user_id int not null,
menu_id int not null
);
insert into tt_user_menu(user_id,menu_id) values(100000,100006);
insert into tt_user_menu (user_id,menu_id) values(100000,100007);
insert into tt_user_menu (user_id,menu_id) values(100000,100008);
insert into tt_user_menu (user_id,menu_id) values(100000,100009);
insert into tt_user_menu (user_id,menu_id) values(100000,1000010);
insert into tt_user_menu (user_id,menu_id) values(100000,100011);
insert into tt_user_menu (user_id,menu_id) values(100000,100012);
insert into tt_user_menu (user_id,menu_id) values(100000,100013);
insert into tt_user_menu (user_id,menu_id) values(100000,100014);
insert into tt_user_menu (user_id,menu_id) values(100000,100015);
insert into tt_user_menu (user_id,menu_id) values(100000,100016);
insert into tt_user_menu (user_id,menu_id) values(100000,100017);
insert into tt_user_menu (user_id,menu_id) values(100000,100018);
insert into tt_user_menu (user_id,menu_id) values(100001,100003);
insert into tt_user_menu (user_id,menu_id) values(100001,100004);
insert into tt_user_menu (user_id,menu_id) values(100002,100005);
insert into tt_user_menu (user_id,menu_id) values(100004,100006);
insert into tt_user_menu (user_id,menu_id) values(100003,100007);
insert into tt_role_user (role_id,user_id) values(100004,100008);
insert into tt_role_user (role_id,user_id) values(100004,100009);
insert into tt_role_user (role_id,user_id) values(100002,100011);
drop table if EXISTS tt_dept_menu;
create table tt_dept_menu(
dept_id int not null,
menu_id int not null
);
insert into tt_dept_menu (dept_id,menu_id) values(100000,100002);
insert into tt_dept_menu (dept_id,menu_id) values(100001,100003);
insert into tt_dept_menu (dept_id,menu_id) values(100001,100004);
insert into tt_dept_menu (dept_id,menu_id) values(100002,100005);
insert into tt_dept_menu (dept_id,menu_id) values(100004,100006);
insert into tt_dept_menu (dept_id,menu_id) values(100003,100007);
drop table if exists tt_role_menu;
create table tt_role_menu(
role_id int not null,
menu_id int not null
);
insert into tt_role_menu (role_id,menu_id) values(100000,100002);
insert into tt_role_menu (role_id,menu_id) values(100001,100003);
insert into tt_role_menu (role_id,menu_id) values(100001,100004);
insert into tt_role_menu (role_id,menu_id) values(100002,100005);
insert into tt_role_menu (role_id,menu_id) values(100004,100006);
insert into tt_role_menu (role_id,menu_id) values(100003,100007);
select * from tt_dept;
select * from tt_dept_user;
select * from tt_role;
select * from tt_role_user;
select * from tt_menu;
select * from tt_user_menu;
select * from tt_dept where 1=1 and dept_name like '%嘘唏%';
select * from tt_user_menu where user_id=100000 AND menu_id=100008;
select role_id,role_name,(select COUNT(*) from tt_role_user as t1 where t1.role_id =t2.role_id)as count from tt_role as t2 and t2.role_name like '%经理%'
select menu_id,menu_name from tt_menu where father_menu_id=1;
select menu_id,menu_name from tt_menu where father_menu_id=10000;
select * from tt_menu as t1, tt_user_menu as t2 where t1.father_menu_id=100000 and user_id =100000 and t1.menu_id=t2.menu_id;
select * from tt_menu as t1 where t1.father_menu_id=100000;
select t2.dept_id,t2.dept_name,(select COUNT(*) from tt_dept_menu as t1 where t1.dept_id=t2.dept_id) as count from tt_dept as t2 ORDER BY t2.dept_id;
select t2.role_id,t2.role_name,t2.role_desc,(select COUNT(*) from tt_role_menu as t1 where t1.role_id=t2.role_id) as count from tt_role as t2 ORDER BY t2.role_id;
select t1.menu_name,t2.user_name from tt_menu as t1,tt_dept_user as t2,tt_user_menu as t3 where t1.menu_id=t3.menu_id and t2.user_id=t3.user_id and t2.user_name='admin';
select m.menu_id,m.menu_name from tt_menu m where menu_id in(select menu_id from tt_user_menu where user_id=(select user_id from tt_dept_user where user_name='admin'))
or menu_id in (select menu_id from tt_dept_menu where dept_id=(select dept_id from tt_dept_user where user_id=(select user_id from tt_dept_user where user_name='admin')))
or menu_id in (select menu_id from tt_role_menu where role_id in (select role_id from tt_role_user where user_id=(select user_id from tt_dept_user where user_name='admin')))
select t2.* ,(select CONCAT(first_name,last_name) from tt_dept_user as t1 where t1.user_id=t2.dept_mgr_id) as dept_manager from tt_dept as t2
1.简单实用的html模板框架
2.只采用简单的jsp+servlet+mysql+json+ajax技术,适用简单初学者来参考学习
3.表单提交使用js,validateForm校验
4.输入框采用了时间日期控件,省市区三级联动的基本控件。
5.基本实现mvc模式,实现数据业务页面分层