[MySQL] statements related to governing tables and Indexes

[MySQL] statements related to table and index management 1. the createtable statement is used to create a table in MySQL. The syntax is not described here. It is very complicated and can be queried on the official website. Fortunately, we do not need to remember all the options, because most of them use the default option, the following describes several common options. 1) Specify the storage engine

[MySQL] statements related to table and index management 1. the create table statement is used to create a table in MySQL. The syntax is not described here. It is very complicated and can be queried on the official website. Fortunately, we do not need to remember all the options, because most of them use the default option, the following describes several common options. 1) Specify the storage engine

[MySQL] statements related to table and index management
1. Create a table

The create table statement is used to create a table in MySQL. The syntax is not described here. It is very complicated and can be queried on the official website. Fortunately, we do not need to remember all the options, because most of them use the default option, the following describes several common options.

1) Specify the storage engine

The default storage engine is specified by default-storage-engine. If it is not specified, it is MyISAM. If you do not want to use the default storage engine when creating a table, you can use the following statement:

create table table_name ( ... ) engine=engine_name;

2) create a table only when the table does not exist.

create table if not exist table_name ... ;

If the table already exists, skip the preceding statement. Otherwise, create a new table.

3) create a temporary table

MySQL temporary tables have the following features:

-The temporary table is visible only in the session where the table is created and hidden from other sessions. After the session is disconnected, the temporary table is automatically deleted.

-When the temporary table is the same as the normal table, the current session will give priority to the temporary table.

The syntax of the temporary table is as follows:

create temporary table temp_table_name ... ;

4) create a new table from another table

MySQL provides two ways to create a table from another table:

-Create table... like: Creates a new table as a blank copy of the original table. That is to say, the new table is an empty table, but it has all the attributes, indexes, and constraints of the original table (this syntax is very useful, oracle does not have a similar function). Its usage is as follows:

create table new_table_name like old_table_name;insert into new_table_name select * from old_table_name;

-Create table... select: All data column attributes will NOT be copied (the reserved attributes include character set, NOLL or not null, default value, comment), without indexes and constraints.

2. delete a table

Deleting a table is much easier than creating a table.

1) delete a common table

drop table table_name;

2) delete a temporary table

drop temporary table table_name;

3) The table is deleted only when it exists.

drop table if exists table_name;

3. to create an index, you can use the create index statement similar to Oracle to create an index. You can also use the alter table statement unique to MySQL to add an index. In MySQL, we recommend that you use the alter table statement, because it is more flexible and versatile, such:

alter table tbl_name add index index_name (index_cols);alter table tbl_name add unique index_name(index_cols);alter table tbl_name add primary key(index_cols);alter table tbl_name add full text index_name(index_cols);alter table tbl_name add spatial index_name(index_cols);

Here, the primary key and spatial indexes require that the index column be not null, and index_name can be omitted. It is generally NOT recommended to be omitted. If omitted, the system will automatically get a name. This Statement supports adding Multiple indexes in an alter table statement, which is more flexible. You can also create indexes when creating a table, for example:

create table tbl_name(  ...  index index_name (index_cols),  primary key (index_cols),  unique (index_cols),  ...);

4. Deleting an index is similar to creating an index. You can use the drop index or alter table statement to delete an index. For example:

drop index index_name on tbl_name;drop index `primary` on tbl_name;alter table tbl_name drop index index_name;alter table tbl_name drop primary index;

5. Change the table structure 1) Change the column data typeIf you want to change the data type of a column, you can use the change or modify statement as follows:

alter table tbl_name modify col_name mediumint unsigned;alter table tbl_name change old_col_name new_col_name mediumint unsigned;

As you can see from the above, the change function is more powerful. It can not only change the data type of the column, but also rename the column, but it is troublesome to do not want to rename the column in real time, write the column name twice.
2) Change the storage engine of the table

alter table tbl_name engine=engine_name;

3) rename a tableYou can use alter table... rename to or rename table to rename a table, for example:

alter table tbl_name rename to new_tbl_name;rename table tbl_name to new_tbl_name;

Alter table... rename can only rename one table at a time, while rename table can rename multiple tables, for example:

rename table t1 to new_t1, t2 to t1;

If you add a prefix to the table name, you can migrate one table from one database to another.