【sql语句】记一些特殊的使用方式

三元表达式：IF(条件,值1,值2)

例子1：（update）性别对换（将表中性别为1的数据全部改为0，0改为1）：

update 表名 set sex=if(sex='1','0','1')
#Case...when实现：
update 表名 set sex = case sex when "0" then "1" else "0" end;

例子2：（select）奇偶id对调（最后一位为奇数时不变）：

select if(id%2=0,id-1,if(id=(select MAX(id) from seat),id,id+1)) as id,student from seat order by id

删除重复数据

 例子1：（delete）删除重复邮箱

delete p1 from Person p1,Person p2 
where p1.Email = p2.Email and p1.Id>p2.Id
#也可以写成
delete p1 from Person p1 join Person p2
on p1.Email = p2.Email and p1.Id>p2.Id

查找重复数据

例子：（select）查找重复邮箱的数据

select email from (
select email, count(Email) num from Person  group by Email ) b 
where b.num>1;
#############################
select Email from Person group by Email having count(Email)>1;

查询在另一张表中没有关联的数据

例子：（select）从不订购的客服（即用户表中的用户未订购商品，所以订购信息表没有该用户的订购单）

#not exists
select c.Name as Customers from Customers c 
where not exists (select 1 from Orders o where o.CustomerId = c.Id);
#not in
select c.name as Customers from Customers c
where c.id not in(select o.CustomerId from Orders o)
#is null
select c.Name as Customers from Customers c 
left join Orders o on o.CustomerId = c.Id where o.Id is null;

DATEDIFF 的使用

DATEDIFF比较两个日期类型的值

例子：查询所有比前天温度还要高的日期的Id

SELECT a.id AS 'Id' FROM  weather a,weather b
 	where DATEDIFF(a.RecordDate, b.RecordDate) = 1
    AND a.Temperature > b.Temperature

查找指定数量的数据

例子：（select）查找超过五名学生的课程

SELECT A.CLASS FROM (SELECT DISTINCT * FROM COURSES) A
    GROUP BY A.CLASS HAVING COUNT(A.CLASS) >= 5;
###########################################################
select class from courses group by class HAVING COUNT(DISTINCT student)>=5

行列转换-group by的使用

例子：

 

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

查询结果格式如下面的示例所示：

 解析：使用任意一个聚合函数（MIN、MAX、AVG、COUNT、SUM）+group by可以实现行变列，再使用三元表达式或case +when确保输出值

#1
SELECT 
    id, 
    max(if(month = 'Jan',revenue,null)) Jan_Revenue,
    max(if(month = 'Feb',revenue,null)) Feb_Revenue,
    max(if(month = 'Mar',revenue,null)) Mar_Revenue,
    max(if(month = 'Apr',revenue,null)) Apr_Revenue,
    max(if(month = 'May',revenue,null)) May_Revenue,
    max(if(month = 'Jun',revenue,null)) Jun_Revenue,
    max(if(month = 'Jul',revenue,null)) Jul_Revenue,
    max(if(month = 'Aug',revenue,null)) Aug_Revenue,
    max(if(month = 'Sep',revenue,null)) Sep_Revenue,
    max(if(month = 'Oct',revenue,null)) Oct_Revenue,
    max(if(month = 'Nov',revenue,null)) Nov_Revenue,
    max(if(month = 'Dec',revenue,null)) Dec_Revenue
FROM Department GROUP BY id
#2
select 
     id
    , sum(case `month` when 'Jan' then revenue else null end) as Jan_Revenue
    , sum(case `month` when 'Feb' then revenue else null end) as Feb_Revenue
    , sum(case `month` when 'Mar' then revenue else null end) as Mar_Revenue
    , sum(case `month` when 'Apr' then revenue else null end) as Apr_Revenue
    , sum(case `month` when 'May' then revenue else null end) as May_Revenue
    , sum(case `month` when 'Jun' then revenue else null end) as Jun_Revenue
    , sum(case `month` when 'Jul' then revenue else null end) as Jul_Revenue
    , sum(case `month` when 'Aug' then revenue else null end) as Aug_Revenue
    , sum(case `month` when 'Sep' then revenue else null end) as Sep_Revenue
    , sum(case `month` when 'Oct' then revenue else null end) as Oct_Revenue
    , sum(case `month` when 'Nov' then revenue else null end) as Nov_Revenue
    , sum(case `month` when 'Dec' then revenue else null end) as Dec_Revenue
from Department group by id

 

以上例子来源于力扣网的数据库专栏，学习一些比较特殊的sql语句