关系型数据库知识点全总结

一、关系型数据库（知道）

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

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

  • 表（table）—> entity / relation

  • 行（row）—> 记录（record） —> tuple

  • 列（column）—> 字段（field） —> attribute

    • 行数 —> 势（cardinality）

    • 列数 —> 度（degree）

    • 数据的取值范围 —> 域（domain）

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

  • 数据定义语言：create / drop / alter / truncate
  • 数据操作语言：insert / delete / update / select
  • 数据控制语言：grant / revoke
  • 事务控制语言：start transaction / commit / rollback

二、DDL

• 创建和删除数据库
  • create database xxx default character set utf8mb4;
  • drop database if exists xxx;
• 创建二维表
  • 语法：create table xxx (…) engine innodb;
  • 数据类型：
    • 获取帮助：? data types; —> ? int;
    • 整数：int (integer) / bigint
    • unsigned - 无符号整数 - 只能表示0和正数
    • zerofill
    • 小数：decimal
      • !!!经验：涉及到钱的业务一律不使用小数，都用整数表示
    • 布尔：boolean
    • 字符串：char / varchar
    • 日期时间：date / time / datetime
    • JSON：json
      • 数组：[1, 2, 3]
      • 对象：{‘name’: ‘Hao’, ‘age’: 43}
  • 约束条件
    • not null —> 非空约束
    • default —> 默认值约束
    • check —> 检查约束
    • primary key —> 主键约束
    • foreign key —> 外键约束
    • unique —> 唯一约束
  • 存储引擎 —> innodb
    • show engines;
    • 外键（维持数据的参照完整性）
    • 事务（把多个操作视为不可分割的原子性操作，要么全做，要么全不做）
    • 行级锁（写操作不需要锁住整个表，只需要锁住对应的行，支持高并发）
  • create table … as select … from …;
  • create table … as select … from … where 1 <> 1;
• ~ 删除和修改表
  • drop table if exists xxx;
  • alter table xxx add column …;
  • alter table xxx drop column …;
  • alter table xxx modify column …;
  • alter table xxx change column …;
  • alter table xxx add constraint …;
  • alter table xxx drop constraint …;
  • alter table xxx rename to …;

三、ER图和表关系

• 实体关系图 —> 概念模型图 —> 物理模型图 —> SQL
  • 矩形框：实体 —> 表
  • 椭圆框：属性 —> 字段
  • 菱形框：关系
    • 一对一：多对一的特例
    • 一对多/多对一：在多的一方添加外键列
    • 多对多：通过中间表将多对多关系转换成两个一对多关系

四、DML

• insert

  • insert into xxx values (…, …, …);
  • insert into xxx (…, …, …) values (…, …, …);
  • insert into xxx (…, …, …) values (…), (…), (…);
  • insert into xxx select … from …;

• delete

  • delete from xxx where key = …;
  • 要点：
    • 删除操作一定要慎重的执行，一定是带条件的删除；
    • 很多产品里面的删除操作，其实都不是真正的删除；

• update

  • update xxx set …, … where key = …;

• select

  • select … as …

    , … as …

    from … join … on …

    where … and … or …

    group by …, …

    with rollup

    having … and … or …

    order by … desc, … asc

    limit … offset …

  • 投影和别名

    • 构造表达式：+ - * / %
    • 分支结构：case … when … then … when … then … else … end
    • 数据处理：
      • 日期函数：curdate / curtime / now / datediff / timestampdiff / adddate
      • 数值函数：round / floor / ceil / abs / sqrt / exp / power / log
      • 字符串函数：char_length / trim / substr / concat / lpad / rpad / format / left / right
      • 其他函数：if / ifnull / nullif / coalesce

  • 谓词：

  • = / <> / > / < / >= / <= / between … and …

  • like / regexp

  • in / not in

  • exists / not exists

  • is null / is not null

  • 数据筛选

    • where —> 分组之前的数据筛选
    • having —> 分组以后的数据筛选

  • 数据透视 - 分组聚合（先将数据分成若干组，在组内使用聚合函数）

    • 根据指定维度拆解数据 —> 解读数据变化的底层逻辑
    • 根据A统计B —> 根据性别统计男女学生人数 / 根据学号统计每个学生的平均成绩
    • 常用聚合函数 —> sum / avg / count / max / min / stddev_pop / var_pop

  • 嵌套查询

    • 把一个查询的结果作为另外一个查询的条件来使用
    • 把一个查询的结果作为表来使用（衍生表） —> 别名

  • 连接查询 —> 如果要查询的字段来自于多张表

    • 交叉连接（cross join） / 笛卡尔积
    • 自然连接（natural join）
    • 内连接（inner join）
    • 外连接（left outer join / right outer join / full outer join）

  • 窗口函数 —> 给数据打上额外的标签 —> 开窗打标

    • 函数() over (partition by … order by … rows between … and …)
    • row_number / rank / dense_rank
    • lag / lead
    • 聚合函数

六、DCL

• 创建和删除用户
  • ​ create user …@… identified by …;
  • ​ drop user …;
• 授予权限
  • ​ grant insert, select on xxx.yyy to …;
  • ​ grant all privileges on xxx.yyy to … with grant option;
• 召回权限
  • ​ revoke insert on xxx.yyy from …;

七、Python程序接入MySQL

• 三方库：

  • mysqlclient —> import MySQLdb
  • pymysql —> import pymysql

• 步骤：

  • 1. 创建连接：connect(host, port, user, password, database, charset) —> Connection
  • 2. 获取游标：Connection --> cursor() —> Cursor
  • 3. 执行SQL：Cursor —> execute() / executemany()（相当于insert的批量插入）
  • 4. 写：Connection —> commit() / rollback()
       读：Cursor —> fetchone() / fetchmany() / fetchall()
  • 5. 关闭连接：Connection --> close()

八、视图 —> 查询的快照

• 语法：
  • create view … as select …;
  • drop view …;
• 作用：
  • 复用SQL查询，预处理数据（运算、格式、标签）
  • 将访问权限控制到指定的列（只给用户查看视图的权限）

九、函数和过程

• 函数：
  • 创建：create function 函数名(…) returns 返回类型 no sql begin … end
  • 调用：select 函数名(…);
• 过程：
  • 创建：create procedure 过程名(…) begin … end
  • 调用：call 过程名(…);

十、执行计划和索引