约束条件之主键
primary key 称之为主键
特点
1.单从约束角度上而言主键等价于非空且唯一(not null unique)
mysql> create table t1(id int primary key,name varchar(32));
Query OK, 0 rows affected (0.04 sec)
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> insert into t1 values(1,'nana');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values(1,'aa');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | nana |
+----+------+
1 row in set (0.00 sec)
2.InnoDB存储引擎规定一张表必须有且只有一个主键(主键可以加快数据的查询)
1.如果创建的表中没有主键也没有非空且唯一的字段，那么InnoDB存储引擎会自动采用一个隐藏的字段作为组件
2.如果创建的表没有主键但是有非空且唯一的字段，那么InnoDB存储引擎会自动将该字段设置为主键
eg:
mysql> create table t2(id int not null unique,name varchar(32));
Query OK, 0 rows affected (0.03 sec)
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> insert into t2 values(1,'nana');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values(1,'aa');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
3.创建表的时候应该有一个‘id'字段(该字段名非固定)，并且该字段应该作为主键
4.创建表可以使用单列主键，也可以使用联合组件
mysql> create table t3(id int, nid int,primary key(id,nid));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t3 values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t3 values(1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
约束条件之自增
auto_increment 称之为自增，该约束条件不能单独使用 必须跟在键后面(主要配合主键一起使用)
mysql> create table t4(id int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table t4(id int primary key auto_increment,name varchar(32));
Query OK, 0 rows affected (0.04 sec)
mysql> desc t4;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
mysql> insert into t4(name) values('nana'),('aa'),('bb'),('cc');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t4;
+----+------+
| id | name |
+----+------+
| 1 | nana |
| 2 | aa |
| 3 | bb |
| 4 | cc |
+----+------+
特点
自增的操作不会因为执行删除数据的操作而回退或者重置
mysql> create table t5(id int primary key auto_increment,name varchar(32));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t5(name) values('aa'),('bb'),('cc'),('dd');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t5;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+----+------+
4 rows in set (0.00 sec)
mysql> delete from t5 where id=4;
Query OK, 1 row affected (0.01 sec)
mysql> select * from t5;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+------+
3 rows in set (0.00 sec)
mysql> insert into t5(name) values('ee'),('ff');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t5;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 5 | ee |
| 6 | ff |
+----+------+
5 rows in set (0.00 sec)
如果非要重置主键 需要格式化表 : truncate 表名; 代表删除表数据并重置主键值
mysql> truncate t5;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from t5;
Empty set (0.00 sec)
mysql> insert into t5(name) values('aa'),('bb'),('cc'),('dd');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t5;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+----+------+
4 rows in set (0.00 sec)
约束条件之外键
外键知识导入
需求：创建一张员工表
| id | name | gender | dep_name | dep_desc |
|---|---|---|---|---|
| 1 | jason | male | 讲师部门 | 传授知识 |
| 2 | kevin | male | 财务部门 | 管理财务 |
| 3 | tony | male | 财务部门 | 管理财务 |
上述表的缺陷
1.表结构不清晰，到底是员工表还是部门表不清楚
2.字段数据反复存取，浪费存储空间
3.表的扩展性极差，牵以发动全身
优化操作：拆表
| id | name | gender |
|---|---|---|
| 1 | jason | male |
| 2 | kevin | male |
| 3 | tony | male |
| id | dep_name | dep_desc |
|---|---|---|
| 1 | 讲师部门 | 传授知识 |
| 2 | 财务部门 | 管理财务 |
拆表之后解决了上述的三个问题，但是出现了很大的问题：如何将两个表关联起来
解决办法
添加一个部门编号字段在员工表里
| id | name | gender | dep_id |
|---|---|---|---|
| 1 | jason | male | 1 |
| 2 | kevin | male | 2 |
| 3 | tony | male | 2 |
外键字段的创建(foreign key)
外键字段专门用于记录表与表之间的数据关系，而数据关系有四种：一对多关系、多对多关系、一对一关系、没有关系
表关系之一对多
表数据关系的判定
关于表数据关系的判定一般采用换位思考的方法
针对员工表和部门表判断数据关系
1.先站在员工的角度：
问：一条员工数据能否对应多条部门数据(一名员工能否属于多个部门)
答：不可以
2.站在部门表的角度：
问：一条部门的数据能否对应多条员工数据(一个部门能否有多名员工)
答：可以
3.得出结论：员工表和部门表是“一对多” ，部门是一 员工是多
ps:针对"一对多"的关系，外键字段建在多的一方
实战
在有外键字段的时候，先写普通字段，再写外键字段
create table emp(id int primary key auto_increment,
name varchar(32),
gender varchar(32) default 'male',
dep_id int,
foreign key(dep_id) references dep(id));
create table dep(id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32));
mysql> insert into dep(dep_name,dep_desc) values('讲师部门','传授知识'),('财务部门','管理财务');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into emp(name,dep_id) values('jason',1),('kevin',2),('tony',2);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from dep;
+----+--------------+--------------+
| id | dep_name | dep_desc |
+----+--------------+--------------+
| 1 | 讲师部门 | 传授知识 |
| 2 | 财务部门 | 管理财务 |
+----+--------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+----+-------+--------+--------+
| id | name | gender | dep_id |
+----+-------+--------+--------+
| 1 | jason | male | 1 |
| 2 | kevin | male | 2 |
| 3 | tony | male | 2 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)
注意
1.创建表的时候需要先创建被关联的表(没有外键)，然后再创建关联表(有外键)
2.插入表数据的时候 针对外键字段只能填写被关联表字段已经穿线过的数据值
3.被关联的字段无法修改和删除(操作限制性太强)
级联更新，级联删除
级联更新，级联删除的作用是被关联的数据一旦变动，关联的数据同步变动
create table emp1(id int primary key auto_increment,
name varchar(32),
gender varchar(32) default 'male',
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade
on delete cascade);
create table dep1(id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32));
insert into dep1(dep_name,dep_desc) values('讲师部门','传授知识'),('财务部门','管理财务');
insert into emp1(name,dep_id) values('jason',1),('kevin',2),('tony',2);
mysql> update dep1 set id =10 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dep1;
+----+--------------+--------------+
| id | dep_name | dep_desc |
+----+--------------+--------------+
| 1 | 讲师部门 | 传授知识 |
| 10 | 财务部门 | 管理财务 |
+----+--------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from emp1;
+----+-------+--------+--------+
| id | name | gender | dep_id |
+----+-------+--------+--------+
| 1 | jason | male | 1 |
| 2 | kevin | male | 10 |
| 3 | tony | male | 10 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)
mysql> delete from dep1 where id =1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from dep1;
+----+--------------+--------------+
| id | dep_name | dep_desc |
+----+--------------+--------------+
| 10 | 财务部门 | 管理财务 |
+----+--------------+--------------+
mysql> select * from emp1;
+----+-------+--------+--------+
| id | name | gender | dep_id |
+----+-------+--------+--------+
| 2 | kevin | male | 10 |
| 3 | tony | male | 10 |
+----+-------+--------+--------+
扩展
在实际工作中，很多时候可能并不会使用外键，因为外键增加了表之间的耦合度，不便于单独操作，资源消耗增加；我们为了能够描述出表数据的关系，又不想使用外键，可以通过自己写SQL，建立代码层面的关系
表关系之多对多
表数据关系的判定
以书籍表和作者表为例
1.站在书籍表的角度：
问：一条书籍数据能否对应多条作者数据（一本书是否可有多个作者）
答：可以
2.站在作者表的角度：
问：一条作者数据能否应对多条书籍书籍(一个作者能否写多本书)
答：可以
3.得出结论：表数据的关系是“多对多”的关系
ps:针对多对多的表关系，外键不能建在任意一方,需要单独开设第三张表，来存储数据关系
实战
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 book_author(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);
mysql> insert into book(title) values('python'),('java'),('go');
mysql> insert into author(name) values('jason'),('kevin');
mysql> insert into book_author(book_id,author_id) values(1,1),(1,2),(2,1),(3,1),(3,2);
mysql> select * from book_author;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
+----+---------+-----------+
表关系之一对一
表数据关系的判定
以用户表和用户详情表为例
1.先站在用户表的角度
问：一条用户用户数据能否对应多条用户详细数据(一个用户是否可以访问多个用户详细信息)
答：不可以
2.站在用户详情表的角度
问：一条用户详情的数据是否对应多条用户数据(以个用户详细数据是否能被多个用户使用)
答：不可以
3.总结：两边都不可以，先考虑是不是他们之间没有关系；如果有关系就是“一对一”
ps:针对"一对一"的表关系，外键字段建在任何一张表都可以，但是建议建在查询率较高的表中便于后续查询
实战
create table user(id int primary key auto_increment,
name varchar(32),
detail_id int,
foreign key(detail_id) references user_detail(id)
on update cascade
on delete cascade);
create table user_detail(id int primary key auto_increment,
addr varchar(32));
mysql> insert into user_detail(addr) values('北京'),('四川'),('上海');
mysql> insert into user(name,detail_id) values('nana',1),('jason',2),('aa',3);
mysql> select * from user;
+----+-------+-----------+
| id | name | detail_id |
+----+-------+-----------+
| 1 | nana | 1 |
| 2 | jason | 2 |
| 3 | aa | 3 |
+----+-------+-----------+
3 rows in set (0.00 sec)
mysql> select * from user_detail;
+----+--------+
| id | addr |
+----+--------+
| 1 | 北京 |
| 2 | 四川 |
| 3 | 上海 |
+----+--------+
作业
判断下列表数据关系 并自定义创建出表（ps:有些表数据关系不是确定 根据具体业务可能有变化）
服务器表与应用程序表
多对多的关系
create table server1(id int primary key auto_increment,
name varchar(32)
);
create table app(id int primary key auto_increment,
name varchar(32));
create table server1_app(id int primary key auto_increment,
server1_id int,
foreign key(server1_id) references server1(id)
on update cascade
on delete cascade,
app_id int,
foreign key(app_id) references app(id)
on update cascade
on delete cascade);
课程表与班级表
首先：一门课程，可以被多个班级上；一个班级可以上多门课程；所以课程表与班级表示多对多的关系
create table course(id int primary key auto_increment,
name varchar(32)
);
create table class(id int primary key auto_increment,
name varchar(32),
num int);
create table course_class(id int primary key auto_increment,
course_id int,
foreign key(course_id) references course(id)
on update cascade
on delete cascade,
class_id int,
foreign key(class_id) references class(id)
on update cascade
on delete cascade);
mysql> insert into course(name) values('python'),('java'),('go');
mysql> insert into class(name,num) values('金牌一班',45),('金牌二班',50);
mysql> insert into course_class(course_id,class_id) values(1,1),(1,2),(2,1),(3,2);
学生表与班级表
首先：一个学生属于一个班级，一个班级含有多名学生；所以学生表与班级表示一对多关系
create table student(id int primary key auto_increment,
name varchar(32),
gender enum('male','female'),
age int,
class1_id int,
foreign key(class1_id) references class1(id)
on update cascade
on delete cascade);
create table class1(id int primary key auto_increment,
name varchar(32),
num int);
mysql> insert into class1(name,num) values('金牌一班',45),('金牌二班',50);
mysql> insert into student(name,gender,age,class1_id) values('nana','female',18,1),('kevin','male',19,2),('tony','male',19,1);
mysql> select * from student;
+----+-------+--------+------+-----------+
| id | name | gender | age | class1_id |
+----+-------+--------+------+-----------+
| 1 | nana | female | 18 | 1 |
| 2 | kevin | male | 19 | 2 |
| 3 | tony | male | 19 | 1 |
+----+-------+--------+------+-----------+
老师表与课程表
首先：一个老师可以教授多门课程，一门课程也可以被多个老师教授，所以老师表与课程表是多对多的关系
create table teacher(id int primary key auto_increment,
name varchar(32));
create table course1(id int primary key auto_increment,
name varchar(32));
create table teacher_course(id int primary key auto_increment,
teacher_id int,
foreign key(teacher_id) references teacher(id)
on update cascade
on delete cascade,
course1_id int,
foreign key(course1_id) references course1(id));
mysql> insert into teacher(name) values('jason'),('tony'),('oscar');
mysql> insert into course1(name) values('python'),('java'),('go');
书籍表与出版社表
首先：一本书籍只属于一个出版社，一个出版社可以出多本书；所以书籍表与出版社表是一对多的关系
create table book1(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);
create table press(id int primary key auto_increment,
name varchar(32));
mysql> insert into press(name) values('北大'),('人民'),('浙大');
mysql> insert into book1(name,press_id) values('python',1),('java',2),('go',1);
mysql> select * from book1;
+----+--------+----------+
| id | name | press_id |
+----+--------+----------+
| 1 | python | 1 |
| 2 | java | 2 |
| 3 | go | 1 |
+----+--------+----------+