https://leetcode.cn/study-plan/sql/?progress=xhqm4sjh

目录

• 选择
• • 595. 大的国家
  • 1757. 可回收且低脂的产品
  • 584. 寻找用户推荐人
  • 183. 从不订购的客户
• 排序 & 修改
• • 1873. 计算特殊奖金
  • 627. 变更性别
  • 196. 删除重复的电子邮箱

选择

595. 大的国家

# Write your MySQL query statement below
select name,population,area
from World
where area>=3000000 or population>=25000000;

# Write your MySQL query statement below
select name,population,area
from World
where area>=3000000
union 
select name,population,area
from World
where population>=25000000;

1757. 可回收且低脂的产品

# Write your MySQL query statement below
select product_id
from Products
where low_fats='Y' and recyclable='Y';

584. 寻找用户推荐人

# Write your MySQL query statement below
select name
from customer
where referee_id<>2 or referee_id is NULL;

183. 从不订购的客户

# Write your MySQL query statement below
select Name Customers
from Customers
where Customers.Id not in(select  CustomerIdfrom Orders
);

排序 & 修改

1873. 计算特殊奖金

SQL中IF函数的使用:
IF(expr1,expr2,expr3)

• expr1 的值为 TRUE，则返回值为 expr2
• expr1 的值为FALSE，则返回值为 expr3

# Write your MySQL query statement below
select employee_id,if(employee_id%2=1 and name not like 'M%',salary,0) bonus
from Employees
order by employee_id;        

627. 变更性别

case when的使用方法:

case 列名when   条件值1   then  选项1when   条件值2    then  选项2.......else     默认值      end

# Write your MySQL query statement below
update Salary
set sex=case sexwhen 'f' then 'm'else 'f' end
;

# Write your MySQL query statement below
update Salary
set sex=case when sex='f'then 'm'else 'f' end
;

196. 删除重复的电子邮箱

# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
delete p1
from Person p1,Person p2
where p1.email=p2.email and p1.id>p2.id;