So what's SQL? - Page 3
January 10, 2002
The set of commands we'll use to tell MySQL what to do for the rest of this article is part of a standard called Structured Query Language, or SQL (pronounced either "sequel" or "ess-cue- ell"—take your pick). Commands in SQL are also called queries (I'll use these two terms interchangeably in this article series).
SQL is the standard language for interacting with most databases, so even if you move from MySQL to a database like Microsoft SQL Server in the future, you'll find that most of the commands are identical. It's important that you understand the distinction between SQL and MySQL. MySQL is the database server software that you're using. SQL is the language that you use to interact with that database.
Creating a Database
Those of you working on your Web host's MySQL server have probably already been assigned a database to work with. Sit tight, we'll get back to you in a moment. Those of you running a MySQL server that you installed yourselves will need to create your own database. It's just as easy to create a database as it is to delete one:
I chose to name the database "jokes", since that fits with the example we're working with. Feel free to give the database any name you like, though. Those of you working on your Web host's MySQL server will probably have no choice in what to name your database, since it will usually already have been created for you.
Now that we have a database, we need to tell MySQL that we want to use it. Again, the command isn't too hard to remember:
You're now ready to use your database. Since a database is empty until you add some tables to it, our first order of business will be to create a table that will hold our jokes.
Creating a Table
The SQL commands we've encountered so far have been pretty simple, but since tables are so flexible it takes a more complicated command to create them. The basic form of the command is as follows:
Let's return to our example "Jokes" table. Recall that it had three columns: ID (a number), JokeText (the text of the joke), and JokeDate (the date the joke was entered). The command to create this table looks like this:
It looks pretty scary, huh? Let's break it down:
Note that, while you're free to type your SQL commands in upper or lower case, a MySQL server running on a UNIX-based system will be case-sensitive when it comes to database and table names, as these correspond to directories and files in the MySQL data directory. Otherwise, MySQL is completely case–insensitive, but for one exception: table, column, and other names must be spelled exactly the same when they're used more than once in the same command.
Note also that we assigned a specific type of data to each column
Now, if you typed the above command correctly, MySQL will respond with "Query OK" and your first table will be created. If you made a typing mistake, MySQL will tell you there was a problem with the query you typed, and will try to give you some indication of where it had trouble understanding what you meant.
For such a complicated command, "Query OK" is pretty a pretty boring response. Let's have a look at your new table to make sure it was created properly. Type the following command:
The response should look like this:
This is a list of all the tables in our database (which I named "jokes" above). The list contains only one table: the "Jokes" table we just created. So far everything looks good. Let's have a closer look at the "Jokes" table itself:
This provides a list of the columns (or fields) in the table. As we can see, there are three columns in this table, which appear as the 3 rows in this table of results. The details are somewhat cryptic, but if you look at them closely for a while you should be able to figure out what most of them mean. Don't worry about it too much, though. We've got better things to do, like adding some jokes to our table!
We need to look at just one more thing before we get to that, though: deleting a table. This is just as frighteningly easy to do as it is to delete a database. In fact, the command is almost identical: