How to modify mysql database table?
Methods to modify mysql database tables: Use the "ALTER TABLE" statement to change the structure of the original table, such as adding fields or deleting fields, modifying the original field data type, renaming fields or tables, modifying Table character set, etc.; syntax "ALTER TABLE
[modify options]".
#The prerequisite for 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 essential in database management. It is just like drawing a sketch. If you draw too much, you can erase it with an eraser. If you draw too little, you can add it with a pen.
Not knowing how to modify the data table is equivalent to throwing away and redrawing as long as we make a mistake, which increases unnecessary costs.
In MySQL, you can use the
ALTER TABLEstatement 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 <表名> [修改选项]Copy after loginThe syntax format for modifying options is as follows:
{ ADD COLUMN <列名> <类型> | CHANGE COLUMN <旧列名> <新列名> <新列类型> | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } | MODIFY COLUMN <列名> <类型> | DROP COLUMN <列名> | RENAME TO <新表名> | CHARACTER SET <字符集名> | COLLATE <校对规则名> }Copy after loginModify the table name
MySQL uses the ALTER TABLE statement to modify the table name. The syntax rules are as follows:
ALTER TABLE <旧表名> RENAME [TO] <新表名>；Copy after loginAmong them, TO is an optional parameter, and whether it is used or not does not affect the result.
Example 1
Use ALTER TABLE to rename the data table student to tb_students_info. The SQL statement and running results 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)Copy after loginTip: Modifying the table name does not modify the structure of the table, so the structure of the table after the name is modified is the same as that of the table before the name was modified. Users can use the DESC command to view the modified table structure,
Modify the table character set
MySQL implements the table character set through the ALTER TABLE statement Modification, the syntax rules are as follows:
ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;Copy after loginAmong them, DEFAULT is an optional parameter, and 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 the collation rule to gb2312_chinese_ci. The SQL statement 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)Copy after loginMySQL data table adding fields
MySQL data table is composed of rows and columns. The "columns" of the table are usually called Field (Field) refers to the "row" of the table as a record (Record). As your business changes, you may need 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 format for adding fields in MySQL is as follows:
ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];Copy after loginThe syntax format is explained as follows:
is the name of the data table;
is the name of the field to be added; is the field that can store data Data type;
[Constraints] is optional and is used to constrain the added fields.
This syntax format adds a new field at the last position of the table (after the last column) by default.
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 statements and running results are as follows:
mysql> USE test; Database changed mysql> CREATE TABLE student ( -> id INT(4), -> name VARCHAR(20), -> sex CHAR(1)); Query OK, 0 rows affected (0.09 sec)Copy after loginUse DESC to view the student table structure. The SQL statements and running results are as follows:
mysql> DESC student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)Copy after loginUse the ALTER TABLE statement to add an INT type field age. The SQL statement and running results are as follows:
mysql> ALTER TABLE student ADD age INT(4); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0Copy after loginUse DESC to view the student table structure and verify whether the age field is added successfully. The SQL statement and running results are as follows:
mysql> DESC student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | age | int(4) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)Copy after loginAs you can see from the running results, the age field has been added to the student table, and the field is at the last position of the table. The field was added successfully.
Add a field at the beginning
MySQL adds a new field at the last position of the table by default. If you want to add a new field at the beginning (before the first column), then You can use the FIRST keyword, the syntax format is as follows:
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;Copy after loginThe FIRST keyword is generally placed at the end of the statement.
Example
Use the ALTER TABLE statement to add the INT type field stuId in the first column of the table. The SQL statement and running results are as follows.
mysql> ALTER TABLE student ADD stuId INT(4) FIRST; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | stuId | int(4) | YES | | NULL | | | id | int(4) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | age | int(4) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)Copy after loginYou can see from the running results that the stuId field has been added to the student table, and the field is at the first position in the table. The field was added successfully.
Add fields in the middle position
In addition to allowing fields to be added at the beginning and end of the table, MySQL also allows adding fields in the middle position (after the specified field) Field, you need to use the AFTER keyword at this time, the syntax format is as follows:
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;Copy after loginThe function of AFTER is to add a new field after an existing field.
Note that you can only add a new field after an existing field, but not in front of it.
Example
使用 ALTER TABLE 语句在 student 表中添加名为 stuno，数据类型为 INT 的字段，stuno 字段位于 name 字段的后面。SQL 语句和运行结果如下：
mysql> ALTER TABLE student ADD stuno INT(11) AFTER name; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | stuId | int(4) | YES | | NULL | | | id | int(4) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | stuno | int(11) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | age | int(4) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)Copy after login由运行结果可以看到，student 表中已经添加了 stuId 字段，且该字段在 name 字段后面的位置，添加字段成功。
MySQL 修改和删除数据表字段
修改字段名称
MySQL 中修改表字段名的语法规则如下：
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；Copy after login其中：
旧字段名：指修改前的字段名；
新字段名：指修改后的字段名；
新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。
例
使用 ALTER TABLE 修改表 tb_emp1 的结构，将 col1 字段名称改为 col3，同时将数据类型变为 CHAR(30)，SQL 语句和运行结果如下所示。
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 | Extra | +--------+-------------+------+-----+---------+-------+ | 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)Copy after loginCHANGE 也可以只修改数据类型，实现和 MODIFY 同样的效果，方法是将 SQL 语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。
提示：由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。
修改字段数据类型
修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下：
ALTER TABLE <表名> MODIFY <字段名> <数据类型>Copy after login其中：
表名：指要修改数据类型的字段所在表的名称；
字段名：指需要修改的字段；
数据类型：指修改后字段的新数据类型。
例
使用 ALTER TABLE 修改表 tb_emp1 的结构，将 name 字段的数据类型由 VARCHAR(22) 修改成 VARCHAR(30)，SQL 语句和运行结果如下所示。
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 | Extra | +--------+-------------+------+-----+---------+-------+ | 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)Copy after login语句执行后，发现表 tb_emp1 中 name 字段的数据类型已经修改成 VARCHAR(30)，修改成功。
删除字段
删除字段是将数据表中的某个字段从表中移除，语法格式如下：
ALTER TABLE <表名> DROP <字段名>；Copy after login其中，“字段名”指需要从表中删除的字段的名称。
例
使用 ALTER TABLE 修改表 tb_emp1 的结构，删除 col2 字段，SQL 语句和运行结果如下所示。
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 | Extra | +--------+-------------+------+-----+---------+-------+ | 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)Copy after login推荐教程：mysql视频教程
The above is the detailed content of How to modify mysql database table?. For more information, please follow other related articles on the PHP Chinese website!
Statement of this WebsiteThe content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
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
Assassin's Creed Shadows: Seashell Riddle Solution3 weeks ago By DDDWhat's New in Windows 11 KB5054979 & How to Fix Update Issues2 weeks ago By DDDWhere to find the Crane Control Keycard in Atomfall3 weeks ago By DDDSaving in R.E.P.O. Explained (And Save Files)1 months ago By 尊渡假赌尊渡假赌尊渡假赌Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
CakePHP Tutorial1386
52
See all articlesMySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".
MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.
MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.
Redis uses a single threaded architecture to provide high performance, simplicity, and consistency. It utilizes I/O multiplexing, event loops, non-blocking I/O, and shared memory to improve concurrency, but with limitations of concurrency limitations, single point of failure, and unsuitable for write-intensive workloads.
MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.
MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen
Effective monitoring of Redis databases is critical to maintaining optimal performance, identifying potential bottlenecks, and ensuring overall system reliability. Redis Exporter Service is a powerful utility designed to monitor Redis databases using Prometheus. This tutorial will guide you through the complete setup and configuration of Redis Exporter Service, ensuring you seamlessly build monitoring solutions. By studying this tutorial, you will achieve fully operational monitoring settings
