MySQL database learning----MySQL function

MySQL functions are divided into several

1. Mathematical functions
2. String functions
3. Date and Time functions
4. Conditional Judgment function
5. System Information functions
6. Cryptographic functions
7. Formatting functions

One: Mathematical functions

A few mathematical functions used primarily

1 ABS ()----absolute value function

eg. SELECT ABS (-5);

Returns 5

2 pi ()----pi function

eg. SELECT PI ();

Returns 3.141596

3 sign ()----symbolic function

Positive returns 1,0 returns 0, negative returns-1

eg. SELECT sign (5), sign (0), sign (-5);

Back to 1,0,-1

4 RAND ()----random function

Returns the number of random numbers between 0~1

eg. SELECT RAND ();

Returns 0.92321421341234

If you need to return a random number of 0~100, select RAND () *100;

5 ceil (x)----integer function

Returns a minimum integer greater than or equal to X

eg. SELECT Ceil (9.1);

Returns 10

6 floor (x)----integer function

Returns a maximum integer less than or equal to X

eg. SELECT floor (9.1);

Returns 9

Mathematical functions and other sine functions, cosine functions and so on, there is no example.

Two: String functions

1 Length (s)----string lengths function

Returns the character length of the string s

eg. Select LENGTH ("MYSQL");

Returns 5

2 CONCAT (S1,s2,....) ----Combine string 1 and string 2 strings into a single string

eg. Select CONCAT ("MYSQL", "DATABASE");

Back to "Mysqldatabase"

3 UPPER (s)/lower (s)----string case function

UPPER (s): string s all uppercase

LOWER (s): string s all lowercase

4 INSTR (S,S1)----character position function in a string

The starting position of the character S1 in the string s

eg. INSTR ("MYSQL", "Y");

Returns 2

5 Left (S,n)/right (s,n)/substring (s,n,len)----string intercept function

Left (S,n): Returns the first n characters of the string s

Right (S,n): Returns the second n characters of the string s

SUBSTRING (S,n,len): Returns the nth character of a string starting with Len string

6 LTRIM (s)/rtrim (s)/trim (s)/trim (S1 from s)----remove spaces at either end of the string or specific character functions

LTRIM (s): Remove the left space of the string s

RTRIM (s): Remove the space to the right of the string s

TRIM (s): Remove the space on both sides of the string s

TRIM (S1 from s): Removes string s on both sides of the string s1

7 REPEAT (S,n)----repeating string function

eg. Select REPEAT ("MYSQL", 2);

Back to "Mysqlmysql"

Three: Date and time functions

MySQL date and time functions, divided into date functions, time functions are also the date and time together of the function, date function refers to the year, month, day these, time function is when, minutes, seconds function

1 curdate ()/current_date ()----Current date function

2 Curtime ()/current_time ()----Current time function

3 Now ()/localtime ()/sysdate ()/current_timestamp ()----Current date time function

4 month (D)----The monthly value of the current date

5 MONTHNAME (D) month name----the current date

6 DayOfMonth (D)----The current date is the day ordinal of this month

7 Dayname (D)----The current date is the day of the week (name)

8 DAYOFWEEK (D)----The current date is the day of the Week (value) 1 represents Sunday, 2 means Monday

9 WEEKDAY (D)----The current date is the day of the week (value) 0 represents Sunday, 1 means Monday

WEEK (D)----The current date is the first week (value) range of the year 0~53

WeekOfYear (D)----The current date is the first week (value) range of the year 1~53

(D)----The current year value

HOUR (T)----The current time value

MINUTE (T)----The minute value of the current time

SECOND (T)----The second value of the current time

Adddate (d,n)----Date of the current date backward N days

Subdate (d,n)----Date of the current date forward N days

Addtime (t,n)----The current time plus N seconds

Subtime (t,n)----current time minus N seconds

Four: Conditional judgment function

1 IF (conditional expression, S1, S2)----judgment statement

If the conditional expression is true, the string is returned S1

If the conditional expression is false, the string is returned s2

eg. Suppose there is a table demo

Id	Count
1	90
2	50

SELECT id,count,if (count>=60, ' PASS ', ' FAIL ') as status from demo;

Id	Count	Status
1	90	PASS
2	50	FAIL

2 ifnull (S1, S2)----to determine if it is a null function

Returns S1 if S1 is not NULL, otherwise returns s2

3 Case function

Case-when conditional expression 1 then S1 when conditional expression 2 then s2 ... END

Case field when conditional expression 1 then S1 when conditional expression 2 then s2 ... END

Case begins .... End

Both of these are the same meaning, which returns S1 when the conditional expression 1 is true, and returns S2 when the conditional expression 2 is true

eg. Suppose there is a table demo

Id	Count
1	90
2	50

SELECT ID, Count case when count>60 ' PASS ' when count<60 ' FAIL ' END as status from demo;

Id	Count	Status
1	90	PASS
2	50	FAIL

V: System Information function

1 version ()----MySQL release function

2 connection_id ()----Database connection number function

3 database ()----The name function currently in use

4 user ()----Current Access database username function

Some of the other system information functions are not often used, here are not examples

Six: Cryptographic functions

1 PASSWORD (s): cryptographic functions, usually encrypted to the user's password, is an irreversible cryptographic function

2 MD5 (s): MD5 encryption, is an irreversible cryptographic function

3 ENCODE (S,S1): Encrypt string s with character S1

4 DECODE (S,S1): decrypts string s with character S1

Seven: Other functions

1 format (x,n): Formatting functions, rounding x values and preserving n digits after the decimal point

2 Inet_aton (IP): convert IP to digital representation

3 Inet_ntoa (IP): Converts a number to an IP representation

MySQL database learning----MySQL function