Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you what are the INFORMIX database functions. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

I. Internal function

1. Internal aggregate function

1) COUNT (*) returns the number of rows

2) COUNT (DISTINCT COLNAME) returns the number of unique values in the specified column

3) SUM (COLNAME/EXPRESSION) returns the numeric sum of the specified column or expression

4) SUM (DISTINCT COLNAME) returns the sum of unique values in the specified column

5) AVG (COLNAME/EXPRESSION) returns the average value in the specified column or expression

6) AVG (DISTINCT COLNAME) returns the average of the unique values in the specified column

7) MIN (COLNAME/EXPRESSION) returns the minimum numeric value in the specified column or expression

8) MAX (COLNAME/EXPRESSION) returns the maximum value in the specified column or expression

2. Date and time function

1) DAY (DATE/DATETIME EXPRESSION) returns the date of the month in the specified expression

2) MONTH (DATE/DATETIME EXPRESSION) returns the month in the specified expression

3) YEAR (DATE/DATETIME EXPRESSION) returns the year in the specified expression

4) WEEKDAY (DATE/DATETIME EXPRESSION) returns the day of the week in the specified expression

5) DATE (NOT DATE EXPRESSION) returns the date value represented by the specified expression

6) TODAY returns the date value of the current date

7) CURRENT [FIRST TO LAST] returns the date-time value of the current date

8) COLNAME/EXPRESSION UNITS PRECISION returns the specified number of units of specified precision

9) MDY (MONTH,DAY,YEAR) returns the date value that identifies the specified year, month and day

10) DATETIME (DATE/DATETIME EXPRESSION) FIRST TO LAST returns the date and time value represented by the expression

11) INTERVAL (DATE/DATETIME EXPRESSION) FIRST TO LAST returns the interval value represented by the expression

12) EXTEND (DATE/DATETIME EXPRESSION, [FIRST TO LAST]) returns the adjusted date or date-time value

The To_char function converts datetime and date values to character values.

The To_date function converts character values to values of type datetime. Example to_date ("1978-10-07 10:00", "% Y-%m-%d% HRV% M)"

Example 1. Used with UNITS to specify a date or time unit (year,month,day,hour,minute,seond,fraction):

Let tmp_date = today + 3 UNITS day

Example 2, let tmp_date = MDY (10pr 30J 2002)-- 2002-10-30

Example 3, let tmp_date = today + interval (7) day today-current time plus 7 days

Note: this function is similar to 1

Example 4. EXTEND converts date or date-time values

Let tmp_inthour = extend (datetime1,hour to hour)

3. Algebraic function

1) ABS (COLNAME/EXPRESSION): take an absolute value

2) MOD (COLNAME/EXPRESSION,DIVISOR) returns the module divided by the divisor (remainder)

3) POW (COLNAME/EXPRESSION,EXPONENT) returns the exponent of a value

Example: let tmp_float = pow (2, 3)-- 8.00000000

4) ROOT (COLNAME/EXPRESSION, [INDEX]) returns the root value of the specified column or expression

5) SQRT (COLNAME/EXPRESSION) returns the square root of the specified column or expression

6) ROUND (COLNAME/EXPRESSION, [FACTOR]) returns the rounded value of the specified column or expression

7) TRUNC (COLNAME/EXPRESSION, [FACTOR]) returns the truncated value of the specified column or expression

Note: of the above two, FACTOR specifies the number of decimal places, if not, it is 0; if it is negative, it is integrated to the left of the decimal point

Note: ROUND is 4 rounding and 5 rounds on finger positioning; TRUNC is directly truncated on finger positioning.

Let tmp_float = round (4.555 Magi 2)-4.56

Let tmp_float = trunc (4.555 Pol 2)-4.55

4. Exponential and logarithmic functions

1) EXP (COLNAME/EXPRESSION) returns the index value of the specified column or expression

2) LOGN (COLNAME/EXPRESSION) returns the natural logarithm of the specified column or expression

3) LOG10 (COLNAME/EXPRESSION) returns the logarithm of the bottom digit 10 of the specified column or expression

5. Trigonometric function

1) COS (RADIAN EXPRESSION) returns the cosine of the specified Radian expression

2) SIN (RADIAN EXPRESSION) sine

3) TAN (RADIAN EXPRESSION) tangent

4) ACOS (RADIAN EXPRESSION) inverse cosine

5) ASIN (RADIAN EXPRESSION) arcsine

6) ATAN (RADIAN EXPRESSION) inverse tangent

7) Polar coordinate Angle component of ATAN2 (XQuery Y) returns coordinates (XMagi Y)

6. Statistical function

1) RANGE (COLNAME) returns the difference between the maximum and minimum values of the specified column = MAX (COLNAME)-MIN (COLNAME)

2) VARIANCE (COLNAME) returns the sample variance of the specified column

3) STDEV (COLNAME) returns the standard deviation of the specified column

7. Other functions

1) USER returns the current user name

2) HEX (COLNAME/EXPRESSION) returns the hexadecimal value of the specified column or expression

3) LENGTH (COLNAME/EXPRESSION) returns the length of the specified character column or expression

4) TRIM (COLNAME/EXPRESSION) deletes the characters before and after the specified column or expression

5) COLNAME/EXPRESSION | | characters returned together by COLNAME/EXPRESSION

8. Cardinal function:

1) the cardinality (IDS only) function counts the number of elements contained in the collection.

Intelligent large object function, (applies to IDS only)

Filetoblob (), copy the file to the BLOB column

Filetoclob (), copy the file to the CLOB column

Locopy (), copying data of type BLOB or CLOB to another BLOB or CLOB column

Lotofile (), copy BLOB or CLOB to a file

9. String handling function:

Lower, which converts each uppercase letter in a string to lowercase

Upper, which converts each lowercase letter in a string to an uppercase letter

Initcap, which converts the first letter of each word in a string to uppercase

Replace, which converts a set of characters in a string into other characters, such as replace (col, "each", "eve")

Substr, which returns a portion of a string, such as substr (col,1,2)

Substring, which returns a portion of a string, such as substring (col,from 1 to 4)

Lpad, a copy of a string that has been populated or truncated on the left with a sequence of characters repeated up to the necessary number of times using the lpad function, depending on the specified length of the padding portion of the string.

For example: the field col is of type char (15), and the select lpad (col,21, "_") from tab_name is shown as adding six _ before the col.

Rpad, a copy of a string that has been populated or truncated on the right with a sequence of characters repeated up to the necessary number of times using the rpad function, depending on the specified length of the padding portion of the string.

For example: the field col is of type char (15), and the select rpad (col,21, "_") from tab_name is shown as adding six _ after the col.

10. Other functions:

Hex, which returns the hexadecimal number of the expression

Round, which returns the rounded value of the expression

Trunc, which returns the truncated value of the expression

Length, calculating the length of the expression

User, which returns the user name (login account name) of the user who executed the query

Today, which returns the current system date

Dbservername, which returns the name of the database server, same as sitename

Dbinfo, which returns information about the database

Decode, a function to convert an expression with one value to another

Decode (test,a,a_value,b,b_value,c,c_value...) The decode function does not support TEXT and BYTE types.

Nvl to convert an expression that evaluates to an empty value into another value you want to specify.

You can also use stored procedures in select statements, such as select spl ($test) from tab_name

2. IDS internal function

1. DBSERVERNAME returns the database server name let tmp_char=DBSERVERNAME

2. SITENAME returns the database server name let tmp_char=SITENAME

Description: both have the same function

3. DBINFO ('SPECIAL_KEYWORD') returns only key values

Example 1: returns the DBSPACE name of each table in the data

Select dbinfo ('dbspace',partnum), tabname from systables

Where tabid > 99 and tabtype='T' (OK)

Example 2: returns the last serial value inserted in any table

Select dbinfo ('sqlca.sqlerrd1') from systables where tabid = 1

Example 3: returns the number of rows processed by the last SELECT,INSERT,UPDATE,DELETE or EXECUTE PROCEDURE statement

Select dbinfo ('sqlca.sqlerrd2') from systables where tabid=1

III. Other

There are 38 functions in this letter.

Currently, the supported Informix versions are ODS 7.x, SE 7.x and Universal Server (IUS) 9.x. In ODS 7.x, all data types are fully supported, while in IUS 9.x, SLOB and CLOB are not supported.

To install Informix Client on Web Server, IFX_LIBDIR, IFX_LIBS and IFX_INCDIR should be set before editing PHP (before configure), and HAVE_IFX_IUS should be configured for version 9.x. At the same time, the option of-- with_informix=yes should be added in the configuration.

Ifx_connect: open the Informix server connection.

Ifx_pconnect: open the continuous connection of the Informix server.

Ifx_close: close the Informix server connection.

Ifx_query: sends out a query string.

Ifx_prepare: the quasi-query string.

Ifx_do: execute the prepared query string.

Ifx_error: get the last error of Informix.

Ifx_errormsg: gets the last error message of Informix.

Ifx_affected_rows: gets the number of columns for the last action shadow of Informix.

Ifx_getsqlca: get the sqlca information after query.

Ifx_fetch_row: returns the fields of a single column.

Ifx_htmltbl_result: convert the query returned data into an HTML table.

Ifx_fieldtypes: lists the SQL field of the Informix.

Ifx_fieldproperties: lists the SQL field attributes of Informix.

Ifx_num_fields: gets the number of returned fields.

Ifx_num_rows: gets the number of returned columns.

Ifx_free_result: release returns to occupy memory.

Ifx_create_char: create character classes.

Ifx_free_char: delete the character category.

Ifx_update_char: change the character category.

Ifx_get_char: gets the character class.

Ifx_create_blob: create long bits.

Ifx_copy_blob: make the long bit.

Ifx_free_blob: except for long bits.

Ifx_get_blob: get long bits.

Ifx_update_blob: change the long bit type.

Ifx_blobinfile_mode: configure long bit mode.

Ifx_textasvarchar: configure the default value for text mode.

Ifx_byteasvarchar: configure the default value for bit mode.

Ifx_nullformat: configure the null character mode default value.

Ifxus_create_slob: establish the slob class.

Ifx_free_slob: excluding the slob category.

Ifxus_close_slob: excluding the slob category.

Ifxus_open_slob: open the slob category.

Ifxus_tell_slob: return to the current file or find the location.

Ifxus_seek_slob: configure the current file or find the location.

Ifxus_read_slob: reads the specified number of slob classes.

Ifxus_write_slob: enter strings into the slob class

These are the INFORMIX database functions that Xiaobian shares with you. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.