MySQL数据库——查询语句SELECT的学习总结，2W字超详细总结，值得收藏

一、基本的SELECT语句

这篇文章是基于数据库图形工具Navicat 上讲解的，不是使用命令行，是我学习的一个总结。

1、SQL分类

SQL语言在功能上主要分为3大类：

• DDL（Data Definiton Languages、数据定义语言），这些语言定义了不同的数据库、表、视图、索引等数据对象，还可以用来创建、删除、修改数据库和数据表的结构。（主要的语句关键字包括CREATE, DROP, SELECT等）
• DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。（简称增删改查）
  ——主要的语句关键字包括INSERT, DELETE, UPDATE, SELECT等。
  ——SELECT是SQL语言的基础，最为重要。
• DCL（Data Control Language 、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
  ——主要的语句关键字包括GRANT, REVOKE, COMMIT, ROLLBACK,SAVEOPINT等。

因为查询语句使用非常的频繁，所以很多人把查询语句单独定义为一个类：DQL(数据查询语句)。

2、SQL的语言规范

2.1、基本规则（必须遵守）

• SQL可以写在一行或者多行，为了提高可读性，每个子句分行写，必要是使用缩进
• 每条命令以; 或\g或\G结束（在图形化工具如navicat上无法使用\g和\G的话，在命令行就可以使用）
• 关键字不能被缩写也不能分行
• 标点符号必须区分中英文，并且保证所有的(),' ', " "括号，单双引号都是成对结束的，字符串和日期时间类型的数据可以使用单引号（’ '）表示，列的别名，尽量使用双引号表示，而且不建议省略as。

这个图形化工具可以多条语句一起执行，也可以选中其中一条执行。

2.2、SQL大小写规范（建议遵守）

• MySQL在Windows环境下是大小写不敏感的
• MySQL在Linux环境下是大小写敏感的
  ——数据库名、表名、表的别名、变量名是严格区分大小写的
  ——关键字、函数名、列名（或字段名）、列的别名是忽略大小写的。
• 推荐采用统一的书写规范： 【数据库名、表名、表别名、字段名、字段别名等都是小写】【SQL关键字、函数名、绑定变量等都是大写】

2.3、注释

主要有三种：
1：#
2：-- （这个和上面都是单行注释，但有一点不同，上面那个#后面直接跟着注释或者空格跟着注释也可以，但–这个需要有个空格）
3：/* (多行注释，中间写注释内容)*/

2.4、数据导入指令

导入现有的数据表、表的数据：

• 方式一：通过命令的方式是用命令来导入：source 文件的路径名，举例：source D:mysql.sql

• 方式二：基于具体的图形化界面的工具（navicat）导入数据：

选入文件就行了。

3、基本的SELECT语句

3.1、SELECT…

3.2、SELECT…FROM

FROM表示从哪个表拿出数据。
第二条语句的那个只显示id的那一列。

3.3、列的别名

• 重命名一个列
• 便于计算
• 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以在便将别名中的包含特殊字符和空格并区分大小写，防止别名为系统关键字。
• AS可以省略不写

例如：

SELECT id as(可以省略)"编号", name "名称", class "班级" FROM employes;
//那几个汉子就是列的别名
//但是这个列的别名不可以在WHERE语句中使用

经过上面的指令运行后，就变成了下面这个：

3.4、去除重复行

去除重复的数据行：

SELECT DISTINCT id FROM employes;
//DISTINCT 必须加在id这个列名的前面，可以同时去除多个列

我们可以看到id这个列有两个重复的111111和Null，所以调用上面这个指令就会去除掉一个重复的：

这样就只剩一个111111和Null了。

3.5、空值参与运算

• 所有的列值和运算符遇到NULL进行运算时，运算结果都为NULL。

SELECT '列名1', '列名2'与NULL做运算 FROM 表名
# 计算出的结果显示出来列表2的值都为NULL

要注意的是，在MySQL里面，空值（NULL）不等于空字符串，也不等于0，一个空字符串的长度为0，而一个空值的长度为空，而且，在MySQL里面，空值是占用空间的。

3.6、着重号

• 着重号的作用是为了将列名或者表名与一些同名的关键字进行区分，防止混淆。

`列名 ` - 这就是着重号的表示形式，用单引号（不是普通的单引号，是键盘上面esc下面这个有波浪线的这个）将表名或者列名引用起来。

3.7、查询常数

• MySQL还可以查询次常数，简单来说，就是不是从表里面取出内容，而是自己定义的一列常数，我们在查询一项内容时，需要添加一项内容，就可以加上这个指令。
• 例如：要在前面添加一个年级，只要加上常数后面加上列名：

4、显示表结构

显示出表的一些结构和特征：

DESCRIBE 表名
DESC 表名
这两种都可以

- 表的别名

//表的别名:在FROM语句后表的名字加上别名
SELECT id , name , class 
FROM employes t1;
//t1就是表的别名,与列的别名不同的是，表的别名可以在WHERE字句中使用

5、过滤数据

• 俗话说就是找出自己想要，过滤掉不要的，筛选出自己需要的，就像if语句一样找出满足条件的
• 语法：

SELECT 字段1，字段2 
FROM 表名
WHERE 过滤条件

• 使用WHERE语句将不满足的条件过滤掉
• 过滤语句必须紧跟在FROM 语句后面，因为FROM和WHERE是最先编译的，在SELECT前面编译

原表：

筛选过的表：

注：代码语句执行的顺序是先从FROM子句执行的，然后就是WHERE过滤语句，和一些条件语句，然后执行SELECT子句，这就是列的别名为什么不能在WHERE子句中使用，而表的别名却可以的原因了。

二、运算符

1、算术运算符

• 算术运算符主要用于数学运算，其可以连接运算符前后两个值或者表达式，和各个语言的运算符很相似。

运算符	名称	作用	实例
+	加法运算符	计算两个值或者两个表达式和	SELECT A+B
-	减法运算符	计算两个值或者两个表达式的差	SELECT A-B
*	乘法运算符	计算两个值或者两个表达式的积	SELECT A*B
/ 或 DIV	除法运算符	计算两个值或者两个表达式的商	SELECT A/B 或 SELECT ADIVB
% 或 MOD	取模运算符	计算两个值或者两个表达式的余数	SELECT A+B或 SELECT AMODB

一些注意事项：

• 类型相同的数或者表达式计算出来的值类型也是一样的，除了除法运算符，在计算机里一个数被做除法，计算机会认为是除不尽的，所以除法得出的都是浮点数。
• MySQL的运算符优先级和普通语言是一样的。
• 一个数或表达式和数字字符做运算时，sql语言会将数字字符进行隐式转换，转换为对应的数字。
• 一个数或表达式和普通字符做运算时，sql语言会将字符看做为0。
• 有NULL参与的计算，计算结果都为NULL。

SELECT 100+'2' FROM DUAL; 
//# 计算得出的结果为102，将字符‘2’隐式转换为数字2
SELECT 100+'a' FROM DUAL;
//# 计算得出100，sql语言会将‘a’看做0
SELECT 100+NULL FROM DUAL;
//输出NULL，NULL参与的计算结果都为NULL

将class的数据都乘以2：

2、比较运算符

• 和其他语言的比较运算符一样，就是比较判断然后返回true或者false，主要配合WHERE过滤语句来使用。

运算符	名称	作用	实例
=	等于运算符	和其他编程语言不一样，不是用来赋值的，而是用来判断是否相等的	SELECT 字段 FROM 表名 WHERE A=B(筛选出A和B相等的数据)
<=>	安全等于	安全的判断两个值，字符串或表达式是否相等，可以用来判断有NULL的表达式	SELECT 字段 FROM 表名 WHERE A<=>B(筛选出A和B相等的数据)
<>或者!=	不等于	两种方式，和其他语言用法一样	SELECT 字段 FROM 表名 WHERE A<>B(A!=B)(筛选出A和B不相等的数据)
<	小于	和其他语言用法一样	SELECT 字段 FROM 表名 WHERE A<B(筛选出A小于B的数据)
<=	小于等于	和其他语言用法一样	SELECT 字段 FROM 表名 WHERE A<=B(筛选出A小于等于的数据)
>	大于	和其他语言用法一样	SELECT 字段 FROM 表名 WHERE A>B(筛选出A大于B的数据)
>=	大于等于	和其他语言用法一样	SELECT 字段 FROM 表名 WHERE A>=B(筛选出A大于等于B的数据)

2.1、= 运算符

• 和其他语言不同，不是用来赋值的而是用来判断是否相等的，相等就返回1，不相等就返回0

运算规则：

• 如果两边都是字符串，则会按照字符串中的每个字符的ANSI编码来判断是否相等。
• 如果一边是整数，一边是字符串，就会将字符串转换为数字进行比较
• 如果运算符两边中有一边是NULL，则比较结果就为NULL

第三种情况：

上面我们可以知道，class里面是有为NULL的数据的，如果我们有=运算符来判断，比较结果就为NULL，就不会通过WHERE，所以就不会有数据，如果我们要显示的话就要使用安全等于<=>。

2.2、 <=> 运算符

和=运算符基本一样，区别就是可以用来含有NULL的比较：

原表：

使用<=>过滤后的表：

使用普通等于就不能出数据，使用安全等于就有数据。

其他运算符都差不多就不详细解释了。

2.3、非字符的运算符

运算符	名称	作用	实例
IS NULL(ISNULL)	为空运算符	判断值、表达式或字符串是否为空	SELECT 字段 FROM 表名 WHERE A IS NULL(判断A里面是NULL的数据)
IS NOT NULL	不为空运算符	判断值、表达式或字符串是否不为空	SELECT 字段 FROM 表名 WHERE A IS NOT NULL(判断A里面不是NULL的数据)
LEAST	最小运算符	返回多个值中最小值	SELECT 字段 FROM 表名 WHERE C LEAST(A,B)
GREATEST	最大值运算符	返回多个值中最大值	SELECT 字段 FROM 表名 WHERE C GREAEST(A,B)
BETWEEN AND	两值之间的运算符	两个值范围之间的值	SELECT 字段 FROM 表名 WHERE C BETWEEN A AND B(A和B之间的数据)
IN	属于运算符	判断一个值是否为列表中的任意一个值	SELECT 字段 FROM 表名 WHERE A IN(B) (判断B是否是A中的值，是的化就返回数据)
NOT IN	不属于运算符	判断一个值是否不是列表中的任意一个值	和上面相反
LIKE	模糊匹配运算符	判断一个值是否符合模糊匹配规则	SELECT 字段 FROM 表名 WHERE A LIKE B(在A中匹配与B的数据)
REGEXP	正则表达式运算符	判断一个值是否符合正则表达式的规则	SELECT 字段 FROM 表名 WHERE A EGEXP B

例如：

其他的和这个差不多。

LIKE:

LIKE ‘%a%’ 查询数据中有a的那个数据
LIKE ‘a%’ 查询数据中有以a开头的那个数据
LIKE ‘%a’ 查询数据中有以a结尾的那个数据
LIKE ‘_a%’ 查询数据中第二个是a的那个数据

3、逻辑运算符

• 逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符返回的结果为1、0或者NULL，这个和其他语言的差不多，MySQL支持四种运算符：

运算符	作用	示例
NOT 或 ！	逻辑非	SELEXT NOT A 或 SELEXT ! A
AND 或 &&	逻辑与	SELEXT A && B 或SELEXT A AND B
OR 或 ||	逻辑或	SELEXT A || B 或SELEXT A OR B
XOR	逻辑异或	SELEXT A XOR B

这些运算符和其他你知道的语言都用法一样，可以对值或表达式进行。
例：

4、位运算符

• 位运算符是在二进制上进行的运算的运算符。位运算符会将操作数装换成二进制数，然后进行位运算，最后再将二进制变为十进制。

MySQL支持的位运算符有：

运算符	作用	示例
&	按位与（位AND）	SELECT A & B
|	按位或（位OR）	SELECT A | B
^	按位异或（位XOR）	SELECT A ^ B
~	按位取反	SELECT ~A
>>	按位右移	SELECT A>>2
<<	按位左移	SELECT A<<2

5、运算符的优先级

这里的运算符级别和其他编程语言差不多，数字越大优先级越高，可以用括号来控制优先级。

优先级	运算符
1	=(赋值)
2	||，OR,XOR
3	&&, AND
4	NOT
5	BETWEEN,CASE,WHEN,ELSE
6	=(比较运算符),<=> , >= , >, <=, <>, !=, IS, LIKE, IN
7	|
8	&
9	<< , >>
10	-, +
11	*, /, DIV, % , MOD
12	^
13	-(负号)， ~(按位取反)
14	!
15	()

三、排序与分页

1、排序

• 使用ORDER BY 字句来进行排序：
• ASC: 升序
• DESC: 降序
• ORDER BY子句放在SELECT语句结尾
• 如果在ORDER BY语句后面不加上额外升序或者降序条件的话，会默认是降序排序。

1.1、单列排序

• 只有一个进行排列的列作为条件

SELECT 字段名 FROM 表名 ORDER BY 字段名(列名);
// # 如果没有升序或者降序条件，默认是升序
SELECT 字段名 FROM 表名 ORDER BY 字段名(列名) DESC;// # 降序

例：

1.2、多列排序

• 有两个或者多个以上的列作为条件

SELECT 字段名 FROM 表名 ORDER BY 字段名1，字段名2... DESC;

例：

先利用class这个字段来进行第一次排序，然后在class排好的基础上在进行id的排序，相同的class的id是按照降序排列的。

2、分页

• 如果我们处理的数据是非常大的，我们要阅读的话就非常麻烦，这个分页有点类似于网页的分页，我们要查询的网页太多的话就可以进行分页，方便我们查找，所以这里的分页也是为了方便查找。

# 使用：
SELECT * FROM 表名 LIMIT 起始偏移量位置,需要显示的数据条数;

关键字就是 LIMIT：
LIMIT 起始偏移量， 需要显示的数据条数；

例：

• 这里先进行排序，然后从0开始，显示5条数据，如果要接着显示下5条数据可以就可以把0加上5。

==新特性：LIMIT … OFFSET… ==

SELECT * FROM 表名 LIMIT 要偏移显示的数据 OFFSET 开始偏移的位置
SELECT * FROM 表名 LIMIT 5 OFFSET 0；# 和上面图里面的一样，也是从0开始偏移，偏移出5个数据。

四、多表查询

多表查询，也称关联查询，指两个表或多个表一起查询。
前提条件：这些表是有关联的（一对一，或者多对一），比如两个表都有class或者其中一个字段，这样才能建立起联系，这样也才能进行多表查询。

假如我们有三个表：
表1：employes，表2：numbler， 表3：city

// # 假如我们想要查询名字叫做ccc的同学在哪个城市,
// 如果按照普通的单表查询的话就需要这样:

SELECT * FROM employes WHERE `name`='ccc';
// # 先用第一个表查询名字对应的班级
SELECT * FROM numbler WHERE class_id=3;
// # 再用第二个表查询班级所对应的学校
SELECT * FROM city WHERE 学校='学校3';
// # 最后用最后一个表查询学校的城市

根据上面这个方法我们就可以知道，这样的查询是非常麻烦的，所以就有了多表查询来帮助我们更简单的查询。在那之前先说一个大家可能会认知的错误：笛卡尔积错误。

1、笛卡尔积（交叉连接）

1.1、案例说明

在我们还没有接触过多表查询的正确方法时，我们进行多表查询是可能会这样做：

SELECT `name`, `学校名称` 
FROM numbler, employes;
// # 通过两个表来查询名字和学校

如果你想上面那样做，想法肯定是没有问题的，但是不完整，这样就会导致下面这样的结果：

这个结果并不是我们需要的结果，正确的查询结果是只有8条，而这里却查出了40条，其余的条数没有显示，所以这犯了笛卡尔积错误。

1.2、笛卡尔积（交叉连接）的理解

• 笛卡尔乘积是一个数学运算，并不是一个错误，上面提到的笛卡尔积错误只是用来说明这样的写法运用到了笛卡尔乘积，而这样的运算不是我们想要的，所以就称之为笛卡尔积错误，但其本身并不是一个错误。
• 假如我们有两个集合x和y，那么经过笛卡尔乘积最后所产生的集合就会是x和y的乘积个元素（xy），一个x的元素会与所有的y相对应，所以就会有xy个。
  

1.3、问题的解决

• 笛卡尔积的错误会在下面条件下产生：
  。省略多个表的连接条件（或关联条件）
  。连接条件无效
  。所有表中的所有行相互连接
• 为了避免笛卡尔积，可以在在WHERE加入有效的连接条件。
• 加入条件后的查询语法:
  注：在使用多表查询时，显示数据的字段最好加上子段的来源，是属于哪个表的，这样有助于防止字段名冲突。其中我们可以使用表的别名来提高阅读性。

SELECT T1.column, T2.column
FROM table1 T1, table2 T2
WHERE T1.column1 = T2.column2; # 连接条件
// # T1,T2都是表的别名
//# 因为FROM最先执行，所以都可以使用表的别名

• 在WHERE子句中写入连接条件

所以上面案例的正确做法：

SELECT e.`name`, n.`学校名称` 
FROM employes e, numbler n
WHERE e.class = n.class_id;
//#连接条件

注: 如果SELECT后面跟着的字段是相同的话，不使用表名来调用的话将会报错，表的别名也可以。

2、多表查询的分类讲解

2.1、等值连接和非等值连接

• 等值连接：具体的说就是连接条件是确定的

上面的例子就是一个等值连接：

SELECT e.`name`, n.`学校名称` 
FROM employes e, numbler n
WHERE e.class = n.class_id;

WHERE子句的连接条件是确定的。

• 非等值连接：连接条件是一个范围，是不确定的

SELECT e.`name`, n.`学校名称` 
FROM employes e, numbler n
WHERE e.class >= n.min AND e.class <= n.max;

用法就是这样的，但我自己弄的的素材不够完成这个语句所以请见谅。

2.2、自连接和非自连接

• 非自连接：就是上面的那样，调用不同的表来完成查询
• 自连接：只调用自身来查询同一个表中相关联的数据。

例：

SELECT emp.em_id,emp.name,mgr.em_id,mgr.name
FROM employees emp , employees mgr
WHERE emp.ma_id = mgr.em_id;

FROM子句处就是自己调用自己，产生两个别名，来查询符合条件的数据。

2.3、内连接和外连接

• 内连接：合并具有同一列的的两个以上的行，结果不包含一个表与另一个表不匹配的行。
• 外连接：两个表连接过程中除了返回满足连接条件的行以外，还返回左(或右)表中不满足的行，这种连接称为左(或右) 外连接，没有匹配的行时，结果表中相应的列为空(NULL)。
• 如果是左外连接，则连接条件中左边的表也称为左表，右边的表称为从表。
• 如果是右外连接，则连接条件中右边的表也称为右表，左边的表称为从表。

例如我们上面的多表查询就是内连接，其中我们匹配的就没有显示：

原本左表是有10个数据的，但我们多表查询出来以后就只有8个数据了

SQL92实现内连接：

看：上面这个利用多表查询出来的就只有8个数据了，这就是内连接查询的结果。
aaa同学和bbb同学因为class为NULL，也就是没有班级，所以和右表无法匹配，就无法显示出数据，接下来我们将使用外连接来把左表数据不匹配的查询出来。

• 左外连接：返回左表中不满足的行。
• 有外连接： 返回右表中不满足的行。

SQL92使用 (+) 来实现来外连接：

• 在SQL92中采用（+）代表从表所在的位置，即左或右外表连接中，（+）表示哪个是从表。
• Oracle对SQL92支持较好，而MySQL不支持SQL92的外连接，也就是无法是（+）来进行外连接。

SELECT e.`name`, n.`学校名称` 
FROM employes e, numbler n
WHERE e.class = n.class_id（+）;

这样就能显示出不匹配的部分了。

3、SQL99语法实现多表查询

1. 基本语法

• SQL99语法中使用 JOIN... ON 的方式实现多表的查询，这种方式也能解决外连接问题。MySQL是支持这种方式的。

SELECT e.`name`, n.`学校名称` 
FROM employes e JOIN numbler n
ON e.class = n.class_id;

JOIN 后面更着另一个表，不需要WHERE子句，替换成ON，如果我们还想比较一个表，那么就再在后面加上 JOIN…ON… ，
例：

2. 内连接的实现
见上；

3. 外连接的实现

• 左外连接：在JOIN前面加上一个LEFT

• 右外连接：在JOIN前面加上一个RIGHT

SELECT e.`name`, n.`学校名称` 
FROM employes e RIGHT JOIN numbler n
ON e.class = n.class_id;

• 满外连接见下。

4、UNION的使用

合并查询结果：
利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集，合并时，两个表对应的列数据类型必须相同，并且相互对应。各SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

SELECT 字段， ... FROM 表1
UNION [ALL]
SELECT 字段，... FROM 表2;

UNION 操作符返回两个查询的结果集的并集，不进行去重操作，中间相同的会保留两份。

- 满外连接：左右所有数据显示：

SELECT e.`name`, n.`学校名称` 
FROM employes e RIGHT JOIN numbler n
ON e.class = n.class_id
UNION ALL
SELECT e.`name`, n.`学校名称` 
FROM employes e RIGHT JOIN numbler n
ON e.class = n.class_id;

5、7中SQL JOIN的实现

代码实现就是他上面的。

1. 中图：内连接。两个表同时满足条件的共同部分，左表和右表不相同不匹配的将不显示
2. 左上图：左外连接。不仅查询出两个表共同满足条件的，而且还查询出左表中不满足的一起显示，也就是中间红和左边红。
3. 右上图：右外连接。不仅查询出两个表共同满足条件的，而且还查询出右表中不满足的一起显示，也就是中间红和右边红。
4. 左下图：满外连接。左右两个表所有的数据都显示。MySQL中无法使用FULL，所以只能使用上面的方法。

6、SQL99语法新特性

6.1、自然连接

• SQL99在SQL92的基础上提供了一些特性语法，比如NATUAL JOIN用来表示自然连接。我们可以把自然连接理解为SQL92中的等值连接。自然连接会自动查询两个连接表中所有相同的字段，然后进行等值连接。

SQL92:

SELECT e.`name`, n.`学校名称` 
FROM employes e LEFT JOIN numbler n
ON e.class = n.class_id;

如果还有其他字段相同的话就可以直接使用自然连接，上面代码就可以写成：

SQL99中:

SELECT e.`name`, n.`学校名称` 
FROM employes e NATUAL JOIN numbler n;

6.2、USING连接

• 上面那个是查询两个表中所有相同字段，如果我们想要查询指定具体的相同字段，就可以使用USING()子句：

SELECT e.`name`, n.`学校名称` 
FROM employes e  JOIN numbler n
USING(class);//但需要两个列名是相同的，也就是说都是class

五、单行函数

• MySQL提供了一些丰富的内置函数，这些函数使得数据的维护与管理跟家方便，能够更好的提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。
• MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里主要将这些丰富的内置函数分为两类：单行函数、聚合函数（或分组函数），这节主要讲单行函数。

1、数值函数

1.1、基本函数

函数	用法
ABS(X)	返回x的绝对值
SIGN(X)	返回x的符号，正数返回1，负数返回-1,0返回0
PI()	返回圆周率
CEIL(X),CEILING(X)	返回大于或等于每个值的最小整数
FLOOR(X)	返回小于或等于每个值的最大整数
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(X,Y)	返回x除以y后的余数
RAND()	返回0~1的随机值
RAND(X)	返回0~1的随机值，其中x的值用作种子值，相同的x值会产生系统的随机数
ROUND(X)	返回一个对x的值进行四舍五入后，接近于x的整数
ROUND(X,Y)	返回一个对x的值进行四舍五入后最接近x的值，并保留到小数点后y位
TRUNCATE(X,Y)	返回数字x截断为y位小数的结果
SQRT(X)	返回x的平方根。当x的值为负数是，返回NULL

下面是表中的几个例子，用法都差不多，就随便就两个：

1.2、角度与弧度互换函数

函数	用法
RADIANS(X)	将角度转化为弧度，其中，参数x为角度值
DEGREES(X)	将弧度转化为角度，其中，参数x为弧度值

1.3、三角函数

• 三角函数其实是和数学里面的一样的，但需要注意的是，函数里面的参数不是角度，而是弧度值，所以如果你想要利用三角函数来计算，就需要配合上面的互换函数来实现。

函数	用法
SIN(X)	返回x的正弦值，其中，参数x是弧度值
ASIN(X)	返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(X)	返回x的余弦值，其中，参数x为弧度值
ACOS(X)	返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(X)	返回x的正切值，其中，参数x是弧度值
ATAN(X)	返回x的反正切值，即获取正切值为x的值
ATAN(m,n)	返回两个参数的正切值
COT(X)	返回x的余切值，其中，x为弧度值

例：

先用转换函数将30度和60度转换成弧度，然后又用三角函数计算出结果，sin(30)=0.5,cos(60)=0.5。

1.4、指数与对数

函数	用法
POW(X,Y),POWER(X,Y)	返回x的y次方
EXP(X)	返回e的x次方，其中e是一个常数: 2.7…
LN(X),LOG(X)	返回以e为底的x的对数，当x<=0时，返回NULL
LOG10(X)	返回以10为底的x的对数，当x<=0时，返回NULL
LOG2(X)	返回以2为底的x的对数，当x<=0时，返回NULL

例：

1.5、进制间的转换

函数	用法
BIN(X)	返回x的二进制编码
HEX(X)	返回x的十六进制编码
OCT(X)	返回x的八进制编码
CONV(X,f1,f2)	返回f1进制数变成f2进制数

例：

2、字符串函数

注意：MySQL中，字符串的位置是从1开始的，其他编程语言是从0开始的，要注意区分。

函数	用法
ASCLL(S)	返回字符串s中的第一个字符的ASCLL码值
CHAR_LENGT(S)	返回字符串s的字符数，作用与CHARACTER_LENGTH(S)相同
LENGTH(S)	返回字符串s的字节数，和字符集有关
CONCAT(S1,S2,…,Sn)	连接s1,s2,…,sn为一个字符串
CONCAT_WS(X,S1,S2,…,Sn)	同CONCAT(s1,s1,…)一样，但是每个字符串之间要加上x
INSERT(srt,idx,len,replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str,a,b)	用字符串b替换字符串str中所有的字符串a
UPPER(S)或USASE(S)	将字符串s中的所有字母转换为大写字母
LOWER(S)或LCASE(S)	将字符串s中的所有字母转换为小写字母
LEFT(str,n)	返回字符串st最左边的n个字符
RIGHT(str,n)	返回字符串st最右边的n个字符
LPAD(str,len,pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str,len,pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(S)	去掉字符串s左侧的空格
RTRIM(S)	去掉字符串s右侧的空格
TRIM(S)	去掉字符串s开始与结尾的空格
TRIM(s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)	去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str,n)	返回str重复n次的结果
SPACE(n)	返回n个空格
STRCMP(s1,s2)	比较字符串s1，s2的ASCLL码值的大小
SUBSTR(s,index,len)	返回从字符串s的index位置其len个字符
LOCATE(substr,str)	返回字符串substr在字符串str中首次出现的位置，未找到返回0
ELT(m,s1,s2,…,sn)	返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn)	返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)	返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(S)	返回s反转后的字符串
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

这个就不举例了，用法都不差多，有兴趣的话可以自己试一下。

3、日期和时间函数

3.1、获取日期、时间

函数	用法
CURDATE(), CURRENT_DATE()	返回当前日期，只包含年、月、日
CURTIME(), CURRENT_TIME()	返回当前时间，只包含时、分、秒
NOW()/SYSDATE()	返回当前系统时期和时间
UTC_DATE()	返回UTC（世界标准时间）日期
UTC_TIME()	返回UTC（世界标准时间）时间

例：

3.2、日期与时间戳的转换

函数	用法
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间。
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

例：

3.3、获取月份、星期、星期数、天数等函数

函数	用法
YEAR(date)/MONTH(date)/DAY()	返回具体日期值
HOUR(time)/MINUTE(time)/SECOND(time)	返回具体时间值
MONTHNAME(date)	返回月份
DAYNAME(date)	返回星期几
WEEKDAY(date)	返回周几
QUARTER(date)	返回日期所对应的季度，范围是1~4
WEEK(date),WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意：周日是1，周一是2…

例：

3.4、日期的操作函数

函数	用法
EXTRACT(type FROM date)	返回指定日期中特定的部分，type指定返回的值

在这个函数中，type是可以有多个取值与含义的：

type取值	含义
MINCROSECOND	返回毫秒数
SECOND	返回秒数
MINUTE	返回分钟数
HOUR	返回小时数
DAY	返回天数
WEEK	返回日期在一年中的第几个星期
MONTH	返回日期在一年中的第几个月
QUARTER	返回日期在一年中的第几个季度
YEAR	返回日期年份
SECOND_MICOSECOND	返回秒和毫秒值
DAY_SECOND	返回天和秒值
DAY_MINUTE	返回天和分钟值
DAY_HOUR	返回天和小时
YEAR_MONTH	返回天和月

SELECT EXRACT(MINUTE FROM NOW())
FROM DUAL;

3.5、时间和秒钟转换的函数

函数	用法
TIME_TO_SEC(time)	将time转换为秒并返回结果值，公式:小时*3600+分钟*60+秒
SEC_TO_TIME(seconds)	将seconds描述转换为包含小时、分钟和秒的时间

SELECT TIME_TO_SEC(NOW())
FROM DUAL;

4、流程控制函数

• 流程处理函数可以根据不同的条件，执行不同的流程控制，可以在SQL语句中实现不同的条件选择，MySQL中的流程处理函数主要包括：IF()、IFULL()和CASE()函数：

函数	用法
IF(条件，值1，值2)	如果条件为真(TRUE),返回值1，否则返回值2
IFUNLL(值1，值2)	如果值1不为NULL，返回值1，否则就返回值2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2…END	相当于if…else if…else…
CASE expr WHEN 常量值1 THEN 值2 WHEN 常量值1 THEN 值1… END	相当于switch…case…

5、加密与解密函数

• 加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。

函数	用法
PASSWORD()str	返回字符串str的加密版本，41位长的字符串，加密结果不可逆，常用于用户的密码加密
MDS(str)	返回字符串str的加密后的值，也是一种加密方式，若参数为NULL，则会返回NULL
SHA(str)	从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5史加安全
ENCODE(value,password_seed)	返回使用password_seed作为加密密码加密value
DECODE(value,password_seed)	返回使用password_seed作为加密密码解密value

SELECT PASSWORD('字符串');

6、 MySQL信息函数

• MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好的对数据库进行维护工作。

函数	用法
VERSION()	返回当前MySQL的版本号
CONNECT	返回当前MySQL服务器的连接数
DATABASE(),SCHEMA()	返回MySQL命令行当前所在的数据库
USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER()	返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHASET(value)	返回字符串value自变量的字符集
COLLATION(value)	返回字符串value的比较规则

例：

版本号为8.0.31.

7、其他函数

MySQL中有些函数无法对其具体的类，但是这些函数在MySQL的开发和运维过程中也是不可忽略的。

函数	用法
FORMAT(value，n)	返回对数字进行格式化后的结果，n表示四合五入后保留到小数点后n位
CONV(value,from,to)	将value的值进行不同进制之间的转换
INET_ATON(ipvalue)	将以点分隔的ip地址转换为一个数字
INET_NTOA(value)	将数字类型的ip地址转换为以点分隔的ip地址
BENCHMAK(n，expr)	将表达式expr重复执行n次，用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code)	将value所使用的字符串编码修改为char_code

例：

六、聚合函数

• 上面我们介绍到了单行函数，SQL函数还有一类叫做聚合（或聚集、分组）函数，它们是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

1、聚合函数的介绍

• 什么是聚合函数？

聚合函数就是将一组数据进行数据处理，最后输出一个符合条件的值，如果是有多组数据，那么就会返回各个组满足条件的值。

• 聚合函数的类型
• AVG() :求平均值
• SUM: 求和
• MAX(): 求最大值
• MIN(): 求最小值
• COUNT():计数

COUNT:
作用：计算出指定字段在查询结构中出现的次数

方式：
1、COUNT(*) : 查询整个字段出现的个数
2、COUNT(1)：和上面的一样
3、COUNT(指定字段)：因为需要考虑NULL，所以不一定对，不计算NULL

原表中有10条数据，第一种就没有错，而使用字段名的就错了，因为不会将NULL算入。

注：那么应该选择谁呢，这里推荐使用第一和第二两种，因为比第三种快，使用第三种的话可以看具体情况，有时候必须使用第三种。

2、GROUP BY

• GROUP BY 是将一整列按照相同的字段相同的就归纳为一个组，比如班级是一样的就可以归为一个组，GROUP BY就是这个作用。

2.1、基本使用

大家可以根据上面的那个表来看，相同的变成了一个组。

聚合函数需要配合这个分组来使用：

这样就可以利用聚合函数求出每个组里最大的一个值或者求和等等。

2.2、使用多个列分组

SELECT 字段1,字段2，SUM(字段)
FROM 表名
GROUP BY 字段1，字段2;
// 将字段1相同切字段二也都相同的部分分为一个组 

注：SELECT的一行语句中只能出现GROUP BY这一行的字段，比如上面的只能出现字段1和字段2，出现别的就会出现错误。

结论：GROUP BY 必须声明在FROM后面、WHERE后面，ORDER BY前面、LIMIT前面

2.3、GROUP BY中使用WITH ROLLUP

• 使用WITH ROLLUP关键字以后，在所有查询出的分组记录之后新增加一条记录，这一条新增加的记录查出来的总和，也就是统计数量。

SELECT 字段1，SUM(字段)
FROM 表名
GROUP BY 字段1 WITH ROLLUP;

注：当使用ROLLUP时，不能使用ORDER BY子句进行结果排序，因为ROLLUP和ORDER BY是相互排斥的，新添加的那一条记录无法参与排序。

3、HAVING

和WHERE一样是用来过滤数据的，但是有所区别。

3.1、基本使用

//查找出每个组里面最大切找过1000的
SELECT 字段1，MAX(字段)
FROM 表名
GROUP BY 字段1
HAVING MAX(字段)>1000;

使用HVING：

1. 行已经被分组了
2. 使用聚合函数
3. 满足HAVING子句中条件的分组将被显示
4. HVING不能单独使用，必须要跟GROUP BY一起使用

3.2、HAVING与WHERE

上面代码的错误写法：

SELECT 字段1，MAX(字段)
FROM 表名
WHERE MAX(字段)>1000;
GROUP BY 字段1

为什么是错误的呢，因为还没有执行到GROUP BY子句，也就是还没有分组，所以不可以使用聚合函数MAX()。
接下来我们要讲一下SELECT的执行过程就明白了。

4、SELECT 的执行过程

查询的结构：

**SQL92：**
SELECT ...(存在聚合函数)
FROM ...
WHERE 多表连接的条件 AND 不包含聚合函数的过滤条件
GROUP BY ...
HAVING 包含聚合函数的过滤条件
ORDER BY ... (ASC/DESC)
LIMIT...

**SQL92：**
SELECT ...(存在聚合函数)
FROM ...(LEFT / RIGHT)JOIN ... ON 多表连接的条件
WHERE 不包含聚合函数的过滤条件
GROUP BY ...
HAVING 包含聚合函数的过滤条件
ORDER BY ... (ASC/DESC)
LIMIT...

SELECT执行顺序：

关键字顺序：

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT …

执行顺序：

FROM -> WHERE -> GROUP -> HAVING -> SELECT -> DISTINCT(去重) -> ORDER BY -. LIMIT

这就是执行语句了，所以上面的WHERE与HAVING的问题应该不是问题了，因为在执行WHERE的时候，还没有执行GROUP BY,还没有进行分组，所以不能使用聚合函数，就只能使用HAVING了。

七、子查询

• 子查询指的是在一个查询语句中再嵌套一个查询，这个和其他语言的for循环嵌套一样
• 我们在查询一个问题时，如果不用子查询的话，可能需要使用多次查询语句，依次得出结果，而使用子查询的话可以一次完成。

问题：找出class大于hhh同学的同学名字和班级？
使用子查询结果：

一个查询语句里面嵌套一个查询，这就是子查询，当然可以嵌套多个，里面的查询叫做内查询，外面的叫做外查询。

1、单行子查询

• 什么是单行子查询？

SELECT `name`, class 
FROM employes 
WHERE class > (
			SELECT class
			FROM employes
			WHERE `name` = 'hhh'
			//内层查询只查出了一个结果
			);

根据上面的例子来解释，内查询的结果只有一个，所以就是单行子查询。

就是

1.1、单行比较操作符

操作符	名称
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

只适用于单行子查询

1.2、子查询的使用

• 基本使用

SELECT 字段1, 字段2 
FROM 表名 
WHERE 字段3 > (
			SELECT 字段3
			FROM 表名
			WHERE 条件判断
			);
# 结合上面的例子看

• HAVING中的子查询

SELECT `name`, class 
FROM employes 
GROUP BY class
HAVING  class > (
				SELECT class
				FROM employes
				WHERE `name` = 'hhh'
				);

• 如果你想要查询的不存在，也就是查询值为NULL，那么结果就会返回NULL。

• 非法使用子查询

简单来说就是内查询中出现了多行查询，使用单行查询的操作符就会不知道该怎么匹配，就会报错。

# 这是一个内查询语句，
# WHERE语句会返回多行数据，就是多行查询，就会报错
SELECT class
FROM employes
WHERE class > 2;

2、多行子查询

简单来说就是内查询会产生多行，多个值。

• 也称为集合比较子查询
• 内查询返回多行
• 使用多行比较操作符

多行比较操作符

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和内查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和内查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常用ANY

# IN
SELECT `name`, class 
FROM employes 
WHERE class IN (
				SELECT class
				FROM employes
				WHERE class >2
				# 内查询返回多行
				);

# ALL / ANY
SELECT `name`, class 
FROM employes 
WHERE class < ANY / ALL (
				SELECT class
				FROM employes
				WHERE class >2
				# 内查询返回多行
				);
# ANY 会返回某一个值
# ALL则会将将class和内查询结果中最小的比较或者最大比较
# 自己可以试试

扩展：
聚合函数是不可以嵌套使用的，但可以改变一下：

# price可以是某个东西，比如工资
# 如果你想这样做是不可以的：MIN(AVG(price))
求出平均工资中的最小值
SELECT MIN(price)
FROM(
	SELECT AVG(price) avg_sal
	FROM employees
	GROUP BY id
	) is_id;
# 给AVG()函数起别名，然后又创建了一个临时表

3、相关子查询

概念：内查询里面使用到了外查询的表的数据，大多情况下是子查询的WHERE子句中引用了外部查询的表。

执行过程：
（1）从外层查询中查询一个数据，将数据相关列的值传给内查询。
（2）执行内层查询，得到子查询操作的值。
（3）外查询根据内查询返回的结果或结果集得到满足条件的行。
（4）然后外层查询取出下一个元组重复操作，直到外层的数据全部处理完毕。
所以，相关子查询时一个类似于循环的过程。

3.1、EXISTS 与 NOT EXISTS关键字

• 关联子查询（或相关子查询）通常也会和EXISTS操作符一起来使用，用来检查子查询中是否存在满足条件的行。

• 如果在子查询中不存在满足条件的行：
  。条件返回FALSE
  。继续在查询中查找

• 如果在查询中存在满足条件的行：
  。不在子查询中继续查找
  。条件返回TRUE

• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE,否则就返回FALSE。

用法：

SELECT id,class
FROM employes e1
WHERE EXISTS (
			SELECT class
			FROM employes e2
			WHERE e1.class> e2.class
  			);
# 如果外查询传进去的值在子查询中条件符合就查找出数据，不符合就继续查找
# NOT EXISTS则是相反

这篇文章就到这了，有用的话点个赞呗，谢谢。