MySQL 基础查询

SELECT 查询

基本查询

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

-- 别名
SELECT username AS name, email AS mail FROM users;

-- 去重
SELECT DISTINCT age FROM users;

-- 常量列
SELECT username, 'VIP' AS level FROM users;

WHERE 条件查询

-- 比较运算符
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age >= 18 AND age <= 30;
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- 逻辑运算符
SELECT * FROM users WHERE age > 18 AND status = 1;
SELECT * FROM users WHERE age < 18 OR age > 60;
SELECT * FROM users WHERE NOT age = 25;

-- IN 操作符
SELECT * FROM users WHERE age IN (18, 20, 22);
SELECT * FROM users WHERE username IN ('user1', 'user2');

-- LIKE 模糊查询
SELECT * FROM users WHERE username LIKE 'user%';  -- user开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- gmail结尾
SELECT * FROM users WHERE username LIKE '%admin%';  -- 包含admin
SELECT * FROM users WHERE username LIKE 'user_';  -- user后跟一个字符

-- NULL 判断
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

ORDER BY 排序

-- 升序（默认）
SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age ASC;

-- 降序
SELECT * FROM users ORDER BY age DESC;

-- 多字段排序
SELECT * FROM users ORDER BY age DESC, created_at ASC;

LIMIT 分页

-- 获取前10条
SELECT * FROM users LIMIT 10;

-- 跳过前10条，获取10条（第11-20条）
SELECT * FROM users LIMIT 10, 10;
SELECT * FROM users LIMIT 10 OFFSET 10;

-- 分页公式：LIMIT (page-1)*size, size
-- 第1页：LIMIT 0, 10
-- 第2页：LIMIT 10, 10
-- 第3页：LIMIT 20, 10

聚合函数

常用聚合函数

-- COUNT：计数
SELECT COUNT(*) FROM users;
SELECT COUNT(id) FROM users;
SELECT COUNT(DISTINCT age) FROM users;  -- 去重计数

-- SUM：求和
SELECT SUM(age) FROM users;
SELECT SUM(balance) FROM accounts;

-- AVG：平均值
SELECT AVG(age) FROM users;

-- MAX/MIN：最大值/最小值
SELECT MAX(age) FROM users;
SELECT MIN(created_at) FROM users;

GROUP BY 分组

-- 按年龄分组统计人数
SELECT age, COUNT(*) as count FROM users GROUP BY age;

-- 多字段分组
SELECT age, status, COUNT(*) FROM users GROUP BY age, status;

-- HAVING 分组后筛选
SELECT age, COUNT(*) as count 
FROM users 
GROUP BY age 
HAVING count > 5;

-- WHERE vs HAVING
-- WHERE：分组前筛选
-- HAVING：分组后筛选
SELECT age, COUNT(*) as count 
FROM users 
WHERE status = 1 
GROUP BY age 
HAVING count > 5;

多表查询

内连接（INNER JOIN）

-- 查询用户及其部门信息
SELECT u.username, d.dept_name 
FROM users u
INNER JOIN dept d ON u.dept_id = d.id;

-- 可以省略INNER
SELECT u.username, d.dept_name 
FROM users u
JOIN dept d ON u.dept_id = d.id;

左连接（LEFT JOIN）

-- 查询所有用户，包括没有部门的用户
SELECT u.username, d.dept_name 
FROM users u
LEFT JOIN dept d ON u.dept_id = d.id;

右连接（RIGHT JOIN）

-- 查询所有部门，包括没有用户的部门
SELECT u.username, d.dept_name 
FROM users u
RIGHT JOIN dept d ON u.dept_id = d.id;

自连接

-- 查询员工及其上级
SELECT e1.name AS employee, e2.name AS manager
FROM employee e1
LEFT JOIN employee e2 ON e1.manager_id = e2.id;

子查询

-- WHERE子查询
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

-- FROM子查询
SELECT * FROM (
    SELECT username, age FROM users WHERE age > 20
) AS temp WHERE temp.age < 30;

-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

联合查询

-- UNION：合并结果并去重
SELECT username FROM users WHERE age < 20
UNION
SELECT username FROM users WHERE age > 60;

-- UNION ALL：合并结果不去重
SELECT username FROM users WHERE age < 20
UNION ALL
SELECT username FROM users WHERE age > 60;

常用函数

字符串函数

-- CONCAT：连接字符串
SELECT CONCAT(username, '@', email) FROM users;

-- LENGTH：字符串长度
SELECT username, LENGTH(username) FROM users;

-- UPPER/LOWER：大小写转换
SELECT UPPER(username), LOWER(username) FROM users;

-- SUBSTRING：截取字符串
SELECT SUBSTRING(username, 1, 3) FROM users;

-- TRIM：去除空格
SELECT TRIM(username) FROM users;

-- REPLACE：替换
SELECT REPLACE(email, 'gmail.com', 'example.com') FROM users;

数值函数

-- ROUND：四舍五入
SELECT ROUND(price, 2) FROM products;

-- CEIL：向上取整
SELECT CEIL(price) FROM products;

-- FLOOR：向下取整
SELECT FLOOR(price) FROM products;

-- ABS：绝对值
SELECT ABS(balance) FROM accounts;

-- MOD：取模
SELECT MOD(age, 2) FROM users;

日期函数

-- NOW：当前日期时间
SELECT NOW();

-- CURDATE：当前日期
SELECT CURDATE();

-- CURTIME：当前时间
SELECT CURTIME();

-- DATE_FORMAT：格式化日期
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') FROM users;

-- YEAR/MONTH/DAY：提取年月日
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at) FROM users;

-- DATE_ADD：日期加法
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);

-- DATEDIFF：日期差
SELECT DATEDIFF(NOW(), created_at) as days FROM users;

控制流函数

-- IF
SELECT username, IF(age >= 18, '成年', '未成年') as type FROM users;

-- CASE WHEN
SELECT username,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age < 60 THEN '成年'
        ELSE '老年'
    END as age_group
FROM users;

-- IFNULL：空值处理
SELECT username, IFNULL(email, '未填写') FROM users;

-- COALESCE：返回第一个非空值
SELECT COALESCE(phone, email, '无联系方式') FROM users;

查询优化技巧

1. 只查询需要的列

-- 不推荐
SELECT * FROM users;

-- 推荐
SELECT id, username, email FROM users;

2. 使用索引

-- 在WHERE、ORDER BY、JOIN的字段上建立索引
CREATE INDEX idx_age ON users(age);

3. 避免在WHERE中使用函数

-- 不推荐（无法使用索引）
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 推荐
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';

4. 使用LIMIT

-- 限制结果集大小
SELECT * FROM users ORDER BY id LIMIT 1000;

💡 提示

这是一个demo文档，欢迎补充更多查询相关内容。