MySQL常用语句与函数大全及举例
在日常的开发与数据库维护中，熟练掌握MySQL的常用语句和函数能够极大提高工作效率。本文将系统性地梳理MySQL中最常用的DDL、DML语句以及各类核心函数，并提供实用的代码示例。
一、数据库与数据表操作（DDL）
DDL（Data Definition Language）主要用于定义或改变表的结构、数据类型等。
-- 创建数据库并指定字符集 CREATE DATABASE my_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE my_db; -- 创建数据表 CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL COMMENT '用户名', email VARCHAR(100) DEFAULT NULL COMMENT '邮箱', age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄', balance DECIMAL(10,2) DEFAULT 0.00 COMMENT '余额', created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 修改表：添加列 ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email; -- 修改表：修改列类型 ALTER TABLE users MODIFY COLUMN age SMALLINT UNSIGNED; -- 删除表 DROP TABLE IF EXISTS temp_table;
二、数据操作（DML）
DML（Data Manipulation Language）主要用于对表中的数据进行增删改查。
-- 插入数据
INSERT INTO users (username, email, age, balance)
VALUES ('zhangsan', 'zhangsan@ipipp.com', 25, 1000.50);
-- 查询数据（条件、排序、分页）
SELECT id, username, balance FROM users
WHERE age > 20
ORDER BY balance DESC
LIMIT 10 OFFSET 0;
-- 更新数据
UPDATE users SET balance = balance + 500 WHERE username = 'zhangsan';
-- 删除数据
DELETE FROM users WHERE id = 1;三、常用聚合函数
聚合函数通常与 GROUP BY 语句一起使用，用于计算汇总值。
-- COUNT: 统计行数 SELECT COUNT(*) AS total_users FROM users; -- SUM: 求和 SELECT SUM(balance) AS total_balance FROM users; -- AVG: 求平均值 SELECT AVG(age) AS avg_age FROM users; -- MAX / MIN: 求最大值 / 最小值 SELECT MAX(balance), MIN(balance) FROM users; -- 结合 GROUP BY 使用 SELECT age, COUNT(*) AS user_count, AVG(balance) AS avg_balance FROM users GROUP BY age HAVING user_count > 1;
四、常用字符串函数
字符串函数用于处理文本类型的数据，如拼接、截取、替换等。
-- CONCAT: 字符串拼接 SELECT CONCAT(username, '-', email) AS user_info FROM users; -- LENGTH: 获取字符串长度 SELECT username, LENGTH(username) AS name_len FROM users; -- SUBSTRING: 截取字符串 (从第1个字符开始，截取3个) SELECT SUBSTRING(username, 1, 3) AS short_name FROM users; -- REPLACE: 替换字符串 SELECT REPLACE(email, 'ipipp.com', 'demo.ipipp.com') AS new_email FROM users; -- TRIM: 去除首尾空格 SELECT TRIM(username) FROM users;
五、常用数值函数
数值函数主要用于数学计算。
-- ROUND: 四舍五入保留小数 SELECT ROUND(3.1415, 2); -- 结果: 3.14 -- CEIL: 向上取整 SELECT CEIL(3.14); -- 结果: 4 -- FLOOR: 向下取整 SELECT FLOOR(3.14); -- 结果: 3 -- ABS: 取绝对值 SELECT ABS(-10); -- 结果: 10
六、常用日期和时间函数
日期函数在处理时间维度的数据统计时尤为重要。
-- NOW / CURDATE / CURTIME: 获取当前日期时间
SELECT NOW(), CURDATE(), CURTIME();
-- DATE_FORMAT: 格式化日期
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS format_time FROM users;
-- DATEDIFF: 计算两个日期相差的天数
SELECT DATEDIFF('2023-12-31', '2023-01-01') AS days_diff;
-- DATE_ADD / DATE_SUB: 日期加减
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH) AS last_month;七、流程控制函数
流程控制函数可以在SQL语句中实现类似编程语言中的条件判断逻辑。
-- IF: 条件判断 SELECT username, IF(age >= 18, '成年', '未成年') AS is_adult FROM users; -- IFNULL: 判空替换（如果balance为NULL，则返回0） SELECT username, IFNULL(balance, 0) AS current_balance FROM users; -- CASE WHEN: 多条件分支判断 SELECT username, CASE WHEN balance > 5000 THEN 'VIP客户' WHEN balance > 1000 THEN '普通客户' ELSE '潜在客户' END AS customer_level FROM users;
掌握上述MySQL常用语句与函数，能够应对绝大多数的数据库操作与数据处理场景。在实际应用中，建议结合索引优化和查询执行计划（EXPLAIN）来进一步提升SQL的执行效率。