Common MySQL Commands

1. View the database encoding format

1	mysql> show variables like‘character_set_database‘;

2. View the encoding format of the data table

1	mysql> show createtable<表名>;

3. Specify the character set of the database when creating the database

Mysql>create databases < database name > character set UTF8;

4. Specify the encoding format of the data table when creating the data table

CREATE TABLE Tb_books (name varchar) is not NULL, the price double is not    null,    bookcount int is not NULL,    author V Archar () NOT null) default CharSet = UTF8;

5. Modify the encoding format of the database

Mysql>alter databases < database name > character set UTF8;

6. Modify the data table encoding format

Mysql>alter Table < name > character set UTF8;

7. Modify the field encoding format

Mysql>alter Table < name > Change < Field name > < Field name > < type > Character set utf8;mysql>alter table user Cha Nge username username varchar (character set UTF8 not null;

8. Add a FOREIGN key

Mysql>alter table tb_product Add constraint fk_1 foreign key (Factoryid) references tb_factory (Factoryid);mysql> ALTER TABLE < table name > Add constraint < foreign key name > Foreign key< Field name > REFERENCES < outer table name >< field name >;

9. Delete foreign keys

Mysql>alter table tb_people drop foreign key Fk_1;mysql>alter table < table name > drop FOREIGN Key < foreign key name >;

10. View the Build Table statement

Show CREATE TABLE table_name

11 Building an Index

ALTER TABLE table_name ADD index (COL_NAME)

12 Deleting an index

DROP INDEX COL_NAME on table_name

13. View Index

Show index from TABLE_NAME

　　

14 Viewing the System support engine

Show engines;

15. Create a hash partition

PARTITION by HASH (store_id) Partitions 4

Do not partition according to the data type, partitions is the number of partitions, hash means that the data will be modulo operation, the data into the corresponding partition

　　

　　

　　

Common MySQL commands (go)