The use of SQLite one

1, download SQLite from the official website after installation, open the terminal change directory to the directory where the Sqlite3 file is located, enter the following command to create a database, if the database already exists will open:

sqlite3 test.db
    (If the following message is displayed, SQLite has been successfully installed :)

MacBook-Pro-MD313: SQL mac $ sqlite3 test.db
SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>


    Then create a table:

sqlite> create table mytable (id integer primary key, value text);
    General data uses a fixed static data type, while SQLite uses a dynamic data type, which is automatically determined based on the stored value. SQLite has the following five data types:

    1.NULL: Null value.
    2.INTEGER: Integer with a sign, depending on the size of the range of stored numbers
    3.REAL: Floating point number, stored as 8-byte IEEE floating point number.
    4.TEXT: string text.
    5.BLOB: binary object.


    But in fact, sqlite3 also accepts the following data types:
     smallint 16-bit integer.
     interger 32-bit integer.
     decimal (p, s) p is an exact value and a decimal integer in the size of s. The exact value p refers to all digits and s refers to the number of digits after the decimal point. If not specified, the system will be set to p = 5; s = 0.
     float 32-bit real number.
     double 64-bit real number.
     char (n) A string of length n, where n cannot exceed 254.
     varchar (n) A string with a fixed length and a maximum length of n. n cannot exceed 4000.
     graphic (n) is the same as char (n), but its unit is two characters double-bytes, n cannot exceed 127. This form is to support two-character fonts, such as Chinese characters.
     vargraphic (n) A variable-length double-character string with a maximum length of n, where n cannot exceed 2000
     date contains the year, month, and date.
     time contains hours, minutes, and seconds.
     timestamp contains year, month, day, hour, minute, second, thousandth of a second.

    datetime contains the datetime format and must be written as ‘2010-08-05’ and not ‘2010-8-5’, otherwise an error will occur when reading!

    

    Insert data:

sqlite> insert into mytable (id, value) values (1, ‘Jacedy’);


    Query data:

sqlite> select * from mytable;


    Create the view:

sqlite> create view nameview as select * from mytable;


    Create the index:

sqlite> create index testIdx on mytable (value);


2.Some commonly used SQLite commands

    Show table structure:

sqlite> .schema [table]
    Get all tables and views:

sqlite> .tables
    Get the index list of the specified table:

sqlite> .indices [table]
    Export data to SQL file:

sqlite> .output [filename]
sqlite> .dump
sqlite> .output stdout
    Import database from SQL file:

sqlite> .read [filename]
    Format output data to CSV format:

sqlite> .output [filename.csv]
sqlite> .separator,
sqlite> select * from test;
sqlite> .output stdout
    Import data from a CSV file into a table:

sqlite> create table newtable (id integer primary key, value text);
sqlite> .import [filename.csv] newtable
    backup database:

/ * usage: sqlite3 [database] .dump> [filename] * /
sqlite3 mytable.db .dump> backup.sql
    Restore the database:

/ * usage: sqlite3 [database] <[filename] * /
sqlite3 mytable.db <backup.sql
    List (in the current database file) the names and files of all attached databases:

.databases
    exit the program:

.quit
//.exit
    Show help:

.help
    Show current settings:

.show
    List all table names:

.tables


Use of SQLite