How to use built-in functions in MySQL and PHP

MySQL built-in functions

MySQL built-in functions can help us process data in the table more conveniently and simplify operations.

Mathematical functions:

##ABS()Take absolute ValueSQRT()Take the rootMOD()Take the moduloFLOOR()Returns the largest integer value that is not greater than CELLING()Returns is not less than The smallest integer valueROUND()RoundingSIN()Take the sineCOS()Get cosine##String function:

Function	Description

Function##LENGTH()Get the length of the stringLOWER()Convert all strings to lowercaseUPPER()Convert all strings to CapitalizeTRIM()Remove both ends, prefix or suffixREPLACE() Replace string Date time function:

Description

FunctionDescriptionNOW()Get the current time and dateCURDATE()Get the current date CURTIME()Get the current timeYEAR()Get the yearMONTH()Get the monthDAY()Get the daydate_format()Convert time formatAggregation function:

FunctionDescriptionCOUNT()Count the number of rowsMAX()Get the maximum valueMIN()Get the minimum valueSUM ()Get the accumulated valueConditional judgment function:

FunctionDescriptionIFIFIFNULLIF NULL CASE WHENConditional judgmentExample 1Check how many files exist in the table Piece of data:

<?php

# 创建连接
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 查看是否连接成功
if ($conn) {
    echo "服务器连接成功!\n";
} else {
    echo mysqli_connect_error();
}

# SQL语句, 函数使用
$SQL = "SELECT count(*) FROM user";

# 执行SQL语句
$result = mysqli_query($conn, $SQL);

# 查看是否执行成功
if ($result) {
    echo "SQL语句执行成功!\n";
} else {
    echo mysqli_error($conn);
}

# 调试输出
while ($line =  mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 关闭连接
mysqli_close($conn);

?>

Output result:

Server connection successful!

SQL statement executed successfully!

Array

(
[count(*) ] => 5
)

##Example 2

Get the highest salary:

<?php

# 创建连接
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 查看是否连接成功
if ($conn) {
    echo "服务器连接成功!\n";
} else {
    echo mysqli_connect_error();
}

# SQL语句, 函数使用
$SQL = "SELECT max(salary) FROM user";

# 执行SQL语句
$result = mysqli_query($conn, $SQL);

# 查看是否执行成功
if ($result) {
    echo "SQL语句执行成功!\n";
} else {
    echo mysqli_error($conn);
}

# 调试输出
while ($line =  mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 关闭连接
mysqli_close($conn);

?>

Output result:

Server connection successful!

SQL statement executed successfully!

Array

(

[max(salary)] => 30000.00
)

The above is the detailed content of How to use built-in functions in MySQL and PHP. For more information, please follow other related articles on the PHP Chinese website!