Following are functions which is most command used in mysql
Least: Return Least value amont all
SELECT LEAST(4,3,8,-1,5);
Greatest: Return greatest value amont all
SELECT GREATEST(4,3,8,-1,5);
Interval(): takes a comparison value as its first argument. The remaining arguments should be a set of values in sorted order. INTERVAL() compares the first argument to the others and returns a value to indicate how many of them are less than or equal to it.
SELECT INTERVAL(2,1,2,3,4);
BETWEEN: The BETWEEN operator takes the two endpoint values of the range and returns true if a comparison value lies between them and The comparison is inclusive.
SELECT * FROM `products` WHERE id between 1 and 10
ISNULL(0): return true if value is NULL
select ISNULL(1)Group By: The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result. IN: This is a clause, which can be used along with any MySQL query to specify a condition.
BETWEEN: This is a clause, which can be used along with any MySQL query to specify a condition.
UNION: Use a UNION operation to combine multiple result sets into one.
COUNT: COUNT aggregate function is used to count the number of rows in a database table.
MAX: The MAX aggregate function allows us to select the highest (maximum) value for a certain column.
MIN: The MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
AVG: The AVG aggregate function selects the average value for certain table column.
SUM: The SUM aggregate function allows selecting the total for a numeric column.
SQRT: This is used to generate a square root of a given number.
RAND: This is used to generate a random number using MySQL command.
CONCAT: This is used to concatenate any string inside any MySQL command.
Switch Case in Mysql
CASE case_expression WHEN when_expression_1 THEN execute_statement1 WHEN when_expression_2 THEN execute_statement1 ELSE execute_statement END CASE;
Example 1
set @variable='10'; select (case when (@variable = '10') then "Equal to 10" else "Not Equal to 10" end)
Example 2
set @variable=10; select (case when (@variable <=10) then "Less Than Equal to 10" else "Greater Than 10" end)
Example 3
set @variable=10;
select (case
when (@variable <10) then "Less Than Equal to 10"
when (@variable =10) then "Equal to 10"
else "Greater Than 10" end)
