metalx1000

Sqlite3 shell script notes

Mar 6th, 2015 (edited)
1,134
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Bash 1.83 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 (1,'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, word1, word2 FROM table1;"
  20.  
  21. #for id auto increment user null
  22. sqlite3 example.db "INSERT INTO table1 VALUES (NULL, 'BOB', 'SMITH');"
  23.  
  24. #create test data
  25. for i in {1..100};
  26. do
  27.     word1="$(grep -v "'" /usr/share/dict/words|shuf|head -n1)"
  28.     word2="$(grep -v "'" /usr/share/dict/words|shuf|head -n1)"
  29.     sqlite3 example.db "INSERT INTO table1 VALUES (NULL,'$word1', '$word2');"
  30. done
  31.  
  32.  
  33. #view column of all entries
  34. sqlite3 example.db "SELECT word1 FROM table1;"
  35. sqlite3 example.db "SELECT id, word1 FROM table1;"
  36.  
  37. #search queary
  38. sqlite3 example.db "SELECT id, word1 FROM table1 WHERE id IS 99;"
  39. sqlite3 example.db "SELECT id, word1 FROM table1 WHERE word1 IS 'epipolize';"
  40. sqlite3 example.db "SELECT id, word1 FROM table1 WHERE word1 LIKE '%er';"
  41.  
  42. #Delete entries
  43. sqlite3 example.db "DELETE FROM table1 WHERE id='99';"
  44.  
  45. #add Column
  46. sqlite3 example.db "ALTER TABLE table1 ADD COLUMN phone TEXT;"
  47.  
  48.  
  49. sqlite3 example.db "INSERT INTO table1 VALUES (NULL, 'JANE', 'SMITH', '555-555-5563');"
  50. #if you don't have a phone number
  51. sqlite3 example.db "INSERT INTO table1 VALUES (NULL, 'JAck', 'SMITH', NULL);"
  52. sqlite3 example.db "INSERT INTO table1 (word2, word1) VALUES ('GREEN', 'PINK');"
  53.  
  54. #update entry
  55. sqlite3 example.db "UPDATE table1 SET phone='555-555-5555' WHERE rowid=4;"
  56.  
  57.  
Advertisement
Add Comment
Please, Sign In to add comment