正文

7.Mysql数据库表引擎与字符集

1.服务器处理客户端请求

其实不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送

一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。那服务器进程对客户

端进程发送的请求做了什么处理，才能产生最后的处理结果呢？客户端可以向服务器发送增删改查各类请求，我们

这里以比较复杂的查询请求为例来画个图展示一下大致的过程：

虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓

存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。

2.存储引擎

MySQL 服务器把数据的存储和提取操作都封装到了一个叫 存储引擎 的模块里。我们知道 表 是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是 存储引擎 负责的事情。为了实现不同的功能， MySQL 提供了各式各样的 存储引擎 ，不同 存储引擎 管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

存储引擎以前叫做 表处理器 ，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

为了管理方便，人们把 连接管理 、 查询缓存 、 语法解析 、 查询优化 这些并不涉及真实数据存储的功能划分为MySQL server 的功能，把真实存取数据的功能划分为 存储引擎 的功能。各种不同的存储引擎向上边的 MySQLserver 层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像"读取索引第一条内容"、"读取索引下一条内容"、"插入记录"等等。

所以在 MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

MySQL 支持非常多种存储引擎：

ARCHIVE 用于数据存档（行被插入后不能再修改）

BLACKHOLE 丢弃写操作，读操作会返回空内容

CSV 在存储数据时，以逗号分隔各个数据项

FEDERATED 用来访问远程表

InnoDB 具备外键支持功能的事务存储引擎

MEMORY 置于内存的表

MERGE 用来管理多个MyISAM表构成的表集合

MyISAM 主要的非事务处理存储引擎

NDB MySQL集群专用存储引擎

3.MyISAM和InnoDB表引擎的区别

1) 事务支持

MyISAM不支持事务，而InnoDB支持。

事务：访问并更新数据库中数据的执行单元。事物操作中，要么都执行要么都不执行

2) 存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。

.frm文件存储表结构。

.MYD文件存储数据。

.MYI文件存储索引。

InnoDB：主要分为两种文件进行存储

.frm 存储表结构

.ibd 存储数据和索引 （也可能是多个.ibd文件，或者是独立的表空间文件）

3) 表锁差异

MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

InnoDB：支持事务和行级锁，是innodb的最大特色。

行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

4) 表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

InnoDB的主键范围更大，最大是MyISAM的2倍。

5) 表的具体行数

MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。 InnoDB：没有保存表的总行数

(只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，

myisam和innodb处理的方式都一样。

6) CURD操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

7) 外键

MyISAM：不支持 InnoDB：支持

8) 查询效率

MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

推荐考虑使用InnoDB来替代MyISAM引擎，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。

另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。

9）MyISAM和InnoDB两者的应用场景

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。 InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。现在默认使用InnoDB。

4.了解一下字符集和乱码

字符集简介

我们知道在计算机中只能存储二进制数据，那该怎么存储字符串呢？当然是建立字符与二进制数据的映射关系了，

建立这个关系最起码要搞清楚两件事儿：

1. 你要把哪些字符映射成二进制数据？

也就是界定清楚字符范围。

2. 怎么映射？

将一个字符映射成一个二进制数据的过程也叫做 编码 ，将一个二进制数据映射到一个字符的过程叫做 解码 。人们抽象出一个 字符集 的概念来描述某个字符范围的编码规则

我们看一下一些常用字符集的情况：

ASCII 字符集

共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码，我们看一些字符的编码方式：

'L' -> 01001100（十六进制：0x4C，十进制：76）

'M' -> 01001101（十六进制：0x4D，十进制：77）

ISO 8859-1 字符集

共收录256个字符，是在 ASCII 字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名 latin1 。

GB2312 字符集

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容 ASCII 字符集，所以在编码方式上显得有些奇怪：

如果该字符在 ASCII 字符集中，则采用1字节编码。

否则采用2字节编码。

这种表示一个字符需要的字节数可能不同的编码方式称为 变长编码方式 。比方说字符串 '爱u' ，其中 '爱' 需要用2个字节进行编码，编码后的十六进制表示为 0xCED2 ， 'u' 需要用1个字节进行编码，编码后的十六进制表示为 0x75 ，所以拼合起来就是 0xCED275 。

小贴士： 我们怎么区分某个字节代表一个单独的字符还是代表某个字符的一部分呢？别忘了 ASCII 字符集只收录128个字符，使用0～127就可以表示全部字符，所以如果某个字节是在0～127之内的，就意味着一个字节代表一个单独的字符，否则就是两个字节代表一个单独的字符。

GBK 字符集

GBK 字符集只是在收录字符范围上对 GB2312 字符集作了扩充，编码方式上兼容 GB2312 。

utf8 字符集

收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用1～4个字节，比方说这样：

'L' -> 01001100（十六进制：0x4C）

'啊' -> 111001011001010110001010（十六进制：0xE5958A）

小贴士： 其实准确的说，utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、

utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符。更详细的Unicode和其编码方案的知识不是本书的重点，大家上网查查哈～ MySQL中并不区分字符集和编码方案的概念，所以后边唠叨的时候把utf8、utf16、utf32 都当作一种字符集对待。

对于同一个字符，不同字符集也可能有不同的编码方式。比如对于汉字 '我' 来说， ASCII 字符集中根本没有收录这个字符， utf8 和 gb2312 字符集对汉字 我 的编码方式如下：

utf8编码：111001101000100010010001 (3个字节，十六进制表示是：0xE68891)

gb2312编码：1100111011010010 (2个字节，十六进制表示是：0xCED2)

5.MySQL中的utf8和utf8mb4

我们上边说 utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而在 MySQL 中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以设计 MySQL的大叔偷偷的定义了两个概念：

utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。

utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

有一点需要大家十分的注意，在 MySQL 中 utf8 是 utf8mb3 的别名，所以之后在 MySQL 中提到 utf8 就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用 utf8mb4 。

字符集的查看

MySQL 支持好多好多种字符集，查看当前 MySQL 中支持的字符集可以用下边这个语句：

show charset;

8.MySQL 数据操作 DML

数据的DML操作：添加数据，修改数据，删除数据

添加数据

格式： insert into 表名[(字段列表)] values(值列表...);

--标准添加（指定所有字段，给定所有的值）

mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lamp138');

Query OK, 1 row affected (0.13 sec)

--指定部分字段添加值

mysql> insert into stu(name,classid) value('lisi','lamp138');

Query OK, 1 row affected (0.11 sec)

-- 不指定字段添加值

mysql> insert into stu value(null,'wangwu',21,'w','lamp138');

Query OK, 1 row affected (0.22 sec)

-- 批量添加值

mysql> insert into stu values

-> (null,'zhaoliu',25,'w','lamp94'),

-> (null,'uu01',26,'m','lamp94'),

-> (null,'uu02',28,'w','lamp92'),

-> (null,'qq02',24,'m','lamp92'),

-> (null,'uu03',32,'m','lamp138'),

-> (null,'qq03',23,'w','lamp94'),

-> (null,'aa',19,'m','lamp138');

Query OK, 7 rows affected (0.27 sec)

Records: 7 Duplicates: 0 Warnings: 0

修改数据

格式：update 表名 set 字段1=值1,字段2=值2,字段n=值n... where 条件

-- 将id为11的age改为35，sex改为m值

mysql> update stu set age=35,sex='m' where id=11;

Query OK, 1 row affected (0.16 sec)

Rows matched: 1 Changed: 1 Warnings: 0

-- 将id值为12和14的数据值sex改为m，classid改为lamp92

mysql> update stu set sex='m',classid='lamp92' where id=12 or id=14 --等价于下面

mysql> update stu set sex='m',classid='lamp92' where id in(12,14);

Query OK, 2 rows affected (0.09 sec)

Rows matched: 2 Changed: 2 Warnings: 0

删除数据

格式：delete from 表名 [where 条件]

-- 删除stu表中id值为100的数据

mysql> delete from stu where id=100;

Query OK, 0 rows affected (0.00 sec)

-- 删除stu表中id值为20到30的数据

mysql> delete from stu where id>=20 and id<=30;

Query OK, 0 rows affected (0.00 sec)

-- 删除stu表中id值为20到30的数据（等级于上面写法）

mysql> delete from stu where id between 20 and 30;

Query OK, 0 rows affected (0.00 sec)

-- 删除stu表中id值大于200的数据

mysql> delete from stu where id>200;

Query OK, 0 rows affected (0.00 sec)

9.MySQL数据查询SQL

语法格式：

select 字段列表|* from 表名

[where 搜索条件]

[group by 分组字段 [having 分组条件]]

[order by 排序字段 排序规则]

[limit 分页参数]

基础查询

# 查询表中所有列 所有数据

select * from users;

# 指定字段列表进行查询

select id,name,phone from users;

Where 条件查询

可以在where子句中指定任何条件

可以使用 and 或者 or 指定一个或多个条件

where条件也可以运用在update和delete语句的后面

where子句类似程序语言中if条件，根据mysql表中的字段值来进行数据的过滤

示例：

-- 查询users表中 age > 22的数据

select * from users where age > 22;

-- 查询 users 表中 name=某个条件值 的数据

select * from users where name = '王五';

-- 查询 users 表中 年龄在22到25之间的数据

select * from users where age >= 22 and age <= 25;

select * from users where age between 22 and 25;

-- 查询 users 表中 年龄不在22到25之间的数据

select * from users where age < 22 or age > 25;

select * from users where age not between 22 and 25;

-- 查询 users 表中 年龄在22到25之间的女生信息

select * from users where age >= 22 and age <= 25 and sex = '女';

and和or 使用时注意

假设要求 查询 users 表中 年龄为22或者25 的女生信息

select * from users where age=22 or age = 25 and sex = '女';

思考上面的语句能否返回符合条件的数据？

实际查询结果并不符合要求？

select * from users where age=22 or age = 25 and sex = '女';

+------+--------+------+-------+-------+------+------+

| id | name | age | phone | email | sex | mm |

+------+--------+------+-------+-------+------+------+

| 1 | 章三 | 22 | | NULL | 男 | 0 |

| 1002 | cc | 25 | 123 | NULL | 女 | NULL |

+------+--------+------+-------+-------+------+------+

2 rows in set (0.00 sec)

-- 上面的查询结果并不符合 查询条件的要求。

-- 问题出在 sql 计算的顺序上，sql会优先处理and条件，所以上面的sql语句就变成了

-- 查询变成了为年龄22的不管性别，或者年龄为 25的女生

-- 如何改造sql符合我们的查询条件呢？

-- 使用小括号来关联相同的条件

select * from users where (age=22 or age = 25) and sex = '女';

+------+------+------+-------+-------+------+------+

| id | name | age | phone | email | sex | mm |

+------+------+------+-------+-------+------+------+

| 1002 | cc | 25 | 123 | NULL | 女 | NULL |

+------+------+------+-------+-------+------+------+

1 row in set (0.00 sec)

Like 子句

我们可以在where条件中使用=,<,> 等符合进行条件的过滤，但是当想查询某个字段是否包含时如何过滤？

可以使用like语句进行某个字段的模糊搜索，

例如： 查询 name字段中包含五的数据

-- like 语句 like某个确定的值 和。where name = '王五' 是一样

select * from users where name like '王五';

+----+--------+------+-------+-----------+------+------+

| id | name | age | phone | email | sex | mm |

+----+--------+------+-------+-----------+------+------+

| 5 | 王五 | 24 | 10011 | ww@qq.com | 男 | 0 |

+----+--------+------+-------+-----------+------+------+

1 row in set (0.00 sec)

-- 使用 % 模糊搜索。%代表任意个任意字符

-- 查询name字段中包含五的

select * from users where name like '%五%';

-- 查询name字段中最后一个字符 为 五的

select * from users where name like '%五';

-- 查询name字段中第一个字符 为 王 的

select * from users where name like '王%';

-- 使用 _ 单个的下划线。表示一个任意字符，使用和%类似

-- 查询表中 name 字段为两个字符的数据

select * from users where name like '__';

-- 查询 name 字段最后为五，的两个字符的数据

select * from users where name like '_五';

注意：where子句中的like在使用%或者_进行模糊搜索时，效率不高，使用时注意：

尽可能的不去使用%或者_

如果需要使用，也尽可能不要把通配符放在开头处

Mysql中的统计函数（聚合函数）

max(),min(),count(),sum(),avg()

# 计算 users 表中 最大年龄，最小年龄，年龄和及平均年龄

select max(age),min(age),sum(age),avg(age) from users;

+----------+----------+----------+----------+

| max(age) | min(age) | sum(age) | avg(age) |

+----------+----------+----------+----------+

| 28 | 20 | 202 | 22.4444 |

+----------+----------+----------+----------+

-- 上面数据中的列都是在查询时使用的函数名，不方便阅读和后期的调用，可以通过别名方式 美化

select max(age) as max_age,

min(age) min_age,sum(age) as sum_age,

avg(age) as avg_age

from users;

+---------+---------+---------+---------+

| max_age | min_age | sum_age | avg_age |

+---------+---------+---------+---------+

| 28 | 20 | 202 | 22.4444 |

+---------+---------+---------+---------+

-- 统计 users 表中的数据量

select count(*) from users;

+----------+

| count(*) |

+----------+

| 9 |

+----------+

select count(id) from users;

+-----------+

| count(id) |

+-----------+

| 9 |

+-----------+

-- 上面的两个统计，分别使用了 count(*) 和 count(id),结果目前都一样，有什么区别？

-- count(*) 是按照 users表中所有的列进行数据的统计，只要其中一列上有数据，就可以计算

-- count(id) 是按照指定的 id 字段进行统计，也可以使用别的字段进行统计，

-- 但是注意，如果指定的列上出现了NULL值，那么为NULL的这个数据不会被统计

-- 假设有下面这样的一张表需要统计

+------+-----------+------+--------+-----------+------+------+

| id | name | age | phone | email | sex | mm |

+------+-----------+------+--------+-----------+------+------+

| 1 | 章三 | 22 | | NULL | 男 | 0 |

| 2 | 李四 | 20 | | NULL | 女 | 0 |

| 5 | 王五 | 24 | 10011 | ww@qq.com | 男 | 0 |

| 1000 | aa | 20 | 123 | NULL | 女 | NULL |

| 1001 | bb | 20 | 123456 | NULL | 女 | NULL |

| 1002 | cc | 25 | 123 | NULL | 女 | NULL |

| 1003 | dd | 20 | 456 | NULL | 女 | NULL |

| 1004 | ff | 28 | 789 | NULL | 男 | NULL |

| 1005 | 王五六 | 23 | 890 | NULL | NULL | NULL |

+------+-----------+------+--------+-----------+------+------+

9 rows in set (0.00 sec)

-- 如果按照sex这一列进行统计，结果就是8个而不是9个，因为sex这一列中有NULL值存在

mysql> select count(sex) from users;

+------------+

| count(sex) |

+------------+

| 8 |

+------------+

聚合函数除了以上简单的使用意外，通常情况下都是配合着分组进行数据的统计和计算

Group BY 分组

group by 语句根据一个或多个列对结果集进行分组

一般情况下，是用与数据的统计或计算，配合聚合函数使用

-- 统计 users 表中 男女生人数

-- 很明显按照上面的需要，可以写出两个语句进行分别统计

select count(*) from users where sex = '女';

select count(*) from users where sex = '男';

-- 可以使用分组进行统计，更方便

select sex,count(*) from users group by sex;

+------+----------+

| sex | count(*) |

+------+----------+

| 男 | 4 |

| 女 | 5 |

+------+----------+

-- 统计1班和2班的人数

select classid,count(*) from users group by classid;

+---------+----------+

| classid | count(*) |

+---------+----------+

| 1 | 5 |

| 2 | 4 |

+---------+----------+

-- 分别统计每个班级的男女生人数

select classid,sex,count(*) as num from users group by classid,sex;

+---------+------+-----+

| classid | sex | num |

+---------+------+-----+

| 1 | 男 | 2 |

| 1 | 女 | 3 |

| 2 | 男 | 2 |

| 2 | 女 | 2 |

+---------+------+-----+

# 注意，在使用。group by分组时，一般除了聚合函数，其它在select后面出现的字段列都需要出现在grouop by 后面

Having 子句

having时在分组聚合计算后，对结果再一次进行过滤，类似于where，

where过滤的是行数据，having过滤的是分组数据

-- 要统计班级人数

select classid,count(*) from users group by classid;

-- 统计班级人数，并且要人数达到5人及以上

select classid,count(*) as num from users group by classid having num >=5;

Order by 排序

我们在mysql中使用select的语句查询的数据结果是根据数据在底层文件的结构来排序的，

首先不要依赖默认的排序，另外在需要排序时要使用orderby对返回的结果进行排序

Asc 升序，默认

desc降序

-- 按照年龄对结果进行排序，从大到小

select * from users order by age desc;

-- 从小到大排序 asc 默认就是。可以不写

select * from users order by age;

-- 也可以按照多个字段进行排序

select * from users order by age,id; # 先按照age进行排序，age相同情况下，按照id进行排序

select * from users order by age,id desc;

Limit 数据分页

limit n 提取n条数据，

limit m,n 跳过m跳数据，提取n条数据

-- 查询users表中的数据，只要3条

select * from users limit 3;

-- 跳过前4条数据，再取3条数据

select * from users limit 4,3;

-- limit一般应用在数据分页上面

-- 例如每页显示10条数据，第三页的 limit应该怎么写？ 思考

第一页 limit 0,10

第二页 limit 10,10

第三页 limit 20,10

第四页 limit 30,10

-- 提取 user表中 年龄最大的三个用户数据 怎么查询？

select * from users order by age desc limit 3;

10.Mysql数据库导入导出和授权

数据导出

1.数据库数据导出

# 不要进入mysql，然后输入以下命令 导出某个库中的数据

mysqldump -u root -p tlxy > ~/Desktop/code/tlxy.sql

导出一个库中所有数据，会形成一个建表和添加语句组成的sql文件之后可以用这个sql文件到别的库，或着本机中创建或回复这些数据

2.将数据库中的表导出

# 不要进入mysql，然后输入以下命令 导出某个库中指定的表的数据

mysqldump -u root -p tlxy tts > ~/Desktop/code/tlxy-tts.sql

数据导入

把导出的sql文件数据导入到mysql数据库中

# 在新的数据库中 导入备份的数据，导入导出的sql文件

mysql -u root -p ops < ./tlxy.sql

# 把导出的表sql 导入数据库

mysql -u root -p ops < ./tlxy-tts.sql

权限管理

mysql中的root用户是数据库中权限最高的用户，千万不要用在项目中。

可以给不同的用户，或者项目，创建不同的mysql用户，并适当的授权，完成数据库的相关操作。这样就一定程度上保证了数据库的安全。

创建用户的语法格式：

grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’;

示例：

# 在mysql中 创建一个 zhangsan 用户，授权可以对tlxy这个库中的所有表 进行 添加和查询 的权限

grant select,insert on tlxy.* to zhangsan@'%' identified by '123456';

# 用户 lisi。密码 123456 可以对tlxy库中的所有表有 所有操作权限

grant all on tlxy.* to lisi@'%' identified by '123456';

# 删除用户

drop user 'lisi'@'%';