MySql Functions

Contents:

• Aggregation Function
  • usage with GROUP BY clause
  • usage with ORDER BY clause
  • usage with HAVING clause
• Mathematical/Numeric Functions
• Text Function
• Data Function

Aggregation Function:

SQL provides a large collection of in-built functions, also called library functions, that can be used directly with SQL statements for performing calculations on data. These functions are the Aggregate functions.

Aggregate functions help to summarize large volumes of data. These functions result in a single value for an entire group or table.

SYNTAX:

SELECT [function](column_name) FROM [table_name];

While working with standard library functions, the following points must be kept in mind:

• The name of the column on which the function is to be executed must be enclosed within parentheses.
• Only one column can be specified within one set of parentheses.
• To use more than one function with the same select statement, functions are written one after the other separated by a comma.
• If the same function is required to be used for more than one column, then again the function name has to be repeated in the select statement.
• Aggregate function are often used GROUP BY and HAVING clause of the select statement.
Some of the aggregate function are as following:

1. MAX():This function returns the maximum value in selected column passed as an attribute in the max function.MAX fucntion ignores NULL values and considers all the values in the column.
   
   
   SYNTAX:
   SELECT MAX(column_name) FROM table_name;
   SELECT MAX(column_name) FROM table_name WHERE condition;
   


2. MIN():This fucntion returns the minimum values in the selected columns.MIN() function ignores NULL values
   
   SYNTAX:
   SELECT MIN(column_name) FROM table_name;
   SELECT MIN(column_name) FROM table_name WHERE condition;
   


3. AVG():This function calculates the average of a column(s).It ignores NULL values
   
   SYNTAX:
   SELECT AVG(column_name) FROM table_name;


4. SUM():This function calaculates the sum of all the values in the column passed as an agrument in the function.
   SYNTAX:
   SELECT SUM(column_name) FROM table_name;


5. COUNT()This function counts the number of items found in a result set. count(*) returns the number of rows ina table including duplicate and NULL.
   SYNTAX:
   SELECT COUNT(*) FROM table_name;
   SELECT COUNT(column_name) FROM table_name;

• GROUP BY clause:This clause is used to group the rows returned by the query based on a given column.
  SYNTAX:
  SELECT column1,column2...Aggregate_Function(column_name) FROM table_name WHERE condition GROUP BY column_name;


• ORDER BY clause:This clause is used to sort the result set returned by the select query. By default it sorts in the ascending order but we it can be changed into descending order by use of DESC keyword
  SYNTAX:
  SELECT column1, column2..... FROM table_name ORDER BY column_name [ASC/DESC];


• HAVING Clause:This clause is used with GROUP BY caluse in the select statement to filter group of rows based on a specified condition.The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
  SYNTAX:
  SELECT column1, column2,....Aggregate_function(column_name) FROM table_name GROUP BY column_name HAVING condition;

Mathematical/Numeric Functions

Mathematical functions accept numeric value, operates on them and return numeric value as a result. Some of the in-built mathematical function in mysql are:

• POW() / POWER(): Returns the argument raised to the specified power. pow() or power() works the same way. Its syntax is: pow(m,n) Here, m is the number and n is the power to be raised to number ‘m’
  
  Example: SELECT pow(2,3);
  SELECT power(2,3);



• ROUND(): round(x) rounds the argument to the 0 decimal place, whereas round(x, d) rounds the argument to d decimal places. The round() function returns a number rounded to a certain number of decimal places.
  Syntax: round(column_name, decimals)
  column_name – Required (It is the field to be rounded off.)
  decimals – Optional (Specifies the number of decimals to be returned.)
  
  Example: SELECT round(25.6398);
  SELECT round(25.6398,2);




• MOD():This function is used to find the remainder of a division operation.
  Syntax: MOD(n,m)
  
  Example: SELECT (5,2);
  

Text Function:

MySql text function manipulate the character string data

• UCASE()/UPPER():

  This function is used to convert the string argument into Uppercase charaters.

  
  SYNTAX:
  UCASE(str)
  OR
  UPPER(str)


• LCASE()/LOWER():

  This function is used to convert the string argument into Lowercase charaters.

  
  SYNTAX:
  LCASE(str)
  OR
  LOWER(str)


• MID():

  This function extracts a substring from a string and returns a string with given length and position.

  
  SYNTAX:
  LCASE(str)
  OR
  LOWER(str)


• SUBSTRING()/SUBSTR():This fucntion works similar to MID function.
  
  SYNTAX:
  SUBSTRING(str,pos.len)


• LENGTH():this function is used to count the numbers of character in the specified string.It returns the length in bytes. This function also count the blank spaces in the string.
  
  SYNTAX:
  LENGTH(str)


• LEFT():This fucntion is used to return number of character from the left end of the string as specified in the function by the second argument.
  
  SYNTAX:
  LEFT(str, len)


• RIGHT():This fucntion is used to return number of character from the right end of the string as specified in the function by the second argument.
  
  SYNTAX:
  RIGHT(str,len)


• INSTR():This function takes two argumnets as str(string) and sub_string(string) and returns the position of the first occurance of a specified sub_str from the given str.
  
  SYNTAX:
  INSTR(str, sub_str)


• LTRIM():This function takes a string and retyrns the same string after removing the spaces from the left end of the string.
  
  SYNTAX:
  LTRIM(str)


• RTRIM():This function takes a string and retyrns the same string after removing the spaces from the left end of the string.
  
  SYNTAX:
  RTRIM(str)


• TRIM():This function enables you to remove leading and trailing space from the string.
  
  SYNTAX:
  TRIM(str)

DATE FUNCTION:

The date function are used to perform some operation on date that is stored in the database. Some of them are listed below:

• NOW():This function returns the current date and time in the configured time zone as a string or a number in the 'YYYY-MM-DD HH:MM:SS' or 'YYYYMMDDHHMMSS' format.
  
  SYNTAX:
  SELECT NOW();


• DATE():This function ectracts the date value from a date.
  
  SYNTAX:
  SELECT DATE(date_value);
  SELECT DATE(NOW());


• MONTH():This function returns the month for date, in the range 1 to 12 for each 12 months.
  
  SYNTAX:
  SELECT MONTH(date);
  SELECT MONTH(NOW());


• MONTHNAME():This function returns the full name of the month for given date;
  
  SYNTAX:
  SELECT MONTHNAME(date);
  SELECT MONTHNAME(NOW());


• YEAR()This function returns the year of the given date. It returns a year value in the range of 1000 to 9999.
  
  SYNTAX:
  SELECT YEAR(date);
  SELECT YEAR(NOW());


• DAY():this function return the day of the month of a given date. the value returned in the range of 1 to 31.
  
  SYNTAX:
  SELECT DAY(date);
  SELECT DAY(NOW());


• DAYNAME():This function return the Day name from the given date. the possible value are Monday, Tuesday...Sunday.
  
  SYNTAX:
  SELECT DAYNAME(day);
  SELECT DAYNAME(NOW());

MySql Query Exercise - INBUILT FUNCTION

In [ ]:

Read More