SQLite | SQLite Basic Knowledge | How to Enter SQL Statements and Commands in the Command-Line Tool

Tags:

• SQLite
• Database

In SQLite, you can use the command-line tool to create databases, search data, and perform other operations. This page explains how to enter commands when using the SQLite command-line tool.

Running Commands

To run the SQLite command-line tool, start a command prompt, move to the directory where sqlite3 is installed, and run the following command. The tool starts and connects to the database specified as the argument.

sqlite3 database-name

This time, run the following command to connect to the sample.sqlite3 database created earlier. Even if the previously created file was deleted or does not exist, the connection command itself still works.

$ sqlite3 sample.sqlite3 
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> 

In the command-line tool, you can run SQLite commands that configure the command-line tool, as well as arbitrary SQL statements.

Try running the SQLite command .show. Enter the command, add any required arguments if needed, and press the Enter key to execute it.

$ sqlite3 sample.sqlite3 
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width: 
    filename: sample.sqlite3
sqlite>

Running SQL Statements

Next, run a simple SQL statement. For SQL statements, everything up to the final semicolon (;) is treated as one SQL statement. After entering the semicolon, press Enter to execute it.

sqlite> create table user(id, name);
sqlite> insert into user(id, name) values (1, 'devkuma');
sqlite> insert into user(id, name) values (2, 'araikuma');
sqlite> select * from user;
1|devkuma
2|araikuma
sqlite> 

If the user table created earlier already exists, you do not need to run the first line.

Splitting a Long SQL Statement Across Multiple Lines

A single SQL statement can become long. If you try to enter the entire statement at once, it can be difficult to type or hard to understand how far you have entered.

sqlite> select id as user_id, name as user_name from user where id = 1 and name = 'devkuma' order by name desc;
1|devkuma
sqlite> 

When a statement is long, the command-line tool also allows you to split it across multiple lines. Press Enter while entering the statement.

sqlite> select id as user_id, name as user_name 
   ...> 

SQLite treats everything up to the final semicolon (;) as one statement. If you press Enter before entering the semicolon, SQLite determines that the statement is still being entered and lets you continue.

Now enter the rest of the statement. Press Enter at appropriate points as shown below.

sqlite> select id as user_id, name as user_name 
   ...> from user
   ...> where id = 1 and name = 'devkuma'
   ...> order by name desc
   ...> 

You do not need to add a space before pressing Enter. When you press Enter in the middle of a command, the text before and after it is automatically treated as separate words.

For SQL statements, when you enter a line that contains ; and press Enter, SQLite determines that the statement input is complete and executes the SQL statement.

sqlite> select id as user_id, name as user_name 
   ...> from user
   ...> where id = 1 and name = 'devkuma'
   ...> order by name desc
   ...> ;
1|devkuma
sqlite>

Entering the statement on one line and splitting it across multiple lines both produce the same result, so use whichever style fits the situation.

–

This page explained how to enter commands and SQL statements in the SQLite command-line tool.