SQL-常见函数

作者: 晓晓桑 | 来源:发表于2020-06-01 19:50 被阅读0次

调用：select 函数名() [from 表];
分类：

1. 单行函数：如concat、length、ifnull等。
  分类：
- 字符函数：传入参数类型是字符型
- 数学函数：传入参数类型是数字型
- 日期函数：传入参数类型是日期型
- 其他函数
- 流程控制函数
1. 分组函数（又叫：统计函数/聚合函数/组函数）：做统计使用

image.png

常见函数

一.字符函数

length 、concat 、substr 、instr、upper、lower、lpad、rpad、replace

1.length

SELECT LENGTH('join');-- 4
SELECT LENGTH('张三丰');-- 9

SHOW VARIABLES LIKE '%char%'; -- character_set_client utf8mb4。因为是uft8所以一个中文字符占3个字节。如果gbk的话，一个中文字符占2个字节。

2.concat 拼接字符

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

3.upper、lower

SELECT UPPER('john'); -- JOHN
SELECT LOWER('JOHN'); -- john

示例：将姓变大写，名变小写，然后拼接。

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

4.substr、substring

-- 1. 截取从指定索引处后面所有的字符
-- 注意：索引从1开始，不是从0开始。索引从pos开始
SELECT SUBSTR('传闻中的陈芊芊',5) 名字; -- 陈芊芊
-- 2. -- 截取从指定索引处指定长度的字符
-- 1:是pos，2：是长度从1开始，截取2个
SELECT SUBSTR('传闻中的陈芊芊',1,2) 名字; -- 传闻

案例：名字中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) 名字
FROM employees;

5.instr 、

-- 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('传闻中的陈芊芊陈芊芊','陈芊芊') as 输出; -- 5

6.trim

-- 去前后空格
SELECT TRIM(' 陈芊芊 ') as 输出;-- 陈芊芊
-- 去前后指定字符
SELECT TRIM('a' FROM 'aaaaaaaaa陈aaa芊芊aaaaaaaaa') as 输出; -- 陈aaa芊芊

7.lpad

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

SELECT LPAD('陈芊芊',10,'*')  输出; -- *******陈芊芊
SELECT LPAD('陈芊芊',2,'*')  输出; -- 陈芊

8.rpad

-- 用指定的字符实现右填充指定的长度

SELECT RPAD('陈芊芊',10,'*')  输出; -- 陈芊芊*******
SELECT RPAD('陈芊芊',2,'*')  输出; -- 陈芊

9.replace

SELECT REPLACE('传闻中的陈芊芊和陈芊芊','陈芊芊','陈小千') as 输出; -- 传闻中的陈小千和陈小千

二.数学函数

round、ceil、floor、truncate、mod、rand

1.round 四舍五入

-- 不管是正负，取绝对值四舍五入，再加正负号就行
SELECT ROUND(1.467); -- 1

-- 小数点后保留2位数
SELECT ROUND(1.467,2);-- 1.47

2.ceil 向上取整

-- 返回大于等于该参数的最小整数
SELECT CEIL(1.001); -- 2
SELECT CEIL(-1.001); -- -1

3.floor 向下取整

-- 返回小于等于该参数的最小整数
SELECT FLOOR(1.001); -- 1
SELECT FLOOR(-1.001); -- -2

4.truncate 截断

-- 小数点后保留1位
SELECT TRUNCATE(1.69,1);-- 1.6

5.mod 取余

-- mod(a,b); a-a/bb（/是取整）：被除数-整数商除数=余数
SELECT MOD(10,3); -- 1 相当于：10%3
SELECT MOD(-10,-3); -- -1
SELECT MOD(10,-3); -- 1
SELECT MOD(10,-3.0); -- 1.0

6.rand获取随机数。

返回0-1直接的小数

三.日期函数

now、currentdate、currenttime、year、month、day、hour、minute、second、str_to_date、date_format、datediff、monthname

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

SELECT NOW();-- 2020-06-02 14:14

2.current_date 返回当前系统日期，不包含时间

SELECT CURRENT_DATE();-- 2020-06-02

3.current_time 返回当前时间时间，不包含日期

SELECT CURRENT_TIME(); -- 14:16:41

获取年、月、日、时、分、秒

年

SELECT YEAR(NOW());-- 2020
SELECT YEAR('1993-06-01');-- 1993
SELECT YEAR(hiredate) 年 FROM employees;

月

SELECT MONTH(NOW());-- 6
SELECT MONTHNAME(NOw());-- June

日

SELECT DAY(NOW());

时

SELECT HOUR(NOW());

分

SELECT MINUTE(NOW());

秒

SELECT SECOND(NOW());

str_to_date 字符转日期

-- 第一个参数str格式是第二个参数‘%Y-%c-%d’的格式
SELECT STR_TO_DATE('2020-6-2','%Y-%c-%d') as 输出; -- 2020-06-02

查询入职日期为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日"); -- 2020年06月02日

查询有奖金的员工名和入职日期（XX月/XX日 XX年）

SELECT last_name,DATE_FORMAT(hiredate,'%c月/%d日 %Y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL ;

datediff 两个日期相差的天数

monthname 以英文形式返回月

四.其他函数

SELECT VERSION(); -- 8.0.11
SELECT DATABASE();-- myemployees
SELECT USER();-- root@localhost
password('str'); -- 字符加密
MD5('str'); -- md5加密

五.流程控制函数

1.if函数

if(条件表达式，表达式1，表达式2)：如果表达式成立，返回表达式1，否则返回表达式2。

SELECT IF(10>8,'大','小'); -- 大

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，哈哈') 
FROM employees;

2.case函数

使用一：switch case的效果

case 要判断的字段或表达式
when 常量1 then 要显示的值1或者语句1;
when 常量1 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 -- then 后面是值的话不加;,如果是语句就加;
    WHEN 40 THEN salary*1.2
    WHEN 50 THEN salary*1.3
    ELSE salary
    END AS 新工资 
FROM employees;

case的使用二有点类似多重if

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;

案例1.显示系统时间（日期+时间）

SELECT NOW();

案例2.查询员工号、姓名、工资，以及工资提高百分之20后的结果（new salary）

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

案例3.将员工的姓名按照首字母排序，并写出姓名的长度

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

案例4.做一个查询，产生下面的结果

-- <lastname> earns <salary> monthly but wants <salary3>
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary3) FROM employees;

分组函数

/*
功能：用作统计使用，又称为聚合函数或者统计函数或者组函数

分类：
sum avg max min count
特点：

sum、avg只处理数值型
max、min、count可以处理任何类型
以上分组函数都忽略null值
可以和distinct搭配实现去重的运算
一般用count(*)看行数
和分组函数一同查询的字段要求是group by后的字段
*/

1.简单使用

SELECT SUM(salary) FROM employees; -- 691400.00
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees; -- 只支持不为null的个数

SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最大,MIN(salary) 最小,COUNT(salary) 个数
FROM employees;

SELECT ROUND(AVG(salary)) FROM employees;

2.参数支持的类型

-- sum
SELECT SUM(last_name) FROM employees;

3.和distinct搭配

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

4.count函数的详细

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees; -- 107 总行数
SELECT COUNT(1) FROM employees;-- 107 总行数

-- 效率： 
-- MYISAM存储引擎下， COUNT(*)的效率最高
-- INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，但是比COUNT(字段)要高一点

5.和分组函数一同查询的字段有限制，要求是group by后的字段

-- employee_id没有意义
SELECT AVG(salary),employee_id FROM employees;

案例1.查询员工表中最大入职时间和最小入职时间相差的天数 DATEDIFF

SELECT DATEDIFF(MAX(hiredate),Min(hiredate))
FROM employees;

案例2.查询部门编号为90的员工个数

SELECT COUNT(*) 
FROM employees
WHERE employee_id=90;

网友评论

本文标题：SQL-常见函数

本文链接：https://www.haomeiwen.com/subject/kaaszhtx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！