MySQL总结

一、数据库概述

1.1什么是数据库？

简而言之，数据库（DataBase）是一个存储数据的仓库，将数据按特定的规律存储在磁盘上，通过数据库管理系统可以有效的管理和组织数据库中的数据。

1.2 SQL语言

SQL（Structured Query Language）即结构化查询语言。分为三个部分数据定义语言（Data Definnition Language,简称为DDL），数据操作语言(Data Manipulation Language，简称为DML)和数据控制语言（Date Control Language,简称为DCL）。
DDL语句:数据定义语言主要用于定义数据库、表、视图、索引和触发器等。其中包括CREATE语句、ALTER语句和 DROP语句。CREATE 语句主要用于创建数据库、创建表和创建视图等。ALTER语句主要用于修改表的定义、修改视图的定义等。DROP语句主要用于删除数据库、删除表和删除视图等。
DML语句:数据操纵语言主要用于插入数据、查询数据、更新数据和删除数据。其中包括INSERT语句、SELECT语句、UPDATE 语句和 DELETE语句。INSERT语句用于插入数据;SELECT 语句用于查询数据;UPDATE语句用于更新数据;DELETE语句用于删除数据。
DCL语句:数据控制语言主要用于控制用户的访问权限。其中包括GRANT 语句和REVOKE语句。GRANT 语句用于给用户增加权限;REVOKE语句用于收回用户的权限。
可以在java中嵌入SQL语句，通过执行java语言调用SQL语句，即可在数据库中实现增删改查。

二、MySQL数据类型

2.1MySQL数据类型介绍

MySQL数据库提供了多种数据类型。其中包括整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型和二进制数据类型。不同的数据类型有各自的特点，使用范围不相同。而且，存储方式也不一样。

2.1.1整数类型

整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两类整数类型。MySQL 数据库除了支持这两种类型以外，还扩展支持了TINYINT、MEDIUMINT和 BIGINT。
int取值范围： 无符号范围：0 ~ 4294967295，有符号：-2147483647 ~ 2147483647。int类型的默认显示宽度为11，无符号的1~ 10，数据的宽度不能大于默认宽度。如果大于默认宽度，那该数据已经超过了该类型的最大值。因为最大值的宽度必须是小于等于默认宽度。如果一个值大于了这个类型的最大值，那么这个值是不可能插入的。

2.1.2 浮点数类型和定点数类型

MySQL中使用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数(FLOAT型）和双精度浮点数（DOUBLE型）。定点数类型就是DECIMAL型。DECIMAL型的取值范围与DOUBLE相同。但是DECIMAL的有效取值范围由M和D决定。而且，DECIMAL型的字节数是M+2。也就是说，定点数的存储空间是根据其精度决定的。MySQL中可以指定浮点数和定点数的精度。也就是说M和D可以自己来指定。
在MySQL中，定点数以字符串形式存储。因此，其精度比浮点数要高。而且, 浮点数会出现误差，这是浮点数一直存在的缺陷。如果要对数据的精度要求比高，还是选择定点数（ DECIMAL）比较安全。

2.1.3 日期和时间类型

时区问题
show variables like ‘%time_zone%’;
±-----------------±-------+
| Variable_name | Value |
±-----------------±-------+
| system_time_zone | |
| time_zone | SYSTEM |
±-----------------±-------+
set time_zone = ‘+8:00’;

修改全局的时区配置

set global time_zone = ‘+8:00’;
flush privileges;
日期与时间类型是为了方便在数据库中存储日期和时间而设计的，MySQL中有多种表示时间的数据类型，其中，YEAR类型表示时间：DATE类型表示日期， TIME类型表示时间;DATETIME 和TIMESTAMP表示日期和时间。

2.1.4 字符串类型

1 CHAR类型和VARCHAR类型

CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。例如，CHAR(100)就是指定CHAR类型的长度为100。
VARCHAR类型的长度是可变的，在创建表时指定了最大长度。定义时其最大值可以取0～65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在О到最大长度之间。例如，VARCHAR(100)的最大长度是100。但是，不是每条记录都要占用100个字节。而是在这个最大值范围内，使用多少分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1。这样，即可有效节约系统的空间。

2 TEXT类型

TEXT类型是一种特殊的字符串类型。TEXT只能保存字符数据，如新闻的内容等。TEXT类型包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

3 ENUM类型

ENUM类型又被称为枚举类型，如果只能选取列表中的一个值，就选择ENUM类型;如果需要选取列表中多个值得组合，则需要选择SET类型。

4 SET类型

SET类型和ENUM类型对于取值在一定范围的离散值很有效。SET类型只能在取值列表内取一个值，ENUM类型可以在取值列表内取多个值。这两个类型的数据都不是直接将数据存入数据库，而是将其列表中的编号存入数据库。

2.1.5二进制类型

二进制类型是在数据库中存储二进制数据的数据类型。二进制类型包括 BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。

2.2如何选择数据类型

2.2.1整数类型和浮点数类型

   整数类型和浮点数类型最大的区别在于能否表达小数。整数类型不能表示小数，而浮点数类型可以表示小数。不同的整数类型的取值范围不同。TINYINT类型的取值范围为0～255。如果字段的最大值不超过255，那选择TINYINT类型就足够了。BIGINT类型的取值范围最大。最常用的整数类型是INT类型。 浮点数类型包括FLOAT类型和 DOUBLE类型。DOUBLE类型的精度比FLOAT类型高。如果需要精确到小数点后10位以上,就应该选择DOUBLE类型,而不应该选择FLOAT类型。

2.2.2 浮点数类型和定点数类型

   对于浮点数和定点数，当插入值的精度高于实际定义的精度时，系统会自动进行四舍五入处理。其目的是为了使该值的精度达到要求。浮点数进行四舍五入时系统不会报警，定点数会出现警告。 DECIMAL型默认整数位为10，小数位为0. 在MySQL中，定点数精度比浮点数要高。而且，浮点数会出现误差。如果要对数据的精度要求比较高，应该选择定点数。

2.2.3 CHAR类型和VARCHAR类型

   CHAR类型的长度是固定的，而 VARCHAR类型的长度是在范围内可变的。因此，VARCHAR类型占用的空间比CHAR类型小。而且，VARCHAR类型比 CHAR类型灵活。对于长度变化比较大的字符串类型，最好是选择VARCHAR类型。 虽然CHAR类型占用的空间比较大，但是CHAR类型的处理速度比VARCHAR 快。因此，对于长度变化不大和查询速度要求较高的字符串类型，最好选择CHAR类型。 

2.2.4时间和日期类型

   YEAR类型只表示年份。如果只需要记录年份，选择YEAR类型可以节约空间。TIME类型只表示时间。如果只需要记录时间，选择TIME类型是最合适的。DATE类型只表示日期。如果只需要记录日期，选择DATE类型是最合适的。如果需要记录日期和时间，可以选择 DATETIME类型和 TIMESTAMP类型。DATETIME类型表示的时间范围比 TIMESTAMP 尖型大。囚此相而时区来易元的。如天，选择DATETIME 类型比较合适。TIMESTAMP类型的时间是根据时区来显示的。如果需要显示的时间与时区对应，那就应该选择TIMESTAMP类型。 

2.2.5 ENUM类型和SET类型

   ENUM类型最多可以有65535个成员，而SET类型最多只能包含64个成员。两者的取值只能在成员列表中选取，ENUM只能选择一个，SET能选择多个。所以男女选择用enum，“爱好”字段选set。

2.2.6TEXT类型和BLOB类型

 TEXT类型与BLOB类型很类似。TEXT类型存储只能存储字符数据。而 BLOB类型可以用于存储二进制数据。如果要存储文章等纯文本的数据，应该选择TEXT类型。如果需要存储图片等二进制的数据，应该选择BLOB类型。

三、数据库基本操作

3.1创建数据库练习

现在我有以下属性要求同学们选择合适的数据类型和约束进行表的创建stu_id(主键+自增) 、 class_id （班级号 非空）、 name（唯一） 、 T_id（班主任的编号） 、age（默认0）、 sex（非空）、 score（小数）考试成绩。 

create table stu( stu_id int primary key auto_increment, class_id int not null, name nvarchar(15) unique, t_id int, age int default 0, sex enum('man','woman') not null, score float(5,2) );

3.2约束条件

约束条件
--------------------------

完整性约束       |说明
--------------------------
primary key      |表示当前属性为该表的主键，可以区分不同的行记录
--------------------------
foreign key      |修饰的属性为该表的外键，表之间关联使用的键
--------------------------
not null         | 表示属性不能为空
--------------------------
unique      	  |表示属性的值是唯一的
--------------------------
auto_increment | MYSQL特色，表示属性是自增的，自增类型为整形
-------------------------------------------------
default          |给属性设定默认值
-------------------------------------------------

3.3MySQL存储引擎

存储引擎的概念是 MySQL 的特点，而且是一种插入式的存储引擎概念。这决定了MySQL 数据库中的表可以用不同的方式存储。用户可以根据自己的不同要求，选择不同的存储方式、是否进行事务处理等。

3.3.1查看当前MySQL版本都支持哪些存储引擎

mysql> show engines\G
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
…后面还有很多支持的存储引擎，这里限于篇幅，就不粘贴完整的内容了，Support后面是DEFAULT，表示当前使用的存储引擎，上面是InnoDB。

3.3.2 MySQL常用存储引擎的差异：

在回答“MySQL不同的存储引擎有哪些区别？”这样的问题的时候，主要从以下几点来回答就可以（问题的核心），这个问题我们主要研究的是：MyISAM、InnoDB、Memory这三个引擎的区别，那其实我们下面的表格就已经能说明问题了：

可以在创建表的时候指定存储引擎，如下：
CREATE TABLE ai (
i BIGINT(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (i)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    也可以修改已存在表的存储引擎，如下：

ALTER TABLE user ENGINE = InnoDB;

还可以修改配置文件，MySQL Server启动的时候都会加载一个配置文件，windows下是my.ini文件，linux下是my.cnf文件，打开配置文件，在[mysqld]下面添加如下内容，保存，重启MySQL Server服务器，默认就采用配置项指定的存储引擎了。记得操作之后一定要保存再重新启动Mysql服务，需要新建数据库老的数据库用的还是老的存储引擎。

3.4 索引

3.4.1索引简介

一列或者多列值 排序之后的一个数据结构，索引的作用是为了提高查询速率，跟sql语句有关。索引建立在表上，
Innodb
.frm 存储表结构
.ibd 存储数据和索引

myIsam：
.myi ：存储索引
.myd ： 存储数据
.frm  存储表结构

优势：类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本。通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势：　实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。

虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。都会调整因为更新所带来的键值变化后的索引信息。 建立索引会占用磁盘空间的索引文件。

3.4.2基本语法

1，INDEX（普通索引）：

ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col’)
最基本的索引，没有任何限制

2，UNIQUE（唯一索引）：

ALTER TABLE ‘table_name’ ADD UNIQUE(‘col’)
与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。

3，PRIMARY KEY（主键索引）：

ALTER TABLE ‘table_name’ ADD PRIMARY KEY(‘col’)
是一种特殊的唯一索引，不允许有空值。

4，FULLTEXT（全文索引）：

ALTER TABLE ‘table_name’ ADD FULLTEXT(‘col’)
仅可用于MyISAM和InoDB，针对较大的数据，生成全文索引很耗时耗空间

5，组合索引：

ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,‘col2’,‘col3’)

3.4.3哪些情况需要或不需要创建索引

需要索引的情况：

①主键自动建立唯一索引
②频繁作为查询条件的字段应该创建索引 　　　
③查询中与其他表关联的字段，外键关系建立索引
④频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引
⑤WHERE条件里用不到的字段不创建索引
⑥单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
⑦查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
⑧查询中统计或者分组字段

不需要索引的情况：

①表记录太少
②经常增删改的表
　　　提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。
③注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

3.4.4 索引失效的情况

1．隐式转换导致索引失效

这一点应当引起重视.也是开发中经常会犯的错误.由于表的字段name定义为varchar(20),但在查询时把该字段作为number类型以where条件传给Mysql,这样会导致索引失效.强转会引起索引失效：
user  id  name 
index（name）;  varchar（20）
错误的例子：select * from test where name = 13333333333; 
需要对name先强转在比较 
100行数据  把100行数据的name属性分别取出来先强转在比较。
原因：需要把所有的数据对应的name属性取出进行强转。
需要将每一行数据都拿出来进行强换之后再比较。
强转之后改变了原有字段的比较规则 
正确的例子：select * from test where name='13333333333'; 能用到索引 

2. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

index(id)
错误的例子：select * from test where id - 1 < 9;
需要将每一个元组中的id值先取出，再减一然后和9再去比较
正确的例子：select * from test where id < 10;能用到索引
select * from test where id = 9+1;

3. 使用MySQL内部函数导致索引失效

对于这样情况应当创建基于函数的索引.
错误的例子：select * from test where 函数(id)=10; 说明，此时id的索引已经不起作用了
									
要使用索引的话需要将：k(x) = y;  ----> x = R(y);
id  < 函数(age);  还是用不到索引

max sum min ........
需要将每一个元组中的id值先取出,再通过函数计算，计算之后的值在和10比较

4. 以下使用会使索引失效，应避免使用；

a. 使用 <> 、not in 、!=
index(id)
select * from id not in (12,13,15); 用不到  没有明确查询条件
id
1
...
...
999 
select * from id  in (12,13,15);    能用到
b. like “%_” 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
%:任意个任意字符  
ndex（name）
select * from name like '%a';  //由于字符串的最左比较法  用不到
字符串的索引是如何建立：从左到右一个字符一个字符进行大小比较然后得出的B+树的结构。
abcdsdsa
abdr
select * from name like 'a%';  //由于字符串的最左比较法  能用到

5、使用OR关键字查询语句

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中列都是索引时，查询中才会使用索引。否则，查询将不使用索引。

 name = "tom" or(或)  age < 90;  ---->index(name)   index(age);

 name = "tom" and  age < 90;   ---->  index(name,age) 

3.4.5 索引的设计原则

1.选择唯一性索引

 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2.为经常需要排序、分组和联合操作的字段建立索引

 经常需要ORDER BY、GROUP BY、DISTINCT 和 UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3.为常作为查询条件的字段建立索引。

 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4.限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间|索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5.尽量使用数据量较少的索引

如果索引很长，查询速度会收到影响。

6.尽量使用前缀来进行索引

如果索引字段的值很长，最好使用值得前缀来索引。

7.删除不再使用或很少使用得索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

注意

  选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则,但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

3.4.6索引练习题

一、根据表结构，判断当分别执行如下5条语句时想要使用索引，分别应该如何建立。
创建表

 create table stu_index1 (id int primary key,
		name varchar(20),score double);
insert stu_index1 values(1,'liu',89);
insert stu_index1 values(2,'sam',78);
insert stu_index1 values(3,'jim',95);

根据哪个属性筛选的就要在哪个属性上建立索引
1.explain select * from stu_index where id = 3\G


InnoDB:由于主键索引默认创建所以不需要任何操作。
叶子节点：存储主键和所有的数据。

MyIsam: 能用到索引就行  由于主键索引默认创建所以不需要任何操作。
要求只查询一次：index(id,name,score);
建立索引的属性和数据对应的地址。

2.explain select * from stu_index where name='liu'\G
InnoDB:index(name);  叶子节点存储建立索引的属性的值和所对应的主键的值。
查询一次：index(name,score);
MyIsam：index(name)  叶子节点存储：建立索引的属性和数据对应的地址。
查询一次：index(name,id,score);

3.explain select * from stu_index where name = 'liu' and score = 89;
 index(score,name); 2 
 select * from stu_index where  score = 89 and name = 'liu';

InnoDB：index(name,score); 可以   
index(score,name); 因为mysql内部有优化，所以也可以。


只走一次   叶子节点存储建立索引的属性的值和所对应的主键的值。
MyIsam：index(name,score);    index(score,name);  会查询两次
叶子节点存储：建立索引的属性和数据对应的地址。
查询一次：index(name,score,id);

4.explain select id from stu_index where name='liu'\G



InnoDB： index(name); 

叶子节点存储建立索引的属性的值和所对应的主键的值。
MyIsam：index(name) 查询两次    叶子节点存储：建立索引的属性和数据对应的地址。
查询一次： index(name,id);   对  index(id,name); 错 



原因：无法进入B+树的结构。

5.explain select score from stu_index where name='liu'\G
MyIsam/InnoDB：查询一次：index(name,score);
原因：两个引擎下的索引的叶子节点都存储建立索引的属性的值。

二、根据表结构，判断当分别执行如下语句时想要使用索引，应该如何建立。

 mysql> create table orderlist(name varchar(20) not null,
-> productid int not null,
-> date timestamp);

 1.explain select * from orderlist where name ='Tom' order by date\G

1.explain select * from orderlist  where name ='Tom' group by date\G

group date 
index(name,date);
 index(name,date)

InnoDB ：index(name,date);对  index(date,name);错 

三、根据以下表结构和建立的索引分别判断以下sql语句能否使用到所建立的索引，
能用到回答能，不能用到则回到不能。

    CREATE TABLE `student_index` (
 `id` int(11) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `cid` int(11) DEFAULT NULL,
 `school` char(20) NOT NULL DEFAULT '',
 KEY `name_cid_INX` (`name`,`cid`,`school`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into student_index values
(1,'weixin',12,'caijing'),
(2,'weixin',13,'ligong'),
(3,'weixin',14,'gongye');

最左前缀原则 ：从左往右进行比较的
要使用索引的话：左边第一个属性一定不能缺少。
要是想把索引用完全：从左到右一个属性都不能少，否则索引将使用不完全。

带头大哥不能死，兄弟情谊不能断。

1.explain select * from student_index where name = 'abc' and cid = 123 
 and school = 'caijing'\G


explain select * from student_index where school = 'caijing'  and cid = 123 and name = 'abc'\G


2.explain select * from student_index where name = 'abc' and cid = 123\G


explain select * from student_index where cid = 123 and name = 'abc'\G


 3.explain select * from student_index where name = 'abc'\G


4.explain select * from student_index where name = 'abc' and school='ligong'\G	

833(name+cid+school) 773(name+cid)  768(name)  828(name + school)



 5.explain select * from student_index where cid = 123 and school = 'ligong'\G

最左前缀原则

四、联合查询中如何使用索引，以下两张表均在stu_id 字段上添加索引 index(stu_id)。判断哪张表的索引能用到。

小表决定查询次数，大表决定查询时间（因为只能使用到大表的索引）

mysql> select * from  rb1;   

 +------+--------+------+
| id   | stu_id | name |
+------+--------+------+
|    1 |     23 | sa   |
|    2 |     24 | sa   |
|    3 |     25 | sa   |
|    4 |     40 | sa   |
|    5 |     41 | sa   |
|    6 |     42 | sa   |

mysql> select * from  rb2;  
+------+--------+----------+-------+
| id   | stu_id | class_id | score |
+------+--------+----------+-------+
|    1 |     23 |        1 | 56.60 |
|    2 |     24 |        2 | 99.30 |
|    3 |     25 |        2 | 85.40 |

1.explain select * from rb1 a , rb2 b 
 where a.stu_id = b.stu_id\G
 用的是rb1

explain select * from rb1 where stu_id int (select stu_id from rb2);




2.explain select * from rb1 a join rb2 b 
on a.stu_id = b.stu_id where a.stu_id < 25\G 
用的是rb2的索引	

MySQL会首先判断rb1（别名表a）和rb2（别名表b）哪个表小，这里表小主要指的是行数
少，很显然rb2表小，MySQL会对rb2表进行整表遍历，然后在a表上根据stu_id字段进行查询，
所以rb2表就是小表，无论如何都是要整表遍历的，是使用不到索引的，但是大表rb1表的
stu_id字段创建索引，就能使用到了！所在在连接查询的时候，小表总是要整表搜索的，
建索引没有用，大表创建索引是能提高查询效率的，小表决定查询次数，大表决定查询时间
能不能用到索引，用到是那张表的索引。

小表决定查询次数：
大表决定查询时间：有索引查询的就快，没有索引查询的就慢。

 1.explain select * from rb1 a , rb2 b where a.stu_id = b.stu_id\G
 where a.stu_id = b.stu_id

底层处理：（1）把b中的所有的stu_id取出来，分别和a中的stu_id作比较
a.stu_id in (23,24,25); 比较三次 mysql采用第一种做法
把小表中的所有的数据都取出来然后和大表中的数据作对比。

大表能够使用到索引 而小表用不到索引

（2）把a中的所有的stu_id取出来，分别和b中的stu_id作比较
b.stu_id in (23,24,25,40,41,42);比较六次

两张表中建立的索引都用到了。

rb1使用到索引：
rb2没有使用到索引：

2. explain select * from rb1 a join rb2 b
   on a.stu_id = b.stu_id where a.stu_id < 25\G 现在哪张表是小表？

   explain select * from rb1 a join rb2 b
   on a.stu_id = b.stu_id where a.id < 2\G

alter table rb1 add index(id);

只有大表能用到索引

rb2使用到索引：rb2现在变成大表
rb1使用到索引：因为要先根据sql语句筛选出rb1中stu_id < 25的那部分

3.5 数据库范式

 应用数据库范式可以带来许多好处，但是最重要的好处归结为三点：
1）减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）
2）消除异常（插入异常，更新异常，删除异常）
3）让数据组织的更加和谐…

但是数据库范式绝对不是越高越好，范式越高，意味着表越多，多表联合查询的几率就越大，SQL查询的效率就变低。

一般开发中只遵从第三范式就好，是具体情况而定。

1. 第一范式（1NF）

每一列保持原子特性列都是基本数据项，不能够再进行分割，否则设计成一对多的实体关系。
例如表中的地址字段，可以再细分为省，市，区等不可再分割（即原子特性）的字段，如下：
列是基本数据项、不能再进行擦缝，否则设计成一对多的关系
不满足第一范式不能称之为关系型数据库	

拆分改造后：

   学生表（学号、用户名、性别、年龄，地址）
   例：陕西省西安市西安工大学
例：陕西省西安市未央区学府中路西安工业大学
地址信息还包含省市区可以拆分
拆分改造后：
 学生表（学号、用户名、性别、年龄、地址ID）
 地址表（地址ID、省、市、区、街道、学校）

上图的表就是把地址字段分成更详细的city,country,street三个字段，注意， 不符合第一范式不能称作关系型数据库。

2. 第二范式（2NF）

非主属性完全依赖于主键(主要针对联合主键-》消除部分依赖)
符合第一范式的基础上，非主属性完全依赖于主关键字，如果不是完全依赖主键，应该拆分成新的实体，设计成一对多的实体关系。
 示例：
假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，因为存在如下决定关系：
(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
这个数据库表不满足第二范式，因为存在如下决定关系：
(课程名称) → (学分)
(学号) → (姓名, 年龄)

即存在组合关键字中的字段决定非关键字的情况。
由于不符合2NF，这个选课关系表会存在如下问题：

(1) 数据冗余：

同一门课程由n个学生选修，”学分”就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。

(2) 更新异常：

若调整了某门课程的学分，数据表中所有行的”学分”值都要更新，否则会出现同一门课程学分不同的情况。

(3) 插入异常：

假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有”学号”关键字，课程名称和学分也无法记录入数据库。

(4) 删除异常：

 假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。

示例2：

 学生选课表（学生ID、学生姓名、学生性别、课程ID、课程成绩）
主键（学生ID、课程Id）
 学生姓名-》学生ID  -》部分依赖
学生性别-》学生ID  -》部分依赖
课程成绩-》（学生ID、课程id）-》完全依赖
拆分改造后：
学生表（学生ID、学生姓名、学生性别） 主键：学生ID
课程成绩表（课程ID、学生ID、成绩） 主键：（课程ID、学生ID）  

示例：

选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 课程所占学分)，（学号，课程名称）是联合主键，但是学分字段只和课程名称有关，和学号无关，相当于只依赖联合主键的其中一个字段，不符合第二范式。

3. 第三范式（3NF）

 属性不依赖于其它非主属性（消除依赖传递）
基于第二范式的基础，非主属性只依赖于主属性
要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

示例：

 学生表（学生ID、学生姓名、学生性别、学院id、学院电话）
主键：学生ID
学生姓名-》学生ID
学生性别-》学生ID
学院名称-》学生ID
学院电话 -》  学生ID -》查询学院id-》查询学院电话

数据冗余、更新异常、插入异常和删除异常的情况同样存在。

拆分改造后：
学生表：（学生ID、学生姓名、学生性别、学院ID）主键：学生ID
 学院表：（学院ID、学院名称、学院电话） 主键：学院ID

示例：

    学生关系表为Student(学号, 姓名, 年龄, 课程, 成绩)，学号是主键，但是学院	电话只依赖于所在学院，并不依赖于主键学号，因此该设计不符合第三范式，应该把学院专门设计成一张表，学生表和学院表，两个是一对多的关系。

注意：一般关系型数据库满足第三范式就可以了！

4. BC范式（BCNF）

 每个表中只有一个候选键  
不重复的属性称为候选键
简单的说，BC范式是在第三范式的基础上的一种特殊情况，即每个表中只有一个候选键（在一个数据库中每行的值都不相同，则可称为候选键）。
在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合第三范式。
假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：
主键（仓库ID,管理员ID, 存储物品ID）
（ 仓库ID,管理员ID, 存储物品ID） → (数量)
 但是我们发现：
(仓库ID, 存储物品ID) →(管理员ID, 数量)
(管理员ID, 存储物品ID) → (仓库ID, 数量)
所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是  StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：
(仓库ID) → (管理员ID)
(管理员ID) → (仓库ID)
即存在关键字段决定关键字段的情况，所以其不符合BCNF范式。它会出现如下异常情况：
（1）数据冗余
（2）删除异常：
当仓库被清空后，所有”存储物品ID”和”数量”信息被删除的同时，”仓库ID”和”管理员ID”信息也被删除了。
（3）插入异常：
当仓库没有存储任何物品时，无法给仓库分配管理员。
（4）更新异常：
如果仓库换了管理员，则表中所有行的管理员ID都要修改。
 把仓库管理关系表分解为二个关系表：
仓库管理：StorehouseManage(仓库ID, 管理员ID)；
仓库：Storehouse(仓库ID, 存储物品ID, )。
这样的数据库表是符合BCNF范式的，消除了删除异常、插入异常和更新异常。

我们也可以举一个不受影响的例子
每一个员工的email都是唯一的（不可能两个人用同一个email），则此表不符合BC范式，对其进行BC范式化后的关系图为:

5. 第四范式（4NF）

消除表中的多值依赖

简单来说，第四范式就是要消除表中的多值依赖，也就是说可以减少维护数据一致性的工作。比如图4中的noNF表中的skill技能这个字段，有的人是“java，mysql”，有的人描述的是“Java，MySQL”，这样数据就不一致了，解决办法就是将多值属性放入一个新表，所以满足第四范式的关系图如下：

从上面对于数据库范式进行分解的过程中不难看出，应用的范式越高，表越多。表多会带来很多问题：
1 查询时需要连接多个表，增加了SQL查询的复杂度
2 查询时需要连接多个表，降低了数据库查询性能
因此，并不是应用的范式越高越好，要看实际情况而定。第三范式已经很大程度上减少了数据冗余，并且基本预防了数据插入异常，更新异常，和删除异常了。

3.6 常用sql语句常用

连接前服务端必须要启动

   mysql -u XXX -p XXX
   mysql：代表客户端命令

-u 后面跟用户名(user:)
-p 后面跟用户密码

退出用户端登录：

 exit

库操作SQL格式：
1、创建数据库

  create database database_name;
  create database if not exists database_name;

2、查看数据库

  show databases;

3、使用数据库

 use database_name;
 use school

如果不使用这条sql，在操作表的时候必须
使用数据库名.表名，否则将无法操作表。

5、查看数据库下存在表

   show tables;

6、删除数据库

 drop database database_name; 

 DROP database IF EXISTS database_name;

表操作SQL格式：
1、创建表

 create table table_name(
 属性名1 数据类型 [完整性约束]，
属性名2 数据类型 [完整性约束]，
属性名3 数据类型 [完整性约束]，
);

create table if not exists table_name;

表明可以用数据库名.表名表示

属性名例如：

  id,name,sex

数据类型：

  int char varchar 

2、查看表

2.1、

  desc table_name;

2.2、

   show create table table_name;

3、修改表

在使用过程中不满足要求可能要修改表
用的SQL关键字是alter

3.1、修改表名

alter table old_table_name rename [to] new_table_name; 

3.2、修改表的字段（属性） -》name ->username

 alter table table_name change 旧属性名 新属性名 新属性类型；

3.3、修改表的属性数据类型

 alter table table_name modify 属性名 属性类型

3.4、增加表的字段

 alter table table_name add 属性名 属性类型 [完整性约束]

 id name sex;

新增一个adress;

 alter table table_name add 属性名 属性类型 [完整性约束] first;

增加adress 在name后面

 alter table table_name add 属性名 属性类型 [完整性约束] after 属性名（name）;

3.5、删除字段

 alter table table_name drop 属性名；

3.6、修改字段的排列顺序

 alter table table_name modify 属性1 属性类型 first | after 属性2;

删除表：

DROP table 表名;

DROP table IF EXISTS 表名;

3.7 sql删除与查询

删除语句：

 delete  from 表名 where 筛选条件 

清空整张表：

delete from 表名;
truncate table 表名;

（1）删除年纪大于60岁的人

delete from student where age>60;

（2）删除年纪为33岁的男生

delete from student where age=33 and sex='m';

修改语句：

update 表名 set 属性名 = 新数据  where 筛选条件

（1）修改5号同学名字为小飞

 update student set name='小飞' where id=5;

（2）修改5号同学的2号科目成绩为90

 update score set score=90 where stu_id=5 and course_id=2;

（3）把年龄为33岁并且名字叫小白的人性别改为女（w）

update student set sex='w' where age=33 and name='小白';

（4）如何使用一条sql语句将 age = 99 或者 id = 14 或者 名字叫老张的学生删除

delete from student where age=99 or id=14 or name='老张';


update student set name = '张三' , age = 43 where id = 1;

基本查询：

查看所有字段

   select * from table_name;

查看指定字段

    select 属性1 别名, 属性2 from table_name ;
	select 属性1, 属性2 from table_name;

查看指定记录的所有字段

   select * from table_name where 筛选条件; 

查看指定记录的指定字段

 select 属性1 别名, 属性2  from table_name where 筛选条件;
 （1）年纪为15岁的
select * from student where age = 15;
（2）年纪为15岁并且是女生的
select * from student where age = 15 and sex = 'w';
（3）小于15的
select name,age from student where age < 15;
（4）名字叫做小芳的
select * from student where name = '小芳';
（5）名字最后一个字是明的
select * from student where name like '%明';
%：任意个任意字符
_: 一个任意字符

（6）名字第一个字是小的  并且只显示前3个人（limit）
select * from student where name like '小%' limit 0,3;
select * from student where name like '小%' limit 3;
select * from student where name like '小%' limit 1,2;
limit  off（起始下标）  ，len （长度）
（7）名字中间带安的
select * from student where name like '%c%';  //%可以代表人一个字符
（8）查询年纪为 15 12 13的人    （ in  not in ）
select * from student where age in(15,12,13);
（9）查询年纪在23到56之间    
select * from student where age between 23  and 56;
select * from student where age >= 23  and  age <= 56;	
（10）查询名字叫小强或者是女生的人
select * from student where name = '小强' or sex = 'w';	
（11）对名字第一个字是小字的人按照年龄排序（写两条sql一条升序排列一条降序排列）
select * from student where name like '小%' order by age; 升序
select * from student where name like '小%' order by age desc; 降序
select * from student where name like '小%' order by age ASC;  升序
（12）求各科课程的平均分  
select course_id,avg(score) from score group by course_id;
（13）求各科课程的总分
select course_id,SUM(score) from score group by course_id;;
（14）求各科课程最好成绩
select course_id,max(score) from score group by course_id;
				min
（15）求参加各科课程考试的学生个数
select count(stu_id),course_id from score group by course_id;
（16）求出各科平均分大于60分的科目
select course_id,cast(avg(score) as dec(4,2)) 平均分 from score  group by 
course_id having cast(avg(score) as dec(4,2)) > 60;

select course_id,avg(score) 平均分 from score  group by 
course_id having avg(score) > 60;

select vourse_id ,AVG(score) from score where AVG(score) > 60 group by course_id;
（17）年纪加上5岁等于30的人
select * from student where (age + 5) = 30;

<=>
select course_id ,AVG(score) from score having AVG(score) > 60 group by course_id;

select * from student  where name regexp '^小' order by age ASC;

联合查询语法

意义相同的字段 EX
如果找不到意义相同的字段就说明：
这几张没有关系。
内连接：

select * 
select 表1/表2表3.属性名，表1/表2表3.属性名  from 表1，表2，表3 ...... 
where 表1.EX = 表2.EX and 表2.EX = 表3.EX ....  ;

select 表1/表2表3.属性名，表1/表2表3.属性名  from 表1  join 表2
on 表1.EX = 表2.EX   join  表3 on 表2.EX  =  表3.EX ...  join 表20 on 
表19.EX = 表20.EX .........

外连接：
左连接 右连接

select 表1/表2表3.属性名，表1/表2表3.属性名  from 表1 left/right join 表2
on 表1.EX = 表2.EX  left/right join  表3 on 表2.EX  =  表3.EX
employee
	+------+------+--------+------+------+--------+
	| num  | did  | name   | age  | sex  | addr   |
	+------+------+--------+------+------+--------+
	|    1 | 1001 | 张三   |   26 | 男   | 北京   |
	|    2 | 1001 | 李四   |   24 | 女   | 上海   |
	|    3 | 1002 | 王五   |   23 | 男   | 南京   |
	|    4 | 1004 | Aric   |   15 | 男   | 成都   |
	+------+------+--------+------+------+--------+
department
	+------+-----------+--------------+---------------------+
	| did  | dname     | func         | addr                |
	+------+-----------+--------------+---------------------+
	| 1001 | 科研部    | 研发产品     | 3号楼5层            |
	| 1002 | 生产部    | 生产产品     | 5号楼1层            |
	| 1003 | 销售部    | 策划销售     | 1号楼销售大厅       |
	+------+-----------+--------------+---------------------+

（1）查询属于这家公司的员工的基本信息

 select e.*,d.dname from employee e , department d where d.did = e.did;
 select e.*,d.dname from employee e join department d on d.did = e.did;

（2）查询每个人对应的单位的部门信息如果查不到单位信息查不到显示null

 select e.*,d.dname from employee e left join department d on d.did = e.did;

（3）查询这些部门都对应哪些人，如果找不到显示null
（4）查询该公司员工年龄大于24岁的

  select e.*,d.dname from employee e , department d 

where d.did = e.did and age > 24 ;

子查询

mysql> select * from scholarship;
+-------+-------+
| level | score |
+-------+-------+
|     1 |    90 |
|     2 |    80 |
|     3 |    70 |
+-------+-------+
 select * from computer_stu;
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1005 | Lucy |    65 |
| 1006 | Andy |    99 |
| 1007 | Anda |    85 |
| 1008 | Jeck |    70 |
+------+------+-------+

select * from computer_stu2;
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    56 |
| 1002 | Tom  |    43 |
| 1003 | Jim  |    90 |
| 1004 | Aric |    92 |
| 1005 | Lucy |    88 |
| 1006 | Andy |    87 |
| 1007 | Anda |    83 |
| 1008 | Jeck |    79 |
+------+------+-------+

内部查询 外部查询
外部查询会以内部查询的结果作为查询条件 进行数据的查询

问题：

（1）查询属于这家公司的员工的基本信息
     select * from employee where did in 
 (select did from department);
（2）查询一等奖获得者的学生信息信息  带比较的子查询完成
    select id,name from computer_stu where score 
	>=(select score from scholarship where level=1);


    select * from computer_stu where   
	score >all(select score from scholarship) ;


（3）查询没有24岁的员工的部门信息 带比较的子查询完成
（4）如果有1004这个部门则获取所有人的用户信息  带exists的子查询完成 
 exists
 select * from employee where  exists
 (select did from department where did  = 1009);


（5）查询将所有获奖的员工学生信息   带Any的子查询完成
	select * from computer_stu where   
	score >=any(select score from scholarship) ;
	
	select id,name from computer_stu where score 
	>=(select score from scholarship where level=3);

（6）查出computer_stu班成绩高于所有computer_stu2班的学生信息

合并查询：
（1）将department和employee中所有出现的部门编号展示出来

 select did from department 
union 
select did from employee ;

早期的mysql 不允许使用子查询 会产生中间表

四、事务

4.1事务的简介

4.1.1什么是事务，事物的的特性

一个事务是由一条或者多条对数据库操作的SQL语句所组成的一个不可分割的单元，只有当事务中的所有操作都正常执行完了，整个事务才会被提交给数据库；如果有部分事务处理失败，那么事务就要回退到最初的状态,或者回到事务操作的任何一个状态。因此，事务要么全部执行成功，要么全部失败。
rollback;当sql执行出错的时候可以通过rollback退回commit;只有当所有sql语句确认无误的时候，才提交给mysql才会对数据造成持久性的影向。

事务的特性(A C I D)

事务的原子性（Atomic）：

  事务是一个不可分割的整体，事务必须具有原子特性，及当数据修改时，要么全执行，要么全不执行，即不允许事务部分的完成。

事务的一致性（Consistency）：

 一个事务执行之前和执行之后，数据库数据必须保持一致性状态。数据库的一致性状态必须由用户来负责，由并发控制机制实现。就拿网上购物来说，你只有让商品出库，又让商品进入顾客的购物车才能构成一个完整的事务！由于并发操作带来的数据不一致性包括读脏数据（脏读），不可重复读和虚读（幻读）。

事务的隔离性（Isolation）：

 当两个或者多个事务并发执行时，为了保证数据的安全性，将一个事物内部的操作与其它事务的操作隔离起来，不被其它正在执行的事务所看到，例如对任何一对事务T1和T2，对T1而言，T2要么在T1开始之前已经结束，要么在T1完成之后再开始执行。隔离性使得每个事务的更新在它被提交之前，对其它事务都是不可见的。让我们看看事务处理之父Jim Gray对事务隔离性的定义[1]：Isolation: Concurrently executing transactions see the stored information as if they were running serially (one after another).
隔离性这里分为了几种级别，不同的隔离级别能够处理，再多个事务同时操作时所产生的问题。
4种  --》解决不同的问题

事务的持久性（Durability）：

事务完成以后，DBMS保证它对数据库中的数据的修改是永久性的，即使数据库因为故障出错，也应该能够恢复数据！

4.2 事务操作

由于Mysql在敲下sql语句按下回车之后会自动提交所以第一步开启事务实际上就是关闭自动提交机制。

1. set session transaction isolation level READ COMMITTED; 设置事务的隔离级别
如下4种分别对应，我们之前讲过的4种隔离级别
Read uncommitted Read committed  Repeatable read  Serializable 序列化
2.	查询事务的隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |    // MySQL默认工作在“可重复读”的隔离级别  重启恢复默认  
+-----------------+
1 row in set (0.00 sec) 
3.可通过select @@autocommit;  查看MySQL是否已经关闭自动提交。 
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |     # 0表示手动提交事务    1表示自动提交事务
+--------------+
1 row in set (0.01 sec)

 4.开启事务，关闭自动提交。

如果未关闭，可使用set autocommit = 0操作;设置事务提交方式为手动提交事务

5.begin 或 start transaction 显式地开启一个事务；（可写可不写）
6.commit;   当组成事务的所有sql语句都执行成功，调用commit提交一个事务
7.rollback;	如果在执行事务的过程当中有一个事务执行失败
回滚一个事务到初始的位置
8.savepoint  point1;  设置一个名字为point1的保存点
9.rollback to point1;  事务回滚到保存点point1，而不是回滚到初始状态

事务操作分为那几步：

（1）设置隔离级别。  将事务的级别设置为4种中的一种。
（2）开启事务
（3）begin 
（4）书写组成事务的sql语句
（5）如果事务执行过程中有bug  rollback/rollback      to;如果没有任何bug我们就commit;

4.3多个事务同时操作时可能产生的问题

脏读（Dirty Read）：

一个事务读取了另一个事务未提交的数据。例如当事务A和事务B并发执行时，
 当事务A更新后，事务B查询读取到A尚未提交的数据，此时事务A回滚，则  事务B读到的数据就是无效的脏数据。（事务B读取了事务A尚未提交的数据）
count : 40   B   ---> 60   rollback
A: select ---> 60    40  

只要没有commit  或者 rollback 事务都不算结束

不可重复读（NonRepeatable Read）：

    一个事务的操作导致另一个事务前后两次读取到不同的数据。例如当事务A和事务B并发执行时，当事务B查询读取数据后，
事务A更新操作更改事务B查询到的数据，此时事务B再次去读该数据，发现前后两次读的数据不一样。
（事务B读取了事务A已提交的数据）

虚读（PhantomRead）/幻读：

  一个事务的操作导致另一个事务前后两次查询的结果数据量不同。
 例如当事务A和事务B并发执行时，当事务B查询读取数据后，事务A新增或者删除了一条满足事务
B查询条件的记录，此时事务B再去查询，发现查询到前一次不存在的记录，或者前一次查询的一些记录不见了。（事务B读取了事务A新增加的数据或者读不到事务A删除的数据）

4.4 事务的隔离性

不同的隔离级别解决不同的问题：

 隔离级别 


隔离级别	脏读	不可重复读   幻读
未提交读	可以	可以	     可以
已提交读	不可以	可以	     可以
可重复读	不可以	不可以	     可以
串行化	    不可以	不可以	    不可以   效率最低

1. TRANSACTION_NONE。 表示不支持事务
2. TRANSACTION_READ_UNCOMMITTED。未提交读。
说明在提交前一个事务可以看到另一个事务的变化。这样读”脏”数据，不可重复读和虚读都是被允许的。
3. TRANSACTION_READ_COMMITTED。已提交读。
说明读取未提交的数据是不允许的。这个级别仍然允许不可重复读和虚读产生。
 4. TRANSACTION_REPEATABLE_READ。可重复读。
说明事务保证能够再次读取相同的数据而不会失败，但虚读仍然会出现。
 5. TRANSACTION_SERIALIZABLE。可序列化/串行化。
是最高的事务级别，它防止读脏数据，不可重复读和虚读。

4.5隔离级别与不同隔离级别解决的问题

准备工作：
create table ac_a(
	id int primary key,
	money int
);

insert into ac_a values(1,50);

create table ac_b(
	id int primary key,
	money int
);
insert into ac_b values(1,100);
insert into ac_b values(2,300);


(1)	设置mysql隔离级别为 未提交读：
A\B窗口
set session transaction isolation level Read uncommitted;
B窗口
 begin;
update ac_b set money = 50 where id = 1;
 update ac_a set money = 100 where id = 1;
A窗口
begin;
select * from ac_a;
B窗口
rollback;
A窗口
select * from ac_a;
rollback;


(2)设置隔离级别为已提交读
A\B窗口
set session transaction isolation level Read committed;
B窗口:
begin;
select * from ac_a;
A窗口
begin;
update ac_a set money = 60 where money = 50;
commit;
B窗口:
select * from ac_a;
rollback;
超市系统 ： 商品价格： 
定时程序： 超时之后恢复原价 12：00

 11  11   11:59:59  4个商品 
1: 0.5 * 10 = 
100


(3)设置隔离级别为可重复读
 Repeatable read
update ac_a set money =50 where id = 1;
  commit;
 A\B窗口
set session transaction isolation level Repeatable read;
B窗口:
begin;
select * from ac_a;
 A窗口
begin;
 insert ac_a values(2,100);

commit;
B窗口：
select * from ac_a;
insert ac_a values(2,100);
rollback;


 delete  from ac_a where id = 2;
 commit;
4.隔离级别设置为序列化  再操作上述
set session transaction isolation level Serializable;

4.6 三种引擎怎么选择

MyISAM：

  默认的MySQL插件式存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

需要锁  行锁  行锁 ：粒度  行锁的添加也更加消耗资源

InnoDB：

  用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB存储引擎应该是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（ Commit） 和回滚（ Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统， InnoDB 都是合适的选择。采用事务日志，系统崩溃后易于数据的恢复。

MEMORY：

  将所有数据保存在RAM中，而且支持Hash索引，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中 ，而且不支持BLOB,TEXT类型，而且使用的表级锁，并发性能低。现在很少使用，一般用于作为中间表保存中间数据。