Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to modify a database object in sql. It is very detailed and has a certain reference value. Friends who are interested must read it!

The SQL keyword for 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 you can add, delete, or modify columns in the existing table.

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

SQL ALTER DATABASE statement

In MySQL, you can use ALTER DATABASE to modify the relevant parameters of a database that has been created or exists. Modify the syntax format of the database as follows:

ALTER DATABASE [database name] {[DEFAULT] CHARACTER SET | [DEFAULT] COLLATE}

The syntax is as follows:

ALTER DATABASE is used to change the global properties of the database.

To use ALTER DATABASE, you need to obtain 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 declaration 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)

Use the command line tool to modify the specified character set of the database test_db to gb2312, and the default proofreading rule 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)

(recommended tutorial: mysql video tutorial)

SQL ALTER TABLE statement

The SQL ALTER TABLE statement is used to add, delete, or modify columns in an existing table. Use ALTER TABLE statements to change the structure of the original table, such as adding or deleting columns, changing the original column type, renaming columns or tables, and so on.

The syntax format is as follows:

ALTER TABLE [modify options]

The syntax format of the modification option 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 modifies the table name through the ALTER TABLE statement. The syntax rules are as follows:

ALTER TABLE RENAME [TO]

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

Modify table character set

MySQL modifies the character set of the table through the ALTER TABLE statement. The syntax rules are as follows:

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

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

Modify field name

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

ALTER TABLE 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 the same as the original, but the data type cannot be empty.

Modify field data type

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

ALTER TABLE MODIFY

Where:

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

Field name: refers to the field to be modified

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

Delete a field

Deleting a field removes a field from the data table in the following syntax format:

ALTER TABLE DROP

Where Field name refers to the name of the field that needs to be deleted from the table.

Add a field

1. Add a field at the end

A complete field includes the field name, data type, and constraints. The syntax format of the MySQL add field is as follows:

ALTER TABLE ADD [constraint]

The syntax format is described as follows:

Is the name of the data table

The name of the field to be added

The data type that can store data for the field to be added

[constraints] are optional and are used to constrain added fields.

By default, this syntax format adds a new field at the end of the table (after the last column).

2. Add a field at the beginning

By default, MySQL adds a new field at the end of the table. If you want to add a new field at the beginning (before the first column), you can use the FIRST keyword with the syntax format as follows:

ALTER TABLE ADD [constraint] FIRST

The FIRST keyword is usually placed at the end of the statement.

3. Add a field in the middle

MySQL not only allows fields to be added at the beginning and end of the table, but also allows fields to be added in the middle (after the specified field). In this case, the AFTER keyword is required, and the syntax format is as follows:

ALTER TABLE ADD [constraint] AFTER

The role of AFTER is to add a new field to the end of an existing field.

Note that you can only add a new field after an existing field, not before it.

These are all the contents of the article "how to modify a database object in sql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.