MySQL电商管理系统练习题及答案

一 、表结构

• 用户表(user)：id(主键)、username、password、email、phone、age
• 商品表(product)：id(主键)、name、price、stock、description
• 订单表(order)：id(主键)、user_id(外键，关联用户表)、total_price、status、create_time、update_time
• 订单详情表(order_detail)：id(主键)、order_id(外键，关联订单表)、product_id(外键，关联商品表)、quantity

二、创建数据表

-- 创建用户表
CREATE TABLE IF NOT EXISTS `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `password` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `age` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 创建商品表
CREATE TABLE IF NOT EXISTS `product` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL,
  `stock` INT(11) NOT NULL,
  `description` TEXT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 创建订单表
CREATE TABLE IF NOT EXISTS `order` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `total_price` DECIMAL(10, 2) NOT NULL,
  `status` VARCHAR(20) NOT NULL,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 创建订单详情表
CREATE TABLE IF NOT EXISTS `order_detail` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `order_id` INT(11) NOT NULL,
  `product_id` INT(11) NOT NULL,
  `quantity` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`order_id`) REFERENCES `order`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `product`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

三、单表查询练习题

#2.	查询用户表中所有用户的id、username和email。
SELECT id,username,email FROM `user`;
#3.	查询商品表中所有商品的name、price和stock。
SELECT `name`,price,stock FROM product;
#4.	查询订单表中所有订单的user_id、total_price和status。
SELECT user_id,total_price,`status` FROM `order`;
#5.	查询订单详情表中所有订单详细信息的id、order_id和product_id。
SELECT id,order_id,product_id FROM order_detail;
#6.	查询用户表中用户名包含“@”的用户的id、username和email。
SELECT id,username,email FROM `user` WHERE username LIKE "%@%";
#7.	查询商品表中价格大于100的商品的name、price和stock。
SELECT `name`,price,stock FROM product WHERE price >100;
#8.	查询订单表中状态为“已完成”的订单的user_id、total_price和status。
SELECT user_id,total_price,`status` FROM `order` WHERE `status` ="已完成";
#9.	查询用户表中phone为空的用户的id、username和email。
SELECT  id,username,email FROM `user` WHERE phone IS NULL;
#10.	查询商品表中description为空的商品的name、price和stock。
SELECT `name`,price,stock FROM product WHERE description IS NULL;
#11.	查询订单详情表中product_id为1的订单详细信息的id、order_id和quantity。
SELECT id,order_id,quantity FROM order_detial  WHERE product_id=1;
#12.	查询用户表中email长度大于10的用户id、username和email。
SELECT id,username,email FROM `user` WHERE LENGTH(email)>10;
#13.	查询商品表中stock小于10的商品的name、price和stock。
SELECT `name`,price,`stock` FROM product WHERE `stock`<10;
#14.	查询订单表中status为“待付款”的订单的user_id、total_price和status。
SELECT user_id,total_price,`status` FROM `order` WHERE `status` = "待付款";
#15.	查询订单详情表中quantity大于10的订单详细信息的id、order_id和quantity。
SELECT id,order_id,quantity FROM order_detial  WHERE quantity>10;
#16.	查询用户表中username以“张”开头的用户id、username和email。
SELECT id,username,email FROM `user` WHERE username="张%";
#17.	查询商品表中price大于100且stock大于10的商品的name、price和stock。
SELECT `name`,price,`stock` FROM product WHERE `stock`>10 AND price>100;
#18.	查询订单表中status为“已完成”且total_price大于500的订单的user_id、total_price和status。
SELECT user_id,total_price,`status`FROM `order` WHERE `status`='已完成'AND total_price>500;
#19.	查询订单表中，用户id为1的订单的创建时间（create_time）和更新时间（update_time）。
SELECT user_id,create_time,update_time FROM `order` WHERE user_id=1;
#20.	查询订单表中，订单状态为“待付款”的订单的id、user_id和create_time。
SELECT id,user_id,create_id FROM `order` WHERE `status`='待付款';
#1.	查询用户表中所有用户的昵称（username）和邮箱（email），按照邮箱地址升序排序。
SELECT username,email FROM `user` ORDER BY email ASC;
#2.	查询商品表中所有商品的名字（name）、价格（price）和库存（stock），按照价格降序排序，库存不足10的商品优先显示。
SELECT `name`,price,stock FROM product ORDER BY price DESC,stock ASC;
#3.	查询订单表中所有订单的创建时间（create_time），按照时间升序排序，并显示最近10条订单。
SELECT create_time FROM `order` ORDER BY create_time LIMIT 10;
#4.	查询订单表中的订单总数
SELECT COUNT(*) '订单总数' FROM `order`;
#5.	查询订单表中订单的总金额（total_price）
SELECT SUM（total_price）'总金额' FROM `order`;
#6.	查询订单表中订单的平均金额
SELECT AVG（total_price）'平均金额' FROM `order`;
#7.	查询订单表中订单状态为“已完成”的订单数量。
SELECT COUNT(*)'订单数量'FROM `order` WHERE status='已完成';
#8.	查询订单表中订单状态为“进行中”的订单总金额 
SELECT SUM(total_price)'订单总金额 'FROM `order` WHERE status='进行中';
#9.	查询订单表中第一个订单的创建时间。
SELECT create_time FROM `order` ORDER BY create_time ASC LIMIT 0,1;
#10.	查询订单表中最后一条订单的创建时间。
SELECT create_time FROM `order` ORDER BY create_time DESC LIMIT 0,1;
#11.	查询用户表中年龄在20到30岁的用户，按照年龄升序排序。
SELECT * FROM `user` WHERE age BETWEEN 20 AND 30 ORDER BY age;
#12.	查询商品表中价格在100到500之间的商品，按照价格升序排序。
SELECT * FROM product WHERE price BETWEEN 100 AND 500 ORDER BY price;
#13.	查询订单表中订单状态为“进行中”的订单，按照创建时间升序排序。
SELECT * FROM `order` WHERE status='进行中' ORDER BY create_time;
#14.	查询订单表中由用户1创建的订单，按照订单金额升序排序。
SELECT * FROM `order` WHERE user_id=1 ORDER BY total_price;
#15.	查询订单详情表中订单id为1的订单详情，按照商品名称升序排序。
SELECT * FROM `order` WHERE user_id=1 ORDER BY name;
#16.	查询商品表中库存大于10的商品，并显示商品名和库存。
SELECT `name`,stock FROM product WHERE stock > 10;
#17.	查询订单表中订单状态为“已完成”的订单，按照订单金额降序排序，显示前5条。
SELECT total_price FROM `order` WHERE `status`='已完成'  ORDER BY total_price DESC LIMIT 0,5;
#18.	查询用户表中年龄大于25岁的用户，并显示用户名。
SELECT username FROM `user` WHERE age>25; 
#19.	查询商品表中价格低于200的商品，并显示商品名。
SELECT`name`FROM product WHERE price<200;
#20.	查询订单表中订单状态为“进行中”的订单，按照订单创建时间降序排序，显示最近10条。
SELECT create_time FROM `order` WHERE `status`='进行中' ORDER BY  create_time DESC LIMIT 0,10;
#21.	查询用户表中用户id为1的用户，并显示其昵称和邮箱。
SELECT username,email FROM `user` WHERE id=1;
#22.	查询商品表中库存小于5的商品，并显示商品名和库存。
SELECT `name`,stock FROM product WHERE stock<5;
#23.	查询订单表中订单总金额大于1000的订单，按照订单创建时间升序排序。
SELECT * FROM product WHERE price>1000 ORDER BY creat_time ASC;
#24.	查询订单表中订单状态为“已完成”的订单，并显示订单号和订单状态。
SELECT id,`status` FROM `order` WHERE `status`='已完成';
#25.	查询订单详情表中订单id为1的订单详情，按照商品编号升序排序，显示前10条。
SELECT * FROM order_deatil WHERE order_id=1 ORDER BY product_id ASC LIMIT 0,10;
#26.	 查询订单表中订单状态为“进行中”的订单，按照订单创建时间升序排序，显示最近20条。 
SELECT * FROM `order` WHERE `status`='进行中' ORDER BY create_time ASC LIMIT 0,20;
#27.	查询用户表中年龄在20到30岁的用户，按照年龄降序排序，显示最近5条。
SELECT * FROM `user` WHERE age BETWEEN 20 AND 30 ORDER BY age DESC LIMIT 0,5;
#28.	查询用户表中，按照年龄分组后，年龄在20到30岁的用户数量，并显示各年龄组的用户数量。
SELECT age, COUNT(*) FROM `user` WHERE age BETWEEN 20 AND 30 GROUP BY age;
#29.	查询订单表中，按照订单状态分组后，各订单状态的数量，并按照订单状态降序排序。
SELECT `status`,COUNT(*) '数量' FROM `order` GROUP BY `status` ORDER BY `status` DESC;
#30.	查询订单表中，按照订单状态分组，每个状态下的总金额，并按照总金额降序排序，显示前5条。
SELECT `status`,SUM(total_price) FROM `order` GROUP BY `status` ORDER BY SUM(total_price) DESC LIMIT 0,5;

欢迎大家阅读，本人见识有限，写的博客难免有错误或者疏忽的地方，还望各位指点，在此表示感激不尽。文章持续更新中…