Mysql exercise Alter usage summary _ MySQL

Mysql exercise Alter usage summary bitsCN.com

Mysql exercise Alter usage summary

Alert usage summary:

Alter database syntax

Alter database is used to change the global features of a DATABASE. These features are stored in the db. opt file in the Database Directory. To use alter database, you must obtain the ALTER permission for the DATABASE.

The character set clause is used to change the default database character set. The COLLATE clause is used to change the default database order.

The database name can be ignored. in this case, the statement corresponds to the default database. You can also use alter schema.

Alter table syntax

Alter table is used to change the structure of the original TABLE. For example, you can add or delete columns, create or cancel indexes, change the type of original columns, or rename columns or tables. You can also modify the table comment and table type.

1: delete columns

Alter table [TABLE name] DROP [column name]

2: Add columns

Alter table [TABLE name] ADD [column name] int not null comment 'Comment description'

3. modify the column type information.

Alter table [TABLE name] CHANGE [column name] [New column name (here you can use the same name as the original column)] bigint not null comment 'annotation description'

If you want to CHANGE the column type rather than the name, the CHANGE syntax still requires the old and new column names, even if the old and new column names are the same. For example:

Mysql> alter table t1 CHANGE B BIGINT NOT NULL;

You can also use MODIFY to change the column type without renaming:

Mysql> alter table t1 MODIFY B BIGINT NOT NULL;

If you use CHANGE or MODITY to shorten the column length, the column contains an index, and the shortened column length is smaller than the index length, MySQL will automatically shorten the index length.

· When you use CHANGE or MODIFY to CHANGE the column type, MySQL will try to convert the original column value to the new type.

· You can use FIRST or AFTER col_name to add columns at a specific position in a table row. By default, columns are added to the end. You can also use FIRST and AFTER in CHANGE or MODIFY statements.

4: rename a column

Alter table [TABLE name] CHANGE [column name] [New column name] bigint not null comment 'annotation description'

5. rename a table

Alter table [TABLE name] RENAME [new TABLE name]

6. delete the primary key in the table.

Alter TABLE [TABLE name] drop primary key

7. add a primary key

Alter table tabelname add new_field_id int (5) unsigned default 0 not null auto_increment, add primary key (new_field_id );

Alter table sj_resource_charges add constraint PK_SJ_RESOURCE_CHARGES primary key (resid, resfromid)

8: add an index

Alter table sj_resource_charges add index INDEX_NAME (name );

9: add an index with unique restrictions

Alter table sj_resource_charges add unique emp_name2 (cardnumber );

(Emp_name2 is your name)

[SQL]

Alter table good add unique ugood_name (name );

10: delete an index

Alter table tablename drop index emp_name;

11 Add primary key auto-increment

[SQL]

Alter table good modify id int auto_increment;

12. delete unique conditions

Alter table good add unique good_name

(Name );

In fact, it is to delete the index. Adding a unique limit adds an index.

[Plain]

Alter table 'good'

Drop index 'ugood _ name ';

13. deleting a primary key increases automatically.

[SQL]

Alter table 'good'

Modify column 'id' int (11) not null first;

BitsCN.com