SQLite3 basic instructions
SQLite3 provides some C function interfaces, and you can use these functions to operate the database. By using these interfaces and passing some standard sql statements (in char * type) to the sqlite function, SQLite3 will operate the database for you.
SQLite3 is a file database like MS access. That is to say, a database is a file. Many tables can be created in this database, indexes, triggers, etc., but what it actually gets is a file. Backing up this file backs up the entire database.
SQLite3 does not require any database engine, which means that if you need sqlite to save some user data, you do not even need to install a database.
SQLite3 command (Commonly used Sql statements)operate
Create table: createtable table name (element name type,...);
Delete table: droptable table name;
Insert data: insertinto table name values(,);
Create an index: create[unique]index index name on table name (col….);
Delete index: dropindex index name (the index cannot be changed, if you want to change it, you must delete it and rebuild it)
Delete data: deletefrom table name;
Update data: update table name set field = 'modified content' where condition;
Add a column: Altertable table name addcolumn field data type;
Select query: select fields (separated by ",") from table name where condition;
Date and time:Selectdatetime('now')
Date: selectdate('now');
Time:selecttime('now');
Total number: selectcount(*)fromtable1;
Sum: selectsum(field1)fromtable1;
Average: selectavg(field1)fromtable1;
Maximum: selectmax(field1)fromtable1;
Minimum: selectmin(field1)fromtable1;
Sorting: select field fromtable1orderby field (desc or asc); (descending or ascending order)
Grouping: select field fromtable1groupby field, field...;
Limit output: select field fromtable1limitxoffsety;
Data types supported by SQLite3
NULL The value is NULL
The INTEGER value is a signed integer, stored with 1, 2, 3, 4, 6, 8 bytes according to the category
REAL value is a floating point type, stored in 8 bytes
The TEXT value is a text string, stored using database encoding (UTF-8, UTF-16BEorUTF-16-LE)
BLOB value is binary data, please see the actual input for details.
But in fact, sqlite3 also accepts the following data types:
smallint 16-bit integer
Interger 32-bit integer
Decimal(p,s) The exact value of p and the decimal integer of size s. The exact value p refers to the total number of digits (digits), and s refers to the number of digits after the decimal point. If not specified, the system will set p=5;s=0.
Float 32-bit real number.
double 64-bit real number.
char(n) n-length string, n cannot exceed 254.
Varchar (n) is not fixed and its maximum length is N, N cannot exceed 4000.
Graphic(n) is the same as char(n), but its unit is double-bytes, and n cannot exceed 127. This form is to support two-character length fonts, such as Chinese characters.
vargraphic(n) A two-character string with variable length and a maximum length of n. n cannot exceed 2000.
Date contains year, month, and date.
Time includes hours, minutes, and seconds.
Timestamp contains year, month, day, hour, minute, second and thousandth of a second.
If a column of the declaration table is set to INTEGERPRIMARYKEY, it has:
1． Whenever you insert a NULL value into the column, the NULL is automatically converted to an integer that is 1 greater than the maximum value in the column;
2. If the table is empty, it will be 1;
arithmetic functions
abs(X) returns the absolute value of the given numeric expression.
max(X,Y[...]) returns the maximum value of the expression.
Min(X,Y[...]) returns the minimum value of the expression.
Random(*) returns a random number.
round(X[Y]) returns a numeric expression rounded to the specified length or precision.
Character processing functions
length(X) returns the number of characters in the given string expression.
lower(X) returns the character expression after converting uppercase character data to lowercase character data.
Upper(X) returns a character expression that converts lowercase character data to uppercase.
substr(X,Y,Z) returns part of the expression.
randstr()
quote(A)
Like(A,B)
Determines whether the given string matches the specified pattern.
glob(A,B)
conditional judgment function
coalesce(X,Y[...])
Ifnull(X,Y)
nullif(X,Y)
aggregate function
avg(X) returns the average of the values in the group.
Count(X) returns the number of items in the group.
max(X) returns the maximum value in the group.
Min(X) returns the minimum value in the group.
Sum(X) returns the sum of all values in the expression.
Other functions
Typeof(X) returns the type of data.
Last_insert_rowid() returns the ID of the last inserted data.
sqlite_version(*) returns the SQLite version.
Change_count() returns the number of rows affected by the previous statement.
last_statement_change_count()
SQLite3 update log:
1. Fixed BUG, the new version has a better experience
2. Some pages have been changed
Huajun editor recommends:
SQLite3, produced with conscience, guaranteed performance, personally tested by the editor, I believe you can try it on my children's shoes! There are more useful software in our Huajun Software ParkWancai Office Master OfficeBox,Foxit PDF Suite Advanced Editor,GWD Text Editor,Lizard Safeguard Secure PDF Viewer,Sante CT Viewer!
it works
it works
it works