MySQL影院订票系统

DDL

-- 影院表
CREATE TABLE Theaters (
    TheaterID INT PRIMARY KEY,
    Name VARCHAR(100),
    Address VARCHAR(200)
);


-- 电影表
CREATE TABLE Movies (
    MovieID INT PRIMARY KEY,
    Title VARCHAR(100),
    ReleaseDate DATE
);


-- 放映场次表
CREATE TABLE Showings (
    ShowingID INT PRIMARY KEY,
    TheaterID INT,
    MovieID INT,
    ShowDate DATE,
    StartTime TIME,
    FOREIGN KEY (TheaterID) REFERENCES Theaters(TheaterID),
    FOREIGN KEY (MovieID) REFERENCES Movies(MovieID)
);


-- 座位表
CREATE TABLE Seats (
    SeatID INT PRIMARY KEY,
    TheaterID INT,
    RowNumber INT,
    SeatNumber INT,
    FOREIGN KEY (TheaterID) REFERENCES Theaters(TheaterID)
);


-- 订票表
CREATE TABLE Bookings (
    BookingID INT PRIMARY KEY,
    ShowingID INT,
    SeatID INT,
    CustomerName VARCHAR(100),
    BookingDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ShowingID) REFERENCES Showings(ShowingID),
    FOREIGN KEY (SeatID) REFERENCES Seats(SeatID)
);

2.创建索引（可选，但可以提高查询性能）

CREATE INDEX idx_showing_date_time ON Showings(ShowDate, StartTime);
CREATE INDEX idx_theater_id_seat_id ON Seats(TheaterID, RowNumber, SeatNumber);

3.DML
-- 插入影院数据
INSERT INTO Theaters (TheaterID, Name, Address) VALUES (1, 'Cinema A', 'Address A');
INSERT INTO Theaters (TheaterID, Name, Address) VALUES (2, 'Cinema B', 'Address B');


-- 插入电影数据
INSERT INTO Movies (MovieID, Title, ReleaseDate) VALUES (1, 'Movie 1', '2023-01-01');
INSERT INTO Movies (MovieID, Title, ReleaseDate) VALUES (2, 'Movie 2', '2023-02-01');


-- 插入放映场次数据（需要确保TheaterID和MovieID存在）
INSERT INTO Showings (ShowingID, TheaterID, MovieID, ShowDate, StartTime) VALUES (1, 1, 1, '2023-03-01', '19:00:00');
INSERT INTO Showings (ShowingID, TheaterID, MovieID, ShowDate, StartTime) VALUES (2, 2, 2, '2023-03-02', '20:00:00');


-- 插入座位数据（需要确保TheaterID存在）
INSERT INTO Seats (SeatID, TheaterID, RowNumber, SeatNumber) VALUES (1, 1, 1, 1);
INSERT INTO Seats (SeatID, TheaterID, RowNumber, SeatNumber) VALUES (2, 1, 1, 2);

-- ... 插入更多座位数据 ...


-- 插入订票数据（需要确保ShowingID和SeatID存在）
INSERT INTO Bookings (BookingID, ShowingID, SeatID, CustomerName) VALUES (1, 1, 1, 'John Doe');

-- ... 插入更多订票数据 ...

4.触发器

CREATE TRIGGER trg_after_booking
AFTER INSERT ON Bookings
FOR EACH ROW
BEGIN
    INSERT INTO BookingLogs (LogID, ShowingID, SeatID, BookingDate, Message)
    VALUES (NEW.BookingID, NEW.ShowingID, NEW.

DML

-- 插入影院数据
INSERT INTO Theaters (TheaterID, Name, Address) VALUES (1, '万达影院', '市中心万达四楼');
INSERT INTO Theaters (TheaterID, Name, Address) VALUES (2, '大光明电影院', '光明路3420号');
INSERT INTO Theaters (TheaterID, Name, Address) VALUES (3, '金逸影视', '金逸路1102号');


-- 插入电影数据
INSERT INTO Movies (MovieID, Title, ReleaseDate) VALUES (1, '迪迦终极对决', '2024-01-01');
INSERT INTO Movies (MovieID, Title, ReleaseDate) VALUES (2, '赛罗终极对决', '2024-02-02');
INSERT INTO Movies (MovieID, Title, ReleaseDate) VALUES (3, '泰罗终极对决', '2024-02-03');
INSERT INTO Movies (MovieID, Title, ReleaseDate) VALUES (4, '赛文终极对决', '2024-02-04');
INSERT INTO Movies (MovieID, Title, ReleaseDate) VALUES (5, '樱花树下的托雷基亚', '2024-03-01');


-- 插入放映场次数据（需要确保TheaterID和MovieID存在）
INSERT INTO Showings (ShowingID, TheaterID, MovieID, ShowDate, StartTime) VALUES (1, 1, 1, '2023-03-01', '19:00:00');
INSERT INTO Showings (ShowingID, TheaterID, MovieID, ShowDate, StartTime) VALUES (2, 2, 2, '2023-03-02', '20:00:00');
INSERT INTO Showings (ShowingID, TheaterID, MovieID, ShowDate, StartTime) VALUES (3, 3, 5, '2023-03-02', '22:00:00');


-- 插入座位数据（需要确保TheaterID存在）
INSERT INTO Seats (SeatID, TheaterID, RowNumber, SeatNumber) VALUES (1, 1, 1, 1);
INSERT INTO Seats (SeatID, TheaterID, RowNumber, SeatNumber) VALUES (2, 1, 1, 2);
INSERT INTO Seats (SeatID, TheaterID, RowNumber, SeatNumber) VALUES (3, 2, 1, 1);
INSERT INTO Seats (SeatID, TheaterID, RowNumber, SeatNumber) VALUES (4, 2, 1, 2);

-- ... 插入更多座位数据 ...


-- 插入订票数据（需要确保ShowingID和SeatID存在）
INSERT INTO Bookings (BookingID, ShowingID, SeatID, CustomerName) VALUES (1, 1, 1, '唐飞昊');
INSERT INTO Bookings (BookingID, ShowingID, SeatID, CustomerName) VALUES (2, 2, 1, '吴飞鼠');
INSERT INTO Bookings (BookingID, ShowingID, SeatID, CustomerName) VALUES (3, 3, 3, '和海迪');