##### 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;
```