SQLite getting started tutorial 1 Basic Console (terminal) commands

I. Basic Introduction

SQLite is a self-contained, serverless, zero-configuration, and transactional relational database engine. Because it is very small, it can also be built into your application as an embedded database. SQLite is used in Solaris 10, Mac OS, iPhone, and Skype. QT4, Python, and PHP all support SQLite by default, and popular applications such as Firefox Amarok also use SQLite internally.

SQLite Database Engine implements the main SQL-92 standard, the engine itself has only one file, the size of less than 300 k, but not as an independent process to run, instead, it is dynamically or statically linked to other applications. The database file it generates is a common disk file, which can be placed in any directory. SQLite is developed in C language and is open-source and cross-platform. It is supported by all mainstream programming languages.

Related Resources

Sqlite.org
Wikipedia.org


Ii. Download and install

For Windows: sqlite-shell-win32-x86-3070701.zip

The command line version is downloaded here, so it is an executable file and a dynamic link library version. If your application requires an embedded database, you can download this version. Of course, you can download the source code and compile it yourself. After the download is complete, extract the file sqlite3.exe, which can be placed in any PATH, and then add the PATH to the PATH environment variable, in this way, you can run the SQLite command line tool on the console at any time.

III. Basic commands

1. Enter the command line environment: sqlite3
Open a console window and enter sqlite3 and press Enter. Then you enter the SQLite command line environment,

It displays the version number and tells you that each SQL statement must end with a semicolon.

2. Command Line help:. help
In the command line environment, enter. help and press enter to display all available commands and the help of these commands. Note: All commands start with a vertex.

3. Exit the command line Environment
You can exit either. quit or. exit.

4. Database and table commands

1. Create a new database: sqlite3 file name

Create a Db directory, create a test. Db database file in the db directory, and open the console window. The command is as follows:

Copy codeThe Code is as follows:
Mkdir Db
Cd Db
Sqlite3 test. db

2. Open an existing database: sqlite3 file name

Creating a new database is the same as opening an existing database command. If the file does not exist in the current directory, it is created. If yes, it is opened.

3. import data:. read data file

Open notepad, copy the following SQL statements to notepad, save as test. SQL to the Db directory mentioned above, and enter

. Read test. SQL

Import all data to the test. db database.

Test. db import data

Copy codeThe Code is 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, 'volv', 29000 );
Insert into Cars VALUES (5, 'bentlil', 350000 );
Insert into Cars VALUES (6, 'citroen', 21000 );
Insert into Cars VALUES (7, 'hummer ', 41400 );
Insert into Cars VALUES (8, 'volk', 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, "William ");
Insert into Orders (OrderPrice, Customer) VALUES (200, "Robert tson ");
Insert into Orders (OrderPrice, Customer) VALUES (40, "Robert tson ");
Insert into Orders (OrderPrice, Customer) VALUES (1640, "Smith ");
Insert into Orders (OrderPrice, Customer) VALUES (100, "Robert tson ");
Insert into Orders (OrderPrice, Customer) VALUES (50, "William ");
Insert into Orders (OrderPrice, Customer) VALUES (150, "Smith ");
Insert into Orders (OrderPrice, Customer) VALUES (250, "Smith ");
Insert into Orders (OrderPrice, Customer) VALUES (840, "Brown ");
Insert into Orders (OrderPrice, Customer) VALUES (440, "Black ");
Insert into Orders (OrderPrice, Customer) VALUES (20, "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, 'elastic', '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 MERs (CustomerId integer primary key, Name text );
Insert into MERs (Name) VALUES ('Paul novak ');
Insert into MERs (Name) VALUES ('terry Neils ');
Insert into MERs (Name) VALUES ('Jack fonda ');
Insert into MERs (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, '2017-22-11 ');
Insert into Reservations (CustomerId, Day) VALUES (2, '2017-28-11 ');
Insert into Reservations (CustomerId, Day) VALUES (2, '2017-29-11 ');
Insert into Reservations (CustomerId, Day) VALUES (1, '2017-29-11 ');
Insert into Reservations (CustomerId, Day) VALUES (3, '2017-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 Books VALUES (1, 'War and Peace ', 'Leo tolstoy', '2014-978 ');
Insert into Books VALUES (2, 'the Brothers Karamazov ',
'Fyodor dostoyevsky', '2017-1000 ');
Insert into Books VALUES (3, 'Crime and Punishment ',
'Fyodor dostoyevsky', '2017-1000 ');
COMMIT

4. list all data tables: . Tables

After completing all the above work, we can list all the data tables.

5. display the database structure:. Schema

It is actually some SQL statements that describe the structure of the database,

6. display table structure:. SchemaTable Name

7. Export the data of a table:. DumpTable Name

Now we can see that the entire table is exported in the form of an SQL statement, but it is only displayed on the terminal. How can we export it to a file?

8. Set the export target:

. Output file name
Or
. Output stdout
Run. output cars. SQL first, and then run the. dump command? Run. output stdout

V. Data Display commands

1. Set the separator:. separator

We can run SELECT * FROM Names; first, and we can see that the default Delimiter is |
Run. separator: Later, SELECT * FROM Names;. You can see that the separator has changed:

2. Set the display mode:. ModeMode

There are several display modes, the default is the list display mode, we generally use the column Display mode, there are several other display modes to view the mode-related content. help. Look at the figure below. Is it different from the one shown above?

3. display the title bar:. HeadersOn

Look, isn't it the same?

4. Set the display width of each column:. WidthW1, w2, w3 .........

Some content, the default width cannot be displayed, this command is useful

5. Set the NULL value to what it looks like:. NullvalueThe format of the NULL value you want

By default, the NULL value is not displayed. You can set it to what you want.

6. List the current display format settings:. Show

7. Configuration File. Sqliterc

If we need to reset the display format every time we enter the command line, it is very troublesome. all settings listed by the show command can be saved to one. in the sqliterc file, it is automatically set every time you enter the command line .. The sqlterc file is stored in the Home directory of the user in Linux and can be saved to any directory in Windows. However, you need to set the environment variable so that the database engine can find it, for more information, see help.