步骤/目录：
1.再谈数据库基础概念
2.mysql基础命令
  （1）SHOW与USE
  （2）HELP
3.检索数据
  （1）SELECT
  （2）DISTINCT
  （3）LIMIT
  （4）ORDER BY
  （5）WHERE
  （6）IN
  （7）LIKE与通配符%
  （8）正则表达式
4.检索数据的进阶操作
  （1）计算字段
  （2）数据处理函数
  （3）聚集函数
  （4）数据分组
  （5）子查询
  （6）联结表
  （7）其他联结概念
  （8）组合查询
  （9）全文本搜索
5.插入数据
6.更新删除数据
7.创建和操作表
8.视图
9.存储过程及其他
10.数据库维护
11.mysql使用的原则
12.python中使用mysql

本文首发于个人博客https://lisper517.top/index.php/archives/43/，转载请注明出处。
本文的目的是对MySQL进行进阶学习、总结。
本文写作日期为2022年9月3日。本文主要整理自《MySQL必知必会》。

1.再谈数据库基础概念

数据库，从字面意思上理解就是存储数据的仓库。其实把爬虫爬取到的页面存成html放到一个文件夹里也算数据库，只是存取效率较慢。实际上，现在说的数据库一般指数据库管理系统（DBMS，database management system），相比于简单地把数据存成文件，DBMS的存取效率更高，占用空间更小。

前一篇文章中提到，在数据库中的每一行数据应该有一项独特的数据区别于其他行，称为主键。实际上，主键也可以是几项数据的组合，比如说有一群人，存储他们的身高、体重两项数据，单独一项拿出来可能很容易有重复，但两项都相同的情况就会大大减少，大概就是这个意思。一般来说，主键确定以后就不要更新，即使某一行被删除、它的主键也不应该复用，主键的值在未来不应该更改。

SQL即structured query language，与程序设计的C++、Python语言等不同，SQL的构成词少、相对简单，主要是为了操作数据而存在。很多数据库都支持SQL，比如MySQL就使用标准SQL语言，但是不同数据库软件间的语法可能有一些区别，mysql的语句就有可能无法在其他数据库中正常运行（除了mysql的同卵双胞胎mariadb）。

MySQL的运行模式是C/S，即Client/Server，数据库存储在服务器上，其他机器通过mysql客户端访问（即使一台机器访问在本机运行的mysql服务器也要通过客户端）。mysql的客户端和服务器端使用同一套软件，就是说安装了mysql就同时安装了服务器端和客户端；或者也可以使用编程语言，如python的pymysql库当作客户端访问服务器端；或者有一些图形化的管理工具，可以一定程度上简化mysql操作。

目前（2022年9月）MySQL的版本已经到8.0.29（指docker-mysql），但很多语句还是通用的。

一般使用的mysql客户端就是mysql命令行，即你在安装了mysql后用 mysql -h 服务器ip -P 端口号 -uroot -p 进入的界面。mysql命令行中使用命令有两个注意事项：每行命令用 ; 或 \g 结尾后再按 enter键才会执行（除了少部分简单的语句）；输入 exit 或 quit 可退出。另外还有一些图形化的界面，笔者在这里推荐 MySQL Workbench ，其下载页面为 https://dev.mysql.com/downloads/workbench/ ，它是mysql官方推出的工具（之前mysql被Oracle拿下了），功能强大。另外mysql现在还推出了MySQL Installer，可以一键管理各种mysql官方软件。

最后，如果要在mysql命令行里清屏，使用 system clear 或 system cls ，前者适用于linux客户端、后者适用于cmd。

2.mysql基础命令

（1）SHOW与USE

SHOW DATABASES 显示所有数据库；
USE 数据库名 使用某个数据库，后续操作都在该库中进行；
SHOW TABLES 展示库中的表名；
SHOW COLUMNS FROM 表名 或 DESCRIBE 表名 来查看一张表有哪些列，列的数据类型等信息，返回的表格式为：

+--------------+--------------+------+-----+-------------------+-------------------+
| Field        | Type         | Null | Key | Default           | Extra             |
+--------------+--------------+------+-----+-------------------+-------------------+

分别表示列名，数据类型，可否为NULL ，是否主键，默认值，其他（自增，默认生成等）。
SHOW CREATE TABLE 表名 可以查看创建表时的完整语句。
SHOW 的其他命令还有 SHOW STATUS（查看mysql服务器状态）、 SHOW GRANTS（查看所有mysql用户的权限）、 SHOW ERRORS 和 SHOW WARNINGS（查看mysql服务器的错误和警告）。

（2）HELP

使用 HELP 其他命令名 可以查看mysql里的命令帮助手册，比如在忘记 SELECT 各种从句顺序时就可以使用 HELP SELECT。

最后，以上所有的命令都使用大写，但实际上mysql不区分大小写。标准的规范中，应当尽量只把列、表名、值等小写；同一条命令可以拆成多行输入mysql命令行，便于阅读调试，最后加 ; 即可。

3.检索数据

（1）SELECT

检索数据使用 SELECT 语句，一个简单的例子是：

SELECT 列名 FROM 表名

由于没有指定输出顺序，这里的返回的数据顺序可能有变化。
列名也可以写多个，用 , 分隔：

SELECT 列名1,列名2,列名3... FROM 表名

或者用 * 表示输出所有列，即 SELECT * FROM 表名 。

最后， SELECT 中的列名和表名还可以写成完全限定的形式：

SELECT 表名.列名 FROM 库名.表名

这在一些复杂的 SELECT 语句中比较有用，同时注意即使用 表名.列名 已经指定了表，还是需要 FROM 表名 来指定表，即 SELECT 表名.列名; 这样的写法无效。

（2）DISTINCT

用 DISTINCT 可修饰列名，只输出该列有多少种不同的值：

SELECT DISTINCT 列名 FROM 表名

DISTINCT 用于单独一列时可以理解为将相同的行合并后输出。 DISTINCT 修饰多个列名时， DISTINC 放在哪里都等效于放在最前面，即 DISTINCT 列名1,列名2... ，这时的一行数据，只有列出的 列名1,列名2... 都相同才会被当成同一条数据合并。

（3）LIMIT

用 LIMIT 可限制输出行数、从第几行后开始输出（不包括）。它的第一种用法为：

SELECT 列名 FROM 表名 LIMIT n

这里n为一个整数，表示最后返回的结果应该 <= n 行。
如果写成 LIMIT m,n 表示从第 m 行后开始查找，输出 <= n 行。所以 LIMIT n 也可以理解为 LIMIT 0,n 。
由于 LIMIT m,n 容易让人搞不清从第 m 行还是从第 n 行开始，mysql 5 之后的版本支持 LIMIT n OFFSET m 的写法， OFFSET 表示偏移量。

（4）ORDER BY

前面已经提到，不指定顺序时mysql返回的数据排序比较随意，这种顺序一般是表中数据在底层的存储顺序，尤其是在表中删除、新增过行后，mysql有一些重新利用空间的操作，底层顺序可能改变。要指定顺序，可以用 ORDER BY 从句：

SELECT 列名1 FROM 表名 ORDER BY 条件

这个条件一般可以用检索的列名，即 ORDER BY 列名1 ，但使用其他列对输出列排序也可以，即 ORDER BY 列名2 。如果该列是数字，就按数字大小排序（日期时间也是）；字符串则按字母表顺序。 ORDER BY 默认是升序输出的（大的数字或靠后的字母在下面）。

ORDER BY 后面也可以接多个条件，比如 ORDER BY 列名1,列名2 ，那么mysql会先根据列名1 排序，当两行的列名1 相同时则按列名2 排序。

另外， ORDER BY 条件 DESC 可以指定降序输出。对于多个条件，如果写成 ORDER BY 列名1 DESC,列名2 则按列名1 降序，按列名2 升序，即 DESC 只对它紧跟着的前面的那个条件生效。升序也可以通过 ASC 显式指定，但一般不用写。

如果聪明的读者，可能已经想到，用 ORDER BY 和 LIMIT 可以选出最大值或最小值。其实，mysql中有专门实现这项功能的函数，即 MAX() 和 MIN() ，这将在后面提及。

最后， ORDER BY 可能区分大小写，也可能不区分（在ASCII表中小写字母排在大写字母前面），取决于数据库的设置（collate）。

（5）WHERE

用于指定条件。比如在 SELECT 中使用：

SELECT 列名 FROM 表名 WHERE 条件

WHERE 也可以和前面介绍的 ORDER BY 和 LIMIT 联用，如果不确定这三个关键字的顺序，可以用 HELP SELECT 查看。

WHERE 条件中有以下的操作符：
= 表示等于， <> 和 != 都可以表示不等于，其他的还有 < 、 > 、 <= 、 >= ，另外还有 BETWEEN 表示在两个值之间（包括两端）。
WHERE 条件可以为多个，用 AND （ BETWEEN 也要用到 AND ）、 OR 、 NOT 连接。
使用例子：

WHERE 列名="字符串1"
WHERE 列名!=123
WHERE 列名 BETWEEN 123 AND 234
WHERE NOT 列名=123
WHERE ((列名1="字符串1") AND (列名2!=123)) OR (列名3 BETWEEN 123 AND 234)

在最后一条语句中，使用 () 明确指定三项条件的组合顺序。在默认情况下， AND 比 OR 优先。

另外，如果要检查列值是否为NULL，使用 IS ：

WHERE 列名 IS NULL
WHERE 列名 IS NOT NULL
WHERE NOT 列名 IS NULL

这里提醒一下，NULL和数值0、空格（字符串）、空字符串（""）都不同，在创建表时请尽量禁止列的值可以为NULL，而是用0、空字符串等来代替，可提高检索性能。由于NULL值比较特殊，对于 != 等刚才提到的操作符的检索，它是不会出现的：

WHERE 列名!=1
WHERE 列名!=1 OR 列名 IS NULL

所以上面两行命令中，前者不会返回该列值为NULL的行，后者会。

（6）IN

刚才已经提到了 WHERE 子句中 AND 、 OR 和 NOT ，现在介绍一下 IN 。 IN 后面跟的可以理解为集合，比如：

SELECT 列名 FROM 表名 WHERE 列名 IN (1,2,3,4)

上面的例子也可以用4个 OR 来代替。实际上， IN 后面更多情况下跟的是其他 SELECT 语句（嵌套 SELECT ，即子查询，见后），来实现复杂功能。

在（5）中提到过 NOT ，实际上 NOT 在mysql中用途广泛，比如：

NOT IN
IS NOT
NOT BETWEEN
CREATE TABLE IF NOT EXISTS 表名

NOT 可以对各种条件取反。

（7）LIKE与通配符%

前面的 = 只适用于完全匹配、精确相等，如果查找字符串时只知道其中某一段，就可以用 LIKE 和通配符 % 、 _ 。如：

SELECT 列名 FROM 表名 WHERE 列名 LIKE "%jet%"

这里的 LIKE 替换了 = ， "%jet%" 被称为模式（pattern）。本例中，任何字符串只要包含连续的 jet 三个字母，就算是满足了 WHERE 从句中的条件。所以 % 可以替代任意个字符（包括0个）。如果写成 %jet 或 jet% ，则指定字符串必须以 jet 结尾或开头；若写成 je%t ，则指定字符串必须以 je 开头、以 t 结尾，中间可以有0~n个字符。另外，NULL值仍然是一个特例，不会被 LIKE "%" 匹配到。

另一个匹配符是 _ ，它与 % 的唯一不同是只能匹配一个任意字符。

由于通配符搜索较慢，使用时请谨慎，能用其他方法替代的就用其他方法。

（8）正则表达式

REGEXP 在mysql中用来支持re（regular expression）：

SELECT 列名 FROM 表名 WEHRE 列名 REGEXP "模式"

REGEXP 在这里和 LIKE 类似，也替换了 = 。另外，如果要让mysql的re区分大小写，用 REGEXP BINARY "模式" 。在mysql中写正则，用转义字符要写 \\ ，比如匹配 \ 就用 \\\ ，因为mysql和mysql的re库各需要用一个 \ ，这和python的re库类似。另外需要注意的是 LIKE 和 REGEXP 的小区别， REGEXP 默认只匹配字符串的一部分，除非用 ^ 和 $ 指定了开头结尾。

关于re的更多内容，请参考 runoob教程，或查看笔者后续的文章。

4.检索数据的进阶操作

前面介绍的是一些基础操作，没有涉及 SELECT 嵌套或其他高级内容。下面介绍的进阶操作中，最关键也是最难的是子查询，联结和 UNION 。

（1）计算字段

如果mysql表中记录的内容不是直接需要的，比如要对表中数值做一定计算，或者把字符串进行大小写转换，然后把处理后的数据返回客户端，就叫做创建了计算字段。当然，也可以把表中的数据都传给客户端后再处理，比如用python时，使用pymysql取得数据、在python中进行计算，但这种方法的缺点是传输的数据更多（占用带宽），并且mysql服务器端处理起来一般效率更高（DBMS从设计时就考虑到了这些情况。即使服务器端的机器配置有时可能比客户端差，能在服务器端完成的操作也应该尽量在服务器端完成）。

计算字段的特点是并没有实际存储在表中，它是由mysql服务器从已有的数据推导、计算出来的。当然，从客户端的角度，并没有办法区分返回的数据是实际存储的，还是计算字段。

第一个计算字段的例子是拼接函数 CONCAT() （即 concatenate 。其他数据库软件可能使用 + 或者 || 操作符，mysql中使用函数）：

SELECT CONCAT(列名1,"123",列名2,114.514) FROM ...

在这个例子中，返回的值为列值1 、 "123" 、列值2 、 "114.514" 拼接后的结果（如果读者进行实践的话，注意这一列的名字，列名不存在于原来的表中）。使用 HELP CONCAT ，可看到mysql对这个函数行为的说明，主要特点有：
如果所有参数都不是字符串的二进制表示，则返回的结果也是一个非二进制形式表示的字符串；
只要参数中有一个是二进制字符串，返回的结果就也会是二进制字符串；
如果参数是数值，它将被转换为非二进制字符串，字面意思和原来一样；
只要其中一个参数为NULL，则返回的结果也为NULL。

第二个函数的例子是 RTRIM() ，它用于删除字符串末尾的空格， LTRIM() 、 TRIM() 同理。

刚才提到，计算字段得到的列，其列名不存在于原来的表中，有时可能显得有点丑陋。这个问题可以用别名解决，别名需要用到 AS 关键字：

SELECT CONCAT(列名1,"123",列名2,114.514) AS 列的别名 FROM ...

这时列名会变为指定的列的别名。当然， AS 不只是为了美观而存在，别名有其他的用处。别名（alias）又可称为导出列（derived column），因为它一般和原来的表中列名不同。

计算字段中另外一种重要的计算是对数值的处理。如进行简单算数（乘法）：

SELECT 列名1*列名2 FROM ...

列名1 和列名2 中的数值应该是数值型的。实际上， + 、 - 、 * 、 / 都是支持的。

除了 CONCAT() 、3个 TRIM 函数，还有很多其他函数，将在后面一一介绍。

（2）数据处理函数

mysql虽然使用标准的SQL语言，但各个DBMS在函数上的区别比较大，有些函数可能功能相同，但在某些情况下行为不同，或者压根就没有一些函数。为了可移植性，在编写SQL时应该少用函数，或者用了函数以后写上详细的注释。函数可以分为4类，处理数字、字符串、日期时间的3类，以及返回DBMS信息的函数。最后，如果要对函数进行一些实验，可以使用 SELECT 函数(参数) ，就不需要到表里取数据计算了。

最后，以下所有的函数，如果有不清楚的地方，可以用 HLEP 函数名 获得帮助。

在字符串处理函数中，有如下函数与功能：
UPPER() ，将字符串中的字母全部转换为大写； LOWER() 则相反。要注意这两个函数对二进制字符串无效（BINARY、VARBINARY和各种BLOB）。
LEFT(str, n) ，返回str中最靠左的n个字符。 RIGHT() 同理。
LENGTH() ，返回一个字符串占的字节数； CHAR_LENGTH() 则返回字符串中的字符个数。这两个函数在处理中文时有区别。
LOCATE(substr, str, pos) ，其中 pos参数可选，用于返回 str 中从左边开始数，第一次出现 substr 是第几个字符，如果不包含则返回0。若任意一个参数为NULL，则会返回NULL。
SOUNDEX() ，用于返回一个字符串的SOUNDEX值（也是字符串），这个值用于描述字符串的发音。要注意这个功能主要是针对英语的，其他非英文字符会被当成元音处理。另外，对于utf-8等双字节的字符集，这个函数可能会有一些bug。
SUBSTRING() ，用于返回字符串的子字符串，它有两种共四个形态： SUBSTRING(str, pos) （同 SUBSTRING(str FROM pos) ）和 SUBSTRING(str, pos, len) （同 SUBSTRING(str FROM pos FOR len) ），具体请用 HELP SUBSTRING 查看。

在日期时间函数中，有下列函数与功能：
ADDDATE() ，用于返回日期加某个时间后的日期，有两种形式： ADDDATE(原日期, INTERVAL 数字单位) 和 ADDDATE(原日期，天数) ，前者如 ADDDATE('2020-01-01',INTERVAL 30 DAY) （DAY可以换成MIN、YEAR等其他单位，适用性更强），而且前者的功能和 DATE_ADD() 相同；后者如 ADDDATE('2020-01-01', 30) ，把整数解读为天数。除了 ADDDATE() 、 DATE_ADD() ，还有 SUBDATE() 和 DATE_SUB() 。
ADDTIME(表达式1, 表达式2) ，返回两个时间表达式相加后的值。前一个表达式需要为时间或日期时间表达式，后者为时间表达式。
CURDATE() 返回当前日期（以 'YYYY-MM-DD' 的字符串形式，或 YYYYMMDD 的数值形式，取决于本函数是否在数值环境中使用，比如 CURDATE()+0）， CURTIME([fsp]) 返回当前时间，fsp（fractional seconds precision）可选项用于指定秒的精度，同样有 'hh:mm:ss' 和 hhmmss 两种形式。类似的还有 NOW() ，返回当前日期时间。
DATE(日期时间表达式) ，用于返回日期时间表达式的日期部分。 TIME() 同理。类似的还有 YEAR() 、 MONTH() 、 DAY()（ DAY() 是 DAYOFMONTH() 的同义函数）、 DAYOFWEEK() 、 HOUR() 、 MINUTE() 、 SECONDS() ，分别返回年份、月份、日期、星期几、小时数、分钟数、秒数。
DATEDIFF(表达式1, 表达式2) 用于对两个日期时间表达式做差，返回相差天数，分正负号。注意，虽然两个日期时间表达式中包含了时间，但只有日期的部分被使用。
DATE_FORMAT(表达式, 格式) 用于将日期时间表达式格式化输出，类似python中的 time.strftime 方法。
另外补充一点日期时间类型的知识。日期时间在mysql中也要用单或双引号括起来，可以进行 = 、 != 等比较，也可以用 BETWEEN 。需要注意的是只写日期时，表示时间为 '00:00:00' ，可能对大小比较有影响，可用 DATE() 函数处理。

在数值处理函数中，有如下函数与功能：
ABS() ，取绝对值。
COS(X) ，返回角度的余弦（这里的X单位为弧度），类似的有 SIN() 、 TAN() 等三角函数。
EXP(X) ，返回e的X次方，e是自然常数。相反的函数为 LN() 和只有一个参数的 LOG() 。
MOD(N, M) ，返回N除以M后的余数，也可以写成 N MOD M 或 N % M 。
PI() ，返回圆周率，默认精度为小数点后6位，但实际上它在mysql中是一个双精度浮点数。
RAND() ，返回0~1之间的随机单精度浮点数。如果要获得一个随机整数R，比如 i<=R<j （i、j都是整数），需要搭配 FLOOR() ： FLOOR(i + (RAND() * (j-i) )) 。
FLOOR(X) ，返回小于等于 X 的最大整数。
SQRT(X) ，返回X的平方根。
在4种函数中，数值计算函数在各大DBMS中几乎都相同。

（3）聚集函数

有时常需要将表中的结果汇总后输出，比如求某列的均值、最值，或者总和、行数，就可以用聚集函数，常用的有：
AVG(列名) ，返回某列的均值（忽略为NULL的行）。这里的列名可用 DISTINCT 修饰，即 AVG(DISTINCT 列名) 。
COUNT(列名) ，返回某列值不为NULL的行数。如果写作 COUNT(*) ，则返回所有行数，不管是否为NULL（可能因为主键一般不会是NULL）。
MAX() 和 MIN() 用于寻找最值，注意它们对字符串、数值和日期时间都能使用，因为mysql中这三种类型在内部都能比较大小。
SUM() 用于求和。
另外还有求标准差、方差的函数，就不介绍了。最后，所有聚集函数的参数中都可以带 + 、 - 、 * 、 / ，或者其他函数。

（4）数据分组

GROUP BY 从句，可以对数据进行分组，经常使用的一种格式是：

SELECT 列名1,COUNT(*) FROM 表名 GROUP BY 列名1

这将会输出两列，左边的列名就是列名1，右边为 COUNT(*) ；左边类似用了 DISTINCT 限定，不会输出重复的值，右边则会输出该值出现过多少次。这个例子看起来用 DISTINCT 也能实现，但由于 DISTINCT 需要作用于所有列、所以不能有聚集函数 COUNT() ，于是这里只能用 GROUP BY 实现。
如果对 GROUP BY 的效果有一定了解，应该可以理解其以下设定：
GROUP BY 可以包含任意数目的列，效果和 DISTINCT 类似，也作用于所有列，就是说如果 GROUP BY 包含有两行，这两行所有选定的列值都相同，就会被算作同一行数据；
GROUP BY 中，出现在 SELECT 后的列名，都必须出现在 GROUP BY 后面，除了聚集函数（如果不使用联结的话。在后文中介绍联结时，将打破这项规则）；
如果列值中有NULL，它也会被当成分组（对NULL生效）。

如果要对 GROUP BY 设置条件，用 HAVING 代替 WHERE ，语法相同。如：

SELECT 列名1,COUNT(*) FROM 表名 GROUP BY 列名1 HAVING COUNT(*)>2

这里可以理解为， WHERE 在数据汇总（ GROUP BY ）之前对数据过滤， HAVING 则针对分组后的数据过滤。

另外， GROUP BY 经常和 ORDER BY 搭配使用。到这里，各种关键字的顺序已经有点复杂了，忘记时记得用 HELP SELECT 查看。

最后，在 GROUP BY 里还可以添加 WITH ROLLUP ：

SELECT 列名1,COUNT(*) FROM 表名 GROUP BY 列名1 WITH ROLLUP

它可以对某一列（如果它是数值类型）进行求和，但不能和 ORDER BY 一起出现。

（5）子查询

子查询即嵌套的 SELECT 。对于多张表或单张表，都经常会用到子查询。一般使用的格式为：

SELECT 列名1 FROM 表名1 WHERE 列名2 IN (SELECT 列名3 FROM 表名2 WHERE ...)

注意这里的 IN ，之前说过，可以把 IN 后面接的当成一个集合，这里就比较好理解了。有时， IN 也可以替换成 = 、 <> 。
如果需要的话，可以嵌套很多层，但从性能上考虑，还是尽量少嵌套太多，有时候，子查询可以被其他方法替代。
从编写SQL和mysql执行的角度上看，都应该从内到外看待子查询。

在涉及多张表的子查询中，两张表可能会有相同的列名，这时可以用之前提到的完全限定列名，即 表名.列名 格式来指定要使用哪张表中的列。

子查询比较复杂，这里举一个例子，需要对表中数据进行去重。构造以下的表：

CREATE TABLE IF NOT EXISTS test(
no INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL DEFAULT "unknown" COMMENT "姓名",
`id` INT UNSIGNED NOT NULL DEFAULT "1" COMMENT "学号",
PRIMARY KEY (no))COMMENT="学生姓名与学号";

这个例子中，创建了一张表，name列存储学生的姓名，id列存储学生的学号（ COMMENT 是对列或表的注释，用 SHOW FULL COLUMNS FROM 表名 可查看）。然后插入值：

INSERT INTO test (name,`id`) VALUES ("a",1001),("a",1001),("b",1002),("c",1003),("c",1003),("c",1003),("d",1004);

由于工作人员在录入时粗心大意，本来只有4个学生，却录入了7条记录，有3条需要删除。按子查询的构造原则，从内层构造，先构建一个查询出现次数超过1的查询：

SELECT name,`id`,COUNT(*) AS times 
FROM test 
GROUP BY name,`id` 
HAVING COUNT(*)>1;

然后构造一个查询重复的行的主键的查询：

SELECT no,name,`id` 
FROM test 
WHERE (name,`id`) IN (
SELECT name,`id` 
FROM test 
GROUP BY name,`id` 
HAVING COUNT(*)>1);

注意，这里对里面嵌套的子查询返回的结果进行了修改。然后，由于每种重复的数据需要保留一条最初的记录，因此要排除no最小的记录。这里我们构造一个查询每种重复数据最小的no的查询：

SELECT MIN(no),name,`id` 
FROM test 
GROUP BY name,`id` 
HAVING COUNT(*)>1;

最后，把这两个查询组合到一起：

SELECT no,name,`id` 
FROM test 
WHERE (name,`id`) IN (
SELECT name,`id` 
FROM test 
GROUP BY name,`id` 
HAVING COUNT(*)>1)
AND no NOT IN (
SELECT MIN(no) 
FROM test 
GROUP BY name,`id` 
HAVING COUNT(*)>1);

这样就返回了需要删除的行的主键，name和id（实际上只需要主键），结合后面会讲解的 DELETE ，就可删除重复行。

（6）联结表

在mysql中，常需要用很多表来存储信息，比如下面的例子：有很多导演，每一名导演执导过不同的电影（导演还需要存储其身高体重年龄等其他信息）；有很多电影，每部电影由一名导演执导（电影还要存储其票房等其他信息），那么要存储这些信息，最好是用两张表，一张表存储导演的信息，另一张表存储电影的信息。那么此时这两张表就产生了关系，因为导演表中有电影列，电影表中有导演列。如下创建这两张表：

CREATE TABLE IF NOT EXISTS director(
name VARCHAR(30) NOT NULL,
height SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT "身高，单位cm",
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT "年龄",
PRIMARY KEY (name))COMMENT="导演";

CREATE TABLE IF NOT EXISTS movie(
name VARCHAR(30) NOT NULL,
director VARCHAR(30) NOT NULL DEFAULT "unknown" COMMENT "导演",
box_office BIGINT NOT NULL DEFAULT -9223372036854775808 COMMENT "票房",
PRIMARY KEY (name))COMMENT="电影";

需要注意，导演对电影是一对多的关系（假设一名导演不只有一部电影），电影对导演则是一对一（假设一部电影有且仅有一名导演）；在创建表时，导演表里没有其执导过的电影，电影表里有其对应的导演。
然后我们给两张表插入数据：

INSERT INTO director (name,age) VALUES ("a",20),("b",25),("c",30),("d",40);

INSERT INTO movie (name,director,box_office) VALUES ("movie1","a",100),("movie2","a",150),("movie3","a",50),("movie4","b",55),("movie5","c",65),("movie6","c",80),("movie7","b",25),("movie8","d",35),("movie9","d",95);

现在，我需要输出一张表，其列分别为电影名、票房、导演、导演年龄，那么就需要联结两张表。为了使两张表产生联结，可以将导演表的主键关联到电影表里，即把电影表里的director列作为外键（一个外键是另一张表的主键），也就是说本例中电影表里的director列是导演表的主键（这里进行演示时没有创建外键，后续将会讲解如何创建外键。创建外键后，更改另一张表中的信息，改动会同步到本张表，比如本例中更改导演表中的导演名，改动会同步到电影表中的导演名这一列）。但是要注意，这种联结只在查询时临时创建，在mysql中它并不物理存在。

从多张相互关联的表里创建联结，只要使用完全限定列名即可：

SELECT movie.name AS movie,movie.box_office AS box,movie.director AS director,director.age AS director_age  
FROM director,movie 
WHERE movie.director=director.name;

关键之处是它的 FROM 接了两张表， WHERE 里指定两张表的某个列值相同，另外它所有的列名都用了完全限定（有些列名可以不用，但笔者建议所有列名都用）和 AS 。最后，这里的 movie.director AS director 也可以改成 director.name AS director
实际上，这里的运行逻辑是这样的，mysql先执行前两行语句（这两句你也可以试试执行，最好把下面语句中 SELECT 后面跟的列名改成 * ）：

SELECT movie.name AS movie,movie.box_office AS box,movie.director AS director,director.age AS director_age  
FROM director,movie;

这将会联结每一行，就是说最终的返回的结果总行数为笛卡尔积，即两张表各自总行数的乘积（本例中为4*9即36行）。
然后mysql再用 WHERE 中给出的条件对结果进行筛选，最后只剩下符合要求的行（这里限定的条件使得最终行数和movie表中的行数相同，即9行）。

最后，既然说到这里，不如试试 WITH ROLLUP 的效果。输入以下查询语句：

SELECT director,sum(box_office) FROM movie GROUP BY director WITH ROLLUP;

这将输出每位导演执导电影的总票房，最后一行则是这几位导演的总票房之和。

刚才的例子被称为等值联结，因为 WHERE 里的条件是两张表的某个值相等。但其实等值联结还有一种写法，称为内部联结：

SELECT movie.name AS movie,movie.box_office AS box,director.name AS director,director.age AS director_age  
FROM director INNER JOIN movie 
ON movie.director=director.name;

在这种写法中，明确了两张表是使用内部联结，并且把 WHERE 换成了 ON 。实际上，这种方法是更推荐的，因为它的联结表示得更加显眼，且有时内部联结的效率更高。

最后，除了两张表，也可以联结多张表，但性能下降的很快（因为此时的笛卡尔积是所有表的总行数的乘积）。之前曾经介绍过子查询，举了一个例子（在一张表里查重），如果子查询涉及多张表时，常常可以用表联结的方法替代，针对不同的情况，子查询和联结的性能表现也不同，需要自己实际测试。
现在，在脑海中回想一下子查询与联结：子查询是 SELECT 的嵌套，需要用 IN ；等值联结或内部联结是在表的笛卡尔积中返回符合条件的行，需要用 INNER JOIN 和 ON （这两个关键字用于内部联结写法，比等值联结写法更推荐），最好完全限定列名（有时候不需要，但涉及多张表时都推荐写成完全限定的形式）。另外要注意，现在并没有举在一张表中使用联结的例子，但这种联结也是存在的。

（7）其他联结概念

除了等值联结、内部联结（这两种联结是一种方法的不同写法，但后者有时效率更高），还有其他联结概念，包括自联结、自然联结和外部联结。要了解这些联结，需要先了解如何给表起别名。

在前面的例子中，我们对列和计算字段起过别名，格式是在 SELECT 后跟的列里，写： SELECT 列名1 AS 别名1, 计算字段1 AS 别名2, ... 。其实，还可以用 AS 对表起别名：

SELECT m.name AS movie,m.box_office AS box,d.name AS director,d.age AS director_age  
FROM director AS d INNER JOIN movie AS m
ON m.director=d.name;

这里沿用了上一个例子中的两张表。注意，只是在 FROM 从句中对两张表指定了别名，后续和前面所有用到这两张表名字的地方都使用了别名（这里是缩写，所以大大减少了SQL的长度），包括给两张表指定别名之前，即 SELECT 中。实际上，mysql会先读完语句，最后再执行。
表别名的主要作用是缩短SQL，和多次复用表别名（包括临时生成的表）。

下面讲解自联结、自然联结和外部联结。首先介绍一下自联结，仍然使用导演表、电影表的例子。现在我们发现一部电影（ "movie1" ）可能涉嫌严重的偷税漏税，需要查找该电影导演的其他电影作品（用电影查找导演，再查找电影）。我们可以用两条语句，即：

SELECT director FROM movie WHERE name="movie1";
#返回结果为a
SELECT name FROM movie WHERE director="a";

但是这样逼格明显太低。下面试试用一条语句，首先用子查询：

SELECT name FROM movie WHERE director IN (SELECT director FROM movie WHERE name="movie1");

接下来，我们使用自联结：

SELECT m1.name
FROM movie AS m1, movie AS m2 
WHERE m2.name="movie1" 
AND m1.director=m2.director;

这一条语句可以这样理解：mysql用同一张表创建两个相同的临时表，对这两个临时表求笛卡尔积，最后返回符合条件的行。
由于是对一张表使用联结，这种联结称为自联结。

然后是自然联结。这里的自然联结更像是一种概念、一种哲学，比如在导演表和电影表的例子中，我会把电影表的导演名这一列，和导演表的姓名这一列用来比较（等值联结），这是自然而然的事情（虽然它们在两张表中的列名不同，但表达的是同一个事物，即导演的名字）；如果我把电影表的票房这一列和导演表的年龄这两列拿去比较，虽然它们都是INT，但却没有任何意义，所以有悖自然、有悖人伦。实际上，非自然联结没有任何意义。

最后是外部联结。比如我要输出电影名，导演名，导演年龄这三列，可以使用：

SELECT m.name,d.name,d.age
FROM movie AS m LEFT OUTER JOIN director AS d 
ON m.director=d.name;

这个功能使用前面的内部联结、等值联结也能轻易做到，这里需要关注的重点是导演年龄其实和电影名关系很小（所以在电影表中没有存储导演的年龄）。所以，使用 LEFT OUTER JOIN 或 RIGHT OUTER JOIN ，将两个表中非关联列（非外键列）也输出的行为，就称为外部联结。 LEFT 和 RIGHT 用于指定以哪张表为主，影响的只有输出顺序，如果你用我的电影表和导演表实验，会发现输出的电影名顺序不同；在其他方面二者没有区别。

最后，简单讲一下带聚集函数的联结。比如我要统计每位导演有多少部电影作品（这当然可以只在电影表中用 GROUP BY 完成，但这里使用联结+聚集函数）：

SELECT d.name,COUNT(*) AS num_of_movies 
FROM movie AS m INNER JOIN director AS d 
ON m.director=d.name
GROUP BY d.name;

在这里要注意，前文曾说过， SELECT 后的列名在 GROUP BY 后必须出现，上例也遵守了这项规则；但在联结中用 GROUP BY ，有时可以不遵循该规则。

最后，再提醒一下，有些情况下可用子查询，也可用联结，二者查询性能如何需要做实验来确定。

（8）组合查询

一个 SELECT 返回一个结果集，有时候我们需要将结果集求并集一起返回，就可使用组合查询（一条语句查询所有结果）。细分下来，组合查询可分为两种情况：
对单个表进行多次查询（查询的返回结果相同），将结果集求并后返回；或者有时一张表能存储的数据达到上限，不得已将一张表分成多张表，这也可以算是对单张表进行多次查询（但从操作过程上看是对多张表多次查询）。
对多张表进行多次查询（查询结果的数据结构类似），求并后返回结果集合。比如电影表和导演表中，有些小众电影也有导演，但在导演表中没有记录这些导演的名字，如果我想得到所有导演的名字，就可以用组合查询。

其实 WHERE 条件中的 OR 也可以理解为求并集，所以这种带 OR 的 SELECT 语句也可以改写成组合查询的形式。如同子查询和联结，这两种等效写法的性能也互有高低，需要测试，但大部分情况下 OR 的效率更低，尤其是有很多个 OR 时。

组合查询只需要简单地在 SELECT 语句间加一个 UNION 就完事。这里简单举一个例子，我想得到导演"a"执导的所有电影名，以及票房超过80的所有电影名，可以执行下面的SQL:

SELECT name FROM movie WHERE director="a" OR box_office>80;

把它改写成组合查询的形式如下：

SELECT name FROM movie WHERE director="a"
UNION ALL
SELECT name FROM movie WHERE box_office>80;

UNION 的规则也很好理解，它在每个 SELECT 间插入一个 UNION （比如4个 SELECT 就需要3个 UNION ），每条 SELECT 后跟的列、表达式或聚集函数必须有相似的数据类型（比如TINYINT和INT这种可以转换的类型就算相似），而且如果把每条 SELECT 后跟的列、表达式或聚集函数看作一个集合，那么各个集合的元素个数相同，且可以建立一一对应的关系。组合查询不要求所有 SELECT 后跟的列、表达式或聚集函数的顺序相同，但笔者建议最好写成相同的。

另外，由于这是求并集，所以mysql会把重复的部分去除（ A并B=A+B-(A交B) ）。如果想把重复的部分也输出，用 UNION ALL 。

最后，组合查询也能排序，但 ORDER BY 仅需要在最后的 SELECT 后面写一次。毕竟每条 SELECT 后面都写的话太麻烦了，而且一不留神还可能写错顺序。

（9）全文本搜索

全文本搜索，即 FULL TEXT ，是一种空间换时间的做法，用于对mysql中的字符串进行快速高效的模糊匹配。前文提到用 LIKE 做模糊匹配，但FULLTEXT是对所有的文本建立索引，查起来效率比 LIKE 高到不知道哪里去了；可惜由于需要对全文本建立索引，FULLTEXT的硬盘占用比较高。

mysql中有数据库引擎这一说，常用的引擎有MyISAM（现在基本不用）和InnoDB（现在的默认引擎），在mysql 6之后的版本，二者都支持FULLTEXT。据说mysql 5的版本对中文用FULLTEXT有bug，不知道现在如何。

使用FULLTEXT的场景一般是大量的文本数据，比如你用爬虫爬取了一家报纸媒体的所有文章。要使用FULLTEXT，从建表时就要有相关的设计：

CREATE TABLE IF NOT EXISTS articles(
no int NOT NULL AUTO_INCREMENT,
author VARCHAR(40) NOT NULL DEFAULT "unknown",
content TEXT NULL,
PRIMARY KEY (no),
FULLTEXT (content)
)ENGINE=InnoDB;

之后，如果content列里的数据发生改变，mysql会自动更新索引。由于更新索引也需要一定的时间（虽然很少），如果需要把一个FULLTEXT列导入到一张新表，最好的做法是先创建正常的表，导入数据完成后再指定这一列为FULLTEXT。更改列属性的方法将在后面介绍。

创建完成后，使用 MATCH() 和 AGAINST() 函数搜索：

SELECT content FROM articles 
WHERE MATCH(content) AGAINST("搜索表达式");

全文本搜索也不区分大小写，需要的话用 BINARY 指定。如果在 SELECT 后指定了多个FULLTEXT列，在 MATCH() 中必须以相同的顺序列出这些列，就是说次序和个数都要正确。在 AGAINST() 的使用上，比如想搜索含有 'rabbit' 的行，就写 AGAINST("rabbit") 。注意，FULLTEXT返回的顺序是按匹配程度排序的，比如 'rabbit' 越靠前则匹配程度越高，在返回结果中的排名也越靠前。

另外， AGAINST() 中还可以使用查询扩展，如 AGAINST('rabbit' WITH QUERY EXPANSION) ，它的工作原理是先找到含有 'rabbit' 的行；对不含有 'rabbit' 的行，如果它们含有一些词语，这些词语在能匹配到 'rabbit' 的行里也出现过，则这些不含 'rabbit' 的行也可以被输出，只是排序靠后。

最后，还有布尔搜索，它不需要使用FULLTEXT的索引，但可能比较慢。你可以给它提供一些参数，包括：
要匹配的词；
要排除的词。当某个值含有要排除的词，它就绝对不会出现在查询结果中，即使它含有要匹配的词；
对要匹配的词的重要性排序；
其他内容。

用 AGAINST(... IN BOOLEAN MODE) 即可指定布尔搜索，前面同样是 MATCH() 。
比如，想要包含abc，但不以xyz开头的行： AGAINST('abc-xyz*' IN BOOLEAN MODE) 。
其他操作符也能使用，含义如下：

布尔操作符      说 明
    +      包含，词必须存在
    -      排除，词必须不出现
    >      包含，而且增加等级值
    <      包含，且减少等级值
    ()     把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
    ~      取消一个词的排序值
    *      词尾的通配符
    ""     定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

另外一个注意的地方是，布尔搜索中不会对返回的结果进行匹配程度的排序。

5.插入数据

到此为止，检索数据的操作讲完了。正常的数据库要实现的功能有增删查改（用的最多的就是查，所以讲的篇幅最长），接下来讲其他的，首先就是增，即插入数据。

插入数据使用 INSERT ，一般格式为：

INSERT INTO 表名 (列名1, 列名2, ...)
VALUES (值1, 值2, ...);

如果对一张表内的所有值都进行指定（除了一些特殊列，比如 AUTO_INCREMENT 的列），则可以忽略列名：

INSERT INTO 表名 VALUES (NULL, 值1, 值2, ...);

这里使用NULL，是假设第一列为 AUTO_INCREMENT 的列，由于不能跳过它，就给了一个NULL值，不过这个NULL值会被mysql自动忽略。在生产环境中，不指定列名的写法尽量不要使用，因为它高度依赖列的顺序；在第一种写法中，对于有默认值或自增的列，可以不写出来。

如果插入多行数据，用：

INSERT INTO 表名 (列名1, 列名2, ...)
VALUES (值1, 值2, ...),
(值1, 值2, ...),
(值1, 值2, ...),
(值1, 值2, ...);

这样的格式。如果需要插入多条数据，尽量用这种形式，它的执行效率更高。

最后，有一种 INSERT SELECT 的写法，它一般用于从旧表中导出新表，一个比较简单的写法为：

INSERT INTO 新表 (列名1, 列名2, ...)
SELECT 列名1, 列名2, ... FROM 旧表;

新旧表的列名不需要相同，但次序是固定的。这里列出的 SELECT 比较简单，其实也可以写复杂一点。

6.更新删除数据

UPDATE 可以用于更新表中的数据。它的写法为：

UPDATE 表名 SET 
列名1=新值1,列名2=新值2, ... 
WHERE 条件;

一定要注意条件的设置，否则所有行都会被更新。如果确实要更新所有行的话，那就不写 WHERE 从句。
默认情况下， UPDATE 会有all or nothing的特性，即如果每一行都成功更新，则最后所有更新作为一个整体提交；一旦某一行更新时出现错误，则整个 UPDATE 操作都会被取消，之前更新的行也会恢复到原来的数据。所以 UPDATE 要么不更新，要么全部更新，没有中间状态，称为all or nothing（或者称为粒子性、事务管理）。

DELETE 用于删除数据。和 UPDATE 相同，如果不用 WHERE 从句限制，它会删除所有行。其一般格式为：

DELETE FROM 表名
WHERE 条件;

但如果确实要删除表中所有数据，更推荐使用 TRUNCATE TABLE 表名 ，它实际上是把原来的表删除并重建一张表，速度比 DELETE 快。经过笔者实验，如果表中有自增列，使用 DELETE 删除整张表后新建列不是从1开始，而 TRUNCATE 和 DROP 是从1开始的。

由于 UPDATE 和 DELETE 会破坏原来的数据，在更新或删除之前最好仔细检查 WHERE 指定的更新或删除范围是否正确，不要最后从删库到跑路。

7.创建和操作表

用 CREATE TABLE 创建表，其一般格式为：

CREATE TABLE 表名(
列名1 类型 是否可为NULL [默认值] [其他特殊项],
列名2 类型 是否可为NULL [默认值] [其他特殊项],
列名3 类型 是否可为NULL [默认值] [其他特殊项],
...
PRIMARY KEY (主键列名)
)[ENGINE=引擎名] [CHAR_SET=];

笔者更推荐的创建语句需要加上 IF NOT EXISTS 和一个自增的主键，如下：

CREATE TABLE IF NOT EXISTS 表名(
no INT UNSIGNED NOT NULL AUTO_INCREMENT, #这行的 NOT NULL 可以省略
列名1 类型 是否可为NULL [默认值] [其他特殊项],
列名2 类型 是否可为NULL [默认值] [其他特殊项],
列名3 类型 是否可为NULL [默认值] [其他特殊项],
...
PRIMARY KEY (no));

对于数据类型，可查看 Python，爬虫与深度学习（4）——番外篇（二）MySQL数据库基础 ；
是否可为NULL，只需要写 NULL 或 NOT NULL ，后者表示不可为NULL（后者更推荐，因为NULL在很多操作中被自动忽略，且可能影响性能，一般的做法是用一些不可能的值代替NULL，比如空字符串 "" ）；
默认值用 DEFAULT 设置，比如 DEFAULT "unknown" ；
其他特殊项，比如 AUTO_INCREMENT 。

笔者使用的是一个单独的自增列做主键，实际上也可以用其他列，只要这个列的值在每行都是唯一的，而且确定后就不会变化（笔者的导演表中使用导演名字做主键，但其实并不稳妥，因为人是可以改名的）；或者也可以用多个列的组合做主键，只要这些列值的组合在每个列也是唯一的就行，这时就写成 PRIMARY KEY (列名1, 列名2, ...) 即可。需要注意的是，主键列不允许可以为NULL值，即主键列必须是 NOT NULL 。

之前说过，如果表中有自增列，使用 DELETE 删除整张表后新建列不是从1开始，而 TRUNCATE 和 DROP 是从1开始的。用 SELECT LAST_INSERT_ID() 可获取最后使用的一个自增列值，把它+1后就是下一个即将使用的自增值。要注意的是，这个函数返回的是最近一次成功的 INSERT 操作插入的自增值，所以这个函数最好在插入操作后立即使用。

最后，mysql现在支持用函数做默认值，至少笔者知道datetime类型的列可以设置默认值为 DEFAULT NOW() 。

对表的定义进行更改称为操作表，从数据库维护的角度看，创建表的时候就要考虑各种情况，尽量在后期运行时不要更改表。使用 ALTER TABLE 可以操作表，常用操作如下：

ALTER TABLE 表名 ADD 列名 类型 是否可为NULL [默认值] [其他特殊项];
#在所有列的最后新增一个列
ALTER TABLE 表名 DROP COLUMN 列名;
#删除一个列
ALTER TABLE 表1名
ADD CONSTRAINT 外键名（可用fk_表1_表2，这里fk表示foreign key）
FOREIGN KEY (表1的列名) REFERENCES 表2名 (表2的列名);
#添加外键。这里 表2的列名 为表2的主键列名

另外，使用 RENAME 可以重命名表，格式为 RENAME TABLE 表名 TO 新的表名 。

如果要更改复杂、庞大的表，建议用数据迁移的方法而不用 ALTER ，步骤为：
用新的列布局创建新表；
用 INSERT SELECT 将旧表中的数据迁移到新表；
检验测试新表；
重命名或删除旧表，把新表改名为旧表名；
重新创建外链、触发器、索引等。

8.视图

SELECT 返回的是一个结果集，前面也提到从客户端的角度根本分辨不了哪些列是库中物理存在的，哪列数据是mysql服务器从已有的数据计算、导出的。其实， SELECT 返回的结果很像一张表，有时我们需要多次重复使用一个 SELECT 返回的结果（多次重复使用一条 SELECT 语句），那么就可以把该SQL存成一个视图。视图本质上是一个虚拟表，要遵守表的规则，比如：
不能与数据库中的其他表或视图重名；
和真实的表一样，视图也有访问权限；
视图可以当成真实的表使用，比如视图查询语句可以嵌套。

视图也有不同于表的地方，比如不能增删改（实际上可以，对视图操作就是对基表操作。但推荐不要通过视图更新基表，直接到基表里更新。视图只应该用于检索），不能索引，也不能设置默认值等。更重要的是每次调用视图时其实都是执行了原来的SQL，这可能对性能有影响。最后，要注意的是，如果创建视图的SQL中含有 ORDER BY ，那么调用视图的查询中再使用 ORDER BY 会被覆盖。

下面是视图的基本操作：

CREATE VIEW 视图名 AS SELECT语句
#创建视图
SHOW CREATE VIEW 视图名
#查看创建视图的语句
DROP VIEW 视图名
#删除视图
CREATE OR REPLACE VIEW 视图名 AS SELECT语句
#创建或更新视图。推荐用这条语句更新视图。

视图最主要的作用就是复用SQL，简化新创建的SQL语句。

9.存储过程及其他

存储过程是为了满足更复杂的需要而创建的SQL，有时可能涉及到条件判断等高级操作，它实际上是把SQL当高级语言用。这实际上已经一定程度上超出了使用mysql的日常需要了，而且使用python的pymysql库等高级语言进行处理有时更为方便，所以笔者这里就不进行介绍了。有兴趣的请自行搜索，或者查看 runoob教程。

其他类似的mysql进阶概念还有：
游标（用于交互式应用，比如用户滚动网页、更新加载查询结果）；
触发器（满足一些条件后就执行的SQL）；
事务管理和回退（就是各种操作的all or nothing。虽然pymysql使用时会涉及，但只是很少一点，并且是范式）；
字符集（charset）和校对（collate）；
安全管理（即用户管理，比如不要经常用mysql的root登录，严格控制用户权限等）。

对这些内容有需要的可查看《MySQL必知必会》原书。

10.数据库维护

主要是检查mysql是否正常运行，备份、还原数据库。

ANALYZE TABLE 表名
#检查表键是否正确
CHECK TABLE 表名 EXTENDED
#彻底检查表的一些常见问题
OPTIMIZE TABLE 表名
#在表中删除大量数据后，用来优化其存储空间的利用

在mysql中导出数据时，其实导出的是创建库、表所需要的SQL。导出、导入数据的命令如下：

mysqldump -uroot -p 库名 表名 > /backup/库名.表名.sql
#导出一张表。注意这里是在mysql命令行外面，即cmd或terminal里执行的。后面的路径或文件名可自行更改
mysqldump -uroot -p 库名 > /backup/库名.sql
#导出一个库
mysqldump -uroot -p --all-databases > /backup/all_database_dump.sql
#导出mysql中的所有库
mysql -u root -p 库名 < /backup/库名.表名.sql
#在一个库中导入一张备份的表
mysql -u root -p < /backup/all_database_dump.sql
#导入所有库

导入导出文件的后缀名没有什么特殊要求，但一般用.sql以示区分。

11.mysql使用的原则

在《MySQL必知必会》一书的最后，作者给出了使用MySQL的忠告：

（1）首先，MySQL（与所有DBMS一样）具有一定的硬件要求。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对于生产环境中的服务器来说，应该整点好的配置。一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
（2）MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用 SHOW VARIABLES; 和 SHOW STATUS; 。）
（3）MySQL是一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用 SHOW PROCESSLIST 显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用 KILL 命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
（4）总是有不止一种方法编写同一条查询语句，建议试验联结、并、子查询等，找出最佳的方法。
（5）使用 EXPLAIN 语句可以让MySQL解释它将如何执行一条 SELECT 语句。
（6）一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。
（7）应该总是使用正确的数据类型，在创建表时要仔细考虑。
（8）不要检索需求以外的数据。换言之，不要用 SELECT * ，除非你真的需要所有列。
（9）有的操作（包括 INSERT ）支持一个可选的 DELAYED 关键字，如果使用它，将在有可能时（服务器有空闲资源时）才实际执行该操作，但是它会立即给客户端返回一个信号，假装这个操作已经执行了，客户端就可以继续进行其他操作，如查询等。
（10）在导入数据时，应该关闭自动提交。你可能还要记住不要在新表建立时（指把旧表数据导入新表时）就创建索引（包括 FULLTEXT索引 ），而是应该在导入完成后再重建它们。
（11）建议对表创建索引以改善检索的性能。确定对哪些列创建索引是一件比较繁琐的任务，需要分析使用的 SELECT 语句以找出重复的 WHERE 和 ORDER BY 子句。如果一个简单的 WHERE 子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。但是有些时候不要创建索引，见下一条。
（12）索引可以改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们经常更改但不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
（13）你的 SELECT 语句中有一系列复杂的 OR 条件吗？改用 UNION ，你能看到极大的性能提升。
（14） LIKE 很慢。一般来说，最好是使用 FULLTEXT 而不是 LIKE （空间换时间）。
（15）数据库是不断变化的，一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
（16）最重要的规则就是，每条规则在某些条件下都会被打破。

12.python中使用mysql

首先，用pip安装pymysql库：

pip install pymysql

然后，在.py文件里使用pymysql：

import pymysql


conn = pymysql.connect(host='127.0.0.1',
                        port=3306,
                        user='root',
                        passwd='mysqlpasswd',
                        db='mysql',
                        charset='utf8')
cur = conn.cursor()

try:
    cur.execute('会对mysql进行更改的操作')
    conn.commit()
except:
    conn.rollback()

cur.execute('查询操作')
results=cur.fetchall()
#注意results是一个二维元组

注意更改host，port，user，passwd的值。另外，注意 conn.commit() 和 conn.rollback() 的使用，它们只在对mysql进行更改操作（增删改）时才使用。

Python，爬虫与深度学习（5）——番外篇（三）MySQL数据库进阶