MySQL data directory.

Low cost ecommerce web development India flash website design

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

Creating a Table

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 Appendix C.

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;

+----------+---------+------+-----+------------+----------------+

+----------+---------+------+-----+------------+----------------+

| ID | int(11) | | PRI | NULL | auto_increment |

+----------+---------+------+-----+------------+----------------+

3 rows in set

Getting Started with MySQL

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;

website designer freelance ASP PHP ecommerce web developer
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110