MYSQL 教程:§8, 在SELECT中使用内置函数

Table 8.1. Comparison Operators

Operator

Meaning

=

Equality

!= or <>

Inequality

< 

Less than

<=

Less than or equal to

> 

Greater than

>=

Greater than or equal to

n BETWEEN min AND max

Range testing

n IN (set)

Set membership. Can be used with a list of literal values or expressions or with a subquery as the set. An example of a set is (apple, orange, pear)

<=>

NULL safe equal. This will return 1 (true) if we compare two NULL values

n IS NULL

Use to test for a NULL value in n

ISNULL(n)

Use to test for a NULL value in n

Table 8.2. Logical Operators

Operator

Example

Meaning

AND or &&

n && m

Logical AND. Here is the truth table:

true&&true = true

false&&anything = false

All other expressions evaluate to NULL.

OR or ||

n || m

Logical OR. Here is the truth table:

true||anything = true

NULL||false = NULL

NULL||NULL = NULL

false||false = false

NOT or !

NOT n

Logical NOT. Here is the truth table:

!true = false

!false = true

!NULL = NULL

XOR

n XOR m

Logical exclusive OR. Here is the truth table:

true XOR true = false

true XOR false = true

false XOR true = true

NULL XOR n = NULL

n XOR NULL = NULL

Table 8.3. String Processing Functions

Function

Purpose

concat(s1, s2, ...)

Concatenate the strings in s1, s2, ....

conv (n, original_base, new_base)

Convert the number n from original_base to new_base. (It may surprise you to see this as a string function, but some bases use letters in their notations, such as hexadecimal.)

length(s)

Returns the length in characters of the string s.

load_file(filename)

Returns the contents of the file stored at filename as a string.

locate(needle, haystack, position)

Returns the starting position of the needle string in the haystack string. The search will start from position.

lower(s) and upper(s)

Convert the string s to lowercase or uppercase.

quote(s)

Escapes a string s so that it is suitable for insertion into the database. This involves putting the string between single quotes and inserting a backslash.

replace(target, find, replace)

Returns a string based on target with all incidences of find replaced with replace.

soundex(s)

Returns a soundex string corresponding to s. A soundex string represents how the string sounds when pronounced. It can be easier to match soundex strings of names than names themselves, for example.

substring (s, position, length)

Returns length characters from s starting at position.

trim(s)

Removes leading and trailing whitespace from s. (You can also use ltrim() to just remove whitespace from the left or rtrim() for the right.)

Table 8.4. Numeric Functions

Function

Purpose

abs(n)

Returns the absolute value of n—that is, the value without a sign in front of it.

ceiling(n)

Returns the value of n rounded up to the nearest integer.

floor(n)

Returns the value of n rounded down to the nearest integer.

mod(n,m) and div

These two functions divide n by m. div returns the integral quotient, and mod() returns the integral remainder.

power(n,m)

Returns n to the power of m.

rand(n)

Returns a random number between 0 and 1. The parameter n is optional, but if supplied, it is used as a seed for the pseudorandom number generation. (Giving the same n to rand will produce the same pseudorandom number.)

round(n[,d])

Returns n rounded to the nearest integer. If you supply d, n will be rounded to d decimal places.

sqrt(n)

Returns the square root of n.

Table 8.5. Date and Time Functions

Function

Purpose

adddate(date, INTERVAL n type) and subdate(date, INTERVAL n type)

These functions are used to add and subtract dates. Both start from the date supplied in date and add or subtract the period specified after the keyword INTERVAL. You need to specify both a quantity n and the type of that quantity.

The type can be SECOND, MINUTE, HOUR, DAY, MONTH, YEAR, MINUTE:SECOND (the format of n should be 'm:s'), HOUR:MINUTE ('h:m'), DAY_HOUR ('d h'), YEAR_MONTH ('y-m'), HOUR_SECOND ('h:m:s'), DAY_MINUTE ('d h:m'), DAY_SECOND ('d h:m:s').

These functions are really useful, but remembering the data formats is virtually impossible (because they are all different), so you will usually have to look them up.

curdate(), curtime(), now()

These return the current date, the current time, and the current date and time, respectively.

date_format(date, format) and time_format(time, format)

These are used to reformat dates and times to pretty much any format you like. You do this by supplying a format string, such as date_format(workdate, '%W %D of %M, %Y'). (This gives, for example, 'Monday 16th of June, 2003'). There is a massive list of formats, so consult the manual for details.

dayname(date)

This returns the name of the day in date (for example, 'Monday').

extract(type FROM date)

This returns the value of type in date. For example, if you specify YEAR, it will return the year from date. The types are the same as in adddate() and subdate().

unix_timestamp([date])

This returns the current Unix timestamp. (That's the number of seconds since the first of January 1970.) If called with a date, this returns the timestamp corresponding to that date.

Table 8.6. Miscellaneous Functions

Function

Purpose

benchmark(count, expression)

Evaluates expression count times. Always returns zero—the point of this function is to time execution and look at the execution time at the bottom of the result set.

encrypt(s[,salt])

Encrypts s using a Unix crypt system call. The salt string is an optional two-character string. If crypt is not available on your system (for example, Windows), this function will return NULL.

found_rows()

Returns the number of rows that would have been returned by the last query if no limit clause was used. Works only if SQL_CALC_FOUND_ROWS was specified in the SELECT statement, as discussed in Chapter 7.

last_insert_id()

Returns the last automatically generated AUTO_INCREMENT value. This is useful if we have inserted a row into one table and now need that row's id to insert as a foreign key into another table.

md5(s)

Returns the 128-bit MD5 hash of string s. If you are writing an application to store usernames and passwords, this is the recommended method for storing passwords in your database.

Encryption algorithms have a limited useful lifetime. As the power of computers increases, stronger algorithms are required. MD5 is currently regarded as fairly secure.

password(s)

Calculates a password string for the string s. This is the scheme that is used to represent MySQL user passwords, as we will discuss in Chapter 11, "Managing User Privileges." It is not recommended that you use password() to store passwords in your own applications.

Table 8.7. Grouping Functions

Function

Purpose

avg(column)

Returns the average value in column.

count(column)

Returns the number of values in column.

min(column)

Returns the smallest value in column.

max(column)

Returns the largest value in column.

std(column)

Returns the standard deviation of the values in column.

sum(column)

Returns the sum of values in column.