栈记
MySQL

字段约束条件

主键、外键、唯一、非空与默认值等字段约束

其实 ORM 把这些事情都做了,不过还是熟悉一下常用的比较好;

通过 desc 查看表结构的时候会有一些字段会显示这些约束的条件;

+-------+---------------------------+------+-----+-----------+----------------+
| Field | Type                      | Null | Key | Default   | Extra          |
+-------+---------------------------+------+-----+-----------+----------------+
| id    | int                       | NO   | PRI | NULL      | auto_increment |
| port  | int(10) unsigned zerofill | YES  | MUL | NULL      |                |
| host  | varchar(15)               | NO   |     | 192.0.0.1 |                |
+-------+---------------------------+------+-----+-----------+----------------+

type 类型约束

  • auto_increment 自增
  • unsigned 数字使用,标识使用无符号尽可能多约束条件的最前面
  • zerofill 数字使用,用 0 填充;当给 int 类型设置长度时会有作用 int(3),没啥用
-- 自增字段应该是 primary key 或者 unique
create table t1 (id int primary key auto_increment, name char(10));

-- 不论数据是否被删除以及插入什么数据,自增数据都会以上一条数据为准开始自增
insert into t1(name) values ('tom'), ('jack');

null 约束

not null 不能为空

create table t1 (id int not null);

default 默认值

default [value] 不能为空

create table t1 (id int not null default 0);

key 主键约束

primary key (fieldName) 设置主键,约束特性:不为空且唯一 unique + not null 会被数据库识别为 primary key

create table t1 (id int primary key, name char(10));

-- 可以放在最后通过括号指定
create table t1 (id int, name char(10), primary key (id));

-- 也可以通过 primary key 指定多个字段,叫做复合主键
create table t1 (id int, host char(15), port int, primary key(host, port));

unique 全表数据唯一

create table t1 (id int unique, name char(10));

-- 可以放在最后通过括号指定
create table t9 (id int, name char(10), unique(id));

-- 联合唯一,多个字段不允许重现重复
-- host 可以重复、port 可以重复,但是不允许 host 和 port 同时重复
create table t10 (id int, host varchar(15), port int, unique(host, port));

外键约束

create table dep(
  id int primary key,
  name varchar(16),
  -- 关键字要用反引号转义
  `desc` varchar(64)
);

create table emp(
  id int primary key,
  name varchar(16),
  gender enum ('male', 'female'),
  mobile varchar(11),
  dep_id int,
  -- 依赖 dep 表中的 id, 后续再插入数据时不能输入 dep 中不存在的 id
  -- 前提是 dep 存在
  foreign key (dep_id) references dep(id)
  on delete cascade
  on update cascade -- 删除或者修改数据时直接同步修改或者删除
);

数据表关系

一对多

表 A 的一条记录在表 B 中可对应多条记录,但表 B 的一条记录只对应表 A 的一条记录,是最常见的关系。

比如一名小说作者可能出版很多小说;

create table user (id int primary key auto_increment, name varchar(16));

create table novel (
  id int primary key auto_increment,
  name varchar(20),
  user_id int,
  foreign key (id) references user(id)
);

多对多

表 A 的一条记录可对应表 B 的多条记录,反之亦然,需借助中间表(连接表)实现。

比如每个学生都需要上多门课程;

create table student (id int primary key, name varchar(16));

create table course (id int primary key, name varchar(16));

create table student2course (
  student_id int,
  course_id int,
  -- 设置复合主键
  primary key (student_id, course_id),
  -- 设置外键
  foreign key (student_id) references student(id),
  foreign key (course_id) references course(id)
);

insert into student values (1, '张三'), (2, '李四');
insert into course values (1, '语文'), (2, '数学');

insert into student2course values (1, 1), (1, 2), (2, 1), (2, 2);

一对一

表 A 的一条记录在表 B 中最多有一条对应记录,反之亦然,常用于拆分数据或扩展字段。

比如每个用户都只能存在一个身份证号;虽然有点牵强。

create table user (id int primary key, name varchar(16));

create table id_card (
  id int primary key, 
  card_number int, 
  user_id int unique, -- 通过 unique 限制不要重现重复的 user_id
  foreign key (user_id) references user(id);
);

On this page