How to create and modify table structures in MySQL using SQL statements?

How to use SQL statements to create and modify table structures in MySQL?

As a relational database management system, MySQL provides a large number of SQL statements to create and modify table structures. This article will introduce in detail how to use SQL statements to create and modify table structures in MySQL, and provide specific code examples.

1. Create table structure
Creating a table structure in MySQL requires the use of the CREATE TABLE statement. Here is an example:

CREATE TABLE students (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT(3) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    PRIMARY KEY (id)
);

The above code creates a table named students. The table includes four fields: id, name, age and gender. Among them, the id field is of type INT, which is not allowed to be empty, and will grow automatically; the name field is of type VARCHAR, which is not allowed to be empty; the age field is of type INT, which is not allowed to be empty; the gender field is of type ENUM, which can only be 'male'. ' or 'Female', not allowed to be empty. PRIMARY KEY keyword is used to specify the primary key.

2. Modify the table structure
To modify the table structure in MySQL, you need to use the ALTER TABLE statement. The following are several common table structure modification operations and their code examples:

1. Add fields
   Use the ADD COLUMN clause of the ALTER TABLE statement to add new fields to the table. The sample code is as follows:

ALTER TABLE students
ADD COLUMN birthday DATE NOT NULL AFTER name;

The above code adds a field named birthday in the students table, the type is DATE, empty is not allowed, and is inserted after the name field.

2. Modify fields
   Use the MODIFY COLUMN clause of the ALTER TABLE statement to modify the definition of existing fields in the table. The sample code is as follows:

ALTER TABLE students
MODIFY COLUMN age INT(2) NOT NULL;

The above code changes the type of the age field in the students table to INT(2), and does not allow it to be empty.

3. Delete fields
   Use the DROP COLUMN clause of the ALTER TABLE statement to delete fields in the table. The sample code is as follows:

ALTER TABLE students
DROP COLUMN gender;

The above code deletes the gender field in the students table.

4. Modify the table name
   Use the RENAME TO clause of the ALTER TABLE statement to modify the name of the table. The sample code is as follows:

ALTER TABLE students
RENAME TO new_students;

The above code changes the name of the students table to new_students.

3. Summary
This article introduces how to use SQL statements to create and modify table structures in MySQL. Table structures can be easily created and modified through the CREATE TABLE and ALTER TABLE statements. The above example code is only for reference and needs to be adjusted according to specific needs when used in practice. I hope this article can help you learn and use SQL statements in MySQL.

The above is the detailed content of How to create and modify table structures in MySQL using SQL statements?. For more information, please follow other related articles on the PHP Chinese website!