表约束条件 非空 默认值 唯一 主键 自增 外键的三种关系
无符号 unsigned 零填充 zerofill
unsigned
id int unsigned # 是int无正负号
zerofill
id int(5) zerofill # 当int传入的数据的位数不够5位，用0填充
非空 not null
添加表数据的方法
实例t1表：
'''mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+'''
语法：
insert into t1 values(1，'cloud')
insert into t1(id) values(1);
insert into t1(name) values('cloud');
insert into t1(name,id) values('alice',2);
1.可以指定时t1表的哪个列（name），后面新增的值要跟你指定的列对应
2.没有指定的情况下,需要按顺序输入值，且不能少输值，少输入则报错：
insert into t1 values('cloud') # 报错
字段默认可以为空
# 1. 所有字段类型不加约束条件的情况下默认都可以为空
create table t1(
id int,
name varchar(16)
);
insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1(name,id) values('kevin',2);
mysql> select * from t1;
结果：
'''
+------+-------+
| id | name |
+------+-------+
| 1 | NULL |
| NULL | jason |
| 2 | kevin |
+------+-------+
3 rows in set (0.00 sec)
'''
# 2.给字段添加null
'''
mysql> insert into t1(name,id) values(NULL,NULL);
Query OK, 1 row affected (0.16 sec)
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | NULL |
| NULL | jason |
| 2 | kevin |
| NULL | NULL |
+------+-------+
4 rows in set (0.00 sec)
'''
给字段设置非空
create table t2(
id int,
name varchar(16) not null
);
insert into t2(id) values(1);
insert into t2(name) values('jason');
insert into t2 values(1,'');
insert into t2 values(2,null);
在字段name后面加入约束条件not null：
验证 是否真的能不传值：
- 我们给name设置了非空 不给name传值会报错
- 给name传值 不给id传值 没问题
- 传空字符串也算传值 不会报错！！！（备忘）
- name无法插入Null值
默认值 default
create table t3(
id int default 666,
name varchar(16) default '默认'
);
insert into t3(id) values(1);
insert into t3(name) values('jason');
insert into t3 values(2,'kevin');
可以在建立表的时候给某个字段设置默认值，查看default属性：
在插入数据的时候，如果你不输入全，那默认值会帮你补全：
默认值配合非空使用
如果你输入null的情况,表中会显示null,而不是默认值：
默认值配合非空使用：
输入null会报错：
唯一值 unique
单列唯一
意思是给name这个字段存储的值不能有重复的，就是不能有重复的姓名：
添加重复的姓名时会报错：
联合唯一
这里举的是ip 加端口号的例子，不能出现ip和端口都相同的情况，如果这两个值都相同就报错。
注意这个unique是写在逗号后面的 而不是和字段类型连写。
查看完整版代码
MUL的意思是复合约束 下面显示的是完整版的语句
主键 primary key
非空且唯一
1.单从约束层面上而言主键相当于not null + unique(非空且唯一)
主键的key值：
有且只有一个主键
- InnoDB存储引擎规定了所有的表都必须有且只有一个主键
- 主键是组织数据的重要条件并且主键可以加快数据的查询速度
被隐藏的主键
当你不主动创建主键的时候，难道表中就没有主键吗？
不是的，innoDB规定所有表都必须有且只有一个主键。
你不创建的情况下，会进行默认创建主键，并且这个主键是隐藏的，你看不见摸不着。
第二，隐藏意味着无法使用，这会导致数据查询的速度极慢。
自动升级主键
当表中没有主键但是有其他非空且唯一的字段 那么会从上往下将第一个该字段自动升级为主键：
自定义主键
"""
我们在创建表的时候应该有一个字段用来标识数据的唯一性 并且该字段通常情况下就是'id'(编号)字段
id nid sid pid gid uid
create table userinfo(
uid int primary key,
);
"""
约束条件 自增 auto_increment
前言：我们的主键不太智能，
比如在数据很多的情况下，我们很有可能记不清楚id写到哪了 或者说每次插入都要写id编号很麻烦.应该是我们不需要写id号，id号自动生成，我们只需要录入数据值就好。自增就可以做到这样的效果。
自增是个约束条件，它主要用于配合主键一起使用
注意：
1.该约束条件不能单独出现
2.一张表只能出现一次
代码实现：
自增初使用，无需输入id号：
注意：使用 insert into t9 values(1,'cloud') 这种写法，还是要写id值的！
自增有很多的特性，需要了解。
自增不会回退
自增不会因为数据的删除而回退 永远自增往前
这样是有好处的，因为有人进行数据的删除操作，我们一眼就会发现。
验证：
再次添加数据：
插入值 < 自增位置 不会改变自增位置
自增位置的概念就是你现在自增到哪了，每插入一个数字id加1。
是允许插入指定的id号的，但是不会更改自增的起始位置。
在id=4插入数值：
新增一个数据，他的id=10：
插入值 > 自增位置 按照插入值的位置自增
插入id=1000的数据：
自增位置改变：
清除表后 自增还是记得
清除表 同时清除自增的计数 truncat
错误的插入也会导致自增
外键前戏
员工表的不足
- 表的语义不明确
- 存取数据很麻烦 存相同的数据还要重复输入 重复的数据还要存储 浪费空间
- 数据的扩展性很差 修改数据很麻烦 例如给讲师部换名字
问题解决
将上述表一分为二：
上述的三个问题全部解决 但是员工跟部门之间没有关系了
此时就需要新增一个字段建立两个表之间的联系 这个字段就叫 外键字段 简称外键
外键字段:用于标识数据与数据之间关系的字段
注意，不能写不存在的外键：
只能写被关联表的id字段出现过的值1、2、3
外键用数字存储 是因为 占用存储空间小
表之间关系的判断
表关系、数据关系其实意思是一样的 知识说法上有区分
关系总共有四种
一对多
多对多
一对一
没有关系
关系的判断可以采用'换位思考'原则
一对多关系
以员工表和部门表为例
1.先站在员工表的角度
问:一名员工能否对应多个部门
答:不可以
2.再站在部门表的角度
问:一个部门能否对应多名员工
答:可以
结论:一个可以一个不可以 那么关系就是'一对多'
针对'一对多'关系 外键字段建在'多'的一方
建议：外键字段建立在数据多的一方：员工
外键字段的建立 foreign key(field) references table(field)
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
);
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
语法: foreign key（外键字段名）references 表名（被关联字段名）
查看外键的key值
注意事项：
1. 创建表的时候一定要先创建被关联表
2. 录入表数据的时候一定要先录入被关联表
录入也要先录入被关联表！！
比如一个员工表和部门表，部门表就是被关联表，外键放在员工表。
所以录入的时候就要先录入部门表，没有部门何谈员工。
3. 修改数据的时候外键字段无法修改和删除
- 构建表
- 删除值，直接报错，无法删除
当外键字段：部门下面没有员工时 此时可以将部门外键字段删除。
级联更新 级联删除 on update cascad on delete cascade
create table emp1(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade
on delete cascade
);
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
- 生成两张表
- 你删我也删
- 你改我也改
总结
外键 >>>> 建立关系 >>>> 强耦合
外键建立多了 会很慢 牵一发动全身 可能发生未知结果
我们以后表较多的时候 不会使用外键 而是使用代码建立逻辑层面上的关系。
删员工部门会没吗？
- 创建表 由于我创建错了两次所以自增了
- 删除员工 部门还在
- 删除部门 员工没了
这可能是因为外键放在员工表，如果外键放在部门是否会不同？
多对多关系
外键放在第三张表 第三张表要对其余两张表关联
以书籍表与作者表为例
1.先站在书籍表的角度
问:一本书能否对应多个作者
答:可以
2.再站在作者表的角度
问:一个作者能否对应多本书
答:可以
结论:两个都可以 关系就是'多对多'
针对'多对多'不能在表中直接创建 需要新建第三张关系表
create table book(
id int primary key auto_increment,
title varchar(32),
price float(5,2)
);
create table author(
id int primary key auto_increment,
name varchar(32),
phone bigint
);
create table book2author(
id int primary key auto_increment,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
推导流程
- 书籍表和作者表
- 外键怎么写？两张表都得写外键字段
针对多对多 需要第三张表为什么？
因为创表都创不出来！被关联表需要先创建。 - 用第三张表来存储数据的外键
- 注意书有两个作者时 第三章表要记录两次
同样第三张表也不能出现前两张表没出现的id
代码
第三张表的创建：
游离的数据 比如书籍的价格 未被第三张表绑定：
一对一
别忘了unique！这是和一对多的唯一区别
以用户表与用户详情表为例
1.先站在用户表的角度
问:一个用户能否对应多个用户详情
答:不可以
2.再站在用户详情表的角度
问:一个用户详情能否对应多个用户
答:不可以
结论:两个都可以 关系就是'一对一'或者没有关系
针对'一对一'外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中
create table user(
id int primary key auto_increment,
name varchar(32),
detail_id int unique,
foreign key(detail_id) references userdetail(id)
on update cascade
on delete cascade
);
create table userdetail(
id int primary key auto_increment,
phone bigint
);
推导流程
将qq数据拆成 用户表和用户详情表：
所以这两个表是 一对一 关系
一对一 外键字段建在任何一方都可以
但是建议建在查询频率最高的一方 ---> 用户表
代码实现
- 创建表
一对一不能重复绑定外键（重要）（最复杂版本）
不符合要求：因为一对一不能重复绑定
对于jason，应该只对应id=1,也就只对应他的详细数据。别的人不能对应id=1。
所以我们希望外键，用户表的外键不能重复：
用了一次就不能在也不能用了！所以要用unique
注意，这里只有两个约束条件，后面的一长串是一个约束条件。
练习
有些表数据关系不是确定 根据具体业务可能有变化
服务器表与应用程序表 # 一对多
课程表与班级表 # 多对多
学生表与班级表 # 一对多
老师表与课程表 # 多对多
书籍表与出版社表 # 一对多
用户表与用户信息详表 #一对一
学生表与乘法表 # 没有关系
补充
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-redefine-column
修改表, 需要加上属性
[db2] 10:17:39> alter table t1 modify age int not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
[test]> alter table t1 modify name char(22) not null unique;
change和modify
目前我知道的change只能修改表名
[db2] 10:27:55> desc t1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | NO | UNI | NULL | |
| id_1 | int | NO | PRI | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)
[db2] 10:28:12> alter table t1 change id_1 id_2 int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
[db2] 10:28:22> desc t1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| id_2 | int | YES | UNI | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
