数据库基础

数据库是现代应用的核心。本文整理数据库常用基础函数与查询语句，涵盖字符串处理、数值运算、日期操作及聚合函数。

---

一、字符串函数

常用字符串函数

函数	说明	示例
CONCAT(s1, s2, ...)	字符串拼接	CONCAT('Hello', ' World') → Hello World
LENGTH(str)	返回字符串长度（字节）	LENGTH('中文') → 6
CHAR_LENGTH(str)	返回字符数	CHAR_LENGTH('中文') → 2
UPPER(str) / LOWER(str)	大小写转换	UPPER('hello') → HELLO
TRIM(str)	去除两端空格	TRIM(' abc ') → abc
SUBSTRING(str, pos, len)	截取子串	SUBSTRING('Hello', 1, 2) → He
REPLACE(str, old, new)	替换字符串	REPLACE('abc', 'b', 'x') → axc
LEFT(str, len)	左侧截取	LEFT('Hello', 2) → He
RIGHT(str, len)	右侧截取	RIGHT('Hello', 2) → lo

使用示例

-- 拼接姓名
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- 获取邮箱域名
SELECT SUBSTRING(email, INSTR(email, '@') + 1) AS domain FROM users;

-- 清理输入
SELECT TRIM(input_text) FROM form_data;

---

二、数值函数

函数	说明	示例
ABS(x)	绝对值	ABS(-5) → 5
CEIL(x)	向上取整	CEIL(3.14) → 4
FLOOR(x)	向下取整	FLOOR(3.14) → 3
ROUND(x, d)	四舍五入	ROUND(3.1415, 2) → 3.14
MOD(x, y)	取模	MOD(10, 3) → 1
RAND()	0~1 随机数	RAND() → 0.724...
POW(x, y)	x 的 y 次方	POW(2, 3) → 8
SQRT(x)	平方根	SQRT(9) → 3

-- 计算圆面积
SELECT ROUND(PI() * POW(radius, 2), 2) AS area FROM circles;

-- 随机抽取样本
SELECT * FROM products ORDER BY RAND() LIMIT 10;

---

三、日期函数

函数	说明	示例
NOW()	当前日期时间	2023-12-15 14:30:00
CURDATE()	当前日期	2023-12-15
DATE_FORMAT(d, fmt)	格式化日期	DATE_FORMAT(NOW(), '%Y-%m-%d')
DATEDIFF(d1, d2)	日期差（天数）	DATEDIFF('2023-12-31', '2023-12-01') → 30
DATE_ADD(d, INTERVAL)	日期加	DATE_ADD(NOW(), INTERVAL 7 DAY)
YEAR(d) / MONTH(d) / DAY(d)	提取年月日	YEAR(NOW()) → 2023
UNIX_TIMESTAMP(d)	转 Unix 时间戳	秒数

-- 计算用户注册天数
SELECT username, DATEDIFF(NOW(), created_at) AS days_since_register 
FROM users;

-- 按月份统计订单
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS cnt
FROM orders
GROUP BY month;

-- 查询最近 7 天的记录
SELECT * FROM logs 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

---

四、聚合函数

函数	说明
COUNT(*)	统计行数
SUM(col)	求和
AVG(col)	平均值
MAX(col)	最大值
MIN(col)	最小值
GROUP_CONCAT(col)	将分组值拼接为字符串

示例

-- 各部门人数与平均薪资
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees
GROUP BY department
HAVING employee_count > 5;

-- 拼接每个部门成员
SELECT 
    department,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees
GROUP BY department;

---

五、条件与流程函数

函数	说明
IF(cond, val1, val2)	条件判断
IFNULL(val, default)	NULL 值替换
CASE WHEN ... THEN ... END	多条件判断

-- 成绩等级评定
SELECT 
    name, score,
    CASE 
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM students;

-- NULL 值处理
SELECT name, IFNULL(phone, '未填写') AS phone FROM users;

---

六、综合练习

表结构

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    gender ENUM('男', '女'),
    score DECIMAL(5,2),
    class VARCHAR(20),
    created_at DATETIME DEFAULT NOW()
);

练习查询

-- 1. 各班级平均分
SELECT class, AVG(score) AS avg_score FROM students GROUP BY class;

-- 2. 各分数段人数
SELECT 
    CASE 
        WHEN score >= 90 THEN '90-100'
        WHEN score >= 80 THEN '80-89'
        WHEN score >= 70 THEN '70-79'
        WHEN score >= 60 THEN '60-69'
        ELSE '不及格'
    END AS score_range,
    COUNT(*) AS count
FROM students
GROUP BY score_range;

-- 3. 男女生最高分
SELECT gender, MAX(score) AS top_score FROM students GROUP BY gender;

---