mysql高级用法常用函数

mysql高级用法

1、自定义排序

select *
from movies
order by field(actors, '成龙', '靳东', '刘亦菲', '范冰冰');
// 字段中存在null值
select *
from movies
order by field
(coalesce(actors,'null'),'成龙', '靳东', '刘亦菲', '范冰冰','null')

2、空值NULL排序（ORDER BY IF(ISNULL))

order by if(isnull(字段),0,1)语法将null值转化为0或1，实现null值数据排序到数据集前面还是后面
// 如果actors为null时，赋值为0，不为null时，赋值为1.
select * from movies order by actors,price desc;
select * from movies order by if(ISNULL(actors),2,1),actors,price desc;

3、CASE表达式（CASE。。。WHEN）

select *,
       case when price > 80 then '高'
       else '低' end as level
       from movies;

4、分组连接函数（GROUP_CONCAT)

分组连接函数可以在分组后指定字段的字符串连接方式，并且还可以指定排序逻辑，连接符默认为英文逗号。也可以加distinct
select actors,group_concat(movie_name),group_concat(price) from movies group by actors;

select actors,group_concat(movie_name order by price desc separator '_')
     ,group_concat(price order by price desc separator '_')
from movies group by actors;

5、分组统计数据后再进行统计汇总（with rollup)

with rollup在分组统计数据的基础上再进行数据统计汇总，即将分组后的数据进行汇总
select actors,sum(price) from movies group by actors with rollup ;

6、子查询提取（WITH AS)

查询中多个子查询需要使用同一个子查询结果
with m1 as(select * from movies where price > 50),
     m2 as(select * from movies where price >= 65)
select * from m1 where m1.id not in(select m2.id from m2) and m1.actors = '刘亦菲'

7、优雅处理数据插入，更新时主键，唯一键重复

更新数据时，有时会遇到主键重复的场景
1、插入数据时使用ignore,作用时插入的值遇到主键或唯一键重复时自动忽略重复的数据，不影响后面数据的插入，有则忽略，无则插入。
insert ignore into movies(id,movie_name,actors,price,release_date)
values (13,'神话','成龙',100,'2022-11-11')

2、使用replace关键字，当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入，即有则删除+插入，无则插入
replace into movies(id,movie_name,actors,price,release_date)
values (13,'神话','成龙',100,'2022-11-11')

3、更新数据时使用on duplicate key update。作用就是当插入的记录遇到主键或者唯一键重复时，会执行后面定义的update操作，相当于先执行insert操作，再根据主键或者唯一键执行update操作，即有则更新，无则插入
insert into movies(id,movie_name,actors,price,release_date)
values (12,'神话','成龙',100,'2022-11-11') on duplicate key update price = price + 10

8、分页查询慢sql优化

1、order by 和 select 字段加联合索引
alter table t_file add index idx_score_date_name(score,release_date,film_name)
select score,release_date,film_name from t_film order by score desc limit 90000,20;
2、order by 字段加索引并手动回表
select score,release_date,film_name from t_film a join 
(select id from t_film order by score desc limit 900000,20) b on a.id = b.id

9、sql索引的分类

1、创建索引
create [unique|fulltext] index index_name on table_name(index_col_name,...)
2、查看索引
show index from table_name;
3、删除索引
drop index index_name on table_name;
例如：
普通索引：
create index idx_user_name on tb_user(name)
唯一索引：
create unique index idx_user_phone on tb_user(phone);
联合索引：
create index idx_user_pro_age_sta on tb_user(profession,age,status);

10、mysql常用函数

1、日期函数

1、Date函数
对于数据库中存在Timestamp类型可以使用DATE()转换
select user_id,DATE(create_at) from test_enum

2、if函数

1、if(expression,v1,v2) 如果表达式expression成立，返回结果v1，否则，返回结构v2
select user_id,
       user_name,
       if(user_sex = '0', '女', if(user_sex = '1', '男', '未知')) as userSex,
       user_status
from test_enum;
2、ifnull(v1,v2),如果v1的值不为null，则返回v1，否则返回v2
select user_id,
       user_name,
       ifnull(user_sex,'男'),
       user_status
from test_enum;
3、isnull(expr) 如果expr的值为null，则返回1，如果expr的值不为null，则返回0，可用于含有null值的排序。
select user_id,
       user_name,
       user_status,
       isnull(user_sex)
from test_enum;
4、nullif(expr1,expr2)比较两个字符串，如果字符串expr1和expr2相等，返回null，否则返回expr1

3、数据类型转换函数CAST()：函数可以将任何类型的值转换为具有指定类型的值,利用该函数可以直接在数据库层处理部分因数据类型引起的问题

支持的类型：
BINARY	二进制型
CHAR	字符型
DATE	日期，格式为 ‘YYYY-MM-DD’
DATETIME	日期加具体的时间，格式为 ‘YYYY-MM-DD HH:MM:SS’
TIME	时间，格式为 ‘HH:MM:SS’
DECIMAL	float 型
SIGNED	int 型
UNSIGNED	无符号int
1、int类型值转为CHAR 字符型
select user_name,cast(user_status as char ) from test_enum;
2、固定时间字符串转为DATE 日期，格式为 'YYYY-MM-DD’
select user_name,cast(user_status as char ),cast(create_at as date) from test_enum;
3、固定时间字符串转为DATETIME 日期加具体的时间，格式为 'YYYY-MM-DD HH:MM:SS’
SELECT CAST('2019-08-29 16:50:21' as DATETIME) as result
4、固定时间字符串转为TIME 时间，格式为 'HH:MM:SS’
SELECT CAST('2019-08-29 16:50:21' as TIME) as result
运行结果：16:50:21
5、float型值通过DECIMAL 获取精度
SELECT CAST(220.23211231 AS DECIMAL(10, 3)) AS result 
运行结果：220.232
6、固定字符串转为SIGNED int 型
SELECT CAST("12321" AS SIGNED  ) AS result 
运行结果：12321
7、固定字符串转为UNSIGNED 无符号int
SELECT CAST("12321" AS UNSIGNED   ) AS result 
运行结果：12321

4、格式化函数FORMAT(x,n)

FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位。这个过程需要进行四舍五入。例如FORMAT(2.356,2)返回的结果将会是2.36；FORMAT(2.353,2)返回的结果将会是2.35。下面使用FORMAT(x,n)函数来讲235.3456和235.3454进行格式化，都保留到小数点后3位。

11、窗口函数