142 lines
8.8 KiB
SQL
142 lines
8.8 KiB
SQL
-- ============================================================
|
||
-- 资产管理系统 — 数据库初始化脚本
|
||
-- 数据库：asset_db | 字符集：utf8mb4 | 引擎：InnoDB
|
||
-- ============================================================
|
||
|
||
-- 创建数据库
|
||
CREATE DATABASE IF NOT EXISTS asset_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
|
||
|
||
USE asset_db;
|
||
|
||
-- ============================================================
|
||
-- 1. 用户表（t_user）
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS t_borrow;
|
||
DROP TABLE IF EXISTS t_disposal;
|
||
DROP TABLE IF EXISTS t_asset;
|
||
DROP TABLE IF EXISTS t_category;
|
||
DROP TABLE IF EXISTS t_user;
|
||
|
||
CREATE TABLE t_user (
|
||
id INT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
username VARCHAR(50) NOT NULL COMMENT '用户名',
|
||
password VARCHAR(100) NOT NULL COMMENT '密码（MD5加密）',
|
||
real_name VARCHAR(50) NOT NULL COMMENT '真实姓名',
|
||
role VARCHAR(20) NOT NULL DEFAULT 'user' COMMENT '角色（admin/user）',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态（1启用/0禁用）',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_username (username)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
|
||
|
||
-- ============================================================
|
||
-- 2. 资产分类表（t_category）
|
||
-- ============================================================
|
||
CREATE TABLE t_category (
|
||
id INT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
category_code VARCHAR(20) NOT NULL COMMENT '分类编号',
|
||
category_name VARCHAR(50) NOT NULL COMMENT '分类名称',
|
||
description VARCHAR(200) DEFAULT NULL COMMENT '分类描述',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_category_code (category_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资产分类表';
|
||
|
||
-- ============================================================
|
||
-- 3. 资产表（t_asset）
|
||
-- ============================================================
|
||
CREATE TABLE t_asset (
|
||
id INT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
asset_code VARCHAR(30) NOT NULL COMMENT '资产编号',
|
||
asset_name VARCHAR(100) NOT NULL COMMENT '资产名称',
|
||
category_id INT NOT NULL COMMENT '分类ID',
|
||
status VARCHAR(20) NOT NULL DEFAULT 'available' COMMENT '状态（available/borrowed/disposed）',
|
||
value DECIMAL(12,2) NOT NULL COMMENT '资产价值（元）',
|
||
purchase_date DATE NOT NULL COMMENT '购入日期',
|
||
location VARCHAR(100) DEFAULT NULL COMMENT '存放位置',
|
||
description VARCHAR(500) DEFAULT NULL COMMENT '资产描述',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_asset_code (asset_code),
|
||
INDEX idx_category_id (category_id),
|
||
INDEX idx_status (status),
|
||
CONSTRAINT fk_asset_category FOREIGN KEY (category_id) REFERENCES t_category (id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资产表';
|
||
|
||
-- ============================================================
|
||
-- 4. 借用表（t_borrow）
|
||
-- ============================================================
|
||
CREATE TABLE t_borrow (
|
||
id INT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
asset_id INT NOT NULL COMMENT '资产ID',
|
||
user_id INT NOT NULL COMMENT '借用人ID',
|
||
borrow_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '借用时间',
|
||
expected_return_date DATE NOT NULL COMMENT '预计归还日期',
|
||
actual_return_date DATETIME DEFAULT NULL COMMENT '实际归还日期',
|
||
reason VARCHAR(200) NOT NULL COMMENT '借用原因',
|
||
status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态（pending/approved/rejected/returned/overdue）',
|
||
approver_id INT DEFAULT NULL COMMENT '审批人ID',
|
||
approve_time DATETIME DEFAULT NULL COMMENT '审批时间',
|
||
approve_remark VARCHAR(200) DEFAULT NULL COMMENT '审批备注',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (id),
|
||
INDEX idx_borrow_asset (asset_id),
|
||
INDEX idx_borrow_user (user_id),
|
||
INDEX idx_borrow_status (status),
|
||
CONSTRAINT fk_borrow_asset FOREIGN KEY (asset_id) REFERENCES t_asset (id),
|
||
CONSTRAINT fk_borrow_user FOREIGN KEY (user_id) REFERENCES t_user (id),
|
||
CONSTRAINT fk_borrow_approver FOREIGN KEY (approver_id) REFERENCES t_user (id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='借用表';
|
||
|
||
-- ============================================================
|
||
-- 5. 报废表（t_disposal）
|
||
-- ============================================================
|
||
CREATE TABLE t_disposal (
|
||
id INT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
asset_id INT NOT NULL COMMENT '资产ID',
|
||
user_id INT NOT NULL COMMENT '申请人ID',
|
||
reason VARCHAR(200) NOT NULL COMMENT '报废原因',
|
||
status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态（pending/approved/rejected）',
|
||
approver_id INT DEFAULT NULL COMMENT '审批人ID',
|
||
approve_time DATETIME DEFAULT NULL COMMENT '审批时间',
|
||
approve_remark VARCHAR(200) DEFAULT NULL COMMENT '审批备注',
|
||
disposal_date DATETIME DEFAULT NULL COMMENT '报废执行日期',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (id),
|
||
INDEX idx_disposal_asset (asset_id),
|
||
INDEX idx_disposal_user (user_id),
|
||
INDEX idx_disposal_status (status),
|
||
CONSTRAINT fk_disposal_asset FOREIGN KEY (asset_id) REFERENCES t_asset (id),
|
||
CONSTRAINT fk_disposal_user FOREIGN KEY (user_id) REFERENCES t_user (id),
|
||
CONSTRAINT fk_disposal_approver FOREIGN KEY (approver_id) REFERENCES t_user (id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报废表';
|
||
|
||
-- ============================================================
|
||
-- 初始数据
|
||
-- ============================================================
|
||
|
||
-- 管理员用户（密码：admin123，MD5加密后：0192023a7bbd73250516f069df18b500）
|
||
INSERT INTO t_user (username, password, real_name, role, status) VALUES
|
||
('admin', '0192023a7bbd73250516f069df18b500', '管理员', 'admin', 1);
|
||
|
||
-- 4个初始分类
|
||
INSERT INTO t_category (category_code, category_name, description) VALUES
|
||
('CAT001', '办公设备', '打印机、复印机、扫描仪等办公设备'),
|
||
('CAT002', '电子设备', '电脑、显示器、投影仪等电子设备'),
|
||
('CAT003', '家具', '办公桌、椅子、文件柜等家具'),
|
||
('CAT004', '交通工具', '汽车、电动车等交通工具');
|
||
|
||
-- 6条示例资产数据（分布在不同分类下，状态各不同）
|
||
INSERT INTO t_asset (asset_code, asset_name, category_id, status, value, purchase_date, location, description) VALUES
|
||
('AST20260001', '惠普激光打印机', 1, 'available', 2999.00, '2025-03-15', 'A栋3楼打印室', '惠普M1136激光一体机'),
|
||
('AST20260002', '联想笔记本电脑', 2, 'borrowed', 5999.00, '2025-06-20', 'B栋2楼研发部', 'ThinkPad E14 i7处理器'),
|
||
('AST20260003', '实木办公桌', 3, 'available', 1500.00, '2024-11-10', 'A栋5楼经理室', '1.4米实木办公桌'),
|
||
('AST20260004', '爱普生投影仪', 2, 'disposed', 3500.00, '2023-08-05', 'C栋1楼会议室', '已报废处理'),
|
||
('AST20260005', '比亚迪电动轿车', 4, 'available', 159800.00, '2025-01-18', '地下车库B2', '秦PLUS DM-i 2025款'),
|
||
('AST20260006', '华为平板电脑', 2, 'borrowed', 3299.00, '2025-09-01', 'B栋3楼设计部', 'MatePad Pro 12.6英寸');
|