（三）MySQL基础——常见函数

概念：类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

好处：1、隐藏了实现细节；2、提高代码的重用性

实现：

SELECT 函数名(实参列表) FROM 表;

分类：

1、单行函数：CONCAT、LENGTH、IFNULL等；

2、分组函数：具有统计功能的作用，所以分组函数又称为统计函数或聚合函数或组函数。

 

单行函数：

（1）字符函数：索引从1开始，不是从0开始的

-- 1、LENGTH 获取参数值的字节个数
SELECT LENGTH('JOIN');

-- 2、CONCAT 拼接字符串（不局限于两个字符串的数量）
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees; 

-- 3、UPPER 字母变大写 \ LOWER 字母变小写 
SELECT UPPER('join');   示例结果为：JOIN
SELECT LOWER('JOin');   示例结果为：join

-- 示例：将姓变大写，名变小写，然后拼接。
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;   示例结果为：李莫愁

-- 5、INSTR 返回子串第一次出现的索引，如果找不到则返回0   
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;   示例结果为7    

-- 6、TRIM 只能去除前后空格或者想要去除的字符或字符串 
SELECT TRIM('    张翠山    ') AS out_put;    

SELECT TRIM('A' FROM 'AAAAA张AAAAA翠山AAAAA') AS out_put;   示例结果为：张AAAAA翠山

-- 7、LPAD 用指定的字符实现左填充指定长度   示例结果为：*******殷素素    
SELECT LPAD('殷素素',10,'*') AS out_put;  

SELECT LPAD('殷素素',2,'*') AS out_put;    示例结果为：殷素

-- 8、RPAD  用指定的字符实现左填充指定长度 
SELECT LPAD('殷素素',12,'ab') AS out_put;      示例结果为：殷素素ababababa

-- 9、REPLACE 全部替换 
SELECT REPLACE('张无忌爱上了周芷若，周芷若，周芷若','周芷若','赵敏') AS out_put;    示例结果为：张无忌爱上了赵敏，赵敏，赵敏

（2）数学函数：一般第二个参数的含义都表示为小数点后保留几位

-- 1、ROUND 四舍五入  不管是正数还是负数，先根据绝对值四舍五入，再加上正负号  
SELECT RONUD(-1.55);      示例结果为：-2
-- 重载 第二个参数为，小数点后保留几位  
SELECT ROUND(1.5678,2);   示例结果为：1.57

-- 2、CEIL 向上取整,返回>=该参数的最小整数   
SELECT CELL(1.0002);      示例结果为：2

SELECT CELL(-1.0002);     示例结果为：-1

-- 3、FLOOR 向下取整，返回<=该参数的最大整数  
SELECT FLOOR(-9.99);      示例结果为：-10

-- 4、TRUNCATE 截断 第二个参数为，小数点后保留几位 
SELECT TRUNCATE(1.6999,1);    示例结果：1.6

-- 5、MOD 取余 跟使用%效果一样 被除数为正数则结果为正数，反之为负数
SELECT MOD(-10,-3);       示例结果：-1

补充MOD取余：被除数为正数则结果为正数，反之为负数

mod(a,b) 其实就相当于：a-a/b*b 这个代数式 而/表示取整的含义

示例：mod(-10,-3) ： -10 - （-10）/（-3）*（-3）=====》-10-3*（-3）=====》-10-（-9）=====》-1

（3）日期函数：

-- 1、NOW 返回当前系统日期+时间
SELECT NOW();

-- 2、CURDATE 返回当前系统日期，不包括时间
SELECT CURDATE();

-- 3、CURTIME 返回当前系统时间，不包括日期
SELECT CURTIME();

-- 4、可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW())  年;
SELECT MONTH(NOW())  月;
-- 英文单词表示的英文
SELECT MONTHNAME(NOW())  月；

-- 5、STR_TO_DATE 将日期格式的字符转换成指定格式的日期   
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y') AS out_put;   示例结果为：1999-09-13

-- 6、DATE_FORMAT  将日期转换成字符
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日') AS out_put;   示例结果为：2018年06月06日    

格式符	功能
%Y	4位的年份
%y	2位的年份
%m	月份（01，02，....11,12）
%c	月份（1，2，....11,12）
%d	日（01，02，....）
%H	小时（24小时制）
%h	小时（12小时制）
%i	分钟（00，01，....59）
%s	秒（00，01....59）

（4）其他函数：

-- 当前数据库服务器的版本
SELECT VERSION();   
-- 当前打开的数据库
SELECT DATABASE();
-- 当前用户
SELECT USER();
-- 返回该字符的密码形式,即自动加密
SELECT PASSWORD('字符');
-- 返回该字符的md5加密形式
SELECT MD5('字符');

（5）流程控制函数：

学习mysql中CASE的使用格式一：

CASE    要判断的字段或表达式

WHEN    常量1   THEN   要显示的值1或语句1；

WHEN    常量2   THEN   要显示的值2或语句2；

....

ELSE    要显示的值N或语句N；

END

学习mysql中CASE的使用格式二：

CASE    

WHEN    条件1   THEN   要显示的值1或语句1；

WHEN    条件2   THEN   要显示的值2或语句2；

...

ELSE    要显示的值N或语句N；

END

-- 1、IF函数：能实现if else 的效果
SELECT IF(10>5,'大','小');    示例结果为：大

-- 2、CASE函数的使用一：相当于 switch case 的效果
-- 案例：查询员工的工资，要求如下：部门号=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 
    emlpoyees;

-- 3、CASE函数的使用一：相当于 多重if 的效果
-- 案例：查询员工的工资情况，要求如下：如果工资>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;

回顾 swicth 在java中的使用格式：  适用于等值判断

switch（变量或表达式）{

case 常量1：语句1； break；

...

default：语句n； break；

}

回顾 多重fi 语句在java中的使用格式：  进行区间判断

if(条件1){

语句1；

}else if(条件2){

语句2；

}

...

else{

语句N；

}

---

分组函数：

分类：SUM 求和、AVG 求平均值、MAX 求最大值、MIN 求最小值、 COUNT 计算个数

特点：

1、SUM \ AVG一般用于处理数值型的参数，MAX \ MIN \COUNT 可以处理任何类型的参数

2、是否忽略NULL值：是的，以上分组函数都忽略NULL值

3、可以和DISTINCT搭配

4、一般使用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;

（2）参数支持哪些类型：要根据不同函数的功能，传入的参数要有意义才行

（3）是否忽略NULL值：NULL+任何值都为NULL，但是所有的分组函数都忽略NULL值

（4）和DISTINCT搭配：

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

（5）count函数的详细介绍：这个函数有4种重载

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;