数据库知识回顾(数据库知识点总结归纳)
本周知识回顾
数据定义语言：create / drop / alter
数据操作语言：insert / delete / update
数据查询语言：select
数据控制语言：grant / revoke
- 行（row）：记录（record） —> tuple —> 行数（cardinality）
主键（primary key）
外键（foreign key）
列（column）：字段（field） —> attribute —> 列数（degree）
- 关系型数据库
理论基础：关系代数（关系运算、集合论、一阶谓词逻辑）
具体表象：用二维表组织数据
编程语言：SQL —> Structured Query Language —> 结构化查询语言
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 …;
- MySQL5.5+ 默认的引擎就是 innodb —> 底层数据结构 —> engine=innodb
外键
事务（把多个操作视为不可分割的原子性操作，要么全做，要么全不做）—> transaction —> 一致性
行级锁（增删改的操作不需要锁住整个表，只需要锁住对应的行，能支持高并发）
- 约束条件
not null —> 非空约束
default —> 默认值约束
check —> 检查约束
primary key —> 主键约束
foreign key —> 外键约束
unique —> 唯一约束
- ~ 二进制：blob （blob / longblob）—> 不推荐这么做！！！
弱结构：json —> 灵活性
MySQL特有的类型：set / enum
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, …]’)
语法：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 / weekdaycreate table … as select … from …;
create table … as select … from … where 1<>1;
- DDL
创建二维表
删除和修改表
ER图和表关系
- 实体关系图 —> 设计表
矩形框：实体 —> 表
椭圆框：属性 —> 字段
菱形框：关系
一对一：主键一对一 / 多对一特例（推荐）
一对多/多对一：在多的一方添加外键列
多对多：通过中间表将多对多转换成两个一对多关系
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 … 语法构造分支结构- 要点：
删除操作一定要慎重的执行，一定是带条件的删除；
很多产品里面的删除操作，其实都不是真正的删除，可能是一个更新或者数据转移的操作
如果配置了bin-log，可以通过日志进行误删除的恢复（可以通过专业工具）
delete from … where key=…;
如果要删除全表而且不走日志 —> truncate table …;
insert into … values (…, …, …); —> 不推荐
insert into … (…, …, …) values (…, …, …);
insert into … (…, …, …) values (…), (…), (…);
insert into … select … from …;
- DML
insert
delete
update
- 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
步骤：
创建连接：connect(host, port, user, password, database, charset) —> Connection
try-except-else:获取游标：Connection --> cursor() —> Cursor
发送SQL：Cursor —> execute(’…’, (…)) / executemany()
- 两种情况：
增删改：Connection —> commit() / rollback()
查询：Cursor —> fetchone() / fetchmany(size) / fetchall()
finally:关闭连接：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 —> 持久性
爬虫知识点回顾
- 4xx：请求有问题
400 - 数据错误
401 - 未授权
403 - 禁止访问
404 - 没有资源
405 - 方法错误
429 - 请求过于频繁
2xx：成功
3xx：重定向
5xx：服务器出问题
响应行：HTTP/1.1 200 OK
响应头 —> 键值对
空行
消息体 —> 服务器发给你的数据
请求行：GET / HTTP/1.1
请求头 —> 键值对
空行
消息体 —> 你发给服务器的数据
- HTTP - 超文本传输协议 —> 请求响应式协议
HTTP请求
HTTP响应
响应状态码：
CSV —> csv
Excel —> openpyxl
数据库 —> MySQL —> pymysql / mysqlclient
- XPath解析 —> 效率可以，需要安装三方库，书写难度不大 —> lxml
从浏览器的开发者工具中拷贝 —> full XPath
- CSS选择器解析 —> 效率较低，需要安装三方库，但书写难度较低 —> beautifulsoup4
标签选择器 / 属性选择器 / 类选择器 / ID选择器 / 父子选择器 / 后代选择器 / 兄弟选择器 / …
从浏览器的开发者工具中拷贝 —> CSS Selector
- 正则表达式 —> 效率高，不需要三方库，但正则表达式可能不太好写 —> re
match / search / findall / finditer
status_code：获取响应状态码
content：获取响应的二进制数据
text：获取响应的文本内容
json()：将JSON格式的数据处理成字典
- 爬虫程序的工作步骤
抓取页面 —> requests / Session —> get() —> Response
解析页面
保存数据
标签：回顾,数据库,知识,alter,table,create,数据,选择器,select
来源： https://blog.csdn.net/weixin_44292782/article/details/121428152