What is a database?

You’ve probably used or seen an Excel spreadsheet. Well, a database looks similar, but it’s a lot more powerful, a database can do things that Excel only dreamed of doing. A “database” is made up of “tables” which are made up of a bunch of “columns” and “rows”.

Notice how it is broken up, there is a table for users in there. That will hold the information for all the users. We make tables logically for the different items we would like to keep track of.

Databases that are used most commonly are called “Relational Databases”. What that means is that each of the tables can link to another table. The most common way to imagine databases is to draw webs how they relate. As you can see in the picture: A User is related to polls and answers. A Poll is related to answers, choices and users. A Choice is related to polls and answers. Finally, an Answer is related to choices, polls, and users.

This lets us do stuff like to find a User record and then see all his answers and polls.

Now that we know a little bit more about tables, lets look and what a table consists of. It is made up of columns (or fields) that we want to track. If we were to look at it it would look like this:

Now to actually look through a database ourselves would be time consuming and wouldn’t be very helpful (we would have to look at all the users, get the id of the user that we want, then look the polls and find the user_id that matches our user’s id, then look at all the choices and find the ones that match our poll’s id) so there is a way to interact with databases called SQL (Structured Query Language). So we can basically ask the database questions and it will return answers. To get the first result above (the tables) I typed:

show tables;

Notice how my SQL statement or query ends in a semicolon “;”? From the command line this is usually how you tell your database that you are done typing the commands and it should go out and get you the answers. The answer that query returns are the list of all the tables in the database. In order the first three queries I performed were:

(Get the list of tables)show tables;

(Get the columns in users)describe users;

(Get all the user rows)select * from users;

Now that last one was a little different. In the last one I’m saying
“select” (go get)
“*” (all values of all the columns)
“from users” (from the users table)
So it reads “go get all the values of all the columns from the users table” and the database goes out and fetches it.

The next query I did after I saw the user was to do:

select * from polls where user_id = 2;

The “select * from polls” is the same as what we did for the users but we added a “condition” (something that the database should look for)
“where user_id = 2” (find only records where user_id is equal to 2)
So it reads “go get all the values of all the columns from the polls table and find only records where the user_id is equal to 2” and the database went out and fetched those results for me.

The final query I performed was:

select * from choices where poll_id = 11;
This does the exact same thing as the query above only it looks in the choices table and finds only records where the poll_id is equal to 11.

Multiple Databases

All the examples up until now are assuming you only have one database and you have already selected it. However that isn’t normally the case. Lets move back into our Excel analogy and apply it to the database and look at some of the SQL used as well.

Starting from the beginning

GOAL: Find the database we want.EXCEL EQUIVALENT: Finding the Excel file we want in a folder full of excel files we have to look at the names for all the excel files.SQL: show databases;RESULT:

Reading this reminds me of my previous room mate. That guy was one of the smartest human beings I know, but he was a little nutty for my tastes though. Anyways I appreciated reading this, thanks. Will give me something to argue about when I see him.