Clear, delete, and modify mysql tables

1. Clear the deletefrom table name in the mysql table; truncatetable table name; delete statements without the where parameter can delete all contents in the mysql table, and truncatetable can also be used to clear all contents in the mysql table. In terms of efficiency, truncate is faster than delete, but after truncate is deleted, mysql logs are not recorded and data cannot be recovered. De

1. delete from table name in mysql table; truncate table name; delete statement without where parameter can delete all contents in mysql table, you can also use truncate table to clear all contents in the mysql table. In terms of efficiency, truncate is faster than delete, but after truncate is deleted, mysql logs are not recorded and data cannot be recovered. De

I. Clear mysql table data

Delete from table name;
Truncate table name;
The delete statement without the where parameter can delete all content in the mysql table. You can also use the truncate table to clear all content in the mysql table.
In terms of efficiency, truncate is faster than delete, but after truncate is deleted, mysql logs are not recorded and data cannot be recovered.
The delete operation is a bit like deleting all records in the mysql table one by one until the deletion is complete,
Truncate is equivalent to retaining the structure of the mysql table and re-creating the table. All the statuses are equivalent to new tables.

Ii. delete some data in the table

Delete from Command Format: delete from table name where expression

For example, to delete a record numbered 1 in MyClass:

Mysql> delete from MyClass where id = 1;

3. modify a table
1. Select Database

> Use Database Name;

2. query all data tables
> Show tables;

3. query table Field Information
> Desc table name;

3. 1. Modify the field type of a table and specify it as null or not
> Alter table name change field Name field type [whether to allow non-null];
> Alter table name modify Field Name field type [whether to allow non-null];

. Modify the field name of a table and specify it as null or not
> Alter table name change field Original Name field New Name field type [whether to allow non-null];

For example:
Modify the birth field in Table expert_info. It is allowed to be empty.

> Alter table expert_info change birth varchar (20) null;

1. Add a field (one column)

Alter table table_name add column column_name type default value; type indicates the type of the field, and value indicates the default value of the field.

For example:

Alter table mybook add column publish_house varchar (10) 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 indicates the original field name, dest_col_name

Indicates the name of the modified field.

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

For example:

Alter table userinfo change column 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 (10) auto_increment primary key;

6. For backup of a table in the database, enter:

Mysqldump-u root-p database_name table_name> bak_file_name

For example:

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

7. Export data

Select_statment into outfile "dest_file ";

For example:

Select cooperatecode, createtime from publish limit 10 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 in the two tables and insert them into the other table. The following example shows how to splice the values of the com2 and com1 fields in Table T1.

Field.

For example:

Insert into tx select t1.com1, concat (t1.com2, t2.com1) from t1, t2;

10. delete a field

Alter table form1 drop column name;