Mysql【知识点总结】

Mysql知识点总结

1. 关系型数据库（知道）
    ~ 理论基础：关系代数（集合论、一阶逻辑、关系运算）
    ~ 具体表象：用二维表组织数据
        - 表（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


2. 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
            
        - 约束条件
            ~ not null ---> 非空约束
            ~ default ---> 默认值约束
            ~ check ---> 检查约束
            ~ primary key ---> 主键约束
            ~ foreign key ---> 外键约束
            ~ unique ---> 唯一约束
        - 存储引擎 ---> innodb
            ~ show engines;
            ~ 外键（维持数据的参照完整性）
            ~ 事务（把多个操作视为不可分割的原子性操作，要么全做，要么全不做）
            ~ 行级锁（写操作不需要锁住整个表，只需要锁住对应的行，支持高并发）
        - create table ... as select ... from ...;（相当于是copy）

	create table tb_emp2 as select * from tb_emp;
	create table tb_emp3 as select * from tb_emp where 1<>1;
	create table tb_emp4 as select * from tb_emp where dno = 20;

        - 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 student  modify  name  char(15)；修改的是name的类型
	
        - alter table xxx change column ...;修改字段名称
	alter   table   student   change  dsex   sex   varchar(10)；修改的是dsex变成sex
        - alter table xxx add constraint ...;添加约束
        - alter table xxx drop constraint ...;删除约束
        - alter table xxx rename to ...;重命名

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

4. DML
    ~ insert
        - insert into xxx values (..., ..., ...);
        - insert into xxx (..., ..., ...) values (..., ..., ....);
        - insert into xxx (..., ..., ...) values (...), (...), (...);插入多数据

        - insert into xxx select ... from ...;

	insert into tb_emp select dname from tb_emp2 where dno = 10;

    ~ 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 )
       

      - 窗口函数 ---> 给数据打上额外的标签 ---> 开窗打标
            函数() over (partition by ... order by ... rows between ... and ...)
            ~ row_number / rank / dense_rank
            ~ lag / lead
            ~ 聚合函数
6. 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 ...;

7. 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()
        - 4. 
            ~ 写：Connection ---> commit() / rollback()
            ~ 读：Cursor ---> fetchone() / fetchmany() / fetchall()
        - 5. 关闭连接：Connection --> close()

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

9. 函数和过程
    ~ 函数：
        - 创建：create function 函数名(...) returns 返回类型 no sql begin ... end
        - 调用：select 函数名(...);
    ~ 过程：
        - 创建：create procedure 过程名(...) begin ... end
        - 调用：call 过程名(...);

10. Leetcode刷题