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/

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/

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

Popular posts from this blog

ALTER statement in MySQL

MySQL Constraint – PRIMARY KEY

Order By Clause in MySQL