【无标题】

hyperkiller-17

于 2024-06-27 22:38:03 发布

阅读量267

点赞数 1

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/2301_80888325/article/details/140025429

版权

建表语句

-- 创建用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    first_name VARCHAR(50) NOT NULL COMMENT '名',
    last_name VARCHAR(50) NOT NULL COMMENT '姓',
    email VARCHAR(100) COMMENT '邮箱地址',
    phone VARCHAR(20) COMMENT '电话号码',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '账户余额'
);
 
 
-- 创建股票信息表
CREATE TABLE stocks (
    stock_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '股票ID',
    stock_code VARCHAR(10) NOT NULL COMMENT '股票代码',
    stock_name VARCHAR(100) NOT NULL COMMENT '股票名称',
    current_price DECIMAL(10, 2) NOT NULL COMMENT '当前价格',
    open_price DECIMAL(10, 2) COMMENT '开盘价',
    close_price DECIMAL(10, 2) COMMENT '收盘价',
    change_rate DECIMAL(5, 2) COMMENT '涨跌幅'
);
 
 
-- 创建交易记录表
CREATE TABLE transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '交易ID',
    user_id INT NOT NULL COMMENT '用户ID',
    stock_id INT NOT NULL COMMENT '股票ID',
    transaction_type ENUM('BUY', 'SELL') NOT NULL COMMENT '交易类型（买/卖）',
    price_per_share DECIMAL(10, 2) NOT NULL COMMENT '每股价格',
    quantity INT NOT NULL COMMENT '交易数量',
    transaction_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '交易时间',
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (stock_id) REFERENCES stocks(stock_id)
);
 
 
-- 创建交易订单表（可选，用于保存未成交的订单）
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    user_id INT NOT NULL COMMENT '用户ID',
    stock_id INT NOT NULL COMMENT '股票ID',
    order_type ENUM('BUY', 'SELL') NOT NULL COMMENT '订单类型（买/卖）',
    price_per_share DECIMAL(10, 2) NOT NULL COMMENT '每股价格',
    quantity INT NOT NULL COMMENT '订单数量',
    order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单时间',
    status ENUM('PENDING', 'FILLED', 'CANCELLED') NOT NULL DEFAULT 'PENDING' COMMENT '订单状态',
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (stock_id) REFERENCES stocks(stock_id)
);
 
 
-- 创建股票持仓表（可选，用于记录用户当前的股票持仓情况）
CREATE TABLE portfolios (
    portfolio_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '持仓ID',
    user_id INT NOT NULL COMMENT '用户ID',
    stock_id INT NOT NULL COMMENT '股票ID',
    quantity INT NOT NULL COMMENT '持仓数量',
    average_cost_per_share DECIMAL(10, 2) NOT NULL COMMENT '平均每股成本',
    current_value DECIMAL(10, 2) NOT NULL COMMENT '当前总价值',
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (stock_id) REFERENCES stocks(stock_id)
);
 
 
-- 创建资金流水表（可选，用于记录用户的资金变动情况）
CREATE TABLE fund_flows (
    flow_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '流水ID',
    user_id INT NOT NULL COMMENT '用户ID',
    transaction_id INT COMMENT '交易ID',
    amount DECIMAL(10, 2) NOT NULL COMMENT '金额',
    flow_type ENUM('DEPOSIT', 'WITHDRAWAL', 'TRADE') NOT NULL COMMENT '流水类型（充值/提现/交易）',
    flow_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '流水时间',
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id)
);

DDL语句

CREATE TABLE `fund_flows` (
  `flow_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `transaction_id` int(11) DEFAULT NULL COMMENT '交易ID',
  `amount` decimal(10,2) NOT NULL COMMENT '金额',
  `flow_type` enum('DEPOSIT','WITHDRAWAL','TRADE') NOT NULL COMMENT '流水类型（充值/提现/交易）',
  `flow_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '流水时间',
  PRIMARY KEY (`flow_id`),
  KEY `user_id` (`user_id`),
  KEY `transaction_id` (`transaction_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

INSERT INTO `fund_flows` VALUES ('1', '1', '1', '1045.00', 'TRADE', '2024-06-27 22:01:47');
INSERT INTO `fund_flows` VALUES ('2', '2', '2', '-787.50', 'TRADE', '2024-06-27 22:01:47');
INSERT INTO `fund_flows` VALUES ('3', '3', '3', '1776.00', 'TRADE', '2024-06-27 22:01:47');
INSERT INTO `fund_flows` VALUES ('4', '4', '4', '-1000.00', 'TRADE', '2024-06-27 22:01:47');
INSERT INTO `fund_flows` VALUES ('5', '5', '5', '1365.00', 'TRADE', '2024-06-27 22:01:47');
INSERT INTO `fund_flows` VALUES ('6', '6', '6', '-1205.00', 'TRADE', '2024-06-27 22:01:47');
INSERT INTO `fund_flows` VALUES ('7', '7', '7', '1180.00', 'TRADE', '2024-06-27 22:01:47');
INSERT INTO `fund_flows` VALUES ('8', '8', '8', '-900.00', 'TRADE', '2024-06-27 22:01:47');

CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `stock_id` int(11) NOT NULL COMMENT '股票ID',
  `order_type` enum('BUY','SELL') NOT NULL COMMENT '订单类型（买/卖）',
  `price_per_share` decimal(10,2) NOT NULL COMMENT '每股价格',
  `quantity` int(11) NOT NULL COMMENT '订单数量',
  `order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单时间',
  `status` enum('PENDING','FILLED','CANCELLED') NOT NULL DEFAULT 'PENDING' COMMENT '订单状态',
  PRIMARY KEY (`order_id`),
  KEY `user_id` (`user_id`),
  KEY `stock_id` (`stock_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;


INSERT INTO `orders` VALUES ('1', '1', '2', 'BUY', '15.60', '30', '2024-06-27 22:01:34', 'PENDING');
INSERT INTO `orders` VALUES ('2', '3', '1', 'SELL', '10.60', '50', '2024-06-27 22:01:34', 'PENDING');
INSERT INTO `orders` VALUES ('3', '4', '3', 'BUY', '8.60', '50', '2024-06-27 22:01:34', 'PENDING');
INSERT INTO `orders` VALUES ('4', '5', '6', 'BUY', '10.00', '50', '2024-06-27 22:01:34', 'PENDING');
INSERT INTO `orders` VALUES ('5', '6', '7', 'SELL', '11.60', '20', '2024-06-27 22:01:34', 'PENDING');
INSERT INTO `orders` VALUES ('6', '8', '5', 'BUY', '9.60', '50', '2024-06-27 22:01:34', 'PENDING');
INSERT INTO `orders` VALUES ('7', '7', '4', 'SELL', '8.00', '100', '2024-06-27 22:01:34', 'PENDING');

CREATE TABLE `portfolios` (
  `portfolio_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '持仓ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `stock_id` int(11) NOT NULL COMMENT '股票ID',
  `quantity` int(11) NOT NULL COMMENT '持仓数量',
  `average_cost_per_share` decimal(10,2) NOT NULL COMMENT '平均每股成本',
  `current_value` decimal(10,2) NOT NULL COMMENT '当前总价值',
  PRIMARY KEY (`portfolio_id`),
  KEY `user_id` (`user_id`),
  KEY `stock_id` (`stock_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


INSERT INTO `portfolios` VALUES ('1', '1', '1', '100', '10.45', '1045.00');
INSERT INTO `portfolios` VALUES ('2', '2', '2', '50', '15.75', '787.50');
INSERT INTO `portfolios` VALUES ('3', '3', '3', '200', '8.88', '1776.00');
INSERT INTO `portfolios` VALUES ('4', '4', '4', '100', '10.45', '1000.00');
INSERT INTO `portfolios` VALUES ('5', '5', '5', '150', '10.50', '1365.00');
INSERT INTO `portfolios` VALUES ('6', '6', '6', '110', '8.45', '1205.00');
INSERT INTO `portfolios` VALUES ('7', '7', '7', '80', '6.45', '1180.00');
INSERT INTO `portfolios` VALUES ('8', '8', '8', '160', '11.45', '900.00');

CREATE TABLE `stocks` (
  `stock_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '股票ID',
  `stock_code` varchar(10) NOT NULL COMMENT '股票代码',
  `stock_name` varchar(100) NOT NULL COMMENT '股票名称',
  `current_price` decimal(10,2) NOT NULL COMMENT '当前价格',
  `open_price` decimal(10,2) DEFAULT NULL COMMENT '开盘价',
  `close_price` decimal(10,2) DEFAULT NULL COMMENT '收盘价',
  `change_rate` decimal(5,2) DEFAULT NULL COMMENT '涨跌幅',
  PRIMARY KEY (`stock_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


INSERT INTO `stocks` VALUES ('1', '000001', '蜀汉股份', '10.50', '10.20', '10.40', '2.94');
INSERT INTO `stocks` VALUES ('2', '000002', '曹魏集团', '15.80', '15.50', '15.70', '1.94');
INSERT INTO `stocks` VALUES ('3', '000003', '东吴证券', '8.90', '8.70', '8.85', '2.30');
INSERT INTO `stocks` VALUES ('4', '000004', '百年马家', '7.90', '8.80', '8.88', '1.30');
INSERT INTO `stocks` VALUES ('5', '000005', '怒吼集团', '4.90', '7.70', '5.85', '3.30');
INSERT INTO `stocks` VALUES ('6', '000006', '无忧传媒', '5.90', '10.70', '9.55', '2.30');
INSERT INTO `stocks` VALUES ('7', '000007', '兄弟情义', '8.50', '7.00', '10.00', '3.30');
INSERT INTO `stocks` VALUES ('8', '000008', '忠义公司', '8.90', '10.70', '6.85', '2.30');

CREATE TABLE `transactions` (
  `transaction_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '交易ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `stock_id` int(11) NOT NULL COMMENT '股票ID',
  `transaction_type` enum('BUY','SELL') NOT NULL COMMENT '交易类型（买/卖）',
  `price_per_share` decimal(10,2) NOT NULL COMMENT '每股价格',
  `quantity` int(11) NOT NULL COMMENT '交易数量',
  `transaction_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '交易时间',
  PRIMARY KEY (`transaction_id`),
  KEY `user_id` (`user_id`),
  KEY `stock_id` (`stock_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


INSERT INTO `transactions` VALUES ('1', '1', '1', 'BUY', '10.45', '100', '2024-06-27 22:01:12');
INSERT INTO `transactions` VALUES ('2', '2', '2', 'SELL', '15.37', '50', '2024-06-27 22:01:12');
INSERT INTO `transactions` VALUES ('3', '3', '3', 'BUY', '8.88', '200', '2024-06-27 22:01:12');
INSERT INTO `transactions` VALUES ('4', '4', '4', 'BUY', '6.66', '50', '2024-06-27 22:01:12');
INSERT INTO `transactions` VALUES ('5', '5', '5', 'SELL', '6.66', '50', '2024-06-27 22:01:12');
INSERT INTO `transactions` VALUES ('6', '6', '6', 'BUY', '3.00', '50', '2024-06-27 22:01:12');
INSERT INTO `transactions` VALUES ('7', '7', '7', 'BUY', '4.20', '100', '2024-06-27 22:01:12');
INSERT INTO `transactions` VALUES ('8', '8', '8', 'BUY', '5.00', '100', '2024-06-27 22:01:12');

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `first_name` varchar(50) NOT NULL COMMENT '名',
  `last_name` varchar(50) NOT NULL COMMENT '姓',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱地址',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
  `gender` enum('男','女') NOT NULL COMMENT '性别',
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '账户余额',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


INSERT INTO `users` VALUES ('1', '刘备', '123', '刘', '备', 'liubei@example.com', '13800138000', '男', '10000.00');
INSERT INTO `users` VALUES ('2', '曹操', '123', '曹', '操', 'caocao@example.com', '13900139000', '男', '20000.00');
INSERT INTO `users` VALUES ('3', '孙权', '123', '孙', '权', 'sunquan@example.com', '13700137000', '男', '15000.00');
INSERT INTO `users` VALUES ('4', '马超', '123', '马', '超', 'machao@example.com', '13700137001', '男', '16000.00');
INSERT INTO `users` VALUES ('5', '张飞', '123', '张', '飞', 'zhangfei@example.com', '13700137002', '男', '17000.00');
INSERT INTO `users` VALUES ('6', '貂蝉', '123', '貂', '蝉', 'diaochan@example.com', '13700137003', '女', '18000.00');
INSERT INTO `users` VALUES ('7', '关羽', '123', '关', '羽', 'guanyu@example.com', '13700137004', '男', '14000.00');
INSERT INTO `users` VALUES ('8', '赵云', '123', '赵', '云', 'zhaoyun@example.com', '13700137005', '男', '13000.00');

DML语句

-- 插入用户数据
INSERT INTO users (username, password, first_name, last_name, email, phone, gender, balance) VALUES
('刘备', '123', '刘', '备', 'liubei@example.com', '13800138000', '男', 10000.00),
('曹操', '123', '曹', '操', 'caocao@example.com', '13900139000', '男', 20000.00),
('孙权', '123', '孙', '权', 'sunquan@example.com', '13700137000', '男', 15000.00),
('马超', '123', '马', '超', 'machao@example.com', '13700137001', '男', 16000.00),
('张飞', '123', '张', '飞', 'zhangfei@example.com', '13700137002', '男', 17000.00),
('貂蝉', '123', '貂', '蝉', 'diaochan@example.com', '13700137003', '女', 18000.00),
('关羽', '123', '关', '羽', 'guanyu@example.com', '13700137004', '男', 14000.00),
('赵云', '123', '赵', '云', 'zhaoyun@example.com', '13700137005', '男', 13000.00);
 
 
-- 插入股票数据
INSERT INTO stocks (stock_code, stock_name, current_price, open_price, close_price, change_rate) VALUES
('000001', '蜀汉股份', 10.50, 10.20, 10.40, 2.94),
('000002', '曹魏集团', 15.80, 15.50, 15.70, 1.94),
('000003', '东吴证券', 8.90, 8.70, 8.85, 2.30),
('000004', '百年马家', 7.90, 8.80, 8.88, 1.30),
('000005', '怒吼集团', 4.90, 7.70, 5.85, 3.30),
('000006', '无忧传媒', 5.90, 10.70, 9.55, 2.30),
('000007', '兄弟情义', 8.50, 7.00, 10.00, 3.30),
('000008', '忠义公司', 8.90, 10.70, 6.85, 2.30);
 
 
-- 插入交易记录数据（假设刘备买了蜀汉股份，曹操卖了曹魏集团，孙权买了东吴证券）
 
-- 假设刘备的user_id是1，蜀汉股份的stock_id是1
INSERT INTO transactions (user_id, stock_id, transaction_type, price_per_share, quantity, transaction_date) VALUES
(1, 1, 'BUY', 10.45, 100, NOW()); -- 刘备购买蜀汉股份

 
-- 假设曹操的user_id是2，曹魏集团的stock_id是2
INSERT INTO transactions (user_id, stock_id, transaction_type, price_per_share, quantity, transaction_date) VALUES
(2, 2, 'SELL', 15.37, 50, NOW()); -- 曹操卖出曹魏集团
 
 
-- 假设孙权的user_id是3，东吴证券的stock_id是3
INSERT INTO transactions (user_id, stock_id, transaction_type, price_per_share, quantity, transaction_date) VALUES
(3, 3, 'BUY', 8.88, 200, NOW()); -- 孙权购买东吴证券
 
 -- 假设马超的user_id是4，百年马家的stock_id是4
INSERT INTO transactions (user_id, stock_id, transaction_type, price_per_share, quantity, transaction_date) VALUES
(4, 4, 'BUY',6.66, 50, NOW()); -- 马超购买百年马家

 -- 假设张飞的user_id是5，怒吼集团的stock_id是5
INSERT INTO transactions (user_id, stock_id, transaction_type, price_per_share, quantity, transaction_date) VALUES
(5, 5, 'SELL',6.66, 50, NOW()); -- 张飞卖出怒吼集团

 -- 假设貂蝉的user_id是6，无忧传媒的stock_id是6
INSERT INTO transactions (user_id, stock_id, transaction_type, price_per_share, quantity, transaction_date) VALUES
(6, 6, 'BUY',3.00, 50, NOW()); -- 貂蝉购买无忧传媒

  -- 假设关羽的user_id是7，兄弟情义的stock_id是7
INSERT INTO transactions (user_id, stock_id, transaction_type, price_per_share, quantity, transaction_date) VALUES
(7, 7, 'BUY',4.20, 100, NOW()); -- 关羽购买兄弟情义

 -- 假设赵云的user_id是8，忠义公司的stock_id是8
INSERT INTO transactions (user_id, stock_id, transaction_type, price_per_share, quantity, transaction_date) VALUES
(8, 8, 'BUY',5.00, 100, NOW()); -- 赵云购买忠义公司

 
-- 插入交易订单数据（可选，这里作为示例插入）
INSERT INTO orders (user_id, stock_id, order_type, price_per_share, quantity, order_date, status) VALUES
(1, 2, 'BUY', 15.60, 30, NOW(), 'PENDING'),  -- 刘备打算购买曹魏集团
(3, 1, 'SELL', 10.60, 50, NOW(), 'PENDING'),  -- 孙权打算卖出蜀汉股份
(4, 3, 'BUY', 8.60, 50, NOW(), 'PENDING'),  -- 马超打算购买东吴证券
(5, 6, 'BUY', 10.00, 50, NOW(), 'PENDING'),  -- 张飞打算购买无忧集团
(6, 7, 'SELL', 11.60, 20, NOW(), 'PENDING'),  -- 貂蝉打算卖出兄弟情义
(8, 5, 'BUY', 9.60, 50, NOW(), 'PENDING'),  -- 赵云打算购买怒吼集团
(7, 4, 'SELL', 8.00, 100, NOW(), 'PENDING');  -- 关羽打算卖出百年马家
 
 
-- 插入股票持仓数据（可选，这里根据交易记录数据插入）
INSERT INTO portfolios (user_id, stock_id, quantity, average_cost_per_share, current_value) VALUES
(1, 1, 100, 10.45, 1045.00),  -- 刘备持仓蜀汉股份
(2, 2, 50, 15.75, 787.50),    -- 曹操剩余持仓曹魏集团（假设他原本有更多）
(3, 3, 200, 8.88, 1776.00),   -- 孙权持仓东吴证券
(4, 4, 100, 10.45, 1000.00),  -- 马超持仓百年马家
(5, 5, 150, 10.50, 1365.00),  -- 张飞持仓怒吼集团
(6, 6, 110, 8.45, 1205.00),  -- 貂蝉持仓无忧传媒
(7, 7, 80, 6.45, 1180.00),  -- 关羽持仓兄弟情义
(8, 8, 160, 11.45, 900.00);  -- 赵云持仓忠义公司
 
 
-- 插入资金流水数据（可选，这里根据交易记录数据插入）
INSERT INTO fund_flows (user_id, transaction_id, amount, flow_type, flow_date) VALUES
(1, 1, 1045.00, 'TRADE', NOW()),  -- 刘备购买蜀汉股份的资金流出
(2, 2, -787.50, 'TRADE', NOW()),  -- 曹操卖出曹魏集团的资金流入（负数表示资金流入）
(3, 3, 1776.00, 'TRADE', NOW()),  -- 孙权购买东吴证券的资金流出
(4, 4, -1000.00, 'TRADE', NOW()),  -- 马超购买百年马家的资金流入
(5, 5, 1365.00, 'TRADE', NOW()),  -- 张飞购买怒吼集团的资金流出
(6, 6, -1205.00, 'TRADE', NOW()),  -- 貂蝉购买无忧传媒的资金流入
(7, 7, 1180.00, 'TRADE', NOW()),  -- 关羽购买兄弟情义的资金流出
(8, 8, -900.00, 'TRADE', NOW());  -- 赵云购买忠义公司的资金流入