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', 1),
(3, 'Charlie', 2);

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

-- 创建视图
CREATE VIEW department_employee_count AS
SELECT d.name AS department_name, COUNT(e.id) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name;

-- 查询视图
SELECT * FROM department_employee_count;

+----------------+---------------+
| department_name | employee_count|
+----------------+---------------+
| HR             |             2 |
| Engineering    |             1 |
+----------------+---------------+