MySQL数据库创建员工表代码 MySQL如何创建数据库员工表代码全览

CREATE TABLE

CREATE DATABASE IF NOT EXISTS company_db; -- 如果数据库不存在，则先创建它
USE company_db; -- 切换到这个数据库

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工唯一ID，自动增长',
    first_name VARCHAR(50) NOT NULL COMMENT '名',
    last_name VARCHAR(50) NOT NULL COMMENT '姓',
    email VARCHAR(100) UNIQUE NOT NULL COMMENT '员工邮箱，必须唯一',
    phone_number VARCHAR(20) COMMENT '联系电话',
    hire_date DATE NOT NULL COMMENT '入职日期',
    job_title VARCHAR(50) COMMENT '职位',
    salary DECIMAL(10, 2) NOT NULL COMMENT '薪资，保留两位小数',
    department_id INT COMMENT '部门ID，可关联部门表',
    manager_id INT COMMENT '上级经理ID，可关联员工表自身',
    date_of_birth DATE COMMENT '出生日期',
    gender ENUM('Male', 'Female', 'Other') COMMENT '性别',
    address TEXT COMMENT '住址',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公司员工信息表';

-- 假设你有一个部门表，可以添加外键约束来维护数据一致性
-- ALTER TABLE employees
-- ADD CONSTRAINT fk_department
-- FOREIGN KEY (department_id) REFERENCES departments(department_id)
-- ON DELETE SET NULL ON UPDATE CASCADE;

-- 如果需要自关联经理ID
-- ALTER TABLE employees
-- ADD CONSTRAINT fk_manager
-- FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
-- ON DELETE SET NULL ON UPDATE CASCADE;

employee_id

INT

AUTO_INCREMENT

PRIMARY KEY

first_name

last_name

VARCHAR

email

UNIQUE

NOT NULL

phone_number

VARCHAR

INT

hire_date

DATE

salary

DECIMAL(10, 2)

job_title

VARCHAR

department_id

manager_id

INT

date_of_birth

gender

ENUM

address

TEXT

created_at

updated_at

PRIMARY KEY

NOT NULL

UNIQUE

FOREIGN KEY

department_id

manager_id

ON DELETE CASCADE

SET NULL

INDEX

employee_id

email

last_name

department_id

CHARSET

COLLATE

utf8mb4

utf8mb4_unicode_ci

utf8mb4_general_ci

utf8mb4

utf8

mb4

InnoDB

MyISAM

InnoDB

MyISAM

ALTER TABLE