Mysql外键对应关系

一.外键:
foreign key 注意:
1.被关联的字段必须是一个key,通常是id字段

2.创建表时:必须先建立被关联的表,才能建立关联表
外键:
constraint 外键名字 (可以不写,不写系统会自动分配)。
foreign key (当前表需要关联的id,直接写自己设置的id)
references 被关联的表名(被关联表的id)

3.插入记录时:必须先往被关联的表插入记录,才能往关联表中插入记录
删除时:应该先删除关联表中的记录,再删除被关联对应的记录

4.更新和删除同步:on update cascade on delete cascade

5.必须先删除关联的表 才能删被关联的表
二.下面举例说明:
多对一
1.先创建被关联表:

create table dep(
      id int primary key auto_increment,
      dep_name varchar(20),
      dep_info varchar(20)
                );

2.创建关联表:

create table emp(
      id int primary key auto_increment,
      name varchar(20),
      age int,
      dep_id int,
      constraint fk_emp_dep
      foreign key(dep_id) references dep(id)
      on update cascade on delete cascade);

3.插入被关联表的内容:

insert into dep(dep_name,dep_info)values
      ('python','python_course'),
      ('music','music_course'),
      ('java','java_course');

4.插入关联表内容:

insert into emp(name,age,dep_id) values
      ('dahai',18,1),
      ('xishi',19,2),
      ('zhuge',23,3),
      ('xialuo',24,1),
      ('zhouyu',21,3);

5.查询:

select * from emp,dep where emp.dep_id=dep.id;

+----+--------+------+--------+----+----------+---------------+
| id | name   | age  | dep_id | id | dep_name | dep_info      |
+----+--------+------+--------+----+----------+---------------+
|  1 | dahai  |   18 |      1 |  1 | python   | python_course |
|  4 | xialuo |   24 |      1 |  1 | python   | python_course |
|  2 | xishi  |   19 |      2 |  2 | music    | music_course  |
|  3 | zuge   |   23 |      3 |  3 | java     | java_course   |
|  5 | zhouyu |   21 |      3 |  3 | java     | java_course   |
+----+--------+------+--------+----+----------+---------------+

2.多对多Mysql外键对应关系

关系:
author 多对多 book
author2book 多对一 author
author2book 多对一 book

解决办法:
1.先创建两个被关联表

create table author(
        id int primary key auto_increment,
        name varchar(16),
        age int
                    );

create table book(
        id int primary key auto_increment,
        bname varchar(20),
        price int
                 );

2.创建关联表并删改同步:

create table author2book(
        id int primary key auto_increment,
        author_id int,
        bool_id int,
        unique key(author_id,book_id),
        foreign key(author_id) references author(id)
        on update cascade on delete cascade,
        foreign key(book_id) references book(id)
        on update cascade on delete cascade
                        );

3.插入被关联表数据

insert into author(name,age)values
        ('dahai',22),
        ('xialuo',23),
        ('guan',18),
        ('xishi',19),
        ('jiujiu',20);

insert into book(bname,price)values
        ('玉女真经',5),
        ('九阳神功',3),
        ('太极拳',4),
        ('如来神掌',2),
        ('玉女剑法',6);

4.插入关联表数据

insert into author2book(author_id,book_id) values
        (1,1),
        (1,2),
        (1,3),
        (2,3),
        (2,4),
        (3,2),
        (3,3),
        (3,4),
        (4,3),
        (5,2);

5.查询:

select * from author,book,author2book where author.id=author2book.author_id and book.id=author2book.book_id;

结果这里就不展示了

3.一对一(主键id当外键)
1.创建表

create table student(
        id int primary key,
        name varchar(10)
                    );


create table stu_detail(
        s_id int primary key,
        age int,
        sex  enum('man','woman') not null,
        foreign key(s_id) references student(id)
        on update cascade
        on delete cascade
                     );

插入数据

 insert into student values
    (1,'dahai'),
    (2,'xialuo'),
    (3,'xishi');

    insert into stu_detail values
    (1,18,'man'),
    (2,18,'man'),
    (3,18,'woman');



 select * from student,stu_detail where student.id = stu_detail.s_id;


+----+--------+------+------+-------+
| id | name   | s_id | age  | sex   |
+----+--------+------+------+-------+
|  1 | dahai  |    1 |   18 | man   |
|  2 | xialuo |    2 |   18 | man   |
|  3 | xishi  |    3 |   18 | woman |
+----+--------+------+------+-------+
上一篇:图数据结构——邻接表


下一篇:13 myBatis-05-高级ResultMap