MySQL高级查询技巧：分组、聚合、子查询与分页【MySQL系列】

SELECT [字段列表]
FROM [表名]
WHERE [条件]
GROUP BY [分组字段]
HAVING [聚合条件]
ORDER BY [排序字段]
LIMIT [偏移量, 行数]

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

SELECT department_id, job_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id, job_id;

SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

SELECT department_id, GROUP_CONCAT(first_name) AS employee_names
FROM employees
GROUP BY department_id;

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

HAVING COUNT(*) > 5 -- 推荐
-- HAVING employee_count > 5 -- 有些版本不支持

SELECT employee_id, salary,
       salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

SELECT department_id, avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg
WHERE avg_salary > 10000;

SELECT department_id, department_name
FROM departments
WHERE department_id IN (
    SELECT DISTINCT department_id FROM employees
);

SELECT * FROM employees
LIMIT 10; -- 取前10条

SELECT * FROM employees
LIMIT 10 OFFSET 20; -- 从第21条开始，取10条

SELECT * FROM employees
LIMIT 20, 10;

-- page = 3, pageSize = 10
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 20, 10;

SELECT department_id, 
       DATE_FORMAT(hire_date, '%Y-%m') AS month,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department_id, month
ORDER BY department_id, month;

SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

SELECT *
FROM employees e
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = e.department_id
);

-- 仅查询主键或索引字段
SELECT employee_id
FROM employees
ORDER BY hire_date DESC
LIMIT 100000, 10;

-- 第一步：查主键
SELECT employee_id
FROM employees
ORDER BY hire_date DESC
LIMIT 100000, 10;

-- 第二步：再查详情
SELECT * 
FROM employees
WHERE employee_id IN (…);

-- 假设上次最后一条ID是 120
SELECT * FROM employees
WHERE employee_id > 120
ORDER BY employee_id
LIMIT 10;

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(100) NOT NULL,
  email VARCHAR(100),
  status TINYINT DEFAULT 1 COMMENT '0:禁用, 1:启用',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE roles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255)
);

CREATE TABLE permissions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  code VARCHAR(50) NOT NULL UNIQUE COMMENT '用于权限标识，如 user:view'
);

CREATE TABLE user_role (
  user_id INT,
  role_id INT,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE role_permission (
  role_id INT,
  permission_id INT,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

CREATE TABLE login_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  ip_address VARCHAR(45),
  login_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO roles(name, description) VALUES ('admin', '系统管理员'), ('user', '普通用户');

INSERT INTO permissions(name, code) VALUES
('查看用户', 'user:view'),
('新增用户', 'user:create'),
('删除用户', 'user:delete');

-- 分配权限给角色
INSERT INTO role_permission(role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3), -- admin 拥有全部权限
(2, 1);                 -- user 仅能查看用户

INSERT INTO users(username, password, email) VALUES
('alice', 'hashed_pwd1', 'alice@example.com'),
('bob', 'hashed_pwd2', 'bob@example.com');

-- 分配角色
INSERT INTO user_role(user_id, role_id) VALUES
(1, 1), -- alice 为管理员
(2, 2); -- bob 为普通用户

SELECT u.id, u.username, r.name AS role
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.status = 1;

SELECT p.name, p.code
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN role_permission rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.username = 'alice';

SELECT u.username, l.ip_address, l.login_time
FROM login_logs l
JOIN users u ON l.user_id = u.id
WHERE l.login_time >= NOW() - INTERVAL 7 DAY
ORDER BY l.login_time DESC;

SELECT *
FROM users
WHERE username LIKE '%bob%'
ORDER BY created_at DESC
LIMIT 0, 10;

START TRANSACTION;

INSERT INTO users(username, password, email) VALUES('charlie', 'hashed_pwd3', 'charlie@example.com');
SET @uid = LAST_INSERT_ID();
INSERT INTO user_role(user_id, role_id) VALUES(@uid, 2); -- 默认赋普通角色

COMMIT;

START TRANSACTION;

-- 假设某权限不存在导致失败
INSERT INTO role_permission(role_id, permission_id) VALUES(1, 999);

-- 失败时回滚
ROLLBACK;

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_user_log ON login_logs(user_id);

EXPLAIN SELECT u.username, r.name FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id;