staff建立一張員工表:id(主鍵),empName(非空),departNum(非空,外來鍵),empHobby,empAge(大於20小於50)
department建立一張部門表:id(主鍵),departName(唯一),departAddress(非空)
新增一定的資訊,保證資料的完整性和同一性.
查詢每個部門中員工的資訊.
工具/原料
需要安裝Mysql
方法/步驟
先建立主表
create table department(
id int primary key auto_increment,
departName varchar(20) not null,
departAddtrss varchar(50) not null
);
在建立從表
create table staff(
id int primary key auto_increment,
empName varchar(20) not null,
departNum int not null,
empHobby varchar(20),
enpAge int check(age>20 and age<50),
foreign key (departNum) references department(id));
建立主表資料
insert into department(departName,departAddtrss) values('HR','aaa');
insert into department(departName,departAddtrss) values('設計','bbb');
insert into department(departName,departAddtrss) values('程式設計','ccc');
insert into department(departName,departAddtrss) values('美工','ddd');
insert into department(departName,departAddtrss) values('特工','eee');
建立從表資料
insert into staff(empName,departNum,empHobby,enpAge) values('張無忌',1,'爬山',23);
insert into staff(empName,departNum,empHobby,enpAge) values('張三丰',2,'爬樹',40);
insert into staff(empName,departNum,empHobby,enpAge) values('趙敏',3,'跳舞',21);
insert into staff(empName,departNum,empHobby,enpAge) values('東邪',4,'瞎搞',35);
insert into staff(empName,departNum) values('張翠山',2);
注意事項
要先建立主表才能建立從表