复习宝典之Mysql数据库

查看更多宝典，请点击

第一章：mysql数据库

1）mysql与mariadb

mariadb数据库管理系统是mysql的一个分支，主要由开源社区在维护，采用gpl授权许可 mariadb的目的是完全兼容mysql，包括api和命令行，使之能轻松成为mysql的代替品。在存储引擎方面，使用xtradb(英语:xtradb)来代替mysql的innodb。 mariadb由mysql的创始人michael widenius(英语:michael widenius)主导开发，他早前曾以10亿美元的价格，将自己创建的公司mysql ab卖给了sun，此后，随着sun被甲骨文收购，mysql的所有权也落入oracle的手中。mariadb名称来自michael widenius的女儿maria的名字。

mariadb基于事务的maria存储引擎，替换了mysql的myisam存储引擎，它使用了percona的 xtradb，innodb的变体，分支的开发者希望提供访问即将到来的mysql 5.4 innodb性能。这个版本还包括了 primebase xt (pbxt) 和 federatedx存储引擎。

 

2）如何获取mysql版本

2.1 没有连接到mysql服务器，就想查看mysql的版本。打开cmd，切换至mysql的bin目录，运行下面的命令即可：

mysql -v 或 mysqladmin --version 或 mysql --help|find "distrib"

2.2 如果已经连接到了mysql服务器，则运行下面的命令：

select version(); 或 status 或 \s

2.3 在命令行连接上mysql服务器时，其实就已经显示了mysql的版本，如：

mysql -uroot -padmins

 

3）mysql基础知识

3.1 mysql密码

mysql初始密码为空，默认端口3306，默认最大连接数为100；

修改密码方式：

在dos下进入目录mysql\bin，然后键入以下命令：

mysqladmin -u用户名 -p旧密码 password 新密码

如： mysqladmin -u root -p ab12 password djg345

3.2 命令行修改root密码：

mysql> update mysql.user set password=password(’新密码’) where user=’root’;

mysql> flush privileges;

显示当前的user：

mysql> select user();

3.4 命令行下数据库的操作

使用 show databases; 展示所有数据库；

使用 use+数据库名称 进入或改变当前使用的数据库；

使用 show+数据库名称 展示该数据库下的所有表；

3.5 查看表结构的方法：

登录mysql，执行：

desc+表名 或 describe+表名 或 show columns from 表名 或 explain+表名;

使用mysql的工具mysqlshow.exe：

mysql+数据库名称+表名

3.6 如何导出与导入建表语句与数据

导出：mysqldump -uroot -padmins 数据库名 表名 > database_dump.sql

导入：mysql -uroot -padmins 数据库名 < database_dump.sql

3.7 如何备份所有数据库

导出数据库：mysqldump -uroot -padmins 数据库名 > database.sql

导入数据库：mysql -uroot -padmins 数据库名 < database.sql

另外可以使用图形化界面进行导出导入

3.8 mysql中有一个with rollup是用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息；

3.9 mysql 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，mysql会自动删除表并释放所有空间。

通过mysql> create temporary table 表名 创建临时表，

 

4）mysql数据库引擎

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

 

mysql支持三个引擎：isam、myisam和heap

另外两种类型innodb和berkley（bdb），也常常可以使用。如果技术高超，还可以使用mysql+api自己做一个引擎。

isam：isam是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数。因此，isam执行读取操作的速度很快，而且不占用大量的内存和存储资源。isam的两个主要不足之处在于，它不 支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把isam用在关键任务应用程序里，那就必须经常备份你所有的实 时数据，通过其复制特性，mysql能够支持这样的备份应用程序。
myisam：myisam是mysql的isam扩展格式和缺省的数据库引擎。除了提供isam里所没有的索引和字段管理的大量功能，myisam还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行optimize table命令，来恢复被更新机制所浪费的空间。myisam还有一些有用的扩展，例如用来修复数据库文件的myisamchk工具和用来恢复浪费空间的 myisampack工具。myisam强调了快速读取操作，这可能就是为什么mysql受到了web开发如此青睐的主要原因：在web开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和internet平台提供商只允许使用myisam格式。myisam格式的一个重要缺陷就是不能在表损坏后恢复数据。
heap：heap允许只驻留在内存里的临时表格。驻留在内存里让heap要比isam和myisam都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，heap也不会浪费大量的空间。heap表格在你需要使用select表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。
innodb：innodb数据库引擎都是造就mysql灵活性的技术的直接产品，这项技术就是mysql+api。在使用mysql的时候，你所面对的每一个挑战几乎都源于isam和myisam数据库引擎不支持事务处理（transaction process）也不支持外来键。尽管要比isam和 myisam引擎慢很多，但是innodb包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者 或者两者，那你就要*使用后两个引擎中的一个了。
如果感觉自己的确技术高超，你还能够使用mysql+api来创建自己的数据库引擎。这个api为你提供了操作字段、记录、表格、数据库、连接、安全帐号的功能，以及建立诸如mysql这样dbms所需要的所有其他无数功能。深入讲解api已经超出了本文的范围，但是你需要了解mysql+api的存在及其可交换引擎背后的技术，这一点是很重要的。估计这个插件式数据库引擎的模型甚至能够被用来为mysql创建本地的xml提供器（xml provider）。（任何读到本文的mysql+api开发人员可以把这一点当作是个要求。）

innodb和myisam是许多人在使用mysql时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：myisam类型不支持事务处理等高级处理，而innodb类型支持。myisam类型的表强调的是性能，其执行数度比innodb类型更快，但是不提供事务支持，而innodb提供事务支持已经外部键等高级数据库功能。

一般来说，myisam适合：

(1)做很多count 的计算； (2)插入不频繁，查询非常频繁； (3)没有事务。

innodb适合：

(1)可靠性要求比较高，或者要求事务； (2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建

 

如何查看mysql的当前存储引擎？

一般情况下，mysql会默认提供多种存储引擎,你可以通过下面的查看:

看你的mysql现在已提供什么存储引擎:

mysql> show engines;

看你的mysql当前默认的存储引擎:

mysql> show variables like '%storage_engine%';

你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):

mysql> show create table 表名;

 

5）mysql基本工作流程

mysql是由sql接口，解析器，优化器，缓存，存储引擎组成的。

数据库通常不会被直接使用，而是由其他编程语言通过sql语句调用mysql，由mysql处理并返回执行结果。那么mysql接受到sql语句后，又是如何处理的呢？

首先程序的请求会通过mysql的connectors与其进行交互，请求到处后，会暂时存放在连接池（connection pool)中并由处理器（management serveices & utilities）管理。当该请求从等待队列进入到处理队列，管理器会将该请求丢给sql接口（sql interface）。sql接口接收到请求后，它会将请求进行hash处理并与缓存中的结果进行对比，如果完全匹配则通过缓存直接返回处理结果；否则，需要完整的走一趟流程：

(1)由sql接口丢给后面的解释器（parser），上面已经说到，解释器会判断sql语句正确与否，若正确则将其转化为数据结构。

(2)解释器处理完，便来到后面的优化器（optimizer），它会产生多种执行计划,最终数据库会选择最优化的方案去执行,尽快返会结果。

(3)确定最优执行计划后，sql语句此时便可以交由存储引擎（engine）处理，存储引擎将会到后端的存储设备中取得相应的数据，并原路返回给程序。

 

6）ddl,dml,dql,tcl

ddl：数据定义语言

包含：

1、``create` `: 在数据库中创建新的数据对象
2、``alter` `: 修改数据库中对象的数据结构
3、``drop` `: 删除数据库中的对象
4、disable/enable ``trigger` `: 修改触发器的状态
5、``update` `statistic : 更新表/视图统计信息
6、``truncate` `table` `: 清空表中数据
7、comment : 给数据对象添加注释
8、rename : 更改数据对象名称

dml：数据操作语言

包含：

1、``insert` `：将数据插入到表或视图
2、``delete` `：从表或视图删除数据
3、``select` `：从表或视图中获取数据
4、``update` `：更新表或视图中的数据
5、merge ： 对数据进行合并操作(插入/更新/删除)

dcl：数据控制语言

包含：

1、``grant` `: 赋予用户某种控制权限
2、``revoke` `：取消用户某种控制权限

tcl：事务控制语言

包含：

1、``commit` `: 保存已完成事务动作结果
2、savepoint : 保存事务相关数据和状态用以可能的回滚操作
3、``rollback` `: 恢复事务相关数据至上一次``commit``操作之后
4、``set` `transaction` `: 设置事务选项

 

7）事务的基本概念

事务是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。

事务的acid/四大特征：

原子性：表示事务内操作不可分割。要么都成功、要么都是失败；

一致性：要么都成功、要么都是失败.后面的失败了要对前面的操作进行回滚；

隔离性：一个事务开始后，不能后其他事务干扰；

持久性/持续性：表示事务开始了，就不能终止。

 

8）事务的隔离级别

脏读：是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户a向用户b转账100元，对应sql命令如下

    update account set money=money+100 where name=’b’;  (此时a通知b)
​
    update account set money=money - 100 where name=’a’;

　　当只执行第一条sql时，a通知b查看账户，b发现确实钱已到账（此时即发生了脏读），而之后无论第二条sql是否执行，只要该事务不提交，则所有操作都将回滚，那么当b以后再次查看账户时就会发现钱其实并没有转。

 

不可重复读：是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

例如事务t1在读取某一数据，而事务t2立马修改了这个数据并且提交事务给数据库，事务t1再次读取该数据就得到了不同的结果，发送了不可重复读。

　　不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

　　在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据a和b依次查询就可能不同，a和b就可能打起来了……

 

幻读：是事务非独立执行时发生的一种现象。

例如事务t1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务t2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务t1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务t2中添加的，就好像产生幻觉一样，这就是发生了幻读。

　　幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

 

现在来看看mysql数据库为我们提供的四种隔离级别：

　　① serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

　　② repeatable read (可重复读)：可避免脏读、不可重复读的发生。

　　③ read committed (读已提交)：可避免脏读的发生。

　　④ read uncommitted (读未提交)：最低级别，任何情况都无法保证。

 

　　以上四种隔离级别最高的是serializable级别，最低的是read uncommitted级别，当然级别越高，执行效率就越低。像serializable这样的级别，就是以锁表的方式(类似于java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在mysql数据库中默认的隔离级别为repeatable read (可重复读)。

　　在mysql数据库中，支持上面四种隔离级别，默认的为repeatable read (可重复读)；而在oracle数据库中，只支持serializable (串行化)级别和read committed (读已提交)这两种级别，其中默认的为read committed级别。

 

在mysql数据库中查看当前事务的隔离级别：

    select @@tx_isolation;

在mysql数据库中设置事务的隔离级别：

    set  [glogal | session]  transaction isolation level 隔离级别名称;
​
    set tx_isolation=’隔离级别名称;’

 

9）索引

索引用于快速找出在某个列中有一特定值的行，不使用索引，mysql必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，mysql能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

索引我们分为四类来：

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。

断裂索引又分为：

普通索引：mysql中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引：索引列中的值必须是唯一的，但是允许为空值。

主键索引：是一种特殊的唯一索引，不允许有空值。

组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

全文索引：全文索引，只有在myisam引擎上才能使用，只能在char,varchar,text类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。

空间索引：空间索引是对空间数据类型的字段建立的索引，mysql中的空间数据类型有四种，geometry、point、linestring、polygon。

　　　　　　　　　　　　　　　空间索引是对空间数据类型的字段建立的索引，mysql中的空间数据类型有四种，geometry、point、linestring、polygon。

在创建空间索引时，使用spatial关键字。要求，引擎为myisam，创建空间索引的列，必须将其声明为not null。

 

创建索引方法：

create index 索引名 on 表名（字段名）;

alter table 表名 add index 索引名（字段名）；

create table(id int not null,username varchar(10),index 索引名(username(length)));

 

删除索引方法：

alter table 表名 drop index 索引名；

drop index 索引名 on 表名；

 

查看索引：

可以使用 show index 命令来列出表中的相关的索引信息。可以通过添加 \g 来格式化输出信息。

如：mysql> show index from table_name; \g

 

10）b树结构

1970年，r.bayer和e.mccreight提出了一种适用于外查找的树，它是一种平衡的多叉树，称为b树（或b-树、b_树）。

数据库索引底层常用就是用就是b树或者是b+树这种结构

• 先说说树吧，其实树就是从一个根节点出发，其可以有很多子节点，而子节点又可以有很多子节点，这样就像我们现实生活中的树一样，不过我们这颗树是倒立的！因为树的分支太多且没有规律所以很难控制，要想让树发挥他的作用就得在基本的树结构上加上一些特性，让有了特性的树成为帮助我们解决问题的结构，最常用的就是二叉树了，二叉树听名字就知道是一个节点至多只有两个节点，这样对数进行了一定的限制，整棵树看起来就顺眼多了。

• 二叉树的拓展1：二叉搜索树，二叉搜索树的节点满足一个规律，父节点的左孩子的键值小于父节点的键值，而右孩子的键值大于父节点的键值，这样当我们在这颗数中查询某个键值时就可以根据当前节点的键值和要寻找的键值的大小比较，确定该忘哪条路走下去。二叉搜索树还有一个特点就是中序遍历的时候其键值是按大小排序的。

• 二叉树的拓展2：平衡二叉树，由于我们要插入的数据可能是本身就排好序的，所以会导致插入数据时树变成线性的结构，只有一条路。。于是我们需要保证二叉树的平衡，当发现这棵树要出现往一边倒的情况时就要想某种方式让其保持平衡（叶子节点的高度差最大为1），这就设计到一些节点的旋转，变换了。

• 二叉树的拓展3：红黑树，红黑树也是一种平衡二叉树，不过加入了一些新的特性，听名字就知道，在红黑树中节点的颜色要么是红色要么是黑色的，当然还有其他的一些特性，当插入或者删除数据破坏了红黑树的这些特性时，我们需要进行一些操作（一般是颜色改变和树的旋转）红黑树保持其原有的特性。

• 由于二叉树是二叉的，所以当树的节点不断增加时就会导致树的高度不断的增加，所以查询的效率就很低了，当我们面对海量数据（像数据库中保存的数据）的时候这种结构是不行的，所以我们又衍生出了新的树结构。

• 二叉树的拓展4：b树，b数一样拥有自平衡的特性，最大的区别在于b树不是二叉的，而是多叉的，具体有多少个叉要根据树的阶数来判断。

• 二叉树的拓展5：b+树，和b树相比，b+树又增加了一些特性，b+树主要是为了方便查询一个区间的数据集合，因为我们使用b树的时候要想查询某个区间内的数据得使用中序遍历将树中的数据全部遍历一遍，这样的时间复杂度是o（n）,效率太低了。而b+树只用叶子节点保存具体值的地址，非叶子节点只保存其子节点的指针，叶子节点之间通过指针链接起来，是有序的，所以在查找一个范围内的数据是很有效的。其时间复杂度为o(logn+m)，m为要查找的数据个数。