字符串

内建字符串函数

数值运算

控制数字精度

时间数据

字符串到日期转换

时间的内置函数

转换函数

字符串

在SQL语句中，字符串使用单引号分隔，因此会对本身含有单引号的字符串产生警告，需要在字符串中加上转义符。

使用单引号作为转义符： UPDATE string_tbl SET text_fld='string didn''t work';

使用反斜杠\作为转义符： UPDATE string_tbl SET text_fld='string didn\'t work';

使用内建函数quote(): SELECT quote(text_fld) FROM string_tbl; -> 'string didn\'t work'

内建字符串函数

SELECT * FROM string_tbl;
+------------------------------+------------------------------+------------------------------+
| char_fld                     | vchar_fld                    | text_fld                     |
+------------------------------+------------------------------+------------------------------+
| This string is 28 characters | This string is 28 characters | This string is 28 characters |
+------------------------------+------------------------------+------------------------------+

length()

返回字符串的字符数，下面查询对每个列都用一次函数。

SELECT LENGTH(char_fld) cha_length,LENGTH(vchar_fld) 
    -> varchar_length,LENGTH(text_fld) text_lenght FROM string_tbl;
+-------------+----------------+-------------+
| cha_length | varchar_length | text_lenght |
+-------------+----------------+-------------+
|          28 |             28 |          28 |
+-------------+----------------+-------------+

position()

查找子字符串在字符串中位置。在数据库中字符串第一个位置号为1，如果返回0值，则是没有找到。

SELECT POSITION('characters' IN vchar_fld) FROM string_tbl;
+-------------------------------------+
| POSITION('characters' IN vchar_fld) |
+-------------------------------------+
|                                  19 |
+-------------------------------------+
SELECT POSITION('This' IN vchar_fld) FROM string_tbl;
+-------------------------------+
| POSITION('This' IN vchar_fld) |
+-------------------------------+
|                             1 |
+-------------------------------+
SELECT POSITION('there' IN vchar_fld) FROM string_tbl;
+--------------------------------+
| POSITION('there' IN vchar_fld) |
+--------------------------------+
|                              0 |
+--------------------------------+

locate()

从字符串任意位置搜素，而不是仅从第一个字符。接受可选的第三个参数。

SELECT LOCATE('is' ,vchar_fld,5) FROM string_tbl;
+---------------------------+
| LOCATE('is' ,vchar_fld,5) |
+---------------------------+
|                        13 |
+---------------------------+

strcmp()

接受两个字符串作为参数，并返回下面结果之一：此函数对大小写不敏感

-1，第一个字符串的排序位于第二个之前

0，两个字符串相同

1，第一个字符串的排序位于第二个之后

SELECT vchar_fld FROM string_tbl ORDER BY vchar_fld;
+-----------+
| vchar_fld |
+-----------+
| 12345     |
| abcd      |
| QRSTUV    |
| qrstuv    |
| xyz       |
+-----------+
SELECT STRCMP('12345','12345') 12345_12345,
    -> STRCMP('abcd','xyz') abcd_xyz,
    -> STRCMP('abcd','QRSTUV') abcd_QRSTUV,
    -> STRCMP('qrstuv','QRSTUV') qrstuv_QRSTUV,
    -> STRCMP('12345','xyz') 12345_xyz,
    -> STRCMP('xyz','qrstuv') xyz_qrstuv;
+-------------+----------+-------------+---------------+-----------+------------+
| 12345_12345 | abcd_xyz | abcd_QRSTUV | qrstuv_QRSTUV | 12345_xyz | xyz_qrstuv |
+-------------+----------+-------------+---------------+-----------+------------+
|           0 |       -1 |          -1 |             0 |        -1 |          1 |
+-------------+----------+-------------+---------------+-----------+------------+

MYSQL还可以使用like和regexp操作符来比较字符串，比较结果为1或0。

SELECT name, name LIKE '%ns' ends_in_ns FROM department;
+----------------+------------+
| name           | ends_in_ns |
+----------------+------------+
| Operations     |          1 |
| Loans          |          1 |
| Administration |          0 |
+----------------+------------+
SELECT cust_id,cust_type_cd,fed_id, fed_id REGEXP '.{3}-.{2}-.{4}' is_ss_no_format FROM customer;
+---------+--------------+-------------+-----------------+
| cust_id | cust_type_cd | fed_id      | is_ss_no_format |
+---------+--------------+-------------+-----------------+
|       1 | I            | 111-11-1111 |               1 |
|       2 | I            | 222-22-2222 |               1 |
|       3 | I            | 333-33-3333 |               1 |
|       4 | I            | 444-44-4444 |               1 |
|       5 | I            | 555-55-5555 |               1 |
|       6 | I            | 666-66-6666 |               1 |
|       7 | I            | 777-77-7777 |               1 |
|       8 | I            | 888-88-8888 |               1 |
|       9 | I            | 999-99-9999 |               1 |
|      10 | B            | 04-1111111  |               0 |
|      11 | B            | 04-2222222  |               0 |
|      12 | B            | 04-3333333  |               0 |
|      13 | B            | 04-4444444  |               0 |
+---------+--------------+-------------+-----------------+

concat()

向已经存储的字符串前面或后面追加额外的字符。

SELECT text_fld FROM string_tbl;
+-------------------------------+
| text_fld                      |
+-------------------------------+
| This string was 29 characters |
+-------------------------------+
UPDATE  string_tbl SET text_fld=CONCAT(text_fld, ', but now it is longer');
+-----------------------------------------------------+
| text_fld                                            |
+-----------------------------------------------------+
| This string was 29 characters, but now it is longer |
+-----------------------------------------------------+

根据独立地字符串碎片构建字符串。

SELECT CONCAT(fname,' ',lname,' has been a ',title,' since ',start_date) emp_narrative FROM employee WHERE title='Teller' OR tit
le='Head Teller';
+---------------------------------------------------------+
| emp_narrative                                           |
+---------------------------------------------------------+
| Helen Fleming has been a Head Teller since 2008-03-17   |
| Chris Tucker has been a Teller since 2008-09-15         |
| Sarah Parker has been a Teller since 2006-12-02         |
| Jane Grossman has been a Teller since 2006-05-03        |
| Paula Roberts has been a Head Teller since 2006-07-27   |
| Thomas Ziegler has been a Teller since 2004-10-23       |
| Samantha Jameson has been a Teller since 2007-01-08     |
| John Blake has been a Head Teller since 2004-05-11      |
| Cindy Mason has been a Teller since 2006-08-09          |
| Frank Portman has been a Teller since 2007-04-01        |
| Theresa Markham has been a Head Teller since 2005-03-15 |
| Beth Fowler has been a Teller since 2006-06-29          |
| Rick Tulman has been a Teller since 2006-12-12          |
+---------------------------------------------------------+

insert()

接受4个参数：原始字符串、字符串操作开始位置、需要替换的字符数以及替换的字符串。

SELECT INSERT('goodbye world', 9, 0, 'cruel ') string;
+---------------------+
| string              |
+---------------------+
| goodbye cruel world |
+---------------------+
SELECT INSERT('goodbye world', 1, 7, 'hello') string;
+-------------+
| string      |
+-------------+
| hello world |
+-------------+

数值运算

所有的算术操作符(+、-、*、/)都可用于执行计算，并且可以使用括号改变优先级

SELECT (37*59)/(78-(8*6));
+--------------------+
| (37*59)/(78-(8*6)) |
+--------------------+
|            72.7667 |
+--------------------+

mod()

计算两数相除所得余数的求模。

SELECT MOD(10,4);
+-----------+
| MOD(10,4) |
+-----------+
|         2 |
+-----------+
SELECT MOD(22.75,5);
+--------------+
| MOD(22.75,5) |
+--------------+
|         2.75 |
+--------------+

pow()

求第一个参数的第二个参数幂次方

SELECT POW(2,10) kilobyte, POW(2,20) megabyte, POW(2,30) gigabyte ,POW(2,40) terabyte;
+----------+----------+------------+---------------+
| kilobyte | megabyte | gigabyte   | terabyte      |
+----------+----------+------------+---------------+
|     1024 |  1048576 | 1073741824 | 1099511627776 |
+----------+----------+------------+---------------+

控制数字精度
ceil()、floor()

SELECT CEIL(72.445), FLOOR(72.445);
+--------------+---------------+
| CEIL(72.445) | FLOOR(72.445) |
+--------------+---------------+
|           73 |            72 |
+--------------+---------------+

round()、truncate()

round()四舍五入,提供第二个参数以指定在小数点右侧保留几位。truncate()只是简单去掉不需要的小数，不进行四舍五入

SELECT ROUND(72.0909,1), ROUND(72.0909, 2), ROUND(72.0909, 3);
+------------------+-------------------+-------------------+
| ROUND(72.0909,1) | ROUND(72.0909, 2) | ROUND(72.0909, 3) |
+------------------+-------------------+-------------------+
|             72.1 |             72.09 |            72.091 |
+------------------+-------------------+-------------------+

SELECT TRUNCATE(72.0909,1), TRUNCATE(72.0909, 2), TRUNCATE(72.0909, 3);
+---------------------+----------------------+----------------------+
| TRUNCATE(72.0909,1) | TRUNCATE(72.0909, 2) | TRUNCATE(72.0909, 3) |
+---------------------+----------------------+----------------------+
|                72.0 |                72.09 |               72.090 |
+---------------------+----------------------+----------------------+

sign()、abs()

sign()当负数返回-1，正数返回1,0返回0。abs为绝对值。

SELECT account_id, SIGN(avail_balance), ABS(avail_balance) FROM account WHERE account_id > 23;
+------------+---------------------+--------------------+
| account_id | SIGN(avail_balance) | ABS(avail_balance) |
+------------+---------------------+--------------------+
|         24 |                   1 |           23575.12 |
|         25 |                   0 |               0.00 |
|         27 |                   1 |            9345.55 |
|         28 |                   1 |           38552.05 |
|         29 |                   1 |           50000.00 |
+------------+---------------------+--------------------+

时间数据

必须的日期部件

DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MI:SS
TIMESTAMP	YYYY-MM-DD HH:MI:SS
TIME	HHH:MI:SS

直接利用内建函数获得当前日期和时间

SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();
+----------------+----------------+---------------------+
| CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() |
+----------------+----------------+---------------------+
| 2018-11-28     | 15:43:29       | 2018-11-28 15:43:29 |
+----------------+----------------+---------------------+

字符串到日期转换

str_to_date()函数利用第二个参数指明转换字符串的日期格式。

SELECT birth_date FROM individual WHERE cust_id = 3;
+------------+
| birth_date |
+------------+
| 1963-02-06 |
+------------+

UPDATE individual SET birth_date = STR_TO_DATE('September 17, 2008', '%M %d,%Y') WHERE cust_id=3;
Query OK, 1 row affected (0.30 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT birth_date FROM individual WHERE cust_id = 3;
+------------+
| birth_date |
+------------+
| 2008-09-17 |
+------------+

日期格式部件

%M	月名称（1月-12月）
%m	月序号（01-12）
%d	日序号（0-31）
%j	日在一年内的序号（001-366）
%W	星期名称（星期日-星期六）
%Y	四位数的年份表示（000999），%y 两位数的年份表示（00-99）
%H	24小时制小时数（0-23）
%i	分钟（00-59）
%M	月名称（1月-12月）
%s	秒（00-59）
%p	A.M或P.M

时间的内置函数
- date_add()

为指定日期增加任意一段时间间隔并产生另一个日期。第二个参数包含三个元素：interval关键字，所要增加的数量，时间间隔类型

时间间隔类型

Second	秒数
Minute	分钟数
Hour	小时数
Day	天数
Month	月份
Year	年份
Minute_second	分钟数和秒数，用‘：’隔开
Hour_second	小时数，分钟数和秒数，用‘：’隔开
Year_month	年份和月份，用‘-’隔开

为当前日期增加5天
SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY);
+------------------------------------------+
| DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY) |
+------------------------------------------+
| 2018-12-03                               |
+------------------------------------------+

transaction表中某个记录实际要晚3小时27分钟11秒
SELECT txn_date FROM transaction WHERE txn_id=3;
+---------------------+
| txn_date            |
+---------------------+
| 2008-01-05 00:00:00 |
+---------------------+
UPDATE transaction SET txn_date = DATE_ADD(txn_date, INTERVAL '3:27:11' HOUR_SECOND) WHERE txn_id=3;
+---------------------+
| txn_date            |
+---------------------+
| 2008-01-05 03:27:11 |
+---------------------+

出生日期增加9年11个月
SELECT start_date FROM employee WHERE emp_id=3;
+------------+
| start_date |
+------------+
| 2005-02-09 |
+------------+
UPDATE employee SET start_date=DATE_ADD(start_date, INTERVAL '9-11' YEAR_MONTH) WHERE emp_id=3;
+------------+
| start_date |
+------------+
| 2015-01-09 |
+------------+

last_day()

求本年本月的最后一天是多少号

SELECT LAST_DAY('2018-11-28');
+------------------------+
| LAST_DAY('2018-11-28') |
+------------------------+
| 2018-11-30             |
+------------------------+

dayname()

确定某一日期是星期几

SELECT DAYNAME('2018-11-28');
+-----------------------+
| DAYNAME('2018-11-28') |
+-----------------------+
| Wednesday             |
+-----------------------+

extract()

从日期值中提取信息

SELECT EXTRACT(YEAR FROM '2008-09-18 22:19:05');
+------------------------------------------+
| EXTRACT(YEAR FROM '2008-09-18 22:19:05') |
+------------------------------------------+
|                                     2008 |
+------------------------------------------+

datediff()

返回两个日期之间的天数

SELECT DATEDIFF('2009-09-03','2009-06-24');
+-------------------------------------+
| DATEDIFF('2009-09-03','2009-06-24') |
+-------------------------------------+
|                                  71 |
+-------------------------------------+

转换函数

使用cast()时，必须提供一个作为关键字的值或表达式，以及所需要转换的类型。

SELECT CAST('1456328' AS SIGNED INTEGER);
+-----------------------------------+
| CAST('1456328' AS SIGNED INTEGER) |
+-----------------------------------+
|                           1456328 |
+-----------------------------------+

如果过程中遇到非数字字符，那么转换将中止并且不返回错误。

SELECT CAST('999ABC111' AS SIGNED INTEGER);
+-------------------------------------+
| CAST('999ABC111' AS SIGNED INTEGER) |
+-------------------------------------+
|                                 999 |
+-------------------------------------+
SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '999ABC111' |
+---------+------+------------------------------------------------+

利用cast显式的转换字符串为date，datetime和time

SELECT CAST('2008-09-17 15:30:00' AS DATETIME);
+-----------------------------------------+
| CAST('2008-09-17 15:30:00' AS DATETIME) |
+-----------------------------------------+
| 2008-09-17 15:30:00                     |
+-----------------------------------------+

SELECT CAST('2008-09-17 ' AS DATE) date_field,CAST('108:17:57' AS TIME) time_field;
+------------+------------+
| date_field | time_field |
+------------+------------+
| 2008-09-17 | 108:17:57  |
+------------+------------+

SQL 数据处理操作字符串数值运算时间数据转换函数

字符串

内建字符串函数

数值运算

控制数字精度

时间数据

字符串到日期转换

时间的内置函数

转换函数

继续阅读

三种方式反转Python列表

python中几个常用的算术函数

大家要及时查看支付宝账单，发现不明扣费能第一时间处理，特别是一些自动月租扣费。取消会员续费详情您可在【支付宝首页】-【我

渐变不会调色总是剪出坑？总是剪的不细腻？1分钟教会你！渐变调色细节全流程。上面的色调一刀都不要剪，有些人调色的时候会发现

python、pandas、Excel、Powerbi中对日期的处理方法

vue实现左右滑动切换年份

[Language]Python中函数与函数式编程

Python3学习2023-6-20-时间处理问题

【转】python的内建函数:filter,map,reduce,lambda,sorted等

js 时间处理相关方法

日期格式转换，很实用！

python基础【二】

数据预处理-时间相关处理总结1 当天时间2 时间特征3 代码耗时

freemarker函数功能的使用之时间处理

Golang：carbon一个轻量级、语义化、对开发者友好的 golang 时间处理库

【python基础-字典】