/*
Navicat MySQL Data Transfer
Source Server : MySQL
Source Server Version : 50625
Source Host : 127.0.0.1:3306
Source Database : db_book
Target Server Type : MYSQL
Target Server Version : 50625
File Encoding : 65001
Date: 2016-08-16 09:03:05
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tb_book
-- ----------------------------
DROP TABLE IF EXISTS `tb_book`;
CREATE TABLE `tb_book` (
`book_id` INT(11) NOT NULL,
`book_name` VARCHAR(255) DEFAULT NULL,
`price` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`book_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_book
-- ----------------------------
INSERT INTO `tb_book` VALUES (\'21\', \'管理学\', \'30.00\');
INSERT INTO `tb_book` VALUES (\'22\', \'计算机\', \'50\');
INSERT INTO `tb_book` VALUES (\'23\', \'地理\', \'90\');
INSERT INTO `tb_book` VALUES (\'24\', \'西游记\', \'20\');
-- ----------------------------
-- Table structure for tb_client
-- ----------------------------
DROP TABLE IF EXISTS `tb_client`;
CREATE TABLE `tb_client` (
`client_id` INT(11) NOT NULL,
`client_name` VARCHAR(255) DEFAULT NULL,
`phone` VARCHAR(255) DEFAULT NULL,
`address` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`client_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_client
-- ----------------------------
INSERT INTO `tb_client` VALUES (\'1\', \'a\', \'1234567\', \'北京\');
INSERT INTO `tb_client` VALUES (\'2\', \'b\', \'1234568\', \'上海\');
INSERT INTO `tb_client` VALUES (\'3\', \'c\', \'1234569\', \'太原\');
-- ----------------------------
-- Table structure for tb_client_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_client_order`;
CREATE TABLE `tb_client_order` (
`client_id` INT(11) DEFAULT NULL,
`order_id` INT(11) DEFAULT NULL,
KEY `client_id` (`client_id`),
KEY `order_id` (`order_id`),
CONSTRAINT `client_id` FOREIGN KEY (`client_id`) REFERENCES `tb_client` (`client_id`),
CONSTRAINT `order_id` FOREIGN KEY (`order_id`) REFERENCES `tb_order` (`order_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_client_order
-- ----------------------------
INSERT INTO `tb_client_order` VALUES (\'1\', \'11\');
INSERT INTO `tb_client_order` VALUES (\'1\', \'12\');
INSERT INTO `tb_client_order` VALUES (\'2\', \'13\');
INSERT INTO `tb_client_order` VALUES (\'3\', \'14\');
-- ----------------------------
-- Table structure for tb_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order` (
`order_id` INT(11) NOT NULL,
`book_id` INT(11) DEFAULT NULL,
`count` INT(11) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_order
-- ----------------------------
INSERT INTO `tb_order` VALUES (\'11\', \'21\', \'5\');
INSERT INTO `tb_order` VALUES (\'12\', \'22\', \'2\');
INSERT INTO `tb_order` VALUES (\'13\', \'23\', \'2\');
INSERT INTO `tb_order` VALUES (\'14\', \'24\', \'5\');
1.请写出一条语句，查询出每个客户的所有订单并按地址排序，要求输出格式如下：address client_name phone order_id；
`tb_order` total_price；
2.请写出一条语句，查询出每个客户订购的图书总价，要求输出格式如下：client_name total_price；
3.如果要求每张订单可以包含多种图书，应该如何修改Order表的主键？为了保证每张订单只被一个客户拥有，应该如何在ClientOrder表上增加怎样的约束？（提示：使用索引）。
1. SELECT address ,client_name,phone ,c.order_id FROM tb_book a,tb_client b,tb_client_order c,tb_order d WHERE a.book_id=d.book_id AND d.order_id =c.order_id AND c.client_id=b.client_id ORDER BY CONVERT(address USING gbk);
2.
SELECT b.client_name ,SUM(a.price*d.count) AS total_price FROM tb_book a,tb_client b,tb_client_order c,tb_order d WHERE a.book_id=d.book_id AND d.order_id =c.order_id AND c.client_id=b.client_id
GROUP BY client_name;
3. ALTER TABLE tb_order DROP PRIMARY KEY ,ADD PRIMARY KEY ( order_id , book_id )
ALTER TABLE tb_client_order DROP INDEX client_id ,ADD UNIQUE (client_id)
2. SELECT tt1.client_name,SUM(tt2.count* tt2.price) AS total_price FROM
(SELECT client_name,order_id
FROM tb_client A
INNER JOIN tb_client_order B
ON A.client_id=B.client_id) tt1
INNER JOIN
(SELECT order_id,COUNT,price
FROM tb_order C
INNER JOIN tb_book D
ON C.book_id=D.book_id) tt2
ON tt1.order_id=tt2.order_id
GROUP BY tt1.client_name;