Simple use of SQLite

[Root @ glnode04 ~] # Sqlite3 test. DB: Create the database file test. DB in the current directory. If the database file exists, open the database file.
SQLite version 3.3.6
Enter ". Help" for instructions
SQLite>. Help // The command does not need to end with a semicolon.
. Databases list names and files of attached Databases
. Dump? Table? ... Dump the database in an SQL text format
. Echo on | off turn command echo on or off
. Exit exit this program
. Explain on | off turn output mode suitable for explain on or off.
. Header (s) on | off turn display of headers on or off
. Help show this message
. Import file table import data from file into table
. Indices table show names of all indices on table
. Mode 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 for table
Line one value per line
List values delimited by. Separator string
Tabs tab-separated values
TCL list elements
. Nullvalue string print string in place of null values
. Output Filename send output to filename
. Output stdout send output to the screen
. Prompt main continue Replace the standard prompts
. Quit exit this program
. Read filename Execute SQL in Filename
. Schema? Table? Show the create statements
. Separator string change Separator Used by output mode and. Import
. Show show the current values for various settings
. Tables? Pattern? List names of tables matching a like pattern
. Timeout MS try opening locked tables for MS milliseconds
. Width num... Set column widths for "column" Mode
SQLite>. databases;
Unknown command or invalid arguments: "databases;". Enter ". Help" for help
SQLite>. Databases
SEQ name file
----------------------------------------------------------------------------
0 main/root/test. DB

SQLite> drop table test; // use a semicolon to end the SQL statement.
SQLite> Create Table Test (ID varchar (20) primary key, name nvarchar (20) Unique );
SQLite> insert into test values ('20140901', 'zhankunlin ');
SQLite> insert into test values ('20140901', 'zhankunlin ');
SQL error: column name is not unique // The primary key constraint is violated.
SQLite> insert into test values ('20140901', 'zhankunlin ');
SQL error: column name is not unique
SQLite> insert into test values ('20170101', 'zhaoyanhong ');
SQLite> select * from test;
1234 | zhankunlin
123 | zhaoyanhong
SQLite>. schema test // View table Definitions
Create Table Test (ID varchar (20) primary key, name nvarchar (20) Unique );
SQLite>. Tables
Test
SQLite>. Databases // view the current database
SEQ name file
----------------------------------------------------------------------------
0 main/root/test. DB

1 temp/var/tmp/sqlite_qrnxvqclzkc6lwo

For more use, refer to: http://www.cnblogs.com/hnrainll/archive/2011/04/22/2024627.html

Use of SQLite command line tool

Recently, SQLite is widely used. The more you get fascinated with it, the more you write at work or by yourself. As long as you have data storage, you first think of SQLite, I have always been disgusted with data storage in the C/S mode. I remember that I used XML to store my data for a long time before I knew SQLite, I like to store data in a single file. I can carry data to my body at any time, and they are compatible with each other and can be used wherever I hold data files, even in an embedded system...

SQLite features

1. Acid transactions
2. Zero Configuration-no installation or configuration management required
3. A complete database stored in a single disk file
4. Database files can be freely shared among machines with different byte sequences.
5. Supports database size up to 2 TB
6. Small enough, roughly 30 thousand lines of C code, 250 K
7. Faster than some popular databases in most common databases
8. Simple and Easy APIs
9. Includes TCL binding, and supports binding in other languages through wrapper.
10. Source code with good comments and more than 90% test coverage
11. Independent: no additional dependencies
12. Source completely open, you can use it for any purpose, including selling it
13. Supports multiple development languages, including C, PHP, Perl, Java, ASP. NET, and python.

SQLite Installation
Now there are ready-made software packages available for installation in all major Linux versions, and most of the systems are self-contained. Check whether the system is running or not.
$sqlite3
Non-Linux system: http://www.sqlite.org/download.htmldownload Installation
Next we will use the SQLite command line tool.

Open or create a database
$sqlite3 test.db3
In this way, you can open or create a new database file.
$sqlite3 test.db3
SQLite version 3.6.23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> 
It looks like this.

SQLite built-in commands
In addition to executing SQL statements, it also provides a set of built-in commands, which are.For example, to view the help information. HelpExit is. ExitAnd. Quit

Create a table
Most of the SQL statements in the command line are supported. Now we want to create two tables.

sqlite>create table Artists (
--->ArtistID INTEGER PRIMARY KEY,
--->ArtistName TEXT);
SQLite is not case sensitive to SQL statements.
sqlite>create table CDs (
--->CDID INTEGER PRIMARY KEY,
--->ArtistID INTEGER NOT NULL,
--->Title TEXT NOT NULL,
--->Date TEXT);

Note that all other fields except integer primary key can be non-typed, that is, they can store any data without declaring or declaring them.

Insert data
sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Peter Gabriel');
sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Bruce Hornsby');
sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Lyle Lovett');
sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Beach Boys');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,'So','1984');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,'Us','1992');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,2,'The Way It Is','1986');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,2,'Scenes from the Southside','1990');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,'Security','1990');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,3,'Joshua Judges Ruth','1992');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,4,'Pet Sounds','1966');
SQLite supports importing SQL files by using built-in commands.. ReadFor example, we can create an SQL file named insert_table. SQL using the preceding command.

Insert into artists (artistid, artistname) values (null, 'Peter Gabriel ');
Insert into artists (artistid, artistname) values (null, 'Bruce hornsby ');
Insert into artists (artistid, artistname) values (null, 'lyle lovet ');
Insert into artists (artistid, artistname) values (null, 'beach boys ');
Insert into CDs (cdid, artistid, title, date) values (null, 1, 'so', '123 ′);
Insert into CDs (cdid, artistid, title, date) values (null, 1, 'us', '123 ′);
Insert into CDs (cdid, artistid, title, date) values (null, 2, 'the way it is ', '123 ′);
Insert into CDs (cdid, artistid, title, date) values (null, 2, 'scenes from the Southside ', '123 ′);
Insert into CDs (cdid, artistid, title, date) values (null, 1, 'security', '123 ′);
Insert into CDs (cdid, artistid, title, date) values (null, 3, 'joshua judges Ruth ', '200 ′);
Insert into CDs (cdid, artistid, title, date) values (null, 4, 'Pet sounds ', '123 ′);

Then run
sqlite>.read insert_table.sql

Table query
Let's take a look at the content in the two tables. Execute
sqlite>select * from Artists; 
And
sqlite>select * from CDs;
If you want to view the table header at the same time, open the headers option before running the query statement.
sqlite>.headers ON
The output result looks like this.


ArtisID|ArtistName
1 |Peter Gabriel
2 |Bruce Hornsby
3 |Lyle Lovett
4 |Beach Boys

And


CDID|ArtisID|Title |Date
1 |1 |So |1984
2 |1 |Us |1992
3 |2 |The Way It Is |1986
4 |2 |Scenes from the Southside|1990
5 |1 |Security |1990
6 |3 |Joshua Judges Ruth |1992
7 |4 |Pet Sounds |1966

Other query statements
sqlite>SELECT Title AS AlbumName FROM CDs;

sqlite>SELECT Title FROM CDs WHERE Date>=1990 ORDER BY Title;

sqlite>SELECT Date FROM CDs;

sqlite>SELECT DISTINCT Date FROM CDs;

sqlite>SELECT Title FROM CDs GROUP BY ArtistID;

Multi-Table query
Run
sqlite>SELECT t1.ArtistName,CDs.Title FROM Artists t1, CDs WHERE t1.ArtistID=CDs.ArtistID
Result
ArtistName |Title
Peter Gabriel|So
Peter Gabriel|Us
Peter Gabriel|Security
Bruce Hornsby|The Way It Is
Bruce Hornsby|Scenes from the Southside
Lyle Lovett |Joshua Judge Ruth
Beach Boys |Pet Sounds

Update Fields
Insert a data entry
sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Supernatural');
If you want to change the name of a singer to Santana
sqlite>UPDATE Artists SET ArtistName ='Santana' WHERE ArtistID=5;
You can.

Delete Field
Run
sqlite>select * FROM CDs WHERE Title LIKE 'Super%';
Check whether the data you want to delete is executed.
sqlite>DELETE FROM CDs WHERE Title LIKE 'Super%'; 
Run again
sqlite>select * FROM CDs WHERE Title LIKE 'Super%';
Check whether it has been deleted?

If the above command line is not intuitive and efficient enough, and you like to use the mouse a lot, we recommend that you install the SQLite manager Firefox extension, which is really very convenient.