mysql基础知识小记

数据库查询操作

1、简单查询

select ..
from ...
where ...
group by ...
having ...
order by ...asc|desc
limit (pageNo-1)*pageSize,pageSize

-- 分页查询
select ... limit (pageNo-1)*pageSize,pageSize;

#聚合函数：avg	min	max	count	sum
#分组查询可以出现在字段列表中的字段：参与分组的字段和聚合函数

2、复杂查询

子查询

select
			可以出现子查询
from
			可以出现子查询
where
			可以出现子查询	但在in/call/some/any子查询中不能使用limit
group by
			不可以
having
			可以出现子查询
order by
			不可以
			
-- 【子查询】中不能出现limit关键字，如果需要将子查询封装成视图

合并查询

select
			FIELDS...
from
			TABLE1,...,TABLEN
...

连接查询

1、内连接

select ..
from table A
inner join table B
on A.fk = B.pk

2、外连接

select ...
from table A
left|right join table B
on A.fk = B.pk

视图 view

#本质：就是一条复杂的SELECT命令
-- 创建视图
   create view VIEW_NAME
   as select ...
-- 删除视图
   drop view if exists VIEW_NAME;

临时表

#临时表专属于当前连接
#可以在连接中删除临时表
#或者等连接断开时mysql会自动删除临时表
#在同一个存储过程中临时表只能OPEN一次

#删除临时表
drop temporary table TABLENAME;

#创建临时表
create temporary table TABLENAME
(SELECT...)

默认提交符

-- mysql中默认提交符：
	-- 修改提交符号
	delimiter//
	-- 定义基于当前连接的会话(全局)变量并初始化
	set @VAR=VALUE;
	set @VAR:=VALUE;
	-- 自增
	SET @VAR:=@VAR+N;
	-- 定义局部变量（函数参数，存储过程的输入参数）
	declare VAR_NAME DATA_TYPE[(len)] [default VAL];

列转行

#######列转行：聚合
	select 
		group_field,...,		-- 分组字段
		group_concat(FIELD/concat(FIELD,'SEP',...)) alias		-- 分组聚合
	from ...
	where...
	group by ...
	having ...
	
-- 班上同学的方法
SELECT 
substring_index(substring_index('a,b,c,d,e',',',help_topic_id+1),',',-1) num 
FROM 
mysql.help_topic 
WHERE 
help_topic_id < length('a,b,c,d,e')-length(replace('a,b,c,d,e',',',''))+1;

+-----+
| num |
+-----+
| a   |
| b   |
| c   |
| d   |
| e   |
+-----+

行转列

########行转列：将一列拆成多行
1，张三，游泳_下棋_美女
	列转行
1，张三，游泳
1，张三，下棋
1，张三，美女
select ... from tabA
union all
select ... from tabA
...

高阶函数

数学函数

mod(x,y)
ceil(float)
floor(float)
round(float[,n])

字符串函数

length(varchar)			返回字符串的字节长度
char_length(varchar)	返回字符串的长度
concat(str1,str2,...)	合并字符串
concat_ws(joinStr,str1,str2,...)	连接字符串
lower(str1)/upper(str1)
left(str1,n)/right(str1,n)/mid(str1,startPos,n)
instr(str1,deststr)		返回deststr在str1中的位置（从1开始），不存在返回0
reverse(str1)			字符串反转

日期函数

now()/curdate()
year(date)/month(date)/day(date)/dayofweek(date)/quarter(date)
datediff(bigDate,smallDate)

-- 以date为基础，增加或减少指定unit(单位)指定num(数量)之后的日期
date_add(date,interval ±num unit) -- unti写day/month/quarter/year

条件函数

if(condition,v1,v2)
ifnull(v1,v2)		<=> if(v1=null,v2,v1)
case expr when val1 then v1 ... else vn end

系统函数

version()
connection_id()
user()

加密管理

password(field/str)
md5(concat('prefix',field/str,'suffix'))
encode(dest,pass)
decode(crypt,pass)

数据类型转换

cast(field/val as type)
TYPE类型受限：
signed/unsigned integer
decimal

concat

#行内拼接：拼接字符串
concat(F1,...,FN)

#行内拼接：指定分隔符拼接
concat_ws(sep,F1,...,FN)

collect

#分组拼接：分组收集函数
	-- 将该列所有行收集成一个集合
	collect_list(FIELD)
	-- 将该列所有行收集成一个去重后的集合
	collect_SET(FIELD)

group_concat

#分组拼接：组内拼接字符串，支持组内排序
group_concat(FIELD[ order by FIELD ASC/DESC]) -- 默认分隔符为‘,’
group_concat(FIELD separator '_') -- 指定分隔符

substring_index

#截取字符串
-- delimiter 分隔符
-- count 前几个分隔符
	-- count >0 前几个分隔符
	-- count <0 后几个分隔符
substring_index(string,delimiter,count)
-- 提取第n个
substring_index(substring_index(string,delimiter,n),delimiter,-1)

find_in_set

#集合包含元素
find_in_set(FIELD/CONST,'V1,V2,... ')
-- 定位子字符串在父字符串中的位置
-- 从startPos(inclusive)开始在string中找到substr首次出现的位置
	-- 如果substr==null || string==null  return null
	-- 如果substr not in string return 0
	-- 否则 返回1~char_length(string)

locate

locate(substr,string[,startPos])
-- 返回substr在string中第一次出现的首字符的位置

instr

instr(string,substr)
instr（’源字符串’ , ‘目标字符串’ ,’开始位置’,’第几次出现’）
select instr(‘abcd’,’a’,1,1) from dual; —1
select instr(‘abcd’,’c’,1,1) from dual; —3
select instr(‘abcd’,’e’,1,1) from dual; —0

函数与事务（存储过程）

创建函数

create function func_name(param_name DARA)
delimiter//
create procedure pro_name(
    	#输入参数
        in inParamName TYPE,
        in ...,
        #输出参数
        out outParamName TYPE,
        out ...,
        # 输入输出参数
        inout ioParamName TYPE
        inout ...
    )
begin
		...
end;
delimiter;
	
####################例子#######################
delimiter//
create procedure pro_name()
begin
	select 123 num;
	select * from v_top3_score;
	select * from v_top3_salary;
end;
delimiter;
###############################################

函数基本语法

create function substr_non(listStr varchar(200),n int)
returns carchar(200)
return substring_index(substring_index(listrStr,',',n),',',-1)

存储过程（事务）

-- 查看存储过程
show procedure status [like '%NAME']; -- 列表
		
-- 调用存储过程
call pro_name;
-- 存储过程中的视图都会显示
-- 删除存储过程
drop procedure pro_name;

#存储过程特点：
	-- 多输出：输出参数
	-- 查询操作
	-- 嵌套事务
transaction(ACID)
	-- 在RDBMS中确保数据一致性的手段
	-- 特征
		-- A 原子性：不可拆分
		-- C 一致性：事务执行前后数据一致
		-- I 隔离性：事务之间互相独立
		-- D 持久性：rollback/commit
	-- 应用
		set autocommit=0; #开启事务
		...
		rollback;(回滚) / commit;(提交)
		
###############存储过程#################
create procedure proname()
begin
	[start transaction;]
	...
	[commit/rollback;]
end;
#######################################

锁(lock)

• 锁规模

  行锁

  表锁

• 锁类型

  ​ – 共享锁：读

  ​ – MySQL中读默认情况不加锁：快照读snapshot

  ​ – select … lock in share mode; 添加共享锁

  ​ – select … for update; 添加排它锁

  ​ – 独占锁：写

事务+锁

set autocommit=0;
  
select ...;      					快照查询，无锁
select ... lock in share mode;    	共享锁，在非修改模式下互不影响
select ... for update;				排它锁，只要有其他的共享或排它锁未释放就无法执行
  
insert/delete/update ...			行级排他锁
  
commit/rollback;

索引

what

#功能：索引相对于数据，好比目录相对于字典
#目的：快查
#本质：一种数据结构
	-- BTree：平衡树
#优劣
	-- 优点：寻址快
	-- 缺点：
		1、占用表空间
		2、维系增删操作后索引树的平衡开销大
		3、查询时索引不一定生效

分类

1、聚簇索引：唯一决定数据物理存储顺序的索引，相当于拼音查字目录
	primary key -- 自动添加聚簇索引
2、唯一索引：辅助索引，相当于偏旁部首查字目录
	unique key -- 自动添加唯一索引
3、全文索引：ElasticSearch 搜索引擎 => 倒排索引
4、普通索引
	-- 单列索引
	-- 组合索引

why

-- 适合创建索引的列类型
	1、频繁作为检索条件的列
	2、数据变化比较大的数据列
	3、有一定的数据规模
-- 什么样的字段适合建索引
	1、主键、唯一键、连表字段(通常为外键)
	2、where ... 非通配符开始的like					a like 'henry%'
	3、min、max
	4、group by、order by
-- 索引何时会失效
	1、多个索引字段作为条件，尤其是组合索引，不能使用 or
	2、字符串索引列作为条件，模糊查询时，左侧不能出现 %
	3、对索引列的值进行修改后(数值类型的列进行数学计算，对列进行函数调用)
	4、联结查询，主外键字段编码格式不一致
	5、逻辑计划优化阶段，评测结果为全表检索更优
	6、多表联查，引擎优化后的物理计划主表
	7、组合索引(a,b,c),条件组合方式(a,ab,abc)
	8、where FIELD is Null

how

-- 创建索引
create [primary/unique/fulltext/...] index IX_TABLE_FIELDS on TABLE_NAME(FIELD[,...]);

###################例子#####################
show index from TABLENAME;
create [unique] index INXEXNAME on TABLENAME(FIELDS(length:string)...);
drop index INDEXNAME on TABLENAME;

alter table TABLENAME add constraint foreign key FK_CONSTRAINT_NAME FKFIELDNAME references TABLENAME(PKFIELDNAME);
alter table TABLENAME drop constraint FK_CONSTRAINT_NAME;
############################################