ycg
569c1c8813
- 重构数据访问层：引入DAO模式，支持MySQL/SQLite双数据库 - 新增数据库架构：完整的股票数据、AI分析、自选股管理表结构 - 升级AI分析服务：集成豆包大模型，支持多维度分析 - 优化API路由：分离市场数据API，提供更清晰的接口设计 - 完善项目文档：添加数据库迁移指南、新功能指南等 - 清理冗余文件：删除旧的缓存文件和无用配置 - 新增调度器：支持定时任务和数据自动更新 - 改进前端模板：简化的股票展示页面 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
213 lines
9.6 KiB
SQL
213 lines
9.6 KiB
SQL
-- 股票监控系统扩展数据库结构 (支持全市场股票)
|
|
-- 在原有表结构基础上添加新功能
|
|
|
|
-- 1. 行业分类表
|
|
CREATE TABLE IF NOT EXISTS industries (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
industry_code VARCHAR(20) NOT NULL UNIQUE,
|
|
industry_name VARCHAR(100) NOT NULL,
|
|
parent_code VARCHAR(20) NULL,
|
|
level INT DEFAULT 1 COMMENT '行业层级',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_industry_code (industry_code),
|
|
INDEX idx_parent_code (parent_code)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 2. 概念/板块表
|
|
CREATE TABLE IF NOT EXISTS sectors (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
sector_code VARCHAR(20) NOT NULL UNIQUE,
|
|
sector_name VARCHAR(100) NOT NULL,
|
|
description TEXT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_sector_code (sector_code)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 3. 扩展股票表，添加行业和板块信息
|
|
ALTER TABLE stocks ADD COLUMN IF NOT EXISTS industry_code VARCHAR(20) NULL;
|
|
ALTER TABLE stocks ADD COLUMN IF NOT EXISTS sector_code VARCHAR(20) NULL;
|
|
ALTER TABLE stocks ADD COLUMN IF NOT EXISTS list_date DATE NULL COMMENT '上市日期';
|
|
ALTER TABLE stocks ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT TRUE COMMENT '是否活跃交易';
|
|
ALTER TABLE stocks ADD COLUMN IF NOT EXISTS market_type VARCHAR(20) NULL COMMENT '市场类型:主板/创业板/科创板等';
|
|
|
|
-- 添加外键约束
|
|
ALTER TABLE stocks
|
|
ADD CONSTRAINT fk_stock_industry
|
|
FOREIGN KEY (industry_code) REFERENCES industries(industry_code)
|
|
ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE stocks
|
|
ADD CONSTRAINT fk_stock_sector
|
|
FOREIGN KEY (sector_code) REFERENCES sectors(sector_code)
|
|
ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- 4. K线数据表 (日K、周K、月K)
|
|
CREATE TABLE IF NOT EXISTS kline_data (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
stock_code VARCHAR(10) NOT NULL,
|
|
kline_type ENUM('daily', 'weekly', 'monthly') NOT NULL DEFAULT 'daily',
|
|
trade_date DATE NOT NULL,
|
|
open_price DECIMAL(10,3) NOT NULL,
|
|
high_price DECIMAL(10,3) NOT NULL,
|
|
low_price DECIMAL(10,3) NOT NULL,
|
|
close_price DECIMAL(10,3) NOT NULL,
|
|
volume BIGINT NOT NULL DEFAULT 0,
|
|
amount DECIMAL(15,2) NOT NULL DEFAULT 0 COMMENT '成交额(万元)',
|
|
change_percent DECIMAL(8,4) DEFAULT 0 COMMENT '涨跌幅(%)',
|
|
change_amount DECIMAL(10,3) DEFAULT 0 COMMENT '涨跌额',
|
|
turnover_rate DECIMAL(8,4) DEFAULT 0 COMMENT '换手率(%)',
|
|
pe_ratio DECIMAL(10,2) DEFAULT NULL COMMENT '市盈率',
|
|
pb_ratio DECIMAL(10,2) DEFAULT NULL COMMENT '市净率',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_stock_kline_date (stock_code, kline_type, trade_date),
|
|
INDEX idx_stock_code (stock_code),
|
|
INDEX idx_trade_date (trade_date),
|
|
INDEX idx_kline_type (kline_type),
|
|
INDEX idx_stock_type_date (stock_code, kline_type, trade_date)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 5. 股票-板块关联表 (支持多个概念板块)
|
|
CREATE TABLE IF NOT EXISTS stock_sector_relations (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
stock_code VARCHAR(10) NOT NULL,
|
|
sector_code VARCHAR(20) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_stock_sector (stock_code, sector_code),
|
|
INDEX idx_stock_code (stock_code),
|
|
INDEX idx_sector_code (sector_code),
|
|
FOREIGN KEY (stock_code) REFERENCES stocks(stock_code) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
FOREIGN KEY (sector_code) REFERENCES sectors(sector_code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 6. 市场行情统计表 (每日统计)
|
|
CREATE TABLE IF NOT EXISTS market_statistics (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
stat_date DATE NOT NULL UNIQUE,
|
|
market_code VARCHAR(10) NOT NULL COMMENT '市场代码: SH/SZ/BJ',
|
|
total_stocks INT DEFAULT 0 COMMENT '总股票数',
|
|
up_stocks INT DEFAULT 0 COMMENT '上涨股票数',
|
|
down_stocks INT DEFAULT 0 COMMENT '下跌股票数',
|
|
flat_stocks INT DEFAULT 0 COMMENT '平盘股票数',
|
|
limit_up_stocks INT DEFAULT 0 COMMENT '涨停股票数',
|
|
limit_down_stocks INT DEFAULT 0 COMMENT '跌停股票数',
|
|
total_volume BIGINT DEFAULT 0 COMMENT '总成交量',
|
|
total_amount DECIMAL(15,2) DEFAULT 0 COMMENT '总成交额(亿元)',
|
|
index_change DECIMAL(8,4) DEFAULT 0 COMMENT '主要指数涨跌幅',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_stat_date (stat_date),
|
|
INDEX idx_market_code (market_code)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 7. 数据更新任务表
|
|
CREATE TABLE IF NOT EXISTS data_update_tasks (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
task_name VARCHAR(100) NOT NULL,
|
|
task_type ENUM('daily_basic', 'kline_data', 'stock_list', 'industry_data') NOT NULL,
|
|
status ENUM('pending', 'running', 'completed', 'failed') DEFAULT 'pending',
|
|
start_time TIMESTAMP NULL,
|
|
end_time TIMESTAMP NULL,
|
|
processed_count INT DEFAULT 0 COMMENT '已处理数量',
|
|
total_count INT DEFAULT 0 COMMENT '总数量',
|
|
error_message TEXT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_task_type (task_type),
|
|
INDEX idx_status (status),
|
|
INDEX idx_created_at (created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 8. 热门股票统计表
|
|
CREATE TABLE IF NOT EXISTS hot_stocks (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
stock_code VARCHAR(10) NOT NULL,
|
|
stat_date DATE NOT NULL,
|
|
rank_type ENUM('volume', 'amount', 'change', 'turnover') NOT NULL,
|
|
rank_position INT NOT NULL COMMENT '排名位置',
|
|
rank_value DECIMAL(15,2) NOT NULL COMMENT '排名值',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_hot_stock_rank (stock_code, stat_date, rank_type),
|
|
INDEX idx_stat_date (stat_date),
|
|
INDEX idx_rank_type (rank_type),
|
|
FOREIGN KEY (stock_code) REFERENCES stocks(stock_code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 初始化基础行业数据
|
|
INSERT IGNORE INTO industries (industry_code, industry_name, level) VALUES
|
|
('A01', '农林牧渔', 1),
|
|
('B02', '采矿业', 1),
|
|
('C03', '制造业', 1),
|
|
('D04', '电力、热力、燃气及水生产和供应业', 1),
|
|
('E05', '建筑业', 1),
|
|
('F06', '批发和零售业', 1),
|
|
('G07', '交通运输、仓储和邮政业', 1),
|
|
('H08', '住宿和餐饮业', 1),
|
|
('I09', '信息传输、软件和信息技术服务业', 1),
|
|
('J10', '金融业', 1),
|
|
('K11', '房地产业', 1),
|
|
('L12', '租赁和商务服务业', 1),
|
|
('M13', '科学研究和技术服务业', 1),
|
|
('N14', '水利、环境和公共设施管理业', 1),
|
|
('O15', '居民服务、修理和其他服务业', 1),
|
|
('P16', '教育', 1),
|
|
('Q17', '卫生和社会工作', 1),
|
|
('R18', '文化、体育和娱乐业', 1),
|
|
('S19', '综合', 1);
|
|
|
|
-- 初始化主要概念板块
|
|
INSERT IGNORE INTO sectors (sector_code, sector_name, description) VALUES
|
|
('BK0453', '新能源汽车', '新能源汽车产业链相关股票'),
|
|
('BK0885', '人工智能', '人工智能技术应用相关股票'),
|
|
('BK0500', '半导体', '半导体芯片设计、制造、封测相关股票'),
|
|
('BK0476', '医疗器械', '医疗器械设备和服务相关股票'),
|
|
('BK0727', '军工', '国防军工装备制造相关股票'),
|
|
('BK0489', '光伏概念', '光伏产业链相关股票'),
|
|
('BK0729', '5G概念', '第五代移动通信技术相关股票'),
|
|
('BK0896', '国产软件', '国产软件和信息服务相关股票'),
|
|
('BK0582', '碳中和', '碳中和发展目标相关股票'),
|
|
('BK0456', '生物医药', '生物制药和医药研发相关股票'),
|
|
('BK0857', '数字货币', '数字货币和区块链相关股票'),
|
|
('BK0735', '新基建', '新型基础设施建设相关股票'),
|
|
('BK0557', '大消费', '消费升级相关股票'),
|
|
('BK0726', '国企改革', '国有企业改革相关股票'),
|
|
('BK0439', '雄安新区', '雄安新区建设相关股票');
|
|
|
|
-- 更新 stocks 表的索引
|
|
ALTER TABLE stocks ADD INDEX IF NOT EXISTS idx_industry_code (industry_code);
|
|
ALTER TABLE stocks ADD INDEX IF NOT EXISTS idx_sector_code (sector_code);
|
|
ALTER TABLE stocks ADD INDEX IF NOT EXISTS idx_market_type (market_type);
|
|
ALTER TABLE stocks ADD INDEX IF NOT EXISTS idx_is_active (is_active);
|
|
|
|
-- 为现有数据添加一些示例的行业和板块分类 (如果需要的话)
|
|
UPDATE stocks
|
|
SET industry_code = 'I09', market_type = '创业板'
|
|
WHERE stock_code LIKE '00%' AND stock_code IN ('002065', '002415', '002230');
|
|
|
|
UPDATE stocks
|
|
SET industry_code = 'C03', market_type = '主板'
|
|
WHERE stock_code LIKE '60%' AND stock_code IN ('600589', '600179', '600000');
|
|
|
|
UPDATE stocks
|
|
SET industry_code = 'C03', market_type = '科创板'
|
|
WHERE stock_code LIKE '68%';
|
|
|
|
-- 创建视图便于查询
|
|
CREATE OR REPLACE VIEW v_stock_detail AS
|
|
SELECT
|
|
s.stock_code,
|
|
s.stock_name,
|
|
s.market,
|
|
s.market_type,
|
|
i.industry_name,
|
|
GROUP_CONCAT(sec.sector_name) as sector_names,
|
|
s.list_date,
|
|
s.is_active,
|
|
s.created_at
|
|
FROM stocks s
|
|
LEFT JOIN industries i ON s.industry_code = i.industry_code
|
|
LEFT JOIN stock_sector_relations ssr ON s.stock_code = ssr.stock_code
|
|
LEFT JOIN sectors sec ON ssr.sector_code = sec.sector_code
|
|
WHERE s.is_active = TRUE
|
|
GROUP BY s.stock_code, s.stock_name, s.market, s.market_type, i.industry_name, s.list_date, s.is_active, s.created_at; |