🐬 MySQL 常用查询语句 ​

-- 查看所有数据库
SHOW DATABASES;

-- 使用数据库
USE db_name;

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESCRIBE table_name;

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

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

-- 给结果列起别名
SELECT name AS username, email AS user_email FROM users;

-- 去重
SELECT DISTINCT city FROM users;

-- WHERE 子句
SELECT * FROM users WHERE age > 18;

-- 多条件 AND / OR
SELECT * FROM users WHERE age > 18 AND gender = 'M';

-- IN (多选匹配)
SELECT * FROM users WHERE city IN ('北京', '上海', '广州');

-- BETWEEN (范围查询)
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- 模糊查询 LIKE
SELECT * FROM users WHERE name LIKE 'A%';   -- 以 A 开头
SELECT * FROM users WHERE name LIKE '%son'; -- 以 son 结尾
SELECT * FROM users WHERE name LIKE '%an%'; -- 包含 an

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

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

-- LIMIT 限制结果
SELECT * FROM users LIMIT 10;           -- 前 10 条
SELECT * FROM users LIMIT 5, 10;        -- 从第 6 条开始取 10 条

-- COUNT / SUM / AVG / MAX / MIN
SELECT COUNT(*) FROM users;                  -- 总数
SELECT AVG(age) FROM users;                  -- 平均年龄
SELECT MAX(salary), MIN(salary) FROM users;  -- 最大最小

-- GROUP BY 分组
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

-- HAVING 分组后条件过滤
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING user_count > 5;

-- INNER JOIN 内连接
SELECT u.id, u.name, o.order_no
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN 左连接（保留左表所有数据）
SELECT u.id, u.name, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN 右连接（保留右表所有数据）
SELECT u.id, u.name, o.order_no
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 子查询作为条件
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- 子查询作为临时表
SELECT t.user_id, t.total_amount
FROM (
  SELECT user_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
) t
WHERE t.total_amount > 1000;

-- 插入数据
INSERT INTO users (name, age, email)
VALUES ('Tom', 25, 'tom@example.com');

-- 更新数据
UPDATE users
SET email = 'new@example.com'
WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

-- 去重计数
SELECT COUNT(DISTINCT city) FROM users;

-- CASE 条件判断
SELECT name,
  CASE
    WHEN age < 18 THEN '未成年'
    WHEN age BETWEEN 18 AND 59 THEN '成年人'
    ELSE '老年人'
  END AS age_group
FROM users;