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 FAQs
(1) How do I create an auto-grow field?
Short answer: The 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 that column, NULL is automatically converted to an integer that is 1 larger than the maximum value in the column, or 1 if the table is empty. (if the maximum possible primary key 9223372036854775807, that, will be the key value will be 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);
is 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 insertion on the column in the table. The key value is unique in the current table. It is possible, however, to overlap values that have been removed from the table. To establish a unique key value throughout the life cycle of the table, you need to add the AutoIncrement declaration on the INTEGER PRIMARY key. Then, the new key value will be 1 larger than the maximum value that could have existed in the table. If the maximum possible integer value has ever existed in the data table, insert will fail and return the Sqlite_full error code.
--------------------------------------------------------------------------------
(2) What data types are supported by SQLite3?
Null
INTEGER
REAL
TEXT
Blob
But in fact, 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 is the total number (digits) size value, s refers to the number of digits after the decimal point. If there is no special designation, the system will be set to p=5; S=0.
Float 32-bit real number.
A double 64-bit real number.
char (n) n-length string, n cannot exceed 254.
The varchar (n) length is not fixed and its maximum length is n, and N cannot exceed 4000.
Graphic (n) is the same as char (n), but its unit is two characters Double-bytes and N cannot exceed 127. This pattern is designed to support two character-length fonts, such as Chinese characters.
Vargraphic (n) variable-length double-character string with a maximum length of n, N cannot exceed 2000.
Date contains the year, month, and date.
Time contains hours, minutes, seconds.
Timestamp contains the year, month, day, time, minute, second, 1 per thousand seconds.
See http://www.sqlite.org/datatype3.html.
--------------------------------------------------------------------------------
(3) SQLite allows you to insert strings into an integer field!
This is a feature, 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-type 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 a string of any length except in one case: A column marked as an integer PRIMARY key can store only 64-bit integers, and an error occurs when inserting data into such a column except for integers.
However, SQLite does use the declared column type 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 different rows of the same table?
The primary key must be a numeric type, and changing the primary key to a text type will not work.
Each row must have a unique primary key. For a numeric column, SQLite thinks ' 0 ' and ' 0.0 ' are the same, because they are equal when compared as integers (see previous question). So, this value is not unique.
--------------------------------------------------------------------------------
(5) Do multiple applications or multiple instances of an application have access to the same database file at the same time?
Multiple processes can open the same database at the same time. Multiple processes can perform a select operation at the same time, but at any one time there can be only one process making changes to the database.
SQLite uses read and write locks to control access to the database. (In a system that does not support read and write locks, such as win95/98/me, a probabilistic simulation is used instead.) However, it is important to note that this locking mechanism may not work properly if the database file resides on an NFS file system. This is because the FCNTL () file lock is not implemented correctly on many NFS. Avoid placing database files on NFS When multiple processes may have simultaneous access to the database. On Windows, Microsoft's documentation says: If you use the FAT file system without running the Share.exe daemon, the lock may not be working properly. Those who have a lot of experience on Windows told me: for network files, file lock implementation has a lot of bugs, is unreliable. If they are right, sharing a database between two or more Windows machines can cause undesirable problems.
We realized that no other embedded SQL database engine could handle so much 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. Other processes only need to wait for the write process to finish the job. 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 a higher level of concurrency and allows multiple processes to write the same database at the same time. This mechanism is possible on the database of the CLIENT/SERVER structure because there is always a single server process that controls and coordinates access to the database well. 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 require much less concurrency than their designers think.
When SQLITE tries 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 in C code to adjust this behavior.
---------------------------------------------------------------------------
Current 1/2 Page 12 next page
The above describes the SQLite database installation PHP SQLite learning notes and common problems analysis 1th/2, including the content of the SQLite database installation, I hope to be interested in PHP tutorial friends helpful.
