简单查询与多表联合复杂查询

原创 于 2024-06-24 11:21:35 发布 · 263 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #java #sql

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
);

big富翁
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值