Knowledge Points: Functions of basic Mysql usage

Function

Many built-in functions are available in MySQL

For example: SQL Built-in functions:

The mathematical function ROUND (x, y) returns the rounding of the parameter x with a Y decimal value RAND () returns a random value from 0 to 1, which can be generated by supplying a parameter (seed) to the rand () random number generator to generate a specified value 。 The aggregate function (in a select query commonly used in a GROUP BY clause) AVG (COL) returns the mean Count (col) of the specified column returns the number of non-null values in the specified column min (col) returns the minimum value of the specified column max (COL) returns the maximum of the specified column A value of SUM (COL) returns all values of the specified column and Group_concat (COL) Returns the result of a combination of column values that belong to a set of three, String function Char_length (str) return value is the length of the string str , the unit of length is a character.    A multibyte character counts as a single character.        CONCAT (STR1,STR2,...)    string concatenation if any one of the arguments is NULL, the return value is null.        Concat_ws (SEPARATOR,STR1,STR2,...) string concatenation (custom connector) Concat_ws () does not ignore any empty strings. (all nulls are ignored, however) CONV (n,from_base,to_base) binary conversions such as: SELECT CONV (' A ', 16,2); Represents the conversion of a from 16 to a 2 binary string representation of format (X,D) that formats the number X as' #,###,###.## ', which retains the D-bit after the decimal point in a rounded manner, and returns the result as a string. If D is 0, the result is returned without a decimal point, or with no fractional parts. For example: SELECT FORMAT (12332.1,4); The result: ' 12,332.1000 'Insert (STR,POS,LEN,NEWSTR) inserts the string at the specified location in str pos: To replace the location of the position Len: the length of the replacement newstr: New  String Special: If the Pos exceeds the original string length, the original string is returned if Len exceeds the original string length, then the new string is completely replaced by the INSTR (STR,SUBSTR) return string str    The first occurrence of a neutron string.    Left (Str,len) returns the substring character of the string Str from the beginning of the Len position.            LOWER (str) to lowercase UPPER (str) to uppercase REVERSE (str) returns the string str, in reverse order and character order. SUBSTRING (Str,pos), SUBSTRING (str from POS) SUBSTRING (Str,pos,len), SUBSTRING (str from POS for len) format without len parameter from The string str returns a substring starting at position pos. The format with the Len parameter returns a substring of the same length as the Len character from the string str, starting at position pos. Use the from format as standard SQL syntax. You may also use a negative value for the POS. If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string.        You can use a negative value for the POS in the following format function. MySQL> SELECT SUBSTRING (' quadratically ', 5); ' Ratically 'MySQL> SELECT SUBSTRING (' Foobarbar ' from 4); ' Barbar 'MySQL> SELECT SUBSTRING (' quadratically ', 5,6); ' Ratica 'MySQL> SELECT SUBSTRING (' Sakila ',-3); ' Ila 'MySQL> SELECT SUBSTRING (' Sakila ',-5, 3); ' Aki 'MySQL> SELECT SUBSTRING (' Sakila ' FROM-4 for 2); ' Ki 'the date and Time function curdate () or current_date () returns the current date Curtime () or Current_time () returns the current time DAYOFWEEK (date) returns D The day of the week on which ate was represented (1~7dayofmonth (date) returns the day ordinal of one months (1~31dayofyear (date) returns the day ordinal of a year (1~366dayname (date) returns the week name of date, such as: SELECT Dayname (current_date); From_unixtime (TS,FMT) Formats the UNIX timestamp TS HOUR (time) based on the specified FMT format to return the hour value (0~23) MINUTE (time) returns the minute value of time (0~59) month (date) returns the month value of date (1~12MONTHNAME (date) returns the month name of date, such as: SELECT MONTHNAME (current_date); Now () returns the current date and time QUARTER (date) Returns a date in the quarter of the year (1~4), such as Select QUARTER (current_date); WEEK (date) Returns a date of the week ordinal of a year (0~53year (date) returns the date of the day (1000~9999) Focus: Date_format (Date,format) formatting DATE values from the format string MySQL> SELECT date_format (' 2009-10-04 22:23:00 ', '%W%M%Y '); ' Sunday October 2009 'MySQL> SELECT date_format (' 2007-10-04 22:23:00 ', '%h:%i:%s '); ' 22:23:00 'MySQL> SELECT date_format (' 1900-10-04 22:23:00 ',        , '%d%y%a%d%m%b%j '); , ' 4th Thu Oct 277 'MySQL> SELECT date_format (' 1997-10-04 22:23:00 ',        '%H%k%I%r%T%s%w '); -10:23:00 PM 22:23:00 00 6 'MySQL> SELECT date_format (' 1999-01-01 ', '%x%V '); ' 1998 52 'MySQL> SELECT date_format (' 2006-06-00 ', '%d '); ' 00 'The cryptographic function MD5 () computes the string Str's MD5 checksum PASSWORD (str) returns the encrypted version of the string str, which is irreversible, and is used by the UNIX password encryption process        Different algorithms. Six, control flow function case When[test1] then [RESULT1] ... ELSE [default] END If TESTN is true, return RESULTN, otherwise return to default case [test] when[val1] Then [result] ... ELSE [default]end if test and VALN are equal, returns RESULTN, otherwise returns the default if (test,t,f) if test is true, returns T; otherwise returns F Ifnull (ARG1,ARG2) If Arg1 is not empty, return arg1, otherwise return arg2 Nullif (arg1,arg2) if Arg1=ARG2 returns NULL, otherwise returns ARG1

Examples of Date_format:

#1basic use of MySQL> SELECT date_format (' 2009-10-04 22:23:00 ', '%W%M%Y '); ' Sunday October 2009 'MySQL> SELECT date_format (' 2007-10-04 22:23:00 ', '%h:%i:%s '); ' 22:23:00 'MySQL> SELECT date_format (' 1900-10-04 22:23:00 ',    , '%d%y%a%d%m%b%j '); , ' 4th Thu Oct 277 'MySQL> SELECT date_format (' 1997-10-04 22:23:00 ',    '%H%k%I%r%T%s%w '); -10:23:00 PM 22:23:00 00 6 'MySQL> SELECT date_format (' 1999-01-01 ', '%x%V '); ' 1998 52 'MySQL> SELECT date_format (' 2006-06-00 ', '%d '); ' 00 '#2prepare table and record CREATE TABLE blog (id INT PRIMARY KEY auto_increment, NAME CHAR (32), Sub_time datetime); INSERT into blog (NAME, sub_time) VALUES (' 1th ', ' 2015-03-01 11:31:21 '),    (' 2nd ', ' 2015-03-11 16:31:21 '),    (' 3rd ', ' 2016-07-01 10:21:31 '),    (' 4th ', ' 2016-07-22 09:23:21 '),    (' 5th ', ' 2016-07-23 10:11:11 '),    (' 6th ', ' 2016-07-25 11:21:31 '),    (' 7th ', ' 2017-03-01 15:33:21 '),    (' 8th ', ' 2017-03-01 17:32:21 '),    (' 9th ', ' 2017-03-01 18:31:21 ');#3. Extract the value of the Sub_time field, followed by the format of the result is "year"to Group Select Date_format (Sub_time,'%y-%m '), COUNT (1) from the blog GROUP by Date_format (sub_time, '%y-%m '); #结果+-------------------------------+----------+| Date_format (sub_time, '%y-%m ') | COUNT (1) |+-------------------------------+----------+|        2015-03 | 2 | |        2016-07 | 4 | |        2017-03 | 3 |+-------------------------------+----------+3 rows in Set (0.00 sec)

One, Custom function

！！！ Attention!!! Do not write SQL statements in the function (otherwise it will error), the function is just a function, is a function that is applied in SQL to want to be in begin...end ... Write SQL, use the stored procedure

Examples of custom functions:

// Create function F1 (    int,    intintBEGIN     int;     = I1 + i2;     return  //delimiter;

Second, delete the function

Drop function Func_name;

Third, the executive function

   Gets the return value of select UPPER (' Egon ') into @res;　　SELECT @res; Use Select F1 (11,nid) in the query, name from TB2;

Attached: Mysql basic usage

First, "Mysql Basic usage view"

Second, "Mysql basic usage of the trigger"

Third, "Mysql basic usage of the business"

Iv. "Stored Procedures for Mysql basic usage"

V. "Functions of basic Mysql usage"

Vi. "Mysql Basic usage Process Control"

Knowledge Points: Functions of basic Mysql usage