【介绍】MySQL
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。
MEET IT
数据库
数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式存储，可以通过命令对数据进行增加、修改、删除、查询等操作。
SQL
（Structured Query Language）结构化查询语句
-
DDL（Data Definition Language）定义数据库对象
-
DML（Data Manipulation Language）操作数据表记录
-
DCL（Data Control Language）定义数据库的访问权限、安全管理
-
DQL（Data Query Language）查询数据表记录
DO IT
数据库操作
创建数据库
create database 数据库名字（character set 字符集）;
查看数据库
查看服务器中所有数据库：show databases;
查看某个数据库的定义信息：show create database 数据库名字；
删除数据库
drop database 数据库名字；
其他
切换数据库：use 数据库名字；
查看正在使用的数据库：select database();
表操作
字段类型
| 分类 | 类型名称 | 说明 |
|---|---|---|
| 整数类型 | tinyint | 1字节 (-128，127) (0，255) 小整数值 |
| smallint | 2 字节 (-32 768，32 767) (0，65 535) 大整数值 | |
| mediumint | 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值 | |
| int | 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 | |
| bigint | 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 | |
| 小数类型 | float | 单精度浮点数值 |
| double | 双精度浮点数 | |
| decimal | 压缩严格的定点数 | |
| 日期类型 | year | YYYY 1901~2155 |
| time | HH:MM:SS -838:59:59~838:59:59 | |
| date | YYYY-MM-DD 1000-01-01~9999-12-03 | |
| datetime | YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~9999-12-31 23:59:59 | |
| timestamp | YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC | |
| 文本二进制 | CHAR(M) | M 为0~255之间的整数 |
| VARCHAR(M) | M 为0~65535之间的整数 | |
| TINYBLOB | 允许长度0~255字节 | |
| BLOB | 允许长度0~65535字节 | |
| MEDIUMBLOB | 允许长度0~167772150字节 | |
| LONGBLOB | 语序长度0~4294967295字节 | |
| TINYTEXT | 允许长度0~255字节 | |
| TEXT | 允许长度0~65535字节 | |
| MEDIUMTEXT | 允许长度0~167772150字节 | |
| LONGTEXT | 允许长度0~4294967295字节 | |
| VARBINARY(M) | 允许长度0~M个字节的变长字节字符串 | |
| BINARY(M) | 允许长度0~M个字节的定长字节字符串 |
约束
primary key 主键约束
unique 唯一约束
not null 非空约束
主键约束包括唯一约束和非空约束
创建表
create table 表名 （
字段名 类型（长度） 约束，
字段名 类型（长度） 约束，
…
）；
查看表
查看数据库中的所有表：show tables
查看表结构：desc 表名
删除表
drop table 表名
修改表
alter table 表名 add 列名 类型(长度) 约束；
alter table 表名 modify 列名 类型(长度) 约束；
alter table 表名 change 旧列名 新列名 类型(长度) 约束；
alter table 表名 drop 列名；
rename table 表名 to 新表名；
alter table 表名 character set 字符集；
插入记录
insert into 表名 values (值1,值2,…);
insert into 表名(列名1,列名2,…) values (值1,值2,…);
insert into 表名 values select * from 表名；
insert into 表名(列名1,列名2,…) values select (列名1,列名2,…) from 表名；
字段值字符串&日期需要加引号
更新记录
update 表名 set 字段名1=值, 字段名2=值；
update 表名 set 字段名1=值, 字段名2=值 where 条件；
字段值字符串&日期需要加引号
删除记录
delete from 表名 [where 条件]；
truncate 是将表删除重新建表，auto_increment将重新记值。
SQL查询
select [distinct] * [列名1, 列名2] from 表名 [where 条件]；
条件
| 分类 | 说明 | |
|---|---|---|
| 比较运算符 | < > <= >= = <> | 小于、大于、小于等于、大于等于、等于、不等于 |
| between…and… | 显示在某一区间的值（含头含尾） | |
| in(set) | 显示在in列表中的值 | |
| like | 模糊查询，‘%’代表零个或多个，‘_’ 代表一个字符 | |
| is null | 判断是否为空 | |
| and | 多个条件同时成立 | |
| or | 多个条件任一成立 | |
| not(条件) | 不成立 |
排序
order by asc（升序）/desc（降序）
聚合函数
sum()：求某一列的和
avg()：求某一列的平均值
max()：求某一列的最大值
min()：求某一列的最小值
count()：求某一列的元素个数
分组
group by
having
select语句中的列（非聚合函数列），必须出现在group by 子句中
group by 子句中的列，不一定要出现在select语句中
表之间的关系
一对一
一对多
多对多（需要中间表来实现）
外键
通常是通过外键来约束表与表之间的关系
查询原理
单表查询：根据where条件过滤表中的记录，形成中间表（用户不可见）；然后根据select的选择列选择相应的列进行返回最终结果。
两表连接查询：对两表求积（笛卡尔积）并用on条件和连接类型过滤形成中间表；然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。
多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，知道所有表都连接上为止，最终形成一个中间的结果表，然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。
MySQL架构
Connectors
指的是不同语言中与SQL的交互
Connection Pool
- 管理缓冲用户连接、线程处理等需要缓存的需求。
- 负责监听对MySQL Server的各种请求、接收连接请求，转发所有连接请求到线程管理模块。
Management Services & Utilities
系统管理和控制工具
SQL Interface
接收用户的SQL命令，并返回永不需要的查询结果。
Parser
SQL命令传递到解析器的时候会被解析器验证和解析。
- 将SQL语句进行语义和语法分析，分解成数据结构，按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤。
- 如果在分解中遇到错误，那么就说明这个sql语句是不合理的。
Optimizer
sql语句在查询之前会使用查询优化器对查询进行优化。
它使用的是“选取-投影-联接”策略进行查询。
- 这个select 查询先根据 where 语句进行选取，而不是先将表全部查询出来以后再进行过滤
- 这个select查询先根据查询属性进行属性投影，而不是将属性全部取出在进行过滤
- 将这两个查询条件联接起来生产最终查询结果
Cache 、 Buffer
它的主要功能是将客户端提交给MySQL select请求的返回结果集cache到内存中，与该query的一个hash值走一个对应。该query所取数据的基表发生任何数据的变化之后，MySQL会自动使该query的cache失效。
在读写比例非常高的应用系统中，query cache对性能的提高是非常显著的。当然它对内存消耗也是非常大的。
存储引擎接口
-
多存储引擎是MySQL有别与其他数据库的一大特性
-
存储引擎是基于表的，而不是数据库。
-
MySQL 5.5 之后 默认的存储引擎由MyISAM变为InnoDB。
查看存储引擎： show engines
| InnoDB | MyISAM | |
|---|---|---|
| 存储文件 | .frm 表定义文件 .idb 数据文件 |
.frm 表定义文件 .myd 数据文件 .myi 索引文件 |
| 锁 | 表锁、行锁 | 表锁 |
| 事务 | ACID | 不支持 |
| CRUD | 读、写 | 读多 |
| count | 扫表 | 专门存储的地方 |
| 索引结构 | B+Tree | B+Tree |
MySQL物理结构
MySQL是通过文件系统对数据进行存储和管理的。
MySQL从物理结构上可以分为日志文件和数据文件。
日志文件
MySQL通过日志记录了数据库操作信息和错误信息。常用的日志文件包括错误日志、二进制日志、查询日志、慢查询日志和InnoDB引擎在线Redo日志、中继日志等。
错误日志（err log）
- 默认是开启的，而且从5.5.7以后无法关闭错误日志。
- 记录了运行过程中遇到的所有严重的错误信息，以及MySQL每次启动和关闭的详细信息。
- 默认的错误日志名称： hostname.err
- 错误日志所记录的信息是可以通过log-error和log-warnings来定义
- log_error可以直接定义为文件路径，也可以为ON/OFF; log_warnings只能使用1/0来定义开关启动
二进制日志（bin log）
- 默认是关闭的，需要通过配置：log-bin=mysql-bin进行开启。其中mysql-bin是binlog日志文件的basename，binlog日志文件的名称：mysql-bin-000001.log
- binlog记录了数据库所有的DDL语句和DML语句，但不包括select语句内容，语句以时间的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息，binlog主要作用是用于恢复数据，因此binlog对于灾难恢复和备份恢复来说至关重要。
- 如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中。
- binlog还用于实现mysql主从复制。
- binlog还用于数据恢复。
通用查询日志
- 默认情况下通用查询日志是关闭的。
- 由于通用查询日志会记录用户的所有操作，其中还包含增删改查等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响MySQL的性能。如若不是为了调试数据库的目的建议不要开启查询日志。