Yuav siv MySQL qhov twg nqe lus? qhov twg clause ntau conditional syntax

Yuav siv cov lus WHERE li cas hauv MySQL ? Syntax rau ntau yam mob hauv cov lus WHERE.

MySQL kab lus WHERE

Peb paub siv SQL SELECT nqe lus los nyeem cov ntaub ntawv los ntawm MySQL cov lus.

Yog xav xaiv cov ntaub ntawv los ntawm lub rooj raws li qhov xwm txheej, koj tuaj yeem ntxiv ib kab lus WHERE rau hauv kab lus SELECT.

qauv sau ntawv

Cov lus qhia dav dav rau kev siv WHERE clause hauv SQL SELECT statement los nyeem cov ntaub ntawv los ntawm lub rooj yog cov hauv qab no:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• Koj siv tau ib lossis ntau lub rooj hauv koj cov lus nug, cais los ntawm cov cim commas., Faib cov lus nug thiab siv kab lus WHERE los teeb tsa cov xwm txheej ntawm cov lus nug.
• Koj tuaj yeem hais qhia txhua yam xwm txheej hauv WHERE clause.
• Koj tuaj yeem teev ib lossis ntau yam kev siv uas siv THIAB lossis LOSSIS.
• Cov lus txib WHERE kuj tseem siv tau nrog SQL cov lus txib DELETE lossis UPDATE.
• Cov kab lus WHERE zoo ib yam li cov xwm txheej if hauv cov lus programming, thiab nws nyeem cov ntaub ntawv teev tseg raws li cov nqi teb hauv MySQL lub rooj.

Cov hauv qab no yog cov npe ntawm cov neeg ua haujlwm uas siv tau hauv kab lus WHERE.

Cov piv txwv hauv lub rooj hauv qab no xav tias A yog 10 thiab B yog 20.

Cov kab lus WHERE muaj txiaj ntsig zoo yog tias peb xav nyeem cov ntaub ntawv tshwj xeeb los ntawm MySQL lub rooj.

Siv tus yuam sij tseem ceeb ua ib qho mob hauv kab lus WHERE yog ib qho lus nug ceev heev.

Yog tias cov xwm txheej uas tau muab tsis muaj cov ntaub ntawv sib phim hauv lub rooj, cov lus nug yuav tsis xa cov ntaub ntawv rov qab.

Nyeem cov ntaub ntawv los ntawm cov lus txib.

Peb yuav siv cov lus WHERE hauv peb cov lus qhia SQL SELECT los nyeem cov ntaub ntawv los ntawm MySQL lub rooj chenweiliang_tbl:

piv txwv

Cov piv txwv hauv qab no yuav nyeem tag nrho cov ntaub ntawv los ntawm lub rooj chenweiliang_tbl qhov twg daim teb chenweiliang_author muaj tus nqi "Sanjay":

MySQL cov WHERE clause string sib piv tsis muaj teeb meem loj thiab me. Koj siv tau BINARY keyword los ua kom WHERE clause string sib piv tsis muaj teeb meem loj thiab me.

Piv txwv li:

mysql> SELECT * from chenweiliang_tbl WHERE BINARY chenweiliang_author='chenweiliang.com';
Empty set (0.01 sec)
 
mysql> SELECT * from chenweiliang_tbl WHERE BINARY chenweiliang_author='chenweiliang.com';
+-----------+---------------+---------------+-----------------+
| chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3 | JAVA 教程 | chenweiliang.com | 2016-05-06 |
| 4 | 学习 Python | chenweiliang.com | 2016-03-06 |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

Piv txwv siv lo lus tseem ceeb BINARY , uas yog qhov tseem ceeb raws li cov ntawv loj thiab me, yog li ntawd cov lus nug chenweiliang_author='chenweiliang.com' tsis xa cov ntaub ntawv rov qab.

Siv ib daim ntawv PHP los nyeem cov ntaub ntawv

Koj siv tau PHP function mysqli_query() thiab tib SQL SELECT command nrog WHERE clause los nrhiav cov ntaub ntawv.

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

piv txwv

Cov piv txwv hauv qab no yuav xa cov ntaub ntawv los ntawm lub rooj chenweiliang_tbl uas siv tus nqi chenweiliang_author hauv daim teb chenweiliang_author ua chenweiliang.com :

Kev sim kab lus ntawm MySQL WHERE:

<?
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");
 
// 读取 chenweiliang_author 为 chenweiliang.com 的数据
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl
 WHERE chenweiliang_author="chenweiliang.com"';
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 MySQL WHERE 子句测试<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_free_result($retval);
mysqli_close($conn);
?>