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语句只有系统管理员、数据库管理员才有权限执行。

转载请注明本文标题和链接：《MySQL系统学习一：MySQL语法概要》