How to query MySQL like? The usage of like statement in MySQL database

How to perform a LIKE query in MySQL ? What are the usage rules of the LIKE statement in MySQL databases ?

MySQL LIKE clause

We know to use the SQL SELECT command to read data in MySQL, and we can use the WHERE clause in the SELECT statement to get the specified records.

The WHERE clause can use the equals sign (=) to set the conditions for retrieving data, such as "chenweiliang_author = 'chenweiliang.com'".

But sometimes we need to get all the records whose chenweiliang_author field contains "COM" characters, then we need to use the SQL LIKE clause in the WHERE clause.

The SQL LIKE clause uses the percent sign % character to represent any character, similar to the asterisk * in UNIX or regular expressions.

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

grammar

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

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

• You can specify any condition in the WHERE clause.
• You can use the LIKE clause in the WHERE clause.
• You can use the LIKE clause in place of the equals sign =.
• LIKE is usually associated with % Used together, similar to a metacharacter search.
• You can specify one or more conditions using AND or OR.
• You can use the WHERE...LIKE clause in DELETE or UPDATE commands to specify conditions.

Using the LIKE clause in the command prompt

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

Examples

The following is a list of all records from the chenweiliang_tbl table that end with "COM" in the chenweiliang_author field :

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)

Using LIKE clause in PHP script

You can use the PHP function mysqli_query() and the same SQL SELECT command with a WHERE...LIKE clause to get data.

This function is used to execute SQL commands and then output data for all queries through the PHP function mysqli_fetch_assoc().

But if it is a SQL statement using the WHERE...LIKE clause in DELETE or UPDATE, there is no need to use the mysqli_fetch_array() function.

Examples

Here's how we use a PHP script to read all records ending in COM in the chenweiliang_author field in the chenweiliang_tbl table:

<?
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);
?>