MySQL多表查询详解：内连接、外连接、自连接通通搞懂【MySQL系列】

SELECT A.*, B.*
FROM tableA A
INNER JOIN tableB B
ON A.common_field = B.common_field;

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

SELECT e.employee_id, e.first_name, d.department_name, j.job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id;

SELECT A.*, B.*
FROM tableA A
LEFT JOIN tableB B
ON A.common_field = B.common_field;

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

SELECT A.*, B.*
FROM tableA A
RIGHT JOIN tableB B
ON A.common_field = B.common_field;

SELECT e.employee_id, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

SELECT e.employee_id, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id

UNION

SELECT e.employee_id, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

SELECT e.employee_id, e.first_name, m.first_name AS manager_name
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

SELECT a.department_name AS child, b.department_name AS parent
FROM departments a
LEFT JOIN departments b
ON a.parent_id = b.department_id;

SELECT employee_id FROM employees WHERE department_id = 10
UNION
SELECT employee_id FROM employees WHERE department_id = 20;

SELECT employee_id FROM employees WHERE department_id = 10
UNION ALL
SELECT employee_id FROM employees WHERE department_id = 20;

SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1000;

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY e.hire_date DESC
LIMIT 10 OFFSET 20;

SELECT e.*
FROM employees e
JOIN (
    SELECT department_id, MAX(hire_date) AS latest_hire
    FROM employees
    GROUP BY department_id
) t
ON e.department_id = t.department_id AND e.hire_date = t.latest_hire;

SELECT d.department_id, d.department_name
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

SELECT name, 'customer' AS source FROM customers
UNION
SELECT name, 'supplier' AS source FROM suppliers;

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;