mysql约束条件

目录

• 无符号、零填充
• 非空
• 默认值
• 唯一值
• 主键
• 自增
• 外键前戏
• 关系的判断
• 一对多关系
• 外键字段的建立
• 多对多关系
• 一对一关系

无符号、零填充

1. unsigned无符号
2. zerofill零填充
   

非空

create table t2(
    id int not null,
    name varchar
);
insert into t2(id) values(1);
insert into t2(id,name) values(0,'');
insert into t2(id,name) values(0,null)
select * from t2;
ps:所有字段类型不加约束条件的情况下默认都可以为空
空字符串也算值

非空字段必须要添加值

默认值

create table t3(
    id int default 666,
    name varchar(16),
    gender varchar(8) default 'male'
)
insert into t3(name) values('张三');
insert into t3 values(1,'李四','female');
insert into t3 values();


默认值要跟随前面字段类型设置

唯一值

1. 单列唯一

   create table t4(
       id int unique,
       name varchar(16) unique
   )
   insert into t4(id,name) values(1,'张三');
   insert into t4(name,id) values('李四',2);
   insert into t4(id,name) values(1,'王五');
   insert into t4(id,name) values(3,'张三');
   select * from t4;
   

   

2. 联合唯一

   create table t5(
       id int,
       ip varchar(150),
       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);
   

   

主键

1. 单从约束层面上而言主键相当于not null + unique(非空且唯一)

   create table t6(
   	id int primary key,
       name varchar(32)
   );
   insert into t6(name) values('jsaon');
   insert into t6 values(1,'张三');
   insert into t6 values(1,'李四');
   
   

   

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

   • 当表中没有主键也没有其他非空且唯一的字段的情况下InnoDB会采用一个隐藏的字段作为表的主键 隐藏意味着无法使用 基于该表的数据查询只能一行行查找 速度很慢
   • 当表中没有主键但有其他非空且唯一的字段 那么会从上往下将第一个字段自动升级为主键

   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
   	
   );
   desc t7;
   

   

3. 我们在创建表的时候应该有一个字段用来标识数据的唯一性 并且该字段通常情况下就是 ’id‘ （编号）字段

   id sid tid pid gid uid
   create table userinfo(
   	id int primary key
   );
   

自增

1. 该约束条件不能单独 并且一张表中只能出现一次 主要就是配合主键一起用

   create table t8(
   	id int primary key,
       name varchar(32)
   
   );
   desc t8;
   insert into t8 values(1,'a');
   select * from t8;
   # 需要手动指定主键数字
   create table t9(
   	id int primary key auto_increment,
       name varchar(32)
   
   );
   desc t9;
   insert into t9(name) values('a');
   insert into t9(name) values('b');
   select * from t9;
   
   # 可以不手动指定主键数字
   
   

   

   自增特性自增不会因为数据删除而回退 永远自增往前
   如果自己设置了更大的数 则之后按照更大的往前自增

   

   如果想要重置某张表的主键 可以使用户

   truncate t9;
   清空表数据并重置主键
   

   
   也可以手动指定自增数值

   ALTER TABLE `djg07`.`user_user` AUTO_INCREMENT = 1;
   

外键前戏

我们需要一张员工表
	id	name	age	dep_name	dep_desc
1.表语义不明确（到底是员工还是部门）		可以忽略
2.存取数据过于冗余（浪费存储空间）		 可以忽略
3.数据的扩展性极差					  不能忽略

将上述表一分为二
	id		name		age
    id		dep_name	dep_desc
上述的上问题全部解决 但是员工跟部门之间没有了关系
外键字段：用于标识数据与数据之间关系的字段

关系的判断

表关系、数据关系其实意思是一样的 只是说法上有区分

关系总共有四种

1. 一对多
2. 多对多
3. 一对一
4. 没有关系

关系判断可以采用 换位思考 也就是站在两个表位置去思考

一对多关系

以员工表和部门表为例
	1.先站在员工表的角度
    	问：一个员工能否对应多个部门
        答：不行
    2.在站在部门表的角度
    	问：一个部门能否有多个员工
        答：可以
    结论：一个可以一个不行 所以关系就是一对多
    针对一对多关系 外键要建在多的一方例如上面就是建在员工表中

外键字段的建立

小技巧：先定义出含有普通字段的表 之后再考虑外键字段的添加

create table dep(
	did int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(64)

);

create table emp(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(did)
    
);

1. 创建表的时候一定要先创建被关联表
2. 录入数据的时候一定要先录入被关联表
3. 关于 Mysql 数据库什么情况下插入失败 id 仍会自增的问题
   关于 Mysql 数据库什么情况下插入失败 id 仍会自增的问题

级联更新，级联删除

update dep set did = 2 where did = 1;
delete from dep where did = 1;

create table dep1(
	did int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);

create table emp1(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep1(did)
    on update cascade
    on delete cascade
);

外键其实是强耦合 不符合解耦合的特性

​ 所以很多时候 实际项目中当表较多的情况 我们可能不会使用外键 而是使用代码建立逻辑层面的关系

多对多关系

以书籍表与作者表为例
	1.先站在书籍表的角度
  		问:一本书能否对应多个作者
      答:可以
 	2.再站在作者表的角度
    	问:一个作者能否对应多本书
      	答:可以
   结论:两个都可以 关系就是'多对多'	
 	针对'多对多'不能在表中直接创建 需要新建第三张关系表

    

create table author(
	id int primary key auto_increment,
    name varchar(32),
    phone bigint

);
create table book(
	id int primary key auto_increment,
    b_name varchar(32),
    price float(10,2)
);

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

);

一对一关系

以用户表与用户详情表为例
	1.先站在用户表的角度
  		问:一个用户能否对应多个用户详情
      答:不可以
 	2.再站在用户详情表的角度
    	问:一个用户详情能否对应多个用户
      	答:不可以
   结论:两个都可以 关系就是'一对一'或者没有关系	
 	针对'一对一'外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中

create table userdetial(
	id int primary key auto_increment,
    phone bigint
);

create table user(
	id int primary key auto_increment,
    name varchar(32),
    detial_id int,
    foreign key(detial_id) references userdetial(id)
    on update cascade
    on delete cascade
);