"Database" MySQL deep

Reprint please specify the original address: http://www.cnblogs.com/ygj0930/p/5866388.html

MySQL Common function Comb:

1: Mathematical Functions

Use format: Generally used to insert, modify the statement, the direct function (parameter) can be used to insert, modify the return result.

RAND (): Random number generation.

ROUND (x, y): Gets the Y-bit of X rounded decimal.

Log (x, y): Gets the logarithm of x as the base and Y.

SQRT (x): Gets the square root of x.

Application Examples:

INSERT into test values (RAND (0), ROUND (4.5462,3), LOG (2,8), SQRT (9));
SELECT * from Test;

Results:

0,4.54600000000000000000000000000,3.0000000000000000000000000000000,3.0000000000000000000000000000000

2: Aggregation function

Use format: Aggregate functions are generally used in conjunction with the group BY statement, and can also be used to count the whole table, the whole column.

SQL aggregation functions

function	Description
AVG ()	Returns the average of a column
COUNT ()	Returns the number of rows (that is, the number of records) for a column/group/whole table
MAX ()	Returns the maximum value of a column
MIN ()	Returns the minimum value of a column
SUM ()	Returns the sum of a column

Examples:

Select COUNT (*) from the test GROUP by ID;

Select AVG (grade) from student;

Select MAX (grade) from student;

Select MIN (grade) from student;

Select SUM (grade) from student;

3: String function

Use format for string functions: Select function (parameter) from table;

CONCAT (STR1,STR2,...): Returns the string resulting from the connection parameter. If any one of the arguments is NULL, the return value is null.

ASCII (str): Returns the value of the leftmost character of the string str. If Str is an empty string, the return value is 0. If STR is NULL, the return value is null. ASCII () is used for characters with numeric values from 0 to 255.

BIN (N): A string that returns the binary value of N.

Char_length (str): The return value is the length of the string str, and the length of the unit is a character.

CONV (str,from_base,to_base): Different binary conversions. Returns a string representation of the number of STR strings that are converted from from_base into to_base binary.

ELT (N,STR1,STR2,STR3,...) : Returns the nth parameter after the first argument. returns if n = 1, the return value is str1, and if n = 2, the return value is str2, and so on.

FIELD (STR,STR1,STR2,STR3,...) : Return to STR1, str2, Str3,...... The number of STR in the list. If STR is not found, the return value is 0.

Find_in_set (str,strlist): If the string str is in the string list strlist, it returns its position in the list. A list of strings is a string of self-chains that are separated by ', ' symbols.

Format (X,D): Sets number X to the format ' #,###,###.## ', which is rounded to the D-bit after the decimal point, and the result is a string.

INSERT (STR,POS,LEN,NEWSTR): The position of the POS to Len length of the string str is replaced by NEWSTR.

INSTR (STR,SUBSTR): Returns the first occurrence of a string str substr.

POSITION (substr in str): Returns the substring substr the first occurrence of the string str, if SUBSTR is not inside STR, returns 0.

LOCATE (substr,str,pos) returns a substring substr the first occurrence of the string str, starting at position pos. If the substr is not inside STR, return 0.

Left (Str,len): Returns the substring of the string str from the long len.

Right (Str,len): Starts with the string str and returns the most-True Len character.

Length (str): The value of the return string str, in bytes.

LOWER (str): Turns str all lowercase.

REPEAT (str,count): Str repeats a new string of count times.

Replace (STR,FROM_STR,TO_STR): Replaces the FROM_STR content in Str with TO_STR.

REVERSE (str): Reverse str.

Rpad (STR,LEN,PADSTR): Fills str with PADSTR to Len length.

SUBSTRING (Str,pos), SUBSTRING (str from POS): String str Returns a substring starting at position pos.

SUBSTRING (Str,pos,len), SUBSTRING (str from POS for len): string str Returns a substring of the same length as the Len character, starting at position pos.

TRIM ([{BOTH | Leading | TRAILING} [REMSTR] from] str), TRIM (Remstr from str): Removes remstr from str.

UPPER (str): Convert str to uppercase.

4: Date and Time functions

Now (): Returns the statement "Note that this is the statement, not the function." The difference is that the following sysdate () function "runs at a specific datetime.

Eg:select now ();

Re:2016-09-12 21:25:06

Sysdate (): The DateTime function is similar to now (), except that now () is obtained at the start value of the statement execution, and sysdate () dynamically gets the value when the function executes.

Mysql> Select Now (), Sleep (3), now ();

+---------------------+----------+---------------------+
| Now () | Sleep (3) | Now () |
+---------------------+----------+---------------------+
|        2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |
+---------------------+----------+---------------------+

Mysql> Select Sysdate (), Sleep (3), sysdate ();

+---------------------+----------+---------------------+
| Sysdate () | Sleep (3) | Sysdate () |
+---------------------+----------+---------------------+
|        2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 |
+---------------------+----------+---------------------+

As you can see, the time value of the now () function two times is the same, although sleep is 3 seconds in the middle, and the time value of the sysdate () function is 3 seconds apart from two.

Curdate (): Gets the current date.

Curtime (): Gets the current time function.

Time selection function:

Select various parts of the DateTime: Date, time, year, quarter, month, day, hour, minute, second, microsecond

set  @dt  =  ' 2008-09-10 07:15:30.123456 ';

Select date (@dt);         -- 2008-09-10
select  Time (@dt);         -- 07:15:30.123456
Select year (@dt);         -- 2008
Select quarter (@dt);      -- 3
Select month (@dt);        -- 9
Select  week (@dt);         -- 36
Select day (@dt);          -- 10
Select hour (@dt);         -- 7
Select minute (@dt);       -- 15
Select second (@dt);       -- 30
Select microsecond (@dt);  -- 123456

Dayname (), MonthName (): Returns the name of the week and month.
Select Dayname (@dt); --Friday
Select MonthName (@dt); --August:

WEEKDAY (date): Returns the week index of date (0= Monday, 1 = Tuesday, ...). 6= Sunday).

DayOfMonth (date): Returns the month of date in the range of 1 to 31.

DayOfYear (date): Returns the number of days in a year, in the range 1 to 366.

Month (date): Returns the month of date, ranging from 1 to 12.

Dayname (date): Returns the day of the week name, for example: Friday.

MONTHNAME (date): Returns the month name of the date.

QUARTER (date): Returns the quarter of a date year, ranging from 1 to 4.

5: Cryptographic decryption function

ENCODE (,) DECODE (,): encrypts the decrypted string. The function has two parameters: a string that is encrypted or decrypted, and a key that is the basis for encryption or decryption. The encode result is a binary string that is stored as a BLOB type.

Encrypt INSERT into users (username, password) VALUES (' Joe ', ENCODE (' guessme ', ' Abracadabra '));

Decrypt SELECT DECODE (password, ' Abracadabra ') from the users WHERE username= ' Joe ';

MD5 (): Computes the MD5 checksum (128 bits) of the string.

SHA5 (): Computes the SHA5 checksum of a string (160-bit)

6: Formatting functions

Format (x, y): formats x as a comma-separated sequence of numbers, and Y is the number of decimal digits of the result. At the same time, the results are divided by 3 digits, like the amount expressed.

The date_format (date,fmt) and Time_format (TIME,FMT) functions can be used to format date and time values: These two functions accept a date or time value and a formatted string that specifies the result format. This formatted string contains a special symbol. Different symbols represent different content, and a string is a series of optional symbols, so that the date or time is expressed in the format of the string representation.

Specifiers	Description
%a	Abbreviated name of the weekday (Sun.. Sat)
%b	Abbreviated name of the month (Jan: DEC)
%c	month, digital form (0..12)
%d	Date of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%d	Date of the month, digital form (00..31)
%e	Date of the month, digital form (0..31)
%f	microseconds (000000..999999)
%H	Hours (00..23)
%h	Hours (01..12)
%I	Hours (01..12)
%i	Minutes, digital form (00..59)
%j	Number of days in the year (001..366)
%k	Hours (0..23)
%l	Hours (1..12)
%M	Month name (January): December)
%m	month, digital form (00..12)
%p	Morning (AM) or PM (PM)
%r	Time, 12 hours (hours hh: minutes mm: seconds SS after plus am or PM)
%s	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24 hours (hours hh: minutes mm: Seconds ss)
%u	Week (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), of which Sunday is the first day of the week; Simultaneous use with%x
%v	Week (01..53), of which Monday is the first day of the week; Simultaneous use with%x
%W	Weekday Name (Sunday: Saturday
%w	Daily of the Week (0= Sunday). 6= Saturday)
%x	The year of the week, of which Sunday is the first day of the week, the number form, 4 digits; and%v use simultaneously
%x	The year of the week, of which Monday is the first day of the week, the number form, 4 digits; and%v use simultaneously
%Y	Year, number form, 4 digits
%y	Year, digital form (2 digits)
%%	'% ' literal characters

"Database" MySQL deep