Sqlite3 shell script notes

#shell scripts
#http://www.sqlitetutorial.net/sqlite-autoincrement/

#create Database and table with auto increment rowid and no empty entries
sqlite3 example.db "CREATE TABLE people( fname text NOT NULL, lname text NOT NULL);"

#manual id
sqlite3 example.db "create table table1(id integer primary key,word1 text,word2 text)"


#insert into table
sqlite3 example.db "INSERT INTO table1 VALUES ('My First Entry', 'My Second Entry')"


#view all entries
sqlite3 example.db "SELECT * FROM table1"

#or to see with rowid
sqlite3 example.db "SELECT rowid, fname, lname FROM table1;"

#create test data
for i in {2..100};
do
    word1="$(cat /usr/share/dict/words|shuf|head -n1)"
word2="$(cat /usr/share/dict/words|shuf|head -n1)"
sqlite3 example.db "INSERT INTO table1 VALUES ($i,'$word1', '$word2')"
done


#view column of all entries
sqlite3 example.db "SELECT word1 FROM table1"
sqlite3 example.db "SELECT id, word1 FROM table1"

#search queary
sqlite3 example.db "SELECT id, word1 FROM table1 WHERE id IS 99"
sqlite3 example.db "SELECT id, word1 FROM table1 WHERE word1 IS 'epipolize'"
sqlite3 example.db "SELECT id, word1 FROM table1 WHERE word1 LIKE '%er'"

#Delete entries
sqlite3 example.db "DELETE FROM table1 WHERE id='99'"

#add Column
sqlite3 example.db "ALTER TABLE table1 ADD COLUMN phone TEXT"

#update entry
sqlite3 example.db "UPDATE table1 SET phone='555-555-5555' WHERE rowid=4"