case函数&单行函数练习
#2case 函数 1/switch case 等值
/*
java中
switch(变量或表达式){
case 常量1:语句1:break;
...
default:语句n;break;
}
MySQL中
case 要判断的变量/字段或表达式
when常量1 then 要显示的值1或语句1;
when常量2 then 要显示的值1或语句1;
...
else 要显示的值n或语句n;
end
*/
/*案例:查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资
*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary*1
END AS 新工资
FROM employees;
#case函数使用2:类似多重if 判断区间,< >
/*
java中
if(条件1){
语句1;
}else if(条件2){
语句2;
}
...
else{
语句n;
}
mysql
case
when 条件1 then 要显示的值1或 语句1;
when 条件2 then 要显示的值1或 语句2;
...
else 要显示的值n或 语句n;
end
*/
/*案例;查询员工的工资情况,
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
*/
SELECT last_name,salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 等级
FROM employees;
#显示系统时间
SELECT NOW();
#查询员工号/姓名/工资/工资提高20%后的结果(新工资)
SELECT department_id,last_name,salary,salary*1.2 AS 新工资 FROM employees;
#查询员工姓名按首字母排序,并写出姓名的长度
SELECT last_name,LENGTH(last_name) AS 姓名长度
FROM employees ORDER BY SUBSTR(last_name,1) ASC;
#做一个查询显示下面的结果
<last_name>earns<salary>monthly but wants <salary*3>
SELECT last_name,salary,salary*3 AS 'dream salary' FROM employees;
#修改
SELECT
CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS 'dream salary'
FROM employees;
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS 'dream salary'
FROM employees
WHERE salary=24000;
/*使用 case-when,按照下面条件
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
*/
SELECT job_id AS job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS grade
FROM employees;
#单行函数总结
1/字符函数
length/concat/substr/upper/lower/trim/instr/lpad/rpad/replace
2/数学函数
round/ceil/floor/mod/truncate
3/日期函数
now/curdate/curtime/year/date/month/monthname/hour/minute/second
str_to_date/date_format
5/流程函数
if-T,F
case-when-then