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

最新推荐文章于 2024-01-10 16:09:20 发布

程序猿微刊

最新推荐文章于 2024-01-10 16:09:20 发布

阅读量137

点赞数 1

分类专栏：【MySQL】文章标签： mysql 数据库

本文链接：https://blog.csdn.net/charlesyoosky/article/details/134690697

版权

【MySQL】专栏收录该内容

22 篇文章 4 订阅

订阅专栏

首先先让我们创建表,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