MySQL数据库操作（四）——多表之间的关系

个人博客：haichenyi.com。感谢关注

主键与外键

1、一张表可以有多个外键

2、外键是表的主键，可以是别的表，也可以是本身（少见）

3、外键可以为null，可以重复，但是不能不存在，必须要查的到

创建语法 foreign key(本表的列名) references 关联的表名(关联表的主键)

//创建一个部门表
CREATE TABLE dept(
    did INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(50)
);

//插入几条数据
INSERT INTO dept(did,dname) VALUE(NULL,'销售部');
INSERT INTO dept(did,dname) VALUE(NULL,'技术部');
INSERT INTO dept(did,dname) VALUE(NULL,'人力部');
INSERT INTO dept(did,dname) VALUE(NULL,'保洁部');

//创建一个员工表，这里的的edid是一个外键，对应部门表的主键did
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(50),
    edid INT,
    CONSTRAINT fk_empl_dept FOREIGN KEY(edid) REFERENCES dept(did)
);

//或者
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(50),
    edid INT
);

alter table employee add CONSTRAINT fk_empl_dept FOREIGN KEY(edid) REFERENCES dept(did);

//上面这样也可以设置外键

一对一关系 把从表的主键设置成外键与主表的主键关联

create table emp(
    eid int primary key auto_increment,
    ename varchar(50)
);

create table hmp(
    hid int primary key auto_increment,
    hname varchar(50),
    constraint fk_hmp_emp foreign key(hid) references emp(eid)
);

//如上，这样创建的两张表emp与hmp就是一对一的关系

//插入数据
insert into emp values(1,'张三');
insert into hmp values(1,'李四');
//这样张三跟李四就对应上了

多对多关系 两个主表+一个关联表

create table teacher(
    tid int primary key auto_increment,
    tname varchar(50)
);

create table student(
    sid int primary key auto_increment,
    sname varchar(50)
);

create table stu_tea(
    tid int,
    sid int,
    constraint fk_student foreign key(sid) references student(sid),
    constraint fk_teacher foreign key(tid) references teacher(tid)
);
//这里的stu_tea就是关联表。多对多并不是说多张表对应多张表

//插入数据
insert into teacher values(1,'黄老师');
insert into teacher values(2,'李老师');
insert into teacher values(3,'赵老师');


insert into student values(1,'张三');
insert into student values(2,'李四');
insert into student values(3,'王五');
insert into student values(4,'赵六');

//这里黄老师教过张三，李四
insert into stu_tea values(1,1);
insert into stu_tea values(1,2);
//李老师教过王五，赵六
insert into stu_tea values(2,3);
insert into stu_tea values(2,4);
//赵老师教过张三，李四，王五，赵六
insert into stu_tea values(3,1);
insert into stu_tea values(3,2);
insert into stu_tea values(3,3);
insert into stu_tea values(3,4);

//上面这样，一个学生对应多个老师，一个老师也对应多个学生，叫做多对多的关系    

一对多关系 一方不变，另一方在变化

//我们一开始创建的员工表与部门表就是一对多
create table dept(
    did int primary key auto_increment,
    dname varchar(50)
);

create table emp(
    eid int primary key auto_increment,
    ename varchar(50)
    edid int,
    constraint fk_emp_dept foreign key(edid) references dept(did)
);

//这里的一就是部门表（主表），多就是员工表（附表）
//插入数据
insert into dept values(10,'技术部');
insert into dept values(20,'人事部');

//张三，李四属于技术部，王五属于人事部
insert into emp values(null,'张三',10);
insert into emp values(null,'李四',10);
insert into emp values(null,'王五',20);

//一个部门对应多个员工，一对多