When we need to modify the data table name or modify the data table fields, we need to use the MySQL ALTER command.
Before starting this tutorial, let us first create a table named: testalter_tbl.
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> create table testalter_tbl -> ( -> i INT, -> c CHAR(1) -> ); Query OK, 0 rows affected (0.05 sec) 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 table fields
The following command uses the ALTER command and DROP clause to delete the i field of the above created table:
mysql> ALTER TABLE testalter_tbl DROP i;
If in the data table If there is only one field left, you cannot use DROP to delete the field.
The ADD clause is used in MySQL to add columns to the data table. The following example adds the i field in the table testalter_tbl and defines the data type:
mysql> ALTER TABLE testalter_tbl ADD i INT;
Execute the above After the 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 keywords FIRST (set in the first column) and AFTER field name (set after a certain field) provided by MySQL.
Try the following ALTER TABLE statement. After successful execution, use SHOW COLUMNS to view the changes in the table structure:
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 only used for the ADD clause, so if you want to repeat To set the position of a data table field, you need to first use DROP to delete the field and then use ADD to add the field and set the position.
Modify the 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(10);
Use the CHANGE clause, the syntax is very different. After the CHANGE keyword, follow the name of the field you want to modify, and then specify the type and name of the new field. Try the following example:
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT; mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
The impact 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 a default value.
In the following example, the specified field j is NOT NULL and the default value is 100.
mysql> ALTER TABLE testalter_tbl -> MODIFY j BIGINT NOT NULL DEFAULT 100;
If you do not set a default value, MySQL will automatically set the field to NULL by default.
Modify the default value of the field
You can use ALTER to modify the default value of the field. Try the following example:
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; 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 DROP clause to delete The default value of the field, as shown 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) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) Changing a Table Type:
To modify the data table type, you can use the ALTER command and TYPE clause to complete. Try the following example, we change 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_tbl Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0Max_data_length: 25769803775 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2007-06-03 08:04:36 Update_time: 2007-06-03 08:04:36 Check_time: NULL Create_options: Comment:1 row in set (0.00 sec)
Modify the table name
If you need to modify the name of the data table, you can use the RENAME clause in the ALTER TABLE statement.
Try the following example 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 the MySQL data table. We will cover this function Introduced in the following chapters.
【Related Recommendations】
1. Special Recommendation:"php Programmer Toolbox" V0.1 version Download
2. Free mysql online video tutorial
3. Those things about database design
The above is the detailed content of Share an example of the MySQL ALTER command. For more information, please follow other related articles on the PHP Chinese website!

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SublimeText3 English version
Recommended: Win version, supports code prompts!

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)
