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...)