mysql知识总结

mysql知识总结

1、什么是数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database

2、数据库分类

数据库通常分为“层次式数据库、网络式数据库和关系式数据库”三种。而不同的数据库是按不同的数据
结构来联系和组织的。而在当今的互联网中，最常见的数据库模型主要是两种，即关系型数据库和非关系
型数据库。
关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，
对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取
等运算来实现数据库的管理。
非关系型数据库提出另一种理念，例如，以键值对存储，且结构不固定，每一个元组可以有不一样的字
段，每个元组可以根据需要增加一些自己的键值对，这样就不会局限于固定的结构，可以减少一些时间
和空间的开销。使用这种方式，用户可以根据需要去添加自己需要的字段，这样，为了获取用户的不同
信息，不需要像关系型数据库中，要对多表进行关联查询。仅需要根据id取出相应的value就可以完成查
询。
关系型数据库通过外键关联来建立表与表之间的关系，非关系型数据库通常指数据以对象的形式存储在
数据库中，而对象之间的关系通过每个对象自身的属性来决定。

关系型数据库和非关系型数据库的种类和区别

现在主流的还是关系型数据库，非关系型数据库中Redis和MongoDB最受欢迎

MongoDB 更类似 MySQL，支持字段索引、游标操作，其优势在于查询功能比较强大，擅长查询 JSON 数
据，能存储海量数据，但是不支持事务。
MySQL 在大数据量时效率显著下降，MongoDB 更多时候作为关系数据库的一种替代。
Redis 是一个内存中的数据结构存储系统，它可以用作数据库、缓存和消息中间件。它支持多种类型的数据
结构，如 字符串（strings）， 散列（hashes），列表（lists）， 集合（sets）， 有序集合（sorted sets）
与范围查询等其它查询。 并通过 Redis哨兵（Sentinel）和自动 分区（Cluster）提供高可用性

Redis 数据全部存在内存，定期写入磁盘，当内存不够时，可以选择指定的 LRU 算法删除数据。
MongoDB 数据存在内存，由 linux系统 mmap 实现，当内存不够时，只将热点数据放入内存，其他数
据存在磁盘。

数据库系统主要有以下 3 个组成部分：

数据库：用于存储数据的地方。
数据库管理系统：用于管理数据库的软件。
数据库应用程序：为了提高数据库系统的处理能力所使用的管理数据库库的软件补充。
数据库管理系统（Database Management System，DBMS）是位于操作系统与用户之间
的一种操纵和管理数据库的软件，按照一定的数据模型科学地组织和存储数据，同时可以
提供数据高效地获取和维护。

数据库访问接口

不同的程序设计语言会有各自不同的数据库访问接口，程序语言通过这些接口，执行 SQL 语句，进行数
据库管理。主要的数据库访问接口有 JDBC、ADO.NET 和 PDO。
JDBC
Java Data Base（JDBC，Java 数据库连接）用于 Java 应用程序连接数据库的标准方法，是一种用于执行
SQL 语句的 Java API，可以为多种关系数据库提供统一访问，它由一组用 Java 语言编写的类和接口组成。
ADO.NET
ADO.NET 是微软在 .NET 框架下开发设计的一组用于和数据源进行交互的面向对象类库。ADO.NET 提供
了对关系数据、XML 和应用程序的访问，允许和不同类型的数据源以及数据库进行交互。
PDO
PDO（PHP Data Object）为 PHP 访问数据库定义了一个轻量级的、一致性的接口，它提供了一个数据访
问抽象层，这样，无论使用什么数据库，都可以通过一致的函数执行查询和获取数据。PDO 是 PHP 5 新加
入的一个重大功能。

3、RDBMS 术语

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

4、mysql数据库

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL 是开源的，目前隶属于 Oracle 旗下产品。
MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL 使用标准的 SQL 数据语言形式。
MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。
MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

5、mysql数据库操作

MySQL 创建数据库

CREATE DATABASE 数据库名；

查看数据库命令

SHOW DATABASES;

使用PHP来连接mysql的函数

mysqli_connect() 函数打开一个到 MySQL 服务器的新的连接。
mysqli_error() 函数返回最近调用函数的最后一个错误描述。
mysqli_close() 函数关闭先前打开的数据库连接
mysqli_query() 函数执行某个针对数据库的查询。
mysql_select_db()选择数据库。

删除已创建的数据库

drop database <数据库名>;

6、存储引擎

简单来说，存储引擎就是指表的类型以及表在计算机上的存储方式。数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
MySQL 支持多种类型的数据库引擎，可分别根据各个引擎的功能和特性为不同的数据库处理任务提供各自不同的适应性和灵活性。在 MySQL 中，可以利用 SHOW ENGINES（引擎） 语句来显示可用的数据库引擎和默认引擎。
MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

如何选择 MySQL 存储引擎

不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提
供了哪些不同的功能。

可以根据以下的原则来选择 MySQL 存储引擎： 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的
MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。
提示：使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

MySQL 存储引擎MyISAM 与InnoDB 区别

1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改
中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
3）InnoDB支持外键，MyISAM不支持
4）从MySQL5.5.5以后，InnoDB是默认引擎
5）InnoDB不支持FULLTEXT类型的索引
6）InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，
但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要
扫描整个表。
7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联
合索引。
8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。
9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’
有人说MYISAM只能用于小型应用，其实这只是一种偏见。
如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，读写分离，而不是单纯地依赖存储引擎。
现在一般都是选用InnoDB了，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低MyISAM对于读写密集型应用一般是不会去选用的。

总之：
1.MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。
2.MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

7、数据类型

数据类型（data_type）是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。
数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。
如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。
MySQL 的数据类型有大概可以分为 3 种，分别是数值类型、日期和时间类型、字符串类型等。

数值类型

在 int（integer） 系列中，只能存储整型值，且可以在后面用括号指定显示的尺寸（M），如果不指定则会默认分配。如果实际值的显示宽度大于设定值，将会显示实际值而不会截断以适应显示尺寸。
如 smallint(3) 中的 3 即为显示尺寸，即显示三位的数值（不包括 - 号）
int 类型可以指定 UNSIGNED 属性，即无符号（非负），所以存储范围有两种
在 float、double 及 decimal 类型中，不能指定 UNSIGNED 属性，其显示尺寸包含了小数点精度（D），即 float(3,1) 保存范围为 -99.9 至 99.9
decimal 必须指定显示尺寸（M）和小数点精度（D），float 和 double 都是可选的
在可能涵盖取值范围的基础上，尽可能选择较小的类型以提高效率和节约存储空间，如年龄，就选择 tinyint(3) 。该原则对于字符类型同样适用

字符串

char与varchar的区别
char (15)长度固定， 如’www.javazx.com’ 存储需要空间 14个字符
varchar(15) 可变长 如’www.javazx.com’ 需要存储空间 15字符,
char(10) 如果你存abc，需要空间为10，3个存abc，7个存空字符，因为char是固定长度
varchar(10)如果你存abc，需要空间为4，3个存abc，1个存长度，varchar是可变长度，超过10的部分，都会被截断
从上面可以看得出来char 长度是固定的，不管你存储的数据是多少他都会都固定的长度。而varchar则处可变长度但他要在总长度上加1字符，这个用来存储位置。所以实际应用中用户可以根据自己的数据类型来做。
由于某种原因char 固定长度，所以在处理速度上要比varchar快速很多，但是对费存储空间，所以对存储不大，但在速度上有要求的可以使用char类型，反之可以用varchar类型来实例。
个人建意:
myisam 存储引擎 建议使用固定长度，数据列代替可变长度的数据列。
memory存储引擎 目前都使用固定数据行存储，因此无论使用char varchar列都没关系，
innodb 存储引擎 建意使用varchar 类型
MySQL 5.0以上的版本：
1.一个汉字占多少长度与编码有关： UTF－8：一个汉字＝3个字节 GBK：一个汉字＝2个字节
2.varchar(n)表示n个字符，无论汉字和英文，Mysql都能存入n个字符，仅是实际字节长度有所区别
3.MySQL检查长度，可用SQL语言：
select length(字段名) from tablename 来查看
varchar类型的均按照字符数截断

时间日期类型

温馨提示：在 PHP 中，一般情况下对于时间都是按照 UNIX 时间戳以 int 类型存储于表中，再根据实际需要用 PHP 的时间函数进行处理，但不完全都是这样。

8、创建数据表

CREATE TABLE table_name (column_name column_type);
table_name 要创建的表名
column_name 列名，又叫字段名
column_type 字段类型，或者叫字段的数据类型
一般来说，创建数据表有如下注意事项：
原始记录数据与表的对应关系
表名和字段名应遵循命名语法且应该明确含义
指定字段的数据类型
指定字段的其他如是否非空、是否有默认值等属性
定义表的属性如主外键、约束、索引等
与其他表的关系

CREATE TABLE think_form (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
content varchar(255) NOT NULL,
create_time int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

实例解析：
如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

9、修改、删除数据表

修改表指的是修改数据库中已经存在的数据表的结构。MySQL 使用 ALTER TABLE 语句修改表。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。
ALTER TABLE <表名> [修改选项]

随着业务的变化，可能需要在已经存在的表中添加新的字段，一个完整的字段包括字段名、数据类型、完整性约束。添加字段的语法格式如下：
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]；
新字段名为需要添加的字段的名称；FIRST 为可选参数，其作用是将新添加的字段设置为表的第一个字段；AFTER 为可选参数，其作用是将新添加的字段添加到指定的已存在的字段名的后面。

删除字段

使用如下语法可以完成数据表字段的删除，在该字段删除后，和这个字段相关的索引和表约束也会被自动删除。
ALTER TABLE tb_name DROP column

修改字段名称

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

其中，旧字段名指修改前的字段名；新字段名指修改后的字段名；新数据类型指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。
使用 ALTER TABLE 修改表 think_form 的结构，将 title字段名称改为 name，同时将数据类型变为 CHAR(50)，输入的 SQL 语句和运行结果如右图所示。

10、主键、外键约束

约束是一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
约束分为常见的六大类
主键约束
外键约束
唯一约束
检查约束
非空约束
默认值
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添
加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号

主键（PRIMARY KEY ）

主键（PRIMARY KEY）”的完整称呼是“主键约束”，主键约束即在表中定义一个主键来唯一确定表中每
一行数据的标识符。主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键。主键
应该遵守下面的规则：
• 每个表只能定义一个主键。
• 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值。这是
唯一性原则。
• 一个列名只能在复合主键列表中出现一次。
• 复合主键不能包含不必要的多余列。当把复合主键的某一列删除后，如果剩下的列构成的主键仍然满足唯
一性原则，那么这个复合主键是不正确的。这是最小化原则。
在 CREATE TABLE 语句中，主键是通过 PRIMARY KEY 关键字来指定的。
语法规则： <字段名> <数据类型> PRIMARY KEY [默认值]
主键由多个字段联合组成，语法规则如下：
PRIMARY KEY [字段1，字段2，…,字段n]

外键约束（FOREIGN KEY ）

MySQL 外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可
以有一个或多个外键。
外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中
主键的某个值。
外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有
关联关系的行。
外键的主要作用是保持数据的一致性、完整性。例如，用户表 tb_user 的主键是 id，在地址表 tb_address 中
有一个键 userid与这个 id 关联。 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的
表就是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。
定义一个外键时，需要遵守下列规则：
• 外键要存在，首先必须保证表的引擎是 InnoDB（默认的存储引擎）。
• 父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一
个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为父表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键
中，这个外键的内容就是正确的。
• 在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
• 外键中列的数目必须和父表的主键中列的数目相同。
• 外键中列的数据类型必须和父表主键中对应列的数据类型相同。
• 如果外键约束模式选择SET NULL ，那么字段必须允许为NULL。

外键约束有三种模式，分别为：
district：严格模式（默认），父表不能删除或更新一个已经被子表数据引用的记录；
cascade：级联模式，父表的操作，对应子表关联的数据也跟着被删除；
set null：置空模式，父表的操作之后，子表对应的数据（外键字段）被置空。

设计外键的界面，有七列：
名（name）：可以不填，保存时会自动生成。
栏位（FieldName）：要设置的外键，如：student 里的c_id
参考数据库（Reference DataBase）：外键关联的数据库
被参考表（Reference Table）：关联的表，这里是class表
参考栏位（Forgin filed Names）：关联的字段，class里的id
删除时（ondelete）：删除时候的动作，当时我选择的是SETNULL
更新时（onupdate）：更新时候的动作，我选择的是CASCADE
以上字段也可以按照自己的需求填写，设置完成后保存即可。
某个表已经有记录了，添加外键失败，这时候只需要将两个要关联的表中的数据清空再从新添加外键关系
即可。

11、增删改查

查询数据

SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M]
查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
返回数据表 user的所有记录:
select * from user;
使用 PHP 函数的 mysqli_query() 及 SQL SELECT 命令来获取数据。该函数用于执行 SQL 命令，然后通过 PHP 函数 mysqli_fetch_array() 来使用或输出所有查询的数据。
mysqli_fetch_array() 函数从结果集中取得一行作为关联数组，或数字数组，或二者兼有 返回根据从结果集取得的行生成的数组，如果没有更多行则返回 false。

UPDATE 更新

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
可以同时更新一个或多个字段。
可以在 WHERE 子句中指定任何条件。
可以在一个单独表中同时更新数据。

DELETE 语句

DELETE FROM table_name [WHERE Clause]
如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

插入数据

INSERT INTO table_name ( field1, field2,…fieldN ) VALUES ( value1, value2,…valueN );
注意：如果数据是字符型，必须使用单引号或者双引号，如：“value”。
对于含有中文的数据插入，需要添加 mysqli_query($conn , “set names utf8”); 语句。

12、where语句

在使用 MySQL SELECT语句时，可以使用 WHERE 子句来指定查询条件，从 FROM 子句的中间结果中选取适当的数据行，达到数据过滤的效果。
WHERE <查询条件> {<判定运算1>，<判定运算2>，…}
支持在 WHERE 子句中指定任何条件。
可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。

WHERE 表达式中运算符说明：

去重（过滤重复数据）

例如：我们查询结果返回了 10 条记录，其中有一些重复的 age 值，有时出于对数据分析的要求，需要消除重复的记录值。这时候就需要用到 DISTINCT 关键字指示 MySQL 消除重复的记录值，语法格式为：
SELECT DISTINCT <字段名> FROM <表名>;

AS：设置别名

使用 MySQL 查询时，当表名很长或者执行一些特殊查询的时候，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用这个别名代替表原来的名称。
<表名> [AS] <别名>
其中各子句的含义如下：
• <表名>：数据中存储的数据表的名称。 ·
• <别名>：查询时指定的表的新名称。
• AS：关键字为可选参数。

LIMIT ：限制查询结果的记录条数

在使用 MySQL SELECT 语句时往往返回的是所有匹配的行，有些时候我们仅需要返回第一行或者前几行，这时候就需要用到 MySQL LIMT 子句。
[<位置偏移量>,] <行数>

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

第一个参数“位置偏移量”指示 MySQL 从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是 0，第二条记录的位置偏移量是 1，以此类推）；第二个参数“行数”指示返回的记录条数。

ORDER BY：对查询结果进行排序

SELECT 语句中，ORDER BY 子句主要用来将结果集中的数据按照一定的顺序进行排序。
ORDER BY {<列名> | <表达式> | <位置>} [ASC|DESC]
语法说明如下：

1. 列名
   指定用于排序的列。可以指定多个列，列名之间用逗号分隔。
2. 表达式
   指定用于排序的表达式。
3. 位置
   指定用于排序的列在 SELECT 语句结果集中的位置，通常是一个正整数。
4. ASC|DESC
   关键字 ASC 表示按升序分组，关键字 DESC 表示按降序分组，其中 ASC 为默认值。这两个关键字必须位于对应的列名、表达式、列的位置之后。

JOIN

（MySQL） JOIN 用于根据两个或多个表中的字段之间的关系，从这些表中得到数据。
JOIN 通常与 ON 关键字搭配使用
… FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
JOIN 按照功能大致分为如下三类：
• INNER JOIN（内连接）：取得两个表中存在连接匹配关系的记录。
• LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。
• RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）
并无匹配对应记录。

聚合函数

count：返回数组中数据的数量
sum ：返回指定数据的和
avg:计算指定数据的平均值
max :计算数据中的最大值
min :计算数据中的最小值

13、索引

为什么要有索引：

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1. 顺序访问
   顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。
2. 索引访问
   索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表 students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。根据存储方式的不同，MySQL中常用的索引在物理上分为以下两类。

1. B- 树索引
   B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：
   • 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
   • 分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
   • 根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行
。
B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：
• 查询必须从索引的最左边的列开始。
• 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
• 存储引擎不能使用索引中范围条件右边的列。

2. 哈希索引
   哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射）通过散列算法变换成固定长度的输出，该输出就是散列值。
   哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B- 树索引和 HASH 索引，且将 HASH 当成默认索引。
   HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点： MySQL 需要读取表中索引列的值来参与散列计算，
   散列计算是一个比较耗时的操作。也就是说，相对于 B- 树索引来说，建立哈希索引会耗费更多的时间。不能使用 HASH 索引排序。
   HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
   HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

1. 普通索引
   普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是 INDEX 或 KEY。
2. 唯一性索引
   唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一性索引。

创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。

3. 主键索引
   主键索引是一种唯一性索引，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 PRIMARY KEY。 注意：主键是数据库考察的重点。注意每个表只能有一个主键。
4. 空间索引
   空间索引主要用于地理空间数据类型 GEOMETRY。
5. 全文索引
   全文索引只能在 VARCHAR 或 TEXT 类型的列上创建，并且只能在 MyISAM 表中创建。

索引的使用原则和注意事项
索引只是提高效率的一个因素，因此在建立索引的时候应该遵循以下原则： 在经常需要搜索的列上建立索引，可以加快搜索的速度。
• 在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。
• 在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度。
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。
• 在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。
• 在经常使用 WHERE 子句的列上创建索引，加快条件的判断速度。
与此对应，在某些应用场合下建立索引不能提高 MySQL 的工作效率，甚至在一定程度上还带来负面效应，降低了数据库的工作效率，一般来说不适合创建索引的环境如下：
• 对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。
• 对于那些只有很少数据值的列也不应该创建索引。因为这些列的取值很少，例如人事表的性别列。查询结果集的数据行占了表中数据行的很大比例，增加索引并不能明显加快检索速度。
• 对于那些定义为 TEXT、IMAGE 和 BIT 数据类型的列不应该创建索引。因为这些列的数据量要么相当大，要么取值很少。
• 当修改性能远远大于检索性能时，不应该创建索引。因为修改性能和检索性能是互相矛盾的。当创建索引时，会提高检索性能，降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远
大于检索性能时，不应该创建索引。

创建索引（CREATE INDEX ）

用 使用 CREATE TABLE 语句
索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。语法格式：
CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的主键。
KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的索引。
UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。

14、视图

视图是一个虚拟表，其内容由查询定义。同真实表一样，但视图并不是数据库真实存储的数据表。视图是从一个、多个表或者视图中导出的表，包含一系列带有名称的数据列和若干条数据行。它们的区别在于以下几点：
• 视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。
• 存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。
• 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
• 视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。
• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。
• 视图的建立和删除只影响视图本身，不影响对应的基本表。

视图与表在本质上虽然不相同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。
视图具有如下优点：

1. 定制用户数据，聚焦特定的数据在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信
   息表的视图。
2. 简化数据操作
   在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。
3. 提高基表数据的安全性
   视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。
4. 共享所需数据
   通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。
5. 更改数据格式
   通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。
6. 重用 SQL 语句
   视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

注意：要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

使用视图的时候，还应该注意以下几点：
• 创建视图需要足够的访问权限。
• 创建视图的数目没有限制。
• 视图可以嵌套，即从其他视图中检索数据的查询来创建视图。
• 视图不能索引，也不能有关联的触发器、默认值或规则。
• 视图可以和表一起使用。
• 视图不包含数据，所以每次使用视图时，都必须执行查询中所需的任何一个检索操作。如果用多个连接和过滤条件创建了复杂的视图或嵌套了视图，可能会发现系统运行性能下降得十分严重。因此，在部署大量视图应用时，应该进行系统测试。

操作视图

创建视图（CREATE VIEW ）

创建视图是指在已经存在的 MySQL 数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。
CREATE VIEW <视图名> AS <SELECT语句>
语法说明如下：
• <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
• <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
• 用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
• SELECT 语句不能引用系统或用户变量。
• SELECT 语句不能包含 FROM 子句中的子查询。
• SELECT 语句不能引用预处理语句参数。

查询视图

视图一经定义之后，就可以如同查询数据表一样，使用 SELECT 语句查询视图中的数据，语法和查询基础表的数据一样。
视图用于查询主要应用在以下几个方面：
• 使用视图重新格式化检索出的数据。
• 使用视图简化复杂的表连接。
• 使用视图过滤数据。
DESCRIBE 可以用来查看视图，语法如下：
DESCRIBE 视图名；
注意：DESCRIBE 一般情况下可以简写成 DESC，输入这个命令的执行结果和输入 DESCRIBE 是一样的。

修改视图（ALTER VIEW ）

修改视图是指修改 MySQL 数据库中存在的视图，当基本表的某些字段发生变化时，可以通过修改视图来保持与基本表的一致性。
ALTER VIEW <视图名> AS <SELECT语句>
语法说明如下：
• <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
• <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
需要注意的是，对于 ALTER VIEW 语句的使用，需要用户具有针对视图的 CREATE VIEW 和 DROP 权限，以及由 SELECT 语句选择的每一列上的某些权限。修改视图的定义，除了可以通过 ALTER VIEW 外，也可以使用 DROP VIEW 语句先删除视图，再使用 CREATE VIEW 语句来实现。

视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据。视图中的行和基本表的行之间必须具有一对一的关系。
还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：
• 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
• DISTINCT 关键字。
• GROUP BY 子句。
• HAVING 子句。
• UNION 或 UNION ALL 运算符。
• 位于选择列表中的子查询。
• FROM 子句中的不可更新视图或包含多个表。
• WHERE 子句中的子查询，引用 FROM 子句中的表。
• ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

删除视图（DORP VIEW ）

删除视图是指删除 MySQL 数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。
可以使用 DROP VIEW 语句来删除视图。语法格式如下：
DROP VIEW <视图名1> [ , <视图名2> …]
其中：<视图名> 指定要删除的视图名。DROP VIEW 语句可以一次删除多个视图，但是必须在每个视图上拥有DROP 权限。

15、触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

MySQL 数据库中触发器是一个特殊的存储过程，不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，只要一个预定义的事件发生就会被MySQL自动调用。

引发触发器执行的事件一般如下：
• 增加一条学生记录时，会自动检查年龄是否符合范围要求。
• 每当删除一条学生信息时，自动删除其成绩表上的对应记录。
• 每当删除一条数据时，在数据库存档表中保留一个备份副本。

触发程序的优点如下：
• 触发程序的执行是自动的，当对触发程序相关表的数据做出相应的修改后立即执行。
• 触发程序可以通过数据库中相关的表层叠修改另外的表。
• 触发程序可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。

触发器与表关系密切，主要用于保护表中的数据。特别是当有多个表具有一定的相互联系的时候，触发器能够让不同的表保持数据的一致性。只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器。

在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。

1. INSERT 触发器
   在 INSERT 语句执行之前或之后响应的触发器。
   使用 INSERT 触发器需要注意以下几点：
   • 在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
   • 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
   • 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。

2. UPDATE 触发器
   在 UPDATE 语句执行之前或之后响应的触发器。
   使用 UPDATE 触发器需要注意以下几点：
   • 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
   • 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
   • 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
   • OLD 中的值全部是只读的，不能被更新。

注意：当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将
不被允许。

3. DELETE 触发器
   在 DELETE 语句执行之前或之后响应的触发器。
   使用 DELETE 触发器需要注意以下几点：
   • 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
   • OLD 中的值全部是只读的，不能被更新。
   总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误：
   若对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；
   对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。
   若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。
   若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。
   仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行AFTER触发程序。

触发器操作

创建触发器（CREATE TRIGGER ）

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 语句创建触发器。语法格式如下：
CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

1. 触发器名
   触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。
2. INSERT | UPDATE | DELETE
   触发事件，用于指定激活触发器的语句的种类。
   注意：三种触发器的执行时间如下。
   • INSERT：将新行插入表时激活触发器。例如，INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT语句激活，也能被 LOAD DATA 语句激活。
   • DELETE： 从表中删除某一行数据时激活触发器，例如 DELETE 和 REPLACE 语句。
   • UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句。
3. BEFORE | AFTER
   BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件，则使用 BEFORE 选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。
4. 表名
   与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。
5. 触发器主体
   触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END复合语句结构。
6. FOR EACH ROW
   一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

注意：每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER，因此每个表最多支持 6 个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。

总结触发器四个要素：

1. 监视地点（table）
2. 监视事件（insert/update/delete）
3. 触发时间（after/before）
4. 触发事件（insert/update/delete）

修改和删除触发器（DROP TRIGGER ）

修改触发器可以通过删除原触发器，再以相同的名称创建新的触发器。
DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>
语法说明如下：

1. 触发器名
   要删除的触发器名称。
2. 数据库名
   可选项。指定触发器所在的数据库的名称。若没有指定，
   则为当前默认的数据库。
3. 权限
   执行 DROP TRIGGER 语句需要 SUPER 权限。
4. IF EXISTS
   可选项。避免在没有触发器的情况下删除触发器。

16、事务

事务（Transaction） 是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都 执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每 个事务结束时，都能保持数据一致性。

同时，事务有着严格的地定义，必须满足四个特性，也就是我们一直说的ACID，但是，并不是说各种数据库就一定会满足四个特性，对于不同的数据库的实现来说，在不同程度上是不一定完全满足要求的，比如，Oracle数据库来说，默认的事务隔离级别是READ COMMITTED，是不满足隔离性的要求的。

事务的四大特性简称为：ACID，分别是原子性、一致性、隔离性和持久性。

事务分类

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SETAUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READUNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

事务处理方法

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

17、锁

‘我们在操作数据库的时候，可能会由于并发问题而引起的数据的不一致性（数据冲突），如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素，从这一角度来说，锁对于数据库而言就显得尤为重要。’

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。
比如：
• MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
• InnoDB存储引擎既支持行级锁（ row-level locking），也支持表级锁，但默认情况下是采用行级锁。

• 表级锁：开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定力度大，发生锁冲突的概率最高,并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定力度最小，发生锁冲突的概率最低,并发度也最高。
• 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定力度介于表锁和行锁之间，并发度一般。

行锁 和 表锁

1. 主要是针对 锁力度 划分的，一般分为：行锁、表锁、库锁
   （1）行锁：访问数据库的时候，锁定整个行数据，防止并发错误。
   （2）表锁：访问数据库的时候，锁定整个表数据，防止并发错误。
2. 行锁 和 表锁 的区别：
   • 表锁：开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
   • 行锁：开销大，加锁慢，会出现死锁；锁定力度小，发生锁冲突的概率低，并发度高

悲观锁 和 乐观锁

（ （1 ）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block（限制）直到它拿到锁。
传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
（ （2 ）乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
（ （3 ）悲观锁 和 乐观锁的区别：
两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

共享锁
共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。相当于对于同一把门，它拥有多个钥匙一样。就像这样，你家有一个大门，大门的钥匙有好几把，你有一把，你女朋友有一把，你们都可能通过这把钥匙进入你们家，这个就是所谓的共享锁。

刚刚说了，对于悲观锁，一般数据库已经实现了，共享锁也属于悲观锁的一种，那么共享锁在mysql中是通过什么命令来调用呢。在执行语句后面加上lock in share mode就代表对某些资源加上共享锁了。

表级锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。
• 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
• 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
• 当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。

表锁和行锁应用场景

• 表级锁使用与并发性不高，以查询为主，少量更新的应用，比如小型的web应用；
• 而行级锁适用于高并发环境下，对事务完整性要求较高的系统，如在线事务处理系统。

死锁

死锁是指多个进程因竞争资源而造成的一种僵局（互相等待），若无外力作用，这些进程都将无法向前推进。例如，在某一个计算机系统中只有一台打印机和一台输入 设备，进程P1正占用输入设备，同时又提出使用打印机的请求，但此时打印机正被进程P2 所占用，而P2在未释放打印机之前，又提出请求使用正被P1占用着的输入设备。这样两个进程相互无休止地等待下去，均无法继续执行，此时两个进程陷入死锁状态。

死锁的四个

• 互斥条件：一个资源每次只能被一个进程使用，即在一段时间内某 资源仅为一个进程所占有。此时若有其他进程请求该资源，则请求进程只能等待。
• 请求与保持条件：进程已经保持了至少一个资源，但又提出了新的资源请求，而该资源 已被其他进程占有，此时请求进程被阻塞，但对自己已获得的资源保持不放。
• 不可剥夺条件:进程所获得的资源在未使用完毕之前，不能被其他进程强行夺走，即只能 由获得该资源的进程自己来释放（只能是主动释放)。
• 循环等待条件: 若干进程间形成首尾相接循环等待资源的关系这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

死锁的避免与预防

1. 死锁避免
   死锁避免的基本思想：系统对进程发出的每一个系统能够满足的资源申请进行动态检查，并根据检查结果决定是否分配资源，如果分配后系统可能发生死锁，则不予分配，否则予以分配，这是一种保证系统不进入死锁状态的动态策略。
   如果操作系统能保证所有进程在有限时间内得到需要的全部资源，则系统处于安全状态否则系统是不安全的。

2. 死锁 预防
   我们可以通过破坏死锁产生的4个必要条件来 预防死锁，由于资源互斥是资源使用的固有特性是无法改变
   的。
   • 破坏“不可剥夺”条件：一个进程不能获得所需要的全部资源时便处于等待状态，等待期间他占有的资源将被隐式的释放重新加入到 系统的资源列表中，可以被其他的进程使用，而等待的进程只有重新获得自己原有的资源以及新申请的资源才可以重新启动，执行。
   • 破坏”请求与保持条件“：第一种方法静态分配即每个进程在开始执行时就申请他所需要的全部资源。第二种是动态分配即每个进程在申请所需要的资源时他本身不占用系统资源。
   • 破坏“循环等待”条件：采用资源有序分配其基本思想是将系统中的所有资源顺序编号，将紧缺的，稀少的采用较大的编号，在申请资源时必须按照编号的顺序进行，一个进程只有获得较小编号的进程才能申请较大编号的进程。