数据库

create database [IF NOT EXISTS] <数据库名>
USE <数据库名>
ALTER DATABASE [数据库名] {[DEFAULT] CHARACTER SET <字符集名> | [DEFAULT] COLLATE <校对规则名>}
DROP DATABASE [IF EXISTS] <数据库名>
SHOW DATABASES [LIKE <pattern>]

表定义

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项]
  eg. CREATE TABLE students(
        student_id INT NOT NULL AUTO_INCREMENT,
        student_name CHAR(50) NOT NULL,
        PRIMARY KEY(student_id)
        ) ENGINE = InnoDB

数据类型：BIT, TINYINT, BOOL/BOOLEAN, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, DATE, DATETIME, TIMESTAMP, TIME, YEAR, CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, ENUM
引擎类型：SHOW ENGINE

ALTER TABLE <表名> {ADD COLUMN 列名 类型| CHANGE COLUMN 旧列名 新列名 新类型| ALTER COLUMN 列名{SET DEFAULT 默认值| DROP DEFAULT}| MODIFY COLUMN 列名 类型| DROP COLUMN 列名| RENAME TO 新表名}
RENAME TABLE 表名 TO 新表名
CREATE TABLE 表名 [LIKE | AS 旧表名]  /*复制*/
DROP TABLE 表名[,表名2...]
SHOW TABLES [FROM 数据库名] [LIKE pattern]
SHOW [FULL] COLUMNS FROM tbl_name [FROM database_name][LIKE pattern] # DESCRIBE 快捷方式

表操作

SET NAMES GBK /* for Chinese characters */
INSERT INTO tbl_name [(column_name ...)] VALUES (value...)
REPLACE INTO tbl_name [(column_name ...)] {VALUES (value...)| SET col=val| SELECT ...}
DELETE FROM tbl_name [WHERE ...][ORDER BY col][LIMIT ...]
DELETE tbl1[,tbl2 FROM tbl1[,tbl2,tbl3...][WHERE condition]
TRUNCATE TABLE tbl_name
UPDATE tbl_name[,tbl2,tbl3...] SET col=val[,col2=val2...][WHERE ...][ORDER BY ...][LIMIT ...]
SELECT col1[,col2...] FROM tbl_name [WHERE ...][GROUP ...][HAVING ...][ORDER BY ...][LIMIT ...]
    CASE 
    WHEN condition THEN expression
    WHEN condition THEN expression
    ...
    ELSE
      END [AS alias]
    CROSS JOIN
    tbl1 INNER JOIN tbl2 ON condition
    NATURAL JOIN
    LEFT OUTER JOIN / RIGHT OUTER JOIN
    WHERE
        WHERE ... {=| <| >| |<= |>= |<=> |<> |!= } {ALL |SOME |ANY} ...
        WHERE ... [NOT] LIKE ...    //通配符 % _
        WHERE ... [NOT] [REGEXP|RLIKE] ...    //LIKE 和 REGLIKE区别，LIKE 'A' 只匹配值为‘A’ 而 REGLIKE 'A' 匹配任何包含'A'的字符串
    WHERE ... [NOT] BETWEEN ... AND ...
        WHERE ... IS [NOT] NULL
        WHERE ... [NOT] IN (val1,val2...)
    GROUP BY ... HAVING ...  //HAVING 和 WHERE 区别, HAVING 分组后过滤，可包含聚合函数，WHERE相反
    UNION [DISTINCT |ALL] //默认DISTINCT

索引

INDEX / KEY
UNION
PRIMARY KEY
CREATE INDEX index_name ON tbl_name(col[(length)][ASC |DESC][USING BTREE|HASH]
SHOW INDEX FROM tbl_name [FROM db_name]
DROP INDEX index_name ON tbl_name
ALTER TABLE tbl_name {DROP PRIMARY KEY| DROP INDEX index_name |DROP FOREIGN KEY foreign_key}

在CREATE TABLE语句内使用：

KEY|INDEX [index_name][index_type](col1,col2...)
UNIQUE [INDEX|KEY] [index_name][index_type](col1,col2...)
FOREIGN KEY index_name col

在ALTER TABLE语句内使用：

ADD INDEX [index_name][index_type](col1,col2...)
ADD PRIMARY KEY [index_type](col1,col2...)
ADD UNIQUE [INDEX|KEY][index_name][index_type](col1,col2...)
ADD FOREIGN KEY [index_name](col1,col2...)

视图

CREATE VIEW view_name AS select_statement [WITH CHECK OPTION]   //修改视图时检查插入的数据是否符合WHERE设置的条件
DROP VIEW view1[,view2...]
ALTER VIEW view_name AS select_statement
SHOW CREATE VIEW view_name

完整性

实体完整性

PRIMARY KEY
UNIQUE

参照完整性

CREATE TABLE语句内：

FOREIGN KEY(key_name)
REFERENCES tbl2[(col[(length)])]
[ASC |DESC][MATCH FULL| MATCH PARTIAL| MATCH SIMPLE]
ON [DELETE |UPDATE] [RESTRICT |CASCADE |SET NULL |NO ACTION] //限制，级联，置空，不采取实施策略

外键指向的列/列组合必须是父表中的唯一确定的（取自主键或候选键）

用户定义完整性

NOT NULL
CHECK(expression)

命名完整性

CONSTRAINT 约束名 约束定义

更新完整性

ALTER TABLE语句内：
ADD FOREIGN KEY [索引名](col,...)
DROP PRIMARY KEY
DROP FOREIGN KEY 外键名

表维护

ANALYZE TABLE tbl1[,tbl2...]
CHECKSUM TABLE tbl1[,tbl2...] [QUICK |EXTENDED]
CHECK TABLE tbl1[,tbl2...][FOR UPGRADE| QUICK| FAST| MEDIUM| EXTENDED| CHANGED]
REPAIRE TABLE tbl1[,tbl2...][QUICK| EXTENDED| USE_FRM]
OPTIMIZE TABLE tbl1[,tbl2...]

触发器

CREATE TRIGGER trigger_name {BEFORE |AFTER} {INSERT |UPDATE |DELETE} ON tbl FOR EACH ROW trigger_body
DROP TRIGGER [IF EXISTS] [db_name.]trigger_name
INSERT触发器可用NEW虚拟表  
UPDATE触发器可用OLD, NEW虚拟表，OLD只读，涉及对触发表本身修改时只能用BEFORE  
DELETE触发器可用OLD虚拟表，只读  
同一表只能有6个触发器，不可修改只能删除重新创建

事件

SET GLOBAL EVENT_SCHEDULER = TRUE
CREATE EVENT event_name ON SCHEDULE schedule DO event_body
ALTER EVENT event_name [RENAME TO new_event_name][DO event_body][ENABLE | DISABLE]
DROP EVENT [IF EXISTS] event_name

schedule 格式 AT 子句或 EVERY 子句：

AT timestamp [+ INTERVAL time_span]...
EVERY time_span [STARTS timestamp [+ INTERVAL time_span]...] [ENDS timestamp [+ INTERVAL time_span]...]

time_span格式：
YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, WEEK, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND

event_body 多条语句应用BEGIN...END包裹

存储过程

DELIMITER ??
CREATE PROCEDURE procedure_name([parameters]) body
CALL precedure_name[([param1,param2...])]    //无参数时可省略括号
DROP {PROCEDURE|FUNCTION} [IF EXISTS] procedure_name

参数格式：

[IN |OUT |INOUT] para_name para_type

body内可用语句：

DECLARE var_name[,...] var_type [DEFAULT val]   //此处为局部变量，与用户变量、系统变量区别：用户变量用@开头(SET @var = val)，系统变量@@开头
SET var = val[,var2=val2...]    //声明后才可用SET
SELECT col[,...] INTO var[,...] ...

IF ... THEN ...
[ELSEIF ... THEN ...]
[ELSE ...]
END IF

CASE param
WHEN val THEN ...
[WHEN val2 THEN ...]
[ELSE ...]
END CASE

CASE
WHEN condition THEN ...
[WHEN condition THEN ...]
END CASE

<tag> LOOP
body        //LEAVE <tag> 退出循环，否则一直循环
END LOOP <tag>

<tag> WHILE condition DO
body
END WHILE<tag>

<tag> REPEAT
body
UNTIL condition
END REPEAT<tag>

DECLARE cursor_name CURSOR FOR select_statement
OPEN cursor_name
FETCH cursor_name INTO val[,val2...]    //取一行
CLOSE cursor_name

存储函数

DELIMITER ??                            //修改语句结束标志
CREATE FUNCTION fun_name([var1 type1[,var2,type2,...]])
    RETURNS type
    body??                          //body内语句用;隔开

SELECT fun_name([params])
DROP FUNCTION [IF EXISTS] fun_name

用户帐号管理

SELECT * FROM mysql.user
CREATE USER user_name [IDENTIFIED] BY [PASSWORD] key    //关键字PASSWORD取消对key的散列加密
DROP USER user1[,user2...]
RENAME USER user_old TO user_new
SET PASSWORD [FOR user] = { PASSWORD('password') | 'hash' }
GRANT privileges[(col,...)] ON {*|db}.{*|tbl} TO user [IDENTIFIED BY [PASSWORD] key] [WITH GRANT OPTION]
REVOKE privileges[(col,...)] ON {*|db}.{*|tbl} FROM user1[,user2...]
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1[,user2...]