MySQL高级函数exists；rollup；cube；field等

目录

1.Exists；not exists：

Exists：

Not exists：

2.Group by相关（with rollup; with cube; grouping; group_concat）

With rollup：

With cube：

Grouping：

Group_concat：

3.Order by相关(field)：

4.其它：

COALESCE()

IF(expr,v1,v2)

IFNULL(v1,v2)

ISNULL(expression)

NULLIF(expr1, expr2)

---

使用表：

表1：student

表2：score

---

1.Exists；not exists：

Exists和not exists一般用来代替子查询，使用前者比后者更加高效。

Exists：

我们首先来看一个子查询：

SELECT * FROM student 
WHERE id IN (SELECT studentid FROM score WHERE score = 80 AND gender = '男');

再来看使用exists得到的结果：

SELECT * FROM student 
WHERE EXISTS(
SELECT unitid FROM score 
WHERE student.id = score.`studentid` AND score = 80 AND gender = '男');

我们发现与使用子查询的结果是相同的，那么Exists背后的逻辑是什么呢？实际上在exists返回的是boolean值，在上面的例子中，是对student表中的每一行返回一个boolean值，boolean值为1的行表示该行符合exists子句的条件，为0则表示不符合。

好，我们再看exists的子句：

SELECT unitid 
FROM score 
WHERE student.id = score.`studentid` AND score = 80 AND gender = '男';

我们看到在select子句中是使用了unitid这个字段，但实际上在exists中，因为输出的是boolean值，与select子句的字段没有任何关系，因此这里不仅可以使用unitid，也可以使用任何一个字段，甚至是*或者是Null，或者是一个字符串，或者是一个整数，都是可以的，可以自己试一下。

接着看from子句的表名，我们发现是score，那么换成student这个表行不行呢？这是不行的，因为我们在where子句里的条件是依据score表里设定的。

好，最后我们来看where子句里的条件。相比于使用子查询，exists的where子句里多出一个student.id = score.’studentid’，这个条件实际上就是将student表和score表联系在了一起，之后的两个条件就是普通的筛选条件了。

为了更直观地说明exists的返回值是boolean格式，这里我们将exists放在select子句之中，得到的结果多了boolean这一列，所以原SQL语句，就是将boolean字段中为1的记录筛选了出来。

SELECT *, EXISTS(
  SELECT unitid FROM score 
  WHERE student.id = score.`studentid` AND score = 80 AND gender = '男'
) AS 'boolean' 
FROM student;

Not exists：

Not exists与exists正相反，就是将返回的boolean值中为0的记录筛选了出来。

SELECT * FROM student 
WHERE NOT EXISTS(
  SELECT unitid FROM score 
  WHERE student.id = score.`studentid` AND score = 80 AND gender = '男'
);

同样为了直观说明，将not exists放在select子句中：

SELECT *, NOT EXISTS(
  SELECT unitid FROM score 
  WHERE student.id = score.`studentid` AND score = 80 AND gender = '男'
) AS 'boolean' 
FROM student;

那么为什么exists和not exists比子查询更好呢？

首先，exists可以用到索引，并且exists在查到一条记录满足我们的条件时，就会终止查询，而IN则是采用全表扫描，另外，IN还会生成临时表，而exists不会。

当然子查询也可以使用join替代，这个就仁者见仁，智者见智了。

---

2.Group by相关（with rollup; with cube; grouping; group_concat）

With rollup：

With rollup能够让我们相当方便的一次性得到不同分组下的聚合结果，我们使用score表举一个例子，在group by子句后添加with rollup：

SELECT studentid, unitID, AVG(score) 
FROM score 
GROUP BY studentid, unitID WITH ROLLUP;

可以看到与单单group by不同，加上with rollup后返回的条数变多了，并且在group by子句的字段下出现了null值。

我们首先看最后一条记录，即studentid和unitID都为null的记录，其score的平均值是79.8571。

如果我们直接对所有的score计算平均：

SELECT Null studentid, Null unitID, AVG(score) FROM score;

咦？原来所有记录的平均就是79.8571，与上面使用了with rollup的最后一个记录的数据一样。

这是怎么回事呢？先别急，我们来看下仅对studentid进行group by的情况：

SELECT studentid, NULL unitID, AVG(score) FROM score GROUP BY studentid;

这里的结果与使用了with rollup的结果比较，发现这三条记录的score的平均值与使用了with rollup的结果中unitID为Null的三条结果是一样的。

这样结论就出来了。

实际上with rollup的作用是在需要对不同情况做分组聚合的情况下，一个比多次select，group by并union all，在语句上更为简洁的方法。

怎么理解呢？如果我们只使用group by的话，那么想要得到上面的结果，我们需要输入的SQL的语句如下：

SELECT NULL studentid, NULL unitID, AVG(score) FROM score
UNION ALL
SELECT studentid, NULL unitID, AVG(score) FROM score GROUP BY studentid
UNION ALL
SELECT * FROM score;

得到的结果与使用with rollup的结果是一致的。

对于group by Col_1, Col_2, Col_3 with rollup这条语句来说，实际上是union all了多次group by的结果，这多次group by包括group by Col_1, Col_2, Col_3；group by Col_1, Col_2；group by Col_1以及对所有记录进行聚合运算。

这样就算彻底理解了with rollup的用法，当然，group by对后面子句的字段的顺序是敏感的，加上with rollup也是一样的。

SELECT studentid, unitID, AVG(score) 
FROM score 
GROUP BY unitID, studentid WITH ROLLUP;

这里将unitID和studentid调了位置，得到的结果就变了。

在使用with rollup时需要注意，with rollup和order by是互斥的，也就是在使用with rollup时，不能使用order by。另外，如果分组的列中包含NULL值，此时 with rollup的结果会出错，因此可以在分组之前，将null值用0替换。

With cube：

With cube与with rollup实际上思路是相同的，也是一种更简洁的解决需要多次group by的需求的方法，区别在于，with cube会遍历group by子句中字段的所有排列组合。

比如group by Col_1, Col_2 with roll up的组合为（Col_1, Col_2）;（Col_1）和（），但是对于group by Col_1, Col_2 with cube来说，其组合为（Col_1, Col_2）; (Col_1)；(Col_2)；（）。

由于在我个人电脑上的版本不支持with cube无法演示，这里就不做举例，尽管如此，在了解with rollup的用法后，with cube就手到擒来了。

Grouping：

我们知道在with rollup和with cube的分类聚合语句中，会在字段中出现Null值，grouping语句则用1代表出现Null值得记录，用0表示没有出现Null值的记录。举例如下：

SELECT studentid, unitID, AVG(score), grouping(studentid), grouping(unitID) 
FROM score GROUP BY unitID, studentid WITH ROLLUP;

可以看到字段和出现Null值的记录都是一一对应的，那么这个grouping有什么用呢？它可以让我们方便的选择我们想要输出的group by的排列组合，比如在with rollup后面加上having语句：

SELECT studentid, unitID, AVG(score), grouping(studentid) a, grouping(unitID) b
FROM score GROUP BY unitID, studentid WITH ROLLUP HAVING a = 1;

这样，就只出现studentid为Null的记录了。

这里可能会有人有一些疑惑，为什么不直接在where子句中添加studentid is null呢？这是因为where是在group by之前执行的，where studentid is null只是在原始表格中将studentid为null的记录筛选出来，再进行group by了，这样的话得到的不是我们想要的结果：

SELECT studentid, unitID, AVG(score), grouping(studentid) a, grouping(unitID) b
FROM score WHERE studentid IS NULL GROUP BY unitID, studentid WITH ROLLUP;

发现得到的结果是空表，这是自然的，因为原始表里不存在studentid为null的记录。

Group_concat：

Group_concat用于一些特殊的需求，比如我们想要得到的表是，每个学生id后面要显示它们所有上过的课的id：

SELECT studentid, GROUP_CONCAT(unitID), SUM(score) 
FROM score GROUP BY studentid;

这里就可以只用一行，就能看到每个学生上过的课了，group_cancat默认使用逗号进行分割。

那如果我们不仅想要每个学生后面显示他们上过的课程，还要显示每个课程的得分，那怎么办呢？我们可以这样：

SELECT studentid, GROUP_CONCAT(unitID, score), SUM(score) 
FROM score GROUP BY studentid;

但是我们发现unitID和score合到一起了，我们可以对此稍加改进，将group_concat(unitID, score)变成group_concat(unitID, ‘|’, score)：

SELECT studentid, GROUP_CONCAT(unitID, '|', score), SUM(score) 
FROM score GROUP BY studentid;

这样，课程号和分数就分开了。

---

3.Order by相关(field)：

在进行order by时，要么是从小到大，要么是从大到小，但有时需求不是这样，比如对于score表中，如果unitID为2的课程是最重要的，我们想要让unitID为2的记录排在最前面，那该怎么办呢？无论是ASC还是DESC，unitID为2的记录都排在中间：

SELECT * FROM score ORDER BY unitID;

SELECT * FROM score ORDER BY unitID DESC;

这时，就轮到field()出马了，field是这样用的：order by field(字段名，该字段的值1，该字段的值2，……，该字段的值N)，这样，返回的结果就会按照field中的字段和顺序进行排序了：

SELECT * FROM score ORDER BY FIELD(unitID, 2, 3, 1);

当然，我们也可以使用连续的filed，在对第一个字段进行自定义排序后，再对第二个字段进行自定义排序：

SELECT * FROM score ORDER BY FIELD(unitID, 2, 3, 1), FIELD(studentid, 2, 1, 3);

---

4.其它：

COALESCE()

返回参数中的第一个非空表达式（从左向右）

SELECT COALESCE(NULL,NULL,'woshisb','fword',NULL,2) AS test;

IF(expr,v1,v2)

如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。

SELECT IF(1 = 1,'woshisb','fword') AS test1, IF (1 = 2, 'woshisb', 'fword') AS test2;

IFNULL(v1,v2)

如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。

SELECT IFNULL(NULL,'woshisb') AS test1, IFNULL('fword','woshisb') AS test2;

ISNULL(expression)

判断表达式是否为 NULL

SELECT ISNULL('woshisb') AS test1, ISNULL(NULL) AS test2;

NULLIF(expr1, expr2)

比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1

SELECT NULLIF('woshisb','woshisb') AS test1, NULLIF('woshisb','fword') AS test2;