Mysql database table creation, query, modification, and deletion commands

Create a database table

It is too common to create a database table. If you use a database, you can use the create statement to create a database table. See the following syntax:

Create table tbl_name (create_definition,...) [type = table_type]
Create_definition: col_name type [not null | null] [default default_value] [auto_increment] [primary_key]

Create table is a fixed keyword followed by the name of the table to be created. It contains the field content. Optional values: null, default, and primary key, whether it is self-increasing or not, for example:

Create table test01_02 (id varchar (50) not null auto_increment primary key,
Name nvarchar (40) null default "002 ",
Age int (5) null default 444 );

Select to create a database table

There are multiple ways to create a database table. The preceding describes one method. Let's look at another commonly used method, which is created using the select statement. See the following syntax:

Create table tb_new_name select * from tb_old_name;

The preceding statement indicates that a copy is copied from the latter table and added to the new database table. The former is a new database table and does not exist before. For example:

Create table test01_03 select * from test01_01;

Display database table information

Sometimes we need to view the relevant information of a database table, such as the structure and column name. Then we need to use the show/describe statement to view the information. See the following syntax:

Show tables [from db_name] [like wild]
Show columns from tbl_name [from db_name] [like wild]
Show index from tbl_name [from db_name]
Show table status [from db_name] [like wild]
{Describe | desc} tbl_name {col_name | wild}

The first syntax indicates: View all the database tables in a database, or use like to fuzzy view a database table. For example:

Show tables from test01;
Show tables from test01 like "% a % ";

The second syntax indicates: view the column attributes in a database table. like is followed by a keyword of the column name. For example:

Show columns from test01_01;
Show columns from test01_01 from test01 like "% n % ";

The third syntax indicates: view the indexes in a database table. For example:

Show index from test01_01 from test01;

Syntax 4: view the status information of all or a table in the database. More information is provided. For example:

Show table status from test01;
Show table status from test01 like "% t % ";

The fifth syntax: view the information of the database table, is another way of show; for example:

Desc test01_01;
Describe test01_01 "% n % ";

Alter table structure

Sometimes you may need to change the structure of the existing table, so the alter table statement will be your proper choice.

Add column

Alter table tbl_name add col_name type

For example, add a weight column:

Alter table test01_01 add weight int;

Modify columns

Alter table test01_01 modify weight varchar (50 );

Delete column

Alter table test01_01 drop weight;

Another common method is as follows:

Rename a column

Alter table test01_01 change weight wei int;
Rename a table

Alter table test01_01 rename test01_04;

Drop delete database table

Drop table: delete one or more database tables. The data and table definitions in all tables are deleted. Therefore, use this command carefully! In MySQL 3.22 or later versions, you can use the keyword if exists to avoid an error that does not exist in the table. Syntax:

Drop table [if exists] tbl_name [, tbl_name,...]

For example, to delete the table test01_04, you must delete multiple tables separated by commas. Example:

Drop table if exists test01_04;