MySQL常用速查手册

2020-01-16 本文已影响0人国猫

1. SQL命令

1.1. 库操作

1.1.1. 创建数据库

CREATE database dbname;

1.1.2. 删除库

DROP database db1;

1.1.3. 切换到库

USE dbname;

1.2. 表操作

1.2.1. 创建数据表

CREATE TABLE <表明>
  （
     列名1, 数据类型[约束][默认值],
     列名2, 数据类型[约束][默认值],
     列名n, 数据类型[约束][默认值],
     )[ENGINE=存储引擎][CHARACTER SET=字符集];

例：

CHARACTER TABLE t1
  (
    id INT PRIMARY KEY,   #设定该列为主键，主键内的值必须唯一，会自动创建主键索引
    id2 INT not null,     #设置id2不允许为空
    name varchar(10),
    sex ENUM('F','M','UN')
    )ENGINE=MYISAM CHARACTER SET utf8;

# 创建表t1，并设置两个列，一个列叫id类型是整形INT长度10，第二列叫name类型是可变长字符串VARCHAR长度20
#其中的长度代表显示的长度，一般没效果
CREATE TABLE t1( id INT(10), name VARCHAR(20) );

设定主键的第二种方式，先设定好每一列，最后再声明主键，方便一次性给多列设置主键

CREATE TABLE t1
  (
    uid INT(100),
    certid INT(20),
    name VARCHAR(10),
    sex ENUM('F','M','UN'),
    PRIMARY KEY(uid,certid)
    )ENGINE=InnoDB CHARACTER SET uft8;

创建表并沿用另外一张表的数据结构

CREATE TABLE t2 LIKE t1;

1.2.2. 表约束

1.2.2.1. 非空约束

#name列设置为非空列
CREATE TABLE t1
  (
    uid INT PRIMARY KEY,
    name VARCHAR(10) NOT NULL,             #非空
    )ENGINE=MYISAM CHARACTER SET utf8;

1.2.2.2. 唯一性约束

约束列中的值不能重复，可以有但只能有一个空值
CREATE TABLE t13
  (
    certid VARCHAR(20) UNIQUE
    )ENGINE=MYISAM CHARACTER SET utf8;

1.2.2.3. 默认默认约束（默认值约束）

约束列中的值的默认值。

除非默认值为空值，否则不允许插入空值

CREATE TABLE t1
  (
      sex ENUM('F', 'M', 'NU') DEFAULT 'UN'
  )ENGINE=MYISAM CHARACTER SET utf8;

1.2.2.4. 自增长约束

用于系统自动生成字段的主键值

用于数据量较大且数据又连续性增长的列

值不能为空

CREATE TABLES t1
(
    uid INT PRIMARY KEY AUTO_INCREMENT
)ENGINE=MYISAM CHARACTER SET utf8;

1.2.3. 创建表，并设定外键

#设定外键的名称为fk_t1，关联到当前表t2的fid列，关联到其他表t1点uid列
CREATE TABLE t2
  (
    fid INT(100),
    phone varchar(16),
    location varchar(50),
    CONSTRAINT fk_t1 FOREIGN KEY(fid) REFERENCES t1(uid)
    );

1.2.4. 删除表

DROP TABLE t1;

1.2.5. 删除表,如果表存在就删除，如是不存在也不会报错

DROP TABLE IF EXISTS t1;

1.2.6. 显示库中的所有表

SHOW TABLES;

1.2.7. 查看表结构

DESC tablename;
DESCRIBE tablename;

1.2.8. 修改表数据

1.2.8.1. 对表插入单行数据，有SET关键字

INSERT INTO table_name SET <字段1>=值1, <字段2>=值2, <字段n>=值n;

例

INSERT INTO tablename SET id = 1, name = 'tube';

1.2.8.2. 对表插入多行数据，无SET关键字

字符串值必须用单引号引起来

如果要插入所有字段，则字段可省略

INSERT INTO table_name
  [(字段1, 字段2, 字段n)]
VALUES
  （值1, 值2, 值n), (值1n, 值2n, 值3n);

例

INSERT INTO tablename (id,name) VALUES (2, 'kevin'), (3, 'mark');

1.2.8.3. 对表插入查询结果

将select的查询结果插入到表中

INSERT INTO table_name1
  (字段1, 字段2, 字段n)
SELECT 字段a, 字段b, 字段c FROM table_name2 [WHERE condition];

例

INSERT INTO t2
  (id, name, location)
SELECT id, name, locaton FROM t3;

1.2.8.4. 更新数据 update

UPDATE table_name SET
  字段1=值1,
  字段2=值2,
  字段n=值n
[WHERE condition];

例

UPDATE t1 SET
  name='Tom'
WHERE id=1;

1.2.8.5. 删除数据 DELETE

如果不带上where语句的话，delete会直接清空整张表

DELETE FROM table_nam [WHERE <condition>];

例子

DELETE FROM t8 where id> 13;
DELETE FROM t8 where id> 13 AND id< 18;
DELETE FROM t8 where id> 13 OR id< 3;

1.2.9. 其他操作

1.2.9.1. 显示创建库时使用的sql命令

SHOW CREATE database db3;

1.2.9.2. 显示有关在当前 session 中执行语句所产生的条件(错误，警告和注释)的信息

SHOW WARNINGS;

1.2.9.3. 显示错误总数

SHOW COUNT(*) WARNINGS;

1.2.9.4. 显示创建该表的指令

SHOW CREATE TABLE tablename; 
# \G将输出的结果旋转90度变成纵向
SHOW CREATE TABLE tablename\G;

1.2.9.5. 统计表中的行数

#统计t2表的行数
SELECT COUNT(*) FROM t2;

#通过WHERE指定一个条件来计数;
SELECT COUNT（*） FROM t2 WHERE id = 2；

#加上DISTINCT，统计表中的唯一行
SELECT COUNT(DISTINCT id) FROM t2;

#以一列作为基准，统计列中每个值出现的次数
SELECT id, count(*) FROM t2 GROUP BY id;

#以一列为基准，统计列中每个值出现的次数，并只列出出现9次以上的结果
SELECT id, count(*) FROM t2 GROUP BY id HAVING count(*) >= 9;

1.2.9.6. 分析查询语句执行的过程

使用explain命令可以分析查询语句执行的过程

EXPLAIN SELECT * FROM t2 WHERE id>1 AND age<25;

1.2.9.7. 刷新查询缓存

清空查询缓存

reset query cache;

1.2.10. 修改表结构

1.2.10.1. 修改表名

ALTER TABLE <旧表名> RENAME <新表名>;

例：

ALTER TABLE ti RENAME t4;

1.2.10.2. 修改字段的数据类型

当表内已经有数据，一定要谨慎修改

ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

例：

ALTER TABLE t1 MODIFY name VARCHAR(20);

1.2.10.3. 修改字段名

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

例：

ALTER TABLE t1 CHANGE name username VARCHAR(20);

1.2.10.4. 添加字段（添加列）

当表中数据量很大时，会严重影响性能

ALTER TABLE <表名> ADD <新字段名> <新数据类型> [约束条件] [FIRST|AFTER 已存在当字段名];

例：

ALTER TABLE t1 ADD location VARCHAR(20) AFTER uname;
ALTER TABLE t1 ADD location VARCHAR(20) NOT NULL AFTER uname;
#创建在第一列
ALTER TABLE t1 ADD location VARCHAR(20) FIRST;

1.2.10.5. 删除字段（删除列）

ALTER TABLE <表名> DROP <字段名>;

例

ALTER TABLES t1 DROP location;

1.2.10.6. 修改字段排列位置

ALTER TABLE <表名> MODIFY <字段名1> <数据类型> FIRST|AFTER <字段2>;

例:

ALTER TABLE t1 MODIFY name VARCHAR(20) AFTER uid;

1.2.10.7. 修改表存储引擎

ALTER TABLE <表名> ENGINE=<新引擎名称>;

例

ALTER TABLE t1 ENGINE=InnoDB;

1.2.10.8. 删除表的外键约束

在删除所有对应的外键之前，主键对应的表是无法被删掉的

ALTER TABLE <表名> DROP FOREIGN KEY <外键名>;

例

ALTER TABLE t2 DROP FOREIGN KEY fk_1;

1.2.11. 事务操作

使用InnoDB数据引擎的表支持事务操作

默认情况Mysql开启了自动提交，每提交一条sql语句会自动作为一个事务处理

BEGIN开启一个事务
ROLLBACK 回滚一个事务
COMMIT 提交一个事务

例子：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t8 VALUES (1, 'simon' ,'10');
Query OK, 1 row affected (0.01 sec)

#此时，数据只插入到了当前事务内（redolog），还未提交到物理存储中（binlog）
#所以该条目目前只能在当前事务内（session内）看到

mysql> select * from t8;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | simon |   10 |
+------+-------+------+
1 row in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
#commit后数据才提交到了物理存储中

如果事务添加时，后悔了，在commit之前可以使用rollback回滚操作

mysql> select * from t8;
Empty set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t8 VALUES (2, 'simon2' ,'10');
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t8;
Empty set (0.00 sec)

查看与关闭自动提交

在做大量单行提交时，关闭自动提交能提高效率，减少mysql commit的次数

#查看mysql环境配置
mysql> show variables like '%commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

#关闭自动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | OFF   |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.01 sec)

1.2.11. 表查询操作

1.2.11.1. 单表查询操作

select指定需要查询的列名
from 指定需要查询的表
where过滤值条件

SELECT * FROM city;
SELECT name, population FROM city WHERE id=1;

IN关键字查询

IN相当于where or条件，相当于例子中查询id为100或101的条目

SELECT id, name, population FROM city WHERE id IN (100,101);

AND多条件查询，代表必须同时符合多个条件才会显示

SELECT name,district,population FROM city WHERE district LIKE 'Nord' AND id< 5;

OR多条件查询，代表只需要满足多个条件中的任意一个即可

SELECT name,district,population FROM city WHERE district LIKE 'Nord' OR id< 5;

AND与OR可以一起使用，但是AND优先生效优先级高于OR

SELECT name,district,population FROM city WHERE district LIKE 'Nord' OR district LIKE 'shanghai' AND id< 5;

NOT排除条件，一般需要组合使用例如NOT LIKE

SELECT * FROM city WHERE id< 5 AND district NOT LIKE 'N%d';

WHERE BETWEEN AND 范围查询

例子查询10～20的数据

SELECT name,population FROM city WHERE id BETWEEN 10 AND 20;

WHERE LIKE搜索字符查询

SELECT name,district,population FROM city WHERE district LIKE 'Nord';

模糊查询，模糊匹配，模糊搜索字符

使用百分号%代表任意个任意字符，_代表一个任意字符

SELECT name,district,population FROM city WHERE district LIKE 'N%d';
SELECT name,district,population FROM city WHERE district LIKE 'No_d';

LIMIT限制显示行数

显示头20行

SELECT * FROM city LIMIT 20;

从第10行开始显示之后10行，显示特定范围

SELECT * FROM city LIMIT 10,10;

DISTINCT查询结果不重复

SELECT DISTINCT * FROM city WHERE district id< 20;

ORDER BY查询结果排序

Mysql 默认采用升序（ASC），也可以指定采用降序（DESC）

例子里根据population列排序

如果使用order by排序的时候不起作用，原因是order by的字段是varchar类型的，在字段后加上'+0' 则转化为int类型，例如: select * from ORDER BY 字段+0 即可

SELECT * FROM city WHERE id < 10 ORDER BY population;
SELECT * FROM city WHERE id < 10 ORDER BY population DESC;

多列排序，按照先后进行排序，中间用逗号分开

SELECT * FROM city WHERE id <10 ORDER BY countrycode,name;

GROUP BY分组查询，将相应的结果组织到一起回显

count(*)是一个统计函数，统计行数

AS给列起一个别名

例子里先用GROUP BY将结果分组，再使用count(*)统计每一个列的行数

SELECT CountryCode,count(*) AS Total FROM city WHERE ID<10 GROUP BY countrycode;

多字段分组，用逗号分隔列名，会先分组一个，再分组第二个

SELECT * FROM city GROUP BY countrycode,district;

HAVING过滤分组

用于数据输出前的最后一次筛选，二次过滤使用

SELECT CountryCode,count(*) AS Total FROM city 
WHERE id <101 
GROUP BY CountryCode
HAVING count(*) >10;

WITH ROLLUP 统计求和

需要配合GROUP BY使用，输出结果的末尾会增加一个总和

mysql> select name,count(*) as total from t group by name with rollup;
+-------+-------+
| name  | total |
+-------+-------+
| qq    |     2 |
| simon |     2 |
| NULL  |     4 |
+-------+-------+
3 rows in set (0.00 sec)

mysql>

1.2.11.2. 多表查询操作

1.2.11.2.1. 内连接查询`INNER JOIN`

内连接查询使用比较运算符进行表间列数据的比较操作。

并输出符合条件的结果。

FROM后面跟着要查询的多个表，用逗号分隔

SELECT后面查询的列需要声明从哪个表查，例如City.Name查询的是City表的Name列，

如果某一列只存在于其中一个表，这个列可以不需要声明表名，例如ID

如果不加上WHERE会出现显示异常，称为笛卡尔积的现象

#简单写法
SELECT ID, City.Name, Population, LifeExpectancy
FROM City, Coutry
WHERE ID< 10 and City.CountryCode = Country.Code;

#标准写法
SELECT ID, City.Name, Population, LifeExpectancy
FROM City INNER JOIN Coutry
WHERE ID< 10 and City.CountryCode = Country.Code;

1.2.11.2.2. 外连接查询

在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL

OUTER JOIN,LEFT and RIGHT ON

外连接分为左连接与右连接

左连接代表以左表作为基准LEFT OUTER JOIN ON

右连接代表以右表作为基准RIGHT OUTER JOIN ON

SELECT ID, City.Name, City.Population, LifeExpectancy
FROM City LEFT OUTER JOIN Country
ON ID<10 and City.CountryCode=Country.Code LIMIT 10;

1.2.11.2.3. 子查询

ANY与SOME子查询

通过创建表达式，对返回对结果进行比较，并输出符合条件的结果

下面例子，查询t2表的name与age列，并过滤出age大于t1表中任何age的结果

SELECT name, age FROM t2
WHERE age > ANY (SELECT age FROM t1);

EXISTS与NOT EXISTS子查询

这是一种判断子查询

EXISTS判断子查询是否有返回行，如果有则执行外层语句中的查询，如果没有则不执行。

NOT EXISTS相反

例子中，查询state表，过滤出Nginx等于Fail的结果，如果结果存在，则再查询log表过滤出category='Nginx'

SELECT * FROM log
WHERE category='Nginx'
AND EXISTS (SELECT * from state WHERE Nginx='Fail');

IN子查询

判断子查询是否有返回行，如果有则执行外层语句中的查询，如果没有则不执行。

且有返回行的情况下，比对查询结果，输出值相同的行

例子查询blacklist表的Name列，如果有数据，则与People比对，输出People中Name存在于Blacklist的行

SELECT * FROM People
WHERE name IN (SELECT Name FROM blacklist);

UNION子查询

用于合并查询结果，可以将多条select结果组合成单个结果。

要求被组合的表列数必须相同。数据类型也必须相同。

默认组合会去掉相同的结果，只留下一条

SELECT * FROM t1
UNION SELECT * FROM t2;

而加上ALL语句，则会将重复的行都显示出来

SELECT * FROM t1
UNION ALL SELECT * FROM t2;

1.2.11.3. 使用函数查询

COUNT()函数，统计行数

默认用于统计所有数据行的总行数，不包括空行

SELECT COUNT(*) FROM city;

统计特定列的行数

SELECT COUNT(name) FROM city;

SUM()函数，数据求和

用于列求和，在数字类型的数据使用可用，在字符类型列使用则会返回0

SELECT SUM(population) from city;

AVG()函数，统计平均

SELECT AVG(population) from city;

MAX()函数，取出列最大值

SELECT name,MAX(population) from city;

MIN()函数，取出最小值

SELECT name,MIN(population) from city;

1.2.11.4. 正则表达式查询`REGEXP`

以特定字符开头 REGEXP ^

SELECT * FROM city WHERE contrycode like 'CHN' and District REGEXP '^z';

以特定字符结尾 REGEXP $

SELECT * FROM city WHERE contrycode like 'CHN' and District REGEXP 'g$';

匹配任意单个字符REGEXP .

SELECT * FROM city WHERE contrycode like 'CHN' and District REGEXP 'C.N';

匹配前面的字符0个或多个REGEXP *

例子中可匹配开头包含S，或开头包含Sh的结果，等价于{0,}

例如可匹配到Shanghai、S、Hongkong

SELECT * FROM city WHERE district REGEXP '^Sh*';

匹配前面的字符1个或多个REGEXP +

例子中可匹配开头包含Sh的结果，例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}

SELECT * FROM city WHERE district REGEXP '^Sh+';

匹配一个字符串或另外一个或多个字符串|

SELECT * from city
WHERE district REGEXP 'Shan｜Guang'

匹配任意一个字符[Sh]

如果写 [^Sh]则是不包含这两个字母的结果

SELECT * from city
WHERE district REGEXP '[Sh]'

匹配指定字符连续出现的次数h{1,2}

h{1,}与h{1}相当于匹配h连续出现1次或以上

SELECT * from city
WHERE district REGEXP 'h{1,5}'

1.2.12. 数据库视图

视图是一个虚拟表，是从数据库中一个或多个表导出的表。

视图是一个编译好的sql语句，而表不是

视图保存在内存中，所以速度更快

当建视图的SQL语句中包含以下子句时，无法使用MERGE算法：

聚集函数
DISTINCT
GROUP BY
HAVING
集合操作（UNION，UNION ALL）
子查询

视图的特点：

视图用于提高安全性
简化工作
逻辑独立

1.2.12.1. 创建单表视图

语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED ] MERGE | TEMPTABLE}]
# REPLACE 重新设置视图时使用
# ALGORITHM 定义视图算法，默认 undefined 会自动选择合适的视图
# MERGE 合并视图的语意定义，如果能使用底层表的索引则会自动使用这个算法
# TEMPTABLE 如果底层数据表没有索引，则使用这个算法，该算法会创建一个临时表，效率更低
VIEW view_name 【（column_list)】
# 设置视图的名称，可选性加上列名称
AS SELECT_statement
# 视图的查询语句
[WITH [ CASCADED | LOCAL ] CHECK OPTION]
# CASCADED 默认值，更新时必须满足底层表的条件，例如非空约束等等
# LOCAL 更新时仅满足该视图本身定义的条件即可，忽视底层表的数据结构约束

# 创建名为view_user的视图，其值包含从表user查出的列name, age
CREATE VIEW view_user
AS SELECT name, age FROM user;

1.2.12.2. 查看视图

# 查询视图
SELECT * FROM view_user;

#查看视图结构
DESC view_user;

#查看创建视图的sql语句（不能加引号）
SHOW CREATE VIEW view_user;

#查看表/视图的属性信息
SHOW TABLE STATUS LIKE 'view_userinfo' \G;

#在infomation_schema表view列中查看视图
SELECT * FROM infomation_schema.views \G;

1.2.12.3. 修改视图

因为视图是一个虚拟表，其中显示的数据是视图指向的基本表的数据

修改或删除视图的内容就相当于修改或删除了视图所指向表的内容

# 修改视图的表内容
UPDATE view_user SET age = 20 
WHERE name='simon';

# 修改视图的结构
CREATE OR REPLACE VIEW view_user
AS SELECT id, name FROM user;

# 使用ALTER语句修改视图结构
ALTER [ALGORITHM = {UNDEFINED ] MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS SELECT_statement [WITH [ CASCADED | LOCAL ] CHECK OPTION]

ALTER VIEW view_user
AS SELECT name from user;

1.2.12.4. 创建多表视图

CREATE VIEW view_userinfo(new_name, new_phone)
# 视图名称后括号里设置里视图里列的名称
AS SELECT user.name, userinfo.phone FROM user, userinfo
WHERE id= fid;

1.2.12.5. 删除视图

# 如果被删除的视图原本就不存在，会报错
DROP VIEW view_user;
# 就算视图不存在也不会报错
DROP VIEW IF EXISTS view_user;

2. Shell命令

2.1. 初始化mysql，创建默认库

./mysql_install_db --basedir=/usr/local/mysql/(mysql的安装路径) \
--datadir=/var/lib/mysql(数据库的存放路径) \
--no-defaults --user=mysql

2.2. 在shell环境里执行sql语句

使用-e参数在shell环境里执行sql语句

其中i是外部变量

md5()函数用于生成值的md5值

i=10
mysql db1 -e "insert into test1 value ($i, md5($i));"

3. 关键说明

3.1. 内置库

3.1.1. infomation_schema

这个库并不存在磁盘，而是mysql启动时临时创建出来的

这个数据库中保存了MySQL服务器所有数据库的信息。

如数据库名，数据库的表，表栏的数据类型与访问权限等。

再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，

每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information_schema里面。

information_schema的表schemata中的列schema_name记录了所有数据库的名字
information_schema的表tables中的列table_schema记录了所有数据库的名字
information_schema的表tables中的列table_name记录了所有数据库的表的名字
information_schema的表columns中的列table_schema记录了所有数据库的名字
information_schema的表columns中的列table_name记录了所有数据库的表的名字
information_schema的表columns中的列column_name记录了所有数据库的表的列的名字

3.1.2. performance_schema

这个库并不存在磁盘，而是mysql启动时临时创建出来的

主要用于保存性能收集信息

3.2. 索引

普通索引和唯一索引
- 普通索引是MySQL的基本索引类型
- 唯一索引对应列的值必须唯一，但允许空值。如果是组合索引，则列值但组合必须唯一。
- 主键索引是一种特殊的唯一索引，不允许空值。给列添加主键约束时，会自动添加主键索引。
单列索引和组合索引
- 单列索引指只包含一列的索引。一个表可以有多个单列索引。
- 组合索引指表的多个字段组合上创建的索引。遵循做前缀组合。
全文索引
- FULLTEXT类型索引。可以在CHAR，VARCHAR或者TEXT类型的列上创建。
- 仅MyISAM支持
空间索引
- 对空间数据类型对字段建立的索引。

索引原则

索引并非越多越好。每次插入数据，就会触发重新计算索引，如果索引多将造成很大压力。
数据量不多不需要键索引。
列中的值变化不多也不需要建索引，因为查询时数据会存入缓存，缓存速度很快。
经常排序和分组的数据列要建立索引。
唯一性约束对应使用唯一性索引。

3.2.1. 创建表的时候创建索引

3.2.1.2. 创建普通索引

定义好每个列后使用INDEX语句声明要创建索引的列，例子中针对name列创建索引

CREATE TABLE t1
  (
    id INT PRIMARY KEY,
    name VARCHAR(10),
    sex ENUM('F', 'M', 'UN'),
    INDEX(name) 
  )ENGINE=InnoDB CHARACTER SET utf8;

3.2.1.3. 创建唯一索引

UNIQUE INDEX关键词创建唯一索引，其后必须跟着可自定义的索引名称id_in，最后声明对id列创建索引

CREATE TABLE t1
  (
    id INT NOT NULL,
    name VARCHAR(10),
    sex ENUM('F', 'M', 'UN'),
    UNIQUE INDEX 'id_in' ('id')
  )ENGINE=InnoDB CHARACTER SET utf8;

3.2.1.4. 创建单列索引

用INDEX关键词创建单列索引，其后跟上自定义的索引名称'name_in'，之后声明对name列创建索引，并且指定索引长度为10个字符

CREATE TABLE t1
  (
    name VARCHAR(10),
    sex ENUM('F', 'M', 'UN'),
    INDEX 'name_in' ('name'(10))
  )ENGINE=InnoDB CHARACTER SET utf8;

3.2.1.5. 创建组合索引

组合索引与单列所以的区别就在于，创建索引事声明的列为多个

使用了组合索引，查询语句的查询条件必须包含了索引声明的第一个列（如id）才会触发索引查询

例如where name like 'Tom' and age <25 无法触发索引查询

例如where name like 'Tom' and id <25 能触发索引查询

CREATE TABLE t1
  (
    id INT NOT NULL,
    name VARCHAR(20),
    age INT NOT NULL,
    INDEX muti_in (id,name) 
  )ENGINE=InnoDB CHARACTER SET utf8;

3.2.1.6. 创建全文索引（FULLTEXT索引支持MyISAM，不支持InnoDB）

使用FULLTEXT INDEX关键词创建全文索引

CREATE TABLE t1
  (
    id INT NOT NULL,
    name VARCHAR(20),
    age INT NOT NULL,
    info TEXT,
    FULLTEXT INDEX 'info_in' (info)
  )ENGINE=InnoDB CHARACTER SET utf8;

3.2.2. 对已有表创建或删除索引

使用ALTER创建索引

ALTER TABLE t1 ADD INDEX nameIdx (name(20));

使用CREATE INDEX创建索引，对t1表的name列创建索引

CREATE INDEX nameIdx ON t1(name);

使用ALTER删除索引

ALTER TABLE t1 DROP INDEX nameIdx;

使用DROP INDEX删除索引

DROP INDEX nameIdx ON t1;

3.2.3. 查看表拥有哪些索引

show create table t1;

4. Mysql的权限管理

Mysql使用逐级下查的方式确认权限，使用以下的顺序查询用户权限，

当匹配到有权限则不再继续下查

mysql先查询mysql库的user表，user表是全局生效的，当用户对库有权限则对所有库都有权限
之后再查询db表，db表内描述的是用户对某一个库的权限
之后再查询host表，用户对应用户主机的权限
之后查询tables_priv表的权限，或procs_priv，用户对表级别的权限
之后是columns_priv表，用户对某个列的权限
如果以上的查询结果都是no，则返回用户无权限

4.1. 创建用户

4.1.1. `CREATE USER`语句创建

# 这样创建的用户没有任何权限
CREATE USER 'username'@'host' [ IDENTIFIED BY 'PASSWORD' ];

# 创建用户允许从任意主机访问过来
CREATE USER 'simon'@'%' IDENTIFIED BY 'PASSWORD' ;

# 创建用户只允许从本地访问
CREATE USER 'simon'@localhost ;

4.1.2. `GRANT`语句创建用户并授权，如果用户不存在则会自动创建

# 语法
GRANT <ALL|priv1, priv2, ...privn> 
# 设置授予的数据操作权限，all就是所有权限
ON [object]
# object可以是表、函数、存储过程
[WITH GRANT OPTION];

WITH GRANT OPTION]附加设定，附加设定有以下可选：

1、GRANT OPTION代表给这个用户的授权允许下发，允许把自己权限下发给其他人

2、MAX_QUERIES_PER_HOUR设定每小时能发起几次查询

3、MAX_UPDATES_PER_HOUR设定每小时能发起几次数据更新操作

4、MAX_CONNECTIONS_PER_HOUR允许每小时发起多少次连接

5、MAX_USER_CONNECTIONS允许该用户发起总连接多少个

#授权simon用户允许从192.168.1.1发起连接，并允许操作db1库的所有表，允许操作表操作
GRANT ALL ON db1.* to 'simon'@'192.168.1.1' ;

4.2. `FLUSH PRIVILEGES`刷新授权表

将内存中的缓存信息写入磁盘

4.3. 删除用户

删除用户并不是删除一个用户所有的授权

而是删除某一个用户从某个来源地址的授权

DROP USER 'username'@'host';

4.4. 查看用户权限

查询时正常会看到一个用户存在两条GRANT

其中一条GRANT USAGE代表创建用户，这句没有赋予任何权限

SHOW GRANTS FOR 'username'@'host';
SHOW GRANTS FOR 'username'@'host' \G;

mysql> show grants for 'simon';
+-------------------------------------------------+
| Grants for simon@%                              |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `simon`@`%`               |
| GRANT ALL PRIVILEGES ON `test`.* TO `simon`@`%` |
+-------------------------------------------------+
2 rows in set (0.00 sec)

4.5. 回收权限

语法

REVOKE <ALL|priv1, priv2, ...privn>
[ON table1, table2, ...tableN]
FROM 'username'@'host' [, 'username'@'host'];

例

REVOKE ALL ON db1.* FROM 'simon'@'192.168.1.1';

4.6. 修改密码

4.6.1. `SET PASSWORD`修改密码

# 修改自己的密码
SET PASSWORD=PASSWORD('yourpassword');

SET PASSWORD=PASSWORD('1234');

# 修改其他用户密码
SET PASSWORD FOR 'user'@'host' =PASSWORD('newpassword');

4.6.2. 直接修改user表来修改密码

UPDATE mysql.user SET
PASSWORD=PASSWORD('newpassword')
WHERE User='simon' AND Host= 'host';

4.6.3. 使用`mysqladmin`命令修改密码

mysqladmin -u username -p'oldpassword' password "newpassword"

4.7. 忘记root密码怎么处理

1、关闭数据库

2、使用这个命令启动mysqlmysqld_safe --skip-grant-tables &

3、使用空密码进入数据库（mysql命令后直接回车）

4、使用UPDATE语句修改root密码

update user set password=password('newpass') where user='root';

5、关闭数据库并重新以正常方式启动

5. Mysql数据库备份

5.1. 备份前的规划

需要备份哪些库
数据库的体积
确认存储引擎
选择备份工具以及备份方式
锁和宕机带来的影响
备份保存到什么地方
数据变化的频率
行业规范或者合规性

备份方式大致分为两种：

1、操作系统级别的备份，文件备份

2、逻辑方式备份，SQL语句方式备份

5.2. 操作系统级别备份，文件备份

特点：

操作简单
速度最快
需要停服务操作
需要结合其他手段共同使用

5.3. 逻辑方式备份

特点：

其实是用SQL语句描述数据库，或是输出所有的查询结果
兼容性最好，跨版本、平台、产品。
执行效率最慢，影响较大。

（To be Continued...)