You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
32 lines
1.1 KiB
32 lines
1.1 KiB
-- 学生信息视图
|
|
CREATE VIEW StudentInfoView AS
|
|
SELECT s.student_id, s.name, p.phone, p.email
|
|
FROM student s
|
|
JOIN personal_info p ON s.personal_info_id = p.info_id;
|
|
|
|
-- 教练信息视图
|
|
CREATE VIEW CoachInfoView AS
|
|
SELECT c.coach_id, c.name, p.phone, p.email
|
|
FROM coach c
|
|
JOIN personal_info p ON c.personal_info_id = p.info_id;
|
|
|
|
-- 预约详情视图
|
|
CREATE VIEW AppointmentDetailView AS
|
|
SELECT a.appointment_id, s.name AS student_name, c.name AS coach_name, v.name AS venue_name, a.period, a.status
|
|
FROM appointment a
|
|
JOIN student s ON a.student_id = s.student_id
|
|
JOIN coach c ON a.coach_id = c.coach_id
|
|
JOIN venue v ON a.venue_id = v.venue_id;
|
|
|
|
-- 训练记录视图
|
|
CREATE VIEW TrainingRecordView AS
|
|
SELECT t.record_id, s.name AS student_name, c.name AS coach_name, t.feedback
|
|
FROM training_record t
|
|
JOIN student s ON t.student_id = s.student_id
|
|
JOIN coach c ON t.coach_id = c.coach_id;
|
|
|
|
-- 发票详情视图
|
|
CREATE VIEW InvoiceDetailView AS
|
|
SELECT i.invoice_id, i.amount, i.date, a.student_id, a.coach_id, a.venue_id
|
|
FROM invoice i
|
|
JOIN appointment a ON i.appointment_id = a.appointment_id; |