mysql使用

##### cha04 mysql

```mysql
#1、视图 ：永久存在，除非手动删除
    drop view v_name;
    create or replace view V_name as
        select ...
    #视图只能针对已存在的表建立，只能用来查询，

#2、临时表 ：基于当前连接存在的，连接断开临时表自动消亡
    create temporary table T_name(...);
    #临时表可以任意
    #可以是新数据：insert into ... values ...
    #也可以其他已存在表的数据 insert into ... select ...

#3、行转列(多行转多列)
    # 数值类型列
    select f1,f2,...,sum(if(条件,col,0)),sum(if(...))...
    # 字符串类型列
    select f1,f2,...,group_concat(if(条件,col,NULL)),group_concat(if(...))...

#4、行转列(多行转单列)：正常分组聚合即可

#5、列转行(多列转多行)
    union #去重，排序
    union all #不去重，不排序
    select ... union [all] select ...

#6、单列(以固定分隔符分割的长字符串)转多行
    select f1,...,substring_index(substring_index(field,sep,help_topic_id),sep,-1)
    from table,mysql.help_topic
    where help_topic_id<character_length(field)-character_length(replace(field,sep,''))

# mysql 数据出入                            java     大数据
    导入：sql脚本(source PATH),excel,txt       jdbc        sqoop(jdbc+hdfs)导入 停更
    导出：txt, sql脚本                       jdbc        sqoop
                                                   阿里->数据中台...

# 索引
数据结构：B+Tree / Hash
目的：提高检索的速度，提升并发性能

建索引的最佳时间
    建表时
    建表后
    建表且数据更新完成后 √

索引特点
    非越多越好：更新速度慢，索引会占用表空间容量
    索引不一定起作用：
        什么样的字段适合创建索引
            一般主键，唯一键，外键，join键，where条件键适合创建索引
        什么情况不适合创建索引
            字段值变化小(如：性别)，小表(几百行数据)，超大表：上亿行数据(不推荐使用关系型数据库)
        什么情况下索引失效
            引擎如果认为全表检索比索引开销更小
            ixCol <> 'val'
            ixCol is [not] null
            ixCol like '%val'
            or
            left|right|mid(ixCol)
    如何使用索引
        where|on 
            =
            in
            and
            (A,AB,ABC)

InnoDB(默认)
主键索引(聚族索引)     字段值必须唯一，不可以是null，一张表只能有一个
唯一索引            字段值必须唯一，可以是null，一张表中可以有多个
普通索引            字段值不唯一，且可以是null，一张表中可以有多个

单列索引            单列
组合索引            多列

MyISAM               elasticsearch(倒排索引)
全文索引
    关键词 权重

# show engines \G
# index
    create [unique|fulltext] index IX_NAME on TAB(COL(length)[,COL2,...])
    alter table TAB add [index|unique|fulltext] IX_NAME on(COL(length)[,COL2,...])
    create table TAB(col type[ primary key]|,primary key|unique key|fulltext(col))
    
    show indexes from student_info;
    
    drop index IX_NAME on TAB;
    
    # 建表时指定
    create table index_test(
      id int primary key,
      name varchar(20) unique key
    );

    create table index_test(
       id int,
       name varchar(20),
       primary key(id),
       unique key(name)
    );

    create table index_test(
       id int,
       name varchar(20)
    );

    # 直接创建索引
    create unique index IX_UQ_NAME on index_test(name);
    create index IX_G_ID_NAME on index_test(id,name(20));

    # 修改表添加索引
    alter table index_test add primary key(id);

# mysql 变量
#全局变量
    #初始化
    set @i = 2;
    #计算
    set @i := @i+3;
#局部变量(只能用于方法或存储过程中)

```

##### 函数&存储过程

```mysql
# mysql 语法
    # 默认以分号结束
    # 默认遇到分号自动提交
        select * from tab;
    # 修改默认结束符号
        delimiter $$ //

# 变量
    # 局部变量：只能声明在函数或存储过程中
        declare v TYPE default INIT_V;
    # 全局变量：在单个连接中共享
        set @v = INIT_V;
    # 系统变量：所有连接间共享
        @@IDENTITY @@ERROR
# 变量赋值
    set [@]v = 0;
    select ... into [@]v from ...
    set @v := ...

# 创建函数的语法
    delimiter $$
    create function FUNC_NAME(p TYPE,...) returns TYPE
    begin
        ...
        return V;
    end $$
    delimiter ;
    
    select FUNC_NAME(...);

# 创建存储过程
    delimiter $$
    create procedure PRO_NAME(in|out|inout v TYPE,...)
    begin
        declare continue|exit handler for sqlexception set ...
        ...
        start transaction;
        ... #事务核心代码
        if CONDITION1 then
            commit;
        else
            rollback;
        end if;
        ...
    end $$
    delimiter ;
    
    # 存储过程参数类型介绍
    # in 输入参数，传值进存储过程，传值
    # out 输出参数，从存储过程中带值出来，传引用
    # inout 双向参数，既可以传入值，也可以带出值
    
    #若存储过程中存在输出参数需要提前声明注册
    set @outV1 = INIT_VALUE,...;
    call PRO_NAME(inV1,...,@outV1,...);
    select @outV1,...
    
    #innodb engine support transaction
        # 目的：保证并发操作前后数据的一致性
        # 场景：并发读写
        # ACID：原子性(Atomicity),一致性(Consistency),隔离性(Isolation),持久性(Durability)
            # 事务隔离的级别 Isolation
                # 读未提交 Read Uncommited    脏读，不可重复读，幻读
                # 读已提交 Read Commited    不可重复读，幻读
                # 可重复读 Repeatable Read    幻读(mysql默认)
                    update ... where pk=V;
                # 串行化 Serialiable         皆无
            # 规避：脏读，不可重复读，幻读
            
            #mysql查看当前事务隔离级别
            show variables like 'tx_isolation';
            select @@tx_isolation;
            
    #异常
        declare continue|exit handler for sqlexception set ...

# 锁  hashtable VS CurrentHashMap
    获取锁机制
        1、根据操作数据的语句特征，自动获取锁机制
        2、innodb：
            select 默认无锁 -> 读快，数据的一致性有欠缺
                select ... lock in share mode #手动添加共享锁
                select ... for update #手动添加排它锁
        
    innodb：表锁，行锁
    myisam：表锁
    
    lock
        ...
    unlock;

```