210 MySQL数据库小结

一、基础概念

1.1 数据

描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机

在计算机中描述一个事物，就需要抽取这一事物的典型特征，组成一条记录，就相当于文件里的一行内容。

1.2 什么是数据库

数据库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的

过去人们将数据存放在文件柜里，现在数据量庞大，已经不再适用

数据库是长期存放在计算机内、有组织、可共享的数据即可。

数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种 用户共享

1.3 什么是数据库管理系统（DataBase Management System 简称DBMS）

在了解了Data与DB的概念后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键

这就用到了一个系统软件---数据库管理系统

如MySQL、Oracle、SQLite、Access、MS SQL Server

mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。
oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。

1.4 数据库服务器、数据管理系统、数据库、表与记录的关系（重点理解）

记录：1 Mark 324245234 22（多个字段的信息组成一条记录，即文件中的一行内容）

表：student,scholl,class_list（即文件）

数据库：oldboy_stu（即文件夹）

数据库管理系统：如mysql（是一个软件）

数据库服务器：一台计算机（对内存要求比较高）

1.5 小结

数据库服务器：运行数据库管理软件

数据库管理软件：管理-数据库

数据库：即文件夹，用来组织文件/表

表：即文件，用来存放多行内容/多条记录

二、基础的SQL语句

2.1 简介

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：

1. DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2. DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3. DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

2.2 SQL语句

操作文件夹
增：create database db1 charset utf8;
查：show databases;
改：alter database db1 charset latin1;
删除: drop database db1;
操作文件

先切换到文件夹下：use db1

增：create table t1(id int,name char);

查：show tables

改：alter table t1 modify name char(3);

alter table t1 change name name1 char(2);

删：drop table t1;
操作文件中的内容/记录

增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');

查：select * from t1;

改：update t1 set name='sb' where id=2;

删：delete from t1 where id=1;

三、常用存储引擎

3.1 数据库存储引擎

对于初学者来说我们通常不关注存储引擎，但是 MySQL 提供了多个存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

3.2 存储引擎简介

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。 存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。

3.2.1 InnoDB

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的 MySQL 5.6.13 版，InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。

自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

外键约束。MySQL支持外键的存储引擎只有InnoDB。

支持自动增加列AUTO_INCREMENT属性。

从 5.7 开始innodb存储引擎成为默认的存储引擎。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

3.2.2 MyISAM

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。例如，我建立了一个MyISAM引擎的tb_Demo表，那么就会生成以下三个文件：

• tb_demo.frm，存储表定义。
• tb_demo.MYD，存储数据。
• tb_demo.MYI，存储索引。

MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。

插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

3.2.3 MRG_MYISAM

MRG_MyISAM存储引擎是一组MyISAM表的组合，老版本叫 MERGE 其实是一回事儿，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。

Merge存储引擎的使用场景。对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

ENGINE=MERGE，指明使用MERGE引擎，其实是跟MRG_MyISAM一回事儿，也是对的，在MySQL ##已经看不到MERGE了。

UNION=(t1, t2)，指明了MERGE表中挂接了些哪表，可以通过alter table的方式修改UNION的值，以实现增删MERGE表子表的功能。比如:alter table tb_merge engine=merge union(tb_log1) insert_method=last;INSERT_METHOD=LAST，INSERT_METHOD指明插入方式，取值可以是：0 不允许插入；FIRST 插入到UNION中的第一个表； LAST 插入到UNION中的最后一个表。

MERGE表及构成MERGE数据表结构的各成员数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型，索引也必须按照同样的顺序和同样的方式定义。

3.2.4 MEMORY

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。

一般在以下几种情况下使用Memory存储引擎：

目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。

如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。

存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。

3.2.5 CSV

CSV 存储引擎是基于 CSV 格式文件存储数据。

CSV 存储引擎因为自身文件格式的原因，所有列必须强制指定 NOT NULL 。

CSV 引擎也不支持索引，不支持分区。

CSV 存储引擎也会包含一个存储表结构的 .frm 文件，还会创建一个 .csv 存储数据的文件，还会创建一个同名的元信息文件，该文件的扩展名为 .CSM ，用来保存表的状态及表中保存的数据量。

每个数据行占用一个文本行。

因为 csv 文件本身就可以被Office等软件直接编辑，保不齐就有不按规则出牌的情况，如果出现csv 文件中的内容损坏了的情况，也可以使用 CHECK TABLE 或者 REPAIR TABLE 命令检查和修复

3.2.6 ARCHIVE

Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL ##版以前，Archive是不支持索引，但是在MySQL ##以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

3.2.7 BLACKHOLE

黑洞存储引擎，所有插入的数据并不会保存，BLACKHOLE 引擎表永远保持为空，写入的任何数据都会消失，

3.2.8 PERFORMANCE_SCHEMA

主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表，一般用于记录binlog做复制的中继。在这里有官方的一些介绍MySQL Performance Schema

3.2.9 FEDERATED

主要用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取；在MariaDB的上实现是FederatedX

3.2.10 其他

这里列举一些其它数据库提供的存储引擎，OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE。提供的名字仅供参考。

3.3 如何选择合适的存储引擎

提供几个选择标准，然后按照标准，选择对应的存储引擎即可，也可以根据常用引擎对比来选择你使用的存储引擎。使用哪种引擎需要根据需求灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎，将会提高整个数据库的性能。

• 是否需要支持事务；
• 是否需要使用热备；
• 崩溃恢复，能否接受崩溃；
• 是否需要外键支持；
• 存储的限制；
• 对索引和缓存的支持；

四、查询中关键字的执行优先级

重点中的重点：关键字的执行优先级

1. from：找到表
2. where：拿着where指定的约束条件，去文件/表中取出一条条记录
3. group by：将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4. select：执行select
5. distinct：去重
6. having：将分组的结果进行having过滤
7. order by：将结果按条件排序：order by
8. limit：限制结果的显示条数