Learn Python

Learn Data Structure & Algorithm

Learn Numpy

Learn Pandas

Learn Matplotlib

Learn Seaborn

Learn Statistics

Learn Math

Learn MATLAB

Learn Machine learning

Learn Github

Learn OpenCV

Learn Deep Learning

MySQL Introduction

MySQL Setup

MySQL Operators

MySQL Basic Works

MySQL Create Table

MySQL insert and get values

MySQL Condition

MySQL GROUP BY

MySQL Case

MySQL sorting and calculate avg,sum,count

MySQL Update

MySQL Time

MySQL String

MySQL Rollback & Commit

MySQL Join and Concatenate

MySQL Index

MySQL Date

MySQL View

MYSQL With Python

Learn MongoDB

Learn Web scraping

Learn Excel

Learn Power BI

Learn Tableau

Learn Docker

Learn Hadoop

SQL condition

How to get a selected columns value using condition of a table?

You can use AND, OR, IN and NOT IN to set multiple condition

Syntax:
select * from table_name where column_name="condition";

Example 1:
select * from books_store_1 where price>150;

Example 2:
select * from books_store_1 where books="python";

Example 3:
select * from books_store_1 where books="python";

Example 4:
select * from books_store_1 where books in ("SQL");

Example 5:
select * from first_table where Books not in ("SQL");

Example 6:
select * from first_table where Books not in ("SQL") and Price>200;

Example 7:
select Books from first_table where Id< 2;

here we wanted books name whose id is less than 2

How to use between function?

Syntax:
SELECT column_name's FROM table_name WHERE column_name BETWEEN value1 AND value2;

We use this to get value between one value to another value.

Example 1:
SELECT Id FROM first_table WHERE Id BETWEEN 2 AND 4;

Example 2:
SELECT Id,price FROM first_table WHERE Id BETWEEN 2 AND 4;

Example 3:
SELECT Id,price FROM first_table WHERE Price BETWEEN 200 AND 400;

Example 4:
SELECT Id,price,Books FROM first_table WHERE Books BETWEEN "SQL" AND "Python";

We can also use IN, NOT IN Here
Example:
WHERE Id BETWEEN 2 AND 4 AND Books NOT IN ("SQL");

How to use NOT BETWEEN function?

Syntax:
SELECT column_name's FROM table_name WHERE column_name BETWEEN value1 AND value2;

We use this to get value which is not between a range of values.

Example 1:
SELECT Id FROM first_table WHERE Id NOT BETWEEN 2 AND 4;

Example 2:
SELECT Id,price FROM first_table WHERE Id NOT BETWEEN 2 AND 4;

Example 3:
SELECT Id,price FROM first_table WHERE Price NOT BETWEEN 200 AND 400;

LIKE operator

The LIKE operator is used in a WHERE clause. Like is used to search a specified pattern in a column. There are two wildcards which are commonly used in the LIKE operator.
These wildcards are used to
define or create the pattern:
1. Percent sign (%): It represents zero or one or multiple characters.
2. Underscore sign (_): It represents one or single character

To create a pattern we can use % and _ separately and also together.

Let's see some example of patterns:

Pattern	Description
'e%'	Finds any values which start with "e"
'%p'	Finds any values which end with "p"
'h_%'	Finds any values which start with "h" and are at least 2 characters in length
'z__%'	Finds any values which start with "z" and are at least 3 characters in length
'%ra%'	Finds any values which have "ra" in any position
'_s%'	Finds any values which have "s" in the second position
'a%o'	Finds any values which start with "a" and ends with "o"

Syntax:
SELECT *
FROM table_name
WHERE columnN LIKE pattern;

Example 1:
SELECT * FROM Customers
WHERE CustomerName LIKE '%a%';

Example 2:
SELECT books, price FROM Customers
WHERE CustomerName LIKE '_z%';

Use of BINARY
If you normally write letters in a pattern then it is not case sensitive. Suppose you write letters in the pattern in upper case and you want to get upper case pattern matched results then you have to write BINARY between WHERE and column_name. For case-sensitive works use BINARY.

Syntax:
SELECT *
FROM table_name
WHERE BINARY column_name LIKE pattern;

NOT LIKE operator

LIKE gives an output that contains the given pattern but NOT LIKE gives output exact opposite. It means NOT LIKE gives an output that doesn't contain the given pattern.

Syntax:
SELECT column1,column2,column3......
FROM table_name
WHERE column_name NOT LIKE pattern;

If you write asterisk(*) sign in that place where you wrote columns name then you will get all the column values in the output.

Syntax:
SELECT *
FROM table_name
WHERE column_name NOT LIKE pattern;

Let's see some example:
Suppose you have a table name student where you have four columns name, phone_number, address, and id.

SELECT * FROM student
WHERE name NOT LIKE "Rafs%"

SELECT * FROM student
WHERE name NOT LIKE "%Rafsan%";

SELECT * FROM student
WHERE name NOT LIKE "Rafsan%Ahmad";

SELECT * FROM student
WHERE phone_number NOT LIKE "0834%";

SELECT * FROM student
WHERE phone_number NOT LIKE "%0834%";

SELECT * FROM student
WHERE name NOT LIKE "_Raf%";

SELECT * FROM student
WHERE name NOT LIKE "____Raf%";

SELECT * FROM student
WHERE name NOT LIKE "____Raf%" OR name LIKE "ahmad%";

BINARY
If you normally write letters in a pattern then it is not case sensitive. Suppose you write letters in the pattern in upper case and you want to get upper case pattern matched results then you have write BINARY between WHERE and column_name. For case sensitive works use BINARY.

Syntax:
SELECT *
FROM table_name
WHERE BINARY column_name NOT LIKE pattern;

How to use Limit?

Suppose you wrote a query and according to the query you will get some output. Now the number of outputs can be huge but you want to see some outputs among all the outputs.
For example, the actual number of number of outputs are 100 but you want to see or get only 10 outputs. In this case, you will use limit and pass value 10. By doing this you will see only 10 outputs.

Syntax:
SELECT *
FROM table_name
WHERE condition
LIMIT number;

Example:
SELECT * FROM books_store_1
where books="python"
LIMIT 3;

Because we used * sign that's why we will get all the columns but if you want some specific column then write those column names in the position of the asterisk (*) sign.

CodersAim is created for learning and training a self learner to become a professional from beginner. While using CodersAim, you agree to have read and accepted our terms of use, privacy policy, Contact Us