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小,结果为负。