一 格式
select 函数名() [from 表名]
二 聚合函数(又称统计函数,分组函数),用来做统计
SUM() 求和,AVG() 求平均:只能处理数值型数据,忽略null值
MAX() 求最大值,MIN() 求最小值:能处理任何类型的数据,忽略null值
COUNT() 统计非null值的数量:能处理任何类型的数据,忽略null值
1. 单独使用
select SUM(salary) from employees as out_put; out_put=691400
select MAX(salary) from employees as out_put; out_put=24000
select MIN(salary) from employees as out_put; out_put=2100
select AVG(salary) from employees as out_put; out_put=6461.682243
select COUNT(salary) from employees as out_put; out_put=107
select SUM(salary) as 和,MAX(salary) 最大,MIN(salary) 最小, AVG(salary) 平均,COUNT(salary) as 数量 from employees;
2. 和DISTINCT配合使用
select SUM(DISTINCT salary) from employees; 先去重再计算总和
select COUNT(DISTINCT salary) from employees; 先去重再计算个数
其他的聚合函数也可以和DISTINCT配合使用
3. COUNT的详细介绍
select count(salary) from employees; #统计所有salary字段不为空的行数
select count(*) from employees; #统计表中所有行数,因为没一行都会至少有一个字段不为空,最常用
select count(1) from employees; #统计表中所有行数,它是给给表加一列1,统计总行数
4. 和聚合函数一起使用的字段有限制
select AVG(salary), employee_id from employees; X会报错
和聚合函数一起使用的字段一般是GROUP BY (后面会深入讲)后面的字段
三 单行函数,用来做处理
1. 字符函数
1)LENGTH(),用于获取参数的字节个数
select LENGTH("mary");
select LENGTH("杰克");
查询出来的结果和字符编码有关
2)CONCAT(),用于拼接字符串
select CONCAT("jack","loves","rose");
3) UPPER() 和 LOWER(),获取字符串的大小写
select UPPER("JACKrose");
select LOWER("JACKrose");select CONCAT(UPPER('Jack'),LOWER('Rose'));
4)SUBSTR(), 获取字符串的子串
要注意在mysql中字符串的索引是从1开始的,且SUBSTR() 函数包括首尾
select SUBSTR('helloworld',4) as out_put; out_put=‘loworld’ #截取从指定索引处及其后面所有子串
select SUBSTR('helloworld',2,4) as out_put; out_put='ello' #截取从指定索引处的指定长度的子串
5)INSTR(str1,str2),返回子串第一次出现的索引,如果没出现过则返回值为0
select INSTR('老鼠爱大米哈哈','大米') as out_put; out_put=4
select INSTR('老鼠爱大米哈哈大米','大米') as out_put; out_put=4
select INSTR('老鼠爱大米哈哈大米','小米') as out_put; out_put=0
6) TRIM(), 去掉字符串两端的指定字符,默认为去掉两端的空格
select TRIM(' 你今天学习了吗 ') as out_put; out_put='你今天学习了吗'
select TRIM('a' from 'aaaaa你今天学习了吗aaaa') as out_put; out_put='你今天学习了吗'
7) LPAD(str1,length,str2), 将用指定字符串str2将字符串str1左填充为指定长度的字符串
select LPAD('也有梦想',8,'咸鱼') as out_put; out_put='咸鱼咸鱼也有梦想'
select LPAD('也有梦想',7,'咸鱼') as out_put; out_put='咸鱼咸也有梦想'
select LPAD('也有梦想',2,'咸鱼') as out_put; out_put='也有'
8) RPAD(str1,length,str2), 将用指定字符串str2将字符串str1右填充为指定长度的字符串
select LPAD('也有梦想',8,'咸鱼') as out_put; out_put='也有梦想咸鱼咸鱼'
select LPAD('也有梦想',7,'咸鱼') as out_put; out_put='也有梦想咸鱼咸'
select LPAD('也有梦想',2,'咸鱼') as out_put; out_put='也有'
9)REPLACE(str,from_str,to_str), 替换,如果要替换的子串不存在,则不替换
select REPLACE('如果我是程序员你愿意嫁给我吗?','程序员','DJ') as out_put; out_put='如果我是DJ你愿意嫁给我吗?'
select REPLACE('如果我是程序员你愿意嫁给我吗?','宇航员','DJ') as out_put; out_put='如果我是程序员你愿意嫁给我吗?'
2. 数学函数
1)ROUND(X) 和 ROUND(X,D)
四舍五入,也可以做指定保留位数的四舍五入
select ROUND(1.65) as out_put; out_put=2
select ROUND(1.12) as out_put; out_put=1select ROUND(1.9243,2) as out_put; out_put=1.92
2) CEIL(X), 向上取整,取大于等于X的最小整数
FLOOR(X),向下取整,取小于等于该数的最大整数
select CEIL(1.93) as out_put; out_put=2
select CEIL(-2.76) as out_put; out_put=-2select CEIL(1.00) as out_put; out_put=1
3)TRUNCATE(X,D),按照指定位数截断
select TRUNCATE(1.9342,2) as out_put; out_put=1.93
select TRUNCATE(-1.9342,2) as out_put; out_put=-1.93
4) MOD(X,Z) 取余 a%b=a/b*b
select MOD(10,3) as out_put; out_put=1
select MOD(-10,3) as out_put; out_put=-1
select MOD(-10,-3) as out_put; out_put=-1
select MOD(10,-3) as out_put; out_put=1
3. 日期函数
1)NOW() 返回当前日期和时间
select NOW() as out_put; out_put=2020-04-08 21:15:27
2) CURDATE() 返回当前日期
select CURDATE() as out_put; out_put=2020-04-08
3) CURTIME() 返回当前时间
select CURTIME() as out_put; out_put=21:15:27
4)YEAR() MONTH() MONTHNAME() DAY() HOUR() MINUTE() SECOND获取指定的年、月、日、时、分、秒
select YEAR(NOW()) as out_put; out_put=2020
select YEAR('2020-04-08 21:15:27') as out_put; out_put=2020
select MONTH('2020-04-08 21:15:27') as out_put; out_put=4
select MONTHNAME('2020-04-08 21:15:27') as out_put; out_put=April
......
5) DATEDIFF(expr1,expr2),求两个日期相差的天数
select DATEDIFF(MAX(hiredate),MIN(hiredate)) as difference from employees;
6) STR_TO_DATE(str,format) 将指定格式的字符串转换为日期
select STR_TO_DATE('09-13-2020','%m-%d-%Y') as 月日年; 月日年=2020-09-13
这个函数是有实际的应用场景的,比如我们要在employees表中查询出来入职日期为2015年7月18日的员工信息,那我们可能是从网页上获得用户输入的这个值,该值是字符串类型的,字符串类型的值有一种自动转换格式,即‘1993-10-12’年月日,如果符合这个格式,可以自动转换,否则需要我们手动转换
7)DATE_FORMAT(date,format) 将日期转换为指定格式的字符串
select DATE_FORMAT(NOW(),'%d日--%m月--%Y年') as out_put; out_put=08日--04月--2020年
实际应用场景如下:
select last_name,DATE_FORMAT(hiredate,'%Y年%m月%d日') as 入职日期 from employees where commission_pct is not null;
4. 流程控制函数(补充)
1) IF(expr1,expr2,expr3) ,能达到IF...ELSE的效果
select IF(10>5,'大','小') as out_put; out_put='大'
select last_name,commission_pct, IF(commission_pct is null,'没奖金,需要努力啊', '有奖金,再接再厉啊') as 备注 from employees;
2)CASE表达式有两种使用情况
![]()
select #案例1
last_name, department_id, salary as 原工资,
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 order by department_id;select last_name,salary, #案例2
CASE
WHEN salary>20000 THEN 'A级别'
WHEN salary>15000 THEN 'B级别'
WHEN salary>10000 THEN 'C级别'
ELSE 'D级别'
END as 工资级别
from employees order by salary DESC;
5. 其他函数(补充)
1)VERSION() 数据库的版本
select VERSION() as version; version=8.0.11
2) DATABASE() 当前使用的数据库
select DATABASE() as database; database=myemployees
3) USER()
select USER() as user; user=root@localhost