员工与部门之间的关系

来源：互联网发布：懒惰的开发者知乎编辑：程序博客网时间：2024/05/06 19:27

drop table emp;
drop table dept;

--部门表--
CREATE TABLE `dept` (
`deptid` int(11) NOT NULL,
`deptname` varchar(30) DEFAULT NULL,
`deptdate` date DEFAULT NULL,
PRIMARY KEY (`deptid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 员工表 --
CREATE TABLE `emp` (
`empid` int(11) NOT NULL AUTO_INCREMENT,
`empname` varchar(20) DEFAULT NULL,
`deptid` int(11) DEFAULT NULL,
`empdate` date DEFAULT NULL,
PRIMARY KEY (`empid`),
KEY `deptid` (`deptid`),
CONSTRAINT `emp_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into dept(deptid,deptname,deptdate) values(100001, 'renli',date_format(now(),'%Y%m%d'));
insert into dept(deptid,deptname,deptdate) values(100002, 'xingzheng',date_format(now(),'%Y%m%d'));
insert into dept(deptid,deptname,deptdate) values(100003, 'caiwu',date_format(now(),'%Y%m%d'));
insert into dept(deptid,deptname,deptdate) values(100004, 'jishu',date_format(now(),'%Y%m%d'));
insert into dept(deptid,deptname,deptdate) values(100005, 'shichang',date_format(now(),'%Y%m%d'));

insert into emp(empid,empname,deptid,empdate) values(200001,'qyf',100001,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200002,'zhangsan',100001,date_format(now(),'%Y%m%d'))
insert into emp(empid,empname,deptid,empdate) values(200003,'lisi',100001,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200004,'wangwu',100002,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200005,'zhaoliu',100002,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200006,'likun',100003,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200007,'wang',100004,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200008,'jiameng',100004,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200009,'liuliang',100005,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200010,'wanglin',100005,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200011,'liucong',100005,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200012,'changpin',100005,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200013,'nanguo',100005,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200014,'liangli',100002,date_format(now(),'%Y%m%d'));
insert into emp(empid,empname,deptid,empdate) values(200015,'luluo',100002,date_format(now(),'%Y%m%d'));
commit;

select * from emp;
select * from dept;

select d.deptid, d.deptname, e.count from dept d,
(select deptid,count(deptid) count from emp group by deptid) e
where d.deptid=e.deptid;