约束条件
无符号
unsigned 加在字段类型的后面
可以取消正负号
零填充
zerofill
用零填充
id int(5) zerofill
非空
输入的值不能为空 "not null"
create table t3(
id int not null,
name varchar(32)
);
insert into t3(name) values('tank');
# Field 'id' doesn't have a default value 字段` id `没有默认值
insert into t3(id) values(1);
+----+------+
| id | name |
+----+------+
| 1 | NULL |
+----+------+
name 为Null 就是空，得知如果不加以限制默认是空
-----------------------------------------------------------------------------------
我们查看该表信息
desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
可以看到该表的id的Null显示NO 就是不可以为空
默认值
"default "
create table t1(
id int default 6,
name varchar default 'tank');
insert into t1(id) values(1);
insert into t1(name) values('lxj');
insert into t1 values(2,'baba');
+------+------+
| id | name |
+------+------+
| 1 | tank |
| 6 | lxj |
| 2 | baba |
+------+------+
可以看出 没有输入的值则会默认使用我们设定的默认值
查看该表信息
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | 6 | |
| name | varchar(32) | YES | | tank | |
+-------+-------------+------+-----+---------+-------+
default 下的默认值从null变成了我们设置的值
唯一值
"单列唯一 unique "
"顾名思义 用该约束条件设置的字段 的数据只能是唯一，不能重复。"
create table t1(
id int unique,
name varchar(32) unique);
insert into t1 values(1,'jason');
insert into t1 values(1,'tank');
# 对于键'id'，重复条目'1'
+------+------+
| id | name |
+------+------+
| 1 | tank |
+------+------+
查看 表信息
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(32) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
key 下 UNI 唯一
====================================================================================
"""多列唯一
对于使用约束条件unique 的字段组合在一起的结果 不能重复 """
create table t1(
id int,
name varchar(32),
ip varchar(32),
port int,
unique(ip,port)); # ip与端口号 组合 约束
insert into t1 values(1,'tank','192.168.1.88',1433);
insert into t1 vaalues(2,'jack','192.168.1.88',1433);
# 报错
insert into t1 values(2,'jack','192.168.1.87',1433);
+------+------+--------------+------+
| id | name | ip | port |
+------+------+--------------+------+
| 1 | tank | 192.168.1.88 | 1433 |
| 2 | jack | 192.168.1.87 | 1433 |
+------+------+--------------+------+
查看表信息
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| ip | varchar(32) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
key 下 MUL 多列唯一
*主键
primary key
1.但从约束层面上讲 主键相当于not null + unique 非空且唯一
create table t1(
id int primary key,
name varchar(32));
查看表信息
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
null 为NO 非空， PRI 主键必须是唯一。
====================================================================================
2.是InnoDB存储引擎规定的一张表有且必须要有一个主键
(主键是组织数据的重要条件并且主键可以加快数据的查询速度)
如果创建表创建的时候没有设置主键也没有其他的键 InnoDB会采用一个隐藏的字段作为表的主键 隐藏意味着无法使用 基于该表的数据查询只能一行行查找 速度很慢
如果没有主键但是有非空且唯一的字段 那么会自动升级成主键(从上往下的第一个)
create table t1(
id int,
name varchar(32) not null unique,
phone bigint not null unique
);
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | NO | PRI | NULL | |
| phone | bigint(20) | NO | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
可以看出 name 被升级成了主键
自增
auto_increment
该约束条件配合主键一起使用，不能单独出现。一张表只能出现一次
create table t1(
id int primary key auto_increment,
name varchar(32));
insert into t1(name) values('jack');
insert into t1(name) values('tank');
insert into t1(name) values('jerry');
+----+-------+
| id | name |
+----+-------+
| 1 | jack |
| 2 | tank |
| 3 | jerry |
+----+-------+
我们并没有添加id 数据,可以看出 id 是自增的
查看表数据
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
可以看到 id 后的 Extra属性 auto_increment
'''
自增不会随着数据的删除而后退，只会从原来在哪就直接在哪开始增加 ,可以手动增加 但是没啥用下次还会从原来的地方开始增加
如果想重置某张表的主键值 可以使用
truncate t1; 清空表数据并重置主键
等于格式化了这个表数据全没了
'''
delete from t1 where id=3;
+----+------+
| id | name |
+----+------+
| 1 | jack |
| 2 | tank |
+----+------+
insert into t1(name) values('zyg');
+----+------+
| id | name |
+----+------+
| 1 | jack |
| 2 | tank |
| 4 | zyg |
+----+------+
可以看出 并没有按照2开始自增而是从我们删除的3开始自增。
外键简介
外键字段就是用来记录表与表之间的数据的关系
# 外键前戏
创建一个员工表
id name age dep_name dep_desc
1 owen 20 外交部 搞外交
2 tom 19 教育部 教学
3 avin 21 外交部 搞外交
4 Gavin 24 教育部 教学
# 表格缺陷
1、表的重点不清晰
因为这是员工表，你也可以说这是部门表
2、表中的部门名和职责的数据都一直在重复
浪费存储空间
3、表的扩展性极差
# 解决措施
将表格分为两个
员工表
id name age
部门表
id dep_name dep_desc
# 解决了上面的三个问题，但是有一个致命问题，部门和员工的关系怎么绑定
措施：
在员工表中增加一个部门编号字段与部门表中的主键字段对应
"""
这个字段就是外键字段
外键字段就是用来记录表与表之间的数据的关系
"""
关系的判断
# 关系种类
1、一对多
2、多对多
3、一对一
4、没有关系
"""
关系的判断四字口诀: 换位思考
"""
一对多关系
# 一对多时
1、以员工表与部门表为例
员工表角度:
一个员工可以对应多个部门
不能(比较现实，总不能一个人好几个部门职位)
部门表角度:
一个部门能否对应多个员工
可以
所以关系是"一对多" 部门是"一" 员工为"多"
'''
关系表达也只能是一对多， 不能用多对一
'''
一对多的关系中，外键字段在"多"的一方
建立外键字段
考虑:
当我们在建立表时 关联表里有被关联表的主键编号,此时我们在创建的时候就会报错因为含有不存在的数据，因此我们在建立外键字段的时候要遵循：
1.创建表的时候一定要先创建被关联表(没有外键字段的表)
2.录入表数据时一定要先录录被关联表(没有外键字段的表)
外键SQL代码
'''
当表中有外键字段，那么就先编写普通字段，最后在考虑外键字段
'''
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));
# 先给被关联表建立部门
insert into dep(dep_name,dep_desc) values('总装备部','研究装备'),('总政治部','政治委员'),('总参谋部','作战参谋'),('总后勤部','配发物资');
# 再给含有外键字段表增加信息
insert into emp(name,age,dep_id) values('李晓健',18,1),('张洋',19,1),('徐豹',18,1),('坦克',21,2),('破军',23,3),('蛤蟆',19,4);
修改、删除被关联表数据都会出现障碍
update dep set id=200 where id=2;
delect from dep where id=2;
针对修改删除做了优化
诞生了 级联更新 on update cascade
级联删除 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 vachar(32),
dep_desc vachar(64));
多对多关系
以图书馆为例
1.站在图书馆里的书籍表的立场
一本书是否可以对应对个作者
可以
2. 站在作者的立场
一个作者是否可以对应多本书
可以
结论 两个都可以 就是多对多
"""
我们设想 多对多里数据表与作者表里都有外键字段，那么我们肯定是创建不了的，怎么解决呢
解决方法：新建第三个表 关系表"""
代码
# 建立书籍表
create table book(
id int primary key auto_increment,
title varchar(32),
price float(5,3));
# 建立作者表
create table author(
id int primary key auto_increment,
name varchar(32),
phone bigint);
#建立关系表
create table book_author(
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 book(title,price) values('极品公子'，20),('雪中悍刀行',30),('完美世界',30),('神墓',30),('遮天',30);
# 给作者表添加数据
insert into author(name,phone) values('烽火戏诸侯',110),('辰东',120);
# 给关系表绑定关系
insert into book_author(author_id,book_id) values(1,1),(1,2),(2,3),(2,4),(2,5);
经测试发现自身问题：
1. 删除作者时 关系内关联该作者关系全被删除，但是书籍表没有影响。
2. 删除书籍时 关系内关联该书籍的关系全被删除，但是作者表没有影响。
结论：
我们在操作被关联表(书籍表)时只能影响到关联表内与之相关的数据
"因为关系表与它建立了外键"
我们在操作关系表的时候并不能影响到书籍表与作者表
"因为书籍表或作者表并没有外键字段"
意思就是 删除数据只能影响到与含有他外键字段的表
一对一关系
# 针对用户信息表，里面的数据可以分为两类
1、热数据:
经常使用的数据: 电话、姓名、年龄
2、冷数据:
不经常使用的数据: 出生日期、家庭地址、政治面貌
为了降低资源并 降低数据库压力，将表一分为二
1、用户表
存使用频率较高的数据字段
2、用户详情表
存使用频率较低的数据字段
# 表与表之间的关系
1、用户表角度
一个用户数据能否对应多个用户详情数据
肯定是不能的
2、用户详情表
一个用户详情数据能否对应多个用户表
也肯定是不能的
# 两边都不可以，所以有两种可能
'没有关系'
'一对一的关系'
那肯定是后者 ，那么一对一关系外键字段建在任意一方都可以，推荐建在使用查询频率比较高的一方
代码
create table user1(id int primary key auto_increment,
name varchar(32),
age int,
phone bigint,
details_id int unique, # 详细表的编号必须为独一无二
foreign key(details_id) references details(id)
on update cascade
on delete cascade);
create table details(id int primary key auto_increment,
birth varchar(32),
family varchar(64));
