如何在 MySQL 中实现关键字搜索?
mysqlmysqli database
要在 MySQL 中实现关键字搜索，可以使用 LIKE 运算符。语法如下 −
SELECT *FROM yourTableName where yourColumnName Like ‘%anyKeywordName%’ or yourColumnName Like ‘%anyKeywordName%’;
为了进一步理解，让我们首先创建一个表。以下是创建表的查询 −
mysql> create table KeywordSearchDemo −> ( −> StudentId int −> , −> StudentName varchar(100) −> ); Query OK, 0 rows affected (0.86 sec)
使用 insert 命令在表中插入一些记录。插入记录的查询如下 −
mysql> insert into KeywordSearchDemo values(100,'Adam John'); Query OK, 1 row affected (0.40 sec) mysql> insert into KeywordSearchDemo values(101,'John Smith'); Query OK, 1 row affected (0.17 sec) mysql> insert into KeywordSearchDemo values(103,'John Taylor'); Query OK, 1 row affected (0.15 sec) mysql> insert into KeywordSearchDemo values(104,'Carol Taylor'); Query OK, 1 row affected (0.21 sec) mysql> insert into KeywordSearchDemo values(105,'Maria Garcia'); Query OK, 1 row affected (0.20 sec) mysql> insert into KeywordSearchDemo values(106,'James Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into KeywordSearchDemo values(110,'Mike Brown'); Query OK, 1 row affected (0.22 sec)
使用 select 语句显示表中的所有记录。查询如下 −
mysql> select *from KeywordSearchDemo;
以下是输出 −
+-----------+--------------+ | StudentId | StudentName | +-----------+--------------+ | 100 | Adam John | | 101 | John Smith | | 103 | John Taylor | | 104 | Carol Taylor | | 105 | Maria Garcia | | 106 | James Smith | | 110 | Mike Brown | +-----------+--------------+ 7 rows in set (0.00 sec)
以下查询仅选择与关键字相关的名称。查询如下 −
mysql> select StudentName from KeywordSearchDemo −> where StudentName Like '%John%' or StudentName Like '%Taylor%';
以下是显示包含关键字 “John” 和 “Taylor” 的记录的输出 −
+--------------+ | StudentName | +--------------+ | Adam John | | John Smith | | John Taylor | | Carol Taylor | +--------------+ 4 rows in set (0.10 sec)
您甚至可以从表中返回 StudentId 列。
mysql> select *from KeywordSearchDemo −> where StudentName Like '%John%' or StudentName Like '%Taylor%';
以下是输出 −
+-----------+--------------+ | StudentId | StudentName | +-----------+--------------+ | 100 | Adam John | | 101 | John Smith | | 103 | John Taylor | | 104 | Carol Taylor | +-----------+--------------+ 4 rows in set (0.00 sec)
