MySQL数据库03

自增特性

create table t1(     id int primary key auto_increment,   name varchar(32) );   insert into t1(name) values('jason'),('kevin'),('tony'); insert into t1(name) values('oscar');  # id=4  delete from t1 where id=4; insert into t1(name) values('oscar');  # id=5

自增不会随着数据的删除而回退！！！

delete from t1;  # 删除数据但无法重置主键 insert into t1(name) values('jason'),('kevin'),('tony'); truncate t1;  # 删除数据并重置主键值 insert into t1(name) values('jason'),('kevin'),('tony');

外键引入

'''     创建一张员工表       id name age dep_name dep_desc   缺陷:       1.表的重点不清晰(可以忽略)             既可以说是员工表也可以说是部门表         2.表中某些字段对应的数据一直在重复(可以忽略)            浪费存储空间         3.表的扩展性极差 牵一发而动全身(不能忽略)            耦合度太高 不利于维护   解决:       将一张表一分为二     员工表                        部门表     id    name age                id   dep_name  dep_desc ''' ''' 拆分之后，员工与部门之间没有了绑定关系； 在员工表中添加一个部门编号字段与部门表中的主键字段对应； 这个字段就是外键，因此引入了外键的概念 '''

社会中存储需要可以构建成表的数据, 它们形成的表，往往之间存储某种或某些社会关系，mysql数据库建立表结构就是社会中产生的各种数据,，分门别类管理但mysql建立的(代码层次的)表之间,，同样需要处理表与表之间的关系，形成了 多对一 、多对多 、一对一的关系。

外键字段就是用来记录表与表之间数据的关系。

外键关系

外键关系总共分为三种：一对多；多对多；一对一。

note：关系表达只能用一对多，不能用多对一。

外键关系的判断方法:换位思考，即：思考两个表格数据之间的''【一对多】''的对应关系。

  • 一对多:外键字段建在多的一方

一个员工对应一个部门，但是一个部门可以对应多个员工。

create table emp(     id int primary key auto_increment comment '编号',   name varchar(32) comment '姓名',   age int comment '年龄',   dep_id int comment '部门编号',   foreign key(dep_id) references dep(id) ); create table dep(     id int primary key auto_increment comment '编号',   dep_name varchar(32) comment '部门名称',   dep_desc varchar(32) comment '部门描述' );  在创建表字段的时候也可以给每个字段添加中文注释:comment '中文注释'

外键关键字：

foreign key(dep_id) references dep(id)

foreign key的约束效果：

  1. 创建表的时候，应该先创建被关联表(没有外键字段的表)
  2. 插入数据的时候，应该先插入被关联表(没有外键字段的表)；外键字段填入的值只能是被关联表中已经存在的值
  3. 修改、删除被关联表数据都会出现障碍（是不被允许的）

级联更新和级联删除：

要想打破这种约束效果，实现表数据之间的联动，需要使用级联更新和级联删除方法。

create table emp1(     id int primary key auto_increment comment '编号',   name varchar(32) comment '姓名',   age int comment '年龄',   dep_id int comment '部门编号',   foreign key(dep_id) references dep1(id)    on update cascade  # 级联更新   on delete cascade  # 级联删除 ); create table dep1(     id int primary key auto_increment comment '编号',   dep_name varchar(32) comment '部门名称',   dep_desc varchar(32) comment '部门描述' );

ps：

在实际工作环境中，外键也可能不会使用，因为外键会消耗额外的资源，并且会增加表的复杂度；
表很多的情况下，我们也可以通过SQL语句的形式建立逻辑意义上的表关系。

  • 多对多:外键字段重新建立一个存储表关系的表格数据

create table book(     id int primary key auto_increment,     title varchar(32),     price float(10,2) ); create table author(     id int primary key auto_increment,     name varchar(32),     gender enum('male','female','others') ); create table book2author(     id int primary key auto_increment,     author_id int,     book_id int,     foreign key(author_id) references author(id)      on update cascade   # 级联更新     on delete cascade,  # 级联删除     foreign key(book_id) references book(id)      on update cascade  # 级联更新     on delete cascade  # 级联删除 );

ps：

两张基表内的数据没有在第三张表内绑定关系的情况下，是可以随意进行增、改、删等操作的。

  • 一对一:外键字段建在任意一方都可以,但是推荐建在查询频率较高的较好的基表那一方

create table User(   id int primary key auto_increment,   name varchar(32),   gender enum('male','female','others'),   user_detail_id int unique,  # 好好体会为什么加unique   foreign key(user_detail_id) references UserDetail(id)    on update cascade   # 级联更新   on delete cascade,  # 级联删除 ); create table UserDetail(   id int primary key auto_increment,   phone bigint,   age int );