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

MySQL databaseYuav ua li cas nug cov ntaub ntawv hauv lub rooj? 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 PHP functions mysqli_query() thiab SQL SELECT txib kom tau cov ntaub ntawv.

Txoj haujlwm no yog siv los ua SQL cov lus txib thiab tom qab ntawd dhau qhov ua haujlwm PHP mysqli_fetch_array() siv los yog tso tawm tag nrho cov ntaub ntawv nug.

mysqli_fetch_array() Muaj nuj nqi tau txais ib kab los ntawm cov txiaj ntsig tau teeb tsa raws li kev sib koom ua ke, lossis cov lej lej, lossis ob qho tib si Rov qab ib qho array raws li cov kab coj los ntawm cov txiaj ntsig tau teeb tsa, lossis cuav yog tias tsis muaj kab ntxiv.

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.

Nco tseg:Nco ntsoov yog tias koj yuav tsum siv qhov sib txawv ntawm txoj hlua, muab qhov sib txawv hauv curly braces.

Hauv qhov piv txwv saum toj no, qhov thib ob parameter ntawm PHP mysqli_fetch_array() muaj nuj nqi yog MYSQL_ASSOC, teeb tsa qhov ntsuas no thiab cov lus nug tshwm sim rov qab los ntawm kev sib koom array.

PHP muab lwm txoj haujlwm mysqli_fetch_assoc(), qhov kev ua haujlwm no tau txais ib kab los ntawm cov txiaj ntsig tau teeb tsa raws li kev sib koom ua ke. Rov qab ib qho associative array raws li cov kab uas tau muab los ntawm cov txiaj ntsig tau teeb tsa, lossis cuav yog tias tsis muaj kab ntxiv.

piv txwv

Sim ua piv txwv hauv qab no, uas siv mysqli_fetch_assoc() Muaj nuj nqi kom tso tawm 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_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 MYSQL_NUM Cov tsis 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_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);
?>