Don’t you just hate losing things? Whether it’s your car keys, that 25% off coupon for Urban Outfitters, or your application’s data, there’s nothing worse than not being able to keep up with what you need... when you need it. And when it comes to your applications, there’s no better place to store your important information than in a table. So turn the page, come on in, and take a walk through the world of relational databases.
Greg knows many lonely single people. He likes keeping track of what his friends are up to, and enjoys introducing them to each other. He has lots of information about them scrawled on sticky notes like this:
Greg’s been using his system for a very long time. Last week he expanded his connections to include people who are seeking new jobs, so his listings are growing quickly. Very quickly...
Exactly right. A database is just what we need.
But before you can get into creating databases, you’re going to need to have a better idea of what kinds of data you’re going to want to store and some ways of categorizing it.
Then if you cut up another sticky note with the categories you just noticed, and put the pieces above their corresponding information, you’d have something that looks a lot like this:
Here’s that same information nicely displayed in a TABLE in columns and rows.
San Antonio, TX
Single, but involved
Friends, Women to date
San Antonio, TX
Unix System Administrator
San Francisco, CA
Before we get into the details of what tables, rows, and columns are, let’s step back and look at the bigger picture. The first SQL structure you need to know about is the container that holds all your tables known as a database.
Every time you search online, go shopping, call information, use your TiVo, make a reservation, get a speeding ticket, or buy groceries, a database is being asked for information, otherwise known as being queried.
All of the tables in a database should be connected in some way. For example, here are the tables that might be in a database holding information about doughnuts:
Here’s an example of what an address book table containing your personal information might look like. You’ll often see the word field used instead of column. They mean the same thing. Also, row and record are often used interchangeably.
Exactly. You can identify categories for the type of data you’re collecting for each person. Your categories then become your columns. Each sticky note becomes a row. You can take all that information from your stickies and turn it into a table.
Start up your SQL relational database management system (RDBMS) and open a command-line window or graphical environment that allows you to communicate with your RDBMS. Here’s our terminal window after we start MySQL.
First you’re going to need to create a database to hold all your tables.
Type in the line of code below to create your database called
Spaces aren’t allowed in the names of databases and tables in SQL, so an underscore can be used instead.
Did you read the intro?
We’re using MySQL to command our databases, so commands in your Database Management System (DBMS) might look a little different. See Appendix B for instructions on installing MySQL on your server.
Let’s see all this in action with the doughnut data. Say you were having trouble remembering what type of doughnuts a snack in your list was just from its name, you might create a table to save having to remember them instead. Below is a single command to type into your console window. When you’ve typed it, you can press RETURN to tell your SQL RDBMS to carry out the command.
You’ve seen that to create a table you categorize your data into columns. Then you come up with the right data type and length for each column. After you estimate how long each column needs to be, writing the code is straightforward.
Now you know exactly what each line is doing, you can type in the
CREATE TABLE command. You can enter it one line at a time, copying the code at the top of this page.
Or you can enter it all as one really long single line:
Whichever way you choose to enter it, before you hit return after the semicolon, make sure you haven’t missed any characters:
last_name VARCHAR(3) is a very different column than
Suppose we added a price column to our doughnut table. We wouldn’t want to store that as a VARCHAR. Values stored as VARCHARs are interpreted as text, and you won’t be able to perform mathematical operations on them But there are more data types you haven’t met yet...
These data type names may not work with your SQL RDBMS!
Unfortunately, there are no universally accepted names for various data types. Your particular SQL RDBMS might use different names for one or more of these types. Check your documentation to find the correct names for your RDBMS.
Good call. Checking your work is important.
To see how the
my_contacts table you created looks, you can use the
DESC command to view it:
You try it.
When you’ve entered the
DESC command. You’ll see something that looks similar to this:
That’s a very good idea, and you’ll want to use a text editor throughout this book.
That way, you can copy and paste the statements into your SQL console whenever you need to. This will keep you from having to retype everything. Also, you can copy and edit old SQL statements to make new ones.
DROP TABLE will work whether or not there is data in your table, so use the command with extreme caution. Once your table is dropped, it’s gone, along with any data that was in it.
DROP TABLE deletes your table and any data in it!
Now you can enter your new
CREATE TABLE statement:
A bunch of SQL keywords and data types, in full costume, are playing the party game “Who am I?” They give you a clue, and you try to guess who they are, based on what they say. Assume they always tell the truth about themselves. If they happen to say something that could be true for more than one guy, then write down all for whom that sentence applies. Fill in the blanks next to the sentence with the names of one or more attendees.
CREATE DATABASE, USE DATABASE, CREATE TABLE, DESC, DROP TABLE, CHAR, VARCHAR, BLOB, DATE, DATETIME, DEC, INT
Answers in Who am I?.
This pretty much does what it says in the name. Take a look at the statement below to see how each part works. The values in the second set of parentheses have to be in the same order as the column names.
The command below isn’t a real command, it’s a template of a statement to show you the format of an
INSERT statement you might use if you had a table of doughnut purchases. Notice how, in the values, the numbers that match the dozens of donuts purchased and price columns have no quotes.
Changing the order of columns
You can change the order of your column names, as long as the matching values for each column come in that same order!
Omitting column names
You can leave out the list of column names, but the values must be all there, and all in the same order that you added the columns in. (Double-check the order in Create the INSERT statement if you’re unsure.)
Leaving some columns out
You can insert a few columns and leave some out.
Because the sticky is missing some data, Greg will have to enter an incomplete record. But that’s okay, he’ll be able to add in the missing information later.
Don’t worry what the
SELECT statement does for now.
We’ll be looking at it in a lot more detail in Chapter 2. For now, just sit back and marvel at the beauty of your table when you use the statement.
Now try it yourself. You’ll have to stretch out your window to see all the results nicely laid out.
There are certain columns in your table that should always have values. Remember the incomplete sticky note for Pat, with no last name? She (or he) isn’t going to be very easy to find when you have twenty more
NULL last name entries in your table. You can easily set up your table to not accept
NULL values for columns.
If we have a column that we know is usually a specific value, we can assign it a
DEFAULT value. The value that follows the
DEFAULT keyword is automatically inserted into the table each time a row is added if no other value is specified. The default value has to be of the same type of value as the column.
Using a DEFAULT value fills the empty columns with a specified value.
You’ve got Chapter 1 under your belt, and you already know how to create databases and tables, as well as how to insert some of the most common data types into them while ensuring columns that need a value get a value.