MySQL架构篇

大厂面试问题

存储引擎的InnoDB与MyISAM的区别，优缺点，使用场景？

说说MySQL 优化之道？

UndoLog和RedoLog的区别和联系？

MySQL索引的数据结构是什么，及为什么使用这种数据结构？

索引失效的场景有哪些？

什么是死锁和死锁的排查和解决？

RC和RR的实现原理及区别和使用场景？

分库与分表带来的分布式困境与应对之策？

。。。。。

逻辑架构

架构逻辑图

MySQL将数据存储到硬盘中，MySQL就是如何操作硬盘上的文件

客户端是提出需求的，SQL层是对SQL请求进行一步一步处理，而可插拔式存储引擎层是用来将数据从文件系统中存储或者查询的

SQL Interface

接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户

Parser

进行语法解析和词法解析，首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理

Optiomizer

优化索引选择，对SQL命令按照标准流程进行优化分析

InnoDB是默认的存储引擎

存储引擎（Pluggable Storage Engines）

以表为单位，每张表在建立的时候都会默认使用存储引擎，默认使用InnoDB，如果想使用其他存储引擎，则在建表的时候就声明

1

creat table xxx() engine=InnoDB/Memory/MyISAM

MySQL的存储引擎是针对表进行指定的（engine=InnoDB\myisam）

xtraDB存储引擎是由Percona公司提供的存储引擎，该公司还出品了Percona Server这个产品，它是基于MySQL开源代码进行修改之后的产品，阿里对于Percona Server服务器进行修改，衍生了自己的数据库（alisql）

InnoDB和MyISAM存储引擎区别

只有行锁才支持事务

存储引擎的选型

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，不需要持久保存，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎

简版执行流程图

详细执行流程图

物理架构

物理架构更多的看文件系统

• MySQL是通过文件系统对数据和索引进行存储的
• MySQL从物理结构上可以分为日志文件和数据索引文件
• MySQL在Linux中的数据索引文件和日志文件都在/var/lib/mysql目录下
• 日志文件采用顺序IO方式存储、数据文件采用随机IO方式存储

硬盘运行原理

页、块、扇区之间的关系和区别

页：内存以页这个单位去进行IO读取，一般大小为4K，在MySQL中可以通过Innodb_page_size设置大小，一般设置为16K，一页=2^n块

块：操作系统以块这个逻辑单位去操作磁盘，常见为4K，一块=2^n扇区

扇区：磁盘以扇区这个物理最小磁盘单位去存储数据，常见为512Byte

页大小查看：getconf PAGE_SIZE，常见为4K

磁盘块大小查看：stat /boot/|grep “IO Block”，常见为4K

扇区大小查看：fdisk -l，常见为512Byte

指针默认长度为6bit，如果key为bigint的话，为8bit，那么一个索引的话为8+6 = 14bit

索引节点：是页的倍数，一个索引节点，可以存储N多页的数据，MySQL可以设置大小

数据文件

1

SHOW VARIABLES LIKE '%datadir%';

InnoDB数据文件

.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息

.ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件

ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件

MyISAM数据文件

.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息

.myd文件：主要用来存储表数据信息

.myi文件：主要用来存储表数据文件中任何索引的数据树

日志文件

错误日志（errorlog）

默认是开启的，而且从5.5.7以后无法关闭错误日志，错误日志记录了运行过程中遇到的所有严重的错误信息,以及MySQL每次启动和关闭的详细信息

二进制日志（bin log）

记录数据变化

binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中。生产中开启

数据备份、恢复、主从都和二进制日志有关

通用查询日志（general query log）

什么都记录，耗性能生产中，不开启

慢查询日志（slow query log）

SQL调优，定位慢的select

默认是关闭的

需要通过以下设置进行开启

1
2
3
4
5
6

#开启慢查询日志
slow_query_log=ON
#慢查询的阈值
long_query_time=3
#日志记录文件如果没有给出file_name值，默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
slow_query_log_file=file_name

记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句

重做日志（redo log）

回滚日志（undo log）

中继日志（relay log）

看日志开启情况

1

show variables like 'log_%';

在my.cnf配置这些文件的信息

MySQL索引篇

索引介绍

索引是什么

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别

索引的优势和劣势

优势

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。– 检索
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。–排序
被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一
些。
如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
where 索引列在存储引擎层处理索引下推ICP
覆盖索引select 字段字段是索引

劣势：
索引会占据磁盘空间
索引虽然会提高查询效率，但是会降低更新表的效率**。比如每次对表进行增删改操作，
MySQL不仅要保存数据，还有保存或者更新对应的索引文件

索引的分类

在高性能mysql中

MySQL里的索引类型主要有以下几种

1. B-Tree索引

最常见的索引类型，基于B-Tree数据结构。B-Tree的基本思想是，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）。但是当索引多列时，列的顺序特别重要，需要格外注意。InnoDB和MyISAM都支持B-Tree索引。InnoDB用的是一个变种B+Tree，而MyISAM为了节省空间对索引进行了压缩，从而牺牲了性能。

2. Hash索引

基于hash表。所以这种索引只支持精确查找，不支持范围查找，不支持排序。这意味着范围查找或ORDER BY都要依赖server层的额外工作。目前只有Memory引擎支持显式的hash索引（但是它的hash是nonunique的，冲突太多时也会影响查找性能）。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引。

例子

1
2
3
4
5

CREATE TABLE testhash (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    KEY USING HASH(fname)
) ENGINE =MEMORY;

3. Spatial (R-Tree)（空间）索引

只有MyISAM引擎支持，并且支持的不好。可以忽略。

4. Full-text索引（全文索引）

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。Full-text索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的WHERE语句的参数匹配。你可以对某列分别进行full-text索引和B-Tree索引，两者互不冲突。Full-text索引配合MATCH AGAINST操作使用，而不是一般的WHERE语句加LIKE。

在一般的教学中

普通索引

MySQL中最基本的索引类型，没有什么限制，允许定义索引的列中插入重复值和空值，纯粹就是为了查询数据更快一些

唯一索引

索引列中的值必须是唯一的，但是允许空值

主键索引

一种特殊的唯一索引，内容不能重复，不允许有空值

单列索引

单列，一个表可以多个单值索引， age ，name

组合索引

在表中的多个字段组合上创建的索引，遵循最左前缀原则

全文索引

全文索引只能在MyISAM存储中才能使用，而且只能在char、varchar、text 类型字段上才能使用

空间索引

位图索引Oracle

索引的使用

创建索引

单列索引之普通索引

1
2

CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;

单列索引之唯一索引

1
2

CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);

单列索引之全文索引

1
2

CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column)

组合索引

1

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;

注意：如果一个字段是primary key，则该字段默认就是主键索引

删除索引

1

DROP INDEX index_name ON table

查看索引

1

SHOW INDEX FROM table_name

索引原理分析

索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

看一个例子

如图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)O(log2n)的复杂度内获取到相应数据。
虽然这是一个货真价实的索引，但是实际的数据库系统几乎没有使用二叉查找树或其进化品种红黑树（red-black tree）实现的，原因会在下文介绍

索引的存储结构

• 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
• MyISAM和InnoDB存储引擎：只支持B+ TREE索引，也就是说默认使用BTREE，不能够更换
• MEMORY/HEAP存储引擎：支持HASH和BTREE索引

B-Tree和B+Tree

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，在本文的下一节会结合存储器原理及计算机存取原理讨论为什么B-Tree和B+Tree在被如此广泛用于索引，这一节先单纯从数据结构角度描述它们

数据结构示例网站

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B-Tree（B树）

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的，data为数据记录除key外的数据

B树图示

B树是为了磁盘或其它存储设备而设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树。多叉平衡

• B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数
• 如果是三层树结构—支撑的数据可以达到20G，如果是四层树结构—支撑的数据可以达到几十T

那么B-Tree是满足下列条件的数据结构

d为大于1的一个正整数，称为B-Tree的度。
h为一个正整数，称为B-Tree的高度。
每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为
null 。
所有叶节点具有相同的深度，等于树高h。
key和指针互相间隔，节点两端是指针。
一个节点中的key从左到右非递减排列。
所有节点组成树结构。

B+Tree（B+树）

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构

与B-Tree相比，B+Tree有以下不同点

每个节点的指针上限为2d而不是2d+1

内节点不存储data，只存储key

叶子节点不存储指针

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关，将在下面讨论

B和B+的区别

B树和B+树的最大区别在于非叶子节点是否存储数据的问题

B树是非叶子节点和叶子节点都会存储数据

B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的

###

聚集索引和非聚集索引

data中存储的是表数据还是磁盘地址（指针），这就区分为是聚集索引还是非聚集索引

聚集索引（InnoDB）

将表数据和索引数据都存到一个主键索引上

主键索引

主键索引必须要有（MyISAM可以没有）

如果有主键（最好是int），则直接使用

如果没有主键，就会使用唯一索引建立聚集索引

如果没有唯一索引，则MySQL默认创建唯一隐藏列，由该列去创建key作为主键索引

数据文件和索引文件都保存在*.ibd中

辅助索引（次要索引）

不存表数据也不存地址值，只存主键索引的主键值（key）

如果是非主键查询，则需要搜索两次索引树，一次是辅助索引树，一次是主键索引树，最终取出来数据

非聚集索引（MyISAM）

表数据和索引数据没有存储到一起，主索引和次要索引，都是存储表数据的磁盘地址

主键索引

数据文件存储在*.myd中

索引文件存储在*.myi中

查询只要使用到索引，就会找数据文件中的记录

辅助索引（次要索引）

补充

• 创建索引的时候，只要不是主键就会创建次要索引，比如说普通的列就是次要索引，次要索引直到主键是哪一个。主键索引是自动创建，主键索引是每张表必有的，除此之外，其他的索引都需要通过命令才能够创建

• 只要是组合索引（除了联合主键），都是次要索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

什么是数据表的复合主键
所谓的复合主键 就是指你表的主键含有一个以上的字段组成
比如
create table test
(
   name varchar(19),
   id number,
   value varchar(10),
   primary key (name,id)
)

上面的name和id字段组合起来就是你test表的复合主键
它的出现是因为你的name字段可能会出现重名，所以要加上ID字段这样就可以保证你记录的唯一性
一般情况下，主键的字段长度和字段数目要越少越好

联合主键
什么是联合主键？
(主键原则上是唯一的，别被唯一值所困扰。)
顾名思义就是多个主键联合形成一个主键组合
一个简单的例子
主键A跟主键B组成联合主键
主键A跟主键B的数据可以完全相同(困扰吧，没关系)，联合就在于主键A跟主键B形成的联合主键是唯一的。
下例主键A数据是1，主键B数据也是1，联合主键其实是11，这个11是唯一值，绝对不充许再出现11这个唯一值。(这就是多对多关系)
主键A数据 主键B数据
1　　　　　　1
2　　　　　　2
3　　　　　　3
主键A与主键B的联合主键值最多也就是
11
12
13
21
22
23
31
32
33

• 一个组合索引有多个列，会创建一个索引树

• B+树的叶子节点中的数据，都是有序的，其实是一个链表

联合索引的存储结构

联合索引的如何存储与如何排序

首先，表T1有字段a,b,c,d,e这5个列，其中a是主键，除e为varchar其余为int类型，并创建了一个联合索引
idx_t1_bcd(b,c,d)，然后b、c、d三列作为联合索引（组合索引），下面是假设的表数据以及对其联合索引在B+树上的结构图的改进。PS：基于InnoDB存储引擎

bcd联合索引在B+树上的结构图

联合索引的查找方式

当我们的SQL语言可以应用到索引的时候，比如select * from T1 where b = 12 and c = 14 and d = 3;
也就是T1表中a列为4的这条记录。存储引擎首先从根节点（一般常驻内存）开始查找，第一个索引的第一个索引列为1,12大于1，第二个索引的第一个索引列为56,12小于56，于是从这俩索引的中间读到下一个节点的磁盘文件地址，从磁盘上Load这个节点，通常伴随一次磁盘IO，然后在内存里去查找。当Load叶子节点的第二个节点时又是一次磁盘IO，比较第一个元素，b=12,c=14,d=3完全符合，于是找到该索引下的data元素即ID值，再从主键索引树上找到最终数据

最左前缀匹配原则

之所以会有最左前缀匹配原则和联合索引的索引构建方式及存储结构是有关系的

首先我们创建的idx_t1_bcd(b,c,d)索引，相当于创建了(b)、（b、c）（b、c、d）三个索引，看完下面你就知道为什么相当于创建了三个索引

我们看，联合索引是首先使用多列索引的第一列构建的索引树，用上面idx_t1_bcd(b,c,d)的例子就是优先使用b列构建，当b列值相等时再以c列排序，若c列的值也相等则以d列排序。我们可以取出索引树的叶子节点看一下

索引的第一列也就是b列可以说是从左到右单调递增的，但我们看c列和d列并没有这个特性，它们只能在b列值相等的情况下这个小范围内递增，如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素

由于联合索引是上述那样的索引构建方式及存储结构，所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含b列如（c,d）、(c）、(d)是无法应用缓存的，以及跨列也是无法完全用到索引如(b,d)，只会用到b列索引

索引使用场景

哪些情况需要创建索引

1. 主键自动建立唯一索引

2. 频繁作为查询条件的字段应该创建索引，就是where后的字段

3. 多表关联查询中，关联字段应该创建索引on两边都要创建索引

1

select * from user left join order on user.id = order.userid

4. 查询中排序的字段，应该创建索引，B + tree 有顺序

5. 覆盖索引好处是？不需要回表组合索引（回表的意思是回主键里，即primary key的表数据查询）

   但是如果加了一个字段sex，但是sex不是组合所引，此时就需要回表查询

1
2
3

user表中有一个组合索引（name,age）
select * from user ------- 全表扫描，没有使用索引
select name,age from user ---- 使用到了索引，索引覆盖，不需要回表

6、统计或者分组字段，应该创建索引

哪些情况不需要创建索引

1. 表记录太少，因为索引是要有存储的开销
2. 频繁更新，相应的索引也需要更新，索引是要维护
3. 查询字段使用频率不高

为什么使用组合索引

推荐使用组合索引

由多个字段组成的索引使用顺序就是创建的顺序

1

ALTER TABLE 'table_name' ADD INDEX index_name(col1,col2,col3);

组合索引的好处

1. 建一个索引树，相当于创建了多棵索引树，比如说创建了组合索引（name,age）就相当于创建了以name为key的索引树和以name、age创建的索引树，节省了空间

   1 2	select * from user where name = 'zhangsan' and age=18; select * from user where name = 'zhangsan';

​ 以上两个查询都可以使用一个组合suoy

2. 可以优化排序

   比如说创建了组合索引（name,age）

   1 2	select * from user order by name,age;--可以使用索引 select * from user order by name;--不能使用索引

3. 可以使用覆盖索引优化查询

   比如说创建了组合索引（name,age）

   1 2 3

   select name,age from user;--可以使用索引 select name from user;--不能使用索引 select name,age,sex from user;--不能使用索引

组合索引的使用

组合索引的列不一定全部被使用到

遵循最左前缀原则：对于组合索引的使用，需要按照组合索引的创建顺序，从左往右依次匹配

能匹配一个就可以使用组合所引，如果中间断开，则后续的列使用不到，如果使用<、>、between、like

1、前缀索引

like 常量% 使用索引，like %常量不使用索引

2、最左前缀

从左向右匹配直到遇到范围查询> < between ，索引失效

最左前缀的原理

最左前缀就是从左边开始找索引树的叶子节点（key）

注意：不管一张表有几个索引，在查询的时候最多只能使用一个索引，除了index-merge这种情况

索引失效

查看执行计划

执行计划是在优化器中产生的

1
2
3
4
5
6
7
8
9
10
11
12
13

create table tuser(
id int primary key,
name varchar(100),
age int,
  sex char(1),
address varchar(100)
);

alter table tuser add index idx_name_age(name(100),age);
alter table tuser add index idx_sex(sex(1));

insert into tuser(id,name,age,sex,address) values (1,'zhangsan',20,'0','致真
大厦');

介绍

MySQL 提供了一个EXPLAIN 命令, 它可以对SELECT 语句的执行计划进行分析, 并输出SELECT 执行的详细信息, 以供开发人员针对性优化

使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看

可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

EXPLAIN 命令用法十分简单, 在SELECT 语句前加上explain 就可以了, 例如

参数说明

EXPLAIN 命令的输出内容大致如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> explain select * from user where id = 2\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
      table: user_info
  partitions: NULL
        type: const
possible_keys: PRIMARY
key: PRIMARY
    key_len: 8
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

各列的含义如下

id: SELECT 查询的标识符，每个SELECT 都会自动分配一个唯一的标识符

select_type: SELECT 查询的类型

table: 查询的是哪个表

partitions: 匹配的分区

type: join 类型

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引

ref: 哪个字段或常数与key 一起被使用

rows: 显示此查询一共扫描了多少行， 这个是一个估计值

filtered: 表示此查询条件所过滤的数据的百分比

extra: 额外的信息

id

每个单位查询的SELECT语句都会自动分配的一个唯一标识符，表示查询中操作表的顺序

有四种情况：

• id相同：执行顺序由上到下
• id不同：如果是子查询，id号会自增，id越大，优先级越高
• id相同的与不同的可以同时存在
• id列为null的就表示这是一个结果集，不需要使用它来进行查询

select_type（重要）

单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询

simple

表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个

primary

一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个

union

union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union

dependent union

union result

包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null

subquery

除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

dependent subquery

与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

derived

from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

table

显示的单位查询的表名，有如下几种情况

• 如果查询使用了别名，那么这里显示的是别名
• 如果不涉及对数据表的操作，那么这显示为null
• 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于
  这个查询产生
• 如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查
  询的id为M,N的结果集

type（重要）

显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差

1
2
3
4
5
6
7
8
9
10
11
12

system
const
eq_ref
ref
fulltext
ref_or_null
unique_subquery
index_subquery
range
index_merge
index
ALL

注意事项

• 除了all之外，其他的type都可以使用到索引
• 除了index_merge之外，其他的type只可以用到一个索引
• 最少要使用到range级别

system

表中只有一行数据或者是空表

const（重要）

使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描

eq_ref（重要）

前提

• 多表关联

• 等值连接

• 等值连接的两个表的列是唯一索引列或者主键索引列

此类型通常出现在多表的join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是= ， 查询效率较高

ref（重要）

前提

• 多表关联

• 等值连接

• 等值连接的两个表的列是非唯一索引列

针对非唯一性索引，使用等值（=）查询，或者是使用了最左前缀规则索引的查询

1. 组合索引

2. 非唯一索引

fulltext

全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

ref_or_null

与ref方法类似，只是增加了null值的比较。实际用的不多

unique_subquery

用于where中的in形式子查询，子查询返回不重复值唯一值

index_subquery

用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重

range（重要）

索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中

index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range

index（重要）

select结果列中使用到了索引，type会显示为index

全部索引扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询

all（重要）

这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录，注意全表扫描和全索引扫描

possible_keys

此次查询中可能选用的索引，一个或多个

key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个

key_len

key_len

ref

如果是使用的常数等值查询，这里会显示const

如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段

如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows

这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB里面使用了MVCC并发机制）

extra（重要）

这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，常用的有

using filesort（重要）

说明排序的列没有使用到索引

• 排序时无法使用到索引时，就会出现这个，常见于order by和group by语句中

• 说明MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取

• MySQL中无法利用索引完成的排序操作称为“文件排序”

1
2
3
4
5
6
7
8
9

mysql> explain select * from tuser order by address;
+----+-------------+-------+------+---------------+------+---------+------+--
----+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra         |
+----+-------------+-------+------+---------------+------+---------+------+--
----+----------------+
| 1 | SIMPLE     | tuser | ALL | NULL         | NULL | NULL   | NULL |  2 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--
----+----------------+

排序是数据库中的一个基本功能，MySQL也不例外。用户通过Order by语句即能达到将指定的结果集排序的目的，其实不仅仅是Order by语句，Group by语句，Distinct语句都会隐含使用排序。我们首先会简单介绍SQL如何利用索引避免排序代价，然后会介绍MySQL实现排序的内部原理，并介绍与排序相关的参数

1排序优化与索引使用

为了优化SQL语句的排序性能，最好的情况是避免排序，合理利用索引是一个不错的方法。因为索引本身也是有序的，如果在需要排序的字段上面建立了合适的索引，那么就可以跳过排序的过程，提高SQL的查询速度

下面我通过一些典型的SQL来说明哪些SQL可以利用索引减少排序，哪些SQL不能

假设t1表存在索引key1(key_part1,key_part2)，key2(key2)

a.可以利用索引避免排序的SQL

1
2
3
4
5

SELECT * FROM t1 ORDER BY key_part1,key_part2;
SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
SELECT * FROM t1 WHERE key_part1=constant1 AND key_part2>constant2 ORDER BY
key_part2;

b.不能利用索引避免排序的SQL

1
2
3
4
5
6
7
8

-- 排序字段在多个索引中，无法使用索引排序
SELECT * FROM t1 ORDER BY key_part1,key_part2, key2;
-- 排序键顺序与索引中列顺序不一致，无法使用索引排序
SELECT * FROM t1 ORDER BY key_part2, key_part1;
-- 升降序不一致，无法使用索引排序
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
-- key_part1是范围查询，key_part2无法使用索引排序
SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;

排序实现的算法

对于不能利用索引避免排序的SQL，数据库不得不自己实现排序功能以满足用户需求，此时SQL的执行计划中会出现“Using filesort”，这里需要注意的是filesort并不意味着就是文件排序，其实也有可能是内存排序，这个主要由sort_buffer_size参数与结果集大小确定。MySQL内部实现排序主要有3种方式，常规排序，优化排序和优先队列排序，主要涉及3种排序算法：快速排序、归并排序和堆排序。假设表结构和SQL语句如下

1
2
3
4
5
6
7
8
9

CREATE TABLE t1(
id int,
  col1 varchar(64),
col2 varchar(64),
col3 varchar(64),
PRIMARY KEY(id),key(col1,col2)
);

SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;

a.常规排序

(1)从表t1中获取满足WHERE条件的记录

(2)对于每条记录，将记录的主键+排序键(id,col2)取出放入sort buffer

(3)如果sort buffer可以存放所有满足条件的(id,col2)对，则进行排序；否则sort buffer满后，进行排序并固化到临时文件中。(排序算法采用的是快速排序算法)

(4)若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的

(5)循环执行上述过程，直到所有满足条件的记录全部参与排序

(6)扫描排好序的(id,col2)对，并利用id去捞取SELECT需要返回的列(col1,col2,col3)

(7)将获取的结果集返回给用户

从上述流程来看，是否使用文件排序主要看sort buffer是否能容下需要排序的(id,col2)对，这个buffer的大小由sort_buffer_size参数控制。此外一次排序需要两次IO，一次是捞(id,col2),第二次是捞(col1,col2,col3)，由于返回的结果集是按col2排序，因此id是乱序的，通过乱序的id去捞(col1,col2,col3)时会产生大量的随机IO。对于第二次MySQL本身一个优化，即在捞之前首先将id排序，并放入缓冲区，这个缓存区大小由参数read_rnd_buffer_size控制，然后有序去捞记录，将随机IO转为顺序IO

b.优化排序

常规排序方式除了排序本身，还需要额外两次IO。优化的排序方式相对于常规排序，减少了第二次IO。主要区别在于，放入sort buffer不是(id,col2),而是(col1,col2,col3)。由于sort buffer中包含了查询需要的所有字段，因此排序完成后可以直接返回，无需二次捞数据。这种方式的代价在于，同样大小的sortbuffer，能存放(col1,col2,col3)数目要小于(id,col2)，如果sort buffer不够大，可能导致需要写临时文件，造成额外的IO。当然MySQL提供了参数max_length_for_sort_data，只有当排序元组小于max_length_for_sort_data时，才能利用优化排序方式，否则只能用常规排序方式

c.优先队列排序

为了得到最终的排序结果，无论怎样，我们都需要将所有满足条件的记录进行排序才能返回。那么相对于优化排序方式，是否还有优化空间呢？5.6版本针对Order by limit M，N语句，在空间层面做了优化，加入了一种新的排序方式–优先队列，这种方式采用堆排序实现。堆排序算法特征正好可以解limitM，N 这类排序的问题，虽然仍然需要所有元素参与排序，但是只需要M+N个元组的sort buffer空间即可，对于M，N很小的场景，基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素

using index（重要）

查询时不需要回表查询，直接通过索引就可以获取查询的数据

• 表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不错
• 如果同时出现Using Where ，说明索引被用来执行查找索引键值
• 如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作

1
2
3
4
5
6
7
8
9
10

mysql> explain select name,age,sex from tuser ;
+----+-------------+-------+-------+---------------+------------------+------
---+------+------+-------------+
| id | select_type | table | type | possible_keys | key |key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------------+------
---+------+------+-------------+
|  1 | SIMPLE | tuser | index | NULL | idx_name_age_sex | 312 | NULL | 2 | Using index |
+----+-------------+-------+-------+---------------+------------------+------
---+------+------+-------------+
全值匹配覆盖索引

using index condition（重要）

说明用到了索引下推

ICP只能针对二级索引进行使用。不需要针对主键索引

MySQL5.6之前，查询的时候，只会根据Index Key去存储引擎层，确定索引的范围，然后将该范围内的记录取出来，返回到Sql Server层，SqlServer层，再根据where中其他的过滤条件进行过滤。有也就是说IndexFilter和TableFilter都是在SqlServer层进行过滤的

介绍

Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用WHERE子句中的其他条件去过滤这些数据行

因为MySQL的架构原因，分成了server层和引擎层，才有所谓的“下推”的说法。所以ICP（Index Condition Pushdown，索引下推）其实就是实现了index filter技术，将原来的在server层进行的table filter中可以进index filter的部分，在引擎层面使用index filter进行处理，不再需要回表进行table filter

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数

where条件分类

要想深入理解ICP 技术，必须先理解数据库是如何处理where 中的条件的
对where 中过滤条件的处理，根据索引使用情况分成了三种：index key、index filter、table filter，这三个都不是必须要有的

1. index key

用于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件，被称之为Index Key。由于一个范围，至少包含一个起始与一个终止，因此Index Key也被拆分为Index First Key和Index Last Key，分别用于定位索引查找的起始，以及索引查询的终止条件。也就是说根据索引来确定扫描的范围。

2. index filter

在使用index key 确定了起始范围和介绍范围之后，在此范围之内，还有一些记录不符合where 条件，如果这些条件可以使用索引进行过滤，那么就是index filter。也就是说用索引来进行where条件过滤

3. table filter

where 中的条件不能使用索引进行处理的，只能访问table，进行条件过滤

也就是说各种各样的where 条件，在进行处理时，分成了上面三种情况，一种条件会使用索引确定扫描的范围，一种条件可以在索引中进行过滤，一种必须回表进行过滤

如何确定哪些where条件分别是index key,index filter,table filter？

在MySQL5.6 之前，并不区分Index Filter与Table Filter，统统将Index First Key与Index LastKey范围内的索引记录，回表读取完整记录，然后返回给MySQL Server层进行过滤

而在MySQL 5.6（包含）之后，Index Filter与Table Filter分离，Index Filter下降到InnoDB的索引层面进行过滤，减少了回表与返回MySQL Server层的记录交互开销，提高了SQL的执行效率

所以所谓的ICP 技术，其实就是index filter技术而已。只不过因为MySQL的架构原因，分成了server层和引擎层，才有所谓的“下推”的说法。所以ICP其实就是实现了index filter技术，将原来的在server层进行的table filter中可以进行index filter的部分，在引擎层面使用index filter进行处理，不再需要回表进行table filter

不使用ICP扫描的过程

storage层

只将满足index key条件的索引记录对应的整行记录取出，返回给server层

server 层

对返回的数据，使用后面的where条件过滤，直至返回最后一行

使用ICP扫描的过程

storage层

首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤

将满足的index filter条件的索引记录才去回表取出整行记录返回server层

不满足index filter条件的索引记录丢弃，不回表，也不会返回server层

server 层

对返回的数据，使用table filter条件做最后的过滤

使用前后的成本差别

• 使用ICP前，存储层多返回了需要被index filter过滤掉的整行记录

• 使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本

ICP 例子

例子1

官方文档给出了一个例子

1

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

上面例子中的lastername like '%etrunia%'和address like '%Main Street%'本来是无法使用复合索引index(zipcode, lastername, firstname)进行过滤的，但是因为有了ICP技术，所以他们可以在index filter
阶段使用索引进行过滤，无需回表进行table filter

例子2

role_goods 表上有组合索引index(roleId,status,number)，下面的select语句，因为“索引最左前缀原则”，只能使用到组合索引的roleId 部分，但是因为ICP 技术的存在，现在number 条件过滤也可以在index filter 阶段完成了，无需像以前一样需要进行table filer了

1
2
3
4
5
6
7
8
9
10

mysql> explain select * from role_goods where roleId=100000001 and number=1;
+----+-------------+------------+------+---------------+----------+---------
+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len |ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------
+-------+------+-----------------------+
|  1 | SIMPLE | role_goods | ref | roleId_2 | roleId_2 | 9 |const | 14 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------
+-------+------+-----------------------+
1 row in set (0.01 sec)

案例解析

可以看到key_len = 9, 因为roleId 是big int 类型，所以key_len = 8 + 1 = 9; 所以在index key 阶段中，并没有使用到组合索引index(roleId,status,number) 中的number 字段(因为中间有一个status字段没有出现在where 条件中)，但是“Using index condition” 却说明使用到了ICP技术，显然是number =1条件过滤使用到了ICP技术

ICP的使用条件

• 只能用于二级索引(secondary index)

• explain显示的执行计划中type值（join 类型）为range、ref、eq_ref或者ref_or_null

• 且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)

• 对于InnnoDB表，ICP仅用于二级索引。（ICP的目的是减少全行读取的次数，从而减少IO操作），对于innodb聚集索引，完整的记录已被读入到innodb缓冲区，在这种情况下，ICP不会减少io

• ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）

using temporary

• 表示使用了临时表存储中间结果
• MySQL在对查询结果order by和group by时使用临时表
• 临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来

distinct

在select部分使用了distinct关键字（索引字段）

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> explain select distinct a.id from tuser a,tdep b where a.dep=b.id;
+----+-------------+-------+--------+--------------------------------------
----------+---------+---------+------------+------+--------------------------
-----------------+
| id | select_type | table | type| possible_keys  | key | key_len | ref| rows | Extra |
+----+-------------+-------+--------+--------------------------------------
----------+---------+---------+------------+------+--------------------------
-----------------+
| 1 | SIMPLE | a | index |PRIMARY,idx_loginname,idx_name_age_sex,idx_dep | idx_dep | 5   | NULL |2 | Using where; Using index; Using temporary |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4| kkb2.a.dep |1 | Using index; Distinct|
+----+-------------+-------+--------+--------------------------------------
----------+---------+---------+------------+------+--------------------------
-----------------+

using where（重要）

表示存储引擎层返回的记录并不是所有的都满足查询条件，需要在server层进行过滤

数据在存储引擎层没有过滤干净，需要在SQL server层继续过滤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

--查询条件无索引
mysql> explain select * from tuser where address='beijing';
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
|  1 | SIMPLE | tuser | ALL | NULL | NULL | NULL| NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
--索引失效
mysql> explain select * from tuser where age=1;
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
|  1 | SIMPLE | tuser | ALL | NULL | NULL | NULL   | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
mysql> explain select * from tuser where id in(1,2);
+----+-------------+-------+-------+---------------+---------+---------+---
---+------+-------------+
| id | select_type | table | type | possible_keys | key| key_len |ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+---
---+------+-------------+
|  1 | SIMPLE| tuser | range | PRIMARY | PRIMARY | 4 |NULL |  2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+---
---+------+-------------+

• 查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量
• extra列显示using index condition

1
2
3
4
5
6
7
8
9

mysql> explain select * from tuser where name='asd';
+----+-------------+-------+------+------------------+------------------+----
-----+-------+------+-----------------------+
| id | select_type | table | type | possible_keys| key |key_len | ref   | rows | Extra  |
+----+-------------+-------+------+------------------+------------------+----
-----+-------+------+-----------------------+
|  1 | SIMPLE | tuser | ref | idx_name_age_sex | idx_name_age_sex | 303| const |1 | Using index condition |
+----+-------------+-------+------+------------------+------------------+----
-----+-------+------+-----------------------+

索引失效分析

1
2
3
4
5
6
7
8
9
10
11
12
13

 NAME VARCHAR (24) NOT NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;
 
 
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',24,'dev',NOW());
SELECT * FROM staffs;
 
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

全值匹配我最爱

1
2
3

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

最佳左前缀法则

带头索引不能死，中间索引不能断

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

1
2
3

EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
 
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

错误示例

带头索引死

不在索引列上做计算

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

1

EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

范围条件右边的列失效

不能继续使用索引中范围条件（between、<、>、in等）右边的列

范围条件能够使用，但是范围条件右边的索引不能使用

尽量使用覆盖索引

尽量使用覆盖索引（索引列和查询列一致），减少select *

索引字段上不要使用不等

索引字段上使用（!=或<>）判断时，会导致索引失效而转向全表扫描

索引字段上不要判断null

索引字段上使用is null/is not null判断使，会导致索引失效而转向全表扫描

索引字段使用like不以通配符开头

索引字段使用like以通配符开头（’%字符串’）时，会导致索引失效而转向全表扫描

由结果可知，like以通配符结束相当于范围查找，索引不会失效。与范围条件（bettween、<、>、in等）不同的是，不会导致右边的索引失效

面试题：解决like '%字符串%'时索引失效问题的方法？在select后使用覆盖索引可以解决

索引字段字符串要加单引号

索引字段是字符串，但是查询时候不加单引号，会导致索引失效而转向全表扫描

索引字段不要使用or

索引字段使用or时，会导致索引失效而转向全表扫描

总结

假设index(a,b,c)

优化总结口诀

全职匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写*(星)；

不等空值还有OR，索引影响要注意；

VAR引号不可丢， SQL优化有诀窍。

MySQL介绍和基本的SQL编写及解析

MySQL介绍篇

数据库概述

什么是数据库

数据库就是[存储数据的仓库]，其本质是一个[文件系统]，数据按照特定的格式将数据存储起来，用户可以通SQL对数据库中的数据进行增加，修改，删除及查询操作

什么是关系型数据库

数据库中的[记录是有行有列的数据库]就是关系型数据库，与之相反的就是NoSQL数据库了

数据库和表

数据库管理系统（DataBase Management System，DBMS）：指一种[操作和管理数据库]的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。(记录)

常见的数据库管理系统

• MYSQL ：开源免费的数据库，小型的数据库.已经被Oracle收购了。MySQL5.5版本之后都是由
  Oracle发布的版本。
• Oracle ：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。
• DB2 ：IBM公司的数据库产品,收费的。常应用在银行系统中。在中国的互联网公司，要求去
  IOE（IBM小型机、Oracle数据库、EMC存储设备）
• SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
• SyBase ：已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。
• SQLite : 嵌入式的小型数据库，应用在手机端。

MySQL介绍

MySQL是什么

MySQL 是最流行的【关系型数据库管理系统】，在WEB应用方面 MySQL是最好的RDBMS应用软件之一

MySQL发展历程

• MySQL的历史可以追溯到1979年，一个名为Monty Widenius的程序员在为TcX的小公司打工，并且用BASIC设计了一个报表工具，使其可以在4MHz主频和16KB内存的计算机上运行。当时，这只是一个很底层且仅面向报表的存储引擎，名叫Unireg。
• 1990年，TcX公司的客户中开始有人要求为他的API提供SQL支持。Monty直接借助于mSQL的代码，将它集成到自己的存储引擎中。令人失望的是，效果并不太令人满意，决心自己重写一个SQL支持。
• 1996年，MySQL 1.0发布，它只面向一小拨人，相当于内部发布。
• 到了1996年10月，MySQL 3.11.1发布(MySQL没有2.x版本)，最开始只提供Solaris下的二进制版本。一个后，Linux版本出现了。在接下来的两年里，MySQL被依次移植到各个平台。
• 【1999～2000年】，【MySQL AB】公司在瑞典成立。Monty雇了几个人与Sleepycat合作，开发出了【Berkeley DB引擎】, 由于BDB支持事务处理，因此MySQL从此开始支持事务处理了
• 2000，MySQL不仅公布自己的源代码，并采用GPL(GNU General Public License)许可协议，正式进入开源世界。同年4月，MySQL对旧的存储引擎ISAM进行了整理，将其命名为MyISAM。
• 2001年，集成Heikki Tuuri的存储引擎【InnoDB】，这个引擎不仅能【支持事务处理，并且支持行级锁】。后来该引擎被证明是最为成功的MySQL事务存储引擎。【MySQL与InnoDB的正式结合版本是4.0】
• 2003年12月，【MySQL 5.0】版本发布，提供了视图、存储过程等功能。
• 【2008年1月】，【MySQL AB公司被Sun公司以10亿美金收购】，MySQL数据库进入Sun时代。在Sun时代，Sun公司对其进行了大量的推广、优化、Bug修复等工作。
• 2008年11月，MySQL 5.1发布，它提供了分区、事件管理，以及基于行的复制和基于磁盘的NDB集群系统，同时修复了大量的Bug。
• 【2009年4月】，Oracle公司以74亿美元收购Sun公司，自此MySQL数据库进入Oracle时代，而其第三方的存储引擎InnoDB早在2005年就被Oracle公司收购。
• 2010年12月，【MySQL 5.5发布】，其主要新特性包括半同步的复制及对SIGNAL/RESIGNAL的异常处理功能的支持，【最重要的是InnoDB存储引擎终于变为当前MySQL的默认存储引擎】。MySQL 5.5不是时隔两年后的一次简单的版本更新，而是加强了MySQL各个方面在企业级的特性。Oracle公司同时也承诺MySQL 5.5和未来版本仍是采用GPL授权的开源产品。

SQL介绍

什么是SQL

【SQL是Structured Query Language的缩写】

它的前身是著名的关系数据库原型系统System R所采用的SEQUEL语言。作为一种访问【关系型数据库的标准语

言】，SQL自问世以来得到了广泛的应用，不仅是著名的大型商用数据库产品Oracle、DB2、Sybase、SQL

Server支持它，很多开源的数据库产品如PostgreSQL、MySQL也支持它，甚至一些小型的产品如Access也支持

SQL。近些年蓬勃发展的NoSQL系统最初是宣称不再需要SQL的，后来也不得不修正为Not Only SQL，来拥抱

SQL。蓝色巨人IBM对关系数据库以及SQL语言的形成和规范化产生了重大的影响，第一个版本的SQL标准SQL86

就是基于System R的手册而来的。Oracle在1979年率先推出了支持SQL的商用产品。随着数据库技术和应用的发

展，为不同RDBMS提供一致的语言成了一种现实需要。

对SQL标准影响最大的机构自然是那些著名的数据库产商，而具体的制订者则是一些非营利机构，例如【国际标准

化组织ISO、美国国家标准委员会ANSI】等。各国通常会按照 ISO标准和ANSI标准（这两个机构的很多标准是差不

多等同的）制定自己的国家标准。中国是ISO标准委员会的成员国，也经常翻译一些国际标准对应的中文版。标准

为了避免采用具体产品的术语，往往会抽象出很多名词，从而增加了阅读和理解的难度，翻译成中文之后更容易词

不达意。对于数据库系统实现者和用户而言，很多时候还不如直接读英文版本为好。虽然正式的标准不像RFC那样

可以从网络上免费获得，标准草案还是比较容易找到的（例如：http://www.jtc1sc32.org/doc/）。待批准的标准

草案和最终的标准也没有什么实质上的区别，能够满足日常工作的需要。

下面是SQL发展的简要历史：

1
2
3
4
5
6
7

1986年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-86
1989年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-89
1992年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2）
1999年，ISO/IEC 9075:1999，SQL:1999（SQL3）
2003年，ISO/IEC 9075:2003，SQL:2003
2008年，ISO/IEC 9075:2008，SQL:2008
2011年，ISO/IEC 9075:2011，SQL:2011

如果要了解标准的内容，比较推荐的方法是【泛读SQL92】（因为它涉及了SQL最基础和最核心的一些内容），然后增量式的阅读其他标准。

不只是mysql还有其他数据库，在SQL92或者SQL99这些国际SQL标准基础之上，它们还扩展了自己的一些SQL语句，比如MySQL中的limit关键字

SQL语言分类

• 数据定义语言：简称【DDL】(Data Definition Language)，用来定义数据库对象：数据库，表，列
  等。关键字：create，alter，drop等
• 数据操作语言：简称【DML】(Data Manipulation Language)，用来对数据库中表的记录进行更
  新。关键字：insert，delete，update等
• 数据控制语言：简称【DCL】(Data Control Language)，用来定义数据库的访问权限和安全级别，
  及创建用户；关键字：grant等
• 数据查询语言：简称【DQL】(Data Query Language)，用来查询数据库中表的记录。关键字：
  select，from，where等

MySQL基础篇

MySQL单机安装

1
2

操作系统：CentOS 7 
MySQL：5.6

MySQL的卸载

查看MySQL软件

1
2

rpm -qa|grep mysql
yum repolist all | grep mysql

卸载MySQL

1
2
3
4

yum remove -y mysql mysql-libs mysql-common #卸载mysql
rm -rf /var/lib/mysql #删除mysql下的数据文件
rm /etc/my.cnf #删除mysql配置文件
yum remove -y mysql-community-release-el6-5.noarch #删除组件

查看是否还有 MySQL 软件，有的话继续删除

安装MySQL

1
2
3
4
5
6

#下载rpm文件
wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
#执行rpm源文件
rpm -ivh mysql-community-release-el6-5.noarch.rpm
#执行安装文件
yum install mysql-community-server

启动MySQL

1

systemctl start mysqld

设置root用户密码

例如：为 root 账号设置密码为 root

1
2
3

/usr/bin/mysqladmin -u root password 'root'
#没有密码 有原来的密码则加
/usr/bin/mysqladmin -u root -p '123' password 'root'

登录MySQL

登录命令

1

mysql -uroot -proot

命令说明

1
2

-u：指定数据库用户名
-p：指定数据库密码，记住-u和登录密码之间没有空格

配置MySQL

1

vim /etc/my.cnf

修改内容如下

1
2
3
4
5
6

[mysqld]
# MySQL设置大小写不敏感：默认：区分表名的大小写，不区分列名的大小写
# 0：大小写敏感 1：大小写不敏感
lower_case_table_names=1
# 默认字符集
character-set-server=utf8

重启MySQL

1

systemctl restart mysqld.service

注

启动mysql服务

1

systemctl start mysqld.service

停止mysql服务

1

systemctl stop mysqld.service

重启mysql服务

1

systemctl restart mysqld.service

查看mysql服务当前状态

1

systemctl status mysqld.service

设置mysql服务开机自启动

1

systemctl enable mysqld.service

停止mysql服务开机自启动

1

systemctl disable mysqld.service

MySQL远程连接授权

授权命令

1

grant 权限 on 数据库对象 to 用户

示例

授予root用户对所有数据库对象的全部操作权限

1

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

命令说明

ALL PRIVILEGES:表示授予所有的权限，此处可以指定具体的授权权限

*.* :表示所有库中的所有表

'root'@'%'： root是数据库的用户名，%表示是任意ip地址，可以指定具体ip地址

IDENTIFIED BY 'mypassword' ：mypassword是数据库的密码

刷新权限

1

mysql>FLUSH PRIVILEGES;

查看当前用户的权限

1

show grants for root;

关闭linux的防火墙

1
2

systemctl stop firewalld（默认）
systemctl disable firewalld.service（设置开启不启动）

客户端远程访问

利用navicat/SQLyog可以远程访问MySQL，通过远程连接SSH实现

连接不上的解决办法

如果连接不上，可以按以下步骤排错

1、MySQL是否正常启动

1
2
3
4
5

[root@localhost ~]# ps -ef | grep mysql
root 1114 1 0 10:21 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe -
-datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-
file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 1698 1114 0 10:21 ? 00:00:03 /usr/sbin/mysqld

2、查看防火墙是否关闭

1
2
3
4

[root@localhost ~]# systemctl status firewalld
firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled)
Active: inactive (dead)

3、查看root权限为所有ip都可以访问

1
2
3
4
5
6
7

mysql> show grants for root;
+-------------------------------------------------------------------------------
-------------------------------------------------+
| Grants for root@%
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD
'*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
+-----------------------------------------------------------------

4、服务器与客户端是否可以ping通

1
2
3

ping 192.168.239.129
正在 Ping 192.168.239.129 具有 32 字节的数据:
来自 192.168.239.129 的回复: 字节=32 时间<1ms TTL=64

5、客户端是否可以telnet到服务器端

1

telnet 192.168.239.129 3306

6、Navicat是否正确安装

DDL语句

数据库操作database

创建数据库

1
2

create database 数据库名;
create database 数据库名 character set 字符集;

查看数据库

查看数据库服务器中的所有的数据库

1

show databases;

查看某个数据库的定义的信息

1

show create database 数据库名;

删除数据库（慎用）

1

drop database 数据库名称;

其他数据库操作命令

切换数据库

1

use 数据库名;

查看正在使用的数据库

1

select database();

表操作table

字段类型

常用的类型有

数字型：int

浮点型：double

字符型：varchar（可变长字符串）

日期类型：date（只有年月日，没有时分秒），datetime（年月日，时分秒）

boolean类型：不支持，一般使用tinyint替代（值为0和1）

创建表

1
2
3
4

create table 表名(
    字段名 类型(长度) 约束,
    字段名 类型(长度) 约束
);

单表约束

• 主键约束：primary key
• 唯一约束：unique
• 非空约束：not null

注意

主键约束 = 唯一约束 + 非空约束

查看表

查看数据库中的所有表

1

show tables;

查看表结构

1

desc 表名;

删除表

1

drop table 表名;

修改表

1
2
3
4
5
6
7
8
9
10
11

alter table 表名 add 列名 类型(长度) 约束; --修改表添加列

alter table 表名 modify 列名 类型(长度) 约束; --修改表修改列的类型长度及约束

alter table 表名 change 旧列名 新列名 类型(长度) 约束; --修改表修改列名

alter table 表名 drop 列名; --修改表删除列

rename table 表名 to 新表名; --修改表名

alter table 表名 character set 字符集; --修改表的字符集

DML语句

插入记录：insert

语法

1
2
3
4
5
6
7

insert into 表 (列名1,列名2,列名3..) values (值1,值2,值3..); -- 向表中插入某些列

insert into 表 values (值1,值2,值3..); --向表中插入所有列

insert into 表 (列名1,列名2,列名3..) values select (列名1,列名2,列名3..) from 表

insert into 表 values select * from 表

注意

1. 列名数与values后面的值的个数相等
2. 列的顺序与插入的值得顺序一致
3. 列名的类型与插入的值要一致
4. 插入值得时候不能超过最大长度
5. 值如果是字符串或者日期需要加引号’’ （一般是单引号）

例如

1
2
3
4
5
6
7

INSERT INTO sort(sid,sname) VALUES('s001', '电器');

INSERT INTO sort(sid,sname) VALUES('s002', '服饰');

INSERT INTO sort VALUES('s003', '化妆品');

INSERT INTO sort VALUES('s004','书籍');

更新记录：update

语法

1
2
3

update 表名 set 字段名=值,字段名=值;

update 表名 set 字段名=值,字段名=值 where 条件;

注意

1. 列名的类型与修改的值要一致
2. 修改值得时候不能超过最大长度
3. 值如果是字符串或者日期需要加’’

删除记录：delete

语法

1

delete from 表名 [where 条件];

面试题

1
2
3
4

删除表中所有记录使用【delete from 表名】，还是用【truncate table 表名】？
删除方式：
- delete ：一条一条删除，不清空auto_increment记录数。
- truncate ：直接将表删除，重新建表，auto_increment将置为零，从新开始。

DQL语句

创建商品表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

#商品表
CREATE TABLE product (
    pid INT PRIMARY KEY AUTO_INCREMENT, # 自增加 AUTO_INCREMENT
    pname VARCHAR(20),#商品名称
    price DOUBLE, #商品价格
    pdate DATE, # 日期
    cid int #分类ID
);

#目录表
create table category(
    id INT PRIMARY KEY ,
    cname varchar(100)
);

INSERT INTO product VALUES(NULL,'泰国大榴莲', 98, NULL, 1);
INSERT INTO product VALUES(NULL,'泰国大枣', 38, NULL, 1);
INSERT INTO product VALUES(NULL,'新疆切糕', 68, NULL, 2);
INSERT INTO product VALUES(NULL,'十三香', 10, NULL, 2);
INSERT INTO product VALUES(NULL,'泰国大枣', 20, NULL, 2);
insert into product values(null,'泰国大枣',98,null,20); #没有对应
insert into product values(null,'iPhone手机',800,null,30);#没有对应

INSERT INTO category VALUES(1,'国外食品');
INSERT INTO category VALUES(2,'国内食品');
INSERT INTO category VALUES(3,'国内服装'); #没有对应

完整DQL语法顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT DISTINCT
	< select_list >
FROM
	< left_table > < join_type >
JOIN 
	< right_table > ON < join_condition >
WHERE
	< where_condition >
GROUP BY
	< group_by_list >
HAVING
	< having_condition >
ORDER BY
	< order_by_condition >
LIMIT 
	< limit_number >

简单查询

SQL语法关键字

1
2

SELECT
FROM

案例

查询所有的商品

1

select * from product;

查询商品名和商品价格

1

select pname,price from product;

别名查询，使用的as关键字，as可以省略的

表别名

1

select * from product as p;

列别名

1

select pname as pn from product;

去掉重复值

1

select distinct price from product;

查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示

1

select pname,price+10 from product;

条件查询

SQL语法关键字

1

WHERE

案例

查询商品名称为十三香的商品所有信息

1

select * from product where pname = '十三香';

查询商品价格>60元的所有的商品信息

1

select * from product where price > 60;

where后的条件写法

> ,<,=,>=,<=,<>

like 使用占位符 _ 和 % _代表一个字符 %代表任意个字符

1

select * from product where pname like '%新%';

in在某个范围中获得值（exists）

1

select * from product where pid in (2,5,8);

排序

SQL语法关键字

1
2

ORDER BY
ASC（升序） DESC（降序）

案例

查询所有的商品，按价格进行排序.(asc-升序,desc-降序)

1

select * from product order by price;

查询名称有新的商品的信息并且按价格降序排序

1

select * from product where pname like '%新%' order by price desc;

聚合函数（组函数）

特点：只对单列进行操作

常用的聚合函数

1
2
3
4
5
6
7
8
9

sum()：求某一列的和

avg()：求某一列的平均值

max()：求某一列的最大值

min()：求某一列的最小值

count()：求某一列的元素个数

案例

获得所有商品的价格的总和

1

select sum(price) from product;

获得所有商品的平均价格

1

select avg(price) from product;

获得所有商品的个数

1

select count(*) from product;

分组

SQL语法关键字

1
2

GROUP BY
HAVING

案例

SQL语法关键字

1
2

GROUP BY
HAVING

案例

根据cno字段分组，分组后统计商品的个数

1

select cid,count(*) from product group by cid;

根据cno分组，分组统计每组商品的平均价格，并且平均价格> 60

1

select cid,avg(price) from product group by cid having avg(price)>60;

注意事项

1. select语句中的列（非聚合函数列），必须出现在group by子句中
2. group by子句中的列，不一定要出现在select语句中
3. 聚合函数只能出现select语句中或者having语句中，一定不能出现在where语句中

分页查询

关键字

1

LIMIT [offset,] rows

LIMIT 可以传入一个或两个参数，LIMIT [位置偏移量]，返回数据的数量

位置偏移量是指：查询的开始的位置，例如：第一条数据的偏移量则为 0，第二条数据的偏移量为 1 …….

返回数据的数量：查询数据所返回的记录条数

简单来说就是

1

select * from student limit a,b

a 指：开始的index位置，从0开始，表示第一条数据，b 指：返回的数据量

LIMIT关键字不是 SQL92 标准提出的关键字，它是 MySQL 独有的语法，通过 limit 关键字， MySQL 实现了物理分页

分页分为逻辑分页和物理分页

逻辑分页：将数据库中的数据查询到内存之后再进行分页

物理分页：通过LIMIT关键字，直接在数据库中进行分页，最终返回的数据，只是分页后的数据

格式

1

SELECT * FROM table LIMIT [offset,] rows

offset ：偏移量，rows：每页多少行记录

案例

1查询商品表中前10条数据（从第一条数据开始，查询10条数据）

1

select * from product limit 10

1

select * from product limit 0,10

2一般情况，limit 用于分页场景

1
2
3
4
5
6
7
8

// 当前的页数 (例如当前为第一页)
 int currentPage = 0; 
 
// 每页显示多少条记录 (例如显示每页10条记录)
 int pageSize = 10;
 
//开始的位置(位置偏移量)
 int startRow = (currentPage - 1) * pageSize;

数据库limit分页语句

1

select * from product limit startRow,pageSize

子查询

定义

子查询允许把一个查询嵌套在另一个查询当中

子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询

子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、limit、join和union等，但是对应的外部查询必须是以下语句之一：select、insert、update、delete

位置

select中、from 后、where 中，group by 和order by 中无实用意义

举例

查询所有的商品和产品所属的分类

1

select a.* , (select cname from category b where a.cid=b.id) cname from product a;

1
2
3
4
5
6
7
8
9
10
11
12

+-----+-----------------+-------+-------+------+--------------+
| pid | pname           | price | pdate | cid  | cname        |
+-----+-----------------+-------+-------+------+--------------+
|   1 | 泰国大榴莲      |    98 | NULL  |    1 | 国外食品     |
|   2 | 泰国大枣        |    38 | NULL  |    1 | 国外食品     |
|   3 | 新疆切糕        |    68 | NULL  |    2 | 国内食品     |
|   4 | 十三香          |    10 | NULL  |    2 | 国内食品     |
|   5 | 泰国大枣        |    20 | NULL  |    2 | 国内食品     |
|   6 | 泰国大枣        |    98 | NULL  |   20 | NULL         |
|   7 | iPhone手机      |   800 | NULL  |   30 | NULL         |
+-----+-----------------+-------+-------+------+--------------+
7 rows in set (0.02 sec)

查询商品和对应的分类编号

1

select * from product where cid in (select id from category);

1
2
3
4
5
6
7
8
9

+-----+-----------------+-------+-------+------+
| pid | pname           | price | pdate | cid  |
+-----+-----------------+-------+-------+------+
|   1 | 泰国大榴莲      |    98 | NULL  |    1 |
|   3 | 新疆切糕        |    68 | NULL  |    2 |
|   6 | 泰国大枣        |    98 | NULL  |   20 |
|   7 | iPhone手机      |   800 | NULL  |   30 |
+-----+-----------------+-------+-------+------+
4 rows in set (0.00 sec)

查询价格大于50的商品和分类编号

1

select a.* from (select * from product where price >50) a;

1
2
3
4
5
6
7
8
9
10
11
12

+-----+-----------------+-------+-------+------+--------------+
| pid | pname           | price | pdate | cid  | cname        |
+-----+-----------------+-------+-------+------+--------------+
|   1 | 泰国大榴莲      |    98 | NULL  |    1 | 国外食品     |
|   2 | 泰国大枣        |    38 | NULL  |    1 | 国外食品     |
|   3 | 新疆切糕        |    68 | NULL  |    2 | 国内食品     |
|   4 | 十三香          |    10 | NULL  |    2 | 国内食品     |
|   5 | 泰国大枣        |    20 | NULL  |    2 | 国内食品     |
|   6 | 泰国大枣        |    98 | NULL  |   20 | NULL         |
|   7 | iPhone手机      |   800 | NULL  |   30 | NULL         |
+-----+-----------------+-------+-------+------+--------------+
7 rows in set (0.02 sec)

其他查询语句

union 集合的并集（不包含重复记录）

unionall 集合的并集（包含重复记录）

SQL解析顺序

接下来再走一步，让我们看看一条SQL语句的前世今生

首先看一下示例语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT DISTINCT
	< select_list >
FROM
	< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
	< where_condition >
GROUP BY
	< group_by_list >
HAVING
	< having_condition >
ORDER BY
	< order_by_condition >
LIMIT 
	< limit_number >

然而它的执行顺序是这样的

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 行过滤
1 FROM <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table> 第二步和第三步会循环执行
4 WHERE <where_condition> 第四步会循环执行，多个条件的执行顺序是从左往右的
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
--列过滤
7 SELECT 分组之后才会执行SELECT
8 DISTINCT <select_list>
--排序
9 ORDER BY <order_by_condition>
-- MySQL附加
10 LIMIT <limit_number> 前9步都是SQL92标准语法。limit是MySQL的独有语法。

虽然自己没想到是这样的，不过一看还是很自然和谐的，从哪里获取，不断的过滤条件，要选择一样或不一样的，排好序，那样才知道要取前几条，既然如此了，那就让我们根据案例一步步来看看其中的细节

FROM

当涉及多个表的时候，左边表的输出会作为右边表的输入，之后会生成一个虚拟表VT1

(1-J1)笛卡尔积

计算两个相关联表的笛卡尔积(CROSS JOIN) ，生成虚拟表VT1-J1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

mysql> select * from product,category;
+-----+-----------------+-------+-------+------+----+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+-----------------+-------+-------+------+----+--------------+
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 1 | 国外食品 |
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 2 | 国内食品 |
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 3 | 国内服装 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 2 | 国内食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 3 | 国内服装 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 1 | 国外食品 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 2 | 国内食品 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 3 | 国内服装 |
| 4 | 十三香 | 10 | NULL | 2 | 1 | 国外食品 |
| 4 | 十三香 | 10 | NULL | 2 | 2 | 国内食品 |
| 4 | 十三香 | 10 | NULL | 2 | 3 | 国内服装 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 1 | 国外食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 3 | 国内服装 |
| 6 | 泰国大枣 | 98 | NULL | 20 | 1 | 国外食品 |
| 6 | 泰国大枣 | 98 | NULL | 20 | 2 | 国内食品 |
| 6 | 泰国大枣 | 98 | NULL | 20 | 3 | 国内服装 |
| 7 | iPhone手机 | 800 | NULL | 30 | 1 | 国外食品 |
| 7 | iPhone手机 | 800 | NULL | 30 | 2 | 国内食品 |
| 7 | iPhone手机 | 800 | NULL | 30 | 3 | 国内服装 |
+-----+-----------------+-------+-------+------+----+--------------+
21 rows in set (0.00 sec)

(1-J2)ON过滤

基于虚拟表VT1-J1这一个虚拟表进行过滤，过滤出所有满足ON 谓词条件的列，生成虚拟表VT1-J2

注意

这里因为语法限制，使用了WHERE代替，从中也可以感受到jion和on之间微妙的关系（inner-join）

1
2
3
4
5
6
7
8
9
10
11

mysql> select * from product a , category b where a.cid=b.id;
+-----+-----------------+-------+-------+------+----+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+-----------------+-------+-------+------+----+--------------+
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 1 | 国外食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 2 | 国内食品 |
| 4 | 十三香 | 10 | NULL | 2 | 2 | 国内食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
+-----+-----------------+-------+-------+------+----+--------------+
5 rows in set (0.00 sec)

(1-J3)添加外部列

如果使用了外连接(LEFT,RIGHT,FULL)，主表（保留表）中的不符合ON条件的列也会被加入到VT1-J2中，作为外部行，生成虚拟表VT1-J3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

mysql> select * from product a left outer join category b on a.cid=b.id; # 以左表
数据为准
+-----+-----------------+-------+-------+------+------+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+-----------------+-------+-------+------+------+--------------+
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 1 | 国外食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 2 | 国内食品 |
| 4 | 十三香 | 10 | NULL | 2 | 2 | 国内食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
| 6 | 泰国大枣 | 98 | NULL | 20 | NULL | NULL |
| 7 | iPhone手机 | 800 | NULL | 30 | NULL | NULL |
+-----+-----------------+-------+-------+------+------+--------------+
7 rows in set (0.00 sec)
mysql> select * from product a right outer join category b on a.cid=b.id; #以右表
数据为准
+------+-----------------+-------+-------+------+----+--------------+
| pid | pname | price | pdate | cid | id | cname |
+------+-----------------+-------+-------+------+----+--------------+
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 1 | 国外食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 2 | 国内食品 |
| 4 | 十三香 | 10 | NULL | 2 | 2 | 国内食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
| NULL | NULL | NULL | NULL | NULL | 3 | 国内服装 |
+------+-----------------+-------+-------+------+----+--------------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

CREATE TABLE `tbl_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `locAdd` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `tbl_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 

INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
 
INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);

1 A、B两表共有

1

select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;

2 A、B两表共有+A的独有

1

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;

3 A、B两表共有+B的独有

1

select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

4 A的独有

1

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;

5 B的独有

1

select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;

6 AB全有

1
2
3
4
5

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT * FROM tbl_emp A LEFT JOIN tbl_dept B ON A.deptId = B.id
UNION
SELECT * FROM tbl_emp A RIGHT JOIN tbl_dept B ON A.deptId = B.id

7 A的独有+B的独有

1
2
3

SELECT * FROM tbl_emp A LEFT JOIN tbl_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM tbl_emp A RIGHT JOIN tbl_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

WHERE

对VT1过程中生成的临时表进行过滤，满足WHERE子句的列被插入到VT2表中

注意

此时因为分组，不能使用聚合运算，也不能使用SELECT中创建的别名

与ON的区别

如果有外部列，ON针对过滤的是关联表，主表（保留表）会返回所有的列

如果没有添加外部列，两者的效果是一样的

应用

对主表的过滤应该放在WHERE

对于关联表，先条件查询后连接则用ON，先连接后条件查询则用WHERE

1
2
3
4
5
6
7
8
9
10

mysql> select * from product a left outer join category b on a.cid=b.id where
a.pname='泰国大枣';
+-----+--------------+-------+-------+------+------+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+--------------+-------+-------+------+------+--------------+
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
| 6 | 泰国大枣 | 98 | NULL | 20 | NULL | NULL |
+-----+--------------+-------+-------+------+------+--------------+
3 rows in set (0.00 sec)

GROUP BY

这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表

注意

其后处理过程的语句，如SELECT，HAVING，所用到的列必须包含在GROUP BY中。对于没有出现的，得用聚合函数

原因

GROUP BY改变了对表的引用，将其转换为新的引用方式，能够对其进行下一级逻辑操作的列会减少

理解

根据分组字段，将具有相同分组字段的记录归并成一条记录，因为每一个分组只能返回一条记录，除非是被过滤掉了，而不在分组字段里面的字段可能会有多个值，多个值是无法放进一条记录的，所以必须通过聚合函数将这些具有多值的列转换成单值

1
2
3
4
5
6
7
8
9
10

mysql> select * from product a left outer join category b on a.cid=b.id where
a.pname='泰国大枣' group by a.price;
+-----+--------------+-------+-------+------+------+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+--------------+-------+-------+------+------+--------------+
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 6 | 泰国大枣 | 98 | NULL | 20 | NULL | NULL |
+-----+--------------+-------+-------+------+------+--------------+
3 rows in set (0.01 sec)

HAVING

这个子句对VT3表中的不同的组进行过滤，只作用于分组后的数据，满足HAVING条件的子句被加入到VT4表中

1
2
3
4
5
6
7
8
9

mysql> select * from product a left outer join category b on a.cid=b.id where
a.pname='泰国大枣' group by a.price having b.id <=2;
+-----+--------------+-------+-------+------+------+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+--------------+-------+-------+------+------+--------------+
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
+-----+--------------+-------+-------+------+------+--------------+
2 rows in set (0.00 sec)

SELECT

这个子句对SELECT子句中的元素进行处理，生成VT5表

(5-J1)计算表达式

计算SELECT 子句中的表达式，生成VT5-J1

(5-J2)DISTINCT

寻找VT5-1中的重复列，并删掉，生成VT5-J2

如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表VT5是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据

1
2
3
4
5
6
7
8

mysql> select distinct a.pname from product a left outer join category b on
a.cid=b.id where a.pname='泰国大枣' group by a.price ;
+--------------+
| pname |
+--------------+
| 泰国大枣 |
+--------------+
1 row in set (0.00 sec)

ORDER BY

从VT5-J2中的表中，根据ORDER BY 子句的条件对结果进行排序，生成VT6表

注意

唯一可使用SELECT中别名的地方

1
2
3
4
5
6
7
8
9

mysql> select * from product a left outer join category b on a.cid=b.id where
a.pname='泰国大枣' group by a.price having b.id <=2 order by b.id;
+-----+--------------+-------+-------+------+------+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+--------------+-------+-------+------+------+--------------+
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
+-----+--------------+-------+-------+------+------+--------------+
2 rows in set (0.00 sec)

LIMIT（MySQL特有）

LIMIT子句从上一步得到的VT6虚拟表中选出从指定位置开始的指定行数据

注意

offset 和 rows 的正负带来的影响

当偏移量很大时效率是很低的，可以这么做

采用子查询的方式优化 ，在子查询里先从索引获取到最大id，然后倒序排，再取N行结果集

采用INNER JOIN优化 ，JOIN子句里也优先从索引获取ID列表，然后直接关联查询获得最终结果

1
2
3
4
5
6
7
8
9

mysql> select * from product a left outer join category b on a.cid=b.id where
a.pname='泰国大枣' group by a.price having b.id <=2 order by b.id limit 1;
+-----+--------------+-------+-------+------+-
-----+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+--------------+-------+-------+------+------+--------------+
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
+-----+--------------+-------+-------+------+------+--------------+
1 row in set (0.00 sec)

解析顺序总结

图示

流程分析

1. FROM（将最近的两张表，进行笛卡尔积）—VT1-J1
2. ON（将VT1-J1按照它的条件进行过滤）—VT1-J2
3. LEFT JOIN（保留左表的记录）—VT1-J3
4. WHERE（过滤VT1中的记录）–VT2
5. GROUP BY（对VT2的记录进行分组）—VT3
6. HAVING（对VT3中的记录进行过滤）—VT4
7. SELECT（对VT5中的记录选取指定的列）–VT5-J2
8. ORDER BY（对VT5-J2的记录进行排序）–VT6
9. LIMIT（对排序之后的值进行分页）–MySQL特有的语法

流程说明

• 单表查询：根据 WHERE 条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的），然后根据 SELECT 的选择列选择相应的列进行返回最终结果

• 两表连接查询：对两表求积（笛卡尔积）并用 ON 条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据 SELECT 指定的列返回查询结果

  笛卡尔积：行相乘、列相加

• 多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果

WHERE条件解析顺序

MySQL ：从左往右去执行 WHERE 条件的

Oracle ：从右往左去执行 WHERE 条件的

写WHERE条件的时候，优先级高的部分要去编写过滤力度最大的条件语句

表与表之间的关系

表关系的概念

现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系

一对多

对多（1:n）例如：班级和学生，部门和员工，客户和订单，分类和商品

一对多建表原则：在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

多对多

多对多（m:n）例如：老师和学生，学生和课程，用户和角色

多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

一对一

一对一（1:1）在实际的开发中应用不多.因为一对一可以创建成一张表

两种建表原则：

一对多关系案例

需求

一个旅游线路分类中有多个旅游线路

界面

表与表的关系

具体操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- 创建旅游线路分类表tab_category
-- cid旅游线路分类主键，自动增长
-- cname旅游线路分类名称非空，唯一，字符串100
create table tab_category (
cid int primary key auto_increment,
cname varchar(100) not null unique
)

-- 添加旅游线路分类数据：
insert into tab_category (cname) values ('周边游'), ('出境游'), ('国内游'), ('港澳游');

select * from tab_category;

-- 创建旅游线路表tab_route
/*
rid旅游线路主键，自动增长
rname旅游线路名称非空，唯一，字符串100
price价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/
create table tab_route(
rid int primary key auto_increment,
rname varchar(100) not null unique,
price double,
rdate date,
cid int,
foreign key (cid) references tab_category(cid)
)

-- 添加旅游线路数据
INSERT INTO tab_route VALUES
(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵高铁3天惠贵团】尝味友鸭面线住1晚鼓浪屿', 1499,
'2018-01-27', 1),
(NULL, '【浪漫桂林阳朔西街高铁3天纯玩高级团】城徽象鼻山兴坪漓江西山公园', 699, '2018-02-
22', 3),
(NULL, '【爆款￥1699秒杀】泰国曼谷芭堤雅金沙岛杜拉拉水上市场双飞六天【含送签费泰风情广州
往返特价团】', 1699, '2018-01-27', 2),
(NULL, '【经典•狮航￥2399秒杀】巴厘岛双飞五天抵玩【广州往返特价团】', 2399, '2017-12-23',
2),
(NULL, '香港迪士尼乐园自由行2天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店
暨会议中心标准房1晚住宿】', 799, '2018-04-10', 4);

select * from tab_route;

多对多关系案例

需求

一个用户收藏多个线路，一个线路被多个用户收藏

对于多对多的关系我们需要增加一张中间表来维护他们之间的关系

具体操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

/*
创建用户表tab_user
uid用户主键，自增长
username用户名长度100，唯一，非空
password密码长度30，非空
name真实姓名长度100
birthday生日
sex性别，定长字符串1
telephone手机号，字符串11
email邮箱，字符串长度100
*/
create table tab_user (
uid int primary key auto_increment,
username varchar(100) unique not null,
password varchar(30) not null,
name varchar(100),
birthday date,
sex char(1) default '男',
telephone varchar(11),
email varchar(100)
)
-- 添加用户数据
INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
select * from tab_user;
/*
创建收藏表tab_favorite
rid 旅游线路id，外键
date 收藏时间
uid用户id，外键
rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
rid int,
date datetime,
uid int,
-- 创建复合主键
primary key(rid,uid),
foreign key (rid) references tab_route(rid),
foreign key(uid) references tab_user(uid)
)
-- 增加收藏表数据
INSERT INTO tab_favorite VALUES
(1, '2018-01-01', 1), -- 老王选择厦门
(2, '2018-02-11', 1), -- 老王选择桂林
(3, '2018-03-21', 1), -- 老王选择泰国
(2, '2018-04-21', 2), -- 小王选择桂林
(3, '2018-05-08', 2), -- 小王选择泰国
(5, '2018-06-02', 2); -- 小王选择迪士尼
select * from tab_favorite;

表与表之间的关系小结

分类表

1
2
3
4

create table category(
    cid varchar(32) primary key,
    cname varchar(100)
);

商品表

1
2
3
4
5

create table product(
    pid varchar(32) primary key,
    pname varchar(40),
    price double
);

订单表

1
2
3
4

create table orders(
    oid varchar(32) primary key,
    totalprice double
);

订单项表

1
2
3
4

create table orderitem(
    oid varchar(50),
    pid varchar(50)
);

表与表之间的关系

表与表之间的关系，说的就是表与表之间数据的关系

• 一对一关系
  常见实例：一夫一妻
• 一对多关系
  常见实例：会员和订单
• 多对多关系（需要中间表实现）
  常见实例：商品和订单

外键

如何表示表与表之间的关系呢？就是使用外键约束表示的

要想理解外键，我们先去理解表的角色：主表和从表（需要建立关系才有了主从表的角色区分）

主从表的理解

现在我们有两张表“分类表”和“商品表”

目前从表的声明上来说，没有关系，但是我们有个需求：

商品应该有所属的分类，这个时候需要将分类表和商品表建立关系，如何建立？

按照以上需求分析：

主表是：商品表。主表中，应该有一个字段去关联从表，而这个关联字段就是外键

从表是：分类表。从表中，应该有一个字段去关联主表，而这个关联字段就是主键

主键外键的理解

如何操作外键

主表添加外键的格式

1

alter table 表名add [constraint][约束名称] foreign key (主表外键字段) references 从表(从表主键)

主表删除外键的格式

1

alter table 表名drop foreign key 外键约束名称

使用外键目的

保证数据完整性（数据保存在多张表中的时候）

在互联网项目中，一般情况下，不建议建立外键关系

一对一关系（了解）

在实际工作中，一对一在开发中应用不多，因为一对一完全可以创建成一张表

案例：一个丈夫只能有一个妻子

建表语句

1
2
3
4
5
6
7
8
9
10

CREATE TABLE wife(
  id  INT PRIMARY KEY ,
  wname  VARCHAR(20),
  sex CHAR(1)
);
CREATE TABLE husband(
  id  INT PRIMARY KEY ,
  hname  VARCHAR(20),
  sex CHAR(1)
);

一对一关系创建方式1之外键唯一

添加外键列wid，指定该列的约束为唯一（不加唯一约束就是一对多关系）

1

ALTER TABLE husband ADD wid INT UNIQUE;

添加外键约束

1

alter table husband add foreign key (wid) references wife(id);

一对一关系创建方式2之主键做外键

思路：使用主表的主键作为外键去关联从表的主键

一对多关系

案例：一个分类对应多个商品

总结

有外键的就是多的一方

注意事项

一对多关系和一对一关系的创建很类似，唯一区别就是外键不唯一

一对多关系创建

添加外键列

添加外键约束

案例

在商品表中添加一条记录，该记录的cid在分类表中不存在

在分类表中，删除一条记录，这条记录在商品表中有外键关联

多对多关系

案例1

同一个商品对应多个订单，一个订单对应多个商品

注意事项

需要中间表去完成多对多关系的创建

多对多关系其实就是两个一对多关系的组合

多对多关系创建

创建中间表，并在其中创建多对多关系中两张表的外键列

在中间表中添加外键约束

在中间表中添加联合主键约束

案例2

用户和角色

1个用户对多个角色

1个角色对多个用户

中间表用户角色表uid rid

多表关联查询

我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据

本章节我们将向大家介绍如何使用MySQL 的JOIN 在两个或多个表中查询数据

你可以在SELECT , UPDATE 和DELETE 语句中使用MySQL 的JOIN 来联合多表查询

JOIN 按照功能大致分为如下三类：

• CROSS JOIN （交叉连接）
• INNER JOIN （内连接或等值连接）
• OUTER JOIN （外连接）

交叉连接

关键字

1

CROSS JOIN

交叉连接也叫笛卡尔积连接。笛卡尔积是指在数学中，两个集合X 和Y 的笛卡尓积（Cartesian product ），又称直积，表示为X*Y ，第一个对象是X 的成员而第二个对象是Y 的所有可能有序对的其中一个成员

交叉连接的表现

行数相乘、列数相加

隐式交叉连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

SELECT *  FROM   A, B
mysql> select * from product,category;
+-----+-----------------+-------+-------+------+----+--------------+
| pid | pname           | price | pdate | cid | id | cname       |
+-----+-----------------+-------+-------+------+----+--------------+
|   1 | 泰国大榴莲    |    98 | NULL |    1 |  1 | 国外食品    |
|   1 | 泰国大榴莲    |    98 | NULL |    1 |  2 | 国内食品    |
|   1 | 泰国大榴莲    |    98 | NULL |    1 |  3 | 国内服装    |
|   2 | 泰国大枣      |    38 | NULL |    1 |  1 | 国外食品    |
|   2 | 泰国大枣      |    38 | NULL |    1 |  2 | 国内食品    |
|   2 | 泰国大枣      |    38 | NULL |    1 |  3 | 国内服装    |
|   3 | 新疆切糕      |    68 | NULL |    2 |  1 | 国外食品    |
|   3 | 新疆切糕      |    68 | NULL |    2 |  2 | 国内食品    |
|   3 | 新疆切糕      |    68 | NULL |    2 |  3 | 国内服装    |
|   4 | 十三香        |    10 | NULL |    2 |  1 | 国外食品    |
|   4 | 十三香        |    10 | NULL |    2 |  2 | 国内食品    |
|   4 | 十三香        |    10 | NULL |    2 |  3 | 国内服装    |
|   5 | 泰国大枣      |    20 | NULL |    2 |  1 | 国外食品    |
|   5 | 泰国大枣      |    20 | NULL |    2 |  2 | 国内食品    |
|   5 | 泰国大枣      |    20 | NULL |    2 |  3 | 国内服装    |
|   6 | 泰国大枣      |    98 | NULL |   20 |  1 | 国外食品    |
|   6 | 泰国大枣      |    98 | NULL |   20 |  2 | 国内食品    |
|   6 | 泰国大枣      |    98 | NULL |   20 |  3 | 国内服装    |
|   7 | iPhone手机    |   800 | NULL |   30 |  1 | 国外食品    |
|   7 | iPhone手机    |   800 | NULL |   30 |  2 | 国内食品    |
|   7 | iPhone手机    |   800 | NULL |   30 |  3 | 国内服装    |
+-----+-----------------+-------+-------+------+----+--------------+
21 rows in set (0.00 sec)

显式交叉连接

1

SELECT * FROM A CROSS JOIN B

内连接

关键字

1

INNER JOIN

内连接也叫等值连接，内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行

隐式内连接

1
2
3
4
5
6
7
8
9
10
11
12

SELECT *  FROM A,B  WHERE A.id = B.id
mysql> select * from product a , category b where a.cid=b.id;
+-----+-----------------+-------+-------+------+----+--------------+
| pid | pname           | price | pdate | cid | id | cname       |
+-----+-----------------+-------+-------+------+----+--------------+
|   1 | 泰国大榴莲    |    98 | NULL |    1 |  1 | 国外食品    |
|   2 | 泰国大枣      |    38 | NULL |    1 |  1 | 国外食品    |
|   3 | 新疆切糕      |    68 | NULL |    2 |  2 | 国内食品    |
|   4 | 十三香        |    10 | NULL |    2 |  2 | 国内食品    |
|   5 | 泰国大枣      |    20 | NULL |    2 |  2 | 国内食品    |
+-----+-----------------+-------+-------+------+----+--------------+
5 rows in set (0.00 sec)

显式内连接

1

SELECT * FROM A INNER JOIN B ON A.id = B.id

外连接

外联接可以是左向外联接、右向外联接或完整外部联接。也就是说外连接又分为左外连接、右外连接、全外连接

外连接需要有主表或者保留表的概念

在FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定

左外连接

1

LEFT JOIN 或者LEFT OUTER JOIN

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT *  FROM A RIGHT  JOIN B ON A.id = B.id
mysql> select * from product a right outer join category b on a.cid=b.id; #以右表
数据为准
+------+-----------------+-------+-------+------+----+--------------+
| pid | pname           | price | pdate | cid | id | cname       |
+------+-----------------+-------+-------+------+----+--------------+
|    1 | 泰国大榴莲    |    98 | NULL |    1 |  1 | 国外食品    |
|    2 | 泰国大枣      |    38 | NULL |    1 |  1 | 国外食品    |
|    3 | 新疆切糕      |    68 | NULL |    2 |  2 | 国内食品    |
|    4 | 十三香        |    10 | NULL |    2 |  2 | 国内食品    |
|    5 | 泰国大枣      |    20 | NULL |    2 |  2 | 国内食品    |
| NULL | NULL           |  NULL | NULL | NULL |  3 | 国内服装    |
+------+-----------------+-------+-------+------+----+--------------+
6 rows in set (0.00 sec)

全外连接（MySQL不支持）

FULL JOIN 或FULL OUTER JOIN

SELECT * FROM A FULL JOIN B ON A.id = B.id

外连接总结

通过业务需求，分析主从表

如果使用LEFT JOIN ，则主表在它左边

如果使用RIGHT JOIN ，则主表在它右边

查询结果以主表为主，从表记录匹配不到，则补null

Shell

什么是Shell

shell是壳的意思，好像嵌入在Linux这样的操作系统中的一个微型编程语言，Shell不像C语言，C++，Java等编程语言那么完整，但是Shell语言可以帮我们完成很多自动化任务，例如保存数据，监测系统的负载等

Shell相比C语言的优势在于它是完全嵌入在Linux中的，不需要安装，不需要编译，而且不需要学习太多新的东西

Shell脚本

脚本（Script）是批处理文件，是一种纯文本的程序

计算机脚本程序是确定的一系列控制计算机进行运行操作动作的组合，在其中可以实现一定的逻辑分支

不同的终端命令环境称为不同的Shell

几种主流的Shell

Sh：Bourne Shell的缩写，可以说是目前所有Shell的祖先

Bash：Bourne Again Shell，是Sh的进阶版本，比Sh更优秀，Bash是目前大多是Linux发行版和macOS操作系统的默认Shell

Ksh：Korn Shell，一般在收费的Unix版本上比较常见，但也有免费版本

Csh：C Shell，此Shell的语法优点类似C语言

Tcsh：Tenex C Shell，Csh的优化版本

Zsh：Z shell的，比较新近的一个Shell，集Bash，Ksh，Tcsh各家之大成，Github上有一个Zsh的配置程序叫oh-my-zsh

Shell可以做什么

Shell是管理命令行的程序，其实是Shell这个程序等待你输入那个命令

Shell可以做的事情

记住你前在终端里输入过的命令，用组合键Ctrl+R在终端的历史记录中搜索执行过的命令，用Tab键自动补全要输入的命令

以rc结尾的文件，里面包含了软件运行前会去读取并运行那些初始化命令

创建脚本文件

vim test.sh

sh就是shell的缩写，后缀名.sh已经成为一种约定俗成的命名惯例

指定脚本要使用的Shell

在写一个Shell脚本时，第一个要做的事就是指定要使用哪种Shell来解析和运行它，因为Sh、Ksh、Bash等等Shell的语法不尽相同

指定脚本要使用的Shell

#!/bin/bash，此语句并不是必不可少的，但是它可以保证此脚本会被指定的Shell执行

#!被称作Sha-bang，或者Shebang

运行命令

原则很简单，只需要写入你想要执行的命令

给脚本文件添加可执行的权限

加上可执行权限chmod +x test.sh

运行脚本

运行脚本文件./test.sh

以调试模式运行

电脑系统或程序中，隐藏着的一些未被发现的缺陷或问题的统称

调试一个脚本程序bash -x test.sh

参数-x表示以调试模式运行，Shell就会把我们的脚本文件运行时的细节打印出来

PATH环境变量

PATH是Linux的一个系统变量，这个变量包含了你系统里所有可以被直接执行的程序的路径

打印输出PATH变量的值：echo $PATH

Shell中的变量

Shell语言中也有变量，variable，可以用变量在内存中暂时存储信息

CentOS服务器

CentOS服务器基本配置

查看IP信息的两个命令

ifconfig：比较旧的命令，net-tools中的命令

ip addr：比较新的命令，iproute2中的命令

Linux

数据处理

grep命令

筛选数据（Globally search a Regular Expression and Print）的缩写，全局搜索一个正则表达式并打印，就是在文件中查找关键字，并显示关键字所在的行

grep命令的最基本用法

grep text file，text代表要搜索的文本，file代表供搜索的文件

-i参数：ignore，忽略大小写，默认情况下，grep命令是区分大小写的

-n参数：number，显示搜索到的文本所在的行号，grep -n path /etc/profile

-v参数：invert，只显示文本不在的行，grep -v path /etc/profile

-r参数：recursive，递归，grep -r "Hello World" folder/

注：rgrep命令，作用相当于grep -r

栈和队列

栈Stack

栈也是一种线性结构，相比数组，栈对应的操作是数组的子集

只能从一端添加元素，也只能从一端取出元素，这一端称为栈顶

栈是一种后进先出的数据结构，Last In First Out（LIFO），在计算机世界里，栈拥有着不可思议的作用

栈的应用

无处不在的Undo操作（撤销）

程序调用的系统栈

数组

数组基础

把数据码成一排进行存放

Linux

Linux几乎无处不在

品牌手机与电脑，为其提供服务的服务器基本都是Linux系统

Android系统的底层用的是修改过的Linux内核

全球500台最快的超级计算机中，100%采用Linux系统

电脑的启动顺序

• 启动界面（主板）
• 操作系统的启动（Windows）
• 其他程序的启动

[zhangxiaoyu@localhost ~]$

叫做“命令行提示符”，提示在其后面输入命令

zhangxiaoyu是当前用户的名字（username）,Linux是多用户的操作系统

@前面是用户名，后面是所在的域，例如87195243@qq.com

localhost是电脑、主机的名字，也就是主机名（hostname）

~表示当前用户的家目录（home directory），是当前所在目录的名字，会随着用户进入不同目录而改变