mysql

建表语句：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `Booking`
-- ----------------------------
DROP TABLE IF EXISTS `Booking`;
CREATE TABLE `Booking` (
  `Hotel_No` char(3) NOT NULL,
  `Guest_No` char(6) NOT NULL,
  `Date_From` char(20) NOT NULL,
  `Date_To` char(20) NOT NULL,
  `Room_No` char(4) NOT NULL,
  PRIMARY KEY (`Hotel_No`,`Room_No`,`Date_From`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `Booking`
-- ----------------------------
BEGIN;
INSERT INTO `Booking` VALUES ('H01', 'G01003', '2004-04-25', '2004-05-14', 'R001'), ('H01', 'G02007', '2005-04-11', '2005-09-02', 'R001'), ('H01', 'G02007', '2018-01-11', '2018-01-22', 'R001'), ('H01', 'G02007', '2018-03-11', '2018-04-30', 'R001'), ('H01', 'G02003', '2004-04-24', '2004-04-26', 'R103'), ('H01', 'G01011', '2005-03-11', '2005-04-30', 'R103'), ('H01', 'G01011', '2018-03-11', '2018-04-30', 'R103'), ('H01', 'G01011', '2004-04-25', '2004-04-30', 'R209'), ('H05', 'G02003', '2005-03-12', '2005-05-15', 'R003'), ('H05', 'G01011', '2005-04-15', '2005-04-16', 'R003'), ('H05', 'G01003', '2005-05-05', '2005-05-14', 'R003'), ('H05', 'G02003', '2018-03-12', '2018-05-15', 'R003'), ('H05', 'G02003', '2005-04-14', '2005-04-16', 'R101'), ('H07', 'G02007', '2017-04-15', '2018-05-02', 'R104'), ('H28', 'G01003', '2005-03-11', '2005-04-30', 'R003'), ('H28', 'G01003', '2010-01-01', '2010-01-10', 'R003');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `Guest`
-- ----------------------------
DROP TABLE IF EXISTS `Guest`;
CREATE TABLE `Guest` (
  `Guest_No` char(6) NOT NULL,
  `Guest_Name` varchar(30) NOT NULL,
  `Address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`Guest_No`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `Guest`
-- ----------------------------
BEGIN;
INSERT INTO `Guest` VALUES ('G01003', 'John White', '6 Lawrence Street, Glasgow'), ('G01011', 'Mary Tregear', '5 Tarbot Rd, Aberdeen'), ('G02003', 'Aline Stewart', '64 Fern Dr, London'), ('G02005', 'Mike Ritchie', '18 Tain St, London, W1H 7DL, England'), ('G02007', 'Joe Keogh', null), ('G02008', 'Scott Summers', 'London, W1H 7DL, England'), ('G12345', 'CS 3630', 'London');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `Hotel`
-- ----------------------------
DROP TABLE IF EXISTS `Hotel`;
CREATE TABLE `Hotel` (
  `Hotel_No` char(3) NOT NULL,
  `Name` varchar(15) NOT NULL,
  `Address` varchar(30) NOT NULL,
  PRIMARY KEY (`Hotel_No`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `Hotel`
-- ----------------------------
BEGIN;
INSERT INTO `Hotel` VALUES ('H01', 'Grosvenor', 'London'), ('H05', 'Glasgow', 'London'), ('H07', 'Aberdeen', 'London'), ('H12', 'London', 'Glasgow'), ('H16', 'Aberdeen', 'Glasgow'), ('H24', 'London', 'Aberdeen'), ('H28', 'Glasgow', 'Aberdeen');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;



SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `Room`
-- ----------------------------
DROP TABLE IF EXISTS `Room`;
CREATE TABLE `Room` (
  `Room_No` char(4) NOT NULL,
  `Hotel_No` char(3) NOT NULL,
  `RType` char(6) NOT NULL,
  `Price` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`Hotel_No`,`Room_No`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `Room`
-- ----------------------------
BEGIN;
INSERT INTO `Room` VALUES ('R001', 'H01', 'Single', '30'), ('R002', 'H01', 'Single', '100'), ('R103', 'H01', 'Double', '30'), ('R105', 'H01', 'Double', '119'), ('R209', 'H01', 'Family', '150'), ('R219', 'H01', 'Family', '190'), ('R001', 'H05', 'Double', '39'), ('R003', 'H05', 'Single', '40'), ('R101', 'H05', 'Double', '40'), ('R103', 'H05', 'Single', '55'), ('R104', 'H05', 'Double', '105'), ('R104', 'H07', 'Double', '100'), ('R105', 'H12', 'Double', '45'), ('R201', 'H12', 'Family', '80'), ('R003', 'H28', 'Family', '50');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

拼结果集，逗号分隔：

select hotel_no
,GROUP_CONCAT(date_from)'date_froms'
,GROUP_CONCAT(date_to)'date_tos'
from booking 
group by hotel_no