MySQL commonly used statements for table creation, adding fields, modifying fields, and adding indexes.

MySQL commonly used statements for table creation, adding fields, modifying fields, and adding indexes.

This article describes the common SQL statements used by MySQL to create tables, add fields, modify fields, and add indexes. We will share this with you for your reference. The details are as follows:

Table creation:

Drop table if exists bulletin; create table bulletin (id int not null primary key AUTO_INCREMENT, # primary key uid INT (11) not null default 0, # creator id context VARCHAR (600) not null default '', # announcement details (300 words) begintime DEC (20) not null default 0, # announcement start time endtime DEC (20) not null default 0, # announcement end time createtime DEC (20) not null default 0, # creation time modifytime DEC (20) not null default 0 # modification time primary key ('id '),) default charset = UTF8 TYPE = INNODB;

Modify the original field name and type:

ALTER TABLE bulletin CHANGE uid username VARCHAR(50) NOT NULL DEFAULT '';

Add new fields:

Alter table bulletin add citycode varchar (6) not null default 0; # city code

1. Set encoding when creating a database

create database test character set utf8;

2. Set the encoding when creating a table

create table test(id int primary key)DEFAULT charset=utf8;

3. Modify the database code

alter database test character set utf8;

4. Modify the default table encoding.

alter table test character set utf8;

5. Modify the field encoding.

alter table test modify col_name varchar(50) CHARACTER SET utf8;

How to add an index

1. Add the primary key (primary key index)

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2. add UNIQUE (UNIQUE index)

mysql>ALTER TABLE `table_name` ADD UNIQUE (`column`)

3. Add an INDEX (Common INDEX)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4. Add FULLTEXT (full-text index)

mysql>ALTER TABLE `table_name` ADD FULLTEXT (`column`)

5. Add multi-column Indexes

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )