文章目录
约束条件之主键
关键字:primary key
-
定义
主键(PRIMARY KEY
)的完整称呼是“主键约束”。MySQL主键约束是一个列或者列的组合,其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键,通过它可以强制表的实体完整性。 -
强调
在关系数据库,一个表中,只能有一个主键(primary key),有些数据库没有主键,系统报错。在MySQL数据库中,建立表时,可以有主键,也可以没有(推荐建立表时必须要有主键)。
-
单从约束角度上而言主键等价于非空且唯一 not null unique
mysql> create table t1( -> id int primary key, -> name varchar(32) default 'lzq' -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into t1(name) values('jason'); ERROR 1364 (HY000): Field 'id' doesn't have a default value mysql> insert into t1(id, name) values(1, 'jason'); Query OK, 1 row affected (0.05 sec) mysql> insert into t1(id, name) values(1, 'jason'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into t1(id, name) values(2, 'kevin'); Query OK, 1 row affected (0.05 sec)
-
InnoDB存储引擎规定一张表必须有且只有一个主键
-
如果既没有一个主键,也没有not null + unique的情况下,那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键(类似于书的目录)
-
如果创建表的时候没有主键,但是有非空加唯一,那么InnoDB存储引擎会自动将该字段设置为主键(隐藏就意味着而无法使用 即无法加快数据查询)
如果没有主键但是有非空且唯一的字段 那么会自动升级成主键(从上往下的第一个) create table t2( tid int not null unique, pid int not null unique, cid int not null unique );
-
-
创建表的时候都应该有一个
id
字段 并且该字段应该作为主键create table t3( id int primary key, name varchar(32) );
-
联合主键(多个字段组合 本质还是一个主键)
create table t4( id int, name varchar(32), pwd int, primary key(id, pwd) );
-
自增
关键字:
auto_increment
该约束条件不能单独使用 必须跟在键后面(主要配合主键一起使用)
mysql> create table t2( -> id int primary key auto_increment, -> name varchar(32) -> ); Query OK, 0 rows affected (0.11 sec) mysql> insert into t2(name) values('jason'); Query OK, 1 row affected (0.05 sec) mysql> insert into t2(name) values('kevin'); Query OK, 1 row affected (0.05 sec) mysql> select * from t2; +----+-------+ | id | name | +----+-------+ | 1 | jason | | 2 | kevin | +----+-------+ 2 rows in set (0.00 sec)
特点:
-
自增的操作不会因为执行删除数据的操作而回退或者重置
mysql> delete from t2 where id=2; Query OK, 1 row affected (0.05 sec) mysql> insert into t2(name) values('oscar'); Query OK, 1 row affected (0.05 sec) mysql> select * from t2; +----+-------+ | id | name | +----+-------+ | 1 | jason | | 3 | oscar | +----+-------+ 2 rows in set (0.00 sec)
-
如果非要重置主键 需要格式化表
truncate 表名; # 删除表数据并重置主键值
mysql> truncate t2; Query OK, 0 rows affected (0.08 sec) mysql> select * from t2; Empty set (0.00 sec)
优点:
(1)数据库自动编号,速度快,而且是增量增长,按顺序存放,对于检索非常有利;
(2)数字型,占用空间小,易排序,在程序中传递也方便;
(3)如果通过非系统增加记录时,可以不用指定该字段,不用担心主键重复问题。 -
约束条件之外键
-
定义
外键约束(Foreign Key)即数据库中两个数据表之间的某个列建立的一种联系。
-
作用
MySQL通过外键约束的引入,可以使得数据表中的数据完整性更强;保证数据的一致性
-
强调
- 与外键关联的主表的字段必须设置为主键
- 要求从表不能是临时表,主从表的字段具备相同的数据类型、字符长度和约束。
外键约束的创建
-
创建外键约束的条件
MySQL数据库外键的创建,需要满足以下四个条件,否则会被MySQL数据库拒绝:
- 创建外键的表和列存在
- 组成外键的列存在索引
- 必须指定数据表的引擎为InnoDB
- 外键字段和关联字段,数据类型必须一致
-
外键关系
外键是用来记录表与表之间的数据关系,关系一共有四种
- 一对一
- 一对多
- 多对多
- 没有关系
-
在创建数据表时创建外键约束
在创建数据表时创建外键约束,只需要在创建数据表的create语句后面,使用foreign key关键字指定本表的外键字段,使用reference关键字指定关联表的关联字段,并且明确约束行为即可。
创建外键约束的SQL语句示例如下:create table course( id int primary key auto_increment, name varchar(32), stu_id int, foreign key(stu_id) references student(id) on update cascade on delete cascade ); create table student( id int primary key auto_increment, name varchar(32) );
-
foreign key的约束效果
1.创建表的时候 应该先创建被关联表(没有外键字段的表)
2.插入数据的时候 应该先插入被关联表(没有外键字段的表)
外键字段填入的值只能是被关联表中已经存在的值
3.被关联字段无法修改和删除 -
级联更新、级联删除
理解
被关联数据一旦变动 关联的数据会随之同步变动
on update cascade # 级联更新 on delete cascade # 级联删除
扩展:
在实际工作中 很多时候可能并不会使用外键
因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
我们为了能够描述出表数据的关系 又不想使用外键
自己通过写SQL 建立代码层面的关系
表关系之多对多
-
举例说明
以书籍表和作者表为例说明
1.先站在书籍的层面上
问:一本书籍可以对应多个作者吗?
答:可以2.再站在作者的层面上
问:一个作者可以对应多个书籍吗?
答:可以总结:如果两边层面都可以,那么关系则为
多对多
针对多对多表关系,外键字段不能建在任意一方!!!应该创建第三方表专门用来存放对应关系
create table book(
id int primary key auto_increment,
title varchar(32)
);
create table author(
id int primary key auto_increment,
name varchar(32)
);
create table book2author(
id int primary key auto_increment,
book_id int,
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade, # 级联删除
author_id int,
foreign key(author_id) references author(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
如果不创建第三方表来存放对应关系的话,会出现双方需要的字段都还未创建
表关系之一对一
-
举例说明
以用户表和用户详情表为例说明
1.先站在用户的层面上
问:单个用户可以对应多个用户详情吗?
答:不可以2.再站在用户详情表的层面上
问:单个用户详情可以对应多个用户吗?
答:不可以总结:如果两边层面都不可以,那么关系则为
一对一
或者是没有关系
针对一对一的表关系 外键字段建在任何一张表都可以 但是建议你建在查询频率较高的表中便于后续查询
create table User(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
detail_id int unique,
foreign key(detail_id) references UserDetail(id)
on update cascade
on delete cascade
);
create table UserDetail(
id int primary key auto_increment,
phone bigint,
age int
);
表关系之一对多
-
举例说明
以学生表和部门表为例说明
1.先站在学生的层面上
问:一个学生可以对应多个部门吗?
答:不可以2.再站在部门的层面上
问:单个部门可以对应多个学生吗?
答:可以总结:如果一边层面可以,另一边层面不可以,那么关系则为
一对多
针对一对多的表关系 外键字段建在多的表里
其实一对一跟一对多的区别就在于一个唯一性的关键字(unique)
create table student(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
dep_id int,
foreign key(dep_id ) references dep(id)
on update cascade
on delete cascade
);
create table dep(
id int primary key auto_increment,
dep_desc varchar(32)
);
今日作业
书籍表与出版社表(多对多)
mysql> create table book(
-> id int primary key auto_increment,
-> book_name varchar(32)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> create table press(
-> id int primary key auto_increment,
-> press_name varchar(32)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> create table book2press(
-> id int primary key auto_increment,
-> book_id int,
-> foreign key(book_id) references book(id)
-> on update cascade
-> on delete cascade,
-> press_id int,
-> foreign key(press_id) references press(id)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.04 sec)
学生表与班级表(一对多)
mysql> create table student(
-> stu_id int primary key auto_increment,
-> stu_name varchar(32),
-> class_id int,
-> foreign key(class_id) references class(id)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table class(
-> id int primary key auto_increment,
-> class_name varchar(32)
-> );
Query OK, 0 rows affected (0.08 sec)
老师表与课程表(一对一)
mysql> create table course(
-> id int primary key auto_increment,
-> course_name varchar(32)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> create table teacher(
-> tea_id int primary key auto_increment,
-> tea_name varchar(32),
-> course_id int unique,
-> foreign key(course_id) references course(id)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.04 sec)
课程表与班级表(多对多)
mysql> create table course1(
-> course_id int primary key auto_increment,
-> course_name varchar(32)
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> create table class1(
-> class_id int primary key auto_increment,
-> class_name varchar(32)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> create table course2class(
-> id int primary key auto_increment,
-> cou_id int,
-> foreign key(cou_id) references course1(course_id)
-> on update cascade
-> on delete cascade,
-> cla_id int,
-> foreign key(cla_id) references class1(class_id)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.12 sec)
服务器表与应用程序表(一对多)
mysql> create table server(
-> pid int primary key auto_increment,
-> server_name varchar(32)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> create table client(
-> id int primary key auto_increment,
-> client_name varchar(32),
-> server_pid int,
-> foreign key(server_pid) references server(pid)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.11 sec)