MySQL学习笔记:单行函数【字符函数(concat, substr,instr,trim,lpad,replace)、数学函数、日期函数(str_to_date、date_format)练习】和分组函数【sum,avg,min,max,count】练习

1、字符函数:
/*
进阶4: 常见函数学习 函数含义:封装常用的有特定功能的一组逻辑语句,对外暴露方法名; 好处:隐藏实现细节;提高代码重用性 调用方法:select 函数名(参数) 【from 表名】 备注: 如果参数是取自特定表, 才需要加from 表名 特点:函数名;功能; 常见函数: ①单行函数,concat(), length(), ifnull() ②分组函数(统计函数、聚合函数、组函数),做统计使用: 区别: 单行函数传入参数为一个值;分组函数传入参数为一组值 */ # 单行函数: 字符函数、数学函数、日期函数、其他函数、流程控制函数 # 字符函数: # 1、length(str):获取参数的字节数 select length('john') # 返回4
select length('张三jsjsjs') # 返回3*2+6 = 12
# utf8编码方式中:一个汉字占3个字节, 一个字母占一个字节(mysql使用utf8)
# DBK编码方式中:一个汉字占2个字节, 一个字母占一个字节
# 查看客户端client 使用的字符集
show variables like '%char%'; 

# 2、concat(str1,str2,...)  : 拼接字符串
select concat(first_name,',',last_name) as name from employees;

# 3upper(), lower(): 转换大小写
select upper(first_name) from employees;
select lower(job_id) from employees;

# 4、substr() = substring() :截取字符串,pos表示从pos位置开始截取,
/*注意:
1、substr有四个重载函数:名字一样,参数不一样
2、所有的mysql语言中,索引均从1开始
3、除了length()函数,其余函数参数中如果有表示长度的参数,一般指字符长度,而非字节长度
*/
# substr(str,pos) :截取从指定索引处(pos)后面所有字符
select substr('李莫愁爱上了陆展元',7) as output;   # 截取'陆展元',
# substr(str,pos1,pos2):截取从指定索引处(pos1,pos2)指定字符长度的字符
select substr('李莫愁爱上了陆展元',1,3) as output;  # 截取李莫愁

# 姓名首字母大写,其他字符小写
select concat(upper(substr(last_name,1,1)),'_', lower(substr(last_name,2))) from employees;

# 5、instr():返回特定字符(子串)在str中的索引位置, 第一次出现的位置, 如果没有,返回0
select instr('杨不悔爱上了殷六侠','殷六侠') as output;

# 6、trim():消除前后空格/特定字符
select trim('    张翠山    ') as output;
select trim('a' from 'aaaaaa张aaaaaa翠山aaaaaaa') as output;

# 7、lpad():left pad左填充,用特定符号将字符串左填充至固定位数
select lpad('殷素素',10,'*') as output;
select lpad('殷素素',2,'*') as output;

# 8、rpad():right pad左填充,用特定符号将字符串右填充至固定位数
select rpad('殷素素',10,'ab') as output;

# 9replace(): 替代
select replace('张无忌爱上了周芷若','周芷若','赵敏') as output;
select replace('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') as output;

# ...
/*
2、 数学函数

*/

# 1round(): 四舍五入
select round(1.65) as output;
select round(-1.65) as output;
select round(1.567,2) as output; # 四舍五入,小数点后保留两位

# 2、ceil(): 向上取整:返回>=该参数的最小整数
select ceil(1.52) as output;
select ceil(1.12) as output;   #2
select ceil(-1.12) as output;  #-1

# 3floor():向下取整:返回<=该参数的最小整数
select floor(1.12) as output;   #1
select floor(-1.12) as output;  #-2

# 4truncate(float,num) 截断,小数点后保留num位数
select truncate(1.599,2) as output;

# 5、mod(num1,num2): 取余  = num1%num2
select mod(10,3) as output;     #1
select mod(-10,-3) as output;   #-1
select mod(-10,3) as output;    #-1
select mod(10,-3) as output;    #1
/*
3、日期函数
*/

# 1、now():返回当前系统日期+时间
select now();

# 2、curdate(); current date:返回当前系统日期
select curdate();

# 3、curtime():current time:返回当前系统时间

# 4、获取指定部分:年、月、日、时、分、秒
select year(now());
select month(now());
select monthname(now());   
select day(now());
select hour(now());
select minute(now());
select second(now());

use myemployees;
select year(hiredate) as `hire_year` from employees;
select year(hiredate) as 'hire_year' from employees;

# 5、日期转换函数之 str_to_date: 日期格式的字符转换成指定格式的日期
select str_to_date('5-18-2021','%m-%d-%Y');         # 2021-05-18
select str_to_date('2021-5-18','%Y-%m-%d');         # 2021-05-18
select str_to_date('2021年5月18日','%Y年%m月%d日'); # 2021-05-18 
/*
日期格式符及对应功能:
%Y 四位年份
%y 两位年份
%m 月份(01,02,...,12)
%c 月份(1,2,...,12)
%d 日(01,02,..)
%H 小时(24小时制)
%h 小时(12小时制)
%i 分钟(00,01,02,...59)
%s 秒(00,01,02,...59)

*/

# 查询入职日期
desc employees;
select hiredate from employees;
select str_to_date(hiredate,'%Y-%m-%d') as output from employees; 

# 查询入职日期为4月3号的员工姓名
select last_name,hiredate from employees where hiredate = '1992-04-03' ;     # 成功
select last_name,hiredate from employees where hiredate = '1992-4-3' ;       # 成功
# 根据用户输入的日期进行查询,用户输入为字符串
select last_name,hiredate from employees where hiredate = str_to_date('04-03 1992','%m-%d %Y'); 
select last_name,hiredate from employees where hiredate = str_to_date('4-3 1992','%c-%d %Y'); 

#date_format: 将日期转化为字符
select date_format('2021-5-18','%Y年%c月%d日');   #2021年5月18日
select date_format('2021/5/18','%Y年%c月%d日');   #2021年5月18日
select date_format('5/18/2021','%c月%d日%Y年');   #错误

# 查询特定日期后转化成字符
select date_format(hiredate,'%Y年%m月%d日') from employees;  # 将表格中日期转化为字符
# 查询有奖金的员工名和入职日期
# (xxxx年xx月xx日);
select last_name,date_format(hiredate,'%Y年%m月%d日'),commission_pct from employees where commission_pct is not null;
# (xx月xx日 xx年);
select last_name,date_format(hiredate,'%m月/%d日 %y年'),commission_pct from employees where commission_pct is not null;
4、其他函数
use myemployees;
select version();  #查看MySQL版本
select database(); #查看当前库
select user();     #查看用户
-- 分组函数
-- 功能:用作统计,又称为聚合函数、组合函数
-- 常见:sum(),avg(),min(),max(),count()

-- 1、简单应用
use myemployees;
select sum(salary) from employees;
select avg(salary) from employees;
select max(salary) from employees;
select min(salary) from employees;
select count(salary) from employees; # 只统计非空值个数

-- 2、参数支持哪些类型
-- sum(),avg()只支持数值型
-- max(),min()支持数值、字符、日期等可排序的数据类型
-- count()支持各种类型
select min(hiredate) from employees;        # max表示最近的日期,min表示最早的日期
select count(hiredate) from employees;      # 107
select count(commission_pct) from employees;      # 35, 因为commission有null值

-- 3、是否忽略null值: max,min,sum,avg,count均忽略null值
select sum(commission_pct)/count(commission_pct) from employees;  # 不考虑null值个数的均值
select avg(commission_pct) from employees;  
# 不考虑null值个数的均值,结果与上述代码相同

-- 4、分组函数的使用特点
-- 可以和distinct搭配进行去重操作,用sum实例
select sum(distinct salary),sum(salary) from employees;  #两者不同,distinct是去重之后再相加
-- 查看有几种工资类型
select count(distinct salary),count(salary)  from employees;

-- 5、count函数的详细介绍
select count(salary) from employees;
select count(*) from employees;       # 统计行数,用得最频繁
# 用于统计总行数,由于不同列可能会出现null值,因此只统计一列的count,可能不能反应总行数,因为count不对null进行计数
select count(1) from employees;       # 统计行数,也可以用count(2),count(x), 相当于在表格中加了一列的1然后对其进行计数

-- 效率:
-- INNODB存储引擎下,count(*)和count(1)差不多,比count(字段)高
-- MYISAM存储引擎下,count(*)最高

-- 6、和分组函数一同查询的字段有限制,要求是group by后的字段
select avg(salary),employee_id from employees;  # 显示错误

-- 测试题
-- 1、查询公司员工工资的最大值、最小值、平均值、总和
select max(salary) mx_sal,min(salary) mn_sal,round(avg(salary),2) ag_sal,sum(salary) sm_sal from employees;
-- 2、查询员工表中最大入职时间和最小入职时间的相差天数(difference), 用到函数datediff
select datediff(max(hiredate),min(hiredate)) as difference from employees;    
-- 3、查询部门编号为90的员工个数;
select count(department_id) from employees where department_id = 90;
select count(*) from employees where department_id = 90;        # 与上一行代码等同
 
原文地址:https://www.cnblogs.com/feynmania/p/14782228.html