MySQL Functions

JavaScript Editor Js editor     Website development 



Main Page

Previous Page
Next Page

MySQL Functions

Preformatting the results returned by a query makes your data more usable and can cut down on the amount of programming interface required. To do so, you make use of MySQL's built-in functions, first introduced in Chapter 6, "MySQL Functions." Of course, you can also use functions in your clauses to limit what records are returned, to group them, and more.

Table B.10 shows the functions used with strings. Table B.11 has most, but not all, of the number-based functions. Table B.12 lists date-related functions. Table B.13 has the formatting parameters for the DATE_FORMAT() and TIME_FORMAT() functions. Table B.14 covers the aggregate or grouping functions. Table B.15 discusses most of the encryption functions and Table B.16 is the catchall for miscellaneous functions.

Table B.10. These functions are used for manipulating string values, in columns or otherwise.

Text Functions

Function and Usage

Purpose

FIND_IN_SET(string, set)

Returns a positive number if string is found in set; returns 0 otherwise.

LEFT(string, x)

Returns the leftmost x characters from a string.

LENGTH(string)

Returns the length of the string.

LOCATE(substring, string)

Returns the first instance of substring in string, if applicable.

LOWER(string)

Turns the string into an all-lowercase format.

LTRIM(string)

Trims excess spaces from the beginning of the string.

REPLACE(string, find, replace)

Returns the string with every instance of find substituted by replace.

RIGHT(string, x)

Returns the rightmost x characters from a string.

RTRIM(string)

Trims excess spaces from the end of the stored string.

STRCMP(string1, string2)

Returns 0 if the strings are the same, 1 or 1 otherwise.

SUBSTRING(string, start, length)

Returns length characters from string beginning with start (indexed from 1).

trIM(string)

Trims excess spaces from the beginning and end of the string.

UPPER(string)

Capitalizes the entire string.


Table B.11. Here are some of the numeric functions MySQL has, excluding the trigonometric and more esoteric ones.

Numeric Functions

Function and Usage

Purpose

ABS(num)

Returns the absolute value of num.

CEILING(num)

Returns the next-highest integer based upon the value of num.

FLOOR(num)

Returns the integer value of num.

FORMAT(num, y)

Returns num formatted as a number with y decimal places and commas inserted every three spaces.

GREATEST(num1, num2, num3...)

Returns the greatest value from a list.

LEAST(num1, num2, num3...)

Returns the smallest value from a list.

MOD(x, y)

Returns the remainder of dividing x by y (either or both can be a column).

POW(x, y)

Returns the value of x to the y power.

RAND()

Returns a random number between 0 and 1.0.

ROUND(x, y)

Returns the number x rounded to y decimal places.

SIGN(num)

Returns a value indicating whether num is negative (1), zero (0), or positive (+1).

SQRT(num)

Calculates the square root of num.


Table B.12. MySQL uses several different functions for working with dates and times in your databases. In the usage examples, dt could represent a date, a time, or a datetime.

Date and Time Functions

Function and Usage

Purpose

HOUR(dt)

Returns just the hour value of dt.

MINUTE(dt)

Returns just the minute value of dt.

SECOND(dt)

Returns just the second value of dt.

DATE(dt)

Returns just the date value of dt.

DAYNAME(dt)

Returns the name of the day of dt.

DAYOFMONTH(dt)

Returns just the numerical day of dt.

MONTHNAME(dt)

Returns the name of the month of dt.

MONTH(dt)

Returns just the numerical month value of dt.

YEAR(dt)

Returns just the year value of dt.

DATE_ADD(dt, INTERVAL x type)

Returns the value of x units added to dt.

DATE_SUB(dt, INTERVAL x type)

Returns the value of x units subtracted from dt.

ADDTIME(dt, t)

Returns the value of t time added to dt.

SUBTIME(dt, t)

Returns the value of t time subtracted from dt.

DATEDIFF(dt, dt)

Returns the number of days between the two dates.

TIMEDIFF(dt, dt)

Returns the time difference between the dates or dates and times.

CONVERT_TZ(dt, from_zone, to_zone)

Converts dt from one time zone to another.

CURDATE()

Returns the current date.

CURTIME()

Returns the current time.

NOW()

Returns the current date and time.

UNIX_TIMESTAMP(dt)

Returns the number of seconds since the epoch or since the date specified.


Table B.13. The DATE_FORMAT() and TIME_FORMAT() functions make use of these special characters to format a date or time.

DATE_FORMAT() and TIME_FORMAT() Parameters

Term

Meaning

Example

%e

Day of the month

131

%d

Day of the month, two digit

0131

%D

Day with suffix

1st31st

%W

Weekday name

SundaySaturday

%a

Abbreviated weekday name

SunSat

%c

Month number

112

%m

Month number, two digit

0112

%M

Month name

JanuaryDecember

%b

Month name, abbreviated

JanDec

%Y

Year

2002

%y

Year

02

%l

Hour

112

%h

Hour, two-digit

0112

%k

Hour, 24-hour clock

023

%H

Hour, 24-hour clock, two-digit

0023

%i

Minutes

0059

%S

Seconds

0059

%r

Time

8:17:02 PM

%T

Time, 24-hour clock

20:17:02

%p

AM or PM

AM or PM


Table B.14. The grouping functions are frequently tied to a GROUP BY clause to aggregate values in a column.

Grouping Functions

Function and Usage

Purpose

AVG(column)

Returns the average of the values of the column.

COUNT(column)

Counts the number of rows.

COUNT(DISTINCT column)

Counts the number of distinct column values.

GROUP_CONCAT(values)

Returns a concatenation of the grouped values.

MIN(column)

Returns the smallest value from the column.

MAX(column)

Returns the largest value from the column.

SUM(column)

Returns the sum of all of the values in the column.


Table B.15. Different encryption functions are available as of new releases of MySQL, so know what version you are using!

Encryption Functions

Function

Version

Notes

MD5()

3.23.2

Returns a 32-digit hash.

SHA1()

4.0.2

Returns a 40-digit hash.

AES_ENCRYPT()

4.0.2

Encrypts data using AES algorithm.

AES_DECRYPT ()

4.0.2

Decrypts AES_ENCRYPT() data.

ENCODE()

3.x

Older encryption function.

DECODE()

3.x

Decrypts ENCODE() data.

DES_ENCRYPT()

4.0.1

Encrypts data using DES algorithm, requires SSL.

DES_DECRYPT ()

4.0.1

Decrypts DES_ENCRYPT() data.

ENCRYPT()

3.x

May not be available on Windows; no decryption possible.

PASSWORD()

3.x

Used by the mysql database; do not use yourself.


Table B.16. These various functions are for everything from encryption to concatenation.

Other Functions

Function and Usage

Purpose

CONAT(column1, ' - ', column2)

Combines the elements in parentheses into one string.

CONCAT_WS(' - ', column1, column2)

Combines the elements with the one common separator.

DATABASE()

Returns the name of the database currently being used.

LAST_INSERT_ID()

Returns the previous auto-incremented value.

USER()

Returns the name of the user of the current session.


Most every function can be applied either to the value retrieved from a column or to a literal one:

SELECT ROUND (3.142857, 2)
SELECT ROUND (columnname) FROM tablename


Previous Page
Next Page


JavaScript Editor Js editor     Website development


©