MySQL Select Statement

In this lesson you will be learn how to use SELECT statement in MySQL and you can also learn how to use SELECT statement with WHERE clause. The SELECT statement is used to retrieve the records from the table.

MySQL Select Statement

MySQL Select Statement

In this lesson you will be learn how to use SELECT statement in MySQL and you can also learn how to use SELECT statement with WHERE clause. The SELECT statement is used to retrieve the records from the table. There are some keywords uses in SELECT statement that are described in the following table

Keywords Description
SELECT  SELECT statement is used to retrieve fields from one or more tables. 
FROM Tables containing  to the fields.
WHERE The WHERE clause is used to describe the criteria to restrict the records retrieved.
GROUP BY The GROUP BY clause is used to determine how the records should be grouped.  
HAVING HAVING clause used with GROUP BY to define the criteria for grouped records
ORDER BY  The ORDER BY clause is used to described the criteria for ordering the record.
LIMIT The LIMIT clause is used to restrict the limit of number of records retrieved.

Here is the video instruction of using select statement in SQL Query:

The simple SELECT statement is used to retrieve the all records from table. By the following example you can retrieve the full list of Emp table.

mysql> SELECT * FROM employee ;

If you want to retrieve only some fields from a table, then you have to provide a comma separated list of column names. By the following example you select Name, City and Age fields from the Emp table.

mysql> SELECT first_name, last_name  from employee ;

The WHERE clause is used to limit the number of records. The comparison operators are used with WHERE clause to limit the number of records. Comparison operator?s list are given below: 

 Operator  Description 
 = Equal to 
<> or != Not equal to 
< Less then
< =  Less then or equal to 
>  Greater then 
> =  Greater then or equal to 
Like  Used for comparing string 
Between  Used for checking value between a range.
IN  Used to check values in a list
NOT IN  Used to check the value is not in the list. 

First of all you should create a table with following query:


create table employee (
  EmpId VARCHAR(10),
  first_name VARCHAR(20), 
  last_name VARCHAR(20), 
  city VARCHAR(20),
  Profit bigint,
  Designation VARCHAR(20)
);

Now you should insert some data into the table. Please use follow sql query for inserting data into employee table:


insert into employee values('E001','Raj','Singh','Delhi', 10000, 'Manager');
insert into employee values('E002','Nilesh','Roy','Banglore', 5000, 'Sales Rep');
insert into employee values('E003','Ram','Singh','UP', 5050, 'IT Engineer');
insert into employee values('E004','Vikram','Singh','UP', 1050, 'IT Engineer');
insert into employee values('E005','Chandra','Aggarwal','Delhi', 8045, 'Sales Rep');

Now we are ready to learn select statement in MySQL.

MySQL Like Statement

% Character - If you are working with Strings, then % character can be used as a wildcard. By the following example you can retrieve the all fields from Emp table where the Designation field contain the text, 'Manager'.

mysql> SELECT * FROM employee WHERE Designation LIKE '%Manager%';

_ character - The underscore character can be used as a placeholder. By the following example you can selects the all records from the table Emp, where the Name starts with 'R' followed by four characters. For this we have to use four underscores.

mysql> SELECT * FROM employee WHERE first_name LIKE 'R%';

BETWEEN Clause - The BETWEEN clause can be used with numbers, dates and text. The following example is used to retrieve all fields Emp table where the Salary is between 10000 AND 20000.

mysql> SELECT * FROM employee WHERE Profit BETWEEN 10000 AND 20000;

OR Clause - The OR clause is used to check the values against the range of values that have been specified. The following example retrieves the list of all records where the Designation is either Manager or Assistant in the Emp table.

mysql> SELECT * FROM employee WHERE Designation ='Manager' OR 'Assistant';

IN Clause - The IN clause is used to check the values against to many values that have been specified in IN clause. The following example retrieves the list of all records where the Designation is either Manager or Assistant in the Emp table.

mysql> SELECT * FROM employee WHERE Designation IN ('Manager', 'Assistant');

NOT IN Clause - You can use the NOT modifier with IN clause for checking the values,. Which are not within the list. The following example retrieves the list of all records where the Designation is not equal to Manager or Assistant in the Emp table.

mysql> SELECT * FROM employee WHERE Designation NOT IN ('Manager', 'Assistant');

The following list shows you a Aggregate Function that available in MySQL.

  • AVG( );
    The AVG( ) function returns the average value in a group of records. Example of the AVG( ) function:-
    SELECT AVG(Profit) FROM employee GROUP BY EmpId;
    •  
  • COUNT( );
    The COUNT( ) function returns the number of  records in a group of records. Example of the COUNT( ) function:-
    SELECT COUNT(Profit) FROM employee GROUP BY EmpId;
    •  
  • MAX( );
     The MAX( ) function return the maximum value in a group of records. Example of the MAX( ) function:-
    SELECT MAX(Profit) FROM employee GROUP BY EmpId;
    •  
  • MIN( );
    The MIN( ) function returns minimum value in a group of records. Example of the MIN( ) function:- 
    SELECT MIN(Profit) FROM employee GROUP BY EmpId;
    •   
  • SUM( );
    The SUM( ) function return the sum of the field. Example of the SUM() function :
    SELECT SUM(Profit) FROM employee;

The HAVING Clause
As you know the WHERE clause is used to restrict the records in a query. But if you want to restrict the records by using the Aggregate function then you have to use the HAVING clause. The HAVING clause restricts the records after they have been grouped. The following example shows the list of all Employees who did profit over 10000 on average.

mysql> SELECT AVG(Profit) FROM employee GROUP BY EmpId HAVING AVG(Profit) > 5000;

The ORDER BY Clause
The ORDER BY clause can be used to set the order of the retrieved records. The following example shows the list of all employees in the Emp table in alphabetical order. In this clause we can use the ASC or DESC modifiers to set the order of records in ascending or descending order. If any modifier is not provided then the records are listed in ascending order.

mysql> SELECT first_name FROM employee ORDER BY first_name;

The LIMIT Clause
The LIMIT clause can be used to limit the number of records that have been returned by the SELECT statement. You can specify the start row, and number of records retrieved.

mysql>  SELECT * FROM employee  LIMIT 0,2;

Tutorials

  1. MySQL Column Type Database
  2. MySQL Training ? Online Free MySQL Training
  3. Introduction to MySQL
  4. What is MySQL
  5. Installing MySQL on Windows
  6. MySQL Configuration
  7. MySQL Creating account and changing password
  8. Understanding Various MySQL Executable Files
  9. MySQL User Interface
  10. MySQL Monitoring
  11. MySQL Creating and Deleting Database
  12. Database Designing
  13. Identifiers & Qualifiers
  14. Operators & Functions
  15. MySQL Select Statement
  16. Selecting All Columns or Specific columns
  17. Data Definition Statement
  18. Data Manipulation Statements
  19. Joins
  20. Views
  21. Stored Procedures and Functions
  22. Writing Subqueries
  23. Flow Control Constructs
  24. CURSORS
  25. TRIGGERS
  26. Locking Issues
  27. MySQL Transactional and Locking Statements
  28. Table Maintenance Statements
  29. SET Syntax
  30. Account Management Statements
  31. MySQL String Function
  32. MySQL Training ? Online Free MySQL Training
  33. Identifiers & Qualifiers
  34. Identifiers & Qualifiers
  35. The info Attribute of page Directive In JSP
  36. MySQL Configuration
  37. Creating Database Tables and Indexes
  38. MySQL Directory Structure
  39. MySQL How to Topic
  40. Identifiers Qualifiers