实际工作中常用SQL

查看版本

select version()

数据准备

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255)  DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `department` varchar(255)  DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (1, 'Joe', 70000.00, '研发部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (2, 'Henry', 80000.00, '研发部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (3, 'Sam', 60000.00, '研发部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (4, 'Max', 90000.00, '研发部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (5, 'Janet', 69000.00, '人事部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (6, 'Randy', 85000.00, '人事部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (7, 'Eva', 85000.00, '人事部');

时间相关

Date和字符串互转

-- Date转字符串
select created_time,DATE_FORMAT(created_time, '%Y-%m-%d'),DATE_FORMAT(created_time, '%Y-%m-%d %H:%i:%s') from items;
-- 字符串转Date
SELECT STR_TO_DATE('2023-07-12', '%Y-%m-%d'),STR_TO_DATE('2023-07-12 14:30:00', '%Y-%m-%d %H:%i:%s');

Date和时间戳互转

-- 秒转Date  &  毫秒转Date
SELECT FROM_UNIXTIME(1688301600),FROM_UNIXTIME(1688301600000 / 1000);;
-- Date转秒
SELECT UNIX_TIMESTAMP(created_time) FROM items;

分组取前三

开窗函数

以下是一个使用ROW_NUMBER()的例子，它会为每个分组内的行分配一个唯一的序号，即使值相同也会被分配不同的序号。

SELECT *
FROM (
  SELECT department,name, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
  FROM employee
) AS tt
WHERE row_num <= 3;

如果你想保留重复值的相对顺序，并且只是为了排序，不需要分配唯一序号，可以使用DENSE_RANK()：

SELECT department,name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employee

普通写法

-- 部门分组取薪水前三的，但是这样写不严谨，对于并列的会出现多条

SELECT
  e.`department` AS '部门',
  e.`name` AS '员工',
  e.salary AS '工资'
FROM
  employee e
WHERE
  (
    SELECT
      count(em.salary)
    FROM
      employee em
    WHERE
      em.salary > e.salary
      AND em.department = e.department
  ) < 3
ORDER BY
  e.department,
  e.salary DESC

分组之后得到数组集合

JSON_ARRAYAGG函数

警告

JSON_ARRAYAGG() 这个函数可以将一组结果合成为一个JSON数组。 它是在 MySQL 5.7.22 及更高版本中引入的。

SELECT department, JSON_ARRAYAGG(name) AS order_ids
FROM employee
GROUP BY department;

GROUP_CONCAT函数

警告

如果你使用的是MySQL的一个旧版本，不支持JSON_ARRAYAGG，你可以使用GROUP_CONCAT函数， 它可以将分组后的多个值连接为一个字符串，但输出格式为字符串，不是真正的数组。

SELECT department, GROUP_CONCAT(name) AS order_ids
FROM employee
GROUP BY department;

JSON_ARRAY创建JSON格式的数组

警告

这个函数用于创建一个JSON格式的数组。它是在 MySQL 5.7.8 版本首次引入的，与JSON相关的其他函数一起随着对JSON数据类型的支持一起推出

SELECT JSON_ARRAY('apple', 'banana', 'cherry') AS fruitArray;

如何实现两个数组内容的合并?

高于5.7版本

MySQL提供了JSON_MERGE()（在MySQL 5.7.8-5.7.21版本中）或在MySQL 5.7.22及更新的版本中提供的JSON_MERGE_PRESERVE()和JSON_MERGE_PATCH()函数来实现JSON数组（或对象）的合并。

警告

JSON_MERGE_PRESERVE()函数将保留两个JSON数组中的所有元素。如果合并的JSON包含对象，JSON_MERGE_PRESERVE()在遇到同名的key时会将对应的value合并为一个新的数组。

SET @json1 = JSON_ARRAY('apple', 'banana');
SET @json2 = JSON_ARRAY('cherry', 'date');

SELECT JSON_MERGE_PRESERVE(@json1, @json2) as merged_json;

低于5.7版本

如果您的MySQL版本是5.7.22之前的，您可以使用JSON_MERGE()

SET @json1 = JSON_ARRAY('apple', 'banana');
SET @json2 = JSON_ARRAY('cherry', 'date');

SELECT JSON_MERGE(@json1, @json2) as merged_json;