字段的约束条件

字段的约束条件

约束的作用：对表中的数据进行限制。
保证数据的正确性 有效性和完整性  
一个表如果添加了约束，不正确的数据将无法再插入到表中，约束在创建表
的时候添加比较合适


字段约束的语法：
      create table 表名（  字段名 字段类型 列级约束，
        

约束条件之 无符号|零填充

unsigned （去除正负号）

语法：
create table l2(id int unsigned)

用处：清除整数int 小数 float等数字的正负号，例如 年龄 学号等应用场景 
如果再次插入-1 负数 则会报错


zerofill  填充0

语法：
create table l3(id int(6) zerofill unsigned);
# 创建表 字段ID 类型 int 最高6位数 不足0填充 去除符号

insert into l3 values(1);
+--------+
| id     |
+--------+
| 000001 |
+--------+
#当不满足6位时，按照左边补0，例如：000001；满足时，正常显示。

insert into l3 values(123456);
# 满足是则正常显示

约束条件之非空(not null)

create table t1(id int,name,varchar(6));

insert into t1 values('','');

所有字段类型在不加约束条件的情况下默认都是可以为空


非空：加入数据时字段内容不可以为空，必须填写数据
关键词：not null 

create table t1(id int not null,name varchar(6) not null);
# 创建一个表 字段 id 类型 int 约束条件 not null 

desc l1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   |     | NULL    |       |
| name  | varchar(4) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

NUll = NO (是否可以为空，不可以)

insert into l1 values (101);
# Column count doesn't match value count at row 1
非空情况下，插入数据必须添加。

insert into l1 values(10001,'张无忌');
# ok

约束条件之默认值(default)

default 
语法：
create table l1(id int not null,name char(4) default '无名');
# 创建表 字段name 类型char字符串 默认值 无名

create table l3(id int(5) zerofill default 00000);
# 如果想默认多个0 那就需要使用填充0的方法 不然默认还是一个0


+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
| name  | char(4) | YES  |     | 无名    |       |
+-------+---------+------+-----+---------+-------+


有了默认值以后 该字段信息在没有填写的情况下就会默认用默认值

约束条件之唯一值(unique)

unique
确保字段或列只有一个唯一的值，约束字段不会有任何重复的数据
可以防止一个字段中拥有相同的值

语法：
create table l1(id int unsigned not null unique);
#创建一个表 字段id 类型int 约束 去除符号 非空 唯一值

这里表名 字段id唯一值  如果插入同样数值会报错

'联合唯一'
create table l1(id int unique,ip varchar(16),port int,unique(ip,port));
# unique(ip,port) 唯一值 ip+port 意思这两个值相加是唯一值就可以

insert into l1 values (10001,192.168,8888);
# 可以
insert into l1 values (10002,192.168,8889);
# 可以 ip和port 相加 没有重复的
insert into l1 values (10003,192.169,8888);
# 可以  ip和port 相加 没有重复的
insert into l1 values (10004,192.168,8888);
# 不可以  ip和port 相加 重复

主键(primary key)

1.单从约束层面上来看 主键相当于 not null nuique 非空且唯一
  主键 primary key 特征 非空且唯一

关键词 primary key
   
create table l1(id int primary key)

# 创建一个表 字段id 设置为主键  主键自带非空 唯一属性

2.设置主键的意义：
  在innoDB储存引擎规定了所有的表都必须有且只有一个主键（主键是组织数据的重要条件 并且主键可以加快数据的查询速度

  
3.没有主键：
当表中没有设置主键 也没有其他非空且唯一的字段情况下：InnoDB引擎会采用一个隐藏的字段作为表的主键 隐藏意味着无使用 基于该表的数据查询只能一行行的查找 速度相对较慢

有多个非空且唯一的字段，将会从上往下将第一个字段自动升级为主键

例如：create table l1(id int not null unique,
                  phone bigint not null unique)
# 创建表 字段id 类型 int 非空 唯一 
# 字段phone bigint 类型 非空唯一 
# 由于没有手动设定主键primary key 多个具有非空且唯一的字段默认第一个字段为主键  id

'''
我们在创建表的时候应该有一个字段用来标识数据的唯一性 并且该字段通常情况下都是id 编号字段
'''


create table l1(id int primary key,name varchar(6) not null)

# 以后在我们创建表 通常都会这样开始

自增(auto_increment)

1.什么是自增:
  自增和主键一起使用，默认数据起始id为1 
  自增需要和整数类型一起使用 
  设置完成后每次添加新的数据 都会在之前的自增值上面加一
  
  
关键词 auto_increment （因克门特）

语法：
create table l1(id int primary key auto_increment,
               name varchar(6));

# 创建表 字段id 类型 主键 自增 字段 name 类型 字符串

insert into l1 values(100,'moon');
# 插入数据 到 表L1 id 100 name moon

insert into l1(name) values ('calvin');
# 第二次插入没有填写id 但由于id有自增属性 会自动加一
# 所以自动为101

+-----+--------+
| id  | name   |
+-----+--------+
| 100 | moon   |
| 101 | calvin |
+-----+--------+

'''
自增特性 
   自增不会因为数据的删除而回退 永远自增向前
   如果自己手动设置了更大的数 则之后会按照更大的往前自增
   
	 如果想重置某张表 可以使用
'''

外键前戏

外键的概念
首先我们先搞懂一个概念，什么是 参照完整性


举例：现在有一张员工表，字段为工号 姓名 所属部门 部门职责

  id   name  dep_name  dep_desc 
 1000  月亮    财务部     打款
 1001  太阳    安保部     保安
 1002  月光    管理部     管理
 1003  阳光    财务部     打款
  
 1.首先这个表语义不明确 这不知道是 员工信息表 还是 职责表
 2.这样我们每次插入 都需要输入部门 和 部门职责  
   部门和部门职责在表中会高度复用 数据过于重复 浪费空间
 3.可扩展性极差  例如要更改某个部门名称 那所有的都要手动改

换一个思维，我们把这个表一分为二 然后在互相关联也可以达到目的



  现在把上表分成2个表
  
  id    name    dep_id       dep_id   name   desc
 1000   月亮       1            1     财务部   打款
 1001   太阳       2            2     安保部   保安
 1002   月光       3            3     管理部   管理
 1003   阳光       1            
  
  
  
  好处：
  '''
  表义明确    信息表    部门表
  数据重复减少  优化空间 少了很多重复储存
  扩展性提高  例如更改 部门职责  直接改部门表里面就可以
  因为  信息表 关联 部门表 关联的是部门表的id 部门表可以改
  很方便
  '''
  
  外键字段:用于标识数据与数据之间关系的字段
  
  

关系的判断

表关系：

一共四种：
   一 对 多
   多 对 多
   一 对 一
   没有关系
    
    
# 没有所谓的 多 对 一 只有 一对多

表与表的关系判断可以采用 换位思考 原则

一对多关系

已员工表和部门表为例
1.站在员工表角度 
  一个员工是否可以对应多个部门：
  不可以
2.站在部门表的角度
  一个部门能否对应多名员工
  可以
 
结论：一个可以 一个不可以 就是 一对多关系
    外键字段健在多的一方  依旧是 部门表里面
  
语法  
foreign key(外键字段名) references 表名(字段名)
# 设置一个字段为外键字段  引用外表的id

create table l1(id int primary unsigned,name char(6),
               dep_id int,
               foreign key(dep_id) references dep(id));
# 创建表 字段 id 类型 int 设为主键 去除符号，字段 name 类型 字符串 字段 dep_id 类型 int，设置外键dep_id作为外键foreign key(dep_id) ，数据关联 references dep(id)) dep表 id字段相关联

create table dep(id int primary,name varchar);
# 被关联表正常创建即可



1.首先要先有被关联表 才可以创建拥有外键的表，要不然无法创建，因为外键关联的表都还没有创建 他不知道关联谁
2.录入表数据的时候 一定要先录入被关联表
3.修改数据的时候外键字段无法修改和删除
  如果要修改被关联表的id 是不可以的 因为你修改了 主表并不会跟随变化，这里就要用到级连
  


在主表创建是加入 级联更新 和 删除

create table l1(id int primary key unsigned,
                name varchar(6),
                dep_id int,   
      foreign key(dep_id) references dep(id)
               on update cascade
               # 设置外键id更新 dep_id同步更新
               on delete cascade 
               # 设置外键id删除 dep_id同步删除 
               )


create table l2(id int primary key unsigned,
               level int
               );
               
 当你更改了 L2 里面的id L1里面外键id也会更改同步更新
 如果你删除了L2 里面的id L1里面外键关联的数据也会同步被删除



+------+-----------+-------+
| id   | name      | l2_id |
+------+-----------+-------+
|    1 | moon      |     8 |
|  101 | jaosn     |     1 |
| 1001 | 康世红    |     2 |
| 1002 | 立志      |     2 |
| 1003 | 张红      |     2 |
+------+-----------+-------+
5 rows in set (0.00 sec)

delete from l2 where id=8;
# 删除了L2里面的 id8
Query OK, 1 row affected (0.00 sec)

select * from l1;
+------+-----------+-------+
| id   | name      | l2_id |
+------+-----------+-------+
|  101 | jaosn     |     1 |
| 1001 | 康世红    |     2 |
| 1002 | 立志      |     2 |
| 1003 | 张红      |     2 |
+------+-----------+-------+
# 发现L1里面原来关联外键l2_id8 对应的数据都被同步删除了

多对多关系

以书籍与作者关系为例
1.先站在书籍表角度
  一本书能否对应多个作者
  可以
2.再站在作者角度
  一个作者能否对应多本书
  可以

结论 如果两站表 都有互相关联 关系就是 多对多
针对多对多数据 不能在表中直接创建 需要新建第三张关系表

书籍表
create table book( id int primary key,
                   name char(4),
                  price float(10.2)
);

作者表
create table author( id int primary key,
                    name char(4),
                    phone bigint
);


关联表
create table book_author( id int primary key,
           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
);


+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id        | int(11) | NO   | PRI | NULL    |       |
| author_id | int(11) | YES  | MUL | NULL    |       |
| book_id   | int(11) | YES  | MUL | NULL    |       |
+-----------+---------+------+-----+---------+-------+


+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         2 |       2 |
|  3 |         2 |       1 |
|  4 |         1 |       2 |
+----+-----------+---------+
用第三张表 就是这样来储存 相对的关系 

一对一关系

已用户表与用户详情表为例
1.先站在用户表的角度
  一个用户可以对应多个用户详情吗？
  不可以
2.站在用户详情表的角度
  一个用户详情可以对应多个用户吗
  不可以
  
结论：两个都不可以  关系可以为 一对一 或者没有关系

针对一对一 外键字段 可以健在任何一方都可以 
但是推荐建在查询频率较高的表中，（主表中）

create table user(id int primary,name char(5),
                  info_id int,
     foreign key (info_id) references info(id)
     on update cascade  on delete cascade           
                 );
                 
 # 创建一个表 字段 id 设置为 主键  字段 info_id 类型 int
 # 设置info_id 为外键来源 info表 id字段数据
create table info(id int primary,phone bigint,)