三元表达式: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语句