Reading Data

Getting data out of your database

Backup to a text file

Once you deploy a site, most of your interactions
will need to be via the command line.

Text files can also be backed up using version control.

Open and connect database in SQLite Studio

Right click the database in the sidebar

Click export

Select all and tick export data from tables

Continue

Format as SQL and choose file destination

Click Done

Backup to text file using shell

Open database in shell

Set the output

.output message-board-backup.sql

Dump the database

.dump

Quit sqlite

.quit

Check file in text editor

Look for your table names and check
that the data appears to be there.

Creating table reports

Set the output mode

.mode csv

Set the output file name

.output user-report.txt

Execute a table query

SELECT * FROM users;

Check the report file

Open the user-report.txt in your editor
and check that the information is listed.

Challenge: Create a messages report

Generate a report containing all messages
and output it to messages-report.txt

Creating simplified table reports

You can choose to select just some columns
from a table’s data, keep it relevant!

Set the output mode

.mode csv

Set the output file name

.output user-list.txt

Execute a table query

SELECT first_name, last_name, email_address FROM users;

Creating complex linked reports

Often you’ll need to create detailed reports
which combine data from multiple tables.

There are three ways to combine table data

  • Inner Join
    Most common

  • Outer Join

  • Cross Join
    Rarely used

Inner Joins

Combines data from two tables by
matching up the values in a column.

Using an Inner Join

SELECT *
FROM messages INNER JOIN users
USING (user_id);

Challenge: Message and username

Modify the query to display every message
and the first name of the user who created it.

"Hello from tanya",Tanya
"Hello back, from Sarah",Sarah
"Woof woof",Pascal

Outer Join

Combines data from two tables by displaying
all data from the first and any relevant matches
from the second.

Using an outer join

SELECT first_name, content
FROM users LEFT OUTER JOIN messages
USING (user_id);

Saving complex reports to file

Set the output mode

.mode column

Turn on headers

.headers on

Set the output file name

.output messages-list.txt

Execute a query

PRINT 
SELECT first_name, last_name 
FROM users
WHERE first_name = "Tanya";

This query will write to the file.

Execute another query

SELECT *

Execute a table query

SELECT first_name, last_name, email_address 
FROM users;

Thumbs Up!

Reading Data: Complete!

Take me to the next chapter!

Loading...