阅读MySQL必知必会，查缺补漏

MySQL自带数据库

information_schema：是MySQL自带的数据库，主要保持MySQL数据库服务器的系统信息，比如数据库的名称，数据库表的名称，字段名称，存储权限等。

performance_schema：是MySQL系统自带的数据库，可以用来监控MySQL的各类性能指标。

sys：是MySQL系统自带的数据库，主要作用是，以一种更容易被理解的方式展示MySQL数据库服务器的各类性能指标。

MySQL主键

MySQL中的主键主要有3个特征：

• 必须唯一，不能重复
• 不能是空
• 必须可以唯一标识数据表中的记录

alter table demo.test
add column itenumber int primary key auto_increment;

MySQL数据类型

整数类型：

在评估用哪种整数类型的时候，我们需要考虑存储空间和可靠性平衡的问题。使用占用字节数少的整数类型可以节省存储空间，但是如果超出取值范围那么可能会引起系统错误，影响可靠性。

浮点数类型和定点数类型：

FLOAT（单精度浮点数），DOUBLE（双精度浮点数），REAL（默认是DOUBLE）

但是你可以通过这个语句把默认改变。

SET sql_mode = "REAL_AS_FLOAT";

浮点数类型有一个缺陷，就是不精确

那么为什么会出现这样的情况呢？

MySQL对浮点类型数据的存储方式

4个字节存储FLOAT类型，8个字节存储DOUBLE类型数据。但是它们都是用二进制方式来进行存储的。例如9.625用二进制表示，就是1001.101，你可以发现，如果尾数不是0或者5的话那么你无法用一个二进制数来精确的表达，就只能四舍五入了。

于是MySQL提供了定点数类型：DECIMAL

浮点数的存储方式决定了它是不可能精确的，而DECIMAL的存储方式决定了它一定是精确的。

DECIMAL把十进制转换成二进制数存储，DECIMAL则不同，它是把十进制数的整数部分和小数部分拆开，分别转换成十六进制数来进行存储。

DECIMAL(M，D)其中M表示整数加小数一共多少位，M <= 65。D表示小数部分位数,D < M。

文本类型：

CHAR(M)：固定长度字符串，必须预先定义字符串长度。

VARCHAR(M)：可变长度字符串，必须预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照字符串长度存储的。

TEXT：系统自动按照实际长度存储，不需要预先定义，因此TEXT不能做主键。

MySQL的基本操作

有哪些约束？

• 非空约束
• 唯一性约束
• 自增约束

如何修改表？

把原来的表复制一下：

CREATE TABLE demo.importheadhist LIKE demo.importhead;

插入查询结果：

INSERT INTO 表名(字段名)
SELECT 字段名或值
FROM 表名
WHERE 条件

修改数据：

UPDATE 表名
SET 字段名=值
WHERE 条件

查询数据：

• GROUP BY ：告诉MySQL查询结果要如何分组，经常与MySQL的聚合函数一起使用。
• HAVING：用于筛选查询结果，跟WHERE类似。

FROM：

FROM关键字表示查询的数据源。

数据源不一定是表，也可以是一个查询结果。

SELECT a.goodsname, a.price 
FROM (SELECT *FROM demo.goodsmaster
) AS a;

这个就是派生表：

    SELECT *FROM demo.goodsmaster

或者叫做子查询。

意思是我们把一个查询结果数据集当作一共虚拟的数据表来看待。MySQL规定，必须要用AS关键字给这个派生表起一个别名。

ORDER BY：

ASC表示升序，DESC表示降序。

如何设置主键？

• 尽量不要用业务字段，也就是跟业务有关的字段作为主键。
• 使用自增字段做主键（不推荐），多台服务器就寄了。
• 采用手动赋值的方法，通过一定的逻辑性，确保字段值全系统的唯一性。

外键和连接

外键和连接：如何做关联查询？

这种把分散在多个不同的表里面的数据查询出来的操作就叫做多表查询。

在MySQL中为了把2个表关联起来，会用到2个重要功能：外键（FOREIGN KEY）和连接（JOIN）。

外键需要在创建表的阶段就定义出来；连接可以通过相同意义的字段把两个表连接起来用在查询阶段。

如何创建外键？

假设我们有两张表，A和B，它们通过一个公共字段id发生关联关系，我们把这个关联关系叫做R。如果id在表A中是主键，那么表A就是这个关系R中的主表。相应的，表B就是这个关系中的从表，表B中的id，就是表B用来引用表A中数据的，叫外键。所以外键就是从表中用来引用主表数据的那个公共字段。

如何创建外键：

CREATE TABLE 从表名
(-- 定义外键约束，提出外键字段和参照的主表字段CONSTRAINT 外键约束名FOREIGN KEY (字段名) REFERENCES 主表名 (字段名)
)

也可以修改表的字段：

ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY 字段名 REFERENCES 主表名

连接：

• 内连接 – 表示查询结果只返回符合连接条件的记录，这种连接方式比较常用。
• 外连接 – 表示查询结果返回某一个表中的所有记录，以及另一个表中满足连接条件的记录。

内连接：

关键字JOIN，INNER JOIN，CROSS JOIN的含义是一样的。

我们定义一个背景：

SELECT a.transactionno,a.itemnumber,a.quantity,a.price,a.transdate,b.membername
FROMdemo.trans AS aJOINdemo.membermaster AS b ON (a.cardno = b.cardno);

可以看到外面通过公共字段cardno把两个表关联到了一起，查询出了会员消费的数据。

关键字JOIN与关键字ON配对使用，意思是查询满足关联条件demo.trans表中cardno的值与demo.membermaster表中的cardno值相等两个表中的所有记录。

外连接：

• 左连接，一般写成LEFT JOIN，返回左边所有记录以及右表中符合连接条件的记录。
• 右连接，一般写成RIGHT JOIN，返回右边表中的所有记录，以及左表中符合连接条件的记录。

SELECT a.transactionno,a.itemnumber,a.quantity,a.price,a.transdate,b.membername
FROMdemo.trans AS a -- 这个是左边的表LEFT JOINdemo.membermaster AS b ON (a.cardno = b.cardno);

我们使用右连接也可以完成一样的效果：

SELECT a.transactionno,a.itemnumber,a.quantity,a.price,a.transdate,b.membername
FROMdemo.membermaster AS b -- 这个是左边的表RIGHT JOINdemo.trans AS a ON (a.cardno = b.cardno);

关联查询的误区：

MySQL允许关联查询的时候没有外键的约束。因为外键的约束是要占用系统资源的，如果是高并发的网站的话，这是无法承担的。但是我们既然要关联查询，就说明有两张表，也就是我们的插入或者删除操作是无法用一条SQL语句完成的，也就是没有原子性。所以如果没有外键约束的话，需要在应用层解决原子性的问题，否则会出现业务上的错误。

where和having有什么区别？

WHERE是直接对表中的字段进行限定。HAVING则需要根据分组关键字GROUP BY一起使用，通过对分组字段或分组计算函数进行限定。

• WHERE关键字的特点是，直接用表的字段对数据集进行筛选。如果需要通过关联查询从其他的表获取需要的信息，那么执行的时候，也是先通过WHERE条件进行筛选，用筛选后的比较小的数据集进行连接。这样一来，连接过程中占用的资源比较少，执行效率也比较高。
• HAVING的查询过程：
  • 我们需要把所有的信息都准备好，包括从关联表中获取需要的信息，对数据集进行分组，形成一个包含所有需要的信息的数据集合。接着，再通过HAVING条件的筛选，得到需要的数据。

也就是说：

• 如果需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。这一点就已经决定了WHERE比HAVING更高效。因为WHERE可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING则需要先把结果集准备好，也就是用没有被筛选的数据集进行关联，然后对这个大的数据集进行筛选，占用资源多，效率低。
• WHERE可以直接把表中的字段作为筛选条件，但是不能使用分组中的计算函数作为筛选条件。HAVING必须要和GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。
• 因此这也决定了HAVING可以完成WHERE不可能完成的任务。因为在查询语法结构中，WHERE在GROUP BY之前，所以无法对分组结果进行筛选。HAVING在GROUP BY之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选。

解释一下EXPLAIN之后的字段：

• type=range：使用索引查询特定范围的数据记录
• rows=5411：表示需要读取的记录数
• possible_keys=index_trans：表示可以选择的索引是index_trans
• key=index_trans：表示实际选择的索引是index_trans
• extra=Using index condition；Using where；Using MRR；这里面的信息对SQL语句的执行细节做了进一步解释，包含了3层含义：
  • 执行时使用了索引
  • 执行时通过WHERE条件进行了筛选
  • 使用了顺序磁盘读取的策略

临时表：

临时表是一种特殊的表，用来存储查询的中间结果，并且会随着当前连接的结果而自动删除。MySQL中两种连接表，分别是内部临时表和外部临时表。

• 内部临时表主要用于性能优化，由系统自动产生，我们无法看到
• 外部临时表通过SQL语句创建，我们可以使用

CREATE TEMPORARY TABLE 表名
(字段名 字段类型,...
);

• 临时表创建的时候需要加上格外的TEMPORARY字段
• 临时表创建完成之后，只有当前连接可见，其他连接是不可见的，具有连接隔离性。
• 临时表在当前连接结束之后，会被自动删除。

内存临时表和磁盘临时表：

内存临时表：

CREATE TEMPORARY TABLE demo.mytrans
(itemnumber int,groupnumber int,branchnumber int
) ENGINE = MEMORY; (临时表存在内存中)

磁盘临时表：

CREATE TEMPORARY TABLE demo.mytrans
(itemnumber int,groupnumber int,branchnumber int
);

内存临时表如果断电的话数据就全部消失了。

高并发系统不建议使用！！！

视图

视图：如何简化查询？

创建视图：

CREATE [OR REPLACE]
VIEW 视图名称 [(字段列表)]
AS 查询语句

• 子查询：嵌套在另一个查询中的查询。
• 派生表：如果我们在查询中把子查询的结果作为一共表来使用，这个表就是派生表

查询的具体步骤是：

• 通过子查询获得单品销售统计的查询结果
• 把第一步中的查询结果作为一个派生表，跟历史库存表进行连接，查询获得包括销售日期，商品名称，销售数量和历史库存数量在内的最终结果。

使用视图的查询语句明显简单多了，可读性更好，也更容易维护。

如果操作视图和视图中的数据？

修改视图：

ALTER VIEW 视图名
AS 查询语句;

查看视图：

DESCRIBE 视图名

删除视图要使用DROP关键字：

DROP VIEW 视图名

视图的优缺点：

优点：

• 因为我们可以把视图当作一张表来进行查询，所以在使用视图的时候，我们不用考虑视图本身是如何获取数据的，里面有什么逻辑，包括了多少张表，有哪些关联操作，而是可以直接使用。这样一来，实际上就把查询模块化了，查询变的简单，提高了开发和维护的效率。所以你可以把那些经常会用到的查询和复杂的子查询定义成视图，存储到数据库中，这样可以为你以后的使用提供方便。

• 视图跟实际数据不一样，它是存储查询语句。所以在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源。

• 视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。也就是说用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这样不仅提高了数据表的安全性，同时也通过视图把用户实际需要的信息汇总在了一起，查询起来很轻松。

• 视图的数据结构相对独立，即便实际数据表的结构发生变换，我们也可以通过修改定义视图的查询语句，让查询结果集里面的字段保持不变。

我们举一个例子让大家理解：

假设我们有一个实际的数据表（demo.goodsmaster），包括商品编号、条码、名称、规格和售价等信息：

缺点：

• 数据表的更变，需要及时对视图进行维护，特别当视图是由视图生成的时候，维护会变的非常复杂。

MySQL日志

系统出现问题，应该如何及时发现？

我们应该马上看MySQL的日志

通用查询日志：

通用查询日志记录了所有用户的连接开始时间以及截止时间，以及发给MySQL数据库服务器的所有SQL指令。

mysql> SHOW VARIABLES LIKE '%general%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| general_log      | OFF -- 通用查询日志处于关闭状态                 
| general_log_file | LAPTOP-7IL73D6I.log -- 通用查询日志的名称
+------------------+---------------------+
2 rows in set, 1 warning (0.00 sec)

默认是关闭，因为一旦开启通用查询日志，MySQL会记录所有的连接起止和相关的SQL操作，这样会消耗系统资源并且占用磁盘空间。

开启通用查询日志

set GLOBAL general_log = 'ON';set @@global.general_log_file = 'D:\Lxy';

慢查询日志：

慢查询日志用来记录执行时间超过指定时长的查询。它的作用是帮助我们发现那些执行时间特别长的SQL查询，并且有针对性的进行优化，从而提高系统的整体效率。当我们的数据库发生阻塞，运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。

slow_query_log=1 -- 表示开启慢查询日志，系统将会对慢查询进行记录
slow_query_log_file="GJTECH-PC-slow.log"; -- 表示慢查询日志的名称
long_query_time=10 -- 表示慢查询的标准是查询执行时间超过10秒min_exmined_row_limit=0 -- 表示扫描过的记录大于等于这个变量的值就算慢查询

错误日志：

错误日志中记录了MySQL服务器启动，停止运行的时间，以及系统自动启动，运行和停止过程中的诊断信息，包含错误，警告和提示等。当我们的数据库服务器发生系统故障的时候，错误日志是首选。

log-error="GJTECH-PC.err"

二进制日志：

二进制日志主要数据库的更新事件。比如创建数据库表，更新表中的数据，数据更新所花费的时长等信息。通过这些信息，我们可以再先数据更新的全过程。是数据恢复和数据复制的利器。

如何操作二进制日志？

查看二进制日志主要有3种情况，分别是查看当前正在写入的二进制日志，查看所有的二进制日志和查看二进制日志种的所有数据更新事件。

查看正在写入的二进制日志的SQL语句是：

SHOW MASTER STATUS;

查看所有的二进制文件：

SHOW BINARY LOGS

查看二进制日志中所有数据更新事件的SQL语句：

SHOW BINLOG EVENTS IN 二进制文件名

刷新二进制日志：

FLUSH BINARY LOGS

这条语句的意思是：关闭服务器正在写入的二进制日志文件，并重新打开一个新文件，文件的后缀名在现有的基础上加1.

中继日志：

中继日志只是主从服务器架构存在。

回滚日志：

进行事物回滚。

这些日志太过重要，我们到时候会单独进行讲解。

范式

范式：如何消除冗余和高效存储？

• 第一范式：所有字段都是基本数据字段，不可以进一步拆分
• 第二范式：在满足第一范式的基础上，还要满足数据表里面的每一条记录，都是可唯一标识的。而且所有字段都必须完全依赖主键，不能只依赖主键的一部分。
• 在满足第二范式的基础上，数据表中不能存在可以被其他非主键字段派生出来的字段，或者说，不能存在依赖于非主键字段的字段。

设计表原则

表太大了，如何设计才能提高性能？

1. 数据类型优化

   • 优化字段的数据类型。

     • 对整数类型数据进行优化

     • 既可以使用文本类型也可以使用整数类型的字段，要使用整数类型而不是文本类型。

2. 合理增加冗余字段以提高效率

3. 拆分表

4. 尽量使用非空约束。

   1. 可以节省一个比特位，并且省去判断是否为空的逻辑。
   2. 开发时会如果提交给数据库的数据不符合数据库的条件，则会报错。保证开发时不会忽略掉该做的一些非空判断等
   3. 客户保证逻辑正确
   4. 安全，防止sql注入