MySQL笔记

1.MySQL存储引擎

1.1存储引擎介绍

ＩnnoDB:是ＭySQL的默认存储引擎，

• 其表在执行提交和回滚操作时是事务安全的，可以通过创建保存点（savepoint)来实现部分回滚．
• 在系统崩溃后可以直接恢复；
• 外键和引用完整性支持，包括级联删除和更新；
• 基于行级别的锁定和多版本化，使得在执行同时包含有检索和更新操作的组合条件查询时，可以表现出很好的并发性能；
• 从5.6开始支持全文搜索和ＦＵＬＬＴＥＸＴ索引；

MyISAM:
- 当保存连续相似的字符串索引值时，它会对键进行压缩，此外，ＭｙＩＳＡＭ还可以压缩相似的数字索引值，因为这些数值都是按高字节优先的方式来保存的，启动数字压缩功能将PACK_KEYS=1;
- 与其他存储引擎相比，它为AUTO_INCREMENT列提供了更多的功能；
- 每个ＭｙＩＳＡＭ表都有一个标志，它会在执行表检查操作时被设置，ＭｙＩＳＡＭ表还有一个标志，用于表明该表在上次使用后是否被正常关闭，如果服务器意外宕机或机器崩溃，那么可用这个标志来判断表是否需要检查和修复，如果象进行自动检查，则需要在启动服务器时，将mysiam_recover_options系统变量设置为一个包含有ＦＯＲＣＥ选项的值，
- 支持全文检索和ＦＵＬＬＴＥＸＴ索引；
- 支持空间数据类型和ＳＰＡＴＩＡＬ索引；

MEMORY存储引擎
　　ＭＥＭＯＲＹ存储引擎会把表存储在内存中，并且这些表的行长度固定不变；
在某种意义上来讲，ＭＥＭＯＲＹ表是临时性的，当服务器掉电时，其内容也会消失，也就是说，ＭＥＭＥＯＲＹ表在服务器重启之后依然存在，只是他们的内容为空，不过相对于临时表来说，ＭＥＭＯＲＹ表对其他客户端来说是可见的；
　　ＭＥＭＯＲＹ表的以下几个特点使他们比其他类型的表更易处理，因此速度也更快；
　
- 默认情况下，ＭＥＭＯＲＹ表使用的是散列索引，这种索引对＂相等比较＂非常快，对＂范围比较＂非常慢，因此，散列索引只适合于相等运算符＂＝＂和＂＜＞＂的操作；不适合于＜及＞操作及ＯＲＤＥＲ　ＢＹ子句里；
- 为更便于处理，存储在ＭＥＭＯＲＹ表里的行使用长度固定不变的格式．这意味着不能使用ＢＬＯＢ和ＴＥＸＴ，ＶＡＲＣＨＡＲ是一种长度可变的类型，但由于在ＭｙＳＱＬ内部，它被当作是一种长度固定不变的ＣＨＡＲ类型，所以可以在ＭＥＭＯＲＹ表里使用．

ＮＤＢ存储引擎
　　ＮＤＢ存储引擎是ＭｙＳＱＬ的集群存储引擎，对于这个存储引擎，ＭｙＳＱＬ服务器实际上变成了一个其他进程的集群客户端，集群节点会处理彼此间的通信，从而在内存中实现对表的管理，为了实现冗余，这些表会在集群进程之间被复制，内存存储提供了高性能，而集群机制则提供了高可用性，因为即使某个节点发生了故障，整个系统页不会崩溃．

ＡＲＣＨＩＶＥ存储引擎
　　该引擎提供了数据归档存储功能，主要适合于大批量存储那些＂写了就不会再更改＂的行，因此，它所支持的ＳＱＬ语句很有限，ＩＮＳＥＲＴ和ＳＥＬＥＣＴ可以而ＤＥＬＥＴＥ和ＵＰＤＡＴＥ不可以使用，为了节省空间，在存储时会对行进行压缩，而在检索时再对他们进行解压，ＡＵＣＨＩＶＥ表可以包含一个带索引的ＡＵＴＯ＿ＩＮＣＲＥＭＥＮＴ列，但其他列不能被索引；

ＢＬＡＣＫＨＯＬＥ存储引擎
　　该引擎所创建的表，其写操作会被忽略，读操作是返回空内容．

ＣＳＶ存储引擎
　　该引擎在存储数据时，会用逗号进行分割，对于每个表，它会在数据库目录里创建一个.CSV文件，这是一种普通文本文件，其中每个表行占用一个文本行，ＣＳＶ存储引擎不支持索引．
ＦＥＤＲＥＡＴＥＤ存储引擎
　　该引擎提供了访问由其他ＭｙＳＱＬ服务器进行管理的表的能力，也就是说，ＦＥＤＥＲＡＴＥＤ表的内容实际上位于远程，当创建ＦＥＤＲＥＡＴＥＤ表时，需要指定一台运行着其他服务器的主机，并提供该服务器上的某个账户的用户名和密码，需要访问ＦＥＤＲＥＡＴＥＤ表时，本地服务器将使用这个账户连接那台远程服务器．
ＭＥＲＧＥ存储引擎
　　该引擎提供了一种把多个ＭｙＩＳＡＭ表合并为一个逻辑单元的手段，查询一个ＭＥＲＧＥ表时，相当于查询其所有的成员表，这种做法的好处之一是，可以突破文件系统对单个ＭｙＩＳＡＭ表的最大尺寸所设定的限制，分区表可以替换ＭＥＲＧＥ表，并且不会受限于ＭｙＩＳＡＭ表．

1.1存储引擎相关属性：

• 并发控制

  当多个连接对记录进行修改时保证数据的一致性和完整性;

• 锁类型

  –>共享锁：在同一时间内，多个用户可以读取同一个资源，数据不发生变化;

  –>排他锁：任何时候只允许一个用户写入数据，阻塞其他的读取或者写锁：

• 锁颗粒

  –>表锁：开销最小的锁策略

  –>行锁：开销最大的锁策略

• 事务

  用于保证数据库的完整性，事务特性包括原子性，一致性，隔离性，持久性（ACID）

• 外键

  用户保证数据一致性的策略;

• 索引

  是对数据表中一列或者多列的值进行排序的一种结构，（普通索引，唯一索引，全文索引，btree索引，hash索引）

1.2 存储引擎的区别

	MyISAM	InnoDB	Memory	Archive
存储限制	256TB	64TB	有	无
事务安全	-	支持	-	-
支持索引	支持	支持	支持	-
锁颗粒	表锁	行锁	表锁	行锁
数据压缩	支持	-	-	支持
支持外键	-	支持	支持	-

MyISAM:适用于事务处理不多的情况;
InnoDB:适用于事务处理较多，需要有外键支持的情况;

###1.3 存储引擎的配置

• 通过修改MySQL配置文件

  Default-storage-engine = engine;

• 通过创建表时命令来实现

  CREATE TABLE table_name (……) ENGINE = engine;

• 通过修改数据表命令

  ALTER TABLE table_name ENGINE [=] engine_name;

• 查看存储引擎

  SHOW ENGINES;

2 MySQL数据类型

2.1 整型

数据类型	存储范围	字节
TINYINT	有符号：-2^7～2^8-1，无符号：0～2^8-1	1
SMALLINT	有符号：-2^15~2^15-1,无符号：0～2^16-1	2
MEDIUMINT	有符号：-2^23~2^23-1,无符号：0～2^24-1	3
INT	有符号：-2^31~2^31-1,无符号：0～2^32-1	4
BIGINT	有符号：-2^63~2^63-1,无符号：0～2^64-1	8

2.2 浮点型

数据类型	存储范围	有效位	字节
FLOAT[(M,D)]	1.175494351 E-38～3.402823466 E+38	6～7	4
DOUBLE[(M,D)]	2.2250738585072014 E-308 ~1.7976931348623158 E+308	15~16	8

2.3 日期时间型

数据类型	范围	字节
YEAR	1901～2155	1
TIME	-838：59：59～838：59：59	3
DATE	1000-01-01～9999-12-31	4
DATETIME	1000-01-01 00：00：00～9999-12-31 23：59：59	8
TIMESTAMP	1970-01-01 00：00：00～2037 00:00:00	4

2.4 字符型

数据类型	存储范围
CHAR（M）	M个字节，0<=M<=255
VARCHAR(M)	L+1个字节，其中L<=M,0<=M<=2^16
TINYTEXT	L+1个字节，L<=2^8
TEXT	L+2个字节，L<=2^16
MEDIUMTEXT	L+3个字节，L<=2^24
LONGTEXT	L+4个字节，L<=2^32

3 数据库操作

3.1 创建数据库

• CREATE DATABASE database_name;#创建数据库
• GRANT ALL ON test.* to user(s); #为指定用户（或所有用户）提升权限

3.2 使用数据库

• USE database_name;

3.3 删除数据库

• DROP DATABASE test;

3.4 创建表

• CREATE TABLE [IF NOT EXISTS] table_name (column name,datatype);

3.5 查看数据表列表

• SHOW TABLES [FROM db_name];

3.6 查看数据表结构

• SHOW COLUMNS FROM tbl_name;

4. 数据表的基本操作

4.1 插入记录

• INSERT [INTO] tbl_name [(col_name,…)] VALUES (val,…);

4.2 记录查找

• SELECT expr,… FROM tbl_name;

5.约束的基本介绍

5.1 非空约束（NOT NULL）

5.2 主键约束（PRIMARY KEY）

每张数据表只能存在一个主键

5.3 唯一约束（UNIQUE KEY）

保证数据的唯一性
一张表可以有多个唯一约束
允许为空

5.4 默认约束（DEFAULT）

当输入数据时没有明确赋值，自动赋予默认值

#举例
CREATE TABLE t2( 
    id INT, 
    name VARCHAR(20), 
    sex ENUM('1','2','3') DEFAULT'3');

5.5 外键约束（FOREIGN KEY）

• 保持数据一致性，完整性
• 实现一对一或一对多关系
• 外键约束要求
  父表与子表使用InnoDB存储引擎， 且不能使用临时表
  外键列和参照列具有相似的数据结构
  外键列和参照列必须创建索引

5.6 外键约束的基本操作

• CASCADE
  自动删除或者更新子表中匹配的行

• SET NULL
  从父表删除或更新行，并设置子表中的外键列为NULL

• RESTRICT
  拒绝对父表的删除或更新操作

• NO ACTION
  标准SQL的关键字，与RESTRICT相同

//举例
CREATE TABLE t1(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20));
CREATE TABLE t2(
    id INT AUTO_INCREMENT PRIMARY KEY,
    type INT)
    FOREIGN KEY (type) REFERENCES t1(id ON DELETE CASCADE));```

##5.7 添加/删除列
- **添加单列**

ALTER TABLE tbl_name ADD[COLUMN] col_name column_defination [FIRST|AFTER column_name];“`

• 添加多列

ALTER TABLE tbl_name ADD[COLUMN](colunmn_name, column_defination,...);```

- **删除列**

ALTER TABLE tbl_name DROP[COLUMN] column_name,DROP[COLUMN] column_name;“`

5.8 添加/删除约束

5.8.1 添加/删除主键约束

• 添加主键约束

ALTER TABLE tbl_name ADD[CONSTRAINT[symbol]] PRIMARY KEY[index_type](index_col_name,...);

• 删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY;

5.8.2 添加/删除唯一约束

• 添加唯一约束

ALTER TABLE tbl_name ADD[CONSTRAINT[symbol]] UNIQUE [INDEX|KEY][index_name] [index_type](index_col_name,...);

• 删除唯一约束

ALTER TABLE tbl_name DROP{INDEX|KEY} index_name;

5.8.3 添加/删除外键约束

• 添加外键约束

ALTER TABLE tbl_name ADD[CONSTRAINT [symbol]] FOREIGN KEY [index_type](index_col_name,...) reference_definition ;

• 删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

5.8.4 添加/删除默认约束

• 添加默认约束

ALTER TABLE tbl_name ALTER[COLUMN] column_name SET DEFAULT literal;

• 删除默认约束

ALTER TABLE tbl_name ALTER[COLUMN] col_name DROP DEFAULT;

5.9 列定义和更名数据表

5.9.1 修改列定义/列名称

• 修改列定义

ALTER TABLE tbl_name MODIFY[COLUMN] col_name column_definition [FIRST|AFTER col_name];```
- **修改列名称**

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];“`

5.9.2 修改数据表

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;```

RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]…;“`

6.操作数据表和基本查询

6.1 插入记录——INSERT

INSERT [INTO] tbl_name [(col_name,...)]`{VALUES|VALUE} ({expr|DEFAULT},...),(...);```

INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},…“`

6.2 更新和删除记录

6.2.1 更新表记录

UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name={expr1|DEFAULT},[col_name2={expr2|DEFAULT}]...[WHERE where_condition];```

##6.2.2删除表记录

DELETE FROM tbl_name [WHERE where_condition];“`

6.3 查询表达式

SELECT select_expr [select_epxr...] FROM tbl_name
[WHERE where_condition]
//查询结果分组
[GROUP BY {col_name|position}[ASC|DESC],...]
//分组条件
[HAVING where_condition]
//查询结果排序
[ORDER BY {col_name|expr|position}[ASC|DESC],...]
//查询结果限制数量
//注意:limit如果限制某一范围，其顺序号是从0开始，然后再加上增幅;
[LIMIT {[offset],row_count|row_count OFFSET offset}];```

## 6.4 子查询
子查询关键字
    |---|ANY|SOME|ALL|
    |----|----|----|----|
    |>,>=|最小值|最小值|最大值|
    |<,<=|最大值|最大值|最小值|
    |<>,!=|----|----|任意值|

另外，IN等同于ANY, NOT IN 等同于ALL

## 6.5 插入查询结果
- **将查询结果写入数据表**

```INSERT [INTO] tbl_name [(col_name，...)] SELECT ... ```

如将t1表中的查询结果插入至t2表中
```INSERT t2(name) SELECT cate FROM t1 GROUP BY cate;```





<div class="se-preview-section-delimiter"></div>

## 6.6 多表更新
``` UPDATE tbl_name1{[INNER|CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN } tbl_name2 ON conditional_expr SET col_name1={expr1|DEFAULT}[col_name2={expr2|DEFAULT} [WHERE where_conditon];```

达到创建的目的，我们总共走了3步，第1步是创建表格，第2步是插入数据，第3步是创建外键，我们可以将这3步简化为2步，即第1步创建并插入数据，第2步创建外键；




<div class="se-preview-section-delimiter"></div>

```//创建表格及插入数据
CREATE TABLE [IF NOT EXIST] tbl_name [(create_defination,...)] Select_statement```
举例如下：

// 将t1表的type列分组并放入新建的t3表type列
CREATE TABLE t3(
id INT AUTO_INCREMENT PRIMARY KEY,
type VARCHAR(40)
SELECT type FROM t1 GROUP BY type;“`

6.7 内外连接

• 内连接：
  显示A表和B表符合连接条件的记录

• 左外连接：
  显示A表的全部记录和B表符合连接条件的记录

• 右外连接：
  显示A表符合连接条件的记录及B表的全部记录

6.8 多表连接

• 自身连接：同一个数据表对其自身进行连接

//t5表中包含三列，分别是id,name,parent_id,其中parent_id代表其坐在id行的name指向的父类id,我们可以通过此方式将
SELECT A.id,A.name,B.id,B.name FROM t5 AS A LEFT JOIN t5 AS B ON A.parent_id =B.id;```

#7 实现函数
## 7.1 字符串函数

| 函数名称|说明|
|----|----|
|CONCAT()|字符连接|
|CONCAT_WS()|使用分隔符连接字符串|
|UPPER()|转化成大写|
|LOWER()|转换成小写|
|FORMAT()|格式化|
|LEFT()|读取左侧字符|
|RIGHT()|读取右侧字符|

```//举例
// CONCAT()
SELECT CONCAT（'str_1','str_2',...);
// CONCAT_WS()
SELECT CONCAT('delimiter','str_1','str_2',...);
// UPPER()
SELECT UPPER('str_1');
// FORMAT()
SELECT FORMAT(1234,3);#结果为1234.000
// LEFT()
SELECT LEFT('str_1',4); #读取str_1左侧的4个字符```

|函数名称|说明|
|----|----|
|LTRIM()|删除前续空格|
|RTRIM()|删除后续空格|
|TRIM()|删除前后空格|
|[NOT] LIKE|模式匹配|
|REPLACE|字符串替换|
|LENGTH()|字符串长度|
|SUBSTRING()|字符串截取|





<div class="se-preview-section-delimiter"></div>

```//举例
//删除特定字符
SELECT TRIM(LEADING 'symbol' FROM 'str');#删除str前symbol符号
SELECT TRIM(TRAILING 'symbol' FROM 'str');#删除str后symbol符号
SELECT TRIM(BOTH 'symbol' FROM 'str');#删除str前后symbol符号
// 替换指定字符
SELECT REPLACE('str','need_replace','want_replace');
//字符串截取
SELECT SUBSTRING('str_1','from_cut','cut_num');
// 模式匹配
SELECT * FROM t1 WHERE name LIKE '%肉' ;```

## 7.2 运算符和函数

|函数名称|说明|备注|
|----|----|----|
|CEIL()|进一取整|CEIL(5.01)结果为6|
|FLOOR()|舍一取整|FLOOR(5.01)结果为5|
|DIV|整数除法|如果不能整除默认采取舍一取整方式|
|MOD|取余|-|
|POWER()|幂运算|-|
|ROUND()|四舍五入|-|
|TRUNCATE()|数字截取|
|[NOT] BETWEEN...AND...|[不]在特定范围|-|
|[NOT] IN()|[不]在特定列出值中|-|
|IS [NOT] NULL|[不]为空值|-|

## 7.3 其他常用函数

|函数名称|说明|备注|
|----|----|----|
|NOW()|当前日期和时间|-|
|CURDATE()|当前日期|-|
|CURTIME()|当前时间|-|
|DATE_ADD()|日期变化|`SELECT DATE_ADD('2017-1-1',INTERVAL 365 DAY);`
|DATEDIFF()|日期差值|
|DATE_FORMAT()|日期格式化|

## 7.4 自定义函数
- **创建自定义函数**
```CREATE FUNCTION func_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_body```
    - routine_body函数体
        - 函数体由合法的SQL语句组成；
        - 可以是简单的SELECT 或INSERT 语句；
        - 复合结构则使用BEGIN...AND语句；
        - 复合结构可以包含声明，循环和控制结构；
- **删除函数**




<div class="se-preview-section-delimiter"></div>

DROP FUNCTION [IF EXISTS] func_name;

<div class="se-preview-section-delimiter"></div>

# 8 MySQL存储过程




<div class="se-preview-section-delimiter"></div>

## 8.1 存储过程介绍
一组为了完成特定功能的SQL语句和控制语句的集合，存储过程经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字和参数来执行它，存储过程是数据库中的一个重要对象。

```CREATE PROCEDURE sp_name ([proc_parameter[...]]) [characteristic...] routine_body```

- 参数
    - IN:参数必须在调用存储过程时指定；
    - OUT:参数可以被存储过程改变并且可以返回；
    - INOUT:可以调用时指定，也可以改变和返回；
- routine_body过程体
    - 过程体由合法的SQL语句组成；
    - 可以是最简单的SELECT或INSERT语句；
    - 复合结构则使用BEGIN...AND语句；
    - 复合结构可以包含声明、循环和控制结构；
- 存储过程的优点
    - 增强SQL语句的功能性和灵活性；
    - 加快SQL语句的执行速度；
    - 减少数据传送的流量；




<div class="se-preview-section-delimiter"></div>

## 8.2 创建IN类型参数的存储过程
```CREATE [DEFINER={user|CURRENT_USER} PROCEDURE sp_name ([proc_parameter[...]] [characteristic...]) routine_body```

- 修改存储过程
``` ALTER PROCEDURE sp_name [characteristic...] comment 'string'|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY{DEFINER|INVOKER}```
- 删除存储过程
```DROP PROCEDURE [IF EXISTS] sp_name```





<div class="se-preview-section-delimiter"></div>

## 8.3 创建OUT类型参数的存储过程 
- 存储过程与自定义函数的区别
    - 存储过程实现的过程要复杂一些，而函数的针对性较强；
    - 存储过程可以有多个返回值，而自定义函数只有一个返回值；
    - 存储过程一般独立的来执行，而函数往往作为其他SQL语句的一部分来使用；
暂时性表的创建

　　临时表会在客户端会话结束后自动删除，但最好还是在用完之后显性的删除以节省内存；
　　临时表只对创建该表的客户端可见；
　　临时表的名字可以与某个已有的永久表的名字相同，此时永久表会隐藏起来，如果删除的话也会先删除临时表，而永久表会显现出来；
　　使用临时表需注意：
　　 1. 如果客户端程序在与服务器的连接意外断开时自动连接，那么在重新连接之后，断开前的临时表将不复存在，如果之前用临时表来＂隐藏＂某个与之同名的永久表，那么这个永久表将成为现在使用的表；
　　 ２. 由于临时表只在创建它们的会话里是可见的，因此他们与连接池机制一起使用时没有什么用处；
　　 ３．如果使用连接池或永久性连接，那么当应用程序终止时，临时表与ＭｙＳＱＬ服务器之间的连接不一定被关闭，那些机制可能会保持处于连接状态，以供其他客户端使用．这表示当应用程序终止后不能假设临时表会自动消失．





<div class="se-preview-section-delimiter"></div>

## 2.6.2.4根据其他表或查询结果来创建表
MySQL有两种方法来创建表副本

- ```CREATE TABLE new_tbl_name LIKE tbl_name```会根据原有表创建一个新表，该表是原有表的一个空副本，它会把原有表的结构丝毫不差的复制过来，而且会保留各列的所有属性，索引结构也照样会被复制，不过，由于这个表是空的，因此还需要一条数据填充语句（INSERT INTO ..SELECT），请注意，该语句不能根据原有表的列子集创建出新表，也不能使用除原有表以外的任何其他表里的列．




<div class="se-preview-section-delimiter"></div>

```CREATE TABLE new_tbl_name LIKE tbl_name;
INSERT INTO new_tbl_name SELECT * FROM tbl_name;```

- ```CREATE TABLE...SELECT...```，默认情况下，这条语句不会复制所有的列属性，如AUTO_INCREMENT，也不会把原有表里任何索引复制过去，因为结果集不会自己索引．不过该语句可完成创建表并填充数据这两个操作．
- ```CREATE TABLE new_tbl_name SELECT * FROM tbl_name;```
由于该条语句的不足在于不会把原有数据的所有特征全部复制到新表的结构中去，因此在某些场合可以采用在语句的SELECT部分使用CAST()函数的方式，在新表里强制使用某些特定的属性，如：
```CREATE  TABLE mytbl SELECT 
CAST(1 AS UNSIGEND) AS i,
CAST(CURTIME() AS TIME) AS t,
CAST(PI() AS DECIMAL(10,5)) AS d;```

    允许强制转换的类型包括：BINARY，CHAR，DATE，DATETIME，TIME，SIGNED，SIGNEDINTEGER，UNSIGNED INTEGER和DECIMAL．





<div class="se-preview-section-delimiter"></div>

## 2.6.2.5 使用分区表
 分区表的两个好处为：

 - 表存储可以分布在多个设备上，可以通过Ｉ／Ｏ并行机制来缩短访问时间．
 - 优化器可以把本地化搜索限定在某个特定的分区，或者并行搜索多个分区

 分区函数的分区依据：

 - 根据范围来分区，适用这种方式的情形是，行包含的是一些可划分为多个离散区间的值域，如日期，收入水平或重量．
 - 根据列表来分区，适用这种方式的情形是，每个分区都分别对应于某些明确的列表值，如邮政编码，电话号码区号，按地理区域划分出的各实体的编号．
 - 根据散列值来分区，适用这种方式的情形是，根据从行键计算出的散列值，把行分布到各个分区．
 - **分区函数必须具有确定性，这样，在分配行时，才会把相同的输入值分配到同一个分区，有些函数不适合这个规则，如ＲＡＮＤ（）和ＮＯＷ（）**






<div class="se-preview-section-delimiter"></div>

#2.6.4索引
MySQL提供了多种灵活的索引创建方法

 - 可以对单个列或多个列建立索引，多列索引也被称作复合索引；
 - 索引可以只包含唯一值，也可以包含重复值；
 - 可以为同一个表创建多个索引，帮助优化对表的不同类型的查询．
 - 对于除ENUM和SET以外的字符串类型，可以利用列的前缀创建索引，这种方法的优点在于对列的前缀而不是整个列进行索引，可以让索引变的更小，访问速度更快．
 MySQL中支持索引的引擎包括InnoDB,MyISAM,MEMORY.
 MySQL可以创建多种类型的索引：
    - 唯一索引
    - 常规索引（非唯一性）
    - FULLTEXT索引，这种索引适用于ＭyISAM和5.6.4版本以上的InnoDB
    - SPATIAL索引，这种索引只适用于包含空间值的MyISAM表．
    - HASH索引，这是MEMORY表的默认索引类型，不过可以通过创建BTREE索引来改写它．

添加索引的方式：





<div class="se-preview-section-delimiter"></div>

ALTER TABLE tbl_name ADD INDEX index_name(index_columns);
CREATE INDEX index_name ON tbl_name(index_columns);“`

删除索引的方法


DROP INDEX index_name ON tbl_name;
DROP INDEX `PRIMARY` ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name;

2.6.5 更改表结构

1. 更改列的数据类型

   如果在mytbl表里,列i的数据类型为SMALLINT UNSIGNED，如果要把它改为MEDIUMINT UNSIGNED ，则：

   ALTER TABLE mytbl MODIFY i MEDIUMINT UNSIGNED;
ALTER TABLE mytbl CHANGE i i MEDIUMINT UNSIGNED;

   为什么在使用CHANGE语句时要把i写两遍呢，因为CHANGE语句在修改数据类型的同时可以将该列重命名．

2. 如果要修改列的字符集：

   ALTER TABLE t MODIFY c CHAR(20) CHARACTER SET ucs2;

   修改存储引擎时应注意不同引擎之间功能是否兼容：

   • 包含有BLOB列的表不能转换成MEMORY引擎；
   • 包含有外键的InnoDB表不能转换为其他引擎；
   • 如果希望某个表的内容在服务器重新启动后依然存在，那么不能使用MEMORY引擎；

3. 重新命名表

   ALTER TABLE tbl_name RENAME TO new_tbl_name;
RENAME TABLE tbl1_name TO new_tbl1_name,tbl2_name TO new_tbl2_name,...;

2.12 事务处理

事务处理的四个特性，ACID
- Atomic 原子性，构成事务的所有语句应该是一个独立的逻辑单元。
- Consistent 一致性，数据库在事务的执行前后都必须是一致的。
- Isolated 独立性，事务之间不应该相互受影响。
- Durable 持久性，当事务成功执行完成时，其影响将永久性地记录到数据库里。

执行事务的两个方法：

• 方法1：STRAT TRANSACTION;
//DO SOMETHING;
COMMIT;

• 方法2：SET autocommit = 0;
//DO SOMETHING;
COMMIT;

  • 此方法会将所有操作当做事务，通过COMMIT提交，解决方法为再输入SET autocommit =1；

2.12.1 事务保存点

CREATE TABLE t1(i INT) ENGING = InnoDB;
START TRANSACTION;
INSERT INTO t VALUES(1);
SAVEPOINT my_savepoint;
INSERT INTO t VALUES(2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO t VALUES(3);
COMMIT;
SELECT * FROM t;
//result
| i |
|--------|
| 1 |
| 3 |

2.12.2 事务隔离

MyISAM执行的是表锁，不过这种方法在大量更新操作时难以提供很好的并发性能；
InnoDB执行的是另一种方法，当有两个客户端想要同时修改某个行，那么先锁定该行的那个客户端可以先修改它，这种方式提供了更好的并发性能，不过带来了另外一个问题，即一个客户端的事务是否应该看到另一个客户端的事务所做的修改，可能出现的问题如下：

• 脏读（dirty read）
• 不可重复读
• 幻影行，

处理方法为：

• READ UNCOMMITTED ,允许某个事务看到其他事务尚未提交的行修改；
• READ UNCOMMITTED, 只允许某个事务看到其他事务已经提交的行修改；
• REPEATABLE READ, 如果某个事务两次执行同一条SELECT语句，其结果是可重复的，也就是说，即使有其他事务在同时插入或修改行，这个事务所看到的结果也是一样的。
• SERIALIZABLE , 与REPEATABLE READ相似，但对事务的隔离更彻底，主要体现在，对于某个事务正在查看的行，只有等到该事务完成才能被其他事务所修改。

隔离级别	脏读	不可重复读	幻影行
READ UNCOMMITED	是	是	是
READ COMMITED	否	是	是
REPEATABLE READ	否	否	否
SERIALIZABLE	否	否	否

2.13 外键和引用完整性

[CONSTRAINT constraint_name]
FOREIGN KEY [fk_name] ( index_columns)
REFERENCES tbl_name(index_columns)
[ON DELETE action]
[ON UDPATE action]
[MATCH FULL| MATCH PARTIAL| MATCH SIMPLE]

• ON DELETE 包括
  
  • ON DELETE NO ACTION
  • ON DELETE CASCADE
  • ON DELETE SET NULL

2.13 外键和引用完整性

[CONSTRAINT constraint_name]
FOREIGN KEY [fk_name] ( index_columns)
REFERENCES tbl_name(index_columns)
[ON DELETE action]
[ON UDPATE action]
[MATCH FULL| MATCH PARTIAL| MATCH SIMPLE]

• ON DELETE 包括
  
  • ON DELETE NO ACTION
  • ON DELETE CASCADE
  • ON DELETE SET NULL

2.14 FULLTEXT搜索

2.14.1 全文搜索的类型

• 自然语言搜索：把搜索字符串解析成一系列的单词，然后搜索出包含这些单词的行。
• 布尔模式搜索：在搜索字符串中单词可以包含修饰字符，用以表明某些特定的要求，如某个给定的单词必须出现（或不出现）在匹配行里，或者某些行必须恰好包含某个短语。
• 查询扩展搜索：这种搜索分两个阶段，第一阶段是自然语言搜索，第二阶段则先把原来的搜索字符串，与在第一阶段的搜索里高度匹配的那些行，连接在一起，然后再进行一次搜索，这种做法扩大了搜索范围，可以把那些与原有搜索字符串相关，但用这些字符串却匹配不到的那些行也找出来，

2.14.2 自然语言FULLTEXT搜索

SELECT * FROM tbl_name WHERE MATCH(row_name) AGAINST('key_word');