A summary of how to write commonly used SQL statements in MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

A summary of how to write commonly used SQL statements in MySQL

May 22, 2017 pm 02:15 PM

mysql sql statement Add index

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 &#39;&#39;, # 公告详细内容（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;

Copy after login

Modify the original field name and Type:

ALTER TABLE bulletin CHANGE uid username VARCHAR(50) NOT NULL DEFAULT &#39;&#39;;

Copy after login

Add new field:

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

Copy after login

1. Set encoding when creating database

create database test character set utf8;

Copy after login

2. Create table Set the encoding

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

Copy after login

3. Modify the database encoding

alter database test character set utf8;

Copy after login

4. Modify the table default encoding

alter table test character set utf8;

Copy after login

5. Modify the field encoding

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

Copy after login

Add index method

1. Add PRIMARY KEY (primary key index)

mysql&gt;ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

Copy after login

2. Add UNIQUE (unique index)

mysql&gt;ALTER TABLE `table_name` ADD UNIQUE (
`column`
)

Copy after login

3 .Add INDEX (normal index)

mysql&gt;ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

Copy after login

4.Add FULLTEXT (full-text index)

mysql&gt;ALTER TABLE `table_name` ADD FULLTEXT (
`column`
)

Copy after login

5.Add multi-column index

mysql&gt;ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

Copy after login

[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!

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn