MySQL database nug cov ntaub ntawv hauv ib lub rooj li cas?Query Statement/Command/Syntax

Yuav ua li cas nug cov ntaub ntawv los ntawm lub rooj hauv MySQL database ? Cov lus nug / lus txib / syntax

MySQL Cov ntaub ntawv nug

MySQL database siv SQL SELECT nqe lus los nug cov ntaub ntawv.

Koj tuaj yeem nug cov ntaub ntawv hauv database los ntawm mysql> hais kom ua lub qhov rais, lossis nug cov ntaub ntawv los ntawm PHP tsab ntawv.

qauv sau ntawv

Cov hauv qab no yog SELECT syntax rau querying cov ntaub ntawv hauv MySQL database:

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

• Koj tuaj yeem siv ib lossis ntau lub rooj nyob rau hauv nqe lus nug. Siv commas (,) cais cov ntxhuav, thiab siv daim ntawv WHERE los teeb cov lus nug.
• Cov lus txib SELECT tuaj yeem nyeem ib lossis ntau cov ntaub ntawv.
• Koj tuaj yeem siv lub hnub qub (*) los hloov lwm daim teb.
• Koj tuaj yeem siv WHERE nqe lus kom suav nrog txhua yam mob.
• Koj tuaj yeem siv OFFSET los qhia cov ntaub ntawv offset uas SELECT nqe lus pib nug. Los ntawm lub neej ntawd qhov offset yog 0.
• Koj tuaj yeem siv LIMIT tus cwj pwm los teeb tsa tus lej ntawm cov ntaub ntawv xa rov qab.

Tau txais cov ntaub ntawv los ntawm kev hais kom ua

Hauv qhov piv txwv hauv qab no, peb yuav siv SQL SELECT cov lus txib kom tau txais cov ntaub ntawv los ntawm MySQL cov ntaub ntawv rooj chenweiliang_tbl:

piv txwv

Cov piv txwv hauv qab no yuav rov qab tag nrho cov ntaub ntawv ntawm cov ntaub ntawv rooj chenweiliang_tbl:

select * from chenweiliang_tbl;

Siv PHP tsab ntawv kom tau txais cov ntaub ntawv

Siv cov PHP function mysqli_query() thiab SQL SELECT command los nrhiav cov ntaub ntawv.

Cov haujlwm no siv los ua cov lus txib SQL, thiab tom qab ntawd cov haujlwm PHP mysqli_fetch_array() siv los nkag mus lossis tso tawm tag nrho cov ntaub ntawv nug.

Lub function mysqli_fetch_array() rov qab tau ib kab los ntawm cov txiaj ntsig teeb tsa ua ib qho associative array, ib qho numeric array, lossis ob qho tib si, thiab rov qab los ib qho array tsim los ntawm kab rov qab los ntawm cov txiaj ntsig teeb tsa. Yog tias tsis muaj kab ntxiv lawm, nws rov qab los cuav.

Cov piv txwv hauv qab no nyeem tag nrho cov ntaub ntawv los ntawm cov ntaub ntawv rooj chenweiliang_tbl.

piv txwv

Sim ua piv txwv hauv qab no los tso saib tag nrho cov ntaub ntawv ntawm cov ntaub ntawv rooj 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);
?>

Hauv qhov piv txwv saum toj no, txhua kab ntawm cov ntaub ntawv nyeem tau muab rau qhov sib txawv $ kab, thiab tom qab ntawd txhua tus nqi luam tawm.

Lus Cim: Nco ntsoov muab qhov variable no tso rau hauv cov curly braces yog tias koj xav tau siv nws hauv ib txoj hlua.

Hauv qhov piv txwv saum toj no, qhov parameter thib ob ntawm PHP mysqli_fetch_array() function yog MYSQL_ASSOC . Kev teeb tsa qhov parameter no rov qab los ua ib qho associative array, thiab koj tuaj yeem siv lub npe teb ua tus lej ntawm cov array.

PHP muab lwm txoj haujlwm, `mysqli_fetch_assoc()` , uas rov qab tau ib kab los ntawm cov txiaj ntsig teeb tsa ua ib qho associative array. Nws rov qab los ua ib qho associative array raws li cov kab rov qab los ntawm cov txiaj ntsig teeb tsa; yog tias tsis muaj kab ntxiv lawm, nws rov qab los cuav.

piv txwv

Sim cov piv txwv hauv qab no, uas siv mysqli_fetch_assoc() function los tso tawm tag nrho cov ntaub ntawv ntawm lub rooj 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);
?>

Koj tuaj yeem siv qhov tsis tu ncua MYSQL_NUM raws li qhov kev sib cav thib ob rau PHP mysqli_fetch_array() muaj nuj nqi, uas xa rov qab cov lej array.

piv txwv

Cov piv txwv hauv qab no siv cov parameter MYSQL_NUM los tso saib tag nrho cov ntaub ntawv hauv lub rooj 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);
?>

nco tso tawm

Nws yog ib qho kev xyaum zoo kom tso tus cursor nco tom qab peb tau ua tiav SELECT nqe lus.

Nco tuaj yeem tso tawm los ntawm PHP ua haujlwm mysqli_free_result().

Cov piv txwv hauv qab no qhia txog kev siv cov haujlwm no.

piv txwv

Sim ua piv txwv hauv qab no:

Siv mysqli_free_result rau lub cim xeeb dawb:

<?
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);
?>