sql函数

CASE WHEN

条件表达式函数（类似java的if…else…）
Case when 的用法: 一旦满足了某一个WHEN, 则这一条数据就会退出CASE WHEN , 而不再考虑 其他CASE;

举个栗子：

±------------±---------+
| Column Name | Type |
±------------±---------+
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
±------------±---------+
id 是这个表的主键。
sex 这一列的值是 ENUM 类型，只能从 (‘m’, ‘f’) 中取。
本表包含公司雇员的信息。

• 请你编写一个 SQL 查询来交换所有的 ‘f’ 和 ‘m’ （即，将所有 ‘f’ 变为 ‘m’ ，反之亦然），仅使用 单个 update
  语句 ，且不产生中间临时表。

  注意，你必须仅使用一条 update 语句，且 不能 使用 select 语句。

update Salary set sex = (case when sex ='m' then 'f' else 'm' end)

CONCAT(str1,str2,…)

字符连接函数

MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL

UPPER(str) 将字符串改为大写字母
LOWER(str) 将字符串改为小写字母
LENGTH(str) 判定字符串长度
SUBSTRING(str,a,b) 提取字段中的一段，从字符串str的第a位开始提取，提取b个字符
LEFT(str,n) 提取字符串最左边的n个字符
RIGHT(str,n) 提取字符串最右边的n个字符

举个栗子：

±---------------±--------+
| Column Name | Type |
±---------------±--------+
| user_id | int |
| name | varchar |
±---------------±--------+
user_id 是该表的主键。
该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。

• 编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。

  返回按 user_id 排序的结果表。

select user_id,CONCAT(UPPER(LEFT(name,1)),LOWER(LOWER(SUBSTRING(name,2)))) as name from Users ORDER BY user_id 

distinct 去重
GROUP_CONCAT 将某一字段的值按指定的字符进行累加

举个栗子：

表 Activities：
±------------±--------+
| 列名 | 类型 |
±------------±--------+
| sell_date | date |
| product | varchar |
±------------±--------+
此表没有主键，它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。

• 编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
• 每个日期的销售产品名称应按词典序排列。
• 返回按 sell_date排序的结果表。

select sell_date,count(distinct product) as num_sold,GROUP_CONCAT(distinct product) as products from Activities group by sell_date order by sell_date

union all 对两个结果集进行并集操作，包括重复行，不进行排序；
IF 表达式

IF( expr1 , expr2 , expr3 )
expr1 的值为 TRUE，则返回值为 expr2
expr1的值为FALSE，则返回值为 expr3

举个栗子：

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。
±—±-----+
| id | p_id |
±—±-----+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
±—±-----+
树中每个节点属于以下三种类型之一：
叶子：如果这个节点没有任何孩子节点。
根：如果这个节点是整棵树的根，即没有父节点。
内部节点：如果这个节点既不是叶子节点也不是根节点。

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：
±—±-----+
| id | Type |
±—±-----+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
±—±-----+

1.union all解法：

select id,'Root' as Type from tree where p_id is null union all 
select id,'Inner' as Type from tree where id in (select distinct p_id from tree) union all 
select id,'Leaf' as Type from tree where id not in (select distinct p_id from tree where p_id is not null) and p_id is not null order by id

2.case when解法：

select id, case when p_id is null then 'Root' when id in (select distinct p_id from tree) then 'Inner'else 'Leaf' end as Type from tree order by id

3.if 解法：

select id ,IF(ISNULL(p_id),'Root',IF(id in (select distinct p_id from tree),'Inner','Leaf')) Type from tree order by id

limit子句用于限制查询结果返回的数量，常用于分页查询

用法：【select * from tableName limit i,n 】
参数：tableName: 为数据表；
i: 为查询结果的索引值（默认从0开始）；
n: 为查询结果返回的数量

offset 去掉几行元素

举个栗子：

Employee 表：
±------------±-----+
| Column Name | Type |
±------------±-----+
| id | int |
| salary | int |
±------------±-----+
id 是这个表的主键。
表的每一行包含员工的工资信息。
编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。

select(select distinct salary from Employee order by salary desc limit 1 offset 1) SecondHighestSalary

Date日期函数
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式 YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
• YEAR - 格式 YYYY 或 YY

举个栗子：
#删除30天前的数据

DELETE FROM a_sub_user WHERE delete_flag = true AND Date(data_update_time) <= DATE(DATE_SUB(NOW(),INTERVAL 30 DAY)) 

JOIN……ON

• 对 FROM 子句中的前两个表执行笛卡尔积(交叉联接)，生成临时表(n×m行)
• on:对以上临时表进行条件筛选

DATEDIFF

DATEDIFF(date1，date2)
MySQL 中 DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。计算时只用到这些值的日期部分。

举个栗子：

表： Weather
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| recordDate | date |
| temperature | int |
±--------------±--------+
id 是这个表的主键
该表包含特定日期的温度信息

• 编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。
• 返回结果 不要求顺序 。

select w1.id id from Weather w1 JOIN Weather w2 ON DATEDIFF(w1.recordDate,w2.recordDate) = 1 and w1.Temperature > w2.Temperature

coalesce() 函数的作用就是返回一个不为null的值