MySQL LIKE 子句

We know that in MySQL, we use the SQL SELECT command to read data, At the same time, we can use the WHERE clause in the SELECT statement to retrieve specific records.

WHERE clause can use the equals sign = to set the conditions for retrieving data, such as "w3codebox_author = 'oldtoolbag.com".

But sometimes we need to get w3The codebox_author field contains all records with the character "COM", at this point we need to use the SQL LIKE clause in the WHERE clause.

In the SQL LIKE clause, the percentage sign % character represents any character, similar to the asterisk in UNIX or regular expressions. *.

If the percentage sign % is not used, the LIKE clause has the same effect as the equals sign =.

Syntax

The following is the general syntax of the SQL SELECT statement using LIKE clause to read data from a table:

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

• You can specify any condition in the WHERE clause.

• You can use LIKE clause in the WHERE clause.

• You can use LIKE clause instead of the equals sign =.

• LIKE is usually used with %, similar to a wildcard search.

• You can use AND or OR to specify one or more conditions.

• You can use WHERE...LIKE clause in DELETE or UPDATE commands to specify conditions.

Use LIKE clause in the command prompt

Below we will use WHERE...LIKE clause in the SQL SELECT command to retrieve data from the MySQL table w3codebox_tbl to read data.

在线示例

Below is what we will use w3codebox_tbl table to get w3codebox_author 字段中以 COM 为结尾的的所有记录：

mysql> use w3codebox;
Database changed
mysql> SELECT　*　from w3codebox_tbl WHERE w3codebox_author LIKE '%COM';
+-----------+---------------+---------------+-----------------+
| w3codebox_id | w3codebox_title | w3codebox_author | submission_date |
+-----------+---------------+---------------+-----------------+
|　3　　　　　　　　　| 学习 Java | oldtoolbag.com |　2015-05-01　　　　　　|
|　4　　　　　　　　　| 学习 Python | oldtoolbag.com |　2016-03-06　　　　　　|
+-----------+---------------+---------------+-----------------+
2　rows in set (0.01　sec)

在PHP脚本中使用 LIKE 子句

你可以使用PHP函数的 mysqli_query() 及相同的 SQL SELECT 带上 WHERE...LIKE 子句的命令来获取数据。

该函数用于执行 SQL 命令，然后通过 PHP 函数 mysqli_fetch_array() 来输出所有查询的数据。

但是如果是 DELETE 或者 UPDATE 中使用 WHERE...LIKE 子句的 SQL 语句，则无需使用 mysqli_fetch_array() 函数。

在线示例

以下是我们使用PHP脚本在 w3codebox_tbl 表中读取 w3codebox_author 字段中以 COM 为结尾的的所有记录：

<?php
$dbhost = 'localhost';　　//　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 w3codebox_id, w3codebox_title,　
　　　　　　　　w3codebox_author, submission_date
　　　　　　　　FROM w3codebox_tbl
　　　　　　　　WHERE w3codebox_author LIKE "%COM"';
　
mysqli_select_db($conn, 'w3codebox');
$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, MYSQLI_ASSOC))
{
　　　　echo "<tr><td> "{$row['w3codebox_id'}</td> ".
　　　　　　　　　"<td>{$row['w3codebox_title']} </td> ".
　　　　　　　　　"<td>{$row['w3codebox_author']} </td> ".
　　　　　　　　　"<td>{$row['submission_date']} </td> ".
　　　　　　　　　"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

输出结果如图所示：