What is the SQL keyword that indicates modifying a database object?-Mysql Tutorial-php.cn

The SQL keyword that indicates modifying a database object is: ALTER. In the database, you can use the "ALTER TABLE" statement to change the structure of the original table, and add, delete, or modify columns in the existing table.

What is the SQL keyword that indicates modifying a database object?

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The SQL keyword indicating modifying a database object is: alte.

SQL ALTER DATABASE statement

In MySQL, you can use ALTER DATABASE to modify Relevant parameters of the database that has been created or exists. The syntax format for modifying the database is:

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

Copy after login

The syntax description is as follows:

ALTER DATABASE is used to change the global characteristics of the database.
Using ALTER DATABASE requires database ALTER permissions.
The database name can be ignored, and the statement corresponds to the default database.
The CHARACTER SET clause is used to change the default database character set.

Example:

View the execution results of the definition statement of the test_db database

mysql> SHOW CREATE DATABASE test_db;
+----------+--------------------------------------------------------+
| Database | Create Database                                        |
+----------+--------------------------------------------------------+
| test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8 */|
+----------+--------------------------------------------------------+
1 row in set (0.05 sec)

Copy after login

Use the command line tool to modify the specified character set of the database test_db to gb2312 , the default proofreading rule is modified to gb2312_unicode_ci

mysql> ALTER DATABASE test_db
    -> DEFAULT CHARACTER SET gb2312
    -> DEFAULT COLLATE gb2312_chinese_ci;
mysql> SHOW CREATE DATABASE test_db;
+----------+--------------------------------------------------------+
| Database | ALTER Database                                        |
+----------+--------------------------------------------------------+
| test_db  | ALTER DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET gb2312 */|
+----------+--------------------------------------------------------+
1 row in set (0.00 sec)

Copy after login

(Recommended tutorial: mysql video tutorial)

SQL ALTER TABLE statement

SQL ALTER TABLE statement is used to add, delete, or modify columns in an existing table. 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 <表名> [修改选项]

Copy after login

The 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 login

Modify table name

MySQL via ALTER TABLE statement is used to modify the table name. The syntax rules are as follows:

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

Copy after login

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

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 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;

Copy after login

Among them, DEFAULT is Optional parameters, whether used or not does not affect the results.

Modify field names

The syntax rules for modifying table field names in MySQL are as follows:

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

Copy after login

Among them:

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.

Modify field data type

Modifying the data type of a field is to convert the data type of the field into another data type. The syntax rules for modifying field data types in MySQL are as follows:

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

Copy after login

Among them:

Table name: refers to the name of the table where the field whose data type is to be modified is located;
Field name: refers to the field that needs to be modified;
Data type: refers to the new data type of the modified field.

Delete a field

Deleting a field is to remove a field in the data table from the table. The syntax format is as follows:

ALTER TABLE <表名> DROP <字段名>；

Copy after login

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

Add fields

1. 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 login

The 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.

2. 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 (in front of the first column), you can use the FIRST key word, the syntax format is as follows:

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

Copy after login

FIRST keyword is generally placed at the end of the statement.

3. 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 fields to be added in the middle position (after the specified field). At this time You need to use the AFTER keyword, the syntax format is as follows:

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;

Copy after login

The 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.

For more knowledge about computer programming, please visit: Programming Video! !

The above is the detailed content of What is the SQL keyword that indicates modifying a database object?. For more information, please follow other related articles on the PHP Chinese website!

Related labels：

sql database

source：php.cn

Previous article：Take a look at MySQL concurrency parameter tuning Next article：Introducing the preprocessing (prepared statement) performance test of MySQL database

Statement of this Website

The 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