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.联合查询 : UNIONUNION 是 SQL 的集合操作符用来把多个 SELECT 查询的结果合并成一个结果集。select * from venus1 union select * from venus2;1UNION 的使用规则必须满足列数必须相同所有被 UNION 连接的 SELECT 语句查询出来的列数必须完全一样。列的顺序必须相同不仅列数要一样每一列的含义和顺序也需要对应。比如第一个查询是 SELECT 姓名, 年龄第二个也应该是 SELECT 姓名, 年龄而不是 SELECT 年龄, 姓名。数据类型必须兼容对应位置的列数据类型必须能匹配。例如第一个查询的第一列是数字如年龄那么第二个查询的第一列也应该是数字或可以转换成数字的类型。2UNION 的关键特性默认去重使用 UNION 时数据库会自动删除结果集中完全重复的行。如果想保留所有行包括重复的应该使用 UNION ALL。只增加行数不增加列数UNION 的结果列数和第一个 SELECT 语句的列数完全相同只是在垂直方向上增加了行数。3相关的集合操作符UNION并操作合并两个结果集自动去重。UNION ALL并操作不去重合并两个结果集保留所有重复行。INTERSECT交操作只保留两个结果集中都存在的行交集。EXCEPT差操作只保留第一个结果集中有、但第二个结果集中没有的行差集。2.组合查询order byORDER 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:121.获取当前日期时间 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); 计算两个日期之间的天数差前者减后者 782字符串函数CONCAT() 字符串连接函数CONCAT() 函数用于将两个或多个字符串连接成一个单一的字符串。CONCAT(str1, str2, ...)参数str1, str2, ... 是要连接的字符串。可以是字符串常量、列名或其他返回字符串的表达式。返回值返回连接后的新字符串。如果任何一个参数为 NULL则整个函数返回 NULL。-- 连接三个字符串 SELECT CONCAT(Hello, , World); -- 运行结果: Hello WorldSELECT 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); 结果123345GROUP_CONCAT作用把分组内的数据拼接成一行LOCATE作用查找子串第一次出现的位置LOCATE(要找的字符, 原串) LOCATE(要找的字符, 原串, 起始位置)SELECT LOCATE(a,locala,5);INSERT替换字符串作用从 pos 位置开始替换 len 长度为 str2INSERT(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); -- 运行结果: 136FLOOR() :向下取整函数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%;8MYSQL安装路径char查询语句 show variables like%char%;9查看当前系统版本version_compile_os查询语句 select version_compile_os;6.字符串截取函数1left (字符串长度)作用取左边 n 个字符示例SELECT LEFT(user(), 1);判断第一个字符是不是rSELECT LEFT(user(),1)r;正确返回1错误返回02 right (字符串长度)作用取右边 n 个字符示例SELECT RIGHT(user(), 1); SELECT RIGHT(user(),1)r;3 substr /mid截取子串作用从第 n 位开始截取 len 长度语法substr(字符串, 起始位置, 截取长度) mid(字符串, 起始位置, 截取长度)示例截取第 2 个字符判断是否为 rSELECT substr(database(),2,1)r; SELECT mid(database(),2,1)r;7.逻辑判断函数1IF (条件成立返回不成立返回)SELECT IF(12, 1, 0); 结果12CASE WHENCASE WHEN 条件 THEN 结果1 ELSE 结果2 END8.延时函数SQL 注入必考SLEEP(N)作用让 SQL 暂停 N 秒SELECT SLEEP(5);注入里用于时间盲注9.编码转换函数1ASCII()取字符的 ASCII 值2HEX()转为 16 进制3CHAR()数字 → 字符4OCT()转为 8 进制5ORD()返回字符串第一个字符的 ASCII 值三、information_schemaMySQL 元数据库作用存储所有数据库、表、字段的信息查所有库information_schema.SCHEMATA查所有表information_schema.TABLES查所有字段information_schema.COLUMNS