数据在本专栏的第一篇博客里
本篇考察函数,主要有日期函数、字符函数、流程控制函数和分组函数
题目汇总
3.1.显示系统时间, 日期+时间
SELECT
NOW();
3.2.查询员工号、姓名、工资,以及工资提高百分之20%后的结果,命名为new salary
SELECT employee_id, last_name, salary, salary *( 1+0.2 ) AS 'new salary'
FROM
employees;
3.3.将员工的姓名按照首字母排序,并写出姓名的长度length
SELECT
last_name,
LENGTH( last_name ),
SUBSTR( last_name, 1, 1 ) AS 首字母
FROM
employees
ORDER BY
首字母;
3.4.做一个查询,显示下面的效果:<last_name> earns <salary> monthly but wants <salary*3>
SELECT
CONCAT( last_name, ' earns ', salary, ' monthly but wants ', salary * 3 )
FROM
employees;
3.5.使用case-when
完成条件如下:
| job | grade |
| AD_PRES | A |
| ST_MAN | B |
| IT_PROG | C |
| SA_REP | D |
| ST_CLERK| E |
产生下面的结果:
last_name job_id grade
king AD_PRES
代码如下:
SELECT
last_name,
job_id,
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' ELSE '无'
END AS GRADE
FROM
employees;
3.6.查询公司员工工资的最大值、最小值、平均值、总和
SELECT
MAX( salary ),
MIN( salary ),
AVG( salary ),
SUM( salary )
FROM
employees;
3.7.查询员工表中的最大入职时间和最小入职时间的相差天数(difference)
#用到一个新函数:datediff 求两个日期之间的天数
SELECT
DATEDIFF(
MAX( hiredate ),
MIN( hiredate )) diff
FROM
employees;
3.8.查询部门编号为90的员工个数
SELECT
COUNT(*)
FROM
employees
WHERE
department_id = 90;