Select statement in MySQL
Hello and
welcome everyone. In this article, we will learn about Select statement in
MySQL. Let’s go.
MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/
C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/
Web API 2- .Net Core In depth In 5 Hours click
on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/
The SELECT
statement is using to retrieve data from a database or one or more tables in
MySQL. SELECT is a DML (Data Manipulation Language) command. You can use JOIN,
WHERE condition, GROUP BY clause, HAVING clause, ORDER BY, LIMIT with SELECT
statement.
Syntax of SELECT statement:
SELECT
column_name_1,
column_name_2, column_name_3, …
FROM
table_name;
OR
SELECT
*
FROM
table_name;
Here is * (Star) symbol using for
select all the columns in table.
Syntax of SELECT statement
with JOIN, WHERE condition, GROUP BY clause, HAVING clause, ORDER BY, LIMIT:
SELECT
column_name_1,
column_name_2, column_name_3, …
FROM
table_name_1
INNER or LEFT or RIGHT JOIN
table_name_2 ON condition
WHERE condition
GROUP BY column_name or
expressions
HAVING condition
ORDER BY column_name ASC or
DESC
LIMIT offset;
Example of SELECT statement:
SELECT
First_name,
Last_name, Address
FROM
Employee.
Note 1: You can use a SELECT statement without reference to any table to
retrieve rows.
For
example:
SELECT 2 + 2;
It will show result is 4.
SELECT 3 – 2;
It will show result is 1.
Note 2: If you want more clearly column name in your result then you can
achieve this using column alias name. Following are the syntax:
SELECT
column_name_1 AS alias_name_1,
column_name_2 AS alias_name_2
FROM
table_name
For Example:
SELECT
First_name as Fname,
Last_name as Lname, Address
FROM
Employee.
Now we
learn SELECT statement with COUNT, JOIN, WHERE condition, GROUP BY clause,
HAVING clause, ORDER BY, LIMIT.
COUNT: You can use COUNT command with the SELECT statement to display
the total number of records in the table.
Syntax:
SELECT COUNT (*) FROM
table_name;
Example
SELECT COUNT (*) FROM
employee;
It will show you the total number of
records from the employee table.
JOIN: You can get data from two or more tables and it’s based on the join
condition.
Syntax:
SELECT
column_name_1,
column_name_2, column_name_3, …
FROM
table_name_1
INNER or LEFT or RIGHT JOIN
table_name_2 ON condition
WHERE condition
Example:
SELECT
e_ID,
e_Name, d_name FROM employee
LEFT JOIN
department ON ( employee.d_name =
department.d_name );
WHERE: When you want to view only records that match a specific
condition, then use WHERE conditions.
Syntax:
SELECT
column_name_1,
column_name_2, column_name_3, …
FROM
table_name_1
WHERE condition OR
expression;
Example:
SELECT
e_ID,
e_Name, e_Age FROM employee
WHERE Age > 30;
GROUP BY: It collects multiple records and group the
results based on certain conditions.
Syntax:
SELECT
column_name_1,
column_name_2, column_name_3, …
FROM
table_name_1
WHERE condition
GROUP BY column_name or
expressions
Example:
SELECT
e_ID,
e_Name, e_Age FROM employee
WHERE e_Age > 30
GROUP BY e_Name;
HAVING: You can use the HAVING clause to restrict the records further.
Syntax:
SELECT
column_name_1,
column_name_2, column_name_3, …
FROM
table_name_1
WHERE condition
HAVING condition
Example:
SELECT
e_ID,
e_Name, e_Age FROM employee
GROUP BY e_Name
HAVING e_Age > 30;
ORDER BY: It is used to state the sort order of your
result set. Most important things are, if you do not give anything it will show
the result by ascending order.
Syntax:
SELECT
column_name_1,
column_name_2, column_name_3, …
FROM
table_name_1
ORDER BY column_name ASC or
DESC
Example:
SELECT
e_ID,
e_Name, e_Age
FROM
employee
ORDER BY e_Name DESC;
LIMIT: How many records do you want to display in your result? Then you
can use LIMIT and it will give you specific records.
Syntax:
SELECT
column_name_1,
column_name_2, column_name_3, …
FROM
table_name_1
WHERE condition
LIMIT offset;
Example:
SELECT
e_ID,
e_Name, e_Age FROM employee
WHERE e_Age > 30
LIMIT 10;
It will display only 10 records which e_Age is greater than
30.
In this
article, we have learned SELECT statement and it is the most commonly used
command. We will learn WHERE, JOIN, GROUP BY, HAVING, and ORDER BY in later
article.
MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/
C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/
Web API 2- .Net Core In depth In 5 Hours
click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/
Comments
Post a Comment