mysql like ilike_MySQLilike 子句

我们已经看到SQLSELECT命令从MySQLi表中获取数据。我们也可以使用一个名为WHERE子句的条件语句来选择所需的记录。

等号(=)的WHERE子句可以正常工作，我们要做完全匹配。喜欢“name ="sai"”。但是可能有一个要求，我们要过滤掉所有结果，其中name应该包含“johar”。这可以使用SQLLIKE子句以及WHERE子句来处理。

If SQL LIKE clause is used along with % characters, then it will work like a meta character (*) in UNIX while listing out all the files or directories at command prompt.

Without a % character, LIKE clause is very similar to equals sign along with WHERE clause.

Syntax

Here is generic SQL syntax of SELECT command along with LIKE clause to fetch data from MySQLi table −

SELECT field1, field2,...fieldN table_name1, table_name2...

WHERE field1 LIKE condition1 [AND [OR]] filed2 = "somevalue"

You can specify any condition using WHERE clause.

You can use LIKE clause along with WHERE clause.

You can use LIKE clause in place of equals sign.

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

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

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

Using LIKE clause at Command Prompt

这将使用SQL SELECT命令与WHERE ... LIKE子句从MySQLi表tutorials_inf中获取所选数据。

例

以下示例将返回tutorials_inf表中的所有记录，作者姓名以johar结尾，

root@host# mysql -u root -p password;

Enter password:*******

mysql> use TUTORIALS;

Database changed

mysql> SELECT * from tutorials_inf

-> WHERE name LIKE "%johar";

+----+-------+

| id | name |

+----+-------+

| 2 | johar |

+----+-------+

1 row in set (0.00 sec)

mysql>

在PHP脚本中使用LIKE子句

您可以将类似于WHERE ... LIKE子句的语法用于PHP函数mysqli_query()。此函数用于执行SQL命令，而后续的另一个PHP函数如果使用WHERE ... LIKE子句以及SELECT命令，则可以使用mysqli_fetch_array()来获取所有选定的数据。

但是如果WHERE ... LIKE子句与DELETE或UPDATE命令一起使用，则不需要进一步调用PHP函数。

例

试试下面的例子返回所有记录tutorials_inf对于其名称中包含表乔哈尔-

$dbhost="localhost:3306";$dbuser="root";$dbpass="";$dbname="TUTORIALS";$conn=mysqli_connect($dbhost,$dbuser,$dbpass,$dbname);if(!$conn){die("Could not connect: ".mysqli_error());}echo"Connected successfully
";$sql="SELECT * FROM tutorials_inf WHERE name LIKE "%johar%"";$result=mysqli_query($conn,$sql);$row=mysqli_fetch_array($result,MYSQLI_ASSOC);printf("%s

",$row["name"]);mysqli_free_result($result);mysqli_close($conn);?>

样本输出应该是这样的 -

Connected successfully

johar