MySql数据库

        MySQL是一种开源的关系型数据库管理系统（RDBMS），它主要用于存储、管理和处理大量结构化数据。MySQL由瑞典MySQL AB公司开发，并由Oracle公司维护和支持。用户可以自由地获 取、使用和修改其源代码，也可根据需要进行定制和优化。

一、数据库操作

创建数据库和表：

MySQL 是最常用的数据库，在数据库操作中，基本都是增删改查操作，简称CRUD。

在这之前，需要先安装好 MySQL ，然后创建好数据库、数据表、操作用户。

数据库操作语言:

数据库在操作时，需要使用专门的数据库操作规则和语法，这个语法就是 SQL(Structured Query Language) 结构化查询语言。

SQL 的主要功能是和数据库建立连接，进行增删改查的操作。SQL是关系型数据库管理系统的标准语言。

SQL 语言的作用：

1. 数据定义语言 DDL(Data Definition Language) 。用于创建数据库，数据表。

2. 数据操作语言 DML(Data Manipulation Language) 。用于从数据表中插入、修改、删除数据。

3. 数据查询语言 DQL(Data Query Language) 。用于从数据表中查询数据。

4. 数据控制语言 DCL(Data Control Language) 。用来设置或修改数据库用户或角色的权限。

使用 SQL 操作数据库时，所有的 SQL 语句都以分号结束。(切换数据库时可以不用分号)

在 SQL 语句中，不区分大小写，编写 SQL 语句时可以根据情况用大小写的区别来增加可读性。

创建数据库:

1. 连接 MySQL

输入 mysql -u root -p 命令，回车，然后输入 MySQL 的密码(不要忘记了密码)，再回车，就连接上 MySQL 了。

mysql -u root -p

最初，都是使用 root 用户登录，工作中如果一直用 root 用户登录，因为权限太大，风险是很大的，所以等创建好权限适合的用户后，就不要经常登录 root 用户了。

2. 查看当前的数据库

使用 show databases; 查看当前安装的 MySQL 中有哪些数据库。

show databases;

刚安装 MySQL 时，默认有四个数据库，information_schema，mysql，perfomance_schema，sys 。通常情况下，我们不会直接使用这四个数据库，但千万不要把这四个数据库删了，否则会带来很多不必要的麻烦。如果不小心删了，建议是重新安装 MySQL ，在重装之前把自己的数据迁移出来备份好，或者从其他服务器上迁移一个相同的数据库过来。

3. 创建数据库

使用 create database 数据库名; 创建数据库。

create database MyDB_one;

4. 创建数据库时设置字符编码

使用 create database 数据库名 character set utf8; 创建数据库并设置数据库的字符编码。

create database MyDB_two character set utf8;

直接创建的数据库，数据库的编码方式是 MySQL 默认的编码方式 latin1 (单字节编码) ，通常我们会在数据库中存放中文数据，所以最好把数据库的编码方式设置成 utf-8 ，这样中文才能正常显示。

create database MyDB_three charset utf8;
character set 可以缩写成 charset ，效果是一样的。

5. 查看和显示数据库的编码方式

使用 show create database 数据库名; 显示数据库的创建信息。

show create database MyDB_one;
show create database MyDB_two;

如果不知道一个数据库的编码方式是什么，可以使用 show create database 数据库名 来查看数据库的编码方式。可以看到刚才创建的 MyDB_one 的编码方式是 MySQL 的默认编码 latin1 ，MyDB_two 的编码方式是 utf-8 。

当然，这种方式不能在创建的同时显示，只能查看一个已经存在的数据库的编码方式。

6. 使用 alter database 数据库名 character set utf8； 修改数据库编码

alter database MyDB_one character set utf8;

如果一个数据库的编码方式不符合使用需求，可以进行修改。刚才创建的 MyDB_one 经过修改后，编码方式也变成了 utf-8 。

7. 进入或切换数据库

使用 use 数据库名 进入或切换数据库。

1. use MyDB_one

2. use MyDB_two

刚连接上 MySQL 时，没有处于任何一个数据库中，如果要使用某一个数据库，就需要进入到这个数据库中。

use 数据库名 这个命令后面的分号可以省略，这是 SQL 语句中唯一可以省略分号的语句。

8. 显示当前数据库 select database();

select database();

进入数据库中，可以使用 select database(); 来查看当前处于哪个数据库中。长时间操作数据库时，在很多数据库中来回切换后，查看当前的数据库，避免操作错了数据库。

创建数据表:

1. 查看当前数据库中的表

使用 show tables；查看当前数据库中有哪些表。

show tables;
在刚才创建的数据库 MyDB_one 中，还没有创建任何表，所以当前是空的。

2. 创建表

使用 create table 表名(字段1 字段类型,字段2 字段类型,字段3 字段类型,…); 来创建一张表。

create table Phone_table(pid INT, name CHAR(20), price INT);
在 MyDB_one 中创建了一个叫 Phone_table 的数据表，这张表有三个字段 pid，name，price 。为了增加 SQL 的可读性，字段名我用的是小写，字段类型用大写。

3. 显示表信息

用 show create table 表名; 来显示已创建的表的信息。

show create table Phone_table;

使用 show create table 表名;  可以显示表的字段信息， MySQL 的引擎，和默认的字符编码等信息。与显示数据库信息一样，show 只能显示已经创建了的数据表的信息，不能在创建的同时显示信息。

如果想更好地展示表的字段信息，可以使用 desc 表名; 来显示表的字段信息。

4. 给表增加字段

使用 alter table 表名 add 字段名 数据类型; 为已存在的表添加一个新字段。

alter table Phone_table add color CHAR(20);

添加后，刚才的表中多了一个字段，新增成功。

5. 删除表的字段

使用 alter table 表名 drop 字段名; 删除一个表中已存在的字段。

alter table Phone_table drop price;

删除字段后，表中不再有该字段。

6. 修改字段的数据类型

使用 alter table 表名 modify 字段名 数据类型; 修改表中现有字段的数据类型。

alter table Phone_table modify name VARCHAR(12);

修改之后，该字段的数据类型发生改变。

7. 修改字段的数据类型并且改名

使用 alter table 表名 change 原字段名 新字段名 数据类型; 修改表中现有字段的字段名和类型。

alter table Phone_table change name pname CHAR(18);

现在，将表的 name 改成了 pname ，同时修改了 pname 的数据类型。

MySQL 常用字段类型:

一个数据表是由若干个字段组成的，一个表十几个字段也很正常，每个字段表示不同的信息，需要使用不同类型的数据。

所以在创建表的时候，要为每个字段指定适合的数据类型。

MySQL 中常用的字段类型有以下这些：

1. 整数类型

数据类型    数据范围
TINYINT    -128 -- 127
SMALLINT    -32768 -- 32767
MEDIUMINT    -2^23 -- 2^23-1
INT    -2^31 -- 2^31-1
BIGINT    -2^63 -- 2^63-1
2. 字符串类型

数据类型    字节范围    用途
CHAR(n)    0 -- 255字节    定长字符串
VARCHAR(n)    0 -- 65535字节    变长字符串
TEXT    0 -- 65535字节    长文本数据
LONGTEXT    0 -- 2^32-1字节    极大文本数据
BLOB    0 -- 65535字节    二进制长文本数据
LONGBLOB    0 -- 2^32-1字节    二进制极大文本数据
3. 小数类型

m 表示浮点数的总长度，n 表示小数点后有效位数。

数据类型    数据用法    数据范围
Float    Float(m,n)    7位有效数
Double    Double(m,n)    15位有效数
Decimal    Decimal(m,n)    28位有效数
4. 时间类型

数据类型    格式    用途
DATE    YYYY-MM-DD    日期
TIME    HH:MM:SS    时间
YEAR    YYYY    年份
DATETIME    YYYY-MM-DD HH:MM:SS    日期和时间
TIMESTAMP    10位或13位整数（秒数）    时间戳
5. 枚举类型

enum(枚举值1,枚举值2,...)

枚举类型只能在列出的值中选择一个，如性别。

二、sql语句的理解

 SQL（结构化查询语言）是一种用于管理和操作关系型数据库的语言。它是一种标准化的语言，广泛应用于各种数据库系统中，包括MySQL、Oracle、Microsoft SQL Server等。

SQL通过使用不同的命令和语句来完成对数据库的操作，包括数据的查询、插入、更新和删除等。以下是SQL的主要方面和常用语句的解释：

1.1数据查询（SELECT）：

SELECT语句用于从数据库中检索数据。您可以指定要查询的表格和字段，并可以通过WHERE子句添加条件以过滤结果。

1.2数据插入（INSERT）：

INSERT语句用于将新数据插入到数据库表中。您需要指定要插入的表格和值，以及要插入的列。

1.3数据更新（UPDATE）：

UPDATE语句用于更新表格中的数据。您可以指定要更新的表格和条件，并设置新的值。

1.4数据删除（DELETE）：

DELETE语句用于从表格中删除数据。您可以指定要删除的表格和条件，从而决定删除哪些行。

1.5数据排序（ORDER BY）：

ORDER BY子句用于对查询结果进行排序。您可以根据一个或多个字段按升序或降序排序查询结果。

1.6数据过滤（WHERE）：

WHERE子句用于在查询中添加条件，从而筛选出符合条件的数据。

1.7数据聚合（GROUP BY）：

GROUP BY子句用于根据指定的字段将数据分组。通常与聚合函数（如SUM、COUNT、AVG等）一起使用，以对每个组进行汇总计算。

1.8表格联结（JOIN）：

JOIN操作用于将多个表格中的数据进行关联。可以通过指定联结条件来决定如何关联表格，并从中获取所需的数据。

1.9数据约束（Constraints）：

约束是用于限制表格中数据的完整性和一致性的规则，包括主键约束、外键约束、唯一约束、默认值约束等。

1.10数据定义（DDL）：

DDL（数据定义语言）用于创建和管理数据库对象（如表格、索引、视图等），包括CREATE、ALTER和DROP等语句。

三、数据库的范式

关系数据库中的关系满足一定要求的，满足不同程度要求的为不同的范式。满足最低要求的叫第一范式，简称1NF；在第一范式的基础上满足进一步要求的称为第二范式，简称2NF，其余范式以此类推。对于各种范式之间有如下关系：

1. 第一范式 1NF

定义： 属于第一范式关系的所有属性都不可再分，即数据项不可分。

理解： 第一范式强调数据表的原子性，是其他范式的基础。如下图所示数据库就不符合第一范式：

上表将商品这一数据项又划分为名称和数量两个数据项，故不符合第一范式关系。改正之后如下图所示：

上表就符合第一范式关系。

但日常生活中仅用第一范式来规范表格是远远不够的，依然会存在数据冗余过大、删除异常、插入异常、修改异常的问题，此时就需要引入规范化概念，将其转化为更标准化的表格，减少数据依赖。

规范化： 一个低一级的关系模式通过模式分解可以转化为若干个高一级范式的关系模式的集合，这个过程叫做规范化。

2. 第二范式 2NF

定义： 若某关系R属于第一范式，且每一个非主属性完全函数依赖于任何一个候选码，则关系R属于第二范式。此处我们需要理解非主属性、候选码和完全函数依赖的概念。候选码： 若关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，则称该属性组为候选码。若一个关系中有多个候选码，则选定其中一个为主码。以下所有内容中，主码或候选码都简称为码。例如下图所示的学生表中，学号和姓名都可以唯一标识一个元组，故该表的候选码为学号和姓名，主码我们可以随便选定其中一个，则选学号为主码。

学号	姓名	年龄	性别
101	张三	19	女
102	李四	21	男
103	王五	20	男
104	赵六	19	女
105	马七	20	女

主属性： 所有候选码的属性称为主属性。不包含在任何候选码中的属性称为非主属性或非码属性。

在上面的学生表中，学号和姓名就是该关系的主属性，年龄和性别就是非主属性。

函数依赖： 设R(U)是属性集U上的关系模式，X、Y是U的子集。若对于R(U)的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称Y函数依赖于X或X函数确定Y。

完全函数依赖： 设R(U)是属性集U上的关系模式，X、Y是U的子集。如果Y函数依赖于X，且对于X的任何一个真子集X’，都有Y不函数依赖于X’，则称Y对X完全函数依赖。记作：如果Y函数依赖于X，但Y不完全函数依赖于X，则称Y对X部分函数依赖。

理解： 第二范式是指每个表必须有一个（有且仅有一个）数据项作为关键字或主键（primary key），其他数据项与关键字或者主键一一对应，即其他数据项完全依赖于关键字或主键。由此可知单主属性的关系均属于第二范式。

判断一个关系是否属于第二范式：

找出数据表中的所有码；
找出所有主属性和非主属性；
判断所有的非主属性对码的部分函数依赖。

第三范式 3NF:

定义： 非主属性既不传递依赖于码，也不部分依赖于码。

首先我们要理解传递函数依赖的概念。

理解： 第三范式要求在满足第二范式的基础上，任何非主属性不依赖于其他非主属性，即在第二范式的基础上，消除了传递依赖。

在下图S-L关系中，Sloc对Sno传递函数依赖，故该关系不属于第三范式。

四、数据库事务

数据库事务的四大特性

数据库系统必须维护事务的以下特性(简称ACID)：

原子性(Atomicity)

一致性(Consistency)

隔离性(Isolation)

持久性(Durability)

⑴ 原子性（Atomicity）
　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

⑵ 一致性（Consistency）
　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

　　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

⑶ 隔离性（Isolation）
　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

　　即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

　　关于事务的隔离性数据库提供了多种隔离级别。

⑷ 持久性（Durability）
　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

数据库事务的四大隔离级别

MySQL数据库针对这四种特性，为我们提供的四种隔离级别，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

　　1、Read uncommitted 读未提交

　　公司发工资了，把50000元打到我的账号上，但是该事务并未提交，而我正好去查看账户，发现工资已经到账，是50000元整，非常高兴。可是不幸的是，领导发现发给的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，最后我实际的工资只有2000元，空欢喜一场。

　　脏读是两个并发的事务，“事务A：领导发工资”、“事务B：我查询工资账户”，事务B读取了事务A尚未提交的数据。

　　当隔离级别设置为Read uncommitted时，就可能出现脏读，如何避免脏读，请看下一个隔离级别。

　　2、Read committed 读提交

　　我拿着工资卡去消费，系统读取到卡里确实有2000元，而此时老婆也正好在网上转账，把工资卡的2000元转到她账户，并在我之前提交了事务，当我扣款时，系统检查到工资卡已经没有钱，扣款失败，十分纳闷，明明卡里有钱，为何......

　　不可重复读是两个并发的事务，“事务A：消费”、“事务B：老婆网上转账”，事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

　　当隔离级别设置为Read committed时，避免了脏读，但是可能会造成不可重复读。

　　3、Repeatable read 重复读

　　当隔离级别设置为Repeatable read时，可以避免不可重复读。当我拿着工资卡去消费时，一旦系统开始读取工资卡信息（即事务开始），我老婆就不可能对该记录进行修改，也就是不能在此时转账。

　　虽然Repeatable read避免了不可重复读，但还有可能出现幻读。例如：老婆工作在银行部门，她时常通过银行内部系统查看我的信用卡消费记录。有一天，她正查询到我当月信用卡的总消费金额（select sum(amount) from transaction where month = 本月）为80元，而我此时正好在外面吃完大餐后在收银台买单，消费1000元，即新增了一条1000元的消费记录（insert transaction ... ），并提交了事务，随后老婆将我的当月信用卡消费的明细打印到A4纸上，却发现消费总额为1080元，老婆很诧异，以为出现了幻觉，幻读就这样产生了。

        4.Serializable序列化

数据库被设计为单线程数据库,可以防止上述所有问题

从安全性上考虑: Serializable>Repeatable read>read committed>readuncommitted

从效率上考虑: read uncommitted>read committed>Repeatableread>Serializable

真正使用数据的时候,根据自己使用数据库的需求,综合分析对安全性和对效率的要求,选择一个隔离级别使数据库运行在这个隔离级别上.mysql 默认下就是Repeatable read隔离级别，oracle 默认下就是read committed个隔离级别。

查询当前数据库的隔离级别:select@@tx_isolation;设置隔离级别:set [global/session] transactionisolation level xxxx;其中如果不写默认是session指的是修改当前客户端和数据库交互时的隔离级别,而如果使用golbal,则修改的是数据库的默认隔离级别。

五、数据库索引

索引及分类

1.索引的概念

索引是一种特殊的文件，包含着对数据表中所有记录的引用指针
通俗点说，索引就好比是一本书的目录，能加快数据库的查询速度
例如需要遍历 200 条数据，在没有索引的情况下，数据库会遍历全部 200 条数据后选择符合条件的
而有了相应的索引后，数据库会直接在索引中查找符合条件的选项
数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录

2.索引的作用

建立索引的目的是为了加快对表中记录的查找或排序：

设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建所有的最主要的原因
当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度
可以降低数据库的 IO 成本，并且索引还可以降低数据库的排序成本
通过创建唯一性索引，可以保证数据表中每一行数据的唯一性
可以加快表与表之间的连接
在使用分组和排序字句进行数据查询时，可大大减少分组和排序的时间

3.索引的副作用

为表设置索引也有代价需要付出：

索引需要占用额外的磁盘空间
对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址；而 InnoDB 引擎的表数据文件本身就是索引文件
在插入和修改数据时要花费更多的时间（因为索引也要随之变动）

4.索引的分类

4.1 普通索引
这是最基本的索引类型
而且它没有唯一性之类的限制
4.2 唯一性索引
与普通索引类似，区别是唯一索引列的所有值都只能出现一次，即必须唯一
当现有数据库中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存
数据库还可能防止添加将在表中创建重复键值的新数据
例如，如果在员工信息统计表中对员工的姓（name）上创建了唯一索引，则任何两个员工都不能同姓
4.3 主键索引
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”
在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型
该索引要求主键中每个值都唯一
4.4 全文索引
索引类型为 FULLTEXT
适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息
在 MySQL5.6 版本以前 FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引
全文索引可以在 CHAR、VARCHAR 或 TEXT 类型的列上创建
每个表只允许有一个全文索引
4.5 组合索引（单列索引与多列索引）
索引可以是单列上创建的索引，也可以是在多列上创建的索引
多列索引可以区分其中一列可能有相同值的行
如果经常同时搜索两列、多列、按两列或多列排序时，索引也很有帮助
例如，如果经常在同一查询中为姓和名两列设置查询条件，那么这两列上创建多列索引将很有意义
5.创建索引的原则依据
索引可以提升数据库查询的速度，但并不是任何情况下都适合创建索引
因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担
下面列出创建索引的原则依据：

表的主键、外键必须有索引；主键具有唯一性，索引值也是有唯一的，查询时可以快速定位到数据行；外键一般关联的是另一个表的主键，所以在多表查询时也可以快速定位
记录数超过300行的表应该有索引；如果没有索引，需要把表遍历一遍，会严重影响数据库的性能
经常与其他表进行连接的表，在连接字段上应该建立索引
唯一性太差的字段不适合建立索引，并不能提升查询速度，反而会变慢
更新太频繁地字段不适合创建索引；在表中进行增、删、改、查时，索引也会有响应操作产生；字段更新的过于频繁，会导致对于系统资源的过多占用
经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引
索引应该建在选择性高的字段上；如果很少的字段拥有相同值，即有很多独特之，则选择性很高
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

六、sql优化的常用方法

1、explain 输出执行计划

在select语句前加上explain就可以了(MySQL 5.6开始，不仅仅支持select )能够简单分析sql的执行情况，是否走索引等。

• type列，连接类型。一个好的SQL语句至少要达到range级别。从最好到最差的连接类型为const、eq_reg、- ref、range、index和ALL。一般来说，得保证查询至少达到range级别（范围扫描），最好能达到ref（索引访问）。

• key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。

• key_len列，索引长度。

• rows列，扫描行数。该值是个预估值。

• extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

2、in 和 not in 要慎用

SQL语句中IN包含的值不应过多，MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的

ep:

select id from t where num in(1,2,3)

select id from t where num between 1 and 3

对于连续的数值，能用 between 就不要用 in 了：

当IN的取值范围较大时会导致索引失效，走全表扫描。By the way：如果使用了 not in，则不走索引

3、少用select *

SELECT语句务必指明字段名称，select * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；

4、善用limit 1

这是为了使explain中type列达到const类型。当只需要一条数据的时候，使用limit 1，如果加上limit1，查找到就不用继续往后找了。

5、 order by字段建索引

避免全表扫描，首先应考虑在 where 及 order by涉及的列上建立索引，如果排序字段没有用到索引，就尽量少排序,可以在程序中排序。

6、count(*)推荐使用

count()、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。
count() 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

7、where 子句中避免is null /is not null

应尽量避免在 where 子句中对字段进行 null 值判断，使用is null 或者is not null 理论上都会走索引,存在Null值会导致mysql优化器处理起来比较复杂，容易导致引擎放弃使用索引而进行全表扫描。

select id from t where num is null

所以设计表字段时尽量避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。

 8、应尽量避免在 where!=或<>

子句中使用!=或<>操作符，引擎放弃使用索引而进行全表扫描。

9、应尽量避免在 where 子句中使用 or

如果使用or那么要求or两边的条件字段都要有索引,才会走索引,如果其中一边有一个字段没索引, 另一个字段上的索引也会失效。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

应尽量避免在 where 子句中使用 or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。

select id from t where num=10 or num=20

可以这样查

select id from t where num=10
union all
select id from t where num=20

10、尽量用union all代替union 

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

11、应尽量避免在where子句中对字段进行函数操作

select id from t where substring(name,1,3)=‘abc’

不要在子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

12、可以选择 exists 代替 in

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

13、避免%xxx式查询

在非覆盖索引场景下,大家知道MySQL索引有最左原则,所以通过 like '%xx%'查询的时候一定会造成索引失效(5.7版本覆盖索引可以走索引),一般采用like 'xx%'右边匹配的方式来索引。

当想要获取的字段多了以后，select * from t_user where name like “%xx”; 要查询的数据就不能只在索引树里找了，得需要回表操作才能完成查询的工作，再加上是左模糊匹配，无法利用索引树的有序性来快速定位数据，所以得在索引树逐一遍历，获取主键值后，再到聚簇索引树检索到对应的数据行，这样实在太累了。
优化器认为上面这样的查询过程的成本实在太高了，所以直接选择全表扫描的方式来查询数据。

所以，使用左模糊匹配（like ‘%xx’）并不一定会走全表扫描，但也容易失效，关键还是看数据表中的字段。

14、选择重复值较低的字段建索引

在创建索引时，一定要选择重复值较低的字段。离散型非常的差，优化器可能直接就选择不走索引了，因为优化器可能认为，走索引和全表扫描差不多。值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段不适合做索引。

15、高效的分页

select id,name,age from user limit 10000, 20;

mysql会查询10020条，然后丢弃前面10000条，这个比较浪费资源
可以优化：

select id,name,age from user id>10000 limit 20;

16、join使用问题 

用连接查询代替子查询、join表不易超过3个、小表驱动大表、链接字段建索引。

17、关于索引本身

使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引。

七、sql语句的顺序

1.书写顺序(从上往下顺序书写)

SELECT <字段名> 
FROM <表名>
JOIN <表名> 
ON <连接条件>
WHERE <筛选条件>
GROUP BY <字段名>
HAVING <筛选条件>
UNION 连接两个虚拟表，合并两个表生成一个新的虚拟表
ORDER BY <字段名>
LIMIT <限制行数>;

2.执行顺序

（1.FORM：选择from后面跟的表，产生虚拟表1。
（2.ON：ON是JOIN的连接条件，符合连接条件的行会被记录在虚拟表2中。
（3.JOIN：如果指定了LEFT JOIN，那么保留表中未匹配的行就会作为外部行添加到虚拟表2中，产生虚拟表3。如果有多个JOIN链接，会重复执行步骤1~3，直到处理完所有表。
（4.WHERE：对虚拟表3进行WHERE条件过滤，符合条件的记录会被插入到虚拟表4中。
（5.GROUP BY：根据GROUP BY子句中的列，对虚拟表2中的记录进行分组操作，产生虚拟表5。
（6.HAVING：对虚拟表5进行HAVING过滤，符合条件的记录会被插入到虚拟表6中。
（7.SELECT：SELECT到一步才执行，选择指定的列，插入到虚拟表7中。
（8.UNION：UNION连接的两个SELECT查询语句，会重复执行步骤1~7，产生两个虚拟表7，UNION会将这些记录合并到虚拟表8中。
（9.ORDER BY: 将虚拟表8中的记录进行排序，虚拟表9。
（10.LIMIT：取出指定行的记录，返回结果集。

3.总结

书写顺序：SELECT -> FROM -> JOIN -> ON -> WHERE -> GROUP BY -> HAVING -> UNION -> ORDER BY ->LIMIT

执行顺序：FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> UNION -> ORDER BY ->LIMIT

 注意：

1.SELECT语句总是写在最前面，但在大部分语句之后才执行。所以在SQL语句中，我们不能在WHERE、GROUP BY、 HAVING语句中使用在 SELECT 中设定的别名。

但是MYSQL有个特性，在GROUP BY、 HAVING语句中，可以使用 SELECT 中设定的别名。这不是因为MYSQL中会提前执行SELECT，而是因为在GROUP BY这一步返回了游标，大家了解一下就好。

2.无论是书写顺序，还是执行顺序，UNION 都是排在 ORDER BY 前面的。SQL语句会将所有UNION 段合并后，再进行排序。

八、sql单表查询以及多表联查

1.单表查询

1.1 根据表名查询全部数据

select * from 表名；

1.2 根据表名查询某些字段的信息

select 字段1，字段2，字段3 from 表名；

1.3 知道表名按照一定条件查询

select * from 表名 where 查询条件；

1.4

关系运算符：（=等于、>大于、>=大于等于、<小于、<=小于等于、!=不等）

逻辑运算符：and or not

select * from 表名 where 条件1 and 条件2；

1.5 查询范围内数据

select * from 表名 where 字段名 between 数字1 and 数字2；

1.6 模糊查询

select * from 表名 where 字段名 like %匹配数据%；

1.7 分组查询

select * from 表名 group by 字段 having 条件；（having与where作用相同，但having只能用于group by这个函数）

1.8 分页查询

select * from 表名 limit m,n;     (m代表从第m+1条数据开始查，n代表一页多少条)

1.9  排序

select * from 表名 order by 按照排序的字段名; (默认顺序，倒叙在字段名后加desc)

1.10 分页查询和排序组合

可以查询最后几条数据或者最前几条数据：
select * from 表名 order by 字段名 limit 要从第几的下条开始查,显示几条数据;

1.11 聚合函数
查个数：count()、求和sum()、平均avg()、最大max()、最小min()

2.联表查询

一、多表关系
概述:
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多（多对一）：在多的一方建立外键，指向一的一方的主键
多对多：建立中间表，包含两个外键，分别关联两方主键
一对一：一对一关系多用于单标拆分，将一张表的基础字段放在一张表中，其他详细字段放在另一张表，以提升操作效率；实现：在仁义一方加入外键，并且设置外键为唯一的（UNIQUE）
二、多表查询概述
多表查询概述:
概述：指从多张表中查询数据
笛卡尔积：笛卡尔积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

多表查询分类：
连接查询：
①内连接：相当于查询A,B交集部分数据
②外连接：
左外连接：查询左表所有数据，以及两张表交集部分数据
右外连接：查询右表所有数据，以及两张表交集部分数据
③自连接：当前表与自身的连接查询，自连接必须使用表别名
子查询：

三、内连接
（一）内连接查询语法
隐式内连接：
SELECT 字段列表 FROM 表1,表2 WHERE 条件；

显式内连接：
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件
内连接查询是两张表交集的部分（绿色部分）

四、外连接
左外连接:
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件....;
相当于查询表1（左表）的所有数据包含表1和表2交集部分的数

右外连接:
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件.....;
相当于查询表2（右表）的所有数据包含表1和表2交集部分的数据

五、自连接
（一）自连接查询语法
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件;
1
自连接查询，可以是内连接查询，也可以是外连接查询。

（二）联合查询
联合查询-union,union all
对于union查询，就是把多查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A
UNION ALL
SELECT 字段列表 FROM 表B;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

六、子查询
概念：
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT FROM t1 WHERE column1 =(SELECT column1 FROM t2);
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。

根据子查询结果不同，分为：
标量子查询（子查询结果为单个值）
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。
常用的操作符：=<>>>=<<=

列子查询（子查询结果为一列）
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN、NOT IN、ANY、SOME、ALL

操作符描述
IN    在指定的集合范围之内，多选一
NOT IN    不在指定的集合范围之内
ANY    子查询返回列表中，有任意一个满足即可
SOME    与ANY等同，使用SOME的地方都可以使用
ANYALL    子查询返回列表的所有值都必须满足
行子查询（子查询结果为一行）
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：=、<>、IN、NOT IN

表子查询（子查询结果为多行多列
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN

根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。