mysql学习教程，从入门到精通，SQL常用函数（40）

1、SQL常用函数

SQL（Structured Query Language）是一种用于管理和操作关系数据库的编程语言。SQL提供了许多内置函数，这些函数可以对数据库中的数据进行各种计算和操作。以下是一些常用的SQL函数，按功能分类：

1.1、字符串函数

1. CONCAT()：连接两个或多个字符串。

   SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
   

2. SUBSTRING() 或 SUBSTR()：从字符串中提取子字符串。

   SELECT SUBSTRING('Hello World', 1, 5) AS SubstringResult;
   

3. LENGTH() 或 LEN()（在某些数据库中）：返回字符串的长度。

   SELECT LENGTH('Hello World') AS StringLength;
   

4. UPPER()：将字符串转换为大写。

   SELECT UPPER('hello') AS UppercaseString;
   

5. LOWER()：将字符串转换为小写。

   SELECT LOWER('HELLO') AS LowercaseString;
   

6. TRIM()：去除字符串两端的空格或指定字符。

   SELECT TRIM('   Hello World   ') AS TrimmedString;
   

7. REPLACE()：替换字符串中的子字符串。

   SELECT REPLACE('Hello World', 'World', 'SQL') AS ReplacedString;
   

1.2、数值函数

1. ABS()：返回数值的绝对值。

   SELECT ABS(-10) AS AbsoluteValue;
   

2. CEILING() 或 CEIL()：返回大于或等于指定数值的最小整数。

   SELECT CEILING(4.2) AS CeilingValue;
   

3. FLOOR()：返回小于或等于指定数值的最大整数。

   SELECT FLOOR(4.8) AS FloorValue;
   

4. ROUND()：将数值四舍五入到指定的小数位数。

   SELECT ROUND(4.567, 2) AS RoundedValue;
   

5. MOD() 或 %：返回两个数相除的余数。

   SELECT MOD(10, 3) AS Remainder;
   

6. POWER() 或 POW()：返回数值的指定幂。

   SELECT POWER(2, 3) AS PowerValue;
   

1.3、日期和时间函数

1. NOW()：返回当前的日期和时间。

   SELECT NOW() AS CurrentDateTime;
   

2. CURDATE()：返回当前的日期（不包含时间）。

   SELECT CURDATE() AS CurrentDate;
   

3. CURTIME()：返回当前的时间（不包含日期）。

   SELECT CURTIME() AS CurrentTime;
   

4. DATE_ADD()：向日期添加指定的时间间隔。

   SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY) AS NewDate;
   

5. DATEDIFF()：返回两个日期之间的天数差。

   SELECT DATEDIFF('2023-01-10', '2023-01-01') AS DaysDifference;
   

6. YEAR()、MONTH()、DAY()：从日期中提取年、月、日。

   SELECT YEAR('2023-01-01') AS Year, MONTH('2023-01-01') AS Month, DAY('2023-01-01') AS Day;
   

1.4、聚合函数

1. COUNT()：返回匹配的行数。

   SELECT COUNT(*) AS TotalRows FROM Employees;
   

2. SUM()：返回数值列的总和。

   SELECT SUM(Salary) AS TotalSalary FROM Employees;
   

3. AVG()：返回数值列的平均值。

   SELECT AVG(Salary) AS AverageSalary FROM Employees;
   

4. MAX()：返回数值列的最大值。

   SELECT MAX(Salary) AS MaxSalary FROM Employees;
   

5. MIN()：返回数值列的最小值。

   SELECT MIN(Salary) AS MinSalary FROM Employees;
   

1.5、条件函数

1. IF()：在SQL中实现简单的条件逻辑。

   SELECT IF(Salary > 5000, 'High', 'Low') AS SalaryLevel FROM Employees;
   

2. CASE：实现更复杂的条件逻辑。

   SELECT 
       CASE 
           WHEN Salary > 10000 THEN 'Very High'
           WHEN Salary > 5000 THEN 'High'
           ELSE 'Low'
       END AS SalaryLevel 
   FROM Employees;
   

这些函数只是SQL中可用函数的一小部分。不同的数据库管理系统（如MySQL、PostgreSQL、SQL Server、Oracle等）可能提供额外的函数和特性。因此，建议查阅特定数据库的文档以获取更详细的信息和示例。

以下是一些具体的SQL函数使用案例，这些案例将进一步展示SQL中不同函数的应用。

1.6、字符串函数案例

1. 查找子字符串位置

SELECT CHARINDEX('world', 'Hello world, SQL!') AS position;

这个查询将返回子字符串’world’在字符串’Hello world, SQL!'中的位置，结果应该是7（因为’w’是第七个字符）。

2. 字符串替换（带通配符）

假设我们有一个包含用户名的表users，其中有些用户名包含特定的数字序列，我们想要将这些数字序列替换为“NUMBER”。

UPDATE users
SET username = REPLACE(username, '%[0-9]%', 'NUMBER')
WHERE username LIKE '%[0-9]%';

注意：这个查询在实际SQL中可能不会直接工作，因为REPLACE函数通常不支持正则表达式。这个案例是为了说明字符串替换的概念，并可能需要使用其他函数或存储过程来实现带有通配符的替换。不过，在某些SQL方言（如PostgreSQL）中，可以通过正则表达式函数来实现类似的功能。

3. 字符串分割

虽然SQL标准没有直接的字符串分割函数，但我们可以使用其他函数和技巧来实现。例如，假设我们有一个包含逗号分隔值的字符串，我们想要将其分割成多个行。

WITH SplitString AS (
    SELECT 
        value = PARSENAME(REPLACE(STRING_AGG(SUBSTRING(t.csv, n, CHARINDEX(',', t.csv + ',', n) - n), ','), '.', ','), 1)
    FROM (
        SELECT 
            csv,
            n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
        FROM (
            SELECT csv = 'a,b,c,d' -- 假设这是我们的逗号分隔字符串
        ) t
        CROSS APPLY (
            SELECT TOP (LEN(t.csv) - LEN(REPLACE(t.csv, ',', '')) + 1) 
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
            FROM sys.objects s1 -- 使用系统表来生成一个数字序列
        ) n
        WHERE n <= LEN(t.csv) - LEN(REPLACE(t.csv, ',', ''))
    ) t
    CROSS APPLY (
        SELECT value = SUBSTRING(
            ',' + t.csv, 
            n + 1, 
            CHARINDEX(',', ',' + t.csv, n + 1) - n - 1
        )
    ) v
    WHERE value <> ''
)
SELECT * FROM SplitString;

这个查询使用了STRING_AGG（在某些SQL版本中可能需要替换为其他字符串聚合函数）、SUBSTRING、CHARINDEX、ROW_NUMBER等函数和技巧来实现字符串分割。请注意，这个查询是为了演示目的而编写的，并且可能需要根据您的SQL版本和具体需求进行调整。

1.7、日期函数案例

1. 计算两个日期之间的天数

SELECT DATEDIFF(day, '2023-01-01', '2023-12-31') AS days_difference;

这个查询将计算两个日期之间的天数差，结果应该是364（或365，取决于是否是闰年）。

2. 将日期转换为特定格式

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS formatted_date;

这个查询将当前日期和时间转换为’yyyy-MM-dd HH:mm:ss’格式的字符串。请注意，FORMAT函数在某些SQL版本中可能不可用，您可能需要使用CONVERT或其他日期格式化函数。

3. 计算日期的季度

虽然SQL标准没有直接的季度计算函数，但我们可以使用其他函数和技巧来实现。例如，在SQL Server中，我们可以使用DATEPART函数：

SELECT DATEPART(quarter, GETDATE()) AS current_quarter;

这个查询将返回当前日期的季度（1到4）。

1.8、聚合函数案例

1. 计算每个部门的平均工资

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

这个查询将按部门对员工进行分组，并计算每个部门的平均工资。

2. 查找薪资最高的员工

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

这个查询将返回薪资最高的员工的ID、名字、姓氏和薪资。

3. 计算每个部门的员工数量，并按数量降序排列

SELECT department_id, COUNT(*) AS number_of_employees
FROM employees
GROUP BY department_id
ORDER BY number_of_employees DESC;

这个查询将按部门对员工进行分组，计算每个部门的员工数量，并按数量降序排列结果。
这些案例进一步展示了SQL中不同函数的应用，并提供了更复杂的查询示例。请注意，根据您的SQL版本和具体需求，这些查询可能需要进行适当的调整。