约束条件之主键
primary key 主键
# 主键就相当于是新华字典的目录，可以加快查询速度
1.单从约束条件而言，主键等价于非空且唯一(not null unique)
create table t1(
id int not null unique, # 相当于 id int primary key,
name varchar(32)
);
2.InnoDB存储引擎规定一张表必须有且只有一个主键字段
2.1 如果创建的表中没有主键也没有非空且唯一的字段，那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键。
2.2 如果创建的表中没有主键，但是有非空且唯一的字段，那么InnoDB存储引擎会自动将该字段升级为主键，如果有多个这种类型的字段，则只会升级第一个。
create table t2(
pid int not null unique, # 升级为主键
wid int not null unique,
nid int not null unique,
name varchar(32)
);
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int(11) | NO | PRI | NULL | |
| wid | int(11) | NO | UNI | NULL | |
| nid | int(11) | NO | UNI | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
# 第一个非空且唯一的字段 pid 被升级为了主键
3.创建表的时候都应该有一个'id'字段 并且这个字段一般应该被设置为主键
uid pid wid id 等等...
单列主键：
id int primary key
联合主键： # 用的很少
sid int,
nid int,
primary key (sid, nid)
约束条件之自增
auto_increment
该约束条件不能单独使用，必须跟在键后面(一般是跟在主键或外键后面)
create table t3(
id int primary key auto_increment,
name varchar(32)
);
insert into t3(name) values('jason');
insert into t3(name) values('jack');
select * from t3;
+----+-------+
| id | name |
+----+-------+
| 1 | jason |
| 2 | jack |
+----+-------+
# 即使没有往id字段添加值，由于auto_increment的存在，我们在insert的时候会自动往表的id字段中添加1、2、3...的编号。
自增的特点：
自增的操作不会因为执行删除数据的操作而回退或者重置
DELETE from t3 where id = 2; # 删除id为2的记录
insert into t3(name) values('kevin') # 新增记录
select * from t3;
+----+-------+
| id | name |
+----+-------+
| 1 | jason |
| 3 | kevin |
+----+-------+
# 新增数据的id不会从2开始 而是从3开始
如果非要重置自增的主键 需要格式化表(删除表中所有数据)
truncate 表名; # 删除表数据并重置主键自增
约束条件之外键
1.什么是外键
如果一个表的某个字段指向另一个表的主键，这个字段就称为外键。被指向的表称为主表或父表，负责指向的表称为从表或子表。
2.外键的作用
· 为了一张表的数据不要太过冗余
· 保持数据的一致性，完整性。
3.外键字段的作用
专门用来记录表与表之间数据的关系
4.外键的创建
语法：foreign key(外键字段) references 主表(被关联字段)
'建表时先写普通字段，之后在写外键字段'
create table dep( # 先创主表：部门表
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);
create table emp( # 再创从表：员工表
id int primary key auto_increment,
name varchar(32),
gender enum('male', 'female') default 'male',
dep_id int, # 外键字段 链接dep表中的id字段
foreign key(dep_id) references dep(id)
);
"
1.创建表的时候要先创建被关联的表(没有外键)，再创建关联的表(有外键)
2.插入数据的时候，针对外键字段只能填写被关联表字段已经出现过得数据值
3.被关联字段无法直接修改和删除(需要设置级联更新/级联删除)
"
5.级联更新/级联删除
定义；被关联的的数据一旦变动，关联的数据也要跟着变动。
# 部门表
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);
# 员工表
create table emp1(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female') default 'male',
dep1_id int, # 外键
foreign key(dep1_id) references dep1(id) # 建立关系
on update cascade # 级联更新
on delete cascade # 级联删除
);
扩展：
在实际工作中很多时候可能并不需要使用外键，因为外键增加了表与表之间的耦合度，不便于操作，资源消耗增加。
我们如果想描述出表数据间的关系又不想使用外键，可以多写几条SQL语句。
表关系之一对多
'没有多对一 只有一对多'
以员工表和部门表为例
一个员工只能存在于一个部门，一个部门内有多个员工。
部门表和员工表之间是一对多关系，部门是1，员工是多。
多对多关系的表建立外键：
对于一对多关系，我们只需要在'多'的这张表中建立'一'的外键关联即可，而'一'这边的表不需要做任何修改。
比如员工和部门表：部门表不变，员工表增加部门id作为外键关联部门表。
mysql> desc emp1;
+---------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | male | |
| dep1_id | int(11) | YES | MUL | NULL | |
+---------+-----------------------+------+-----+---------+----------------+
表关系之多对多
以书籍表和作者表为例：
一本书籍可以由多个作者共同完成
一个作者可以编写多本书籍
针对多对多关系，外键字段不能建立在任意一方！必须单独开设第三张表，专门存储数据关系。
create table book(
id int primary key auto_increment,
tittle varchar(32)
);
create table auther(
id int primary key auto_increment,
name varchar(32)
);
# 第三张表 专门记录book表和auther表的关系
create table book_auther(
id int primary key auto_increment,
book_id int, # 外键1 关联book表
auther_id int, # 外键2 关联auther表
foreign key(book_id) references book(id)
on update cascade on delete cascade,
foreign key(auther_id) references autger(id)
on update cascade on delete cascade
);
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| book_id | int(11) | YES | MUL | NULL | |
| auther_id | int(11) | YES | MUL | NULL | |
+-----------+---------+------+-----+---------+----------------+
表关系之一对一
以用户表和用户详情表为例
一个用户只有唯一的一份用户信息
一份用户信息只能属于一个用户
针对一对一的表关系，外键字段设置在任何一张表都可以，但是建议设置在查询频率较高的表中，以便后续查询。
create table userdetail(
id int primary key auto_increment,
phone char(11)
);
create table user(
id int primary key auto_increment,
name varchar(32),
detail_id int unique,
foreign key(detail_id) references userDetail(id)
on update cascade on delete cascade
);
mysql> desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| detail_id | int(11) | YES | UNI | NULL | |
+-----------+-------------+------+-----+---------+----------------+
练习
判断下列表数据关系 并自定义创建出表
ps:有些表数据关系不是确定 根据具体业务可能有变化
1.服务器表与应用程序表
一对多?多对多?
2.课程表与班级表
多对多
create table class(
id int primary key auto_increment,
name varchar(32) not null,
stu_num int not NULL
);
create table course(
id int primary key auto_increment,
name varchar(32) not null
);
create table class_course(
class_id int ,
course_id int,
foreign key(class_id) references class(id)
on update CASCADE on delete cascade,
foreign key (course_id) references course(id)
on update cascade on delete CASCADE
);
desc CLass;
desc course;
desc class_course;
insert into course(name) values('java');
insert into class (name,stu_num) values('三年二班',30);
select * from class;
select * from course;
3.学生表与班级表
一对多
create table stu(
id int PRIMARY key auto_increment,
name varchar(32) not null,
gender enum('male','female') DEFAULT 'male',
hobby set('唱','跳','rap','篮球'),
class_id int , # 外键
foreign key (class_id) REFERENCES class(id)
on update cascade on delete cascade
);
desc stu;
insert into stu(name, hobby, class_id)
values('张三','唱,跳,rap',1);
select * from stu;
4.老师表与课程表
一对一
create table teacher(
id int primary key auto_increment,
name varchar(32) not null,
age int ,
course_id int ,
foreign key (course_id) references course(id)
on update cascade on delete CASCADE
);
desc teacher;
insert into teacher(name,course_id)
values ('jason',1);
select * from teacher;
5.书籍表与出版社表
一对多
create table press(
id int primary key auto_increment,
name varchar(32)
);
create table book(
id int primary key auto_increment,
name varchar(32),
press_id int ,
foreign key(press_id) references press(id)
on update cascade on delete CASCADE
);
desc press;
desc book;
