作业2

1、创建订单表 order ，包括字段：
订单编号 y0101202009019876
商品名称
商品销售价 （最高价100万）
客户姓名
客户性别
客户地址
送货日期
安装日期
并插入至少两条记录
CREATE TABLE order (
id VARCHAR(17) COMMENT ‘订单编号’,
name VARCHAR(15) COMMENT ‘商品名称’,
price DECIMAL(9,2) COMMENT ‘商品售价’,
custom_name VARCHAR(25) COMMENT ‘客户姓名’,
custom_gender ENUM(‘男’,’女’,’未知’) COMMENT ‘客户性别’,
custom_addr VARCHAR(50) COMMENT ‘客户地址’,
delivery_date DATE COMMENT ‘送货日期’,
installation_date DATE COMMENT ‘安装日期’,
PRIMARY KEY(id)
);
INSERT INTO order VALUES (‘w0101202102220001’, ‘空调’, 5200.00, ‘王五’, ‘男’, ‘上海市虹桥火车站3号候车室’, ‘2021-02-22’, ‘2021-03-08’);
INSERT INTO order VALUES (‘w0101202102230002’, ‘空调5P’, 23500.00, ‘姬峰’, ‘男’, ‘内蒙古呼和浩特市大学西路444号’, ‘2021-02-23’, ‘2021-03-18’);


2、根据课件内容，创建房客表、客房表、房客入住表 。
并插入5个客户信息，2个客房信息，客户要全部入住。
CREATE TABLE custom(
cid VARCHAR(12) COMMENT ‘客户id’,
cname VARCHAR(50) COMMENT ‘客户姓名’,
cidnum CHAR(18) COMMENT ‘客户身份证号’
);
CREATE TABLE room(
rid CHAR(5) COMMENT ‘房间id’,
rtype ENUM(‘标间’,’单间’,’三人间’,’套房’,’其他’) COMMENT ‘房间类型’,
rbednum TINYINT unsigned COMMENT ‘床位数’,
rdes VARCHAR(100) COMMENT ‘房间描述’,
rprice DECIMAL(7,2) COMMENT ‘房间价格’,
rstate ENUM(‘入住’,’待售’,’清扫’,’维修’,’其他’) COMMENT ‘房间状态’,
chknum TINYINT unsigned COMMENT ‘当前入住人数’
);
CREATE TABLE checklog(
cid VARCHAR(12) COMMENT ‘客户id’,
rid CHAR(5) COMMENT ‘房间id’,
chkindate DATE COMMENT ‘入住日期’,
chkoutdate DATE COMMENT ‘结账日期’,
pledge DECIMAL(7,2) COMMENT ‘押金’,
amount DECIMAL(9,2) COMMENT ‘总金额’
);

房间信息：
INSERT INTO room VALUES
(‘18001’,’标间’,2,NULL,280.00,’待售’,0),
(‘06018’,’三人间’,3,NULL,350.00,’待售’,0);
客户信息：
INSERT INTO custom VALUES
(‘1’,’张三’,’323232199001010011’),
(‘2’,’李四’,’24242420001010001X’),
(‘3’,’王五’,’111111196606060071’),
(‘4’,’钱六’,’22222220010101003X’),
(‘5’,’刘七’,’333333199012121234’);

办理入住：
INSERT INTO checklog VALUES
(‘1’,’18001’,’2021-01-02’,NULL,1000.00,840.00),
(‘2’,’18001’,’2021-01-02’,NULL,0.00,0.00);
UPDATE room SET rstate=’入住’,chknum=2 WHERE rid=’18001’;

INSERT INTO checklog VALUES
(‘3’,’06018’,’2021-01-01’,NULL,2000.00,1400.00),
(‘4’,’06018’,’2021-01-01’,NULL,0.00,0.00),
(‘5’,’06018’,’2021-01-01’,NULL,0.00,0.00);
UPDATE room SET rstate=’入住’,chknum=3 WHERE rid=’06018’;

3、根据课件范式举例内容：创建学生表，院系表，学生分数表，
并举例中数据插入表中 。
CREATE TABLE department(
dname VARCHAR(10) PRIMARY KEY,
ddean VARCHAR(15)
);
CREATE TABLE student(
sid CHAR(8) PRIMARY KEY,
sname VARCHAR(15),
sgender ENUM(‘男’,’女’),
dname VARCHAR(10),
CONSTRAINT fk_dname FOREIGN KEY(dname) REFERENCES department(dname)
);
CREATE TABLE score(
sid CHAR(8),
course VARCHAR(10),
score DECIMAL(5,2),
PRIMARY KEY(sid,course),
CONSTRAINT fk_sdi FOREIGN KEY(sid) REFERENCES student(sid)
);

INSERT INTO department VALUES
(‘计科系’,’张宝’),
(‘法律系’,’刘德’);
INSERT INTO student VALUES
(‘20200401’,’李小四’,’男’,’计科系’),
(‘20200402’,’张小兰’,’女’,’计科系’),
(‘20200403’,’张帅八’,’男’,’法律系’),
(‘20200404’,’李小四’,’男’,’法律系’);
INSERT INTO score VALUES
(‘20200401’,’高等数学’,89),
(‘20200401’,’大学英语’,90),
(‘20200401’,’微机原理’,99),
(‘20200402’,’高等数学’,89),
(‘20200402’,’大学英语’,90),
(‘20200402’,’线性代数’,99),
(‘20200403’,’高等数学’,89),
(‘20200403’,’法律基础’,98),
(‘20200404’,’大学英语’,90),
(‘20200404’,’法律基础’,99);