What are the database functions?

Database functions include: 1. Mathematical function [abs(x), bin(x)]; 2. Aggregation function [avg(col)]; 3. String function [ascii(char)]; 4 , Date and time function [curdate()]; 5. Encryption function [decode(str,key)].

Database functions include:

1. Mathematical functions

• 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 smallest integer value greater than x

• exp(x) Returns the x power of the value e (the base of the natural logarithm)

• floor(x) returns the largest integer value less than x

• greatest(x1,x2,...,xn) returns the set The largest value

• least(x1,x2,...,xn) returns the smallest value in the set

• ln(x) returns The natural logarithm of x

• log(x,y) returns the base y logarithm of x

• mod(x,y ) Returns the modulus (remainder) of x/y

• pi() returns the value of pi (pi)

• rand() returns 0 to For a random value within 1, you can make the rand() random number generator generate a specified value by providing a parameter (seed).

• round(x,y) returns the rounded value of parameter x with y decimal places

• sign(x) returns the representative number x The value of the sign

• sqrt(x) returns the square root of a number

• truncate(x,y) returns the number x truncated to y Result with decimal places

2. Aggregation function(commonly used in select query of group by clause)

• ##avg (col)Returns the average value 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 all values ​​of the specified column The sum of values

• group_concat(col) returns the result of the concatenation of column values ​​belonging to a group

3. String function

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

• bit_length(str) returns the bit length of the string

• concat(s1,s2...,sn) concatenates s1,s2...,sn into a string

• concat_ws( sep,s1,s2...,sn) concatenate s1,s2...,sn into a string and separate them with sep characters

• insert(str,x,y,instr ) Replace the y-character-long substring of string str starting at the , if str is found, return the position of str in the 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 the leading spaces from the string str
position(substr in str) Returns the substring substr in character The first occurrence of string str
quote(str) Use backslash to escape the single quote in str
repeat( str, srchstr, rplcstr) returns the result of string str repeated x times
reverse(str) returns the result of inverting string str
right(str,x) Returns the rightmost x characters in the string str
rtrim(str) Returns the spaces at the end of the string str
strcmp(s1,s2) Compare strings s1 and s2
trim(str) Remove all spaces at the beginning and end of the string
ucase(str) or upper(str) returns the result of converting all characters in the string str to uppercase
• 4. 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 plus interval time int (int must be formatted according to the keyword), such as: selectdate_add(current_date,interval 6 month);

• date_format(date,fmt) Format the date value according to the specified fmt format

• date_sub(date,interval int keyword) Returns the date date plus interval The result of time int (int must be formatted according to keywords), such as: selectdate_sub(current_date,interval 6 month);

• ##dayofweek(date) Returns the week represented by date The day of the month (1~7)

• dayofmonth(date) returns the day of the month (1~31)

• dayofyear(date) Returns the day of the year that date is (1~366)

• dayname(date) Returns the day of the week name of date, such as: select dayname(current_date);

• from_unixtime(ts,fmt) Format the unix timestamp ts according to the specified fmt format

• hour(time) Return the hour value of time (0~23)

• minute(time) Returns the minute value of time (0~59)

• ##month(date) Returns the minute value of date Month value (1~12)

• monthname(date) Returns the month name of date, such as: select monthname(current_date);

• now () Returns the current date and time

• quarter(date) Returns the quarter (1~4) of date in the year, such as select quarter(current_date);

• week(date) Return date as the week of the year (0~53)

• year(date) Return date as the year (1000~9999)

5. Encryption function

• ##aes_encrypt(str,key) returns the result of encrypting the string str using the Advanced Encryption Standard algorithm using the key key. The result of calling aes_encrypt is a binary string, stored in blob type

• aes_decrypt(str,key) returns the result of decrypting the string str using the Advanced Encryption Standard algorithm using the key key

• decode(str,key) Use key as the key to decrypt the encrypted string str

• encrypt(str,salt) Use unixcrypt() Function, use the keyword salt (a string that can uniquely determine the password, just like a key) to encrypt the string str

• encode(str,key) Use key as the key to encrypt characters String str, the result of calling encode() is a binary string, which is stored in blob type

• md5() Calculates the md5 checksum of the string str

• password(str) Returns the encrypted version of the string str. This encryption process is irreversible and uses a different algorithm from the Unix password encryption process.

• sha() Calculate the secure hash algorithm (sha) checksum of the string str

6. Control flow function

Mysql has 4 functions for conditional operations. These functions can implement sql conditional logic and allow developers to convert some application business logic to the database background.

mysql control flow function:

• case when[test1] then [result1]...else [default] endIf testn is true, return resultn, otherwise Return default

##case [test] when[val1] then [result]...else [default]end If test and valn are equal, return resultn, otherwise return default
if(test,t,f) If test is true, return t; otherwise return f
ifnull(arg1,arg2) If arg1 is not empty, Returns arg1, otherwise returns arg2

• ##nullif(arg1,arg2) If arg1=arg2 returns null; otherwise returns arg1

• The first of these functions One is ifnull(), which has two parameters and judges the first parameter. If the first parameter is not null, the function will return the first parameter to the caller; if it is null, the second parameter will be returned.

7. Format function

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

• format(x,y) Format x into a comma-separated sequence of numbers, y is the number of decimal places in the result

• inet_aton(ip) Return ip The numerical representation of the address

• inet_ntoa(num) returns the ip address represented by the number

• time_format(time,fmt) according to the string fmt format time value
8. Type conversion function

In order to convert the data type, mysql provides the cast() function, which can convert Converts a value to the specified data type. Types include: binary, char, date, time, datetime, signed, unsigned

9. System information function

database() returns the current Database name

• benchmark(count,expr) Repeat the expression expr count times

• connection_id() Return 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
##Related free learning recommendations:

mysql video tutorial

The above is the detailed content of What are the database functions?. For more information, please follow other related articles on the PHP Chinese website!