How does MySQL database query data in a table?Query Statement/Command/Syntax

MySQL databaseHow to query the data in the table?Query Statement/Command/Syntax

MySQL Query data

MySQL databases use SQL SELECT statements to query data.

You can query data in the database through the mysql> command prompt window, or through a PHP script.

grammar

The following is the general SELECT syntax for querying data in a MySQL database:

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

• In the query statement, you can use one or more tables, separate the tables with commas (,), and use the WHERE statement to set query conditions.
• The SELECT command can read one or more records.
• You can use an asterisk (*) to replace other fields, the SELECT statement will return all field data of the table
• You can use the WHERE statement to include any condition.
• You can specify the data offset at which the SELECT statement starts the query with OFFSET.By default the offset is 0.
• You can use the LIMIT property to set the number of records returned.

Get data via command prompt

In the following example, we will use the SQL SELECT command to obtain the data of the MySQL data table chenweiliang_tbl:

Examples

The following example will return all records of the data table chenweiliang_tbl:

select * from chenweiliang_tbl;

Use PHP script to get data

using PHP functions mysqli_query() And SQL SELECT command to get the data.

This function is used to execute SQL commands and then pass PHP functions mysqli_fetch_array () to use or output data for all queries.

mysqli_fetch_array () The function fetches a row from the result set as an associative array, or an array of numbers, or both. Returns an array generated from the rows fetched from the result set, or false if there are no more rows.

The following example reads all records from the data table chenweiliang_tbl.

Examples

Try the following example to display all records of the data table chenweiliang_tbl.

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl';
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 mysqli_fetch_array 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
 echo "<tr><td> {$row['chenweiliang_id']}</td> ".
 "<td>{$row['chenweiliang_title']} </td> ".
 "<td>{$row['chenweiliang_author']} </td> ".
 "<td>{$row['submission_date']} </td> ".
 "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

In the above example, each row of records read is assigned to the variable $row, and then each value is printed out.

note:Remember that if you need to use a variable in a string, put the variable in curly braces.

In the above example, the second parameter of the PHP mysqli_fetch_array() function is MYSQL_ASSOC, set this parameter to query the result to return an associative array, you can use the field name as the index of the array.

PHP provides another function mysqli_fetch_assoc (), the function takes a row from the result set as an associative array.Returns an associative array generated from the rows taken from the result set, or false if there are no more rows.

Examples

Try the following example, which uses mysqli_fetch_assoc () function to output all records of the data table chenweiliang_tbl:

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl';
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 mysqli_fetch_assoc 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_assoc($retval))
{
 echo "<tr><td> {$row['chenweiliang_id']}</td> ".
 "<td>{$row['chenweiliang_title']} </td> ".
 "<td>{$row['chenweiliang_author']} </td> ".
 "<td>{$row['submission_date']} </td> ".
 "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

You can also use the constant MYSQL_NUM as the second parameter of the PHP mysqli_fetch_array() function, which returns an array of numbers.

Examples

The following examples use MYSQL_NUM The parameter displays all records of the data table chenweiliang_tbl:

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl';
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 mysqli_fetch_array 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_NUM))
{
 echo "<tr><td> {$row[0]}</td> ".
 "<td>{$row[1]} </td> ".
 "<td>{$row[2]} </td> ".
 "<td>{$row[3]} </td> ".
 "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

memory release

It's a good practice to free cursor memory after we've executed a SELECT statement.

The memory can be released through the PHP function mysqli_free_result().

The following example demonstrates the use of this function.

Examples

Try the following examples:

Free memory with mysqli_free_result:

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl';
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 mysqli_fetch_array 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_NUM))
{
 echo "<tr><td> {$row[0]}</td> ".
 "<td>{$row[1]} </td> ".
 "<td>{$row[2]} </td> ".
 "<td>{$row[3]} </td> ".
 "</tr>";
}
echo '</table>';
// 释放内存
mysqli_free_result($retval);
mysqli_close($conn);
?>