Until you learn! Find information before you learn
SQLite's SQL
ATTACH DATABASE
BEGIN TRANSACTION
Comment
COMMIT TRANSACTION
COPY
CREATE INDEX
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DELETE
DETACH DATABASE
DROP INDEX
DROP TABLE
DROP TRIGGER
DROP VIEW
End TRANSACTION
EXPLAIN
Expression
INSERT
On CONFLICT clause
PRAGMA
REPLACE
ROLLBACK TRANSACTION
SELECT
UPDATE
SQLite FAQ
(1) How to establish an automatic growth field?
Short answer: A column declared as an INTEGER PRIMARY KEY will grow automatically.
Long answer: If you declare the table as an integer PRIMARY KEY, then whenever you insert a null value on the column, NULL is automatically converted to an integer that is 1 larger than the maximum value in the column, and if the table is empty, it will be 1. (If it is the maximum possible primary key 9223372036854775807, that, the key value will be a random unused number.) For example, the following tables are available:
CREATE TABLE T1 (
A INTEGER PRIMARY KEY,
b INTEGER
);
On the table, the following statements
INSERT into T1 VALUES (null,123);
Logically equivalent to:
INSERT into T1 VALUES (SELECT Max (a) from T1) +1,123);
There is a new API called Sqlite3_last_insert_rowid (), which returns the most recently inserted integer value.
Note that the integer is 1 larger than the maximum value before the insertion on the column in the table. The key value is unique in the current table. However, it is possible to overlap values that have been removed from the table. To establish a unique key value for the entire life cycle of the table, you need to add the AutoIncrement declaration on the INTEGER PRIMARY key. The new key value will be 1 greater than the maximum value that existed in the table. If the largest possible integer value exists in the datasheet, the insert fails and returns the Sqlite_full error code.
--------------------------------------------------------------------------------
(2) What type of data does SQLite3 support?
Null
INTEGER
Real
TEXT
Blob
In practice, however, Sqlite3 also accepts the following data types:
smallint a 16-bit integer.
Interger a 32-bit integer.
Decimal (P,S) p exact value and s size decimal integer, the exact value p means that all have several number (digits) size values, S is the number of several digits after the decimal point. If not specifically specified, the system is set to p=5; S=0.
A float 32-bit real number.
A double 64-bit real number.
char (n) n length of string, n cannot exceed 254.
varchar (n) length is not fixed and its maximum length is n of the string, n can not exceed 4000.
Graphic (n) is the same as char (n), but its unit is two characters Double-bytes and N cannot exceed 127. This form is designed to support two-character-length fonts, such as Chinese characters.
Vargraphic (N) a two-character string with a variable length and a maximum length of n that cannot exceed 2000.
Date contains the year, month, and date.
Time contains hours, minutes, and seconds.
Timestamp contains years, months, days, hours, minutes, seconds, and 1 per thousand seconds.
See http://www.sqlite.org/datatype3.html.
--------------------------------------------------------------------------------
(3) SQLite allows you to insert a string into an integer field!
This is an attribute, not a bug. SQLite does not enforce data type constraints. Any data can be inserted into any column. You can insert a string of any length into an integer column, insert a floating-point number into a Boolean column, or insert a date value into a character column. The data type specified in the CREATE TABLE does not restrict the insertion of any data into the column. Any column can accept strings of any length (except in one case: A column with an integer PRIMARY key can store only 64-bit integers, and an error is generated when you insert data into such a column except for integers.)
But SQLite does use declared column types to indicate the format you expect. So, for example, when you insert a string into an integer column, SQLite attempts to convert the string to an integer. If it can be converted, it inserts the integer, otherwise the string is inserted. This feature is sometimes referred to as type or column affinity (type or columns affinity).
--------------------------------------------------------------------------------
(4) Why is SQLite not allowed to use 0 and 0.0 as primary keys on two rows different from the same table?
The primary key must be a numeric type, and changing the primary key to the text type will not work.
Each row must have a unique primary key. For a numeric column, sqlite that ' 0 ' and ' 0.0 ' are the same because they are equal when compared as integers (see the previous question). So, that's not the only value.
--------------------------------------------------------------------------------
(5) Multiple applications or multiple instances of an application can access the same database file at the same time?
Multiple processes can open the same database at the same time. Multiple processes can have a select operation at the same time, but at any one time only one process changes the database.
SQLite uses read and write locks to control access to the database. (in win95/98/me and other systems that do not support read and write locks, a probabilistic simulation is used instead.) But use caution: If the database file is stored on an NFS file system, this locking mechanism may not work correctly. This is because the FCNTL () file lock is not implemented correctly on many NFS. You should avoid putting database files on NFS When you may have multiple processes simultaneously accessing the database. On Windows, Microsoft's documentation says that if you use the FAT file system without running the Share.exe daemon, the lock may not work properly. Those who have a lot of experience on Windows tell me: For network files, the implementation of the file lock has a lot of bugs, is unreliable. If they are right, sharing a database between two or more Windows machines may cause an unexpected problem.
We realize that no other embedded SQL database engine can handle so many concurrency like SQLite. SQLite allows multiple processes to open a database at the same time, while reading a database. When any process wants to write, it must lock the database file during the update process. But that's usually just a few milliseconds ' time. Other processes simply wait for the write process to finish. Typically, other embedded SQL database engines allow only one process to connect to the database at the same time.
However, the Client/server database engine (such as PostgreSQL, MySQL, or Oracle) typically supports higher-level concurrency and allows multiple processes to write the same database at the same time. This mechanism is possible on a database of client/server structures because there is always a single server process that controls and coordinates access to the database. If your application requires a lot of concurrency, then you should consider using a database with a client/server structure. But experience has shown that many applications need more concurrency than their designers think.
When SQLITE attempts to access a file that is locked by another process, the default behavior is to return sqlite_busy. You can use the Sqlite3_busy_handler () or sqlite3_busy_timeout () API functions to adjust this behavior in C code.
---------------------------------------------------------------------------
Current 1/2 page
12 Next read the full text
