MySQL基础函数使用

目录

简介

1. 单行函数

1.1 字符串函数

1.2 日期函数

1.3 数值函数

1.4 转换函数

1.5 其他函数

2. 多行函数

示例：

3. 数据分组

示例：

4. DQL单表关键字执行顺序

示例：

5. 多表查询

示例：

6. 表与表的外连接

示例：

7. 表与表的自连接

8. 表与表的子连接

9. 表与表的伪表查询

---

简介

在数据库操作中，函数是不可或缺的工具，它们可以帮助我们快速处理数据、转换格式、进行计算等。MySQL提供了丰富的内置函数，涵盖了字符串、日期、数值、转换等多种类型。本文将详细介绍MySQL中的基础函数使用方法，帮助你更好地理解和应用这些函数。

1. 单行函数

单行函数是指对一行数据进行操作并返回一行数据的函数。无论输入多少行数据，单行函数都会逐行处理并返回相应的结果。

1.1 字符串函数

字符串函数主要用于处理字符串数据，常见的字符串函数包括：

• LENGTH: 返回字符串的长度。

  SELECT ename, LENGTH(ename) FROM emp;

• SUBSTR: 截取字符串的一部分。

  SELECT ename, SUBSTR(ename, 1, 3) FROM emp;
  SELECT * FROM emp WHERE SUBSTR(ename, 5, 1) = 'S';

• UPPER/LOWER: 将字符串转换为大写或小写。

  SELECT ename, UPPER(ename), LOWER(ename) FROM emp;

• CONCAT: 拼接字符串。

  SELECT CONCAT(empno, '=', ename) FROM emp;

• REPLACE: 替换字符串中的指定字符。

  SELECT ename, REPLACE(ename, 'T', '—') FROM emp;

1.2 日期函数

日期函数用于处理日期和时间数据，常见的日期函数包括：

• SYSDATE(): 获取当前系统时间。

  SELECT hiredate, SYSDATE() FROM emp;
  SELECT hiredate, CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP() FROM emp;

• DATE_FORMAT: 格式化日期。

  SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%i:%s');
  SELECT hiredate, DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分%s秒') FROM emp;

• ADDDATE: 对日期进行加减操作。

  SELECT hiredate, ADDDATE(hiredate, 9), ADDDATE(hiredate, -9) FROM emp;

1.3 数值函数

数值函数用于处理数值数据，常见的数值函数包括：

• CEIL/FLOOR: 向上或向下取整。

  SELECT CEIL(12.1), FLOOR(12.9);

• ROUND/TRUNCATE: 四舍五入或截取指定小数位。

  SELECT ROUND(1.4999999, 2), ROUND(1.4999999), ROUND(1.4999999, -1);
  SELECT TRUNCATE(1.4999999, 2);

1.4 转换函数

转换函数用于在不同数据类型之间进行转换，常见的转换函数包括：

• DATE_FORMAT: 将日期转换为字符串。

  SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%i:%s');

• STR_TO_DATE: 将字符串转换为日期。

  SELECT STR_TO_DATE('2020-4-16 17:15:24', '%Y-%c-%d %H:%i:%s');

1.5 其他函数

其他函数包括处理空值、加密等操作：

• IFNULL: 处理空值。

  SELECT IFNULL(comm, 888) FROM emp;

• MD5/AES_ENCRYPT/AES_DECRYPT: 加密和解密。

SELECT MD5('123456');
SELECT AES_ENCRYPT('123456', 'abcd'), AES_DECRYPT(AES_ENCRYPT('123456', 'abcd'), 'abcd');

2. 多行函数

多行函数是对多行数据进行操作并返回一行数据的函数。常见的多行函数包括：

• MAX/MIN: 获取最大值或最小值。

• AVG: 计算平均值。

• SUM: 求和。

• COUNT: 统计总数。

示例：

• 查询每个部门的平均薪资：

  SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;

• 查询公司最高薪资的员工：

  SELECT ename, sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

3. 数据分组

数据分组是将数据按照某个条件进行分组，并对每组数据进行统计。常见的分组操作包括：

• GROUP BY: 对数据进行分组。

• HAVING: 对分组后的数据进行过滤。

示例：

• 查询每个部门的最高薪资和最低薪资：

  SELECT deptno, MAX(sal), MIN(sal) FROM emp GROUP BY deptno;

• 查询每个部门的人数和每月工资总数：

  SELECT deptno, COUNT(*), SUM(sal) FROM emp GROUP BY deptno;

• 查询每个部门，每种工作的平均薪资：

  SELECT deptno, job, AVG(sal) FROM emp GROUP BY deptno, job;

• 查询平均薪资高于2500的部门：

  SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2500;

4. DQL单表关键字执行顺序

在SQL查询中，关键字的执行顺序非常重要。常见的执行顺序如下：

1. FROM: 指定数据来源。

2. WHERE: 行级过滤。

3. GROUP BY: 数据分组。

4. SELECT: 选择显示的列。

5. HAVING: 组级过滤。

6. ORDER BY: 排序。

7. LIMIT: 限制返回的行数。

示例：

• 查询10和20部门中，在二月份入职的员工，每个部门中平均薪资高于1500的工作，并按照部门和平均薪资排序：

  SELECT deptno, job, AVG(sal) 
  FROM emp 
  WHERE MONTH(hiredate) = 2 AND deptno IN (10, 20) 
  GROUP BY deptno, job 
  HAVING AVG(sal) > 1500 
  ORDER BY deptno, AVG(sal);

5. 多表查询

多表查询是指从多张表中获取数据。常见的多表查询方式包括：

• 等值连接: 通过等值条件连接两张表。

• 自然连接: 自动选择列名相同且类型相同的列进行连接。

• USING: 指定连接列。

• ON: 指定连接条件。

示例：

• 查询每个员工所在的部门名称：

  SELECT e.ename, d.dname 
  FROM emp e 
  JOIN dept d ON e.deptno = d.deptno;

• 查询薪资大于2000的员工姓名和部门名称：

  SELECT e.ename, d.dname 
  FROM emp e 
  JOIN dept d ON e.deptno = d.deptno 
  WHERE e.sal > 2000;

6. 表与表的外连接

外连接用于在关联查询中显示未匹配的数据。常见的外连接包括：

• 左外连接: 显示左表所有数据。

• 右外连接: 显示右表所有数据。

示例：

• 统计每个部门的人数：

  SELECT d.dname, COUNT(e.empno) 
  FROM dept d 
  LEFT JOIN emp e ON d.deptno = e.deptno 
  GROUP BY d.dname;

7. 表与表的自连接

自连接是指将一张表视为两张表进行查询。常见的自连接操作包括：

• 查询每个员工与其直属领导的名字：

  SELECT e1.ename AS employee, e2.ename AS manager 
  FROM emp e1 
  LEFT JOIN emp e2 ON e1.mgr = e2.empno;

8. 表与表的子连接

子连接是指将一个查询结果作为另一个查询的条件。常见的子连接操作包括：

• 查询公司中谁的薪资高于平均薪资：

  SELECT ename, sal 
  FROM emp 
  WHERE sal > (SELECT AVG(sal) FROM emp);

• 查询谁的薪资高于20部门员工的薪资：

  SELECT ename, sal 
  FROM emp 
  WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 20);

9. 表与表的伪表查询

伪表查询是指将多个查询结果作为伪表进行管理。常见的伪表查询操作包括：

• 查询高于自己部门平均薪资的员工信息：

  SELECT e.ename, e.sal, e.deptno 
  FROM emp e 
  JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) AS dept_avg 
  ON e.deptno = dept_avg.deptno 
  WHERE e.sal > dept_avg.avg_sal;