SQL Utilities

SQL Utilities Intro

The main idea of these utilities is to be able work in SQL on the Unix command Line.

Unix Command Line + SQL = Unbeatable Data Analysis and Data Discovery.

Like this:

   echo "select * from books where book_id = 5 \G" | devdb -t

And like this:

   cat books.sql | devdb -t

Database Aliases

First you must setup a set of aliases to your database connections. You can put this in a file called ~/bin/database_aliases.sh. And then source it in your ~/.bash_profile.

Something like this:

/home/dlink/bin/database_aliases.sh:

   alias devdb='mysql -udlink -pbojangles -hlocalhost --database=library'
   alias stagedb='mysql -uzimuser -psecrete -hstagedb.acme.com --database=library'
   alias proddb='mysql -uzimuser -psecrete -hdb01.izimbra.com --database=library'
   alias proddbro='mysql -uzimuser -psecrete -hdb02.izimbra.com --database=library'

You may need to install the mysql client. You can get more information about on the MySQL Command-Line Tool Page.

Notice the use of --database=<databasename> rather then simply <databasename>. Both work, but the first one allows us to tack other options on the end like -t (table format output).  

Putting it to use

Now we can go to work exploring our database. Here are some examples:

1. What tables are there in the database?

   $ show_tables | devdb -t

2. Is the status table singluar or plural?

   $ show_tables | devdb |grep -i status

3. What fields are in the books table?

   $ desc books | devdb -t

4. How many authors records are there?

   $ count authors | devdb -t

5. Show me one record from the books table

   $ selectsome books id 10 | devdb -t

6. What's the greatest id in the books table?

   $ selectmax books id | devdb -t

7. Put all the status data into a csv file

   $ selectall statuses | devdb | t2c > statuses.csv

8. Show me a frequency histogram of which status_ids are used most frequently in the books table

  $ tally books status_id | devdb -t

9. What indexes does the books table have?

  $ show_indexes books | devdb -t

10. What constraints does the books table have?

  $ show_create books | devdb -t

11. Why is this query so slow?

  $ explain customers_by_provider.sql | proddb -t

12. What tables have foreign key references to the authors table?

  $ fk_usage library authors | devdb -t

Classes of SQL Utilities

To get Schema related information about your data see:

    desc, fk_usage. show_create, show_indexes, show_tables

To query your data see:

    count, count_distinct, select5, selectall, selectmax, selectmin, selectsome, tally

Miscellaneous:

    explain. sql_pretty.

count

Return the count, or number of records for a given table

   count <filename> | <dbalias>

Examples:

   $ count pics | vpicsdb  -t

   +-------+
   | count |
   +-------+
   |    69 |
   +-------+

Create a list of all database tables and their row counts:

   $ show_tables | vrdb | grep -v Tables | \
   >    while read t; do echo -n "$t: "; count $t | vrdb | grep -v count ;  done

   City: 4079
   Country: 239
   CountryLanguage: 984
   authors: 10
   book_authors: 11
   book_genres: 22
   books: 25
   countries: 3
   genres: 17

   desc <tablename> | <dbalias>

   $ desc pages | vpicsdb -t

+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field        | Type             | Null | Key | Default           | Extra                       |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| page_id      | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| name         | varchar(30)      | NO   | UNI | NULL              |                             |
| last_updated | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| created      | datetime         | YES  |     | NULL              |                             |
+--------------+------------------+------+-----+-------------------+-----------------------------+

   $ desc providers | devdb | grep -i date

   start_dated   datetime   YES      NULL  
   last_updated   timestamp   NO      CURRENT_TIMESTAMP   on update CURRENT_TIMESTAMP
   created   datetime   YES      NULL  

   explain <filename> | <dbalias>

   $ explain bad_customers.sql | penergy -t

+----+-------------+-------+------+----------------------+------------+---------+-------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys        | key        | key_len | ref   | rows     | Extra                                        |
+----+-------------+-------+------+----------------------+------------+---------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | i     | ref  | customerId,TransDate | customerId | 5       | const |   392878 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | i2    | ALL  | customerId           | NULL       | NULL    | NULL  | 10225890 | Using where; Using join buffer               |
+----+-------------+-------+------+----------------------+------------+---------+-------+----------+----------------------------------------------+

   selectall <tablename> | <dbalias>

   $ selectall genres | vrdb -t

+----+-----------------------+
| id | name                  |
+----+-----------------------+
|  1 | Philosophical         |
|  2 | Gothic                |
|  3 | Speculative           |
|  4 | Roman à clef         |
|  5 | Prose Poetry          |
|  6 | Science Fiction       |
|  7 | Historical Fiction    |
|  8 | Romance               |
|  9 | Horror                |
| 10 | Novella               |
| 11 | Ghost story           |
| 12 | Satire                |
| 13 | Adventure             |
| 14 | Children's literature |
| 15 | Humour                |
| 16 | Fantasy               |
| 17 | Sword and planet      |
+----+-----------------------+

   show_tables | <dbalias>

   $ show_tables | wpdb -t

+-----------------------+
| Tables_in_wordpress   |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
11 rows in set (0.00 sec)

   $ show_tables | nadb -t | grep -i status

customer_statuses
order_statues
statuses

   sql_pretty [OPTIONS] <filename>

       OPTIONS: -l  - lowercase keywords
		-h  - HTML Output

   $ sql_pretty country_cities_by_population.sql
   select
      co.name as country, count(*) as count
   from
      City ci
      join Country co on ci.CountryCode = co.Code
   where
      ci.population > 5000000
   group by
      1
   order by
      2 desc;

   echo "select id as book_id, title from books where read='Y'" | nadb | t2c > books_read.csv