Mysql七种高阶用法，让你少走弯路

科技前沿 • 2025-03-07 19:29 • 阅读 62

大家好，我是讯享网，很高兴认识大家。

首先先让我们创建表,SQL如下

 CREATE TABLE `moives` ( `id` int(11) NOT NULL AUTO_INCREMENT, `movie_name` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL, `actor_name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL, `price` decimal(10,0) DEFAULT NULL, `release_date` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `NewTable_id_IDX` (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

讯享网

1、使用自定义字段排序
a、通过FIELD关键字，定义排序顺序

讯享网SELECT * FROM moives order by FIELD(actor_name,"成龙","刘亦菲","范冰冰","靳东")

b、使用 case when转换为自定义排序

SELECT * FROM moives order by CASE actor_name WHEN "成龙" THEN 1 WHEN "刘亦菲" then 2 when "范冰冰" then 3 else 4 end DESC

c、使用locate instr 两个函数进行排序
mysql的instr函数有着相似的功能，instr(str,sub)返回的是字符串sub在字符串str第一次出现的位置，其中instr(str,sub) = 0 表示字符串str不包含字符串sub。

还能和like一样作为模糊查询

讯享网 SELECT * FROM moives order by LOCATE(actor_name,"成龙,刘亦菲,范冰冰,靳东") SELECT * FROM moives order by INSTR("成龙,刘亦菲,范冰冰,靳东",actor_name)

2、null空值排序

讯享网

SELECT * FROM moives order by IF(ISNULL(actor_name),2,1),price

3、case when表达式语句

讯享网SELECT *, CASE WHEN price>90 THEN "昂贵" WHEN price>80 then "贵" when price >70 then "比较亲民" else "不及格" end level FROM moives

4、分组连接函数 group_concat

SELECT actor_name,GROUP_CONCAT(movie_name),GROUP_CONCAT(price) FROM moives m group by actor_name -- 按照字段排序，使用自定义分隔符 SELECT actor_name,GROUP_CONCAT(movie_name order by price DESC SEPARATOR "-"),GROUP_CONCAT(price order by price DESC SEPARATOR "-") FROM moives m group by actor_name

5、分组后统计汇总 WITH ROLLUP

讯享网 SELECT actor_name,SUM(price) FROM moives m group by actor_name WITH ROLLUP

6、子查询提取 with as

其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明

With m1 AS(SELECT * FROM moives m WHERE price >50), m2 AS (SELECT * FROM moives WHERE price>80) SELECT * FROM m1 WHERE m1.id NOT IN (SELECT m2.id FROM m2) AND m1.actor_name="刘亦菲"

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

讯享网SELECT * FROM moives WHERE id>10 INSERT INTO moives (id, movie_name, actor_name, price, release_date) VALUES(13,"神话11","成龙",100,"2005-12-22"); -- 使用ignore 有则忽略，无则插入 INSERT IGNORE INTO moives (id, movie_name, actor_name, price, release_date) VALUES(13,"神话11","成龙",100,"2005-12-22"); -- 使用replace 有则删除之后插入，无则直接插入 REPLACE INTO moives (id, movie_name, actor_name, price, release_date) VALUES(13,"神话11","成龙",1020,"2005-12-22"); -- on duplicate key update 有就更新，没有就插入 INSERT IGNORE INTO moives (id, movie_name, actor_name, price, release_date) VALUES(13,"神话11","成龙",100,"2005-12-22") on DUPLICATE KEY UPDATE price=price+100

Mysql七种高阶用法，让你少走弯路

相关推荐