一、单行函数
1、字符函数
concat拼接
substr截取子串
upper转换成大写
lower转换成小写
trim去前后指定的空格和字符
ltrim去左边空格
rtrim去右边空格
replace替换
lpad左填充
rpad右填充
instr返回子串第一次出现的索引
length 获取字节个数
2、数学函数
round 四舍五入
rand 随机数
floor向下取整
ceil向上取整
mod取余
truncate截断
3、日期函数
now当前系统日期+时间
curdate当前系统日期
curtime当前系统时间
str_to_date 将字符转换成日期
date_format将日期转换成字符
4、流程控制函数
if 处理双分支
case语句 处理多分支
情况1：处理等值判断
情况2：处理条件判断
5、其他函数
version版本
database当前库
user当前连接用户
--------------------------------------------------------------------------------------------------------------------
#一、字符函数 #1.length 获取参数值的字节个数 SELECT LENGTH('john'); SELECT LENGTH('张三丰hahaha'); SHOW VARIABLES LIKE '%char%' #2.concat 拼接字符串 SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees; #3.upper、lower SELECT UPPER('john'); SELECT LOWER('joHn'); #示例：将姓变大写，名变小写，然后拼接 SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees; #4.substr、substring 注意：索引从1开始 #截取从指定索引处后面所有字符 SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put; #截取从指定索引处指定字符长度的字符 SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put; #案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来 SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM employees; #5.instr 返回子串第一次出现的索引，如果找不到返回0 SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put; #6.trim SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put; SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put; #7.lpad 用指定的字符实现左填充指定长度 SELECT LPAD('殷素素',2,'*') AS out_put; #8.rpad 用指定的字符实现右填充指定长度 SELECT RPAD('殷素素',12,'ab') AS out_put; #9.replace 替换 SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put; #二、数学函数 #round 四舍五入 SELECT ROUND(-1.55); SELECT ROUND(1.567,2); #ceil 向上取整,返回>=该参数的最小整数 SELECT CEIL(-1.02); #floor 向下取整，返回<=该参数的最大整数 SELECT FLOOR(-9.99); #truncate 截断 SELECT TRUNCATE(1.69999,1); #mod取余 /* mod(a,b) ： a-a/b*b mod(-10,-3):-10- (-10)/(-3)*（-3）=-1 */ SELECT MOD(10,-3); SELECT 10%3; #三、日期函数 #now 返回当前系统日期+时间 SELECT NOW(); #curdate 返回当前系统日期，不包含时间 SELECT CURDATE(); #curtime 返回当前时间，不包含日期 SELECT CURTIME(); #可以获取指定的部分，年、月、日、小时、分钟、秒 SELECT YEAR(NOW()) 年; SELECT YEAR('1998-1-1') 年; SELECT YEAR(hiredate) 年 FROM employees; SELECT MONTH(NOW()) 月; SELECT MONTHNAME(NOW()) 月; #str_to_date 将字符通过指定的格式转换成日期 SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put; #查询入职日期为1992--4-3的员工信息 SELECT * FROM employees WHERE hiredate = '1992-4-3'; SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y'); #date_format 将日期转换成字符 SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put; #查询有奖金的员工名和入职日期(xx月/xx日 xx年) SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期 FROM employees WHERE commission_pct IS NOT NULL; #四、其他函数 SELECT VERSION(); SELECT DATABASE(); SELECT USER(); #五、流程控制函数 #1.if函数： if else 的效果 SELECT IF(10<5,'大','小'); SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注 FROM employees; #2.case函数的使用一： switch case 的效果 /* java中 switch(变量或表达式){ case 常量1：语句1;break; ... default:语句n;break; } mysql中 case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1; when 常量2 then 要显示的值2或语句2; ... else 要显示的值n或语句n; end */ /*案例：查询员工的工资，要求 部门号=30，显示的工资为1.1倍 部门号=40，显示的工资为1.2倍 部门号=50，显示的工资为1.3倍 其他部门，显示的工资为原工资 */ SELECT salary 原始工资,department_id, CASE department_id WHEN 30 THEN salary*1.1 WHEN 40 THEN salary*1.2 WHEN 50 THEN salary*1.3 ELSE salary END AS 新工资 FROM employees; #3.case 函数的使用二：类似于 多重if /* java中： if(条件1){ 语句1； }else if(条件2){ 语句2； } ... else{ 语句n; } mysql中： case when 条件1 then 要显示的值1或语句1 when 条件2 then 要显示的值2或语句2 。。。 else 要显示的值n或语句n end */ #案例：查询员工的工资的情况 如果工资>20000,显示A级别 如果工资>15000,显示B级别 如果工资>10000，显示C级别 否则，显示D级别 SELECT salary, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END AS 工资级别 FROM employees; #二、分组函数 /* 功能：用作统计使用，又称为聚合函数或统计函数或组函数 分类： sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数 特点： 1、sum、avg一般用于处理数值型 max、min、count可以处理任何类型 2、以上分组函数都忽略null值 3、可以和distinct搭配实现去重的运算 4、count函数的单独介绍 一般使用count(*)用作统计行数 5、和分组函数一同查询的字段要求是group by后的字段 */ #1、简单 的使用 SELECT SUM(salary) FROM employees; SELECT AVG(salary) FROM employees; SELECT MIN(salary) FROM employees; SELECT MAX(salary) FROM employees; SELECT COUNT(salary) FROM employees; SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees; SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees; #2、参数支持哪些类型 SELECT SUM(last_name) ,AVG(last_name) FROM employees; SELECT SUM(hiredate) ,AVG(hiredate) FROM employees; SELECT MAX(last_name),MIN(last_name) FROM employees; SELECT MAX(hiredate),MIN(hiredate) FROM employees; SELECT COUNT(commission_pct) FROM employees; SELECT COUNT(last_name) FROM employees; #3、是否忽略null SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees; SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees; SELECT COUNT(commission_pct) FROM employees; SELECT commission_pct FROM employees; #4、和distinct搭配 SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees; #5、count函数的详细介绍 SELECT COUNT(salary) FROM employees; SELECT COUNT(*) FROM employees; SELECT COUNT(1) FROM employees; 效率： MYISAM存储引擎下 ，COUNT(*)的效率高 INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些 #6、和分组函数一同查询的字段有限制 SELECT AVG(salary),employee_id FROM employees;
#进阶5：分组查询 /* 语法： select 查询列表 from 表 【where 筛选条件】 group by 分组的字段 【order by 排序的字段】; 特点： 1、和分组函数一同查询的字段必须是group by后出现的字段 2、筛选分为两类：分组前筛选和分组后筛选 针对的表 位置 连接的关键字 分组前筛选 原始表 group by前 where 分组后筛选 group by后的结果集 group by后 having 问题1：分组函数做筛选能不能放在where后面 答：不能 问题2：where——group by——having 一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率 3、分组可以按单个字段也可以按多个字段 4、可以搭配着排序使用 */ #引入：查询每个部门的员工个数 SELECT COUNT(*) FROM employees WHERE department_id=90; #1.简单的分组 #案例1：查询每个工种的员工平均工资 SELECT AVG(salary),job_id FROM employees GROUP BY job_id; #案例2：查询每个位置的部门个数 SELECT COUNT(*),location_id FROM departments GROUP BY location_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、分组后筛选 #案例：查询哪个部门的员工个数>5 #①查询每个部门的员工个数 SELECT COUNT(*),department_id FROM employees GROUP BY department_id; #② 筛选刚才①结果 SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>5; #案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 SELECT job_id,MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000; #案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资 manager_id>102 SELECT manager_id,MIN(salary) FROM employees GROUP BY manager_id HAVING MIN(salary)>5000; #4.添加排序 #案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序 SELECT job_id,MAX(salary) m FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING m>6000 ORDER BY m ; #5.按多个字段分组 #案例：查询每个工种每个部门的最低工资,并按最低工资降序 SELECT MIN(salary),job_id,department_id FROM employees GROUP BY department_id,job_id ORDER BY MIN(salary) DESC;