MySQL数据库基础练习系列目标
很多学生或者说是初学者在学习完成数据库的基础增删改查后就自认为在数据库这里就很熟悉了，但是不接触项目根本部知道需求，我这里准备了50个项目的基本需求来让大家来熟练各类项目的列信息，让大家更好的深入项目进行实战式的练习，可以让大家在后面面试的时候有更多更丰富的资历让大家可以与面试官侃侃而谈。
数据库环境
MySQL版本：5.7.31-log
数据库字符集，所有数据库通用字符集与排序规则，支持中文数据。
字符集：utf8
排序规则：utf8_general_ci
使用工具：Navicat Premium 15，可以在下面的连接中下载
https://download.csdn.net/download/feng8403000/89403778
项目名称与项目简介
股票交易系统是一个综合性的金融服务平台，它提供了股票买卖、交易查询、用户管理、股票信息管理以及资金账户管理等功能。系统旨在为用户提供一个安全、高效、便捷的股票交易环境，让用户能够实时掌握市场动态，做出合理的投资决策。
该系统的主要功能包括：
用户管理：注册新用户、修改用户信息、密码重置等。
股票信息管理：添加、修改和删除股票信息，包括股票代码、名称、价格等。
股票交易：用户可以进行股票的买入和卖出操作，系统记录交易详情。
交易查询：用户可以查询自己的交易记录，包括买入、卖出以及持仓情况。
资金账户管理：管理用户的资金账户，包括充值、提现、查看账户余额等。
数据库DDL（注意创建顺序）
为了直接运行DDL语句并创建表，我们需要确保在创建含有外键约束的表之前，相关的被引用表（即外键指向的表）已经存在。所以我们在创建表的时候一定要按照一定的顺序来创建，否则就会出现没有外键关系导致的创建异常。
-- 创建用户表
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)
);插入数据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);
-- 插入股票数据
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);
-- 插入交易记录数据（假设刘备买了蜀汉股份，曹操卖了曹魏集团，孙权买了东吴证券）
-- 假设刘备的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()); -- 孙权购买东吴证券
-- 插入交易订单数据（可选，这里作为示例插入）
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'); -- 孙权打算卖出蜀汉股份
-- 插入股票持仓数据（可选，这里根据交易记录数据插入）
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); -- 孙权持仓东吴证券
-- 插入资金流水数据（可选，这里根据交易记录数据插入）
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()); -- 孙权购买东吴证券的资金流出遵循的数据库三范式
数据库建表的三范式（3NF，Third Normal Form）是关系型数据库设计的基本原则，用于确保数据库结构的逻辑性和减少数据冗余。这三个范式是逐步细化的，每一个范式都是在前一个范式的基础上建立的。下面我将详细解释这三个范式：
第一范式（1NF, First Normal Form）
定义：
列不可分割，即数据库表的每一列都是不可分割的原子数据项。
每一列都是不可再分的最小数据单元（也称为最小的原子单元）。
解释：
-
在第一范式中，主要关注的是列的原子性。也就是说，表中的每一列都应该只包含一个值，而不能包含集合、数组或其他复合数据类型。
-
例如，如果有一个“地址”列，它包含了街道、城市、省份和国家等信息，那么这就违反了第一范式。应该将这个“地址”列拆分成多个独立的列，如“街道”、“城市”、“省份”和“国家”。
第二范式（2NF, Second Normal Form）
定义：
满足1NF。
非主键列必须完全依赖于主键，而不能只依赖于主键的一部分（针对复合主键而言）。
解释：
-
第二范式建立在第一范式的基础上，主要关注于主键与非主键列之间的依赖关系。
-
在第二范式中，一个表只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
-
如果表中的某一列只与复合主键的一部分有关，那么它就不应该存在于这个表中，而应该被分离出去形成另外一张新表。
第三范式（3NF, Third Normal Form）
定义：
满足2NF。
非主键列必须直接依赖于主键，不能存在传递依赖。即非主键列必须直接依赖于整个主键，而不能依赖于主键的一部分。
解释：
-
第三范式是在第二范式的基础上进一步细化的。它主要关注于消除传递依赖，即非主键列不应该依赖于主键的某一部分，而应该直接依赖于整个主键。
-
如果存在传递依赖，那么应该考虑将这个非主键列分离出去，形成新的表，并通过主键或外键与原表进行关联。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
