MySQL外键

目录

一.外键

1.表与表之间建立关系

2.什么是外键

3.一对多关系

4.多对多关系

（1）建表会遇到的问题

（2）解决循环建表的问题

5.一对一关系、

6.小结

二.多表查询

1.数据准备

2.多表查询案例

（1）联表

（2）子查询

三.Navicat的使用

1.用途

2.简单使用

（1）execute

（2）fetchone

（3）fetchall

（4）fetchmany

（5）scroll

---

一.外键

1.表与表之间建立关系

• 表与表之间只有三种关系
  • 一对一
  • 多对一
  • 一对多
• 在MySQL的关系中没有多对一的说法
• 一对多、多对多，都叫一对多

2.什么是外键

外键是关系数据库中的一个概念，用于建立两个关系表之间的关联关系

它是一个列或一组列，用来指向另一个表的主键

外键在建立数据表与数据表之间的关系时起到了重要的作用

3.一对多关系

• 一对多关系，外键建在字段多的地方
• 在创建表的时候一定要先创建被关联表
• 在录入数据的时候必须先录入被关联表

以员工部门表为例：

• 在员工表
  • 要考虑到员工表里面的一个员工是否能对应部门表里的多个部门
• 在部门表
  • 要考虑到一个部门是否能对应员工表里多个员工
• 总结
  • 员工表与部门表只是单项的一对多成立，那么员工表与部门表就是一对多关系

创建部门表

create table dep(id int primary key auto_increment,dep_name char(16),dep_desc char(32)
);#Query OK, 0 rows affected (0.82 sec)

查看部门表

desc dep;+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment |
| dep_name | char(16) | YES  |     | NULL    |                |
| dep_desc | char(32) | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

创建员工表

create table emp(id int primary key auto_increment,emp_name char(16),emp_gender enum("male","female","others") default "male",dep_id int,foreign key (dep_id) references dep(id)
);# Query OK, 0 rows affected (0.92 sec)

查看员工表

desc emp;+------------+--------------------------------+------+-----+---------+----------------+
| Field      | Type                           | Null | Key | Default | Extra          |
+------------+--------------------------------+------+-----+---------+----------------+
| id         | int(11)                        | NO   | PRI | NULL    | auto_increment |
| emp_name   | char(16)                       | YES  |     | NULL    |                |
| emp_gender | enum('male','female','others') | YES  |     | male    |                |
| dep_id     | int(11)                        | YES  | MUL | NULL    |                |
+------------+--------------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

插入部门表数据

insert into dep(dep_name,dep_desc) values("sm运动社","日常活动"),("游戏社","休闲娱乐"),("技术部","能力提升"),("cp外交部","社交沟通");# Query OK, 4 rows affected (0.14 sec)
# Records: 4  Duplicates: 0  Warnings: 0

插入员工表数据

insert  into emp(emp_name,emp_gender,dep_id) values("dream","male",1),("chimeng","female",4),("mengmeng","female",2),("drunkmeng","male",3);# Query OK, 4 rows affected (0.13 sec)
# Records: 4  Duplicates: 0  Warnings: 0

查看部门表数据

select * from dep;+----+-------------+--------------+
| id | dep_name    | dep_desc     |
+----+-------------+--------------+
|  1 | sm运动社    | 日常活动     |
|  2 | 游戏社      | 休闲娱乐     |
|  3 | 技术部      | 能力提升     |
|  4 | cp外交部    | 社交沟通     |
+----+-------------+--------------+
4 rows in set (0.00 sec)

查看员工表数据

select * from emp;+----+-----------+------------+--------+
| id | emp_name  | emp_gender | dep_id |
+----+-----------+------------+--------+
|  1 | dream     | male       |      1 |
|  2 | chimeng   | female     |      4 |
|  3 | mengmeng  | female     |      2 |
|  4 | drunkmeng | male       |      3 |
+----+-----------+------------+--------+
4 rows in set (0.00 sec)

4.多对多关系

以图书表和作者为例

• 站在图书表角度
  • 一本书可以有多个作者
• 站在作者表的角度
  • 一个作者可以写多本书
• 总结
  • 如果两个都可以那么就是多对多关系

*****  针对多对多的表关系，外键字段在第三张表中  *****

（1）建表会遇到的问题

创建图书表

id title price author_idcreate table book(id int primary key auto_increment,title varchar(32),price int,author_id int,foreign key(author_id) references author(id)on update cascadeon delete cascade)

创建作者表

id name age book_idcreate table author (id int primary key auto_increment,name varchar(32),age int,book_id int,foreign key(book_id) references book(id)on update cascadeon delete cascade
)

这种方式建表，由于外键具有”在创建表的时候一定要先创建被关联表“的特性，从而导致都无法创建彼此的关联表，针对多对多字段关系，不能在原有两张表的基础上创建外键，需要创建一张新表来建立两表的关系

（2）解决循环建表的问题

建表

book
id title priceauthor
id name agebook_connect
id boo_id author_id

创建表

# 图书表
create table book(id int primary key auto_increment, title varchar(32), price int
);# 作者表
create table author(id int primary key auto_increment,name varchar(32),age int
);# 中转联系表
create table book_connect(id int primary key auto_increment,author_id int,book_id int,foreign key(author_id) references author(id)on update cascadeon delete cascade,foreign key(book_id) references book(id)on update cascadeon delete cascade
);

查看表

desc book;+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| title | varchar(32) | YES  |     | NULL    |                |
| price | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)desc author;+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)desc book_connect;+-----------+---------+------+-----+---------+----------------+
| 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)

插入数据

insert into book(title,price) values("西游记",18),("水浒传",29),("三国演义",99),("如何让富婆爱上你",999);insert into author(name,age) values("dream",18),("chimeng",28),("mengmeng",38);insert into book_connect(author_id,book_id) values(1,3),(2,1),(2,3),(1,1);

查看表数据

select * from book;+----+--------------------------+-------+
| id | title                    | price |
+----+--------------------------+-------+
|  1 | 西游记                   |    18 |
|  2 | 水浒传                   |    29 |
|  3 | 三国演义                 |    99 |
|  4 | 如何让富婆爱上你         |   999 |
+----+--------------------------+-------+
4 rows in set (0.00 sec)select * from author;+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | dream    |   18 |
|  2 | chimeng  |   28 |
|  3 | mengmeng |   38 |
+----+----------+------+
3 rows in set (0.00 sec)select * from book_connect;+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       3 |
|  2 |         2 |       1 |
|  3 |         2 |       3 |
|  4 |         1 |       1 |
+----+-----------+---------+
4 rows in set (0.00 sec)

5.一对一关系

在MySQL的关系中没有多对一的说法

一对多、多对多 都叫做 一对多

如果一个表的字段特别多，每次查询又不是所有字段的数据都需要，那么可以将表一分为二

• 用户表
  • 一个用户不能对应多个用户详情
• 用户详情表
  • 一个用户详情表不属于多个用户
• 结论
  • 单向的一对多都不能成立，那么这个时候两者之间的表关系要么是一对一，要么就没有关系

创建表

authors
id name age author_detail_idauthor_detail
id phone addr

一对一，外键建在任意一方都可以，但是建议建立在查询频率较高的表内

创建表

create table author_detail(id int primary key auto_increment,phone int,addr varchar(64)
);create table authors(id int primary key auto_increment,name varchar(16),age int,author_detail_id int unique,foreign key(author_detail_id) references author_detail(id)on update cascadeon delete cascade
);

查看表

desc author_detail;+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| phone | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(64) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)desc authors;+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| id               | int(11)     | NO   | PRI | NULL    | auto_increment |
| name             | varchar(16) | YES  |     | NULL    |                |
| age              | int(11)     | YES  |     | NULL    |                |
| author_detail_id | int(11)     | YES  | UNI | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

6.小结

• 表关系的建立需要用到foreign key
• 一对多
  • 外键在多的一方
• 多对多
  • 建立第三张表作为媒介
• 一对一
  • 建在任意一方均可，推荐建在查询频率较高的表里
• 判断表与表之间的关系要站在双方的角度考虑

二.多表查询

1.数据准备

创建数据库

create database day04;

创建表

create table dep(id int,name varchar(20)
);CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20),sex ENUM("male","female") NOT NULL DEFAULT "male",age INT,dep_id INT
);

插入数据

insert into dep(id,name) values
("200","技术部"),
("201","人力资源"),
("202","销售部"),
("203","运营部"),
("204","售后部"),
("206","外交部");insert into emp(name,sex,age,dep_id) values
("dream","male",18,200),
("chimeng","female",18,201),
("menmgneg","male",38,202),
("hope","male",18,203),
("own","male",28,204),
("thdream","male",18,205);

2.多表查询案例

• 只要涉及到多表查询就两种思路：
  • 联表
  • 子查询

（1）联表

先拿到部门和员工表拼接之后的结果

对拼接后的结果进行部门分组

查询数据

select * from emp inner join dep on emp.dep_id = dep.id;+----+----------+--------+------+--------+------+--------------+
| id | name     | sex    | age  | dep_id | id   | name         |
+----+----------+--------+------+--------+------+--------------+
|  1 | dream    | male   |   18 |    200 |  200 | 技术部       |
|  2 | chimeng  | female |   18 |    201 |  201 | 人力资源     |
|  3 | menmgneg | male   |   38 |    202 |  202 | 销售部       |
|  4 | hope     | male   |   18 |    203 |  203 | 运营部       |
|  5 | own      | male   |   28 |    204 |  204 | 售后部       |
+----+----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

查询数据

select dep.name from emp inner join dep 
on emp.dep_id = dep.id
group by dep.name
having avg(age) > 25
;select dep.name from emp inner join dep 
on emp.dep_id = dep.id
group by dep.name
having avg(age) > 25
;

（2）子查询

分步操作

查询数据

select name from dep where id in 
(select dep_id from emp group by dep_idhaving avg(age) > 25);+-----------+
| name      |
+-----------+
| 销售部    |
| 售后部    |
+-----------+
2 rows in set (0.00 sec)

三.Navicat的使用

1.用途

Navicat可以充当多个数据库的客户端

2.简单使用

（1）execute

返回的是查询到的数据的条数

# -*-coding: Utf-8 -*-
# @File : 01 简介 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql# （1）链接数据库
conn = pymysql.connect(# 指定 ip端口host='127.0.0.1',port=3306,# 指定用户名密码user='root',password='1314521',# 指定数据库database='day04',# 指定编码charset='utf8'
)# （2）创建游标对象 - 执行命令 对象
cursor = conn.cursor()# （3）创建SQL语句
sql = 'select * from emp;'# （4）游标对象执行SQL语句
# 【1】execute: 返回的是数据的条数
res = cursor.execute(sql)
print(res) # 6

（2）fetchone

返回查询到的第一条数据

返回数据以元祖形式：

# -*-coding: Utf-8 -*-
# @File : 01 简介 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql# （1）链接数据库
conn = pymysql.connect(# 指定 ip端口host='127.0.0.1',port=3306,# 指定用户名密码user='root',password='1314521',# 指定数据库database='day04',# 指定编码charset='utf8'
)# （2）创建游标对象 - 执行命令 对象
cursor = conn.cursor()# （3）创建SQL语句
sql = 'select * from emp;'# （4）游标对象执行SQL语句
# 【2】
res = cursor.execute(sql) # 先执行这条语句
res1 = cursor.fetchone() # 拿第一条数据
print(res1)  # (1, 'dream', 'male', 18, 200)

返回数据以字典形式：

# -*-coding: Utf-8 -*-
# @File : 01 简介 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql# （1）链接数据库
conn = pymysql.connect(# 指定 ip端口host='127.0.0.1',port=3306,# 指定用户名密码user='root',password='1314521',# 指定数据库database='day04',# 指定编码charset='utf8'
)# （2）创建游标对象 - 执行命令 对象
# cursor=pymysql.cursors.DictCursor：将查询的参数以字典的形式返回
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# （3）创建SQL语句
sql = 'select * from emp;'# （4）游标对象执行SQL语句
# 【2】
res = cursor.execute(sql)  # 先执行这条语句
res1 = cursor.fetchone()  # 拿一条数据 - 返回的是字典
print(res1)  # {'id': 1, 'name': 'dream', 'sex': 'male', 'age': 18, 'dep_id': 200}

（3）fetchall

# -*-coding: Utf-8 -*-
# @File : 01 简介 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql# （1）链接数据库
conn = pymysql.connect(# 指定 ip端口host='127.0.0.1',port=3306,# 指定用户名密码user='root',password='1314521',# 指定数据库database='day04',# 指定编码charset='utf8'
)# （2）创建游标对象 - 执行命令 对象
# cursor=pymysql.cursors.DictCursor：将查询的参数以字典的形式返回
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# （3）创建SQL语句
sql = 'select * from emp;'# （4）游标对象执行SQL语句
# 【2】
res = cursor.execute(sql)  # 先执行这条语句
res2 = cursor.fetchall()  # 拿一条数据 - 返回的是列表
print(res2)  # [{'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}, {'id': 3, 'name': 'menmgneg', 'sex': 'male', 'age': 38, 'dep_id': 202}, {'id': 4, 'name': 'hope', 'sex': 'male', 'age': 18, 'dep_id': 203}, {'id': 5, 'name': 'own', 'sex': 'male', 'age': 28, 'dep_id': 204}, {'id': 6, 'name': 'thdream', 'sex': 'male', 'age': 18, 'dep_id': 205}]

（4）fetchmany

# -*-coding: Utf-8 -*-
# @File : 01 简介 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql# （1）链接数据库
conn = pymysql.connect(# 指定 ip端口host='127.0.0.1',port=3306,# 指定用户名密码user='root',password='1314521',# 指定数据库database='day04',# 指定编码charset='utf8'
)# （2）创建游标对象 - 执行命令 对象
# cursor=pymysql.cursors.DictCursor：将查询的参数以字典的形式返回
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# （3）创建SQL语句
sql = 'select * from emp;'# （4）游标对象执行SQL语句
# 【2】
res = cursor.execute(sql)  # 先执行这条语句
res3 = cursor.fetchmany(2)  # 拿指定条数据 - 返回的是列表
print(res3) # [{'id': 1, 'name': 'dream', 'sex': 'male', 'age': 18, 'dep_id': 200}, {'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}]

（5）scroll

# -*-coding: Utf-8 -*-
# @File : 02 scroll方法 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysqldef connect_mysql():import pymysql# （1）链接数据库conn = pymysql.connect(# 指定 ip端口host='127.0.0.1',port=3306,# 指定用户名密码user='root',password='1314521',# 指定数据库database='day04',# 指定编码charset='utf8')return conndef create_cursor():conn = connect_mysql()# （2）创建游标对象 - 执行命令 对象# cursor=pymysql.cursors.DictCursor：将查询的参数以字典的形式返回cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# （3）创建SQL语句sql = 'select * from emp;'cursor.execute(sql)return cursordef main_fetch():cursor = create_cursor()print('第一次fetchone:>>>>', cursor.fetchone())print('第二次fetchone:>>>>', cursor.fetchone())print('fetchall:>>>>', cursor.fetchall())# 第一次fetchone:>>>> {'id': 1, 'name': 'dream', 'sex': 'male', 'age': 18, 'dep_id': 200}# 第二次fetchone:>>>> {'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}# fetchall:>>>> [{'id': 3, 'name': 'menmgneg', 'sex': 'male', 'age': 38, 'dep_id': 202}, {'id': 4, 'name': 'hope', 'sex': 'male', 'age': 18, 'dep_id': 203}, {'id': 5, 'name': 'own', 'sex': 'male', 'age': 28, 'dep_id': 204}, {'id': 6, 'name': 'thdream', 'sex': 'male', 'age': 18, 'dep_id': 205}]# 当已经执行了 fetchone 时 光标就会向下移动一次 所以最后的查询是从索引 3 开始的def main_scroll():cursor = create_cursor()# 【1】 cursor.scroll(1, 'absolute'))# 控制 光标的移动# print('第一次fetchone:>>>>', cursor.fetchone())# print('第二次fetchone:>>>>', cursor.fetchone())# print('第二次fetchone:>>>>', cursor.scroll(1, 'relative'))  # 相当于光标所在的位置向后移动一次# print('fetchall:>>>>', cursor.fetchall())# 第一次fetchone:>>>> {'id': 1, 'name': 'dream', 'sex': 'male', 'age': 18, 'dep_id': 200}# 第二次fetchone:>>>> {'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}# 第二次fetchone:>>>> None# fetchall:>>>> [{'id': 4, 'name': 'hope', 'sex': 'male', 'age': 18, 'dep_id': 203}, {'id': 5, 'name': 'own', 'sex': 'male', 'age': 28, 'dep_id': 204}, {'id': 6, 'name': 'thdream', 'sex': 'male', 'age': 18, 'dep_id': 205}]# 当遇到 scroll 的时候 ，光标向下移动了一次，所以最后的索引是从4 开始的# 【2】 cursor.scroll(1, 'absolute'))print('第一次fetchone:>>>>', cursor.fetchone())print('第二次fetchone:>>>>', cursor.fetchone())print('第二次fetchone:>>>>', cursor.scroll(1, 'absolute'))  # 相当于光标所在的位置向后移动一次print('fetchall:>>>>', cursor.fetchall())# 第一次fetchone:>>>> {'id': 1, 'name': 'dream', 'sex': 'male', 'age': 18, 'dep_id': 200}# 第二次fetchone:>>>> {'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}# 第二次fetchone:>>>> None# fetchall:>>>> [{'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}, {'id': 3, 'name': 'menmgneg', 'sex': 'male', 'age': 38, 'dep_id': 202}, {'id': 4, 'name': 'hope', 'sex': 'male', 'age': 18, 'dep_id': 203}, {'id': 5, 'name': 'own', 'sex': 'male', 'age': 28, 'dep_id': 204}, {'id': 6, 'name': 'thdream', 'sex': 'male', 'age': 18, 'dep_id': 205}]# 相对于数据的起始位置向后移动一位if __name__ == '__main__':main_scroll()