设计mysql表时如何预留_史上最简单MySQL教程详解（基础篇）之SQL语句以及预留关键字介绍...

SQL简单介绍

前面我们已经介绍过，通过编写命令的方式实现对数据库的操作，这个以字符串的形式存在的命令被称为查询(Query).。当我们编写这些Query的时候，这些语句就被称为SQL语句。SQL代表结构化查询语言(Structured Query Language)。SQL是用于访问数据库的标准化语言。SQL语句主要分为以下三种类型的语句：

数据操作语句 (Data Manipulation Language,DML)

这个就是我们接下来将主要介绍的重点了，主要包括了【SELECT】,【INSERT】，【UPDATE】,【DELETE】语句。

数据定义语句 (Data Definition Language ,DDL)

这个前面我们已经接触非常多的对于表，索引，视图等使用的【CREATE】,【DROP】,【ALTER】等语句。

数据控制语句 (Data Control Language ,DCL)

这个就是我们前面在用户管理时接触到的【GRANT】,【REVOKE】；还有我们后面高级教程中事物处理即将使用到的【COMMIT】,【BEGIN】,【COMMIT】,【ROLLBACK】等语句。

这里只需要对于SQL的分类有个大概的了解就可以了。不需要大家深入了解。

SQL语句使用规则(监视器)

规则一：SQL语句必须有结束符

其实在前面我们已经对于在监视器中敲SQL命令y有了一个比较深刻的感觉了。通常的SQL语句是必须以【;】作为语句的结束符，如果在输入SQL语句的时候忘记输入了结束符，监视器会认为用户尚未输入完成，会自动跳到下一行继续等待用户输入，这个时候只需要将结尾符补上即可。当然，结尾不仅仅只有【;】还有【\G】这种形式，具体的使用情况前面我们也已经进行了举例说明。

规则二：保留关键字不区分大小写关键字

保留关键字是SQL事先定义好了的，如我们之前已经提到的【SELECT】,【ALTER】等就属于关键字的范围。在SQL语句中这些关键字是不区分大小写的。但是为了让SQL语句更加方便阅读和理解，我们通常：以大写的形式表达预留的关键字，以小写的形式表示数据库，表，和列名。同时，当我们在创建表，列时，尽量要避免我们的命名与保留字重复或者冲突，以免造成不可预知的错误，这样错误的排查将变得十分的困难，我也将在后面贴出MySQL5.7版本的预留关键字，大家可以大致了解一下。

规则三：可自由地加入空白或者换行符

一个SQL语句可以作为一行来编写，但是当我们编写比较复杂的SQL语句的时候，就会发现变得异常的麻烦，所以SQL就加入了这一特点。但是需要注意的是在一个关键字的中间加入空格或者换行符是不允许的。

例如：

mysql> sel

-> ect * from user;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘sel

ect * from user’ at line 1

规则四：使用【- -】或者【/*…*/】注释

【- -】主要是用于单行注释，【/*…*/】主要是进行多行注释

MySQL5.7的保留关键字一览表

关键字

关键字

关键字

ACCESSIBLE (R)

ACCOUNT ACTION

ADD (R)

AFTER

AGAINST

AGGREGATE

ALGORITHM

ALL (R)

ALTER (R)

ALWAYS

ANALYSE

ANALYZE (R)

AND (R)

ANY

AS (R)

ASC (R)

ASCII

ASENSITIVE (R)

AT

AUTOEXTEND_SIZE

AUTO_INCREMENT

AVG

AVG_ROW_LENGTH

BACKUP

BEFORE (R)

BEGIN

BETWEEN (R)

BIGINT (R)

BINARY (R)

BINLOG

BIT

BLOB (R)

BLOCK

BOOL

BOOLEAN

BOTH (R)

BTREE

BY (R)

BYTE

CACHE

CALL (R)

CASCADE (R)

CASCADED

CASE (R)

CATALOG_NAME

CHAIN

CHANGE (R)

CHANGED

CHANNEL[C]

CHAR (R)

CHARACTER (R)

CHARSET

CHECK (R)

CHECKSUM

CIPHER

CLASS_ORIGIN

CLIENT

CLOSE

COALESCE

CODE

COLLATE (R)

COLLATION

COLUMN (R)

COLUMNS

COLUMN_FORMAT

COLUMN_NAME

COMMENT

COMMIT

COMMITTED

COMPACT

COMPLETION

COMPRESSED

COMPRESSION并[d]

CONCURRENT

CONDITION (R)

CONNECTION

CONSISTENT

CONSTRAINT (R)

CONSTRAINT_CATALOG

CONSTRAINT_NAME

1CONSTRAINT_SCHEMA

CONTAINS

CONTEXT

CONTINUE (R)

CONVERT (R)

CPU

CREATE (R)

CROSS (R)

CUBE

CURRENT

CURRENT_DATE (R)

CURRENT_TIME (R)

CURRENT_TIMESTAMP (R)

CURRENT_USER (R)

CURSOR (R)

CURSOR_NAME

DATA

DATABASE (R)

DATABASES (R)

DATAFILE

DATE

DATETIME

DAY

DAY_HOUR (R)

DAY_MICROSECOND (R)

DAY_MINUTE (R)

DAY_SECOND (R)

DEALLOCATE

DEC (R)

DECIMAL (R)

DECLARE (R)

DEFAULT (R)

DEFAULT_AUTH

DEFINER

DELAYED (R)

DELETE (R)

DESC (R)

DESCRIBE (R)

DES_KEY_FILE

DETERMINISTIC (R)

DIAGNOSTICS

DIRECTORY

DISABLE

DISCARD

DISK

DISTINCT (R)

DISTINCTROW (R)

DIV (R)

DO

DOUBLE (R)

DROP (R)

DUAL (R)

DUMPFILE

DUPLICATE

DYNAMIC

EACH (R)

ELSE (R)

ELSEIF (R)

ENABLE

ENCLOSED (R)

ENCRYPTION[e]

END

ENDS

ENGINE

ENGINES

ENUM

ERROR

ERRORS

ESCAPE

ESCAPED (R)

EVENT

EVENTS

EVERY

EXCHANGE

EXECUTE

EXISTS (R)

EXIT (R)

EXPANSION

EXPIRE

EXPLAIN (R)

EXPORT

EXTENDED

EXTENT_SIZE

FALSE (R)

FAST

FAULTS

FETCH (R)

FIELDS

FILE

FILE_BLOCK_SIZE[F]

FILTER[G]

FIRST

FIXED

FLOAT (R)

FLOAT4 (R)

FLOAT8 (R)

FLUSH

FOLLOWS[H]

FOR (R)

FORCE (R)

FOREIGN (R)

FORMAT

FOUND

FROM (R)

FULL

FULLTEXT (R)

FUNCTION

GENERAL

GENERATED[i](R)

GEOMETRY

GEOMETRYCOLLECTION

GET (R)

GET_FORMAT

GLOBAL

GRANT (R)

GRANTS

GROUP (R)

GROUP_REPLICATION[j]

HANDLER

HASH

HAVING (R)

HELP

HIGH_PRIORITY (R)

HOST

HOSTS

HOUR

HOUR_MICROSECOND (R)

HOUR_MINUTE (R)

HOUR_SECOND (R)

IDENTIFIED

IF (R)

IGNORE (R)

IGNORE_SERVER_IDS

IMPORT

IN (R)

INDEX (R)

INDEXES

INFILE (R)

INITIAL_SIZE

INNER (R)

INOUT (R)

INSENSITIVE (R)

INSERT (R)

INSERT_METHOD

INSTALL

INSTANCE[K]

INT (R)

INT1 (R)

INT2 (R)

INT3 (R)

INT4 (R)

INT8 (R)

INTEGER (R)

INTERVAL (R)

INTO (R)

INVOKER

IO

IO_AFTER_GTIDS (R)

IO_BEFORE_GTIDS (R)

IO_THREAD

IPC

IS (R)

ISOLATION

ISSUER

ITERATE (R)

JOIN (R)

JSON[1]

KEY (R)

KEYS (R)

KEY_BLOCK_SIZE

KILL (R)

LANGUAGE

LAST

LEADING (R)

LEAVE (R)

LEAVES

LEFT (R)

LESS

LEVEL

LIKE (R)

LIMIT (R)

LINEAR (R)

LINES (R)

LINESTRING

LIST

LOAD (R)

LOCAL

LOCALTIME (R)

LOCALTIMESTAMP (R)

LOCK (R)

LOCKS

LOGFILE

LOGS

LONG (R)

LONGBLOB (R)

LONGTEXT (R)

LOOP (R)

LOW_PRIORITY (R)

MASTER

MASTER_AUTO_POSITION

MASTER_BIND (R)

MASTER_CONNECT_RETRY

MASTER_DELAY

MASTER_HEARTBEAT_PERIOD

MASTER_HOST

MASTER_LOG_FILE

MASTER_LOG_POS

MASTER_PASSWORD

MASTER_PORT

MASTER_RETRY_COUNT

MASTER_SERVER_ID

MASTER_SSL

MASTER_SSL_CA

MASTER_SSL_CAPATH

MASTER_SSL_CERT

MASTER_SSL_CIPHER

MASTER_SSL_CRL

MASTER_SSL_CRLPATH

MASTER_SSL_KEY

MASTER_SSL_VERIFY_SERVER_CERT (R)

MASTER_TLS_VERSION

MASTER_USER

MATCH (R)

MAXVALUE (R)

MAX_CONNECTIONS_PER_HOUR

MAX_QUERIES_PER_HOUR

MAX_ROWS

MAX_SIZE

MAX_STATEMENT_TIME[n]

MAX_UPDATES_PER_HOUR

MAX_USER_CONNECTIONS

MEDIUM

MEDIUMBLOB (R)

MEDIUMINT (R)

MEDIUMTEXT (R)

MEMORY

MERGE

MESSAGE_TEXT

MICROSECOND

MIDDLEINT (R)

MIGRATE

MINUTE

MINUTE_MICROSECOND (R)

MINUTE_SECOND (R)

MIN_ROWS

MOD (R)

MODE

MODIFIES (R)

MODIFY

MONTH

MULTILINESTRING

MULTIPOINT

MULTIPOLYGON

MUTEX

MYSQL_ERRNO

NAME

NAMES

NATIONAL

NATURAL (R)

NCHAR

NDB

NDBCLUSTER

NEVER[O]

NEW

NEXT

NO

NODEGROUP

NONBLOCKING[P]

NONE

NOT (R)

NO_WAIT

NO_WRITE_TO_BINLOG (R)

NULL (R)

NUMBER

NUMERIC (R)

NVARCHAR

OFFSET

OLD_PASSWORD[Q]

ON (R)

ONE

ONLY

OPEN

OPTIMIZE (R)

OPTIMIZER_COSTS[r](R)

OPTION (R)

OPTIONALLY (R)

OPTIONS

OR (R)

ORDER (R)

OUT (R)

OUTER (R)

OUTFILE (R)

OWNER

PACK_KEYS

PAGE

PARSER

PARSE_GCOL_EXPR[S]

PARTIAL

PARTITION (R)

PARTITIONING

PARTITIONS

PASSWORD

PHASE

PLUGIN

PLUGINS

PLUGIN_DIR

POINT

POLYGON

PORT

PRECEDES[T]

PRECISION (R)

PREPARE

PRESERVE

PREV

PRIMARY (R)

PRIVILEGES

PROCEDURE (R)

PROCESSLIST

PROFILE

PROFILES

PROXY

PURGE (R)

QUARTER

QUERY

QUICK

RANGE(R)

READ (R)

READS (R)

READ_ONLY

READ_WRITE (R)

REAL (R)

REBUILD

RECOVER

REDOFILE

REDO_BUFFER_SIZE

REDUNDANT

REFERENCES (R)

REGEXP (R)

RELAY

RELAYLOG

RELAY_LOG_FILE

RELAY_LOG_POS

RELAY_THREAD

RELEASE (R)

RELOAD

REMOVE

RENAME (R)

REORGANIZE

REPAIR

REPEAT (R)

REPEATABLE

REPLACE (R)

REPLICATE_DO_DB[U]

REPLICATE_DO_TABLE[V]

REPLICATE_IGNORE_DB[W]

REPLICATE_IGNORE_TABLE[X]

REPLICATE_REWRITE_DB[Y]

REPLICATE_WILD_DO_TABLE[Z]

REPLICATE_WILD_IGNORE_TABLE[AA]

REPLICATION

REQUIRE (R)

RESET

RESIGNAL (R)

RESTORE

RESTRICT (R)

RESUME

RETURN (R)

RETURNED_SQLSTATE

RETURNS

REVERSE

REVOKE (R)

RIGHT (R)

RLIKE (R)

ROLLBACK

ROLLUP

ROTATE[AB]

ROUTINE

ROW

ROWS

ROW_COUNT

ROW_FORMAT

RTREE

SAVEPOINT

SCHEDULE

SCHEMA (R)

SCHEMAS (R)

SCHEMA_NAME

SECOND

SECOND_MICROSECOND (R)

SECURITY

SELECT (R)

SENSITIVE (R)

SEPARATOR (R)

SERIAL

SERIALIZABLE

SERVER

SESSION

SET (R)

SHARE

SHOW (R)

SHUTDOWN

SIGNAL (R)

SIGNED

SIMPLE

SLAVE

SLOW

SMALLINT (R)

SNAPSHOT

SOCKET

SOME

SONAME

SOUNDS

SOURCE

SPATIAL (R)

SPECIFIC (R)

SQL (R)

SQLEXCEPTION (R)

SQLSTATE (R)

SQLWARNING (R)

SQL_AFTER_GTIDS

SQL_AFTER_MTS_GAPS

SQL_BEFORE_GTIDS

SQL_BIG_RESULT (R)

SQL_BUFFER_RESULT

SQL_CACHE

SQL_CALC_FOUND_ROWS (R)

SQL_NO_CACHE

SQL_SMALL_RESULT (R)

SQL_THREAD

SQL_TSI_DAY

SQL_TSI_HOUR

SQL_TSI_MINUTE

SQL_TSI_MONTH

SQL_TSI_QUARTER

SQL_TSI_SECOND

SQL_TSI_WEEK

SQL_TSI_YEAR

SSL (R)

STACKED

START

STARTING (R)

STARTS

STATS_AUTO_RECALC

STATS_PERSISTENT

STATS_SAMPLE_PAGES

STATUS

STOP

STORAGE

STORED[ac](R)

STRAIGHT_JOIN (R)

STRING

SUBCLASS_ORIGIN

SUBJECT

SUBPARTITION

SUBPARTITIONS

SUPER

SUSPEND

SWAPS

SWITCHES

TABLE (R)

TABLES

TABLESPACE

TABLE_CHECKSUM

TABLE_NAME

TEMPORARY

TEMPTABLE

TERMINATED (R)

TEXT

THAN

THEN (R)

TIME

TIMESTAMP

TIMESTAMPADD

TIMESTAMPDIFF

TINYBLOB (R)

TINYINT (R)

TINYTEXT (R)

TO (R)

TRAILING (R)

TRANSACTION

TRIGGER (R)

TRIGGERS

TRUE (R)

TRUNCATE

TYPE

TYPES

UNCOMMITTED

UNDEFINED

UNDO (R)

UNDOFILE

UNDO_BUFFER_SIZE

UNICODE

UNINSTALL

UNION (R)

UNIQUE (R)

UNKNOWN

UNLOCK (R)

UNSIGNED (R)

UNTIL

UPDATE (R)

UPGRADE

USAGE (R)

USE (R)

USER

USER_RESOURCES

USE_FRM

USING (R)

UTC_DATE (R)

UTC_TIME (R)

UTC_TIMESTAMP (R)

VALIDATION

VALUE

VALUES (R)

VARBINARY (R)

VARCHAR (R)

VARCHARACTER (R)

VARIABLES

VARYING (R)

VIEW

VIRTUAL[ae](R)

WAIT

WARNINGS

WEEK

WEIGHT_STRING

WHEN (R)

WHERE (R)

WHILE (R)

WITH (R)

WITHOUT[AF]

WORK

WRAPPER

WRITE (R)

X509

XA

XID[AG]

XML

XOR (R)

YEAR

YEAR_MONTH (R)

ZEROFILL (R)