Mysqli 简明教程

MySQLi - Like Clause

我们已经见过 SQL SELECT 命令以从 MySQL 表中获取数据。我们还可以使用一个称为 WHERE 子句的条件子句来选择所需的记录。

We have seen the SQL SELECT command to fetch data from the MySQL table. We can also use a conditional clause called as the WHERE clause to select the required records.

带有“等于”符号 (=) 的 WHERE 子句可以正常工作，在这种情况下我们希望进行精确匹配。例如“tutorial_author = 'Sanjay'”。但可能会有一些要求，即我们希望过滤出教程作者名称应包含“jay”的所有结果。这可以使用 SQL LIKE Clause 和 WHERE 子句一起处理。

A WHERE clause with the ‘equal to’ sign (=) works fine where we want to do an exact match. Like if "tutorial_author = 'Sanjay'". But there may be a requirement where we want to filter out all the results where tutorial_author name should contain "jay". This can be handled using SQL LIKE Clause along with the WHERE clause.

如果 SQL LIKE 子句与 % 字符一起使用，则它将像一个元字符（ ) as in UNIX, while listing out all the files or directories at the command prompt. Without a % character, the LIKE clause is very same as the *equal to 符号和 WHERE 子句一起）。

If the SQL LIKE clause is used along with the % character, then it will work like a meta character () as in UNIX, while listing out all the files or directories at the command prompt. Without a % character, the LIKE clause is very same as the *equal to sign along with the WHERE clause.

Syntax

以下代码块具有 SELECT 命令的通用 SQL 语法以及 LIKE 子句，以从 MySQL 表中获取数据。

The following code block has a generic SQL syntax of the SELECT command along with the LIKE clause to fetch data from a MySQL table.

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

  1. You can specify any condition using the WHERE clause.

  2. You can use the LIKE clause along with the WHERE clause.

  3. You can use the LIKE clause in place of the equals to sign.

  4. When LIKE is used along with % sign then it will work like a meta character search.

  5. You can specify more than one condition using AND or OR operators.

  6. A WHERE…​LIKE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.

Using the LIKE clause at the Command Prompt

这将使用具有 WHERE…​LIKE 子句的 SQL SELECT 命令从 MySQL 表中获取选定的数据 – tutorials_tbl 。

This will use the SQL SELECT command with the WHERE…​LIKE clause to fetch the selected data from the MySQL table – tutorials_tbl.

Example

以下示例将返回 tutorials_tbl 表中作者名称以 jay 结尾的所有记录 −

The following example will return all the records from the tutorials_tbl table for which the author name ends with jay −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl
   → WHERE tutorial_author LIKE '%jay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      |  JAVA Tutorial |     Sanjay      |    2007-05-21   |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Using LIKE clause inside PHP Script

PHP 使用 mysqli query() 或 mysql_query() 函数使用 Like 子句来选择 MySQL 表中的记录。此函数接受两个参数并在成功时返回 TRUE，在失败时返回 FALSE。

PHP uses mysqli query() or mysql_query() function to select records in a MySQL table using Like clause. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)

Sr.No.

Parameter & Description

1

$sql Required - SQL query to select records in a MySQL table using Like Clause.

2

$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

尝试以下示例，以在表中使用 like 子句选择记录 -

Try the following example to select a record using like clause in a table −

将以下示例复制粘贴为 mysql_example.php：

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Using Like Clause</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');

         $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author like "Mah%"';

         $result = $mysqli→query($sql);

         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
                  $row["tutorial_id"],
                  $row["tutorial_title"],
                  $row["tutorial_author"],
                  $row["submission_date"]);
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

Output

访问在 apache web 服务器上部署的 mysql_example.php，并验证输出。在运行选择脚本之前，我们在此表中输入了多条记录。

Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021