MySQL -- 常用汇总

1、MySQL回顾：
0、查询：
SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

1、DISTINCT 去重：
1、DISTINCT 关键字只能在 SELECT 语句中使用
2、在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最前面
3、如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下才会被去重
4、在实际情况中，我们经常使用 DISTINCT 关键字来返回不重复字段的条数
5、 mysql> SELECT COUNT(DISTINCT name,age) FROM student;
+--------------------------+
| COUNT(DISTINCT name,age) |
+--------------------------+
| 5 |
+--------------------------+

2、别名：
1、<表名> [AS] <别名> AS关键字可以省略，省略后需要将表名和别名用空格隔开
2、<字段名> [AS] <别名> AS关键字可以省略，省略后需要将字段名和别名用空格隔开
3、注意：
1、表的别名不能与该数据库的其它表同名
2、字段的别名不能与该表的其它字段同名
3、在条件表达式中不能使用字段的别名，否则会出现“ERROR 1054 (42S22): Unknown column”这样的错误提示信息
4、表别名只在执行查询时使用，并不在返回结果中显示
5、字段定义别名之后，会返回给客户端显示，显示的字段为字段的别名

3、limit：
1、【指定初始位置】LIMIT 初始位置，记录数：“初始位置”表示从哪条记录开始显示；“记录数”表示显示记录的条数
2、【不指定初始位置】LIMIT 记录数：
1、“记录数”表示显示记录的条数。如果“记录数”的值小于查询结果的总数，则会从第一条记录开始，显示指定条数的记录。如果“记录数”的值大于查询结果的总数，则会直接显示查询出来的所有记录

4、排序order：
1、【语法】：ORDER BY <字段名> [ASC|DESC]
ASC：升序；DESC：降序
2、注意：
1、ORDER BY 关键字后可以跟子查询
2、当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待
3、ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序

3、多字段排序：
1、在对多个字段进行排序时，排序的第一个字段必须有相同的值，才会对第二个字段进行排序。如果第一个字段数据中所有的值都是唯一的，MySQL 将不再对第二个字段进行排序

2、多字段、降序、升序同时出现：
1、SELECT name,height FROM tb_student_info ORDER BY height DESC,name ASC;
2、解释：

1、DESC 关键字只对前面的列进行降序排列，在这里只对 height 字段进行降序。因此，height 按降序排序
2、而 name 仍按升序排序
3、如果想在多个列上进行降序排序，必须对每个列指定 DESC 关键字

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

6、like模糊查询：
1、[NOT] LIKE '字符串'：
1、NOT ：可选参数，字段中的内容与指定的字符串不匹配时满足条件。
2、字符串：指定用来匹配的字符串。“字符串”可以是一个很完整的字符串，也可以包含通配符。

2、LIKE 关键字支持百分号“%”和下划线“_”通配符

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

3、区分大小写BINARY:
1、【不区分】SELECT name FROM tb_students_info WHERE name LIKE 't%';
2、【区分】SELECT name FROM tb_students_info WHERE name LIKE BINARY 't%';

4、注意：
1、匹配的字符串必须加单引号或双引号
2、注意大小写。MySQL 默认是不区分大小写的。如果区分大小写，像“Tom”这样的数据就不能被“t%”所匹配到。
3、注意尾部空格，尾部空格会干扰通配符的匹配。例如，“T% ”就不能匹配到“Tom”。
4、注意 NULL。“%”通配符可以到匹配任意字符，但是不能匹配 NULL。也就是说 “%”匹配不到 tb_students_info 数据表中值为 NULL 的记录。

5、使用通配符要记住的技巧：
1、不要过度使用通配符，如果其它操作符能达到相同的目的，应该使用其它操作符。因为 MySQL 对通配符的处理一般会比其他操作符花费更长的时间。
2、在确定使用通配符后，除非绝对有必要，否则不要把它们用在字符串的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
3、仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

6、如果查询内容中包含通配符，可以使用“\”转义符
1、例如，在 tb_students_info 表中，将学生姓名“Dany”修改为“Dany%”后，查询以“%”结尾的学生姓名，SQL 语句和运行结果如下：
2、mysql> SELECT NAME FROM test.`tb_students_info` WHERE NAME LIKE '%\%';

7、范围查询 BETWEEN AND:
1、[NOT] BETWEEN 取值1 AND 取值2
1、NOT：可选参数，表示指定范围之外的值。如果字段值不满足指定范围内的值，则这些记录被返回。
2、取值1：表示范围的起始值。
3、取值2：表示范围的终止值。

2、在 MySQL 中，BETWEEN AND 能匹配指定范围内的所有值，包括起始值和终止值

8、IS NULL空值查询：
1、MySQL 提供了 IS NULL 关键字，用来判断字段的值是否为空值（NULL）。空值不同于 0，也不同于空字符串
2、IS [NOT] NULL
3、注意：
1、IS NULL 是一个整体，不能将 IS 换成“=”。如果将 IS 换成“=”将不能查询出任何结果，数据库系统会出现“Empty set(0.00 sec)”这样的提示。
2、同理，IS NOT NULL 中的 IS NOT 不能换成“!=”或“<>”

9、分组GROUP BY: http://c.biancheng.net/view/7408.html
1、GROUP BY <字段名> ：“字段名”表示需要分组的字段名称，多个字段时用逗号隔开

2、单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录

3、GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来

10、过滤分组：having

1、语法：having <查询条件>

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

3、但是 WHERE 和 HAVING 关键字也存在以下几点差异：
1、一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
2、WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
3、WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
4、WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
5、WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

4、错误实例：
1、mysql> SELECT name,sex FROM tb_students_info HAVING height>150;
ERROR 1054 (42S22): Unknown column 'height' in 'having clause'

select 关键字后没有查询出 HAVING 查询条件中，使用的height字段，则产生错误信息：having 子句中的列’height‘未知

2、mysql> SELECT GROUP_CONCAT(name),sex,height FROM tb_students_info WHERE AVG(height)>170 GROUP BY height;
ERROR 1111 (HY000): Invalid use of group function

如果在where查询条件中使用聚合函数，MySQL会提示错误信息：无效使用组函数

11、多表查询 之 交叉连接（cross join）：

1、作用：一般用来返回连接表的笛卡尔积（交叉连接可以查询两个或两个以上的表）

2、语法：select <字段名> from <table1> cross join <table2> [where子句]（官方建议标准写法）
or
select <字段名> from <table1>, <table2> [where子句]

3、语法说明：
1、字段名：需要查询的字段名称。
2、<表1><表2>：需要交叉连接的表名。
3、WHERE 子句：用来设置交叉连接的查询条件。

4、注意：
1、通过交叉连接的方式进行多表查询的这种方法并不常用，我们应该尽量避免这种查询

2、如果在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。因此，表的数量较多时，交叉连接会非常非常慢。一般情况下不建议使用交叉连接

3、在 MySQL 中，多表查询一般使用内连接和外连接，它们的效率要高于交叉连接

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

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

3、语法：
SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
语法说明如下。
字段名：需要查询的字段名称。
<表1><表2>：需要内连接的表名。
INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
ON 子句：用来设置内连接的连接条件

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

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

3、当对多个表进行查询时，要在 SELECT 语句后面指定字段是来源于哪一张表。因此，在多表查询时，SELECT 语句后面的写法是表名.列名。另外，如果表名非常长的话，也可以给表设置别名，这样就可以直接在 SELECT 语句后面写上表的别名.列名。

5、实例：
1、mysql> SELECT s.name,c.course_name FROM tb_students_info s INNER JOIN tb_course c ON s.course_id = c.id;


13、多表查询 之 外连接（outer join）：
1、内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录

2、外连接可以分为左外连接和右外连接

3、左连接：
1、左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件
2、语法：
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
语法说明如下。
字段名：需要查询的字段名称。
<表1><表2>：需要左连接的表名。
LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
ON 子句：用来设置左连接的连接条件，不能省略

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

3、实例：SELECT s.name,c.course_name FROM tb_students_info s LEFT OUTER JOIN tb_course c ON s.`course_id`=c.`id`;

4、右连接：
1、SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>
2、与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）
3、实例：SELECT s.name,c.course_name FROM tb_students_info s RIGHT OUTER JOIN tb_course c ON s.`course_id`=c.`id`;

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

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

14、子查询：
1、定义：子查询指将一个查询语句嵌套在另一个查询语句中

2、子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。

3、语法：子查询在 WHERE 中的语法格式如下：
WHERE <表达式> <操作符> (子查询)

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

4、in / not in: 当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反
5、exists / not exists: 用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反

6、实例：
1、使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名

eg：SELECT name FROM tb_students_info WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');

2、使用=运算符，在 tb_course 表和 tb_students_info 表中查询出所有学习 Python 课程的学生姓名
eg：SELECT name FROM tb_students_info WHERE course_id = (SELECT id FROM tb_course WHERE course_name = 'Python');

3、查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中的记录

eg：select * from students_info where exists(select course_name from tb_course where id=1);

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

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

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

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

15、子查询【注意事项】：

1、