本文共 5204 字，大约阅读时间需要 17 分钟。

# 常见函数/*函数: 类似于java中的方法常见函数:		字符函数		数学函数		日期函数		流程控制函数*/# 一,字符函数# concat 拼接函数SELECT CONCAT("hello,",first_name) FROM employees;# length 获取字节长度SELECT LENGTH("你好"); //6# cahr_length 获取字符个数SELECT CHAR_LENGTH("你好"); //2# substring 截取子串/*substring(str,起始索引,截取的字符长度) 起始索引从1开始*/SELECT SUBSTR("java是编程语言",1,4); #javaSELECT substr("java是编程语言",6,4); #编程语言# instr 获取字符第一次出现的索引SELECT INSTR("孙悟空三打白骨精", "白骨精"); # 6# trim SELECT TRIM("   java  "); #去除前后空格SELECT TRIM('x' FROM "xxxxjavaxxxx"); # 去除x# LPAD/RPAD 左填充/右填充SELECT LPAD('孙悟空',6,'a'); # aaa孙悟空SELECT RPAD('孙悟空',6,'a'); # 孙悟空aaa# upper/lower 变大写/变小写SELECT UPPER("Java"); # JAVASELECT LOWER("java"); # java# 案例: 查询员工表的姓名, 要求格式: 姓首字符大写,其它字符小写,名所有字符大写,且姓和名之间用_分隔,最后起别名"output"SELECT  UPPER(SUBSTR(last_name,1,1)), last_name FROM employees  # 姓首字符大写SELECT LOWER(SUBSTR(last_name,2)) FROM employees # 其它字符小写SELECT UPPER(first_name) FROM employees; # 名所有字符大写SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2)),'_',UPPER(first_name)) "output" FROM employees;# strcmp 比较两个字符大小# 一个一个字符进行比较,先比第一个字符,第一个比较不出,在比较第二个字符,# 左边大返回1,右边大返回-1,都相等返回0SELECT STRCMP('abc','acaa'); # -1# left/right 截取子串SELECT LEFT("java",2); # jaSELECT RIGHT("java",2); # vaSELECT CONCAT(LEFT("java",2),RIGHT("java",2)) # java# 二,数学函数# 1.abs 绝对值SELECT ABS(-2.4) # 2.4# 2.ceil 向上取整 天花板数SELECT CEIL(1.09); # 2# 3.floor 向下取整 SELECT FLOOR(1.09); # 1# 4.ROUND 四舍五入SELECT ROUND(1.86666) # 2SELECT ROUND(1.86666, 2) # 1.87# 5.TRUNCATE() 截断SELECT TRUNCATE(1.88888,2) # 1.88# 6.mod 取余# a % b = a-a/b*bSELECT MOD(10, 3) # 1# 三,日期函数SELECT NOW(); # 2021-03-12 16:43:12 获取现在的日期和时间SELECT CURDATE(); # 2021-03-12 获取现在的日期SELECT CURTIME(); # 16:43:58 获取现在的时间SELECT DATEDIFF("1999-12-12","2020-12-12"); # -7671 前面减去后面,相差的天数SELECT DATE_FORMAT("1998-12-12","%Y年%m月%d日 %H小时,%i分钟%s秒"); # 1998年12月12日 00小时,00分钟00秒SELECT DATE_FORMAT(hiredate,"%Y年%m月%d日 %H小时,%i分钟%s秒") "入职日期" FROM employees;# str_to_date 按指定格式解析字符串为日期类型SELECT STR_TO_DATE("3/15 1998","%m/%d %Y"); # 1998-03-15SELECT * FROM employees WHERE hiredate < STR_TO_DATE("3/15 1998","%m/%d %Y"); #入职日期小于指定时间# 四,流程控制函数# 1.if 函数SELECT IF(100 > 9,"好","坏"); # 好#需求: 如果有奖金,则显示最终奖金,如果没有,则显示0,SELECT *, if(commission_pct is null,0,12 * salary * (1 + commission_pct)) FROM employees# 2.case 函数# 类似于switch语句,可以实现等值判断/*case 表达式when 值1 then 结果1when 值2 then 结果2else 结果nend案例:部门编号是30,工资显示为2倍部门编号是50,工资显示为3倍部门编号是60,工资显示为4倍*/SELECT department_id, salary,CASE department_id	WHEN 30 THEN salary*2	WHEN 50 THEN salary*3	WHEN 60 THEN salary*4	ELSE salary END "new salaty"FROM employees;/*情况2: 类似于多重if语句,实现区间判断案例: 如果工资>20000,显示级别A			工资>15000,显示级别B			工资>10000,显示级别C			否则,显示D*/SELECT salary,casewhen salary > 20000 then 'A'when salary > 15000 then 'B'when salary > 10000 then 'C'else 'D'endas "等级"FROM employees;# 进阶5: 分组函数/*说明: 分组函数往往用于实现将一组数据进行统计计算,最终得到一个值,又称为聚合函数或统计函数sum(字段名): 求和avg(字段名): 求平均值max(字段名): 求最大值count(字段名): 计算非空字段值的个数*/# 案例1: 查询员工信息中,所有员工的工资和,工资平均值,最高工资,最低工资,有工资的个数SELECT SUM(salary) "工资和", AVG(salary) "平均工资", MAX(salary) "最高工资", MIN(salary) "最低工资", COUNT(salary) "有工资个数" FROM employees# 案例2: 查询员工表employee_id记录数 count默认去掉空值SELECT count(employee_id) FROM employees;# 查询emp表中有工资的人数SELECT COUNT(salary) FROM employees;# 查询emp表中月薪大于25000的人数SELECT COUNT(*) FROM employees WHERE salary > 2500;# count的补充 查询表的总记录数SELECT count(*) FROM employees;SELECT count(*) FROM employees WHERE department_id = 30;# 搭配distinct使用,查询部门数SELECT COUNT(distinct department_id) FROM employees;# 每个部门的总工资,平均工资SELECT SUM(salary),AVG(salary), department_id from employees GROUP BY department_idSELECT * FROM employees# 进阶6:分组查询/*语法:select 查询列表 FROM 表名WHERE 筛选条件GROUP BY 分组列表执行顺序fromwhereGROUP BYHAVINGSELECTORDER BY特点:1.查询列表往往是 分组函数和被分组的字段2.分组查询中的筛选分为两类	        筛选的基表 			    使用的关键词			位置分组前筛选	原始表				      WHERE			 GROUP BY 前面分组后筛选	分组后(GROUP BY)的结果集    HAVING		 GROUP BY 后面		WHERE -> GROUP BY -> HAVING		问题: 分组函数做条件只可以放在 HAVING 后面*/#1)简单的分组#案例1: 查询每个工种的员工平均工资SELECT AVG(salary), job_id FROM employees GROUP BY job_id;#案例2: 查询每个领导的手下人数SELECT COUNT(manager_id), manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id;#2)可以实现分组前的筛选#案例1:查询邮箱中包含a字符的每个部门的最高工资SELECT MAX(salary), department_id FROM employees WHERE email like "%a%" GROUP BY department_id;#案例2:查询每个领导手下有奖金的员工的平均工资SELECT AVG(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;#3) 可以实现分组后的筛选#案例1: 查询哪个部门的员工个数>5SELECT COUNT(*) "员工个数", department_id FROM employees GROUP BY department_id #分析2:在刚才的基础结果上,筛选哪个部门的员工个数>5   HAVING 分组后的筛选SELECT COUNT(*) "员工个数", department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 5#案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资SELECT MAX(salary), job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id  HAVING MAX(salary) > 12000#案例3:领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资SELECT MIN(salary), manager_id FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000#4) 可以实现排序# 案例: 查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序SELECT MAX(salary), job_id FROM employees WHERE commission_pct IS NULL GROUP BY job_id HAVING MAX(salary) > 6000 ORDER BY MAX(salary)#5)按多个字段分组#案例: 查询每个工种每个部门的最低工资,,并按最低工资降序SELECT MIN(salary), job_id, department_id FROM employees GROUP BY job_id, department_id ORDER BY MIN(salary) desc