7种SQL 进阶用法，你一定能用到

最新推荐文章于 2024-04-24 08:30:00 发布

XL_Leon

最新推荐文章于 2024-04-24 08:30:00 发布

阅读量190

点赞数 1

文章标签： sql 数据库

本文链接：https://blog.csdn.net/u013225431/article/details/134229729

版权

1.自定义排序（ORDER BY FIELD）

在MySQL中ORDER BY排序除了可以用ASC和DESC之外，还可以使用自定义排序方式来实现。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for movies
-- ----------------------------
DROP TABLE IF EXISTS `movies`;
CREATE TABLE `movies`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `movie_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `actors` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `price` decimal(10, 2) NULL DEFAULT NULL,
  `release_date` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of movies
-- ----------------------------
INSERT INTO `movies` VALUES (1, '咱们结婚吧', '靳东', 43.20, '2013-04-12 00:00:00');
INSERT INTO `movies` VALUES (2, '四大名捕', '刘亦菲', 62.50, '2013-12-21 00:00:00');
INSERT INTO `movies` VALUES (3, '猎场', '靳东', 68.50, '2017-11-03 00:00:00');
INSERT INTO `movies` VALUES (4, '芳华', '范冰冰', 55.00, '2017-09-15 00:00:00');
INSERT INTO `movies` VALUES (5, '功夫瑜伽', '成龙', 91.80, '2017-01-28 00:00:00');
INSERT INTO `movies` VALUES (6, '惊天解密', '靳东', 96.90, '2019-08-13 00:00:00');
INSERT INTO `movies` VALUES (7, '铜雀台', NULL, 65.00, '2025-12-16 00:00:00');
INSERT INTO `movies` VALUES (8, '天下无贼', '刘亦菲', 44.90, '2004-12-16 00:00:00');
INSERT INTO `movies` VALUES (9, '建国大业', '范冰冰', 70.50, '2009-09-21 00:00:00');
INSERT INTO `movies` VALUES (10, '赛尔号4：疯狂机器城', '范冰冰', 58.90, '2021-07-30 00:00:00');
INSERT INTO `movies` VALUES (11, '花木兰', '刘亦菲', 89.00, '2020-09-11 00:00:00');
INSERT INTO `movies` VALUES (12, '警察故事', '成龙', 68.00, '1985-12-14 00:00:00');
INSERT INTO `movies` VALUES (13, '神话', '成龙', 86.50, '2005-12-22 00:00:00');

SET FOREIGN_KEY_CHECKS = 1;

用法如下：

select * from movies order by movie_name asc;

select * from movies ORDER BY FIELD(movie_name,'神话','猎场','芳华','花木兰',
'铜雀台','警察故事','天下无贼','四大名捕','惊天解密','建国大业',
'功夫瑜伽','咱们结婚吧','赛尔号4：疯狂机器城');

2.空值NULL排序（ORDER BY IF(ISNULL）

在MySQL中使用ORDER BY关键字加上我们需要排序的字段名称就可以完成该字段的排序。如果字段中存在NULL值就会对我们的排序结果造成影响。
这时候我们可以使用 ORDER BY IF(ISNULL(字段), 1, 0) 语法将NULL值转换成1或0，实现NULL值数据排序到数据集前面还是后面。

select * from movies ORDER BY actors, price desc;
select * from movies ORDER BY if(ISNULL(actors),0,1), actors, price;

3.CASE表达式（CASE···WHEN）

在实际开发中我们经常会写很多if ··· else if ··· else，这时候我们可以使用CASE···WHEN表达式解决这个问题。
以学生成绩举例。比如说：学生90分以上评为优秀，分数80-90评为良好，分数60-80评为一般，分数低于60评为“较差”。那么我们可以使用下面这种查询方式：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `student_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学号',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `sex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
  `age` int NULL DEFAULT NULL COMMENT '年龄',
  `score` float NULL DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`student_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'Jiang Xiuying', 'F', 537, 470.15);
INSERT INTO `student` VALUES ('10', 'Miura Itsuki', 'M', 123, 776.18);
INSERT INTO `student` VALUES ('2', 'Wu Yunxi', 'M', 408, 813.9);
INSERT INTO `student` VALUES ('3', 'Jiang Zitao', 'M', 237, 163.89);
INSERT INTO `student` VALUES ('4', 'Brenda Wright', 'F', 148, 614.82);
INSERT INTO `student` VALUES ('5', 'Fu Ziyi', 'M', 92, 230.4);
INSERT INTO `student` VALUES ('6', 'Mike Gomez', 'M', 886, 980.46);
INSERT INTO `student` VALUES ('7', 'Hung Tak Wah', 'M', 984, 907.99);
INSERT INTO `student` VALUES ('8', 'Phyllis Gonzalez', 'F', 799, 396.23);
INSERT INTO `student` VALUES ('9', 'Kyle Wright', 'M', 613, 409.43);

SET FOREIGN_KEY_CHECKS = 1;

SELECT *,CASE WHEN score > 90 THEN '优秀'
            WHEN score > 80 THEN '良好'
            WHEN score > 60 THEN '一般'
            ELSE '较差' END AS 'level'
FROM student;

4.分组连接函数（GROUP_CONCAT）

分组连接函数可以在分组后指定字段的字符串连接方式，并且还可以指定排序逻辑；连接字符串默认为英文逗号。
比如说根据演员进行分组，并将相应的电影名称按照票价进行降序排列，而且电影名称之间通过“_”拼接。用法如下：

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
ORDER BY IF(ISNULL(actors), 1, 0);

5.分组统计数据后再进行统计汇总（WITH ROLLUP）

在MySql中可以使用 with rollup在分组统计数据的基础上再进行数据统计汇总，即将分组后的数据进行汇总（最后一行即合计数据）。

SELECT actors, SUM(price) FROM movies GROUP BY actors;

SELECT actors, SUM(price) 合计 FROM movies GROUP BY actors WITH ROLLUP
ORDER BY IF(ISNULL(actors), 1, 0);

6.子查询提取（WITH AS）

如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用with as将共用的子查询提取出来并取一个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用。
需求：获取演员刘亦菲票价大于50且小于65的数据。

-- m1 m2 一次定义多次使用
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.优雅处理数据插入、更新时主键、唯一键重复

在MySql中插入、更新数据有时会遇到主键重复的场景，通常的做法就是先进行删除在插入达到可重复执行的效果，但是这种方法有时候会错误删除数据。

插入数据时我们可以使用IGNORE，它的作用是插入的值遇到主键或者唯一键重复时自动忽略重复的数据，不影响后面数据的插入，即有则忽略，无则插入。示例如下：

SELECT * FROM movies WHERE id >= 13;

INSERT INTO movies (id, movie_name, actors, price, release_date) VALUES
(13, '神话', '成龙', 100, '2005-12-22');

INSERT IGNORE INTO movies (id, movie_name, actors, price, release_date) VALUES
(13, '神话', '成龙', 100, '2005-12-22');

INSERT IGNORE INTO movies (id, movie_name, actors, price, release_date) VALUES
(14, '神话2', '成龙', 114, '2005-12-22');

还可以使用REPLACE关键字，当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入，即有则删除+插入，无则插入，示例如下：

REPLACE INTO movies (id, movie_name, actors, price, release_date) VALUES
(14, '神话2', '成龙', 100, '2005-12-22');

REPLACE INTO movies (id, movie_name, actors, price, release_date) VALUES
(15, '神话3', '成龙', 115, '2005-12-22');

更新数据时使用on duplicate key update。它的作用就是当插入的记录遇到主键或者唯一键重复时，会执行后面定义的UPDATE操作。相当于先执行Insert 操作，再根据主键或者唯一键执行update操作，即有就更新，没有就插入。示例如下：

INSERT INTO movies (id, movie_name, actors, price, release_date) VALUES
(15, '神话3', '成龙', 115, '2005-12-22') on duplicate key update price = price + 10;

INSERT INTO movies (id, movie_name, actors, price, release_date) VALUES
(16, '神话4', '成龙', 75, '2005-12-22') on duplicate key update price = price + 10;