MySQL数据库基础3--（函数）完

一、聚合函数

        聚合函数包括COUNT()、SUM()、AVG()、MAX()和MIN()。当需要对表中的记录求和、求平均值、查询最大值和查询最小值等操作时，可以使用聚合函数。GROUP BY关键字通常需要与聚合函数一起使用。

COUNT()用来统计记录的条数；

SUM()用来计算字段的值的总和；

AVG()用来计算字段的值的平均值；

MAX() 用来查询字段的最大值；

MIN()用来查询字段的最小值。

1.count（）

        统计表中数据的行数或者统计指定列其值不为NULL的数据个数。

2.sum（）

        计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0。

3.avg（）

        计算指定列的平均值。

4.max（）

        计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算。

5.min（）

        计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算。

二、查询函数

1.联合查询 : UNION

        UNION 是 SQL 的集合操作符，用来把多个 SELECT 查询的结果，合并成一个结果集。

select * from venus1
union
select * from venus2;

（1）UNION 的使用规则（必须满足）

• 列数必须相同

所有被 UNION 连接的 SELECT 语句，查询出来的列数必须完全一样。

• 列的顺序必须相同

不仅列数要一样，每一列的含义和顺序也需要对应。比如第一个查询是 SELECT 姓名, 年龄，第二个也应该是 SELECT 姓名, 年龄，而不是 SELECT 年龄, 姓名。

• 数据类型必须兼容

对应位置的列，数据类型必须能匹配。例如，第一个查询的第一列是数字（如年龄），那么第二个查询的第一列也应该是数字或可以转换成数字的类型。

（2）UNION 的关键特性

• 默认去重

使用 UNION 时，数据库会自动删除结果集中完全重复的行。
如果想保留所有行，包括重复的，应该使用 UNION ALL。

• 只增加行数，不增加列数

UNION 的结果，列数和第一个 SELECT 语句的列数完全相同，只是在垂直方向上增加了行数。

（3）相关的集合操作符

• UNION（并操作）：合并两个结果集，自动去重。
• UNION ALL（并操作，不去重）：合并两个结果集，保留所有重复行。
• INTERSECT（交操作）：只保留两个结果集中都存在的行（交集）。
• EXCEPT（差操作）：只保留第一个结果集中有、但第二个结果集中没有的行（差集）。

2.组合查询：order by

        ORDER BY 子句用于对 SELECT 查询返回的结果集，按照一个或多个列进行排序。

• 基本用法

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

column1, column2, ...: 你想要排序的列名。
ASC: 升序排列（默认值，可以省略）。
DESC: 降序排列。

• 按列名排序（最常用）

-- 按学生年龄升序排列
SELECT * FROM students ORDER BY age;

-- 按学生姓名降序排列
SELECT * FROM students ORDER BY name DESC;

• 按列索引排序

        除了使用列名，还可以使用列在 SELECT 语句中的位置索引来排序。

-- 假设查询结果有 id, name, age, address 四列
-- 按第3列（age）升序排列
SELECT * FROM students ORDER BY 3;

-- 按第2列（name）降序排列
SELECT * FROM students ORDER BY 2 DESC;

例：

• select * from students order by 7;

        这条语句成功执行了。因为 students 表正好有 7 列（id, name, age, address, phone, bir_date, sex），所以按第 7 列（sex）排序是合法的。

• select * from students order by 8;

        这条语句失败并报错 Unknown column '8' in 'order clause'。因为 students 表只有 7 列，不存在第 8 列，所以数据库无法执行这个排序指令。
        通过这个方法，我们可以知道 students 表一共有 7 列。在 SQL 注入中，ORDER BY 的核心作用是判断原始查询语句的字段数量。

3.嵌套查询(子查询)

        嵌套查询，也叫子查询，是 SQL 中一个非常强大的功能。它允许你在一个查询中执行另一个查询，将内层查询（子查询）的结果作为外层查询（主查询）的条件或数据源。

（1）子查询使用规则

• 规则 1：子查询可以继续嵌套

        这意味着子查询的内部可以再包含一个子查询，形成多层嵌套结构。理论上，嵌套的深度没有限制（但过深的嵌套会影响性能和可读性）。

-- 查询所有在“技术部”且工资高于该部门平均工资的员工
SELECT name, salary
FROM employees
WHERE department_id = (
    SELECT id FROM departments WHERE name = '技术部' -- 第一层子查询
)
AND salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = (
        SELECT id FROM departments WHERE name = '技术部' -- 第二层子查询
    )
);

• 规则 2：子查询中不可以使用 ORDER BY 子句

        这是一个非常重要的规则。原因在于：
        子查询的目的：子查询的结果通常是作为主查询的一个条件（例如 IN 列表）或一个临时表。它是一个中间结果，而不是最终的展示结果。
        排序的意义：排序是为了最终的输出结果更有条理。对一个中间结果集进行排序是没有意义的，而且会增加数据库的计算负担。
        如果需要对最终结果进行排序，应该在最外层的主查询中使用 ORDER BY。

错误示例：
-- 错误用法，子查询中不应有 ORDER BY
SELECT * FROM employees WHERE id IN (SELECT id FROM temp_employees ORDER BY join_date);


正确示例：
-- 正确用法，在主查询中排序
SELECT * 
FROM employees 
WHERE id IN (SELECT id FROM temp_employees)
ORDER BY name; -- 在这里排序

• 规则 3：子查询要用括号 () 括起来

        这是 SQL 的强制语法要求。括号用于明确地界定子查询的开始和结束，避免了 SQL 语句的歧义。

SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = '技术部');

• 规则 4：子查询的结果可以是多种形式

子查询的结果可以是：
         单个值（标量子查询）：用于 = 或 <> 等比较。
        一列值（列子查询）：用于 IN, ANY, ALL 等。
        一个表（表子查询）：可以当作一个临时表来使用。

（2）关键判断关键字

        当子查询返回一个包含多行数据的集合时，我们需要使用这些关键字来判断主查询中的值与这个集合的关系。

• IN / NOT IN

作用：判断一个值是否存在于（IN）或不存在于（NOT IN）子查询返回的集合中。
本质：a IN (x, y, z) 等价于 a = x OR a = y OR a = z。

- 查询所有属于“技术部”或“市场部”的员工
SELECT name, department_id
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE name IN ('技术部', '市场部')
);

• ANY / SOME

作用：判断一个值是否与集合中的任意一个值满足比较条件（如 >, <, =）。只要有一个满足，结果就为真。SOME 是 ANY 的同义词。
本质：a > ANY(...) 等价于 a > MIN(...)。

- 查询工资高于“技术部”任意一名员工工资的员工
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = '技术部')
);

假设技术部员工的工资是 5000, 6000, 7000。那么只要一个员工的工资大于 5000，他就会被选中。

• ALL

作用：判断一个值是否与集合中的所有值都满足比较条件。必须全部满足，结果才为真。
本质：a > ALL(...) 等价于 a > MAX(...)。

- 查询工资高于“技术部”所有员工工资的员工
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = '技术部')
);

        假设技术部员工的最高工资是 7000。那么只有工资大于 7000 的员工才会被选中。

• EXISTS / NOT EXISTS

作用：判断子查询是否返回至少一行结果。它不关心具体返回了什么值，只关心 “有没有结果”。
性能优势：EXISTS 通常比 IN 更高效，因为它一旦找到匹配项就会立即停止搜索，而 IN 可能需要遍历整个列表。
 

- 查询所有有下属的经理（假设employees表有manager_id字段）
SELECT DISTINCT e.name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employees sub WHERE sub.manager_id = e.id
);

        对于每一个员工 e，子查询会检查是否存在以他为经理的下属。如果存在，EXISTS 就为真，这个经理就会被选中。这里 SELECT 1 只是为了返回一个真值，具体选什么不重要。

关键字	作用	等价逻辑	使用场景
IN	是否在集合里	= OR = OR ...	判断值是否属于某个列表
ANY	是否满足任意一个	> MIN() / < MAX()	与集合中最小 / 最大值比较
ALL	是否满足所有	> MAX() / < MIN()	与集合中最大 / 最小值比较
EXISTS	是否存在结果	判断子查询是否为空	判断关联关系是否存在

4.其他常用函数

（1）时间函数

        假设当前时间为：2026-03-15 14:23:12

1.获取当前日期时间

SELECT NOW();	            获取当前日期和时间	2026-03-15 14:23:12
SELECT CURRENT_TIMESTAMP();	同上，NOW() 的同义词	2026-03-15 14:23:12
SELECT CURRENT_DATE();	    获取当前日期	        2026-03-15
SELECT CURRENT_TIME();	    获取当前时间	        14:23:12

2. 提取日期时间的部分

SELECT DAY(NOW());	        提取当前日期的 “日”	            15
SELECT DATE(NOW());	        提取当前日期时间的 “日期” 部分	2026-03-15
SELECT TIME(NOW());	        提取当前日期时间的 “时间” 部分	14:23:12
SELECT YEAR(NOW());	        提取当前日期的 “年”	            2026
SELECT MONTH(NOW());	    提取当前日期的 “月”	            3

3. 日期时间的计算

SELECT ADDTIME('14:23:12','01:02:01');	    给一个时间添加另一个时间	       15:25:13
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);	    给当前时间加 1 天    2026-03-16 14:23:12
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);	给当前时间加 1 个月	2026-04-15 14:23:12
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);	    给当前时间减 1 天	2026-03-14 14:23:12
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);	给当前时间减 1 个月	2026-02-15 14:23:12
SELECT DATEDIFF('2019-07-22','2019-05-05');	计算两个日期之间的天数差（前者减后者）	 78

（2）字符串函数

• CONCAT() ：字符串连接函数

        CONCAT() 函数用于将两个或多个字符串连接成一个单一的字符串。

CONCAT(str1, str2, ...)

参数：str1, str2, ... 是要连接的字符串。可以是字符串常量、列名或其他返回字符串的表达式。
返回值：返回连接后的新字符串。如果任何一个参数为 NULL，则整个函数返回 NULL。

-- 连接三个字符串
SELECT CONCAT('Hello', ' ', 'World');

-- 运行结果: 'Hello World'

SELECT CONCAT('abc',NULL,'fg');

结果：NULL

-- 连接列中的数据（假设表中有 name 和 age 列）
SELECT CONCAT(name, ' is ', age, ' years old.') AS info FROM users;

-- 运行结果: '张三 is 25 years old.'

• CONCAT_WS

        作用：用指定分隔符连接字符串

CONCAT_WS(分隔符, str1, str2)

SELECT CONCAT_WS('+','123','345');
结果：123+345

• GROUP_CONCAT

        作用：把分组内的数据拼接成一行

• LOCATE

        作用：查找子串第一次出现的位置

LOCATE(要找的字符, 原串)
LOCATE(要找的字符, 原串, 起始位置)

SELECT LOCATE('a','locala',5);

---

• INSERT（替换字符串）

        作用：从 pos 位置开始，替换 len 长度为 str2

INSERT(str1, pos, len, str2)

• INSTR() ：子字符串查找函数

        INSTR() 函数用于查找一个子字符串在另一个字符串中第一次出现的位置。

INSTR(str, substr)

参数：   str: 要在其中进行查找的原始字符串。
              substr: 要查找的子字符串。
返回值：返回子字符串在原始字符串中第一次出现的起始位置（从 1 开始计数）。如果未找到，则返回 0。

-- 在 'Hello World' 中查找 'World'
SELECT INSTR('Hello World', 'World');

-- 运行结果: 7 (因为 'W' 是第7个字符)

-- 在 'Hello World' 中查找 'Java'
SELECT INSTR('Hello World', 'Java');

-- 运行结果: 0 (未找到)

• POSITION

        作用：返回子串在字符串中的位置

POSITION(str1 IN str2)

• LENGTH() :字符串长度统计函数

        LENGTH() 函数用于返回字符串的长度，以字节为单位。

LENGTH(str)

参数：str 是要计算长度的字符串。
返回值：返回字符串的字节数。

-- 计算 'Hello' 的长度
SELECT LENGTH('Hello');

-- 运行结果: 5 (每个字母占1个字节)

-- 计算中文字符串的长度（假设使用UTF-8编码，每个中文字符占3个字节）
SELECT LENGTH('你好');

-- 运行结果: 6 ('你'占3字节，'好'占3字节)

（3）数学函数

•  ABS() :绝对值函数

        ABS() 函数用于返回一个数的绝对值。绝对值是指一个数在数轴上所对应点到原点的距离，所以结果总是非负的。

ABS(number)

参数：number 是一个数值（整数或浮点数）。
返回值：返回该数的绝对值。

-- 计算 -136 的绝对值
SELECT ABS(-136);

-- 运行结果: 136

• FLOOR() :向下取整函数

        FLOOR() 函数用于对一个数进行向下取整，即返回不大于该数的最大整数。

FLOOR(number)

参数：number 是一个数值（整数或浮点数）。
返回值：返回不大于该数的最大整数。

-- 对 3.14 向下取整
SELECT FLOOR(3.14);

-- 运行结果: 3

-- 对 3.999 向下取整
SELECT FLOOR(3.999);

-- 运行结果: 3

-- 对 -3.14 向下取整（注意负数的情况）
SELECT FLOOR(-3.14);

-- 运行结果: -4 (因为 -4 是不大于 -3.14 的最大整数)

• CEILING() : 向上取整函数

        CEILING() 函数用于对一个数进行向上取整，即返回不小于该数的最小整数。它也可以简写成 CEIL()。

CEILING(number)

参数：number 是一个数值（整数或浮点数）。
返回值：返回不小于该数的最小整数。

-- 对 3.14 向上取整
SELECT CEILING(3.14);

-- 运行结果: 4

-- 对 3.0001 向上取整
SELECT CEILING(3.0001);

-- 运行结果: 4

-- 对 -3.14 向上取整（注意负数的情况）
SELECT CEILING(-3.14);

-- 运行结果: -3 (因为 -3 是不小于 -3.14 的最小整数)

5.用户相关函数

（1）系统用户名：system_user()

查询语句： select system_user();

                   select user from mysql.user;

（2）用户名：user()

查询语句： select user();

（3）当前用户名：current_user()

查询语句：current_date()当前日期

                  select current_user();

（4）连接数据库用户名：session_user()

查询语句： select session_user();

（5）数据库名：database()

查询语句： select database();

（6）数据库版本：version()

查询语句： select version();

（7）数据库读取路径：@@basedir

查询语句： show variables like '%basedir%';

（8）MYSQL安装路径：@@char

查询语句： show variables like"%char%";

（9）查看当前系统版本：@@version_compile_os

查询语句： select @@version_compile_os;

6.字符串截取函数

（1）left (字符串，长度)

• 作用：取左边 n 个字符
• 示例：

SELECT LEFT(user(), 1);

判断第一个字符是不是 r：

SELECT LEFT(user(),1)='r';

• 正确返回 1，错误返回 0

---

（2） right (字符串，长度)

• 作用：取右边 n 个字符
• 示例：

SELECT RIGHT(user(), 1);
SELECT RIGHT(user(),1)='r';

---

（3） substr /mid（截取子串）

• 作用：从第 n 位开始，截取 len 长度
• 语法：

substr(字符串, 起始位置, 截取长度)
mid(字符串, 起始位置, 截取长度)

• 示例（截取第 2 个字符，判断是否为 r）：

SELECT substr(database(),2,1)='r';
SELECT mid(database(),2,1)='r';

7.逻辑判断函数

（1）IF (条件，成立返回，不成立返回)

SELECT IF(1<2, 1, 0);
结果：1

（2）CASE WHEN

CASE WHEN 条件 THEN 结果1 ELSE 结果2 END

---

8.延时函数（SQL 注入必考）

SLEEP(N)

• 作用：让 SQL 暂停 N 秒

SELECT SLEEP(5);

注入里用于时间盲注

---

9.编码转换函数

（1）ASCII()

• 取字符的 ASCII 值

（2）HEX()

• 转为 16 进制

（3）CHAR()

• 数字 → 字符

（4）OCT()

• 转为 8 进制

（5）ORD()

• 返回字符串第一个字符的 ASCII 值

三、information_schema（MySQL 元数据库）

作用：存储所有数据库、表、字段的信息

• 查所有库：information_schema.SCHEMATA
• 查所有表：information_schema.TABLES
• 查所有字段：information_schema.COLUMNS