Bawo ni MySQL ṣe n beere data ibeere data ni tabili kan?Gbólóhùn Ìbéèrè/Àṣẹ/Sintasi

MySQL databaseBawo ni lati beere awọn data ninu tabili?Gbólóhùn Ìbéèrè/Àṣẹ/Sintasi

MySQL Data ibeere

Awọn apoti isura infomesonu MySQL lo awọn alaye YAN SQL lati beere data.

O le beere data ninu ibi ipamọ data nipasẹ mysql> window aṣẹ aṣẹ, tabi nipasẹ iwe afọwọkọ PHP kan.

girama

Atẹle yii ni gbogbogbo Yan sintasi fun data ibeere ni aaye data MySQL kan:

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

• Ninu alaye ibeere, o le lo ọkan tabi diẹ ẹ sii tabili, ya awọn tabili pẹlu aami idẹsẹ (,), ati lo alaye NIBI lati ṣeto awọn ipo ibeere.
• Aṣẹ Yan le ka ọkan tabi diẹ sii awọn igbasilẹ.
• O le lo aami akiyesi (*) lati rọpo awọn aaye miiran, alaye YAN yoo da gbogbo data aaye ti tabili pada
• O le lo alaye NIBI lati fi ipo eyikeyi kun.
• O le pato aiṣedeede data ninu eyiti alaye yiyan bẹrẹ ibeere naa pẹlu OFFSET.Nipa aiyipada aiṣedeede jẹ 0.
• O le lo ohun-ini LIMIT lati ṣeto nọmba awọn igbasilẹ ti o pada.

Gba data nipasẹ aṣẹ aṣẹ

Ninu apẹẹrẹ atẹle, a yoo lo aṣẹ SQL SELECT lati gba data ti tabili data MySQL chenweiliang_tbl:

Apeere

Apẹẹrẹ atẹle yoo da gbogbo awọn igbasilẹ ti tabili data pada chenweiliang_tbl:

select * from chenweiliang_tbl;

Lo iwe afọwọkọ PHP lati gba data

Lilo PHP awọn iṣẹ mysqli_query() ati SQL yiyan pipaṣẹ lati gba data naa.

Iṣẹ yii ni a lo lati ṣiṣẹ awọn aṣẹ SQL ati lẹhinna kọja awọn iṣẹ PHP mysqli_fetch_array() lati lo tabi gbejade data fun gbogbo awọn ibeere.

mysqli_fetch_array() Iṣẹ naa mu ila kan lati inu abajade ti a ṣeto bi akojọpọ alajọṣepọ, tabi akojọpọ awọn nọmba, tabi awọn mejeeji. Ṣe idapada akojọpọ ti ipilẹṣẹ lati awọn ori ila ti o mu lati eto abajade, tabi eke ti ko ba si awọn ori ila diẹ sii.

Apẹẹrẹ atẹle yii ka gbogbo awọn igbasilẹ lati tabili data chenweiliang_tbl.

Apeere

Gbiyanju apẹẹrẹ atẹle lati ṣafihan gbogbo awọn igbasilẹ ti tabili data 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);
?>

Ni awọn loke apẹẹrẹ, kọọkan kana ti igbasilẹ ka ti wa ni sọtọ si awọn ayípadà $ kana, ati ki o si kọọkan iye ti wa ni tejede.

Akiyesi:Ranti pe ti o ba nilo lati lo oniyipada ninu okun kan, fi oniyipada sinu awọn àmúró iṣupọ.

Ninu apẹẹrẹ ti o wa loke, paramita keji ti iṣẹ PHP mysqli_fetch_array () jẹ MYSQL_ASSOC, Ṣeto paramita yii lati beere abajade lati da akojọpọ alafaramo pada, o le lo orukọ aaye bi atọka ti orun naa.

PHP pese iṣẹ miiran mysqli_fetch_assoc(), iṣẹ naa gba ọna kan lati inu abajade ti a ṣeto bi akojọpọ associative.Pada akojọpọ alajọṣepọ ti ipilẹṣẹ lati awọn ori ila ti o ya lati inu eto abajade, tabi eke ti ko ba si awọn ori ila mọ.

Apeere

Gbiyanju apẹẹrẹ atẹle, eyiti o nlo mysqli_fetch_assoc() iṣẹ lati gbejade gbogbo awọn igbasilẹ ti tabili data 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);
?>

O tun le lo MYSQL_NUM igbagbogbo bi paramita keji ti iṣẹ PHP mysqli_fetch_array(), eyiti o da ọpọlọpọ awọn nọmba pada.

Apeere

Awọn apẹẹrẹ wọnyi lo MYSQL_NUM Paramita naa ṣafihan gbogbo awọn igbasilẹ ti tabili data 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);
?>

iranti Tu

O jẹ adaṣe ti o dara lati gba iranti kọsọ laaye lẹhin ti a ti ṣe alaye yiyan.

Iranti le ṣe idasilẹ nipasẹ iṣẹ PHP mysqli_free_result ().

Apẹẹrẹ atẹle ṣe afihan lilo iṣẹ yii.

Apeere

Gbiyanju awọn apẹẹrẹ wọnyi:

Iranti ọfẹ pẹlu 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);
?>