Commonly Used Text-Manipulation Functions
---------------------------------------------------
Function
---------------------------------------------------
Left()
Length()
Locate()
Lower()
LTrim()
Right()
RTrim()
Soundex()
SubString() Returns characters from within a string
Upper()
---------------------------------------------------
(jlive)[crashcourse]>SELECT vend_name, UPPER(vend_name) AS vend_name_uppercase FROM vendors ORDER BY vend_name;
+----------------+---------------------+
| vend_name
+----------------+---------------------+
| ACME
| Anvils R Us
| Furball Inc.
| Jet Set
| Jouets Et Ours | JOUETS ET OURS
| LT Supplies
+----------------+---------------------+
6 rows in set (0.00 sec)
(jlive)[crashcourse]>SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Y. Lie';
Empty set (0.00 sec)
(jlive)[crashcourse]>SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y Lie');
+-------------+--------------+
| cust_name
+-------------+--------------+
| Coyote Inc. | Y Lee
+-------------+--------------+
1 row in set (0.00 sec)
“听起来”像，就匹配
时间操作函数
提示：5.3以后的版本支持microseconds
Commonly Used Date and Time Manipulation Functions
-------------------------------------------------------
Function Description
-------------------------------------------------------
AddDate() Add to a date (days, weeks, and so on)
AddTime() Add to a time (hours, minutes, and so on)
CurDate() Returns the current date
CurTime() Returns the current time
Date() Returns the date portion of a date time
DateDiff() Calculates the difference between two dates
Date_Add() Highly flexible date arithmetic function
Date_Format() Returns a formatted date or time string
Day() Returns the day portion of a date
DayOfWeek() Returns the day of week for a date
Hour() Returns the hour portion of a time
Minute() Returns the minute portion of a time
Month() Returns the month portion of a date
Now() Returns the current date and time
Second() Returns the second portion of a time
Time() Returns the time portion of a date time
Year() Returns the year portion of a date
-------------------------------------------------------
(jlive)[crashcourse]>SELECT order_date,cust_id,order_num FROM orders WHERE order_date = '2011-09-01';
+---------------------+---------+-----------+
| order_date
+---------------------+---------+-----------+
| 2011-09-01 00:00:00 |
+---------------------+---------+-----------+
1 row in set (0.00 sec)
以上判断非常不安全，这一天的记录可能非常多
(jlive)[crashcourse]>SELECT order_date,cust_id,order_num FROM orders WHERE Date(order_date) = '2011-09-01';
+---------------------+---------+-----------+
| order_date
+---------------------+---------+-----------+
| 2011-09-01 00:00:00 |
+---------------------+---------+-----------+
1 row in set (0.16 sec)
需要用Date()函数重新格式化
(jlive)[crashcourse]>SELECT order_date,cust_id,order_num FROM orders WHERE Date(order_date) BETWEEN '2011-09-01' AND '2011-09-30';
+---------------------+---------+-----------+
| order_date
+---------------------+---------+-----------+
| 2011-09-01 00:00:00 |
| 2011-09-12 00:00:00 |
| 2011-09-30 00:00:00 |
+---------------------+---------+-----------+
3 rows in set (0.15 sec)
上面虽然查出了2011年9月的数据但需要人为的计算该年该月份的起点和终点，如果碰到二月这个特殊的月份就会比较费力
(jlive)[crashcourse]>SELECT order_date,cust_id,order_num FROM orders WHERE Year(order_date) = 2011 AND Month(order_date) = 9;
+---------------------+---------+-----------+
| order_date
+---------------------+---------+-----------+
| 2011-09-01 00:00:00 |
| 2011-09-12 00:00:00 |
| 2011-09-30 00:00:00 |
+---------------------+---------+-----------+
3 rows in set (0.00 sec)
全部交给MariaDB自己去解读
Numeric函数
-------------------------------------------------------------------
Function Description
-------------------------------------------------------------------
Abs() Returns a number’s absolute value
Cos() Returns the trigonometric cosine of a specified angle
Exp() Returns the exponential value of a specific number
Mod() Returns the remainder of a division operation
Pi() Returns the value of pi
Rand() Returns a random number
Sin() Returns the trigonometric sine of a specified angle
Sqrt() Returns the square root of a specified number
Tan() Returns the trigonometric tangent of a specified angle
-------------------------------------------------------------------