5.23

今日学习内容：

假设某大型航空公司数据库，其关系模式（下划线代表主码） 有：

航班表（航班号，起点， 终点， 机型，价格， 折扣）

乘客表（身份证号，姓名，性别，年龄， 会员等级）

订单表（航班号，身份证号，日期，座位号， 票价）

（说明： 订单表中的航班号是外码，引用航班表中航班号； 订单表中的身份证号是 外码， 引用乘客表中的身份证号。）

完成如下题目（每个题目限用一个 SQL 语句实现）： 

1. 订单表的建表语句
sql
CREATE TABLE 订单表 (
航班号 VARCHAR(10) NOT NULL,
身份证号 CHAR(18) NOT NULL,
日期 DATE NOT NULL,
座位号 INT NOT NULL,
票价 DECIMAL(10, 2),
PRIMARY KEY (航班号, 身份证号, 日期, 座位号),
FOREIGN KEY (航班号) REFERENCES 航班表(航班号),
FOREIGN KEY (身份证号) REFERENCES 乘客表(身份证号)
);
2. 新增航班记录
sql
INSERT INTO 航班表 (航班号, 起点, 终点, 机型, 价格, 折扣)
VALUES ('CA1008', '石家庄', '桂林', '中型机', 1260, 0.8);
3. 降低737机型航班价格
sql
UPDATE 航班表
SET 价格 = 价格 * 0.95
WHERE 机型 = '737';
4. 删除年龄高于80岁的乘客
sql
DELETE FROM 乘客表
WHERE 年龄 > 80;
5. 查询航班号第3位为1的航班信息
sql
SELECT 航班号, 起点, 终点, 机型
FROM 航班表
WHERE 航班号 LIKE '__1%'; -- 注意这里使用的是'__1%'而不是'1___'
6. 查询2022年1月白金会员的出行信息
sql
SELECT p.身份证号, p.姓名, f.起点, f.终点
FROM 乘客表 p
JOIN 订单表 o ON p.身份证号 = o.身份证号
JOIN 航班表 f ON o.航班号 = f.航班号
WHERE p.会员等级 = '白金' AND o.日期 BETWEEN '2022-01-01' AND '2022-01-31'
ORDER BY o.日期 ASC;
7. 按会员等级统计乘客人数
sql
SELECT 会员等级, COUNT(*) AS 人数
FROM 乘客表
GROUP BY 会员等级
ORDER BY 人数 DESC;
8. 创建视图view1
sql
CREATE VIEW view1 AS
SELECT f.航班号, o.身份证号, p.姓名, f.终点
FROM 航班表 f
JOIN 订单表 o ON f.航班号 = o.航班号
JOIN 乘客表 p ON o.身份证号 = p.身份证号
WHERE f.起点 = '北京' AND o.日期 = '2022-05-01';
9. 创建存储过程proc_c
sql
DELIMITER //
CREATE PROCEDURE proc_c(IN id CHAR(18))
BEGIN
SELECT f.航班号, o.日期, f.起点, f.终点
FROM 订单表 o
JOIN 航班表 f ON o.航班号 = f.航班号
WHERE o.身份证号 = id;
END //
DELIMITER ;
10. 创建触发器trig_p
sql
CREATE TRIGGER trig_p
AFTER DELETE ON 航班表
FOR EACH ROW
BEGIN
DELETE FROM 订单表 WHERE 航班号 = OLD.航班号;
END;