外键
什么是外键：让表与表有硬性层面上的关系
使用外键条件：1、表类型必须是InnoDB存储引擎。2、关联的字段即references指定的另外一个表的字段必须要唯一。
注意：1、创建表时必须先创建被关联表。2、插入数据时，也必须先插入被关联表数据。
一对多
案例1：部门和员工
思考：
- 一个部门可以有多个员工吗？可以
- 一个员工可以有多个部门吗？不可以
- 结论：部门与员工的关系是一对多。且外键创建在多的一方。
案例二：班级和学生
- 一个班级可以有多个学生吗？可以
- 一个学生可以有多个班级吗？不可以
- 结论：一对多，外键建立在多的一方即学生表里面。
案例三：老师和课程
- 一个课程可以有多个老师吗？可以。
- 一个老师可以有多个课程吗？不可以，一个老师只能教一门课程，语文老师不能同时也是数学老师，不专业。
- 结论：一对多，老师表里面应该设置外键。
只有一方可以一条数据对应对方的多条数据，这种关系叫做一对多。
'''
创建部门表：
'''
create table department(
id int primary key auto_increment,
name varchar(20) not null,
dep_desc varchar(200)
);
'''
创建员工表
'''
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
gender enum('male', 'female', 'others') default 'male',
department_id int,
foreign key(department_id) references department(id)
on update cascade
on delete cascade
);
'''
插入数据
''’
insert into department(name, dep_desc) values('技术部', '技术部都是一群菜*');
insert into employee(name, gender, department_id) values('游哥', 'male', 1);
同步更新与同步删除：更新和删除操作都需要考虑关联与被关联的关系。这时候，出现级联操作
on update cascade
on delete cascade
删除部门后，对应的部门里面的员工表数据对应删除
更新部门后，对应员工表中的标示部门的字段同步更新
多对多
案例一：图书和作者
分析：
- 一本图书可以有多个作者吗？可以
- 一个作者可以有多本书吗? 完全可以！
- 结论：多对多，需要建立中间表。
案例二：老师和学生
- 一个老师可以有多个学生吗？可以
- 一个学生可以有多个老师吗？可以
- 结论：多对多
双方都能一条数据对应对方的多条数据，这种关系叫做多对多。
=====================创建表=================
mysql> create table author(
-> id int primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> create table book(
-> id int primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> create table author_book(
-> 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);
Query OK, 0 rows affected (0.02 sec)
=====================表详情=================
mysql> desc author_book;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| author_id | int(11) | YES | MUL | NULL | |
| book_id | int(11) | YES | MUL | NULL | |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc author;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc book;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
==============================插入数据===================
mysql> insert into author(name) values('游哥');
Query OK, 1 row affected (0.00 sec)
mysql> insert into author(name) values('鸡弟弟');
Query OK, 1 row affected (0.00 sec)
mysql> insert into book(name) values('金瓶11111');
Query OK, 1 row affected (0.00 sec)
mysql> insert into book(name) values('Python从入门到放弃');
Query OK, 1 row affected (0.00 sec)
mysql> insert into author_book(author_id, book_id) values(1,1),(1,2),(2,1);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from author_book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
+----+-----------+---------+
3 rows in set (0.00 sec)
一对一
左表的一条记录唯一对应右表的一条记录，反之也一样。
格式：foreign key + unique
案例一：一个用户只有一个博客
create table user(
id int primary key auto_increment,
name varchar(20)
);
create table blog(
id int primary key auto_increment,
blog_addr varchar(100),
user_id int unique,
foreign key(user_id) references user(id)
);
mysql> desc blog;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| blog_addr | varchar(100) | YES | | NULL | |
| user_id | int(11) | YES | UNI | NULL | |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into user(
-> name) values('游哥');
Query OK, 1 row affected (0.00 sec)
mysql> insert into blog(blog_addr, user_id) values('上海', 1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into blog(blog_addr, user_id) values('上海', 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'user_id'
mysql> select * from blog;
+----+-----------+---------+
| id | blog_addr | user_id |
+----+-----------+---------+
| 1 | 上海 | 1 |
+----+-----------+---------+
1 row in set (0.00 sec)
修改表
1. 修改表名
ALTER TABLE 表名 RENAME 新表名;
alter table user rename userinfo;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
mysql> alter table userinfo add gender enum('male', 'female') default 'male';
mysql> desc userinfo;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| gender | enum('male','female') | YES | | male | |
+--------+-----------------------+------+-----+---------+----------------+
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名 DROP 字段名;
mysql> alter table userinfo drop gender;
4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
复制表
# 查询语句执行的结果也是一张表，可以看成虚拟表
# 复制表结构＋记录 （key不会复制: 主键、外键和索引）
create table new_service select * from service;
# 只复制表结构
select * from service where 1=2; //条件为假，查不到任何记录
create table new1_service select * from service where 1=2;
create table t4 like employees;
mysql> create table user(
-> id int primary key auto_increment,
-> name varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> create table userinfo like user;
Query OK, 0 rows affected (0.02 sec)
mysql> desc userinfo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)