MySQL操作

视图

对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦

解决办法：定义视图

通俗的讲，视图就是一条SELECT语句执行后返回的结果集。
视图是对若干张基本表的引用，一张虚表，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

视图的作用

提高了重用性，就像一个函数
对数据库重构，却不影响程序的运行
提高了安全性能，可以对不同的用户
让数据更加清晰

创建视图

create view view_name as select ...；

查看视图

查看某个库下的所有视图

select TABLE_NAME from information_schema.views where TABLE_SCHEMA='db_name'

查看视图详情

show create view `view_name`;
select * from information_schema.VIEWS where TABLE_SCHEMA='db_name' and TABLE_NAME='view_name';

使用视图

视图的用途就是查询

select * from v_05A;

修改视图

ALTER VIEW view_name AS select ...;

删除视图

drop view view_name;

实例

创建视图

create view v_05A as select "05A"  as "班级",s.name as "姓名",s.gender as "性别" from students as s where s.cid in (select c.id from class as c where c.name="05A");

修改视图

alter view v_05A as select * from students where cid=3;

删除视图

drop view v_05A;

触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

什么是触发器:触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

使用触发器可以帮助我们实现监控、日志…

触发器由 action_timing和 event_manipulation两个参数决定什么情况下触发：

action_timing：AFTER、BEFORE
event_manipulation：INSERT、DELETE、UPDATE

因此，触发器总共由六种触发时机。

创建触发器

/* 
修改MySQL默认的语句结束符,只作用于当前窗口;
将默认的结束符号由;改为$$ 
*/
DELIMITER $$

CREATE TRIGGER `database_name`.`new_trigger_name`
action_timing event_manipulation ON `database_name`.`table_name`
FOR EACH ROW
begin
    /**trigger body**/
    action_statement
end$$

DELIMITER ;

查看触发器

查看某个数据库中的所有触发器

select * from information_schema.triggers where TRIGGER_SCHEMA = 'db_name';

查看触发器详情

select * from information_schema.triggers where TRIGGER_SCHEMA='db_name' and TRIGGER_NAME='trigger_name';

查看创建触发器的语句

show create trigger `trigger_name`;

修改触发器

DROP TRIGGER IF EXISTS `database_name`.`trigger_name`;

DELIMITER $$

CREATE TRIGGER `database_name`.`new_trigger_name`
action_timing event_manipulation ON `database_name`.`table_name`
FOR EACH ROW
begin
    /**trigger body**/
    
end$$

DELIMITER ;

删除触发器

DROP TRIGGER `database_name`.`trigger_name`;

案例

创建数据表：cmd、errlog

CREATE TABLE cmd (
  id INT PRIMARY KEY auto_increment,
  USER CHAR (32),
  priv CHAR (10),
  cmd CHAR (64),
  sub_time datetime, #提交时间
  success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
  id INT PRIMARY KEY auto_increment,
  err_cmd CHAR (64),
  err_time datetime
);

创建触发器

delimiter $$
/* 创建一个触发器，在cmd表添加命令之后触发 */
create trigger tri_after_insert_cmd after insert on cmd 
for each row
begin
		/* 当插入命令失败时，将错误信息记录到errlog中 */
    if NEW.success = 'no' then
        /* new指代的cmd里面的数据对象 */
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志

INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('jason','0755','ls -l /etc',NOW(),'yes'),
    ('jason','0755','cat /etc/passwd',NOW(),'no'),
    ('jason','0755','useradd xxx',NOW(),'no'),
    ('jason','0755','ps aux',NOW(),'yes');

事件

事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。

事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。

事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

事件作用

一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。

可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。

创建事件

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

schedule: {
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
}

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

查看事件

查看库中所有事件

SELECT * FROM information_schema.events WHERE EVENT_SCHEMA='database_name';

查看事件详情

select * from information_schema.events where TRIGGER_SCHEMA='db_name' and EVENT_NAME='event_name';

查看创建存储过程的语句

show create function `event_name`;

修改事件

ALTER
    [DEFINER = user]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    [DO event_body]

删除事件

事件过期之后，会自动删除

DROP EVENT [IF EXISTS] `event_name`;

实例

创建事件

delimiter $$

/* 1分钟后在t2 表插入一条数据 */
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE DO
BEGIN
	INSERT INTO t2 values(3, "a3");
END$$
      
/* 从现在开始，到5分20秒之后，每20秒插入1条数据 */     
CREATE EVENT myevent2
ON SCHEDULE EVERY 20 SECOND
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL "5:20" MINUTE_SECOND DO
BEGIN
  DECLARE total INT;
  select count(*) INTO total from t1;
	INSERT INTO t1 values(total);
END$$

delimiter ;

存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

优势

效率高：存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本上），都要先分析编译才会执行。所以想对而言存储过程效率更高。
降低网络流量：存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。
复用性高：存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。
可维护性高：当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。
安全性高：完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。

创建存储过程

CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] `procedure_name` ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'  /* 描述信息 */
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC    /* 确定性 */
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }   /* 数据访问权限 */
  | SQL SECURITY { DEFINER | INVOKER }   /* SQL Security */
}

routine_body:
    Valid SQL routine statement

修改存储过程

ALTER PROCEDURE proc_name [characteristic ...]

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

调用存储过程

CALL `procedure_name`([proc_parameter[,...]]);

删除存储过程

在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

DROP PROCEDURE [IF EXISTS] `procedure_name`;

查看存储过程

查看数据库中所有的存储过程

SELECT * FROM information_schema.routines where ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_SCHEMA = 'database_name';

查看存储过程详情

SELECT * FROM information_schema.routines where ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_SCHEMA = 'database_name' and ROUTINE_NAME='procedure_name';

查看创建存储过程的语句

show create procedure `procedure_name`;

案例

创建存储过程

DELIMITER $$

CREATE PROCEDURE `students` (IN start INT, IN end INT)
BEGIN
	select * from t2 limit start, end;
END$$

CREATE PROCEDURE `studentCount` (IN first_name varchar(10), OUT total INT)
BEGIN
	select count(*) INTO total from t2 where name like CONCAT(first_name, '%');
END$$

DELIMITER ;

执行存储过程

mysql> CALL students(0, 1);
+----+------+
| id | name |
+----+------+
|  1 | a1   |
+----+------+
1 row in set (0.00 sec)

mysql> CALL studentCount('a1', @total);
Query OK, 1 row affected (0.00 sec)

mysql> select @total$$;
+--------+
| @total |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

删除存储过程

mysql> drop PROCEDURE studentCount;
Query OK, 0 rows affected (0.00 sec)

mysql> drop PROCEDURE students;
Query OK, 0 rows affected (0.00 sec)

函数

一组预先编译好的SQL语句集合，可以理解成批处理语句

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别

存储过程为第三方提供可以处理业务逻辑的接口，返回的是结果集；可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数为第三方提供可以处理业务逻辑的接口，返回单个值；有且只有一个返回，适合做处理数据后返回一个结果
视图为第三方提供接口，但是不能处理业务逻辑，返回结果集；可以有0个返回，也可以有多个返回，适合用来简化复杂的SQL查询

创建函数

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] `function_name` ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

修改函数

ALTER FUNCTION func_name [characteristic ...]

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

调用函数

SELECT `function_name`([func_parameter[,...]]);

查看函数

查看数据库中所有的存储过程

SELECT * FROM information_schema.routines where ROUTINE_TYPE = 'FUNCTION' and ROUTINE_SCHEMA = 'database_name';

查看存储过程详情

SELECT * FROM information_schema.routines where ROUTINE_TYPE = 'FUNCTION' and ROUTINE_SCHEMA = 'database_name' and ROUTINE_NAME='function_name';

查看创建存储过程的语句

show create function `function_name`;

删除函数

DROP FUNCTION [IF EXISTS] `function_name`;

案例

创建函数

DELIMITER $$

CREATE FUNCTION studentCount(first_name varchar(10))
RETURNS int
DETERMINISTIC
BEGIN 
  DECLARE total INT;
  select count(*) INTO total from t2 where name like CONCAT(first_name, '%');
  RETURN total;
END$$

DELIMITER ;

执行函数

mysql> SELECT studentCount('a1');
+--------------------+
| studentCount('a1') |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

删除函数

mysql> DROP FUNCTION studentCount;
Query OK, 0 rows affected (0.00 sec)

事务

mysql 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 mysql 中只有使用了 innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 sql 语句要么全部执行，要么全部不执行。
修改数据的命令会自动的触发事务，包括insert、update、delete

一般来说，事务是必须满足4个条件（acid）：：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

begin 或 start transaction 显式地开启一个事务；
commit 也可以使用 commit work，不过二者是等价的。commit 会提交事务，并使已对数据库进行的所有修改成为永久性的；
rollback 也可以使用 rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
savepoint identifier，savepoint 允许在事务中创建一个保存点，一个事务中可以有多个 savepoint；
release savepoint identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
rollback to identifier 把事务回滚到标记点；
set transaction 用来设置事务的隔离级别。 innodb 存储引擎提供事务的隔离级别有:
- read uncommitted(未提交读)
- read committed(提交读)
- repeatable read(可重复读)
- serializable(序列化)

mysql 事务处理方法

在 mysql 命令行的默认设置下，事务都是自动提交的，即执行 sql 语句后就会马上执行 commit 操作。因此要显式地开启一个事务有以下两种办法：

1、使用事务控制语句

begin 开始一个事务
rollback 事务回滚
commit 事务确认

2、直接用 set 来改变 mysql 的自动提交模式:

set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交

实例

下面具体演示MySQL事务的使用

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql> begin; # 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student values(0,"寒梅",28); # 插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
|  5 | 寒梅   |   28 |
+----+--------+------+
5 rows in set (0.00 sec)

mysql> rollback; # 回滚
Query OK, 0 rows affected (0.04 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql> insert into student values(0,"寒梅",28); # 插入数据
Query OK, 1 row affected (0.15 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
|  6 | 寒梅   |   28 |
+----+--------+------+
5 rows in set (0.00 sec)

mysql> insert into student values(5,"李磊",26);
Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(0,"张雷",20);
Query OK, 1 row affected (0.04 sec)

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

mysql> insert into student values(0,"韩美美",20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-----------+------+
| ID | name      | age  |
+----+-----------+------+
|  1 | 李华      |   25 |
|  2 | 敏柔      | NULL |
|  3 | 赵强      | NULL |
|  4 | 罗晴      |   23 |
|  5 | 李磊      |   26 |
|  6 | 寒梅      |   28 |
|  7 | 张雷      |   20 |
|  8 | 莉莉      |   20 |
|  9 | 韩美美    |   20 |
+----+-----------+------+
9 rows in set (0.00 sec)

mysql> rollback; # 回滚
Query OK, 0 rows affected (0.16 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
|  5 | 李磊   |   26 |
|  6 | 寒梅   |   28 |
|  7 | 张雷   |   20 |
|  8 | 莉莉   |   20 |
+----+--------+------+
8 rows in set (0.00 sec)

mysql> update student set age=23 where name="敏柔"; # 修改数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   |   23 |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
|  5 | 李磊   |   26 |
|  6 | 寒梅   |   28 |
|  7 | 张雷   |   20 |
|  8 | 莉莉   |   20 |
+----+--------+------+
8 rows in set (0.00 sec)

mysql> savepoint point1; # 创建保存点
Query OK, 0 rows affected (0.00 sec)

mysql> update student set age=22 where name="赵强";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   |   23 |
|  3 | 赵强   |   22 |
|  4 | 罗晴   |   23 |
|  5 | 李磊   |   26 |
|  6 | 寒梅   |   28 |
|  7 | 张雷   |   20 |
|  8 | 莉莉   |   20 |
+----+--------+------+
8 rows in set (0.00 sec)

mysql> rollback to point1; # 回滚到保存点
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   |   23 |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
|  5 | 李磊   |   26 |
|  6 | 寒梅   |   28 |
|  7 | 张雷   |   20 |
|  8 | 莉莉   |   20 |
+----+--------+------+
8 rows in set (0.00 sec)

mysql> commit; # 事务提交
Query OK, 0 rows affected (0.04 sec)

索引

在图书馆中是如何找到一本书的？

一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作)，

而且插入操作和更新操作很少出现性能问题，

遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重

当数据库中数据量很大时，查找数据会变得很慢优化方案：索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。

语法

查看索引

SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

删除索引

DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option] ...

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

实例

创建测试表testindex

create table test_index(title varchar(10));

使用python程序（ipython也可以）通过pymsql模块向表中加入十万条数据

from pymysql import connect

def main():
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,database='test_data',user='root',password='mysql',charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    # 插入10万次数据
    for i in range(10000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交数据
    conn.commit()

if __name__ == "__main__":
    main()

开启运行时间监测

set profiling=1;

查找第1万条数据ha-99999

select * from test_index where title='ha-99999';

查看执行的时间

show profiles;

为表title_index的title列创建索引

create index title_index on test_index(title);

执行查询语句

select * from test_index where title='ha-99999';

再次查看执行的时间

show profiles;

要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。

对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了
对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
建立索引会占用磁盘空间

用户

创建用户

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

参数说明

user:
    (username@host )

auth_option: {
    IDENTIFIED BY 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED BY RANDOM PASSWORD [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin [initial_auth_option]
}

2fa_auth_option: {
    IDENTIFIED BY 'auth_string' [AND 3fa_auth_option]
  | IDENTIFIED BY RANDOM PASSWORD [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 3fa_auth_option]
}

3fa_auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

initial_auth_option: {
    INITIAL AUTHENTICATION IDENTIFIED BY {RANDOM PASSWORD | 'auth_string'}
  | INITIAL AUTHENTICATION IDENTIFIED WITH auth_plugin AS 'auth_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
  | FAILED_LOGIN_ATTEMPTS N
  | PASSWORD_LOCK_TIME {N | UNBOUNDED}
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

修改用户信息

ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

ALTER USER [IF EXISTS]
    USER() user_func_auth_option

ALTER USER [IF EXISTS]
    user [registration_option]

ALTER USER [IF EXISTS]
    USER() [registration_option]

ALTER USER [IF EXISTS]
    user DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}

参数

auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED BY RANDOM PASSWORD
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | DISCARD OLD PASSWORD
  | ADD factor factor_auth_option [ADD factor factor_auth_option]
  | MODIFY factor factor_auth_option [MODIFY factor factor_auth_option]
  | DROP factor [DROP factor]
}

user_func_auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | DISCARD OLD PASSWORD
}

factor_auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

registration_option: {
    factor INITIATE REGISTRATION
  | factor FINISH REGISTRATION SET CHALLENGE_RESPONSE AS 'auth_string'
  | factor UNREGISTER
}

factor: {2 | 3} FACTOR

tls_option: 参数同上

resource_option: 参数同上

password_option: 参数同上
lock_option: 参数同上

删除用户

DROP USER [IF EXISTS] user [, user] ...

创建角色

本质上角色和用户是相同的，都是mysql.user表中的数据；不同之处在于无法通过角色连接数据库

CREATE ROLE [IF NOT EXISTS] role [, role ] ...

删除角色

DROP ROLE [IF EXISTS] role [, role ] ...

授予权限

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

GRANT PROXY ON user_or_role
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_or_role: {
    user
  | role
}

移除权限

REVOKE [IF EXISTS]
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]

REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTION
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]

REVOKE [IF EXISTS] PROXY ON user_or_role
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]

REVOKE [IF EXISTS] role [, role ] ...
    FROM user_or_role [, user_or_role ] ...
    [IGNORE UNKNOWN USER]

user_or_role: {
    user
  | role
}

查看用户信息

/* 查询用户信息及全局权限 */
select * from mysql.user where Host='host' and User='username';
/* 查询用户的数据库权限 */
select * from mysql.db where Host='host' and User='username';
/* 查询用户的表权限 */
select * from mysql.tables_priv where Host='host' and User='username';
/* 查询用户的字段权限 */
select * from mysql.columns_priv where Host='host' and User='username';
/* 查询用户的角色权限 */
select * from mysql.role_edges where TO_HOST='host' and TO_USER='username';
/* 查询用户的默认角色 */
select * from mysql.default_roles where HOST='host' and DEFAULT_ROLE_USER='username';

实例

创建用户

# 创建一个admin角色
CREATE ROLE 'admin'@'localhost';
# 创建一个worker角色
create role worker;

# 创建一个只能本地登录的test1用户
CREATE USER 'test1'@'localhost' identified by '!QAZ2wsx';

# 创建一个可以从任意远程主机登陆的用户test2
CREATE USER 'test2'@'%' identified by '!QAZ2wsx';

# 修改用户test2的密码
alter user 'test2'@'%' identified BY '@WSX3edc';

管理权限

/* grant 作用在整个 MySQL 服务器上 */
grant select on *.* to 'test2'@localhost; -- test2 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to 'test2'@localhost; -- 如果要授予所的权限则使用ALL, test2 可以管理 MySQL 中的所有数据库

/* grant 作用在单个数据库上 */
grant select on testdb.* to 'test2'@localhost; -- test2 可以查询 testdb 中的表。

/* grant 作用在单个数据表上 */
grant select, insert, update, delete on testdb.orders to 'test2'@localhost; 
-- 在给一个用户授权多张表时，可以多次执行以上语句 

/* grant 作用在表中的列上 */
grant select(id, se, rank) on testdb.apache_log to test2@localhost;

/* grant 作用在存储过程、函数上 */
grant execute on procedure testdb.pr_add to 'test2'@'localhost'
grant execute on function testdb.fn_add to 'test2'@'localhost'

/* grant 将用户权限授权给其他角色 */
grant 'root'@'localhost' to 'admin'@'localhost' WITH ADMIN OPTION; -- WITH ADMIN OPTION:允许admin将权限授权给其他用户
grant 'root'@'localhost' to worker  WITH ADMIN OPTION;

/* grant 将角色权限授权给其他用户 */
GRANT 'admin'@'localhost' TO 'test2'@'%';
GRANT 'worker' to 'test1'@'localhost' WITH ADMIN OPTION;

/* 将 角色设为默认角色，否则权限不生效 */
set default role 'admin'@'localhost' to 'test2'@'%';
set default role 'worker' to 'test1'@'localhost';

/* revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可 */
revoke all on *.* from test2@localhost;

# 刷新配置，立即启用修改
flush privileges;