mysql父子关系语句

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

INSERT INTO departments (name) VALUES ('HR');
INSERT INTO employees (name, department_id) VALUES ('Alice', 1);
INSERT INTO employees (name, department_id) VALUES ('Bob', 1);

SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 示例：插入不存在的部门ID
INSERT INTO employees (name, department_id) VALUES ('Charlie', 99);
-- 解决方法：确保department_id在departments表中存在

-- 示例：添加索引
ALTER TABLE employees ADD INDEX idx_department_id (department_id);