Sector 930

As a kind of, sort of “junior DBA,” I have learned several things about databases. I know Charlie’s looking to learn about SQL, so I’ll post a series about some of the basic stuff about the language that I picked up.

SQL (some people pronounce it “sequel”) is a fairly simple language, with a pseudo natural language flow. In this post, I’ll start with select statements, and in later posts I will cover insert, update, and delete statements. These four statements are the core of the language. They are practically standardized across all database systems. I’ll use MySQL in my examples, but it shouldn’t matter what system you’re using.

Installing MySQL

I won’t go into how to install MySQL. See the reference manual for that. Definitely give yourself a good hour to install it and set it up.

Setting up the database

Throughout my examples, I will use the freely available Baseball Databank. (This is the point in this blog where I unleash my baseball nerdiness.) Just get the zipped database in MySQL form. After you’ve downloaded it, run the following commands (assuming you have a Unix-like system*):

$ mysql -p -u root

mysql> GRANT ALL ON bbdatabank.* TO 'username'@'localhost' IDENTIFIED BY 'password';

mysql> CREATE DATABASE bbdatabank;

mysql> quit

$ mysql -u username -p -s bbdatabank < BDB-sql-2008-03-28.sql

Then, start the MySQL client, just type:

$ mysql bbdatabank -u username -p

If you have your own database and don’t want to use this one, just modify my examples to fit yours. The important thing is, you need a database, otherwise you will not get far.

select * from table

The basic form of a select statement is

select columns from table;

The “select *” variation is the most general form. With it, you are saying “give me all the columns in the table.”  An example of this query wouldn’t fit on the screen, but you can give it a try. Just run the following command at the MySQL prompt:

mysql> select * from Batting;

Be sure when you try this and anything else on the MySQL prompt, you include the semi-colon at the end. If you forget, just type a semi-colon on the next line and hit enter.

Selecting particular columns

Most of the time, you don’t want all the columns in the table. Instead, you will want to select certain columns to find the information you’re looking for. Here is an example:

mysql> select playerID,yearID,HR from Batting;

This script will pull all of the single season home run totals for every player-year in the database. Again, I won’t put the output here because it will be thousands of rows, but you can try it out.

The where clause

Usually, we don’t want all the rows in the table, only a subset. To limit the rows that we get, we can use the where clause. For example, I might want to look at all the single season home run totals put up by Babe Ruth:

mysql> select playerID,yearID,HR from Batting where playerID='ruthba01';
+----------+--------+----+
| playerID | yearID | HR |
+----------+--------+----+
...
| ruthba01 |   1926 | 47 |
| ruthba01 |   1927 | 60 |
| ruthba01 |   1928 | 54 |
| ruthba01 |   1929 | 46 |
| ruthba01 |   1930 | 49 |
| ruthba01 |   1931 | 46 |
| ruthba01 |   1932 | 41 |
...
+----------+--------+----+
22 rows in set (0.01 sec)

In this example (shortened for length reasons), I used Babe Ruth’s playerID. If you want to try a different player, you will have to find his playerID in the Master table. I will leave that up to you as an exercise. You can find out what columns make up the Master table by executing the describe command:

mysql> describe Master;

or

mysql> desc Master;

Aliases

Sometimes, column names can be funky or cryptic. Other times, we want to combine information from two or more columns and display it as one column in our result set. We can do this with aliases. In this next example, I’ll find the on base percentage (OBP) of Barry Bonds for each of his seasons. OBP is derived using a formula that would take up a lot of space in a column header. Instead we can use OBP as an alias. Notice the as keyword in this example:

mysql> select playerID,yearID as Year,(H+BB+HBP)/(AB+BB+HBP+SF) as OBP from Batting where playerID='bondsba01';
+-----------+------+--------+
| playerID  | Year | OBP    |
+-----------+------+--------+
...
| bondsba01 | 2000 | 0.4399 |
| bondsba01 | 2001 | 0.5151 |
| bondsba01 | 2002 | 0.5817 |
| bondsba01 | 2003 | 0.5291 |
| bondsba01 | 2004 | 0.6094 |
| bondsba01 | 2005 | 0.4038 |
| bondsba01 | 2006 | 0.4544 |
| bondsba01 | 2007 | 0.4801 |
+-----------+------+--------+
22 rows in set (0.02 sec)

select count(*)

Before I wrap up this intro to SQL, I’ll leave you with one more type of query. It’s one that’s quite simple, yet very useful. You can use it to tell the database “give me the number of rows in this table.” In this example, I’ll select the number of rows in the Batting table:

mysql> select count(*) from Batting;
+----------+
| count(*) |
+----------+
|    89945 |
+----------+
1 row in set (0.00 sec)

You’re a DBA now!

Not. But you’ve taken your first step. I encourage you to play around with the your queries and try to get different results. And if you think this intro was crap, well you can just go find another one. A good one is w3schools.com. Another good place is the reference manual for your database software. I hope you got something out of this though, because I’ll be back next time with more topics: the sum function and the group by clause.

*You only have a Windows system? Why are you reading this blog?