02-DQL语言-04常见函数

最新推荐文章于 2024-07-13 18:10:19 发布

Yunwei man

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量75

点赞数

分类专栏：数据库文章标签：数据库 mysql 运维服务器

原文链接：https://www.bilibili.com/video/BV12b411K7Zu?from=search&seid=9899053634445081192

版权

数据库专栏收录该内容

21 篇文章 0 订阅

订阅专栏

在这里插入图片描述

理论

一、概述

功能：类似于java中的方法
好处：提高重用性和隐藏实现细节
调用：select函数名（实参列表）

二、单行函数

1、字符函数

concat 连接
substr：截取子串
upper：变大号
lower：变小写
repleace：替换
length：获取字节长度
trim：去前后空格
ipad：左填充
rpad：右填充
instr：获取

2、数字函数

ceil：向上取整
round：四舍五入
mod：取模（余）
floor：向下取整
truncate：截断
rand：获取随机数，返回-之间的小数

3、日期函数

now：返回当前日期和时间
year：返回年
Month:返回月
day：返回日
date_format：将日期转换成字符
curdate：返回当前日期
str_to_date：返回当前日期
curtime：返回当前时间
hour：小时
minute：分钟
second：秒
datediff：返回两个日期相差的天数
monthname：以英文的形式返回月

4、其他函数

version 当前数据库服务器的版本
database 当前打开的数据库
user 当前用户
password（'字符'）：返回该字符的密码形式
md5（'字符'）：返回该字符的md5格式

5、流程控制函数
①if（条件表达式,表达式1,表达式2）：如果条件表达式成立，返回表达式1，否则返回表达式2

case 情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
…
else 值n
end
 
②case情况2
case
when 条件1 then 值1
when 条件2 then 值2
…
else 值n
end

三、分组函数

1、分类

Max 最大值 
Min 最小值
Sum 和
Avg 平均值
count 计算个数

2、特点
①语法
select max (字段) from 表名;

②支持的类型
sum和avg一般用于处理数值型
Max、min、couunt可以处理任何数据类型

③以上分组函数忽略null
④都可以搭配distinct使用，实现去重的统计
select sum(distinct 字段) from 表;
⑤count函数
count（字段）：统计该字段非空值的个数
count(*)：统计结果集的行数

案例：查询每个部门的员工个数

1	Xx	10
2	Dd	20
3	Hh	20
4	Nn	40
5	Mm	40

count(1)：统计结果集的行数

效率上：
MyISAM存储引擎，count()最高
InnoDB存储引擎，count()和count(1)效率 >count(字段)

⑤和分组函数统一查询的字段，要求是group by 出现的字段

案例

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名好处：1、隐藏了实现细节
2、提高代码的重用性调用：select 函数名（实参列表）【from表】；特点：
①叫什么（函数名）
②干什么（函数功能）分类：
1、单行函数
如concat、length、ifnull等
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
常见函数：
  	字符函数：
    length
    concat
    substr
    instr
    trim
    upper
    lower
    ipad
    rpad
    replace
    
    数学函数：
    round
    ceil
    floor
    truncate
    mod
    
    日期函数：
    now
    curdate
    curtime
    year
    month
    monthname
    day
    hour
    minute
    second
    str_to_date
    date_format
    
    其他函数：
    version
    database
    user
    
    控制函数
    if
    case

一、字符函数

#1、length 获取参数值的字母个数

SELECT LENGTH('jianman');
SELECT LENGTH('郑jm');

#2、concat 拼接字符串

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

#3、upper、lower

SELECT UPPER('jianman');
SELECT LOWER('jianMan');

#示例：将姓变大写，名编小写，然后拼接
SELECT
    CONCAT
    (UPPER(last_name),'_',LOWER(first_name)) AS 姓名
FROM employees

#4、substr、substring

#注意：截取从指定索引处所有字符
SELECT SUBSTR('阿珍爱上了阿强',6) AS 输出1;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('阿珍爱上了阿强',1,2) AS 输出2;

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

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

#5、instr：返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('为了上课早睡','早睡') AS 输出;

#6、trim 去前后

SELECT LENGTH(TRIM('        百岁山      ')) AS out_put;
    
SELECT TRIM('e' FROM 'eeeeeeeeeee白eee岁时eeeeeeeeeeeeeee') AS out_put

#7、Ipad 用指定的字符实现左填充指定长度

SELECT LPAD('牛逼',10,'#') AS out_put;

#8、rpad 用指定的字符实现右填充指定长度

SELECT LPAD('厉害',10,'#') AS out_put;

#9、replace 替换

SELECT REPLACE('柴火柴火星星点柴火柴火柴火','柴火','灯') AS out_put;

二、数学函数

#round 四舍五入
SELECT ROUND(-1.56);
SELECT ROUND(5.156,2);
 
#ceil 向上取整，返回<=该参数得最大整数
SELECT CEIL(6.156);
 
#floor 向下取整，返回<=该参数得最大整数
SELECT  FLOOR(-5.189);
 
#truncate 截断
SELECT TRUNCATE(6.1156154,1);
 
#mod 取余
/*
mod(a,b) : a-a/b*b
mod(a,b) : -10-(10)/(-3)* (-3) = -1
*/
SELECT MOD(10,-3);
SELECT 10%3

三、日期函数

#now 返回但钱系统+时间
SELECT NOW();
 
#curdate返回当前系统日期，不包含时间
SELECT CURDATE();
 
#curtime 返回当前时间，不包含日期
SELECT CURTIME();
 
#可以获取只当的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('2021-1-1') 年
FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

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

#str_to_date 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('2020-5-7','%Y-%c-%d') AS out_put;

#查询入职日期为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日') AS out_put;

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

SELECT
    `last_name`,
    DATE_FORMAT(hiredate,'%m月/%d日 %y年')
FROM employees;

四、其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

五、流程控制函数

#1、if函数： if else的效果

SELECT  IF(10<5,'大','小') 猜大猜小;
SELECT
    last_name,
    IF(`commission_pct` IS NULL,'没奖金，呵呵','有奖金，哈哈') 奖金
FROM employees;

#2.case函数的使用一：swith case 的效果

java中 switch（变量或表达式）{
case 常量1：语句1;break;
…
default:语句n;break;
} mysql中 case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1; when 常量2 then 要显示的值2或语句2; … else 要显示的值n或语句n end

/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/

SELECT 
    salary 原始工资,
    dapartment_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 employees;

#3.case函数的使用二：类似于多重if

java中 if(条件1){
语句1: }else if(条件2){
语句2: } … else{
语句n; } mysql中
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
end

/*
案例：查询员工的工资的情况
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/
SELECT
    last_name AS 名字,
    salary AS 工资,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资等级
FROM
    employees;

六、分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数分类 sum 求和、avg 平均值、max 最大值、min 最小值、 count
计算个数特点： 1、sum、avg一般用于处理数值型 max、min、count可以处理任何类型 2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算 4、count函数单独介绍一般使用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;
    
#------------------------------------
SELECT
    SUM(salary) AS 和,
    AVG(salary) AS 平均值,
    MAX(salary) AS 最大值,
    MIN(salary) AS 最小值,
    COUNT(salary) AS 个数
FROM
    employees;
#---------------------------------------
SELECT
    SUM(salary) AS 和,
    ROUND(AVG(salary),2) AS 平均值,
    MAX(salary) AS 最大值,
    MIN(salary) AS 最小值,
    COUNT(salary) AS 个数
FROM
    employees;

#2、参数支持哪些类型

SELECT SUM(last_name), AVG(last_name) FROM employees;
#--------------------------
SELECT SUM(hiredate), AVG(hiredate) FROM employees;
#-------------------------
SELECT MAX(last_name), MIN(last_name) FROM employees;
#-----------------------
SELECT MAX(hiredate), MIN(hiredate) FROM employees;
#-----------------------
SELECT COUNT(commission_pct) FROM employees;
#------------------------
SELECT COUNT(last_name) FROM employees;

#3、是否忽略null值

SELECT
    SUM(commission_pct),
    AVG(commission_pct),
    SUM(commission_pct)/35,
    SUM(commission_pct)/107
FROM employees;
    
#--------------------------------------
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;

#4、和distinct搭配

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

#5、count函数的详细介绍

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM rmployees;
效率：
MYISAM存储引擎下，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些

#6、和分组函数一同查询的字段有限制

#---------------下面可以实现，但没有意义--------------
SELECT AVG(salary),employee_id FROM employees;

Yunwei man

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
02-DQL语言-04常见函数

一、概述功能：类似于java中的方法好处：提高重用性和隐藏实现细节调用：select函数名（实参列表）二、单行函数1、字符函数concat 连接substr：截取子串upper：变大号lower：变小写repleace：替换length：获取字节长度trim：去前后空格ipad：左填充rpad：右填充instr：获取2、数字函数ceil：向上取整round：四舍五入mod：取模（余）floor：向下取整truncate：截断rand：获取随机数，返回-之间的小.
复制链接

扫一扫