目录
字段约束条件
1.无符号、零填充
unsigned
id int unsigned
zerofill
id int(5) zerofill
2.非空
not null
create table t1(
id int,
name varcher(16)
);
insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1 values('kevin',2);
'''所有字段类型不加约束条件的情况下默认都可以为空'''
create table t2(
id int,
name varcher(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);
3.默认值
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');
4.唯一值
'''单列唯一'''
create table t4(
id int unique,
name varchar(32) unique
);
insert into t4 values(1,'jason'),(2,'jason');
'''联合唯一'''
create table t5(
id int,
ip varchar(32),
port int,
unique(ip,port)
);
insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);
insert into t5 values(4,'127.0.0.1',8080);
5.主键
1.单从约束层面上而言主键相当于not null + unique(非空且唯一)
create table t6(
id int primary key,
name varchar(32)
);
insert into t6(name) values('jason');
insert into t6 values(1,'kevin');
insert into t6 values(1,'jerry');
2.InnoDB存储引擎规定了所有的表都必须有且只有一个主键(主键是组织数据的重要条件并且主键可以加快数据的查询速度)
1.当表中没有主键也没有其他非空且唯一的字段的情况下
InnoDB会采用一个隐藏的字段作为表的主键 隐藏意味着无法使用 基于该表的数据查询只能一行行查找 速度很慢
2.当表中没有主键但是有其他非空且唯一的字段 那么会从上往下将第一个该字段自动升级为主键
create table t7(
id int,
age int not null unique,
phone bigint not null unique,
birth int not null unique,
height int not null unique
);
"""
我们在创建表的时候应该有一个字段用来标识数据的唯一性 并且该字段通常情况下就是'id'(编号)字段
id nid sid pid gid uid
create table userinfo(
uid int primary key,
);
"""
6.自增
该约束条件不能单独出现 并且一张表中只能出现一次 主要就是配合主键一起用
create table t8(
id int primary key,
name varchar(32)
);
create table t9(
id int primary key auto_increment,
name varchar(32)
);
"""
自增特性
自增不会因为数据的删除而回退 永远自增往前
如果自己设置了更大的数 则之后按照更大的往前自增
如果想重置某张表的主键值 可以使用
truncate t9; 清空表数据并重置主键
"""
7.外键
1).外键前戏
我们需要一张员工表
id name age dep_name dep_desc
1.表语义不明确，无法判断到底是员工表还是部门表(不严重 无所谓)
2.字段数据反复取存，浪费存储空间(不严重 无所谓)
3.表的扩展性极差，牵一发而动全身(很严重 效率极低)
优化操作>>>:拆表
id name gender
id dep_name dep_desc
拆表之后解决了上述的三个问题，但是出现了一个致命的缺陷，不知道员工对应的部门，部门里面也不知道存在那些员工
解决措施:在员工表里增加'dep_id'
id name gender dep_id
添加一个部门编号字段填写部门数据的主键值
外键字段：专门用于记录表与表之间数据的关系
2).外键字段的创建
外键字段是用来记录表与表之间数据的关系，而数据的关系有四种
一对多关系、多对多关系、一对一关系、没关系
表数据关系的判定>>>:'换位思考'
针对员工表和部门表判断数据关系
1.先站在员工表的角度：
问：一名员工能否对应多个部门
答：不可以
2.再站在不猛表的角度：
问：一个部门能否对应多么员工
答：可以
结论：一个可以一个不可以 那么关系就是'一对多'
表关系之一对多
"""涉及到外键字段，先写普通字段，然后再写外键字段"""
create table emp(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female') default 'male',
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(32)
);
mysql> insert into dep(dep_name,dep_desc) values('讲师部','教书育人'),('安保部','维护治安');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from dep;
+----+-----------+--------------+
| id | dep_name | dep_desc |
+----+-----------+--------------+
| 1 | 讲师部 | 教书育人 |
| 2 | 安保部 | 维护治安 |
+----+-----------+--------------+
2 rows in set (0.00 sec)
mysql> insert into emp(name,dep_id) values('jason',1),('kevin',2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from emp;
+----+-------+--------+--------+
| id | name | gender | dep_id |
+----+-------+--------+--------+
| 1 | jason | male | 1 |
| 2 | kevin | male | 2 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)
"""
1.创建表的时候需要先创建被关联的表(没有外键) 然后再是关联表(有外键)
2.插入表数据的时候 针对外键字段只能填写被关联表字段已经出现过的数据值
3.被关联字段无法修改和删除
有点不太好 操作限制性太强
"""
级联更新、级联删除
被关联数据一旦变动 关联的数据同步变动
on update cascade # 级联更新
on delete cascade # 级联删除
级联更新、级联删除
被关联数据一旦变动 关联的数据同步变动
create table emp1(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others') default 'male',
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(32)
);
mysql> insert into dep1(dep_name,dep_desc) values('讲师部','教书育人'),('安保部','维护治安');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into emp1(name,dep_id) values('jason',1),('kevin',2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update dep1 set id=200 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp1;
+----+-------+--------+--------+
| id | name | gender | dep_id |
+----+-------+--------+--------+
| 1 | jason | male | 1 |
| 2 | kevin | male | 200 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)
"""
扩展:
在实际工作中 很多时候可能并不会使用外键
因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
我们为了能够描述出表数据的关系 又不想使用外键
自己通过写SQL 建立代码层面的关系
如果表少的话使用外键会方便一点，但是如果表多的话就不建议使用外键了，那样的话就会乱
"""
表关系之多对多
以书籍表与作者表为例
1.先站在书籍表的角度
问:一条书籍数据能否对应多条作者数据
答:可以
2.再站在作者表的角度
问:一条作者数据能否对应多条书籍数据
答:可以
总结:两边都可以 那么表数据关系就是'多对多'
针对多对多表关系 外键字段不能建在任意一方!!!
mysql> create table book(
-> id int primary key auto_increment,
-> title varchar(32),
-> author_id int,
-> foreign key(author_id) references author(id)
-> on update cascade
-> on delete cascade
-> );
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> create table author(
-> id int primary key auto_increment,
-> name varchar(32),
-> foreign key(book_id) references book(id)
-> on update cascade
-> on delete cascade
-> );
ERROR 1072 (42000): Key column 'book_id' doesn't exist in table
'''需要单独开设第三张表，存储数据关系'''
create table book(
id int primary key auto_increment,
title varchar(32)
);
create table author(
id int primary key auto_increment,
name varchar(32)
);
create table book2author(
id int primary key auto_increment,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
表关系之一对一
以用户表和用户详情表
1.先站在用户表的角度
问：一个用户可以对应多个详细信息
答：不可以
2.再站在用户详情表的角度
问：一份详细信息可以对应多个用户
答：不可以
总结：两边都不可以，那么先考虑是不是没有关系
如果有关系那么肯定就是'一对一'
针对'一对一'的表关系 外键字段建在任何一张表都可以 但是建议你建在查询频率较高的表中便于后续查询
create table user(
id int primary key auto_increment,
name varchar(32)
detail_id int unique, # 重点，给detail_id加约束条件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
);