MySQL学习笔记（四）约束条件及表的关系讲解

MySQL学习笔记（四）约束条件及表的关系讲解

下面是听了林海峰老师视频的总结与大家分享~

 一、约束条件

1.1、default默认值

# 补 充知识点   插入数据的时候可以指定字段

create table t1( id int, name char(4));

insert into (name,id) t1 values('jason',1);

# 下面正式讲解default约束条件的用法

create table t2(

        id int,

        name char(4),

        gender enum('male','female') default 'male'

);

insert into t2(id,name) values(1,'json'); #gender字段默认值为'male'

insert into t2 values(2,'egon','female'); 

结果为：

1.2、unique唯一

# 单列唯一

create table t3(

        id int unique,

        name char(4)

);

insert into t3 valuse(1,'jason'),(1,'egon');

# 会报错，id的值添加了unique约束条件，不能有重复值

# 联合唯一

"""

例如：ip+port可以定位到世界上任何一台电脑的软件位置

ip或port单个都可以重复，但是加到一起必须是唯一的

ip   port

1     10

1     11

2     10

2     12

上面都符合联合唯一的情况，但当下面情况时是报错的

ip  port

1   10

1   10

"""

create table t4(

        id int,

        ip char(16),

        port int,

        unique(id,port)

);

insert into t4 values(1,'127.0.0.1',8080);

insert into t4 values(2,'127.0.0.1',8081);

insert into t4 values(3,'127.0.0.2',8080);

insert into t4 values(4,'127.0.0.2',8080);# 报错

1.3、primary key主键

"""

单单从约束效果上来看

primary key等价于not null +unipue

非空且唯一

"""

create table t5(

        id int primary key

);

insert into t5 values(null); #报错

insert into t5 values(1),(1) #报错

""

它除了有约束效果之外，它还是Innodb存储引擎组织数据的依照

Innodb存储引擎在创建表的时候必须要有primary key

因为它类似于书的目录能够帮助提示查询效率并且也是建表的依据

""

# 1 一张表中有且只有一个主键，如果没有设置主键，那么会从上往下搜索知道遇到一个非空且唯一的字段自动升级为主键

create table t5(

        id int,

        name char(4),

        age int not null unique, # 自动将它设为主键

        addr char(16) null unique

);

 

# 2 如果表中没有主键也没有其它任何非空且唯一的字段，那么Innodb会采用自己内部提供的一个隐藏的字段作为主键，隐藏意味着你无法使用到它，就无法提升查询速度

# 3 一张表中通常有一个主键字段，并且通常将id/uid/sid.....字段作为主键

# 联合主键（多个字段联合起来作为表的主键，本质还是一个主键）

create table t6(

        id int,

        ip char(4),

        port int,

        primary key(ip,port)

);

"""

这也意味着，以后我们在创建表的时候，id字段一定要加primary key约束条件

"""

1.4 auto_increment自增

# 当编号特别多的时候，人为的去维和太麻烦了

create table t6(

        id int primary key auto_increment,

        name char(14)

);

insert into t6(name) values('json'),('egon');

查询结果如下所示

# 注意auto_increment只能加在主键上，不能给普通字段加

 # 以后在创建表的id（数据的唯一标识）字段时，直接加上

# id int primary key auto_increment

"""

补充：

delete from 表名： 在删除表中的数据的时候，主键的自增不会停止

truncate 表名： 清空表数据并且重置主键

"""

 二、外键简介及表与表的对于关系

我们之所以把外键从约束条件里拿出来单独讲解，是因为更好的讲解表与表间的关系

# 外键 foregin key就是用来帮助我们建立表与表之间关系的

# 表关系

"""

一对多关系（一对多，多对一都是一样的）

多对多关系

一对一关系（或没有关系）

""" 

2.1、一对多

在确定表与表之间的对应关系时一定要换位思考

假设现在有员工表和部门表，让你确定两者的联系

1、先站在员工表考虑：

        一个员工能否对应多个部门？--》不能

2、再站在部门表考虑：

        一个部门是否对应多个员工？--》能

结论：员工表与部门表只是单向的一对多成立，那么员工表与部门表就是一对多的关系

# SQL语句建立表关系

# 一对多的关系   外键字段建在多的一方

# 在建表的时候一定要先建立被关联表

# 在录入数据的时候，也必须先录入被关联表

create table dep(

        id int primary key auto_increment,

        dep_name char(17),

        dep_desc char(32)

) ;

create table emp(

        id int primary key auto_increment,

        name cahr(16),

        gender enum('male','female') default 'male',

        dep_id int ,

        foregin key(dep_id) references dep(id)

        on update cascade #同步更新

        on delete cascade  #同步删除

)；

# 级联更新

# 级联删除

2.2、多对多

比如图书表与作者表：

先站在图书表：

        一本书可不可以有多个作者--》可以

再站在作者表：

        一个作者可不可以有多个书--》可以

结论：

        图书表和作者表是双向的一对多

        那么表关系就是多对多关系

针对多对多字段表关系，不能在两张原有的表中创建外键

需要单独开设一张专门用来存储两张表数据之间的关系

# SQL语句建立表关系

create table book(

        id int primary key auto_increment,

        title varchar(13),

        price int,

        ath_id int

) ;

create table ath(

        id int primary key auto_increment,

        name varchar(32),

        age int,

        book_id int

);

create table book2author(

        id int primary key auto_increment,

        ath_id int,

        book_id int,

        foregin key(ath_id ) references ath(id)

        on update cascade #同步更新

        on delete cascade  #同步删除

        foregin key(book_id ) references book(id)

        on update cascade #同步更新

        on delete cascade  #同步删除

);

2.3、一对一

# 如果一个表的字段特别多  每次查询又不是所有字段都能用得到

# 可以将表一分为二

"""

例如：一个用户表：

        id,name,age,addr,phone,hobby,.....

一分为二

用户表：

        id,name,age

用户详情表：

        id,addr,phone,hobby,.....

站在用户表：

        一个用户表能否对应多个用户详情表吗？--》不能

站在用户详情表：

        一个详情是否对应多个用户？--》不能

结论：单向的一对多都不成立，那么这个时候两者之间的表关系就是一对一，或者没有关系

""" 

# 一对一 外键字段建在任意一方都可以，但是推荐你建在查询频率比较高的表中