Some common statement notes for table operations in MySQL

0. Create the tables

CREATE TABLE  The name of the table   (the property name   The data type  [ Integrity constraint ] . 
 The property name   The data type  [ Integrity constraint ] . 
 The property name   The data type  [ Integrity constraint ] ) 

Integrity constraints are specific constraints that specify certain fields.
When creating tables using CREATE TABLE, you first select the database using the USE statement. For example, if you have an example database, use USE EXAMPLE to select the database.
Table names cannot use SQL keywords such as create, update, etc. The letters are case-insensitive.
Here is an example of creating a table:

create table example(id int, name varchar(20), sex boolean);

The above example creates the example table with three fields, id integer, name string, and sex Boolean.
The integrity constraint is the restriction on the field. If the integrity constraint is not met, the database system will not perform the user's operation, protecting the integrity of the data in the database.
Here are the integrity constraints:

PRIMARY KEY identifies this property as the primary key of the table, and can only have a tuple corresponding to the identity of 1 FOREIGN KEY identifies this property as the foreign key of the table and is the primary key of a table associated with it NOT NULL identifies that this attribute cannot be null UNIQUE identifies this attribute as having a unique value of 1 AUTO_INCREMENT identifies that the value of this attribute is automatically increased DEFAULT sets the default value for this property

1. View the table structure

describe  The name of the table 

describe can be shortened to desc.
The describe statement lets you see the basic definition of a table. This includes the field name field, the field data type type, whether null is null, whether key is the primary foreign key, the default value default and additional information extra.

2. View the detailed structure of the table

show create table  The name of the table 

show create table  The name of the table  \G

\G Enhanced display viewability

show create table

Statement can view table fields, each field data type, integrity constraints, target storage engine, character encoding, and so on.

3. Change the table name

alter table  The old name of the table  rename [to]  The new name of the table; 

4. Modify the data type of the field

alter table  The name of the table  modify  The property name   Data type; 

5. Change the field name

alter table  The name of the table  change  The old attribute name   A new attribute name   New data types; 

The old property name parameter refers to the field name before modification, the new property name parameter refers to the modified field name, and the data type remains unchanged if no data type is specified.

6. Add fields

alter table  The name of the table  add  The property name 1  The data type  [ Integrity constraint ] [first] after  The property name 2 ; 

The first option means that this field is the first field of the table, and the after attribute name 2 means that it is placed after the specified field, with the default last bit.

7. Delete fields

alter table  The name of the table  drop  The attribute name; 

8. Delete the associated table
(1) Delete the foreign key constraint of the table
A foreign key is a special field that associates a table with its parent table. The foreign key constraint is already set when the table is created. To remove the association between them, use the following statement.

create table example(id int, name varchar(20), sex boolean);

0

The foreign key alias parameter refers to the foreign key identifier set when the table is created.

(2) Delete normal tables that are not associated

create table example(id int, name varchar(20), sex boolean);

1

When you delete a table, all the data in the table is also deleted. It is better to back up the data in the table 1 first when deleting the table.

(3) Delete the parent table associated with other tables
When deleting an associated table, using drop table example1 causes an error because a foreign key depends on the table

For example, an example4 table is created that relies on example1 table, and the foreign key of example4 table stu_id relies on the primary key of example1 table. example1 table is the parent table of example4 table.

If you want to delete the example4 table, you must first remove this dependency. The easiest way is to delete the child table example4 first, and then the parent table example1. However, this may affect other data in the subtables.

Another option is to delete the foreign key constraint of the child table first, and then delete the parent table. This method does not affect the other data of the subtables and can guarantee the security of the database.

For example, the foreign key alias of example4 table is d_fk, and the foreign key constraint of example4 is removed

create table example(id int, name varchar(20), sex boolean);

2

.

You can check with show create table example4 \G to see if it has been deleted.
And then execute it

drop table example1; 

.
Successful execution indicates successful operation.