MySQL -Functions
MYSQL FUNCTIONS
A
function is a special type of predefined command set that performs some
operation and may return a single value. MySQL
supports functions that can be used to manipulate data. Such functions can
be used to manipulate data
Single-row functions return a single result row for every
row of a queried table. They are categorized into:
- Numeric functions
- String functions and
- Date and Time function
NUMERIC FUNCTIONS
• POWER() : Returns the
argument raised to the specified power. POW () works the same way.
Example:
(i) POW(2,4); Result:16
(ii) POW(2,-2); Result:0.25
(iii) POW(-2,3) Result: -8
• ROUND() : ROUND(X) Rounds the
argument to the zero decimal place, Where as ROUND(X,d) Rounds the argument to d
decimal places.
Example
:
(i) ROUND(-1.23); Result: -1
(ii) ROUND(-1.58); Result: -2
(iii) ROUND(1.58); Result: 2
(iv)
ROUND(3.798, 1); Result: 3.8
(v) ROUND(1.298, 0); Result: 1
(vi)
ROUND(23.298, -1); Result: 20
• TRUNCATE() : Truncates the
argument to specified number of decimal places.
Example:
(i) TRUNCATE(7.29,1) Result:
7.2
(ii) TRUNCATE(27.29,-1) Result: 20
SQRT() : Calculate the Square root of a number.
Example:
(i) SQRT(144) Result: 12
(ii)SQRT(-144) Result: Null
Note: SQRT of a negative number will always be
CHARACTER/STRING FUNCTIONS
• LENGTH() : Returns the length of a string in bytes/ no.of characters in string.
Example: LENGTH(‘INFORMATICS’); Result:11
• CHAR() : Returns the corresponding ASCII character for each integer passed.
Example : CHAR(65) ; Result : A
• CONCAT(): Returns concatenated string i.e. it adds strings.
Example : CONCAT(‘Informatics’,’ ‘,‘Practices’); Result : Informatics Practices’
• INSTR(): Returns the index of the first occurrence of substring.
Example : INSTR(‘Informatics’,’ mat’); Result : 6(since ‘m’ of ‘mat’ is at 6th place)
• LOWER()/ LCASE(): Returns the argument after converting it in lowercase.
Example: LOWER(‘INFORMATICS’); Result : informatics
• UPPER()/ UCASE(): Returns the argument after converting it in uppercase.
Example: UCASE(‘informatics’); Result : INFORMATICS
• LEFT() : Returns the given number of characters by extracting them from the left side of the given string.
Example : LEFT(‘INFORMATICS PRACTICES’, 3); Result : INF
• RIGHT():Returns the given number of characters by extracting them from the right side of the given string.
Example : RIGHT(‘INFORMATICS PRACTICES’,3); Result : CES
MID()/SUBSTR() : Returns a substring starting from the specified position in a given string.
Example: MID(‘INFORMATICS PRACTICES’,3,4); Result : FORM
• LTRIM() : Removes leading spaces.
Example : LTRIM(' INFORMATICS')’ Result: 'INFORMATICS’
• RTRIM(): Removes trailing spaces.
Example : RTRIM('INFORMATICS '); Result: 'INFORMATICS’
• TRIM() : Removes leading and trailing spaces.
Example:
TRIM(' INFORMATICS '); Result: 'INFORMATICS’
DATE/TIME FUNCTIONS
CURDATE() : Returns the current date
Example: CURDATE(); Result: '2010-07-21'
NOW() : Returns the current date and time
Example: NOW(); Result : '2010-07-21 13:58:11'
SYSDATE() : Return the time at which the function executes
Example: SYSDATE(); Result: '2010-07-21 13:59:23’
DATE() : Extracts the date part of a date or datetime expression
Example: DATE('2003-12-31 01:02:03'); Result:: '2003-12-31'
MONTH() Returns the month from the date passed
Example: MONTH('2010-07-21'); Result : 7
YEAR() : Returns the year YEAR('2010-07-21'); Result : 2010
DAYNAME() : Returns the name of the weekday
Example: DAYNAME('2010-07-21'); Result : WEDNESDAY
DAYOFMONTH() : Returns the day of the month (0-31)
Example: DAYOFMONTH('2010-07-21'); Result: 21
DAYOFWEEK() : Returns the weekday index of the argument
Example: DAYOFWEEK('2010-07-21'); Result: 4 (Sunday is counted as 1)
DAYOFYEAR() : Return the day of the year(1-366)
Example: DAYOFYEAR('2010-07-21'); Result: 202
AGGREGATE FUNCTIONS or GROUP FUNCTIONS
When multiple records from a table are
to be combined together to perform a calculation, aggregate functions are used.
Aggregate functions are placed with the select clause and the column on
which the calculation is to be performed is specified in parenthesis. There are
five aggregate functions:
COUNT(), SUM(), AVG(), MAX(), & MIN()
In these function with the name of
columns we can also specify DISTINCT or ALL. If we use DISTINCT keyword with
the column name, only the distinct values will be considered while performing
the operations. If ALL keyword is used all values(distinct and duplicate) will
be considered while calculating. ALL keyword is default.
COUNT(<Column name>) – Returns the number of rows for which
specified column value is not null.
SELECT Count([DISTINCT | ALL ]
Column_name) From <tablename> [where<condition>]
The most common usage for this
function is to count the number of records, for this we use an Asterisk ( * )
in place of column name.
SELECT Count(*) FROM <tablename>
SUM() - The SUM aggregate function
calculates the total of values in a column. The column using sum must be of
numeric data type.
SELECT SUM([DISTINCT | ALL ]
Column_name) From <tablename> [where<condition>]
AVG()- The AVG( ) function calculates the
average or arithmetic mean of the values in a numeric column.
SELECT AVG([DISTINCT | ALL ]
Column_name) From <tablename> [where<condition>]
MAX()- The MAX( ) returns the maximum value
of the column data.
SELECT MAX([DISTINCT | ALL ]
Column_name) From <tablename> [where<condition>]
MIN() - The MIN( ) returns the minimum value
of the column data
SELECT MIN([DISTINCT | ALL ]
Column_name) From <tablename> [where<condition>]
NOTE: While performing
aggregate operations no other columns can be selected except the column on
which the operation is being performed.
GROUPING OF DATA
When we use any aggregate function on
a table, it returns a single row and column containing the result of used
aggregate function. Many a times we need to calculate some values in groups
based on some columns i.e. we want to create groups based on some column values
and then to perform aggregate operations separately on each group. E.g. in a
table Student (SRNo, Name, Class, FeesPaid) if we need to find out
the total of fees paid by each class, we need to create groups on columns class
i.e. all records for a particular class will be kept in a single group and then
the total of this group will be find out. As many groups will be used here as
many class values are there in table. So the result of this type of select
statement will give us as many rows in result as many class values are there in
the table. Simply we will find out the class wise total of FeesPaid column.
How to do This : To
perform any aggregate operation in groups, we use GROUP BY clause with the
Select statement
SELECT function_name([DISTINCT | ALL ]
Column_name) From <tablename> [where<condition>]
Group By <name of columns>
NOTE:
1. When using GROUP BY clause we can include
the group column with the select
statement.
2. GROUP BY clause can only be used if
any aggregate function is being used in
select statement.
Example: (solution to the problem
discussed above, to find out class wise total of FEESPAID)
SELECT SUM(FEESPAID), CLASS FROM STUDENTS GROUP BY CLASS;
FILTERING
ON AGGREGATED DATA
In some cases we may need to filter
the data obtained after grouped aggregate operations. This is done using HAVING
clause with select statement. HAVING clause is used to filter the data
generated after grouped aggregated operations.
SELECT function_name([DISTINCT | ALL ]
Column_name) From <tablename>
[where<condition>] Group By
<name of columns> [HAVING <condition using aggregated column>]
NOTE:
HAVING clause can only be used if a GROUP BY clause is used.
Example: (if we want to find out the
classes for which total fees collection is more than Rs. 10000.00 )
SELECT SUM(FEESPAID),
CLASS FROM STUDENTS Group By Class Having SUM(FEESPAID)>10000;
Comments
Post a Comment