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 (1,'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, word1, word2 FROM table1;"

#for id auto increment user null
sqlite3 example.db "INSERT INTO table1 VALUES (NULL, 'BOB', 'SMITH');"

#create test data
for i in {1..100};
do
	word1="$(grep -v "'" /usr/share/dict/words|shuf|head -n1)"
	word2="$(grep -v "'" /usr/share/dict/words|shuf|head -n1)"
	sqlite3 example.db "INSERT INTO table1 VALUES (NULL,'$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;"


sqlite3 example.db "INSERT INTO table1 VALUES (NULL, 'JANE', 'SMITH', '555-555-5563');"
#if you don't have a phone number
sqlite3 example.db "INSERT INTO table1 VALUES (NULL, 'JAck', 'SMITH', NULL);"
sqlite3 example.db "INSERT INTO table1 (word2, word1) VALUES ('GREEN', 'PINK');"

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