1 MySQL常规操作

1.1 MySQL常用命令：

显示所有数据库：show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| besttest           |
| mysql              |
| performance_schema |
| test               |
| zll                |
+--------------------+
6 rows in set (0.00 sec)

选定默认数据库：use dbname;

显示默认数据库中所有表：show tables;

放弃正在输入的命令：\c

显示命令清单：\h

mysql> \h

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

退出mysql程序：\q

查看mysql服务器状态信息：\s

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using  EditLine wrapper

Connection id:          33
Current database:       mysql
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.36 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /application/mysql-5.6.36/tmp/mysql.sock
Uptime:                 1 day 2 hours 22 min 33 sec

Threads: 1  Questions: 260  Slow queries: 0  Opens: 88  Flush tables: 1  Open tables: 81  Queries per second avg: 0.002
--------------

1.2 建立数据库操作：

语法：create database 据库名

叙述：创建一个具有指定名称的数据库。如果要创建的数据库已经存在，或者没有创建它的适当权限，则此语句失败。例：建立一个besttest库：

mysql> create database besttest charset='utf8';
Query OK, 1 row affected (0.00 sec)

1.3 显示数据库结构：

使用MySQL命令show create database besttest;

可以查看student数据库的相关信息（例如MySQL版本ID号、默认字符集等信息）。

mysql> show create database besttest;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| besttest | CREATE DATABASE `besttest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.01 sec)

1.4 删除数据库操作：

语法：drop database 数据库名

叙述：删除数据库是指在数据库系统中删除已经存在的数据库。删除数据库之后，原来分配的空间将被收回。值得注意的是，删除数据库会删除该数据库中所有的表和所有数据、索引。因此，应该特别小心。例：删除besttest库。

 mysql> drop database besttest;

2 数据库存储引擎

2.1概念

MySQL中提到了存储引擎的概念。简而言之，存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。

存储引擎的概念是MySQL的特点，而且是一种插入式的存储引擎概念。这决定了MySQL数据库中的表可以用不同的方式存储。用户可以根据自己的不同要求，选择不同的存储方式、是否进行事务处理等。

MySQL提供了插件式（Pluggable）的存储引擎，存储引擎是基于表的，同一个数据库，不同的表，存储引擎可以不同。甚至同一个数据库表，在不同的场合可以应用不同的存储引擎。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show table status like 't'\G;
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-04-05 23:20:29
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_bin
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.12 sec)

mysql> show engines; 可查看MySQL服务实例支持的存储引擎

mysql> show table status like 't'\G; 查看某个表相关的信息。

2.2 InnoDB存储引擎

InnoDB 是MySQL数据库的一种存储引擎。InnoDB给MySQL的表提供了事务、回滚、崩溃修复能力、多版本并发控制的事务安全。InnoDB 是 MySQL 上第一个提供外键约束的表引擎。而且InnoDB对事务处理的能力，也是MySQL其他存储引擎所无法与之比拟的。

2.2.1 InnoDB存储引擎的特点:

支持外键（Foreign Key）
支持事务（Transaction）：如果某张表主要提供OLTP支持，需要执行大量的增、删、改操作（insert、delete、update语句），出于事务安全方面的考虑，InnoDB存储引擎是更好的选择。

2.2.2 InnoDB四大特性：

插入缓冲（insert buffer),
二次写(double write),
自适应哈希索引(ahi),
预读(read ahead)

2.2.3 InnoDB为什么推荐使用自增ID作为主键？

答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

2.3 MyISAM存储引擎

MyISAM存储引擎是MySQL中常见的存储引擎，曾是MySQL的默认存储引擎。MyISAM存储引擎是基于ISAM存储引擎发展起来的。

2.3.1 MyISAM缺点

MyISAM表不支持事务
MyISAM表不支持外键（Foreign Key）

注：Innodb是行级锁，myisam是表锁，所以现在数据库优化默认存储引擎直接就是改为innodb.

当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择innodb表。当你的数据库主要以查询为主，相比较而言更新和写入比较少，并且业务方面数据完整性要求不那么严格，就选择mysiam表。因为mysiam表的查询操作效率和速度都比innodb要快。

2.4 MEMORY存储引擎

MEMORY存储引擎是MySQL中的一类特殊的存储引擎。其使用存储在内存中的内容来创建表，而且所有数据也放在内存中，读写速度比前两种都要快，因为数据都是放在内存中，一般可以用来做一些临时表。

指定表的存储引擎： create table tmp(…)ENGINE=MEMORY;

设置默认存储引擎： set default_storage_engine=MEMORY;

这样是的临时的把存储引擎改了，mysql重启后会恢复默认的存储引擎，如果要永久修改，需要修改mysql的配置文件。

2.5 InnoDB和Memory的区别

create table t1(id int primary key, c int) engine=Memory;
create table t2(id int primary key, c int) engine=innodb;
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);

由下图可见，InnoDB 和 Memory 引擎的数据组织方式是不同的：

mysql> select *  from t1;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
|  7 |    7 |
|  8 |    8 |
|  9 |    9 |
|  0 |    0 |
+----+------+
10 rows in set (0.00 sec)

mysql> select *  from t2;
+----+------+
| id | c    |
+----+------+
|  0 |    0 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
|  7 |    7 |
|  8 |    8 |
|  9 |    9 |
+----+------+
10 rows in set (0.00 sec)

（1）InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。

（2）而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

这两个引擎的一些典型不同：

InnoDB 表的数据总是有序存放的，而Memory表的数据就是按照写入顺序存放的；
当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；
InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

可以看到，由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉。

其实 InnoDB 表性能还不错，而且数据安全也有保障。而内存表由于不支持行锁，更新语句会阻塞查询，性能也未必就如想象中那么好。

所以，一般建议把普通内存表都用 InnoDB 表来代替。但是，有一个场景却是例外的。在数据量可控，不会耗费过多内存的情况下，可以考虑使用内存表。

内存临时表刚好可以无视内存表的两个不足，主要是下面的三个原因：

临时表不会被其他线程访问，没有并发性的问题；
临时表重启后也是需要删除的，清空数据这个问题不存在；
备库的临时表也不会影响主库的用户线程。

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

其实这里使用内存临时表的效果更好，原因有三个：

相比于 InnoDB 表，使用内存表不需要写磁盘，往表 temp_t 的写数据的速度更快；
索引 b 使用 hash 索引，查找的速度比 B-Tree 索引快；
临时表数据只有 2000 行，占用的内存有限。

2.6 InnoDB和MyISAM的区别

InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；
Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了
MyISAM表格可以被压缩后进行查询操作
InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。
主键索引和辅助索引是独立的。

也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；
而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

2.6.1如何选择用InnoDB或MyISAM？

是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
系统奔溃后，MyISAM恢复起来更困难，能否接受；
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

3 MySQL性能问题

3.1 并发控制

DBMS 中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

3.1.1 悲观锁

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。这对于长事务来讲，可能会严重影响系统的并发处理能力。实现方式：使用数据库中的锁机制。

3.1.2 乐观锁

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁适用于 读多写少 的应用场景，这样可以提高吞吐量。实现方式：一般会使用版本号机制或 CAS 算法实现。

3.1.3 读写锁

锁的概念：

读锁：是共享的，或者说相互不阻塞的；多个用户在同一时刻可以同时读取同一个资源，而互不干扰。
写锁：是排他的，也就是说一个写锁会阻塞其他写锁和读锁。

3.1.4 锁粒度

锁粒度：一种提高共享资源并发性的方式就是让锁定对象更有选择性。

锁策略：在锁的开销和数据的安全性之间寻求平衡。一般都是在表上施加行锁（row-level lock），并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能提供更好的性能。

3.1.5 表锁

表锁：MySQL中最基本的锁策略，并且是开销最小的策略。它会锁定整张表，一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。但是，读锁的话，不受影响。

优先级：写锁比读锁有更高的优先级，因此，一个写锁请求可能会被插入到读锁队列的前面（写锁可以插入到锁队列中读锁的前面，反之读锁则不能插入到写锁的前面）。

3.1.6 行级锁

行锁：可以最大程度的支持并发处理（同时也带来最大的锁开销），在InnoDB和XtraDB中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。

3.1.7 锁的模式

从数据库系统的角度，锁模式可分为以下6 种类型：

1共享锁（他读锁）共享锁（S）：又叫他读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁时，所有的事务都不能对该数据进行修改，直到数据读取完成，共享锁释放。
2排他锁（写锁）排它锁（X）：又叫独占锁、写锁。对数据资源进行增删改操作时，不允许其它事务操作这块资源，直到排它锁被释放，从而防止同时对同一资源进行多重操作。
3 更新锁（更新数据）更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取再修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁就可以避免死锁的出现。

资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排它锁，否则变为共享锁。

4 意向锁（表级锁）意向锁：表示 SQL Server 需要在层次结构中的某些底层资源上获取共享锁或排它锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它锁。

意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁，而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。

5 架构锁（系统架构级别锁）架构锁：在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)，执行表的数据定义语言（DDL）操作（例如添加列或除去表）时使用架构修改锁，当编译查询时，使用架构稳定性锁。
6 大容量更新锁大容量更新锁（BU）：向表中大容量复制数据并指定了 TABLOCK 提示时使用。大容量更新锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。

3.2 事务

银行应用是解释事物的一个经典例子。假设一个银行的数据库有两张表：支票（checking）表和储蓄（savings）表，现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么需要三个步骤：

检查支票账户的余额高于200美元；
从支票账户余额中减掉200美元；
在储蓄账户余额中增加200美元；

START TRANSACTION;
select balance from checking where customer_id=10233276;
update checking set balance = balance - 200.00 where customer_id=10233276;
update savings set balance = balance + 200.00 where customer_id=10233276;
COMMIT;

事物特性：原子性、一致性、隔离性、持久性

3.2.1 原子性（atomicity）

原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚(rollback)，对于事物来说，不可能只执行其中某一部分操作，这就是事物的原子性。

3.2.2 一致性（consistency）

一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。

3.2.3 隔离性（isolation）

隔离性：通常来说，一个事物所做的修改在最终提交以前，对其他事物是不可见的。

3.2.4 持久性（durability）

持久性：一旦事物提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

3.2.5 隔离级别

隔离性远比想象的要复杂，SQL标准中定义了四种隔离级别，每一种级别都规定了一个事物中所做的修改，哪些在事物内和事物间是可见的，哪些是不可见的。级别较低的隔离通常可以执行更高的并发，系统的开销也就更低。

read uncommitted(未提交读)：即使没有提交，对其他事物也都是可见的。事物可以读取未提交的数据，这也被称为脏读（Dirty read）。

read committed（提交读）：大多数的数据库系统默认隔离级别都是read committed（但MySQL不是）。read committed满足隔离性的简单定义：一个事物开始时，只能“看见”已经提交的事物所做的修改。

repeattable read（可重复读）：可重复读解决了脏读（一个事务读取了另一个事务未提交的数据）的问题，该级别保证了同一个事物中多次读取同样记录的结果是一致的。但是，无法解决幻读（phantom read）的问题。所谓幻读，指的是当某个事物在读取某个范围内的记录时，另外一个事物又在该范围内插入新的记录，当之前的事物再次读取该范围的记录时，会产生幻行（phantom row）。 inNoDB解决了幻读的问题。

serializable（可串行化）：最高的隔离级别，它通过强制事物串行执行，避免了幻读的问题。简单来说，可串行化会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。

事物隔离级别与锁的关系

在读取未提交隔离级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突；

在读取已提交隔离级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在可重复读隔离级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁；

可串行化是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

3.2.6 死锁

死锁：是指两个或者多个事物在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事物试图以不同的顺序锁定资源时，就可能产生死锁，多个事物同时锁定同一资源时，也会产生死锁。

事务1
start transaction;
update stockprice set close =45.50 where stock_id=4 and date='2021-05-01';
update stockprice set close =19.80 where stock_id=3 and date='2021-05-02';
commit;

事务2
start transaction;
update stockprice set close =20.12 where stock_id=3 and date='2021-05-02';
update stockprice set close =47.20 where stock_id=4 and date='2021-05-01';
commit;

试问：如果凑巧，两个事物都执行了第一条update语句，更新了一行数据，同时也锁定了该行数据，接着每个事物都尝试着去执行第二条update语句，却发现该行已经被对方锁定，然后两个事物都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环，除非有外部因素介入才可能解除死锁。

解决：为解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。

方法1：越复杂的系统，如innoDB存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。这种解决方法非常有效，否则死锁会导致出现慢查询。
方法2：当查询的时间达到锁等待超时设定后放弃请求，这种方式通常来说不太好。 innodb目前处理死锁的方法是：将持有最少行级排他锁的事物进行回滚（简单的死锁回滚方式）。

死锁产生的原因：有些是因为真正的数据冲突，这种情况很难避免；有些则是由于存储引擎的实现方式导致的。

解锁：死锁产生后，只有部分或者完全回滚其中一个事物，才能打破死锁。

3.2.7 MySQL中的事务

MySQL提供两种事务型的存储引擎：innodb和NDB cluster。

自动提交：AUTOCOMMIT

mysql> show variables like 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set AUTOCOMMIT=1;

1或者ON代表启用，0或者OFF代表禁用。当AUTOCOMMIT=0时，所有查询都在一个事物中，直到显示执行commit提交或者rollback回滚，该事务结束，同时又开始了另一个新事务。

MySQL可以通过执行 set transaction isolation level 命令来设置隔离级别，新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别：

mysql> set session transaction isolation level read committed ;

3.2.8 回滚

回滚方式1

1、检查MySQL是否开启了binlog，设置了以下参数：

[mysqld]
server-id = 1
log_bin = /user/local/log/mysql/mysql-bin.log   # 设置此参数表示启用binlog功能，并指定路径名称
max_binlog_size = 1000M  # 文件的大小
binlog-format = row  # row模式下

执行SQL，若没有开启，则直接 set log_bin =ON;

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

2、检查 my.cnf文件的 log-bin目录，这个目录是记录回滚操作记录文件。

3、使用 #mysql binlog log-bin目录(如上2)/mysql-bin.000001 >/usr/local/log/mysql-bin-000001.sql 例如:#mysqlbinlog /usr/local/log/mysql-bin.000001 > /usr/local/log/mysql-bin-000001.sql 备注：(mysqlbinlog --start-position=120 --stop-position=2174941 操作点参数)

4、打开生成的 /usr/local/log/mysql-bin-000001.sql 文件，把里面的delete, create dababase,create table等无关操作需要删掉，自己一点点排查下，或用操作点--start-position 来过滤。

5、过滤完毕 /usr/local/log/mysql-bin-000001.sql 后执行 #mysql56 -uroot -p </usr/local/log/mysql-bin-000001.sql

6、查询表数据，已经回滚完成。

回滚方式2

使用开源工具binlog2sql进行回滚（github.com/danfengcao/…

3.3 短连接问题

正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。

短连接模型存在一个风险，就是一旦数据库处理得慢一些，连接数就会暴涨。max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。

show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

#方法1：临时方法，重启MySQL数据库则会失效
set global max_connections = 200;

#方法2：永久方法
修改mysql的ini配置文件my.ini
找到mysqld块，修改或者添加下面的设置：
max_connections=200

调高 max_connections 的值，这样做是有风险的。因为设计 max_connections 这个参数的目的是想保护 MySQL，如果我们把它改得太大，让更多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。

解决办法：

1、先处理掉那些占着连接但是不工作的线程:

设置 wait_timeout 参数表示的是，一个线程空闲 wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接。
show processlist，查询当前数据库线程情况，相同sleep状态下查看事物状态；
查看事务具体状态的话，查 information_schema 库的 innodb_trx 表。

mysql> select * from information_schema.innodb_trx\G;

结果里，trx_mysql_thread_id=4，表示 id=4 的线程还处在事务中。

2、重启数据库：

跳过权限验证的方法是：

重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。

3.4 慢查询性能问题

mysql> show variables  like '%slow_query_log%';
+---------------------+---------------------------------------------+
| Variable_name       | Value                                       |
+---------------------+---------------------------------------------+
| slow_query_log      | OFF                                         |
| slow_query_log_file | /application/mysql-5.6.36/data/ray-slow.log |
+---------------------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.04 sec)

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）。my.cnf要增加或修改参数slow_query_log 和slow_query_log_file，如下所示，然后重启MySQL使其生效。

slow_query_log = 1
slow_query_log_file = /tmp/mysql_slow.log

3.4.0 MySQL索引优化分析

性能下降的原因：

从程序员的角度

查询语句写的不好
没建索引，索引建的不合理或索引失效
关联查询有太多的join

从服务器的角度

服务器磁盘空间不足
服务器调优配置参数设置不合理

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。

mysql> explain select * from user where host='%';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | PRIMARY       | PRIMARY | 180     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.02 sec)

mysql>

id：这是SELECT的查询序列号
select_type：select_type就是select的类型，可以有以下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果。
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

table：显示这一行的数据是关于哪张表的
type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
possible_keys：列指出MySQL能使用哪个索引在该表中找到行
key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示使用哪个列或常数与key一起从表中选择行。
rows：显示MySQL认为它执行查询时必须检查的行数。
Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists:MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each
Record（index map:#）没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort:看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary:看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Using where:使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

总结

索引是排好序且快速查找的数据结构。其目的是为了提高查询的效率。
创建索引后，查询数据变快，但更新数据变慢。
性能下降的原因很可能是索引失效导致。
索引创建的原则，经常查询的字段适合创建索引，频繁需要更新的数据不适合创建索引。
索引字段频繁更新，或者表数据物理删除容易造成索引失效。
擅用 explain 分析sql语句
除了优化sql语句外，还可以优化表的设计。如尽量做成单表查询，减少表之间的关联。设计归档表等。

在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：

3.4.1 索引没有设计好；

这种场景一般就是通过紧急创建索引来解决。MySQL 5.6 版本以后，创建索引都支持 Online DDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。

比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：

在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
执行主备切换；这时候主库是 B，备库是 A。
在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。

3.4.2 SQL 语句没写好；

MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();

call query_rewrite.flush_rewrite_rules() 这个存储过程，是让插入的新规则生效，也就是我们说的“查询重写”。你可以用 show warnings 中的方法来确认改写规则是否生效。

3.4.3 MySQL 选错了索引。

应急方案就是给这个语句加上 force index；

3.4.4 预先发现问题

上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
在测试表里插入模拟线上的数据，做一遍回归测试；
观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致。
使用开源工具检查SQL语句 pt-query-digest(www.percona.com/doc/percona…

3.5 MySQL执行计划

-- 创建user表
create table user(
    id int,
    name varchar(20),
    role_id int,
    primary key(id)
)engine=innodb default charset=utf8;

-- 创建role表
create table role(
    id int,
    name varchar(20),
    primary key(id)
)engine=innodb default charset=utf8;

-- 查询执行计划
mysql> explain select * from user;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.09 sec)

3.5.1 id ，select 查询序列号

(1)、id相同，从上往下一次执行；

mysql> explain select * from user a left join user b on a.id=b.id;
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref      | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+
|  1 | SIMPLE      | a     | ALL    | NULL          | NULL    | NULL    | NULL     |    1 | NULL  |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | zll.a.id |    1 | NULL  |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+
2 rows in set (0.07 sec)

mysql> explain select * from user a right join user b on a.id=b.id;
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref      | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL     |    1 | NULL  |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | zll.b.id |    1 | NULL  |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+
2 rows in set (0.00 sec)

通过left join 和 right join 验证；id一样（注意执行计划的table列），left join 先扫描a表，再扫描b表；right join 先扫描b表，再扫描a表

(2)、id不同，id越大优先级越高，越先被执行

mysql> desc select * from user where role_id=(select id from role where name='开发');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | user  | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
|  2 | SUBQUERY    | role  | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.09 sec)

我们编写查询角色为开发的用户；可以知道先查询角色name为开发角色id，查询序列号为2；再根据角色id查询用户，查询序列号为1；

3.5.2 select_type，查询语句类型

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

-- 查询执行计划
mysql> explain select * from user;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.09 sec)

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

mysql> desc select * from user where role_id=(select id from role where name='开发');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | user  | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
|  2 | SUBQUERY    | role  | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

(3) UNION(UNION中的第二个或后面的SELECT语句)

mysql> desc select * from user where name='Java' union select * from user where role_id=1;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | user       | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where     |
|  2 | UNION        | user       | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where     |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set (0.06 sec)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

4 创建表

建立表操作：

语法：create table 表名（
列名1  列类型  [<列的完整性约束>],
列名2  列类型  [<列的完整性约束>], 
    ... ...   );

叙述：在当前数据库下新创建一个数据表。列类型：表示该列的数据类型。

例：建立一个表school,其由两列组成，第一列属性为非空，并做为主键,并自增

mysql> create table school(
    ->   school_id  int(10) not null auto_increment primary key,
    ->   school_name varchar(20) default  null,
    ->   phone int(10) unique
    -> );
Query OK, 0 rows affected (0.04 sec)

4.1主键和外键

主键：定义：唯一标识一条记录，不能有重复的，不允许为空，主键只能有一个。作用：用来保证数据完整性。

外键：定义：表的外键是另一表的主键, 外键可以有重复的, 可以是空值，一个表可以有多个外键。作用：用来和其他表建立联系用的。

举例：学生表(学号，姓名，性别，班级) 其中每个学生的学号是唯一的，学号是学生表的主键；成绩表(学号,成绩) 成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，成绩表中的学号是学生表的外键；

mysql> CREATE TABLE `student` (
    -> id INT (10) NOT NULL UNIQUE PRIMARY KEY ,
    -> name VARCHAR (20) not null,
    ->  sex    VARCHAR(4) ,
    ->  birth  YEAR,
    -> department VARCHAR(20) ,
    -> address VARCHAR(50)
    -> ) ;

mysql> CREATE TABLE `score` (
    -> id INT (11) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
    -> stu_id INT (11)  not NULL,
    ->  c_name VARCHAR(20) ,
    ->  grade INT(10),
    -> KEY `student` (`id`),
    -> CONSTRAINT `score_forge` FOREIGN KEY (`id`) REFERENCES `student` (`id`)
    -> );
Query OK, 0 rows affected (0.03 sec)

4.3常见的唯一性约束

PRIMARY  KEY 	   主码约束(主键)
UNIQUE		唯一性约束
NOT  NULL	非空值约束		
AUTO_INCREMENT	用于整数列默认自增1
UNSIGNED    无符号整数
DEFAULT default_value		默认值约束
DEFAULT cur_timestamp 创建新记录时默认保存当前时间（仅适用timestamp数据列）
ON UPDATE cur_timestamp 修改记录时默认保存当前时间（仅适用timestamp数据列）
CHARACTER SET name 指定字符集（仅适用字符串）

4.4查看表结构

desc 表名;

mysql> desc score;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| stu_id | int(11)     | NO   |     | NULL    |                |
| c_name | varchar(20) | YES  |     | NULL    |                |
| grade  | int(10)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

show create table 表名;

mysql> show create table score;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| score | CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(11) NOT NULL,
  `c_name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `grade` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `student` (`id`),
  CONSTRAINT `score_forge` FOREIGN KEY (`id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5 修改表

修改表是指修改数据库中已存在的表的定义。修改表比重新定义表简单，不需要重新加载数据，也不会影响正在进行的服务。MySQL中通过ALTER TABLE语句来修改表。修改表包括修改表名、修改字段数据类型、修改字段名、增加字段、删除字段、修改字段的排列位置、更改默认存储引擎和删除表的外键约束等。

5.1 修改表名

表名可以在一个数据库中唯一的确定一张表。数据库系统通过表名来区分不同的表。例如，数据库school中有student表。那么，student表就是唯一的。在数据库school中不可能存在另一个名为“student”的表。MySQL中，修改表名是通过SQL语句ALTER TABLE实现的。其语法形式如下：

ALTER  TABLE  旧表名  RENAME  [TO]  新表名 ;
Alter table school rename school2;

5.2 修改字段的数据类型MODIFY

字段的数据类型包括整数型、浮点数型、字符串型、二进制类型、日期和时间类型等。数据类型决定了数据的存储格式、约束条件和有效范围。表中的每个字段都有数据类型。MySQL中，ALTER TABLE语句也可以修改字段的数据类型。其基本语法如下：

ALTER  TABLE  表名  MODIFY  属性名  数据类型 ;
Alter table school modify school _name char(20);

5.3 修改字段的数据类型CHANGE

字段名可以在一张表中唯一的确定一个字段。数据库系统通过字段名来区分表中的不同字段。

MySQL中，ALTER TABLE语句也可以修改表的字段名。其基本语法如下：

ALTER  TABLE  表名  CHANGE  旧属性名  新属性名  新数据类型 ;
Alter table school change school_name name char(20);

5.4 modify和change的区别

Modify和change的区别：

1、能用change重命名，而modify不能。

2、modify能修改字段类型和约束，而change不能.

结论：change用来字段重命名，不能修改字段类型和约束； modify不用来字段重命名，只能修改字段类型和约束；

5.5 增加字段

在创建表时，表中的字段就已经定义完成。如果要增加新的字段，可以通过ALTER TABLE语句进行增加。MySQL中，ALTER TABLE语句增加字段的基本语法如下：

ALTER  TABLE  表名  ADD  属性名1   数据类型  [完整性约束条件]  [FIRST |  AFTER  属性名2] ;

增加无完整性约束条件的字段
增加有完整性约束条件的字段
表的第一个位置增加字段
表的指定位置之后增加字段

Alter table school add addr varchar(50) not null first;

5.6 增加外键

如果在定义表的时候没有创建外键，则可以通过alter语句来增加外键，mysql中增加外键的语句格式如下：

alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
alter table score add constraint FK_1 foreign key(student_id) REFERENCES student(id);

5.7 修改字段的排列位置

创建表的时候，字段在表中的排列位置就已经确定了。如果要改变字段在表中的排列位置，则需要ALTER TABLE语句来处理。MySQL中，修改字段排列位置的ALTER TABLE语句的基本语法如下：

ALTER  TABLE  表名  MODIFY  属性名1  数据类型  FIRST | AFTER  属性名2;

其中，“属性名1”参数指需要修改位置的字段的名称；“数据类型”参数指“属性名1”的数据类型；“FIRST”参数指定位置为表的第一个位置；“AFTER 属性名2”参数指定“属性名1”插入在“属性名2”之后。

字段修改到第一个位置
字段修改到指定位置

alter table school modify addr varchar(50) after school_name ;

5.8 删除字段

删除字段是指删除已经定义好的表中的某个字段。在表创建好之后，如果发现某个字段需要删除。可以采用将整个表都删除，然后重新创建一张表的做法。这样做是可以达到目的，但必然会影响到表中的数据。而且，操作比较麻烦。MySQL中，ALTER TABLE语句也可以删除表中的字段。其基本语法如下：

ALTER  TABLE  表名  DROP  字段名 ;
Alter table school drop addr;

5.9 清空表

清空表是指把表中现有数据全部清空，使用TRUNCATE关键字，truncate之后，自增长列的id也从建表时设置的起始id开始，和delete的区别是delete之后数据会清空，但是自增长列的id不会从起始id开始，truncat之后不能回滚，delete之后，可以回滚。需要注意的是，清空表的时候也需要遵守外键约束。

Truncate 表名;
Truncate students;

5.10 删除表

删除表是指删除数据库中已存在的表。删除表时，会删除表中的所有数据。因此，在删除表时要特别注意。MySQL中通过DROP TABLE语句来删除表。由于创建表时可能存在外键约束，一些表成为了与之关联的表的父表。要删除这些父表，情况比较复杂。

DROP TABLE 表名;
drop table school;

如果要删除一个有外键的表，是不能直接删除的，要先删除它关联的外键表.

5.11 更改表的存储引擎

MySQL存储引擎是指MySQL数据库中表的存储类型。

MySQL存储引擎包括InnoDB、MyISAM、MEMORY等。 在创建表的时候，存储引擎就已经设定好了。如果要改变，可以通过重新创建一张表来实现。这样做是可以达到目的，但必然会影响到表中的数据。而且，操作比较麻烦。MySQL中，ALTER TABLE语句也可以更改表的存储引擎的类型。其基本语法如下：

ALTER  TABLE  表名  ENGINE=存储引擎名 ;
Alter table school engine = MyISAM;

6 插入、更新与删除数据

数据库通过插入、更新和删除等方式来改变表中的记录。插入数据是向表中插入新的记录，通过INSERT语句来实现。更新数据是改变表中已经存在的数据，使用UPDATE语句来实现。删除数据是删除表中不再使用的数据，通过DELETE语句来实现。在这一章中将讲解的内容包括：

插入新纪录
更新数据
删除记录

6.1 插入数据

插入数据是向表中插入新的记录。通过这种方式可以为表中增加新的数据。MySQL中，通过INSERT语句来插入新的数据。使用INSERT语句可以同时为表的所有字段插入数据，也可以为表的指定字段插入数据。INSERT语句可以同时插入多条记录，还可以将一个表中查询出来的数据插入到另一个表中。

6.1.1为表的所有字段插入数据

通常情况下，插入的新记录要包含表的所有字段。INSERT语句有两种方式可以同时为表的所有字段插入数据。

第一种方式是不指定具体的字段名。
第二种方式是列出表的所有字段。
INSERT语句中不指定具体的字段名
INSERT语句中列出所有字段

insert into teacher values('001','大叔','11000000000');
insert into teacher values('002','老牛','11000000000');
insert into teacher values('003','猴子','11000000000');

6.1.2 为表的指定字段插入数据

前面的INSERT语句是指定插入全部字段值，第二种方式是插入指定字段值。其基本语句形式如下：

INSERT INTO 表名(属性1, 属性2, … , 属性m) VALUES(值1,值2, …, 值m);
insert into student (id,name,sex,address) values (1,"stu01","男","北京");
insert into student (id,name,sex,address) values (2,"stu02","男","上海");
insert into student (id,name,sex,address) values (3,"stu03","女","河北");
insert into student (id,name,sex,address) values (4,"stu04","女","天津");

6.1.3 同时插入多条记录

同时插入多条记录是指一个INSERT语句插入多条记录。当用户需要插入好几条记录，用户可以使用上面两个小节中的方法逐条插入记录。但是，每次都要写一个新的INSERT语句。这样比较麻烦。MySQL中，一个INSERT语句可以同时插入多条记录。其基本语法形式如下：

INSERT INTO 表名 [ (属性列表) ] 
VALUES(取值列表1),(取值列表2)
… ,
(取值列表n) ;
insert into score values(1,"1","mysql",50), (2,"2","loadruner",60), (3,"3","jmter",80), (4,"4","python",80);

6.1.4 将查询结果插入到表中

INSERT语句可以将一个表中查询出来的数据插入到另一表中。这样，可以方便不同表之间进行数据交换。其基本语法形式如下：

INSERT INTO 表名1  (属性列表1) 
SELECT 属性列表2 FROM 表名2  WHERE  条件表达式;
create table new_student like student;
insert into new_student select * from student;
select * from new_student;

6.1.5 使用replace插入新记录

replace语句的语法格式有三种语法格式。

语法格式1：replace into 表名 [（字段列表）] values （值列表）

语法格式2：replace [into] 目标表名[(字段列表1)] 
select (字段列表2) from 源表 where 条件表达式

语法格式3：
replace [into] 表名
set 字段1=值1, 字段2=值2

replace语句的功能与insert语句的功能基本相同，不同之处在于：使用replace语句向表插入新记录时，如果新纪录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除（注意：已有记录删除时也不能违背外键约束条件），然后再插入新记录。

使用replace的最大好处就是可以将delete和insert合二为一，形成一个原子操作，这样就无需将delete操作与insert操作置于事务中了。

replace into new_student values(1,"stu_replace","boy","SH");

6.2 更新数据

更新数据是更新表中已经存在的记录。通过这种方式可以改变表中已经存在的数据。例如，学生表中某个学生的家庭住址改变了，这就需要在学生表中修改该同学的家庭地址。MySQL中，通过UPDATE语句来更新数据。MySQL中，UPDATE语句的基本语法形式如下：

UPDATE 表名 
SET 属性名1=取值1, 属性名2=取值2,
 …, 
属性名n=取值n
WHERE 条件表达式;

update new_student set sex="girl" where id=1;

select * from new_student;

6.3 删除数据

删除数据是删除表中已经存在的记录。通过这种方式可以删除表中不再使用的记录。例如，学生表中某个学生退学了，这就需要从学生表中删除该同学的信息。MySQL中，通过DELETE语句来删除数据。MySQL中，DELETE语句的基本语法形式如下：

DELETE FROM 表名 [ WHERE 条件表达式 ] ;
delete from new_student where id=1;
select * from new_student;

delete from new_student; #删除表中全部数据
select * from new_student;

7 查询数据

查询数据指从数据库中获取所需要的数据。查询数据是数据库操作中最常用，也是最重要的操作。用户可以根据自己对数据的需求，使用不同的查询方式。通过不同的查询方式，可以获得不同的数据。MySQL中是使用SELECT语句来查询数据的。将讲解的内容包括：

查询语句的基本语法
在单表上查询数据
使用聚合函数查询数据
多表上联合查询
子查询
合并查询结果
为表和字段取别名
使用正则表达式查询

7.1 基本查询语句

查询数据是数据库操作中最常用的操作。通过对数据库的查询，用户可以从数据库中获取需要的数据。数据库中可能包含着无数的表，表中可能包含着无数的记录。因此，要获得所需的数据并非易事。MySQL中可以使用SELECT语句来查询数据。根据查询的条件的不同，数据库系统会找到不同的数据。通过SELECT语句可以很方便的获取所需的信息。

MySQL中，SELECT的基本语法形式如下：

SELECT  属性列表  
FROM  表名和视图列表
[ WHERE  条件表达式1 ]
[ GROUP BY  属性名1  [ HAVING 条件表达式2 ] ]
[ ORDER BY  属性名2  [ ASC | DESC ] ]

7.2 单表查询

单表查询是指从一张表中查询所需要的数据。查询数据时，可以从一张表中查询数据，也可以从多张表中同时查询数据。两者的查询方式上有一定的区别。因为单表查询只在一张表上进行操作，所以查询比较简单。本小节将讲解在单表上查询所有的字段、查询指定的字段、查询指定的行、多条件查询、查询结果不重复、给查询结果排序、分组查询和用LIMIT限制查询结果的数量等内容。

7.3 查询所有字段

查询所有字段是指查询表中所有字段的数据。这种方式可以将表中所有字段的数据都查询出来。MySQL中有两种方式可以查询表中所有的字段。

列出表的所有字段
使用“*”查询所有字段

select * from student;

7.4 查询指定字段

查询数据时，可以在SELECT语句的“属性列表”中列出所要查询的字段。这种方式可以指定需要查询的字段，而不需要查询出所有的字段，也可以通过as关键字来指定显示结果列的名字，一般来说as关键字可以省略，下面查询users表中id、name、sex和addr这四个字段的数据。SELECT语句的代码如下：

select name,sex from new_student;

7.5 查询指定记录

SELECT语句中可以设置查询条件。用户可以根据自己的需要来设置查询条件，按条件进行查询。查询的结果必须满足查询条件。例如，用户需要查找d_id为1001的记录，那么可以设置“d_id=1001”为查询条件。这样查询结果中的记录就都会满足“d_id=1001”这个条件。WHERE子句可以用来指定查询条件。其语法规则如下：

select * from 表名 WHERE  条件表达式
select * from new_student where id=2;

7.6 多表查询

多表查询是指从多张表中查询所需要的数据，一般查询的这几张表都有一个相同的字段关联这几张表。多表连接可以通过join关键字来连接，也可以直接用关联表中相同的id来进行关联；

Join： Left join：左连接，连接两张表，以左边表的数据匹配右边表中的数据，如果左边表中的数据在右边表中没有，会显示左边表中的数据。

Right join：右连接，连接两张表，以右边表的数据匹配左边表中的数据，如果左边表中的数据在左边边表中没有，会显示右边表中的数据。

Inner join：内连接，连接两张表，匹配两张表中的数据，和前面两个不同的是只会显示匹配的数据。

#为了证明，在左右表数据不一样的情况下的展示：
insert into score (stu_id,c_name,grade) values (5,"php",60);
#关联
select * from student a,score b where a.id=b.stu_id;
#左右关联
select * from student a left join score b on a.id = b.stu_id;
select * from student a right join score b on a.id = b.stu_id;

7.7 为表起别名

如果一个表名比较长，写的时候比较容易出错，可以给表名起一个别名，就可以使用别名来代表这个表，使用as关键字，或者不适用关键字，直接在表名后面跟一个别名。例如：

select a.name 学生姓名,b.score 学生成绩 from students a,score b where  a.id=b.student_id;
select a.name as 学生姓名,b.score as 学生成绩 from students as a,score as b where  a.id=b.student_id;

7.8 带IN关键字的查询

IN关键字可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中，则满足查询条件，该纪录将被查询出来。如果不在集合中，则不满足查询条件。其语法规则如下：

[ NOT ]  IN  ( 元素1, 元素2, …, 元素n ) 

select * from student where id in (1,2);
select * from student where id not in (1,2);

7.9 带OR的多条件查询

OR关键字也可以用来联合多个条件进行查询，但是与AND关键字不同。使用OR关键字时，只要满足这几个查询条件的其中一个，这样的记录将会被查询出来。如果不满足这些查询条件中的任何一个，这样的记录将被排除掉。OR关键字的语法规则如下：

条件表达式1  OR  条件表达式2  [ …OR  条件表达式n ]
其中，OR可以用来连接两个条件表达式。而且，可以同时使用多个OR关键字，这样可以连接更多的条件表达。
select * from student   where id=1  or id=2;
select * from student   where id!=1  or id!=2;

7.10 带AND的多条件查询

AND关键字可以用来联合多个条件进行查询。使用AND关键字时，只有同时满足所有查询条件的记录会被查询出来。如果不满足这些查询条件的其中一个，这样的记录将被排除掉。AND关键字的语法规则如下：

条件表达式1  AND  条件表达式2  [ … AND 条件表达式n ]
其中，AND可以连接两个条件表达式。而且，可以同时使用多个AND关键字，这样可以连接更多的条件表达式。
Select * from student where id >2  and sex = “男”;
Select * from student where id >3 and sex = "女" and address="天津";

7.11 查询结果剃重和限制查询条数

如果表中的某些字段上没有惟一性约束，这些字段可能存在着重复的值，可以使用distinct来剃重。例如，users表中的phone字段就存在着重复的情况。

select distinct grade from score;
# 如果查询结果过多，而只想取想要的条数，可以通过limit关键字来限制条数
select * from student limit 2;
select * from student limit 2,2;

7.12 带BETWEEN AND的范围查询

BETWEEN AND关键字可以判读某个字段的值是否在指定的范围内。如果字段的值在指定范围内，则满足查询条件，该纪录将被查询出来。如果不在指定范围内，则不满足查询条件。其语法规则如下：

[ NOT ]  BETWEEN 取值1  AND  取值2 
 select * from score where grade  between 70 and 90;
 select * from score where grade  not between 70 and 90;

7.13 带LIKE的字符匹配查询

LIKE关键字可以匹配字符串是否相等。如果字段的值与指定的字符串相匹配，则满足查询条件，该纪录将被查询出来。如果与指定的字符串不匹配，则不满足查询条件。其语法规则如下：

[ NOT ]  LIKE  '字符串' 
select * from student where name like '张_';
select * from student where name like '张%;
select * from student where name like '%三%';

7.14 查询空值

IS NULL关键字可以用来判断字段的值是否为空值（NULL）。如果字段的值是空值，则满足查询条件，该记录将被查询出来。如果字段的值不是空值，则不满足查询条件。其语法规则如下：

IS  [ NOT ]  NULL
其中，“NOT”是可选参数，加上NOT表示字段不是空值时满足条件。
insert into score (stu_id,c_name,grade) values ("6","",null);
select * from score where c_name= "";
select * from score where grade is null;
select * from score where grade not  null;

7.15 对查询结果排序

从表中查询出来的数据可能是无序的，或者其排列顺序不是用户所期望的顺序。为了使查询结果的顺序满足用户的要求，可以使用ORDER BY关键字对记录进行排序排序的方向可以是升序（asc）或降序（desc），使用order by字段名后不加参数的话，默认是升序，asc，其语法规则如下：

ORDER  BY  属性名  [ ASC | DESC ]
#升序
 select * from score order by grade;
 select * from score order by grade asc;
 #降序
select * from score order by grade desc;

7.16 使用聚合函数查询

聚合函数包括COUNT()、SUM()、AVG()、MAX()和MIN()。其中，COUNT()用来统计记录的条数；SUM()用来计算字段的值的总和；AVG()用来计算字段的值的平均值；MAX()用来查询字段的最大值；MIN()用来查询字段的最小值。当需要对表中的记录求和、求平均值、查询最大值、查询最小值等操作时，可以使用集合函数。例如，需要计算学生成绩表中的平均成绩，可以使用AVG()函数。GROUP BY关键字通常需要与集合函数一起使用。

7.16.1 COUNT()函数

COUNT()函数用来统计记录的条数。如果要统计employee表中有多少条记录，可以使用COUNT()函数。如果要统计employee表中不同部门的人数，使用count()对NULL值统计时，count()函数将忽略NULL值。sum()函数、avg()函数、max()以及min()函数等统计函数，统计数据时也将忽略NULL值。

SELECT  COUNT(*)  FROM  student;
select count(*)  学生人数 from student;

7.16.2 SUM()函数

SUM()函数是求和函数。使用SUM()函数可以求出表中某个字段取值的总和。例如，可以用SUM()函数来求学生的总成绩。

select sum(grade) 总成绩 from score;
select student.name 学生名称,sum(score.grade) 学生总成绩 from student,score where student.id = score.stu_id;

7.16.3 AVG()函数

AVG()函数是求平均值的函数。使用AVG()函数可以求出表中某个字段取值的平均值。例如，可以用AVG()函数来求平均年龄，也可以使用AVG()函数来求学生的平均成绩。

select student.name 学生名称,avg(score.grade) 学生总成绩 from student,score where student.id = score.stu_id;

7.16.4 MAX()函数

MAX()函数是求最大值的函数。使用MAX()函数可以求出表中某个字段取值的最大值。例如，可以用MAX()函数来查询最大年龄，也可以使用MAX()函数来求各科的最高成绩。

select student.name 学生名称,max(score.grade) 学生总成绩 from student,score where student.id = score.stu_id;

7.16.5 MIN()函数

MIN()函数是求最小值的函数。使用MIN()函数可以求出表中某个字段取值的最小值。例如，可以用MIN()函数来查询最小年龄，也可以使用MIN()函数来求各科的最低成绩。

select max(grade) 最高分,min(grade) 最低分 from score;

7.17 group by子句

GROUP BY关键字可以将查询结果按某个字段或多个字段进行分组。字段中值相等的为一组。其语法规则如下：

GROUP BY  属性名  [ HAVING 条件表达式 ] 
1．单独使用GROUP BY关键字来分组
2．GROUP BY关键字与集合函数一起使用
3．GROUP BY关键与HAVING一起使用
4．按多个字段进行分组

7.18 group by子句与聚合函数

例如把学生表中的男生和女生分成两组。
select * from student GROUP BY sex;

例如统计学生表中的男女生人数。
select count(*),sex from student group by sex;
 
Tips:group by 只会返回每个组中，第一个，数据

7.19 group by子句与having子句

having子句用于设置分组或聚合函数的过滤筛选条件，having子句通常与group by子句一起使用。having子句语法格式与where子句语法格式类似，having子句语法格式如下。

Having 条件表达式
其中条件表达式是一个逻辑表达式，用于指定分组后的筛选条件。

例如查询索隆班男女学生的人数。
select * from student group by sex having name="stu03";

7.20 合并结果集

使用union可以将多个select 语句的查询结果集组合成一个结果集。

select 字段列表1  from  table1 
union [all]
select 字段列表2   from  table2...
说明：字段列表1与字段列表2的字段个数必须相同，且具有相同的数据类型。合并产生的新结果集的字段名与字段列表1中的字段名对应。

比如有学生表和教师表，要查出来所有的学生和教师，就可以用union，连接起来两个sql
select name from student union select name from teacher;

union 与 union all 的区别：

当使用union时，MySQL 会筛选掉select结果集中重复的记录（在结果集合并后会对新产生的结果集进行排序运算，效率稍低）。
而使用union all时，MySQL会直接合并两个结果集，效率高于union。如果可以确定合并前的两个结果集中不包含重复的记录，建议使用union all。

7.21 子查询

如果一个select语句能够返回单个值或者一列值，且该select语句嵌套在另一个SQL语句中（例如select语句、insert语句、update语句或者delete语句）中，那么该select语句称为子查询（也叫内层查询），包含子查询的SQL语句称为主查询（也叫外层查询）。为了标记子查询与主查询之间的关系，通常将子查询写在小括号内。

子查询一般用在主查询的where子句或having子句中，与比较运算符或者逻辑运算符一起构成where筛选条件或having筛选条件。这些比较运算符包括=、!=、>、>=、<、<=、<>等。其中，<>与!=是等价的。比较运算符在子查询时使用的非常广泛。如查询分数、年龄、价格、收入等。

比如说要把成绩低于60分的学生的名字都改成笨蛋
update student set name ="stupid" where id in (select a.stu_id from score a where a.grade < 60);

查询所有分数大于60的人用where in 字句
select * from student where id in (select stu_id from score where grade >60);

7.22 比较运算符

比较运算符是查询数据时最常用的一类运算符。SELECT语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。

运算符“=”
运算符“<>”和“!=”
运算符“>”
运算符“>=”
运算符“<”
运算符“<=”

8 为数据库授权

授权就是为某个用户赋予某些权限。例如，可以为新建的用户赋予查询所有数据库和表的权限。合理的授权能够保证数据库的安全。不合理的授权会使数据库存在安全隐患。MySQL中使用GRANT关键字来为用户设置权限。

Grant 命令的格式
grant  权限  on  数据库对象  to  用户
比如说增加一个超级用户，拥有所有的权限，只允许本地登录
Grant all on *.* to 'andashu'@'localhost' IDENTIFIED BY '123456‘with grant option;
增加一个普通用户，只有对bugfree数据库查询的修改的权限，允许远程登录
Grant select, insert,update on bugfree.* to 'tester'@'%' IDENTIFIED BY '123456';
增加一个超级用户，限制登录ip为192.168.1.101
Grant all privileges on *.* to dba@‘192.168.1.101' IDENTIFIED BY '123456';
增加一个只有查询bugfree的bug表中的bugtitle的用户
Grant select(title) on bugfree.bf_bug_info to 'bugfree'@'%' IDENTIFIED BY '123456';

8.1 取消数据库用户权限

取消权限用revoke 关键字
revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可
Revoke 权限 on 数据库对象 from 用户

比如要取消andashu用户的超级权限
Revoke all on *.* from andashu@localhost;

取消dba用户查询权限
Revoke select on *.* from dba@localhost;

8.2 为数据库用户设置密码

使用set password
set password for tester= password("123456");

使用update语句
Update user set password=password('123456') where user='tester';

删除用户
Delet from user where user=tester;

9 数据库的备份与恢复

9.1 使用mysqldump命令备份

mysqldump命令可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。 mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。这些CREATE语句和INSERT语句都是还原时使用的。还原数据时就可以使用其中的CREATE语句来创建表。使用其中的INSERT语句来还原数据。

备份一个数据库
备份多个数据库
备份所有数据库

9.2 mysqldump

mysqldump命令可以备份数据库中的数据。但是，备份时是在备份文件中保存了CREATE语句和INSERT语句。不仅如此，mysqldump命令还可以导出文本文件。其基本的语法形式如下：

mysqldump -uuser -pPassword dbname table [option] > xx.sql;
mysqldump -uroot -proot 0429mysql student > student.sql

Mysqldump常用参数：
--all-databases  , -A 导出全部数据库
--add-drop-database 每个数据库创建之前添加drop数据库语句
--no-data, -d 不导出任何数据，只导出数据库表结构。
--no-create-db,  -n只导出数据，而不添加CREATE DATABASE 语句。
--no-create-info,  -t 只导出数据，而不添加CREATE TABLE 语句。

9.3 恢复数据

9.3.1 执行MySQL命令恢复数据

恢复刚才备份的bugfree数据库中的数据
mysql –ulepus –p123456 bugfree < bugfree.sql
恢复刚才备份的所有数据库的数据
mysql –ulepus –p123456 < all.sql

这种方式除了恢复数据还可以用来批量执行sql，比如说有一个比较大的sql文件需要执行，在可视化工具中打开再去执行就比较麻烦，可以使用这种方式来执行。

9.3.2 写一个自动备份数据库的shell脚本:

在学习linux的时候我们已经学习过怎么写shell脚本，现在我们要每天的1点自动备份一下所有的数据库，把备份的文件已当天的日期命名，放到/usr/local/db_back目录下

#!/bin/bash
file_name=`date +%Y%m%d%H%M%S`
db_path='/usr/local/db_back/'
mysqldump -ulepus -p123456 -A > ${db_path}${file_name}_all.sql
echo "backup ok"

10 索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引。在这一章中将讲解的内容包括：

索引的含义和特点
索引的分类
如何设计索引
如何创建索引
如何删除索引

10.1 索引简介

索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。本小节将详细讲解索引的含义、作用、优缺点。索引有起明显的优势，也有其不可避免的缺点。

索引的优点:

增加查询速度;
利用索引的唯一性来控制记录的唯一性;
降低查询中分组和排序的时间;
可以加速表与表之间的连接 ;

索引的缺点:

存储索引占用磁盘空间;
执行数据修改操作(INSERT、UPDATE、DELETE)产生索引维护;
每次修改表结构都需要重建索引;

10.2 索引的分类

MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引等。本小节将详细讲解这几种索引的含义和特点。

普通索引:这是最基本的索引，它没有任何限制。
惟一性索引:与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）
全文索引:MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；
单列索引、多列索引

****Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

  PRIMARY KEY（主键索引）  ALTER TABLE `table_name` ADD PRIMARY KEY ( `col` ) 

　　UNIQUE(唯一索引)     ALTER TABLE `table_name` ADD UNIQUE (`col`)

　　INDEX(普通索引)     ALTER TABLE `table_name` ADD INDEX index_name (`col`)

　　FULLTEXT(全文索引)      ALTER TABLE `table_name` ADD FULLTEXT ( `col` )

　　组合索引   ALTER TABLE `table_name` ADD INDEX index_name (`col1`, `col2`, `col3` )

Mysql各种索引区别 ：
普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它是一种特殊的唯一索引，不允许有空值。
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率） 作限制条件的列放在最左边，依次递减。

10.3 创建索引

创建索引的语法格式：

创建普通索引：
create index 索引名称 on  表名(列)
alter table 表名 add  index 索引名称  (列)

创建唯一索引：
create unique index  索引名称 on 表名(列名）
alter table 表名 add  unique index 索引名称  (列)

例子：
# 给student 表的 phone加上唯一索引:
Create unqiue index st_phone on student(phone);

# 给student表的name加上普通索引:
Create index st_name on student(name);

10.4 删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。对应已经存在的索引，可以通过DROP语句来删除索引。基本形式如下：

DROP  INDEX  索引名  ON  表名 ;
drop index st_name on student;

10.5 索引设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。本小节将向读者介绍一些索引的设计原则。

选择惟一性索引
为经常需要排序、分组和联合操作的字段建立索引
为常作为查询条件的字段建立索引
限制索引的数目
尽量使用数据量少的索引
尽量使用前缀来索引
删除不再使用或者很少使用的索引

11 视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用看到整个数据库表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。在这一章中将讲解的内容包括：

视图的含义和作用
如何创建视图
如何修改视图
如何查看视图
如何删除视图

11.1 视图的含义

视图是一种虚拟的表。视图从数据库中的一个或多个表导出来的表。视图还可以从已经存在的视图的基础上定义。数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

11.2 视图的作用

视图是在原有的表或者视图的基础上重新定义的虚拟表，这可以从原有的表上选取对用户有用的信息。那些对用户没有用，或者用户没有权限了解的信息，都可以直接屏蔽掉。这样做既使应用简单化，也保证了系统的安全。视图起着类似于筛选的作用。视图的作用归纳为如下几点：

使操作简单化
增加数据的安全性
提高表的逻辑独立性

11.3 创建视图

创建视图的语法格式
Create view 视图名称（视图列1，视图列2） as select 语句

#创建一个TV视图，只显示name,sex
create view tv(name,sex)as select name,sex from student;
Select * from tv;

# 创建一个视图，显示bug表中的bugid，bug标题，bug状态，bug创建者，归属产品，多表视图
create view tv02 (name,grade) as select a.name,b.grade from student a,score b where a.id=b.stu_id;
select * from tv02;

11.4 查看视图

查看视图是指查看数据库中已存在的视图的定义。查看视图必须要有SHOW VIEW的权限，mysql数据库下的user表中保存着这个信息。查看视图的方法包括DESCRIBE语句、SHOW TABLE STATUS语句、SHOW CREATE VIEW语句和查询information_schema数据库下的views表等。本节将详细讲解查看视图的方法。

Desc 视图名称
Desc bug_view;

Show table status;
Show create view 视图名称
Show create view bug_view;

# information_schema数据库中查看所有数据库下的视图
SELECT  *  FROM  information_schema.views ;

11.5 修改视图

CREATE OR REPLACE VIEW 语句修改

语法格式：
CREATE OR REPLACE VIEW 视图名称 (列1,列2) as select语句

示例，把上面创建的视图修改成只显示10条的bug标题：
create or replace view tv02(name,grade) as select a.name,b.grade from student a,score b where a.id=b.stu_id limit 1;

Alter view 语句修改
语法格式
Alter view 视图名称(列1,列2) as select语句

示例，把上面创建的视图修改成只显示15条的bugid和标题：
alter view tv02(name,grade) as select a.name,b.grade from student a,score b where a.id=b.stu_id limit 1;

11.6 删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。MySQL中，使用DROP VIEW语句来删除视图。但是，用户必须拥有DROP权限。本节将详细讲解删除视图的方法。对需要删除的视图，使用DROP VIEW语句进行删除。基本形式如下：

DROP  VIEW  [ IF EXISTS]  视图名列表
举例，删除上面创建的两个视图
DROP  VIEW IF EXISTS tv02,tv;

12 存储过程和函数

存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。

12.1 概念

创建存储过程和函数是指将经常使用的一组SQL语句的组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。例如，银行经常需要计算用户的利息。不同类别的用户的利率是不一样的。这就可以将计算利率的SQL代码写成一个存储过程或者存储函数。只要调用这个存储过程或者存储函数，就可以将不同类别用户的利息计算出来。本节将向读者介绍创建存储过程和函数的方法，并且将讲解如何定义变量、如何使用流程控制。

12.2 创建存储过程

MySQL中，创建存储过程的基本形式如下：

delimiter $$;
CREATE PROCEDURE 名称(参数列表)
BEGIN
SQL语句块
End
$$;
delimiter;

注意：由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT

例子：

delimiter $;
CREATE PROCEDURE test_p03()
begin
Select * from student limit 2;
End
$;
Delimiter ;
Call test_p03;

Tips:注意Delimiter后的空格

12.3 存储过程中变量的定义、参数类型和传参

变量定义：
declare 变量名 数据类型 default 默认值

参数类型：
    in参数
    表示该参数的值必须在调用存储过程之前指定，在存储过程中修改的值不能被返回，也就是调用的时候就得指定，默认传入的就是in参数

    Out参数
    该值可在存储过程内部改变，并可以返回.往往是用于获取存储过程里的参数值。
 
    Inout参数
    该值可以在调用时指定，并可修改和返回。
  
传参 :
CREATE PROCEDURE test_p(in 参数名 参数类型,out 参数名 参数类型)

调用：
Call 存储过程名

12.4 存储过程定义变量实例

delimiter $$;
create procedure test_p5(id int,phone int) #传参两个
begin
declare s_name varchar(12); #定义一个变量，没有默认值
declare sex varchar(10) default '男'; #定义变量赋默认值
set id = id+1; 
set s_name = '大叔'; #变量复制
insert into students values (id,s_name,phone,sex);
end
$$;
delimiter;

12.5 存储过程in参数实例

delimiter $;
create procedure test_p5(id int)
begin
declare s_name varchar(12);
declare sex varchar(10) default '男';
set id = id+1;
set s_name = '大叔';
insert into student (id,name,sex) values (id,s_name,sex);
end
$;
Delimiter ;

set @id=10;
call test_p5(@id);
select * from student;
select id;

12.6 存储过程out参数实例

delimiter $$;
create procedure test_p7(id int,out phone int)
begin
declare s_name varchar(12);
declare sex varchar(10) default '男';
set id = id+1;
Select phone;
set phone = 186125312;
set s_name = '安大叔';
insert into students values (id,s_name,phone,sex);
end
$$;
delimiter;

set @phone=99888;
call test_p7(68,@phone);
select * from students;
select @phone;

12.7 存储过程inout参数实例

delimiter $$;
create procedure test_p8(id int,out phone int,inout s_name varchar(20))
begin
declare sex varchar(10) default '男';
set id = id+1;
set phone = 186125312;
select s_name;
set s_name = 'BESTTEST';
insert into students values (id,s_name,phone,sex);
end
$$;
delimiter;

set @phone=99888;
set @s_name='besttest'
call test_p8(70,@phone,@s_name);
select * from students;
select @s_name;

12.8 存储过程中的if条件判断

if语句格式：
if 条件 then
语句
elseif  条件 then
语句
else
语句
end if;

# 存储过程if判断实例
现在有一个best表，里面有班级和学生id，来写一存储过程实现插入小于100的学号班级是以前班，大于等于100的是乔班，大于等于200的是隆班
create table best (s_no int,class varchar(20));#创建表
delimiter $$;
create procedure test_p9(s_no int)
begin
declare class_name varchar(10);
if s_no < 100 then
set class_name='前班';
elseif s_no>=100 and s_no<200 then
set class_name='乔班';
else
set class_name='隆班';
end if;
insert into best values (s_no,class_name);
end
$$;
delimiter;
call test_p9(100)

12.9 存储过程中的case条件判断

case语句格式：
case value
when 条件 then
sql语句
when 条件2 then
sql语句
else#如果上面条件都不满足的话执行
sql语句
end case

#存储过程case判断实例
现在有一个orders表，里面有id和订单状态，0代表未支付，1代表已支付，2代表已发货，3代表已收货，4代表已完成，其他值代表未知状态

create table orders (id int, status varchar(20));
delimiter $$;
create procedure test_p10(order_id int)
begin
declare status varchar(20);
case oder_id
when 0 then
set status='未支付';
when 1 then
set status='已付款';
when 2 then
set status='已发货';
when 3 then
set status='已收货';
when 4 then
set status='已完成';
else
set status='未知';
end case;
insert into orders values (order_id,status);
end
$$;
delimiter;

12.10 存储过程中的while循环

while是一种循环的语句，我们经常使用while来造数据
while语句格式：
while 条件 do
sql语句
end while;

delimiter $;
create procedure test_p11(count int)
begin
declare name varchar(20);
declare id int;
set id= 0;
while id<count do
set name=CONCAT('安大叔',id);
insert into todata  values (id,name);
set id=id+1;
end while;
end
$;
Delimiter ;


call test_p11(500);
select count(*) from todata;

12.11 存储过程中的repeat循环

repeat和while一样，也是一种循环的语句，和while不一样的事，repeat循环至少会执行一次。 repeat语句格式：

repeat 
sql语句
until 条件
end repeat;

delimiter $$;
create procedure test_p12(count int)
begin
declare name varchar(20);
declare sex varchar(10);
declare phone int(20);
declare i int;
set i = 0;
set sex='男';
repeat
set name=CONCAT('Cccccc',i);
set phone=18612545+i;
insert into students (name,phone,sex)values (name,phone,sex);
set i=i+1;
until i>count
end repeat;
end
$$;
delimiter;

call test_p12(500);
select count(*) from students;

12.12 查看存储过程和函数

show status语句
show procedure status;
show function status;

show create 语句
show create procedure 存储过程名称;
show create function 函数名称;

从information_schema查看
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=‘存储过程/函数名称' ;

13 函数

函数和存储过程类似，区别是函数有返回值，存储过程没有返回值。定义函数的格式如下：

create function 函数名( 变量1，变量2.....)
 returns 数据类型
begin
   ......执行的程序代码
  return 数据;
end;

13.1 函数实例

写一个函数，实现传入学生名称，返回学生id；

delimiter $$;
create FUNCTION get_stuid(s_name varchar(20)) 
RETURNS int 
begin
declare num int;
select id from students where name=s_name into num;
return num;
end
$$;
delimiter ;

调用 select get_stuid('大叔');

13.2 函数格式

函数和存储过程类似，区别是函数有返回值，存储过程没有返回值。定义函数的格式如下：

create function 函数名( 变量1，变量2.....)
 returns 数据类型
begin
   ......执行的程序代码
  return 数据;
end;

13.3 删除函数

删除存储过程和函数指删除数据库中已经存在的存储过程和函数。MySQL中使用DROP PROCEDURE语句来删除存储过程。通过DROP FUNCTION语句来删除存储函数。其基本形式如下：

DROP { PROCEDURE| FUNCTION } sp_name;

14 触发器

触发器是由INSERT、UPDATE和DELETE等事件来触发某种特定操作。满足触发器的触发条件时，数据库系统就会执行触发器中定义的程序语句。这样做可以保证某些操作之间的一致性。例如，当学生表中增加了一个学生的信息时，学生的总数就必须同时改变。可以在这里创建一个触发器，每次增加一个学生的记录，就执行一次计算学生总数的操作。这样就可以保证每次增加学生的记录后，学生总数是与记录数是一致的。触发器触发的执行语句可能只有一个，也可能有多个。

14.1 创建触发器

MySQL中，触发器触发的执行语句可能有多个。创建有多个执行语句的触发器的基本形式如下：

CREATE  TRIGGER  触发器名  BEFORE | AFTER  触发事件
ON  表名  FOR  EACH  ROW  
BEGIN
执行语句列表
END;

14.2 触发器实例

在students表中插入一条数据后，把这条数据同步到students的备份表中

先创建一个备份表 create table stu_bak like students;
delimiter $$;
create trigger bak_stdents after insert
on students for each row
begin
insert into stu_bak select * from students where id=new.id;
end;
$$;
delimiter ;
insert into students (name,phone,sex) values ('niuhy','187122342','nan');

在students表中删除一条数据后，从score表中也把它的成绩删除
delimiter $$;
create trigger del_score after delete
on students for each row
begin
delete from socre where student_id=old.id;
end;
$$;
delimiter ;
Delet from studens where id =1;

在students表中更新一条数据后，把最新的数据更新到stu_bak表中。
delimiter $$;
create trigger update_stu_bak after update
on students for each row
begin
update stu_bak set id=new.id,name=new.name,phone=new.phone,sex=new.sex where id=old.id;
end;
$$;
delimiter ;
update students set name='大叔1',sex='不知道' where name='大叔';

14.3 查看触发器

show triggers语句
show triggers;

show create 语句
show create trigger 触发器名;

从information_schema查看
SELECT * FROM information_schema.triggers where TRIGGER_NAME='存储过程名称';

14.4 删除触发器

删除触发器指删除数据库中已经存在的触发器。MySQL中使用DROP TRIGGER语句来删除触发器。其基本形式如下：

DROP  TRIGGER  触发器名 ;

15 事件

MySQL5.1开始提供了一种新的存储代码：事件（event）。它类似与定时任务（crontab），但内部机制却完全不同。你可以创建事件，它会在某个特定时间或时间间隔执行一次预先写好的SQL代码。通常的方式就是将复杂的SQL语句包装到一个存储过程中，然后调用一下即可。

事件不接受参数，也不返回值，也没有任何连接让它们可以得到输入或返回输出。

Linux 中的 crontab只能精确到每分钟执行一次，而 MySQL 的事件调度器可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下非常实用。

使用事件的话，需要开启事件，可以用
show VARIABLES like '%event_scheduler%';

查看事件功能是否开启
如果没有开启可以使用下面的语句开启
set global event_scheduler=1;

15.1事件语法格式

语法格式
create event 事件名称 on schedule 执行频率
Starts 开始时间
ends 停止时间 
do
Sql语句

15.2 事件实例

有一张临时表stu_tmp,写一个事件实现每30分钟删一下这个表里的数据

delimiter $$;
create event del_tmp on schedule every 30 MINUTE
do
delete from stu_tmp;
$$;
delimiter ;

15.3 事件实例-指定结束时间

delimiter $$;
create event del_tmp1 on schedule every 20 second
ENDS CURRENT_TIMESTAMP + INTERVAL 1 minute
do
delete from stu_tmp;
$$;
delimiter ;

15.4 事件实例-指定开始和结束时间

delimiter $$;
create event del_tmp3 on schedule every 20 second
starts CURRENT_TIMESTAMP + INTERVAL 1 minute
ENDS CURRENT_TIMESTAMP + INTERVAL 2 minute
do
delete from stu_tmp;
$$;
delimiter ;

15.5 事件实例-指定什么时候运行

delimiter $$;
create event del_tmp3 on schedule every 20 second
starts CURRENT_TIMESTAMP + INTERVAL 1 minute
ENDS CURRENT_TIMESTAMP + INTERVAL 2 minute
do
delete from stu_tmp;
$$;
delimiter ;

15.6 查看事件

show event语句
show triggers;

show create 语句
show create event 事件名;

从information_schema查看
select * from information_schema.EVENTS;

15.7 删除事件

Drop event 事件名称;

MySQL基础

1 MySQL常规操作

1.1 MySQL常用命令：

1.2 建立数据库操作：

1.3 显示数据库结构：

1.4 删除数据库操作：

2 数据库存储引擎

2.1概念

2.2 InnoDB存储引擎

2.2.1 InnoDB存储引擎的特点:

2.2.2 InnoDB四大特性：

2.2.3 InnoDB为什么推荐使用自增ID作为主键？

2.3 MyISAM存储引擎

2.3.1 MyISAM缺点

2.4 MEMORY存储引擎

2.5 InnoDB和Memory的区别

2.6 InnoDB和MyISAM的区别

2.6.1如何选择用InnoDB或MyISAM？

3 MySQL性能问题

3.1 并发控制

3.1.1 悲观锁

3.1.2 乐观锁

3.1.3 读写锁

3.1.4 锁粒度

3.1.5 表锁

3.1.6 行级锁

3.1.7 锁的模式

3.2 事务

3.2.1 原子性（atomicity）

3.2.2 一致性（consistency）

3.2.3 隔离性（isolation）

3.2.4 持久性（durability）

3.2.5 隔离级别

事物隔离级别与锁的关系

3.2.6 死锁

3.2.7 MySQL中的事务

3.2.8 回滚

3.3 短连接问题

1、先处理掉那些占着连接但是不工作的线程:

2、重启数据库：

3.4 慢查询性能问题

3.4.0 MySQL索引优化分析

性能下降的原因：

总结

3.4.1 索引没有设计好；

3.4.2 SQL 语句没写好；

3.4.3 MySQL 选错了索引。

3.4.4 预先发现问题

3.5 MySQL执行计划

3.5.1 id ，select 查询序列号

3.5.2 select_type，查询语句类型

4 创建表

4.1主键和外键

4.3常见的唯一性约束

4.4查看表结构

5 修改表

5.1 修改表名

5.2 修改字段的数据类型MODIFY

5.3 修改字段的数据类型CHANGE

5.4 modify和change的区别

5.5 增加字段

5.6 增加外键

5.7 修改字段的排列位置

5.8 删除字段

5.9 清空表

5.10 删除表

5.11 更改表的存储引擎

6 插入、更新与删除数据

6.1 插入数据

6.1.1为表的所有字段插入数据

6.1.2 为表的指定字段插入数据

6.1.3 同时插入多条记录

6.1.4 将查询结果插入到表中

6.1.5 使用replace插入新记录

6.2 更新数据

6.3 删除数据

7 查询数据

7.1 基本查询语句

7.2 单表查询

7.3 查询所有字段