MySQL experience 6 -- MySQL language structure -- Function _ MySQL

MySQL tips 6-MySQL language structure-function bitsCN.com 1. mathematical functions are used to perform complex arithmetic operations. MySQL supports many mathematical functions. If an error occurs, all mathematical functions return NULL. (1) greatest () and least () functions are commonly used in mathematical functions. Their function is to obtain the maximum and minimum values of a set of numbers. For example: SELECTGREATEST (, 1), LEAST (, 3); mathematical functions can also be nested for use, for example: select greatest (-2, LEAST )), LEAST (1, GREATEST (1, 2); note: MySQL does not allow spaces between function names and parentheses. (2) FLOOR () and CEILING () functions floor () are used to obtain the maximum integer smaller than a single number. the ceiling () function is used to obtain the smallest integer greater than a single number. for example: select floor (-1.2), CEILING (-1.2), FLOOR (9.9), CEILING (9.9); result:-2,-, 10; (3) ROUND () and the TRUNCATE () function round () is used to obtain the rounding integer of a number: SELECTROUND (5.1), ROUND (25.501), ROUND (9.8); the result is, 10; truncate () function is used to intercept a number into a number with a specified decimal number. The number following the comma indicates the number of the specified decimal number: select truncate (1.54578, 2 ), TRUNCATE (-76.12, 5); result: 1.54,-76.12000; (4) ABS () function: used to obtain a number The absolute value (5) sign () function returns the symbol of a number. the returned result is positive (1), negative (-1), or zero (0): select sign (-2 ), SIGN (2), SIGN (0); result:-, 0; (6) sqrt () function returns the square root of a number (7) POW () function pow () the function uses a number as the index of another number and returns the following results: SELECTPOW (0.01), POW (10,-2), POW (); results: 4, 0; (8) SIN (), COS (), TAN (), ASIN (), ACOS (), and ATAN () functions return an angle (radian) the degrees () and radians () functions can be used to convert the sine, cosine, tangent, arcsin, arccosine, and arctangent values of a given number. (9) BIN (), OTC () and HEX () functions BIN (), OTC (), and HEX () the function returns the binary, octal, and hexadecimal values of a number as a string. 2. the aggregate function MySQL has a set of functions designed specifically to sum or summarize the data in the table. This group of functions is called aggregate functions. Aggregate functions are often used to calculate a group of values and then return a single value. By adding aggregate functions (such as COUNT and SUM) to the SELECT statement block with a GROUPBY clause, data can be aggregated. Aggregation means finding a sum, average, frequency, sum, rather than a single value. 3. string functions because the MySQL database not only contains numeric data but also Strings, MySQL has a set of functions designed for string operations. In a string function, the contained strings must be enclosed in single quotes. (1) ASCII () function syntax format: ASCII (char) returns the ASCII value of the leftmost character of the character expression. The char type is a regular expression, and the return value is an integer. For example, return the ASCII value of letter. SELECTASCII ('A'); result: 65 (2) CHAR () function syntax format: CHAR (x1, x2, x3 ,...) Set x1, x2 ...... The ASCII code is converted into characters, and the result is combined into a string. Parameters x1, x2, x3 ...... It is between 0 and ~ An integer between 255. The return value is of the dense type. For example, a string consisting of 65, 66, and 67 ASCII values is returned. SELECTCHAR (65,66, 67); result: ABC (3) LEFT and RIGHT function syntax format: left | right (str, x) returns x characters starting from the left and right sides of the str string. Example: select left (what is the course, 2) execution result: Course; (4) TRIM, LTRIM, and RTRIM function (delete) syntax format: trim | ltrim | rtrim (str) use LTRIM and RTRIM to delete the leading (left) space and trailing (right) space in the string respectively. The return value is the string. The str parameter is a struct expression, and the return value type is varchar. TRIM deletes all spaces at the beginning and end of the string. (5) syntax format of RPAD and LPAD functions: rpad | lpad (str, n, pad) use RPAD and LPAD respectively to fill the right and left sides of the str string with string pad until the number of characters in str reaches n, and finally return the filled string. If the number of characters in str is greater than n, the first n characters in str are returned. For example, run the following statement: select rpad ('China restart', 8 ,'! '), LPAD ('Welcome', 10, '*'); the result is: China !!!! (8 in total), *** welcome (10 in total); (6) syntax format of the REPLACE function: replace (str1, str2, str3) the REPLACE function is used to REPLACE str2 in str1 with str3. Returns the replaced string. For example, run the following statement: SELECTREPLACE ('welcome to China', 'O', 'k'); the result is: WelcKme tK CHINA (7) CONCAT function syntax format: concat (s1, s2 ,... Sn) the CONCAT function is used to connect several specified strings. For example, run the following statement: select concat ('China', 'restart'). The result is: China (8) SUBSTRING function syntax format: substring (expression, Start, Length) returns part of the data specified in expression. The expression parameter can be a string, binary string, text, image field, or expression.

Both Start and Length are integer types. The former indicates the Start position of the stator string and the latter indicates the Length of the stator string (the number of bytes to be returned ). If expression is of the character type and binary type, the return value type is the same as the expression type. If it is of the text type, varchar is returned. For example, the following program returns the last names of all female students in the XS table in one column and the names in the other column. USEXSCJ; select substring (name, 1, 1) AS surname, SUBSTRING (name, 2, LENGTH (name)-1) AS name from xs where gender = 0 order by name; description: the LENGTH function returns the LENGTH of a string. (9) syntax format of the STRCMP function: the strcmp (s1, s2) STRCMP function is used to compare two strings. equal returns 0, s1 is greater than s2 returns 1, s1 is less than s2 returns-1. For example, execute the following statement: select strcmp ('A', 'A'), STRCMP ('ABC', 'opq'), STRCMP ('t', 'B '); result: 0,-; 4. date and time functions MySQL has many date and time data types, so there are quite a few date and time functions. (1) NOW () use the NOW () function to get the current date and time, which returns the current date and time in YYYY-MM-DD HH: MM: SS format: (2) the functions CURTIME (), CURDATE (), curtime (), and curdate () are more specific than those of NOW. they return the current time and date, respectively, with no parameter: (3) YEAR () the YEAR () function analyzes a date value and returns the section about the YEAR: SELECTYEAR (20080512142800), YEAR ('1996-11-02 '). The result is 1982.; (4) the MONTH (), MONTHNAME () month (), and monthname () functions return the part of the MONTH in numeric and string format: (5) DAYOFYEAR (), DAYOFWEEK () and the DAYOFMONTH () dayofyear (), DAYOFWEEK () and DAYOFMONTH () functions return the day in one year, one week, respectively. And the ordinal number in a month: (6) DAYNAME () is similar to MONTHNAME (). dayname () returns the WEEK name in string format: (7) WEEK () and YEARWEEK () the WEEK () function returns the WEEK number of the year for the specified date, and the YEARWEEK () function returns the WEEK of the year for the specified date: (8) HOUR () the MINUTE () and SECOND () HOUR (), MINUTE () and SECOND () functions return the hours, minutes, and seconds of the time value respectively: (9) DATE_ADD () and DATE_SUB () DATE_ADD () and DATE_SUB () functions can perform arithmetic operations on the date and time. They are used to increase and decrease the date value respectively. the keywords used are shown in the following table. Format of the delimiter value format of the delimiter value DAY date MINUTE DAY_HOUR date: hour MINUTE _ second minute: SECOND DAY_MINUTE date: Hour: minute month DAY_SECOND date: Hour minute: second hour year HOUR_MINUTE HOUR: minute YEAR_MONTH YEAR-month HOUR _ second hour: minute: SECOND DATE_ADD () the syntax format of the and DATE_SUB () functions is: DATE_ADD | DATE_SUB (date, INTERVAL int keyword) date is the required date and time, and the INTERVAL keyword indicates a time INTERVAL. Int indicates the time value to be calculated. keyword is already listed in the table above. The DATE_ADD function is used to calculate the value after the date plus the interval, and DATE_SUB is used to calculate the value after the date minus the interval. Example: SELECT DATE_ADD ('2017-08-08 ', INTERVAL 17 DAY); 5. encryption function (not important) MySQL specially designed some functions to encrypt data. Here we will briefly introduce the following functions. (1) the syntax format of the AES_ENCRYPT and AES_DECRYPT functions is: aes_encrypt aes_decrypt (str, key) AES_ENCRYPT function returns the key pair String str encrypted using the Advanced Encryption Standard (AES) algorithm, the result is a binary string stored as BLOB. The AES_DECRYPT function is used to decrypt data encrypted by advanced encryption. If invalid data or incorrect filling is detected, the function returns NULL. The AES_ENCRYPT and AES_DECRYPT functions can be seen as the most secure encryption functions commonly used in MySQL. (2) the syntax format of the ENCODE and DECODE functions is: encode | deCODE (str, key) ENCODE function is used to encrypt a string 'str'. the returned result is a binary string, it is stored as BLOB. The DECODE function uses the correct key to decrypt the encrypted value. Compared with the above AES_ENCRYPT and AES_DECRYPT functions, these two functions are relatively weak in encryption. (3) the ENCRYPT function uses the UNIX crypt () system to encrypt the string, ENCRYPT (str, salt) the function receives the string to be encrypted and the salt used for the encryption process (a string that can identify a unique password ). Unavailable on Windows. (4) PASSWORD function format: password (str) returns the string str encrypted PASSWORD string, suitable for inserting into the MySQL security system. This encryption process is irreversible and uses different algorithms than the UNIX password encryption process. It is mainly used for the authentication system of MySQL. For example, the encrypted version of the string "MySQL" is returned. SELECTPASSWORD ('mysql'); 6. control flow functions MySQL has several functions used for conditional operations. These functions can implement the SQL conditional logic, allowing developers to convert some application business logic to the database background. (1) the syntax format of IFNULL and NULLIF functions is: IFNULL (expr1, expr2). The function is used to determine whether the expr1 parameter is NULL, if expr1 is NULL, expr2 is returned. if not NULL, expr1 is returned. The returned value of IFNULL is a number or string. For example, run the following statement: select ifnull (1/0), IFNULL (NULL, 'mysql'), IFNULL (10.0000, 10); result: 1, MySQL; the syntax format of the NULLIF function is: NULLIF (expr1, expr2) NULLIF function is used to check whether two provided parameters are equal. if the two parameters are equal, NULL is returned. if the two parameters are not equal, the first parameter is returned. For example, run the following statement: SELECTNULLIF (), NULLIF ('A', 'B'), NULLIF (2 + 3, 3 + 4); the result is null,, 5; (2) like the IF () function provided by many scripting languages, MySQL's IF () function can also create a simple conditional test. Syntax format: IF (expr1, expr2, expr3) this function has three parameters, the first is the expression to be judged, IF the expression is true, IF () the second parameter is returned; IF () is false, the third parameter is returned. For example, if the value of 2*4 is greater than 9-5, "Yes" is returned; otherwise, "no" is returned ". SELECTIF (2*4> 9-5, 'yes', 'no'); result: yes; example: returns the student name, gender, and major name whose XS table name is two characters. If the gender value is 0, it is displayed as "female". if the gender value is 1, it is displayed as "male ". SELECT name, IF (Gender = 0, 'female ', 'male') AS gender, professional name fromxs where name LIKE' _ '; 7. the formatting function MySQL also has some functions designed specifically for formatting data. (1) the syntax FORMAT of the format () function is FORMAT (x, y) FORMAT (). The value is formatted as a numerical sequence separated by commas. The first parameter x of FORMAT () is the formatted data, and the second parameter y is the decimal point of the result. For example: select format (111111.23654, 2), FORMAT (-5468,4); result: 111111.23,-5468.0000; (2) DATE_FORMAT () and TIME_FORMAT () the function is used to format the date and time values. Syntax format: DATE_FORMAT/TIME_FORMAT (date | time, fmt) where date and time are the date and time values to be formatted, and fmt is the format of date and time value formatting, the following table lists the date/time formatting codes in MySQL. Format of the key-character interval value % the name of the week abbreviated to a (Sun, Mon ...) % P AM or PM % B abbreviated month name (Jan, Feb ...) % R time, 12 hours in the format of % d in the number of days % S second () % H Hour ...) % T time, in 24-hour format % I minutes ...) % W days in a week (001,002) % j days in a year ...) % W long week name (Sunday, Monday ...) % M month, 2 digits ...) Example: SELECT DATE_FORMAT (NOW (), '% W, % d, % M, % Y % r'); Note: These two functions are case sensitive. (3) the INET_NTOA () and INET_ATON () functions in MySQL can convert the IP address to a number or perform the opposite operation. The following example shows SELECTINET_ATON ('2017. 168.1.1 '); 8. the type conversion function MySQL provides the cast () function for data type conversion. it can convert a value to a specified data type. Syntax format: CAST (expr, AS type) expr is the value to be converted by the CAST function, and type is the data type after conversion. In the CAST function, MySQL supports these data types: BINARY, CHAR, DATE, TIME, DATETIME, SIGNED, and UNSIGNED. Generally, when a numeric value is used, the string is automatically converted to a number. Therefore, the two operations in the following example get the same result: SELECT1 + '99 ', 1 + CAST ('99' as signed); strings can be designated AS binary type, so that their comparison operations become case sensitive. Using the CAST () function to specify a string as a BINARY string has the same effect as using the BINARY keyword before the string. For example, run the following statement: SELECT 'a' = BINARY 'A', 'a' = CAST ('A' as binary); the result is 0, 0; (the result of both expressions is zero, indicating that both expressions are false.) MySQL can also force the value of the date and time functions as a number rather than a string output. For example, the current date is displayed as a numerical value. SELECTCAST (CURDATE () as signed); 9. the system information function MySQL also has some special functions used to obtain information about the system. The following table lists most information functions. Function compute DATABASE () returns the current DATABASE name benchmark (n, expr), returns the character set Connection_id () of the str string when the expression expr is repeatedly run for n times charsetstr () returns the Connection ID FOUND_ROWS () of the current customer. returns the number of records returned by the last SELECT query (not restricted by the LIMIT statement) to GET_LOCK (str, dur) get a lock IS_FREE_LOCK (str) named by the string str with The dur seconds delay and check whether the lock named by str releases LAST_INSERT_ID () returns the value of the last autoincrement id automatically generated by the system, master_pos_wait (log, pos, dur) lock the master server dur seconds until the slave server synchronizes please_lock (str) to the position specified by the log of the master server and the log of the master server to release the locked USER () named by the string str () Or SYSTEM_USER () returns the current login username version () returns the MySQL server VERSION (1) DATABASE (), USER (), and version () the function returns the information of the selected database, current user, and MySQL version respectively. (2) the BENCHMARK () function is used to repeatedly execute the expression expr n times. It can be used to calculate the speed of MySQL processing expressions. The result value is usually zero. Another use comes from the MySQL client, which can report the number of queries executed and infer the server performance based on the time value. For example: select benchmark (10000000, ENCODE ('hello', 'Goodbye '); Result: 0; in this example, MySQL calculates ENCODE ('hello', 'Goodbye ') it takes 2.16 seconds for the expression to be 10 000 times. (3) the FOUND_ROWS () function is used to return the number of record rows returned by the last SELECT statement. For example, if the last SELECT statement is: SELECT * from xs; then execute the following statement: SELECT FOUND_ROWS (); the result is: 22; note: The SELECT statement may include a LIMIT clause, used to limit the number of rows that the server returns to the client. In some cases, you do not need to run the statement again to know how many lines are returned when there is no LIMIT. To know the number of rows, SELECT SQL _CALC_FOUND_ROWS in the SELECT statement, and then call FOUND_ROWS (). For example, run the following statement: SELECT SQL _CALC_FOUND_ROWS * FROM XS WHERE gender = 1 LIMIT 5; then you can use the FOUND_ROWS () function to display the number of rows returned by the SELECT statement without the LIMIT clause. Result 14;
Author tianyazaiheruanbitsCN.com