数据库知识回顾

2022-11-25 18:31:09

本周知识回顾

关系型数据库
理论基础：关系代数（关系运算、集合论、一阶谓词逻辑）
具体表象：用二维表组织数据
- 行（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
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 …;
ER图和表关系
- 实体关系图 —> 设计表
  矩形框：实体 —> 表
  
  椭圆框：属性 —> 字段
  菱形框：关系
  - 一对一：主键一对一 / 多对一特例（推荐）
  - 一对多/多对一：在多的一方添加外键列
  - 多对多：通过中间表将多对多转换成两个一对多关系
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

步骤：

创建连接：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操作视为不可分割的原子性操作，要么全都做，要么全都不做。

码农公寓

本周知识回顾

相关文章