Hello hackers! This is my ongoing blog series about various topics in web development. Previously, we covered command line usage from a complete beginner's perspective, eventually moving towards some more advanced command line topics.
We'll be firing up the terminal for some examples later on, so if that sounds like something you wouldn't be comfortable with, I definitely recommend checking out last week's post which was a review of sorts.
Today, we're gonna cover a topic that should be near and dear to your heart: SQL! But to get there, we're gonna have to gather some background information, including the history of SQL, how it relates to databases, and what it really does.
What is all of this stuff?
First, let's start with the definition of SQL, graciously provided by Wikipedia: "SQL, or Structured Query Language is a special-purpose programming language designed for managing data in relational database management systems." Oh, well okay then. So SQL is a language that's used for database things, but what's a database? And for that matter, what's data?
Data, in its simplest form, is meaningful facts about the world that are recorded/stored. A database is simply a collection of data that's (ususally) related. You could have a database that's a list of information about your favorite trance songs, your least favorite bugs, or maybe even a database of the contact information of people you know. David wrote an excellent pair of blog posts as an introduction to database design that touches on the very basics of databases, and you can check that out right now. No, go ahead. I won't mind, I'll be right here when you get back.
We'll circle back around to SQL in a bit, but there's a few more concepts we should cover first. In the Wikipedia definition above, the phrase "relational database" indicates a specific kind of database; if you read through David's post above, then you have a general idea of what a relational database looks like. Data that's related can be stored in different tables entirely, then linked together by the use of keys, a special field that typically doesn't mean anything by itself. Keys usually only exist to chain tables together by linking the data in meaningful ways.
Database systems that use this kind of organization are said to be relational databases. The organization itself is called the relational model. There are certain database types that don't use the relational model, and by extension, don't use SQL – you may have heard of NoSQL databases. That's a topic that I'd like to cover in a future post, so keep that in the back of your mind for now.
Okay, so back to SQL. SQL is a language, that we know; it's used to create and query databases. When querying, you can add, modify, or delete data from the database. Since it's a language, it'll be more useful to see an example in action.
Let's get started
First, you should check out http://postgresapp.com. The relational database we'll be using is PostgreSQL, but configuring it is hairy to say the least. If you download and install that app, you'll be able to follow along with this example without trouble. After installing, run this command from your prompt:
This'll put you into a PostgreSQL prompt, where you can type SQL statements to create and manipulate a database. So let's do that!
We're telling SQL to create a database named "contacts"; by custom, SQL keywords like "create" and "database" are capitalized. When you're writing longer SQL statements, this approach makes the code more readable. Next, let's connect to the database and make a table:
connect is an internal command for the PostgreSQL prompt; it's not SQL itself. The next command is all SQL all the time, though. We're creating a table inside our database (that's an important concept to wrap your brain around: databases contain tables, which contain rows of information), and we're specifying what kind of information it can contain. We added three fields: "firstname", "lastname", and "phone", and we specified a type of TEXT for each of them. TEXT is essentially a string with an unlimited length. If that's not clear, then maybe inserting the data itself will help it make sense:
We just inserted a "record" (or a "row") into our friends table. We arranged the columns in the order that the table could expect, then we followed up with the actual values to insert. How can we check and make sure we actually inserted the right values?
Here, we're using SQL to tell PostgreSQL to tell us what's in the friends table. The asterisk indicates that we want every possible value. Later on, we'll be able to specify only certain records that we want returned using a SQL "WHERE" clause, but for now this will do. You should see an ASCII table that represents the table in its current state, containing the record we just entered.
Next, let's delete that sucker (we didn't really like David that much anyway):
After running that command, try the SELECT from above again and verify that the record was removed. Feel free to play around with the psql prompt some more. You can add records to that table, see those records, then delete them. If you're feeling pretty confident, try Googling "SQL update" and playing with that command.
When you're all done playing with this database, it can be deleted. First, we want to list out all of our databases to make sure we delete the right one. Try typing:
This will give you an ASCII table with all of your databases. Voila – there's contacts! Now that we know that's the database we want to delete, let's delete it with the following command:
After this runs, you can list out your databases again to see if it's a part of the list. Now that we're all cleaned up, we can exit the prompt to get back to our normal console:
There you have it, folks! That's a pretty high level view of databases, with a brief example thrown in for funsies. If you have any questions or comments, post them below – I'd love to hear if there's anything in particular you'd like to know about databases!