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。
日期和时间处理函数
使用的一些技巧:
- 在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 |
+---------+-----------+
举一反三:日期类似的方法:
- Time()=>返回一个日期的时间部分
- Year()=>返回一个日期的年份部分
- Day()=>返回一个日期的天数部分
- DayOfWeek()=>返回一个日期的对应的星期几
select DayOfWeek('2000-06-13');
+-------------------------+
| DayOfWeek('2000-06-13') |
+-------------------------+
| 3 |
+-------------------------+
- Month()=>返回一个日期的月部分
时间类似的方法:
- Hour()=>返回一个时间的小时部分
- Minute()=>返回一个时间的分钟部分
- 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 |
+---------------------+
明天第一次去面试,希望能成功!
求关注求点赞!