Mysql basic functions and custom function usage

What are Mysql functions

A Java-like approach wraps a set of logical statements in the method body exposing the method name to the outside world

Some features provided in advance can be used directly

Functions can be used on select statements and their clauses

It can also be used in update, delete statements

Function classification

• 1) String function
• 2) Numerical function
• 3) Date and time function
• 4) Process function
• 5) Aggregate function
• 6) Custom functions
• 7) Other functions

String function

• concat(s1,s2… sn): Joins the incoming string into a string.

Note: Any string concatenated with null results in null

• insert (srt, x,y,instr): Replaces the string str with the specified string starting at x bits and y characters long substring

• lower(str)andupper(str): Converts the string to case
• left (str,x)andright(str,x): Returns the leftmost x characters and the rightmost X characters of the string, respectively

• lpad(str,n,pad)andrpad (str.n,pad): Fill the leftmost or rightmost string with string pad, directly to the length of n characters

• ltrim(str)andrtim(str): Removes the leftmost and leftmost Spaces in the string
• trim(str): Removes the leftmost space with the rightmost space
• repeat(str, x): The query returns the result of repeating str x times

• REPLACE (str, a,b): Replaces all strings in str where a occurs with string b

• substring(str,x,y): Returns a character of y length from position x in the string str

Numerical function

select MOD(10,3); remainder

SELECT truncate(15.344323,2); Truncate how many decimals to keep

SELECT FLOOR(1.23); - 1 Round down SELECT FLOOR(1.99). -1 SELECT FLOOR(-1.23); -- 2 SELECT FLOOR(-1.99); -- 2 SELECT floor(1.0);

SELECT CEILING(1.23); - 2 Round up >=(that is, add one to the top, there must be a decimal, add one to the top) SELECT CEILING(1.99); - 2 SELECT CEILING(-1.23); -- -1 SELECT CEILING(-1.99); - -1 SELECT CEIL(1.23); - 2 SELECT CEIL(1.99); - 2 SELECT CEIL(-1.23); -- -1 SELECT CEIL(-1.99); - -1 SELECT ceil(1.5);

Rounding function

ROUND(X) ROUND(X,D)

- Returns the parameter X whose value is close to the nearest integer.

- In the case of two arguments, return X, whose value is reserved to the D decimal place, and the D decimal place is reserved by rounding.

- To preserve the D bit to the left of the decimal point of the X value, set D to negative

SELECT ROUND(1.5)

SELECT ROUND(2.5), ROUND(25E-1);

This is a question of precision.

Returns the argument as a sign of -1, 0, or 1, depending on whether the value of X is negative, zero, or positive. Negative numbers return -1, 0 returns 0, positive numbers return 1

SELECT SIGN(-2); – -1
SELECT SIGN(0); – 0
SELECT SIGN(2); – 1

TRUNCATE(X,D)

- Returns the number X that has been rounded to D decimal places. If the value of D is 0, the result has no decimal point or fractional part.

- You can set D to negative if you want to cut off (return to zero) all lower values after the DTH place from the left of the X decimal point.

SELECT TRUNCATE(1.233,1); - 1.2 SELECT TRUNCATE(1.9565,1); - 1.9 SELECT TRUNCATE(1.988,0); -1 SELECT TRUNCATE(-1.998,1); -- -1.9 SELECT TRUNCATE(1224,-2); - 1200 SELECT TRUNCATE(10.24 x 100,0). - 1024

Date and time functions

select CURDATE(); - The current time contains only the year, month and day

select CURTIME(); -- The current time contains only minutes and seconds

select NOW(); -- Return the current date and time, including the year, month, day, hour, and second

select UNIX_TIMESTAMP(); -- Returns the current timestamp

SELECT FROM_UNIXTIME(1550371079); -- Converts the timestamp to the current year, month, day, minute and second

SELECT WEEK(‘2008-05-02’); -- This function returns the number of weeks of the date

SELECT YEAR(‘2019-09-09’); - Return the date given is the year

SELECT HOUR(‘11:15:00’); -- Returns the hour of the current time

SELECT MINUTE(‘12:01:00’); -- Returns the minutes of the current time

DATE_FORMAT(date,format)

format the date value according to the format string.

%a short name for a working day (Sun... Sat) %b Abbreviated name of the month (Jan... Dec) %c month, numeric form (0... 12) %D Date of the month with English suffixes (0th, 1st, 2nd, 3rd,...) %d Date of the month, in numerical form (00... 31) %e The date of the month, in numeric form (0... 31) %f microsecond (000000... 999999) %H hours (00... 23) %h hours (01... 12) %I hours (01... 12) %i minutes, in numeric form (00... 59) %j Number of days in the year (001... 366) %k hours (0... 23) %l hours (1... 12) %M Name of the month (January... December) %m month, number form (00... 12) %p AM (AM) or PM (PM) %r time, 12-hour system (hours hh: minutes mm: seconds ss followed by AM or PM) %S seconds (00... 59) %s seconds (00... 59) %T time, 24 hours (hours hh: minutes mm: seconds ss) %U weeks (00... 53), where Sunday is the first day of the week %u week (00... 53), where Monday is the first day of the week %V week (01... 53), where Sunday is the first day of the week; Use %v cycle with %X (01... 53), where Monday is the first day of the week; Use %W workday name with %x (Sunday... Saturday) %w Every day of the week (0= Sunday... 6= Saturday) %X the year of the week, where Sunday is the first day of the week, numerical form,4 digits; Use %X with %V the year of the week, where Monday is the first day of the week, numeric form,4 digits; And %v simultaneously use %Y year, numeric form, 4-digit %Y year, numeric form (2-digit) %% '%' text character

Examples:

SELECT DATE_ADD(‘2015-01-04',INTERVAL 3 DAY);

-- Calculate the interval of time (+, -)

SELECT DATEDIFF(‘2018-12-01',NOW()); 

- Calculate the number of days between the two times

Flow function

select if(2>3,‘true',‘false'); 

- Return true if the value is true otherwise false

case

SELECT IFNULL(NULL,1); 

- If value 1 is not null, 1 is returned. Otherwise, value 2 is returned

SELECT CASE WHEN 2<3 THEN 'true' ELSE 'wrong' end;

- Judgment

Custom function

Concept:

A user-defined function UDF extends MySQL with a new function that acts like an inherent (built-in) function like ABS() or CONCAT().

So UDF is an extension of the functionality of MySQL

(1) Create a UDF:

CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,…] ) RETURNS {STRING|INTEGER|REAL} runtime_body

(In short:

• CREATE FUNCTION Function name (parameter list)
• RETURNS Indicates the return value type

Function body)

(2) DROP FUNCTION function_name to delete UDF

SELECT function_name(parameter_value,...)

UDFCREATE FUNCTION simpleFun()RETURNS VARVHAR(20) RETURN "Hello World!" ;

create function f1() returns varchar(30) Create function F1 () Returns VARCHAR (30)

return date_format(now(),'%Y year %m month %d day %H hour %i minute %s second ');

Call this function: select f1();

(7) The argument to the CAST() function is an expression that contains the source value and the target data type separated by the AS keyword.

Other functions

SELECT DATABASE(); - Returns the current database name

select VERSION(); -- Returns the database version number

select USER(); -- Current user

elect password(123); -- Password encryption

select MD5(123); -- Password encryption

SELECT SHA1(‘abc’); SHA1() can be considered a more cryptographically secure function

SELECT SHA(‘abc’);

Sum up

The above is personal experience, I hope to give you a reference, but also hope that you support the script home.

Latest comments