1 use test;
2 drop table if exists staff;
3 drop table if exists sections;
4 create table sections
5 (
6 id int,
7 name varchar(16) not null,
8 primary key (id)
9 )
10 type=InnoDB
11 CHARACTER SET utf8;
12
13 insert into sections values(1, '西游');
14 insert into sections values(2, '水浒');
15 insert into sections values(3, '红楼');
16 insert into sections values(4, '三国');
17
18 create table staff
19 (
20 id int,
21 name varchar(8) not null,
22 gender int not null default 1,
23 birth date,
24 section_id int,
25 email varchar(50),
26 salary decimal(12,3) not null default 0.00,
27 holiday int not null default 0,
28 index in_section(section_id),
29 foreign key(section_id) references sections(id) on delete cascade on update cascade,
30 primary key(id, section_id)
31 )
32 type=InnoDB
33 CHARACTER SET utf8;
34
35 insert into staff values(1,'孙悟空',1,'1970/04/01',1,'sunwukong@kerbor.com', 10000,0);
36 insert into staff values(2,'唐僧',1,'1960/12/01',1,'tangseng@kerbor.com', 12000,0);
37 insert into staff values(1,'宋江',1,'1974/06/01',2,'songjiang@kerbor.com', 8000,0);
38 insert into staff values(2,'孙二娘',0,'1970/04/01',2,'sun2niang@kerbor.com', 7500,0);
39 insert into staff values(1,'王熙凤',0,'1971/05/19',3,'wangxifeng@kerbor.com', 16000,1);
40 insert into staff values(2,'薛宝钗',0,'1984/09/04',3,'xuebaoX@kerbor.com', 7500,0);
41 insert into staff values(1,'吕布',1,'1982/02/19',4,'lvbu@kerbor.com', 12000,1);
42 insert into staff values(2,'貂禅',0,'1984/09/16',4,'diaochan@kerbor.com', 6500,1);
