本周知识回顾

1. 关系型数据库

   理论基础：关系代数（关系运算、集合论、一阶谓词逻辑）

   具体表象：用二维表组织数据

   • 行（row）：记录（record） —> tuple —> 行数（cardinality）

     主键（primary key）

     外键（foreign key）

   • 列（column）：字段（field） —> attribute —> 列数（degree）

   编程语言：SQL —> Structured Query Language —> 结构化查询语言

   • 数据定义语言：create / drop / alter
   • 数据操作语言：insert / delete / update
   • 数据查询语言：select
   • 数据控制语言：grant / revoke

2. DDL

   创建二维表

   • 语法：create table tb_xxx (…) engine=innodb default charset utf8mb4;
   • 数据类型的选择：
     ? data types —> ? int~ 整数：int (integer) / bigint —> unsigned / primary key —> auto_increment
     int —> -2^31 ~ 2^31-1 / 0 ~ 2^32-1
     bigint —> -2^63 ~ 2^63 - 1 / 0 ~ 2^64-1
     boolean —> tinyint —> 0 / 1~ 小数：float（不推荐） / double（推荐）/ decimal
     经验：涉及到钱的业务一律不使用小数，都用整数表示~ 字符串：char / varchar（推荐使用）/ —> clob（text / longtext）—> 放文件路径或者资源的URL
     字符集 —> utf8mb4 —> 排序规则 —> 默认
     和字符串相关的函数：
     - char_length / length / concat / format / left / right / substring / trim / ucase/ lcase~ 日期时间：year / date / time / datetime / timestamp（不推荐）
     和日期时间相关的函数：
     - adddate / subdate / curdate / now / datediff / year / quarter / month / day / hour / minute / second / weekday

     ~ 二进制：blob （blob / longblob）—> 不推荐这么做！！！

     弱结构：json —> 灵活性

     • JSON对象：{“key1”: “value1”, “key2”: “value2”, …}

     • JSON数组：[elem1, elem2, …]

       json_unquote(json_extract(col_name, ‘$.key1’)) as alias_name

       col_name ->> ‘$.key1’ as alias_name

       elem member of (col_name->’$’)

       json_contains(col_name->’$’, ‘[e1, e2, …]’)

       json_overlaps(col_name->’$’, ‘[e1, e2, …]’)

     MySQL特有的类型：set / enum

   • 约束条件

     not null —> 非空约束

     default —> 默认值约束

     check —> 检查约束

     primary key —> 主键约束

     foreign key —> 外键约束

     unique —> 唯一约束

   • MySQL5.5+ 默认的引擎就是 innodb —> 底层数据结构 —> engine=innodb

     外键

     事务（把多个操作视为不可分割的原子性操作，要么全做，要么全不做）—> transaction —> 一致性

     行级锁（增删改的操作不需要锁住整个表，只需要锁住对应的行，能支持高并发）

   • create table … as select … from …;
   • create table … as select … from … where 1<>1;

   删除和修改表

   • drop table …;
   • alter table … add column …;
   • alter table … drop column …;
   • alter table … modify column …;
   • alter table … change column …;
   • alter table … add constraint …;
   • alter table … drop constraint …;

3. ER图和表关系

   • 实体关系图 —> 设计表

     矩形框：实体 —> 表

     椭圆框：属性 —> 字段

     菱形框：关系

     • 一对一：主键一对一 / 多对一特例（推荐）
     • 一对多/多对一：在多的一方添加外键列
     • 多对多：通过中间表将多对多转换成两个一对多关系

4. DML

   insert

   • insert into … values (…, …, …); —> 不推荐
   • insert into … (…, …, …) values (…, …, …);
   • insert into … (…, …, …) values (…), (…), (…);
   • insert into … select … from …;

   delete

   • delete from … where key=…;

   • 要点：

     删除操作一定要慎重的执行，一定是带条件的删除；

     很多产品里面的删除操作，其实都不是真正的删除，可能是一个更新或者数据转移的操作

     如果配置了bin-log，可以通过日志进行误删除的恢复（可以通过专业工具）

   • 如果要删除全表而且不走日志 —> truncate table …;

   update

   • update … set …, … where key=…;
   • show variables like ‘sql_safe_updates’;
   • select @@sql_safe_updates;
   • set @@sql_safe_updates=on; —> 不能够不带条件更新
   • set @@sql_safe_updates=off; —> 不带条件更新全表
     —> 更新的时候也可以使用 case … when … else … 语法构造分支结构

5.DQL

语法：
select … as …, … as …, … as …, …
—> 窗口函数 over (partition by … order by …) —> 排名 / TopN
—> row_number / rank / dense_rank / lead / first_value / cume_dist
from t1, t2, …
[from t1 … join t2 on …]
where … and … or … —> like / regexp
group by …, … —> 聚合函数（max / min / avg / sum / count / stddev_pop / var_pop）
having … and … or …
order by … desc, … asc
limit … offset … —> MySQL方言

表达式：

• • • / %
      case … when … then … when … then … else … end
      函数：
    • 数值函数 --> abs / ceil / floor / round / exp / log / log2 / log10 / rand / sqrt / power
    • 流程控制 --> if / ifnull
    • 其他 —> md5 / sha1 / aes_encrypt / aes_decrypt / compress / uncompress / uuid

谓词：
= / <> / > / < / >= / <= / between … and …
like / regexp —> % _
in / not in
exists / not exists
is null / is not null

数据筛选

• where —> 分组之前的数据筛选
• having —> 分组以后的数据筛选（这里可以使用分组聚合的结果）

数据透视 —> 分组聚合（先将数据分成若干组，在组内使用聚合函数）
数据按某个维度拆解 —> 定位数据异常到底是什么引起的
根据 A 统计 B —> 根据性别统计男女学生人数 / 根据学号统计每个学生的平均成绩

嵌套查询 —> 把一个查询的结果作为另外一个查询的一部分来使用
派生表：把查询的结果当做表来使用

连接查询 —> 如果要查询的字段来自于多张表
如果没有连接数据的条件 —> 笛卡尔积
内连接
自然连接 —> 外键 / 同名列 —> 笛卡尔积
外连接（左外连接 / 右外连接）

6. DCL

创建用户
create user …@… identified by …;
drop user …;

授权
grant select on xxx.* to …;
grant all privileges on . to … with grant option;

召回权限
revoke select on xxx.* from …;

7. Python程序接入MySQL

三方库：

• mysqlclient —> import MySQLdb
• pymysql —> import pymysql —> pip install pymysql

步骤：

1. 创建连接：connect(host, port, user, password, database, charset) —> Connection
   try-except-else:
2. 获取游标：Connection --> cursor() —> Cursor
3. 发送SQL：Cursor —> execute(’…’, (…)) / executemany()

4. 两种情况：

   增删改：Connection —> commit() / rollback()

   查询：Cursor —> fetchone() / fetchmany(size) / fetchall()
   finally:

5. 关闭连接：Connection --> close()

8. 视图 —> 查询的快照

语法：

• create view … as select …;
• drop view …;

作用：

• 复用SQL语句
• 将访问权限控制到指定的列（只给用户查看视图的权限）

9. 函数和过程

函数：create function 函数名(…) returns 返回类型 no sql …

过程：create procedure 过程名(…) …

区别：过程没有返回值，但是可以使用输出参数获取数据

10. 事务 —> 事务环境 —> 支持原子性操作

定义：把多个DML操作视为不可分割的原子性操作，要么全都做，要么全都不做。

相关操作

• start transaction; —> 开启事务环境
• commit; —> 提交
• rollback; —> 回归

多个并发请求同时访问数据时，就需要通过事务来保证数据访问不出现错误

• 第一类丢失更新
• 第二类丢失更新
• 脏读（读脏数据）
• 不可重复读
• 幻读
  —> 锁（表锁/行锁）—> 事务隔离级别
• 查看事务隔离级别：select @@transaction_isolation;
• 修改事务隔离级别：global / session
  set session transaction isolation level read committed;
  set session transaction isolation level serializable;

ACID特性：

• Atomicity —> 原子性
• Consistency —> 一致性
• Isolation —> 隔离性
• Duration —> 持久性

爬虫知识点回顾

1. HTTP - 超文本传输协议 —> 请求响应式协议

   HTTP请求

   • 请求行：GET / HTTP/1.1
   • 请求头 —> 键值对
   • 空行
   • 消息体 —> 你发给服务器的数据

   HTTP响应

   • 响应行：HTTP/1.1 200 OK
   • 响应头 —> 键值对
   • 空行
   • 消息体 —> 服务器发给你的数据

   响应状态码：

   • 2xx：成功
   • 3xx：重定向

   • 4xx：请求有问题

     400 - 数据错误

     401 - 未授权

     403 - 禁止访问

     404 - 没有资源

     405 - 方法错误

     429 - 请求过于频繁

   • 5xx：服务器出问题

2. 爬虫程序的工作步骤

   抓取页面 —> requests / Session —> get() —> Response

   • status_code：获取响应状态码
   • content：获取响应的二进制数据
   • text：获取响应的文本内容
   • json()：将JSON格式的数据处理成字典

   解析页面

   • 正则表达式 —> 效率高，不需要三方库，但正则表达式可能不太好写 —> re

     match / search / findall / finditer

   • CSS选择器解析 —> 效率较低，需要安装三方库，但书写难度较低 —> beautifulsoup4

     标签选择器 / 属性选择器 / 类选择器 / ID选择器 / 父子选择器 / 后代选择器 / 兄弟选择器 / …

     从浏览器的开发者工具中拷贝 —> CSS Selector

   • XPath解析 —> 效率可以，需要安装三方库，书写难度不大 —> lxml

     从浏览器的开发者工具中拷贝 —> full XPath

   保存数据

   • CSV —> csv
   • Excel —> openpyxl
   • 数据库 —> MySQL —> pymysql / mysqlclient