MySql Select

Select Statement

The SELECT statement is used to select data from a database.

Syntax
In its simplest form, the syntax for the MySQL SELECT statement is:

SELECT expressions
FROM tables
WHERE conditions;

Here The full syntax for the MySQL SELECT statement is:

SELECT [ ALL | DISTINCT | DISTINCTROW ]
   expressions(colounm Name)
FROM tables
WHERE conditions
[ GROUP BY expressions(colounm Name) ]
[ HAVING condition ]
[ ORDER BY expression(colounm Name) [ ASC | DESC ] ]
[ LIMIT [offset_value] number_rows | LIMIT number_rows OFFSET offset_value ]
Parameters or Arguments

ALL, DISTINCT, and DISTINCTROW are optional and only one of these can be specified.

Value Explanation
ALL Returns all matching rows
DISTINCT Removes duplicates from the result set. Learn more about DISTINCT clause
DISTINCTROW Synonym for DISTINCT. Removes duplicates from the result set
Query:- Get all employee details from the employee table

SELECT * FROM emp;

Query:- Get all Distinct(remove duplicate) Record from the employee table

SELECT DISTINCT salary
FROM emp;

Query:- Get all Distinctrow(remove duplicate) Record from the employee table

SELECT DISTINCTROW salary
FROM emp;

Query:-Get employee details from employee table whose Salary between 15000 and 25000.

SELECT *
FROM emp
WHERE salary
BETWEEN 15000
AND 25000

Query:-Get employee details from employee table whose Salary less than 25000

SELECT *
FROM emp
WHERE salary<25000;

Query:- Get First_Name from employee table in upper case.Using upper() function.

SELECT upper( firstName )
FROM emp;

Query:- Get First_Name from employee table in lower case.Using lower() function.

SELECT lower( firstName )
FROM emp;

Query:- Get First_Name from employee table using alias name “Employee Name”

SELECT firstName Employee
FROM emp

Here:-Employee is an alias name.
Query:- Get current Date & Time of Mysql.Using now() funtion.

 SELECT now( );

Query:- Select first 3 characters of FIRST_NAME from EMPLOYEE.

 SELECT substring( firstName, 1, 3 )
FROM emp

Query:- Get length of firstname from employee table.

select length(firstName) from emp

Query:-Get firstname from employee table after removing white spaces from right side.

SELECT RTRIM( firstName )
FROM emp

Query:-Get first Name from employee table after removing white spaces from left side.

select LTRIM(firstName) from emp

Query:-Get First_Name from employee table after replacing 'o' with '$'.

SELECT REPLACE( firstName, 'o', '$' )
FROM emp

Using Limit Keyword

If you want to find the record between range then use the limit keyword.

SYNTAX

Query:- Get all Record from the employee table between 0 to 5

 SELECT empid, lastName, firstName
FROM emp
LIMIT 0 , 5 

Query:- Get 4 Records from the employee table.

SELECT *
FROM emp
LIMIT 4  

Query:- Get 6 Records from the employee table start from record no. 4 .

SELECT *
FROM emp
LIMIT 4 ,6 
MySQL: ORDER BY Clause
Description

The MySQL ORDER BY clause is used to display The Records in Ordering Form (Sorting)

SELECT expressions(Coloum Name)
FROM tables
WHERE conditions
ORDER BY expression(Colunm Name) [ ASC | DESC ];

Parameters or Arguments

expressions are the columns or calculations that you wish to retrieve.

tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

conditions are conditions that must be met for the records to be selected.

ASC is optional. It sorts the result set in ascending order by expression (default, if no modifier is provider).

DESC is optional. It sorts the result set in descending order by expression.

Note

  • If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by expression in ascending order (which is equivalent to "ORDER BY expression ASC").
  • The ORDER BY clause can be used in a SELECT statement,SELECT LIMIT statement, and DELETE LIMIT statement in MySQL.
Query:-Get all employee details from the employee table order by First Name Ascending order

SELECT *
FROM emp
ORDER BY firstName ASC;

Query:-Get all employee details from the employee table order by First name descending

SELECT *
FROM emp
ORDER BY firstName DESC;

Query:-Get all employee details from the employee table where salary> 1000 and empid in descending order

SELECT *
FROM emp 
WHERE salary >1000 
ORDER BY empid DESC ;

MySQL: GROUP BY Clause

Description

The MySQL GROUP BY Clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

Syntax

SELECT expression1(colonm 1), expression2(colonm 2), ... expression_n(colonm n), 
       aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1(colonm 1), expression2(colonm 2), ... expression_n(colonm n);

Parameters or Arguments

expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.

aggregate_function can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

conditions are conditions that must be met for the records to be selected.

Query:-Get Total Salary of all employee from emp table as Group By Salary using(sum() function)

SELECT salary, SUM( salary ) AS "Total Salary"
FROM emp
GROUP BY salary;

Query:-Get Total Salary of all employee from emp table where salary>1000 as Group By manager

SELECT manager, sum( salary ) AS "Total Salary"
FROM emp
WHERE salary >1000
GROUP BY manager

Query:-Get Total Number Of Employee from emp table where salary>1000 as Group By manager

SELECT manager, COUNT( * ) AS "Number of Employee"
FROM emp
WHERE salary >=1000
GROUP BY manager

Query:-Get Total Number Of Employee from emp table where salary>1000 as Group By manager using(count Function)

SELECT manager, COUNT( * ) AS "Number of Employee"
FROM emp
WHERE salary >=1000
GROUP BY manager

Query:-Get Lowest Salary from emp table as Group By manager using(min ()Function)

SELECT empid, MIN( salary ) AS "Lowest salary"
FROM emp
GROUP BY manager;

Query:-Get Highest Salary from emp table using(max()Function)

SELECT empid, MAX( salary ) AS "Highest salary"
FROM emp

Query:-Get Highest Salary from emp table as Group By manager using(max()Function)

SELECT manager, MAX( salary ) AS "Highest salary"
FROM emp
GROUP BY manager

Query:-Get managerwise average salary from employee table order by salary ascending

SELECT manager, avg( SALARY ) AvgSalary
FROM emp
GROUP BY manager
ORDER BY AvgSalary ASC

Query:-Get Managerwise maximum salary from employee table order by salary ascending

SELECT manager, max( SALARY ) MaxSalary
FROM emp
GROUP BY manager
ORDER BY MaxSalary ASC
MySQL: HAVING Clause
Description

The MySQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.

Syntax

SELECT expression1(colonm name1), expression2(colonm name2), ... expression_n(colonm namen), 
       aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1(colonm name1), expression2(colonm name2), ... expression_n(colonm namen)
HAVING condition;

Parameters or Arguments

aggregate_function can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.

condition is the condition that is used to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

Query:-Get all record of employee manager where manager id =1001 if that employee having manager.

SELECT empid
FROM emp
GROUP BY manager =1001
HAVING empid

Query:-Count all records of employee from emp table where salary>=15000 if that employee having empid.

SELECT empid, COUNT( empid ) AS "Number of Employee"
FROM emp
WHERE salary >=20000
GROUP BY empid
HAVING COUNT( empid ) ;

Query:-Get Minimum Salary from emp table group by Manager And if that employee having empid.

SELECT empid, min( salary ) AS "Lowest Salary"
FROM emp
GROUP BY manager
HAVING empid;

Query:-Get Hieght Salary from emp table group by Manager And if that employee having empid.

SELECT empid, max( salary ) AS "Height Salary"
FROM emp
GROUP BY manager
HAVING empid;

MySQL: LIKE Condition with Wild Card Search and Using _ wildcard (underscore wildcard)

LIKE condition to perform pattern matching with syntax and examples.

Syntax

expression LIKE pattern [ ESCAPE 'escape_character' ]

Parameters or Arguments

expression is a character expression such as a column or field.

pattern is a character expression that contains pattern matching. The patterns that you can choose from are:

  • % allows you to match any string of any length (including zero length)
  • _ allows you to match on a single character

escape_character is optional. It allows you to test for literal instances of a wildcard character such as % or _. If you do not provide the escape_character, MySQL assumes that "\" is the escape_character.

Example - Using % wildcard (percent sign wildcard)

The first MySQL LIKE example that we will look at involves using the % wildcard (percent sign wildcard).

Query:- Get employee details from employee table whose first name starts with 'J'

SELECT *
FROM emp
WHERE firstName LIKE 'J%'

Query:-Get employee details from employee table whose first name contains 'o'

SELECT *
FROM emp
WHERE firstName LIKE '%o%'

Query:-Get employee details from employee table whose first name ends with 'e'

SELECT *
FROM emp
WHERE firstName LIKE '%e'

Query:-Get all employees details from employee table whose salary ends with ''_00%'.

SELECT *
FROM emp
WHERE salary LIKE '_00%'

Query:-Get all employees details Finds any values that start with 2 and are at least 3 characters in length

SELECT *
FROM emp
WHERE salary LIKE '2_%_%'

Admin has written 171 articles

Leave a Reply