MySQL Commonly Used to Create Table Add Fields Modify Fields Add Indexes SQL Statement Writing Summary

In this paper, examples of MySQL commonly used to build tables, add fields, modify fields, add index SQL statement writing. Share it for your reference, as follows:

Form building:

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, #  Founder id
 context VARCHAR(600) NOT NULL DEFAULT '', #  Announcement details ( 300 Word) 
 begintime DEC(20) NOT NULL DEFAULT 0, #  Announcement start time 
 endtime DEC(20) NOT NULL DEFAULT 0, #  End time of announcement 
 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 a new field:

alter table bulletin add citycode varchar(6) not null default 0; #  City code 

1. Set encoding when you create a database

create database test character set utf8;

2. Set encoding when creating tables

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

3. Modify the database coding

alter database test character set utf8;

4. Modify the table default encoding

alter table test character set utf8;

5. Modify the field encoding

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

Add index method

1. Add PRIMARY KEY (primary key index)

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

2. Add UNIQUE (only 1 index)

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

3. Add INDEX (Common Index)

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

0

4. Add FULLTEXT (full-text index)

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

1

5. Add a multi-column index

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

2

More readers interested in MySQL can see the topics on this site: "MySQL Common Functions Summary", "MySQL Log Operation Skills Collection", "MySQL Transaction Operation Skills Collection", "MySQL Stored Procedure Skills Collection" and "MySQL Database Lock Related Skills Collection"

I hope this article is helpful to everyone's MySQL database.