Build Your Own DatabaseDriven Website Using PHP amp;amp; MySQL [Electronic resources] نسخه متنی

Creating a Table

The SQL commands we've encountered so far have been reasonably simple,
but as 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 fairly 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 "MySQL Column Types".

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
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 | NULL       | auto_increment |
| JokeText | text    | YES  |     | NULL       |                |
| JokeDate | date    |      |     | 0000-00-00 |                |
+----------+---------+------+-----+------------+----------------+
3 rows in set

As we can see, there are three columns (or fields) 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 task is as frighteningly easy as deleting a database. In
fact, the command is almost identical:

mysql>DROP TABLE tableName;