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/
Visit Our Website : https://expertwithsagarjaybhay.com/




Comments

Post a Comment

Popular posts from this blog

ALTER statement in MySQL

Image
Hello and welcome everyone. In this article, we will learn about the ALTER table 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/ MySQL ALTER command is used to modify the table, view an existing database. When you have completed your database design and after that, you realize some of the information was left out when creating a database. You want to change without losing existing data, and then you can change data using ALTER command. MySQL ALTER command is used to add a column with an existing table, add multiple columns, drop a column, modify a column, rename a column, and rename the table. ALTER command also used to add and drop constraints on the existing table. ...
Read more

Order By Clause in MySQL

Image
Hello and welcome everyone. In this article, we will learn about the Order By Clause 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/ MySQL Order by clause is used to sort the records or data in ascending or descending order. Order by clause allows us to sort records or data by a single column or multiple columns in ascending or descending order. There are two key using in Order by clause:          ASC: - sorting data or records by ascending order.          DESC: - sorting data or records by descending order. Syntax:          SELECT column_name_1, column_n...
Read more

MySQL Constraint – PRIMARY KEY

Image
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/ Hello everyone in the previous article we learn about MySQL Constraint – UNIQUE and in this article, we will learn PRIMARY KEY constraint with example. Let’s go. MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/ What is the PRIMARY KEY? MySQL PRIMARY KEY constraint uniquely identifies each row in a table and also it creates a unique index for accessing the table will access faster. The columns of a Primary Key cannot have null values in MySQL and it must contain unique values. A Primary key has only one on a table, if the primary key used with multiple fields, then they are called a Composite Key. Syntax 1 for creating PRIMARY KEY with CREATE table statement.      ...
Read more