MySQL-mysql tutorial

The table structure is as follows: mysqlshowcreatetableperson; | person | CREATETABLE 'person '('number' int (11) DEFAULTNULL, 'name' varchar (255) DEFAULTNULL, 'birthday' dateDEFAULTNULL) enginemyisamdefacharcharsetutf8 | delete a column: ALTERTABLEperso

The table structure is as follows: mysql show create TABLE person; | person | CREATE table 'person '('number' int (11) default null, 'name' varchar (255) default null, 'Birthday' date default null) ENGINE = MyISAM default charset = utf8 | delete a column: alter table perso

The table structure is as follows:

Mysql> show create table person;
| Person | create table 'person '(
'Number' int (11) default null,
'Name' varchar (255) default null,
'Birthday' date DEFAULT NULL
) ENGINE = MyISAM default charset = utf8 |

Delete column:

Alter table person drop column birthday;

Add column:

Alter table person add column birthday datetime;

Modify the column number to bigint:

Alter table person MODIFY number bigint not null;

Or change the number to id and the type is bigint:

Alter table person CHANGE number id BIGINT;

Add primary key:

Alter table person add primary key (id );

Delete primary key:

Alter table person drop primary key;

Add a unique index:

Alter table person add unique name_unique_index ('name ');

A unique index is created for the name column. The index name is name_unique_index.

Add a common index:

Alter table person add index birthday_index ('birthday ');

Delete an index:

Alter table person drop index birthday_index;
Alter table person drop index name_unique_index;

Disable non-unique indexes

Alter table person disable keys;

Alter table... disable keys: stop MySQL from updating non-unique indexes in the MyISAM TABLE.

Activate a non-unique index

Alter table person enable keys;

Alter table... enable keys to recreate the lost index.

Change the default character set and all character columns (CHAR, VARCHAR, TEXT) of the table to the new character set:

Alter table person convert to character set utf8;

Modifies the encoding of a column in a table.

Alter table person CHANGE name varchar (255) character set utf8;

Only change the default character set of a table

Alter table person default character set utf8;

Modify table name

Rename table person TO person_other;

Move a table to another database

Rename table current_db.tbl_name TO other_db.tbl_name;