Uyibuza kanjani i-MySQL njengokuthanda? Ukusetshenziswa kwesitatimende esifana nesizindalwazi se-MySQL

Ungayenza kanjani i-LIKE query ku-MySQL ? Yimiphi imithetho yokusebenzisa isitatimende se-LIKE kuma -database e-MySQL ?

MySQL LIKE isigatshana

Siyazi ukusebenzisa umyalo we-SQL KHETHA ukuze sifunde idatha ku-MySQL, futhi singasebenzisa isigatshana esithi WHERE esitatimendeni esithi KHETHA ukuze sithole amarekhodi ashiwo.

Isigaba se-WHERE singasebenzisa uphawu lokulingana (=) ukusetha izimo zokuthola idatha, njenge-"chenweiliang_author = 'chenweiliang.com'".

Kodwa ngezinye izikhathi sidinga ukuthola wonke amarekhodi inkambu yawo ethi chenweiliang_author iqukethe izinhlamvu ze-"COM", bese sidinga ukusebenzisa isigatshana esithi SQL LIKE esigatshaneni esithi LAPHO.

Isigaba se-SQL LIKE sisebenzisa uhlamvu lwe- % yesibonakaliso sephesenti ukumela noma yiluphi uhlamvu, olufana ne-asterisk * ku-UNIX noma izinkulumo ezijwayelekile.

Uma uphawu lwephesenti % lungasetshenziswanga , isigaba esithi LIKE sinomphumela ofanayo nophawu olulinganayo = .

uhlelo lolimi

Okulandelayo i-syntax evamile yesitatimende se-SQL KHETHA ukufunda idatha kusuka kuthebula ledatha usebenzisa isigatshana esithi LIKE:

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• Ungacacisa noma yimuphi umbandela esigatshaneni esithi LAPHO.
• Ungasebenzisa isigatshana esithi LIKE esigatshaneni esithi LAPHO.
• Ungasebenzisa isigatshana esithi LIKE esikhundleni sophawu lokulingana =,
• U-LIKE kuvame ukuhlotshaniswa ne % Kusetshenziswe ndawonye, ​​​​kufana nokusesha kwe-metacharacter.
• Ungacacisa umbandela owodwa noma ngaphezulu usebenzisa KANYE noma NOMA.
• Ungasebenzisa elithi LAPHO...NJENGAPHO isigatshana esithi SUSA noma BUYEKEZA imiyalo ukuze ucacise izimo.

Ukusebenzisa isigatshana esithi LIKE kumyalo womyalo

Ngezansi sizosebenzisa okuthi LAPHO...NJENGEKUPHI kwesigatshana kumyalo we-SQL KHETHA ukuze ufunde idatha kuthebula ledatha le-MySQL chenweiliang_tbl.

Isibonelo

Okulandelayo uhlu lwawo wonke amarekhodi avela kuthebula le-chenweiliang_tbl eliphela ngo- "COM" ensimini ye-chenweiliang_author :

mysql> use chenweiliang;
Database changed
mysql> SELECT * from chenweiliang_tbl WHERE chenweiliang_author LIKE '%COM';
+-----------+---------------+---------------+-----------------+
| chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3 | 学习 Java | chenweiliang.com | 2015-05-01 |
| 4 | 学习 Python | chenweiliang.com | 2016-03-06 |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

Ukusebenzisa isigatshana esithi LIKE kusikripthi se-PHP

Ungasebenzisa umsebenzi we-PHP mysqli_query() kanye nomyalo ofanayo we-SQL KHETHA onesigatshana esithi LAPHO...NJENGAMAGAMA ukuze uthole idatha.

Lo msebenzi usetshenziselwa ukusebenzisa imiyalo ye-SQL bese ukhipha idatha yayo yonke imibuzo ngomsebenzi we-PHP mysqli_fetch_assoc().

Kodwa uma kuyisitatimende se-SQL kusetshenziswa okuthi LAPHO...NJENGOKUTHI SUSA noma UKUBUYISA, asikho isidingo sokusebenzisa umsebenzi we-mysqli_fetch_array().

Isibonelo

Nansi indlela esisebenzisa ngayo umbhalo we-PHP ukuze sifunde wonke amarekhodi agcina ngo-COM kunkambu ye-chenweiliang_author kuthebula le-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
 WHERE chenweiliang_author LIKE "%COM"';
 
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);
?>