字元串

内建字元串函數

數值運算

控制數字精度

時間資料

字元串到日期轉換

時間的内置函數

轉換函數

字元串

在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基礎-字典】