Basic functions in Mysql

Basic functions in Mysql

Select curdate (); returns, excluding the hour, minute, and second select curtime (); returns 14:13:22, excluding the year, month, and day select now (); returns 10:46:17 select unix_timestamp (now ()); unix_timestamp (date) returns the UNIX timestamp of date select unix_timestamp ('2017-09-01 '); the Unix timestamp is the number of seconds since January 1, 2013, regardless of the run time, may encounter problems in 2038. Select from_unixtime (1184134516); returns 14:15:16, which is opposite to unix_timestamp (date. Select week (now (), year (now (); returns 36 and 2014, week (date) returns the week of the year, year (date) returns select hour (now (), minute (now (); -- returns 10 and 57. hour (date) and minute (date) will return the given time hour, minute. Select monthname (now (); returns September, returns the month in English

Format the date value date_format (date, fmt) and format the date value by fmt, so that the date can display the specific format as needed. Select date_format (now (), '% M, % D, % y'); 'September, 12th, 2014' select date_format (now (), '% Y-% m-% d % t'); '2017-09-12 11:06:03' is returned'

Select date_add (now (), INTERVAL 31 day) for the given date difference interval period; '2017-10-13 11:10:17 'is returned, which is the date after 31 days. Select date_add (now (), interval '1 _ 2' year_month); returns 11:10:55, which is the date after 1 year and 2 months select date_add (now (), interval 31 day); with select date_add (now (), interval, '-interval -2'); it can still be expressed as a negative number, query the date 31 days ago and the date one year and two months ago.

Select datediff ('2017-08-08 ', now (); Return-2008

String function connection string select concat ('A', 'bb', 'cc'); -- Return aabbcc select concat ('AA', null); -- return null, null is returned when it is connected to null.

Replace and insert select insert ('zhongguoren', 6,3, 'shance') according to the position; -- returns zhongshanren, starting from 6th characters (including 6th characters ), replace the three consecutive characters with 'shance' select insert ('zhongguoren', 'shanc'); -- returns zhongshanguoren and inserts 'shanc' before the six characters'

Replace select replace ('zhongguo', 'guo', 'shany') based on a specific string; -- Return 'zhongsha'

Lower case select lower ('beijing'), upper ('China ');

Returns the LEFT and RIGHT substrings select LEFT ('beijing2008 ', 7), RIGHT ('beijing2008', 4); -- returns being ing and 2008

Returns the substring select substring ('beijing2008 ', 4th) based on the position. -- returns 'jing2008'. Returns 4th characters (including characters and 8 consecutive characters)

Left and right fill select lpad ('20170101', 20, 'beijinging'), rpad ('beijinging', 20, '20160301'); Return beijingbeijingbe2008 and beijing200820082 lpad (str, n, pad) is to fill the pad string on the left until the entire string reaches n length.

Remove left and right spaces select ltrim ('beijinging'), rtrim ('beijinging'); -- Remove left and right spaces select trim ('beijinging ');

Repeated string select repeat ('mysql', 3); returns 'mysql mysql'

Select strcmp ('abcd', 'dbca '); -- returns-1 STRCMP (s1, s2). If s1 is smaller than s2,-1 is returned, equal return 0, greater than return 1.

Returns the absolute values of select abs (-0.8), abs (0.8); -- returns 0.8.

Ceiling integer select ceil (-0.8), ceil (0.8); -- returns 0 and 1

Floor integer select floor (-0.8), floor (0.8); -- returns-1 and 0

Returns the modulo select mod (5, 3), 5% 3; of x/y; -- returns the same result as 2 mod (x, y) and x % y.

Returns the random value select rand () between 0 and 1 ();

If you want to generate a random integer between 0 and 100 select ceil (* rand (); -- Return 84

Returns the rounded value of round (x, y). If y is not specified, y is 0 select round (1.1) by default. -- 1 select round (1.1, 2) is returned) -- returns 1.10 select round (103); -- returns 103 select round (100,-1); -- returns, accurate to 10

Returns the truncation value, which is similar to round (). The difference is that the truncation is not rounded to select truncate (1.235, 2); -- returns 1.23 select truncate (18.235,-1); -- returns 10

Select bin (4); -- returns 100

Flow function if (value, t, f) if value is true, return t; otherwise, return f if function like oracle decode select if (role_id = 13, 'distributor ', if (role_id = 14, 'delimer', null) as "role type" from upgrade. customer;

Replace the Null Value Function ifnull (v1, v2). If v1 is not empty, v1 is returned. Otherwise, v2 is returned.

Select case when salary <= 2000 then 'low' else 'high' end from salary;

Multi-branch case statement select case salary when 1000 then 'low' when 2000 then 'mid 'else' high 'end from salary;

Other functions database () Return Current database Name version () Return Current version user () Return Current login username inet_aton (ip) return ip address number represents inet_ntoa (num) return the IP address password (str) represented by the number. Return the encrypted str version md5 (str). Return the MD5 value of the str string.