Mysql exercise Alter usage Summary

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 www.2cto.com 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 alter table Name DROP column name 2: add alter table name ADD column name int not null comment 3: modify the column type information alter 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 length of a column, an index exists in the column, and the length of the shortened column is smaller than the index length, mySQL automatically shortens the index length. Www.2cto.com. When you use CHANGE or MODIFY to CHANGE the column type, MySQL tries its best to convert the original column value to a 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 the alter table column [TABLE name] CHANGE [column name] [new column name] bigint not null comment 'annotation description' 5: rename alter table rename new TABLE name 6: Delete TABLE primary key Alter TABLE name drop primary key 7: add 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 limit primary key (resid, resfromid: add an index www.2 Cto.com alter table sj_resource_charges add index INDEX_NAME (name); 9: add the unique restriction index 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 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 the unique restriction alter table good add unique good_name (name); Delete Except the index. Adding a unique limit adds an index. Www.2cto.com [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;