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:

mysql> CREATE DATABASE jokes;

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:

mysql> USE jokes;

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:

mysql> CREATE TABLE table_name (
    ->  column_1_name column_1_type column_1_details,
    ->  column_2_name column_2_type column_2_details,
    ->  ...
    -> );

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:

mysql> CREATE TABLE Jokes (
    ->  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  JokeText TEXT,
    ->  JokeDate DATE NOT NULL
    -> );

It looks pretty scary, huh? Let's break it down:

• The first line is pretty simple: it says that we want to create a new table called Jokes.
• The second line says that we want a column called ID that will contain an integer (INT), that is, a whole number. The rest of this line deals with special details for this column. First, this column is not allowed to be left blank (NOT NULL). Next, if we don't specify any value in particular when we add a new entry to the table, we want MySQL to pick a value that is one more than the highest value in the table so far (AUTO_INCREMENT). Finally, this column is to act as a unique identifier for the entries in this table, so all values in this column must be unique (PRIMARY KEY).
• The third line is super-simple; it says that we want a column called JokeText, which will contain text (TEXT).
• The fourth line defines our last column, called JokeDate, which will contain data of type DATE, and which cannot be left blank (NOT NULL).

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 we created. ID will contain integers, JokeText will contain text, and JokeDate will contain dates. MySQL requires you to specify a data type for each column in advance. Not only does this help keep your data organized, but it allows you to compare the values within a column in powerful ways (as we'll see later). For a complete list of supported MySQL data types, see the MySQL Reference Manual, or refer to Appendix C of the book version of this series.

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:

mysql> SHOW TABLES;

The response should look like this:

+-----------------+
| Tables in jokes |
+-----------------+
| Jokes           |
+-----------------+
1 row in set

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:

mysql> DESCRIBE Jokes;
+----------+---------+------+-----+------------+----------------+
| Field    | Type    | Null | Key | Default    | Extra          |
+----------+---------+------+-----+------------+----------------+
| ID       | int(11) |      | PRI | 0          | auto_increment |
| JokeText | text    | YES  |     | NULL       |                |
| JokeDate | date    |      |     | 0000-00-00 |                |
+----------+---------+------+-----+------------+----------------+
3 rows in set

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:

mysql> DROP TABLE tableName;