SQLite: Learning

SQLite is widely used in the Open Source (public domain) embedded RDBMS system, basically follow the standard of SQL-92

Features: simple, 0 configuration, efficient, reliable, single file, support for transactions (acid: basic database lock), full cross-platform, cross 32 \ 64, multi-read + 1 Write

Note: The weak column type (null, integer, real, text, blob: although it can be declared during DDL, the RDBMS system does not enforce it)

: Weak column length constraints. You can define the length, but RDMS is not enforced. When processing an auto-increment column (INT primary key ),

 

 

Use and Management of basic sqlite3 command line Interfaces

Backup and Restoration	. Backup? DB? File backup dB (default "Main") to file . Restore? DB? File restore content of dB (default "Main") from File
Import and Export	. output Filename send output to filename . output stdout send output to the screen . import file table import data from file into Table . separator string change Separator Used by output mode and. import . nullvalue string use string in place of null values
Query Dictionary data	. Databases list names and files of attached Databases . Indices? Table? Show names of all indices. If table specified, only show indices for tables matching like pattern table. . Schema? Table? Show the create statements. If table specified, only show tables matching like pattern table. . Tables? Table? List names of tables. If table specified, only list tables matching like pattern table. Two internal architecture tables:Sqlite_master, sqlite_temp_master Lists All Tables, indexes, and triggers in the database.
Interactive Interface: format settings	. Mode? Table? Set output mode where mode is one: CSV comma-separated values Column left-aligned columns. (See. width) HTML <Table> code Insert SQL insert statements Table -- this parameter must be provided. Otherwise, the generated insert SQL statement cannot be executed. Line one value per line List values delimited by. Separator string Tabs tab-separated values TCL list elements . Nullvalue string use string in place of null values . Separator string change Separator Used by output mode and. Import . Width num1 num2... Set column widths for "column" Mode . Header (s) on | off turn display of headers on or off . Echo on | off turn command echo on or off
Performance profile	. Stats on | off turn stats on or off . Timer on | off turn the CPU timer measurement on or OFF . Explain? On | off? Turn output mode suitable for explain on or off. With no ARGs, it turns explain on.
Log System	. Log File | off turn logging on or off. file can be stderr/stdout: Error Log . Trace file | off output each SQL statement as it is run: General SQL statement tracking log
Error Handling Control	. Bail On | off stop after hitting an error. Default off
Transaction Control	. Timeout MS try opening locked tables for MS milliseconds
Execute an External SQL File	. Read filename Execute SQL in Filename
Other assistance	. Help show this message . Print string... print literal string . Prompt main continue Replace the standard prompts . Show show the current values for various settings
Exit	. Exit exit this program . Quit exit this program

 

Sqlite3 command line interface display method ". mode", very powerful!

Format options	Description
Tabs	Text Format of tab Segmentation
List	Text Format separated by. separator,The default value is "|"
CSV	Text Format separated by commas
TCL	Empty separated text format enclosed by double quotation marks
Insert	DirectGenerateInsert SQLCode.This mode must be used with the table parameter of the. mode command.
Html	GenerateHTML page number,Note that it is not a complete web page number, but only a row or column part.
Line	Similar to other tools such as MySQL and sqlplusVertical display \ G
Column	Left-aligned left text filled with spaces in paddingFixed-width text table.Suitable for data in [fixed group length] format

 

Major features of SQL-92 not supported by sqlite3

Until the time I wrote: the smaller the number (that is, the more features mentioned earlier), the more willing to implement these features as soon as possible, but not necessarily!

2. Right and full outer join:Left Outer JoinIs implemented, but not right Outer Join or full outer join.
4. Complete alter table supportOnly the rename table and add ColumnVariants of the alter table command are supported. Other kinds of alter table operations such as drop column, alter column, add constraint, and so forth are omitted.
6. Complete trigger supportFor each row triggersAre supported but notFor each statementTriggers.
8. Writing to ViewsViews in SQLite are read-only. You may not executeDelete, insert, or updateStatement on a view. But you can create a trigger that fires on an attempt to delete, insert, or update a view and do what you need in the body of the trigger.
10. Grant and revokeSince SQLite reads and writes an ordinary disk file, the only access permissions that can be applied areThe normal file access permissions of the underlying operating system. The grant and revoke commands commonly found on Client/Server rdbmses are not implemented because they wocould be meaningless for an embedded database engine.