SELECT *FROM emp_sal;
SELECT ename,salary '月薪',salary*12+bonus '年薪' FROM emp_sal;
-- ifnull用法
SELECT IFNULL(bonus,0) FROM emp_sal;
SELECT ename,salary '月薪',salary*12+IFNULL(bonus,0) '年薪' FROM emp_sal;
-- asc升序排序(默认升序),desc降序排序
SELECT *FROM emp_sal ORDER BY salary;
SELECT *FROM emp_sal ORDER BY salary ASC;
SELECT *FROM emp_sal ORDER BY salary DESC;
-- 多字段先后参与排序(默认升序)
SELECT *FROM emp_sal ORDER BY salary,bonus;
SELECT *FROM emp_sal ORDER BY salary ASC,bonus ASC;
SELECT *FROM emp_sal ORDER BY salary DESC,bonus DESC;
SELECT *FROM emp_sal ORDER BY salary ASC,bonus DESC;
-- case when then else end用法一(else可以省略)
-- case value
-- when value1 then result1
-- when value2 then result2
-- else other
-- end
SELECT ename,salary,bonus,
CASE bonus
WHEN 500 THEN '一等奖金'
WHEN 300 THEN '二等奖金'
WHEN 200 THEN '三等奖金'
ELSE '无奖金'
END '奖金等级'
FROM emp_sal ORDER BY bonus DESC;
-- case when then else end用法二(else可以省略)
-- case
-- when 表达式1 then result1
-- when 表达式2 then result2
-- else other
-- end
SELECT ename,salary,bonus,
CASE
WHEN bonus>250 THEN '高奖金'
WHEN bonus>0 THEN '低奖金'
ELSE '无奖金'
END '奖金分类'
FROM emp_sal ORDER BY bonus DESC;
--
SELECT ename,salary,bonus,
CASE
WHEN bonus>200 AND bonus<400 THEN '中奖金'
ELSE '其它奖金'
END '奖金分类'
FROM emp_sal ORDER BY bonus DESC;
-- case when then else end用法三(else可以省略)
-- 用于批量修改(需要where过滤,否则全列都改了salary)
-- case
-- when 表达式1 then result1
-- when 表达式2 then result2
-- else other
-- end
UPDATE emp_sal
SET salary=
CASE
WHEN ename='小徐' THEN 1201
WHEN ename='小红' THEN 1201
END
WHERE ename IN('小徐','小红')
SQL-asc/desc/case-when-then-else-end用法整理
于 2022-08-31 16:24:44 首次发布