MySQL约束条件

约束条件

主键 primary key

主键的作用可以加快查询速度,

使用innodb引擎每个表都必须有且仅有一个主键

相当于 not null unique  多用于一个id字段并且该字段设置为表的主键字段

如果不指定主键,则采用隐藏的字段作为主键,反正就是必须要有主键,但是采用了隐藏字段就无法加快查询速度

主键就好比是一个书的目录,没有主键,查询速度会很慢,

当表中没有主键,但是有从上往下第一个非空且唯一的字段则自动升级为主键

自增 auto_increment

 与主键一起使用,可以做到id字段自动增加,

create table t1(id int primary key auto_increment, name char(16))

这样以后可以直接添加人名,id 字段会自动增加

insert into t1 (name) values('max'),('lll'),('ton');

 

 #但是主键的自增不会因为数据的删除而回退只会增加

哪怕用delete from xxx也不会回退

可以用 truncate 删除数据同时也重置了文件,id 就会从1继续自增

约束条件之外键

当有一个表记录员工姓名,员工部门及工作岗位,工作内容时,为了避免重复录入以及方便数据的查询与修改可以拆分成两个表,

一个记录员工姓名,员工部门 一个记录工作岗位和工作内容

并给员工部门添加一个字段id,这样在可以通过id号获取到员工的部门,极大程度降低了数据搜索与修改的难度

外键简单点来说可以是表与表之间建立数据关系的字段

有点类似于excel中的vloocup 但是可以修改其中数据

其中有 一对多,多对多,一对一的表对应关系.

一对多对应关系

首先要先对两个表格进行表关系的判断

要遵循换位思考的原则.

以员工表为例:

 

一个可以 一个不可以,那么员工表与部门表就是一对多,员工表是 多 部门表是 一 但是不能说多对一!!!

那么一对多的表关系的外键字段建在多的一方

具体的SQL语句为:

create table emp(
id int primary key auto_increment,
name char(16),
age int,
dep_id int,foreign key(dep_id) references dep(id)
);


create table dep(
id int primary key auto_increment,
dep_name char(16),
desc_name char(16) 　　
);

外键字段的创建规则:

1.在创建表的时候一定要先创建被关联的表,也就是没有外键字段的表

 如果先加了则会报错,按照正确的顺序创建后,

 

2.在插入数据时,也是先插入被关联表再插入关联表.

所以要先往部门表中插入数据,

 

 

 3.被关联表中的数据无法自由删除和修改

4.级联更新 级联删除可以做到删除和更改

可以在 foreign key(dep_id) references dep(id) 后添加

on update  cascade

on delete cascade

create table t1(id int primary key auto_increment,name char(16),age int,dep_id int,foreign key(dep_id)references t2(id)on update cascade on delete cascade);

删除也是一样的

多对多关系

以游戏开发与游戏开发作者为例

 

 当两边都可以 那么表关系就是多对多

#针对多对多的两个表 外键字段必须建在第三章表中

 

 意为 max开发了游戏 1 和2 jerry 开发了游戏2

create table game(id int primary key auto_increment,game_name char(16),price int);


create table author(id int primary key auto_increment,age int );

create table game_author(
    id int primary key auto_increment,
    author_id int,
    game_id int,
    foregin key(author_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(game_id) references game(id)
    on update cascade
    on delete cascade
)

一对一关系

以用户与用户详情表为例

一个用户只能对应一个用户详情表

把经常使用的放在用户表中,把一些不常用的数据放在用户详情表,也就是存储热数据和冷数据表,

很显然是一对一关系

SQL语句体现为

create table user(
    id int primary key auto_increment,
    name varchar(16),
    detail_id int unique,
    foreign key(detail_id) references user_detail(id)
    on update cascade
    on delete cascade
);
create table user_detail(
    id int primary key auto_increment,
    phone bigint,
    addr varchar(16)
);

外键可以加载任意一张表中,推荐加在使用频率高的那张表中

修改表相关SQL语句

1. 修改表名  

alter table 原表名 rename 新表名

2.增加字段

create table t1(id int primary key auto_increment,name char(16))

将gender 添加进table t1(默认添加在末尾)

alter table t1 add hobby set('read','game','movie')

alter table t1 add agg int after id

 

 删除字段

alter table t1 drop agg

更改字段名以及类型

alter table t1 change agg age int;/alter table t1 change agg age tinyint;