mysql多表创建视图

-- 创建 employees 表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
);

-- 创建 departments 表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 插入示例数据
INSERT INTO employees (id, name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', 1);

INSERT INTO departments (id, name) VALUES
(1, 'HR'),
(2, 'Engineering');

-- 创建多表视图
CREATE VIEW employee_department AS
SELECT e.id AS employee_id, e.name AS employee_name, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 错误示例
CREATE VIEW employee_department AS
SELECT e.id AS employee_id, e.name AS employee_name, d.name AS department_name
FROM employees e
JOIN departmentss d ON e.department_id = d.id;  -- 表名拼写错误

-- 正确示例
CREATE VIEW employee_department AS
SELECT e.id AS employee ID, e.name AS employee_name, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 在 department_id 上创建索引
CREATE INDEX idx_department_id ON employees(department_id);