编辑代码

CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    location VARCHAR(100),
    manager_id INT
);

CREATE TABLE positions (
    position_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(50) NOT NULL,
    min_salary DECIMAL(10,2),
    max_salary DECIMAL(10,2)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    gender ENUM('男','女','其他'),
    birth_date DATE,
    hire_date DATE NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    address VARCHAR(200),
    dept_id INT,
    position_id INT,
    salary DECIMAL(10,2),
    manager_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    FOREIGN KEY (position_id) REFERENCES positions(position_id),
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

ALTER TABLE departments
ADD FOREIGN KEY (manager_id) REFERENCES employees(emp_id);

INSERT INTO departments (dept_name, location) VALUES 
('人力资源部', '总部大楼3楼'),
('财务部', '总部大楼2楼'),
('技术研发部', '研发中心A栋'),
('市场营销部', '总部大楼1楼'),
('客户服务部', '总部大楼1楼');

INSERT INTO positions (title, min_salary, max_salary) VALUES 
('CEO', 50000, 200000),
('部门经理', 20000, 50000),
('高级工程师', 15000, 35000),
('工程师', 10000, 20000),
('财务主管', 18000, 30000),
('会计', 8000, 15000),
('人力资源经理', 18000, 35000),
('招聘专员', 6000, 12000),
('市场总监', 25000, 60000),
('销售代表', 5000, 15000),
('客服主管', 10000, 20000),
('客服专员', 5000, 10000);

INSERT INTO employees (full_name, gender, birth_date, hire_date, email, phone, position_id, salary) VALUES 
('曹操', '男', '1970-05-15', '2010-01-10', 'caocao@company.com', '13800138000', 1, 180000);

SET @ceo_id = LAST_INSERT_ID();

UPDATE departments SET manager_id = @ceo_id WHERE dept_id = 1;

INSERT INTO employees (full_name, gender, birth_date, hire_date, email, phone, dept_id, position_id, salary, manager_id) VALUES 
('司马昭', '男', '1975-08-20', '2012-03-15', 'simayi@company.com', '13800138001', 3, 2, 45000, @ceo_id),
('荀彧', '男', '1972-11-05', '2011-05-22', 'xunyu@company.com', '13800138002', 2, 5, 42000, @ceo_id),
('郭嘉', '男', '1978-04-30', '2013-07-10', 'guojia@company.com', '13800138003', 4, 9, 48000, @ceo_id);

SET @hr_manager_id = LAST_INSERT_ID();
SET @tech_manager_id = (SELECT emp_id FROM employees WHERE email = 'simayi@company.com');
SET @finance_manager_id = (SELECT emp_id FROM employees WHERE email = 'xunyu@company.com');
SET @market_manager_id = (SELECT emp_id FROM employees WHERE email = 'guojia@company.com');

UPDATE departments SET manager_id = @hr_manager_id WHERE dept_id = 1;
UPDATE departments SET manager_id = @finance_manager_id WHERE dept_id = 2;
UPDATE departments SET manager_id = @tech_manager_id WHERE dept_id = 3;
UPDATE departments SET manager_id = @market_manager_id WHERE dept_id = 4;

INSERT INTO employees (full_name, gender, birth_date, hire_date, email, phone, address, dept_id, position_id, salary, manager_id) VALUES 
('曹植', '男', '1990-12-25', '2018-06-18', 'caozhi@company.com', '13800138004', '魏', 3, 4, 18000, @tech_manager_id),
('甄洛', '女', '1992-03-08', '2019-02-14', 'zhenmi@company.com', '13800138005', '群', 1, 8, 9000, @hr_manager_id),
('夏侯惇', '男', '1985-07-12', '2015-09-05', 'xiahoudun@company.com', '13800138006', '魏', 3, 3, 28000, @tech_manager_id),
('张辽', '男', '1988-09-28', '2016-11-20', 'zhangliao@company.com', '13800138007', '魏', 4, 10, 12000, @market_manager_id),
('许褚', '男', '1983-04-15', '2014-08-12', 'xuchu@company.com', '13800138008', '群', 3, 4, 15000, @tech_manager_id),
('贾诩', '男', '1979-10-03', '2017-05-30', 'jiaxu@company.com', '13800138009', '魏', 2, 6, 11000, @finance_manager_id),
('蔡文姬', '女', '1991-06-22', '2020-01-15', 'caiwenji@company.com', '13800138010', '汉', 5, 12, 8000, @hr_manager_id);

SELECT e.emp_id, e.full_name, d.dept_name, p.title, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN positions p ON e.position_id = p.position_id;

SELECT e.emp_id, e.full_name, e.gender, p.title, e.salary
FROM employees e
JOIN positions p ON e.position_id = p.position_id
WHERE e.dept_id = (SELECT dept_id FROM departments WHERE dept_name = '技术研发部');

SELECT * FROM employees WHERE full_name = '曹植';