Python基础 3.8 MySQL数据库的高级应用

3.8 MySQL数据库的高级使用

3.8.1 分表需求

当一个表无法满足我们任意筛选的时候，我们一般会为其创建多一个表，使用创建的表来进行筛选查询，比如我要查询相关品牌的所有机型，单靠where实际上效率是很低的，使用连接能对应提高效率。但是我们也不该重新创建并且一行一行输入数据，这样效率很低。

1. 将查询结果插入到商品分类表格中

目前只有一个goods表，我们想要增加一个商品分类信息，比如：移动设备这个分类信息，只通过goods表无法完成商品分类的添加，那么如何实现添加商品分类信息的操作?

答案:

1. 创建一个商品分类表，把goods表中的商品分类信息添加到该表中。
2. 将goods表中的分类名称更改成商品分类表中对应的分类id

首先我们创建一个商品分类表格

-- 创建商品分类表
create table good_cates(
    id int not null primary key auto_increment, 
    name varchar(50) not null
);

然后把商品里所有的商品类型添加到该表格里

-- 查询goods表中商品的分类信息
select cate_name from goods group by cate_name;

-- 将查询结果插入到good_cates表中
insert into good_cates(name) select cate_name from goods group by cate_name;

-- 添加移动设备分类信息
insert into good_cates(name) values('移动设备');

表复制格式

insert into 表2(选中的的字段名) select (要添加的字段名 from 表1 group by 要添加的字段名);

2. 使用连接更新goods表中商品分类信息

我们已经创建了一个商品分类表(good_cates)，并完成了商品分类信息的插入，现在需要更新goods表中的商品分类信息，把商品分类名称改成商量分类id。

接下来我们实现第二步操作:

• 将goods表中的分类名称更改成商品分类表中对应的分类id

-- 查看goods表中的商品分类名称对应的商品分类id
select * from goods inner join good_cates on goods.cate_name = good_cates.name;

-- 把该语句中from 后的语句理解为一张虚表  
update goods g inner join good_cates gc on g.cate_name=gc.name set g.cate_name=gc.id;

3. 创建品牌表的时候直接插入goods表里的品牌字段

1. 创建一个品牌表，把goods表中的品牌信息添加到该表中。
2. 将goods表中的品牌名称更改成品牌表中对应的品牌id

-- 查询品牌信息 
select brand_name from goods group by brand_name;

-- 通过create table ...select来创建数据表并且同时插入数据
-- 创建商品分类表，注意: 需要对brand_name 用as起别名，否则name字段就没有值
create table good_brands (     
id int unsigned primary key auto_increment,     
name varchar(40) not null) select brand_name as name from goods group by brand_name;

说明:

• create table (…) select 列名 … 表示创建表并插入数据
• select 字段名 as 别名 中别名必须与创建表的字段名一致
• 这种也是表复制的一种方式

3. 更新goods表里的品牌信息

-- 将goods表中的品牌名称更改成品牌表中对应的品牌id
update goods as g inner join good_brands gb on g.brand_name = gb.name set g.brand_name = gb.id;

4. 批量修改goods表结构

目前我们已经把good表中的商品分类和品牌信息已经更改成了商品分类id和品牌id，接下来需要把 cate_name 和 brand_name 字段分别改成 cate_id和 brand_id 字段，类型都改成int类型

-- 查看表结构
desc goods;
-- 通过alter table语句修改表结构
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;

拓展

modify add,drop等操作亦可以批量操作

 alter table goods modify cate_id int unsigned not null, modify brand_id int unsigned not null;

3.8.2 事务

1. 事务的介绍

事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。

事务的使用场景:

在日常生活中，有时我们需要进行银行转账，这个银行转账操作背后就是需要执行多个SQL语句，假如这些SQL执行到一半突然停电了，那么就会导致这个功能只完成了一半，这种情况是不允许出现，要想解决这个问题就需要通过事务来完成。

2. 事务的四大特性

• 原子性(Atomicity)
• 一致性(Consistency)
• 隔离性(Isolation)
• 持久性(Durability)

原子性:

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性:

数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在转账过程中系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

隔离性:

通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

持久性:

一旦事务提交，则其所做的修改会永久保存到数据库。

说明:

事务能够保证数据的完整性和一致性，让用户的操作更加安全。

3. 事务的使用

在使用事务之前，先要确保表的存储引擎是 InnoDB 类型, 只有这个类型才可以使用事务，MySQL数据库中表的存储引擎默认是 InnoDB 类型。

查看MySQL数据库支持的表的存储引擎:

-- 查看MySQL数据库支持的表的存储引擎
show engines;

说明:

• 常用的表的存储引擎是 InnoDB 和 MyISAM
• InnoDB 是支持事务的
• MyISAM 不支持事务，优势是访问速度快，对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表

查看goods表的创表语句:

-- 选择数据库
use jing_dong;
-- 查看goods表
show create table goods;

mysql root@(none):jing_dong> show create table goods;
+-------+--------------------------------------------------------+
| Table | Create Table                                           |
+-------+--------------------------------------------------------+
| goods | CREATE TABLE `goods` (                                 |
|       |   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,       |
|       |   `name` varchar(150) NOT NULL,                        |
|       |   `cate_id` int(10) unsigned NOT