In the last few topics I’ve been talking about these information storage repositories called databases, but what are they really? We’ll talk about SQL databases as applied to MySQL examples, but understand that the fundamental principles of databases apply to all the engines, whether you’re using SQL Server, PostgreSQL, Oracle, SQLlite, MySQL, or some other variant.

Merriam-Webster’s defines a database as:

 “a collection of data organized especially for rapid search and retrieval (as by computer)”

If you open any given database file you’ll see a bunch of plaintext (human-readable) characters, and some large chunks of numbers. If the database is encrypted for security purposes, you won’t be able to read it like this. From this we can see that the actual data storage isn’t really what’s special; it’s the SQL engine that interprets this data.

Data is stored in tables, or sets of similar data. Inside a table is a set of columns, just like in a spreadsheet. Let’s look at an example of this data structure. Say we have a database, MyLibrary. Inside MyLibrary you can imagine we might have tables for Books, Magazines, Movies, and Music. The books table would have columns for (Title, Author Last, Author First, Genre, Publisher, ISBN, Call Letters, etc.). When we insert data into the books table, we simply add a new row. Each of the other tables (Magazines, Movies, and Music) would have similar columns to store and sort the relevant data.

So how do we actually create our databases? Once you have your selected client installed, you need to construct a CREATE TABLE statement that will form the database table and columns we want. For our books table, we would execute the following:

mysql> CREATE TABLE Books (Title TEXT, AuthorLast VARCHAR(30), AuthorFirst VARCHAR(30), Genre VARCHAR(50), Publisher TEXT, ISBN VARCHAR(13), CallLetters VARCHAR(3) );

Let’s break the statement down and look at what each part means. The “mysql>” part isn’t something you would type – that’s the prompt in the MySQL Command Line window (graphic). The words after each of our columns are format specifiers that set the data type of each column. All of the data we are storing right now is text-based (TEXT and VARCHAR). TEXT columns can store large amounts of text data, such as news articles or essays. VARCHAR columns are fixed-width, meaning you must specify the maximum number of characters that can be stored. For the Author fields, I assumed that most names are not going to be over 30 characters long. The ISBN is a much more valid assumption since the maximum ISBN is indeed 13 characters long. If you try to insert data that is longer than the field width it will simply be truncated to length. There are several other datatypes we can store such as INT (numbers), DECIMAL (numbers with decimal places), and BLOB (binary data, such as pictures).

To interact with your database you can use either a GUI (graphical user interface) based system where you click and drag elements around to insert, update, and delete data, or a command line tool. Both are equally good methods of working with data, but you will learn a lot more if you use the command line tool. It might look a little bit intimidating at first, but if you follow the syntax given here everything should work just fine. To insert data we need to use the following syntax:

mysql> INSERT INTO Books (Title, AuthorLast, AuthorFirst, Genre, Publisher, ISBN, CallLetters) VALUES (‘My First Book’, ‘Brown, ‘Joe’, ‘Romance’, ‘Tommy Nelson Publishers’, ‘0194820184928’, ‘BRO’);

After we specify the table name (Books) we must send a list of columns we want to insert data into. In this example we were inserting data into every single column, so we could have omitted the section between Books ... VALUES. However, in general we need to specify these columns since generally we don’t know all of the information for a particular record at time of insertion.

Once we have some data added, we can use a SQL Query to retrieve records from our database. A record is just a set of data about a unique record. If I want to see all of the books I own, I would execute the following query:

mysql> SELECT * FROM Books;

We start with “SELECT”, which just tells SQL that we will be returning records. Later we’ll look at other words that start SQL statement like DELETE, UPDATE, and INSERT. The “*” is a wildcard character. In this case it means “return all data about this record” or “return all columns of data for this record”. In place of the star we could list a specific subset of the columns or just specify our own order such as (Title, Author Last, Genre) to only return three columns of data. “FROM Books” just tells the database engine that we are trying to get data out of the Table named “Books.” This could have just has easily been Magazine, Movie, or Music, as long as the columns listed in the statement appear in that table. For example, we couldn’t perform a search for a column named “Call Letters” on a Music table if that table didn’t have its own column named “Call Letters”. The semicolon “;” is just a terminating character that tells MySQL to execute the preceding statement.

Now say we want to get a list of books by a specific author. This is where databases start to get really cool – they’re great at sorting your data very quickly. If we want to look up books by Joe Brown, we would construct a query like

mysql> SELECT * FROM Books WHERE AuthorLast = ‘Brown’;

This looks pretty much like our last query, but we’ve added a WHERE clause. The WHERE clause allows us to filter our data so we only retrieve those records we are interested in. You can construct advanced WHERE clauses, such as filtering only books by Joe Brown in the Romance genre published after December 21^st, 1995. That WHERE clause would use “AND” between each statement, such as

… WHERE AuthorLast =’Brown’ AND Genre=’Romance’ AND PublishDate > ‘1995-21-12’;

Another useful filtering uses “OR” between statements. We can use OR to select books by multiple authors or genres, for example. A sample query for using WHERE and OR might look like

… WHERE AuthorLast =’Brown’ OR AuthorLast=’Martin’ OR AuthorLast=’Samson’;

Now you have learned what a database is, how to create a table, store some data, and pull it out with intelligent queries. Next time we’ll talk about what it means to have a so-called “relational” database and how to exploit a fuller feature set of the MySQL database engine.

Labels: Databases, Lesson, MySQL, PHP, SQL

This entry was posted on Thursday, November 27, 2008 at 12:02 PM.