SQL刷题1

SQL刷题1

584.ifnull处理NULL

select name
from customer
where ifnull(referee_id,0) !=2;

183.left join,not in,not exist三种方法

#子查询里面不用加逗号
#子查询是()而不是{}

1873.修改但不是update

1)用case写

SELECT employee_id,
(CASE WHEN MOD(employee_id,2)!=0 AND LEFT(name,1)!='M' THEN salary
    else 0
END) bonus
FROM Employees
ORDER BY employee_id

2）用If写

SELECT employee_id,
IF(MOD(employee_id,2)!=0 AND LEFT(name,1)!='M',salary,0) bonus
FROM Employees
ORDER BY employee_id

3）用Union写

SELECT employee_id ,salary AS bonus
FROM Employees
WHERE employee_id%2!=0 AND name NOT LIKE ('M%')
UNION 
SELECT employee_id ,salary*0 AS bonus
FROM Employees
WHERE employee_id%2=0 OR name LIKE ('M%')
ORDER BY employee_id;

627. update set

# 1.
update salary
set sex = (
    case sex when 'm' then 'f' else 'm' end
);

# 2.
update salary set sex = if(sex='m','f','m');

# 3.
update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

196.删除，自连接

delete p1 from Person p1,Person p2
where p1.email=p2.email and p1.id>p2.id;

1667,一些函数的使用

select user_id,concat(upper(left(name,1)),lower(substr(name,2)))as name
from Users
order by user_id;

concat,upper,lower,left,right,substr函数的应用

1484.group_concat的运用

# Write your MySQL query statement below
select 
    sell_date, 
    # 获取“不同的”产品数【count(distinct product)】
    count(distinct product) as num_sold, 
    # “不同的”【distinct product】产品按照字典排序【order by product】  & “,”分割【separator ','】
    group_concat(distinct product order by product separator ',') as products
from Activities
group by sell_date;

1965.外连接

康师傅的06.多表查询的图，左连接

select A.employee_id 
from Employees A left join Salaries  B
on A.employee_id=B.employee_id
where B.employee_id is null
union all
select B.employee_id
from Salaries B left join Employees A 
on B.employee_id=A.employee_id
where A.employee_id is null
order by employee_id;

直接查

select employee_id from employees
where employee_id not in (select employee_id from salaries)
union
select employee_id from salaries
where employee_id not in (select employee_id from employees)
order by employee_id

mysql不支持full join

利用union all 不去重的特性

select 
    employee_id 
from 
    (
    select employee_id from employees
    union all 
    select employee_id from salaries
) as t
group by 
    employee_id
having 
    count(employee_id) = 1
order by 
    employee_id;

197.DateDiff

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。

SELECT w2.Id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w2.RecordDate, w1.RecordDate) = 1
AND w1.Temperature < w2.Temperature

datediff(日期1, 日期2)： 得到的结果是日期1与日期2相差的天数。 如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。