数据库（字段的约束条件，表之间的关系）(2)

当前位置:

首页 > Python基础教程 >

数据库（字段的约束条件，表之间的关系）(2)

'), ('wxx'); mysql> select * from author; +----+------+ | id | name | +----+------+ | 1 | egon | | 2 | alex | | 3 | wxx | +----+------+ insert into book(bname,price) values ('python从入门到入土',200), ('葵花宝典切割到精通',800), ('九阴真经',500), ('九阳神功',100); mysql> select * from book; +----+-----------------------------+-------+ | id | bname | price | +----+-----------------------------+-------+ | 1 | python从入门到入土 | 200 | | 2 | 葵花宝典切割到精通 | 800 | | 3 | 九阴真经 | 500 | | 4 | 九阳神功 | 100 | +----+-----------------------------+-------+ create table author2book( id int primary key auto_increment, author_id int, book_id int, 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); insert into author2book(author_id,book_id) values (1,3),(1,4),(2,2),(2,4),(3,1),(3,2),(3,3),(3,4); mysql> select * from author2book; +----+-----------+---------+ | id | author_id | book_id | +----+-----------+---------+ | 1 | 1 | 3 | | 2 | 1 | 4 | | 3 | 2 | 2 | | 4 | 2 | 4 | | 5 | 3 | 1 | | 6 | 3 | 2 | | 7 | 3 | 3 | | 8 | 3 | 4 | +----+-----------+---------+

10.73 一对一(unique+foreign key)

一对一：左表的一条记录唯一对应右表的一条记录，反之也一样

create table customer(                              #先建被参照的表
    id int primary key auto_increment,
    name char(20) not null,
    qq char(10) not null,
    phone char(16) not null);
create table student(
    id int primary key auto_increment,
    class_name char(20) not null,
    customer_id int unique,                        #该字段一定要是唯一的,一对一
    foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
    on delete cascade on update cascade);
insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230);
mysql> select * from customer;
+----+-----------+-----------+-------------+
| id | name      | qq        | phone       |
+----+-----------+-----------+-------------+
|  1 | 李飞机    | 31811231  | 13811341220 |
|  2 | 王大炮    | 123123123 | 15213146809 |
|  3 | 守榴弹    | 283818181 | 1867141331  |
|  4 | 吴坦克    | 283818181 | 1851143312  |
|  5 | 赢火箭    | 888818181 | 1861243314  |
|  6 | 战地雷    | 112312312 | 18811431230 |
+----+-----------+-----------+-------------+
insert into student(class_name,customer_id) values
('python',3),('java',4),('c++',5);
mysql> select * from student;
+----+-------------+-------------+
| id | class_name  | customer_id |
+----+-------------+-------------+
|  1 | python     |           3 |
|  2 | java       |           4 |
|  3 | c++        |           5 |
+----+-------------+-------------+

栏目列表