Mysql 琐碎知识点集

UNION的使用方法

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。但是请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。 另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

各种连接 JOIN 查询的语句

对于这么多种JOIN查询，到底什么使用应该用哪种呢？
假设查询语句是：

SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：

LEFT OUTER JOIN是选出左表存在的记录。
RIGHT OUTER JOIN是选出右表存在的记录。
FULL OUTER JOIN则是选出左右表都存在的记录。

数据库使用 on 和 where 的区别

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用 left jion 时，on 和 where 条件的区别如下：

1、on 条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

时间间隔函数

TIMESTAMPDIFF 函数

计算相差天数：

select TIMESTAMPDIFF(DAY,'2019-05-20', '2019-05-21'); # 1

计算相差小时数：

select TIMESTAMPDIFF(HOUR, '2015-03-22 07:00:00', '2015-03-22 18:00:00'); # 11

计算相差秒数：

select TIMESTAMPDIFF(SECOND, '2015-03-22 07:00:00', '2015-03-22 7:01:01'); # 61
返回值是相差的天数
DATEDIFF('2007-12-31','2007-12-30'); # 1
DATEDIFF('2010-12-30','2010-12-31'); # -1

返回从0000年到现在的天数
to_days("2015-01-04")
将时间/日期间隔添加到日期
adddate("2015-01-03",INTERVAL 1 day) #2015-01-04
从日期减去指定的时间间隔
DATE_SUB("2008-12-29",INTERVAL 2 DAY) #2008-12-27
interval
"2015-01-03"+interval'1' day #2015-01-04

Case When 判断语句的使用技巧

1、添加列

现有学生表一张, 先在需要根据具体的生日列的具体日期, **生成新的一列 **：显示90后，00后，10后

代码：

SELECT 
s_name
,s_birthday 
,CASE 
        WHEN YEAR(s_birthday)>=1990 and YEAR(s_birthday)<2000 THEN "90后"
        WHEN YEAR(s_birthday)>=2000 and YEAR(s_birthday)<2010 THEN "00后"
        WHEN YEAR(s_birthday)>=2010 and YEAR(s_birthday)<2020 THEN "10后"
    ELSE "未知"
    END 
    AS "阶段"
from student ;

2、行转为列

现统计了学生的总成绩。现在想将赵雷和李云的总成绩展示成：

SELECT 
 SUM(CASE WHEN s_name = "李云" THEN score ELSE 0 END) as "李云"
,SUM(CASE WHEN s_name = "赵雷" THEN score ELSE 0 END) as "赵雷"
FROM
 score a INNER JOIN student b   on a.s_id=b.s_id;

3、实现分组统计

一般我们都使用group by来实现分组统计，但是有的时候需要对字段先分组再统计。比如我们想知道成绩表现为不及格、良、优秀的课程数分别是多少?

实现人次的分组统计

SELECT 
CASE 
        WHEN score<60 THEN "不及格"
        WHEN score>=60 and score<85 THEN "良"
        WHEN score>=85 THEN "优秀"
    ELSE "未知"   END     AS "阶段"
  ,count(*) as "人次"
from  score a INNER JOIN student b  on a.s_id=b.s_id
GROUP BY CASE 
        WHEN score<60 THEN "不及格"
        WHEN score>=60 and score<85 THEN "良"
        WHEN score>=85 THEN "优秀"
    ELSE "未知"
    END ;

因为每个人会参加多门课程，所以当使用count(*)的时候，就是对于人次计算的，学生是没有去重的.

4、和set使用可以具体设定某一列的值，这是个很有意思的技巧

UPDATE db_user
SET
gender = 1,
nickname = CASE uid WHEN 1 THEN '翻滚的胖子' WHEN 2 THEN '壮实的瘦子' ELSE '神秘人' END 
WHERE uid < 10

上述例子，将db_user表中uid小于10的用户；

性别（gender）修改为1；

同时uid为1的用户昵称修改为“翻滚的胖子”，为2的用户昵称修改为“壮实的瘦子“，其余则修改为“神秘人”。