Clear, delete, and modify MySQL table instructions

Personal Original: http://www.phpthinking.com/archives/365

First, clear the MySQL table data

Delete from table name;
TRUNCATE TABLE name;
The DELETE statement without the Where parameter removes everything from the MySQL table, and you can also empty all the contents of the MySQL table using TRUNCATE table.
The efficiency of truncate is faster than delete, but the MySQL log is not recorded after Truncate deleted, and data cannot be recovered.
The effect of delete is a bit like removing all records from a MySQL table to the end of a delete,
The truncate is equivalent to preserving the structure of the MySQL table and recreating the table, all of which are equivalent to the new table.

Ii. deleting some data from a table

Delete from command format: Delete from table name where expression

For example, delete the record numbered 1 in table MyClass:
Mysql> Delete from MyClass where id=1;
Third, modify the table
1. Select a database

>use database name;

2. Querying all data Sheets
>show tables;

3. Querying the table for field information
>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 [whether non-null allowed];
>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 [whether non-null allowed];

For example:
Modify the field birth in table Expert_info to allow it to be empty
>alter table Expert_info Change birth birth varchar () null;

1. Add a field (one column)

ALTER TABLE table_name ADD COLUMN column_name type default value; Type refers to the field, and value refers to the default value of the field.

For example: ALTER TABLE mybook add column Publish_house varchar (TEN) default ";

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

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

Refers to the changed field name

For example: 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: 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: 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: ALTER TABLE book add column ID int (TEN) auto_increment PRIMARY KEY;

6. A backup of a table in the database, entered at the command line:

MYSQLDUMP-U ROOT-P database_name table_name > Bak_file_name

Example: Mysqldump-u root-p f_info user_info > User_info.dat
7. Exporting data

Select_statment into outfile "Dest_file";

For example: Select Cooperatecode,createtime from publish limit ten into outfile "/home/mzc/temp/tempbad.txt";

8. Import data

Load data infile "file_name" into table table_name;

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

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

field.

For example: INSERT INTO TX select T1.com1,concat (T1.COM2,T2.COM1) from T1,t2;

10, delete field

ALTER TABLE Form1 drop column name;

Clear, delete, and modify MySQL table instructions