影院订票系统

admin管理员组

文章数量:1579086

1、绘制ER图 

 2、创建【films】数据库语句。

CREATE DATABASE `films` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

数据库通用字符集与排序规则

字符集：utf8

排序规则：utf8_general_ci

3、数据库DDL

-- 用户表（user）
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(50),
    phone VARCHAR(20)
);

   
-- 电影表（movie）
CREATE TABLE movie (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    duration INT NOT NULL,
    release_date DATE,
    director VARCHAR(50),
    actors VARCHAR(200),
    description TEXT
);

   
-- 影院表（cinema）
CREATE TABLE cinema (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200),
    phone VARCHAR(20)
);

  
-- 放映厅表（hall）
CREATE TABLE hall (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cinema_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    seat_count INT NOT NULL,
    FOREIGN KEY (cinema_id) REFERENCES cinema(id)
);

-- 放映计划表（schedule）
CREATE TABLE schedule (
    id INT PRIMARY KEY AUTO_INCREMENT,
    movie_id INT NOT NULL,
    hall_id INT NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (movie_id) REFERENCES movie(id),
    FOREIGN KEY (hall_id) REFERENCES hall(id)
);

  
-- 订单表（order）
CREATE TABLE `order` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    schedule_id INT NOT NULL,
    seat_number VARCHAR(20) NOT NULL,
    order_time DATETIME NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    status ENUM('待支付', '已支付', '已取消') NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (schedule_id) REFERENCES schedule(id)
);

-- 评论表（comment）
CREATE TABLE comment (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    movie_id INT NOT NULL,
    content TEXT NOT NULL,
    rating DECIMAL(3, 1) NOT NULL,
    comment_time DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (movie_id) REFERENCES movie(id)
);

4、插入数据DML

-- 插入用户数据
INSERT INTO user (username, password, email, phone) VALUES
('john_doe', 'pass1234', 'john@example', '1234567890'),
('jane_smith', 'jane123', 'jane@example', '0987654321'),
('alice_jones', 'alice987', 'alice@jones', '5556667777'),
('bob_brown', 'bob2024', 'bob@brown', '6665554444'),
('charlie_lee', 'charlie789', 'charlie@lee', '7778889999');


-- 插入影片数据
INSERT INTO movie (name, duration, release_date, director, actors, description) VALUES
('Inception', 148, '2010-07-16', 'Christopher Nolan', 'Leonardo DiCaprio, Joseph Gordon-Levitt', 'A thief who steals corporate secrets through the use of dream-sharing technology'),
('The Matrix', 136, '1999-03-31', 'Lana Wachowski, Lilly Wachowski', 'Keanu Reeves, Laurence Fishburne', 'A computer hacker learns about the true nature of reality and his role in the war against its controllers'),
('Interstellar', 169, '2014-11-07', 'Christopher Nolan', 'Matthew McConaughey, Anne Hathaway', 'A team of explorers travel through a wormhole in space in an attempt to ensure humanity\'s survival'),
('The Godfather', 175, '1972-03-24', 'Francis Ford Coppola', 'Marlon Brando, Al Pacino', 'The aging patriarch of an organized crime dynasty transfers control of his clandestine empire to his reluctant son'),
('Pulp Fiction', 154, '1994-05-21', 'Quentin Tarantino', 'John Travolta, Uma Thurman', 'The lives of two mob hitmen, a boxer, a gangster\'s wife, and a pair of diner bandits intertwine in four tales of violence and redemption');


-- 插入影院数据
INSERT INTO cinema (name, address, phone) VALUES
('Cineplex Odeon', '123 Movie Blvd, Cinema City', '555-1234'),
('Regal Cinemas', '456 Film St, Movie Town', '666-2345'),
('AMC Theatres', '789 Show Rd, Screen Village', '777-3456'),
('Vue Cinemas', '321 Screen Ave, Film District', '888-4567'),
('Bow Tie Cinemas', '654 Display Ln, Movie Park', '999-5678');

-- 插入放映厅数据
INSERT INTO hall (cinema_id, name, seat_count) VALUES
(1, 'Screen 1', 150),
(1, 'Screen 2', 120),
(2, 'IMAX 1', 200),
(3, 'Dolby Cinema', 100),
(4, 'Screen 3', 180);


-- 插入放映计划数据
INSERT INTO schedule (movie_id, hall_id, start_time, end_time, price) VALUES
(1, 1, '2024-07-01 19:00:00', '2024-07-01 21:28:00', 12.99),
(2, 2, '2024-07-02 20:00:00', '2024-07-02 22:14:00', 15.99),
(3, 3, '2024-07-03 18:30:00', '2024-07-03 21:59:00', 10.99),
(4, 4, '2024-07-04 14:00:00', '2024-07-04 16:54:00', 8.99),
(5, 5, '2024-07-05 17:00:00', '2024-07-05 19:34:00', 9.99);


-- 插入订单数据（假设user1购买了movie1的放映计划）
INSERT INTO `order` (user_id, schedule_id, seat_number, order_time, total_price, status) VALUES
(1, 1, 'A1', '2024-06-25 10:00:00', 12.99, '待支付'),
(2, 2, 'B2', '2024-06-25 11:00:00', 15.99, '已支付'),
(3, 3, 'C3', '2024-06-25 12:00:00', 10.99, '待支付'),
(4, 4, 'D4', '2024-06-25 13:00:00', 8.99, '已取消'),
(5, 5, 'E5', '2024-06-25 14:00:00', 9.99, '已支付');

-- 插入顾客评论数据
INSERT INTO comment (user_id, movie_id, content, rating, comment_time) VALUES
(1, 1, 'An amazing movie with a complex and intriguing plot.', 4.5, '2024-06-25 15:00:00'),
(2, 2, 'A classic that revolutionized the sci-fi genre.', 5.0, '2024-06-25 16:00:00'),
(3, 3, 'Stunning visuals and a compelling story.', 4.8, '2024-06-25 17:00:00'),
(4, 4, 'A timeless tale of family and power.', 4.9, '2024-06-25 18:00:00'),
(5, 5, 'Tarantino at his best, with memorable dialogues and scenes.', 4.7, '2024-06-25 19:00:00');

5、基础查询 

（1）查询用户信息，仅显示用户的姓名与手机号

SELECT username, phone FROM user;

 （2）根据商品名称进行模糊查询，模糊查询需要可以走索引，需要给出explain语句。

EXPLAIN SELECT * FROM movie WHERE name LIKE '%关键字%';

 (3)统计用户订单信息，查询所有用户的下单数量，并进行倒序排列。

SELECT u.username, COUNT(o.id) AS order_count FROM user u LEFT JOIN `order` o ON u.id = o.user_id GROUP BY u.id ORDER BY order_count DESC;

6、复杂查询

（1）查询用户的基本信息，评论信息。 

SELECT user.username, comment.content, 
comment.rating FROM user JOIN comment ON user.id = comment.user_id;

（2）查看订单中下单最多的产品对应的类别

SELECT movie.name FROM movie JOIN schedule ON movie.id = schedule.movie_id JOIN `order` ON schedule.id = `order`.schedule_id GROUP BY movie.id ORDER BY COUNT(`order`.id) DESC LIMIT 1;

（3）查询下单总金额最多的用户，并查询用户的全部信息。

SELECT user.* FROM user JOIN `order` ON user.id = `order`.user_id GROUP BY user.id ORDER BY SUM(`order`.total_price) DESC LIMIT 1;

7、触发器

(1)用户表添加语句添加触发器，要求在添加用户信息时同时初始化订单表数据，初始数量为0。

DELIMITER //
CREATE TRIGGER init_order_after_user_insert
AFTER INSERT
ON user FOR EACH ROW
BEGIN
    INSERT INTO `order` (user_id, schedule_id, seat_number, order_time, total_price, status)
    VALUES (NEW.id, NULL, NULL, NULL, 0, '待支付');
END;
//
DELIMITER ;

 

（2）电影表修改语句添加触发器，要求在修改电影售价时不允许上下浮动超过10%。

DELIMITER //
CREATE TRIGGER check_movie_price_change BEFORE UPDATE ON movie FOR EACH ROW 
BEGIN 
IF ABS(NEW.price - OLD.price) / OLD.price > 0.1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '电影售价不允许上下浮动超过10%'; 
END IF; 
END;
//
DELIMITER ;

 

（3）订单表删除语句添加触发器，要求在删除订单信息时先删除订单详情表中的订单信息。

DELIMITER //
CREATE TRIGGER delete_order_details BEFORE DELETE ON `order` FOR EACH ROW 
BEGIN
 DELETE FROM order_details WHERE order_id = OLD.id; 
END;
//
DELIMITER ;

 

 8、存储过程

（1）添加一个用户下订单的存储过程，存储过程名称叫做【create_order_infos()】

DELIMITER //
CREATE PROCEDURE create_order_infos(IN user_id INT, IN schedule_id INT, IN seat_number VARCHAR(20), 
IN total_price DECIMAL(10, 2))BEGIN    INSERT INTO `order` (user_id, schedule_id, seat_number, order_time, total_price, status) 
VALUES (user_id, schedule_id, seat_number, NOW(), total_price, '待支付');
END;
//
DELIMITER ;

 

 

 （2）要求传入创建订单所必须的参数内容，例如：用户编号、电影编号、购买数量等信息

DELIMITER //
CREATE PROCEDURE create_order(IN user_id INT, IN movie_id INT, IN seat_number VARCHAR(20), IN total_price DECIMAL(10, 2))
BEGIN   
 INSERT INTO `order` (user_id, schedule_id, seat_number, order_time, total_price, status) VALUES (user_id, movie_id, seat_number, NOW(), total_price, '待支付');
END;
//
DELIMITER ;

 

（3）需要根据传入的信息插入【放映厅表】、【订单表】、【评论表】信息，修改【放映计划表】、【电影表】。

-- 插入放映厅信息（hall）
-- 假设影院ID为1
INSERT INTO hall (cinema_id, name, seat_count) VALUES
(1, 'VIP Hall', 50);
-- 插入订单信息（order）
-- 假设用户ID为1，放映计划ID为1，座位号为'A1'
INSERT INTO `order` (user_id, schedule_id, seat_number, order_time, total_price, status) VALUES
(1, 1, 'A1', NOW(), 25.00, '待支付');
-- 插入评论信息（comment）
-- 假设用户ID为1，电影ID为1
INSERT INTO comment (user_id, movie_id, content, rating, comment_time) VALUES
(1, 1, 'This is an outstanding movie with a brilliant plot and excellent acting.', 4.8, NOW());
-- 修改放映计划信息（schedule）
-- 假设放映计划ID为1，需要修改价格和状态
UPDATE schedule
SET price = 30.00, 
    end_time = DATE_ADD(end_time, INTERVAL 30 MINUTE)
WHERE id = 1;
-- 修改电影信息（movie）
-- 假设电影ID为1，需要更新导演和演员信息
UPDATE movie
SET director = 'New Director Name',
    actors = 'New Actor1, New Actor2'
WHERE id = 1;

 

 

本文标签： 订票影院系统