MySQL操作概述

从上一个$开始到下一个$结束的字符串代表占位串，内容就是其字符串中说明的意思；/* */中的内容为可有可无。

使用MySQL

从终端连接MySQL：

mysql -h $IP地址$ -P $端口号$ -u $用户名$ -p$密码$ /*-D $数据库名$*/

在MySQL中查看帮助：

HELP/* $具体命令名$*/;

退出MySQL：

EXIT;

选择数据库：

USE $数据库名$;

列举存储的所有数据库：

SHOW DATABASES;

选中数据库后列举其中所有的数据表：

SHOW TABLES;

列举指定数据表中所有字段（即列）的信息（字段名、数据类型、是否允许NULL、键信息和默认值等）：

SHOW COLUMNS FROM $数据表名$;
#即
DESCRIBE $数据表名$;

其他关于SHOW的操作：

SHOW STATUS;#显示广泛的服务器状态信息
SHOW CREATE DATABASE $数据库名$;#显示已创建的数据库
SHOW CREATE TABLE $数据表名$;#选中数据库后显示已创建的数据表
SHOW GRANTS;#显示授予用户的安全权限
SHOW ERRORS;#显示服务器错误
SHOW WARNINGS;#显示服务器警告消息

检索数据

检索表中一个或多个列：

SELECT $列名1$/*, $列名2$, ..., $列名n$*/ FROM $数据表名$;

检索表中所有列：

SELECT * FROM $数据表名$;

检索表中某列不同行（去重）：

SELECT DISTINCT $列名$ FROM $数据表名$;

检索不多于n行：

SELECT $列名$ FROM $数据表名$ LIMIT n;

检索从第a行开始共b行：

SELECT $列名$ FROM $数据表名$ LIMIT a, b;

限定列名：

SELECT $数据表名$.$列名$ FROM $数据表名$;

也可以限定数据表名：

SELECT $数据表名$.$列名$ FROM $数据库名$.$数据表名$;

排序检索数据

对数据进行（按列1~m）排序（默认升序ASC，可不写）：

SELECT $列名1$/*, $列名2$, ..., $列名n$*/ FROM $数据表名$ ORDER BY $列名1$ ASC/*, $列名2$, ..., $列名m(m <= n)$*/;

降序排序：

SELECT $列名$ FROM $数据表名$ ORDER BY $列名1$ DESC;

过滤数据

对数据进行过滤（当表达式成立时）：

SELECT $列名$ FROM $数据表名$ WHERE $表达式$;

列举出列1在区间[a, b]间的数据：

SELECT $列名1$ FROM $数据表名$ WHERE $列名1$ BETWEEN a AND b;

检测空值（NULL，即无值）：

SELECT $列名1$ FROM $数据表名$ WHERE $列名2$ IS NULL;

以2个条件“与”对数据进行过滤：

SELECT $列名$ FROM $数据表名$ WHERE $表达式1$ AND $表达式2$;

以2个条件“或”对数据进行过滤：

SELECT $列名$ FROM $数据表名$ WHERE $表达式1$ OR $表达式2$;

以某个集合(a, b, c, d, e)对数据进行过滤：

SELECT $列名1$, $列名2$ FROM $数据表名$ WHERE $列名2$ IN (a, b, c, d, e);

以某个集合(a, b, c, d, e)的补集对数据进行过滤：

SELECT $列名1$, $列名2$ FROM $数据表名$ WHERE $列名2$ NOT IN (a, b, c, d, e);

用通配符进行过滤

%通配符（表示任何字符出现任意次数包括0次）：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ LIKE 'abc%';

_通配符（表示任何字符出现1次）：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ LIKE '_abc';

用正则表达式进行搜索

搜索包含字符串'abc'的数据：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP 'abc';

用.表示任意一个字符：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP '.abc';

用|进行匹配（包含其中一个子串即可）：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP 'abc|def';

用[]进行匹配（包含集合中任一字符即可）：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP '[abc]def';

查找一字符集的补集：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP '[^abc]def';

[]简写：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP '[1-6]789';
#即
SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP '[123456]789';

\\用于转义：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP '\\.abc';

可以搜索预定义的字符集（字符类），如[:alnum:]（任意字符和数字）、[:alpha:]（任意字符）、[:blank:]（空格和制表符）、[:digit:]（任意数字）等。
重复元字符：

用法如下（\\(与\\)都表示括号本身，[0-9]表示任意数字，?使前一个字符可有可无）：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP '\\([0-9] sticks?\\)';

用法再如下（匹配4个数字）：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP '[[:digit:]]{4}';

定位元字符：

用法如下（匹配开头是数字或点符的文本）：

SELECT $列名$ FROM $数据表名$ WHERE $列名$ REGEXP '^[0-9\\.]';

创建计算字段

拼接字段：

SELECT Concat($列名1$,'(',$列名2$,')') FROM $数据表名$;

可以用LTrim()和RTrim()去除串左、右两端空格。
拼接使用别名：

SELECT Concat($列名1$,'(',$列名2$,')') AS $别名(导出列列名)$ FROM $数据表名$;

执行算数计算：

SELECT  $列名1$, $列名2$ * 3 AS $别名$ FROM $数据表名$ WHERE $表达式$

使用数据处理函数

常用的文本处理函数：

常用的日期和时间处理函数：

日期格式必须是yyyy-mm-dd。
常用数值处理函数：

汇总数据

聚集函数：

创建分组（检索出列中所有值对应的行数）：

SELECT $列名$, COUNT(*) AS $别名$ FROM $数据表名$ GROUP BY $列名$;

在分组列名后面加上WITH ROLLUP即可在检索数据最底部得到汇总数据。
过滤分组（检索出超过n行的响应分组）：

SELECT $列名$, COUNT(*) AS $别名$ FROM $数据表名$ GROUP BY $列名$ HAVING COUNT(*) > n;

SELECT字句顺序：

使用子查询

利用子查询进行过滤（子查询，WHERE子句中列必须和子查询中SELECT子句中列一致）：

SELECT $列名1$ FROM $表名1$ WHERE $列名2$ IN (SELECT $列名2$ FROM $表明2$ WHERE $条件$);

子查询可以多重嵌套使用。

用计算字段作为子查询（统计某一主体在另一张表中相关数据数量）：

SELECT $列名1$, $列名2$, (SELECT COUNT(*) FROM $表名2$ WHERE $表名1$.$列名4$ = $表名2$.$列名4$) AS $列名3$ FROM $表名1$ ORDER BY $列名1$;

联结表

主键是表中的一个或多个字段，其值用于唯一的标识表中的某一条记录。

外键是表中的一个或多个字段，包含了另一张表的主键值。

关系型数据库的可伸缩性（能适应持续增加的工作量而不失败）比非关系型数据库好。

联结是一种查询机制，可以联结多个表返回一组输出，在运行时关联正确的行。

等值联结：若不用`WHERE . = .`，则能保证一一配对（笛卡尔积）而不能保证逻辑上是否可以配对。

等值联结（又名内部联结）亦可写为（不用WHERE）：

SELECT $列名1$, $列名2$, $列名3$ FROM $表名1$ INNER JOIN $表名2$ ON $表名1$.$列名4$ = $表名2$.$列名4$;

可以通过在WHERE子句中用AND连接多个条件或者用多重嵌套子查询，来实现多个表的联结。

创建高级联结

给列起别名：

SELECT Concat(RTrim($列名1$), ' (', RTrim($列名2$), ')') AS $列名3$ FROM $表名1$ ORDER BY $列名1$;

给表起别名：

SELECT $列名1$, $列名2$ FROM $表名1$ AS $表名a$, $表名2$ AS $表名b$, $表名3$ AS $表名c$ WHERE $表名a$.$列名3$ = $表名b$.$列名3$ AND $表名b$.$列名4$ = $表名c$.$列名4$ AND $条件$;

使用自联结：

SELECT $列名1$, $列名2$ FROM $表名$ WHERE $列名3$ = (SELECT $列名3$ FROM $表名$ WHERE $列名1$ = n);

上例等价于：

SELECT $列名1$, $列名2$ FROM $表名$ AS $表名a$, $表名$ AS $表名b$ WHERE $表名a$.$列名3$ = $表名b$.$列名3$ AND $表名b$.$列名1$ = $数据$;

自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是同名的列，无须添加连接条件，并且在结果中消除重复的列。

联结包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结。

左外连接（保留左表）：

SELECT $列名1$, $列名2$, $列名3$ FROM $表名1$ LEFT JOIN $表名2$ ON $表名1$.$列名4$ = $表名2$.$列名4$;

右外连接（保留右表）：

SELECT $列名1$, $列名2$, $列名3$ FROM $表名1$ RIGHT JOIN $表名2$ ON $表名1$.$列名4$ = $表名2$.$列名4$;

使用带聚集函数的联结：

SELECT $表名1$.$列名1$, $表名1$.$列名2$, COUNT($表名2$.$列名3$) AS $列名3$ FROM $表名1$ INNER JOIN $表名2$ ON $表名1$.$列名2$ = $表名2$.$列名2$ GROUP BY $表名1$.$列名2$;

组合查询

组合查询，得到多个结果集（自动取消重复行）：

SELECT $列名1$, $列名2$, $列名3$ FROM $表名$ WHERE $条件1$ UNION SELECT $列名1$, $列名2$, $列名3$ FROM $表名$ WHERE $条件2$ UNION SELECT $列名1$, $列名2$, $列名3$ FROM $表名$ WHERE $条件3$;

组合查询，得到多个结果集（保留重复行）：

SELECT $列名1$, $列名2$, $列名3$ FROM $表名$ WHERE $条件1$ UNION ALL SELECT $列名1$, $列名2$, $列名3$ FROM $表名$ WHERE $条件2$;

对组合查询进行排序：

SELECT $列名1$, $列名2$, $列名3$ FROM $表名$ WHERE $条件1$ UNION ALL SELECT $列名1$, $列名2$, $列名3$ FROM $表名$ WHERE $条件2$ ORDER BY $列名1$, $列名2$;

全文本搜索

MyISAM引擎支持全文本搜索，而InnoDB引擎不支持。

进行全文本搜索（用Match()指定被搜索的列，用Against()指定要使用的搜索表达式）：

SELECT $列名$ FROM $表名$ WHERE Match($列名$) Against($字符串$);

全文本搜索会对结果排序。

使用查询扩展可以搜索出所有包含原匹配项中任意子集的行：

SELECT $列名$ FROM $表名$ WHERE Match($列名$) Against($字符串$ WITH QUERY EXPANSION);

可以在Against()中加IN BOOLEAN MODE来实现布尔文本搜索。

插入数据

插入完整的行（插入数据次序要与表中字段一一对应）：

INSERT INTO $表名$ VALUES ($数据1$, $数据2$, $数据3$, $数据4$);

也可以将字段列出，则无需对应：

INSERT INTO $表名$ ($列名1$, $列名2$, $列名3$, $列名4$) VALUES ($数据1$, $数据2$, $数据3$, $数据4$);

插入多行：

INSERT INTO $表名$ ($列名1$, $列名2$, $列名3$, $列名4$) VALUES ($数据1$, $数据2$, $数据3$, $数据4$) INSERT INTO $表名$ ($列名1$, $列名2$, $列名3$, $列名4$) VALUES ($数据5$, $数据6$, $数据7$, $数据8$);

也可以这样表示（插入数据次序要与列出字段一一对应）：

INSERT INTO $表名$ ($列名1$, $列名2$, $列名3$, $列名4$) VALUES ($数据1$, $数据2$, $数据3$, $数据4$), ($数据5$, $数据6$, $数据7$, $数据8$);

插入检索出的数据：

INSERT INTO $表名1$ ($列名1$, $列名2$, $列名3$, $列名4$) SELECT $列名1$, $列名2$, $列名3$, $列名4$ FROM $表名2$;

更新和删除数据

更新某（些）行：

UPDATE $表名$ SET $列名2$ = $数据2$, $列名3$ = $数据3$ WHERE $列名1$ = $数据1$;

删除某个列的值（将其设置为NULL，但前提是表定义允许该列为NULL）：

UPDATE $表名$ SET $列名2$ = NULL WHERE $列名1$ = $数据1$;

删除某（些）行：

DELETE FROM $表名$ WHERE $列名$ = $数据$;

直接删除表中所有行（快速）：

TRUNCATE TABLE $表名$;

创建和操纵表

创建表（NULL即允许NULL值，NOT NULL即不允许NULL值，DEFAULT即指定默认值 ，AUTO_INCREMENT即默认开始为1且每条新纪录递增1, PRIMARY KEY ()即指定主键（可以是复合主键）ENGINE即指定引擎）：

CREATE TABLE $表名$ ($列名1$ $数据类型1$ NOT NULL AUTO_INCREMENT, $列名2$ $数据类型2$ NOT NULL DEFAULT 1, $列名3$ $数据类型3$ NULL, $列名4$ $数据类型4$ NULL, $列名5$ $数据类型5$ NULL, $列名6$ $数据类型6$ NULL, PRIMARY KEY ($列名1$)) ENGINE = InnoDB;

给表添加一个列（字段信息格式与建表时一直）：

ALTER TABLE $表名$ ADD $字段全信息$;

从表中删除一个列：

ALTER TABLE $表名$ DROP COLUMN $列名$;

ALTER TABLE的一种常见用途是定义外键：

ALTER TABLE $表名1$ ADD CONSTRAINT $约束名$ FOREIGN KEY ($列名1$) REFERENCES $表名2$ ($列名1$);

删除表：

DROP TABLE $表名$;

重命名表（加,后可以为多个表重命名）：

RENAME TABLE $表名1$ TO $表名1$;

在表的列上创建索引：

CREATE INDEX $索引名$ ON $表名$ ($列名（可以多个，用逗号分隔）$);

使用视图

可以用视图将查询包装起来，简化操作：

CREATE VIEW $视图名$ AS SELECT $列名1$, $列名2$, $列名3$ FROM $表名1$, $表名2$, $表名3$ WHERE $表名1$.$列名4$ = $表名2$.$列名4$ AND $表名2$.$列名5$ = $表名3$.$列名5$ AND $表名3$.$列名6$ = $数据$;

然后可以从上述视图中检索数据：

SELECT $列名1$, $列名2$ FROM $视图名$ WHERE $列名3$ = $数据$;

可以用视图来格式化查询结果，然后查询视图时就得到了格式化结果，简化了查询操作；也可以用视图过滤不想要的数据；视图同样可以简化计算字段。

更新一个视图将更新基表。

使用存储过程

需要重用且批处理SQL语句时，可使用存储过程。

执行存储过程：

CALL $存储过程名$($参数名列表（参数名前必须加上@，参数间用逗号分隔）$);

创建存储过程：

CREATE PROCEDURE $存储过程名$($参数名列表（逗号分隔）$)
BEGIN
    $多个SQL语句$;
END;

删除存储过程：

DROP PROCEDURE $存储过程名$;

存储过程的参数（前面必须加上@）分为三种，须在创建存储过程时，在参数名之前加上IN（传给存储过程）或OUT（从存储过程中传出）或INOUT（传给存储过程并从存储过程中传出）关键字（有空格），传出参数时需要在SELECT语句加上`INTO `。

查看存储过程创建时状态信息：

SHOW CREATE PROCEDURE $存储过程名$;

使用游标

游标使用户可逐行访问SQL查询的结果集。使用游标的一个主要的原因就是把集合操作转换成单个记录处理方式。

创建游标：

DECLARE $游标名$ CURSOR
FOR
$SQL查询语句$;

使用游标要先打开它：

OPEN $游标名$;

用完游标后要关闭它：

CLOSE $游标名$;

取游标中下一条数据：

FETCH $游标名$ INTO $变量名$;

使用触发器

触发器是MySQL响应DELETE或INSERT或UPDATE而自动执行的一条MySQL语句（或位于BEGIN与END语句之间的一组语句）。

创建触发器：

CREATE TRIGGER $触发器名$ AFTER $DELETE或INSERT或UPDATE语句$ FOR EACH ROW $触发时执行语句$;

删除触发器（触发器不能更新或覆盖，修改得先删除再创建）：

DROP TRIGGER $触发器名$;

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行。在DELETE触发器代码内，你可以引用一个名为OLD（只读）的虚拟表，访问被删除的行。在UPDATE触发器代码中，你可以引用一个名为OLD（只读）的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）。

管理事务处理

事务处理保证成批的SQL语句要么完全执行要么完全不执行，回退是指撤销指定数量的MySQL语句，提交是指将未存储的SQL语句写入数据库表，保留点是指事务处理时设置的临时占位符（可以对其发布回退）。

回撤事务中之前的SQL语句（不包括SELECT语句）：

ROLLBACK;

提交事务：

START TRANSACTION;
$多条SQL语句$;
COMMIT;

使用保留点：

SAVEPOINT $保留点名$; # 设置保留点

ROLLBACK TO $保留点名$; # 回退到保留点

可以用SET autocommit=0;来关闭非事务的SQL语句的自动提交。

全球化和本地化

字符集为字母和符号的集合，编码为某个字符集成员的内部表示，校对为规定字符如何比较的指令。

查看字符集完整列表：

SHOW CHARACTER SET;

查看所支持校队的完整列表：

SHOW COLLATION;

查看字符集和校对：

SHOW VARIABLES LIKE "character%";
SHOW VARIABLES LIKE "collation%";

创建表并指定字符集和校队：

CREATE TABLE $表名$
(
    $列名1$ $数据类型1$ PRIMARY KEY,
    $列名2$ $数据类型2$
) DEFAULT CHARACTER SET $字符集名$
COLLATION $校对名$;

MySQL还支持对表中每个列设置字符集和校队。

SELECT语句中可以根据校对排序：

SELECT * FROM $表名$ ORDER BY $列名$ COLLATE $校对名$;

使用Cast()和Convert()函数可以使串在字符集之间进行转换。

安全管理

获得所有用户账号列表：

USE mysql;
SELECT user FROM user;

创建用户账号：

CREATE USER $新用户名$ IDENTIFIED BY $口令（字符串）$;

重命名用户：

RENAME USER $旧用户名$ TO $新用户名$;

删除用户账号：

DROP USER $用户名$;

查看用户账号访问权限：

SHOW GRANTS FOR $用户名$;

设置权限（使用GRANT语句，至少要给出“要授予的权限”“要授予访问权限的数据库中的表”“用户名”三者）：

GRANT $特定SQL操作$ ON $表名$ TO $用户名$;

撤销权限设置：

REVOKE $特定SQL操作$ ON $表名$ FROM $用户名$;

设置/更改口令：

SET PASSWORD FOR $用户名$ = Password($口令（字符串）$);

设置/更改自己的口令：

SET PASSWORD = Password($口令（字符串）$);

数据库维护

检查表键是否正确：

ANALYZE TABLE $表名$;

发现并修复的表的问题：

CHECK TABLE $表名$;

改善性能

• 关键的生产DBMS应该运行在自己的专用服务器上。
• MySQL是用一些列默认设置预先配置的，但用一段时间后可能需要调整内存分配、缓冲区大小。
• 可用SHOW PROCESSLIST;来查看所用活动进程及其信息，并可以用KILL命令加上进程ID终结某个特定进程。
• 用SELECT时，应试验联结、并、子查询等，找出最佳方法。
• 使用EXPLAIN语句让MySQL解释如何执行一条SELECT语句。
• 总应使用正确的数据类型。
• 不检索多于需求的数据。
• 有些操作如INSERT支持DELAYED关键字（异步调用）。
• 导入数据时，应关闭自动提交，且可以删除索引，在导入完成后重建。
• 必须索引数据库表以改善数据检索的性能。
• 索引提升数据检索性能，但是损害数据插入、删除、更新的性能。
• LIKE很慢（尽量使用FULLTEXT）。