mysql需要掌握的语法_MySQL系统学习一：MySQL语法概要

先来一组sql中的名词，别被吓着了；看着高大上实际就是一些单纯的名词罢了，理解清楚即可。

DDL—数据定义语言[Data Definition Language](Create，Alter，Drop，SHOW、DECLARE)

DML—数据操纵语言[Data Manipulation Language](Select，Delete，Update，Insert)

DCL—数据控制语言[Data Control Language](GRANT，REVOKE，COMMIT，ROLLBACK)

一、DDL语句

1.1、CREATE关键字

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification [, create_specification] ...]

create_specification:

[DEFAULT] CHARACTER SET charset_name

| [DEFAULT] COLLATE collation_name

其中DATABASE或SCHEMA在mysql层面区别不大；CHARACTER设定数据库的字符集，COLLATE设定数据库的校队集(排序规则)。

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

创建数据表

CREATE TABLE tbl_name (column_define_list)

[DEFAULT CHARACTER SET charset_name [COLLATE collation_name]] [COMMENT = '表注释文字']

其中column_define_list是一些用于定义数据表字段名、字段类型、默认值等语句。

CREATE TABLE IF NOT EXISTS `demo_table`(

`id` INT(10) NOT NULL AUTO_INCREMENT,

`title` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`ename` ENUM('y', 'n', 'x') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'y',

`content` TEXT NOT NULL,

PRIMARY KEY (`id`)

) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = '文章表'

创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[USING index_type]

ON tbl_name (index_col_name,...)

index_col_name:

col_name [(length)] [ASC | DESC]

CREATE INDEX被映射到一个ALTER TABLE语句上，用于创建索引。create index语法仅需知晓，透彻了解alert table语法才是关键。

1.2、ALTER关键字

修改数据库全局特性

ALTER {DATABASE | SCHEMA} [db_name]

alter_specification [, alter_specification] ...

alter_specification:

[DEFAULT] CHARACTER SET charset_name

| [DEFAULT] COLLATE collation_name

修改数据库相关全局特性，基本语法结构与create database类似，通过ALERT关键字更改database基础功能就是修改database的字符集和排序规则。

修改数据表

ALTER [IGNORE] TABLE tbl_name

alter_specification [, alter_specification] ...

alter_specification:

ADD [COLUMN] column_definition [FIRST | AFTER col_name ]

| ADD [COLUMN] (column_definition,...)

| ADD INDEX [index_name] [index_type] (index_col_name,...)

| ADD [CONSTRAINT [symbol]]

PRIMARY KEY [index_type] (index_col_name,...)

| ADD [CONSTRAINT [symbol]]

UNIQUE [index_name] [index_type] (index_col_name,...)

| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)

| ADD [CONSTRAINT [symbol]]

FOREIGN KEY [index_name] (index_col_name,...)

[reference_definition]

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

| CHANGE [COLUMN] old_col_name column_definition

[FIRST|AFTER col_name]

| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

| DROP [COLUMN] col_name

| DROP PRIMARY KEY

| DROP INDEX index_name

| DROP FOREIGN KEY fk_symbol

| DISABLE KEYS

| ENABLE KEYS

| RENAME [TO] new_tbl_name

| ORDER BY col_name

| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

| DISCARD TABLESPACE

| IMPORT TABLESPACE

| table_options

| partition_options

| ADD PARTITION partition_definition

| DROP PARTITION partition_names

| COALESCE PARTITION number

| REORGANIZE PARTITION partition_names INTO (partition_definitions)

| ANALYZE PARTITION partition_names

| CHECK PARTITION partition_names

| OPTIMIZE PARTITION partition_names

| REBUILD PARTITION partition_names

| REPAIR PARTITION partition_names

ALTER TABLE用于更改原有表的结构，是一个必须掌握的sql命令。可以增加或删减字段(列)，创建或取消索引，更改原有字段(列)的类型，或重新命名字段(列)或表；还可以更改表(以及字段)的注释和表的类型。

来几个示例：

二、DML语句

DML语句也就是使用频率非常高的“增(insert)、删(delete)、改(update)、查(select)”，一直以为自己会使用这四个关键字，而其实仅仅是了解了DML语句的初步用法，通过复杂的DML语法可以实现很多以前只会在程序中(例如PHP)实现的功能。

DML语句仅列出相关语法结构，有些语句需要单独列出来汇总学习。

2.1、SELECT关键字

select语句用于从一个或多个表中查询条件指定的行，并可以加入union语句和子查询。语法结构如下：

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr, ...

[INTO OUTFILE 'file_name' export_options

| INTO DUMPFILE 'file_name']

[FROM table_references

[WHERE where_definition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_definition]

[ORDER BY {col_name | expr | position}

[ASC | DESC] , ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[PROCEDURE procedure_name(argument_list)]

[FOR UPDATE | LOCK IN SHARE MODE]]

最常用的select类型：select column_name1[ as alias1],column2[as alias2] from table_name where exp [order by DESC|ASC] [limit n,m]

2.2、DELETE关键字

单表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_definition]

[ORDER BY ...]

[LIMIT row_count]

多表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

tbl_name[.*] [, tbl_name[.*] ...]

FROM table_references

[WHERE where_definition]

或：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

FROM tbl_name[.*] [, tbl_name[.*] ...]

USING table_references

[WHERE where_definition]

2.3、UPDATE关键字

单表语法：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_definition]

[ORDER BY ...]

[LIMIT row_count]

多表语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_references

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_definition]

2.4、INSERT关键字

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

VALUES ({expr | DEFAULT},...),(...),...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name={expr | DEFAULT}, ...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

SELECT ...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

三、DCL语句

DCL语句是数据库控制功能语句，是用来设置或更改数据库用户或角色权限的语句，包括(grant,deny,revoke等)语句，DCL语句不需要一般开发者非常了解，仅需知道有这么个语句即可。一般专业做DBA的数据库管理员才需要深入了解这些语句；默认情况下DCL语句只有系统管理员、数据库管理员才有权限执行。