Mysql操作——数据库编程

本文主讲：Mysql数据库中外键的使用、事务的概念、Mysql数据库与Python进行交互等….

外键使用

外键的使用场景

答：两个数据表相关联，一个数据表是产品名称，另一个数据表是这些产品的分类，向产品名称这个数据表添加数据时，但是产品分类这个数据表内没有这个产品的相应分类，我们就不能向名称数据表进行插入数据，这时就要用到外键！

什么是外键

答：两个数据表相关联时，一个数据表的主键出现在另外一个数据表中，称另外这个数据表内的这个字段为外键

图示：

怎么向已经存在的字段添加外键约束

关键字：foreign key 、references

语法：alter table product add foreign key (nid) references classification(id);

表示向 product 表的 nid 字段添加外键，这个外键引用于 classification数据表的主键（id字段）

快速代码体验：

怎么在创建数据表时添加外键

语法：

create table product_test（
		id int not null primary key，
		name varchar（10）not null，
		mid int not null，
		foreign key (mid) references  classification（id））；

上面语法表示创建 product_test 数据表并且为mid字段加上外键，foreign key (mid) references classification（id）语法表示将mid字段设置为外键，外键引用于 classification数据表的 id 字段

注意事项：创建数据表添加外键，引用于哪个数据表的字段，那么这个数据表必须事先存在，例如上述的classification数据表之前就已经创建好！

快速代码体验：

怎么查询数据表中的外键字段名称

show create table 要查找外键的数据表名

例如：show create table product_test;

代码演示

注意事项：上面查询的字段名称不是 mid 而是刚开始的那段字符串，即 product_test_ibfk_1 才是字段名称

怎么删除数据表中的字段外键

alter table 数据表名 drop foreign key 外键字段名

快速代码体验

视图的概念【重点】

什么是视图，视图的使用场景

答：简而言之就是将复杂的 SQL 语句封装成一张视图！只可查询数据，不可更改

使用场景：需要重复使用某条SQL语句时，就可以将这条 SQL 语句封装成视图

图示：

视图的使用【重点】

视图的创建语法以及怎么查看视图是否创建成功

创建语法：create view 视图名 as 封装的SQL语句；

查看语法：show tables；

注意事项：

1：视图名一般要以 v_ 开头 例如 v_name

2：视图只是一个虚拟的表，当视图数据来源的表数据发生变化时，视图内的数据也会发生变化！

快速代码体验：

怎么查看视图内的数据以及怎么删除视图

查看视图数据语法：select * from 视图名；

删除视图语法：drop view 视图名

快速代码体验；

事务概念及特性【面试题】

事物的使用场景

答：假设控制数据库查询某些数据需要好几条SQL语句，而且这几条语句要么全都执行成功，要么全都执行失败，我们就可以把这几条SQL语句加到事物当中！比如说去银行给别人汇钱操作，分为 查询余额、你给别人汇款操作、别人收款操作，要么这几个步骤全部执行成功，要么这几个步骤全部执行失败！

简而言之，数据库的事务，就是当执行某操作，其中一个步骤失败，就回到未执行前的状态！要么事务所有步骤全部执行成功，要么全部失败！

事务功能：保证某个操作要么一定成功执行，要么一定失败执行

事务的四大特性（ACID）

1.原子性：一个事物必须被视为不可分割的最小单元，也就是事务内的所有步骤都是不可分割，要么所有步骤全部成功执行，要么全都失败回滚

2.一致性：数据库总是从一个状态转换到另外一个状态，汇钱例子，我向你打款500元，我余额减少500，你的余额必须增加500

3.隔离性：一个事务所做的修改在最终提交以前，对外是不可见的，例如汇款，我的余额600，正在向你汇款500，汇款的过程未结束时，别人是不可在用你卡向别人汇款的，因为他看到你的余额还是600元

4.持久性：一旦事务提交，则其所做的修改将永久保存到数据库

图示

事务使用【重点】

什么是表引擎以及怎么查看数据表表引擎分类

答：表引擎就是数据表在系统中的存储方式

查看数据表表引擎分类语法：show engines；

代码演示：

怎么查看数据表使用的是什么表引擎

语法：show create table 数据表名；

代码演示：

begin、commit、rollback关键字对事务的影响

begin：开启事务，开启事务之后对数据库的更改都要符合事务的使用规范

commit：提交事务，开启事务后对数据库修改数据，如果不 commit 一下，别人看见的数据还是未修改之前的，所以数据修改完成后，需要commit才能正式生效，注意 commit之后 在 rollback 不能回滚之前的数据库状态

rollback：事务回滚，开启事务后修改数据，没有 commit 之前，执行 rollback后，数据库会回到事务begin之前的状态

注意事项：在事务开启之后，执行了 commit 或者 rollback 之后，事务就关闭了，想在再次开启事务，需再次执行 begin 指令！

代码演示：

验证事务的ACID特性【重点】

目标：掌握如何验证事务的隔离性（I）

如何验证事务的隔离性（I）

答：LINUX同时开启两个客户端，一个客户端开启事务，对数据进行修改，并且不要commit与rollback（代表事务没有结束），另一个客户端不需要开启事务，同时对这个数据进行更改，就会发现没开启事务的更改数据不成功！

代码演示：

数据库的三范式【了解】

前言：数据库设计三范式就是设置数据库时的注意事项

数据库设计的第一、第二、第三范式

第一范式：强调列的原子性，即每一个字段都不能再次拆分成其他几个字段

第二范式：需满足第一范式，一个表内必须要有一个主键，非主键字段必须要完全依赖于主键字段，不能只有一部分字段依赖主键，即所有非主键字段全都要完全依赖于一个主键，不能有一部分依赖这个主键，其他不依赖这个主键

注意事项：如果数据表不满足第二范式，可以将数据表进行拆分

第三范式：必须要满足第二范式，非主键字段必须直接依赖于主键字段，不能存在传递依赖

注意事项：如果数据表不满足第二范式，可以将数据表进行拆分

E-R模型及表间关系【了解】

E-R模型的使用场景

E-R模型的组成部分

组成部分：实体、实体属性、联系

数据表之间的一对一、一对多、多对多关系

一对一：数据表1中的一个字段数据对应数据表2中的一个字段数据，可以靠两个表来实现关系，符号是 1-1

一对多：数据表1中的一个字段数据对应数据表2中的多个字段数据，可以靠两个表来实现关系，图示一个班级对应多名学生，符号为 1-N表示

多对多：数据表1中的一个字段数据对应数据表2中的多个字段数据，数据表2中的一个字段数据对应数据表1中的多个字段数据，不能靠两个表来实现关系，需要第三个中间表才能实现数据的保存，图示一个科目可以有多个学生选，一个学生也可以选多们课程，符号为 N-N表示

58到家数据库的30条军规解读【重点】

军规阅读地址：

https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651959906&idx=1&sn=2cbdc66cfb5b53cf4327a1e0d18d9b4a&chksm=bd2d07be8a5a8ea86dc3c04eced3f411ee5ec207f73d317245e1fefea1628feb037ad71531bc&scene=21&key=e82f935468864c7d48052912f819a6dd5e6242373178c6cf6b2a30bc7a3bc45cf528644761f1c83760a26aa894abf4ecc9a77152e7a9d688d98cf9acbefe04f63cb0dbecd86ed0463d85e6e4022081a7&ascene=7&uin=MjE4OTMzMzMwNA%3D%3D&devicetype=Windows+10&version=6203005d&pass_ticket=HwLIq9t4Fwuxfe3EcHX5%2B6GOZG4yYlLMCZ8YH%2FvGq5QnbR%2FjduDlkLzmQa2Y%2BuiY&winzoom=1#%23

重点内容提取：

1：数据库必须使用 InnoDB存储引擎

2：数据库必须使用 utf-8进行编码

3：并发量大、数据量大的互联网业务禁止使用外键

4：禁止使用小数存储货币

5：禁止使用select * 只获取必要的字段，需要显示说明列属性

6：禁止大表使用join（连接）

python连接操作MySQL【重点】

python 连接数据库的功能

答：用 python 代码充当客户端，对数据进行增删改查等操作

python 连接数据库的步骤并实现查询数据库操作

连接前准备： pip3 install pymysql 安装 pymysql 包

第一步：导入 pymysql 模块 即 import pymysql

第二步：建立数据库连接对象（相当于建立一条通信的高速公路）

语法：connection = pymysql.connect（host，port，root，possword，database，charset）

参数详解：

host：数据库所在的主机

port：连接数据库主机的端口号，可省略，默认3306

user：连接数据库用户名

password：连接数据库密码

database：要连接的数据库名

charset：通信采用的编码，可省略，默认是 utf-8

第三步：在连接（公路）的基础上，（利用高速公路）创建游标对象（相当于高速公路上的通信兵）
详解：cursor 相当于数据库与 python 端的通信兵，数据库数据的操作以及取出都是靠这通信兵来完成

语法：游标对象 = 连接对象.cursor（） 例如 cursor_obj = connection.cursor（）

第四步：利用通信兵的 execute 方法对数据库的数据进行查询操作，并返回影响数据库几行数据的结果！

关键字：execute

语法：num_result = cursor_obj.execute（SQL语句）

注意：

1：返回的结果是num类型

2：SQL语句是查询语句那就影响所有行数

3：SQL语句是字符串类型

第五步：利用通信兵的fetchone或者fetchall方法查询数据库的数据

关键字：

fetchone（）：查询数据库第一行数据

fetchall（）：查询数据库所有数据

语法：

1：data_result = cursor_obj.fetchone( )

2：data_result = cursor_obj.fetchall( )

注意：fetchone或者fetchall方法查询数据返回的结果都是元组，不同的是fetchone返回的是一个元组，如果数据库有多行数据则fetchall返回的是元组的嵌套

第六步：关闭通信兵以及高速公路

关键字：close

语法：

connection.close（）
cursor_obj.close( )

截图演示：

代码演示：

用 python 连接数据库并实现数据库的增删改操作

关键字：execute

语法：

返回影响行数值 = 游标对象 . execute（增删改SQL语句） 例如 result_num = cursor_obj.execute(SQL语句)

连接对象.commit（） 即对数据表修改后需要提交！！

注意事项：

1：在对数据库进行增删改操作之后，需要提交修改，做的更改才会生效，否则不生效

2：对数据库进行增删改需要提交，查询操作不需要提交！

3：commit提交不是用游标对象提交，而是用连接对象来提交

代码演示：

游标对象的 fetchone 与 fetchall 方法在数据库不同操作时打印结果的不同！

答：fetchone（）与fetchall（）方法只有在查询数据库（即查询数据库的SQL语句：select……）的时候才能打印出数据，当增删改数据库里面数据的时候，使用fetchone（）或fetchall（）方法是查询不出来数据的

SQL防注入【重点】

什么是SQL注入

答：在查询数据库数据操作时，当别人在输入一些特殊的字符串的时候，可能会获得我们数据库里面的大量数据，但是这些数据我们是不想让它获取的，这样就造成了数据泄露，也称为SQL注入

怎么防止SQL注入

实现点：在利用 cursor 通信兵对象的 execute 方法时，向这个方法内传入第二个参数可有效防止SQL注入（占位符）

实现语法：num_result = cur_obj.execute(SQL语句，params) 其中 params 是一个列表参数，列表内存放着SQL语句要查询的字段名或其他参数

注意：参数列表中可以有多个参数，因此SQL语句中也要有多个占位符与其一一对应，

代码演示：

未完待续…..