mysql-Video Follow Practice notes

1. Add a column to a table

   1. ALTER TABLE Tbl_user
      Add email varchar (50);

2. Delete a column in a table

   1. ALTER TABLE Tbl_user
      Drop email;

3. Rename a column

   1. ALTER TABLE tbl_user ALTER TABLE name
      Change age user_age int; Change keyword old field name new field name type information for new field

4. Modifying the type definition of a column

   1. ALTER TABLE Tbl_user
      Change User_age user_age tinyint (1) is not null; Change after + field name to be modified + new field name + new type

   2. Note: 3, 4 No essential difference, is actually the same!!

5. Rename the column name:

ALTER TABLE Tbl_user rename user_name;

1. 1. MySQL alter command

      We need to use the MySQL alter command when we need to modify the data table names or modify the field of the data tables.

      Before starting this tutorial, let's create a table with the table named: Testalter_tbl.

      [Email protected]# mysql-u root-p password;EnterPassword:*******Mysql> UseRunoob;DatabaseChangedmysql>CREATE TABLE Testalter_tbl- ( -I INT, -C CHAR(1) - );QueryOk, 0Rows affected(0.05Sec)Mysql>SHOW COLUMNS from Testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+|I| Int(11) |YES| |Null|  ||  C |char (1) |  YES ||  NULL | | +-------+---------+------+-----+---------+-------+2 rows in set   (0.00 Sec   

      Delete, add, or modify a table field

      The following command uses the ALTER command and the drop clause to delete the I field of the created table above:

      MySQL> ALTER TABLE testalter_tbl  DROP i;  

      You cannot use drop to delete a field if only one field is left in the datasheet.

      MySQL uses an ADD clause to add columns to the data table, and the following example adds an I field to the table testalter_tbl and defines the data type:

      MySQL> ALTER TABLE testalter_tbl ADD i INT;  

      After executing the above command, the I field is automatically added to the end of the data table fields.

      Mysql>SHOW COLUMNS from Testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+|C| Char(1) |YES| |Null|  ||  I |int (11) |  YES ||  NULL | | +-------+---------+------+-----+---------+-------+2 rows in set   (0.00 Sec   

      If you need to specify the location of the new field, you can use the keyword first provided by MySQL (set to position one column), after field name (After a field is set).

      Try the following ALTER table statement, and after successful execution, use show COLUMNS to view the table structure changes:

      ALTER TABLE testalter_tbl DROP i;  ALTER TABLE testalter_tbl ADD i INT first;  ALTER TABLE testalter_tbl DROP i;  ALTER TABLE testalter_tbl ADD i INT after C;      

      The first and after keywords are used only for the ADD clause, so if you want to reset the location of the data table fields, you need to use drop to delete the field and then use Add to add the field and set the position.

      Modify field type and name

      If you need to modify the field type and name, you can use the MODIFY or change clause in the ALTER command.

      For example, to change the type of field C from char (1) to char (10), you can execute the following command:

      MySQL> ALTER TABLE testalter_tbl MODIFY C CHAR(ten);    

      The syntax differs greatly by using the change clause. After the Change keyword, follow the name of the field you want to modify, and then specify the new field name and type. Try the following example:

      MySQL> ALTER TABLE testalter_tbl change i j BIGINT;  

      <p If you now want to change the field j= "" From = "" bigint= "" to = "" Int,sql statement as follows: <= "" p= "" >

      MySQL> ALTER TABLE testalter_tbl Change J J INT;  

      Effects of ALTER TABLE on Null values and default values

      When you modify a field, you can specify whether to include only or whether to set the default value.

      The following example specifies that the field J is not NULL and the default value is 100.

      MySQL> ALTER TABLE testalter_tbl     ;    

      If you do not set the default value, MySQL automatically sets the word defaults to be considered NULL.

      Modify field default values

      You can modify the default value of a field by using ALTER to try the following instance:

      Mysql>Alter TABLE TESTALTER_TBL ALTER I SET DEFAULT1000;Mysql>SHOW COLUMNS from Testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+|C| Char(1) |YES| |Null| | | I |int (11) |  YES ||  1000 || +-------+---------+------+-----+---------+-------+2 rows in set   (0.00 Sec   

      You can also use the ALTER command and the drop clause to delete the default value of the field, as in the following example:

      Mysql>Alter TABLE TESTALTER_TBL ALTER I DROP DEFAULT;Mysql>SHOW COLUMNS from Testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+|C| Char(1) |YES| |Null| ||I| int (11 |||  NULL | | +-------+---------+------+-----+---------+-------+2 rows in set   (0.00 Secchanging a table type:            

      To modify a data table type, you can do so by using the ALTER command and the type clause. Try the following example, and we will modify the type of table Testalter_tbl to MYISAM:

      Note: You can use the Show Table STATUS statement to view the data table type.

      Mysql>ALTER TABLE testalter_tbl TYPE=MYISAM;Mysql>SHOW TABLE STATUS like' Testalter_tbl '\g*************************** 1.Row**************** Name:Testalter_tblType: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0Max_data_length: 25769803775 Index_length: 1024 Data_free: 0 Auto_increment:NullCreate_time: 2007-06-03 08:04:36 update_time: 2007-06-03  08:04: 36 check_time: NULL Span class= "Typ" >create_options: comment : 1 row inset  (0.00 sec               /span>                 

      Modify Table Name

      If you need to modify the name of the data table, you can do so by using the RENAME clause in the ALTER table statement.

      Try the following instance to rename the data table Testalter_tbl to ALTER_TBL:

      MySQL> ALTER TABLE testalter_tbl RENAME to Alter_tbl;  

      The ALTER command can also be used to create and delete the index of a MySQL data table, which we'll cover in the next section.

mysql-Video Follow Practice notes