【MySQL练习】单行函数练习
数据库表数据:https://www.cnblogs.com/zhishu/p/16452950.html
1.显示系统时间(注:日期+时间)
这几个都可以 NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP()
SELECT NOW() FROM DUAL;
2.查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary)
SELECT employee_id,last_name,salary,salary*1.2 AS "new salary"
FROM employees;
3.将员工的姓名按首字母排序,并写出姓名的长度(length)
因为姓名都是英文用length()和char_length()都一样。
如果是中文则用char_length(),length()是以字节为单位。一个中文占两个或三个字节。
SELECT last_name,CHAR_LENGTH(last_name)
FROM employees
ORDER BY last_name;
4.查询员工id,last_name,salary,并作为一个列输出,别名为OUT_PUT
SELECT CONCAT(employee_id,',',last_name,',',salary) AS OUT_PUT
FROM employees;
5.查询公司各员工工作的年数、工作的天数,并按工作年数的降序排序
只取整数年可以用floor()向下取整。
datediff(date1,date2),返回date1和date2的间隔天数
SELECT DATEDIFF(CURDATE(),hire_date)/365 AS work_years,DATEDIFF(NOW(),hire_date) AS work_days
FROM employees
ORDER BY work_years DESC;
6.查询员工姓名,hire_date , department_id,满足以下条件:雇用时间在1997年之后(包括1997),department_id为80 或 90 或110, commission_pct不为空。
Year(date),返回date中的年份。
STR_TO_DATE('1997-01-01', '%Y-%m-%d'),按照字符串%Y-%m-%d
对1997-01-01
进行解析,解析为一个日期。
DATE_FORMAT(hire_date,'%Y'),按照字符串%Y
格式化日期hire_date
值
SELECT last_name,hire_date,department_id
FROM employees
WHERE YEAR(hire_date)>=1997
AND department_id IN(80,90,110)
AND commission_pct IS NOT NULL;
SELECT last_name, hire_date, department_id
FROM employees
#WHERE hire_date >= '1997-01-01'
#WHERE hire_date >= STR_TO_DATE('1997-01-01', '%Y-%m-%d')
WHERE DATE_FORMAT(hire_date,'%Y') >= '1997'
AND department_id IN (80, 90, 110)
AND commission_pct IS NOT NULL;
7.查询公司中入职超过10000天的员工姓名、入职时间
to_days(date) 返回日期date距离0000年1月1日的天数
SELECT last_name,hire_date
FROM employees
#WHERE TO_DAYS(NOW()) - to_days(hire_date) > 10000;
WHERE DATEDIFF(CURDATE(),hire_date)>10000;
8.做一个查询,产生下面的结果
truncate(x,y) 返回对x截取为y位小数的结果
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ',
TRUNCATE(salary * 3, 0)) "Dream Salary"
FROM employees;
9.使用case-when,按照下面的条件:
job_id | grade |
---|---|
AD_PRES | A |
ST_MAN | B |
IT_PROG | C |
SA_REP | D |
ST_CLERK | E |
产生下面的结果
SELECT last_name AS Last_name,job_id AS 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 'F'
END AS 'grade'
FROM employees;