Mastering MySQL 4 [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Mastering MySQL 4 [Electronic resources] - نسخه متنی

Ian Gilfillan

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید







Numeric Functions


Numeric functions deal with numbers, mostly taking numeric arguments and returning numeric results. In the case of an error, they will return NULL. You need to take care not to go beyond the numeric range of a number—most MySQL functions work with BIGINTs (263 signed, or 264 unsigned), and if you go beyond the range, MySQL will usually return NULL.


ABS


ABS(number)

Returns the absolute value (positive value) of a number. The function is safe for use with BIGINT values.

For example:

mysql> SELECT ABS(24-26);
+------------+
| ABS(24-26) |
+------------+
| 2 |
+------------+



ACOS


ACOS(number)

Returns the arc cosine of number (the inverse cosine). The number must be between –1 and 1 or the function returns NULL.

For example:

mysql> SELECT ACOS(0.9);
+-----------+
| ACOS(0.9) |
+-----------+
| 0.451027 |
+-----------+



ASIN


ASIN(number)

Returns the arc sine of number (the inverse sine). The number must be between –1 and 1 or the function returns NULL.

For example:

mysql> SELECT ASIN(-0.4);
+------------+
| ASIN(-0.4) |
+------------+
| -0.411517 |
+------------+



ATAN


ATAN(number1 [, number2])

Returns the arc tangent of number (the inverse tangent) or of two numbers (the point number1, number2).

For example:

mysql> SELECT ATAN(4);
+----------+
| ATAN(4) |
+----------+
| 1.325818 |
+----------+
mysql> SELECT ATAN(-4,-3);
+-------------+
| ATAN(-4,-3) |
+-------------+
| -2.214297 |
+-------------+



ATAN2


ATAN2(number1,number2)

A synonym for ATAN(number1,number2).



CEILING


CEILING(number)

Rounds up the number to the nearest integer and returns it as a BIGINT.

For example:

mysql> SELECT CEILING(2.98);
+---------------+
| CEILING(2.98) |
+---------------+
| 3 |
+---------------+
mysql> SELECT CEILING(-2.98);
+----------------+
| CEILING(-2.98) |
+----------------+
| -2 |
+----------------+

Use FLOOR() to round down, and use ROUND() to round up or down.



COS


COS(number_radians)

Returns the cosine of number_radians.

For example:

mysql> SELECT COS(51);
+----------+
| COS(51) |
+----------+
| 0.742154 |
+----------+



COT


COT(number_radians)

Returns the cotangent of number_radians.

For example:

mysql> SELECT COT(0.45);
+------------+
| COT(0.45) |
+------------+
| 2.07015736 |
+------------+



DEGREES


DEGREES(number)

Converts the number from radians to degrees and returns the result.

For example:

mysql> SELECT DEGREES(2);
+-----------------+
| DEGREES(2) |
+-----------------+
| 114.59155902616 |
+-----------------+
mysql> SELECT DEGREES(PI()/2);
+-----------------+
| DEGREES(PI()/2) |
+-----------------+
| 90 |
+-----------------+



EXP


EXP(number)

Returns the number e (the base of natural logarithms) raised to the specified power.

For example:

mysql> SELECT EXP(1);
+----------+
| EXP(1) |
+----------+
| 2.718282 |
+----------+
mysql> SELECT EXP(2.3);
+----------+
| EXP(2.3) |
+----------+
| 9.974182 |
+----------+
mysql> SELECT EXP(0.3);
+----------+
| EXP(0.3) |
+----------+
| 1.349859 |
+----------+



FLOOR


FLOOR(number)

Rounds the number down to the nearest integer and returns it as a BIGINT.

For example:

mysql> SELECT FLOOR(2.98);
+-------------+
| FLOOR(2.98) |
+-------------+
| 2 |
+-------------+
mysql> SELECT FLOOR(-2.98);
+--------------+
| FLOOR(-2.98) |
+--------------+
| -3 |
+--------------+

Use CEILING() to round up, and ROUND() to round up or down.



FORMAT


FORMAT(number,decimals)

Formats the number to a format with each three digits separated by a comma and rounds the result to the specified number of places.

For example:

mysql> SELECT FORMAT(88777634.232,2);
+------------------------+
| FORMAT(88777634.232,2) |
+------------------------+
| 88,777,634.23 |
+------------------------+



GREATEST


GREATEST(argument1, argument2 [, ...])

Returns the largest of the arguments. The arguments will be compared in different ways depending on the context of the return value or the argument types, which can be integer, real, or strings (which are case sensitive and the default).

For example:

mysql> SELECT GREATEST(-3,-4,5);
+-------------------+
| GREATEST(-3,-4,5) |
+-------------------+
| 5 |
+-------------------+
mysql> SELECT GREATEST('Pa','Ma','Ca');
+--------------------------+
| GREATEST('Pa','Ma','Ca') |
+--------------------------+
| Pa |
+--------------------------+



LEAST


LEAST(argument1, argument2 [, ...])

Returns the smallest of the arguments. The arguments will be compared in different ways depending on the context of the return value or the argument types, which can be integer, real, or strings (which are case sensitive and the default).

For example:

mysql> SELECT LEAST(-3,-4,5);
+----------------+
| LEAST(-3,-4,5) |
+----------------+
| -4 |
+----------------+
mysql> SELECT LEAST('Pa','Ma','Ca');
+-----------------------+
| LEAST('Pa','Ma','Ca') |
+-----------------------+
| Ca |
+-----------------------+



LN


LN(number)

Synonym for the LOG(number) function.



LOG


LOG(number1 [, number2])

Returns the natural logarithm of number1 if there's one argument. You can also use an arbitrary base by supplying a second argument, in which case the function returns LOG(number2) / LOG(number1).

For example:

mysql> SELECT LOG(2);
+----------+
| LOG(2) |
+----------+
| 0.693147 |
+----------+
mysql> SELECT LOG(2,3);
+----------+
| LOG(2,3) |
+----------+
| 1.584963 |
+----------+



LOG10


LOG10(number1)

Returns the base 10 logarithm of number1. This is equivalent to LOG(number1)/LOG(10).

For example:

mysql> SELECT LOG10(100);
+------------+
| LOG10(100) |
+------------+
| 2.000000 |
+------------+



LOG2


LOG2(number1)

Returns the base 2 logarithm of number1. This is equivalent to LOG(number1)/LOG(2).

For example:

mysql> SELECT LOG2(4);
+----------+
| LOG2(4) |
+----------+
| 2.000000 |
+----------+



MOD


MOD(number1,number2)

Returns the modulus of number1 and number2 (the remainder of number1 divided by number2). This is the same as the % operator. This is safe to use with BIGINTs.

For example:

mysql> SELECT MOD(15,4);
+-----------+
| MOD(15,4) |
+-----------+
| 3 |
+-----------+
mysql> SELECT MOD(3,-2);
+-----------+
| MOD(3,-2) |
+-----------+
| 1 |
+-----------+



PI


PI()

Returns the value of pi (or at least a close representation). MySQL uses the full double precision but only returns five characters by default.

For example:

mysql> SELECT PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
mysql> SELECT PI() + 0.0000000000000000;
+---------------------------+
| PI() + 0.0000000000000000 |
+---------------------------+
| 3.1415926535897931 |
+---------------------------+



POW


POW(number1,number2)

This function is a synonym for POWER(number1,number2).



POWER


POWER(number1,number2)

Raises number1 to the power of number2 and returns the value.

For example:

mysql> SELECT POWER(2,3);
+------------+
| POWER(2,3) |
+------------+
| 8.000000 |
+------------+



RADIANS


RADIANS(number1)

Converts the number from degrees to radians and returns the result.

For example:

mysql> SELECT RADIANS(180);
+-----------------+
| RADIANS(180) |
+-----------------+
| 3.1415926535898 |
+-----------------+



RAND


RAND([number])

Returns a random number (a float) between 0 and 1. The argument is the random number seed. It's common to use the timestamp as a seed. The function can be used to return a result set in random order.

For example:

mysql> SELECT RAND();
+------------------+
| RAND() |
+------------------+
| 0.70100469486881 |
+------------------+
mysql> SELECT RAND(20021010081523);
+----------------------+
| RAND(20021010081523) |
+----------------------+
| 0.80558716673924 |
+----------------------+
mysql> SELECT * FROM t1 ORDER BY RAND() LIMIT 1;
+----+
| f1 |
+----+
| 20 |
+----+



ROUND


ROUND(number1 [, number2])

Returns the argument number1, rounded to the nearest integer. You can supply a second argument to specify the number of decimals to round to (the default is 0, or no decimals). The rounding behavior for numbers exactly in the middle is based upon the underlying C library.

For example:

mysql> SELECT ROUND(2.49);
+-------------+
| ROUND(2.49) |
+-------------+
| 2 |
+-------------+
mysql> SELECT ROUND(2.51);
+-------------+
| ROUND(2.51) |
+-------------+
| 3 |
+-------------+
mysql> SELECT ROUND(-2.49,1);
+----------------+
| ROUND(-2.49,1) |
+----------------+
| -2.5 |
+----------------+



SIGN


SIGN(number)

Returns –1, 0, or 1 depending on whether the argument is negative, zero or not a number, or positive.

For example:

mysql> SELECT SIGN(-7);
+----------+
| SIGN(-7) |
+----------+
| -1 |
+----------+
mysql> SELECT SIGN('a');
+-----------+
| SIGN('a') |
+-----------+
| 0 |
+-----------+



SIN


SIN(number_radians)

Returns the sine of number_radians.

For example:

mysql> SELECT SIN(45);
+----------+
| SIN(45) |
+----------+
| 0.850904 |
+----------+



SQRT


SQRT(number)

Returns the square root of the argument.

For example:

mysql> SELECT SQRT(81);
+----------+
| SQRT(81) |
+----------+
| 9.000000 |
+----------+



TAN


TAN(number_radians)

Returns the tangent of number_radians.

For example:

mysql> SELECT TAN(66);
+----------+
| TAN(66) |
+----------+
| 0.026561 |
+----------+



TRUNCATE


TRUNCATE(number,decimals)

Truncates (or increases) the number to the specified number of decimal places.

For example:

mysql> SELECT TRUNCATE(2.234,2);
+-------------------+
| TRUNCATE(2.234,2) |
+-------------------+
| 2.23 |
+-------------------+
mysql> SELECT TRUNCATE(2.4,5);
+-----------------+
| TRUNCATE(2.4,5) |
+-----------------+
| 2.40000 |
+-----------------+
mysql> SELECT TRUNCATE(2.998,0);
+-------------------+
| TRUNCATE(2.998,0) |
+-------------------+
| 2 |
+-------------------+
mysql> SELECT TRUNCATE(-12.43,1);
+--------------------+
| TRUNCATE(-12.43,1) |
+--------------------+
| -12.4 |
+--------------------+



/ 229