What are the alter commands in Mysql?

The alter command includes adding columns, deleting columns, modifying column names, modifying column data types, setting default values, removing default values, adding primary keys, deleting primary keys, adding indexes, deleting indexes, adding unique constraints, and deleting Unique constraints, add foreign key constraints, delete foreign key constraints, modify the name of the table, etc. Detailed introduction: 1. Add columns: ALTER TABLE table_name ADD column_name data_type;; 2. Delete columns, etc.

The ALTER command in MySQL is used to modify the structure of an existing database table. The following are some common uses and examples of the ALTER command:

1. Add a column:

sql

ALTER TABLE table_name ADD column_name data_type;

2. Delete a column:

sql

ALTER TABLE table_name DROP COLUMN column_name;

3. Modify column name:

sql

ALTER TABLE table_name CHANGE old_column_name new_column_name data_type;

4. Modify column data type:

sql

ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;

5. Set default Value:

sql

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'default_value';

6. Remove the default value:

sql

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

7. Add primary key:

sql

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

8. Delete primary key:

sql

ALTER TABLE table_name DROP PRIMARY KEY;

9. Add index:

sql

ALTER TABLE table_name ADD INDEX index_name (column_name);

10. Delete index:

sql

ALTER TABLE table_name DROP INDEX index_name;

11. Add unique constraints:

sql

ALTER TABLE table_name ADD UNIQUE (column_name);

12. Delete unique constraints:

sql

ALTER TABLE table_name DROP INDEX column_name;

-- Delete the corresponding index, but be aware that not all unique constraints will generate an index, so you need to determine the index name corresponding to the unique constraint you really need to delete.

13. Add a foreign key constraint:

For example, add a foreign key constraint to the orders table to the customer table:

sql

ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customer(id);

14. Delete Foreign key constraints: (Note: MySQL does not directly provide a command to delete foreign key constraints, but you can achieve it indirectly by deleting the foreign key column):

For example, delete the customer_id column in the orders table:

sql

ALTER TABLE orders DROP COLUMN customer_id;

-- Note that this will delete the entire column, not just the foreign key constraint. Make sure this is what you want. If you just want to drop the foreign key constraint but keep the column, you need to find the original SQL statement that created the foreign key constraint and drop it manually. This is usually found in the database's metadata or logs.

15. Modify the name of the table: (Note: MySQL does not directly provide the command to rename the table, but you can achieve it indirectly by copying the table and deleting the old table):

First, copy Table structure and data to a new table:

sql

CREATE TABLE new_table AS SELECT * FROM old_table;

The above is the detailed content of What are the alter commands in Mysql?. For more information, please follow other related articles on the PHP Chinese website!