Mysql drop table, create index, drop index

Mysql drop table, create index, drop index
DROP [TEMPORARY] TABLE [if exists] tbl_name [, tbl_name,...]
[RESTRICT | CASCADE]
Drop table removes one or more tables. All data and table definitions are removed, so be sure to use this command with caution!

In MySQL 3.22 or later versions, you can use the keyword if exists to prevent errors when the table does not exist. In 4.1, when if exists is used, you will get a NOTE for all tables that do not exist. View section 4.5.6.9 show warnings | ERRORS.

RESTRICT and CASCADE are allowed for easier porting. Currently, they do not have any effect.

Note: drop table will automatically commit the current active transaction (unless you are using MySQL 4.1 and use the TEMPORARY keyword ).

Option TEMPORARY is ignored in 4.0. In 4.1, this option works as follows:

Only remove temporary tables.
Does not end a running transaction.
The access permission is not checked.
Using TEMPORARY is a safe way to avoid accidental removal of a real table.

6.5.7 create index syntax

CREATE [UNIQUE | FULLTEXT] INDEX index_name
ON tbl_name (col_name [(length)],...)
The create index syntax is used in versions earlier than MySQL 3.22. In versions 3.22 or later, the create index is mapped to an alter table statement to CREATE an INDEX. View section 6.5.4 alter table syntax.

Generally, when you use create table to CREATE a TABLE, you CREATE all indexes of the TABLE. View section 6.5.3 create table syntax. Create index allows you to add an INDEX to an existing table.

Create a multi-column index for the column list in the format of (col1, col2. The index value is connected by the given column value.

For CHAR and VARCHAR columns, you can use the col_name (length) syntax to create an index using only one column. (For BLOB and TEXT columns, the length is required .) The statement here shows that an index is created using the first 10 characters in the name column:

Mysql> create index part_of_name ON customer (name (10 ));
Because most names are generally different from the first 10 characters, this index should not be slower than the index created with the entire name. Similarly, the index file created with some column values is smaller, which saves a lot of disk space and accelerates the INSERT operation!

Note: If you are using MySQL 3.23.2 or an updated version of the MyISAM Table type, you can create an index on a column with a NULL value, and create an index on a BLOB/TEXT column.

For more information about how to use indexes in MySQL, see section 5.4.3.

FULLTEXT indexes can only be used to index VARCHAR and TEXT columns, and can only be used in MyISAM tables. FULLTEXT indexes can be used in MySQL 3.23.23 and later versions. See section 6.8 MySQL full-text search.

6.5.8 drop index syntax

Drop index index_name ON tbl_name
Drop index removes an INDEX named index_name from the table tbl_name. In earlier versions of MySQL 3.22, do not do anything. In versions 3.22 or later, drop index is mapped to an alter table statement to remove the INDEX.