部门和员工表如下示:
dept:
deptno(primary key),dname,loc
emp:
empno(primary key),ename,job,mgr,sal,deptno
建表:
drop table if exists dept;
drop table if exists emp;
create table dept
(
deptno int not null,
deptname char(20),
deptlocation char(50),
primary key (deptno)
);
create table emp
(
empno int not null,
deptno int,
empname char(20),
empjob char(20),
empsalary float,
empmanager int,
primary key (empno)
);
alter table emp add constraint FK_deptincludeemp foreign key (deptno)
references dept (deptno) on delete restrict on update restrict;
INSERT INTO `dept` VALUES (1,'designing','workin121');
INSERT INTO `dept` VALUES (2,'coding','workin123');
INSERT INTO `dept` VALUES (3,'testing','workin125');
INSERT INTO `dept` VALUES (4,'manager','workin127');
INSERT INTO `dept` VALUES (5,'temp','nolocation');
INSERT INTO `emp` VALUES (1,2,'zhang','code',3000,15);
INSERT INTO `emp` VALUES (2,2,'wang','manager',4000,1);
INSERT INTO `emp` VALUES (3,1,'li','code',3600,1);
INSERT INTO `emp` VALUES (4,5,'zhao','test',2556,2);
INSERT INTO `emp` VALUES (5,1,'wu','code',6500,15);
INSERT INTO `emp` VALUES (6,5,'guo','code',5653,3);
INSERT INTO `emp` VALUES (7,1,'wang','study',1565,4);
INSERT INTO `emp` VALUES (8,1,'sun','code',9999.99,15);
INSERT INTO `emp` VALUES (9,2,'feng','study',1999,12);
INSERT INTO `emp` VALUES (10,1,'ma','code',2343,3);
INSERT INTO `emp` VALUES (11,3,'tu','code',9999.99,16);
INSERT INTO `emp` VALUES (12,1,'xin','code',7900,6);
INSERT INTO `emp` VALUES (13,1,'si','code',4546,2);
INSERT INTO `emp` VALUES (14,1,'fu','test',2464,15);
INSERT INTO `emp` VALUES (15,3,'hao','manager',9999.99,NULL);
INSERT INTO `emp` VALUES (16,4,'liu','test',3521,15);