mysql实验报告总结,mysql实验报告实验结论
mysql实验报告总结
一、实验名称
电商系统数据库设计、多表复杂查询与索引优化
二、实验目的
1. 掌握关系型数据库建模方法，能够根据电商业务需求完成E-R图设计并转化为规范的关系表结构。
2. 熟练使用DDL语句创建数据库、数据表，并正确设置主键、外键、唯一约束及默认值等完整性约束。
3. 掌握DML语句进行数据的插入、更新与删除操作，理解事务的ACID特性及其在电商场景中的应用。
4. 熟练运用多表JOIN连接查询、子查询、聚合函数及分组操作，完成复杂业务数据的检索与分析。
5. 理解B+树索引的工作原理，能够使用EXPLAIN分析SQL执行计划，并进行针对性的索引优化。
6. 了解事务隔离级别的概念，掌握在并发场景下避免脏读、不可重复读和幻读的方法。
三、实验环境
操作系统：Windows 11 / Ubuntu 22.04 LTS
数据库版本：MySQL 8.0.33（Community Server）
客户端工具：Navicat Premium 16 / DataGrip 2023.2 / MySQL Workbench 8.0
硬件配置：Intel Core i7 / 16GB RAM / 512GB SSD
字符集配置：utf8mb4（支持Emoji及多语言字符存储）
四、实验原理
1. B+树索引原理
MySQL的InnoDB存储引擎默认使用B+树作为索引的数据结构。B+树具有以下核心特性：所有数据记录存储在叶子节点上，叶子节点之间通过双向链表相连，非叶子节点仅存储键值用于导航。这种结构使得范围查询和排序操作极为高效。InnoDB中的聚簇索引（主键索引）的叶子节点存储完整的数据行，而二级索引（辅助索引）的叶子节点存储主键值，通过二级索引查询时需要回表操作获取完整数据。
2. JOIN连接原理
MySQL支持多种表连接方式，包括INNER JOIN（内连接）、LEFT JOIN（左外连接）、RIGHT JOIN（右外连接）等。InnoDB引擎在执行JOIN时主要采用Nested Loop Join算法，即驱动表的每一行与被驱动表进行匹配。优化器会自动选择数据量较小的表作为驱动表以减少循环次数。当被驱动表的连接字段存在索引时，可显著提升JOIN性能。
3. 事务与隔离级别
MySQL事务遵循ACID原则（原子性、一致性、隔离性、持久性）。InnoDB支持四种隔离级别：READ UNCOMMITTED（读未提交）、READ COMMITTED（读已提交）、REPEATABLE READ（可重复读，MySQL默认级别）和SERIALIZABLE（串行化）。InnoDB通过MVCC（多版本并发控制）机制在REPEATABLE READ级别下有效避免了不可重复读问题，并通过间隙锁（Gap Lock）防止幻读。
五、实验步骤与核心代码
步骤一：创建数据库与基础表结构（DDL）
根据电商业务需求，设计用户表、商品分类表、商品表、订单表和订单详情表共五张核心表，并建立合理的外键关联关系。
-- ============================================================
-- 步骤1：创建电商数据库
-- ============================================================
CREATE DATABASE IF NOT EXISTS `ecommerce_db`
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
USE `ecommerce_db`;
-- ============================================================
-- 步骤2：创建用户表 (users)
-- ============================================================
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID，主键自增',
`username` VARCHAR(50) NOT NULL COMMENT '用户名，唯一',
`email` VARCHAR(100) NOT NULL COMMENT '电子邮箱',
`phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号码',
`password_hash` VARCHAR(255) NOT NULL COMMENT '密码哈希值',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '账户状态：1-正常 0-禁用',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`user_id`),
UNIQUE KEY `uk_username` (`username`),
UNIQUE KEY `uk_email` (`email`),
KEY `idx_phone` (`phone`),
KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户信息表';
-- ============================================================
-- 步骤3：创建商品分类表 (categories)
-- ============================================================
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
`category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类ID',
`category_name` VARCHAR(50) NOT NULL COMMENT '分类名称',
`parent_id` INT UNSIGNED DEFAULT 0 COMMENT '父分类ID，0表示顶级分类',
`sort_order` INT NOT NULL DEFAULT 0 COMMENT '排序权重',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`category_id`),
KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品分类表';
-- ============================================================
-- 步骤4：创建商品表 (products)
-- ============================================================
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`product_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品ID',
`product_name` VARCHAR(200) NOT NULL COMMENT '商品名称',
`category_id` INT UNSIGNED NOT NULL COMMENT '所属分类ID',
`price` DECIMAL(10,2) NOT NULL COMMENT '商品价格',
`stock` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存数量',
`description` TEXT DEFAULT NULL COMMENT '商品描述',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '商品状态：1-上架 0-下架',
`sales_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '累计销量',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`product_id`),
KEY `idx_category_id` (`category_id`),
KEY `idx_price_status` (`price`, `status`),
KEY `idx_sales_count` (`sales_count`),
CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品信息表';
-- ============================================================
-- 步骤5：创建订单表 (orders)
-- ============================================================
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`order_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`order_no` VARCHAR(32) NOT NULL COMMENT '订单编号（业务唯一）',
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '下单用户ID',
`total_amount` DECIMAL(12,2) NOT NULL COMMENT '订单总金额',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态：0-待支付 1-已支付 2-已发货 3-已完成 4-已取消',
`payment_time` DATETIME DEFAULT NULL COMMENT '支付时间',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`order_id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_user_id` (`user_id`),
KEY `idx_status_created` (`status`, `created_at`),
KEY `idx_user_status` (`user_id`, `status`),
CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单主表';
-- ============================================================
-- 步骤6：创建订单详情表 (order_items)
-- ============================================================
DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items` (
`item_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '明细ID',
`order_id` BIGINT UNSIGNED NOT NULL COMMENT '所属订单ID', `product_id` BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
`product_name` VARCHAR(200) NOT NULL COMMENT '商品名称（冗余字段，快照）',
`unit_price` DECIMAL(10,2) NOT NULL COMMENT '购买时单价',
`quantity` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '购买数量',
`subtotal` DECIMAL(12,2) NOT NULL COMMENT '小计金额',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`item_id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_product_id` (`product_id`),
CONSTRAINT `fk_items_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`),
CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单详情表';
步骤二：构造实验数据（DML）
向各表中插入具有真实业务感的模拟数据，为后续查询与分析提供数据基础。
-- ============================================================
-- 插入用户数据
-- ============================================================
INSERT INTO `users` (`username`, `email`, `phone`, `password_hash`, `status`) VALUES
('zhangsan', 'zhangsan@example.com', '13800138001', SHA2('Pass@1234', 256), 1),
('lisi', 'lisi@example.com', '13800138002', SHA2('Pass@2345', 256), 1),
('wangwu', 'wangwu@example.com', '13800138003', SHA2('Pass@3456', 256), 1),
('zhaoliu', 'zhaoliu@example.com', '13800138004', SHA2('Pass@4567', 256), 0),
('sunqi', 'sunqi@example.com', '13800138005', SHA2('Pass@5678', 256), 1),
('zhouba', 'zhouba@example.com', '13800138006', SHA2('Pass@6789', 256), 1),
('qianjiu', 'qianjiu@example.com', '13800138007', SHA2('Pass@7890', 256), 1),
('wushi', 'wushi@example.com', '13800138008', SHA2('Pass@8901', 256), 1);
-- ============================================================
-- 插入商品分类数据
-- ============================================================
INSERT INTO `categories` (`category_id`, `category_name`, `parent_id`, `sort_order`) VALUES
(1, '电子产品', 0, 1),
(2, '服装鞋帽', 0, 2),
(3, '食品饮料', 0, 3),
(4, '手机', 1, 1),
(5, '笔记本电脑', 1, 2),
(6, '男装', 2, 1),
(7, '女装', 2, 2),
(8, '零食', 3, 1);
-- ============================================================
-- 插入商品数据
-- ============================================================
INSERT INTO `products` (`product_name`, `category_id`, `price`, `stock`, `description`, `status`, `sales_count`) VALUES
('iPhone 15 Pro Max 256GB', 4, 9999.00, 500, '苹果最新旗舰手机', 1, 12580),
('华为 Mate 60 Pro 512GB', 4, 7999.00, 300, '华为新一代旗舰', 1, 8960),
('小米14 Ultra 16+512GB', 4, 5999.00, 800, '徕卡影像旗舰', 1, 6543),
('MacBook Pro 14英寸 M3芯片', 5, 14999.00, 200, '苹果专业笔记本', 1, 3421),
('联想 ThinkPad X1 Carbon', 5,9999.00, 150, '商务轻薄本标杆', 1, 2156),
('戴尔 XPS 15笔记本', 5, 11999.00, 100, '高性能创作本', 1, 1876),
('男士纯棉圆领T恤', 6, 89.00, 5000,'舒适透气纯棉面料', 1, 25670),
('男士商务休闲裤', 6, 199.00, 3000,'弹力修身版型', 1, 12340),
('女士连衣裙 夏季新款', 7, 299.00, 2000,'碎花雪纺连衣裙', 1, 8765),
('女士真丝衬衫', 7, 459.00, 1500,'桑蚕丝高端衬衫', 1, 4321),
('三只松鼠坚果大礼包', 8, 128.00, 10000,'混合坚果零食组合', 1, 45678),
('良品铺子猪肉脯500g', 8, 49.90, 8000, '原味炭烤猪肉脯', 1,32145),
('农夫山泉矿泉水550ml*24瓶', 3, 28.90, 20000,'天然饮用水整箱装', 1, 67890),
('蒙牛纯牛奶250ml*16盒', 3, 49.90, 15000,'全脂纯牛奶整箱', 1, 54321),
('OPPO Find X7 Ultra', 4, 5999.00, 400, '哈苏影像旗舰手机', 0, 3210);
-- ============================================================
-- 插入订单数据
-- ============================================================
INSERT INTO `orders` (`order_no`, `user_id`, `total_amount`, `status`, `payment_time`, `created_at`) VALUES
('ORD20240101001', 1, 10088.00, 3, '2024-01-01 10:30:00', '2024-01-01 10:15:00'),
('ORD20240102001', 2, 7999.00, 3, '2024-01-02 14:20:00', '2024-01-02 14:00:00'),
('ORD20240115001', 1, 327.80, 3, '2024-01-15 09:45:00', '2024-01-15 09:30:00'),
('ORD20240201001', 3, 14999.00, 2, '2024-02-01 16:00:00', '2024-02-01 15:50:00'),
('ORD20240210001', 5, 757.00, 3, '2024-02-10 11:30:00', '2024-02-10 11:20:00'),
('ORD20240220001',2, 5999.00, 1, '2024-02-20 20:10:00', '2024-02-20 20:00:00'),
('ORD20240301001', 6, 299.00, 3, '2024-03-01 08:30:00', '2024-03-01 08:15:00'),
('ORD20240305001', 7, 10098.00, 0, NULL, '2024-03-05 19:45:00'),
('ORD20240310001', 1, 459.00, 3, '2024-03-10 13:00:00', '2024-03-10 12:50:00'),
('ORD20240315001', 8, 177.80, 4, NULL, '2024-03-15 17:30:00'),
('ORD20240320001', 3, 9999.00, 1, '2024-03-20 10:00:00', '2024-03-20 09:50:00'),
('ORD20240401001', 5, 128.00, 3, '2024-04-01 15:20:00', '2024-04-01 15:10:00');
-- ============================================================
-- 插入订单详情数据
-- ============================================================
INSERT INTO `order_items` (`order_id`, `product_id`, `product_name`, `unit_price`, `quantity`, `subtotal`) VALUES
-- 订单1：张三购买iPhone + T恤
(1, 1, 'iPhone 15 Pro Max 256GB', 9999.00, 1, 9999.00),
(1, 7, '男士纯棉圆领T恤', 89.00, 1, 89.00),
-- 订单2：李四购买华为手机
(2, 2, '华为 Mate 60 Pro 512GB', 7999.00, 1, 7999.00),
-- 订单3：张三购买零食
(3, 11, '三只松鼠坚果大礼包', 128.00, 2, 256.00),
(3, 12, '良品铺子猪肉脯500g', 49.90, 1, 49.90),
(3, 14, '蒙牛纯牛奶250ml*16盒', 49.90, 1, 49.90),
-- 订单4：王五购买MacBook
(4, 4, 'MacBook Pro 14英寸 M3芯片', 14999.00,1, 14999.00),
-- 订单5：孙七购买服装
(5, 7, '男士纯棉圆领T恤', 89.00, 3, 267.00),
(5, 8, '男士商务休闲裤', 199.00, 2, 398.00),
(5, 12, '良品铺子猪肉脯500g', 49.90, 2, 99.80),
-- 订单6：李四购买小米手机
(6, 3, '小米14 Ultra 16+512GB', 5999.00, 1, 5999.00),
-- 订单7：周六购买女装
(7, 9, '女士连衣裙 夏季新款', 299.00, 1, 299.00),
-- 订单8：周七购买两台手机（待支付）
(8, 1, 'iPhone 15 Pro Max 256GB', 9999.00, 1, 9999.00),
(8, 7, '男士纯棉圆领T恤', 89.00, 1, 89.00),
-- 订单9：张三购买真丝衬衫
(9, 10, '女士真丝衬衫', 459.00, 1, 459.00),
-- 订单10：吴十购买零食（已取消）
(10, 11, '三只松鼠坚果大礼包', 128.00, 1, 128.00),
(10, 12, '良品铺子猪肉脯500g', 49.90, 1, 49.90),
-- 订单11：王五购买ThinkPad
(11, 5, '联想 ThinkPad X1 Carbon', 9999.00, 1, 9999.00),
-- 订单12：孙七购买坚果
(12, 11, '三只松鼠坚果大礼包', 128.00, 1, 128.00);
步骤三：多表复杂查询（DQL）
基于以上数据，执行多种典型业务场景下的复杂查询。
-- ============================================================
-- 查询1：查询每个用户的订单总数、总消费金额及最近下单时间
-- 涉及：LEFT JOIN + 聚合函数 + GROUP BY
-- ============================================================
SELECT
u.user_id,
u.username,
u.email,
COUNT(DISTINCT o.order_id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS total_spent,
MAX(o.created_at) AS last_order_time
FROM `users` u
LEFT JOIN `orders` o ON u.user_id = o.user_id
AND o.status != 4 -- 排除已取消的订单
WHERE u.status =1 -- 仅查询正常状态的用户
GROUP BY u.user_id, u.username, u.email
ORDER BY total_spent DESC;
-- ============================================================
-- 查询2：查询各商品分类的销售总额与销量排名
-- 涉及：多表JOIN + 聚合 + 排序
-- ============================================================
SELECT
c.category_id,
c.category_name,
COUNT(oi.item_id) AS item_sold_count,
COALESCE(SUM(oi.subtotal), 0) AS category_revenue,
RANK() OVER (ORDER BY COALESCE(SUM(oi.subtotal), 0) DESC) AS revenue_rank
FROM `categories` c
LEFT JOIN `products` p ON c.category_id = p.category_id
LEFT JOIN `order_items` oi ON p.product_id = oi.product_id
LEFT JOIN `orders` o ON oi.order_id = o.order_id
AND o.status IN (1, 2, 3) -- 仅统计有效订单
GROUP BY c.category_id, c.category_name
ORDER BY category_revenue DESC;
-- ============================================================
-- 查询3：查询购买过"电子产品"分类下商品的所有用户信息
-- 涉及：子查询 + IN + 多表关联
-- ============================================================
SELECT DISTINCT
u.user_id,
u.username,
u.email,
u.phone
FROM `users` u
WHERE u.user_id IN (
SELECT o.user_id
FROM `orders` o
INNER JOIN `order_items` oi ON o.order_id = oi.order_id
INNER JOIN `products` p ON oi.product_id = p.product_id
INNER JOIN `categories` c ON p.category_id = c.category_id
WHERE c.category_name = '电子产品'
OR c.parent_id = (
SELECT category_id FROM `categories` WHERE category_name = '电子产品'
)
AND o.status IN (1, 2, 3)
);
-- ============================================================
-- 查询4：统计每月销售额趋势（按月份分组）
-- 涉及：日期函数 + 聚合 + 分组
-- ============================================================
SELECT
DATE_FORMAT(o.created_at, '%Y-%m') AS order_month,
COUNT(o.order_id) AS order_count,
SUM(o.total_amount) AS monthly_revenue,
ROUND(AVG(o.total_amount), 2) AS avg_order_amount
FROM `orders` o
WHERE o.status != 4 -- 排除已取消订单
GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY order_month ASC;
-- ============================================================
-- 查询5：查询每个用户购买金额最高的那笔订单详情
-- 涉及：窗口函数 ROW_NUMBER()
-- ============================================================
SELECT
t.username,
t.order_no,
t.total_amount,
t.created_at
FROM (
SELECT
u.username,
o.order_no,
o.total_amount,
o.created_at,
ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY o.total_amount DESC) AS rn
FROM `users` u
INNER JOIN `orders` o ON u.user_id = o.user_id
WHERE o.status IN (1, 2, 3)
) t
WHERE t.rn = 1
ORDER BY t.total_amount Dmysql实验报告总结,mysql实验报告实验结论
mysql实验报告总结一、实验名称电商系统数据库设计、多表复杂查询与索引优化二、实验目的1. 掌握关系型数据库建模方法，能够根据电商业务...
点击下载文档文档为doc格式
声明：除非特别标注，否则均为本站原创文章，转载时请以链接形式注明文章出处。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
