【MySQL练习】函数练习

练习题目：

1.查询员工号，姓氏，工资，以及工资提高百分之15%后的结果（涨工资）

2.将员工的姓氏按首字母排序，并显示姓氏的长度（length）

3.查询员工id,last_name,salary，并作为一个列合并输出，别名为员工工资汇总表

4.查询公司各员工的employee_id、工作的年数"worked_years"、工作的天数"worked_days"，并按工作年数的降序排序

5.查询员工姓氏，hire_date , department_id，满足以下条件：
#雇用时间在2000年之后，department_id 为70 或 80, commission_pct不为空

6.查询公司中入职超过10000天的员工姓氏、入职时间、入职天数

7.做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3> 

8.使用case-when，按照下面的条件：
/*job                  grade
AD_PRES                  A
ST_MAN                   B
IT_PROG                  C
SA_REP                   D
ST_CLERK                 E

产生下面的结果:
*/

9.查询公司员工工资的最大值，最小值，平均值，总和

10.查询各job_id、以及各job_id员工工资的最大值，最小值，平均值，总和

11.查询具有各个job_id的员工人数

12.查询各j各job_id、以及各job_id的员工最高工资和最低工资的差距（DIFFERENCE） 

13.查询各个管理者manager_id手下员工的最低工资，其中最低工资不能低于5000，没有管理者的员工不计算在内

第7章_函数练习

USE mydb

1.查询员工号，姓氏，工资，以及工资提高百分之15%后的结果（涨工资）

SELECT employee_id,last_name,salary,salary*1.15 'new salary'
FROM employees

注： 代码中的'new salary'是给涨之后的工资的列起了一个别名

2.将员工的姓氏按首字母排序，并显示姓氏的长度（length）

SELECT last_name,LENGTH(last_name)
FROM employees
ORDER BY last_name DESC

 运用了length函数，order by 子句表示排序，desc降序（默认asc升序）

3.查询员工id,last_name,salary，并作为一个列合并输出，别名为员工工资汇总表

SELECT CONCAT(employee_id,' ',last_name,' ',salary) AS "员工工资汇总表"
FROM employees

 concat()函数

1、功能：将多个字符串连接成一个字符串。

2、语法：concat(str1, str2,...)

4.查询公司各员工的employee_id、工作的年数"worked_years"、工作的天数"worked_days"，并按工作年数的降序排序

SELECT employee_id,DATEDIFF(CURDATE(),hire_date)/365 "worked_years",DATEDIFF(CURDATE(),hire_date) worked_days
FROM employees
ORDER BY worked_years DESC

​ 

 DATEDIFF(date1,date2)        | 返回date1 - date2的日期间隔天数   

5.查询员工姓氏，hire_date , department_id，满足以下条件：
#雇用时间在2000年之后，department_id 为70 或 80, commission_pct不为空

SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN(70,80) AND commission_pct IS NOT NULL
AND hire_date >= '2000-01-01'

​

where子句多个查询条件时用and连接 

 6.查询公司中入职超过10000天的员工姓氏、入职时间、入职天数

SELECT last_name,hire_date,DATEDIFF(CURDATE(),hire_date) "worked_days"
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date) >10000

 DATEDIFF(date1,date2)        | 返回date1 - date2的日期间隔天数   

7.做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3> 

SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3)
       AS "Dream Salary"
FROM employees

 

concat()函数

1、功能：将多个字符串连接成一个字符串。

2、语法：concat(str1, str2,...) 

 8.使用case-when，按照下面的条件：

/*job                  grade
AD_PRES                  A
ST_MAN                   B
IT_PROG                  C
SA_REP                   D
ST_CLERK                 E

产生下面的结果:
*/

SELECT job_id,CASE WHEN job_id = 'AD_PRES' THEN 'A' 
			 WHEN job_id = 'ST_MAN'  THEN 'B'
			 WHEN job_id = 'IT_PROG'  THEN 'C'
			 WHEN job_id = 'SA_REP'  THEN 'D' 
			 WHEN job_id = 'ST_CLERK'  THEN 'E'
			 END "details" 
FROM employees;

 

  流程控制函数CASE WHEN

语法：CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END # 类似于java的if ... else if ... else if ... else

9.查询公司员工工资的最大值，最小值，平均值，总和

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees

聚合函数：对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

AVG / SUM ：只适用于数值类型的字段（或变量）

MAX / MIN :可以对**任意数据类型**的数据使用 MIN 和 MAX 函数。

COUNT(*)返回表中记录总数，适用于**任意数据类型**。

 10.查询各job_id、以及各job_id员工工资的最大值，最小值，平均值，总和

SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id

 **可以使用GROUP BY子句将表中的数据分成若干组**

11.查询具有各个job_id的员工人数

SELECT COUNT(*),job_id
FROM employees
GROUP BY job_id

 COUNT：
# ① 作用：计算指定字段在查询结构中出现的个数

 12.查询各j各job_id、以及各job_id的员工最高工资和最低工资的差距（DIFFERENCE） 

SELECT job_id,MAX(salary)-MIN(salary) DIFFERENCE
FROM employees 
GROUP BY job_id

 

13.查询各个管理者manager_id手下员工的最低工资，其中最低工资不能低于5000，没有管理者的员工不计算在内

SELECT manager_id,MIN(salary)
FROM employees
WHERE  manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=5000 

 

 ##查询的结构（顺序不能更改）
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#可以使用GROUP BY子句将表中的数据分成若干组
#满足HAVING 子句中条件的分组 将(才能) 被显示
#HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

**非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。**