c语言 redo前面的函数,sqlite的使用方法和函数(转)

可以是任何在 CREATE TABLE 语句中有效的非空的类型名。

系统支持简单和***函数。一个简单函数可以用于任何表达式。简单函数会在其输入的基础上立即返回结果。***函数只能用于 SELECT 语句中。 ***函数会在返回的结果集上进行跨行计算，并返回相应的结果。

核心函数 Core Functions

下列是默认可以的核心函数。额外的函数可以使用 C 语言编写并使用 sqlite3_create_function() API 加入数据库引擎中。

abs(X)     返回参数 X的绝对值。

coalesce(X,Y,...)     返回参数中第一个非 NULL 的值。若所有参数均为 NULL，则返回 NULL。该函数至少需要两个参数。

glob(X,Y)     该函数用户实现 SQLite 的 "X GLOB Y" 语法。可以使用 sqlite3_create_function() 接口来重载该函数，从而改变 GLOB 操作符的行为方式。

ifnull(X,Y)     返回第一个非 NULL 的参数值，如果所有参数都是 NULL， 则返回 NULL。与上面的 coalesce() 一样。

hex(X)     参数以 BLOB 对待。结果是 BLOB 内容的 16 进制表示。

last_insert_rowid()     返回当前数据库连接中最后插入的一行的 ROWID。该值与使用 sqlite_last_insert_rowid() API 函数返回的值相同。

length(X)     返回 X 字符串的长度。如果 SQLite 配置为支持 UTF-8，则返回的是 UTF-8 字符的长度，而不是字节数。

like(X,Y)

like(X,Y,Z)

该函数用于实现 SQL 的 "X LIKE Y [ESCAPE Z]" 语法。如果有可选的 ESCAPE

语句，将使用具有三个参数的函数，否则，将使用只有两个参数的数据。可以使用 sqlite_create_function()

接口来重载该函数，以改变 LIKE 操作符的行为。如果那样做，一定要注意同时重载两个(两个参数的和三个参数的)版本的函数。否则，依据是否使用了

ESCAPE 子句，可以会调用不同的代码。

load_extension(X)

load_extension(X,Y)

装入 SQLite 共享库之外文件名为 X 而入口点为 Y 的扩展库。结果将是 NULL。 如果省略了 Y，那么，将使用默认的入口点

sqlite3_extension_init。该函数在装入或初始化失败时会引发一个异常。

如果扩展试图修改或删除一个 SQL

函数或对照序列，则该函数会失败。可以使用扩展增加新函数或对照序列，但不能修改或删除已存在的。这是因为那些函数和/或对照序列可能正在被其它的SQL

语句使用。要想装入可以修改或删除函数或对照序列的扩展，使用 sqlite3_load_extension() C 语言API。

lower(X)     返回将字符串 X 转换为小写后的字符串。该函数使用 C 语言库函数 tolower() 进行转换，所以，可能不能正确转换 UTF-8 字符。

ltrim(X)

ltrim(X,Y)     返回一个字符串，它是从字符串 X 的左边删除了任何存在于字符串 Y 中的字符后剩余的字符串。如果省略参数 Y ，则会删除左边的空格。

max(X,Y,...)     返回参数的最大值。除数字外，参数可能是字符串。它使用通常的排序顺序来决定最大值。注意若 max() 有两个或多个参数，则它是一个简单函数。但如果只提供一个参数，它将变成一个***函数。

min(X,Y,...)     返回参数的最小值。除数字外，参数可能是字符串。它使用通常的排序顺序来决定最小值。注意若 min() 有两个或多个参数，则它是一个简单函数。但如果只提供一个参数，它将变成一个***函数。

nullif(X,Y)     如果参数不同，则返回第一个参数，否则返回NULL。

quote(X)

该函数返回一个对其参数值进行处理后适合包含在其它 SQL 语句中的字符串。单引号包括的字符串将会按需要转换成内部引用的格式。

BLOB 将会编码为十六进制字面值。当前实现的 VACUUM 使用该函数。在写触发器来实现 “撤消/重做” 功能时，该函数也是很有用的。

random(*)     返回一个伪随机数。结果在 -9223372036854775808 与 +9223372036854775807之间。

replace(X,Y,Z)     返回一个将 X 字符串中每一个出现 Y 的位置替换为 Z 后的字符串。它使用二进制对照序列进行比较。

randomblob(N)     返回一个 N 字节长的包含伪随机字节的 BLOG。 N 应该是正整数。

round(X)

round(X,Y)     对 X 圆整成小数点后 Y 位。或省略 Y ，则默认Y 为 0。

rtrim(X)

rtrim(X,Y)     返回从 X 的右边去除所有出现在 Y 中字符以后的字符串。如果省略 Y，则去除空格。

soundex(X)     计算字符串 X的读音编码。如果参数为 NULL，则返回 "?000"。默认情况下 SQLite 忽略该函数。仅当在编译时指定 -DSQLITE_SOUNDEX=1 时才有效。

sqlite_version(*)     返回当前运行的 SQLite 库的版本号。如："2.8.0"

substr(X,Y,Z)

substr(X,Y)

返回字符串 X 从第 Y 个字符开始，长度为 Z 的字符串。如果省略 Z， 将返回直到字符串结尾的字符串。 X 的第一个字符从 1

开始。如果 Y 是负数，则从右边开始数。如果 X 是 UTF-8 字符串，那么，下标值将指实际的 UTF-8 characters

字符，而不是字节。如果 X 是一个 BLOB，那么下标就是指字节。

trim(X)

trim(X,Y)     返回从字符串 X 的两头去掉所有存在于 Y 中字符后的字符串。如果省略 Y，则去空格。

typeof(X)     返回表达式 X 的类型。只可能是 "null", "integer", "real", "text", 以及 "blob"。 SQLite 的类型处理在 Datatypes in SQLite Version 3 中有说明。

upper(X)     返回字符串 X 被转换为大写后的字符串。它使用 C 库函数 toupper() 实现，对于 UTF-8，某些字符串可能不能正确转换。

zeroblob(N)

返回一个 N 字节长、全部由 0x00 组成的 BLOB。 SQLite 或以很有效的组织这些

zeroblob。它可以被用于为以后的使用预留空间。以后可以使用 incremental BLOB I/O 来写入 BLOB 数据。

日期和时间函数

日期和时间函数在 SQLite Wiki上。

***函数

有下列默认可和的***函数：可以使用 sqlite3_create_function() API 来增加其它的***函数。

任何***函数都只有一个参数。其参数前可以有一个 DISTINCT。如果有，重复的元素将会在传递给***函数前过滤掉。如，函数 "count(distinct X)" 将只返回在 X 列上不重复的行的总数，而不是在该列上所有非空的行的总数

avg(X)     返回 X 列的一组中所有非空值的平均值。字符串或 BLOB 等非数字值将被认为是 0。即使所有输入都是整数， avg() 的结果也永远是浮点数。

count(X)

count(*)     第一种形式返回在 X列上的一组中非空的行的总数。第二种(无任何参数)返回一组中所有的行数。

group_concat(X)

group_concat(X,Y)     结果是一个所有非空的 X 连接起来的一个字符串。如果有参数 Y，则它会做为连接 X 时的分隔符。若省略Y ，则默认是逗号(,)。

max(X)     返回一组中的最大值。使用通常的排序顺序来确定最大值。

min(X)     返回一组中除非空值以外的最小值。使用通常的排序顺序来确实最小值。只有当整个组中所有值均为 NULL 时才会返回 NULL。

sum(X)

total(X)

返回一组中所有非空值的数值总和。如果在输入中没有非空值，则 sum() 会返回 NULL 而 total() 会返回

0.0。不仅能在 sum() 没有对任何行求和时， NULL能给出有帮助的结果，而且 SQL 标准也需要它。而且其它的 SQL

数据库引擎也是这么实现的。 SQLite 这么做也是为了保持兼容性。我们也提供了一个非标准的 total() 函数，以提供一个方便的途径来绕过

SQL 语言的这一设计问题。

total() 的结果永远是浮点数。 如果所有非空的输入都是整数， sum() 的结果将是整数值。 若任何输入给 sum() 的值是除整数及 NULL 以外的值， sum() 都将返回浮点数。这可能是最接近标准 sum() 的实现方式吧。

如果输入全部是整数或 NULL，在结果溢出时 sum() 将会产生一个 "integer overflow" 异常。 而 total() 永远不会。

限制笔数

select * from film limit 10;

sqlite3 film.db "select * from film;"

输出 HTML 表格：

sqlite3 -html film.db "select * from film;"

将数据库「倒出来」：

sqlite3 film.db ".dump" > output.sql

利用输出的资料，建立一个一模一样的数据库(加上以上指令，就是标准的SQL数据库备份了)：

sqlite3 film.db < output.sql

在大量插入资料时，你可能会需要先打这个指令：

begin;

插入完资料后要记得打这个指令，资料才会写进数据库中：

commit;

SQLite内建函数表

算术函数

abs(X)    返回给定数字表达式的绝对值。

max(X,Y[,...])    返回表达式的最大值。

min(X,Y[,...])    返回表达式的最小值。

random(*)    返回随机数。

round(X[,Y])    返回数字表达式并四舍五入为指定的长度或精度。

字符处理函数

length(X)    返回给定字符串表达式的字符个数。

lower(X)    将大写字符数据转换为小写字符数据后返回字符表达式。

upper(X)    返回将小写字符数据转换为大写的字符表达式。

substr(X,Y,Z)    返回表达式的一部分。

randstr()

quote(A)

like(A,B)    确定给定的字符串是否与指定的模式匹配。

glob(A,B)

条件判断函数

coalesce(X,Y[,...])

ifnull(X,Y)

nullif(X,Y)

集合函数

avg(X)    返回组中值的平均值。

count(X)    返回组中项目的数量。

max(X)    返回组中值的最大值。

min(X)    返回组中值的最小值。

sum(X)    返回表达式中所有值的和。

其他函数

typeof(X)    返回数据的类型。

last_insert_rowid()    返回最后插入的数据的ID。

sqlite_version(*)    返回SQLite的版本。

change_count()    返回受上一语句影响的行数。

last_statement_change_count()

The

core functions shown below are available by default. Additional

functions may be written in C and added to the database engine using the

sqlite3_create_function() API.

abs(X)     Return the absolute value of argument X.

coalesce(X,Y,...)

Return a copy of the first non-NULL argument. If all arguments are

NULL then NULL is returned. There must be at least 2 arguments.

glob(X,Y)

This function is used to implement the "X GLOB Y" syntax of

SQLite. The sqlite3_create_function() interface can be used to override

this function and thereby change the operation of the GLOB operator.

ifnull(X,Y)

Return a copy of the first non-NULL argument. If both arguments

are NULL then NULL is returned. This behaves the same as coalesce()

above.

last_insert_rowid()     Return the ROWID of the last row

insert from this connection to the database. This is the same value that

would be returned from the sqlite_last_insert_rowid() API function.

length(X)

Return the string length of X in characters. If SQLite is

configured to support UTF-8, then the number of UTF-8 characters is

returned, not the number of bytes.

like(X,Y [,Z])     This function

is used to implement the "X LIKE Y [ESCAPE Z]" syntax of SQL. If the

optional ESCAPE clause is present, then the user-function is invoked

with three arguments. Otherwise, it is invoked with two arguments on

ly.

The sqlite_create_function() interface can be used to override this

function and thereby change the operation of the LIKE operator. When

doing this, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.

load_extension(X)

load_extension(X,Y)

Load SQLite extensions out of the shared library file named X

using the entry point Y. The result is a NULL. If Y is omitted then the

default entry point of sqlite3_extension_init is used. This function

raises an exception if the extension fails to load or initialize

correctly.

lower(X)     Return a copy of string X will all

characters converted to lower case. The C library tolower() routine is

used for the conversion, which means that this function might not work

correctly on UTF-8 characters.

max(X,Y,...)     Return the argument

with the maximum value. Arguments may be strings in addition to numbers.

The maximum value is determined by the usual sort order. Note that

max() is a simple function when it has 2 or more arguments but converts

to an aggregate function if given only a single argument.

min(X,Y,...)

Return the argument with the minimum value. Arguments may be

strings in addition to numbers. The minimum value is determined by the

usual sort order. Note that min() is a simple function when it has 2 or

more arguments but converts to an aggregate function if given only a single argument.

nullif(X,Y)     Return the first argument if the arguments are different, otherwise return NULL.

quote(X)

This routine returns a string which is the value of its argument

suitable for inclusion into another SQL statement. Strings are

surrounded by single-quotes with escapes on interior quotes as needed.

BLOBs are encoded as hexadecimal literals. The current implementation of

VACUUM uses this function. The function is also useful when writing

triggers to implement undo/redo functionality.

random(*)     Return a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

round(X)

round(X,Y)

Round off the number X to Y digits to the right of the decimal

point. If the Y argument is omitted, 0 is assumed.

soundex(X)

Compute the soundex encoding of the string X. The string "?000" is

returned if the argument is NULL. This function is omitted from SQLite

by default. It is only available the -DSQLITE_SOUNDEX=1 compiler option is used when SQLite is built.

sqlite_version(*)     Return the version string for the SQLite library that is running. Example: "2.8.0"

substr(X,Y,Z)

Return a substring of input string X that begins with the Y-th

character and which is Z characters long. The left-most character of X

is number 1. If Y is negative the the first character of the substring

is found by counting from the right rather than the left. If SQLite is

configured to support UTF-8, then characters indices refer to actual

UTF-8 characters, not bytes.

typeof(X)     Return the type of the expression X. The only

return values are "null", "integer", "real", "text", and "blob".

SQLite's type handling is explained in Datatypes in SQLite Version 3.

upper(X)

Return a copy of input string X converted to all upper-case

letters. The implementation of this function uses the C library routine

toupper() which means it may not work correctly on UTF-8 strings.

Aggregate Functions

The

aggregate functions shown below are available by default. Additional

aggregate functions written in C may be added using the

sqlite3_create_function() API.

In any aggregate function that

takes a single argument, that argument can be preceeded by the keyword

DISTINCT. In such cases, duplicate elements are filtered before being

passed into the aggregate function. For example, the function

"count(distinct X)" will return the number of distinct values of column X

instead of the total number of non-null values in column X.

avg(X)

Return the average value of all non-NULL X within a group. String

and BLOB values that do not look like numbers are interpreted as 0. The

result of avg() is always a floating point value even if all inputs are

integers.

count(X)

count(*)     The first form return a count of

the number of times that X is not NULL in a group. The second form (with

no argument) returns the total number of rows in the group.

max(X)     Return the maximum value of all values in the group. The usual sort order is used to determine the maximum.

min(X)

Return the minimum non-NULL value of all values in the group. The

usual sort order is used to determine the minimum. NULL is only returned if all values in the group are NULL.

sum(X)

total(X)

Return the numeric sum of all non-NULL values in the group. If

there are no non-NULL input rows then sum() returns NULL but total()

returns 0.0. NULL is not normally a helpful result for the sum of no

rows but the SQL standard requires it and most other SQL database

engines implement sum() that way so SQLite does it in the same way in

order to be compatible. The non-standard total() function is provided as

a convenient way to work around this design problem in the SQL

language.

The result of total() is always a floating point value.

The result of sum() is an integer value if all non-NULL inputs are

integers. If any input to sum() is neither an integer or a NULL then

sum() returns a floating point value which might be an approximation to

the true sum.

Sum() will throw an "integer overflow" exception if

all inputs are integers or NULL and an integer overflow occurs at any

point during the computation. Total() never throws an exception.

Date and Time Functions Overview

Five date and time functions are available, as follows:

1. date( timestring, modifier, modifier, ...)

2. time( timestring, modifier, modifier, ...)

3. datetime( timestring, modifier, modifier, ...)

4. julianday( timestring, modifier, modifier, ...)

5. strftime( format, timestring, modifier, modifier, ...)

All

five functions take a time string as an argument. This time string may

be followed by zero or more modifiers. The strftime() function also

takes a format string as its first argument.

The date() function

returns the date in this format: YYYY-MM-DD. The time() function returns

the time as HH:MM:SS. The datetime() function returns "YYYY-MM-DD

HH:MM:SS". The julianday() function returns the number of days since

noon in Greenwich on November 24, 4714 B.C. The julian day number is the

preferred internal representation of dates. The strftime() routine

returns the date formatted according to the format string specified as

the first argument. The format string supports most, but not all, of the

more common substitutions found in the strftime() function from the

standard C library:

%d day of month

%f ** fractional seconds SS.SSS

%H hour 00-24

%j day of year 001-366

%J ** Julian day number

%m month 01-12

%M minute 00-59

%s seconds since 1970-01-01

%S seconds 00-59

%w day of week 0-6 sunday==0

%W week of year 00-53

%Y year 0000-9999

%% %

The %f and %J conversions are new. Notice that all of the other four functions could be expressed in terms of strftime().

date(...)      -> strftime("%Y-%m-%d", ...)

time(...)      -> strftime("%H:%M:%S", ...)

datetime(...) -> strftime("%Y-%m-%d %H:%M:%S", ...)

julianday(...) -> strftime("%J", ...)

The only reasons for providing functions other than strftime() is for convenience and for efficiency.

Time Strings

A time string can be in any of the following formats:

1. YYYY-MM-DD

2. YYYY-MM-DD HH:MM

3. YYYY-MM-DD HH:MM:SS

4. YYYY-MM-DD HH:MM:SS.SSS

5. YYYY-MM-DDTHH:MM

6. YYYY-MM-DDTHH:MM:SS

7. YYYY-MM-DDTHH:MM:SS.SSS

8. HH:MM

9. HH:MM:SS

10. HH:MM:SS.SSS

11. now

12. DDDD.DDDD

In

formats 5 through 7, the "T" is a literal character separating the date

and the time, as required by the ISO-8601 standard. These formats are

supported in SQLite 3.2.0 and later. Formats 8 through 10 that specify

only a time assume a date of 2000-01-01. Format 11, the string

'now', is converted into the current date and time. Universal

Coordinated Time (UTC) is used. Format 12 is the julian day number

expressed as a floating point value.

Modifiers

The time

string can be followed by zero or more modifiers that alter the date or

alter the interpretation of the date. The available modifiers are as

follows.

1. NNN days

2. NNN hours

3. NNN minutes

4. NNN.NNNN seconds

5. NNN months (see #551 and [1163])

6. NNN years (see #551 and [1163])

7. start of month

8. start of year

9. start of week (withdrawn -- will not be implemented)

10. start of day

11. weekday N (see #551 and [1163])

12. unixepoch

13. localtime

14. utc

The first six modifiers (1 through 6) simply add the specified amount of time to the date specified by the preceding timestring.

The "start of" modifiers (7 through 10) shift the date backwards to the beginning of the current month, year or day.

The

"weekday" modifier advances the date forward to the next date where the

weekday number is N. Sunday is 0, Monday is 1, and so forth.

The "unixepoch" modifier (12) only

works if it immediately follows a timestring in the DDDDDDDDDD format.

This modifier causes the DDDDDDDDDD to be interpreted not as a julian

day number as it normally would be, but as the number of seconds since

1970. This modifier allows unix-based times to be converted to julian

day numbers easily.

The "localtime" modifier (13) adjusts the previous time string so that it displays the correct local time. "utc" undoes this.

Examples

Compute the current date.

SELECT date('now');

Compute the last day of the current month.

SELECT date('now','start of month','+1 month','-1 day');

Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch');

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Compute the current unix timestamp.

SELECT strftime('%s','now');

Compute the number of days since the battle of Hastings.

SELECT julianday('now') - julianday('1066-10-14','gregorian');

Compute the number of seconds between two dates:

SELECT julianday('now')*86400 - julianday('2004-01-01 02:34:56')*86400;

Compute the date of the first Tuesday in October (January + 9) for the current year.

SELECT date('now','start of year','+9 months','weekday 2');

Caveats And Bugs

The

computation of local time depends heavily on the whim of local

politicians and is thus difficult to get correct for all locales. In

this implementation, the standard C library function localtime() is used

to assist in the calculation of local time. Note that localtime() is

not threadsafe, so use of the "localtime" modifier is not threadsafe.

Also, the localtime() C function normally only works for years

between 1970 and 2037. For dates outside this range, SQLite attempts to

map the year into an equivalent year within this range, do the

calculation, then map the year back.

Please surround uses of

localtime() with sqliteOsEnterMutex() and sqliteOsLeaveMutex() so

threads using SQLite are protected, at least! -- e It is so. --drh

[Consider

instead, using localtime_r which is reentrant and may be used *without*

expensive mutex locking. Although non-standard it's available on most

Unixes --hauk] But it is not available on windows, as far as I am aware.

--drh On windows localtime() is thread-safe if the MT C runtime is

used. The MT runtime uses thread-local storage for the static variables,

the kind functions use.--gr [What about using localtime_r, and on

systems where it is unavailable defining it as sqliteOsEnterMutext() ;

locatime() ; sqliteOsLeaveMutex() so that non-windows systems get the

maximum advantage, with almost zero code impact?] The autoconfigury

and patch for localtime_r is here:

¤ . I'm curious why this

obvious fix is not applied. gmtime() also suffers from this same

threadsafety problem.

Date computations do not give correct results for dates before Julian day number 0 (-4713-11-24 12:00:00).

All internal computations assume the Gregorian calendar system.

An anonymous user adds:

For

my use I added new functions and functionalities to the date functions

that come with the sqlite 3.3.0 (can be used in older versions as well

with small effort).

In main lines they are as follows:

1. NNN days

2. NNN hours

3. NNN minutes

4. NNN.NNNN seconds

5. NNN months (see #551 and [1163])

6. NNN years (see #551 and [1163])

7. start of month

8. start of year

9. start of week (!!! implemented)

10. start of day

11. weekday N (see #551 and [1163])

12. unixepoch

13. localtime

14. utc

15. julian (not implemented as of 2004-01-05)

16. gregorian (not implemented as of 2004-01-05)

17. start of minute

18. start of hour

19. end of minute

20. end of hour

21. end of day

22. end of week

23. end of month

24. end of year

25. group seconds by

26. group minutes by

27. group hours by

28. group days by

29. group weeks by

30. group months by

31. group years by

The

"start of" modifiers (7 through 10 and 17 through 18) shift the date

backwards to the beginning of the current minute, hour, week, month,

year or day.

The "end of" modifiers (19 through 24) shift the date forwards to the end of the current minute, hour, week, month, year or day.

The

"group * by" modifiers (25 through 31) round the date to the closest

backward multiple supplied, with some limitations, to the current

seconds (1 through 30), minutes (1 through 30), hours (1 through 12),

days (1 through 15), weeks (1 through 26), months (1 through 6), years

(1 through 100), these limitations are due to dont complicate the

calculations when a multiple can span beyound the unit modified.

Ex:

SELECT datetime('2006-02-04 20:09:23','group hours by 3'); => '2006-02-04 18:00:00'

SELECT datetime('2006-02-05 20:09:23','group days by 3'); => '2006-02-04 00:00:00'

New

functions "week_number(date)" returns the week number of the year on

the supplied date parameter, "datetime2seconds(datetime)" return the

number of seconds from the supplied datetime parameter.

The diff

file ready to be applied to the file "date.c" in the src directory of

sqlite 3.3.0 is at ¤ I hope it's

considered valuable to be merged in the official distribution.