#relational-database和MySQL概述

-- 查看所有支持的存储引擎
SHOW ENGINES;

-- 查看当前默认引擎
SELECT @@default_storage_engine;

-- 创建数据库（指定字符集，支持中文和emoji）
CREATE DATABASE IF NOT EXISTS company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 使用指定数据库
USE company_db;

-- 查看所有数据库
SHOW DATABASES;

-- 删除数据库（谨慎操作！）
DROP DATABASE IF EXISTS old_test_db;

-- 先创建被关联的部门表
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY, -- 主键，自增
    dept_name VARCHAR(100) UNIQUE NOT NULL, -- 部门名唯一且不能为空
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 自动记录创建时间
);

-- 再创建员工表（关联部门表）
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    salary DECIMAL(10, 2) NOT NULL CHECK (salary > 0), -- 薪资必须大于0
    hire_date DATE NOT NULL,
    dept_id INT, -- 外键，关联部门表的dept_id
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 自动更新修改时间
    
    -- 建立索引，加速查询
    INDEX idx_emp_email (email),
    INDEX idx_emp_dept (dept_id),
    
    -- 建立外键约束
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL -- 部门删除时，员工的dept_id设为NULL
        ON UPDATE CASCADE -- 部门dept_id修改时，同步更新员工dept_id
);

-- 查看表结构
DESCRIBE employees;

-- 修改表结构（例如增加手机号列）
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- 插入单个部门
INSERT INTO departments (dept_name) 
VALUES ('技术部');

-- 批量插入部门
INSERT INTO departments (dept_name)
VALUES ('产品部'), ('财务部'), ('运营部');

-- 插入单个员工
INSERT INTO employees (first_name, last_name, email, salary, hire_date, dept_id)
VALUES ('张', '三', 'zhangsan@tech.com', 15000.00, '2023-01-01', 1);

-- 批量插入员工
INSERT INTO employees (first_name, last_name, email, salary, hire_date, dept_id)
VALUES 
    ('李', '四', 'lisi@tech.com', 18000.00, '2023-03-15', 1),
    ('王', '五', 'wangwu@product.com', 12000.00, '2023-05-20', 2);

-- 基本查询：查询技术部所有员工的姓名、邮箱、薪资，并按薪资降序排列
SELECT first_name, last_name, email, salary
FROM employees
WHERE dept_id = 1
ORDER BY salary DESC;

-- 关联查询：查询每个员工及其所属部门名称
SELECT 
    e.first_name, 
    e.last_name, 
    d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- 聚合查询：查询每个部门的平均薪资和人数，只显示人数大于1的部门
SELECT 
    d.dept_name,
    COUNT(e.emp_id) AS emp_count,
    AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id
HAVING COUNT(e.emp_id) > 1;