Python开发-040_mysql表的关系
1 约束条件
# 补充知识点： 插入数据的时候可以指定字段
create table t1(
id int,
name char(16)
);
# 按照t1(name,id)的顺序进行插值
# 如果括号内位空 则按照默认定义表格的顺序
insert into t1(name,id) values('kinght',1);
1.1 default 默认值
顾名思义，即在插入值时，不填写相应字段，而自动获取的默认值
create table t2(
id int,
name char(16) not null,
gender enum('male','female','other') default 'male'
);
MariaDB [demo]> desc t2;
+--------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | NO | | NULL | |
| gender | enum('male','female','other') | YES | | male | |
+--------+-------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [demo]> insert into t2(id,name) values(1,'kinght'); # gender默认值male
MariaDB [demo]> insert into t2(id,name,gender) values(2,'AYM','female');
MariaDB [demo]> select * from t2;
+------+--------+--------+
| id | name | gender |
+------+--------+--------+
| 1 | kinght | male |
| 2 | AYM | female |
+------+--------+--------+
1.2 unique 唯一
在数据表中有很多数据应该只能出现一次，例如ID，在进行数据插入的时候却可以重复插入
MariaDB [demo]> insert into t2(id,name) values(1,'kinght');
MariaDB [demo]> insert into t2(id,name,gender) values(1,'AYM','female');
# id可以重复，不会报错
MariaDB [demo]> select * from t2;
+------+--------+--------+
| id | name | gender |
+------+--------+--------+
| 1 | kinght | male |
| 2 | AYM | female |
| 1 | kinght | male |
| 1 | AYM | female |
+------+--------+--------+
4 rows in set (0.00 sec)
我们可以将id添加一个唯一限制，让他不能出现相同的数值
# 唯一分两种情况
## 单列唯一 某一字段内不能有重复的值
create table t3(
id int unique,
name char(16)
);
MariaDB [demo]> desc t3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [demo]> insert into t3 values(1,'aaaa'),(1,'bbbb');
ERROR 1062 (23000): Duplicate entry '1' for key 'id' # 报错
## 联合唯一 字段分组联合 单个都可以组合，但是组合起来必须唯一
### 例如：IP字段和端口号字段 IP可以重复 端口号也可以重复 但是同一行不能出现IP和端口都重复
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(1,'127.0.0.1',80);
insert into t4 values(1,'192.168.0.1',8080);
insert into t4 values(1,'127.0.0.1',8080); # 报错
MariaDB [demo]> select * from t4;
+------+-------------+------+
| id | ip | port |
+------+-------------+------+
| 1 | 127.0.0.1 | 8080 |
| 1 | 127.0.0.1 | 80 |
| 1 | 192.168.0.1 | 8080 |
+------+-------------+------+
3 rows in set (0.00 sec)
MariaDB [demo]> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| ip | char(16) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
1.3 primary key 主键
从约束效果来看，primary key等价于not null + unique 非空且单列唯一
create table t5(
id int primary key
); # 设置主键
MariaDB [demo]> desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | | # key=PRI 为主键
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [demo]> insert into t5 values(null); #报错
MariaDB [demo]> insert into t5 values(1),(2);
MariaDB [demo]> insert into t5 values(1),(1); #报错
MariaDB [demo]> select * from t5;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
它除了有约束效果之外，它还是Innodb存储引擎组织数据的依据
# Innodb存储引擎在创建表的时候必须要有primary key
# 因为它类似于书的目录 能够帮助提升查询效率且也是建表的依据
# 没有主键的处理办法
# 1.一张表中有且只有一个主键 如果没有人为设置 会自上而下搜索一个非空且唯一的字段升级为主键
MariaDB [demo]> create table t6(
-> id int,
-> name char(16),
-> age int not null unique,
-> addr char(32) not null unique
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [demo]> desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
| age | int(11) | NO | PRI | NULL | | # KEY=PRI 为主键
| addr | char(32) | NO | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 2.如果表内也没有其他任何非空且唯一的字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键进行创建表格(应急机制) 但同时也意味着无法使用到它 无法提升查询速度
# 3.一张表中通常都应该有一个主键字段 并通常使用id字段作为主键
# 4.主键 可以使多个字段联合起来作为主键
create table t7(
ip char(16),
port int,
primary key(ip,port) # 设置联合主键
);
MariaDB [demo]> desc t7;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip | char(16) | NO | PRI | | |
| port | int(11) | NO | PRI | 0 | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1.4 auto_increment 自增
当编号特别多的时候 人为维护太繁琐
create table t8(
id int primary key auto_increment, # 设置id为主键且自增
name char(16)
);
MariaDB [demo]> insert into t8(name) values('kinght'),('AYM'),('alert');
MariaDB [demo]> select * from t8;
+----+--------+
| id | name |
+----+--------+
| 1 | kinght |
| 2 | AYM |
| 3 | alert |
+----+--------+
3 rows in set (0.01 sec)
注意：auto_increment通常都是加在主键上的 不能给普通字段加
auto_increment内部自增 就算把之前的数据删除 auto_increment也不会从0开始算
MariaDB [demo]> select * from t8;
+----+--------+
| id | name |
+----+--------+
| 1 | kinght |
| 2 | AYM |
| 3 | alert |
+----+--------+
3 rows in set (0.00 sec)
MariaDB [demo]> delete from t8;
MariaDB [demo]> insert into t8(name) value('aaa');
MariaDB [demo]> select * from t8;
+----+------+
| id | name |
+----+------+
| 4 | aaa |
+----+------+
1 row in set (0.00 sec)
# 使用truncate t8 清空表数据且重置主键
MariaDB [demo]> truncate t8;
MariaDB [demo]> insert into t8(name) value('bbb');
MariaDB [demo]> select * from t8;
+----+------+
| id | name |
+----+------+
| 1 | bbb |
+----+------+
1 row in set (0.00 sec)
2 foreign key 外键
关系型数据库除了表内关系，表与表之间的关系也尤为重要
就以在校学生而言 学生的个人信息表 社团的会员表 年级学生表 都应该会有他的个人信息，如果要修改一次个人信息，需要去这么多表进行重复修改未免工作量过大，所以想通的信息部分可以使用外键进行关系约束
在进行外键链接之前，一定要判断表与表质检的关系
2.1 一对多关系
以学生表和班级表为例，一个学生只能对应一个班级 一条学生表数据只能对应班级某一条数据。一个班级可以对应多个学生 一条班级数据能对应多条学生数据
即认为 班级表和学生表存在一对多关系
2.1.1 定义一对多关系
# 注意：
# 一对多关系 外键建立在多的一方 即 此案例学生表内
# 在创建表的时候 一定要先建立被关联表
# 录入数据的时候 也必须先录入被关联表
create table Class(
id int primary key auto_increment,
cls_name char(16), #班级名称
cls_desc char(32) #班级描述
);
create table student(
id int primary key auto_increment,
name char(16),
gender enum('male','female','other') default 'male',
cls_id int,
foreign key(cls_id) references Class(id) # 外键关联
);
insert into student(name,cls_id) values('kinght',1); # 插入报错
# ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demo`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cls_id`) REFERENCES `Class` (`id`))
# 需要先插入被关联的键 即班级表
MariaDB [demo]> insert into Class(cls_name,cls_desc) values('Information security','信息安全'),('Computer science','计算机科学'),('Software development','软件开发');
MariaDB [demo]> select * from Class;
+----+------------------+-----------------+
| id | cls_name | cls_desc |
+----+------------------+-----------------+
| 1 | Information secu | 信息安全 |
| 2 | Computer science | 计算机科学 |
| 3 | Software develop | 软件开发 |
+----+------------------+-----------------+
3 rows in set (0.00 sec)
# 现在就可以向学生表插入数据了
MariaDB [demo]> insert into student(name,cls_id) values('kinght',1);
MariaDB [demo]> insert into student(name,gender,cls_id) values('aym','female',3);
MariaDB [demo]> select * from student;
+----+--------+--------+--------+
| id | name | gender | cls_id |
+----+--------+--------+--------+
| 2 | kinght | male | 1 |
| 3 | aym | female | 3 |
+----+--------+--------+--------+
2 rows in set (0.00 sec)
2.1.2 被关联表不能随意修改数据
例如：学生一旦加入班级，班级表中有学生表关联数据的行就不能随意修改数据，否则学生表的外键规则就会冲突
# 没有学生关联数据的行还是可以修改的
MariaDB [demo]> update Class set id=200 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 有学生关联数据的行不能被修改
MariaDB [demo]> update Class set id=100 where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cls_id`) REFERENCES `Class` (`id`))
# 也不能被删除
MariaDB [demo]> delete from Class where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cls_id`) REFERENCES `Class` (`id`))
想要修改有关联数据的行有两种办法
# 方案一 删除关联方数据 再修改被关联方
MariaDB [demo]> delete from student where id=2;
Query OK, 1 row affected (0.00 sec)
MariaDB [demo]> select * from student;
+----+------+--------+--------+
| id | name | gender | cls_id |
+----+------+--------+--------+
| 3 | aym | female | 3 |
+----+------+--------+--------+
1 row in set (0.00 sec)
MariaDB [demo]> update Class set id=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 方案二 同步修改关联方与被关联方数据
# 更新即同步更新 级联更新
# 删除即同步删除 级联删除
2.1.3 级联更新 级联删除
在定义关联表语句的时候，需要添加语句 级联更新on update cascade 和 级联删除 on delete cascade
#删除数据表student减少影响
MariaDB [demo]> drop table student;
# 在定义学生表的时候将语句添加进去
create table User(
id int primary key auto_increment,
name char(16),
gender enum('male','female','other') default 'male',
Class_id int,
foreign key(Class_id) references Class(id)
on update cascade
on delete cascade
# 此语句没有使用逗号 原则本应该是一行 为了美观可以分行写
);
# 现在Class表状态
MariaDB [demo]> select * from Class;
+-----+------------------+-----------------+
| id | cls_name | cls_desc |
+-----+------------------+-----------------+
| 3 | Software develop | 软件开发 |
| 100 | Information secu | 信息安全 |
| 200 | Computer science | 计算机科学 |
+-----+------------------+-----------------+
3 rows in set (0.00 sec)
# User表插入数据
MariaDB [demo]> insert into User(name,Class_id) values('aaa',3);
MariaDB [demo]> select * from User;
+----+------+--------+----------+
| id | name | gender | Class_id |
+----+------+--------+----------+
| 1 | aaa | male | 3 |
+----+------+--------+----------+
1 row in set (0.00 sec)
# 修改class表
MariaDB [demo]> update Class set id=300 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# User表和Class表都改变了
MariaDB [demo]> select * from User;
+----+------+--------+----------+
| id | name | gender | Class_id |
+----+------+--------+----------+
| 1 | aaa | male | 300 |
+----+------+--------+----------+
1 row in set (0.00 sec)
MariaDB [demo]> select * from Class;
+-----+------------------+-----------------+
| id | cls_name | cls_desc |
+-----+------------------+-----------------+
| 100 | Information secu | 信息安全 |
| 200 | Computer science | 计算机科学 |
| 300 | Software develop | 软件开发 |
+-----+------------------+-----------------+
3 rows in set (0.00 sec)
2.2 多对多关系
以书籍表和作者表为例，一本书可以由多个作者联合发布，一个作者也可以发布多本书
所以按照关系逻辑，书和作者应该属双向对应的关系
# 按照理解创建表
#### 注意 此表错误
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
);
问题：之前提到过创建关联之前需要先创建被关联表，但是这两个表互相关联，陷入死循环
2.2.1 拆分多对多关系
解决方案：针对多对多表关系 不能在原有的两张表中创建外键 需要再单独开设一张表 专门存储两张表的关系 将多对多拆分为两个一对多
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
# 建立第三方关联表
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
);
# 插入数据
MariaDB [demo]> insert into author(name,age) values('kinght',25),('AYM',24),('amber',23);
MariaDB [demo]> insert into book(title,price) values('Mysql从删库到跑路',103),('渗透的入狱指南',99),('sir抓错人了我只是
安服仔',71);
# 插入关联数据
MariaDB [demo]> select * from book;
+----+-----------------------------------+-------+
| id | title | price |
+----+-----------------------------------+-------+
| 1 | Mysql从删库到跑路 | 103 |
| 2 | 渗透的入狱指南 | 99 |
| 3 | sir抓错人了我只是安服仔 | 71 |
+----+-----------------------------------+-------+
3 rows in set (0.00 sec)
MariaDB [demo]> select * from author;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | kinght | 25 |
| 2 | AYM | 24 |
| 3 | amber | 23 |
+----+--------+------+
3 rows in set (0.00 sec)
MariaDB [demo]> insert into book2author(author_id,book_id) values(3,2),(2,1),(1,3);
2.2.2 修改表数据
被拆分之后，表数据其实是和中间表做的关联
# 修改作者表
MariaDB [demo]> insert into book2author(author_id,book_id) values(3,2),(2,1),(1,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [demo]> delete from book where id=3;
# 中间表也会随之改变
MariaDB [demo]> select * from book2author;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 3 | 2 |
| 2 | 2 | 1 |
+----+-----------+---------+
2 rows in set (0.00 sec)
2.3 一对一关系
如果一个表中字段非常多，而不是每一个字段每次都需要用到，为了减少数据库的压力，可以使用一对一关系
# 例如 ：
用户数据字段具备 UID 账号名 密码 姓名 年龄 电话号码 邮箱号码 星座等字段
而实际登录只需要 UID 账户名 密码 其他的数据需要点击更多详情才会查询
则可以把他们分为两个表进行 用户表 用户详情表
在一对一情况下，外键字段可以建立在任何一方，但是推荐建立在查询频率比较高的表中
# 由于用户表查询比较多 外键在用户表
# 首先建立详情表
create table authordetail(
uid int primary key auto_increment,
uname varchar(32),
age int,
phone_number varchar(11),
email varchar(64),
constellation varchar(32)
);
# 建立用户表
create table author(
uid int primary key auto_increment,
name varchar(32),
passwd varchar(64),
authordetail_id int unique, # 一对一 不能有重复字段出现
foreign key(authordetail_id) references authordetail(uid)
on update cascade
on delete cascade
);
3 修改表
mysql对大小写不敏感
3.1 修改表名
alter table 表名 rename 新表名;
# 演示
MariaDB [demo]> alter table t8 rename a11;
3.2 增加字段
# 默认添加到最后
alter table 表名 add 字段名 字段类型(宽度) 约束条件;
# 添加到最前面
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first;
# 在某字段后面添加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;
# 演示
MariaDB [demo]> alter table t1 add passwd varchar(64);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [demo]> desc t1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
| passwd | varchar(64) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
3.3 删除字段
alter table 表名 drop 字段名;
# 演示
MariaDB [demo]> alter table t1 drop passwd;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [demo]> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
3.4 修改字段
# 不修改字段名
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
# 连修改字段名一起改
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
# 演示
MariaDB [demo]> alter table t1 change id uid int primary key auto_increment;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [demo]> desc t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| uid | int(11) | NO | PRI | NULL | auto_increment |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
4 复制表的结构和数据
SQL语句执行查询的时候会输出一张表，可以通过那张虚拟表创建一个新的表，但是新的表只有表结构和数据并没有键
create table 表名 select * from 旧表;
# 演示
MariaDB [demo]> select * from book;
+----+-------------------------+-------+
| id | title | price |
+----+-------------------------+-------+
| 1 | Mysql从删库到跑路 | 103 |
| 2 | 渗透的入狱指南 | 99 |
+----+-------------------------+-------+
2 rows in set (0.00 sec)
MariaDB [demo]> create table book_new select * from book;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [demo]> select * from book_new;
+----+-------------------------+-------+
| id | title | price |
+----+-------------------------+-------+
| 1 | Mysql从删库到跑路 | 103 |
| 2 | 渗透的入狱指南 | 99 |
+----+-------------------------+-------+
2 rows in set (0.00 sec)
# 没有各种键
MariaDB [demo]> desc book;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(32) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [demo]> desc book_new;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| title | varchar(32) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 加条件拷贝
create table 表名 select * from 旧表 where 条件;
# 演示
MariaDB [demo]> create table book_new2 select * from book where id=2;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [demo]> select * from book_new2;
+----+-----------------------+-------+
| id | title | price |
+----+-----------------------+-------+
| 2 | 渗透的入狱指南 | 99 |
+----+-----------------------+-------+
1 row in set (0.01 sec)
Python开发-040_mysql表的关系
http://localhost:8080/archives/mZiK7pss