1024programmer Mysql How to modify mysql database table?

How to modify mysql database table?

author: admin Post: August 26, 2023 83read 0Comment

How to modify the mysql database table: Use the “ALTER TABLE” statement to change the structure of the original table, such as adding or deleting fields, modifying the original field data type, renaming the field or table, modifying Table character set, etc.; syntax “ALTER TABLE

[modify options]”.

The premise of modifying the data table is that the table already exists in the database. Modifying a table refers to modifying the structure of an existing data table in the database. The operation of modifying the data table is also indispensable in database management. Just like drawing a sketch, you can use an eraser to erase too much drawing, and add it with a pen if you have less drawing.

If you don’t know how to modify the data table, it is equivalent to throwing away and redrawing as long as you make a mistake, which increases unnecessary costs.

In MySQL, you can use the ALTER TABLE statement to change the structure of the original table, such as adding or deleting columns, changing the original column type, renaming columns or tables, etc.

The syntax format is as follows:

ALTER TABLE
[modify option]

modify option The syntax format is as follows:

{ ADD COLUMN  
 | CHANGE COLUMN   
 | ALTER COLUMN  { SET DEFAULT  | DROP DEFAULT }
 | MODIFY COLUMN  
 | DROP COLUMN 
 | RENAME TO 
 | CHARACTER SET 
 | COLLATE  }

Modify table name

MySQL Modify the table name through the ALTER TABLE statement, the grammar rules are as follows:

ALTER TABLE  RENAME [TO]   ;

Among them, TO is an optional parameter, whether it is used or not will not affect the result.

Example 1

Use ALTER TABLE to rename the data table student to tb_students_info, the SQL statement and the running result are as follows. 

mysql> ALTER TABLE student RENAME TO tb_students_info;
 Query OK, 0 rows affected (0.01 sec)
 mysql> SHOW TABLES;
 +------------------+
 |Tables_in_test|
 +------------------+
 |tb_students_info|
 +------------------+
 1 row in set (0.00 sec)

Tips: Modifying the table name does not modify the structure of the table, so the structure of the table after modifying the name is the same as the table before modifying the name. Users can use the DESC command to view the modified table structure,

modify the table character set

MySQL uses the ALTER TABLE statement to modify the table character set. The syntax rules are as follows:

ALTER TABLE table name [DEFAULT] CHARACTER SET  [DEFAULT] COLLATE ;

Among them, DEFAULT is an optional parameter, whether it is used or not will not affect the result.

Example 2

Use ALTER TABLE to modify the character set of the data table tb_students_info to gb2312, and modify the proofreading rules to gb2312_chinese_ci. The SQL statements and running results are shown below. 

mysql> ALTER TABLE tb_students_info CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;
 Query OK, 0 rows affected (0.08 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 mysql> SHOW CREATE TABLE tb_students_info \G
 *************************** 1. row *********************  *****
        Table: tb_students_info
 Create Table: CREATE TABLE `tb_students_info` (
   `id` int(11) NOT NULL,
   `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=gb2312
 1 row in set (0.00 sec)

Add field to MySQL data table

MySQL data table is composed of rows and columns. Usually, the “columns” of the table are called fields (Field), and the “rows” of the table are called records (Record). As the business changes, it may be necessary to add new fields to existing tables.

MySQL allows adding fields at the beginning, middle and end.

Add fields at the end

A complete field includes field name, data type and constraints. The syntax for adding a field in MySQL is as follows:

ALTER TABLE
ADD [constraints];

The description of the grammar format is as follows:

is the name of the data table;

  • is the name of the field to be added;

  • is the field to be added that can store data Data type;

  • [Constraint] is optional and used to constrain the added field.

    • This syntax format defaults to adding a new field at the last position of the table (after the last column).

    Note: In this section, we only add new fields and do not pay attention to its constraints.

    Example

    Create a new student data table in the test database, the SQL statement and running results are as follows:

    mysql  > USE test;
 Database changed
 mysql> CREATE TABLE student (
     -> id INT(4),
     -> name VARCHAR(20),ABLE
    CHANGE ;

    Where:

    • Old field name: refers to the field name before modification;

    • New field name: refers to the modified field name;

    • New data type: refers to the modified data type. If you do not need to modify the data type of the field, you can set the new data type to be the same as the original one, but the data type cannot be empty.

    Example

    Use ALTER TABLE to modify the structure of table tb_emp1, change the col1 field name to col3, and change the data type to CHAR(30) , the SQL statement and running results are as follows. 

    mysql> ALTER TABLE tb_emp1
     -> CHANGE col1 col3 CHAR(30);
 Query OK, 0 rows affected (0.76 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 mysql> DESC tb_emp1;
 +--------+-------------+------+-----+---------+---  ----+
 | Field | Type | Null | Key | Default |
 +--------+-------------+------+-----+---------+---  ----+
 | col3 | char(30) | YES | | NULL | |
 | id | int(11) | YES | | NULL | |
 | name | varchar(30) | YES | | NULL | |
 | deptId | int(11) | YES | | NULL | |
 | salary | float | YES | | NULL | |
 +--------+-------------+------+-----+---------+---  ----+
 5 rows in set (0.01 sec)

    CHANGE can also only modify the data type to achieve the same effect as MODIFY. The method is to set the “new field name” and “old field name” in the SQL statement to Same name, only “data type” changed.

    Reminder: Since different types of data have different storage methods and lengths in the machine, modifying the data type may affect the existing data records in the data table. Therefore, when there are already data in the data table , do not modify the data type easily.

    Modify field data type

    Modify field data type is to convert field data type to another data type. The syntax rules for modifying field data types in MySQL are as follows:

    ALTER TABLE
    MODIFY

    Among them:

    • Table name: refers to the name of the table where the field whose data type is to be modified;

    • Field name: refers to the field that needs to be modified;

    • Data type: refers to the new data type of the modified field.

    Example

    Use ALTER TABLE to modify the structure of table tb_emp1, and change the data type of the name field from VARCHAR(22) to VARCHAR(30), The SQL statements and running results are shown below. 

    mysql> ALTER TABLE tb_emp1
     -> MODIFY name VARCHAR(30);
 Query OK, 0 rows affected (0.15 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 mysql> DESC tb_emp1;
 +--------+-------------+------+-----+---------+---  ----+
 | Field | Type | Null | Key | Default |
 +--------+-------------+------+-----+---------+---  ----+
 | col1 | int(11) | YES | | NULL | |
 | id | int(11) | YES | | NULL | |
 | name | varchar(30) | YES | | NULL | |
 | col2 | int(11) | YES | | NULL | |
 | deptId | int(11) | YES | | NULL | |
 | salary | float | YES | | NULL | |
 +--------+-------------+------+-----+---------+---  ----+
 6 rows in set (0.00 sec)

    After the statement is executed, it is found that the data type of the name field in the table tb_emp1 has been changed to VARCHAR(30), and the modification is successful.

    Delete field

    Delete field is to remove a field in the data table from the table, the syntax is as follows:

    ALTER TABLE
    DROP ;

    Among them, “field name” refers to the name of the field that needs to be deleted from the table.

    Example

    Use ALTER TABLE to modify the structure of the table tb_emp1, delete the col2 field, the SQL statement and the running result are as follows. 

    mysql> ALTER TABLE tb_emp1
     -> DROP col2;
 Query OK, 0 rows affected (0.53 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 mysql> DESC tb_emp1;
 +--------+-------------+------+-----+---------+---  ----+
 | Field | Type | Null | Key | Default |
 +--------+-------------+------+-----+---------+---  ----+
 | col1 | int(11) | YES | | NULL | |
 | id | int(11) | YES | | NULL | |
 | name | varchar(30) | YES | | NULL | |
 | deptId | int(11) | YES | | NULL | |
 | salary | float | YES | | NULL | |
 +--------+-------------+------+-----+---------+---  ----+
 5 rows in set (0.00 sec)

    Recommended tutorial: mysql video tutorial

    The above is how to modify the mysql database table? For more details, please pay attention to other related articles on 1024programmer.com!

    This article is from the internet and does not represent1024programmerPosition, please indicate the source when reprinting:https://www.1024programmer.com/how-to-modify-mysql-database-table/
    Reward
    tag:

    author: admin

    Previous article
    Next article

    Related recommendations

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    The latest and most comprehensive programming knowledge, all in 1024programmer.com

    Back-end Front-end
    © 2023 1024programmer - Encyclopedia of Programming Field
    Contact Us

    Contact us

    181-3619-1160

    Online consultation: QQ交谈

    E-mail: [email protected]

    Working hours: Monday to Friday, 9:00-17:30, holidays off

    Follow wechat
    Scan wechat and follow us

    Scan wechat and follow us

    Follow Weibo