Sqlite Learning Record

SQLite command Line (CLP)
======================================================================================
. help//for help, the available commands are listed as follows:
. ver//Get version
. Open test.db//Open database file
. Save test.db//Save a database or create a new
. database//gets the database file that exists in the current directory, a file that represents a database
. tables//Get Table List
. Indices test//Get the table's index list
. Schema test//provides table names to get table creation statements, none to get all tables, query Sqlite_master system view can also
. show//gets the settings defined by the user shell
. Nullvale NULL//SET NULL display as NULL
. echo on//Output executed statements

Export to SQL file
. Output File.sql
. dump
. Output stdout

Import SQL file
drop table test;
Drop view schema;
. Read File.sql
or directly in the command line:
Sqlite3 test.db dump > Test.sql
Sqlite3 Test.db < Test.sql
Sqlite3-init Test.sql Test.db

CREATE TABLE test (ID integer primary key, value text); ID primary key and self-increment
CREATE INDEX TEST_IDX on test (value)//.

Simple to delete and change the query, and MySQL similar
INSERT into Test (Id,value) VALUES (1, ' testz ');
INSERT into test (value) VALUES (' Testz ');

. mode column//Query result display column, optional format: CSV,HTML,INSERT,LINE,TABS,TCL, default is List
. headers on//Display header information (contains. Mode column)
SELECT * from test;//query data, do not display column names
Select Last_insert_rowid ();//Gets the last inserted ID

The result output is CSV, where. Mode CSV can also be used. Dump output Backup SQL
. Output File.csv
. mode csv//or with. Separator,
SELECT * from Test;
. Output stdout

Import to Test2 table
CREATE table test2 (ID integer primary key, value text);
. Import Text.csv Test2

Backup source files, binaries, but no SQL porting good
Sqlite3 Test.db Vacuum
CP test.db Test. Backup

Tools
======================================================================================
Sqlite3_analyzer can obtain detailed technical information about the database disk structure.

Sql
======================================================================================
Used as a command terminator.

SELECT * from Test;
INSERT into test (value) VALUES (' Testz ');
Delete from Test where id = 1;
Update teable set value= "zz" where id=3

Many are similar to MySQL syntax, such as:
Like "%test%", COUNT (*), group BY, having, distinct, as, in, subquery, compound query,

Read a line starting at the first line. DESC or ASC, can ignore offset.
SELECT * FROM Test ORDER by DESC ID Limit 1 Offset 1

MySQL-like left connection
SELECT * FROM test a LEFT outer joins TESTB B on a.id=b.id;

Field design
======================================================================================
Default value for Date:
Current_date YYYY-MM-DD
Current_time Hh::mm::ss
Current_timestamp YYYY-MM-DD HH:MM:SS

Check constraint, less than 7 characters will be an error.
CREATE TABLE Test (
ID Integer PRIMARY KEY,
Name text NOT null default ' ZZZ ',
Unique (name),
Check (length (name) >=7));

The ID of the foreign key Test_types table
CREATE TABLE Test (
ID Integer PRIMARY KEY,
type_id integer references test_types (ID)
On delete restrict//When the parent ID is deleted, this data is not deleted.
Deferrable initially deferred,
Name text);

Full rule:
Set NULL: If the parent value is deleted or does not exist, the remaining child value is changed to NULL.
Set Default: If the parent value is deleted or does not exist, the remaining child values are changed to the default values.
Casecade: The child value is also updated or deleted when the parent value is updated or deleted.
Restrict: updating or deleting a parent value may cause orphaned child values to block transactions.
No action: does not interfere with operation execution, only observe changes.
Deferrable: Immediate enforcement or deferred until the end of the entire transaction.

About field types
The type is judged by the notation of the value. There are 5 types: integer, real (floating point), text, blob (binary x at the beginning of quotation marks), null
A field can store different types of values.
Select typeof (3.14) query its type

Index
======================================================================================
Create a name case insensitive index
CREATE INDEX A_IDX Test (name collate nocase);

Case with single field index: IDX on test (a)
column {=|>|>=|<=|<} expression
expression {=|>|>=|<=|<} column
column in (expression-list)
column in (subquery)

Conditions for multi-field indexing: IDX on test (A,B,C,D)
Only A and B use the index
SELECT * FROM Test where a=1 and b=2 and d=3
The expression a>1 is called the rightmost index field, because the index is not available for subsequent query criteria because it uses an equal sign.
SELECT * FROM Test where a>1 and b=2 and C=3 and d=3

Transaction
======================================================================================
Begin,commit,rollback

Mainly divided into: read transactions, write transactions.
When multiple transactions run together by default, the non-read operation of one transaction needs to be committed until the end of another transaction (if there is a statement execution).

To avoid deadlocks, you need to use the correct transaction type with the default of 3 types:
Begin [Deferred | immediate | exclusive] transaction;
which
Deferred
is the default until the lock is acquired when it must be used.

Immediate
Attempting to acquire a reserved lock at execution time, if successful, other transactions cannot modify the database to read only, commits are not blocked, and other transactions can be modified after committing.

Exclusive
Try to get an exclusive lock on the database, only wait for this transaction to complete any operation and commit, the other transaction can start to execute the query or additions and deletions and other statements.
Exclusive a higher level of security than immediate, immediate will not have a deadlock at first.

Sqlite Learning Record