基础查询 — 内置函数

文章目录

- - - 概要
    - - 1.函数概述
      - 2.函数的优缺点
      - 3.内置函数分类
      - 4.基本语法
    - 常用单行函数
    - - 1.字符串函数
      - 2.数学函数
      - 3.日期函数
      - 4.信息函数
      - 5.流程控制函数
    - 常用多行函数
    - - 1.概要
      - 2.特点
      - 3.示例

概要

1.函数概述

一段可以直接被另一端程序直接调用的程序或代码。
将一组功能逻辑封装在函数体中，对外暴露函数名。

2.函数的优缺点

优点：
- 隐藏了代码实现细节。
- 提高了代码的重用性和简洁性。
缺点：可移植性差，类似同能的函数在不同的DBMS中可能函数名不同

3.内置函数分类

单行函数：字符串函数、数字函数、日期函数、信息函数、控制函数……
多行函数：聚合函数。

4.基本语法

select 函数名(实参列表) [from 表名];

常用单行函数

1.字符串函数

长度函数：length、 char_length
- length 取字节个数，char_length取字符个数。
- ```
select length('张三'); # 6
select char_lebgth('张三'); # 2
```

拼接函数：concat

用来拼接字符串，可以指定以什么拼接

# 直接连接
select concat(first_name, last_name) as fillname
from employees;# 以'-'连接
select concat(first_name, '-', last_name) as fillname
from employees;

大小写转换：upper、 lower

upper 全部转换为大写，lower全部转换为小写。

select upper("Hello World"); # HELLO WORLD
select lower("Hello World"); # hello world

操作子串：substr 、instr

substr：取子串，索引从1开始

# 截取从指定索引处后面所有字符
select substr('good good study', 7); # ood study
# 截取从指定索引处指定字符长度的字符
select substr('day day up', 2, 5); # ay da

instr：取子串出现的位置返回子串第一次出现的索引，如果找不到返回 0

select instr('good good study', "good"); # 1
select instr('good good study', "day"); # 0

去除多余空格、填充到指定长度：trim、lpad、rpad

trim：去除多余空格，或某些字符（最前或者最后）。

select trim("siu     "); # siu
select trim('~' from "~~~siu~~~~~ "); # siu~~~~~

lpad左填充、rpad右填充

select lpad('batman', 10, '*'); # ****batman
select rpad('batman', 10, '*'); # batman****

替换：replace

用某些字符串替换掉另一些

select 'hello world' as "before", # hello world
replace('hello world', 'world', 'mysql') as "after"; # hello mysql

2.数学函数

四舍五入与取整：round、ceil、floor

round四舍五入

select round(2.5); # 3
select round(2.4);# 2

ceil向上取整、floor向下取整

select ceil(2.6); # 3
select floor(2.6); # 2

截断：truncate

达到保留需要的小数位个数的效果

select truncate(3.1415926, 2); # 3.14
select truncate(3.1415926, 5); # 3.14159

绝对值与随机数：abs()、rand()

abs取绝对值
```
select abs(-1.414); # 1.414
```

rand取随机数

select rand(); # 任意随机数
select round(rand() * 10); # 1~10的随机数

3.日期函数

取当前日期和时间
- now：返回当前系统日期和时间。
- curdate：返回当前系统日期。
- curtime：返回当前系统时间。
```
select now(); # 2023-06-06 09:36:54
select curdate(); # 2023-06-06
select curtime(); # 09:36:56
```
取时间和日期的部分信息
- 日期：year()、 month()、 day()、 monthname()
- 时间：hour()、minute()、second()
- 几种特殊的：
  - quarter()：返回日期对应的季度。
  - weekofyear：返回一年中的第几周。
  - dayofyear：返回一年中的第几天。
  - dayofmonth：返回某月的第几天
  - dayofweak：返回周几（周日是1）
时间和秒的相互转换
- 秒数只跟时间有关系，跟日期无关。
- 转化公式：h×3600 + m×60 + s。
- time_to_sec 将时间转化为秒，sec_to_time 将秒转换成时间。
```
select curtime(), time_to_sec(now()), sec_to_time(time_to_sec(now()));
# 09:46:04  35164  09:46:04    
```

格式化

按照自定义格式显示日期和时间

fromt 格式化字符串中的占位符

%Y 年，四位数字
%y 年，后两位数字
%m 月 ，数字[1-12]
%M 月名 
%d 日，月份里的第几天，两位数字[1-31]
%D 带有英文前缀的月中的天 
%H 小时，24进制 [0-23]
%h 小时，12进制[0-11]
%i 分钟 [0-59]
%s | %S 秒 0-59

date_format(date, format)：格式化日期

select date_format(now(), '%m/%d-%y')
from dual; # 06/06-23

time_format(date, format)：格式化时间

selecttime_format(curtime(), '%h 点 %i 分 %s 秒')
from dual; # 09 点 55 分 05 秒

字符串转日期：str_to_date

selectstr_to_date('2000-1-2', '%Y-%m-%d'),str_to_date('May 1, 2023', '%M %d,%Y')
from dual;
# 2000-01-02  2023-05-01

4.信息函数

version 查询 MySQL 版本号
user 查询当前登录的账户
database 查询当前使用的数据库

5.流程控制函数

if函数

语法：if(expr, trueValue, falseValue)

select if(10 < 5, 'true', 'false') as "10 < 5"; #false

case函数

语法

case [field_x]when XX then [statement-1]when XX then [statement-2]...else [statement-n]
end

当XX为具体值时需要 case [field_x]，当XX为条件表达式时需要case

/** 查询员工的工资、部分编号和 new_salary，其中 new_salary 的计算如下*   部门编号如果是 30，new_salary 为 1.2 倍工资*   部门编号如果是 40，new_salary 为 1.25 倍工资*   部门编号如果是 50，new_salary 为 1.3 倍工资*   其他部门，new_salary 为原工资
*/select salary, department_id,case department_idwhen 30 then salary*1.2when 40 then salary*1.25when 50 then salary*1.3else salaryend as new_salary
from employees;/** 查询员工的名字、工资以及工资级别 grade，grade 计算如下*   如果工资大于 20000，grade 为 A 级*   如果工资大于 15000，grade 为 B 级*   如果工资大于 10000，grade 为 C 级*   否则，grade 为 D 级
*/select first_name, salary,casewhen salary > 20000 then 'A'when salary > 15000 then 'B'when salary > 10000 then 'C'else 'D'end as grade
from employees;

常用多行函数

1.概要

多行函数又称为聚合函数。
用做统计使用。
分类：sum求和、avg平均值、max最大值、min最小值、count计算个数。

2.特点

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

3.示例

简单使用

selectsum(salary),avg(salary),min(salary),max(salary),count(salary)
from employees;

与distinct搭配去重

selectsum(distinct salary),avg(distinct salary),min(distinct salary),max(distinct salary),count(distinct salary)
from employees;

关于count函数
- count(expression)：返回不包含NULL值的行数。
- count(*)：返回的结果集中的行数，包含NULL值的行数，即所有行。
- count(distinct expression)：返回不包含NULL值的唯一行数。

静静的听，有个声音在说爱你。 ——海龟先生《男孩别哭》