第二题

/
创建房客表结构
/
| tenant | CREATE TABLE tenant (
tenant_id int DEFAULT NULL COMMENT ‘房客编号’,
tenant_name varchar(200) DEFAULT NULL COMMENT ‘房客姓名’,
ID_number char(20) DEFAULT NULL COMMENT ‘身份证号’,
deposit decimal(10,2) DEFAULT NULL COMMENT ‘押金’,
check_in_date datetime DEFAULT NULL COMMENT ‘入住时间’,
chck_out_date datetime DEFAULT NULL COMMENT ‘退房时间’,
price decimal(10,2) DEFAULT NULL COMMENT ‘价格’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
/
插入5个客户信息
/
insert into tenant values
-> (
-> 1,
-> ‘马云云’,
-> ‘370125199903045566’,
-> 200,
-> ‘2021/03/27/15/15/45’,
-> ‘2021/03/28/07/34/56’,
-> 599
-> );

insert into tenant values
-> (
-> 2,
-> ‘马画藤’,
-> ‘370125199845678899’,
-> 300,
-> ‘2021/03/28/16/45/45’,
-> ‘2021/03/29/08/23/34’,
-> 699
-> );

insert into tenant values
-> (
-> 3,
-> ‘王剑林’,
-> ‘370125199803048899’,
-> 200,
-> ‘2021/03/27/19/40/48’,
-> ‘2021/03/28/09/12/24’,
-> 599
-> );

insert into tenant values
-> (
-> 4,
-> ‘黄整’,
-> ‘370125199609146699’,
-> 300,
-> ‘2021/03/28/18/29/49’,
-> ‘2021/03/29/19/46/32’,
-> 699
-> );

insert into tenant values
-> (
-> 5,
-> ‘王行’,
-> ‘370125199509155566’,
-> 200,
-> ‘2021/03/27/17/29/49’,
-> ‘2021/03/28/07/38/50’,
-> 599
-> );
/
输出客户信息表
/

/
创建客房表结构
/
CREATE TABLE room (
room_id char(10) DEFAULT NULL COMMENT ‘客房号’,
describe varchar(100) DEFAULT NULL COMMENT ‘客房描述’,
type enum(‘双人房’,’大床房’) DEFAULT NULL COMMENT ‘客房类型’,
status enum(‘空房’,’满房’) DEFAULT NULL COMMENT ‘客房状态’,
bed_num int DEFAULT NULL COMMENT ‘床位数’,
room_type int DEFAULT NULL COMMENT ‘入住人数’,
price decimal(10,2) DEFAULT NULL COMMENT ‘价格’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/
插入两个房客信息
/
insert into room values
-> (
-> ‘2088’,
-> ‘商务双人房’,
-> ‘双人房’,
-> ‘满房’,
-> 2,
-> 2,
-> 699
-> );
insert into room values
-> (
-> ‘2077’,
-> ‘豪华大床房’,
-> ‘大床房’,
-> ‘满房’,
-> 1,
-> 1,
-> 599
-> );
/
输出客房表
/

/
创建房客入住表结构
/
CREATE TABLE tenant_room (
tenant_id int DEFAULT NULL COMMENT ‘房客编号’,
room_id char(10) DEFAULT NULL COMMENT ‘客房号’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/
插入5位房客的入住信息
/
insert into tenant_room values(1,’2077’);
insert into tenant_room values(2,’2088’);
insert into tenant_room values(3,’2077’);
insert into tenant_room values(4,’2077’);
insert into tenant_room values(5,’2088’);
/
输出房客入住表
*/