Copy, modify, and delete a MySQL table

Document directory

• MySQL table replication
• MySQL table modification
• Delete a MySQL table

I didn't want to write this section, because it was very troublesome. There were many examples, which were representative, and I knew everyone was bored. However, this section is frequently used, because MySQL uses the most data operations, including data insertion, data update, data modification, and data deletion. HoweverA table is the place where data is stored. The quality of a table directly affects the performance of your operations.So I think it is necessary to briefly introduce MySQL to copy, modify, and delete tables. This includes copying a table structure to another table, modifying the table structure (for example, adding deletion fields and adding indexes), and deleting useless tables to free up hard disk space.

Select
* From nickname;

/*
Id name desc
1 simaopig this is the name for girl
2 insects zi is not a dragon, but a worm. They do not need to get up early.
3. chongpig indicates that the insects are pig-loving pigs.
*/

Suppose there is a nickname table above. This log is based on this table. You can directly use this as a reference.

MySQL table replication

Create a table copy:

Create a new table with the same structure and data as the table aboveThe following SQL statement can be used to directly copy the table structure of the original table:

/*
Use a subquery to create a table. The table structure is exactly the same as that of nickname, and the data is the same.
*/
Create Table new_nickname
Select * From nickname;

Create only copies of tables containing specific fields:

Sometimes, I need a new table. I only need to have the name and desc fields of the nickname table. I do not need the id field. I also use subqueries, but it is no longer select *, but directly select the desired field.

/*
Because I only want the name and desc Fields, I only need to query the two fields in the original table.
*/
Create Table new_nickname
Select name, DESC
From nickname;

Only create a copy of the table containing the values of the constraints:

For example, what should I do if I only want all the data in the nickname table with the id <3? (I am not sentimental, for example !) At this time, we need to add conditions for subqueries. Do you not forget the where statement?

/*
Condition, as long as the data of ID <3
*/
Create Table new_nickname
Select * From nickname
Where id <3;

Only copy the table structure. We don't want table data:

Sometimes, I just want to copy the table structure of the old table. What about the data in it? I don't want it anymore. That is, I just want to copy the table structure of a table. What should I do at this time? Don't be afraid. I will teach you three ways to solve this problem.

/*
Like copying specific data above, it's just that this condition is disgusting,
When 0 = 1, I only need your data. Obviously, no data will be copied.
*/
Create Table new_nickname
Select * From nickname
Where 0 =
1;

The above method is a coincidence. Because no qualified data meets the restrictions, only the table structure is copied. In addition, MySQL provides a ready-made syntax, that is:Create table new_talbe_name like old_table_name;

/*
Apply the create table like syntax to copy only the table structure
*/
Create Table new_nickname
Like nickname;

The third method is to use the foreign key key_id method to display the SQL statement when creating the table and then copy it,Show create table table_name;

Copy the structure of the old table and add new fields:

People must innovate, and society is improving. If they are still stuck, they will be eliminated. Therefore, we cannot simply copy them, and we must have our own personality, listen to me on my website. For example, when copying a nickname table, I want to create a new field, that is, the nickname creation date field. Well.

/*
We have a personal personality. We have to declare the new field first and then copy the desired field.
*/
Create Table new_nickname
(Create_time date not
Null, status
Enum ('1', '2 '))
Select name,
Desc from nickname; MySQL table modification

MySQL can use the alter table syntax to modify an existing TABLE. It allows you to add, delete, or modify TABLE fields without recreating the entire TABLE. Its complete structure is:Alter table table-name (action field-definition, action field-definition ,....);

The preceding actions include the keyword "ADD", "DROP", "ALTER", or "CHANGE". They are followed by the same field definitions used by the create table command. This definition contains the name of the field to be modified and the field definition composed of the new field name, type, and constraints.

Add a new field to the table:

This time we are no longer doing tabulation. What is so depressing? You can change it directly on the nickname table to add the new field creation time -- create_time for the nickname table.

/*
I believe you do not need to say that you also know that you should use the add in the action mentioned above.
*/
Alter table nickname
Add create_time date not
NULL;

Add a primary key to a table:

We can't just change the field description of the table, but add new fields. We can also add primary keys and indexes for tables. If you don't believe it, just try adding the id of the nickname table as the primary key.

/*
The description of the primary key is primary key.
*/
Alter table nickname
Add primary key (ID );

Modify the field name and type:

Change the field name of the nickname table to nick_name. You have to take the shot, right? Or soft persimmons let others Pinch away.

Alter table nick_name change name nick_name
Varchar (255 );

When modifying the field type, MySQL allows you to replace change with the modify keyword. The only difference between the two is that change must specify the names of the new and old fields at the same time. Because modify can only be used to modify the field type, it cannot change the field name, therefore, there is no concept of a new field name.

/*
Change the ID in the nick_name table from the original int (3) to bigint (18)
*/
Alter table nick_name change ID
Bigint (18)
Auto_increment unique;

Alter table nick_name
Modify ID bigint (18)
Auto_increment unique;

Delete field:

Well, there are not many fields in the past. You can delete the ID for logs. Well.

/*
Smart children now know that action should be dropped.
*/
Alter table nick_name
Drop id;

Rename a table:

Change the name of the nickname table to new_nickname and rename the table name. MySQL provides a Rename to method, which can be used in two ways. See the example.

Alter table nick_name
Rename to new_nickname;

Rename table nick_name
To new_nickname;

Add a foreign key to the table:

MySQL provides a syntax for adding a foreign key to a table: Add foreign key

/*
Add the name in the nick_name table as a foreign key. The foreign key is referenced in the user table.
*/
Alter tabgle nick_name add
Foreign key (name)
References users (user_name)
On delete cascade
On update cascade;

Change table Type:

The previous log introduced the different table types of MySQL tables, briefly discussed their differences, and when to apply which format. Now let's take a look, if you accidentally select the MySQL table type, how to change it?

/*
Change the nick_name table type from InnoDB to MyISAM
*/
Alter table nick_name
Type = myisam;

Set or delete default values for fields:

The default values can be set for table fields. How can I change the values? Suppose I want to set DESC of nick_name to "Girl, I Love You" by default. What should I do?MySQL provides the following methods to SET and delete DEFAULT values: set default and drop default.When I add a new field, for example, I add the create_time field. What should I do if I want to put this field behind the name field?MySQL provides the first and after clauses to control the field location..

/*
Add default values to a table
*/
Alter table nick_name
Alter desc set
Default = 'girl, I Love You ';

/*
Delete table Default Value
*/
Alter table nick_name
Alter DESC drop
Default;

/*
Put the create_time field behind name
*/
Alter talbe nick_name add create_time
Datetime after name;

/*
Put the create_time field in front of DESC
*/
Alter table nick_name
Add create_time datetime first
Desc; Delete MySQL table

This operation is quite simple, but you must be careful, careful, and careful!!! @_@

Drop table table-name;

/*
Delete the nick_name table
*/
Drop table nick_name;

IF the table does NOT exist, the system reports an error. You can use if exists, just like if not exists, when creating a table.

/*
If the nick_name table exists, delete it.
*/
Drop table if
Exists nick_name;

PS: I have finished writing this article. It's so long, so tired. Well ..

Statement: This article uses
BY-NC-SA protocol for authorization |
Accounts
Copy, modify, and delete a MySQL table