员工与部门之间的关系

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;