表与表关系

外键

什么是外键：让表与表有硬性层面上的关系

使用外键条件：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)