SQLite Introductory Tutorials A basic console (terminal) command _sqlite

first, the basic introduction

SQLite is a self-sustaining (self-contained), server-free, 0-configured, transactional-type relational database engine. Because he is very small, it can also be built into your application as an embedded database. SQLite is used in the Solaris 10 operating system, MAC OS operating system, IPhone and Skype. QT4, Python, PHP support SQLite by default, and popular applications such as Firefox Amarok also use SQLite internally.

The SQLite database engine implements the main SQL-92 standard, the engine itself has only one file, size less than 300k, but is not running as a stand-alone process, but is dynamically or statically linked to other applications. The database file it generates is a normal disk file that can be placed in any directory. SQLite itself is a C language development, open source is also cross-platform, and is supported by all the mainstream programming languages.

Related Resources

sqlite.org
Wikipedia.org

Second, download and install

The download address for Windows version is: Sqlite-shell-win32-x86-3070701.zip

We download the command-line version here, so it's an executable file and a dynamic link library version that you can download if your application requires an embedded database. Of course, if you are willing to toss, download the source code itself can be compiled. Download complete, extract it out on a file: Sqlite3.exe, can be placed under any one path, and then add this path to the PATH environment variable, so we can run the SQLite command-line tool in the console at any time.

iii. Basic Orders

1, into the command line environment: Sqlite3
Open a console window, enter sqlite3 carriage return, then you enter the SQLite command line environment, as shown

It displays the version number and tells you that each SQL statement must be separated by a semicolon;

2, command line help:.
Enter. Help carriage return under the command line environment, showing all available commands and assistance with these commands. Note: All commands begin with a point

3, exit the command line environment
. Quit or. Exit can be withdrawn

Iv. Database and table-related commands

1. Create a new database: sqlite3 filename

First set up a DB directory, and create a test.db database file in the DB directory, open the console window, the command is as follows:

Copy Code code as follows:

mkdir Db
CD Db
Sqlite3 test.db

2. Open a database that already exists: Sqlite3 file name already exists

Creating a new database is exactly the same as opening a database command that already exists, or if the file does not exist in the current directory, new, or open if it exists.

3, Import data:. read Data file

Open Notepad and copy the following SQL statements into Notepad, save as Test.sql to the Db directory mentioned above, and enter in the command line environment

. Read Test.sql

Import all the data into the TEST.DB database.

TEST.DB Import Data

Copy Code code as follows:

BEGIN TRANSACTION;
CREATE TABLE Cars (Id integer PRIMARY KEY, Name text, cost integer);
INSERT into Cars VALUES (1, ' Audi ', 52642);
INSERT into Cars VALUES (2, ' Mercedes ', 57127);
INSERT into Cars VALUES (3, ' Skoda ', 9000);
INSERT into Cars VALUES (4, ' Volvo ', 29000);
INSERT into Cars VALUES (5, ' Bentley ', 350000);
INSERT into Cars VALUES (6, ' Citroen ', 21000);
INSERT into Cars VALUES (7, ' Hummer ', 41400);
INSERT into Cars VALUES (8, ' Volkswagen ', 21600);
COMMIT;

BEGIN TRANSACTION;
CREATE TABLE Orders (Id integer PRIMARY KEY, orderprice integer CHECK (orderprice>0),
Customer text);
INSERT into Orders (Orderprice, Customer) VALUES (1200, "Williamson");
INSERT into Orders (Orderprice, Customer) VALUES ("Robertson");
INSERT into Orders (Orderprice, Customer) VALUES ("Robertson");
INSERT into Orders (Orderprice, Customer) VALUES (1640, "Smith");
INSERT into Orders (Orderprice, Customer) VALUES ("Robertson");
INSERT into Orders (Orderprice, Customer) VALUES ("Williamson");
INSERT into Orders (Orderprice, Customer) VALUES ("Smith");
INSERT into Orders (Orderprice, Customer) VALUES ("Smith");
INSERT into Orders (Orderprice, Customer) VALUES (840, "Brown");
INSERT into Orders (Orderprice, Customer) VALUES (440, "Black");
INSERT into Orders (Orderprice, Customer) VALUES ("Brown");
COMMIT;

BEGIN TRANSACTION;
CREATE TABLE Friends (Id integer PRIMARY KEY, Name text UNIQUE not NULL,
Sex text CHECK (Sex in (' M ', ' F '));
INSERT into Friends VALUES (1, ' Jane ', ' F ');
INSERT into Friends VALUES (2, ' Thomas ', ' M ');
INSERT into Friends VALUES (3, ' Franklin ', ' M ');
INSERT into Friends VALUES (4, ' Elisabeth ', ' F ');
INSERT into Friends VALUES (5, ' Mary ', ' F ');
INSERT into Friends VALUES (6, ' Lucy ', ' F ');
INSERT into Friends VALUES (7, ' Jack ', ' M ');
COMMIT;

BEGIN TRANSACTION;
CREATE TABLE IF not EXISTS Customers (CustomerId integer PRIMARY KEY, Name text);
INSERT into Customers (Name) VALUES (' Paul Novak ');
INSERT into Customers (Name) VALUES (' Terry neils ');
INSERT into Customers (Name) VALUES (' Jack Fonda ');
INSERT into Customers (Name) VALUES (' Tom Willis ');

CREATE TABLE IF not EXISTS reservations (Id integer PRIMARY KEY,
CustomerId integer, day text);
INSERT into reservations (CustomerId, Day) VALUES (1, ' 2009-22-11 ');
INSERT into reservations (CustomerId, Day) VALUES (2, ' 2009-28-11 ');
INSERT into reservations (CustomerId, Day) VALUES (2, ' 2009-29-11 ');
INSERT into reservations (CustomerId, Day) VALUES (1, ' 2009-29-11 ');
INSERT into reservations (CustomerId, Day) VALUES (3, ' 2009-02-12 ');
COMMIT;

BEGIN TRANSACTION;
CREATE TABLE Names (Id integer, Name text);
INSERT into Names VALUES (1, ' Tom ');
INSERT into Names VALUES (2, ' Lucy ');
INSERT into Names VALUES (3, ' Frank ');
INSERT into Names VALUES (4, ' Jane ');
INSERT into Names VALUES (5, ' Robert ');
COMMIT;

BEGIN TRANSACTION;
CREATE TABLE Books (Id integer PRIMARY KEY, Title text, Author text,
ISBN text default ' not available ');
INSERT into the books VALUES (1, ' War and Peace ', ' Leo Tolstoy ', ' 978-0345472403 ');
INSERT into Books VALUES (2, ' The Karamazov ',
' Fyodor Dostoyevsky ', ' 978-0486437910 ');
INSERT into the books VALUES (3, ' Crime and Punishment ',
' Fyodor Dostoyevsky ', ' 978-1840224306 ');
COMMIT

4, list all the data tables: . Tables

With all the above work done, we'll be able to list all the data tables.

5, display database structure:. Schema

is actually a few SQL statements, they describe the structure of the database, as shown in

6, display the structure of the table:. Schema table name

7, export the data of a table: . Dump table name

At this point, we can see that the entire table is exported in the form of an SQL statement, but only on the terminal, how do you export it to a file?

8. Set Export target:

. output file Name
Or
. Output stdout
Run. Output cars.sql before running. dump command try it? If you want to revert to exporting to the terminal (standard output), run. Output stdout

v. Data display related commands

1, set separator:. Separator separator

We can run the SELECT * from Names First, and we can see that the default delimiter is |
Run. Separator: Later, SELECT * from Names, and you can see that the separator has become:

2, set the display mode:. mode mode

There are several display modes, the default is List display mode, in general we use column display mode, there are several other display modes. help see mode related content. Look at the picture below, is it different from what is shown above?

3, display title bar:. Headers on

Look, is it not the same?

4, set the display width of each column:. Width w1,w2,w3 ...

Some content, the default width does not appear, this command is useful

5, set the null value to show what it looks like: . Nullvalue You want the null value format

By default, null values do not show anything, you can set it to the way you want it

6, list the current display format settings:. Show

7, configuration file . Sqliterc

If we want to reset the display format every time we go to the command line, it's a hassle, actually. All the settings items listed in the show command can be saved to a. sqliterc file so that each time you enter the command line, it is automatically set up. sqlterc files are saved under Linux in the user's home Directory, under Windows can be saved to any directory, but need to set environment variables so that the database engine can find it, interested to see help.