Mysql table Empty, delete and modify the operation detailed _mysql

First, clear the MySQL table data

Delete from table name;
TRUNCATE TABLE name;
Delete statements without a where parameter can delete all content in the MySQL table, and you can also empty the MySQL table with TRUNCATE table.
Efficiency truncate faster than delete, but truncate deleted after the MySQL log does not record, can not recover data.
The effect of the delete is a bit like deleting all the records in the MySQL table to the end of the line.
The truncate is equivalent to preserving the MySQL table structure, recreating the table, and all States are equivalent to the new table.

Delete some data from the table

Delete from command format: Delete from table name where expression

For example, delete a record in table MyClass that is numbered 1:

Copy Code code as follows:

Mysql> Delete from MyClass where id=1;

Third, modify the table
1. Select Database

>use database name;

2. Query all data tables
>show tables;

3. Query the field information of the table
>DESC table name;

3.1. Modify the field type of a table and specify null or Non-empty
>alter Table name change field Name field Name field type [Allow Non-null];
>alter Table name Modify field Name field type [Allow Non-null];

3.2. Modify the field name of a table and specify null or Non-empty
>alter Table name change field original Name field new Name field type [Allow Non-null];

For example:
Modify the field birth in the table Expert_info to allow it to be empty

Copy Code code as follows:

>alter table Expert_info Change birth birth varchar (a) null;

1. Add one field (one column)

ALTER TABLE table_name ADD COLUMN column_name type default value; Type refers to the kind of field that is the default value of the field

For example:

Copy Code code as follows:

ALTER TABLE mybook add column publish_house varchar (a) default ";

2. Change the name of a field (you can also change the type and default values)

ALTER TABLE table_name change SORCE_COL_NAME dest_col_name type default value; Source_col_name refers to the original field name, Dest_col_name

The name of the field after the change

For example:

Copy Code code as follows:

ALTER TABLE board_info change ismobile istelphone int (3) unsigned default 1;

3. Change the default value of a field

ALTER TABLE table_name ALTER COLUMN_NAME SET default value;

For example:

Copy Code code as follows:

ALTER TABLE book ALTER flag set default ' 0′;

4. Change the data type of a field

ALTER TABLE table_name Change column column_name column_name type;

For example:

Copy Code code as follows:

ALTER TABLE userinfo change column username username varchar (20);

5. Add a column to a table as the primary key

ALTER TABLE table_name ADD COLUMN column_name type auto_increment PRIMARY KEY;

For example:

Copy Code code as follows:

ALTER TABLE book add column ID int (a) auto_increment PRIMARY KEY;

6. Backup of a table in the database, enter at the command line:

MYSQLDUMP-U ROOT-P database_name table_name > Bak_file_name

For example:

Copy Code code as follows:

Mysqldump-u root-p f_info user_info > User_info.dat

7. Export Data

Select_statment into outfile "Dest_file";

For example:

Copy Code code as follows:

Select Cooperatecode,createtime from publish limit to outfile "/home/mzc/temp/tempbad.txt";

8. Import data

Load data infile "file_name" into table table_name;

For example:

Copy Code code as follows:

Load data infile "/home/mzc/temp/tempbad.txt" into table pad;

9. Insert the data from the two table to the other table. The following example shows the values of the COM1 fields in the COM2 and T2 tables in the T1 table are spliced and inserted into the TX table corresponding to the

In the field.

For example:

Copy Code code as follows:

Insert INTO TX select T1.com1,concat (T1.COM2,T2.COM1) from T1,t2;

10, delete the field

ALTER TABLE Form1 drop column name;