目录

字符串

 内建字符串函数

数值运算

控制数字精度

时间数据

字符串到日期转换

时间的内置函数

转换函数

字符串

在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 |
+------------+---------------------+--------------------+

时间数据

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

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 |
+------------+

• 时间的内置函数

  • date_add()

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

为当前日期增加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 数据处理操作字符串数值运算时间数据转换函数的全部内容，希望文章能够帮你解决SQL 数据处理操作字符串数值运算时间数据转换函数所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。