MySQL LIKE 子句
MySQL LIKE clause
We know that we 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 record.
The equal sign = can be used in the WHERE clause to set the conditions for obtaining data, such as "user_author = 'PHP Chinese website'".
But sometimes we need to get all the records whose user_author field contains "php" characters, then we need to use the SQL LIKE clause in the WHERE clause.
The percent sign % character is used in the SQL LIKE clause 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 =.
Syntax
The following is the general syntax for the SQL SELECT statement to read data from the 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 conditions in the WHERE clause.
You can use the LIKE clause in the WHERE clause.
You can use the LIKE clause instead of the equal sign =.
LIKE is usually used with %, similar to a metacharacter search.
You can use AND or OR to specify one or more conditions.
You can use the WHERE...LIKE clause in a DELETE or UPDATE command to specify conditions.
Using the LIKE clause in the command prompt
Below we will use WHERE in the SQL SELECT command. ..LIKE clause to read data from the MySQL data table user.
Example
The following is how we obtain all the records ending with php in the user_author field from the user table:
mysql> use demo;
Database changed
mysql> SELECT * from user_tbl WHERE user_author LIKE '%php';
+----------+--- ------------+---------------+------------------+
| user_id | user_title | user_author | submission_date |
+-----------+---------------+--- ------------+-----------------+
| 3 | Learn Java | PHP Chinese Network | 2015-05 -01 |
| 4 | Learn Python | PHP Chinese website | 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 Same SQL SELECT command with WHERE...LIKE clause to get data.
This function is used to execute SQL commands, and then output all queried data through the PHP function mysqli_fetch_array().
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.
Example
The following is how we use the PHP script to read all the records ending with php in the user_author field in the user table:
<?php
header("Content -Type: text/html;charset=utf-8");
$dbhost = 'localhost'; // mysql server host address
$dbuser = 'root'; ##$dbpass = 'root'; // mysql username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn)
{
die ('Connection failed: ' . mysqli_error($conn));
}
//Set encoding to prevent Chinese garbled characters
mysqli_query($conn, "set names utf8");
$sql = 'SELECT user_id, user_title,
user_author, submission_date
FROM user
WHERE user_author LIKE "%php"';
mysqli_select_db( $conn, 'demo' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2>PHP Chinese website mysqli_fetch_array test<h2>';
echo '<table border="1"><tr><td>Tutorial ID</td>< ;td>Title</td><td>Author</td><td>Submission Date</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
echo "<tr><td> {$row['user_id']}</td> ".
" "<td>{$row['user_title ']} </td> ".
" "<td>{$row['user_author']} </td> ".
" "<td>{$row['submission_date'] } </td> ".
" "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>
Related video tutorial recommendations: LIKE and MIN
