SQL语言分类

• DQL 数据查询语言
• DML 数据操作语言
• DCL 数据控制语言
• DDL 数据定义语言
• DTL 数据交易语言

---

DQL (Data Query Language)

• SELECT
• SHOW
• HELP

简单查询

SELECT column_name_1[, column_name_2]
FROM [db_name.]table_name

条件查询

SELECT column_name_1[, column_name_2]
FROM [db_name.]table_name
WHERE column_name_1=number/"string"

---

SELECT column_name_1[, column_name_2]
FROM [db_name.]table_name
WHERE column_name_2
[NOT] IN ("string_1","string_2")

---

SELECT column_name_1[, column_name_2]
FROM [db_name.]table_name
WHERE column_name_1
BETWEEN number_1 AND number_2

组合条件查询

SELECT column_name_1[, column_name_2]
FROM [db_name.]table_name
WHERE column_name_1=number_1/"string_1"
AND|OR column_name_2=number_2/"string_2"

逻辑查询

SELECT column_name_1[, column_name_2]
FROM [db_name.]table_name
WHERE column_name_1<number_1
AND|OR column_name_2>number_2

---

SELECT column_name_1[, column_name_2]
FROM [db_name.]table_name
WHERE column_name_1>number_1
AND/OR column_name_2 [NOT] IN ("string_1","string_2")

DML (Data Manipulation Language)

• INSERT
• UPDATE
• DELETE
• CALL
• EXPLAIN PLAN
• LOCK TABLE
• MERGE

插入

INSERT INTO [db_name.]table_name (column_name_1, column_name_2,...)
VALUES (value_list_1, value_list_2);

ALTER

ALTER TABLE table_name
RENAME new_table_name;

---

ALTER TABLE table_name
MODIFY column_name new_column_type;

---

ALTER TABLE table_name
CHANGE column_name new_column_name [new_]column_type;

---

ALTER TABLE table_name
ADD column_name column_type [CONSTRAINT] [FIRST|AFTER column];

---

ALTER TABLE table_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name(column_name[(length)] [ASC|DESC]);

---

ALTER TABLE table_name
DROP column_name;

---

ALTER TABLE table_name
MODIFY column_name_1 FIRST|AFTER column_name_2;

---

ALTER TABLE table_name
ENGINE=MyISAM|InnoDB;

---

ALTER TABLE table_name
DROP PRIMARY KEY pri_key_name;

---

ALTER TABLE table_name
DROP FOREIGN KEY for_key_name;

DDL (Data Definition Language)

• CREATE
• ALTER
• DROP
• TRUNCATE
• COMMIT
• RENAME

CREATE

CREATE DATABASE db_name;

---

CREATE TABLE table_name (
column_name_1 TYPE [CONSTRAINT],
column_name_2 TYPE [CONSTRAINT],
CONSTRAINT 约束名
);

---

CREATE TABLE table_name (
column_name_1 TYPE [CONSTRAINT],
column_name_2 TYPE [CONSTRAINT],
CONSTRAINT 约束名
) AUTO_INCREMENT=num;

---

CREATE TABLE table_name (
column_name_1 TYPE [CONSTRAINT],
column_name_2 TYPE [CONSTRAINT],
...
[UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (column_name[(length)] [ASC|DESC])
);

---

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(column_name[(length)]);

---

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(column_name_1,column_name_2);

DROP

DROP DATABASE db_name;

---

DROP TABLE table_name;

---

DROP INDEX index_name ON table_name;

CONSTRAINT

• NOT NULL
• PRIMARY KEY
• FOREIGN KEY
• AUTO_INCREMENT
• UNIQUE

---

DCL (Data Control Language)

• GRANT
• REVOKE

DTL (Data Transaction Language)

• COMMIT
• ROLLBACK
• SAVEPOINT
• START TRANSACTION