表关系（外键）、表关系、外键约束、多对多表关系、一对一表关系、数据准备、多表查询、navicat客户化工具python操作MySQL、SQL文件

表关系（外键）

外键前戏

　　建立一张表：

　　　　1.表不清晰，分不清楚是员工表还是部门表

　　　　2.字段需要重复写，浪费资源

　　　　3.兼容性差，牵一发动全身

如何解决

　　把该表拆分成员工表和部门表

拆分后问题

　　拆分后两张表没有关系

外键

　　通过字段可以查询到另一张表的数据

四种关系：

　　一对多、一对一、多对多、没有关系

判断表关系

　　换位思考

表关系：一对多

eg：换位思考

　　以图书馆和出版社为例

　　　　先站在图书表的角度

　　　　　　问:一本书能否有多个出版社？

　　　　　　答;不能

　　　　站在出版社的角度

　　　　　　问：一个出版社能出版多本书？

　　　　　　答：能

ps：一个能、一个不能，那么表关系就是‘一对多’

　　一对多外键建在多的一方

在SQL建立一对多关系

　　先创建图书表

　　　　create table book（id int primary key auto_increment，

　　　　　　　　　　　　　title varchar（200），

　　　　　　　　　　　　　prince decimal（8,2），

　　　　　　　　　　　　　publish_id int，

　　　　　　　　　　　　　foreign key（publish_id）reference publish(id)

　　　　　　　　　　　　　）;

　　在创建出版表

　　　　create table publish（id int primary key auto_increment，

　　　　　　　　　　　　　　title varchar（200））；

ps：多表的创建，先创建表的基本字段， 在添加外键字段

往表中录入数据

　　book 表中录入数据

　　　　insert into book (title, price, publish_id) values('水浒传', 1000, 1)，('西游记', 1000, 2);

　　publish 表中录入数据

　　　　　insert into publish (title) values ('北京出版社')，（'东晋出版社’）；

外键约束

1. 在创建表的时候，应该先创建被关联表(没有外键字段的表)
2. 在录入数据的时候，应该先录入被关联表(没有外键字段的表)
3. 在录入数据的时候，应该录入被关联表中已经存在的值.
4. 如果对被关联表中的数据进行修改和删除的时候，需要把关联表中的数据也跟着修改或者删除(不现实)，需要使用以下创建表关系：

　　先创建图书表
　　　　create table book(
　　　　　　　　　　　　id int primary key auto_increment,
　　　　　　　　　　　　title varchar(128),
　　　　　　　　　　　　price decimal(8, 2),
　　　　　　　　　　　　publish_id int,
　　　　　　　　　　　　foreign key(publish_id) references publish(id) # 意思是：book表中的publish_id和publish表中的id是外键关系
　　　　　　　　　　　　on update cascade # 级联更新
　　　　　　　　　　　　on delete cascade #
　　　　　　　　　　　　);

　　在创建出版表
　　　　create table publish(
　　　　　　　　　　　　　　id int primary key auto_increment,
　　　　　　　　　　　　　　title varchar(128)
　　　　　　　　　　　　　　);

　　但是，由于创建了外键关系和级联更新级联删除，那么，两张表之间就有了强制的约束关系，这样就增加了表与表之间的强耦合度

　　所以，以后实际项目中，我们大多数不建立这种强耦合关系，我们使用的是建立逻辑意义上的关系

表关系：多对多

以图书表和作者表为例

　　站在图书表的角度
　　　　问：一本图书能否有多个作者来写?
　　　　答：能
　　再站在作者表的角度
　　　　问：一个作者能否写多本书、
　　　　答：能
ps：此时表关系就是多对多

　　问题：外键字段建在哪里?
　　　　答案：多对多的外键字段需要建立第三张表来存储
在SQL层面建立多对多的关系
　　先创建图书表
　　　　create table book(
　　　　　　　　　　　　id int primary key auto_increment,
　　　　　　　　　　　　title varchar(128),
　　　　　　　　　　　　price decimal(8, 2)
　　　　　　　　　　　　);

　　在建立作者表
　　　　create table author(
　　　　　　　　　　　　　id int primary key auto_increment,
　　　　　　　　　　　　　name varchar(32)
　　　　　　　　　　　　　　);

 建立第三张表来保存两张表的关系
　　create table book2author(
　　　　　　　　　　　　　　id int primary key auto_increment,
　　　　　　　　　　　　　　book_id int,
　　　　　　　　　　　　　　author_id int,
　　　　　　　　　　　　　　foreign key(book_id) references book(id)
　　　　　　　　　　　　　　on update cascade
　　　　　　　　　　　　　　on delete cascade,
　　　　　　　　　　　　　　foreign key(author_id) references author(id)
　　　　　　　　　　　　　　on update cascade
　　　　　　　　　　　　　　on delete cascade
　　　　　　　　　　　　　　);

　　insert into book2author(book_id, author_id) values(1, 1),(1, 2),(2, 1);

表关系：一对一

以作者表和作者详情表为例
　　站在作者表的角度
　　　　问：一个作者能否有多个作者详情信息?
　　　　答：不能
　　再站在作者详情表的角度
　　　　问：一个作者详细信息能否对应多个作者、
　　　　答：不能
ps：两个都不能，表关系就是一对一

　　问题：外键字段建在哪里?
　　　　答案：一对一的外键字段可以建在任何一张表中，但是，推荐建在查询频率较高的一张表中
在SQL层面建立多对多的关系
　　create table author(
　　　　　　　　　　　　id int primary key auto_increment,
　　　　　　　　　　　　name varchar(32),
　　　　　　　　　　　　author_detail_id int unique,
　　　　　　　　　　　　foreign key (author_detail_id) references author_detail(id)
　　　　　　　　　　　);

　　create table author_detail(
　　　　　　　　　　　　　　id int primary key auto_increment,
　　　　　　　　　　　　　　addr varchar(32),
　　　　　　　　　　　　　　height decimal(5,2)
　　　　　　　　　　　　　　);

数据准备

create table dep(
　　　　　　id int primary key auto_increment,
　　　　　　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 values
　　(200,'技术'),
　　(201,'人力资源'),
　　(202,'销售'),
　　(203,'运营'),
　　(205,'保洁');

insert into emp(name,sex,age,dep_id) values
　　('jason','male',18,200),
　　('egon','female',48,201),
　　('kevin','male',18,201),
　　('nick','male',28,202),
　　('owen','male',18,203),
　　('jerry','female',18,204);

多表查询（核心，重要）

多表的意思就是多张表连在一起使用

多表查询的思路：
1. 子查询
　　 一条SQL语句的执行结果当成另外一条SQL语句的执行条件
　　大白话：分步操作

问题：查看姓名为jason的部门名称:
　　1. 先查询部门id
　　　　select dep_id from emp where name='jason';

　　2. 拿着部门id作为条件，在去部门表中查询部门名称
　　　　select * from dep where id=200;

　　3. 把上述两条SQL语句合并为一条SQL语句
　　　　select * from dep where id= (select dep_id from emp where name='jason');
2. 连表查询
把多张实际存在的表按照表关系连成一张虚拟表(不是实际存在的表，而是临时在内存中存的)
　　select *from emp,dep where emp.dep_id=dep.id;

我们连表的时候有专业的连表语法
　　inner join # 内连接，数据只取两张表中共有的数据
　　left join # 左连接，数据以左表为准，展示左表所有的数据，右表没有的数据使用NULL填充
　　right join # 又连接，数据以右表为准，展示右表所有的数据，左表没有的数据使用NULL填充
　　union # 连接多条SQL语句执行的结果

1. inner join
select * from emp inner join dep on emp.dep_id=dep.id;

2. left join
select * from emp left join dep on emp.dep_id=dep.id;

3. right join
select * from emp right join dep on emp.dep_id=dep.id;

4. union
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;

5. 还可以给表名起别名
select * from emp as e inner join dep as d on e.dep_id=d.id;

Navicat客户化工具

是一个工具，需要下载使用
它不是免费的，是收费的
　　1. 破解
　　2. 免费试用14天

去官网下载:https://www.navicat.com.cn/download/navicat-premium

create table t1 (
　　　　　　　　id int primary key auto_increment comment '主键id',
　　　　　　　);

Python操作MySQL

 Python操作MySQL，对于Python这门语言来说，就是客户端
你使用Python操作mysql的时候，也要保证MySQL的服务端正常启动

如何操作MySQL呢

需要借助于第三方模块
1. pymysql
2. mysqlclient----->非常好用，一般情况下很难安装成功
3. mysqldb

pip install pymysql;
import pymysql

1. 连接MySQL的服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='root',
db='db8',
charset='utf8',
autocommit=True
)

2. 获取游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

3. 执行SQL语句了
 sql = 'select *from emp;'
sql = "insert into emp (name, sex, age, dep_id, gender) values('aa', 'male', 10, 1, 0)"
4. 开始执行
affect_rows = cursor.execute(sql) # 影响的行数
print(affect_rows) # 6行

'''增加,修改，删除的数据的时候，需要二次提交， 只有查询的时候不需要二次提交'''
 conn.commit()
5. 如何拿到具体的数据
 print(cursor.fetchall())

 for i in cursor.fetchall():
 pass

 {'id': 1, 'name': 'jason', 'sex': 'male', 'age': 18, 'dep_id': 200, 'gender': 0}
 print(cursor.fetchone()) # None
 print(cursor.fetchmany(3))