Mysql: Common functions

Common functions

Total: Select count as TotalCount from table1

Sum: Select SUM (field1) as Sumvalue from table1

Average: Select AVG (field1) as Avgvalue from table1

Maximum: Select Max (field1) as MaxValue from table1

Min: select min (field1) as MinValue from table1

Replacement: replace (' 1234567890 ', ' 345678 ', ' 0 '); #替换输出12090

Absolute Value: ABS (-1)

Random number: rand () #生成0到1之间随机数

Take character: substring (' 12345 ',1,2) # output 12 1 is position 2 is length

ASCII value: ASCII (' a ') # 97

Rest time: Sleep (0.02) #暂停秒数

Reverse output: Reverse (' 1234 '); # 4321

Get Username: User ()

Encrypted string: MD5 (' a ')

MySQL Edition: Version ()

Currently connected databases: Database () #没有为null

Returns the first two characters and the last two character: Left (' ABCD ',2), right (' ABCD ',2

Date function Date_format ()

Displays a different format.

Date_format (now (), '%b%d%Y%h:%i%p ') # Dec 11:45 PM

Date_format (now (), '%m-%d-%y ') #12-29-2008

Date_format (now (), '%d%b%y ') #29 Dec 08

Date_format (now (), '%d%b%Y%t:%f ') #29 DEC 2008 16:25:46.635

Other functions

Now () #返回日期时间

Current () #返回日期

Curtime () #返回时间

Year (now ()) #当前年

Month (now ()) #当前月

Day (now ()) #当前日

Hour (now ()) #当前时

Minute (now ()) #当前分

Second (now ()) #返回秒

Dayname (now ()) Week English

DayOfWeek () #1表示星期日, 2 = Monday

DayOfYear (now ()) #今天是本年第多少天

DayOfMonth (now ()) #今天是本月第多少天

Weekday () #0表示星期一, 1 = Tuesday

Week (now ()) #本年第多少周

Time_to_sec (now ()), Sec_to_time (3600*8); #转换时间为秒和还原

Mysql: Common functions