MySQL – Introduction to Functions and Operators

MySQL – Functions and Operators – Notes for Class discussion

Note: These are functions which have been selected for class discussion … There are many more functions!  For further reference, see:

LENGTH(field) returns the length of the string
LEFT(field, x) returns the leftmost x characters of a string
RIGHT(field,x) returns the rightmost x characters of a string
TRIM(field) removes both leading and trailing blanks from a string
UPPER(field) capitalizes a string
LOWER(field) puts a string into lower-case
SUBSTRING(field,x,y) returns y characters from the string starting at position x
CONCAT(x,y,z …) concatenates strings and characters


Note: use + – * / and ( ) for standard arithmetic operations
FORMAT(field,x) returns the field formatted with x decimal places and commas
MOD(x,y) returns the remainder of divinding x by y (like the “%” in Java)
RAND() generates a random number between zero and one
ROUND(x,y) rounds x to y decimal places
SQRT returns the square root of the field


Date & Time
CURDATE() returns the current date
YEAR(date field) returns the year value of a date
MONTH(date field) returns the month value of a date
MONTHNAME(date field) returns the name of the month of a date
DAYOFMONTH(date field) returns the value of the day
DAYNAME(date field) returns the name of the day for a date value
NOW() returns the current date and time
CURTIME() returns the current time
HOUR(date field) returns the hour value
MINUTE(date field) returns the minute value


Functions that group data for aggregate calculations
MIN(field) returns the minimum value
MAX(field) returns the highest value
SUM(field) returns the total value
COUNT(field) returns the number of rows; COUNT(DISTINCT field) returns the number of unique rows
AVG(field) returns the average (mean)


AND , OR boolean operators
BETWEEN for a range: e.g. WHERE start_date BETWEEN ’2006-01-01′ AND ’2007-10-01′
IN to select among a limited number of options: e.g. WHERE product_cd IN (‘CHK’,'SAV’,'CD’,'MM’)
LIKE to find similar strings e.g. WHERE author LIKE ‘W%’


Other functions:
LAST_INSERT_ID() returns that user’s most recent AUTO_INCREMENT result