SQL rename table rename analysis instance

Rename table syntax
Rename table tbl_name to new_tbl_name [, tbl_name2 to new_tbl_name2]... this statement is used to rename one or more tables.

The RENAME operation is automatically performed, which means that when the rename is running, other threads cannot read any table. 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 original 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 multiple tables, the rename operation starts from left to right. If you want to exchange the names of two tables, you can do this (assuming there is no table named tmp_table ):

Rename table old_table to tmp_table, new_table to old_table, tmp_table to new_table; as long as the two database tutorials are located in the same file system, you can also rename the tables, move a table from one database to another:

Rename table current_db.tbl_name to other_db.tbl_name; when you execute rename, you cannot have a locked table or a transaction in the active state. You must also have the alter and drop permissions for the original table and the create and insert permissions for the new table.

If an error occurs when renaming multiple tables in the mysql tutorial, mysql will reverse rename all renamed tables and return them to the original state.

As long as you do not try to add the view to another database by renaming, the rename table can also be used for the view

After connecting to mysql from land, use test changes the current database. The specific steps are as follows:

Mysql> use test

Reading table information for completion of table and column names

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

Database changed

Mysql> show tables; // view the table. For more information about how to use the show command, see

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

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

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

| 17:32:48 | 17:32:48 |

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

| 2034-12-12 11:23:45 | 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 operation is equivalent to copying a table, but you can also rename it. We get the same table as the original one. As follows:

Mysql> select * from my;

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

| F1 | f2 |

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

| 17:32:48 | 17:32:48 |

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

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

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

3 rows in set (0.00 sec)

In this case, we can delete the original mytime table.

Mysql> drop table mytime;

Query OK, 0 rows affected (0.00 sec)

We can also use specialized SQL statements. The rename Command is 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 my;

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

| F1 | f2 |

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

| 17:32:48 | 17:32:48 |

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

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

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

3 rows in set (0.00 sec)

In addition, alter table name 1 rename to table name 2 is shown as follows:

Mysql> alter table my rename to mytime;

Query OK, 0 rows affected (0.01 sec)

Instance language names

# Mysql RENAME the table, create a foreign key, add, delete, and change the column name instance

# After adding a field
Alter table tb_nippon_mms_info add province varchar (50) default null after retcode;
Alter table tb_nippon_mms_info add city varchar (50) default null after province;

# Before adding a field
Alter table tb_nippon_mms_info add province varchar (50) default null before retcode;
Alter table tb_nippon_mms_info add city varchar (50) default null before province;

# Delete a column named states
Alter table tb_nine_integral_mo_info drop column states;

# Change the mobile phone number field to the bound key
Alter table business. tb_nine_ticket_popedom change phone varchar (50) not null unique;

# Change the column name flag to states
Alter table tb_nine_integral_mo_info change flag states tinyint (1 );

-Rename a table
Rename table t_softwareport to software_port;

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