SQL Rename table Rename analysis instance

Rename table syntax
Rename table Tbl_name to New_tbl_name [, tbl_name2 to New_tbl_name2] ... Use this statement to rename one or more tables.

The rename operation occurs automatically, which means that other threads cannot read any tables while renaming is running. For example, if you have an existing table old_table, you can create another empty table new_table with the same structure, and then replace the existing table with this empty table:

CREATE TABLE new_table (...); Rename table old_table to Backup_table, new_table to old_table; If this statement is used to rename more than one table, the rename operation is done from left to right. If you want to swap the names of two tables, you can do this (assuming there is no table with the name tmp_table):

Rename table old_table to Tmp_table, new_table to Old_table, tmp_table to new_table; As long as two database tutorials are in the same article System, you can also rename the table to move the table from one database to another:

Rename table Current_db.tbl_name to Other_db.tbl_name; When you perform rename, you cannot have a locked table or a transaction that is active. You must also have alter and drop permissions for the original table, as well as create and insert permissions for the new table.

If the MySQL tutorial encounters an error renaming multiple tables, MySQL returns to its original state by renaming all the renamed tables.

Rename table can also be used for views as long as you do not attempt to add the view to another database by renaming it

When you land to MySQL, use test to change the current database, as shown in the following steps:

mysql> Use test

Reading table information for completion of table and column names

You can turn off the feature to get a quicker startup with-a

Database changed

Mysql> Show tables; View table, about the use of Show Command I blog have, please refer to the other

+----------------+

| Tables_in_test |

+----------------+

| Articles |

| Me |

| MyTime |

+----------------+

3 rows in Set (0.01 sec)

mysql> desc mytime;

+-------+-----------+------+-----+-------------------+-------+

| field | Type | null | Key | Default | Extra |

+-------+-----------+------+-----+-------------------+-------+

| F1 | datetime |     Yes | |       null | |

| F2 | Timestamp |     No | |       Current_timestamp | |

+-------+-----------+------+-----+-------------------+-------+

2 rows in Set (0.02 sec)

Mysql> select * from MyTime;

+---------------------+---------------------+

| F1 | F2 |

+---------------------+---------------------+

| 2008-12-18 17:32:48 | 2008-12-18 17:32:48 |

| 1234-12-12 11:23:45 | 0000-00-00 00:00:00 |

| 2034-12-12 11:23:45 | 2008-12-18 17:39:37 |

+---------------------+---------------------+

3 rows in Set (0.03 sec)

mysql> CREATE TABLE My select * from MyTime;

Query OK, 3 rows affected (0.01 sec)

Records:3 duplicates:0 warnings:0

This action is equivalent to copying a table, but it can also be said to be renamed. We got the same table as the original. As follows:

Mysql> select * from I;

+---------------------+---------------------+

| F1 | F2 |

+---------------------+---------------------+

| 2008-12-18 17:32:48 | 2008-12-18 17:32:48 |

| 1234-12-12 11:23:45 | 0000-00-00 00:00:00 |

| 2034-12-12 11:23:45 | 2008-12-18 17:39:37 |

+---------------------+---------------------+

3 Rows in Set (0.00 sec)

At this point we can delete the original mytime table

mysql> drop table mytime;

Query OK, 0 rows Affected (0.00 sec)

We can also use a special SQL statement to implement. That is the Rename command, as follows:

Mysql> Rename table MyTime to my;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from MyTime;

Error 1146 (42S02): Table ' test.mytime ' doesn ' t exist

Mysql> select * from I;

+---------------------+---------------------+

| F1 | F2 |

+---------------------+---------------------+

| 2008-12-18 17:32:48 | 2008-12-18 17:32:48 |

| 1234-12-12 11:23:45 | 0000-00-00 00:00:00 |

| 2034-12-12 11:23:45 | 2008-12-18 17:39:37 |

+---------------------+---------------------+

3 Rows in Set (0.00 sec)

There is the ALTER TABLE name 1 rename to table Name 2, as follows:

Mysql> ALTER TABLE my rename to MyTime;

Query OK, 0 rows affected (0.01 sec)

Some actually instance language names

# #mysql重命名表, establish a foreign key, add, delete, change column name instance

# #增加到某个字段之后
ALTER TABLE tb_nippon_mms_info add province varchar (m) default null after Retcode;
ALTER TABLE Tb_nippon_mms_info add city varchar (m) default null after province;

# #增加到某个字段之前
ALTER TABLE tb_nippon_mms_info add province varchar (m) default null before Retcode;
ALTER TABLE tb_nippon_mms_info add CITY varchar default null before province;

# #删除名字为states的列
ALTER TABLE tb_nine_integral_mo_info drop column states;

# #改变手机号码字段为约束键
ALTER TABLE BUSINESS.TB_NINE_TICKET_POPEDOM change phone phone varchar is not NULL unique;

# #改变列名flag为states
ALTER TABLE TB_NINE_INTEGRAL_MO_INFO change flag States tinyint (1);

– Renaming tables
Rename table T_softwareport to Software_port;

– Establish a foreign key
ALTER TABLE Software_port add constraint fk_software_port_softwareprocessid foreign key (SOFTWAREPROCESSID)
References software_process (ID) on delete restrict on update restrict;