SQL is the Structured Query Language for reading and writing database files.
The Android version of SQL is called
SQLite.
The SQLite documentation is in the form of
railroad diagrams.
For an example,
see
select.
The command line shell for SQLite is a program named
sqlite3.
It lets us type SQL commands interactively.
sqlite3
documentation on Android website (short)
sqlite3
documentation on SQLite website (long)
sqlite3
documentation on Mac OS X man pages website (shortish)
In this document,
we will begin by running
sqlite3
on the Desktop of your Mac or PC.
You can also run
sqlite3
on your Android device or emulator.
When we installed
Android Studio,
we put
sqlite3
into the directory
~/Library/Android/sdk/platform-tools,
and we put the name of this directory into our
PATH
environment
variable.
(The tilde
~
stands for the full pathname of your home directory.
For example, the full pathname of my home directory on my Mac is
/Users/mark.)
We should therefore be able to give the
sqlite3
command in our Mac terminal window or in our Windows
cmd.exe
window.
which
tells us which version of
sqlite3
would run if we went ahead and typed an
sqlite3
command.
whereis sqlite3
/usr/bin/sqlite3
sqlite3 -version
3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace
which sqlite3
/Users/myname/Library/Android/sdk/platform-tools/sqlite3
sqlite3 -version
3.8.10.2 2015-05-20 18:17:19 2ef4f3a5b1d1d0c4338f8243d40a2452cc1f7fe4
echo $PATH
/Users/myname/Library/Android/sdk/tools:/Users/myname/Library/Android/sdk/platform-tools:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin
echo $PATH | tr : '\n' | cat -n
1 /Users/myname/Library/Android/sdk/tools
2 /Users/myname/Library/Android/sdk/platform-tools
3 /usr/local/bin
4 /usr/bin
5 /bin
6 /usr/sbin
7 /sbin
sqlite3 -help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
-ascii set output mode to 'ascii'
-bail stop after hitting an error
-batch force batch I/O
-column set output mode to 'column'
-cmd COMMAND run "COMMAND" before reading stdin
-csv set output mode to 'csv'
-echo print commands before execution
-init FILENAME read/process named file
-[no]header turn headers on or off
-help show this message
-html set output mode to HTML
-interactive force interactive I/O
-line set output mode to 'line'
-list set output mode to 'list'
-lookaside SIZE N use N entries of SZ bytes for lookaside memory
-mmap N default mmap size set to N
-newline SEP set output row separator. Default: '\n'
-nullvalue TEXT set text string for NULL values. Default ''
-pagecache SIZE N use N slots of SZ bytes each for page cache memory
-scratch SIZE N use N slots of SZ bytes each for scratch memory
-separator SEP set output column separator. Default: '|'
-stats print memory stats before each finalize
-version show SQLite version
-vfs NAME use NAME as the default VFS
The name of our database file will be
~/Desktop/database.db.
cd ~/Desktop
pwd
ls -l
sqlite3 database.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dbinfo ?DB? Show status information about the database
.dump ?TABLE? ... Dump the database in an SQL text format
If TABLE specified, only dump tables matching
LIKE pattern TABLE.
.echo on|off Turn command echo on or off
.eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN
.exit Exit this program
.explain ?on|off? Turn output mode suitable for EXPLAIN on or off.
With no args, it turns EXPLAIN on.
.fullschema Show schema and the content of sqlite_stat tables
.headers on|off Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indexes ?TABLE? Show names of all indexes
If TABLE specified, only show indexes for tables
matching LIKE pattern TABLE.
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode where MODE is one of:
ascii Columns/rows delimited by 0x1F and 0x1E
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator strings
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Use STRING in place of NULL values
.once FILENAME Output for the next SQL command only to FILENAME
.open ?FILENAME? Close existing database and reopen FILENAME
.output ?FILENAME? Send output to FILENAME or stdout
.print STRING... Print literal STRING
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILENAME Execute SQL in FILENAME
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.save FILE Write in-memory database into FILE
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?TABLE? Show the CREATE statements
If TABLE specified, only show tables matching
LIKE pattern TABLE.
.separator COL ?ROW? Change the column separator and optionally the row
separator for both the output mode and .import
.shell CMD ARGS... Run CMD ARGS... in a system shell
.show Show the current values for various settings
.stats on|off Turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables
If TABLE specified, only list tables matching
LIKE pattern TABLE.
.timeout MS Try opening locked tables for MS milliseconds
.timer on|off Turn SQL timer on or off
.trace FILE|off Output each SQL statement as it is run
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set column widths for "column" mode
Negative values right-justify
sqlite> .databases
main: /Users/myname/Desktop/database.db
A
table
is a rectangle made of rows and columns.
The columns are things like lastname, firstname, social security number.
The
datatype
of each column could be
text,
integer (a number without a decimal point or fraction),
real (a number with a decimal point and fraction),
etc.
text
might be called
varchar
in other versions of SQL.
The rows are the records (Moe, Larry, Curly, and Shemp).
The name of our table will be
people.
Each of the commands
drop
table
and
create
table
is two separate words with a space between them.
Don’t forget the semicolon at the end of every SQL statement.
(The statements that began with a dot are not SQL statements,
and do not need a semicolon.
They are the “dot commands” listed under
Special
commands to sqlite3 (dot-commands).)
The sqlite3 continuation prompt is
…>.
Try control-p to go back to the previous line.
sqlite> drop table if exists people;
sqlite> create table people (
lastname text,
firstname text,
ss integer
);
sqlite> .tables
people
sqlite> .schema people
CREATE TABLE people (
lastname text,
firstname text,
ss integer
);
select
means “print”.
sqlite> insert into people (lastname, firstname, ss) values ('Washington', 'George', 000000001);
sqlite> insert into people (lastname, firstname, ss) values ('Adams', 'John', 000000002);
sqlite> insert into people values ('Jefferson', 'Thomas', 000000003);
sqlite> select count(*) from people;
3
The
*
in the following
select
statement
means “all the fields in each selected record”.
By default,
every record in the table will be selected.
sqlite> select * from people;
Washington|George|1
Adams|John|2
Jefferson|Thomas|3
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE people (
lastname text,
firstname text,
ss integer
);
INSERT INTO "people" VALUES('Washington','George',1);
INSERT INTO "people" VALUES('Adams','John',2);
INSERT INTO "people" VALUES('Jefferson','Thomas',3);
COMMIT;
I created a file named
presidents.csv
containing comma-separated values.
It actually contains the presidencies, not the presidents,
since
Grover Cleveland
appears twice.
Use your browser to save
this file onto your Mac or PC Desktop as
presidents.csv,
not
presidents.csv.txt.
Washington,George,000000001 Adams,John,000000002 Jefferson,Thomas,000000003 Madison,James,000000004 Monroe,James,000000005 Adams,John,000000006 Jackson,Andrew,000000007 Van Buren,Martin,000000008 Harrison,William,000000009 Tyler,John,000000010 Polk,James,000000011 Taylor,Zachary,000000012 Fillmore,Millard,000000013 Pierce,Franlin,000000014 Buchanan,James,000000015 Lincoln,Abraham,000000016 Johnson,Andrew,000000017 Grant,Ulysses,000000018 Hayes,Rutherford,000000019 Garfield,James,000000020 Arthur,Chester,000000021 Cleveland,Grover,000000022 Harrison,Benjamin,000000023 Cleveland,Grover,000000024 McKinley,William,000000025 Roosevelt,Theodore,000000026 Taft,William,000000027 Wilson,Woodrow,000000028 Harding,Warren,000000029 Coolidge,Calvin,000000030 Hoover,Herbert,000000031 Roosevelt,Franklin,000000032 Truman,Harry,000000033 Eisenhower,Dwight,000000034 Kennedy,John,000000035 Johnson,Lyndon,000000036 Nixon,Richard,000000037 Ford,Gerald,000000038 Carter,Jimmy,000000039 Reagan,Ronald,000000040 Bush,George,000000041 Clinton,Bill,000000042 Bush,George,000000043 Obama,Barack,000000044
Then give the following
.import
command.
sqlite> delete from people; --every row
sqlite> select count(*) from people;
0
sqlite> .tables
people
sqlite> .mode csv
sqlite> .show
mode: csv
sqlite> .import presidents.csv people
sqlite> select count(*) from people;
44
sqlite> .headers on
sqlite> .mode column
sqlite> .show
sqlite> select * from people;
lastname firstname ss
---------- ---------- ----------
Washington George 1
Adams John 2
Jefferson Thomas 3
Madison James 4
etc.
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE people (
lastname text,
firstname text,
ss integer
);
INSERT INTO "people" VALUES('Washington','George',1);
INSERT INTO "people" VALUES('Adams','John',2);
INSERT INTO "people" VALUES('Jefferson','Thomas',3);
INSERT INTO "people" VALUES('Madison','James',4);
etc.
COMMIT;
sqlite> .headers on sqlite> .mode column sqlite> .show sqlite> select * from people; lastname firstname ss ---------- ---------- ---------- Washington George 1 Adams John 2 Jefferson Thomas 3 Madison James 4 etc. sqlite> select ss, firstname from people; ss firstname ---------- ---------- 1 George 2 John 3 Thomas 4 James etc. sqlite> select rowid, ss, firstname, lastname, firstname from people; rowid ss firstname lastname firstname ---------- ---------- ---------- ---------- ---------- 1 1 George Washington George 2 2 John Adams John 3 3 Thomas Jefferson Thomas 4 4 James Madison James etc.
sqlite> .headers off sqlite> .separator | sqlite> .mode list sqlite> .show sqlite> select * from people; Washington|George|1 Adams|John|2 Jefferson|Thomas|3 etc. sqlite> select * from people where ss > 10; Polk|James|11 Taylor|Zachary|12 Fillmore|Millard|13 etc. sqlite> select * from people where ss % 2 = 0; Adams|John|2 Madison|James|4 Adams|John|6 Van Buren|Martin|8 Tyler|John|10 etc. sqlite> select * from people where ss % 2 = 1; Washington|George|1 Jefferson|Thomas|3 Monroe|James|5 Jackson|Andrew|7 Harrison|William|9 etc. sqlite> select * from people where firstname = 'James'; Madison|James|4 Monroe|James|5 Polk|James|11 Buchanan|James|15 Garfield|James|20 sqlite> select * from people where lastname = 'Roosevelt'; Roosevelt|Theodore|26 Roosevelt|Franklin|32
%
is a wildcard.
sqlite> select * from people where firstname like 'J%'; Adams|John|2 Madison|James|4 Monroe|James|5 etc. sqlite> select firstname from people where firstname like '%y'; Zachary Harry Jimmy sqlite> select lastname from people where lastname like '%son'; Jefferson Madison Jackson Harrison Wilson Johnson
Multiple wildcards:
sqlite> select lastname from people where lastname like '%oo%'; Roosevelt Coolidge Hoover Roosevelt sqlite> select lastname from people where lastname like '%o%o%'; Monroe Johnson Roosevelt Coolidge Hoover Roosevelt Johnson sqlite> select * from people where lastname like '% %'; Van Buren|Martin|8
Core functions, aggregate functions, date and time functions:
sqlite> select * from people where length(firstname) = length(lastname);
Lincoln|Abraham|16
Harrison|Benjamin|23
sqlite> select * from people where substr(firstname, 1, 1) = substr(lastname, 1, 1);
Wilson|Woodrow|28
Coolidge|Calvin|30
Hoover|Herbert|31
Reagan|Ronald|40
sqlite> .separator ''
sqlite> .show
sqlite> select substr(firstname, 1, 1), ". ", lastname, " (", firstname, ")" from people;
G. Washington (George)
J. Adams (John)
T. Jefferson (Thomas)
J. Madison (James)
etc.
sqlite> .separator |
sqlite> select printf("%09d", ss), firstname, lastname from people;
000000001|George|Washington
000000002|John|Adams
000000003|Thomas|Jefferson
000000004|James|Madison
desc
means “descending”.
sqlite> select * from people order by firstname; Lincoln|Abraham|16 Jackson|Andrew|7 Johnson|Andrew|17 Obama|Barack|44 Harrison|Benjamin|23 etc. sqlite> select * from people order by firstname desc; Taylor|Zachary|12 Wilson|Woodrow|28 Harrison|William|9 McKinley|William|25 Taft|William|27 etc. sqlite> select * from people where ss <= 10 order by ss desc; Tyler|John|10 Harrison|William|9 Van Buren|Martin|8 Jackson|Andrew|7 Adams|John|6 Monroe|James|5 etc. sqlite> select * from people order by length(lastname); Polk|James|11 Taft|William|27 Ford|Gerald|38 Bush|George|41 Bush|George|43 Adams|John|2 etc. Washington|George|1 Eisenhower|Dwight|34 sqlite> select * from people order by length(lastname), lastname; Bush|George|41 Bush|George|43 Ford|Gerald|38 Polk|James|11 Taft|William|27 Adams|John|2
From each group of rows that share the same last name, print only one representative row. For example, from the group of two Roosevelts, only one will be printed. And from the group of one Washington, only one will be printed. The following statement prints 38 out of the 44 rows in the table.
sqlite> select * from people group by lastname; Adams|John|6 Arthur|Chester|21 Buchanan|James|15 Bush|George|43 Carter|Jimmy|39 Cleveland|Grover|24 Clinton|Bill|42 Coolidge|Calvin|30 Eisenhower|Dwight|34 Fillmore|Millard|13 Ford|Gerald|38 Garfield|James|20 Grant|Ulysses|18 Harding|Warren|29 Harrison|Benjamin|23 Hayes|Rutherford|19 Hoover|Herbert|31 Jackson|Andrew|7 Jefferson|Thomas|3 Johnson|Lyndon|36 Kennedy|John|35 Lincoln|Abraham|16 Madison|James|4 McKinley|William|25 Monroe|James|5 Nixon|Richard|37 Obama|Barack|44 Pierce|Franlin|14 Polk|James|11 Reagan|Ronald|40 Roosevelt|Franklin|32 Taft|William|27 Taylor|Zachary|12 Truman|Harry|33 Tyler|John|10 Van Buren|Martin|8 Washington|George|1 Wilson|Woodrow|28
For each last name that occurs more than once, print a representative row.
sqlite> select * from people group by lastname having count(*) > 1; Adams|John|6 Bush|George|43 Cleveland|Grover|24 Harrison|Benjamin|23 Johnson|Lyndon|36 Roosevelt|Franklin|32
For each name that occurs more than once, print a representative row. A name consists of a firstname and lastname.
sqlite> select * from people group by lastname, firstname having count(*) > 1; Adams|John|6 Bush|George|43 Cleveland|Grover|24
For each name that occurs more than once,
print all the rows.
The above statement is in the parentheses below.
The three rows of output of the above statement
constitute the three rows of the
temporary_table
below.
sqlite> select people.lastname, people.firstname, people.ss from people, (select * from people group by lastname, firstname having count(*) > 1) as temporary_table where people.lastname = temporary_table.lastname and people.firstname = temporary_table.firstname; Adams|John|2 Adams|John|6 Cleveland|Grover|22 Cleveland|Grover|24 Bush|George|41 Bush|George|43
Another way to get the same output is to join the two tables.
sqlite> select people.lastname, people.firstname, people.ss from people join (select * from people group by lastname, firstname having count(*) > 1) as temporary_table on people.lastname = temporary_table.lastname and people.firstname = temporary_table.firstname; Adams|John|2 Adams|John|6 Cleveland|Grover|22 Cleveland|Grover|24 Bush|George|41 Bush|George|43
sqlite> select * from people where firstname like '%v%' and lastname like '%v%'; Cleveland|Grover|22 Cleveland|Grover|24 sqlite> select * from people where firstname like '%v%' or lastname like '%v%'; Van Buren|Martin|8 Cleveland|Grover|22 Cleveland|Grover|24 Roosevelt|Theodore|26 Coolidge|Calvin|30 Hoover|Herbert|31 Roosevelt|Franklin|32 sqlite> select * from people where not lastname like 'W%'; Adams|John|2 Jefferson|Thomas|3 Madison|James|4 etc.
sqlite> update people set firstname = 'William' where firstname = 'Bill'; sqlite> select * from people where firstname = 'William'; Harrison|William|9 McKinley|William|25 Taft|William|27 Clinton|William|42
Multiply each social security number by 10:
sqlite> update people set ss = ss * 10; sqlite> select * from people; Washington|George|10 Adams|John|20 Jefferson|Thomas|30 etc.
sqlite> delete from people where lastname = 'Nixon'; sqlite> select * from people; sqlite> delete from people; --every row sqlite> select * from people;
Suppose a
sinister
force
changed the above seven characters
'Nixon'
to the 27 characters
'Nixon'; delete from people
Then the computer would execute the following statements,
deleting every row in the table.
This is called an
injection attack.
sqlite> delete from people where lastname = 'Nixon'; delete from people;
After you
.exit
or
.quit
from
sqlite3,
you should see the new file
database.db
in the current directory.
It is not a humanly-readable text file.
sqlite> .exit ls -l database.db file database.db database.db: SQLite 3.x database od -c database.db | head -8 0000000 S Q L i t e f o r m a t 3 \0 0000020 004 \0 001 001 \0 @ \0 \0 \0 002 \0 \0 \0 003 0000040 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 001 \0 \0 \0 004 0000060 \0 \0 \0 \0 \0 \0 \0 003 \0 \0 \0 001 \0 \0 \0 \0 0000100 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 0000120 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 002 0000140 \0 - 346 006 \r \0 \0 \0 001 003 z \0 003 z \0 \0 0000160 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
Press the space bar to tell
more
to release the next screenful of the dump,
or
q
to quit.
sqlite3 database.db .dump | more
Use your browser to save the file
zip5.csv
onto your Mac or PC Desktop as
zip5.csv.
cd ~/Desktop pwd head -3 zip5.csv 00501,Holtsville,NY,40.8152,-73.0455,Suffolk 00544,Holtsville,NY,40.8152,-73.0455,Suffolk 00601,Adjuntas,PR,18.1788,-66.7516,Adjuntas sqlite3 zipcodes.db sqlite> create table zipcodes ( zipcode integer, city text, state text, latitude real, longitude real, county text ); sqlite> .tables sqlite> .schema zipcodes sqlite> .mode csv sqlite> .import zip5.csv zipcodes sqlite> select count(*) from zipcodes; 41755 sqlite> select * from zipcodes where zipcode = 10003; --7 East 12th Street 10003,New York,NY,40.731253,-73.989223,New York
A degree of latitude is equal to approximately 57.2957795 miles
anywhere on the globe,
so a mile is equal to 1/57.2957795 degrees
and a half mile is a equal to .5/57.2957795 degrees.
A degree of longitude is equal to approximately .75 * 57.2957795 miles
in New York City, because the cosine of our latitude is approximately .75.
(The longitude lines are closer together than the latitude lines.)
Let’s find all the zip codes in the square mile centered on 10003.
We should have used
printf
to ensure that every zip code,
even the ones with leading zeroes,
prints out as a full five-digit number.
sqlite> select * from zipcodes
where abs(latitude - 40.731253) < .5 / 57.2957795
and abs(longitude - -73.989223) < .5 / (.75 * 57.2957795);
10003,"New York",NY,40.731253,-73.989223,"New York"
10009,"New York",NY,40.726188,-73.979591,"New York"
10010,"New York",NY,40.737476,-73.981328,"New York"
10012,"New York",NY,40.72553,-73.998284,"New York"
10159,"New York",NY,40.7389,-73.9845,"New York"
10160,"New York",NY,40.7389,-73.9845,"New York"
10211,"New York",NY,40.7314,-73.9904,"New York"
10276,"New York",NY,40.7314,-73.9904,"New York"
cd ~/Desktop pwd sqlite3 america.db
sqlite> create table states (
abbreviation text primary key,
name text,
nickname text
);
sqlite> .tables
sqlite> insert into states (abbreviation, name, nickname) values ('NY', 'New York', 'The Empire State');
sqlite> insert into states (abbreviation, name, nickname) values ('NJ', 'New Jersey', 'The Garden State');
sqlite> insert into states (abbreviation, name, nickname) values ('IL', 'Illinois', 'Land of Lincoln');
sqlite> select * from states;
I created the states table before the cities table because the cities table refers to the states table.
sqlite> create table cities (
name text,
state text
);
sqlite> .tables
sqlite> insert into cities (name, state) values ('New York', 'NY');
sqlite> insert into cities (name, state) values ('Yonkers', 'NY');
sqlite> insert into cities (name, state) values ('Newark', 'NJ');
sqlite> insert into cities (name, state) values ('Chicago', 'IL');
sqlite> select * from cities;
sqlite> select * from cities, states; --4 × 3 = 12 lines of output
New York|NY|NY|New York|The Empire State
New York|NY|NJ|New Jersey|The Garden State
New York|NY|IL|Illinois|Land of Lincoln
Yonkers|NY|NY|New York|The Empire State
Yonkers|NY|NJ|New Jersey|The Garden State
Yonkers|NY|IL|Illinois|Land of Lincoln
Newark|NJ|NY|New York|The Empire State
Newark|NJ|NJ|New Jersey|The Garden State
Newark|NJ|IL|Illinois|Land of Lincoln
Chicago|IL|NY|New York|The Empire State
Chicago|IL|NJ|New Jersey|The Garden State
Chicago|IL|IL|Illinois|Land of Lincoln
sqlite> select name from cities;
New York
Yonkers
Newark
Chicago
sqlite> select cities.name from cities;
sqlite> select cities.name, states.name from cities, states where cities.state = states.abbreviation;
New York|New York
Yonkers|New York
Newark|New Jersey
Chicago|Illinois
sqlite> .separator ''
sqlite> .show
sqlite> select cities.name, ', ', cities.state, ' (', states.name, '--', states.nickname, ')'
from cities, states
where cities.state = states.abbreviation;
New York, NY (New York--The Empire State)
Yonkers, NY (New York--The Empire State)
Newark, NJ (New Jersey--The Garden State)
Chicago, IL (Illinois--Land of Lincoln)
sqlite> .exit ls -l america.db file america.db sqlite3 america.db .dump | more