步骤/目录：
1.mysql特性与基础概念，及安装
2.进入mysql命令行与基本设置
3.mysql命令行的基础命令
4.增删库、表及注意事项
5.表内数据的基础增删查改
6.LIKE 与模糊查找

本文首发于个人博客https://lisper517.top/index.php/archives/42/，转载请注明出处。
本文的目的是对MySQL进行简单介绍，便于用mysql存储爬虫爬取到的数据。
本文写作日期为2022年9月2日。本文主要整理自 runoob教程。进阶学习mysql可以参考《MySQL必知必会》、《高性能MySQL》等其他书籍，github上有收集这些CS书的仓库；或者查看笔者后续的文章。

1.mysql特性与基础概念，及安装

除了mysql，还有redis等各种数据库（或者mysql的亲兄弟mariadb等各种sql），它们是为了高速存储数据而出现的。mysql是一种关系性数据库，从上到下，它的构成是：各数据库（database） - 各张表格（table） - 每张表格由行和列组成，列有列名，每一行存储一个条目/记录，每一列的数据类型相同。另外，mysql中还有主键和索引的概念，每一行记录的主键都不同（其他列可能相同），所以主键可以用于区分每一行记录；索引，类似书籍的目录，用于快速访问数据。

mysql支持大型数据库，单张表的上限主要和操作系统允许的最大文件有关，一般来说，一张表占4GB就差不多了，再高可能有一些性能上的问题。

最后，mysql是开源的，目前是被Oracle买下。

在安装mysql上，笔者建议尽量用docker-mysql的方式安装，可参考 docker-mysql官网，或笔者之前的文章。一个基础的docker-compose模板如下：

version: "3.9"

services:
  mysql:
    image: mysql:8.0.29
    environment:
      MYSQL_ROOT_PASSWORD: mysqlpasswd
    ports:
      - "53306:3306"
    command:
      - mysqld
      - --character-set-server=utf8mb4
    volumes:
      - /docker/mysql/data/mysql:/var/lib/mysql
      - /docker/mysql/conf/mysql:/etc/mysql/conf.d
      - /docker/mysql/backup/mysql:/backup
    logging:
      driver: syslog
    restart: always

注意更改一下 MYSQL_ROOT_PASSWORD: mysqlpasswd 这里的密码和 - "53306:3306" 这里的端口（不建议用默认的3306端口）。

2.进入mysql命令行与基本设置

使用命令： mysql -h 127.0.0.1 -P 3306 -uroot -p 可以mysql里的root用户身份登录mysql， -h 参数指定mysql服务器端的ip， -P 指定端口， -u 指定登录用户（如果用root以外的其他用户登，可用 -u 其他用户名 的格式。注意登录的用户不是linux系统里的用户，而是mysql数据库里的用户）， -p 指定密码登录，之后输入密码即可。如果是本机、默认的3306端口（mysql默认使用3306端口），则前两个参数可以省略。最后，登录其他机器上的mysql服务器时要注意登录的用户是否允许远程登录（docker-mysql则没有这个问题）。

如果使用docker-mysql，根据 docker-mysql官网的描述，将自己的mysql配置文件夹挂载到容器的 /etc/mysql/conf.d 文件夹，如果该文件夹里有任何cnf结尾的文件都会覆盖docker-mysql的默认配置。另外，docker-mysql的默认配置文件为容器里的 /etc/mysql/my.cnf 。关于配置方面，笔者几乎没有更改过，所以这方面了解的不多。

用 mysql -h 127.0.0.1 -P 3306 -uroot -p 登录到mysql命令行后，可以新建其他用户。比较推荐的是用GRANT命令：

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON TUTORIALS.*
TO 'zara'@'localhost'
IDENTIFIED BY 'zara123';

这条命令的第一行是为新建的用户赋予权限。这里赋予了该用户查询、插入、更新、删除数据，及新建、删除数据库/表的权限。也可以自行增加或删除权限，或者用 ALL PRIVILEGES 指代所有权限。
第二行限定该用户只对TUTORIALS这个database下的所有表有这些权限。
第三行指定用户名为zara、登录地点限于从本机登录（当然，对docker-mysql无效）。
最后一行指定zara用户的密码为zara123。
REVOKE 可剥夺用户的选项，语法和 GRANT 是一样的，只是把 TO 换成 FROM 。

3.mysql命令行的基础命令

mysql命令对大小写不敏感，但笔者建议，自己随便查查的时候可以用全小写，但写到python里的还是建议把所有mysql的关键字大写，以后阅读的时候更容易区分。另外，所有的命令最后都以 ; 结尾，只有少数简单的命令（如 USE 数据库名 ）等可以不加 ; ，笔者写的时候有时会省略，自己写的时候记得尽量加上。

SHOW DATABASES 显示所有数据库的名字；
用 USE 数据库名 选择要操作的数据库（database）；
SHOW TABLES 显示该数据库下所有表名；
SHOW COLUMNS FROM 数据表名 或 DESCRIBE 数据表名 显示表的各列是什么数据类型等信息；
SHOW INDEX FROM 数据表名 显示数据表的详细索引；
SHOW TABLE STATUS [FROM 数据库名] [LIKE "模式"] [\G] 可输出数据库的性能、统计信息， [] 里括起来的是可选项。注意 \G 也可以起 ; 的作用，添加这个选项就不加 ; 。

4.增删库、表及注意事项

CREATE DATABASE 数据库名 来创建一个新的库；更保险的做法是 CREATE DATABASE IF NOT EXISTS 数据库名 。
DROP DATABASE 数据库名 删除数据库。

创建表则更为复杂，因为要指定每一列的变量类型。mysql中大致有数值、时间、字符串这三种类型的变量，具体见下：
数值（分有符号和无符号）

名称          占用字节  表示范围
TINYINT       1 Bytes    (-128，127)或(0，255)
SMALLINT      2 Bytes    (-32 768，32 767)或(0，65 535)
MEDIUMINT     3 Bytes    (-8 388 608，8 388 607)或(0，16 777 215)
INT或INTEGER  4 Bytes    (-2 147 483 648，2 147 483 647)或(0，4 294 967 295)
BIGINT        8 Bytes    (-9,223,372,036,854,775,808，9 223 372 036 854 775 807)或(0，18 446 744 073 709 551 615)
FLOAT         4 Bytes    10的+-38次方级
DOUBLE        8 Bytes    10的+-308次方级
DECIMAL       对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2    依赖于M和D的值

另外还有 BIT位、 REAL实数等数值。

时间（分为日期和时间）

名称     占用字节           范围                                                格式
DATE        3       1000-01-01/9999-12-31                                   YYYY-MM-DD
TIME        3       '-838:59:59'/'838:59:59'                                HH:MM:SS
YEAR        1       1901/2155                                               YYYY
DATETIME    8       '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'          YYYY-MM-DD hh:mm:ss
TIMESTAMP   4       '1970-01-01 00:00:01'UTC 到 '2038-01-19 03:14:07'UTC    YYYY-MM-DD hh:mm:ss

字符串（分二进制形式和字符形式）

名称          占用字节                   用途
CHAR        0-255 bytes               定长字符串
VARCHAR     0-65535 bytes             变长字符串
TINYTEXT    0-255 bytes                 短文本字符串
TEXT        0-65 535 bytes            长文本数据
MEDIUMTEXT  0-16 777 215 bytes        中等长度文本数据
LONGTEXT    0-4 294 967 295 bytes     极大文本数据
TINYBLOB    0-255 bytes               不超过 255 个字符的二进制字符串
BLOB        0-65 535 bytes            二进制形式的长文本数据
MEDIUMBLOB  0-16 777 215 bytes        二进制形式的中等长度文本数据
LONGBLOB    0-4 294 967 295 bytes     二进制形式的极大文本数据

另外还有BINARY、VARBINARY等。
对于汉字，用UTF-8编码时占3个字节、用GBK时占2个字节；VARCHAR(n)表示无论汉字或英文，都能存入n个字符（所以占用字节长度会变化，称为 VAR CHAR ）。
一般来说，经常变化的字段用VARCHAR，定长、不超过255字节的用CHAR（不够长度的部分mysql在存储时会用空格补齐），偶尔用TEXT（TEXT不能有默认值）。最常用的就是VARCHAR。

创建数据表示需要指定每列的数据类型，还要指定主键。例句如下：

CREATE TABLE IF NOT EXISTS `test`(
`no` INT UNSIGNED AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL DEFAULT "unknown",
`created_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`no`)
)ENGINE=InooDB DEFAULT CHARSET=utf8;

反引号（就是键盘上ESC下面那个键）起到引号的作用（为了避免有时可能这些名字和mysql关键字冲突，但是尽量勿用关键字做表名、列名）； UNSIGNED 为无符号； AUTO_INCREMENT 说明这列的数据是自动增加的（要注意，如果有些行被删除了后再新建行，后面的行 no 值是从被删除的行开始算的，也就是说一个 no 被用过一次后就不会再用，即使对应的行被删除）； NOT NULL 指定这一列不能为空（应该用"0"或其他值代替NULL，可提高查询性能，这里用的是"unknown"）； DEFAULT 指定默认值； CURRENT_TIMESTAMP 则是该行被创建时的时间戳。 ENGINE=InooDB 指定存储引擎， DEFAULT CHARSET=utf8 指定默认编码（这两个也可以不指定，现在mysql默认引擎就是InooDB，默认编码则可输入 STATUS; 查看）。一般创建表的时候就指定默认编码，笔者使用的docker-compose中有：

    command:
      - mysqld
      - --character-set-server=utf8mb4

mysql默认编码为Latin1，要换成utf8或GBK才能存储中文。

删除表就用 DROP TABLE 表名 即可。

5.表内数据的基础增删查改

用 INSERT 向表内插入数据：

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

可向表内插入一行数据。插入的值是字符串时，使用单引号或双引号括起来。如果某一列有默认值或者是自动生成的，可以忽略、不用写出列名和值。
下面则是添加多行数据：

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

查找数据用 SELECT ，最基本的是 SELECT * FROM 表名 ，返回表内的所有行，其中 * 表示匹配所有列。一般格式为：

SELECT 列名1, 列名2, ...
FROM 表名
[WHERE Clause]
[LIMIT n]
[OFFSET m];

中括号内的都是可选项。 WHERE 从句用来限定条件， LIMIT 限制返回的记录数， OFFSET 指定开始查询的行数（偏移量）， LIMIT m,n 是 LIMIT n OFFSET m 的简写（从第几条开始，返回多少条结果）。
在高级查询中，可以从不止一个表中查询数据，表名间用 , 分隔，搭配 WHERE 使用。实际上，删查改一般都要用 WHERE 限定范围。

mysql中的比较符： = 为等于， <> 或 != 为不等，此外还有 > 、 < 、 >= 、 <= 。另外， BINARY 可以区分大小写：

SELECT * FROM 表名 WHERE BINARY 列名="字符串";

改数据用 UPDATE ，一般格式如下：

UPDATE 表名 SET 列名1=值1, 列名2=值2 [WHERE Clause]

比如，把符合条件的行的列值+1：

UPDATE test SET int_data=int_data+1

又或者用mysql函数：

UPDATE test SET str_data=REPLACE(str_data,"旧字符串","新字符串")

上述命令将该列所有数据内的旧字符串替换为新字符串（部分替换）。

删除记录用 DELETE ，一般格式为：

DELETE FROM 表名 [WHERE Clause]

不加 WHERE 时，会删除所有行。但是，如同之前提到的，如果列中有 AUTO_INCREMENT ，下次再加新行时会从断点继续，就是说新行的自增列不会从1开始。

另外，mysql中的删除其实有三个关键字： DELETE 、 DROP 、 TRUNCATE ，第三个的使用方式：

TRUNCATE 表名

可以把 TRUNCATE 看作是不带 WHERE 的 DELETE FROM ，只用于删除表中的全部数据，优点是执行更快一些；DROP 是把表也给删了，速度最快。

6.LIKE 与模糊查找

前面已经介绍过了， WHERE 从句中可以使用 = 等符号。有时要只想匹配部分字符串，可以使用 LIKE ，或 % 、 _ 通配符。另外mysql也可以使用re，见后续文章。
LIKE 可以替代 = ：

WHERE 列名1 LIKE 条件1

比如：

WHERE field1 LIKE "%com%"

只要某行的field1列中含连续的 com 三个字符，就符合条件。如果改成 _com_ ，就是指必须是5个字符，中间三个为 com 。也就是说， % 匹配任意个数（包括0个）的任意字符， _ 匹配单个任意字符。
另外， LIKE 里的条件类似re，也有 [] （括号内所列的任意一个字符）、 [^] （非括号内的任意一个字符），如：

LIKE "[abc]"

表示匹配单个字符，这个字符为 a 或 b 或 c 。 LIKE "[^abc]" 则表示不能是 a 或 b 或 c 。
在中括号内，用 [] 括起来表示原本的通配符，即 [[%]abc] 、 [[_]abc] 、 [[[]]abc]（匹配 [）。

到这里为止，mysql的基础已经讲解完毕，其实本文的内容对于爬虫中使用mysql几乎够用了，在下一篇文章中将会讲解进阶的内容。

Python，爬虫与深度学习（4）——番外篇（二）MySQL数据库基础

1.mysql特性与基础概念，及安装

2.进入mysql命令行与基本设置

3.mysql命令行的基础命令

4.增删库、表及注意事项

5.表内数据的基础增删查改

6.LIKE 与模糊查找

添加新评论

最新文章

热门文章

最近回复

分类

归档

其它