【mysql】mysql总述——存储引擎、索引、触发器、事务以及简单的SQL语句等

rhel-server-6.3-i386-dvd.iso

service mysqld start

mysql -u root -p

service mysqld start 提起服务器端；

mysql -u root -p      连接到mysql服务器上；

一、存储引擎（重点掌握MYISAM  INNODB）

（一）MYISAM存储引擎：

不支持外键、不支持事务；

支持全文索引、 支持表锁、底层用B+树实现；

会生成的文件：.frm：存储所有的创建信息；.myi：MYISAM的索引；.myd：存储数据

使用非聚簇索引，并且也可以无索引；

（二）INNODB存储引擎

不支持全文索引；

支持外键、支持事务、支持行锁、底层用B+树实现；

INNODB是将数据和索引放在一起。

Innodb采用聚集索引的方式。有主键建立主键索引，没有主键有唯一键建立唯一索引；没有主键，没有唯一键，为每一行生产一个6字节的行id,作为主键。隐藏autoincreament可自增长；

主索引：叶子节点存放真实的数据；进行查询时需要一次查找；

辅助索引：叶子结点存放主索引的索引值；进行查询时需要两次查找；

（三）Memory存储引擎

数据是存放在内存中的；将数据放在内存中，如果数据库重启或者宕机，表数据就会丢失。非常适合存储一些临时表，默认的是哈希索引，不是B+树索引，varchar()默认是按照char()存储的，浪费内存。不支持text<大文本类型>和BLOB<图片类型>类型。如果数据中有text和BLOB类型，数据库会把这些数字转换到磁盘上。

（四）ARCHIVE存储引擎

日志文件<记录系统运转过程中的记录点>，按照1:10的比例进行压缩存储；

支持select和insert操作；不允许update和delete操作；（update和delete操作时需要进行解压，处理完后再次进行压缩，浪费时间和资源）。

总结：

二、索引（重点掌握）

（一）索引的概念

索引是一种数据结构（树形结构）。索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

（二）MYISAM和INNODB的索引处理方式

1、NYISAM索引底层是用B+树实现的；它是将索引和数据分离开来；

主索引：不允许key重复

辅助索引：允许key重复；叶子节点放的是数据的地址；查询1次；

2、INNODB索引底层也是B+树实现；它是把索引当成数据的一部分存储；

主索引：叶子节点放的是真实额的数据；

辅助索引：叶子节点放的是主索引的索引值；查询2次，时间复杂度是O(log x n);

（二）索引的分类

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

Mysql下索引的分类：

普通索引：最基本的索引，没有任何限制；

直接创建索引：

CREATE INDEX index_name ON  table(column(length));

修改表结构创建索引：

ALTER  TABLE table_name ADD INDEX index_name ON(column(length));

创建表的同时创建索引：

CREATE TABLE ‘table’(表中的属性…..    INDEX index_name(title(length)))

删除索引

DROP INDEX index_name ON table；

唯一索引：唯一索引是利用唯一键所建立的索引。也就是说索引列的值必须唯一，但是允许有空值，这和主键不一样。创建方法和普通索引类似。

直接创建索引：

CREATE UNIQUE INDEX index_name ON  table(column(length));

修改表结构创建索引：

ALTER  TABLE table_name ADD UNIQUE index_name ON(column(length));

创建表的同时创建索引：

CREATE TABLE ‘table’(表中的属性…..   UNIQUE  index_name(title(length)))

主键索引：主键索引是利用主键建立的索引。也就是说索引列的值必须是唯一且非空的。

组合索引：组合索引是利用多个字段共同建立的索引。组合索引要满足“最左前缀”的规则。也就是说只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引。

创建组合索引：

CREATE TABLE table_name ADD INDEX index_name (字段一、字段二、、、、、);

相当于分别建立了以下的索引：-----字段一、字段二

                                                               -----字段一

（因为要满足最左前缀，所以没有字段二这样的索引）;

全文索引(FULLTEXT)：全文索引仅仅可用于MYISAM表，它们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。但是生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

（三）使用索引的优点缺点以及索引的优化

索引大大提高了查询速度，但是同时使用索引会降低更新表的速度。因为在更新表时，mysql不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

索引是一种以空间换时间的做法。那么哪种情况下应该使用索引，哪种情况下不应该使用索引呢？

经常查询，有主键或唯一键，连接字段、范围查询以及经常排序的情况下应该使用索引；而不经常查询、数值少或数值大这些情况下不应该使用索引。如下表所示：

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	使用	使用
返回某范围内的数据	使用	不使用
一个或极少不同值	不使用	不使用
小数目的不同值	使用	不使用
大数目的不同值	不使用	使用
频繁更新的列	不使用	使用
外键列	使用	使用
主键列	使用	使用
频繁修改索引列	不使用	使用

（四）关于like语句操作

不鼓励使用like语句。Like”%aaa%”不会使用索引，而一般like”aaa%”可以使用索引。非食用like语句时，尽量避免通配符在最前。

三、事务（重点掌握）

事务是由单独单元的一个或多个SQL语句组成，在这个单元中，每个MYSQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态。如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

（一）事务的ACID特性

A(atomicity)原子性:原子性指的是事务里边的操作一旦开始，要么全部成功执行；要么全部失败回滚，不可以只执行其中的一部分。

C(consistency)一致性：一个事务的执行不应该破坏数据库的完整性约束。

I(isolation)隔离性：事务之间的行为不应该相互影响；

D(durability)持久性：事务提交之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不应该丢失。可以说是日志先行；

（二）不满足隔离性时产生的问题

脏读——事务读取了其他事务执行过程的数据；

事务1，首先读取到的x为100，并且将x重新写为200；与此同时，事务2也对x进行读取，读取到的x为200；随后，事务1发生异常，进行了回滚，又将x回滚到最开始的100.但是事务2读取到的x为200,200是事务1执行过程中的数据，因此事务2读到的数据时脏数据。也就是说事务2发生了脏读。

不可重复读——修改所导致；事务不能读取到原始的数据；或者说事务读取了其他事务不同阶段的结果；

事务1读取到数据x为100，此时操作还未完成；并且与此同时事务2将x写为200并提交了事务。当事务1再次对x进行读取时，所读到的x为200.前后两次读取的数据不同。也就是事务1中发生了不可重复读的错误。

这一种属于丢失更新是不可重复读的一种特例。事务1读取到x是100，但此时操作还未完成；榆次同时事务2对x进行了更新为200并且提交了事务；后来事务 1又将x更新为150并且提交了事务。这样导致的结果就是事务1的修改覆盖了事务2的修改。

幻读——是指当事务不是独立执行时发生的一种现象；例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

事务1首先读取到工资为100的人数有10人，与此同时事务2新插入一条工资也为100的数据；当事务1再次读取工资为100的人数时，读取到的结果是11.此时，事务1发生了幻读。（事务2还未进行提交）；

注意：

不可重复读的重点是修改，同样的条件，你读取过的数据，再次读取出来发现值不一样了

幻读的重点在于新增或者删除，同样的条件，第 1 次和第 2 次读出来的记录数不一样

（三）隔离级别

未提交读(READ UNCOMMITTED):在未提交读的隔离级别下，会产生脏读、不可重复读以及幻读的问题；这是隔离程度较低的一种隔离级别；一般不常用，因为他会引起很多问题。

已提交读(READ COMMITTED):已提交读可以解决脏读的问题，但是会产生不可重复读和幻读的问题；这个隔离级别是许多数据库默认的隔离级别。

可重复读(REPEATABLE READ):可重复读能解决脏读、不可重复读的问题，但还会出现幻读的问题；Mysql默认的隔离级别是可重复读RR，然而mysql的innoDB引擎间隙锁成功解决了幻读的问题。

可序列化(SERIALIZABLE):可序列化是最高的隔离级别。这种隔离级别强制要求所有事物串行执行，在这种隔离级别下，读取的每行数据都加锁，会导致大量的锁征用问题，性能最差。

 

事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。Mysql中支持事务的存储引擎有innoDB和NDB。InnoDB是mysql默认的存储引擎，其默认的隔离级别是RR，并且在RR的隔离级别下更进一步，通过多版本并发控制（MVCC,Multiversion Concurrency Control）解决不可重复读的问题，加上间隙锁（也就是并发控制）解决幻读问题。因此innoDB的RR隔离级别其实实现了可串行化级别的效果，而且保留了比较好的并发性能。事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。说到事务日志，不得不说的就是redo和undo。

（四）日志

Redo log：记录将要执行的操作；

事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是“日志先行”。当事务提交后，在buff pool中映射的数据文件才会慢慢地被刷新到磁盘中。此时如果出现数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，那数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。

Undo log：记录执行前后的数据状态；

undo log主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。

 

四、锁机制

（一）MYISAM的锁机制：支持表锁

         Select：读锁

Insert、delete、update：写锁

读锁：共享读锁  加锁后，其他链接可以查看但是不能修改； 兼容读锁

写锁：独占写锁  加锁后，其他链接不可以做任何操作；  不兼容读写锁

（二）INNODB的锁机制：支持行锁

         Select：不加锁

         Insert、deete、update：写锁

         读锁：共享锁  共享读锁

         写锁：排他锁（排斥一切其他的锁）；

使用索引时，行锁；当commit后，结束事务，锁释放。

（三）锁的分类

按锁的粒度划分：可分为表锁、行锁、页锁；

按锁级别划分：可分为共享锁、排他锁（排斥一切其他的锁）；

按加锁方式划分：可分为自动锁、显式锁

按操作划分：可分为DDL锁和DML锁

按使用方式划分：可分为悲观锁（先加锁控制再执行）和乐观锁（先执行，当遇到问题时再进行加锁控制）；

意向锁：防止死锁

五、触发器

（一）触发器简述

触发器是一个特殊的存储过程，不同的存储过程要用CALL来调用，而触发器不需要用CALL，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被mysql自动调用。触发器是一条语句，当对数据库作修改时，它自动被系统执行。要设置触发器机制，必须满足两个要求：

第一指明什么条件下执行触发器。它被分解为一个引起触发器被检测的事件和一个触发器执行必须满足的条件；

第二指明触发器执行时的动作；

一旦我们将一个触发器输入数据库，只要指定的事件发生，相应额的条件满足，数据库系统就有责任执行它。

MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外，还定义了 LOAD DATA 和 REPLACE 语句，这两种语句也能引起上述6中类型的触发器的触发。

一张表中最多有6种触发器；同类型的触发器只能创建一个。

（二）创建触发器：

CREATE TRIGGER trigger_name trigger_time trigger_event

 ON tbl_name FOR EACH ROW trigger_stmt

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与临时表表或视图关联起来。

trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。

trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：

INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。

UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。

DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。

请注意，trigger_event与以表操作方式激活触发程序的SQL语句并不很类似，这点很重要。

例如，关于INSERT的BEFORE触发程序不仅能被INSERT语句激活，也能被LOAD DATA语句激活。

（三）查看触发器

查看触发器是指数据库中已存在的触发器的定义、状态、语法信息等。

可以使用SHOW TRIGGERS 和在TRIGGERS 表中查看触发器信息

SHOW TRIGGERS

（四）删除触发器

使用DROP TRIGGER 语句可以删除MYSQL中已经定义的触发器，删除触发器的基本语法

DROP TRIGGER [schema_name.]trigger_name

其中（schema_name）是可选的

如果省略了schema（方案），将从当前方案中舍弃触发程序。

注意：对于相同的表相同的事件只能创建一个触发器；

（五）哪些操作能触发触发器，不允许查询

Insert    insert    load   replace

Update  update

Delete   delete

六．存储过程（了解）

七、SQL语句（会写SQL语句）

DDL数据定义语言：create drop alter show

DCL数据控制语言:权限管理 grant revoke

DML数据操纵语言insert  delete  update  select

（一）DDL  数据定义语言   结构

create  drop   alter   show

         1.库

                   创建库：create database [if not exists] 库名;

删除库：drop  database [if exists] 库名;

                   查询：Show databases;

         2.表

                   创建表

                           create table tb_name(

                                      字段名称  字段类型 [字段约束] [注释],

                                     字段名称  字段类型 [字段约束] [注释],

                                     ......

                                     );

                   例子：create table stu(

                                     id varchar(20) primary key,

                                     name varchar(10)  not null,

                                     age  int  not null,

                                     sex enum("man","woman")

                            );

                   字段约束：主键 唯一键 非空唯一 外键 默认空等

            查看表

                                     desc tb_name;  //表的字段属性

                                     show create table tb_name; //查看表的创建过程

                   删除表

                                     drop table tb_name;

                   修改表结构

                            修改字段类型：alter table stu modify id varchar(20);

                            修改字段名称：alter table stu change id mid varchar(20);

                            添加字段        after  first;

                                               alter table stu add score1 float first;

                            删除字段：alter table stu drop score1;

                            修改表名：alter table stu rename student;

（二）DML  数据操纵语言   数据

             insert  delete  update  select

                   1.插入元素 load source //

                                     insert into stu values('003','wangwu',19,"woman"),

                                                                                    ('002','lisi',22,"woman");

                                     insert into stu(mid,name,age) values("004","zhaoliu",17);

                                     insert into stu(mid,name,age) values(" ","",17);

                   2.删除元素 truncate DDL (truncate table stu;)

                                     delete from stu;

                                     delete from stu where age = 17;

                   3.修改元素     

                                     update stu set name = "zhangsan";

                                     update stu set mid = "007" where age = 17 and name = "";

                   4.查询

                            普通查询

                                     select * from tb_name;

                                     select * from stu;

                                     select mid,name,age,sex from stu;

                            去重查询  distinct

                                     select distinct age from stu;

                            排序查询  order by  asc  desc

                                     select distinct age from stu

                                     order by age;

                           等值查询

                           连接查询

                          外连接

                            左外连接（左表的数据都要存在）

                              select name,score

                              from

                              (select id,name from student where age >= 20)a

                            left join

                              (select id,score from result where score<60)b

                              on a.id = b.id

                              //where score is not null;

                       右外连接（右表的数据都要存在）

                              select name,score

                              from

                              (select id,name from student where age >= 20)a

                              right join

                              (select id,score from result where score<60)b

                              on a.id = b.id

                              //where name is not null;

                       全连接（左右表的数据都要存在）

                              select name,score

                              from

                              (select id,name from student where age >= 20)a

                              full join

                              (select id,score from result where score<60)b

                              on a.id = b.id

                              //where score is not null and name is not null;

                内连接（只显示匹配上的结果集）

                            select name,score

                            from

                            (select id,name from student where age >= 20)a

                            inner join

                            (select id,score from result where score<60)b

                            on a.id = b.id

                            //where score is not null and name is not null;

         7.联合查询

                   union 去重

                   select mid,name,age,sex from student

                   union

                   select id,name,age,sex from teacher

 

                   union all 不会去重

（三）DCL数据控制语句

         1.创建用户

                   create user user_name@host;

         2.grand  授权语句

          grant [权限] on [对象类型] to 用户；//授权

          grand select on table.* to user_name;  用户必须有赋权的权限才能给别的用户进行赋权；( all 所有权限)             

         revoke 回收用户权限语句

                   revoke select on table.* from user_name;

         例如：

                   update user set password("123") where user = "user_name";

                   root-->u1-->u2-->u3  授权方向

                   root<--u1<--u2<--u3  回收方向