Related statements for managing tables and indexes in MySQL

1. Create a table

MySQL Create a table is implemented through CREATE TABLE statement, syntax here is not introduced, very complex, you can go to the official online query, fortunately, we do not need to remember all the options, because most of the default can be used, the following introduction to a few more commonly used options.

1) Specify the storage engine

The default storage engine is specified by Default-storage-engine, and if not specified, it is MyISAM, and if you do not want to use the default storage engine when you build the table, you can do so by using the following statement:

CREATE TABLE table_name (...) engine=engine_name;

2) Create only if the table does not exist

CREATE TABLE if not exist table_name ...;

The preceding statement indicates that if the table already exists, it is skipped, otherwise the new table is created.

3) Create a temporary table

The MySQL temporary table has the following characteristics:

-Temporary tables are visible only in the session where the table was created, are hidden for other sessions, and temporary tables are automatically deleted after the session is disconnected.

-When the table name of a temporary table is the same as a normal table, the current session takes precedence over the temporary table.

The syntax for a 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 new table from another table:

-Create TABLE ... like: Creating a new table is a blank copy of the original table, that is, the new table is an empty table, but it has all the attributes, indexes, constraints of the original table (this syntax is useful, Oracle does not have similar functionality), its use 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 properties are not replicated (the reserved attributes are: Character set, NOLL or not NULL, default value, comment), without indexes, without constraints.

2. Delete Table

Deleting a table is much simpler than creating a table.

1) Delete Ordinary table

DROP TABLE table_name;

2) Delete temporary table

Drop temporary table table_name;

3) Table exists before deletion

DROP table if exists table_name;

3. Create an index

You can use an Oracle-like CREATE INDEX statement to rebuild the indexes, or you can use the MySQL unique ALTER TABLE statement to add the index, in MySQL, we recommend the ALTER TABLE statement because it is more flexible and versatile, such as:

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);

Among them, the primary key and spatial index require that the index column must be not null,index_name to be omitted, generally not recommended to omit, if omitted, the system will automatically take a name.

This statement supports adding multiple indexes to an ALTER TABLE statement, which is more flexible.

When you create a table, you can also create indexes, such as:

CREATE TABLE Tbl_name  
(  
  ...  
  ). Index Index_name (index_cols),  
  primary KEY (Index_cols),  
  Unique (index_cols),  
  ...  
);

4. Delete Index

Similar to creating an index, deleting an index can be done with either DROP INDEX or ALTER TABLE statements, such as:

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 data type of the column

If you want to change the data type of the column, you can use the changing or modify words 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 alter the data type of the column, but also rename the column, but it is troublesome to do not want to rename immediately, but also to write the column name two times.

2) Change the default value of the column

MySQL can set or remove the default value of a column through the ALTER COLUMN statement, which directly modifies the. frm file, so it's very fast.

Alter TABLE MyTable ALTER COLUMN foo SET DEFAULT ' bar ';

Alter TABLE MyTable ALTER COLUMN foo DROP DEFAULT;

3 Change the table's storage engine

ALTER TABLE Tbl_name Engine=engine_name;

4) Renaming tables

Renaming tables can use ALTER TABLE ... rename to or rename table, such as:

ALTER TABLE tbl_name Rename to New_tbl_name;

Rename table Tbl_name to New_tbl_name;

ALTER TABLE ... rename only one table can be renamed at a time, and rename table renames multiple tables, such as:

Rename table T1 to new_t1, T2 to T1;

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

5) Add a new column

ALTER TABLE tbl_name ADD column_name VARCHAR () NOT NULL default ... COMMENT ' ... ';

6) Adding constraints

ALTER TABLE tbl_name ADD UNIQUE (col_names);

ALTER TABLE tbl_name ADD PRIMARY KEY (col_names);

7) Add self growth

Alter table tbl_name change ID ID int (a) not NULL auto_increment;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/