Summary of commonly used SQL statement writing methods such as table creation in MySQL

This article mainly introduces the commonly used MySQL SQL statements for creating tables, adding fields, modifying fields, and adding indexes. It summarizes and analyzes the SQL statements involved in MySQL table creation, encoding settings, field additions, and index operations. Friends who need it can For reference, I hope it can help everyone.

Create table:

DROP TABLE IF EXISTS bulletin;
CREATE TABLE bulletin(
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, # 主键
 uid INT(11) NOT NULL DEFAULT 0, # 创建者id
 context VARCHAR(600) NOT NULL DEFAULT '', # 公告详细内容（300字）
 begintime DEC(20) NOT NULL DEFAULT 0, # 公告开始时间
 endtime DEC(20) NOT NULL DEFAULT 0, # 公告结束时间
 createtime DEC(20) NOT NULL DEFAULT 0, # 创建时间
 modifytime DEC(20) NOT NULL DEFAULT 0 # 修改时间
 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 field:

alter table bulletin add citycode varchar(6) not null default 0; # 城市代码

1 .Set the encoding when creating the database

create database test character set utf8;

2.Set the encoding when creating the table

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

3.Modify the database encoding

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 (unique index)

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

3. Add INDEX (normal 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 index

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

Related recommendations:

Mysql Sql statement annotation collection example sharing

How Laravel records SQL statements

How to automatically generate SQL statements

The above is the detailed content of Summary of commonly used SQL statement writing methods such as table creation in MySQL. For more information, please follow other related articles on the PHP Chinese website!