MySQL总结

什么是MySQL?

SQL语句在线练习 （非常不错）

主要内容如下：

• 什么是MySQL?
• MySQL基础
• MySQL语句总结
• MySQL架构

• 附录：SQL注入安全

什么是MySQL?

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• MySQL 是开源的，目前隶属于 Oracle 旗下产品。
• MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL 使用标准的 SQL 数据语言形式。
• MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
• MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。
• MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
• MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

MySQL基础

SQL/DB/DBMS 分别是什么？

DBMS 负责执行 sql 语句， 通过执行 sql 语句来操作 db 中的数据

DBMS -（执行）-> SQL 语句 -（操作）-> DB

DB：DataBase (数据库， 实际上在硬盘上以文件的形似存在)

DBMS：Database Management System（数据库管理系统，常见的有：MySQL, Oracle DB2 Sybase, SqlServer…）

SQL:结构化查询语言， 是一门标准通用语言， 标准的sql语句适用于所有的数据库产偶

什么是表？

表是数据库的基本组成单元， 所有的数据都以表格的格式组织 一个表包括行和列： 行： 被称为数据/记录 列：被称为字段

• 每个字段名包含哪些信息： 字段名， 数据类型， 相关约束

MySQL语句总结

SQL 教程（菜鸟教程）

MySQL 教程（菜鸟教程）

插入

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

查询

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

更新

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

删除

DELETE FROM table_name [WHERE Clause]

LIKE

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE '%xxx%'

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

使用形式如下：

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；
SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；

排序 order

SELECT * FROM table_name1 
ORDER BY 排序字段 [ASC [DESC][默认 ASC]]

分组 group

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数

select 字段名，分组名
from 表名
where 筛选条件  // 分组前筛选
group by 分组名
having 分组后筛选条件  //分组后筛选

联合多表查询 JOIN

select 字段名
from 表名 as 别名
【连接类型】join 表名 as 别名
on 连接条件
where 筛选条件
order by 排序字段

其中【连接类型包括】大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录；左表是主表
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反；右边是主表

过滤重复数据

SELECT DISTINCT last_name, first_name
FROM table;

NULL 值处理

为了处理这种情况，MySQL提供了三大运算符:

• IS NULL: 当列的值是 NULL,此运算符返回 true。
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
• <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

视图 view

多个地方用到同样的查询且SQL相对复杂。不保存结果，只保存SQL。

优点：重用SQL，保护和封装数据。

可以进行插入或者修改，不建议这样，视图最好只有‘只读’权限。

create view 视图名
as
查询语句；

MySQL 函数

MySQL 字符串函数

函数	描述
CHAR_LENGTH(s)	返回字符串 s 的字符数
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串
FIND_IN_SET(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置
LOCATE(s1,s)	从字符串 s 中获取 s1 的开始位置
LOWER(s)	将字符串 s 的所有字母变成小写字母
STRCMP(s1,s2)	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ， 如果 s1>s2 返回 1，如果 s1<s2 返回 -1

MySQL 数字函数

函数名	描述
ABS(x)	返回 x 的绝对值
AVG(expression)	返回一个表达式的平均值
CEIL(x)	返回大于或等于 x 的最小整数
COUNT(expression)	返回查询的记录总数
FLOOR(x)	返回小于或等于 x 的最大整数
MAX(expression)	返回字段 expression 中的最大值
MIN(expression)	返回字段 expression 中的最小值
MOD(x,y)	返回 x 除以 y 以后的余数

MySQL 日期函数

函数名	描述
CURDATE()	返回当前日期
CURRENT_TIME	返回当前时间
CURRENT_TIMESTAMP()	返回当前日期和时间
DATE()	从日期或日期时间表达式中提取日期值
DATE_FORMAT(d,f)	按表达式 f的要求显示日期 d
DAY(d)	返回日期值 d 的日期部分

MySQL架构

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

从上图中，我们可以看出，MySQL体系中包含以下几个重要的部分：

• 连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
• 服务层：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等
• 引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取
• 存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

存储引擎

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

MySQL服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎 。常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键，它具有以下特性：

• ☆通过多版本并发（MVCC）实现高并发性能
• ☆支持SQL标准的4种隔离级别，默认为Repatable级别
• ☆通过间隙锁（Next-Key locking）来避免幻读
• 提供插入缓冲（insert-buffer）
• 支持二次写（double write）
• 自适应哈希索引（adaptive hash index）
• 预读（read ahead）
• …

MySQL 索引

MYSQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构。

索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。

可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例。

左边的数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值，和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到对应的数据，从而快速检索出符合条件的记录。

索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上

在InnoDB中，主要包含3种索引：

• B+树索引
• 全文索引
• 哈希索引

在InnoDB中，索引通过B+树来实现，B+树作为索引的优点：

• 非叶子节点不存储数据，使得每页能存储更多索引，减少IO的次数
• 叶子节点存在相互索引的指针，便于范围查找
• 性能稳定，每次查询都需要通过相同的次数才能获取到需要的数据

优势

• 提高数据检索效率，降低数据库IO成本
• 降低数据排序的成本，降低CPU的消耗

劣势

• 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

查询缓存的使用

执行查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实用

MySQL 事务

什么是事务?

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

事务的四大特性(ACID)

1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务后，数据库从一个正确的状态变化到另一个正确的状态；
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

隔离性

InnoDB的隔离性则通过锁和MVCC来实现，由于完整的隔离性，只能通过互斥锁来实现，而互斥锁带来的问题便是性能急剧下降，因此处于对性能的妥协和结合日常业务的使用，根据加锁的程度不同，又将隔离性分为以下四个级别：

• 读未提交(READ UNCOMMITTED)： 会出现脏读的问题
• 读已提交(READ COMMITTED)： 不会出现脏读，但是会出现不可能重复读的问题
• 可重复读(REPEATABLE READ)： 不会出现不可重复读，但是会出现幻读的问题
• 串行化(SERIALIZABLE)： 不会出现幻读问题

并发事务带来哪些问题?

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

并发事务处理问题的解决办法

• “更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
• “脏读” 、 “不可重复读”和“幻读” ，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决：
• 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 另一种是数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照 （Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

1. 限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

2. 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读；

3. 垂直分区

根据数据库里面数据表的相关性进行拆分。

什么是池化?什么是数据库连接池?为什么需要数据库连接池?

池化设计应该不是一个新名词。我们常见的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。就好比你去食堂打饭，打饭的大妈会先把饭盛好几份放那里，你来了就直接拿着饭盒加菜即可，不用再临时又盛饭又打菜，效率就高了。除了初始化资源，池化设计还包括如下这些特征：池子的初始值、池子的活跃值、池子的最大值等，这些特征可以直接映射到java线程池和数据库连接池的成员属性中。这篇文章对池化设计思想介绍的还不错，直接复制过来，避免重复造轮子了。

数据库连接本质就是一个 socket 的连接。数据库服务端还要维护一些缓存和用户权限信息之类的 所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。 连接池还减少了用户必须等待建立与数据库的连接的时间。

一条SQL语句在MySQL中如何执行的

客户端请求 —-> 连接器（验证用户身份，给予权限） —-> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） —-> 分析器（对SQL进行词法分析和语法分析操作） —-> 优化器（主要对执行的sql优化选择最优的执行方案方法） —-> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） —-> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）图：极客时间

分析器执行顺序

• 首先，应该明确数据来源，因此需要解析FROM，找到对应的数据表
• 找到数据表后，下一步便是通过过滤条件，获取具体的数据行，因此需要解析WHERE
• 拿到具体的数据行之后，接下来需要处理数据行，因此需要解析GROUP BY
• 通过GROUP BY分组之后，就可以将同一个组的数据进行聚合，因此解析count(),sum(),arg()等
• 通过聚合函数之后，则可以将聚合之后的数据进行过滤，因此解析HAVING
• 处理完数据行之后，接下来查看用户需要的数据列，因此解析SELECT
• 剩下的数据便是用户真真需要的数据，但是SQL可能会对数据进行排序等，因此处理 DISTINCT以及ORDER
• 最后，排序完成后，查看用户是否需要返回指定的行，因此处理LIMIT

总结出来执行顺序便是：

FROM`->`WHERE`->`GROUP BY`->`count()`->`HAVING`->`SELECT`->`DISTINCT`->`ORDER`->`LIMIT

一条SQL语句执行得很慢的原因有哪些？

腾讯面试：一条SQL语句执行得很慢的原因有哪些？—-不看后悔系列

CHAR 和 VARCHAR 的区别？

char是固定长度，varchar长度可变：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间

Mysql 的内连接、左连接、右连接有什么区别？

附录：SQL注入安全

什么是sql注入呢?

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

如何避免 sql 注入风险

 1.永远不要信任用户的输入。对用户的输入进行校验，能够通过正则表达式，或限制长度；对单引号和
双"-"进行转换等。检查输入的数据是否具有所期望的数据格式，严格限制变量的类型，例如使用regexp包进行一些匹配处理，
或者使用strconv包对字符串转化成其他基本类型的数据进行判断。

2.永远不要使用动态拼装sql，能够使用參数化的sql或者直接使用存储过程进行数据查询存取。

3.永远不要使用管理员权限的数据库连接，为每一个应用使用单独的权限有限的数据库连接。

4.不要把机密信息直接存放。加密或者hash掉password和敏感的信息。对进入数据库的特殊字符（'"\尖括号&*;等）进行转义处理，
或编码转换

5.应用的异常信息应该给出尽可能少的提示，最好使用自己定义的错误信息对原始错误信息进行包装,避免网站打印出SQL错误信息，
比如类型错误、字段不匹配等，把代码里的SQL语句暴露出来，以防止攻击者利用这些错误信息进行SQL注入。

6.sql注入的检測方法一般採取辅助软件或站点平台来检測。软件一般採用sql注入检測工具jsky，站点平台就有亿思站点安全平台检測工具。
MDCSOFT SCAN等。採用MDCSOFT-IPS能够有效的防御SQL注入。XSS攻击等。

7.严格限制Web应用的数据库的操作权限，给此用户提供仅仅能够满足其工作的最低权限，从而最大限度的减少注入攻击对数据库的危害。

8.在应用发布之前建议使用专业的SQL注入检测工具进行检测，以及时修补被发现的SQL注入漏洞。网上有很多这方面的开源工具，
例如sqlmap、SQLninja等。

9.所有的查询语句建议使用数据库提供的参数化查询接口，参数化的语句使用参数而不是将用户输入变量嵌入到SQL语句中，
即不要直接拼接SQL语句。例如使用database/sql里面的查询函数Prepare和Query，或者Exec(query string, args ...interface{})。

pymysql 简单规避注入风险示列

#错误示范  不要自己去拼接账户和密码
import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='318', database='ftp')
cur = conn.cursor()
username = input('user >>>')
password = input('passwd >>>')
sql = "select * from userinfo where name = %s and password =  %s ;"% (username, password)
cur.execute(sql)
print(cur.fetchone())
cur.close()
conn.close()

user >>>'我不知道账号' or 1=1;--
passwd >>>我也不知道密码
(1, '凯歌318', '666')
Process finished with exit code 0


#正确方法   cur.execute(sql, (username, password)) 把密码和账户交给 execute去拼接
import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='318', database='ftp')
cur = conn.cursor()
username = input('user >>>')
password = input('pwd >>>')
sql = "select * from userinfo where name = %s and pwd = %s"
cur.execute(sql, (username, password))
print(cur.fetchone())
cur.close()
conn.close()

user >>>'我不知道账号' or 1=1;--
pwd >>>也不知道密码
None

Process finished with exit code 0

user >>>凯歌318
pwd >>>666
(1, '凯歌318', '666')
Process finished with exit code 0

特殊情况 - IN - 不定长参数

有一个值列表，我想在一个IN子句中执行的 SQL 语句。 例：

files = ['file1', 'file2', ...]  # this list can have a variable number of elements
con = pymysql.connect(...)
cur = conn.cursor()

result = cur.execute('SELECT * FROM sometable WHERE file_name IN (?)', files)

但是当我执行上面的语句时，我得到一个错误，例如：

ProgrammingError：（’SQL包含1个参数标记，但提供了18个参数’，’HY000’）

我可以使用以下内容生成变量参数字符串：

params = ",".join(["%s"] * len(files))
query = 'SELECT * FROM sometable WHERE file_name IN ({})'.format(params)
con.ping(reconnect=True)

result = cur.execute(query, files)

这样就可以避免SQL注入的风险。

参考

如何使用带有IN子句的SQL参数为pyodbc提供可变数量的值？)

sql 注入风险

MySQL 三万字精华总结 + 面试100 问，吊打面试官绰绰有余（收藏系列）