MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

LIKE Operator
MySQL LIKE Operator
The LIKE Operator in MySQL database is a logical operator that is used to retrieve the data from a table, based on a specified pattern.
To filter and search for some records in a table, in a very basic way is using a WHERE clause. To elaborate, a WHERE clause with the 'equal to' sign (=) works fine whenever we want search for an exact match. But there may be a requirement where we want to filter out all the results wherever the values in a table have a particular pattern. This can be handled by using a LIKE Operator in a WHERE clause.
The LIKE operator is usually used along with a pattern. However, the placement of this pattern (like at the beginning of the record, or at the ending) is decided using some characters known as wildcards. Without a wildcard character, the LIKE operator is very same as the equal to (=) sign in the WHERE clause.
Syntax
Following is the basic syntax of the LIKE operator in MySQL 
SELECT column_name(s) FROM table_name
WHERE column_name LIKE [condition];
You can specify any condition using the WHERE clause.
You can use the LIKE Operator along with the WHERE clause.
You can use the LIKE Operator in place of the equals to sign.
When LIKE is used along with % sign then it will work like a meta character search.
You can specify more than one condition using AND or OR operators.
A WHERE...LIKE clause can also be used in DELETE or UPDATE SQL commands to specify a condition.
Using LIKE Operator with Wildcards
Wildcards are special characters used in SQL queries to match patterns in the data. Following are the four wildcards used in conjunction with the LIKE operator 
S.No
WildCard & Definition
1
%
The percent sign represents zero, one or multiple characters.
2
_
The underscore represents a single number or character.
3
[]
This matches any single character within the given range in the [].
4
[^]
This matches any single character excluding the given range in the [^].
Note− In the LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The two mainly used wildcard characters are '%' and '_'.
Example
In the following query, we are creating a table named CUSTOMERS using the CREATE statement 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);
The below query inserts 7 records into the above-created table 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
Execute the following query to fetch all the records from the CUSTOMERS table 
Select * From CUSTOMERS;
Following is the CUSTOMERS table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Now, we are retrieving the name of the customers ending with "esh" using the LIKE operator with wildcards 
SELECT * from CUSTOMERS WHERE NAME LIKE '%esh';
Output
The output for the above query is produced as given below 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
Using LIKE Operator with AND/OR Operators
In MySQL, we can also use the LIKE operator with multiple string patterns for selecting rows by using the AND or OR operators.
Syntax
Following is the basic syntax of using LIKE operator with AND/OR operator 
SELECT column_name(s)
FROM table_name
WHERE column1 LIKE pattern1 [AND|OR] column2 
LIKE pattern2 [AND|OR] ...;
Example
The following query retrieves the customers whose names start with 'M' and 'R' 
SELECT * FROM CUSTOMERS 
WHERE Name LIKE 'M%' OR Name LIKE 'R%';
Output
Following is the CUSTOMERS table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
7
Muffy
24
Indore
10000.00
Using NOT Operator with LIKE Operator
We can use the NOT operator in conjunction with LIKE operator to extract the rows which does not contain a particular string provided in the search pattern.
Syntax
Following is the basic syntax of NOT LIKE operator in SQL 
SELECT column1, column2, ...
FROM table_name
WHERE column1 NOT LIKE pattern;
Example
In the following query, we are retrieving all the customers whose name does not start with K 
SELECT * FROM CUSTOMERS WHERE Name NOT LIKE 'K%';
Following is the output 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
7
Muffy
24
Indore
10000.00
Example
If the search pattern is exactly as provided string, this operator returns 1 
SELECT 'Tutorialspoint' LIKE 'Tutorialspoint';
Following is the output 
'Tutorialspoint' LIKE 'Tutorialspoint'
1
If the search pattern is not exactly same as the string, it returns 0 as output 
SELECT 'Tutorialspoint' LIKE 'Tutorial';
Following is the output 
'Tutorialspoint' LIKE 'Tutorial'
0
Example
If either of the first two operands is NULL, this operator returns NULL.
SELECT NULL LIKE 'value';
The output for the program above is produced as given below 
NULL LIKE 'value'
NULL
Here, we are providing NULL to the seach pattern operand.
SELECT 'Tutorialspoint' LIKE NULL;
Following is the output 
'Tutorialspoint' LIKE NULL
NULL
Client Program
Besides using MySQL LIKE operator to filter and search for some records in a table, based on a specified pattern, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.
Syntax
Following are the syntaxes of this operation in various programming languages 
PHPNodeJSJavaPython
To fetch records from a table, based on a specified pattern through PHP program, we need to execute the 'SELECT' statement with 'LIKE' clause using the mysqli function query() as follows 
$sql = "SELECT COLUMN_NAME1, COLUMN_NAME2,.. 
   FROM TABLE_NAME WHERE columnn LIKE PATTERN";
$mysqli->query($sql,$resultmode)
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = 'SELECT * FROM tutorials_tbl Like tutorial_author like "Jo%"';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Id: %s, Title: %s, Author: %s, Date: %d ", 
         $row["tutorial_id"], 
         $row["tutorial_title"], 
         $row["tutorial_author"],
         $row["submission_date"]);               
   }
} else {
   printf('No record found.');
}
mysqli_free_result($result);
$mysqli->close();
Output
The output obtained is as follows 
Id: 4, Title: Learn PHP, Author: John Poul, Date: 2023
﻿
﻿
﻿
﻿
﻿

S.No	WildCard & Definition
1	% The percent sign represents zero, one or multiple characters.
2	_ The underscore represents a single number or character.
3	[] This matches any single character within the given range in the [].
4	[^] This matches any single character excluding the given range in the [^].

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

LIKE Operator

MySQL LIKE Operator

Syntax

Using LIKE Operator with Wildcards

Example

Output

Using LIKE Operator with AND/OR Operators

Syntax

Example

Output

Using NOT Operator with LIKE Operator

Syntax

Example

Example

Example

Client Program

Syntax

Example

Output