mysql中使用数据处理函数

---

title: mysql中使用数据处理函数
date: 2021-07-05 17:15:34
tags:

---

前言

SQL语句中使用函数在不同的DBMS上的写法有些差异，只是少数函数。下面例子在mysql中的写法。(根据<mysql必知必会>中的内容做的总结笔记)

---

一、文本处理函数

1、使用upper()将文本转换为大写。

 select vend_name,upper(vend_name) as vend_name_upcase from vendors order by vend_name;
+----------------+------------------+
| vend_name      | vend_name_upcase |
+----------------+------------------+
| ACME           | ACME             |
| Anvils R Us    | ANVILS R US      |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
| LT Supplies    | LT SUPPLIES      |
+----------------+------------------+

2、其他常用的文本处理函数
left()=>返回串左边的字符
right()=>返回串右边的字符

//LEFT(str,length);
str是要提取子字符串的字符串。
length是一个正整数，指定将从左边返回的字符数。
例如：
SELECT LEFT('MySQL LEFT', 5);
+-----------------------+
| LEFT('MySQL LEFT', 5) |
+-----------------------+
| MySQL                 |
+-----------------------+
//
使用 RIGHT 函数返回字符串中右边的字符，输入的 SQL 语句和执行结果如下所示。
mysql> SELECT RIGHT('MySQL',3);
+------------------+
| RIGHT('MySQL',3) |
+------------------+
| SQL              |
+------------------+

locate()=>找出串的一个子串

//locate(subStr,string) ：函数返回subStr在string中出现的位置
// 如果字符串 string 包含 subStr
locate(subStr,string) > 0
 
// 如果字符串 string 不包含 subStr
locate(subStr,string) = 0
例如：
select locate('1a3','1a3');
+---------------------+
| locate('1a3','1a3') |
+---------------------+
|                   1 |
+---------------------+
返回从匹配的第一个字符串的下标，从1开始。
 select locate('1a3','111a3');
+-----------------------+
| locate('1a3','111a3') |
+-----------------------+
|                     3 |
+-----------------------+

Rtrim()=>去串右边的空格
Ltrim()=>去串左边的空格
trim()=>去左右两边的空格

soundex()=>返回串的soundex值

//使用例子：匹配所有发音类似(Y lie)到联系名。 
select cust_name,cust_contact from customers where soundex(cust_contact)=soundex('Y lie');
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

---

使用之前先了解一下mysql中日期与时间的类型

一、日期类型

MySql中关于日期的类型有Date/Datetime/Timestamp三种类型。

1.1 Date类型
此类型的字段，存储数据格式为：YYYY-MM-DD，它支持的范围为’1000-01-01’到’9999-12-31’，并且允许使用字符串或数字为此列赋值。
例如:

//字符串形式:
set Date列='2002-04-12'
//数字赋值形式：
set Date列=20210613

1.2 Datetime类型
日期和时间的组合，存储格式为：YYYY-MM-DD HH:MM:SS，它支持的范围为’1000-01-01 00:00:00’到’9999-12-31 23:59:59’，并且允许使用字符串或数字进行赋值,道理同上。

1.3 TimeStamp格式
这是时间戳，从1970-01-01 00:00:00到当前的时间差值,它精确到毫秒级别，范围为：1970-01-01 00:00:00 到 2037年（参考2038年问题），当值大于2037年就会抛出错误。设置值时只允许设置数字类型的值。

在为TimeStamp类型字段赋值的时候，值必须大于19700101000000，否则就会抛出错误。

二、时间类型

MySQL中时间类型用Time表示。
2.1 Time格式
MySQL以’HH:MM:SS’格式检索和显示TIME值(或对于大的小时值采用’HHH:MM:SS’格式).
TIME值的范围可以从’-838:59:59’到’838:59:59’。小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。

三、年类型

MySQL中年类型用year表示。
3.1 year格式
MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。
可以指定各种格式的YEAR值：

• 四位字符串，范围为’1901’到’2155’。
• 四位数字，范围为1901到2155。
• 两位字符串，范围为’00’到’99’。'00’到’69’和’70’到’99’范围的值被转换为2000到2069和1970到1999范围的YEAR值。
• 两位整数，范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串’0’或’00’或它被解释为0000。
• 函数返回的结果，其值适合YEAR上下文，例如NOW()。
• 非法YEAR值被转换为0000。

---

日期和时间处理函数

使用的一些技巧：

1. 在mysql使用的日期格式最好选择yyyy-mm-dd格式。
   2.如果进行条件判断时列的比较值是日期，最好使用Date()函数对列(因为列的数据类型可能是Datetime类型，如果带了时间数据且不是00-00-00,就会导致匹配不上)进行处理一下，例如：

 select cust_id,order_num from orders where Date(order_date) ='2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+

举一反三：日期类似的方法:

1. Time()=>返回一个日期的时间部分
2. Year()=>返回一个日期的年份部分
3. Day()=>返回一个日期的天数部分
4. DayOfWeek()=>返回一个日期的对应的星期几

select DayOfWeek('2000-06-13');
+-------------------------+
| DayOfWeek('2000-06-13') |
+-------------------------+
|                       3 |
+-------------------------+

5. Month()=>返回一个日期的月部分

时间类似的方法:

1. Hour()=>返回一个时间的小时部分
2. Minute()=>返回一个时间的分钟部分
3. Second()=>返回一个时间的秒部分

例子:如果想检索出2005年9月下的所有订单

//解决方法一：使用[01,30]
select cust_id,order_num from orders where Date(order_date) between '2005-09-01' and '2005-09-30';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
//缺陷：需要知道那个月有多少天以及考虑闰年2月的情况.

//解决方法二：使用Year()和Month()函数提取年份和月份进行匹配
select cust_id,order_num from orders where Year(order_date)=2005 and Month(order_date)=9;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+

数值处理函数，这里不做描述。

可查阅网上资料
相关博客

---

写在最后：

select concat(curDate(),' ',curTime()) as 当前时间;
+---------------------+
| 当前时间            |
+---------------------+
| 2021-07-07 04:20:23 |
+---------------------+

明天第一次去面试，希望能成功！
求关注求点赞!

---