Advertisement
metalx1000

Sqlite3 shell script notes

Mar 6th, 2015
867
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Bash 1.40 KB | None | 0 0
  1. #shell scripts
  2. #http://www.sqlitetutorial.net/sqlite-autoincrement/
  3.  
  4. #create Database and table with auto increment rowid and no empty entries
  5. sqlite3 example.db "CREATE TABLE people( fname text NOT NULL, lname text NOT NULL);"
  6.  
  7. #manual id
  8. sqlite3 example.db "create table table1(id integer primary key,word1 text,word2 text)"
  9.  
  10.  
  11. #insert into table
  12. sqlite3 example.db "INSERT INTO table1 VALUES ('My First Entry', 'My Second Entry')"
  13.  
  14.  
  15. #view all entries
  16. sqlite3 example.db "SELECT * FROM table1"
  17.  
  18. #or to see with rowid
  19. sqlite3 example.db "SELECT rowid, fname, lname FROM table1;"
  20.  
  21. #create test data
  22. for i in {2..100};
  23. do
  24.     word1="$(cat /usr/share/dict/words|shuf|head -n1)"
  25. word2="$(cat /usr/share/dict/words|shuf|head -n1)"
  26. sqlite3 example.db "INSERT INTO table1 VALUES ($i,'$word1', '$word2')"
  27. done
  28.  
  29.  
  30. #view column of all entries
  31. sqlite3 example.db "SELECT word1 FROM table1"
  32. sqlite3 example.db "SELECT id, word1 FROM table1"
  33.  
  34. #search queary
  35. sqlite3 example.db "SELECT id, word1 FROM table1 WHERE id IS 99"
  36. sqlite3 example.db "SELECT id, word1 FROM table1 WHERE word1 IS 'epipolize'"
  37. sqlite3 example.db "SELECT id, word1 FROM table1 WHERE word1 LIKE '%er'"
  38.  
  39. #Delete entries
  40. sqlite3 example.db "DELETE FROM table1 WHERE id='99'"
  41.  
  42. #add Column
  43. sqlite3 example.db "ALTER TABLE table1 ADD COLUMN phone TEXT"
  44.  
  45. #update entry
  46. sqlite3 example.db "UPDATE table1 SET phone='555-555-5555' WHERE rowid=4"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement