第十八天：初级数据库学习笔记2

数据处理函数

参考了b站动力字节数据库文档

关于select语句，我们之前都是这样写：select 字段名 from 表名; 其实，这里的字段名可以看做“变量”，select后面既然可以跟变量，那么可以跟常量吗，尝试一下： 通过以上sql的测试得知，select后面既可以跟变量，又可以跟常量。 以上三条SQL中前两条中100和’abc’都是常量，最后一条SQL的abc没有添加单引号，它会被当做某个表的字段名，因为没有这个字段所以报错。

字符串相关

转大写upper和ucase

# 查询所有员工名字，以大写形式展现
select upper(ename) as ename from emp;

还有一个和upper函数功能相同的函数ucase，也可以转大写，了解一下即可：

# 查询所有员工姓名，以大写形式展现
select ucase(ename) as ename from emp;

# 查询员工smith的岗位、薪资（假如你不知道数据库表中的人名是大写、小写还是大小写混合）
select ename, job, sal from emp where upper(ename) = 'SMITH';

转小写lower和lcase

很简单，不再赘述，直接上代码：

# 查询员工姓名，以小写形式展现
select lower(ename) as ename from emp;
select lcase(ename) as ename from emp;

截取字符串substr

语法：substr(‘被截取的字符串’, 起始下标, 截取长度) 有两种写法。

• 第一种：substr(‘被截取的字符串’, 起始下标, 截取长度)

• 第二种：substr(‘被截取的字符串’, 起始下标)，当第三个参数“截取长度”缺失时，截取到字符串末尾 练习：找出员工名字中第二个字母是A的

• 方式一：select ename from emp where upper(ename like '_A%');

• 方式二：select ename from emp where substr(ename, 2, 1) = 'A';

注意事项：

注意：起始下标从1开始，不是从0开始。（1表示从左侧开始的第一个位置，-1表示从右侧开始的第一个位置。）

例子1： select substr(‘abcdef’,-1,1);结果为f

例子2：select substr(‘abcdef’,-1,2);结果仍为f，截的时候从起始位置左往右截.

获取字符串长度length

注意：一个汉字是2个长度，你好占4个长度。

获取字符的个数char_length

字符串拼接

语法：concat(‘字符串1’, ‘字符串2’, ‘字符串3’…) 拼接的字符串数量没有限制。 注意：在mysql8之前，双竖线||也是可以完成字符串拼接的。但在mysql8之后，||只作为逻辑运算符，不能再进行字符串拼接了。

select 'abc' || 'def' || 'xyz';

mysql8之后，|| 只作为“或者”运算符。

例如：找出工资高于3000或者低于900的员工姓名和薪资：

select ename, sal from emp where sal > 3000 || sal < 900;

mysql中可以使用+进行字符串的拼接吗？不可以，在mysql中+只作加法运算，在进行加法运算时，会将加号两边的数据尽最大的努力转换成数字再求和，如果无法转换成数字，最终运算结果通通是0

去除字符串前后空格trim（重点掌握）

select concat(trim('    abc    '), 'def');

默认是去除前后空白，也可以去除指定的前缀后缀，例如： 去除前置0

select trim(leading '0' from '000111000');

去除后置0

select trim(trailing '0' from '000111000');

前置0和后置0全部去除

select trim(both '0' from '000111000');

数字相关

rand()和rand(x)

rand()生成0到1的随机浮点数。

rand(x)生成0到1的随机浮点数，通过指定整数x来确定每次获取到相同的浮点值。

round(x)和round(x,y)四舍五入

round(x) 四舍五入，保留整数位，舍去所有小数

round(x,y) 四舍五入，保留y位小数

truncate(x, y)舍去

以上SQL表示保留两位小数，剩下的全部舍去。

ceil与floor

数字处理函数除了以上的之外，还有ceil和floor函数：

• ceil函数：向上取整
• floor函数：向下取整

空处理(必须掌握)

凡是有NULL参与的数学运算，最终的计算结果都是NULL。因此用ifnull(x, y)函数来处理这种情况。ifnull(x, y)，空处理函数，当x为NULL时，将x当做y处理。
ifnull(comm, 0)，表示如果员工的津贴comm是NULL时当做0处理。
在SQL语句中：

看这样一个需求：查询每个员工的年薪。（年薪 = (月薪 + 津贴) * 12个月。注意：有的员工津贴comm是NULL。）

以上查询结果中显示SMITH等人的年薪是NULL，这是为什么，这是因为SMITH等人的津贴comm是NULL，有NULL参与的数学运算，最终结果都是NULL，显然这个需要空处理，此时就用到了ifnull函数：

日期和时间相关函数

获取当前日期和时间

now()和sysdate()的区别：

• now()：获取的是执行select语句的时刻。
• sysdate()：获取的是执行sysdate()函数的时刻。

获取当前日期

获取当前日期有三种写法，掌握任意一种即可：

• curdate()
• current_date()
• current_date

获取当前时间

获取档期时间有三种写法，掌握其中一种即可：

• curtime()
• current_time()
• current_time

获取单独的年、月、日、时、分、秒

注意：这些函数在使用的时候，需要传递一个日期参数给它，它可以获取到你给定的这个日期相关的年、月、日、时、分、秒的信息。
一次性提取一个给定日期的“年月日”部分，可以使用date()函数，例如：

一次性提取一个给定日期的“时分秒”部分，可以使用time()函数，例如：

date_add函数

date_add函数的作用：给指定的日期添加间隔的时间，从而得到一个新的日期。
date_add函数的语法格式：date_add(日期, interval expr 单位)，例如：

以’2023-01-03’为基准，间隔3天之后的日期：‘2023-01-06’


以’2023-01-03’为基准，间隔3个月之后的日期：‘2023-04-03’
详细解释一下这个函数的相关参数：

• 日期：一个日期类型的数据
• interval：关键字，翻译为“间隔”，固定写法
• expr：指定具体的间隔量，一般是一个数字。也可以为负数，如果为负数，效果和date_sub函数相同。
• 单位：
  • year：年
  • month：月
  • day：日
  • hour：时
  • minute：分
  • second：秒
  • microsecond：微秒（1秒等于1000毫秒，1毫秒等于1000微秒）
  • week：周
  • quarter：季度

请分析下面这条SQL语句所表达的含义：

以上SQL表示：以2022-10-01 10:10:10为基准，在这个时间基础上添加-1微秒，也就是减去1微秒。
以上SQL也可以采用date_sub函数完成，例如：

另外，单位也可以采用复合型单位，例如：

• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND：几分几秒之后
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE：几小时几分之后
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR：几天几小时之后
• YEAR_MONTH：几年几个月之后

如果单位采用复合型的话，expr该怎么写呢？例如单位采用：day_hour，假设我要表示3天2小时之后，怎么写？

'3,2’这个应该很好理解，表示3天2个小时之后。'3,2’和day_hour是对应的。

date_format日期格式化函数

将日期转换成具有某种格式的日期字符串，通常用在查询操作当中。（date类型转换成char类型）
语法格式：date_format(日期, ‘日期格式’)
该函数有两个参数：

• 第一个参数：日期。这个参数就是即将要被格式化的日期。类型是date类型。
• 第二个参数：指定要格式化的格式字符串。
  • %Y：四位年份
  • %y：两位年份
  • %m：月份（1…12）
  • %d：日（1…30）
  • %H：小时（0…23）
  • %i：分（0…59）
  • %s：秒（0…59）

例如：获取当前系统时间，让其以这个格式展示：2000-10-11 20:15:30

注意：在mysql当中，默认的日期格式就是：%Y-%m-%d %H:%i:%s，所以当你直接输出日期数据的时候，会自动转换成该格式的字符串：

str_to_date函数

该函数的作用是将char类型的日期字符串转换成日期类型date，通常使用在插入和修改操作当中。（char类型转换成date类型）
假设有一个学生表t_student，学生有一个生日的字段，类型是date类型：

drop table if exists t_student;
create table t_student(name varchar(255),birth date
);
desc t_student;

我们要给这个表插入一条数据：姓名zhangsan，生日85年10月1日，执行以下insert语句：

错误原因：日期值不正确。意思是：birth字段需要一个日期，你给的这个字符串’10/01/1985’我识别不了。这种情况下，我们就可以使用str_to_date函数进行类型转换：


当然，如果你提供的日期字符串格式能够被mysql解析，str_to_date函数是可以省略的，底层会自动调用该函数进行类型转换：

如果日期格式符合以上的几种格式，mysql都会自动进行类型转换的。

dayofweek、dayofmonth、dayofyear函数

dayofweek：一周中的第几天（1~7），周日是1，周六是7。
dayofmonth：一个月中的第几天（1~31）
dayofyear：一年中的第几天（1~366）

last_day函数

获取给定日期所在月的最后一天的日期：

datediff函数

计算两个日期之间所差天数：

时分秒不算，只计算日期部分相差的天数。

timediff函数

计算两个日期所差时间，例如日期1和日期2所差10:20:30，表示差10小时20分钟30秒。

if函数

如果条件为TRUE则返回“YES”，如果条件为FALSE则返回“NO”：

SELECT IF(500<1000, "YES", "NO");

例如：如果工资高于3000，则输出1，反之则输出0

再例如：如果名字是SMITH的，工资上调10%，其他员工工资正常显示。

再例如：工作岗位是MANAGER的工资上调10%，是SALESMAN的工资上调20%，其他岗位工资正常。

上面这个需求也可以使用：case… when… then… when… then… else… end来完成：

cast函数

cast函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型
语法：cast(值 as 数据类型)
例如：cast(‘2020-10-11’ as date)，表示将字符串’2020-10-11’转换成日期date类型。
在使用cast函数时，可用的数据类型包括：

• date：日期类型
• time：时间类型
• datetime：日期时间类型
• signed：有符号的int类型（有符号指的是正数负数）
• char：定长字符串类型
• decimal：浮点型

加密函数

md5函数，可以将给定的字符串经过md5算法进行加密处理，字符串经过加密之后会生成一个固定长度32位的字符串，md5加密之后的密文通常是不能解密的：

char：定长字符串类型

• decimal：浮点型

[外链图片转存中…(img-XJGa5LT4-1750234055870)]
[外链图片转存中…(img-X09RABOt-1750234055870)]
[外链图片转存中…(img-8knPqPMM-1750234055870)]
[外链图片转存中…(img-vzxK2tDa-1750234055870)]
[外链图片转存中…(img-sAmwA8Hx-1750234055870)]

加密函数

md5函数，可以将给定的字符串经过md5算法进行加密处理，字符串经过加密之后会生成一个固定长度32位的字符串，md5加密之后的密文通常是不能解密的：
[外链图片转存中…(img-0A7LTXBU-1750234055870)]