EDUCBA

Home » Data Science » Data Science Tutorials » MySQL Tutorial » SELECT in MySQL

SELECT in MySQL

Introduction to SELECT in MySQL

In this topic, we are going to learn about SELECT in MySQL and mostly into DQL which is “Data Query Language”. This comes to play when we try to fetch records from the database and it starts with the “SELECT” command. This command can be used with many SQL clauses as well as other functions to get desired records.

There are many kinds of SQL commands which can be categorized into the following:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

1. The basic syntax of the SELECT command:

SELECT * FROM table_name;

This will fetch all the records with all attributes from a table.

SELECT column1, column2,…. FROM table_name;

This will fetch specified columns from a table that are passed through the query.

2. This SELECT command can also be used with the INSERT command which is used for adding records to the existing table.

INSERT INTO table_name1 SELECT * FROM table_name2;

Here query will fetch all the records from table_name2 and will insert those into table_name1.

Examples to Implement SELECT in MySQL

Let’s consider there is a customer table with the following attributes.

Cust_id First_name Last_name Contact Email City Amount
1001 Rohit Sharma 9876736587 rohit@gmail.com Mumbai 10000
1002 Virat Kohli 8752877855 virat@tahoo.in Delhi 60000
1003 Sachin Tendulkar 9867868678 sachin@hotmail.com Mumbai 15000
1004 Virendra Shewag 9087788988 shewag@gmail.com Delhi 20000

We are going to see some basic SELECT queries using some clauses for the basic understanding of how this command works.

Example #1

SELECT * FROM customer;

(This will fetch all the records with all attributes from a table.)

Output:

Cust_id First_name Last_name Contact Email City Amount
1001 Rohit sharma 9876736587 rohit@gmail.com Mumbai 10000
1002 Virat Kohli 8752877855 virat@tahoo.in Delhi 60000
1003 Sachin Tendulkar 9867868678 sachin@hotmail.com Mumbai 15000
1004 Virendra Shewag 9087788988 shewag@gmail.com Delhi 20000

Example #2

SELECT cust_id, first_name, last_name, email,city
FROM customer;

(This will fetch specified columns from a table which are passed through a query)

Output:

Cust_id First_name Last_name Email City
1001 Rohit sharma rohit@gmail.com Mumbai
1002 Virat Kohli virat@tahoo.in Delhi
1003 Sachin Tendulkar sachin@hotmail.com Mumbai
1004 Virendra Shewag shewag@gmail.com Delhi

Example #3

SELECT cust_id, first_name, last_name, email,city FROM customer
WHERE city = ‘Delhi’;

(WHERE command will fetch those records only, where the city will be ‘Delhi’)

Output:

Cust_id First_name Last_name Email City
1002 Virat Kohli virat@tahoo.in Delhi
1004 Virendra Shewag shewag@gmail.com Delhi

Example #4

SELECT cust_id, first_name, last_name,city, amount FROM customer
WHERE amount BETWEEN 5000 AND 25000;

(BETWEEN clause will return records which satisfy the given range of condition passed in the query)

Output:

Cust_id First_name Last_name City Amount
1001 Rohit sharma Mumbai 10000
1003 Sachin Tendulkar Mumbai 15000
1004 Virendra Shewag Delhi 20000

Example #5

SELECT * FROM customer
ORDER BY amount DESC;

(Used to sort both numeric and string values either in ascending or descending manner. But by default, it sorts in ascending manner. If we want in descending, then we need to specify it after using ORDER BY clause)

Output:

Cust_id First_name Last_name Contact Email City Amount
1002 Virat Kohli 8752877855 virat@tahoo.in Delhi 60000
1004 Virendra Shewag 9087788988 shewag@gmail.com Delhi 20000
1003 Sachin Tendulkar 9867868678 sachin@hotmail.com Mumbai 15000
1001 Rohit sharma 9876736587 rohit@gmail.com Mumbai 10000

Clauses with SELECT Command

Other clauses with SELECT command:

1. SELECT: Used to fetch all the records from a table.

SELECT * FROM table;

2. DISTINCT: Used to fetch all the unique values from a table.

SELECT DISTINCT col_name FROM table;

3. WHERE: Used forgiving conditions in the retrieval of records.

SELECT employee_id FROM employee
WHERE name = ‘stella’;

4. COUNT: Used to get the number of records present in a table.

SELECT COUNT(*) FROM employee;

5. ORDER BY: Used to sort both numeric and string values either in ascending or descending manner. But by default, it sorts in ascending manner. If we want to descend, then we need to specify it after using the ORDER BY clause.

SELECT first_name FROM student
ORDER BY marks desc;

6. LIMIT: This is used to specify the number of records we want after executing the query. If we want the top 5 students of a class, then after sorting the results, we can use this LIMIT by specifying 5. So that it will only fetch the top 5 records.

SELECT first_name FROM student
ORDER BY marks desc
LIMIT 5;

(**ORDER BY used here for sorting value in descending order)

7. AND: If 2 conditions are given and both are met for a record, then only the query will fetch that records.

SELECT employee_id FROM employee
WHERE name = ‘stella’ AND city = ‘Bangalore’;

8. OR: If 2 conditions are given and one of them is met for a record, then that record will be fetched.

SELECT employee_id FROM employee
WHERE department = ‘IT’ OR city = ‘Bangalore’;

9. NOT: Used with conditions. If we specify NOT before any conditions, records that are not meeting that conditions will be fetched.

SELECT employee_id FROM employee
WHERE NOT BETWEEN 1 AND 10;

10. BETWEEN: This operator selects records within a given range. Mostly we use this where we want to specify a range of dates.

SELECT emp_id FROM employee
WHERE emp_id BETWEEN 1 AND 10;

SELECT * FROM employee
WHERE join_date BETWEEN '2007-01-01' AND '2008-01-01';

11. IN: This operator allows us to specify multiple values in a WHERE clause.

SELECT * FROM employee
WHERE employee_id IN (1001,1004,1008,1012);

12. LIKE: This operator is used with the WHERE clause to search for a specified pattern in a column containing the string.

  • ‘A%’ –  string starts with A
  • ‘&A’ – ends with A
  • ‘%A%’ – A will be in between the string
  • ‘_A%’ – Here the 2nd letter will be A
  • ‘%A_’ – The 2nd from the last letter will be A

SELECT first_name FROM table
WHERE first_name LIKE 'A%';

13. SUBSTRING: Used to pick a specific character from a string by specifying the position.

SELECT SUBSTRING(customer_name,1,5) FROM customer_table;

(it will fetch character from 1st  to 5th position of a string)

14. INSTR: This returns a position of a string in another string.

SELECT INSTR(‘independence’, ‘pen’);

(it will find the position of ‘pen’ in the word ‘independence’)

15. GROUP BY: This is used to segregate records on the basis of some given conditions.

SELECT employee_id FROM employee GROUP BY department HAVING salary > 100000;

(Here group by segregated employees on the basis of their department and whose salary more than 100k.
Condition always comes with HAVING statement in GROUP BY clause.)

Aggregate Functions

Below is the different aggregate function:

1. SUM: Calculates the sum of values.

SELECT SUM(salary) FROM employee;

2. AVG: Calculates the average set of values.

SELECT AVG(salary) FROM employee;

3. MIN: Gets the minimum value in a set of values.

SELECT MIN(salary) FROM employee;

4. MAX: Gets the maximum value in a set of values.

SELECT MAX(salary) FROM employee;

Joins in SELECT in MySQL

1. INNER JOIN: Returns records that have a matching value in both tables.

SELECT * FROM order
INNER JOIN customer
ON order.cust_id = customer.cust_id;

inner Join

2. LEFT JOIN: Returns all records from the left table and the matched records from the right table.

SELECT * FROM order
LEFT JOIN customer
ON order.cust_id = customer.cust_id;

Left Join

3. RIGHT JOIN: Returns all records from the right table and the matched records from the left table.

SELECT * FROM order
RIGHT JOIN customer
ON order.cust_id = customer.cust_id;

Right Join

4. FULL OUTER JOIN: Returns all the records when there is a match on either the left or the right table.

SELECT * FROM order
FULL OUTER JOIN customer
ON order.cust_id = customer.cust_id;

FULL OUTER JOIN

Conclusion – SELECT in MySQL

These commands and clauses we have discussed above are very useful in real-time scenarios as it provides the basic concepts of how to use SQL queries to fetch and manipulate data in the database. Apart from this, while using advance and analytical queries like window function etc, these clauses are very important.

Recommended Articles

This is a guide to SELECT in MySQL Here we discuss the Example to implement SELECT in MySQL with Clauses, Aggregate Functions, and Joins. You can also go through our other suggested articles to learn more –

  1. How to Connect Database to MySQL?
  2. MySQL Queries
  3. INSERT in Oracle | Examples
  4. Examples of DISTINCT in Oracle
  5. Condition in MySQL | Conditional Operators in MySQL
  6. Complete Guide to MySQL BETWEEN
  7. How to Declare String in PHP?
  8. Guide to Left Join in Oracle
  9. SQL Right Join | Examples
  10. Guide on How to Use QlikView Left Join?
  11. Learn the GROUP BY in Oracle
  12. How to Use MySQL Outer Join?
  13. Guide to 4 Types of SQL Outer Join
1 Shares
Share
Tweet
Share