Learning tutorials related to like clauses in Mysql _mysql

MySQL like syntax
the LIKE operator is used in a WHERE expression to search for the specified content in a matching field, and the syntax is as follows:

Where column like pattern
WHERE column isn't like pattern

When you precede the like with the NOT operator, you mean the opposite of like, that is, select column does not contain pattern data records.
Like is usually used with wildcard%,% represents what is not in the wildcard pattern. The like syntax, which does not add wildcard characters, represents an exact match whose actual effect equals = equals operator.
Like use instance
Here is an example of using like query data:
User Table raw Data:

SELECT * FROM user WHERE username like ' small% '

Return query results as follows:

The example is to find all username records with "small", and a small% to begin with a "small" character, which can be followed by any character. Similarly, the% small is the end of the "small", and% of the% is to include the "small" character (together with the '% ' and ' small% ').
The following example will query for records that contain a character anywhere in a field in all username:

SELECT * FROM user WHERE username like '%a% '

MySQL like case
MySQL like matching characters, the default is case-insensitive, if you need to match the case case-sensitive, you can add the BINARY operator:

SELECT * from user where username like BINARY '%azz% '
select * from user where username like BINARY '%azz% '

The BINARY operator represents a binary comparison, so the addition of the operator can be strictly case-sensitive, so the contents of the above two SQL queries are different.
MySQL like Chinese character matching
because of the data storage coding problem, in some cases, MySQL does like search returned data in addition to meet the required data, often return a lot of irrelevant data. At this point you also need to add the BINARY operator to the like followed by a binary comparison:

SELECT * FROM user WHERE username like BINARY '%% '

Tips
when you add the BINARY operator to the like match, the English case is strictly distinguished. So when the retrieved content is mixed in Chinese and English and needs to ignore the English case, you will encounter trouble. To solve this problem, you need to introduce the UPPER () and CONCAT () functions in MySQL:
UPPER (): Capitalize the English string, with UCase ()
CONCAT (): Concatenate multiple strings into a string
The syntax is as follows:

UPPER (str)
CONCAT (str1,str2,...)

So when we want to do a mixed-match search in both Chinese and English and ignore the English case, you can use the SQL statement shown in the following example:
SELECT * from username WHERE UPPER (username) like BINARY concatt ('% ', UPPER (' a Chinese B '), '% ')
In this SQL, the search field and the retrieved content are converted to uppercase, then binary matches are made.
the efficiency of the LIKE operator
the LIKE operator performs a one-scan match of the field data, and the actual execution is less efficient, even if the field is indexed (a% in this way). When the amount of data is large, reduce the use of the LIKE operator as much as possible, and there is not much room for optimization.
using the LIKE clause in PHP script
you can use the where ... Like clause syntax similar to the mysql_query () function in PHP. This function is used to execute SQL commands, followed by another PHP mysql_fetch_array () function that can be used to get all the selected data, if where ... The LIKE clause is used along with the Select command.

However, if the where ... The LIKE clause is being used with the delete or UPDATE command, and the PHP function is no longer required.

Example
Try the following example, Tutorials_tbl table all records whose author name contains Jay will be returned:

 <?php $dbhost = ' localhost '; $dbuser = ' root '; $dbpass = '; $conn = mysql_connect ($db
Host, $dbuser, $dbpass); if (! $conn) {die (' could not connect: '. Mysql_error ());} $sql = ' SELECT tutorial_id, Tutorial_title, Tutoria

L_author, submission_date from Tutorials_tbl WHERE the tutorial_author like "%jay%";
mysql_select_db (' Test ');
$retval = mysql_query ($sql, $conn);
  if (! $retval) {die (' could not get data: '. mysql_error ());} while ($row = Mysql_fetch_array ($retval, Mysql_assoc)) {
     Echo ' Tutorial ID: {$row [' tutorial_id ']} <br>.
     "Title: {$row [' Tutorial_title ']} <br>".
     "Author: {$row [' Tutorial_author ']} <br>".
     ' Submission Date: {$row [' submission_date ']} <br>.
"--------------------------------<br>";
echo "fetched data successfully\n"; Mysql_close ($conn);?>