Mysql外來鍵練習?

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);

注意事項

要先建立主表才能建立從表