DDL
CREATE TABLE user (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE user_wallet (
wallet_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
CREATE TABLE user_wallet_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
transaction_type VARCHAR(50) NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
transaction_time TIMESTAMP NOT NULL,
FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
CREATE TABLE product_type (
type_id INT AUTO_INCREMENT PRIMARY KEY,
type_name VARCHAR(100) NOT NULL,
parent_id INT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE product (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
stock INT NOT NULL DEFAULT 0,
type_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (type_id) REFERENCES product_type(type_id) ON DELETE RESTRICT
);
CREATE TABLE `order` (
`order_id` INT AUTO_INCREMENT PRIMARY KEY,
`user_id` INT NOT NULL,
`order_status` VARCHAR(50) NOT NULL DEFAULT '待支付',
`order_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`payment_status` VARCHAR(50) NOT NULL DEFAULT '未支付',
`payment_time` TIMESTAMP NULL,
`total_price` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) ON DELETE RESTRICT
);
CREATE TABLE order_info (
order_info_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES `order`(order_id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT
);
DML
INSERT INTO `user` VALUES ('1', '宋亚轩', 'password1', 'user1@example.com', '13100012341', '2024-06-01 21:05:31');
INSERT INTO `user` VALUES ('2', '贺峻霖', 'password2', 'user2@example.com', '13200043212', '2024-06-02 21:05:32');
INSERT INTO `user` VALUES ('3', '张真源', 'password3', 'user3@example.com', '13300056783', '2024-06-03 21:05:33');
INSERT INTO `user` VALUES ('4', '马嘉祺', 'password4', 'user4@example.com', '13400099994', '2024-05-30 21:05:34');
INSERT INTO `user` VALUES ('5', '丁程鑫', 'password5', 'user5@example.com', '13500055555', '2024-06-04 22:05:35');
INSERT INTO `user` VALUES ('6', '刘耀文', 'password6', 'user6@example.com', '13600066666', '2024-06-01 22:07:36');
INSERT INTO `user` VALUES ('7', '严浩翔', 'password7', 'user7@example.com', '13700099997', '2024-06-01 22:07:37');
INSERT INTO `user` VALUES ('8', '王俊凯', 'password8', 'user8@example.com', '13800033338', '2024-06-04 22:07:38');
INSERT INTO `user` VALUES ('9', '王源', 'password9', 'user9@example.com', '13900000009', '2024-06-08 22:07:39');
INSERT INTO `user` VALUES ('10', '易烊千玺', 'password10', 'user10@example.com', '13800000010', '2024-06-08 22:07:39');
INSERT INTO `user_wallet` (`user_id`, `balance`)
VALUES
(1, 100.50),
(2, 200.75),
(3, 150.20),
(4, 80.00),
(5, 120.30),
(6, 90.15),
(7, 180.40),
(8, 70.60),
(9, 110.80),
(10, 130.70);
INSERT INTO `user_wallet_log` (`user_id`, `transaction_type`, `amount`, `transaction_time`)
VALUES
(1, '充值', 50.00, '2024-06-18 10:00:00'),
(2, '消费', 20.00, '2024-06-18 11:00:00'),
(3, '充值', 80.00, '2024-06-18 12:00:00'),
(4, '消费', 15.00, '2024-06-18 13:00:00'),
(5, '充值', 30.00, '2024-06-18 14:00:00'),
(6, '消费', 10.00, '2024-06-18 15:00:00'),
(7, '充值', 45.00, '2024-06-18 16:00:00'),
(8, '消费', 25.00, '2024-06-18 17:00:00'),
(9, '充值', 60.00, '2024-06-18 18:00:00'),
(10, '消费', 18.00, '2024-06-18 19:00:00');
INSERT INTO `product_type` VALUES ('1', '电子产品', null, '各类电子设备', '2024-05-31 22:13:55');
INSERT INTO `product_type` VALUES ('2','家电', null, '家庭使用的电器', '2024-06-01 22:13:55');
INSERT INTO `product_type` VALUES ('3', '通讯设备', null, '手机等通讯相关', '2024-06-02 22:13:55');
INSERT INTO `product_type` VALUES ('4', '电脑周边', null, '与电脑配套的产品', '2024-06-03 22:13:55');
INSERT INTO `product_type` VALUES ('5', '存储设备', null, '如 U 盘、硬盘等', '2024-06-04 22:13:55');
INSERT INTO `product` VALUES ('1', 'Apple iPhone 15', '8999.99', '50', '1', '2024-06-05 22:13:55');
INSERT INTO `product` VALUES ('2', 'MacBook Pro', '14999.00', '50', '2', '2024-06-06 22:13:55');
INSERT INTO `product` VALUES ('3', '小米智能音箱', '199.00', '200', '3', '2024-06-07 22:13:55');
INSERT INTO `product` VALUES ('4', 'Huawei Mate 60', '6999.99', '30', '1', '2024-06-08 22:13:55');
INSERT INTO `product` VALUES ('5', '索尼降噪耳机', '1299.00', '150', '5', '2024-06-09 22:13:55');
INSERT INTO `product` VALUES ('6', '华为MatePad Pro', '3999.00', '70', '2', '2024-05-31 22:13:55');
INSERT INTO `product` VALUES ('7', 'Xiaomi 14 Pro', '5999.99', '25', '1', '2024-06-01 22:13:55');
INSERT INTO `product` VALUES ('8', 'LG 27寸显示器', '1499.00', '120', '4', '2024-06-02 22:13:55');
INSERT INTO `product` VALUES ('9', 'Samsung Galaxy S24', '7999.99', '40', '1', '2024-06-03 22:13:55');
INSERT INTO `product` VALUES ('10', '一加9 Pro', '4999.00', '60', '1', '2024-05-30 22:13:55');
INSERT INTO `order` (order_id, user_id, order_status, order_time, payment_status, payment_time, total_price)
VALUES
(1, 1, '待支付', '2024-06-18 12:00:00', '未支付', '0000-00-00 00:00:00', 150.00),
(2, 2, '已发货', '2024-06-17 13:30:00', '已支付', '2024-06-17 13:35:00', 220.50),
(3, 3, '已完成', '2024-06-16 10:10:00', '已支付', '2024-06-16 10:15:00', 180.75),
(4, 4, '待支付', '2024-06-15 14:40:00', '未支付', '0000-00-00 00:00:00', 95.30),
(5, 5, '已支付', '2024-06-14 16:20:00', '已支付', '2024-06-14 16:25:00', 120.60),
(6, 6, '已发货', '2024-06-13 11:50:00', '已支付', '2024-06-13 11:55:00', 260.90),
(7, 7, '已完成', '2024-06-12 15:20:00', '已支付', '2024-06-12 15:25:00', 310.50),
(8, 8, '待支付', '2024-06-11 17:10:00', '未支付', '0000-00-00 00:00:00', 80.80),
(9, 9, '已支付', '2024-06-10 13:40:00', '已支付', '2024-06-10 13:45:00', 110.10),
(10, 10, '已发货', '2024-06-09 12:30:00', '已支付', '2024-06-09 12:35:00', 135.00);
INSERT INTO `order_info` VALUES ('1', '1', '1', '2', '50.00');
INSERT INTO `order_info` VALUES ('2', '1', '2', '1', '60.00');
INSERT INTO `order_info` VALUES ('3', '2', '3', '3', '70.00');
INSERT INTO `order_info` VALUES ('4', '3', '2', '1', '60.00');
INSERT INTO `order_info` VALUES ('5', '3', '4', '2', '80.00');
INSERT INTO `order_info` VALUES ('6', '5', '1', '1', '50.00');
INSERT INTO `order_info` VALUES ('7', '6', '4', '1', '80.00');
INSERT INTO `order_info` VALUES ('8', '7', '3', '2', '70.00');
INSERT INTO `order_info` VALUES ('9', '8', '2', '2', '60.00');
INSERT INTO `order_info` VALUES ('10', '9', '1', '3', '50.00');
INSERT INTO `order_info` VALUES ('11', '9', '3', '1', '70.00');
INSERT INTO `order_info` VALUES ('12', '10', '2', '1', '60.00');
1、基础查询
-- 查询用户信息
SELECT username AS '姓名', phone AS '手机号' FROM user;
2、模糊查询
-- 模糊查询
CREATE INDEX idx_product_name ON product(product_name);
-- 统计用户订单信息
SELECT user_id, COUNT(order_id) AS '订单数量'
FROM `order`
GROUP BY user_id
ORDER BY `订单数量` DESC;
3、复杂查询
-- 复杂查询
SELECT
u.user_id, -- 选择用户的用户ID
u.username, -- 选择用户名
u.email, -- 选择邮箱
u.phone, -- 选择电话
uw.wallet_id, -- 选择钱包ID
uw.balance -- 选择钱包余额
FROM
user u -- 从用户表中选择数据
JOIN
user_wallet uw ON u.user_id = uw.user_id; -- 使用JOIN连接用户表和钱包表,连接条件是两个表中的user_id相同
4、查看订单中下单最多的产品
-- 查看订单中下单最多的产品对应的类别
select type_name '下单最多的产品类别'
from product p
INNER join
product_type pt
on p.type_id=pt.type_id
where
product_id =
(select
product_id
from order_info
GROUP BY product_id
ORDER BY count(product_id) desc
limit 1);
5、查询下单总金额最多的用户
-- 查询下单总金额最多的用户
select *
from `user`
join
user_wallet wu
on `user`.user_id=wu.user_id
where
wu.user_id=
(
select user_id
from `order`
group by user_id
ORDER BY sum(total_price) desc
limit 1
);