MySQL基础查询 #

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, age, email, status) VALUES
    ('John', 25, 'john@example.com', 1),
    ('Jane', 30, 'jane@example.com', 1),
    ('Bob', 28, 'bob@example.com', 0),
    ('Alice', 22, 'alice@example.com', 1),
    ('Charlie', 35, 'charlie@example.com', 0);

SELECT * FROM users;

+----+---------+------+--------------------+--------+---------------------+
| id | name    | age  | email              | status | created_at          |
+----+---------+------+--------------------+--------+---------------------+
|  1 | John    |   25 | john@example.com   |      1 | 2024-01-15 10:00:00 |
|  2 | Jane    |   30 | jane@example.com   |      1 | 2024-01-15 11:00:00 |
|  3 | Bob     |   28 | bob@example.com    |      0 | 2024-01-15 12:00:00 |
|  4 | Alice   |   22 | alice@example.com  |      1 | 2024-01-15 13:00:00 |
|  5 | Charlie |   35 | charlie@example.com|      0 | 2024-01-15 14:00:00 |
+----+---------+------+--------------------+--------+---------------------+

SELECT name, age, email FROM users;

+---------+------+--------------------+
| name    | age  | email              |
+---------+------+--------------------+
| John    |   25 | john@example.com   |
| Jane    |   30 | jane@example.com   |
| Bob     |   28 | bob@example.com    |
| Alice   |   22 | alice@example.com  |
| Charlie |   35 | charlie@example.com|
+---------+------+--------------------+

-- 使用AS关键字
SELECT 
    name AS user_name,
    age AS user_age,
    email AS user_email
FROM users;

-- 省略AS关键字
SELECT name user_name, age user_age FROM users;

-- 使用引号（包含空格或特殊字符）
SELECT name AS '用户名', age AS '年龄' FROM users;

SELECT u.name, u.age, u.email
FROM users AS u;

-- 省略AS
SELECT u.name, u.age FROM users u;

-- 查询所有年龄（包含重复）
SELECT age FROM users;

-- 查询不重复的年龄
SELECT DISTINCT age FROM users;

+------+
| age  |
+------+
|   25 |
|   30 |
|   28 |
|   22 |
|   35 |
+------+

-- 多列组合去重
SELECT DISTINCT age, status FROM users;

+------+--------+
| age  | status |
+------+--------+
|   25 |      1 |
|   30 |      1 |
|   28 |      0 |
|   22 |      1 |
|   35 |      0 |
+------+--------+

-- 统计不同年龄的数量
SELECT COUNT(DISTINCT age) FROM users;

-- 统计不同年龄和状态组合的数量
SELECT COUNT(DISTINCT age, status) FROM users;

-- 等于
SELECT * FROM users WHERE age = 25;

-- 不等于
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age <> 25;

-- 大于
SELECT * FROM users WHERE age > 25;

-- 小于
SELECT * FROM users WHERE age < 25;

-- 大于等于
SELECT * FROM users WHERE age >= 25;

-- 小于等于
SELECT * FROM users WHERE age <= 25;

-- AND：同时满足多个条件
SELECT * FROM users WHERE age > 25 AND status = 1;

-- OR：满足任一条件
SELECT * FROM users WHERE age < 25 OR age > 30;

-- NOT：取反
SELECT * FROM users WHERE NOT status = 0;

-- 组合使用（注意优先级）
SELECT * FROM users 
WHERE (age < 25 OR age > 30) AND status = 1;

-- 范围查询（包含边界）
SELECT * FROM users WHERE age BETWEEN 25 AND 30;

-- 等价于
SELECT * FROM users WHERE age >= 25 AND age <= 30;

-- NOT BETWEEN
SELECT * FROM users WHERE age NOT BETWEEN 25 AND 30;

-- 列表匹配
SELECT * FROM users WHERE age IN (25, 30, 35);

-- 等价于
SELECT * FROM users WHERE age = 25 OR age = 30 OR age = 35;

-- NOT IN
SELECT * FROM users WHERE age NOT IN (25, 30, 35);

-- 子查询
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders WHERE status = 'completed'
);

-- % 匹配任意多个字符
SELECT * FROM users WHERE name LIKE 'J%';    -- 以J开头
SELECT * FROM users WHERE name LIKE '%n';    -- 以n结尾
SELECT * FROM users WHERE name LIKE '%oh%';  -- 包含oh

-- _ 匹配单个字符
SELECT * FROM users WHERE name LIKE 'J_hn';  -- John

-- 组合使用
SELECT * FROM users WHERE email LIKE '%@%.com';

-- NOT LIKE
SELECT * FROM users WHERE name NOT LIKE 'J%';

-- IS NULL
SELECT * FROM users WHERE email IS NULL;

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

-- 错误写法
SELECT * FROM users WHERE email = NULL;    -- 不会返回结果
SELECT * FROM users WHERE email != NULL;   -- 不会返回结果

-- <=> 安全等于（可以比较NULL）
SELECT * FROM users WHERE email <=> NULL;

-- 等价于
SELECT * FROM users WHERE email IS NULL;

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

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

-- 先按status排序，再按age排序
SELECT * FROM users ORDER BY status ASC, age DESC;

+----+---------+------+--------------------+--------+
| id | name    | age  | email              | status |
+----+---------+------+--------------------+--------+
|  2 | Jane    |   30 | jane@example.com   |      1 |
|  1 | John    |   25 | john@example.com   |      1 |
|  4 | Alice   |   22 | alice@example.com  |      1 |
|  5 | Charlie |   35 | charlie@example.com|      0 |
|  3 | Bob     |   28 | bob@example.com    |      0 |
+----+---------+------+--------------------+--------+

-- 按计算结果排序
SELECT name, age, age * 2 AS double_age 
FROM users 
ORDER BY double_age;

-- 按函数结果排序
SELECT * FROM users ORDER BY LENGTH(name);

-- 按列位置排序
SELECT name, age FROM users ORDER BY 2;  -- 按第二列（age）排序

-- 使用FIELD函数自定义排序
SELECT * FROM users 
ORDER BY FIELD(status, 1, 0);

-- 使用CASE WHEN
SELECT * FROM users 
ORDER BY CASE status
    WHEN 1 THEN 1
    WHEN 0 THEN 2
    ELSE 3
END;

-- 返回前3条
SELECT * FROM users LIMIT 3;

+----+------+------+------------------+--------+
| id | name | age  | email            | status |
+----+------+------+------------------+--------+
|  1 | John |   25 | john@example.com |      1 |
|  2 | Jane |   30 | jane@example.com |      1 |
|  3 | Bob  |   28 | bob@example.com  |      0 |
+----+------+------+------------------+--------+

-- LIMIT offset, count
-- offset：偏移量（从0开始）
-- count：返回行数

-- 第1页（每页3条）
SELECT * FROM users LIMIT 0, 3;

-- 第2页
SELECT * FROM users LIMIT 3, 3;

-- 第3页
SELECT * FROM users LIMIT 6, 3;

-- 推荐写法（MySQL 8.0+）
SELECT * FROM users LIMIT 3 OFFSET 0;
SELECT * FROM users LIMIT 3 OFFSET 3;

-- 第n页，每页m条
-- offset = (n - 1) * m

-- 例如：第3页，每页10条
SELECT * FROM users LIMIT 20, 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 通常配合使用
SELECT * FROM users 
ORDER BY created_at DESC 
LIMIT 10;

-- 获取最年长的3人
SELECT * FROM users 
ORDER BY age DESC 
LIMIT 3;

-- 统计行数
SELECT COUNT(*) FROM users;

-- 统计非NULL值数量
SELECT COUNT(email) FROM users;

-- 统计不重复值的数量
SELECT COUNT(DISTINCT age) FROM users;

-- 求和
SELECT SUM(age) FROM users;

-- 条件求和
SELECT SUM(CASE WHEN status = 1 THEN age ELSE 0 END) FROM users;

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

-- 保留小数位
SELECT ROUND(AVG(age), 2) FROM users;

-- 最大值
SELECT MAX(age) FROM users;

-- 最小值
SELECT MIN(age) FROM users;

-- 同时获取
SELECT MAX(age) AS max_age, MIN(age) AS min_age FROM users;

SELECT 
    COUNT(*) AS total,
    SUM(age) AS total_age,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM users;

-- 按状态分组统计
SELECT status, COUNT(*) AS count
FROM users
GROUP BY status;

+--------+-------+
| status | count |
+--------+-------+
|      0 |     2 |
|      1 |     3 |
+--------+-------+

-- 按多列分组
SELECT status, age, COUNT(*) AS count
FROM users
GROUP BY status, age;

SELECT 
    status,
    COUNT(*) AS count,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM users
GROUP BY status;

-- 将分组内的值连接成字符串
SELECT 
    status,
    GROUP_CONCAT(name) AS names,
    GROUP_CONCAT(name SEPARATOR ', ') AS names_with_separator
FROM users
GROUP BY status;

+--------+-------------------+-------------------------+
| status | names             | names_with_separator    |
+--------+-------------------+-------------------------+
|      0 | Bob,Charlie       | Bob, Charlie            |
|      1 | John,Jane,Alice   | John, Jane, Alice       |
+--------+-------------------+-------------------------+

-- WHERE：过滤行，在分组前执行
SELECT status, COUNT(*) AS count
FROM users
WHERE age > 25
GROUP BY status;

-- HAVING：过滤分组，在分组后执行
SELECT status, COUNT(*) AS count
FROM users
GROUP BY status
HAVING COUNT(*) > 1;

-- 筛选平均年龄大于25的组
SELECT status, AVG(age) AS avg_age
FROM users
GROUP BY status
HAVING AVG(age) > 25;

-- 筛选人数大于2的组
SELECT status, COUNT(*) AS count
FROM users
GROUP BY status
HAVING COUNT(*) > 2;

SELECT 
    status,
    COUNT(*) AS count,
    AVG(age) AS avg_age
FROM users
WHERE age >= 20
GROUP BY status
HAVING count > 1 AND avg_age > 25
ORDER BY avg_age DESC;

SELECT DISTINCT column_list
FROM table_name
JOIN another_table ON condition
WHERE row_condition
GROUP BY column_list
HAVING group_condition
ORDER BY column_list
LIMIT offset, count;

1. FROM        → 确定数据来源
2. JOIN        → 连接表
3. ON          → 连接条件
4. WHERE       → 过滤行
5. GROUP BY    → 分组
6. HAVING      → 过滤分组
7. SELECT      → 选择列
8. DISTINCT    → 去重
9. ORDER BY    → 排序
10. LIMIT      → 限制行数

SELECT status, COUNT(*) AS count, AVG(age) AS avg_age
FROM users
WHERE age >= 20
GROUP BY status
HAVING count > 1
ORDER BY avg_age DESC
LIMIT 2;

-- 执行顺序：
-- 1. FROM users          → 获取users表数据
-- 2. WHERE age >= 20     → 过滤年龄>=20的行
-- 3. GROUP BY status     → 按status分组
-- 4. HAVING count > 1    → 过滤分组
-- 5. SELECT ...          → 选择列并计算聚合
-- 6. ORDER BY avg_age    → 排序
-- 7. LIMIT 2             → 限制返回2条