MySQL练习_输出订购数量超过3本的会员编号和订购数量

 题目

项目1：在bookstore数据库中进行简单查询

目的：掌握SELECT语句中DISTINCT子句、LIMIT子句、WHERE子句以及ORDER BY 子句的使用。

1. 查询user表，输出积分高于500分的分员昵称和联系电话。
2. 查询会员表，输出积分低于200分的会员昵称和联系电话，分别用英文username，telephone指定别名。
3. 查询user表，输出E-mail是  QQ邮箱的会员昵称及其E-mail.
4. 查询b_order表，输出订购日期是2016年10月的订单的详细信息。
5. 查询b_order表，输出订货的会员编号 ，要求删除重复行。
6. 查询book表，输出图书的名称和价格，并把查询结果按价格降序排列。
7. 查询book表，输出价格最高的三种图书的名称和价格。

项目2：在bookstore数据库中查询中使用集合函数

目的：掌握集合函数、GROUP BY子句、HAVING子句。

内容如下：

1. 查询book表，输出所有图书的最高价格、最低价格、平均价格。
2. 查询book表，输出每一类图书的数量。
3. 查询book表，输出每一类图书的最高价格、最低价格、平均价格。
4. 查询b_order表，输出订购数量超过3本的会员编号和订购数量。

项目3：在bookstore数据库查询中使用连接查询和子查询。

目的：掌握连接查询和子查询。

内容如下：、

1. 输出所有图书的图书名称、价格以及所属类别名称。
2. 输出订购了《平凡的世界》的会员昵称、联系电话、订购数量。
3. 输出订购了图书的会员昵称和联系电话。
4. 输出无人订购的图书名称和价格。
5. 输出详细订购信息，包括订购图书的会员昵称、联系电话、所订图书名称、数量、价格、折扣价。

数据库

/*
 Navicat Premium Data Transfer
 Source Server         : 本地连接
 Source Server Type    : MySQL
 Source Server Version : 50717
 Source Host           : localhost:3306
 Source Schema         : bookstore
 Target Server Type    : MySQL
 Target Server Version : 50717
 File Encoding         : 65001
 Date: 09/12/2022 19:08:22
*/
 
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for b_order
-- ----------------------------
DROP TABLE IF EXISTS `b_order`;
CREATE TABLE `b_order`  (
  `uid` int(11) NOT NULL,
  `bid` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `ordernum` int(11) NULL DEFAULT 1,
  `orderdate` datetime(0) NULL DEFAULT NULL,
  `deliverydate` datetime(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of b_order
-- ----------------------------
INSERT INTO `b_order` VALUES (1001, '1', 2, '2016-03-12 00:00:00', NULL);
INSERT INTO `b_order` VALUES (1001, '1', 2, '2016-03-12 00:00:00', NULL);
INSERT INTO `b_order` VALUES (1001, '3', 0, '2016-04-15 00:00:00', NULL);
INSERT INTO `b_order` VALUES (1001, '1', 1, '2016-09-15 00:00:00', NULL);
INSERT INTO `b_order` VALUES (1003, '7', 1, '2015-12-14 00:00:00', NULL);
INSERT INTO `b_order` VALUES (1003, '3', 1, '2016-10-10 00:00:00', NULL);
INSERT INTO `b_order` VALUES (1005, '5', 1, '2015-08-17 00:00:00', NULL);
INSERT INTO `b_order` VALUES (1005, '7', 3, '2016-11-12 00:00:00', NULL);
INSERT INTO `b_order` VALUES (1006, '5', 1, '2016-09-08 00:00:00', NULL);
INSERT INTO `b_order` VALUES (1006, '1', 2, '2016-10-21 00:00:00', NULL);
INSERT INTO `b_order` VALUES (1006, '7', 2, '2015-11-21 00:00:00', NULL);
 
-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `bid` int(11) NOT NULL,
  `BNAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `AUTHOR` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `PRICE` double(10, 2) NULL DEFAULT NULL,
  `publisher` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `discount` double(10, 2) NULL DEFAULT NULL,
  `cid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`bid`) USING BTREE,
  INDEX `book_cid`(`cid`) USING BTREE,
  CONSTRAINT `book_cid` FOREIGN KEY (`cid`) REFERENCES `category` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, '中国时代', '师永刚', 39.00, '作家出版社', 27.80, 1);
INSERT INTO `book` VALUES (2, '中国历史屈辱', '王重旭', 26.00, '华夏出版社', 18.20, 2);
INSERT INTO `book` VALUES (3, '择业要趁早', '海文', 28.00, '海天出版社', 19.30, 3);
INSERT INTO `book` VALUES (4, '房间', '爱玛', 37.00, '人民文学出版社', 36.30, 4);
INSERT INTO `book` VALUES (5, '平凡的世界', '路遥', 75.00, '北京出版社', 63.75, 4);
INSERT INTO `book` VALUES (6, '心灵鸡汤', '关然', 27.00, '大豫出版社', 20.00, 3);
INSERT INTO `book` VALUES (7, '蜕', '赵婷', 32.00, '上海出版社', 28.50, 3);
 
-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`  (
  `cid` int(11) NOT NULL,
  `cname` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of category
-- ----------------------------
INSERT INTO `category` VALUES (1, '历史');
INSERT INTO `category` VALUES (2, '家教');
INSERT INTO `category` VALUES (3, '文化');
INSERT INTO `category` VALUES (4, '小说');
 
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `uid` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `UNAME` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `EMAIL` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `TNUM` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `SCORE` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1001', '何大姑', 'hxg18@163.com', '13456234445', 20);
INSERT INTO `user` VALUES ('1002', '平平人生', 'lp011@126.com', '1355158219', 300);
INSERT INTO `user` VALUES ('1003', '四十不惑', '12345@qq.com', '18681688818', 1000);
INSERT INTO `user` VALUES ('1004', '桃花岛主', '810124@qq.com', '13068011234', 600);
INSERT INTO `user` VALUES ('1005', '水灵', 'zs123@371.cn', '15838188503', 150);
INSERT INTO `user` VALUES ('1006', '感动心灵', 'gandong@tom.com', '13641151234', 500);
 
SET FOREIGN_KEY_CHECKS = 1;

答案

-- 项目1：在bookstore数据库中进行简单查询
-- 目的：掌握SELECT语句中DISTINCT子句、LIMIT子句、WHERE子句以及ORDER BY 子句的使用。
-- （1）	查询user表，输出积分高于500分的分员昵称和联系电话。
select uname'昵称',TNUM'练习电话' from user WHERE SCORE>500;
-- （2）	查询会员表，输出积分低于200分的会员昵称和联系电话，分别用英文username，telephone指定别名。
select uname 'username',tnum 'telephone' from user WHERE SCORE<200;
-- （3）	查询user表，输出E-mail是	QQ邮箱的会员昵称及其E-mail.
SELECT uname'昵称',email'E-mail' FROM `user` WHERE email like"%@qq.com";
-- （4）	查询b_order表，输出订购日期是2016年10月的订单的详细信息。
SELECT * FROM `b_order` WHERE `orderdate` BETWEEN '2016-10-01 00:00:00' AND '2016-10-31 23:59:59';
-- （5）	查询b_order表，输出订货的会员编号 ，要求删除重复行。
SELECT uid'会员编号' FROM `b_order` GROUP  BY uid; 
-- （6）	查询book表，输出图书的名称和价格，并把查询结果按价格降序排列。
SELECT bname'名称',price'价格' FROM `book` ORDER BY price desc;
-- （7）	查询book表，输出价格最高的三种图书的名称和价格。
SELECT bname'名称',price'价格' FROM `book` ORDER BY price desc LIMIT 0,3;

-- 项目2：在bookstore数据库中查询中使用集合函数
-- 目的：掌握集合函数、GROUP BY子句、HAVING子句。
-- 内容如下：
-- （1）	查询book表，输出所有图书的最高价格、最低价格、平均价格。
SELECT MAX(price)'最高价格',MiN(price)'最低价格',AVG(price) '平均价格' FROM `book`;
-- （2）	查询book表，输出每一类图书的数量。
SELECT cid,count(cid) FROM `book` GROUP  BY cid;
-- （3）	查询book表，输出每一类图书的最高价格、最低价格、平均价格。
SELECT cid'图书类型',MAX(price)'最高价格',MiN(price)'最低价格',AVG(price) '平均价格' FROM `book` GROUP  BY cid;
-- （4）	查询b_order表，输出订购数量超过3本的会员编号和订购数量。
SELECT uid'会员编号',SUM(ordernum)'订购数量' FROM `b_order`  GROUP BY uid HAVING `订购数量`>3;

-- 项目3：在bookstore数据库查询中使用连接查询和子查询。
-- 目的：掌握连接查询和子查询。
-- 内容如下：、
-- （1）	输出所有图书的图书名称、价格以及所属类别名称。
SELECT bname'图书名称',b.price'价格',c.cname'类别名称' FROM `book` b LEFT JOIN `category` c on b.cid=c.cid;
-- （2）	输出订购了《平凡的世界》的会员昵称、联系电话、订购数量。
SELECT u.UNAME'会员昵称',u.tnum'联系电话',o.ordernum'订购数量' FROM `b_order` o  LEFT JOIN  `book` b  on b.bid=o.bid LEFT JOIN `user` u on u.uid=o.uid WHERE b.BNAME='平凡的世界';
-- （3）	输出订购了图书的会员昵称和联系电话。
SELECT u.UNAME'会员昵称',u.tnum'联系电话' FROM `b_order` o  LEFT JOIN  `book` b  on b.bid=o.bid LEFT JOIN `user` u on u.uid=o.uid group by u.uid;
-- （4）	输出无人订购的图书名称和价格。
SELECT bname'图书名称',price'价格' FROM `book` WHERE bid not in(SELECT bid FROM `b_order` GROUP BY bid);
-- （5）	输出详细订购信息，包括订购图书的会员昵称、联系电话、所订图书名称、数量、价格、折扣价。
SELECT u.UNAME'会员昵称',u.tnum'联系电话',b.bname'图书名称',SUM(o.ordernum)'数量',b.price'价格',b.discount'折扣价' FROM `b_order` o  LEFT JOIN  `book` b  on b.bid=o.bid LEFT JOIN `user` u on u.uid=o.uid  group by o.uid;