SQLite Learning Notes (i)

(1) Create a database
Switch to the folder where Sqlite.exe is located in the command line
Type sqlite3 test.db in the command; You can create a database named Test.db
Since there are no tables and data in the database at this time, it is not possible to see the test.db, you must insert a table inside to see the database

(2) Create a table
CREATE TABLE Test (Id Integer primary key, value text);
The creation of the table can be completed at this time, when the primary key is set to Integer, the primary key is automatically growing, when inserting data, the following statements can be used directly:
INSERT into Test values (null, ' Acuzio ');

(3) Gets the last inserted primary key: Select Last_insert_rowid ();

(4) Sqlite>.mode col
Sqlite>.headers on
When querying the database, display the number of rows and headers!

(5) In DOS, type CTRL + C, exit database, UNIX, use Ctrl+d

(6) SQLite Master Table Schema
----------------------------------------- ------------------------
name                        Description
----------------------------------------- ------------------------
type          The object ' s type (table, Index, view, trigger)
name          The object ' s name
Tbl_name       The table the object is associated with
rootpage      the Object ' s root page index in the database (where it begins)
sql            The object ' s SQL definition (DDL)

eg.
sqlite>. Mode col
Sqlite>. Headers on
Sqlite> Select Type, name, Tbl_name, SQL from Sqlite_master order by type;
This allows you to see information in all databases, tables, indexes, views, and so on.

(7) Export data
. output [filename], exported to a file, if the file does not exist, is automatically created
. Dump Export Data command
. Output stdout back to the screen (For additional action)

eg.
Sqlite>.output Acuzio.sql
Sqlite>.dump
Sqlite>.output stdout
This allows data to be imported into the acuzio.sql.

(8) Import data
Import data using the. Read command
eg.
Data in the import (7)
Sqlite>.read Acuio.sql

(9) Backing up the database
When you switch to the SQLite folder
Sqlite3 test.db dump > Test.sql
If you are in the database
sqlite>. Output File.sql
sqlite>. Dump
Sqlite>. Exit

(10) Import Database
When you switch to the SQLite folder
Sqlite3 Test.db < Test.sql

(11) Back up the binary format database, vacuum: Release the deleted space (data and tables are deleted, will not be emptied)
Sqlite3 test.db VACUUM
CP test.db Test.backup

(12) Obtaining Database information
If you want to get information about the structure of the physical database, you can download the SQLite Analyzer Tool on the SQLite website
Use: Sqlite3_analyzer test.db

(13) Other SQLite tools
SQLite Database Browser (http://sqlitebrowser.sourceforge.net)
SQLite Control Center (http://bobmanc.home.comcast.net/sqlitecc.html)
Sqlitemanager (www.sqlabs.net/sqlitemanager.php)

(13) SQLite differs from other databases in that it executes statements (;), not (go).

(14) SQLite Note (-) or (/*/)
eg.
--This was a comment on one line
/* This is a comment spanning
Lines */

(15) Creating a table structure

CREATE [temp| TEMPORARY] TABLE table_name (column_definitions [, constraints]);

The keyword temp, temporary indicates that a temporary table was created

(16) There are 5 basic types in SQLite:
Integer/real/text/blob/null

(17) Ensuring uniqueness can be unique with keywords
eg.
CREATE TABLE contacts (ID INTEGER PRIMARY KEY,
Name TEXT not NULL COLLATE nocase,
Phone TEXT not NULL DEFAULT ' UNKNOWN ',
UNIQUE (Name,phone));

(18) Modify the table
ALTER Table Table {RENAME to name | ADD COLUMN Column_def}
eg.
sqlite> ALTER TABLE Contacts
ADD COLUMN Email TEXT not NULL DEFAULT ' ' COLLATE nocase;
sqlite>. Schema Contacts
CREATE TABLE contacts (ID INTEGER PRIMARY KEY,
Name TEXT not NULL COLLATE nocase,
Phone TEXT not NULL DEFAULT ' UNKNOWN ',
Email TEXT not NULL DEFAULT ' ' COLLATE nocase,
UNIQUE (Name,phone));

(19) Enquiry
SELECT DISTINCT heading from tables WHERE predicate
GROUP by columns have predicate
ORDER by columns LIMIT count,offset;

(20) Limit and Offset keywords
Limit refers to the maximum number of rows to return records
Offset refers to how many rows of data are skipped

SQLite Learning Notes (i)