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, =值2, =值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 ];

例子

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 FIRST|AFTER ;

例:

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;

复制代码

1.2.13. 数据库锁

1.2.13.1. MyISAM数据库锁

#给表加写锁

LOCK TABLES 'table' write;

#给表解锁

UNLOCK TABES;

复制代码

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就是所有权限

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

[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.1. mysqlhotcopy

mysqlhotcopy是MySQL软件包自带的备份工具，企业版与社区版都包含

mysqlhotcopy是一个Perl脚本，需要安装相关软件包

其本质是使用锁表语句后再使用cp或scp命令拷贝数据库文件

备份前后执行FLUSH TABLE WITH READ LOCK与UNLOCK TABLES与mysqldump相似

仅支持MyISAM

语法:

mysqlhotcopy -u root -p password mydb /mnt/backup

复制代码

例：

mysqlhotcopy -u root -p 'password' mydb /backup

mysqlhotcopy -u root -p 'password' mydb1 mydb2 /backup

复制代码

5.3.2. LVM卷快照备份

几乎是热备，因操作之前需要锁表，操作后需要解锁，无法预计锁表与解锁消耗的时间

支持所有存储引擎

备份速度快

虽然很快，但依然需要锁表，锁表后对磁盘LVM卷做一个快照

数据如果分布在多个卷上，操作复杂度将翻倍

例：

#创建LVM

pvcreate /dev/sdb

vgcreate testvg /dev/sdb

lvcreate -L 200M -n testlv testvg

mkfs -t ext4 /dev/testvg/testlv

mount /dev/testvg/testlv /mnt

#导入数据库，这部分操作省略 。。。

#锁表

mysql> flush tables with read lock;

#记录一下二进制日志数字，留意File与Position字段

mysql> show master status \G;

#创建快照

#使用/dev/testvg/testlv产生一个快照来创建一个大小为50M的名称为snap1的卷

lvcreate -L 50m -s -n snap1 /dev/testvg/testlv

#解锁表

mysql> unlock tables;

#之后将snap1卷拷贝走即可

复制代码

5.3.3. mylvmbackup

mylvmbackup是一个开源工具，可以自动创建lvm卷或快照

依赖perl

5.3. 逻辑方式备份

特点：

其实是用SQL语句描述数据库，或是输出所有的查询结果

兼容性最好，跨版本、平台、产品

执行效率最慢，影响较大

5.3.1. mysqldump

mysqldump是MySQL软件包自带的备份工具

使用SQL语句描述数据库及数据并导出

备份时，在MYISAM引擎锁表，InnoDB引擎锁行

数据量很大时不推荐使用，消耗时间长

语法：

#备份单表

mysqldump [OPTIONS] databases [tables]

#备份多个或1个库

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3 ...]

#备份所有库

mysqldump [OPTIONS] --all-databases [OPTIONS]

复制代码

例：

#备份单个表

mysqldump -uroot -p viewdb > backup.sql

#备份多个表

mysqldump -uroot -p viewdb,worlddb >backup.sql

复制代码

5.3.2. Percona XtraBackup

XtraBackup是一个开源、免费的支持对InnoDB进行热备份对软件。

XtraBackup由Percona发布、支持。

XtraBackup特性：

不暂停服务创建InnoDB热备份。

为Mysql做增量备份。

在Mysql服务器之间做在线表迁移。

简化MySQL Replication创建。

低负担备份数据。

Xtrabackup不会备份MyISAM表、.frm文件以及数据库其余部分，需要另外操作备份

使用XtraBackup做完整备份

#如果被备份的mysql版本为5.6，则使用xtrabackup_56命令备份

#备份表空间ibd文件

xtrabackup_56 --backup --target-dir=/var/lib/backup

# 处理事务一致性，将事务日志中已提交的事务补充到库文件内

# --use-memory=500M 用于设置备份时占用多少内存，内存足够多的时候可省略

# 需要执行2次才能达到一致性，数据与事务日志的一致性

xtrabackup_56 --use-memory=500M --prepare --target-dir=/var/lib/backup

复制代码

使用XtraBackup 增量备份

# 模拟每日增量备份，创建备份目录

mkdir -p {mon.tue,wed}

#创建周一备份

xtrabackup_56 --backup --target-dir=/mon/

#参考周一的备份创建周二的差异备份，使用参数--incremental-basedir

xtrabackup_56 --backup --target-dir=/tue/ --incremental-basedir=/mon/

#参考周二创建周三的备份

xtrabackup_56 --backup --target-dir=/wed/ --incremental-basedir=/tue/

复制代码

恢复增量备份

所有的备份恢复都应该设置 apply-log-only 参数(only 指的就是只回放 redo log 阶段，跳过 undo 阶段)，避免未完成事务的回滚。

# 处理原始备份的一致性，将事务日志补充到备份使其一致

xtrabackup_56 --prepare --apply-log-only --target-dir=/var/lib/backup/

# 根据需要将增量合并至原始备份

# 这里举例将周一的增量备份追加到基础备份

xtrabackup_56 --prepare --apply-log-only --target-dir=/var/lib/backup/ --incremetal-dir=/mon/

# 然后将周二的增量备份追加

xtrabackup_56 --prepare --apply-log-only --target-dir=/var/lib/backup/ --incremetal-dir=/tue/

# 最后处理一次事务一致性，得到最终备份

xtrabackup_56 --prepare --target-dir=/var/lib/backup/

复制代码

5.3.3. Percona innobackupex

innobackupex是一个对xtrabackup封装的perl脚本，

其提供了用于myisam和innodb引擎，及混合使用引擎备份的能力，主要是为了方便同时备份InnoDB和MyISAM引擎的表。在处理myisam表时需要加一个读锁。

其加入了一些使用的选项。如slave-info可以记录备份恢复后作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重建slave。

innobackupex比xtarbackup有更强的功能，它整合了xtrabackup和其他的一些功能，它不但可以全量备份/恢复，还可以基于时间的增量备份与恢复。

使用innobackupex做完整备份

# 生成一个完整备份，备份完成后会自动产生一个日期目录

innobackupex --user=root --password=passwd /var/lib/backup/

# 生成InnoDB日志，使备份可用

innobackupex --use-memory=512m --apply-log /var/lib/backup/2019-02-28_02-47-28/

复制代码

使用innobackupex恢复备份

#这条命令根据my.cnf内的datadir设置将备份恢复至数据库

innobackupex --copy-back /var/lib/backup/2019-02-28_02-47-28/

#修改文件权限

chmod -R mysql.mysql /var/lib/mysql

复制代码

使用innobackupex做增量备份

# 先做一个完整备份，假设当天为周一

innobackupex --user=root --password=passwd /var/lib/backup/mon/

# 创建增量备份，直接运行会报错目录不存在，目录需要手动创建

# 以周一为基准创建周二的增量备份

innobackupex --incremental /var/lib/backup/tue/ --incremental-basedir=/var/lib/backup/mon/ --user=root --password=password

复制代码

恢复增量备份

#先处理原始备份的一致性

innobackupex --apply-log --redo-only /var/lib/backup/mon/--use-memory=500m --user=root --password=passwd

# 合并增量备份至原始备份，mon是完整备份，tue是增量备份

# 如果有多个增量备份，也一样按顺序合并到基础备份去

innobackupex --apply-log --redo-only /var/lib/backup/mon/日期/ --incremental-dir=/var/lib/backup/tue/日期/ --use-memory=500m --user=root --password=passwd

# 处理完整备份的最终一致性，将redo与undo都合并

innobackupex --apply-log /var/lib/backup/mon/日期/ --use-memory=500m --user=root --password=pass

# 恢复备份到生产库中

innobackupex --copy-back /var/lib/backup/mon/日期/

#修改文件权限

chmod -R mysql.mysql /var/lib/mysql

复制代码