How to install SQLite in Terminal

1. Go to https://sqlite.org/index.html and download it through package. Or use homebrew

brew install sqlite

2. Install package languageserver in R. (It will go forever in terminal)

R #go to R in terminal

install.packages("languageserver")

3. Install radian (requires R and Python3)

(You probably need to add a new path)

Some commands

Firstly, you have to pick a folder you want to use, then you can create a database file such as data.db by following code:

sqlite3 data.db

To create a table named "users":

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100)
);

Import Data from CSV Files

If you want to want to import data from csv files by SQLite, you have to set the csv mode:

.mode csv

Now for example, you want to import the data from your csv_file.csv to users tables in database.db file:

.import /path/to/your/csv_file.csv users

If you don't know the path toward your files, you can go to the directory in terminal and use the following code to get the path:

pwd

Query data form the table:

SELECT * FROM data;

To know how many rows of data in your database:

SELECT COUNT(*) FROM data;

Export data as CSV file

Open the database using SQlite

sqlite3 data.db

The following code allows you to explort the data as csv:

.mode csv

To create a empty csv file which will be used to store the selected data later:

.output your_exported_data.csv

If you want to export all data in database(this process just select data):

SELECT * FROM your_table_name;

Now export the selected data to your csv file:

.output stdout

Insert New Data

Check the meta infomation of the data

PRAGMA table_info(your_table_name);

INSERT INTO users (name, age) VALUES ('John Doe', 30);