MySQL table modification operations and Applications

The following article describes how to modify a table in MySQL. We first add a field (a column) to introduce the actual operation and application of modifying a table in MySQL, if you are interested in modifying tables in MySQL, you can browse the following articles. Altertabletable_nameaddcolumncolumn_nametypedefa

The following article describes how to modify a table in MySQL. We first add a field (a column) to introduce the actual operation and application of modifying a table in MySQL, if you are interested in modifying tables in MySQL, you can browse the following articles. Altertabletable_nameaddcolumncolumn_nametypedefa

The following article describes how to modify a table in MySQL. We first add a field (a column) to introduce the actual operation and application of modifying a table in MySQL, if you are interested in modifying tables in MySQL, you can browse the following articles.

 
 

  
  
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, And dest_col_name indicates 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 MySQL modification 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. Run the following command to modify the backup of a table in MySQL:

 
 

  
  
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 another MySQL modification table. The following example shows how to splice the values of the com2 and com1 fields in Table t1 and insert them into the fields in Table tx.

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;
 
 

The above content is an introduction to MySQL table modification. I hope you will have some gains.