MySQL基本函数、高阶函数、高级特性

---

前言

本文章主要记录MySQL常用的基础函数和高阶函数，顺带介绍一下存储过程、事务、索引等数据库高级特性

---

一、基础函数

1.聚合函数

-- 求和
​select sum() 			
-- 求平均值
select avg() 		 
-- 最大值	
select max() 	 
-- 最小值
select min() 		
-- 计数	
select count() 	

2.数学函数

-- 绝对值
select abs(-123)  => 123
-- 平方根
select sqrt(4)	  => 2
-- 取余
select mod(5/3)   => 2
-- 向上取整
select ceil(1.001) => 2
-- 向下取整
select floor(1.999) => 1
-- 四舍五入
select round(1.345) => 1     -- 默认保留整数
select round(1.345,2) =>1.35 -- 2为精度
-- 返回参数的符号
select sign(-3) => -1  -- 表示负数
select sign(3)	=> 1   -- 表示正数
-- 幂运算
select pow(3,2) => 9
-- 生成随机0~1的随机数
select rand()  => 0.4523423

3.字符串函数

-- 字节长度
select length('a我c') => 5  -- utf8mb4 3字节长度
-- 字符长度
select character_length('a我c') => 3 
-- 拼接字符串
select concat('我','要','火') => 我要火
-- 位置替换
select insert('FootBall'，1,4，'Basket') => BasketBall  -- 1,4为位置，从1开始
-- 内容替换
select replace('123abc123','123','456') => '456abc456'
-- 字符串截取 
select left('henrychen',5)  =>henry 5表示数量
select right('henrychen',4) =>chen  4表示数量
select mid('henrychen',6,4) =>chen  6表示下标，4表示数量
-- 大小写转换
select lower('ABC') =>  abc
select upper('abc') =>  ABC
-- 去两端空格
select trim('  abc  ') => abc
-- 字符串反转
select reverse=('abc') => cba

4.日期函数

-- 创建日期
select date('2021-1-1')  =>2021-1-1
-- 获取系统当前时间
select now();  => 2021-05-12 19:12:21
-- 获取系统当前日期
select curdate();      => 2021-05-12
select current_date(); => 2021-05-12
-- 获取时间
select current_time(); => 19:12:21
-- 获取当前系统时间戳
select unix_timestamp();  => 16374857
-- 格式化时间戳
select from_unixtime(16374857); => 19:12:21
-- 年
select year(now()); 	=>2021
-- 季度
select quarter(now()); => 2
-- 月
select month(now());	=>5
-- 年周
select weekofyear(now());	=>19
select week(now());
-- 年日
select dayofyear(now())	=>136
select day(now()) =>136
-- 月日
select dayofmonth(now()) => 13
-- 周几
select weekday(date('2021-5-9'))	=>6   周一 ~ 周日 => 0~6
select dayofweek(date('2021-5-9'))	=>1   周七 ~ 周六 => 1~7
select dayname(date('2021-5-9'))	=>  Sunday
-- 算两个时间差(timeType:时间单位，结果是time2-time1的结果)
select  timestampdiff(timeType，time1，time2)
select  timestampdiff(hour, '2021-9-5 14:00:00', '2021-9-3 1:00:00') time
+------+
| time |
+------+
|  -61 |
+------+

5.流程控制函数

-- if(LOGIC_EXPRESSION,VALUE1,VALUE2)函数
select if (true,0,1);   =>0
-- 空值判断
select ifnull(NULL,1)   =>1
select ifnull(2,1)      =>2
-- 多分支
select (case V when 1 then V1 when 2 then V2 else V3 end) ALIAS;  
select(case when V<=1 then V1 when v<=10 then V2 else V3 end) ALIAS; 

二、高阶函数(持续更新…)

1.拼接

-- 行内拼接：拼接字符串
concat(F1,...,FN)
-- 行内拼接：指定分隔符
concat_ws(sep,F1,...FN)
-- 分组拼接：组内拼接字符串
group_concat(FIELD)
group_concat(FIELD separator '_')

2.补充…

DATE_ADD(date,INTERVAL number type),同 ADDDATE()
SELECT DATE_ADD(“2015-12-31 23:59:59”,INTERVAL 1 SECOND)
SELECT DATE_ADD(“2015-12-31 23:59:59”,INTERVAL 1 DAY)
SELECT DATE_ADD(“2015-12-31 23:59:59”,INTERVAL “1:1” MINUTE_SECOND)
SELECT DATE_ADD(“2016-01-01 00:00:00”,INTERVAL “-1 10” DAY_HOUR)
-> 2016-01-01 00:00:00
-> 2016-01-01 23:59:59
-> 2016-01-01 00:01:00
-> 2015-12-30 14:00:00

DATE_SUB(date,INTERVAL number type),同 SUBDATE()
-- 用法和DATE_ADD()与ADDDATE()类似

DATE_FORMAT(date,format)：根据参数对date进行格式化。
SELECT DATE_FORMAT(‘2016-01-16 22:23:00’,’%W %M %Y’)
SELECT DATE_FORMAT(‘2016-01-16 22:23:00’,’%D %y %a %d %m %b %j’)
SELECT DATE_FORMAT(‘2016-01-16 22:23:00’,’%H %k %I %r %T %S %w’)
SELECT DATE_FORMAT(‘2016-01-16 22:23:00’,’%Y-%m-%d %H:%i:%s’)
-> Saturday January 2016
-> 16th 16 Sat 16 01 Jan 016
-> 22 22 10 10:23:00 PM 22:23:00 00 6
-> 2016-01-16 22:23:00

-- %M 月名字(January……December) 
-- %W 星期名字(Sunday……Saturday) 
-- %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 
-- %Y 年, 数字, 4 位 
-- %y 年, 数字, 2 位 
-- %a 缩写的星期名字(Sun……Sat) 
-- %d 月份中的天数, 数字(00……31) 
-- %e 月份中的天数, 数字(0……31) 
-- %m 月, 数字(01……12) 
-- %c 月, 数字(1……12) 
-- %b 缩写的月份名字(Jan……Dec) 
-- %j 一年中的天数(001……366) 
-- %H 小时(00……23) 
-- %k 小时(0……23) 
-- %h 小时(01……12) 
-- %I 小时(01……12) 
-- %l 小时(1……12) 
-- %i 分钟, 数字(00……59) 
-- %r 时间,12 小时(hh:mm:ss [AP]M) 
-- %T 时间,24 小时(hh:mm:ss) 
-- %S 秒(00……59) 
-- %s 秒(00……59) 
-- %p AM或PM 
-- %w 一个星期中的天数(0=Sunday ……6=Saturday) 
-- %U 星期(0……52), 这里星期天是星期的第一天 
-- %u 星期(0……52), 这里星期一是星期的第一天 
-- %% 字符% 

ADDTIME(time,times)：把times加到time上
SELECT ADDTIME(“2015-12-31 23:59:59”,’01:01:01’)
               
LAST_DAY(date )：获取date当月最后一天的日期
SELECT LAST_DAY(SYSDATE())
SELECT LAST_DAY(‘2015-02-02’)
SELECT LAST_DAY(‘2015-02-02 00:22:33’)
-> 2016-01-31
-> 2015-02-28
-> 2015-02-28
               
DATEDIFF() 函数返回两个日期之间的天数。
SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate
-> 1

TIMESTAMPDIFF(unit,begin,end)
select TIMESTAMPDIFF(day,'2021-12-12 12:12:12','2021-12-15 10:10:10')time;    
-> 2       

-- 定位子字符串在父字符串中的位置
-- 从startPos(inclusive)开始在string中找到substr首次出现的位置
	-- 如果substr==null || string -- null  return null
	-- 如果substr not in string return 0
	-- 否则 返回1~
locate(substr,string[,startPos])

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

FIND_IN_SET(str,strlist)
-- 前一个字符串 一定要在后一个字符串集合中才返回大于0的数
select FIND_IN_SET('2','1,2,3,2,4')
->2

select FIND_IN_SET('6','1,2,3,2,4')
->0

-- 使用find_in_set函数一次返回多条记录
select * from classinfo where FIND_IN_SET(classId,'1,2,3,4,5');

+---------+-----------+-------------+------------+------------+
| classId | className | fkProductId | beginDate  | endDate    |
+---------+-----------+-------------+------------+------------+
|       1 | KB11      |           1 | 2020-08-13 | 2021-04-08 |
|       2 | KB12      |           1 | 2021-03-18 | 9999-09-09 |
|       3 | KB13      |           1 | 2021-04-28 | 9999-09-09 |
|       4 | KY08      |           2 | 2020-05-08 | 2020-11-22 |
|       5 | KY09      |           2 | 2020-06-02 | 2020-12-18 |
+---------+-----------+-------------+------------+------------+

三、存储过程

1.存储过程

procedure
	delimiter //
	create procedure pro_name(
    	in paramName TYPE,
        in ...,
        out outParamName TYPE,
        out ...,
        inout ioParamName TYPE
        inout...
    )
    begin
    	...
    end;
    delimiter;
    -- 特点
    	-- 多输出：输出参数
    	-- 查询操作
    	-- 嵌套事务

四、事务

1.事务概念

transaction(ACID)
	-- 在RDBMS中确保数据一致性的手段
	-- 特征
		-- A 原子性: 不可拆分
		-- C 一致性: 事务执行前后数据一致
		-- I 隔离性: 事物之间相互独立
		-- D 持久性: rollback/commit
	-- 应用
		set autocommit=0;
		...
		rollback/commit;

五、锁

1.锁分类

lock
	-- 锁规模
        -- 行锁	
        -- 表锁	
	-- 锁类型	
		-- 共享锁: 读	
			-- mysql中读默认不加锁:快照读 snapshot	
			-- select...lock in share mode; 添加共享锁	
			-- select...for update;添加排它锁	
		-- 独占锁: 写	
	

六、索引

1.索引分类

-- 索引
	-- 功能: 索引相对于数据，好比目录相对于字典
	-- 目的: 快查
	-- 本质: 一种数据结构:  BTree : 平衡树
	-- 优劣
		-- 优点: 寻址快
		-- 缺点: 
			-- 占用表空间
			-- 维系增删操作后索引树的平衡开销大
			-- 查询时索引不一定生效
	-- 分类
		-- 聚簇索引: 唯一决定数据物理存储顺序的索引
		-- 唯一索引: 辅助索引，相当于偏旁部首查字目录
		-- 全文索引: ElasticSearch：搜索引擎 => 倒排索引
		-- 普通索引
			-- 单列索引
			-- 组合索引
	-- 适合创建索引的列类型
		-- 频繁作为检索条件的列
		-- 数据变化比较大的数据列
	-- 索引何时会失效
		-- 多个索引字段作为条件，尤其是组合索引，不能使用or
		-- 字符串索引作为条件，模糊查询时，左侧不能出现 %
		-- 对索引列的值进行修改后(数值类型的列进行数学计算，对列进行函数调用)
		-- 联结查询，主外键字段编码格式不一样
		-- 逻辑计划优化阶段，评测结果为全表检索更优
		-- 多表联查，引擎优化后的物理计划主表
		-- 组合索引(a,b,c),条件组合方式非(a,ab,abc)
		-- where FILED is null
	-- 创建索引
		-- create [primary/unique/fulltext..] index IX_TABLE_FIELDS on TABLE_NAME(FIELD[,...])

总结

以上就是今天要讲的内容，本文简单记录了MySQL部分函数以及部分高级特性的概念，笔记会不断更新遇到的新的函数以及扩展的高级特性使用的工作笔记。