MySQL table design practice: Create a movie information table and cast list

MySQL table design practice: Create a movie information table and cast list

Introduction: In database design, the creation of tables is a very critical link. This article will take the movie information table and cast list as examples to introduce in detail how to design and create MySQL tables, and attach corresponding code examples.

1. Design and creation of movie information table

The movie information table is used to store movie-related information, including movie name, director, release time, movie type and other fields. The following is the design and creation process of the movie information table. First, we need to select the appropriate field type.

1. Create database

First, we need to create a database to store tables and data. Open the MySQL command line or use a visual tool and enter the following command to create a database named "film_db".

CREATE DATABASE film_db;

2. Create a movie information table

Next, we need to create a movie information table named "movie" and define the attributes of each field. Below is the sample code for the design and creation of a movie information table.

CREATE TABLE movie (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  director VARCHAR(100) NOT NULL,
  release_date DATE NOT NULL,
  genre VARCHAR(100) NOT NULL
);

In the above example, we used field types such as INT, VARCHAR, and DATE to represent the movie ID, movie name, director, release date, and movie type respectively. Among them, the id field is defined as the primary key, and the AUTO_INCREMENT attribute is used to implement the auto-increment function.

2. Cast list design and creation

The cast list is used to store actor-related information, including actor name, gender, date of birth, nationality and other fields. The following is the design and creation process of the cast list. You also need to choose the appropriate field type.

1. Create an actor table

First, we need to create an actor table named "actor" and define the properties of each field. Below is sample code for the design and creation of a cast.

CREATE TABLE actor (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  gender ENUM('Male', 'Female') NOT NULL,
  birthdate DATE NOT NULL,
  nationality VARCHAR(100) NOT NULL
);

In the above example, we used field types such as INT, VARCHAR, ENUM, and DATE to represent actor ID, actor name, gender, date of birth, and nationality respectively. Among them, the id field is defined as the primary key, and the AUTO_INCREMENT attribute is used to implement the auto-increment function. The gender field uses ENUM to define the range of gender values.

3. Summary

Through the above sample code, we can see that the process of creating a table in MySQL is very simple. Just define the table name, fields and field properties, and select the appropriate field type. Reasonable table design can improve the performance and scalability of the database, so business needs and data characteristics need to be fully considered when designing tables.

I hope this article will be helpful to you in MySQL table design. In actual applications, you can also add indexes, constraints, triggers and other functions as needed to improve the table design. Through continuous learning and practice, you will become more proficient in designing and creating MySQL tables.

The above is the detailed content of MySQL table design practice: Create a movie information table and cast list. For more information, please follow other related articles on the PHP Chinese website!