电影院数据库的sql语句

以下是一个简单的电影院数据库的SQL语句示例：

创建表格：

CREATE TABLE movies (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255),
  director VARCHAR(255),
  release_year INT,
  rating DECIMAL(3,1)
);

CREATE TABLE theaters (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  location VARCHAR(255)
);

CREATE TABLE showtimes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  movie_id INT,
  theater_id INT,
  showtime DATETIME,
  FOREIGN KEY (movie_id) REFERENCES movies(id),
  FOREIGN KEY (theater_id) REFERENCES theaters(id)
);

插入数据：

INSERT INTO movies (title, director, release_year, rating)
VALUES ('The Shawshank Redemption', 'Frank Darabont', 1994, 9.3),
       ('The Godfather', 'Francis Ford Coppola', 1972, 9.2),
       ('The Dark Knight', 'Christopher Nolan', 2008, 9.0),
       ('Pulp Fiction', 'Quentin Tarantino', 1994, 8.9);

INSERT INTO theaters (name, location)
VALUES ('AMC Empire 25', 'New York, NY'),
       ('Regal Cinemas LA Live', 'Los Angeles, CA'),
       ('Cineplex Odeon Yonge & Dundas', 'Toronto, ON');

INSERT INTO showtimes (movie_id, theater_id, showtime)
VALUES (1, 1, '2022-01-01 18:00:00'),
       (1, 1, '2022-01-01 21:00:00'),
       (1, 2, '2022-01-02 14:00:00'),
       (2, 1, '2022-01-01 19:30:00'),
       (2, 2, '2022-01-01 20:00:00'),
       (3, 3, '2022-01-02 16:00:00'),
       (4, 1, '2022-01-01 22:00:00');

查询：

-- 所有电影
SELECT * FROM movies;

-- 所有放映时间
SELECT * FROM showtimes;

-- 特定电影放映时间及地点
SELECT m.title, t.name, t.location, s.showtime
FROM movies m
INNER JOIN showtimes s ON m.id = s.movie_id
INNER JOIN theaters t ON s.theater_id = t.id
WHERE m.title = 'The Shawshank Redemption';

-- 在某个时间段内播放的电影
SELECT m.title, t.name, t.location, s.showtime
FROM movies m
INNER JOIN showtimes s ON m.id = s.movie_id
INNER JOIN theaters t ON s.theater_id = t.id
WHERE s.showtime BETWEEN '2022-01-01 00:00:00' AND '2022-01-02 23:59:59';