python数据库之完整性约束

SDUTACM

2020-05-05

阅读目录

一、介绍

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性
主要分为：

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT                  为该字段设置默认值

UNSIGNED                无符号
ZEROFILL                  使用0填充

说明：

1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum(‘male‘,‘female‘) not null default ‘male‘
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

二、not null与default

是否可空，null表示空，非字符串
not null - 不可空
null - 可空

default默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值（注意，varchar类型default要加""双引号）

语法：
create table 表名(
字段名字段类型 defalut 默认值,
字段名字段类型
);

代码示例：

==================not null====================
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into t1 values();         #可以插入空
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>


mysql> create table t2(id int not null);            #设置字段id不为空
Query OK, 0 rows affected (0.01 sec)

mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into t2 values();          #不能插入空
ERROR 1364 (HY000): Field ‘id‘ doesn‘t have a default value
mysql>


==================default====================
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
mysql> create table t3(id int default 1);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t4(id int not null default 3);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | 1       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> desc t4;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | 3       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> insert into t3 values();
Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t4;
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

mysql>

==================综合练习====================
mysql> create table student(
    -> id int not null,
    -> name varchar(20) not null,
    -> age int unsigned not null default 18,
    -> sex enum("male","female") default "male",
    -> hobby set("play","study","read","music") default "play,music"
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| id    | int(11)                            | NO   |     | NULL       |       |
| name  | varchar(20)                        | NO   |     | NULL       |       |
| age   | int(10) unsigned                   | NO   |     | 18         |       |
| sex   | enum(‘male‘,‘female‘)              | YES  |     | male       |       |
| hobby | set(‘play‘,‘study‘,‘read‘,‘music‘) | YES  |     | play,music |       |
+-------+------------------------------------+------+-----+------------+-------+
5 rows in set (0.01 sec)

mysql> insert into student(id,name) values(1,"egon");
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+-----+------+------------+
| id | name | age | sex  | hobby      |
+----+------+-----+------+------------+
|  1 | egon |  18 | male | play,music |
+----+------+-----+------+------------+
1 row in set (0.00 sec)

mysql>

三、unique

#============设置唯一约束 UNIQUE===============
#单列唯一
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二：
使用关键字constraint:
#语法：
constraint 约束名 unique(字段名)
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)                        
);


mysql> insert into department1 values(1,‘IT‘,‘技术‘);
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,‘IT‘,‘技术‘);
ERROR 1062 (23000): Duplicate entry ‘IT‘ for key ‘name‘

#联合唯一
例如：socket传输的ip和port
"""
ip和port
单个都可以重复 但是加在一起必须是唯一的
"""
create table t4(
    id int,
    ip char(16),
    port int,
    unique(ip,port)
);
insert into t4 values(1,‘127.0.0.1‘,8080);
insert into t4 values(2,‘127.0.0.1‘,8081);
insert into t4 values(3,‘127.0.0.2‘,8080);
insert into t4 values(4,‘127.0.0.1‘,8080);  #报错

#ot null+unique的化学反应==>primary key主键的效果
mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
row in set (0.00 sec)

not null+unique的化学反应

四、primary key主键

单列主键

设置方法：

============单列做主键===============
#方法一：创建表时在某一个字段类型后用not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

#方法二：创建表时在某一个字段类型后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)

#方法三：创建表时在所有字段后单独使用constraint约束关键字定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

1.单单从约束效果上来看primary key等价于not null + unique (非空且唯一)

2.主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表（类似于书的目录），一张表中必须有且只有一个主键primary key。

一张表中有且只有一个主键如果你没有设置主键那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键

示例：

mysql> create table t1(
    -> id int,
    -> name varchar(10),
    -> age int not null unique,    #升级为主键
    -> addr varchar(32) not null unique   #不会升级为主键，被标识为单列唯一
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | NO   | PRI | NULL    |       |
| addr  | varchar(32) | NO   | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>

如果表中没有主键也没有其他任何的非空且唯一字段那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它就无法提升查询速度
一张表中通常都应该有一个主键字段并且通常将id/uid/sid字段作为主键

联合主键

#==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> (‘172.16.45.10‘,‘3306‘,‘mysqld‘),
    -> (‘172.16.45.11‘,‘3306‘,‘mariadb‘)
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values (‘172.16.45.10‘,‘3306‘,‘nginx‘);
ERROR 1062 (23000): Duplicate entry ‘172.16.45.10-3306‘ for key ‘PRIMARY‘

总结：

以后在创建表的时候id字段一定要加primary key

五、auto_increment自增

约束字段为自动增长，被约束的字段必须同时被key约束（通常用在主键id上进行自增）

# 注意auto_increment通常都是加在主键上的不能给普通字段加

create table t9(
    id int primary key auto_increment,
    name char(16),
    cid int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

示例代码：

#不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum(‘male‘,‘female‘) default ‘male‘
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum(‘male‘,‘female‘) | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> (‘egon‘),
    -> (‘alex‘)
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+


#也可以指定id
mysql> insert into student values(4,‘asb‘,‘female‘);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,‘wsb‘,‘female‘);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values(‘ysb‘);
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values(‘egon‘);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
+----+------+------+
1 row in set (0.00 sec)

总结：

"""
以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候
id int primary key auto_increment
"""

六、表与表之间建立关系：foreign key(外键)以及级联更新和级联删除

"""
表与表之间最多只有四种关系
    一对多关系
        在MySQL的关系中没有多对一一说
        一对多 多对一 都叫一对多！！！
    多对多关系
    一对一关系
    没有关系
"""

一对多关系

foreign key
一对多表关系   外键字段建在多的一方
在创建表的时候 一定要先建被关联表 
在录入数据的时候 也必须先录入被关联表

以员工表与部门表为例：

‘‘‘
先站在员工表
        思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)
            不能！！！
            (不能直接得出结论 一定要两张表都考虑完全)
    再站在部门表
        思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)
            能！！！
    得出结论
        员工表与部门表示单向的一对多
        所以表关系就是一对多
‘‘‘

一对多关系示例：

# SQL语句建立表关系
mysql> create table dep(
    -> id int primary key auto_increment,
    -> dep_name varchar(16),
    -> dep_desc varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table emp(
    -> id int primary key auto_increment,
    -> name varchar(16),
    -> gender enum(‘male‘,‘female‘,‘others‘) default ‘male‘,
    -> dep_id int,
    -> foreign key(dep_id) references dep(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into dep(dep_name,dep_desc) values(‘sb教学部‘,‘教书育人‘),(‘外交部‘,‘多人外交‘),(‘nb技术部‘,‘技术能力有限 部门‘);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into emp(name,dep_id) values(‘jason‘,2),(‘egon‘,1),(‘tank‘,1),(‘kevin‘,3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+----+-------------+--------------------------+
| id | dep_name    | dep_desc                 |
+----+-------------+--------------------------+
|  1 | sb教学部    | 教书育人                 |
|  2 | 外交部      | 多人外交                 |
|  3 | nb技术部    | 技术能力有限部门         |
+----+-------------+--------------------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      2 |
|  2 | egon  | male   |      1 |
|  3 | tank  | male   |      1 |
|  4 | kevin | male   |      3 |
+----+-------+--------+--------+
4 rows in set (0.00 sec)

mysql>

依据上面一对多关系示例：修改操作

# 修改dep表里面的id字段（发现报错，因为加了外键）
mysql> update dep set id=200 where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`userinfo_db`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

# 删除dep表里面的数据（发现报错，因为加了外键）
mysql> delete from dep;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`userinfo_db`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql>

#如果想要修改的话部门dep某一个id，要先讲emp员工表中有属于这个部门id的先删掉，或改到其他部门，再去修改dep中的id，例如想要修改部门dep中的id3改为300。
mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  2 | egon  | male   |      1 |
|  4 | kevin | male   |      3 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)

mysql> select * from dep;
+----+-------------+--------------------------+
| id | dep_name    | dep_desc                 |
+----+-------------+--------------------------+
|  1 | sb教学部    | 教书育人                 |
|  3 | nb技术部    | 技术能力有限部门         |
+----+-------------+--------------------------+
2 rows in set (0.00 sec)

mysql> update dep set id=300 where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`userinfo_db`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql> delete from emp where id=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;
+----+------+--------+--------+
| id | name | gender | dep_id |
+----+------+--------+--------+
|  2 | egon | male   |      1 |
+----+------+--------+--------+
1 row in set (0.00 sec)

mysql> update dep set id=300 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dep;
+-----+-------------+--------------------------+
| id  | dep_name    | dep_desc                 |
+-----+-------------+--------------------------+
|   1 | sb教学部    | 教书育人                 |
| 300 | nb技术部    | 技术能力有限部门         |
+-----+-------------+--------------------------+
2 rows in set (0.00 sec)

mysql>

总结：上面修改操作太过繁琐

真正做到数据之间有关系，级联更新和级联删除

更新就同步更新
删除就同步删除

"""
级联更新   >>>   同步更新
级联删除   >>>   同步删除
"""

mysql> create table dep(
    -> id int primary key auto_increment,
    ->     dep_name char(16),
    ->     dep_desc char(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table emp(
    -> id int primary key auto_increment,
    ->     name char(16),
    ->     gender enum(‘male‘,‘female‘,‘others‘) default ‘male‘,
    ->     dep_id int,
    ->     foreign key(dep_id) references dep(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into dep(dep_name,dep_desc) values(‘sb教学部‘,‘教书育人‘),(‘外交部‘,‘多人外交‘),(‘nb技术部‘,‘技术能力有限 部门‘);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into emp(name,dep_id) values(‘jason‘,2),(‘egon‘,1),(‘tank‘,1),(‘kevin‘,3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+----+-------------+--------------------------+
| id | dep_name    | dep_desc                 |
+----+-------------+--------------------------+
|  1 | sb教学部    | 教书育人                 |
|  2 | 外交部      | 多人外交                 |
|  3 | nb技术部    | 技术能力有限部门         |
+----+-------------+--------------------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      2 |
|  2 | egon  | male   |      1 |
|  3 | tank  | male   |      1 |
|  4 | kevin | male   |      3 |
+----+-------+--------+--------+
4 rows in set (0.00 sec)

mysql> update dep set id=300 where id=3;    #修改dep部门表中的id，emp中会同步更新
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dep;
+-----+-------------+--------------------------+
| id  | dep_name    | dep_desc                 |
+-----+-------------+--------------------------+
|   1 | sb教学部    | 教书育人                 |
|   2 | 外交部      | 多人外交                 |
| 300 | nb技术部    | 技术能力有限部门         |
+-----+-------------+--------------------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      2 |
|  2 | egon  | male   |      1 |
|  3 | tank  | male   |      1 |
|  4 | kevin | male   |    300 |
+----+-------+--------+--------+
4 rows in set (0.00 sec)

mysql>

多对多关系（通过建立中间表存放两张表数据之间的关系）

多对多关系举例：

"""
图书表和作者表
"""

如果还是通过一对多的方式建立这两张表（互相一对多想法）：

create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int,
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);
"""
#按照上述的方式创建 一个都别想成功！！！
#因为建立book表时，要先建立被关联表author，建立author表要先建立被关联表book，so，两个表一个也建不成。

多对多关系，我们通过建立中间表用来存储两张表数据之间的关系

注意：两个表本身并无关联，两个表和中间表有关联

mysql> create table book(
    -> id int primary key auto_increment,
    -> title varchar(32),
    -> price int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table author(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> age int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table book2author(
    -> 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      # 同步删除
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into book(title,price) values("降龙十八掌",999),("九阴真经",888),("九阳神功",666);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into author(name,age) values("egon",23),("tank",19),("jason",18);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into book2author(author_id,book_id) values(1,1),(1,2),(2,2),(2,3),(3,3),(3,1);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from book;
+----+-----------------+-------+
| id | title           | price |
+----+-----------------+-------+
|  1 | 降龙十八掌      |   999 |
|  2 | 九阴真经        |   888 |
|  3 | 九阳神功        |   666 |
+----+-----------------+-------+
3 rows in set (0.00 sec)

mysql> select * from author;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | egon  |   23 |
|  2 | tank  |   19 |
|  3 | jason |   18 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> select * from book2author;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         2 |       2 |
|  4 |         2 |       3 |
|  5 |         3 |       3 |
|  6 |         3 |       1 |
+----+-----------+---------+
6 rows in set (0.00 sec)

mysql> update book set id=100 where id=1;     #修改book表中id
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from book;
+-----+-----------------+-------+
| id  | title           | price |
+-----+-----------------+-------+
|   2 | 九阴真经        |   888 |
|   3 | 九阳神功        |   666 |
| 100 | 降龙十八掌      |   999 |
+-----+-----------------+-------+
3 rows in set (0.00 sec)

mysql> select * from author;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | egon  |   23 |
|  2 | tank  |   19 |
|  3 | jason |   18 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> select * from book2author;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |     100 |
|  2 |         1 |       2 |
|  3 |         2 |       2 |
|  4 |         2 |       3 |
|  5 |         3 |       3 |
|  6 |         3 |     100 |
+----+-----------+---------+
6 rows in set (0.00 sec)

mysql>

一对一关系

"""
id name age addr phone hobby email........
如果一个表的字段特别多 每次查询又不是所有的字段都能用得到
将表一分为二  
    用户表
        用户表
            id name age
        用户详情表
            id addr phone hobby email........
    
    站在用户表
        一个用户能否对应多个用户详情   不能！！！
    站在详情表
        一个详情能否属于多个用户      不能！！！
    结论:单向的一对多都不成立 那么这个时候两者之间的表关系
        就是一对一
        或者没有关系(好判断)

客户表和学生表
    在你们报名之前你们是客户端
    报名之后是学生(期间有一些客户不会报名)
"""

一对一示例：

一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中
#作者详情表
create table authordetail(
    id int primary key auto_increment,
    phone int,
    addr varchar(64)
);
#作者信息表
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,    #作者详情id字段为什么要唯一，因为，如果不唯一，那么随便insert一个作者，作者详情都可以互串，不合理。每个作者只能对应一个详情
    foreign key(authordetail_id) references authordetail(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
)

总结：

"""
表关系的建立需要用到foreign key
    一对多
        外键字段建在多的一方
    多对多
        自己开设第三张存储
    一对一
        建在任意一方都可以 但是推荐你建在查询频率较高的表中

判断表之间关系的方式
    换位思考！！！
        员工与部门
    
        图书与作者
    
        作者与作者详情
"""

mysql添加字段数据完整性 python key