Search This Blog

January 25, 2011

Run SQL in Unix Shell

While reading the book - Agile Web Development with Rails, Third Edition - I have come across a section where the authors show how to run a SQL against a sqlite3 database. Want to know where in that book you can find it? Go purchase the book and find it yourself :P

Now back to my business of curiosity - I wanted to know how do I do this in every other databases that I have used so for. This blog post is a record of that endeavor. This should be very handy mainly for the Rails developers.

Unix, SQL and SQlite3

linux_prompt$ sqlite3 db_file_name.sqlite3 "SELECT * FROM my_db_table"

linux_prompt$ sqlite3 db/development.sqlite3 "SELECT count(*) from schema_migrations"

linux_prompt$ sqlite3 db/development.sqlite3 <<EOF
> SELECT * FROM users; --where 'users' is a table in my development.sqlite3 DB 
> SELECT * FROM schema_migrations; --where 'schema_migrations' is a table in my development.sqlite3 DB
>EOF


Unix, SQL and MySQL

linux_prompt$ export username="dbuser"
linux_prompt$ export password="dbpassword"
linux_prompt$ export db="mydatabase"

linux_prompt$ mysql -u "$username" -p "$password" "$db" <<EOF
> SELECT * FROM users; --where 'users' is a table in my development.sqlite3 DB 
> SELECT * FROM schema_migrations; --where 'schema_migrations' is a table in my development.sqlite3 DB
>EOF

linux_prompt$ mysql -u "$username" -p "$password" <<EOF
> use $db
> SELECT * FROM users; --where 'users' is a table in my development.sqlite3 DB 
> SELECT * FROM schema_migrations; --where 'schema_migrations' is a table in my development.sqlite3 DB
>EOF


Unix, SQL and Oracle

linux_prompt$ export username="dbuser"
linux_prompt$ export password="dbpassword"
linux_prompt$ export db="mydatabase"

linux_prompt$ sqlplus -S $username/$password <<EOF
> SELECT * FROM users; --where 'users' is a table in my development.sqlite3 DB 
> SELECT * FROM schema_migrations; --where 'schema_migrations' is a table in my development.sqlite3 DB
> exit; --command to exit sqlplus environment
>EOF

linux_prompt$ sqlplus -S $username/$password @sql_script_file.sql