MySQL笔记(一)SQL基础

SQL

数据定义语言（Data Definition Language，DDL）

用来创建或删除数据库以及表等对象，主要包含以下几种命令：

• DROP：删除数据库和表等对象
• CREATE：创建数据库和表等对象
• ALTER：修改数据库和表等对象的结构

数据操作语言（Data Manipulation Language，DML）

用来变更表中的记录，主要包含以下几种命令：

• SELECT：查询表中的数据
• INSERT：向表中插入新数据
• UPDATE：更新表中的数据
• DELETE：删除表中的数据

数据查询语言（Data Query Language，DQL）

用来查询表中的记录，主要包含 SELECT 命令，来查询表中的数据。

数据控制语言（Data Control Language，DCL）

用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对数据库中的用户设定权限。主要包含以下几种命令：

• GRANT：赋予用户操作权限
• REVOKE：取消用户的操作权限
• COMMIT：确认对数据库中的数据进行的变更
• ROLLBACK：取消对数据库中的数据进行的变更

常用的SQL命令

SELECT

SELECT 命令用于从数据库中查询（选取）数据，它的用法如下：

SELECT column1, column2....columnN
FROM  table_name;

SQL SELECT 语句用于从表中选取符合条件的数据，该数据以临时表的形式返回，称为结果集。

SELECT 语句的基本语法如下：

SELECT column1, column2, columnN
FROM table_name
WHERE conditions;

• column1, column2, columnN 表示选取的列，conditions 表示筛选条件，只有满足条件的数据才会被选取。

WHERE 子句是可选的，您可以不写，此时 SELECT 语句将变成下面的形式：

SELECT column1, column2, columnN FROM table_name;

不使用 WHERE 子句意味着没有筛选条件，此时表中的所有数据都将被选取。

此外，如果您希望选取所有的列，那么可以使用*代替所有列名，语法如下：

SELECT * FROM table_name;

SELECT 子句

SELECT 可以结合下面的子句一起使用：

• WHERE 子句：用来指明筛选条件，只有满足条件的数据才会被选取。 and | or
• ORDER BY 子句：按照某个字段对结果集进行排序。
• GROUP BY 子句：结合聚合函数，根据一个或多个列对结果集进行分组。
• HAVING 子句：通常和 GROUP BY 子句联合使用，用来过滤由 GROUP BY 子句返回的结果集。

UPDATE

UPDATE 命令用于更新数据库中的数据，它的用法如下：

UPDATE <表名> 
SET 字段 1=值 1 [,字段 2=值 2… ] 
[WHERE 子句 ]
[ORDER BY 子句]
[LIMIT 子句]

# 在 tb_courses_new 表中，更新所有行的 course_grade 字段值为 4
update tb_courses_new set course_grade=4;

• 根据条件修改表中的数据

# 在 tb_courses 表中，更新 course_id 值为 2 的记录，将 course_grade 字段值改为 3.5，将 course_name 字段值改为“DB”
update tb_courses_new set course_name='DB',course_grade=3.5
where course_id=2;

• 多表关联更新

MySQL 可以基于多表查询更新数据。对于多表的 UPDATE 操作需要慎重，建议在更新前，先使用 SELECT 语句查询验证更新的数据与自己期望的是否一致。

下面我们建两张表，一张表为 product 表，用来存放产品信息，其中有产品价格字段 price；另外一张表是 product_price 表。现要将 product_price 表中的价格字段 price 更新为 product 表中价格字段 price 的 80%。

1. 使用UPDATE

# 使用“UPDATE table1 t1,table2,...,table n”的方式来多表更新
update product as p, product_price as pp set pp.price = p.price * 0.8 where p.productid= pp.productId;

2. 通过INNER JOIN

# 是使用 inner join 来多表更新
update product as p inner join product_price pp on p.productid= pp.productid set pp.price = p.price * 0.8;

3. 通过LEFT JOIN

# 也可以使用 left join 来做多表更新，如果 product_price 表中没有产品价格记录的话，将 product 表的 isdelete 字段设置为 1。在 product 表添加 1006 商品，且不在 product_price 表中添加对应信息
update product as p left join product_price as pp on p.productid= pp.productid set p.isdelete = 1 where pp.productid is null;

4. 通过子查询

# 也可以通过子查询进行多表更新
update product_price pp set price = (select price*0.8 from product where productid = pp.productid);

上面的几个例子都是在两张表之间做关联，只更新一张表中的记录。MySQL 也可以同时更新两张表，如下语句就同时修改了两个表。

# 两张表做关联，同时更新了 product_price 表的 price 字段和 product 表的 dateUpdate 两个字段。
update product p inner join product_price pp on p.productid= pp.productid set pp.price = p.price * 0.8, p.dateUpdate = CURDATE()

日常开发中，一般都是用单表 UPDATE 语句，很少写多表关联的 UPDATE。

DELETE

DELETE 命令用于从数据库中删除数据，它的用法如下：

DELETE 
FROM <表名> 
[WHERE 子句] 
[ORDER BY 子句] 
[LIMIT 子句]

• 删除表中的全部数据

# 删除 tb_courses_new 表中的全部数据
 delete from tb_courses_new;

• 根据条件删除表中的数据

# 在 tb_courses_new 表中，删除 course_id 为 4 的记录
delete from tb_courses where course_id=4;

TRUNCATE

truncate和delete的区别：

从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，但是在某些情况下，两者在使用上有所区别。

• DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
• DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
• DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
• DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
• DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
• DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。
• 当不需要该表时，用 DROP；当仍要保留该表，但要删除所有记录时，用 TRUNCATE；当要删除部分记录时，用 DELETE。

CREATE TABLE

CREATE TABLE 命令用于创建一个新的数据表，它的用法如下：

create table table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
primary key( one or more columns )
);

实例

create table website (
    id      int              NOT NULL   AUTO_INCREMENT    comment '用户的id,手机号码',
    name    VARCHAR(20)      NOT NULL,
    url     VARCHAR(30)                 DEFAULT '',
    age     TINYINT UNSIGNED NOT NULL,
    alexa   INT UNSIGNED     NOT NULL,
    uv      FLOAT                       DEFAULT '0',
    country CHAR(3)          NOT NULL   DEFAULT '',
    primary key (`id`)
);

几点说明：

• 某些字段使用了 NOT NULL 约束，表名在插入数据时这些字段不能为 NULL；
• AUTO_INCREMENT 约束用来将某个字段的值设置为自动增长的序列；
• DEFAULT 约束用来设置字段的默认值；
• PRIMARY KEY 用来设置表的主键。

ALTER TABLE

ALTER TABLE 命令用于修改数据表。ALTER TABLE 可以用来修改数据表的字段，例如：

alter table old_table_name rename to new_table_name; 

rename old_table _name to new_table_name;

也可以用来修改数据表的名称，例如：

alter table `table_name` rename to new_table_name;

DROP TABLE

DROP TABLE 命令用来删除数据表，它的用法如下：

drop table `table_name`;

CREATE DATABASE

CREATE DATABASE 用来创建一个新的数据库，它的用法如下：

create database database_name;

INSERT INTO

INSERT INTO 命令向数据库中插入新的数据，它的用法如下：

insert into table_name( column1, column2....columnN)
values ( value1, value2....valueN);
或者
insert into <表名>
set <列名1> = <值1>,
    <列名2> = <值2>,
    …

实例

INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('百度', 'https://www.baidu.com/', 21, 4, 5010.5, 'CN');

INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('淘宝', 'https://www.taobao.com/', 17, 8, 3996.75, 'CN');

INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('C语言中文网', 'http://c.biancheng.net/', 12, 7923, 11.62, 'CN');

INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('Google', 'https://www.google.com/', 23, 1, 36474, 'US');

INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('GitHub', 'https://github.com/', 13, 95, 216.3, 'US');

INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('Stack Overflow', 'https://stackoverflow.com/', 16, 48, 592.2, 'US');

INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('Yandex', 'http://www.yandex.ru/', 11, 53, 591.82, 'RU');

• 向表中指定字段添加值

为表的指定字段插入数据，是在 INSERT 语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值.

# 在 tb_courses 表中插入一条新记录，course_name 值为“System”，course_grade 值为 3，course_info 值为“Operating System”，
insert into tb_courses
    -> (course_name,course_grade,course_info)
    -> values('System',3,'Operation System');

• 使用 INSERT INTO…FROM 语句复制表数据

INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中。

SELECT 子句返回的是一个查询到的结果集，INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

CREATE INDEX

CREATE INDEX 命令用于创建索引，它的用法如下：

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);

DROP INDEX

DROP INDEX 命令用来删除索引，它的用法如下：

ALTER TABLE table_name
DROP INDEX index_name;

单表查询

WHERE：条件查询数据

语法格式：

• WHERE 查询条件

查询条件可以是：

• 带比较运算符和逻辑运算符的查询条件
• 带 BETWEEN AND 关键字的查询条件
• 带 IS NULL 关键字的查询条件
• 带 IN 关键字的查询条件
• 带 LIKE 关键字的查询条件
• 带 AND OR XOR 多条件查询
  • AND：记录满足所有查询条件时，才会被查询出来。
  • OR：记录满足任意一个查询条件时，才会被查询出来。
  • XOR：记录满足其中一个条件，并且不满足另一个条件时，才会被查询出来。

LIKE子句：模糊匹配

SQL LIKE 子句用于在 WHERE 语句中进行模糊匹配，它会将给定的匹配模式和某个字段进行比较，匹配成功则选取，否则不选取。

• LIKE 子句可以和通配符一起使用：

“%”是 MySQL 中最常用的通配符，它能代表任何长度的字符串，字符串的长度可以为 0。例如，a%b表示以字母 a 开头，以字母 b 结尾的任意长度的字符串。该字符串可以代表 ab、acb、accb、accrb 等字符串。

实例

• NOT LIKE 表示字符串不匹配时满足条件。

• 带有“_”通配符的查询

“_”只能代表单个字符，字符的长度不能为 0。例如，a_b可以代表 acb、adb、aub 等字符串。

匹配的字符串必须加单引号或双引号。

使用通配符的注意事项和技巧

注意事项：

• 注意大小写。MySQL 默认是不区分大小写的。如果区分大小写，像“Tom”这样的数据就不能被“t%”所匹配到。
• 注意尾部空格，尾部空格会干扰通配符的匹配。例如，“T% ”就不能匹配到“Tom”。
• 注意 NULL。“%”通配符可以到匹配任意字符，但是不能匹配 NULL。也就是说 “%”匹配不到 tb_students_info 数据表中值为 NULL 的记录。

技巧：

• 不要过度使用通配符，如果其它操作符能达到相同的目的，应该使用其它操作符。因为 MySQL 对通配符的处理一般会比其他操作符花费更长的时间。
• 在确定使用通配符后，除非绝对有必要，否则不要把它们用在字符串的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
• 如果查询内容中包含通配符，可以使用“\”转义符。例如，在 tb_students_info 表中，将学生姓名“Dany”修改为“Dany%”后，查询以“%”结尾的学生姓名。

ORDER BY子句：对返回结果排序

SQL ORDER BY 子句用于根据一个或者多个字段对查询结果（结果集）进行排序，可以是降序(DESC)，也可以是升序(ASC)。默认情况下，大部分数据库将查询结果按照升序排序。

ORDER BY 子句的基本语法如下所示：

SELECT column_list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

您可以在 ORDER BY 子句中指定多个用于排序的字段，它们之间以逗号,分隔；但是，您应该确保这些字段都位于 column_list 中。

ASC 关键字表示升序，DESC 关键字表示降序；如果不写，大部分数据库默认为 ASC。ORDER BY在最后。

GROUP BY子句：对返回结果进行分组

SQL GROUP BY 子句用来根据指定的字段对结果集（选取的数据）进行分组，如果某些记录的指定字段具有相同的值，那么它们将被合并为一条数据。通俗地理解，GROUP BY 子句将根据指定的字段合并数据行。GROUP BY在WHERE之后。

借助 SQL 聚合函数，您可以对分组的数据进行再次加工，例如：

• SUM( ) 函数可以对指定字段的值进行求和；
• COUNT( ) 函数可以计算某个分组内数据的条数；
• AVG( ) 函数可以对指定字段的值求平均数。

GROUP BY 子句的基本语法如下：

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

GROUP 子句使用说明：

• GROUP BY 子句需要和 SELECT 语句一起使用；
• 如果有 WHERE 子句，那么 WHERE 子句需要放在 GROUP BY 子句之前；
• 如果有 ORDER BY 子句，那么 ORDER BY 子句需要放在 GROUP 子句之后。

如果您想知道每个国家网站的总访问量，那么 GROUP BY 子句的写法如下：

select country, sum(uv) as total 
from website
group by country;

您可以使用 TRUNCATE() 函数让小数保留两位数字，具体写法如下：

select country, truncate(sum(uv), 2) as total 
from website
group by country;

• GROUP BY 与 WITH ROLLUP

WITH ROLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

HAVING：过滤分组

在 MySQL 中，可以使用 HAVING 关键字对分组后的数据进行过滤。

• HAVING <查询条件>

HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法

但是 WHERE 和 HAVING 关键字也存在以下几点差异：

• 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
• WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
• WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
• WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
• WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

DISTINCT关键字：对返回结果删除重复记录

SQL DISTINCT 关键字需要和 SELECT 语句一起使用，用来删除结果集中所有重复的记录，仅保留唯一的一条记录。

数据表中有时候会有重复的记录，如果您只需要其中一条，就可以使用 DISTINCT 关键字。

关于 UNIQUE 关键字

UNIQUE 和 DISTINCT 的效果是等价的，都用来消除结果集中的重复记录，但是 UNIQUE 是一种老式的语法，Oracle 数据库曾经使用过。

后来 ANSI（美国国家标准委局）将 DISTINCT 定义为正式的关键字，Oracle 也随即增加了对 DISTINCT 的支持；但是为了保持向后兼容，Oracle 并没有取消 UNIQUE 关键字。

DISTINCT 关键字的基本语法格式如下：

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

示例

LIMIT：限制查询结果的条数

当数据表中有上万条数据时，一次性查询出表中的全部数据会降低数据返回的速度，同时给数据库服务器造成很大的压力。这时就可以用 LIMIT 关键字来限制查询结果返回的条数。

LIMIT 是 MySQL 中的一个特殊关键字，用于指定查询结果从哪条记录开始显示，一共显示多少条记录。

LIMIT 关键字有 3 种使用方式，即指定初始位置、不指定初始位置以及与 OFFSET 组合使用。

• LIMIT 初始位置，记录数

“初始位置”表示从哪条记录开始显示；“记录数”表示显示记录的条数。第一条记录的位置是 0，第二条记录的位置是 1。后面的记录依次类推。注意：LIMIT 后的两个参数必须都是正整数。

# tb_students_info 表中，使用 LIMIT 子句返回从第 4 条记录开始的行数为 5 的记录
select * from tb_students_info limit 3,5;

• LIMIT 记录数

LIMIT 关键字不指定初始位置时，记录从第一条记录开始显示。显示记录的条数由 LIMIT 关键字指定。其中，“记录数”表示显示记录的条数。如果“记录数”的值小于查询结果的总数，则会从第一条记录开始，显示指定条数的记录。如果“记录数”的值大于查询结果的总数，则会直接显示查询出来的所有记录。

# 显示 tb_students_info 表查询结果的前 4 行
select * from tb_students_info limit 4;

• LIMIT 记录数 OFFSET 初始位置

“初始位置”指定从哪条记录开始显示；“记录数”表示显示记录的条数。

IS NULL: 空值查询

MySQL 提供了 IS NULL 关键字，用来判断字段的值是否为空值（NULL）。空值不同于 0，也不同于空字符串。

• IS NOT NULL 表示查询字段值不为空的记录。

多表查询

交叉连接

交叉连接（CROSS JOIN）一般用来返回连接表的笛卡尔积。

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句] 

多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。

• 笛卡尔积

笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。

例如，有 A 和 B 两个集合，它们的值如下：

A = {1,2}
B = {3,4,5}

集合 A×B 和 B×A 的结果集分别表示为：

A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。

并且，从以上结果我们可以看出：

• 两个集合相乘，不满足交换率，即 A×B≠B×A。
• A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数

多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。在实际应用中，应避免使用笛卡尔积，因为笛卡尔积中容易存在大量的不合理数据，简单来说就是容易导致查询结果重复、混乱。

内连接

内连接（INNER JOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。

内连接使用 INNER JOIN 关键字连接两张表，并使用 ON 子句来设置连接条件。如果没有连接条件，INNER JOIN 和 CROSS JOIN 在语法上是等同的，两者可以互换。

语法格式：

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
或者
SELECT <字段名> FROM <表1> JOIN <表2> [ON子句]

• 字段名：需要查询的字段名称。
• <表1><表2>：需要内连接的表名。
• INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
• ON 子句：用来设置内连接的连接条件。

INNER JOIN 也可以使用 WHERE 子句指定连接条件，但是 INNER JOIN ... ON 语法是官方的标准写法，而且 WHERE 子句在某些时候会影响查询的性能。

多个表内连接时，在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。

select tb_students_info.name,tb_course.name from tb_students_info inner join tb_course on tb_students_info.id = tb_course.id;

外连接

内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

外连接可以分为左外连接和右外连接，下面根据实例分别介绍左外连接和右外连接。

左外连接

左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

语法格式

SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
or
SELECT <字段名> FROM <表1> LEFT JOIN <表2> <ON子句>

• 字段名：需要查询的字段名称。
• <表1><表2>：需要左连接的表名。
• LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
• ON 子句：用来设置左连接的连接条件，不能省略。

上述语法中，“表1”为基表，“表2”为参考表。左连接查询时，可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。如果“表1”的某行在“表2”中没有匹配行，那么在返回结果中，“表2”的字段值均为空值（NULL）。

# 在 tb_students_info 表和 tb_course 表中查询所有学生姓名和相对应的课程名称，包括没有课程的学生
select 
			s.name,c.course_name 
from 
			tb_students_info s 
left outer join 
			tb_course c 
on 
			s.`course_id`=c.`id`;

右外连接

右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

语法格式：

SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>
or
SELECT <字段名> FROM <表1> RIGHT JOIN <表2> <ON子句>

与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。

# 在 tb_students_info 表和 tb_course 表中查询所有课程，包括没有学生的课程
select  from tb_students_info as s right outer join tb_course as c on s.course_id = c.id;

多个表左/右连接时，在 ON 子句后连续使用 LEFT/RIGHT OUTER JOIN 或 LEFT/RIGHT JOIN 即可。

使用外连接查询时，一定要分清需要查询的结果，是需要显示左表的全部记录还是右表的全部记录，然后选择相应的左连接和右连接。

子查询

子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。

语法格式：

where <表达式> <操作符> (子查询)

其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

• IN | NOT IN

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。

• EXISTS | NOT EXISTS

用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

IN

# 使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名
select name from tb_students_info where course_id in (select id from tb_course where course_name = "Java");

上述查询过程也可以分为以下 2 步执行:

• 首先单独执行内查询，查询出 tb_course 表中课程为 Java 的 id

SELECT id FROM tb_course WHERE course_name = 'Java'

• 然后执行外层查询，在 tb_students_info 表中查询 course_id 等于 1 的学生姓名

SELECT name FROM tb_students_info WHERE course_id IN (1);

习惯上，外层的 SELECT 查询称为父查询，圆括号中嵌入的查询称为子查询（子查询必须放在圆括号内）。MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询。

NOT IN

# 查询没有学习 Java 课程的学生姓名
SELECT name FROM tb_students_info WHERE course_id NOT IN (SELECT id FROM tb_course WHERE course_name = 'Java');

=

# 在 tb_course 表和 tb_students_info 表中查询出所有学习 Python 课程的学生姓名
SELECT name FROM tb_students_info WHERE course_id = (SELECT id FROM tb_course WHERE course_name = 'Python');

<>

# 在 tb_course 表和 tb_students_info 表中查询出没有学习 Python 课程的学生姓名
SELECT name FROM tb_students_info WHERE course_id <> (SELECT id FROM tb_course WHERE course_name = 'Python');

EXISTS

# 查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中的记录
select * from tb_students_info where (select course_name from tb_course where id = 1);

EXISTS 关键字可以和其它查询条件一起使用，条件表达式与 EXISTS 关键字之间用 AND 和 OR 连接。

# 查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中 age 字段大于 24 的记录
select * from tb_students_info where age >24 and exists (select course_name from tb_course where id =1);

子查询的功能也可以通过表连接完成，但是子查询会使 SQL 语句更容易阅读和编写。

一般来说，表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换。子查询比较灵活、方便、形式多样，适合作为查询的筛选条件，而表连接更适合于查看连接表的数据。

注意事项

• 子查询语句可以嵌套在 SQL 语句中任何表达式出现的位置

在 SELECT 语句中，子查询可以被嵌套在 SELECT 语句的列、表和查询条件中，即 SELECT 子句，FROM 子句、WHERE 子句、GROUP BY 子句和 HAVING 子句。

# 嵌套在 SELECT 语句的 SELECT 子句中的子查询语法格式如下。
select (子查询) from 表名;

子查询结果为单行单列，但不必指定列别名。

# 嵌套在 SELECT 语句的 FROM 子句中的子查询
select * from  (子查询) as 表的别名;

必须为表指定别名。一般返回多行多列数据记录，可以当作一张临时表。

• 只出现在子查询中而没有出现在父查询中的表不能包含在输出列中

多层嵌套子查询的最终数据集只包含父查询（即最外层的查询）的 SELECT 子句中出现的字段，而子查询的输出结果通常会作为其外层子查询数据源或用于数据判断匹配。

# 常见错误如下 -> X
select * from (select * from result);
# 这个子查询语句产生语法错误的原因在于主查询语句的 FROM 子句是一个子查询语句，因此应该为子查询结果集指定别名

# 正确的 
select * from (select * from result) as temp;

子查询改为表链接

子查询如递归函数一样，有时侯能达到事半功倍的效果，但是其执行效率较低。与表连接相比，子查询比较灵活，方便，形式多样，适合作为查询的筛选条件，而表连接更适合查看多表的数据。

一般情况下，子查询会产生笛卡儿积，表连接的效率要高于子查询。因此在编写 SQL 语句时应尽量使用连接查询。

表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换。

在检查那些倾向于编写成子查询的查询语句时，可以考虑将子查询替换为表连接，看看连接的效率是不是比子查询更好些。同样，如果某条使用子查询的 SELECT 语句需要花费很长时间才能执行完毕，那么可以尝试把它改写为表连接，看看执行效果是否有所改善。

• 改写用来查询匹配值的子查询

下面这条示例语句包含一个子查询，它会把 score 表里的考试成绩查询出来：

select * from score where grade_id in(select id from grade where category = "Java");

在编写以上语句时，可以不使用子查询，而是把它转换为一个简单的连接：

Select 
	s.* 
from 
	score as s 
inner join 
	grade as g
on 
	s.grade_id = g.id
where g.category ="Java";

再来看另一个示例。下面这条查询语句可以把所有女生的考试成绩查询出来：

select * from score where student_id in (select student_id from student where sex="F");

这条语句可以转换为以下连接：

select score.*
from 
	score as s
inner join 
	student 
on 
	score.student_id = student.student_id
where 
student.sex ="F";

我们可以发现这些子查询语句都遵从这样一种形式：

select * from table1
where column1 in (select column2 from table2 where column2 = value)

其中，column1 代表 table1 中的字段，column2 和 column2 代表 table2 表中的字段。

这类查询都可以被转换为下面这种形式的连接查询：

select table1.* from table1 inner join table2 on table1.column1 = table2.column2 where table2.column2 = value;

在某些场合，子查询和关联查询可能会返回不同的结果。比如，当 table2 包含 column2 的多个实例时，就会发生这种情况。这种形式的子查询只会为每个 column2 值生成一个实例，而连接操作会为所有值生成实例，并且其输出会包含重复行。如果想要防止这种重复记录出现，就要在编写连接查询语句时使用 SELECT DISTINCT，而不能使用 SELECT。

• 改写用来查询非匹配（缺失）值的子查询

另一种常见的子查询语句类型是：把存在于某个表里，但在另一个表里并不存在的那些值查找出来。“哪些值不存在”有关的问题通常都可以用 LEFT JOIN 来解决。

如下语句用来测试哪些学生没有出现在 absence 表里（用于查找全勤学生）：

select * from student where student_id not in (select student_is from absence);

以上查询语句可以使用 LEFT JOIN 来改写：

select student.* from student left join absence on student.student_id = absence.student_id where absence.student_id is null;

通常情况下，如果子查询语句符合如下所示的形式：

select * from table1 where column1 not in (select column2 from table2);

那么可以把它改写为下面这样的连接查询：

select table1.* from table1 left join table2 on table1.column1 = table2.column2 where table2.column2 is null;

这里需要假设 table2.column2 被定义成了 NOT NULL 的。

与 LEFT JOIN 相比，子查询更加直观。大部分人都可以毫无困难地理解“没被包含在...里面”的含义，因为它不是数据库编程技术带来的新概念。而“左连接”有所不同，很难用自然语言直观地描述出它的含义。

REGEXP：正则表达式

正则表达式主要用来查询和替换符合某个模式（规则）的文本内容。例如，从一个文件中提取电话号码，查找一篇文章中重复的单词、替换文章中的敏感语汇等，这些地方都可以使用正则表达式。正则表达式强大且灵活，常用于非常复杂的查询。

使用 REGEXP 关键字指定正则表达式的字符匹配模式.

语法格式：

属性名 REGEXP '匹配方式'

其中，“属性名”表示需要查询的字段名称；“匹配方式”表示以哪种方式来匹配查询。“匹配方式”中有很多的模式匹配字符，它们分别表示不同的意思。下表列出了 REGEXP 操作符中常用的匹配方式。

^

字符^用来匹配以特定字符或字符串开头的记录。

# 在 tb_students_info 表中，查询 name 字段以“J”开头的记录
select * from tb_students_info where name regexp '^J';

$

用来匹配以特定字符或字符串结尾的记录。

# 在 tb_students_info 表中，查询 name 字段以“y”结尾的记录
select * from tb_students_info where name regexp 'y$';

.

用来替代字符串中的任意一个字符。

# 在 tb_students_info 表中，查询 name 字段值包含“a”和“y”，且两个字母之间只有一个字母的记录
select * from tb_students_info where name regexp 'a.y';

*和+

字符*和+都可以匹配多个该符号之前的字符。不同的是，+表示至少一个字符，而*可以表示 0 个字符。

# 在 tb_students_info 表中，查询 name 字段值包含字母“T”，且“T”后面出现字母“h”的记录
select * from tb_students_info where name regexp '^Th';

# 在 tb_students_info 表中，查询 name 字段值包含字母“T”，且“T”后面至少出现“h”一次的记录
select * from tb_students_info where name '^Th+';

|

匹配指定字符串。正则表达式可以匹配字符串。当表中的记录包含这个字符串时，就可以将该记录查询出来。指定多个字符串时，需要用 | 隔开。只要匹配这些字符串中的任意一个即可.

# 在 tb_students_info 表中，查询 name 字段值包含字符串“an”的记录
select * from tb_students_info where name regexp 'an';

# 在 tb_students_info 表中，查询 name 字段值包含字符串“an”或“en”的记录
select * from tb_students_info where name regexp 'an|en';

字符串与|之间不能有空格。因为，查询过程中，数据库系统会将空格也当作一个字符，这样就查询不出想要的结果。

[]

使用方括号[ ]可以将需要查询的字符组成一个字符集合。只要记录中包含方括号中的任意字符，该记录就会被查询出来。例如，通过“[abc]”可以查询包含 a、b 和 c 等 3 个字母中任意一个的记录。

# 在 tb_students_info 表中，查询 name 字段值包含字母“i”或“o”的记录
select * from tb_students_info where name regexp '[io]';

方括号[ ]还可以指定集合的区间。例如，“[a-z]”表示从 a~z 的所有字母；“[0-9]”表示从 0~9 的所有数字；“[a-z0-9]”表示包含所有的小写字母和数字；“[a-zA-Z]”表示匹配所有字符。

# 在 tb_students_info 表中，查询 name 字段值中包含 1、2 或 3 的记录
select * from tb_students_info where name regexp '[123]';

[^字符集和]

[^字符集合]用来匹配不在指定集合中的任何字符。

# 在 tb_students_info 表中，查询 name 字段值包含字母 a~t 以外的字符的记录
select * from tb_students_info where name regexp '[^a-t]';

字符串{n,}和字符串

使用{n,}或者{n,m}来指定字符串连续出现的次数。字符串{n,}表示字符串连续出现 n 次；字符串{n,m}表示字符串连续出现至少 n 次，最多 m 次。

例如，a{2,} 表示字母 a 连续出现至少 2 次，也可以大于 2 次；a{2,4} 表示字母 a 连续出现最少 2 次，最多不能超过 4 次。

# 在 tb_students_info 表中，查询 name 字段值出现字母‘e’ 至少 2 次的记录
select * from tb_students_info where name regexp 'e{2,}';

SQL查询语句先执行SELECT？

SELECT 语句的完整语法，如下：

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>

其执行顺序如下：

FROM
<表名> # 笛卡尔积
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <JOIN,LEFT JOIN,RIGHT JOIN...>
<JOIN表> # 指定JOIN，用于添加数据到ON之后的虚表中，例如LEFT JOIN会将左表的剩余数据添加到虚表中
WHERE
<WHERE条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于HAVING子句进行判断，在书写上这类聚合函数是写在HAVING判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在GROUP BY子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

引擎在执行上述每一步时，都会在内存中形成一张虚拟表，然后对虚拟表进行后续操作，并释放没用的虚拟表的内存，以此类推。

具体介绍一下 SQL 查询语句的执行顺序（下面“VT”表示虚拟表）:

1. FROM：SELECT * FROM table_1, table_2; 与 SELECT * FROM table_1 JOIN table_2; 的结果一致，都是表示求笛卡尔积；用于直接计算两个表笛卡尔积，得到虚拟表VT1，这是所有 SELECT 语句最先执行的操作，其他操作时在这个表上进行的，也就是 FROM 操作所完成的内容
2. ON：从 VT1 表中筛选符合条件的数据，形成 VT2 表
3. JOIN：将该 JOIN 类型的数据补充到 VT2 表中，例如 LEFT JOIN 会将左表的剩余数据添加到虚表 VT2 中，形成 VT3 表；若表的数量大于 2，则会重复 1-3 步
4. WHERE：执行筛选，（不能使用聚合函数）得到 VT4 表
5. GROUP BY：对 VT4 表进行分组，得到 VT5 表；其后处理的语句，如 SELECT、HAVING，所用到的列必须包含在 GROUP BY 条件中，没有出现的需要用聚合函数
6. HAVING：筛选分组后的数据，得到 VT6 表
7. SELECT：返回列得到 VT7 表
8. DISTINCT：用于去重得到 VT8 表
9. ORDER BY：用于排序得到 VT9 表
10. LIMIT：返回需要的行数，得到 VT10

需要注意的是：

• GROUP BY 条件中，每个列必须是有效列，不能是聚合函数
• NULL 值也会作为一个分组返回
• 除了聚合函数，SELECT 子句中的列必须在 GROUP BY 条件中

根据以上内容，我们可以回答以下经常遇到的问题：

• 可以在 GROUP BY 之后使用 WHERE 吗？不行， WHERE 是在 GROUP BY 之前。
• 可以基于 GROUP BY 里的东西进行 ORDER BY 吗？可以，ORDER BY 基本上是在最后执行的，所以可以基于任何东西进行 ORDER BY。
• LIMIT 是在什么时候执行？在最后

但是在实际应用中，数据库不一定会按照 JOIN、WHERE、GROUP BY 的顺序来执行查询。因为它们会进行一系列优化，在不改变查询结果的前提下，把执行顺序打乱，从而让查询执行得更快。

比如：

select * from score sc left join student stu 
on sc.student_id = stu.id where s.name = 'Jack';

以上语句只需要找出名字叫“Jack”的学生信息，那就没必要对两张表的所有数据执行左连接。在连接之前系统先进行过滤，这样查询会快得多，而且对于这个查询来说，先执行过滤并不会改变查询结果。

MySQL如何处理无效数据值？

MySQL处理数据的基本原则是“垃圾进来，垃圾出去”，通俗一点说就是你传给 MySQL 什么样的数据，它就会存储什么样的数据。如果在存储数据时没有对它们进行验证，那么在把它们检索出来时得到的就不一定是你所期望的内容。

有几种 SQL 模式可以在遇到“非正常”值时抛出错误，如果你对其他数据库管理系统比较熟悉，会发现这种行为和其他的数据库管理系统很像。

下面介绍 MySQL 默认情况下如何处理非正常数据和启用各种 SQL 模式时会对数据处理产生哪些影响。

默认情况下，MySQL 会按照以下规则来处理越界（即超出取值范围）的值和其他非正常值：

• 对于数值列或 TIME 列，超出合法取值范围的那些值将被截断到取值范围最近的那个端点，并把结果值存储起来。
• 对于除 TIME 列以外的其他类型列，非法值会被转换成与该类型一致的“零”值。
• 对于字符串列（不包括 ENUM 或 SET），过长的字符串将被截断到该列的最大长度。
• 给 ENUM 或 SET 类型列进行赋值时，需要根据列定义里给出的合法取值列表进行。如果把不是枚举成员的值赋给 ENUM 列，那么列的值就会变成空字符串。如果把包含非集合成员的子字符串的值赋给 SET 列，那么这些字符串会被清理，剩余的成员才会被赋值给列。

如果在执行增删改查等语句时发生了上述转换，那么 MySQL 会给出警告消息。在执行完其中的某一条语句之后，可以使用 SHOW WARNINGS 语句来查看警告消息的内容。

如果需要在插入或更新数据时执行更严格的检查，那么可以启用以下两种 SQL 模式中的一种：

SET sql_mode = 'STRICT_ALL_TABLES' ;
SET sql_mode = 'STRICT_TRANS_TABLES';

对于支持事务的表，这两种模式都是一样的。如果发现某个值无效或缺失，那么会产生一个错误，并且语句会中止执行，并进行回滚，就像什么事都没发生过一样。

对于不支持事务的表，这两种模式有以下效果。

1. 对于这两种模式，如果在插入或修改第一个行时，发现某个值无效或缺失，那么结果会产生一个错误，语句会中止执行，就像什么事都未发生过一样。 这跟事务表的行为很相似。
2. 在用于插入或修改多个行的语句里，如果在第一行之后的某个行出现了错误，那么会出现某些行被修改的情况。这两种模式决定着，这条语句此时此刻是要停止执行，还是要继续执行。
   • 在 STRICT_ALL_TABLES 模式下，会抛出一个错误，并且语句会停止执行。因为受该语句影响的许多行都已被修改，所以这将会导致“部分更新”问题。
   • 在 STRICT_TRANS_TABLES 模式下，对于非事务表，MySQL 会中止语句的执行。只有这样做，才能达到事务表那样的效果。只有当第一行发生错误时，才能达到这样的效果。如果错误在后面的某个行上，那么就会出现某些行被修改的情况。由于对于非事务表，那些修改是无法撤销的，因此 MySQL 会继续执行该语句，以避免出现“部分更新”的问题。它会把所有的无效值转换为与其最接近的合法值。对于缺失的值，MySQL 会把该列设置成其数据类型的隐式默认值.

通过以下模式可以对输入的数据进行更加严格的检查：

• ERROR_ FOR_ DIVISION_ BY_ ZERO：在严格模式下，如果遇到以零为除数的情况，它会阻止数值进入数据库。如果不在严格模式下，则会产生一条警告消息，并插入 NULL。
• NO_ ZERO_ DATE：在严格模式下，它会阻止“零”日期值进入数据库。
• NO_ ZERO_ IN_ DATE：在严格模式下，它会阻止月或日部分为零的不完整日期值进入数据库。

简单来说，MySQL 的严格模式就是 MySQL 自身对数据进行的严格校验，例如格式、长度、类型等。比如一个整型字段我们写入一个字符串类型的数据，在非严格模式下 MySQL 不会报错。如果定义了 char 或 varchar 类型的字段，当写入或更新的数据超过了定义的长度也不会报错。

虽然我们会在代码中做数据校验，但一般认为非严格模式对于编程来说没有任何好处。MySQL开启严格模式从一定程序上来讲也是对我们代码的一种测试，如果我们没有开启严格模式并且在开发过程中也没有遇到错误，那么在上线或代码移植的时候将有可能出现不兼容的情况，因此在开发过程做最好开启 MySQL 的严格模式。

可通过select @@sql_mode;命令查看当前是严格模式还是非严格模式。

例如，如果想让所有的存储引擎启用严格模式，并对“被零除”错误进行检查，那么可以像下面这样设置 SQL 模式：

SET sql_mode ‘STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO' ;

如果想启用严格模式，以及所有的附加限制，那么最为简单的办法是启用 TRADITIONAL 模式：

SET sql_ mode 'TRADITIONAL' ; 

TRADITIONAL 模式的含义是" 启用严格模式，当向 MySQL 数据库插入数据时，进行数据的严格校验，保证错误数据不能插入。用于事务表时，会进行事务的回滚"。

可以选择性地在某些方面弱化严格模式。如果启用了 SQL 的 ALLOW_ INVALID_ DATES 模式，那么MySQL将不会对日期部分做全面检查。相反，它只会要求月份值在 1~12 之间，而天数处于 1~31 之间，即允许像‘2000-02-30’或‘2000-06-31’这样的无效值。

另一个制止错误的办法是在 INSERT 或 UPDATE 语句里使用 IGNORE 关键字。这样那些会因无效值而导致错误的语句，将只会导致警告的出现。这些选项能让你灵活地为你的应用选择正确的有效性检查级别。

向MySQL发送一个请求的时候，MySQL到底做了些什么？

为什么要知道MySQL的执行过程呢？是因为我们要对sql语句进行优化。这个是很要必要的。

1. 客户端发送一条查询给服务器。
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3. 服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划。
4. MySQL根据优化器生成的执行计划，调用存储引擎的API执行查询。
5. 返回结果给客户端。

对关键的动作进行分析：

• 客户端/服务端通信协议

MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一。

• 查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。

如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

1. 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存

2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。

但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。

如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

1. 用多个小表代替一个大表，注意不要过度设计

2. 批量插入代替循环单条插入

3. 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适

4. 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

最后的忠告是不要轻易打开查询缓存，特别是写密集型应用，可以说是弊大于利，全部关闭。也可以考虑利用Redis、Memcached当充当缓存。

当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，读者可以自行阅读相关资料，这里权当抛砖引玉吧。

(8条消息) 缓存——项目中缓存是如何使用的？为什么要用缓存？缓存使用不当会造成什么后果？_一棵大树-CSDN博客_清理缓存怎么操作

• 语法解析和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

• 查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

show status like 'last_query_cost';

这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

1. 重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）

2. 优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）

3. 提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）

4. 优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）

随着MySQL的不断发展，优化器使用的优化策略也在不断的进化，这里仅仅介绍几个非常常用且容易理解的优化策略，其他的优化策略，大家自行查阅吧。

• 查询执行引擎

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

• 返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如改查询影响到的行数以及执行时间等等。

如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足图中①中所描述的通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

SQL注入是什么？如何避免？

SQL 注入（SQL Injection）是发生在 Web 程序中数据库层的安全漏洞，是网站存在最多也是最简单的漏洞。主要原因是程序对用户输入数据的合法性没有判断和处理，导致攻击者可以在 Web 应用程序中事先定义好的 SQL 语句中添加额外的 SQL 语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步获取到数据信息。

简而言之，SQL 注入就是在用户输入的字符串中加入 SQL 语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行，攻击者就可以执行计划外的命令或访问未被授权的数据。

SQL 注入已经成为互联网世界 Web 应用程序的最大风险，我们有必要从开发、测试、上线等各个环节对其进行防范。下面介绍 SQL 注入的原理及避免 SQL 注入的一些方法。

怎样才会发生SQL的注入

1. 恶意拼接查询

SQL 语句可以查询、插入、更新和删除数据，且使用分号来分隔不同的命令。

select * from users where user_id = ${user_id}

其中，user_id 是传入的参数，如果传入的参数值为“1234; DELETE FROM users”，那么最终的查询语句会变为：

select * from users where user_id= 1234;
delete from users ;

如果以上语句执行，则会删除 users 表中的所有数据。

2. 利用注释执行非法命令。

SQL 语句中可以插入注释。

select count(*) as 'num' from game_score where game_id=24411 and version=$version;

如果 version 包含了恶意的字符串'-1' OR 3 AND SLEEP(500)--，那么最终查询语句会变为：

select count(*) as 'num' from game_score where game_id=24411 and version='-1' or 3 and SLEEP(500)--;

以上恶意查询只是想耗尽系统资源，sleep(500) 将导致 SQL 语句一直运行。如果其中添加了修改、删除数据的恶意指令，那么将会造成更大的破坏。

3. 传入非法参数

SQL 语句中传入的字符串参数是用单引号引起来的，如果字符串本身包含单引号而没有被处理，那么可能会篡改原本 SQL 语句的作用。

select * from user_name where user_name = $user_name;

如果 user_name 传入参数值为 G'chen，那么最终的查询语句会变为：

select * from user_name where user_name ='G'chen';

一般情况下，以上语句会执行出错，这样的语句风险比较小。虽然没有语法错误，但可能会恶意产生 SQL 语句，并且以一种你不期望的方式运行

4. 添加额外条件

在 SQL 语句中添加一些额外条件，以此来改变执行行为。条件一般为真值表达式。

update users set userpass='$userpass' where user_id=$user_id;

如果 user_id 被传入恶意的字符串“1234 OR TRUE”，那么最终的 SQL 语句会变为：

update users set userpass= '123456' where user_id=1234 or true;

这将更改所有用户的密码。

避免SQL注入

对于 SQL 注入，我们可以采取适当的预防措施来保护数据安全。

1. 过滤输入内容，校验字符串

过滤输入内容就是在数据提交到数据库之前，就把用户输入中的不合法字符剔除掉。可以使用编程语言提供的处理函数或自己的处理函数来进行过滤，还可以使用正则表达式匹配安全的字符串。

如果值属于特定的类型或有具体的格式，那么在拼接 SQL 语句之前就要进行校验，验证其有效性。比如对于某个传入的值，如果可以确定是整型，则要判断它是否为整型，在浏览器端（客户端）和服务器端都需要进行验证。

2. 参数化查询

参数化查询目前被视作是预防 SQL 注入攻击最有效的方法。参数化查询是指在设计与数据库连接并访问数据时，在需要填入数值或数据的地方，使用参数（Parameter）来给值。

MySQL 的参数格式是以“?”字符加上参数名称而成，如下所示：

UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4

在使用参数化查询的情况下，数据库服务器不会将参数的内容视为 SQL 语句的一部分来进行处理，而是在数据库完成 SQL 语句的编译之后，才套用参数运行。因此就算参数中含有破坏性的指令，也不会被数据库所运行。

3. 安全测试、安全审计

除了开发规范，还需要合适的工具来确保代码的安全。我们应该在开发过程中应对代码进行审查，在测试环节使用工具进行扫描，上线后定期扫描安全漏洞。通过多个环节的检查，一般是可以避免 SQL 注入的。

有些人认为存储过程可以避免 SQL 注入，存储过程在传统行业里用得比较多，对于权限的控制是有一定用处的，但如果存储过程用到了动态查询，拼接 SQL，一样会存在安全隐患。

下面是在开发过程中可以避免 SQL 注入的一些方法。

• 避免使用动态SQL

避免将用户的输入数据直接放入 SQL 语句中，最好使用准备好的语句和参数化查询，这样更安全。

• 不要将敏感数据保留在纯文本中

加密存储在数据库中的私有/机密数据，这样可以提供了另一级保护，以防攻击者成功地排出敏感数据。

• 限制数据库权限和特权

将数据库用户的功能设置为最低要求；这将限制攻击者在设法获取访问权限时可以执行的操作。

• 避免直接向用户显示数据库错误

攻击者可以使用这些错误消息来获取有关数据库的信息。

一些编程框架对于写出更安全的代码也有一定的帮助，因为它提供了一些处理字符串的函数和使用查询参数的方法。但同样，你仍然可以编写出不安全的 SQL 语句。所以归根到底，我们需要有良好的编码规范，并能充分利用参数化查询、字符串处理和参数校验等多种办法来保护数据库和程序的安全。

视图

基本概念

视图在数据库中的作用类似于窗户，用户可以通过这个窗口看到只对自己有用的数据。既保障了数据的安全性，又大大提高了查询效率。

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全。

下面的数据库中有一张公司部门表 department。表中包括部门号（d_id）、部门名称（d_name）、功能（function）和办公地址（address）。

还有一张员工表 worker。表中包含了员工的工作号（num）、部门号（d_id）、姓名（name）、性别（sex）、出生日期（birthday）和家庭住址（homeaddress）。

由于各部门领导的权力范围不同，因此，各部门的领导只能看到该部门的员工信息；而且，领导可能不关心员工的生日和家庭住址。为了达到这个目的，可以为各部门的领导建立一个视图，通过该视图，领导只能看到本部门员工的指定信息。

为生产部门建立一个名为 product _view 的视图。通过视图 product_ view，生产部门的领导只能看到生产部门员工的工作号、姓名和性别等信息。这些 department 表的信息和 worker 表的信息依然存在于各自的表中，而视图 product_view 中不保存任何数据信息。当 department 表和 worker 表的信息发生改变时，视图 product_view 显示的信息也会发生相应的变化。

有个技巧：如果经常需要从多个表查询指定字段的数据，可以在这些表上建立一个视图，通过这个视图显示这些字段的数据。

MySQL 的视图不支持输入参数的功能，因此交互性上还有欠缺。但对于变化不是很大的操作，使用视图可以很大程度上简化用户的操作。

视图并不同于数据表，它们的区别在于以下几点：

• 视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。
• 存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。
• 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
• 视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。
• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。
• 视图的建立和删除只影响视图本身，不影响对应的基本表。

视图的优点

视图与表在本质上虽然不相同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。

1. 定制用户数据，聚焦特定的数据

在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。

例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。

2. 简化数据操作

在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

3. 提高数据的安全性

视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

4. 共享所需数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

5. 更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。

6. 重用 SQL 语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

要注意区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

使用视图的时候，还应该注意以下几点：

• 创建视图需要足够的访问权限。
• 创建视图的数目没有限制。
• 视图可以嵌套，即从其他视图中检索数据的查询来创建视图。
• 视图不能索引，也不能有关联的触发器、默认值或规则。
• 视图可以和表一起使用。
• 视图不包含数据，所以每次使用视图时，都必须执行查询中所需的任何一个检索操作。如果用多个连接和过滤条件创建了复杂的视图或嵌套了视图，可能会发现系统运行性能下降得十分严重。因此，在部署大量视图应用时，应该进行系统测试。

ORDER BY 子句可以用在视图中，但若该视图检索数据的 SELECT 语句中也含有 ORDER BY 子句，则该视图中的 ORDER BY 子句将被覆盖。

创建视图

创建视图是指在已经存在的 MySQL 数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。

create view <视图名> as <select语句>

• <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
• <select语句>：指定创建视图的 select 语句，可用于查询多个基础表或源视图。

对于创建视图中的 SELECT 语句的指定存在以下限制：

• 用户除了拥有 create view 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
• select 语句不能引用系统或用户变量。
• select 语句不能包含 from 子句中的子查询。
• select 语句不能引用预处理语句参数。

视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。可使用 check table 语句检查视图定义是否存在这类问题。

视图定义中允许使用 oreder by 语句，但是若从特定视图进行选择，而该视图使用了自己的 oreder by 语句，则视图定义中的 order by 将被忽略。

视图定义中不能引用 TEMPORARY 表（临时表），不能创建 TEMPORARY 视图。

with check option 的意思是，修改视图时，检查插入的数据是否符合 where 设置的条件。

• 创建基于单表的视图

# 在 tb_students_info 表上创建一个名为 view_students_info 的视图
create view view_students_info as select * from tb_students_info;
# 使用视图
select * from view_students_info;

# 在 tb_students_info 表上创建一个名为 v_students_info 的视图
create view v_students_info (s_id,s_name,d_id,s_age,s_sex,s_height,s_date) 
as 
select id,name,dept_id,age,sex,height,login_date from tb_students_info;

# 使用视图
select * from v_students_info;

可以看到，view_students_info 和 v_students_info 两个视图中的字段名称不同，但是数据却相同。因此，在使用视图时，可能用户不需要了解基本表的结构，更接触不到实际表中的数据，从而保证了数据库的安全。

• 创建基于多表的视图

# 在表 tb_student_info 和表 tb_departments 上创建视图 v_students_info
create view v_students_info (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
as 
select id,name,dept_id,age,sex,height,login_date from tb_students_info;
# 使用视图
select * from v_students_info;

• 查询视图

视图一经定义之后，就可以如同查询数据表一样，使用 select 语句查询视图中的数据，语法和查询基础表的数据一样。

视图用于查询主要应用在以下几个方面：

• 使用视图重新格式化检索出的数据。
• 使用视图简化复杂的表连接。
• 使用视图过滤数据。

describe 视图名；

describe 一般情况下可以简写成 desc，输入这个命令的执行结果和输入 describe 是一样的。

• 查看视图的详细信息

show create view 视图名;

还可以查看创建视图的语句。创建视图的语句可以作为修改或者重新创建视图的参考，方便用户操作。

修改视图

修改视图是指修改 MySQL 数据库中存在的视图，当基本表的某些字段发生变化时，可以通过修改视图来保持与基本表的一致性。

alter view <视图名> as <select语句>

• <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
• <select 语句>：指定创建视图的 select 语句，可用于查询多个基础表或源视图。

需要注意的是，对于 alter view 语句的使用，需要用户具有针对视图的 create view和 drop 权限，以及由 select 语句选择的每一列上的某些权限。

修改视图的定义，除了可以通过 alter view 外，也可以使用 drop view语句先删除视图，再使用 create view 语句来实现。

删除视图

删除视图是指删除 MySQL 数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。

drop view <视图名1> [ , <视图名2> …]