SQL语法笔记(持续更新）_sql 中使用关键字 而非逗号的函数

SQL语法笔记

1 SQL语言概述

1.1 SQL分类

SQL语言在功能上主要分为以下几类：

• 数据查询语言（Data Query Language，DQL），用于进行数据查询而不会对数据本身进行修改的子句，是最基本的SQL子句。

  • 主要的子句关键字包括SELECT、FROM、WHERE、GROUP BY、HAVING和ORDER BY等。

• 数据定义语言（Data Definition Language，DDL），用于定义数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。

  • 主要的子句关键字包括CREATE、DROP和ALTER等。

• 数据操纵语言（Data Manipulation Language, DML），用于添加、删除、更新和查询数据库记录，并检查数据完整性。

  • 主要的子句关键词包括INSERT、DELETE、UPDATE和SELECT等。

• 数据控制语言（Data Control Language，DCL），用于定义数据库、表、字段、用户的访问权限和安全级别。

  • 主要的子句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK和SAVEPOINT等。

---

1.2 SQL语言的规则和规范

1.2.1 基本规则

• SQL可以写在一行或者多行。为了提高可读性，各句子分行写，必要时使用缩进；
• 每条命令以;结束；
• 关键字不能被缩写也不能分行；
• 关于标点符号：
  • 必须保证所有的()、单引号和双引号是成对结束的；
  • 必须使用英文符号；
  • 字符串型和日期时间类型的数据可以使用单引号(’’)表示；
  • 列的别名，尽量使用双引号("")，而且不要省略as；

1.2.2 SQL大小写规范

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

1.2.3 注释

注释格式如下：

单行注释：#注释文字
单行注释：-- 注释文字（--后面必须包含一个空格）
多行注释：/*注释文字	*/
1
2
3

---

2 SELECT子句

2.1 SELECT子句作用

SELECT子句用于从表中选取数据。

结果被存储在一个结果表中（称为结果集）

2.2 SELECT语法

2.2.1 SELECT … FROM

• 语法：

# 搜索整个表:
SELECT *
FROM 表名;

# 搜索表中的某列(属性):
SELECT 字段
FROM 表名;

# 搜索表中的某些列(属性):
SELECT 字段1, 字段2, ...
FROM 表名;

# 数字类型的值可以运算:
SELECT 字段1 * 2
FROM 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.2.2 AS（列的别名）

• 重命名一个列

• 语法：

# 可用作单个搜索:
SELECT 字段 AS 别名
FROM 表名;

# 可用作多个搜索:
SELECT 字段 AS 别名, 字段 AS 别名, 字段 AS 别名
FROM 表名;

# 可根据情况选择使用:
SELECT 字段, 字段 AS 别名, 字段 AS 别名
FROM 表名;

# 也可使用双引号引起而不使用关键字AS:
SELECT 字段 "别名" 
FROM 表名;

# 也可以直接省略:
SELECT 字段 别名 
FROM 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2.2.3 DISTINCT（去除重复行）

• 去除查询中重复的属性值（列值）
• 语法：

# 单个搜索:
SELECT DISTINCT 字段
FROM 表名;

# 多个搜索:
SELECT DISTINCT 字段1, 字段2
FROM 表名;
1
2
3
4
5
6
7

2.2.4 NULL（空值参与运算）

• 空值（NULL）不等同于0，’’，""，‘NULL’；

• NULL与任何值参与运算，结果都等于NULL；

• IFNULL()函数可以使用值替换NULL：

# 替换NULL值:
SELECT 字段1 * IFNULL(字段2/*NULL*/, 值)
FROM 表名;
1
2
3

2.2.5 着重号（``)

• 属性名与关键字重名时，使用着重号引起来；
• 语法：

# FROM为关键字，使用着重号引起后则为字段:
SELECT `FROM`
FROM 表名;

# FROM为关键字，使用着重号引起后则为表名:
SELECT * FROM `FROM`;
1
2
3
4
5
6

2.2.6 查询常数

• 使用常数时，每一行都会匹配常数;
• 语法：

SELECT 常数, 字段
FROM 表名;
# 结果为：常数 + 字段
1
2
3

---

3 显示表结构

3.1 DESCRIBE（显示表结构）

• 显示表中字段的详细信息

• 语法：

DESCRIBE 表名;

# 缩写:
DESC 表名;
1
2
3
4

• 含义

---

4 过滤数据

4.1 WHERE子句

4.1.1 WHERE子句作用

• 将WHERE子句添加到SELECT子句中，可有条件地从表中选取数据；
• WHERE子句紧随FROM子句；
• 语法：

SELECT 字段
FROM 表名
WHERE 字段 运算符 值;
1
2
3

4.1.2 运算符

• 下列运算符可在WHERE子句中使用：

4.1.3 引号

• 数值不要使用引号
• 如果是文本值则使用单引号
• 子句：

# 数值不需要加引号:
SELECT *
FROM 表名
WHERE 字段 运算符 数值;

# 文本值需要加引号:
SELECT *
FROM 表名
WHERE 字段 运算符 '文本值';
1
2
3
4
5
6
7
8
9

5 运算符

5.1 算术运算符

• 算术运算符主要用于数学运算，其可以连接运算符前后的两个数字或表达式，对数字或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

其中加法没有连接的作用，只表示加法运算，此时会将字符串转换为数值。

5.2 比较运算符

• 比较运算符用来对表达式左边操作数和右边操作数进行比较，比较结果为真则返回1，为假则返回0，其他情况返回NILL。

5.2.1 符号比较运算符

5.2.2 非符号比较运算符

• LIKE需要搭配通配符使用：
  • %代表不确定个数的字符（0个，1个，或多个），可放在字符前后，可连续使用。
  • _代表一个不确定的字符。
  • 加入转义字符\可将上面两个通配符还原为正常字符。

5.2.2.1 正则表达式查询

• 正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配稳重符合要求的特殊字符串。
• 例如从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等。

5.3 逻辑运算符

• 逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的结果返回1、0或NULL。
• MySQL的4中逻辑运算符如下：

5.4 位运算符

• 位运算符是在二进制数上进行计算的运算符。

• 位运算会先将操作数变成二进制数，然后进行位运算。

• 最后将计算结果从二进制变回十进制数。

• MySQL的运算符如下：

---

6 排序与分页

6.1 排序数据

6.1.1 排序规则

• 使用ORDER BY子句排序
  • ASC（ascend）：升序
  • DESC（descend）：降序

6.1.2 一级排序

• ORDER BY子句在SELECT子句的结尾。
• 语法：

# 升序排序于某字段:
SELECT 字段1, 字段2, 字段3
FROM 表名
ORDER BY 字段(1~3);

# 降序排序于某字段:
SELECT 字段1, 字段2, 字段3
FROM 表名
ORDER BY 字段(1~3) DESC;

#使用别名进行排序:
SELECT 字段1, 字段2 AS 别名, 字段3
FROM 表名
ORDER BY 别名;

# ORDER在WHERE后面:
SELECT 字段1, 字段2, 字段3
FROM 表名
WHERE 字段 运算符 值
ORDER BY 字段(1~3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

6.1.3 多级排序

• 在一级排序后，再选择一个字段进行排序。
• 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
• 语法：

SELECT 字段1, 字段2, 字段3
FROM 表名
ORDER BY 字段(1~3), 字段;

# 也可以让第二级降序排序:
SELECT 字段1, 字段2, 字段3
FROM 表名
ORDER BY 字段(1~3), 字段 DESC;

# 前面降序，后面升序:
SELECT 字段1, 字段2, 字段3
FROM 表名
ORDER BY 字段(1~3) DESC, 字段 ASC;
1
2
3
4
5
6
7
8
9
10
11
12
13

6.2 分页

6.2.1 分页作用

• 查询返回的数据太多，实现分页查询可更加便捷；
• 显示规定的某几条数据；

6.2.2 实现规则

• 分页原理
  • 就是将数据库中的结果集，一段一段显示出来需要的条件。
• 语法：

LIMIT[位置偏移量], 行数;
1

第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从第一条记录开始（第一条记录的位置偏移量是0）；

第二个参数“行数”指示每一页多少行。

• 举例：

# 前10条数据:
SELECT *
FROM 表名
LIMIT 0, 10;

# 或者:
SELECT *
FROM 表名
LIMIT 10;

# 第11至20条记录:
SELECT *
FROM 表名
LIMIT 10, 10;

# 第21至30条记录:
SELECT *
FROM 表名
LIMIT 20, 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• 分页显式公式：（当前页数-1）* 每页条数，每页条数

SELECT *
FROM table
LIMIT (PageNo - 1) * PageSize, PageSize;
1
2
3

注意！LIMIT子句必须放在整个SELECT子句的最后！

---

7 多表查询

前提条件：这些一起查询的表之间是有关系的（一对一、一对多、多对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也有可能没有建立外键。

比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

7.1 表的连接

7.1.1 笛卡尔积（交叉连接）

• 将两个或多个表直接进行连接。
• 一般连接出来的数据大多冗余，不能够被使用。
• 语法：

# 方法一: 将两个表进行笛卡尔积
SELECT 字段1/*来自表1*/, 字段2/*来自表2*/
FROM 表1, 表2;

# 方法二:
SELECT 字段1/*来自表1*/, 字段2/*来自表2*/
FROM 表1
CROSS JOIN 表2;
1
2
3
4
5
6
7
8

也称为笛卡尔积错误，一般在下面的条件下产生：

• 省略多个表的连接条件（或关联条件）；
• 连接条件（或关联条件）无效；
• 所有表中的所有行互相连接；

7.1.2 条件连接

• 在笛卡尔积的基础上增加条件。
• 语法：

# 多个表中无重复字段时:
SELECT 字段1/*来自表1*/, 字段2/*来自表2*/
FROM 表1, 表2
WHERE 连接条件;	# 在WHERE后增加筛选的条件

# 多个表中出现重复字段时:
SELECT 表1.字段1/*来自表1*/, 表2.字段1/*来自表2*/
FROM 表1, 表2
WHERE 连接条件;	# 指明各字段所在的表

# 给表起别名:(如果给表起了别名，在SELECT或WHERE中使用表名的话，必须使用表的别名，不能再使用表的原名)
SELECT t1.字段1/*来自表1*/, t2.字段1/*来自表2*/
FROM 表1 t1, 表2 t2
WHERE 连接条件;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

建议：从SQL优化的角度，建议多表查询时，每个字段前都指明其所在的表。

如果有n个表实现多表查询则需要至少需要n-1个连接条件。

7.2 多表查询的分类

• 角度1：等值连接 vs 非等值连接
• 角度2：自连接 vs 非自连接
• 角度3：内连接 vs 外连接

7.2.1 等值连接和非等值连接

• 示例：

# 等值连接:
SELECT t1.字段1/*来自表1*/, t2.字段1/*来自表2*/
FROM 表1 t1, 表2 t2
WHERE 值 = 值;

# 非等值连接:
SELECT t1.字段1/*来自表1*/, t2.字段1/*来自表2*/
FROM 表1 t1, 表2 t2
WHERE 值 [!=|<|<=|>|>=|...] 值;
1
2
3
4
5
6
7
8
9

7.2.2 自连接和非自连接

• 示例：

# 自连接:
SELECT t1.字段1, t2.字段1
FROM 表1 t1, 表2 t2
WHERE 连接条件;
1
2
3
4

7.2.3 内连接和外连接

• 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行（交集）

• 外连接：两个表在连接过程中除了满足连接条件的行以外，还返回左（或右）表中不满足条件的行，这种连接称为左（或右）连接。没有匹配的行时，结果表中相应的列为空NULL。

  • 外连接的分类：左外连接、右外连接、满外连接
  • 如果是左外连接，则连接条件中左边的表也称为主表、右边的表称为从表。

7.3 SQL99语法实现多表查询

7.3.1 SQL99实现内连接

• 语法：

# 内连接(2张表):
SELECT 字段...
FROM 表1 JOIN 表2
ON 表1.Key = 表2.Key;

# 内连接(3张表):
SELECT 字段...
FROM 表1 JOIN 表2
ON 表1.Key = 表2.Key;
JOIN 表3
ON 表2.Key = 表3.Key;
1
2
3
4
5
6
7
8
9
10
11

7.3.2 SQL99实现外连接

• 语法：

# 左外连接:
SELECT 字段...
FROM 表1 LEFT OUTER JOIN 表2
ON 表1.Key = 表2.Key;

# 右外连接:
SELECT 字段...
FROM 表1 RIGHT OUTER JOIN 表2
ON 表1.Key = 表2.Key;

# 满外连接: MySQL不支持，Oracle支持
SELECT 字段...
FROM 表1 FULL OUTER JOIN 表2
ON 表1.Key = 表2.Key;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

7.4 UNION的使用

合并查询结果

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

• 语法：

SELECT 字段...
FROM 表1
UNION [ALL]
SELECT 字段...
FROM 表2;
1
2
3
4
5

7.4.1 UNION操作符

• UNION操作符返回两个查询接过的结果集的并集，区处于重复记录。

7.4.2 UNION ALL操作符

• UNION ALL操作符返回两个查询的结果接的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL子句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL子句，以提高数据查询的效率。

7.5 7种SQL JOINS的实现

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KE3G3TGY-1648026404331)(https://www.runoob.com/wp-content/uploads/2019/01/sql-join.png “JOIN”)]

• 中图：内连接

• 左上图：左外连接；

• 右上图：右外连接；

• 左中图：左上图基础上加WHERE；

• 右中图：右上图基础上加WHERE；

• 左下图：满外连接

  • 方式1：左上图UNION ALL右中图
  • 方式2：左中图UNION ALL右上图

• 右下图：

• • 左中图UNION ALL右中图

7.6 SQL99语法新特性

7.6.1 自然链接

• 语法：

SELECT 字段...
FROM 表1 NATURAL JOIN 表2
1
2

8 单行函数

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以是一列或一个值

8.1 数值函数

8.1.1 基本函数

• 举例：

SELECT
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;
1
2
3
4

8.1.2 角度与弧度互换函数

8.1.3 三角函数

8.1.4 指数函数

8.1.5 进制间的转换函数

8.2 字符串函数

8.3 日期和时间函数

8.3.1 获取日期、时间

8.3.2 日期与时间戳的转换

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

8.3.4 日期的操作函数

EXTRACT(type FROM date)函数中type的取值与含义：

8.3.5 时间和秒钟转换的函数

8.3.6 计算日期和时间的函数

第1组：

上述函数中type的取值：

第2组：

8.3.7 日期的格式化与解析

上述非GET_FORMAT函数中fmt参数常用的格式符：

8.4 流程控制函数

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

8.5 加密与解密函数

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

8.6 MySQL信息函数

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

8.7 其他函数

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

9 聚合函数

• 聚合函数作用于一组数据，并对一组数据返回一个值；
• 聚合函数语法

SELECT [column, ] grounp function(column), ...
FROM 表
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
1
2
3
4
5

聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

9.1 常用聚合函数