MySql-函数查询

分组函数

• 说明：分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数
• 分组函数清单：
  • sum(字段名)：求和
  • avg(字段名)：求平均数
  • max(字段名)：求最大值
  • min(字段名)：求最小值
  • count(字段名)：计算非空字段值的个数
• 特点：
  • sum,avg一般用于处理数值类型， max,min,count可以处理任何类型，以上分组函数都忽略Null值，可以和distinct搭配实现去重的运算
  • count函数的单独介绍，一般使用count(*)用作统计行数
  • 和分组函数一同查询的字段要求是group by后的字段

简单实用

• 迭代

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),ROUND(AVG(salary),2),MAX(salary),MIN(salary),COUNT(salary) FROM employees;

• 参数支持那些类型

SELECT SUM(last_name),AVG(last_name) FROM employees;
SELECT SUM(hiredate),AVG(hiredate) FROM employees;

SELECT MAX(hiredate),MIN(hiredate) FROM employees;// 2016-03-03 00:00:00 1992-04-03 00:00:00
SELECT MAX(last_name),MIN(last_name) FROM employees; 

SELECT COUNT(hiredate) FROM employees; //107
SELECT COUNT(commission_pct) FROM employees; //35

• 是否忽略null值 sum忽略null值

SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,
SUM(commission_pct)/107 FROM employees;//7.80    0.222857 0.222857   0.072897

SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;

SELECT COUNT(commission_pct) FROM employees;

• 和distinct搭配使用

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; // 397900.00      691400.00 

SELECT COUNT(DISTINCT salary) FROM employees;  //57

• count函数的详细介绍

SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;//统计总行数

SELECT COUNT(1) FROM employees;

SELECT COUNT(2) FROM employees;

效率：
MYISAM:存储引擎下,COUNT(*)的效率高
INNODB:存储引擎下,COUNT(*)和COUNT(1)的效率比COUNT('字段')要高一些

• 和分组函数一同查询的字段由限制

SELECT AVG(salary),`employee_id` FROM employees;

常见函数的学习

• 单行函数
• 字符函数
  • concat
  • substr
  • length（str）
  • char_length
  • per
  • lower
  • trim
  • left
  • right
  • lpad
  • rpad
  • instr
  • strcmp
• 数学函数
  • abs
  • ceil
  • floor
  • round
  • truncate
  • mod
• 日期函数
  • now
  • curtime
  • curdate
  • datediff
  • date_format
  • str_to_date
• 流程控制函数
  • if
  • Case

练习

• length 获取参数值的字节个数

SELECT LENGTH(’john‘);

• 拼接字符串concat

SELECT CONCAT(last_name, '_', first_name) FROM employees;

• 大小写转换

SELECT UPPER(last_name) FROM employees;
SELECT LOWER("joHn");

• 截取字符串，索引从1开始
• 从指定位置开始截取到末尾

SELECT SUBSTR("hello", 3); // llo

• 从指定位置开始截取指定个数

SELECT SUBSTR("hello", 1, 2); // he

• 子串第一次出现的位置，找不到返回0

SELECT INSTR('hello world', 'wor') AS out_put; // 7

• 删除前后字符
• 删除空格

SELECT LENGTH(TRIM('   你好   ')) AS out_put; // 6

• 删除指定字符trim

SELECT TRIM('a' FROM 'aaaa你好aaaa你aaaaa') AS out_put;
SELECT TRIM('ab' FROM 'aba你好babab') AS out_put; // a你好b

• 用指定的字符实现左填充指定长度lpad

SELECT LPAD("hello", 10, '*');
SELECT RPAD("hello", 10, '*');

• 替换

SELECT REPLACE("I love U U U...", 'U', 'you');

数学函数

• 四舍五入

SELECT ROUND(1.55);
SELECT ROUND(1.55, 1);

• 向上取整

SELECT CEIL(1.01);

• 向下取整

SELECT FLOOR(1.01);

• 截断

SELECT TRUNCATE(1.69999, 1); // 1.6

• 取模

SELECT MOD(10, 3);//1
SELECT 10%3;

日期函数

• now 返回当前系统日期+时间

SELECT NOW(); //2020-11-15 21:40:26

• curdate 返回当前系统日期，不包含时间

SELECT CURDATE(); //2020-11-15

• curtime 返回当前时间，不包含日期

SELECT CURTIME();

• 可以获取指定的部分，年，月，日，小时，分钟，秒

SELECT YEAR(NOW()); //2020

SELECT YEAR(hiredate) FROM employees;

SELECT MONTH(NOW()); //2020

• str_to_date 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('2020-11-5','%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日');

• 查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT `last_name`,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') FROM employees WHERE `commission_pct` IS NOT NULL;

流程控制函数

• 显示系统时间(注：日期+时间)
• 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）

SELECT employee_id,last_name,salary,salary*1.2 "new salary"
FROM employees;

• 将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT LENGTH(last_name) 长度
FROM employees
ORDER BY SUBSTR(last_name,1,1) ASC;

• 做一个查询，产生下面的结果

  • 结果：

  <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)  "Dream Salary"
  FROM employees;
  

• 使用 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 Grade

FROM employees;