步骤/目录： 1.Redis简介与安装 2.redis的数据结构 （1）string （2）list （3）set （4）hash （5）zset （6）HLL 3.redis的一些命令 4.redis事务 5.TTL（time to live） 6.排序 7.发布/订阅 8.管道 9.redis GEO 10.其他本文首发于个人博客https://lisper517.top/index.php/archives/53/，转载请注明出处。本文的目的是初步介绍Redis、为爬虫使用redis打好基础，主要整理自 runoob教程 。这里介绍的内容非常浅显，更深入的内容请自行查询书籍。本文写作日期为2022年9月15日。运行的平台为win10和树莓派，编辑器为VS Code。1.Redis简介与安装Redis是一个由 Salvatore Sanfilippo 在2008年开始创造的 key-value 存储系统，是一种开源的非关系型（NoSql）数据库，它的特点有：基于内存，分布式，键值对，支持多种语言的API（Redis本身是由ANSI C写的）。虽然它主要在内存中运行，但也可以将数据保存在磁盘；它支持主从式的数据备份；它的读写性能极高（所以把Redis开放到公网上时还要注意安全性）；它支持事务，多个操作可以打包到一起，要么不执行，要么全部执行（all or nothing）；它支持5种数据类型（字符串，列表，hash散列，集合，有序集合）；它支持推送，key过期。总的来说Redis就是好。在使用Redis时，你可以把它用作主数据库，也可以用作辅数据库，不论何种方式，都是对关系型数据库功能的极大补充。由于redis的一些特性，它特别适合做存储标签这种一对多的工作，而存储标签在mysql这样的关系型数据库中比较麻烦；redis可以设置键的过期时间TTL（time to live），到期后自动过期，这是很多服务都需要的（比如登录cookie的过期）；redis的读写效率非常高，在轻度使用中很少会遇到redis是瓶颈的情况；redis的命令比sql要简单得多（因为它以键值对形式存储数据）。下面介绍三种安装redis的方法。需要注意，redis为了效率而牺牲了安全性，所以默认情况下redis几乎没有保护措施，不要把它开放到外网。在windows上安装redis：window版redis由热心网友维护，版本远落后于linux版或docker版（目前是5.0和7.0的差距），所以不推荐下载。需要的话，建议在windows上用docker安装redis，见 Docker入门（一） 。如果嫌麻烦的话，到 windows版redis下载网页 下载zip文件或msi文件，解压或安装即可。解压zip文件，或用msi文件安装后，在cmd中进入redis目录，运行 redis-server.exe redis.windows.conf 即可运行redis服务器端。然后在同样的目录下，运行 redis-cli.exe -h 127.0.0.1 -p 6379 即可连接到redis数据库。但是要注意，目前它的版本只有5.0，笔者在后续将演示redis使用tls双向验证，但这是redis 6.0后才加入的功能。所以有条件的话还是用docker装redis。在linux上：到 https://download.redis.io/releases/ 下载tar.gz文件，解压、 make 即可。具体过程为：mkdir /usr/local/redis cd /usr/local/redis wget https://download.redis.io/releases/redis-7.0.4.tar.gz #最新版本的redis tar -xzf redis-7.0.4.tar.gz cd redis-7.0.4 apt-get install pkg-config make gcc libssl-dev #一些依赖项 make BUILD_TLS=yesBUILD_TLS=yes 这个选项在redis 6.0以后可用，是用tls在传输层对redis通信加密的一种方式，在后续安全性的部分会提到。这里介绍一下如何用docker安装redis（安装docker、docker-compose请参考 Docker入门（一） 和 Docker入门（二））。在linux上使用如下命令（windows上也是一样的步骤，只是路径改一下）：mkdir -p /docker/redis/data/redis vim /docker/redis/docker-compose.yml写入如下内容：version: "3.9" services: redis: image: redis ports: - "56379:6379" volumes: - /docker/redis/data/redis:/data logging: driver: json-file restart: always然后运行：cd /docker/redis docker-compose config docker-compose up -d然后你可以使用redis客户端尝试连接，比如本文后面将用的python，但是首先需要 pip install redis 。然后写一个py文件：import redis, pprint r = redis.StrictRedis(host='127.0.0.1', port=56379, decode_responses=True, db=0) pprint.pprint(r.info())运行，如果成功连接的话它将会打印出redis服务器的一些信息。在python中，更好的连接方式是使用连接池：import redis pool = redis.ConnectionPool(host='127.0.0.1', port=56379, decode_responses=True, db=0) r = redis.Redis(connection_pool=pool)或者你也可以使用redis-cli连接，这是redis服务器端自带的软件。比如你可以进入docker-redis容器：cd /docker/redis docker-compose exec -it redis bash redis-cli -h localhost -p 6379 SAVESAVE 将返回一个 OK ，并且在/docker/redis/data下出现一个dump.rdb文件。实际上， SAVE 会把redis在内存中的数据写到磁盘中，这会在后面提及。更常用的测试连接命令为 ping ，服务器会返回一个 PONG 表示服务器已连接。另外， clear 可以清屏， exit 可以断开连接。最后，如果你想在redis中看到中文，使用 redis-cli --raw 来发起连接。除了redis-server、redis-cli，你还能找到redis-benchmark（性能测试工具，使用方法同redis-cli）、redis-check-aof（AOF文件修复）、redis-check-dump（RDB文件检查）。另外，如果想让redis把内存数据写到硬盘并关闭服务器，可使用 redis-cli SHUTDOWN 命令（由于docker-compose.yml里写了 restart: always ，所以它还会重启）。最后再提醒一下，目前的redis安装方式，效率极高，但安全性几乎没有，不要把它开放到外网了。后续将介绍如何通过一些方法提升redis的安全性。安装好redis后，使用 select 数据库编号 来选择一个数据库。默认情况下redis有16个database，从0~15编号，不支持自定义数据库名（你需要自己记住每个库里存了什么），不支持用户对单独一个数据库有权限。2.redis的数据结构正如前面提到过，redis中有5种数据结构：string字符串，list列表，hash散列，set集合，zset有序集合，接下来将一一介绍它们。下面介绍的操作是一小部分，更多操作参考 redis官网 。（1）string首先是字符串，它是redis最基本的类型，一般的格式就是一个键对应一个字符串。它还可以存储二进制数据，比如图片等，但它最大只能存储512MB（毕竟redis跑在内存里）。你可以在redis-cli尝试下面3种命令（set，get，del）：set test 123 OK get test "123" del test (integer) 1 get test (nil) del test (integer) 0set用于给键赋值，get用于取出键对应的值，del用于删除一个键。在上面的例子中，如果你在python中运行，代码如下：import redis r = redis.StrictRedis(host='localhost', port=536379, decode_responses=True, db=0) a = [] a.append(r.set('test', '123')) a.append(r.get('test')) a.append(r.delete('test')) a.append(r.get('test')) a.append(r.delete('test')) for i in a: print(i)这里把每个返回的值都打印了出来，注意观察对应关系。另外，如果字符串是纯数字的话，redis提供了 INCR ，用于让其自增1，这个函数返回自增后的数字（如果键不存在，会自动创建一个为 "0" 的值并自增为1）。常见的例子是用于存储阅读量，比如键为 page.view ，在里面存储阅读量。注意在redis键的命名中，要力求写详细、直观，一般用 : 或 . 来分隔。在mysql中对列可用指定 AUTO_INCREMENT 来让列值自增，在redis中则可以用 INCR 达到类似的效果。 INCRBY 可达到相同的效果，但是它可以指定增加多少。同系列的命令还有 DECR 、 DECRBY （decrby每太大用，因为incrby可以设置成负值）， INCRBYFLOAT （增加浮点数）。其他关于字符串的命令还有：APPEND ，用于向字符串的末尾追加字符串。STRLEN ，获取字符串长度。注意UTF-8编码中一个中文字符的长度为3。MSET 和 MGET 同时设置、获取多个键值。位操作，包括 GETBIT （获得字符串的一个bit位，超出范围时返回0）、 SETBIT （返回旧值）、 BITCOUNT （返回为1的bit个数）、 BITOP （进行位运算，包括 AND 、 OR 、XOR 、 NOT）。 使用位操作，可以让存储空间非常紧凑。最后，下面介绍的其他类型都是字符串衍生出来的，它们的键值只能是字符串，不能嵌套。（2）list下面介绍一下列表。列表是有序的，可以存储多个字符串，在redis-cli中它支持的方法有：lpush 在列表头部添加一个元素 rpush 在列表末尾添加一个元素 lrange 获取从头到尾、一定范围的元素（包含两端；支持负索引） ltrim 用法和lrange一样，但是它将会删除范围外的元素 lindex 获取从头到尾的第几个元素。支持负索引 lset 给出索引并赋值指定元素 lpop 从列表头部弹出一个或几个元素（弹出，意为返回这个元素的值，并且从列表中删除它） rpop 从列表末尾弹出一个或几个元素 llen 获取列表中元素个数 lrem 删除列表中的几个元素，这些元素的值需要指定。格式为 LREM key count value，count指定要删除几个，当count为正时从左边开始删，为负时从右边开始删，0时删除所有 linsert 在一个元素的前或后插入元素 rpoplpush 将旧列表转移到新列表 brpop 类似rpop，区别在于列表内无元素时会被阻塞（每当列表加入一个新元素时brpop就会返回这个元素）。常用于实现任务队列（可带优先级）如果熟悉数据结构的话，会发现这里的列表可以当作栈或队列使用。在redis-cli中实验如下：lpush test 123 (integer) 1 lpush test 234 (integer) 2 lpush test 345 (integer) 3 rpush test 456 (integer) 4 lrange test 0 -1 1) "345" 2) "234" 3) "123" 4) "456" lindex test 0 "345" lpop test "345" rpop test 2 1) "456" 2) "123" lrange test 0 -1 1) "234" del test (integer) 1除了这些命令，redis对列表还有其他操作，将在后面介绍。另外，一个列表最多可存储 2^32-1 个元素，即 4,294,967,295 。（3）set列表中可以出现相同的元素，且有序，但集合中每个元素不能相同，且set是无序的（set里的元素只有键没有值）。它支持的命令有：sadd 添加一个元素到集合 srem 从集合中移除一个元素 sismember 检查元素是否在集合中 smembers 返回集合的所有元素。这个命令在集合较大时运行很慢 sdiff 差集 sinter 交集 sunion 并集 sdiffstore 存储运算结果 sinterstore 同上 sunionstore 同上 scard 获得集合中元素个数 srandmember 随机获取一个元素。它可以添加count参数，当count为正时取出count个不重复元素，为负时表示可以重复。但是由于redis的底层机制，某些元素出现的概率可能更大 spop 随机弹出一个或多个元素使用例：sadd test 123 (integer) 1 sadd test 234 (integer) 1 sadd test 234 #添加重复元素时被忽略了 (integer) 0 sadd test 345 (integer) 1 sadd test 456 (integer) 1 srem test 123 (integer) 1 sismember test "123" (integer) 0 sismember test "234" (integer) 1 smembers test 1) "234" 2) "345" 3) "456" del test (integer) 1redis中的集合使用哈希表实现（只有键的hash），它增、删、查元素的复杂度都是O(1)。除了这些命令，redis对集合还有其他操作，将在后面介绍。另外，一个集合最多也可存储 2^32-1 个元素，即 4,294,967,295 。在一般的实践中，常用set来存储tag，或者用列表。比如，键名为 tag:标签名:posts ，值为set，存储带有该标签的文章id（4）hash散列是存储键值对的集合，它存储的值可以为字符串或数字（不能嵌套字符串以外的类型）。hash的命令有：hset 在hash中添加或修改一个键值对 hget 获取hash中指定键的值 hmset 添加或修改多个键值对 hmget 获取多个键值 hsetnx 仅当键不存在时新建一个键 hincrby 类似字符串的 incrby 操作 hgetall 获取hash中所有键值对 hexists 判断键是否存在 hdel 如果hash中存在一个或多个键，删除它 hkeys 获得所有键名 hvals 获得所有值 hlen 获得键值对数量使用例：hset test 1 111 (integer) 1 hset test 2 222 (integer) 1 hset test 3 333 (integer) 1 hset test 1 123 (integer) 0 hget test 1 "123" hgetall test 1) "1" 2) "123" 3) "2" 4) "222" 5) "3" 6) "333" hdel test 1 (integer) 1 hget test 1 (nil) del test (integer) 1一个hash可以看作mysql中的一行，不同之处在于每行包含的列可以不同。hash比较适合存储对象；它同样能存储 2^32-1 个键值对，即 4,294,967,295 。现在可用比较一下string和hash。比如你想存储一篇文章，需要存储的信息有文章名，文章url，文章内容等。那么你此时有两种选择，一是把文章对象序列化、转换为二进制数据，用string存储；二是用hash，把它当成mysql中的一行。用hash显得更直观，也不需要在读写是进行编解码。（5）zset有序集合，它可以看作是介于列表和集合之间，因为zset中的元素是唯一的，但它里面的元素有序；但它又像散列，存储键值对（但是zset中的键称为member，值称为score，值必须为浮点数）。zset支持的操作如下：zadd 添加键值对到zset（ +inf 和 -inf 表示正无穷和负无穷） zrange 取出范围内的成员值（键值），按score从小到大 zrevrange 同上，但是score从大到小 zrangebyscore 给定score范围，取出所有在范围内的元素（包含极值。如果不想包含，可加上 ( 如 "80 (100"） zrem 如果zset中存在一个或多个成员，删除它 zscore 获取元素的分数 zincrby 增加score zcard 获取元素个数 zcount 获取分数在范围之间的元素个数 zremrangebyrank 按照元素score从小到大的顺序删除范围内的元素 zremrangebyscore 删除指定分数范围的元素 zrank 获取元素的排名，从小到大排列 zrevrank 同上，从大到小 zinsertstore 计算并导出有序集合的交集。新zset中的分数会使用聚合函数，支持sum、min、max。还能设置权重 zunionstore 计算并导出并集使用例：zadd test 123 1 (integer) 1 zadd test 234 2 (integer) 1 zadd test 345 3 (integer) 1 zrange test 0 -1 1) "1" 2) "2" 3) "3" zrangebyscore test 124 345 1) "2" 2) "3" zrem test 1 (integer) 1 del test (integer) 1它也支持 2^32-1 个键值对。在应用场景上，一般string用于存储文件、文字；hash用于存储对象（描述对象的各种属性），可以当成mysql中的一行来使用；list用于列表、队列；set可用于去重；zset用于排行、权重等。（6）HLL在2.8.9版本中，redis添加了HyperLogLog，这种结构专用于数据量很大的集合，在一定误差内返回集合中元素的个数（不能返回元素本身），它主要用于计算视频点击量（上千万、上亿次）等。它的特点有：占用空间小，一个HLL键在redis中只占12KB，就能统计2^64量级的元素；使用了一些概率算法，标准差在 0.81% 左右（可以设置辅助因子降低，但要付出空间）。redis中HLL相关命令有：pfadd 添加元素到HLL中 pfcount 返回个数估算值 pfmerge 将多个HLL合并为一个HLL3.redis的一些命令除了前面提到的 ping ， exit ， clear ， save ， shutdown ， select ，数据类型相关的命令，还有一些命令：keys 模式 ，返回所有匹配模式的键。在模式中， ? 匹配一个字符， * 匹配0到任意个字符， [] 用于给出单个字符的范围， \ 用于转义。DEL ，删除一个或多个键。不能用通配符，但是可在linux命令行借助管道与xargs实现类似的功能： redis-cli KEYS "模式" | xargs redis-cli DEL 。DUMP ，把一个键序列化，类似python中decode的功能。EXISTS ，检查键是否存在。EXPIRE ，为键设定过期时间（单位为秒）。到过期时间后，键会被删除。EXPIREAT key timestamp ，和 EXPIRE 功能相同，但是使用UNIX时间戳（从1970年1月1日到现在的秒数）。PEXPIRE ，和 EXPIRE 功能相同，只是单位为毫秒。PERSIST ，取消键的过期时间。TTL （time to live），以秒为单位返回键的剩余时间。PTTL ，以毫秒为单位返回键的剩余时间。KEYS ，给出一个pattern，返回符合模式的键。MOVE ，把键转移到另一个库中（redis中默认有16个库）。RANDOMKEY ，返回一个随机键。RENAME ，修改键名。RENAMENX ，仅当要改成的键名不存在时，将旧键名改为新键名。SCAN ，返回可迭代的数据库键（数据库编号）。TYPE ，返回键对应值的类型。更多命令可参考 redis官网 。4.redis事务事务（transaction）是现在很流行的一个概念。设想我进行一个转账1000￥的操作，银行的数据库系统需要进行两步：从我的账户扣去1000，然后给目标账户增加1000。但是如果中间出了差错，比如我的账户扣钱、对方账户却没有收到钱，这怎么办呢？这种类型的问题可用事务解决，包括在事务内的多条命令要么全部执行，要么全部都不执行，不存在执行一半的状态。在redis-cli中执行事务如下：multi 命令1 命令2 ... execredis先接受各条命令，当接收到exec时redis会将所有命令执行。如果在客户端输入命令时断线，redis就不会执行命令。另外，事务不会被其他命令打断，redis在执行一个事务时会把它执行完，再执行其他命令或事务；redis中的事务也不支持回滚。另外，如果事务中的某条命令有错误，其他命令也会继续执行。事务相关的命令除了 multi 和 exec ，还有：discard 取消事务 watch 监视键 unwatch 放弃监视键watch 是事务的重要功能之一，它用于监视键，保证在事务执行前相关的一些键不被改动，一般格式为：watch multi exec如果watch监视到了键被改动，事务的执行将被阻止（watch的作用不是阻止键被改动，而是阻止事务执行）。5.TTL（time to live）这里介绍一下redis的键过期功能。使用 expire 可以为一个键设置过期时间，单位为秒，到时间后该键会自动被删除。使用 ttl 则可以返回一个键剩余的时间，单位为秒（当键不存在时返回-2，键没有设置过期时间时返回-1）。使用 persist 可以取消键的过期时间，成功取消时返回1，否则返回0。另外，如果用 set 、 getset 等命令为键赋值，也会取消过期时间（incr 、 lpush 、 hset 、 zrem 等修改键值的命令则不会）。pexpire 以毫秒设置ttl。对应的有 pttl 。最后，还有 expireat 和 pexpireat ，它们给出的是UNIX时间，到该时间后删除键。另外，如果键是被expire相关命令删除，watch不会认为键被改动过。使用ttl功能，可以方便地为网页提供cookies等服务。6.排序redis中使用 sort 排序（升序），用 sort key desc 降序排序，用 sort key alpha 对字符串排序。在hash中，使用 sort 键名 by 参考键名*->字段名 可以指定排序的字段，redis会用字段值来替换 * ，by只能出现一次； sort by GET 参考键名*->字段名 可以返回指定的字段，get可以出现多次，用 # 可以代替元素本身的值； sort by get STORE 键名 可以把结果保存到新的键中。7.发布/订阅redis中提供了发布、订阅的功能。类似youtube，发布者向频道（channel）中发布一个消息，订阅者会收到这个消息。发布消息的命令为 publish ，订阅频道的命令为 subscribe 。另外还有以下命令：unsubscribe 可以取订一个或所有频道；psubscribe 按一定的模式订阅频道。支持的匹配符有 ? 、 * 等；punsubscribe 按模式退订频道。在分布式的scrapy中，主机发布待爬url就可以用publish，所有订阅url频道的机器就能收到待爬url。另外，redis 5.0后新增了stream，它主要用于消息队列。publish的一个特点是不会存储之前发布的消息，就是说新订阅的用户不能接受到订阅之前发布的消息。stream就是为了解决这个问题而出现的，它对历史发布的消息进行了持久化8.管道在redis的使用中，上面提到的方法都有返回值。日常使用中，redis-cli发出指令，server收到后执行并返回结果，如果client要执行多条语句，有时只需要最后的值，就可以使用管道。管道应用于多条命令，每条命令的执行不依赖于前面的命令，只需要最后的结果时。管道的具体实现和编程语言有关，比如用python：import redis pool = redis.ConnectionPool(host='127.0.0.1', port=56379, db=0, decode_responses=True) r = redis.Redis(connection_pool=pool) pipe = r.pipeline() pipe.set('test', '123') #pipe的其他操作 pipe.execute()你会发现这和pymysql差不多。使用管道，即使在局域网中，也可以大大降低延迟。9.redis GEO在redis 3.2版本新增了geo功能，主要用于计算实际的地理位置。这里就不过多介绍了。10.其他save 命令可将内存中的数据写入磁盘，默认情况下是dump.rdb文件。恢复数据时将这个文件放到redis安装目录（用 config get dir 查看）即可（docker-redis则是放到容器的/data下）。bgsave 在后台执行save。client 相关操作，包括 client list （查看所有连接的客户端）、 client setname （设置当前连接的名称）、 client getname （获取连接名称）、 client pause （客户端挂起多少毫秒）、 client kill 关闭客户端连接。redis支持分区，就是把一个很大的键分成小份，每个redis实例只保存它的一个子集，这个技术稍微有些复杂，不拓展了。redis命令行使用Lua语言，并且支持编写脚本（如同mysql）。客户端提供一个脚本后，其会永久存储在redis中，便于其他客户端使用。Lua的语法这里也不介绍了。config 系列命令，可获取server的配置。redis支持主从模式，这主要是因为一台机器的资源有限。当需要扩展时，你可以在一台机器上运行主redis，其他机器上运行从redis；主redis负责写入的操作，从redis会把数据拷贝到本地、负责读取的操作，也称为读写分离。最后，要提醒一下：注意redis的安全性；安装redis的时候提到过 make BUILD_TLS=yes 这个选项，但是redis应该尽量追求效率，只在内网运行，尽量不用tls等安全手段，因为会降低redis的速度。redis内部使用Lua语言，有时你使用爬虫入库的东西可能被当成脚本执行，比如说给你把redis清空（ flushall ），这点要注意（反爬手段之一）；如果想更深入学习redis，可以参考 redis的官方文档 或 《redis实战》 等书籍。
步骤/目录： 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与USESHOW 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进行更改操作（增删改）时才使用。
步骤/目录： 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=utf8mb4mysql默认编码为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，爬虫与深度学习（2）——爬虫详解（一）爬取金融机构成交持仓排名
步骤/目录： 1.需求分析 2.网页请求分析 3.mysql数据库的设置 4.爬虫编写 （0）安装所需模块 （1）日期 （2）爬虫主体编写 （3）mysql存储 （4）正式运行 5.总结与后续本文首发于个人博客https://lisper517.top/index.php/archives/39/，转载请注明出处。本文的目的是从实战出发讲解爬虫+python。由于还想讲解一下python的写法，所以爬虫写的稍微多了一点。本文写作日期为2022年8月30日。操作的平台为win10，编辑器为VS code。爬虫写的好，牢房进的早。使用爬虫时不要占用服务器太多流量，否则可能惹祸上身。除了python与爬虫，这里实际上还需要了解html格式与mysql命令。可以在 runoob-html 或 runoob-mysql 找到初步的学习资料，或者查看笔者后续的文章。最后，笔者的能力有限，也并非CS从业人员，很多地方难免有纰漏或者不符合代码原则的地方，请在评论中指出。1.需求分析在中国金融期货交易所（中金期货）的官网上有各个金融机构的成交持仓数据，具体网址为 http://cffex.com.cn/ccpm/ 。搜索期货、期权，选择类目、日期，即可看到各机构的成交持仓排名，这个数据可以一定程度上反映各金融机构对大盘走势的判断。本文将介绍如何爬取这些数据，在之后的文章中会试图用机器学习的方法对这些数据进行学习、预判。需求是爬取这些数据，并存入mysql数据库。这里会用到requests、bs4（BeautifulSoup）库，就不用scrapy这种牛刀了（scrapy一般也不会用于爬这种表格请求，而是用于递归爬取全站）。2.网页请求分析在chrome中打开网页，按F12打开网页开发工具，在右上角竖着的三个点里找到 停靠侧 ，选择 取消停靠至单独的窗口 。在网页窗口中选择期货，选择一种类目（比如IF），选择前一天，点击查询，这时网页里会出现数据，网页开发工具里会出现一条新的项目，点击这个项目，发现请求网址为 http://cffex.com.cn/sj/ccpm/2022XX/XX/IF.xml?id=97 ，其中 2022XX/XX 是当天的日期。复制这个请求网址并打开，发现是XML格式的数据（html），其中一项如下：<positionRank> <instrumentid>IF2212</instrumentid> <tradingday>2022XXXX</tradingday> <datatypeid>0</datatypeid> <rank>13</rank> <shortname>宝城期货</shortname> <volume>343</volume> <varvolume>136</varvolume> <partyid>0110</partyid> <productid>IF</productid> ...与原网页对照，其中 datatypeid 可能有0、1、2，分别表示成交量排名、持买单量排名、持卖单量排名。经过实验，网址里的 ?id=97 没有什么具体的意义，大概就是一个1~99的随机数。那么只用日期和品种生成网址就行了。最后检查一下请求和响应里有没有什么特殊的项目，没有则只设置一下请求的User-Agent（UA）即可。3.mysql数据库的设置这里选择用docker在树莓派上运行mysql服务。当然也可以直接安装mysql，各种平台安装mysql的教程见 runoob教程 。无论何种方法运行mysql，最关键的问题是搞清楚运行mysql机器的ip，和mysql的root用户密码。如果不是在本地运行mysql，还要注意mysql的root用户是否能远程登录。在树莓派上进行如下操作：mkdir -p /docker/mysql/data/mysql mkdir -p /docker/mysql/conf/mysql mkdir -p /docker/mysql/backup/mysql docker pull mysql:8.0.29 nano /docker/mysql/docker-compose.yml写入如下内容：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端口）。然后运行：cd /docker/mysql docker-compose config docker-compose up -d接下来进容器服务里看看mysql是否正常运行：docker exec -it mysql-mysql-1 bash #如果是老版本的docker-compose，把这一条命令中的所有 - 换成 _ mysql -uroot -p #输入密码 SHOW DATABASES;到此为止就可以了。在爬虫编写时，会检验需要的数据库、表是否创建，未创建的话会自动创建，这里就不需要手动创建了。如果直接安装mysql并从其他机器访问，可能需要远程登录mysql（docker-mysql无需如下设置），可在mysql命令行中输入如下命令：USE mysql; UPDATE user SET host='%' WHERE user='root';更多关于远程登录mysql的内容可自行搜索，或者查看这篇文章 。4.爬虫编写（0）安装所需模块需要用到requests（处理网络请求）、bs4（对响应内容进行解析）、pymysql（连接mysql数据库）、lxml（网页解析器）、cryptography（连接mysql时密码加密）库，在cmd中使用 pip install requests bs4 pymysql lxml cryptography 下载（下载失败就一直使用上述命令，直到下载成功）。之后新建一个文件夹，用VS Code打开该文件夹、新建一个py文件，进行下面的操作。（1）日期首先编写一个日期类，需要达到的目的主要是给出下一个日期，和计算一个日期是星期几。这里自己创建日期类完全是为了实践，其实可以直接使用python自带的datetime库。#!/usr/bin/python #这一行针对linux系统，指定python解释器的位置 # -*- coding: UTF-8 -*- #这一行指定本文件的内容编码格式为UTF-8 class NotValidateDate(Exception): #(Exception)表示这个类继承自Exception这个父类。python中的类都继承自一个基类，但可以不写出来。 """若日期不合法则抛出这个异常。""" #类、方法/函数、模块的注释都可以这样写。在VS Code中，把鼠标悬停在类、方法/函数、模块的名字或对象上就能看到注释。 def __init__(self): message = "Your input is not a validate date, please check. " super().__init__(message) #super函数用于调用父类的方法。 #如果日期的格式不对，就会抛出这个异常，并提示上述信息 class my_date(): """日期类，最主要的功能是给定一个日期，给出下一个日期；以及查看一个日期是星期几。""" def __init__(self, year: int, month: int, day: int, weekday: int = 0): #类的__init__方法在初始化一个对象时调用。这里对所有参数都指定了类型（int整型），对最后一个参数还指定了默认值。 #year、month、day、weekday 分别表示 年、月、日、星期几，其中星期几可以不提供 if my_date.validate(year, month, day): #验证日期的有效性后再用输入的数据初始化对象 self.year, self.month, self.day, self.weekday = year, month, day, weekday if weekday == 0: #如果weekday是默认值，说明未提供星期几，则进行计算。 self.weekday = self._get_weekday() #为了运行的效率，这里没有验证：若输入了weekday，weekday是否正确。 else: raise NotValidateDate #如果日期有什么地方不对，就抛出无效日期的异常 @staticmethod # @staticmethod 表示静态方法，即使没有实例化对象也可以调用，调用时用 类名.方法名 。另外注意静态方法的参数不要有self def validate(year: int, month: int, day: int) -> bool: # -> bool 指定了这个函数的返回值应该是bool变量 """验证给定的日期是否合法。""" if month < 1 or month > 12: #月份需要在1~12之间 return False elif month in (1, 3, 5, 7, 8, 10, 12): #大月的日期需要在1~31之间 if day < 1 or day > 31: return False elif month in (4, 6, 9, 11): #小月的日期需要在1~30之间 if day < 1 or day > 30: return False elif month == 2: #2月，闰年时日期在1~29，平年在1~28之间 if day < 1 or day > 29: return False elif day == 29: if not my_date.is_leap_year(year): return False return True #以上检测都通过则说明日期有效 # 1,3,5,7,8,10,12 31 # 4,6,9,11 30 # 2 28/29 @staticmethod def is_leap_year(year: int) -> bool: """判断是否闰年，是则返回True。""" return (year % 4 == 0 and year % 100 != 0) or (year % 400 == 0) #闰年的判断：能整除400，或者能整除4且不能整除100 def next_date(self): """以my_date对象形式返回下一天。""" y, m, d, w = self.year, self.month, self.day, self.weekday #python中可以这样进行多个变量赋值。=右边还可以是有序的序列，这种赋值称为解包 w = (w + 1) % 7 if w == 0: #当w=6时，(w + 1) % 7得0，但实际上需要得到7 w = 7 d += 1 if d == 32: #当日期为32，说明月份需要+1 d = 1 m += 1 if m == 13: #当月份为13，说明年份需要+1。反过来，仅当月份为12时年份可能需要+1，而12是大月。 m = 1 y += 1 elif d == 31: #当日期为31且月份为小月，月份也需要+1 if m in (4, 6, 9, 11): d = 1 m += 1 elif d == 30 and m == 2: #当日期为30、月份为2，月份也+1 d = 1 m = 3 elif d == 29 and m == 2: #当日期为29、月份为2、年份为平年，月份要+1 if not my_date.is_leap_year(y): d = 1 m = 3 return my_date(y, m, d, w) # a=my_date(y, m, d, w) 这样的写法赋予了my_date对象一个名字。直接操作 my_date(y, m, d, w) 时，这种对象称为无名对象。 def __lt__(self, another_date): #编写类的 __lt__ （less than）方法可以让对象能使用<符号。这里的目的是比较两个日期对象。 #a < b 时，实际上是a调用了 __lt__ 方法，可以理解为 __lt__(a, b)。 """定义两个日期的 < 操作符。""" #按年、月、日的顺序依次向下比较 if self.year < another_date.year: return True elif self.year > another_date.year: return False elif self.year == another_date.year: if self.month < another_date.month: return True elif self.month > another_date.month: return False elif self.month == another_date.month: if self.day < another_date.day: return True else: return False def __eq__(self, another_date): # __eq__（equal）则是==符号。仅年月日都相同时两个日期才相等。 """定义两个日期的 == 操作符。""" if (another_date.year == self.year) and (another_date.month == self.month) and (another_date.day == self.day): return True else: return False def __gt__(self, another_date): # __gt__ 即 greater than """定义两个日期的 > 操作符。""" if self.year > another_date.year: return True elif self.year < another_date.year: return False elif self.year == another_date.year: if self.month > another_date.month: return True elif self.month < another_date.month: return False elif self.month == another_date.month: if self.day > another_date.day: return True else: return False def __sub__(self, another_date) -> int: # __sub__ 即减法。日期的减法没有太好的方法，这里就从小的日期开始一天一天加。另外注意正负 #按道理来说，还可以定义my_date与整数的加减法，这里用不到，就不定义了。 """定义两个日期对象的减法，返回两者相差的日期数。""" count = 0 if self == another_date: return count elif self > another_date: new_date1 = another_date.next_date() new_date2 = self.next_date() while (new_date1.year != new_date2.year) or ( new_date1.month != new_date2.month) or (new_date1.day != new_date2.day): new_date1 = new_date1.next_date() count += 1 elif self < another_date: new_date1 = another_date.next_date() new_date2 = self.next_date() while (new_date1.year != new_date2.year) or ( new_date1.month != new_date2.month) or (new_date1.day != new_date2.day): new_date2 = new_date2.next_date() count -= 1 return count def _get_weekday(self) -> int: #用前面定义的减法，和一个标准日期，很容易算出星期几 """从标准日期开始，算出星期几。""" standard = my_date(1970, 1, 1, 4) #1970.1.1是一个比较特殊的日期 count = self - standard w = ((4 + count) % 7 + 7) % 7 #考虑到有可能w为负数（要算的年份早于1970），所以再进行一次 + 7) % 7 if w == 0: w = 7 return w def get_date_tuple(self) -> tuple: """以元组的形式返回一个日期，格式为 (年, 月, 日, 星期) """ return (self.year, self.month, self.day, self.weekday) if __name__ == "__main__": a = my_date(2022, 4, 30) b = my_date(2001, 1, 31) print(a.get_date_tuple()) print(b.get_date_tuple()) print(a - b) print(b - a) print(a == b) print(a > b) print(a < b) 可以把 a = my_date(2022, 4, 30) 和 b = my_date(2001, 1, 31) 自行修改数字进行一些测试。（2）爬虫主体编写爬虫的目的是爬取并存储网页信息。如下：base_url = "http://cffex.com.cn/sj/ccpm/" import random, time, requests, os, logging from requests.adapters import HTTPAdapter from traceback import print_exc if not os.path.isdir(os.path.join(os.getcwd(), 'logs')): os.mkdir(os.path.join(os.getcwd(), 'logs')) #如果没有这个文件夹就创建。这是用到某个文件夹时的常规操作，先检查后使用。 logging.basicConfig(level=logging.INFO, filename=os.path.join( os.getcwd(), 'logs', 'log{}.txt'.format( time.strftime("%Y-%m-%d-%H_%M_%S")))) #os.path.join()用于把两个路径连接起来。由于不同系统的目录分隔符不一样，建议用这个函数。 #os.getcwd()可以返回当前脚本运行的路径。VS Code里，最好 打开文件夹 后再运行脚本，否则os.getcwd()返回的当前路径可能不对，但是生产环境中是没问题的。 headers = { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36' } #headers是请求头，这里稍微伪装了一下使用的浏览器。 class spider(): """爬虫类，根据开始日期、结束日期及种类爬取相应的网址，并保存爬到的网页。""" session = requests.Session() session.mount('http://', HTTPAdapter(max_retries=3)) session.mount('https://', HTTPAdapter(max_retries=3)) #用session对象可以设置重连次数。包括第一次连接，若出现异常，最多尝试连接4次 time_spent_total = 0.0 #爬虫总用时 time_spent_every_page = 0.0 #每个页面平均用时 save_path = os.path.join(os.getcwd(), 'page_sources') #文件保存路径。 #在 __init__ 外面写的数据成员，不实例化对象也能使用，像静态方法，调用格式也是 类名.数据成员名 def __init__(self, start_date: tuple, end_date: tuple, kinds: tuple, spider_start_time: float = time.time()): #这个爬虫要求是输入开始、结束日期，及要爬取的品种，爬取相应的数据 if start_date > end_date: start_date, end_date = end_date, start_date #开始日期要小于结束日期 self.start_date = my_date(start_date[0], start_date[1], start_date[2]) self.end_date = my_date(end_date[0], end_date[1], end_date[2]) self.kinds = kinds self.total_days = self.end_date - self.start_date self.spider_start_time = spider_start_time #为了查看耗时，添加了爬虫的开始时间 if not os.path.isdir(spider.save_path): os.mkdir(spider.save_path) @staticmethod def get_url(date: my_date, kind: str) -> str: """根据单个日期和单个种类构建一个网址。""" global base_url y, m, d, w = date.get_date_tuple() return base_url + "{}{:02}/{:02}/{}.xml?id={}".format( y, m, d, kind, random.randint(1, 99)) #id就用随机数生成。一般网址里这样 ?id=xx 这种格式是浏览器会提交给服务器的数据 #有时候用户名密码这样的数据也能这样传递，但现在更多使用安全性更高的方法 @staticmethod def get_abs_file_path(date: my_date, kind: str) -> str: #这里把生成文件名的规则单独写一个静态方法，是为了之后存入mysql时用相同的规则。 """根据日期，返回保存文件的绝对路径。""" year, month, day, weekday = date.get_date_tuple() file_name = "{}{:02}{:02}{}".format(year, month, day, kind) + ".html" return os.path.join(spider.save_path, file_name) @staticmethod def page_spider(date: my_date, kind: str): #根据输入的单个日期、单个种类爬取对应的一个页面。注意这里是把页面迅速存储成文件，也是为了减轻别人服务器的负担。 #存下来的页面后续再进行处理，存入数据库 """对一个页面进行爬取，并将页面存成文件。""" url = spider.get_url(date, kind) session = spider.session try: xml = session.get(url, timeout=30, headers=headers) #xml是响应对象，除了响应的内容，还可以看响应的状态码等。 except requests.exceptions.TooManyRedirects as e: #如果短时间内访问太多次，这个网页会重定向（引发这个异常），这时休息10min再爬。 message = "网页重定向，目前进度为{}。".format(date.get_date_tuple()) logging.warning(message) print('\r' + message, end='') time.sleep(600) xml = session.get(url, timeout=30, headers=headers) xml.encoding = xml.apparent_encoding #这次爬取的网页是gb2312编码，有时候可能遇到UTF-8编码，所以要加这句。 if r' 网页错误 ' in xml.text: return 404 #有时候比如国庆放假、没有当天的数据，网页就会有一个title标签，内容是 网页错误 。 abs_file_path = spider.get_abs_file_path(date,kind) with open(abs_file_path, "w", encoding=xml.encoding) as f: f.write(xml.text) f.close() def start_spider(self): #爬虫启动的入口。增加了爬取进度的显示，log日志的写入。 """启动爬虫，在爬取过程中显示进度等信息。""" current_date = self.start_date while not current_date > self.end_date: y, m, d, w = current_date.get_date_tuple() if w == 6 or w == 7: #星期六、七直接跳过，因为这两天是休息日，绝对没有数据，金融市场不会调休。 current_date = current_date.next_date() continue for kind in self.kinds: result = self.page_spider(current_date, kind) time.sleep(1) # 一定注意不要爬的太快了 #大家多体谅一下，世界会变成美好的人间。爬的不要太快，每个页面爬完休息1s。 if result == 404: message = "步骤1/2：{}-{:02}-{:02}-{}".format( y, m, d, kind) + " 页面不存在。" logging.info(message) print('\r' + message, end='') #如果遇到非周末的放假日，直接跳到下一个日期 continue spider.time_spent_total = time.time() - self.spider_start_time spider.time_spent_every_page = spider.time_spent_total / ( current_date - self.start_date + 1) time_left = (self.end_date - current_date) * spider.time_spent_every_page message = "步骤1/2：成功爬取" + "{}-{:02}-{:02}-{}".format( y, m, d, kind) + "页面，共用时{:.0f}分钟，预计还需{:.0f}分钟，当前进度{:.2%}。".format( spider.time_spent_total / 60, time_left / 60, (current_date - self.start_date) / self.total_days) logging.info(message) print('\r' + message, end='') #在命令行界面和日志中打印、保存爬取成功的信息。其他地方也可以加logging。 current_date = current_date.next_date() if __name__ == "__main__": try: base_url = "http://cffex.com.cn/sj/ccpm/" # base_url 作为全局变量已经声明过了，这里是方便以后若网址变动、可及时修改。 headers = { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36' } start_date = (2022, 7, 1) end_date = (2022, 7, 30) kinds = ('IF', 'IC', 'IM', 'IH', 'TS', 'TF', 'T', 'IO', 'MO') spider(start_date, end_date, kinds).start_spider() print('\n已完成') input() # input() 会让函数卡在这里，这样运行完后命令行界面不会消失。 input() 本来的作用是让用户输入，返回输入的字符串。 except: print_exc() input() # input() 同理。如果要测试这个爬虫，记得把上面那个日期类最后 if __name__ == "__main__": 及后面的部分删掉，添在爬虫主体代码的前面。一定注意每个页面爬取后休息一下，比如1s，不要占用别人服务器太多带宽，做人留一线，日后好相见。另外，爬取到页面后首先把页面存到本地，以后就不爬了，也是为了减轻别人的服务器负担。（3）mysql存储使用mysql存储，后续查找数据更快。import pymysql from bs4 import BeautifulSoup class MysqlOperationFailed(Exception): """若更改mysql出现错误则抛出这个异常。""" def __init__(self): message = "Failed to change data in MySQL, please check. " super().__init__(message) class mysql_save(): conn = pymysql.connect(host='127.0.0.1', port=53306, user='root', passwd='mysqlpasswd)', db='mysql', charset='utf8') #这里注意要对host，port，passwd进行更改。 cur = conn.cursor() time_spent_total = 0.0 time_spent_every_page = 0.0 def __init__(self, start_date: tuple, end_date: tuple, kinds: list, mysql_save_start_time: float = time.time()): if start_date > end_date: start_date, end_date = end_date, start_date self.start_date = my_date(start_date[0], start_date[1], start_date[2]) self.end_date = my_date(end_date[0], end_date[1], end_date[2]) self.kinds = kinds self.total_days = self.end_date - self.start_date self.mysql_save_start_time = mysql_save_start_time def __del__(self): mysql_save.cur.close() mysql_save.conn.close() @staticmethod def get_bs4_object(date: my_date, kind: str): """根据日期和种类，返回用bs4解析后的BeautifulSoup对象；文件不存在时返回 404 整数。""" abs_file_path = spider.get_abs_file_path(date, kind) if not os.path.exists(abs_file_path): message = '{}文件不存在。'.format(abs_file_path) logging.info(message) return 404 with open(abs_file_path, "rb") as f: #注意这里rb是只读、二进制打开文件。由于是二进制，也不需要指定文件编码。 xml = f.read() f.close() return BeautifulSoup(xml, 'lxml') @staticmethod def analyse_page_source(date: my_date, kind: str): """对单个页面进行解析，得到各种数据，并进行存储。如果文件不存在或文件包含 '网页错误' ，分别返回404、403。""" bs_object = mysql_save.get_bs4_object(date, kind) if bs_object == 404: return 404 if bs_object.find('title', text='网页错误'): abs_file_path = spider.get_abs_file_path(date, kind) message = '在{}文件中发现 "网页错误" ，这可能是爬虫出现了错误，请检查。'.format(abs_file_path) logging.warning(message) print('\n' + message) return 403 for dataitem in bs_object.find_all('data'): # find_all() 方法返回一个列表，其元素为所有符合条件的标签。这里的条件是标签名为data。 instrumentid = dataitem.find('instrumentid').get_text() # get_text() 方法以字符串返回标签的内容。 tradingday = dataitem.find('tradingday').get_text() datatypeid = dataitem.find('datatypeid').get_text() paiming = dataitem.find('rank').get_text() shortname = dataitem.find('shortname').get_text() volume = dataitem.find('volume').get_text() varvolume = dataitem.find('varvolume').get_text() partyid = dataitem.find('partyid').get_text() tradingday = tradingday[0:4] + '-' + tradingday[ 4:6] + '-' + tradingday[6:8] datatypeid = int(datatypeid) paiming = int(paiming) volume = int(volume) varvolume = int(varvolume) partyid = int(partyid) instrumentid = instrumentid.strip() shortname = shortname.strip() #在数据存储的过程中，对数据进行清洗是非常重要的一环。有些时候网站创建者可能更改了一些数据的格式，需要注意。 try: mysql_save.cur.execute( 'INSERT INTO cffex (instrumentid, tradingday, datatypeid, paiming, shortname, volume, varvolume, partyid) VALUES ("%s", "%s", %d, %d, "%s", %d, %d, %d);' % (instrumentid, tradingday, datatypeid, paiming, shortname, volume, varvolume, partyid)) #这里没有检验相同的数据是否存在，这一步可以在数据导入完后进行。注意检验时，对每一条记录，只需要查找比它早的记录里有没有重复的就行了，另外最好用mysql提供的查重来检验。 mysql_save.conn.commit() logging.info('将{}的{}数据写入数据库。'.format(date.get_date_tuple(), kind)) except: mysql_save.conn.rollback() raise MysqlOperationFailed @staticmethod def check_table(): """检查一下mysql中是否已经建好了表，若表不存在则新建表。这里并没有检查：若表存在，表的格式是否正确。""" cur, conn = mysql_save.cur, mysql_save.conn try: cur.execute("CREATE DATABASE IF NOT EXISTS futures;") cur.execute("USE futures;") command = '''CREATE TABLE IF NOT EXISTS cffex ( no INT UNSIGNED NOT NULL AUTO_INCREMENT, instrumentid VARCHAR(7) NOT NULL, tradingday DATE NOT NULL, datatypeid TINYINT NOT NULL, paiming TINYINT NOT NULL, shortname VARCHAR(5) NOT NULL, volume MEDIUMINT NOT NULL, varvolume MEDIUMINT NOT NULL, partyid SMALLINT NOT NULL, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (no));''' #创建表的语句。由于 rank 是mysql里的关键字，所以使用 paiming 代替。 command = command.replace('\n', '') command = command.replace(' ', '') cur.execute(command) conn.commit() except: mysql_save.conn.rollback() raise MysqlOperationFailed def start_mysql_save(self): """入口，启动向mysql写入数据的过程，并且显示进度等信息。""" mysql_save.check_table() print('\n步骤1完成') current_date = self.start_date while not current_date > self.end_date: w = current_date.get_date_tuple()[3] if w == 6 or w == 7: current_date = current_date.next_date() continue for kind in self.kinds: mysql_save.analyse_page_source(current_date, kind) mysql_save.time_spent_total = time.time( ) - self.mysql_save_start_time mysql_save.time_spent_every_page = mysql_save.time_spent_total / ( current_date - self.start_date + 1) time_left = (self.end_date - current_date) * mysql_save.time_spent_every_page message = "步骤2/2：存入数据库中，共用时{:.0f}分钟，预计还需{:.0f}分钟，当前进度{:.2%}。".format( mysql_save.time_spent_total / 60, time_left / 60, (current_date - self.start_date) / self.total_days) logging.info(message) print('\r' + message, end='') current_date = current_date.next_date() mysql_save.check_repeat() #进行一次去重 @staticmethod def check_repeat(): """检查爬到的数据里有没有重复的，若有则只保留最早的一条。""" #检查有无重复，若有重复则删除重复数据、只保留最早的一条。 cur, conn = mysql_save.cur, mysql_save.conn cur.execute('USE futures;') cur.execute( 'SELECT instrumentid,tradingday,datatypeid,partyid,volume FROM cffex GROUP BY instrumentid,tradingday,datatypeid,partyid,volume HAVING COUNT(*)>1;' ) repeated_items = cur.fetchall() if len(repeated_items) == 0: print('\n数据库中无重复数据。') return else: print('\n数据库中检测到重复数据，有{}种。'.format(len(repeated_items))) try: #下面的方法是用python循环去重 '''for repeated_item in repeated_items: instrumentid, tradingday, datatypeid, partyid, volume = repeated_item cur.execute( 'SELECT no FROM cffex WHERE instrumentid="{}" AND tradingday="{}" AND datatypeid={} AND partyid={} AND volume={};' .format(instrumentid, tradingday, datatypeid, partyid, volume)) nos = cur.fetchall() for i in range(1, len(nos)): cur.execute('DELETE FROM cffex WHERE no={};'.format( nos[i][0])) conn.commit()''' #上面的方法是用python循环去重 #下面的方法是一条语句去重 command = '''DELETE FROM cffex WHERE no IN (SELECT no FROM (SELECT no FROM cffex WHERE (instrumentid,tradingday,datatypeid,partyid,volume) IN (SELECT instrumentid,tradingday,datatypeid,partyid,volume FROM cffex GROUP BY instrumentid,tradingday,datatypeid,partyid,volume HAVING COUNT(*)>1) AND no NOT IN (SELECT MIN(no) FROM cffex GROUP BY instrumentid,tradingday,datatypeid,partyid,volume HAVING COUNT(*)>1)) AS a);''' command.replace(' ', '') cur.execute(command) conn.commit() #上面的方法是一条语句去重 except: conn.rollback() raise MysqlOperationFailed if __name__ == "__main__": try: base_url = "http://cffex.com.cn/sj/ccpm/" headers = { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36' } start_date = (2022, 7, 1) end_date = (2022, 7, 30) kinds = ('IF', 'IC', 'IM', 'IH', 'TS', 'TF', 'T', 'IO', 'MO') #spider(start_date, end_date, kinds).start_spider() #如果只需要从本地文件中导入数据到数据库，就把爬虫运行的这行注释掉。 mysql_save(start_date, end_date, kinds).start_mysql_save() print('\n已完成') input() except: print_exc() input()注意把数据库连接中的host，port，passwd改成自己的。如果要测试去重的mysql_save.check_repeat方法，可以运行多次start_mysql_save方法，最后用这个去重的方法实验去重。（4）正式运行去掉注释的爬虫文件（还是注意把数据库连接中的host，port，passwd改成自己的。并且这个文件有一些小修改）：#!/usr/bin/python # -*- coding: UTF-8 -*- class NotValidateDate(Exception): """若日期不合法则抛出这个异常。""" def __init__(self): message = "Your input is not a validate date, please check. " super().__init__(message) class my_date(): """日期类，最主要的功能是给定一个日期，给出下一个日期；以及查看一个日期是星期几。""" def __init__(self, year: int, month: int, day: int, weekday: int = 0): if my_date.validate(year, month, day): self.year, self.month, self.day, self.weekday = year, month, day, weekday if weekday == 0: self.weekday = self._get_weekday() else: raise NotValidateDate @staticmethod def validate(year: int, month: int, day: int) -> bool: """验证给定的日期是否合法。""" if month < 1 or month > 12: return False elif month in (1, 3, 5, 7, 8, 10, 12): if day < 1 or day > 31: return False elif month in (4, 6, 9, 11): if day < 1 or day > 30: return False elif month == 2: if day < 1 or day > 29: return False elif day == 29: if not my_date.is_leap_year(year): return False return True # 1,3,5,7,8,10,12 31 # 4,6,9,11 30 # 2 28/29 @staticmethod def is_leap_year(year: int) -> bool: """判断是否闰年，是则返回True。""" return (year % 4 == 0 and year % 100 != 0) or (year % 400 == 0) def next_date(self): """以my_date对象形式返回下一天。""" y, m, d, w = self.year, self.month, self.day, self.weekday w = (w + 1) % 7 if w == 0: w = 7 d += 1 if d == 32: d = 1 m += 1 if m == 13: m = 1 y += 1 elif d == 31: if m in (4, 6, 9, 11): d = 1 m += 1 elif d == 30 and m == 2: d = 1 m = 3 elif d == 29 and m == 2: if not my_date.is_leap_year(y): d = 1 m = 3 return my_date(y, m, d, w) def __lt__(self, another_date): """定义两个日期的 < 操作符。""" if self.year < another_date.year: return True elif self.year > another_date.year: return False elif self.year == another_date.year: if self.month < another_date.month: return True elif self.month > another_date.month: return False elif self.month == another_date.month: if self.day < another_date.day: return True else: return False def __eq__(self, another_date): """定义两个日期的 == 操作符。""" if (another_date.year == self.year) and (another_date.month == self.month) and (another_date.day == self.day): return True else: return False def __gt__(self, another_date): """定义两个日期的 > 操作符。""" if self.year > another_date.year: return True elif self.year < another_date.year: return False elif self.year == another_date.year: if self.month > another_date.month: return True elif self.month < another_date.month: return False elif self.month == another_date.month: if self.day > another_date.day: return True else: return False def __sub__(self, another_date) -> int: """定义两个日期对象的减法，返回两者相差的日期数。""" count = 0 if self == another_date: return count elif self > another_date: new_date1 = another_date.next_date() new_date2 = self.next_date() while (new_date1.year != new_date2.year) or ( new_date1.month != new_date2.month) or (new_date1.day != new_date2.day): new_date1 = new_date1.next_date() count += 1 elif self < another_date: new_date1 = another_date.next_date() new_date2 = self.next_date() while (new_date1.year != new_date2.year) or ( new_date1.month != new_date2.month) or (new_date1.day != new_date2.day): new_date2 = new_date2.next_date() count -= 1 return count def _get_weekday(self) -> int: """从标准日期开始，算出星期几。""" standard = my_date(1970, 1, 1, 4) count = self - standard w = ((4 + count) % 7 + 7) % 7 if w == 0: w = 7 return w def get_date_tuple(self) -> tuple: """以元组的形式返回一个日期，格式为 (年, 月, 日, 星期) """ return (self.year, self.month, self.day, self.weekday) base_url = "http://cffex.com.cn/sj/ccpm/" import random, time, requests, os, logging from requests.adapters import HTTPAdapter from traceback import print_exc if not os.path.isdir(os.path.join(os.getcwd(), 'logs')): os.mkdir(os.path.join(os.getcwd(), 'logs')) logging.basicConfig(level=logging.INFO, filename=os.path.join( os.getcwd(), 'logs', 'log{}.txt'.format( time.strftime("%Y-%m-%d-%H_%M_%S")))) headers = { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36' } class spider(): """爬虫类，根据开始日期、结束日期及种类爬取相应的网址，并保存爬到的网页。""" session = requests.Session() session.mount('http://', HTTPAdapter(max_retries=3)) session.mount('https://', HTTPAdapter(max_retries=3)) # 设置重连次数。包括第一次连接，若出现异常，最多尝试连接4次 time_spent_total = 0.0 time_spent_every_page = 0.0 save_path = os.path.join(os.getcwd(), 'page_sources') def __init__(self, start_date: tuple, end_date: tuple, kinds: tuple, spider_start_time: float = time.time()): if start_date > end_date: start_date, end_date = end_date, start_date self.start_date = my_date(start_date[0], start_date[1], start_date[2]) self.end_date = my_date(end_date[0], end_date[1], end_date[2]) self.kinds = kinds self.total_days = self.end_date - self.start_date self.spider_start_time = spider_start_time if not os.path.isdir(spider.save_path): os.mkdir(spider.save_path) @staticmethod def get_url(date: my_date, kind: str) -> str: """根据日期和种类构建网址。""" global base_url y, m, d, w = date.get_date_tuple() return base_url + "{}{:02}/{:02}/{}.xml?id={}".format( y, m, d, kind, random.randint(1, 99)) @staticmethod def get_abs_file_path(date: my_date, kind: str) -> str: """根据日期，返回保存文件的绝对路径。""" year, month, day, weekday = date.get_date_tuple() file_name = "{}{:02}{:02}{}".format(year, month, day, kind) + ".html" return os.path.join(spider.save_path, file_name) @staticmethod def page_spider(date: my_date, kind: str): """对一个页面进行爬取，并将页面存成文件。""" url = spider.get_url(date, kind) session = spider.session try: xml = session.get(url, timeout=30, headers=headers) except requests.exceptions.TooManyRedirects as e: message = "网页重定向，目前进度为{}。".format(date.get_date_tuple()) logging.warning(message) print('\r' + message, end='') time.sleep(600) xml = session.get(url, timeout=30, headers=headers) xml.encoding = xml.apparent_encoding if r' 网页错误 ' in xml.text: return 404 abs_file_path = spider.get_abs_file_path(date, kind) with open(abs_file_path, "w", encoding=xml.encoding) as f: f.write(xml.text) f.close() def start_spider(self): """启动爬虫，在爬取过程中显示进度等信息。""" current_date = self.start_date while not current_date > self.end_date: y, m, d, w = current_date.get_date_tuple() if w == 6 or w == 7: current_date = current_date.next_date() continue for kind in self.kinds: result = self.page_spider(current_date, kind) time.sleep(1) # 一定注意不要爬的太快了 if result == 404: message = "步骤1/2：{}-{:02}-{:02}-{}".format( y, m, d, kind) + " 页面不存在。" logging.info(message) print('\r' + message, end='') continue spider.time_spent_total = time.time() - self.spider_start_time spider.time_spent_every_page = spider.time_spent_total / ( current_date - self.start_date + 1) time_left = (self.end_date - current_date) * spider.time_spent_every_page message = "步骤1/2：成功爬取" + "{}-{:02}-{:02}-{}".format( y, m, d, kind) + "页面，共用时{:.0f}分钟，预计还需{:.0f}分钟，当前进度{:.2%}。".format( spider.time_spent_total / 60, time_left / 60, (current_date - self.start_date) / self.total_days) logging.info(message) print('\r' + message, end='') current_date = current_date.next_date() import pymysql from bs4 import BeautifulSoup class MysqlOperationFailed(Exception): """若更改mysql出现错误则抛出这个异常。""" def __init__(self): message = "Failed to change data in MySQL, please check. " super().__init__(message) class mysql_save(): conn = pymysql.connect(host='127.0.0.1', port=53306, user='root', passwd='mysqlpasswd', db='mysql', charset='utf8') cur = conn.cursor() time_spent_total = 0.0 time_spent_every_page = 0.0 def __init__(self, start_date: tuple, end_date: tuple, kinds: list, mysql_save_start_time: float = time.time()): if start_date > end_date: start_date, end_date = end_date, start_date self.start_date = my_date(start_date[0], start_date[1], start_date[2]) self.end_date = my_date(end_date[0], end_date[1], end_date[2]) self.kinds = kinds self.total_days = self.end_date - self.start_date self.mysql_save_start_time = mysql_save_start_time def __del__(self): mysql_save.cur.close() mysql_save.conn.close() @staticmethod def get_bs4_object(date: my_date, kind: str): """根据日期和种类，返回用bs4解析后的BeautifulSoup对象；文件不存在时返回 404 整数。""" abs_file_path = spider.get_abs_file_path(date, kind) if not os.path.exists(abs_file_path): message = '{}文件不存在。'.format(abs_file_path) logging.info(message) return 404 with open(abs_file_path, "rb") as f: xml = f.read() f.close() return BeautifulSoup(xml, 'lxml') @staticmethod def analyse_page_source(date: my_date, kind: str): """对单个页面进行解析，得到各种数据，并进行存储。如果文件不存在或文件包含 '网页错误' ，分别返回404、403。""" bs_object = mysql_save.get_bs4_object(date, kind) if bs_object == 404: return 404 if bs_object.find('title', text='网页错误'): abs_file_path = spider.get_abs_file_path(date, kind) message = '在{}文件中发现 "网页错误" ，这可能是爬虫出现了错误，请检查。'.format(abs_file_path) logging.warning(message) print('\n' + message) return 403 for dataitem in bs_object.find_all('data'): instrumentid = dataitem.find('instrumentid').get_text() tradingday = dataitem.find('tradingday').get_text() datatypeid = dataitem.find('datatypeid').get_text() paiming = dataitem.find('rank').get_text() shortname = dataitem.find('shortname').get_text() volume = dataitem.find('volume').get_text() varvolume = dataitem.find('varvolume').get_text() partyid = dataitem.find('partyid').get_text() tradingday = tradingday[0:4] + '-' + tradingday[ 4:6] + '-' + tradingday[6:8] datatypeid = int(datatypeid) paiming = int(paiming) volume = int(volume) varvolume = int(varvolume) partyid = int(partyid) instrumentid = instrumentid.strip() shortname = shortname.strip() try: mysql_save.cur.execute( 'INSERT INTO cffex (instrumentid, tradingday, datatypeid, paiming, shortname, volume, varvolume, partyid) VALUES ("%s", "%s", %d, %d, "%s", %d, %d, %d);' % (instrumentid, tradingday, datatypeid, paiming, shortname, volume, varvolume, partyid)) mysql_save.conn.commit() logging.info('将{}的{}数据写入数据库。'.format(date.get_date_tuple(), kind)) except: mysql_save.conn.rollback() raise MysqlOperationFailed @staticmethod def check_table(): """检查一下mysql中是否已经建好了表，若表不存在则新建表。这里并没有检查：若表存在，表的格式是否正确。""" cur, conn = mysql_save.cur, mysql_save.conn try: cur.execute("CREATE DATABASE IF NOT EXISTS futures;") cur.execute("USE futures;") command = '''CREATE TABLE IF NOT EXISTS cffex ( no INT UNSIGNED NOT NULL AUTO_INCREMENT, instrumentid VARCHAR(7) NOT NULL, tradingday DATE NOT NULL, datatypeid TINYINT NOT NULL, paiming TINYINT NOT NULL, shortname VARCHAR(5) NOT NULL, volume MEDIUMINT NOT NULL, varvolume MEDIUMINT NOT NULL, partyid SMALLINT NOT NULL, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (no));''' command = command.replace('\n', '') command = command.replace(' ', '') cur.execute(command) conn.commit() except: mysql_save.conn.rollback() raise MysqlOperationFailed def start_mysql_save(self): """入口，启动向mysql写入数据的过程，并且显示进度等信息。""" mysql_save.check_table() print('\n步骤1完成') current_date = self.start_date while not current_date > self.end_date: w = current_date.get_date_tuple()[3] if w == 6 or w == 7: current_date = current_date.next_date() continue for kind in self.kinds: mysql_save.analyse_page_source(current_date, kind) mysql_save.time_spent_total = time.time( ) - self.mysql_save_start_time mysql_save.time_spent_every_page = mysql_save.time_spent_total / ( current_date - self.start_date + 1) time_left = (self.end_date - current_date) * mysql_save.time_spent_every_page message = "步骤2/2：存入数据库中，共用时{:.0f}分钟，预计还需{:.0f}分钟，当前进度{:.2%}。".format( mysql_save.time_spent_total / 60, time_left / 60, (current_date - self.start_date) / self.total_days) logging.info(message) print('\r' + message, end='') current_date = current_date.next_date() mysql_save.check_repeat() @staticmethod def check_repeat(): """检查爬到的数据里有没有重复的。""" cur, conn = mysql_save.cur, mysql_save.conn cur.execute('USE futures;') cur.execute( 'SELECT instrumentid,tradingday,datatypeid,partyid,volume FROM cffex GROUP BY instrumentid,tradingday,datatypeid,partyid,volume HAVING COUNT(*)>1;' ) repeated_items = cur.fetchall() if len(repeated_items) == 0: print('\n数据库中无重复数据。') return else: print('\n数据库中检测到重复数据，有{}种。'.format(len(repeated_items))) try: #下面的方法是用python循环去重 '''for repeated_item in repeated_items: instrumentid, tradingday, datatypeid, partyid, volume = repeated_item cur.execute( 'SELECT no FROM cffex WHERE instrumentid="{}" AND tradingday="{}" AND datatypeid={} AND partyid={} AND volume={};' .format(instrumentid, tradingday, datatypeid, partyid, volume)) nos = cur.fetchall() for i in range(1, len(nos)): cur.execute('DELETE FROM cffex WHERE no={};'.format( nos[i][0])) conn.commit()''' #上面的方法是用python循环去重 #下面的方法是一条语句去重 command = '''DELETE FROM cffex WHERE no IN (SELECT no FROM (SELECT no FROM cffex WHERE (instrumentid,tradingday,datatypeid,partyid,volume) IN (SELECT instrumentid,tradingday,datatypeid,partyid,volume FROM cffex GROUP BY instrumentid,tradingday,datatypeid,partyid,volume HAVING COUNT(*)>1) AND no NOT IN (SELECT MIN(no) FROM cffex GROUP BY instrumentid,tradingday,datatypeid,partyid,volume HAVING COUNT(*)>1)) AS a);''' command.replace(' ', '') cur.execute(command) conn.commit() #上面的方法是一条语句去重 except: conn.rollback() raise MysqlOperationFailed if __name__ == "__main__": try: base_url = "http://cffex.com.cn/sj/ccpm/" headers = { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36' } start_date = (2010, 1, 1) end_date = (2022, 8, 31) kinds = ('IF', 'IC', 'IM', 'IH', 'TS', 'TF', 'T', 'IO', 'MO') spider(start_date, end_date, kinds).start_spider() #这一行是爬取并存储网页 #mysql_save(start_date, end_date, kinds).start_mysql_save() #这一行是解析存储的文件，存入mysql print('\n已完成') except: print_exc()几个品类的开始日期：IC、IH：2015.4.16 TS：2018.8.17 TF：2013.9.6 T：2015.3.20 IF：2010.4.16其中IF是最早的，为了保险一点，就从2010.1.1开始爬，把结束日期改成运行时的日期，放到合适的机器上（由于运行时间较长、光爬取就要大概11h，建议放到功耗小的机器，如笔记本或树莓派上；存入数据库还要1.5h）即可运行，运行之前在mysql里用 DROP DATABASE futures; 来删除测试过程中产生的数据。用爬虫爬取完后，再把 spider(start_date, end_date, kinds).start_spider() 注释掉、 #mysql_save(start_date, end_date, kinds).start_mysql_save() 去掉注释，重新运行一遍即可。最后，如果通过putty连接树莓派、希望在树莓派上后台运行爬虫，可如下操作：cd 脚本路径 chmod +x 脚本文件名 nohup python3 ./脚本文件名 > ./stdout.log 2>&1 & ps -aux | grep "python"最后一条命令执行后，可看到有一个进程为 python3 ./脚本文件名 ，说明脚本已在后台运行。在当前目录下会生成nohup.out文件用于记录输出。同时，这里把两个 input() 删掉了。如果要停止脚本，用 kill -9 进程号PID 命令，进程号PID就是 ps -aux | grep "python" 输出的第二列内容。5.总结与后续（1）这里编写的日期类实际上很多地方都能用到，可以把它放进自己编写的库里。当然这里的日期类还没有进行运行的优化，有些地方可能运行较慢。另外这个日期类还有可以添加的地方，比如添加 >= 、<= ，添加与int类之间的加减法。当然，datetime库也有类似的功能，在VS Code上新建一个py文件、写入 import datetime ，然后按住ctrl、左键点击 datetime 就能阅读其源码，好好学习一下。（2）爬虫爬取时要注意不要对服务器访问太过频繁。一是会占用别人的服务器资源，二是自己的ip可能会被别人ban掉。主要的方法有两个，增加访问间隔，以及把响应内容即时存储。如果实在想要短时间内爬取全站内容，可以使用一台机器异步爬取或多台机器分布式爬取（但这需要搭配ip池，代理服务器），这部分内容将在后续文章中讲解。（3）对mysql里的内容可定期进行查重，笔者在python中写成了mysql_save.check_repeat静态方法，方便调用。
步骤/目录： 1.在windows笔记本上下载并配置redis-server 2.购买阿里云服务器，安装frp并配置frps服务器端 3.配置frpc客户端 4.开启服务并验证 5.附录：mysql+frp （1）修改配置 （2）创建远程连接用户 （3）赋予用户权限并刷新退出 （4）打开端口 （5）修改frpc.ini并开启frp本文首发于个人博客https://lisper517.top/index.php/archives/3/，转载请注明出处。本文实验日期2022年1月31日。采用windows笔记本（跑redis服务器）+阿里云服务器（系统镜像Ubuntu 20.04）1.在windows笔记本上下载并配置redis-serverredis官方不建议在windows下运行redis，所以并未提供windows版redis下载。有一些其他大神更新了windows版redis，以下推荐2个：首先是微软版redis。由微软方提供，于2016年7月1日更新了3.0.504版后停止更新。其次是tporadowski版redis（推荐）。由github用户tporadowski提供，目前最新版本为2021年10月18日更新的Redis 5.0.14 for Windows。（若遇到github网页无法打开，可在gitee上注册并导入仓库后下载）（根据runoob网站，Redis5.0新增了Stream数据结构，所以还是下新版的好）对于tporadowski版redis，下载.zip包，解压到安装目录即可。解压完毕后，打开Redis文件夹，编辑redis.windows.conf文件，需要修改3处：将bind 127.0.0.1行注释掉。这一行是仅允许从本地访问redis；用frp应该不用改，但是笔者需要在局域网中访问，所以还是打开了。 将protected-mode yes改为protected-mode no。老版本的redis可能没有这一行，如果没有则建议重新下载新版redis； 新增一行：requirepass 你的密码。由于redis的处理速度较快，建议密码设置的复杂一些。配置完成后，打开cmd命令行，cd到redis文件夹（也可以将redis文件夹添加到系统路径），输入redis-server redis.windows.conf，正常显示redis服务器端界面，即可。在linux下redis的配置也差不多，不同的是linux下redis的配置文件是redis.conf。2.购买阿里云服务器，安装frp并配置frps服务器端首先介绍一下frp。frp分为frps（server）和frpc（client），其中frps运行在有公网ip的云服务器上，frpc运行在内网中的机器上。frp主要用于把内网机器的端口映射到云服务器端口，比如把内网机器的22端口（一般用于ssh）映射到云服务器的6000端口，那么访问云服务器的6000端口即可接入内网机器的ssh服务。如果有其他方法搞到公网ip，也可以跳过这一步。随便选择一个便宜的服务器，购买后安装镜像，这里选择的系统镜像是Ubuntu 20.04。在左侧 服务器运维-远程连接 中设置一下root账户密码，然后下载putty，通过putty连接：连接类型为SSH，端口为默认的22，主机地址填云服务器的ip地址。输入root和root密码后即登录到云服务器。目前frp最新版本为0.38.0（2021年10月25更新）。这里安装frp到/bin目录下（wget github速度慢可以换成gitee上的仓库）：cd /bin wget https://github.com/fatedier/frp/releases/download/v0.38.0/frp_0.38.0_linux_amd64.tar.gz tar -zxvf frp_0.38.0_linux_amd64.tar.gz mv frp_0.38.0_linux_amd64 frp cd frp nano frps.ini然后配置一下frps.ini：[common] bind_port = 7000 #frpc上也要有相同的token值 token = 你的密码 #配置log log_file = /var/logs/frps.log log_level = info log_max_days = 3 #frps统计板 dashboard_port = 7500 dashboard_user = wuhuqifei dashboard_pwd = wuhuqifei kcp_bind_port = 7000解读一下配置选项：bind_port是frp使用的端口；token是密码令牌；log是日志相关；dashboard是统计板，设置好以后，网页访问 服务器ip:7500 即可看到frps的统计情况，user和pwd分别设置统计板的用户名密码（不用太复杂，简单易记即可）。如果后面运行frps时显示没有frps.log文件，可以自己新建一个并chmod改一下权限（或者直接删掉log相关设置）。最后的kcp_bind_port是多消耗15%左右流量、降低tcp延迟的，不要也可以不加。详细配置可以参考frps_full.ini。配置完成后，需要打开云服务器相应端口。在 安全-防火墙 中新增规则，打开7000和7500端口（云服务器在此处打开，非云服务器则使用ufw、iptables等。云服务器用ufw会出问题）。附带一些github上常见问题：流量是否经过服务器中转？ 目前除了 xtcp 外，其他类型的内网穿透模式的流量都需要经过 frps 所在服务器中转。 网络传输速度慢是什么原因？ 由于流量需要经过服务器转发，所以传输速度的快慢取决于服务器的下行带宽和客户端的上行带宽，通常家用宽带的上行带宽较低，限制了出口的速度。 另外一种情况是服务器部署在国外的 VPS 上，丢包率较高，也会影响到传输速度。这种情况下可以考虑开启 kcp 传输模式。 开启加密和压缩会消耗一定的 cpu 资源，且影响到传输速度，具体影响情况取决于机器性能。 客户端连接失败，提示 authorization failed 出现这种情况说明鉴权失败，检查 frps 和 frpc 的配置文件中的 token 是否一致。 客户端连接失败，提示 authorization timeout 出现这种情况是因为 frps 所在服务器和 frpc 所在服务器的系统时间相差较大。如果不希望在身份校验时加入系统时间，可以将 frps 配置文件中的 authentication_timeout 设置为 0 来解决这个问题。 frpc 能否在系统启动阶段无网络时一直等待而不是直接退出？ 在 frpc 的配置文件中将 login_fail_exit 设置为 false，则 frpc 启动后会不断尝试连接 frps，直到连接成功，而不是直接退出。最后使用./frps -c ./frps.ini即可启动frps。启动后不能关闭putty，这时可以访问7500端口查看是否成功开启。3.配置frpc客户端linux安装frp同上。对于windows，在frp下载页面下载frp_0.38.0_windows_amd64.zip，解压到目标文件夹即可。windows安全中心可能会删掉frps.exe，因为用不到所以无妨。配置一下frpc.ini：[common] server_addr = 云服务器ip地址 server_port = 7000 token = 你的密码 login_fail_exit = false [frp-redis] type = tcp local_port = 6379 local_ip = localhost remote_port = 16380解读frpc.ini：[common]是基础设置，下面的[frp-redis]是一个映射。以后要添加新的映射就在下面加，不用改动[common]，具体可以参考frpc_full.ini。要注意的是，如果有多个内网机器的多个端口需要映射，每个映射名要不同。比如多个redis要开放到外网，就可以写[frp-redis-1]、[frp-redis-2]、[frp-redis-3]等等。然后打开云服务器的16380端口（同前），本地的6379端口不用打开（因为frp穿透时，被映射的本地端口接收的操作都会被frp改成从本地发起）。Ubuntu系统可以安装ufw，然后ufw allow 6379（其他发行版请自行搜索）。另外，windows打开端口，是在 控制面板-Windows防火墙-高级设置-入站规则-新建规则 ，新建一个tcp类型、6379端口的规则，之后有程序使用6379端口时即可使用netstat -na查看所有开放的端口。以上都配置完后，使用frpc -c frpc.ini即可开启服务。这时访问dashboard地址，就能在 Proxies-TCP 中看到名为 frp-redis 的服务，其status为online。4.开启服务并验证验证时需要在云服务器上也安装redis。本例中云服务器的系统镜像是Ubuntu20.04，可以直接用apt下载：apt install redis-server开启frps：使用putty连接至云服务器，输入frp路径\frps -c frp路径\frps.ini开启后勿关闭putty。开启frpc：在windows下，cmd中输入frp路径\frpc -c frp路径\frpc.ini开启后勿关闭cmd窗口。开启redis-server：在windows下，cmd中输入redis路径\redis-server redis路径\redis.windows.conf开启后勿关闭cmd窗口。这时用另一个putty窗口连接到云服务器，输入redis-cli -h 云服务器ip -p 16380 -a redis密码（也可省略-a，在redis命令行中使用auth命令）其中redis密码即requirepass中自己设置的密码。登录上后使用ping命令，服务器端会返回PONG。之后为了方便，可以设置开机自动运行frps、frpc、redis-server。之后即可将redis用于scrapy-redis等其他用途。5.附录：mysql+frp这里假设windows10机器上已安装好mysql。（1）修改配置在mysql安装目录下找到配置文件my.ini，添加一行：bind-address=*允许从其他ip地址访问mysql数据库。用frp应该不用改，但是笔者需要在局域网中访问，所以还是打开了。（2）创建远程连接用户在mysql命令行中输入以下命令，可查看mysql用户及允许登录的地点：select distinct concat('User: ''',user,'''@''',host,''';') as query from mysql.user;如果想将root登录的地点改为任意ip，可使用：RENAME USER 'root'@'localhost' TO 'root'@'%';但允许root用户远程登录mysql比较危险，不如创建一个新用户专门用于远程连接：CREATE USER 'mysql-remote1'@'%' IDENTIFIED BY '你的密码';mysql 8.0后用户认证默认使用sha2，这里将该用户改为用原生密码认证：ALTER USER 'mysql-remote1'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';创建用原生密码认证的用户，只需将上述命令的ALTER改为CREATE。（3）赋予用户权限并刷新退出接着赋予该用户一定的权限。输入下面的命令可以给账号全局权限，包括创建（CREATE）、修改（ALTER）、删除（DROP） 数据库、表、用户，任意表的插入（INSERT）、更新（UPDATE）、删除（DELETE）操作权限。可以使用 SELECT 查询数据，使用 REFERENCES 建立外键关系权限，以及使用 RELOAD 权限执行 FLUSH 操作的权限。当然，你也可以根据自己都需求，对账号权限进行调整。GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'mysql-remote1'@'%' WITH GRANT OPTION;这里我需要能远程修改数据库，所以只赋予了部分权限：GRANT ALTER, INSERT, DELETE, SELECT on *.* TO 'mysql-remote1'@'%' WITH GRANT OPTION;然后刷新权限并退出:FLUSH PRIVILEGES; quit;（4）打开端口mysql默认用3306端口，但是本地端口不用打开。在云服务器上打开frp要用的端口（以16381为例）。（5）修改frpc.ini并开启frp在frpc.ini中添加：[frp-mysql] type = tcp local_port = 3306 local_ip = localhost remote_port = 16381之后重启win10机器的frpc即可。