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.

 NOT LIKE Operator
MySQL NOT LIKE Operator
We have previously learned that the LIKE Operator in MySQL database is a logical operator used to perform pattern matching operation on a database table. And NOT LIKE Operator is defined as opposite of this LIKE operator.
Both LIKE and NOT LIKE operators perform pattern matching in a database table. Thus, they both need wildcards and patterns to function. However, if the LIKE operator is used to find the similar patterns mentioned using the wildcards, NOT LIKE operator is used to find all the records that do not contain the specified pattern.
The NOT LIKE operator is nothing but the amalgamation of two SQL operators, NOT and LIKE operators. Thus, having the combination of their functionalities.
It is used to match a particular pattern in the given string and returns 0 in case of a match and returns 1 otherwise. If either of the two operands of this function is NULL, it returns NULL as result.
This operator is useful for finding strings that do not match a specific pattern or do not have certain characteristics.
Syntax
Following is the basic syntax of MySQL NOT LIKE operator with a SELECT statement −
SELECT column_name(s) FROM table_name
WHERE column_name NOT LIKE [condition];
﻿
Using NOT LIKE Operator with Wildcards
Wildcards are special characters used in SQL queries to match patterns in the data. Following wildcards can be used in conjunction with the NOT 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 NOT 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
Let us begin by creating a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
﻿
Using the below INSERT statements, we are inserting 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 below query to display all the records present in 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, let us use the MySQL NOTLIKE operator to displays the all the records in CUSTOMERS table whose name doesn't starts with 'k'.
SELECT * FROM CUSTOMERS where NAME NOT LIKE 'k%';
﻿
Following are the records whose name doesn't starts with 'k' −
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
The following query displays the records of customers whose NAME doesn't end with 'ik'.
SELECT * FROM CUSTOMERS where NAME NOT LIKE '%ik';
﻿
Following are the records whose name doesn't ends with 'ik' −
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
4
Chaitali
25
Mumbai
6500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Here, we are displaying all the records whose name does not contains the substring 'al'.
SELECT * FROM CUSTOMERS where NAME NOT LIKE '%al%';
﻿
Following are the records whose name doesn't contains the substring 'al' −
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
5
Hardik
27
Bhopal
8500.00
7
Muffy
24
Indore
10000.00
The following query displays all the records whose name does not starts with 'm' and ends with 'y'.
SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE 'm___y';
﻿
As we can see in the output table, the seventh record is eliminated because the name starts with 'm' and ends with 'y'.
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
The below query displays all customer names that does not start with 'k' and have exactly 6 characters.
SELECT * FROM CUSTOMERS WHERE name NOT LIKE 'k_____';
﻿
Following is the output −
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.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
Here, we are displaying the records of CUSTOMERS table, where the second character of the records in ADDRESS column is not "h".
SELECT * FROM CUSTOMERS where ADDRESS NOT LIKE '_h%';
﻿
Following is the output −
ID
NAME
AGE
ADDRESS
SALARY
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Using NOT LIKE Operator with AND/OR Operators
We can use the MySQL NOT LIKE operator with different string patterns to choose rows, combining them with the AND or OR operators.
Syntax
Following is the syntax of using NOT LIKE operator with AND/OR operator −
SELECT column_name(s) 
FROM table_name
WHERE column1 NOT LIKE pattern1 [AND|OR] column2 NOT LIKE pattern2 [AND|OR] ...;
﻿
Example
In the following example, we are displaying all records from the CUSTOMERS table where name does not start with 'k' and the address should not start with 'm' using AND operator −
SELECT * FROM CUSTOMERS 
WHERE name NOT LIKE 'k%' AND address NOT LIKE 'm%';
﻿
Output
Executing the query above will produce the following output −
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
5
Hardik
27
Bhopal
8500.00
7
Muffy
24
Indore
10000.00
NOT LIKE Operator on Strings
The MySQL NOT LIKE operator can perform pattern matching not only on database tables but also on individual strings. Here, the result will obtain as 0 if the pattern exists in the given string, or 1 if it doesn't. The result is retrieved as a result-set using the SQL SELECT statement.
Syntax
Following is the syntax of NOT LIKE operator in MySQL −
SELECT expression NOT LIKE pattern;
﻿
Example
In the following query, the pattern 'Tutorix' is not present in the specified string. So, this operator will return 1.
SELECT 'Tutorialspoint' NOT LIKE 'Tutorix';
﻿
Executing the query above will produce the following output −
'Tutorialspoint' NOT LIKE 'Tutorix'
1
Here, the pattern 'Tutorialspoint' is present in the specified string. Thus, it returns 0 as output.
SELECT 'Tutorialspoint' NOT LIKE 'Tutorialspoint';
﻿
Following is the output −
'Tutorialspoint' NOT LIKE 'Tutorialspoint'
0
Example
If either (string or pattern operands) is NULL, this operator returns NULL. In the following query, the string is NULL, so that the output will be returned as NULL.
SELECT NULL NOT LIKE 'value';
﻿
Executing the query above will produce the following output −
NULL NOT LIKE 'value'
NULL
Here, the search pattern is NULL. So, the output will be returned as NULL.
SELECT 'Tutorialspoint' NOT LIKE NULL;
﻿
Following is the output −
'Tutorialspoint' NOT LIKE NULL
NULL
NOT LIKE Operator Using a Client Program
Besides using MySQL queries to perform the Not Like operator, 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 find data in a MySQL database that doesn't match a specific pattern using a PHP program, you can use the Not Like operator. To do this, we need to execute the 'SELECT' statement using the mysqli function query() as −
$sql = "SELECT * FROM EMP where Name NOT LIKE 'Su%'";
$mysqli->query($sql);
﻿
Example
Following are the programs −
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
$sql = "SELECT * FROM EMP where Name NOT LIKE 'Su%'";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        printf("ID %d, Name %s, DOB %s, Location %s", 
        $row['ID'],
        $row['Name'],
        $row['DOB'],
        $row['Location'],);
        printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
﻿
Output
The output obtained is as follows −
Table records:
ID 101, Name Amit, DOB 1970-01-08, Location Hyderabad
ID 0, Name Raja, DOB 1980-11-06, Location Goa
ID 109, Name Javed, DOB 1980-11-06, Location pune
ID 120, Name Vani, DOB 1980-11-06, Location Delhi
ID 0, Name Devi, DOB 1980-11-06, Location Goa   
﻿
﻿
﻿
﻿
﻿
﻿
﻿

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

NOT LIKE Operator

MySQL NOT LIKE Operator

Syntax

Using NOT LIKE Operator with Wildcards

Example

Using NOT LIKE Operator with AND/OR Operators

Syntax

Example

Output

NOT LIKE Operator on Strings

Syntax

Example

Example

NOT LIKE Operator Using a Client Program

Syntax

Example

Output