A summary of how to write commonly used SQL statements 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. The required Friends can refer to

. This article describes the commonly used SQL statement writing methods of creating tables, adding fields, modifying fields, and adding indexes in MySQL. Share it with everyone for your reference, the details are as follows:

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 encoding when creating database

create database test character set utf8;

2. Create table Set the encoding

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]

1. Mysql free video tutorial

2. Detailed explanation of how to use MySQL to generate random numbers and connect strings

3.How to improve the speed of database query for millions of items

4. 5 common database design mistakes

5. The original content of Mysql is not Detailed explanation of the sql statement that changes and adds content later

The above is the detailed content of A summary of how to write commonly used SQL statements in MySQL. For more information, please follow other related articles on the PHP Chinese website!