Mysql基础（二）函数
一、合计/统计函数
1、count 函数
语法：
select count(*)|coount(列名) from table_name [where where_definition]
示例：
-- 统计一个班级一共有多少学生
select count(*) from `student`;
-- 统计数学成绩大于90的学生人数
select count(*) from `student` where math > 90
-- 统计总分大于250的人数
select count(*) from `student`
where (math+chinese+english) > 250
-- count(*) 和 count(列名) 的区别
create table t16(
`name` varchar(32)
)
insert into t16 values('mysql'),('java'),('html'),(null)
select * from t16
-- 返回的是4条记录
select count(*) from t16
-- 返回的是3条记录
select count(`name`) from t16
说明：count(*) 和 count(列名) 的区别
count(*) 返回满足条件的记录的行数
count(列名) 统计满足条件的某列有多少个，但是会排出为null的列
2、sum 函数
语法：
select sum(列名){,sum(列名)...} from table_name
[where where_definition]
sum函数返回满足where条件的行的和，一般使用在数值列
示例代码：
-- 统计一个班级数学总成绩
select sum(`math`) from `student`
-- 统计一个班级语文、数学、英语各科总成绩
select sum(`chinese`),sum(`math`),sum(`english`) from `student`
-- 统计一个班级语文、数学、英语的成绩总和
select sum(chinese+english+math) from `student`
-- 统计一个班级的语文成绩平均分
select sum(`chinese`)/count(*) from `student`
注意：
1、sum仅对数值起作用，其他类型数据没有意义
2、对多列求和 “,” 号不能少
3、avg 函数
avg函数返回满足where条件的一列平均值
select avg(列名){,avg(列名)...} from table_name
[where where_definition]
示例代码：
-- 求一个班级数学的平均分
select avg(`math`) from `student`
-- 求一个班级总分平均分
select avg(chinese+math+english) from `student`
4、max / min 函数
max / min 函数返回满足where条件的一列的最大 / 最小值
语法：
select max(列名) | min(列名) from table_name
[where where_definition]
示例代码：
-- 求一个班级总分的最高分和最低分
select max(math+english+chinese),min(math+chinese+english) from `student`
二、分组统计
使用 group by 子句对列进行分组
select column1,column2,column3... from table_name
group by column
使用 having 子句对分组后的结果进行过滤
select column1,column2,column3... from table_name
group by column
having ...
group by 用于对查询结果分组统计
having 子句用于限制分组显示结果
测试数据和测试表
-- 部门表
create table dept(
depton mediumint unsigned not null default 0,
dname varchar(32) not null default '',
loc varchar(13) not null default ''
);
insert into dept values
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON')
select * from dept
-- 员工表
create table emp(
empon mediumint unsigned not null default 0,
ename varchar(32) not null default '',
job varchar(32) not null default '',
mgr mediumint unsigned,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2),
depton mediumint unsigned not null default 0
);
insert into emp values(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7968,'1991-2-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7968,'1991-9-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1991-4-19',3000.00,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30),
(7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30),
(7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1991-1-23',1300.00,NULL,10);
select * from emp
-- 工资级别表
create table salgrade(
grade mediumint unsigned not null default 0,
losal decimal(17,2) not null,
hisal decimal(17,2) not null
);
insert into salgrade
values(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999)
select * from salgrade
示例代码：
-- 显示每个部门的平均工资和最高工资
select avg(sal),max(sal),depton from `emp` group by depton
-- 显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),job,depton from `emp` group by depton,job
-- 显示平均工资低于2000的部门号和它的平均工资
select depton,avg(sal) from `emp` group by depton having avg(sal) < 2000
-- 使用别名
select depton,avg(sal) as avg_sal from `emp` group by depton having avg_sal < 2000
三、字符串函数
字符串相关函数
| 函数 | 解释 |
|---|---|
| charset(str) | 返回字串字符集 |
| concat(string2 [,...]) | 连接字串 |
| instr(string,substring) | 返回substring在string中出现的位置，没有返回0 |
| ucase(string2) | 转换成大写 |
| lcase(string2) | 转换成小写 |
| left(string2,length) | 从string2中的左边起取length个字符 |
| length(string) | string长度 [按照字节] |
| replace(str,search_str,replace_str) | 在stri中用replace_str替换search_str |
| strcmp(string1,string2) | 逐字符比较两字串大小 |
| substring(str,position [,length]) | 从str的position开始 [从1开始计算]，取length个字符 |
| ltrim(string) rtrim(string2) trim | 去除前端空格或后端空格 |
示例代码：
-- charset(str)返回字串字符集
select charset(`ename`) from `emp`
-- concat(string2 [,....]) 连接字符串，将多个列拼接为一列
select concat(`ename`, ' job is ' , `job`) from `emp`
-- instr(string,substring) 返回substring在string中出现的位置，没有返回0
-- dual 亚元表，系统表，可以作为测试表使用
select instr('xixihaha','ha') from dual
-- ucase(string2) 转换成大写
select ucase(ename) from `emp`
-- lcase(string2) 转换成小写
select lcase(ename) from `emp`
-- left(string2,length) 从string2中的左边起取length个字符
select left(ename,2) from `emp`
-- right(string2,length) 从string2中的右边起取length个字符
select right(ename,2) from `emp`
-- length(string) string长度[按照字节计算]
select length(ename) from `emp`
-- replace(str,search_str,replace_str) 在str中用replace_str替换search_str
select replace(job,'MANAGER','经理') from `emp`
-- strcmp(string1,string2) 逐字符比较两字串大小
select strcmp('hello','world') from dual
-- substring(str,position[,length])从str的position开始 [从1开始计算]，取length个字符
select substring(ename,1,2) from `emp`
-- ltrim(string2)去掉左边的空格
select ltrim(' 学习mysql') from dual
-- rtrim(string2)去掉右边的空格
select rtrim('学习mysql ') from dual
-- trim(string2) 去掉左右两边的空格
select trim(' 学习mysql ') from dual
练习：以首字母小写的方式显示所有员工表的名字
-- 第一种方式：将员工名字首字母提取出来，先转为小写，再取出员工名字剩下的部分，进行拼接
select concat(lcase(left(ename,1)),substring(ename,2)),`ename` from `emp`
-- 第二种方式：将员工名字的首字母替换为小写后的员工名字首字母
select replace(ename,left(ename,1),lcase(left(ename,1))),`ename` from `emp`
四、数学函数
数学相关函数
| 函数 | 说明 |
|---|---|
| abs(num) | 绝对值 |
| bin(decimal_number) | 十进制转二进制 |
| ceiling(num) | 向上取整，得到比num大的最小整数 |
| conv(num2,from_base,to_base) | 进制转换 |
| floor(num) | 向下去整，得到比num小的最大整数 |
| format(num,decimal_places) | 保留小数位数 |
| hex(decimal_num) | 转十六进制 |
| least(number1,number2 [,...]) | 求最小值 |
| mod(numberator,denominator) | 求余 |
| rand([seed]) | 返回一个随机数，rand([seed])其范围为 0<=v<=1.0 |
示例代码：
-- abs(num) 绝对值
select abs(-10) from dual
-- bin(decimal_num) 十进制转为二进制
select bin(3) from dual
-- ceiling(num) 线上取整，得到比num大的最小整数
select ceiling(2.5) from dual
-- conv(num,from_base,to_base) 进制转换
select conv(1010,2,10) from dual
-- floor(num) 向下取整，得到比num小的最大整数
select floor(10.5) from dual
-- format(num,decimal_places) 保留小数位
select format(10.567876454,2) from dual
-- hex(decimal_num)转十六进制
select hex(10) from dual
-- least(num,num2 [,...]) 求最小值
select least(1,2,3,4,5,6,7,8) from dual
-- mod(numberator,denominator) 求余
select mod(4,3) from dual
-- rand([seed]) 返回一个随机数
select rand() from dual
-- 如果加上一个seed，那么随机数每次返回都是一致，就不会变化
select rand(3) from dual
五、时间日期函数
时间日期相关函数
| 函数 | 说明 |
|---|---|
| current_date() | 当前日期 |
| currnet_time() | 当前时间 |
| current_timestamp() | 当前时间戳 |
| date(datetime) | 返回datetime的日期部分 |
| date_add(date2,interval d_valued_type) | 在date2中加上日期或时间 |
| date_sub(date2,interval d_valued_type) | 在date2上减去一个时间 |
| datediff(date1,date2) | 两个日期差（结果是天数） |
| timediff(date1,date2) | 两个时间差（返回时分秒） |
| now() | 当前时间 |
| year | month | date (datetime) from_unixtime() | 年月日 |
示例代码第一部分：
-- current_date() 当前日期
select current_date() from dual
-- current_time() 当前时间
select current_time() from dual
-- current_timestamp() 当前时间戳
select current_timestamp() from dual
-- now() 当前时间
select now() from dual
-- date(datetime) 返回datetime的日期部分
select date('2022-08-25 14:57:47') from dual
示例代码第二部分：
-- 测试数据
create table mes(
id int,
content varchar(32),
send_time datetime
);
insert into mes values(1,'北京新闻',current_timestamp())
insert into mes values(2,'上海时间',now())
insert into mes values(3,'广州新闻',now())
select * from mes
-- 显示所有的留言信息，发布日期只显示日期，不显示时间
select id,content,date(send_time) from mes
-- 查询在10分钟内发布的新闻，
-- 思路：在发送时间的基础之上加上10分钟，再与现在的时间进行比较
select * from mes
where date_add(send_time, interval 10 minute) >= now()
select * from mes
where date_sub(now(),interval 10 minute) <= send_time
-- 求出2011-11-11 和 1991-1-1 相差多少天
select datediff('2011-11-11','1991-1-1') from dual
-- 求出你的生日到现在的天数
select datediff(now(),'1990-11-11') / 365 from dual
-- 求出你现在到80岁的天数
-- 思路：先求出活到80岁时的时间
-- 然后使用datediff(x,now())
select datediff(date_add('1990-11-11',interval 80 year),now()) from dual
-- timediff() 两个时间差（返回时分秒）
select timediff('10:10:10','02:00:00') from dual
说明：
1、date_add() 中的interval 后面可以是 year、day、hour、minute、second等
2、date_sub() 中的interval 后面可以是 year、day、hour、minute、second等
3、datediff(date1,date2)得到的是天数，而且是 date1 - date2的天数，因此可以取负数
4、这四个函数的日期类型可以使 date、datetime 或者 timestamp
示例代码第三部分：
在实际开发中，我们也经常用 int 来保存一个 unix 时间戳，然后使用 from_unixtime() 进行转换，还是非常有实用价值的
-- year() 取出当前时间的年
select year(now()) from dual
-- month() 取出当前时间的月
select month(now()) from dual
-- day() 取出当前时间的日
select day(now()) from dual
-- unix_timestamp() 返回的是1970-1-1 到现在的毫秒数
select unix_timestamp() from dual
select unix_timestamp()/ (24*3600*365) from dual
-- from_unixtime() 可以把unxi_timestamp()转为一个日期，也就是将时间戳转为一个日期
select from_unixtime(unix_timestamp()) from dual
-- %Y-%m-$d 格式是规定号的，表示年月日
select from_unixtime(unix_timestamp(),'%Y-%m-%d') from dual
select from_unixtime(unix_timestamp(),'%Y-%m-%d %H:%m:%s') from dual
六、加密和系统函数
| 函数 | 说明 |
|---|---|
| user() | 查询用户 |
| database() | 数据库名称 |
| md5(str) | 为字符串str算出一个MD5 32的字符串，（用户密码加密） |
| password(str) | 从原文密码 str 计算并返回密码字符串，通常用于对mysql数据库的用户密码加密 |
示例代码：
-- user() 查询当前登录到mysql的用户和ip
select user() from dual
-- database() 查询当前正在使用的数据库
select database() from dual
-- md5 常用与加密 md5加密后的密码一定是32位的
select md5('121345') from dual
-- password(str) 从原文密码 str 计算并返回密码字符串，通常用于对mysql数据库的用户密码加密
select password('12345') from dual
七、流程控制函数
| 函数 | 说明 |
|---|---|
| if(expr1,expr2,expr3) | 如果expr1为true，则返回expr2，为false，则返回expr3 |
| ifnull(expr1,expr2) | 如果expr1不为null，返回expr1，否则返回expr2 |
| select case when expr1 then expr2 when expr3 then expr4 else expr5 end; [类似多重分支] | 如果expr1为true，则返回expr2，如果expr2位true，则返回expr4，否则返回expr5 |
示例代码：
-- if(expr1,expr2,expr3)
select if(true,'上海','北京') from dual
select if(false,'上海','北京') from dual
-- ifnull(expr1,expr2)
select ifnull('tom','上海') from dual
select ifnull(null,'上海') from dual
-- 多重分支
select case
when true
then 'java'
when false
then 'tom'
else 'mary'
end
-- 查询emp表，如果comm是null，则显示 0.0
-- 判断是否为null，要使用is null，判断不为空 使用 is not
select ename,if(comm is null,0.0,comm) from emp
select ename,ifnull(comm,0.0) from emp
-- 如果emp表的job是CLERK则显示职员，如果是MANAGER则显示经理
-- 如果是SALESMAN 则显示 销售人员，其他显示正常
select ename,
(
select case
when job = 'CLERK' then '职员'
when job = 'MANAGER' then '经理'
when job = 'SALESMAN' then '销售人员'
else job
end
)
from emp
