Python-MySQL详解

MySQL数据库

1. 关系型数据库的特征

~ 理论基础 - 关系代数（集合论、一阶逻辑、关系运算）
~ 具体表象 - 用二维表组织数据
    - 表 - 实体- 关系
    - 行 - 记录- 元组
    - 列 - 字段- 属性
    - 主键
    - 外键
    - 行数 - 集合的势）
    - 列数 - 集合的度
    - 取值范围 - 域
~ 编程语言 - 结构化查询语言 - SQL
    - 数据定义语言 - DDL - create / drop / alter / rename / truncate
    - 数据操作语言 - DML - insert / delete / update / select
    - 数据控制语言 - DCL - grant / revoke
    - 事务控制语言 - TCL - start transaction / commit / rollback

2. MySQL命令

~ show - 显示相关信息
    - 显示所有数据库：show databases;
    - 显示所有二维表：show tables;
    - 显示所有字符集：show charset;
    - 显示所有排序规则：show collation;
    - 显示存储引擎：show engines;
~ help - 获取帮助信息
~ system - 调用系统命令
    - 清屏：system clear; / system cls;
    - 查看文件：system ls; / system dir;
~ source - 执行SQL脚本
~ edit - 打开文本编辑器
~ quit - 退出客户端

3. 使用DDL建库建表

    ~ 创建数据库
    create database school default charset utf8mb4 collate utf8mb4_0900_as_cs;

    ~ 删除数据库
    drop database if exists school;

    ~ 切换上下文
    use school;

    ~ 创建二维表
    create table students
    (
        stu_id int not null,
        stu_name varchar(20) not null,
        stu_sex boolean not null default 1,
        stu_birth date not null,
        primary key (stu_id)
    ) engine innodb;

    ~ 显示表结构
    desc students;

    ~ 删除二维表
    drop table if exists students;
    
    ~ 修改二维表
        - 添加列
        alter table students add column stu_tel char(11) not null;

        - 删除列
        alter table students drop column stu_tel;

        - 修改列
        alter table students modify column stu_sex char(1) not null default '男';
        alter table students change column stu_sex stu_gender char(1) not null default '男';

        - 添加约束
        alter table students add constraint ck_students_gender check (stu_gender in ('男', '女'));

        - 删除约束
        alter table students drop constraint ck_students_gender;

        - 修改表名
        alter table students rename to tb_student;

4. 表关系的建立

~ 一对一 - 多对一的特例，可以将任何一方视为多添加外键列，再给外键列加唯一约束
~ 一对多 - 在多的一方添加外键列
~ 多对多 - 创建中间表，将多对多关系变成两个多对一关系

5. 写数据操作

~ 插入数据 - insert
    insert into 表名 (字段1, 字段2, ...) values (值1, 值2, ...);
    insert into 表名 (字段1, 字段2, ...) values (值1, 值2, ...), (值1, 值2, ...);
~ 删除数据 - delete
    delete from 表名 where 条件;
~ 更新数据 - update
    update 表名 set 字段1 = 值1, 字段2 = 值2, ... where 条件;

6. 读数据操作 - 业务提数

~ 投影 - select子句
    - 别名 - as
    - 去重 - distinct
~ 数据来源 - from子句
    - 表连接 - 连接事实表和维度表
        ~ cross   join - 交叉连接 - 笛卡尔积
        ~ inner   join - 内连接 - on
        ~ natural join - 自然连接
        ~ left    join - 左外连接
        ~ right   join - 右外连接
        ~ full    join - 全外连接
~ 分组前筛选 - where子句
    - 比较运算 - =、<>、<、>、<=、>=
    - 逻辑运算 - and、or、not、all、any、in
    - 模糊查询 - like、regexp
    - 空值运算 - is null / is not null
    - 算术运算 - +、-、*、/、%
    - 函数运算 - trim / char_length / substr / timestampdiff
~ 分组 - group by子句 - 给每个组做统计
    - 聚合函数
        ~ sum - 求和
        ~ avg - 算术平均
        ~ max - 最大值
        ~ min - 最小值
        ~ count / count(distinct) - 计数
        ~ variance / var_pop / var_samp - 方差
        ~ stddev / stddev_pop / stddev_samp - 标准差
~ 分组后筛选 - having子句 - 可以写聚合函数
~ 排序 - order by子句
    - 升序 - asc（默认）
    - 降序 - desc
~ 分页 - limit子句 - MySQL方言

7. 窗口函数 - 数据分析函数 - 开窗打标

~ 函数() over ()
~ 函数() over (order by ...)
~ 函数() over (partition by ... order by ...)
~ 函数() over (order by ... rows between ... and ...)
~ 函数() over (order by ... range between ... and ...)
~ 有哪些函数可以在窗口函数语法中使用
    - 聚合函数 - sum() / avg() / count() / max() / min() / stddev() / variance()
    - 排名函数 - rank() / dense_rank() / row_number() / percent_rank()
    - 取数函数 - lag() / lead() / first_value() / last_value() / nth_value()

8. Python程序接入MySQL数据库

~ 创建连接 - pymysql.connect(host, port, user, password, database, charset) - Connection
~ 获取游标 - conn.cursor() - Cursor
~ 执行SQL - cursor.execute() / cursor.executemany() - int
~ 两种情况：
    - 写数据：提交或回滚 - conn.commit() / conn.rollback()
    - 读数据：通过游标抓取 - cursor.fetchone() / cursor.fetchall() / cursor.fetchmany(size)
~ 关闭连接 - conn.close()

9. 视图 - view - 查询的快照 - 命名查询

~ 作用：
    - 实现对数据的预处理 - 数据分析师
    - 隐藏表结构
    - 将访问权限控制到列
~ 限制：
    - 视图可以嵌套 - 基于视图创建视图
    - 视图中的排序会被查询的排序覆盖
    - 视图无法使用索引，不会激发触发器

    ~ 创建或替换视图
    create or replace view view_name as select ...;

    ~ 删除视图
    drop view if exists view_name;

10. 函数

~ MySQL内置函数
    - 聚合函数
        ~ 描述性统计信息 - sum() / avg() / count() / max() / min() / var_pop() / var_samp() / stddev_pop() / stddev_samp()
        ~ 其他 - group_concat() / json_arrayagg() / json_objectagg()
    - 数值函数
        ~ 绝对值和符号函数 - abs() / sign()
        ~ 取整和四舍五入 - ceiling() / floor() / round() / truncate()
        ~ 三角和反三角函数 - sin() / cos() / tan() / cot() / asin() / acos() / atan()
        ~ 指数和对数 - pow() / exp() / log() / log10() / log2()
        ~ 随机数 - rand()
        ~ 弧度和角度 - degrees() / radians()
        ~ 平方根 - sqrt()
    - 字符串函数
        ~ 长度 - char_length() / length()
        ~ 拼接 - concat()
        ~ 大小写 - lower() / upper()
        ~ 取子串 - left() / right() / substring()
        ~ 替换 - replace()
        ~ 修剪 - trim() / ltrim() / rtrim()
        ~ 定位 - instr() / locate()
        ~ 填充 - lpad() / rpad()
        ~ BASE64编解码 - from_base64() / to_base64()
        ~ 反转 - reverse()
    - 日期时间函数
        ~ 获取日期和时间 - current_timestamp() / current_date() / current_time()
        ~ 获取部分信息 - date() / time() / year() / quarter() / month() / day() / weekday() / hour() / minute() / second()
        ~ 计算时间差 - datediff() / timestampdiff()
        ~ 添加间隔 - date_add() / date_sub()
        ~ 格式化日期时间 - date_format()
    - 流程控制函数
        ~ if() / ifnull() / nullif()
    - 窗口函数
        ~ 排序 - rank() / dense_rank() / row_number() / percent_rank()
        ~ 取数 - lag() / lead() / first_value() / last_value() / nth_value()
    - 其他函数
        ~ 类型转换 - cast() / convert()
        ~ 信息获取 - current_role() / current_user() / database() / last_insert_id() / version()
        ~ 全局唯一标识符 - uuid() / uuid_to_bin() / bin_to_uuid()