Shulou(Shulou.com)06/02 Report--

This article mainly introduces what functions are in the database, the introduction in the article is very detailed, has a certain reference value, interested friends must read!

Database functions are: 1, mathematical function [abs (x), bin (x)]; 2, aggregate function [avg (col)]; 3, string function [ascii (char)]; 4, date and time function [curdate ()]; 5, encryption function [decode (str,key)].

The database functions are:

1. Mathematical function

Abs (x) returns the absolute value of x

Bin (x) returns the binary of x (oct returns octal, hex returns hexadecimal)

Ceiling (x) returns the minimum integer value greater than x

Exp (x) returns the x power of the value e (the base of the natural logarithm)

Floor (x) returns a maximum integer value less than x

Greatest (x1 and 2) returns the largest value in the collection

Least (x1 and 2) returns the smallest value in the set

Ln (x) returns the natural logarithm of x

Log (xPowery) returns the y-based logarithm of x

Mod (xPowery) returns the module (remainder) of xCompy.

Pi () returns the value of pi (pi)

Rand () returns a random value from 0 to 1, and you can cause the rand () random number generator to generate a specified value by providing a parameter (seed).

Round (xQuery y) returns the value of the parameter x rounded with y decimal places

Sign (x) returns the value of the symbol that represents the number x

Sqrt (x) returns the square root of a number

Truncate (xQuery y) returns the result of the number x truncated to y decimal places

Aggregate function (commonly used in select queries of group by clauses)

Avg (col) returns the average of the specified column

Count (col) 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 value of the specified column

Sum (col) returns the sum of all the values of the specified column

Group_concat (col) returns the result composed of concatenations of column values that belong to a group

Third, string function

Ascii (char) returns the ascii code value of the character

Bit_length (str) returns the bit length of the string

Concat (s1MageS2... reparationsn) concatenates s1Magins2.. Personsn into a string.

Concat_ws (sep,s1,s2...,sn) concatenates s1direction s2.. concat_ws sn into strings and spaced them with sep characters.

Insert (str,x,y,instr) starts the string str at position x, replaces the substring of y characters with the string instr, and returns the result.

Find_in_set (str,list) parses a comma-separated list of list and, if str is found, returns the location of str in list

Lcase (str) or lower (str) returns the result of changing all characters in the string str to lowercase

Left (str,x) returns the leftmost x characters in the string str

Length (s) returns the number of characters in the string str

Ltrim (str) cuts off the opening space from the string str

Position (substr in str) returns the position where the substring substr first appears in the string str

Quote (str) escapes single quotation marks in str with a backslash

Repeat (str,srchstr,rplcstr) returns the result of the string str repeated x times

Reverse (str) returns the result of reversing the string str

Right (str,x) returns the rightmost x characters in the string str

Rtrim (str) returns the space at the end of the string str

Strcmp (S1 and S2) compare strings S1 and S2

Trim (str) removes all spaces at the beginning and end of a string

Ucase (str) or upper (str) returns the result of converting all characters in the string str to uppercase

IV. Date and time functions

Curdate () or current_date () returns the current date

Curtime () or current_time () returns the current time

Date_add (date,interval int keyword) returns the result of date date plus interval int (int must be formatted according to keywords), such as: selectdate_add (current_date,interval 6 month)

Date_format (date,fmt) formats the date value according to the specified fmt format

Date_sub (date,interval int keyword) returns the result of date date plus interval int (int must be formatted according to keywords), such as: selectdate_sub (current_date,interval 6 month)

Dayofweek (date) returns the day of the week represented by date (1-7)

Dayofmonth (date) returns the day of the month in which date is returned (1-31)

Dayofyear (date) returns the day of the year in which date is returned (1x366)

Dayname (date) returns the week name of date, such as: select dayname (current_date)

From_unixtime (ts,fmt) formats the unix timestamp ts according to the specified fmt format

Hour (time) returns the hourly value of time (0,23)

Minute (time) returns the minute value of time (0,59)

Month (date) returns the month value of date (1-12)

Monthname (date) returns the month name of the date, such as: select monthname (current_date)

Now () returns the current date and time

Quarter (date) returns the quarter of the year (1x4) of date, such as select quarter (current_date)

Week (date) returns the date date is the week ordinal of the year (0,53)

Year (date) returns the date date's year (100009999)

5. Encryption function

Aes_encrypt (str,key) returns the result of encrypting the string str using the advanced encryption standard algorithm with the key key. The result of calling aes_encrypt is a binary string, which is stored in blob type.

Aes_decrypt (str,key) returns the result of decrypting the string str using the advanced encryption standard algorithm with the key key

Decode (str,key) uses key as the key to decrypt the encrypted string str

Encrypt (str,salt) uses the unixcrypt () function to encrypt the string str with the keyword salt (a string that uniquely determines the password, just like a key)

Encode (str,key) uses key as the key encryption string str, and the result of calling encode () is a binary string, which is stored as a blob type.

Md5 () calculates the md5 checksum of the string str

Password (str) returns the encrypted version of the string str, which is irreversible and uses a different algorithm than the unix password encryption process.

Sha () calculates the secure hash algorithm (sha) checksum of string str

Control flow function

Mysql has four functions for conditional operations, which can implement the conditional logic of sql and allow developers to convert some application business logic to the database background.

Mysql control flow function:

Case when [test1] then [result1]... else [default] end returns resultn if testn is true, default otherwise

Case [test] when [val1] then [result]... else [default] end returns resultn if test and valn are equal, otherwise returns default

If (test,t,f) returns t if test is true; otherwise returns f

Ifnull (arg1,arg2) returns arg1 if arg1 is not empty, arg2 otherwise

Nullif (arg1,arg2) returns arg1 if arg1=arg2 returns null; otherwise

The first of these functions is ifnull (), which takes two parameters and determines the first parameter. If the first argument is not null, the function returns the first argument to the caller; if it is null, the second argument is returned.

Formatting function

Date_format (date,fmt) formats the date value according to the string fmt

Format format x as a sequence of numbers separated by commas, where y is the number of decimal places of the result

Inet_aton (ip) returns a numerical representation of the ip address

Inet_ntoa (num) returns the ip address represented by the number

Time_format (time,fmt) formats the time value according to the string fmt

Type conversion function

For data type conversion, mysql provides the cast () function, which converts a value to a specified data type. The types are: binary,char,date,time,datetime,signed,unsigned

IX. System information function

Database () returns the current database name

Benchmark (count,expr) repeats the expression expr count times

Connection_id () returns the connection id of the current customer

Found_rows () returns the total number of rows retrieved by the last select query

User () or system_user () returns the current login user name

Version () returns the version of the mysql server

These are all the contents of the article "what functions are there in the database"? thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.