sql常用的函数与代码示例
1、字符串函数
CONCAT
SELECT CONCAT('Hello', ' ', 'World') AS greeting;
UPPER 和 LOWER
SELECT UPPER('hello') AS upper_text, LOWER('HELLO') AS lower_text;
TRIM
SELECT TRIM(' Hello World ') AS trimmed_text;
LENGTH 或 CHAR_LENGTH (在某些数据库中)
SELECT LENGTH('Hello') AS string_length;
SUBSTRING 或 SUBSTR (在某些数据库中)
SELECT SUBSTRING('Hello World', 1, 5) AS substring_result;
2、数值函数
ROUND
SELECT ROUND(123.4567, 2) AS rounded_number;
CEIL 或 CEILING
SELECT CEIL(123.45) AS ceiling_number;
FLOOR
SELECT FLOOR(123.45) AS floor_number;
ABS
SELECT ABS(-123) AS absolute_value;
3、日期和时间函数
NOW 或 CURRENT_TIMESTAMP
SELECT NOW() AS current_datetime;
CURDATE
SELECT CURDATE() AS current_date;
DATE_ADD (在MySQL中)
SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS next_date;
DATEDIFF
SELECT DATEDIFF('2023-10-23', '2023-10-01') AS days_difference;
DAY, MONTH, YEAR
SELECT DAY('2023-10-23') AS day_part, MONTH('2023-10-23') AS month_part, YEAR('2023-10-23') AS year_part;
4、聚合函数
AVG
SELECT AVG(salary) AS average_salary FROM employees;
COUNT
SELECT COUNT(*) AS total_employees FROM employees;
MAX 和 MIN
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees;
SUM
SELECT SUM(salary) AS total_salary FROM employees;
5、转换函数
CAST
SELECT CAST(123 AS VARCHAR(10)) AS number_as_string;
CONVERT (在某些数据库中与CAST类似)
SELECT CONVERT(VARCHAR(10), 123) AS number_as_string;
6、条件函数
IF (在MySQL中)
SELECT IF(salary > 50000, 'High Salary', 'Low Salary') AS salary_level FROM employees;
CASE
SELECT CASE WHEN salary > 50000 THEN 'High Salary'WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium Salary'ELSE 'Low Salary'END AS salary_level
FROM employees;