mysql之常见函数(几道小题目)

mysql之常见函数(几道小题目)

我就直接po代码了

#1.显示系统时间(注：日期+时间)
SELECT NOW() AS 当前时间;

#2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT 
  employee_id,
  last_name,
  salary,
  salary * (1+0.2) AS "new salary" 
FROM
  employees ;
  
#3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT 
  last_name,
  LENGTH(last_name) AS 姓名长度,
  SUBSTR(last_name, 1, 1) AS "first" 
FROM
  employees 
ORDER BY `first` ASC ;


/*

#4.做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000
*/
#
SELECT 
  CONCAT(
    last_name,
    ' earns ',
    salary,
    ' monthly but wants ',
    salary * 3
  ) AS "Dream Salary" 
FROM
  employees 
WHERE salary = 24000 ;

/*#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	  A
*/
#
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' 
  END AS Grade 
FROM
  employees 
WHERE job_id = 'AD_PRES' ;

#LIMIT关键字(先感受一下，后面我再说LIMIT)
SELECT * FROM employees 
ORDER BY last_name ASC
LIMIT 5;
#
SELECT * FROM employees 
ORDER BY last_name ASC
LIMIT 0, 5;

#
SELECT * FROM employees 
ORDER BY last_name ASC
LIMIT 5, 3;