mysql modification command

MySQL is a commonly used relational database. Due to its simplicity, ease of use, efficiency and stability, it is widely used and recognized in data storage. MySQL provides many commands to change and modify the database. This article will introduce the MySQL modification commands and their usage in detail, hoping to help readers better manage the MySQL database.

1. Modify the table structure

In the MySQL database, we often need to modify the structure of the existing table, such as adding a column or deleting a column. The following introduces some commonly used commands to modify the table structure.

1. ALTER TABLE ADD COLUMN

This command is used to add a new column to an existing table. The syntax format is: ALTER TABLE table_name ADD COLUMN column_name column_definition;

Among them, table_name represents the name of the table to which a new column needs to be added, column_name represents the name of the new column, and column_definition describes the data type of the new column and its related restrictions. condition.

For example, we have a customers table and need to add a new column Email. Its data type is varchar(50) and is set to NOT NULL. You can use the following command to add this column:

ALTER TABLE customers ADD COLUMN Email varchar(50) NOT NULL;

2. ALTER TABLE DROP COLUMN

This command is used to delete the a certain column. The syntax format is: ALTER TABLE table_name DROP COLUMN column_name;

where table_name represents the table name of the column that needs to be deleted, and column_name represents the column name that needs to be deleted.

For example, in our customers table, the column Email just added is no longer needed. You can use the following command to delete it:

ALTER TABLE customers DROP COLUMN Email;

3. ALTER TABLE MODIFY COLUMN

This command is used to modify the data type or data length of a column in an existing table. The syntax format is: ALTER TABLE table_name MODIFY COLUMN column_name column_definition;

Among them, table_name represents the table name that needs to be modified, column_name represents the column name that needs to be modified, and column_definition describes the new data type and its restrictions.

For example, in our customers table, we need to change the data type of column Country from varchar(20) to varchar(30). You can use the following command to complete the modification:

ALTER TABLE customers MODIFY COLUMN Country varchar(30);

2. Modify data

In the MySQL database, we often need to modify data, such as changing a certain column of a record, etc. The following introduces some commonly used commands to modify data.

1. UPDATE

This command is used to modify the data in the table. The syntax format is: UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... WHERE condition;

Among them, table_name represents the table name that needs to be modified, column_name represents the column name that needs to be modified, and value represents the column name that needs to be modified. The new value is formed, and condition is the restriction condition of the record that needs to be modified.

For example, we have a customers table and need to change all "Beijing" in the City column to "Shanghai". You can use the following command:

UPDATE customers SET City = 'Shanghai' WHERE City = 'Beijing';

2. REPLACE

This command is used to replace the value of a record in the table. The syntax format is: REPLACE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

where table_name represents the table name that needs to be modified, and column represents the column to be modified. The name and value are the new values ​​that need to be modified.

For example, in our customers table, if you need to modify the City value in the record with id=1, you can use the following command:

REPLACE INTO customers(id, City) VALUES(1, 'Shanghai ');

3. Modify users and permissions

In the MySQL database, we also need to frequently modify users and their permissions. The following introduces some commonly used commands to modify users and permissions.

1. CREATE USER

This command is used to create a new user. The syntax format is: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

where username represents the user name to be created, localhost represents the host name of the user, and password is the user password.

For example, if we need to create a new user root with a password of passwd123, we can use the following command:

CREATE USER 'root'@'localhost' IDENTIFIED BY 'passwd123';

2. GRANT

This command is used to grant user permissions. The syntax format is: GRANT privilege ON database.table TO 'username'@'localhost';

where privilege represents the granted permissions, database.table represents the database and table name, and username@localhost represents the user and its host name.

For example, if we need to give the root user SELECT, INSERT, and UPDATE permissions on the customers table, we can use the following command:

GRANT SELECT, INSERT, UPDATE ON customers TO 'root'@'localhost ';

3. REVOKE

This command is used to revoke user permissions. The syntax format is: REVOKE privilege ON database.table FROM 'username'@'localhost';

Among them, privilege represents the revoked permission, database.table represents the database and table name, and username@localhost represents the user and its host. name.

For example, if we need to cancel the SELECT permission of the root user on the customers table, we can use the following command:

REVOKE SELECT ON customers FROM 'root'@'localhost';

IV ,Summarize

MySQL is an efficient and secure database. Learning and mastering the usage of its modification commands can better manage the database. This article introduces commonly used MySQL modification commands such as modifying table structure, modifying data, and modifying user permissions, as well as their syntax format and examples. I believe that after readers master these modification commands in practice, they will be able to manage the MySQL database more efficiently and improve work efficiency.

The above is the detailed content of mysql modification command. For more information, please follow other related articles on the PHP Chinese website!