create table department(
pid int primary key auto_increment not null,
pname varchar(50) not null,
premark text
);
create table employee(
uid int primary key auto_increment not null,
pid int(11) not NULL,
sid int(11) not NULL,
uname varchar(20) not null,
upsw varchar(20) not null,
urealname varchar(20) not null
);
create table Record(
xid int primary key auto_increment not null,
uid int(50) not NULL,
xtitle varchar(100) not null,
xcontent text,
xbegintime varchar(100) not null,
xendtime varchar(100) not null,
xcontentt varchar(100) not null,
xflag int(10) not null
);
INSERT INTO `department` VALUES ('1', '管理规划部', '此部门为公司的最高管理部门');
INSERT INTO `department` VALUES ('2', '开发部', '此部门为公司的最高开发部门');
INSERT INTO `department` VALUES ('3', '财务部', '此部门为公司的最高财务部门');
INSERT INTO `roles` VALUES (null, '系统管理员');
INSERT INTO `roles` VALUES (null, '普通员工');
INSERT INTO `roles` VALUES (null, '部门主管');
INSERT INTO `employee` VALUES ('1', '1','1','admin', '123','张三');
INSERT INTO `employee` VALUES ('2', '2','2','sa', '123','李四');
INSERT INTO `employee` VALUES ('3', '3','3','xl', '123','小刘');
INSERT INTO `Record` VALUES ('1', '1','回家', '想家了','2021-02-14','2021-02-15','准','1');
INSERT INTO `Record` VALUES ('2', '2','回家1', '想家了1','2021-02-14-1','2021-02-15-1','准','2');
INSERT INTO `Record` VALUES ('3', '3','回家2', '想家了2','2021-02-14-2','2021-02-1-2','不行','3');
INSERT INTO `Record` VALUES ('4', '1','回家3', '想家了3','2021-02-14-3','2021-02-15-3','改改','4');
select * from employee,
select e.uid,e.pid,e.sid,e.uname,e.urealname from employee e left join department d on e.pid=d.pid
left join roles r on e.sid=r,sid
select a.uid,b.pname,c.sname,a.uname,a.urealname value
from roles c join employee a
on c.sid = a.sid
join department b
on a.pid = b.pid
select a.uid,b.pname,c.sname,a.uname,a.urealname value from roles c join employee a on c.sid = a.sid join department b on a.pid = b.pid
select a.uid,a.pid,a.sid,a.uname,a.urealname value from roles c join employee a on c.sid = a.sid join department b on a.pid = b.pid
select a.uid,a.pid,a.sid,a.uname,a.urealname value from roles c join employee a on c.sid = a.sid join department b on a.pid = b.pid
select x.xid,e.urealname,x.xtitle,x.xcontent,x.xbegintime,x.xendtime,x.xcontentt,x.xflag from Record x , employee e where x.uid = e.uid
select x.xid,e.urealname,x.xtitle,x.xcontent,x.xbegintime,x.xendtime,x.xcontentt,x.xflag from (Record inner employee on x.uid = e.uid) where employee
urealname='张三'
select xid,urealname,xtitle,xcontent,xbegintime,xendtime,xcontentt,xflag from Record left join employee on record.uid = employee.uid where employee.urealname='张三';
INSERT INTO employee (pid,sid,uname,upsw,urealname) VALUES ('1', '1','1', '1','1')
SELECT * FROM record where uid='1'
select*from employee where uname='sa' and upsw='123' and sid=2
select xid,urealname,xtitle,xcontent,xbegintime,xendtime,xcontentt,xflag from record l JOIN employee s on l.uid=s.uid where (l.xflag=2 or l.xflag=4) and l.uid='2'
select*from record
select l.*,s.urealname from leaveRequest l JOIN staff s on l.uid=s.uid where (l.xflag=1 or l.xflag=3) and l.uid=?
select xid,urealname,xtitle,xcontent,xbegintime,xendtime,xcontentt,xflag from record l JOIN employee s on l.uid=s.uid where (l.xflag=2 or l.xflag=4) and l.uid='2'
select xid,urealname,xtitle,xcontent,xbegintime,xendtime,xcontentt,xflag from record l JOIN employee s on l.uid=s.uid where (l.xflag=3 ) and l.uid='2'
select xid,urealname,xtitle,xcontent,xbegintime,xendtime,xcontentt,xflag from record l JOIN employee s on l.uid=s.uid where (l.xflag=3 ) and l.uid='2'
select xid,urealname,xtitle,xcontent,xbegintime,xendtime,xcontentt,xflag from Record l JOIN employee s on l.uid=s.uid where l.xflag=0 and s.pid='3'
select xid,urealname,xtitle,xcontent,xbegintime,xendtime,xcontentt,xflag from Record l JOIN employee s on l.uid=s.uid where l.xid='8'
select xid,urealname,xtitle,xcontent,xbegintime,xendtime,xcontentt,xflag from record l JOIN employee s on l.uid=s.uid where (l.xflag=1 or l.xflag=4) and l.uid=2