Mysql多表联查

暖月寒星

发布于 2020-03-12 10:17:45

1.5K0

发布于 2020-03-12 10:17:45

文章被收录于专栏：浅枫沐雪

- 创建三张表

/*
Navicat MySQL Data Transfer

Source Server         : condb
Source Server Version : 50722
Source Host           : localhost:3306
Source Database       : qfmx

Target Server Type    : MYSQL
Target Server Version : 50722
File Encoding         : 65001

Date: 2019-04-16 10:15:59
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for authors
-- ----------------------------
DROP TABLE IF EXISTS `authors`;
CREATE TABLE `authors` (
  `author_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '图书编号',
  `author_name` varchar(55) DEFAULT NULL COMMENT '图书名称',
  PRIMARY KEY (`author_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of authors
-- ----------------------------
INSERT INTO `authors` VALUES ('1', 'Kimm');
INSERT INTO `authors` VALUES ('2', 'Abel');
INSERT INTO `authors` VALUES ('3', 'Bill');
INSERT INTO `authors` VALUES ('4', 'Bon');
INSERT INTO `authors` VALUES ('5', 'Bob');
INSERT INTO `authors` VALUES ('6', 'Lili');
INSERT INTO `authors` VALUES ('7', 'BErews');
INSERT INTO `authors` VALUES ('8', 'Berton');

-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
  `book_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '图书编号',
  `author_id` int(11) DEFAULT NULL COMMENT '作者编号',
  `start_date` datetime DEFAULT NULL COMMENT '开始日期',
  `end_date` datetime DEFAULT NULL COMMENT '结束日期',
  PRIMARY KEY (`book_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of books
-- ----------------------------
INSERT INTO `books` VALUES ('1', '4', '2019-04-02 09:59:04', '2019-04-19 09:59:12');
INSERT INTO `books` VALUES ('2', '1', '2019-02-13 09:59:25', '2019-04-12 09:59:32');
INSERT INTO `books` VALUES ('3', '6', '2019-01-16 09:59:42', '2019-04-11 09:59:46');
INSERT INTO `books` VALUES ('4', '6', '2018-04-09 10:00:00', '2019-04-01 10:00:04');
INSERT INTO `books` VALUES ('5', '2', '2018-01-05 10:00:23', '2019-04-02 10:00:33');
INSERT INTO `books` VALUES ('6', '7', '2019-04-10 10:00:50', '2019-07-11 10:00:55');
INSERT INTO `books` VALUES ('7', '8', '2019-04-03 10:01:08', '2019-12-16 10:01:12');

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
  `book_id` int(11) DEFAULT NULL COMMENT '图书编号',
  `price` double(8,2) DEFAULT NULL COMMENT '价格',
  `order_date` datetime DEFAULT NULL COMMENT '下单日期',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '4', '52.00', '2019-04-02 10:01:45');
INSERT INTO `orders` VALUES ('2', '3', '55.00', '2019-04-01 10:02:04');
INSERT INTO `orders` VALUES ('3', '5', '66.00', '2019-04-02 10:02:10');
INSERT INTO `orders` VALUES ('4', '1', '54.00', '2019-02-16 10:02:31');
INSERT INTO `orders` VALUES ('5', '3', '44.00', '2019-04-09 10:02:45');
INSERT INTO `orders` VALUES ('6', '2', '47.00', '2019-04-01 10:02:58');

作者表

图书表

订单表

- 作者表和图书表两表联查

SELECT
    a.*, b.book_id
FROM
    `authors` a
LEFT JOIN books b ON a.author_id = b.author_id;

- 作者表和图书表,订单表三表联查

SELECT
    a.*, b.book_id,
    o.order_id,
    o.price,
    o.order_date
FROM
    `authors` a
LEFT JOIN books b ON a.author_id = b.author_id
LEFT JOIN orders o ON b.book_id = o.book_id;

- 根据book_id计算每一本书的订单总额

SELECT
    `authors`.*, sum(`orders`.price)
FROM
    `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id
GROUP BY
    `books`.book_id

- 选取在一定时间区间范围内的order订单

SELECT
    `authors`.*, `books`.book_id,
    `orders`.order_id,
    sum(`orders`.price)
FROM
    `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id
AND `orders`.order_date >= `books`.start_date
AND `orders`.order_date <= `books`.end_date
GROUP BY
    `books`.book_id

- where不能用于选取列的AS别名判断，MYSQL的处理机制是先进行选取，再进行筛选，在选取阶段就启用了where条件，因为这时并不存在prices的筛选结果后才产生的字段，会抛出错误;选取阶段order表是存在price字段的，所以只有price不为空的记录才会被选取

SELECT
    `authors`.*, `books`.book_id,
    `orders`.order_id,
    sum(`orders`.price) AS prices
FROM
    `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id
AND `orders`.order_date >= `books`.start_date
AND `orders`.order_date <= `books`.end_date
WHERE
    `orders`.price IS NOT NULL
GROUP BY
    `books`.book_id

- Having子句的使用，查找价格大于52的图书作者

SELECT
    `authors`.*, `books`.book_id,
    sum(`orders`.price) AS prices
FROM
    `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id
GROUP BY
    `books`.book_id
HAVING
    prices > 52

- 组合查询

SELECT
    `authors`.*, `books`.book_id,
    sum(`orders`.price) AS prices
FROM
    `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id
GROUP BY
    `books`.book_id
HAVING
    prices >= 20
ORDER BY
    prices ASC
LIMIT 1,2