Introduction Creating the remote database Creating the local database OK, so now what? Deciding on fields Deciding on an index Creating the table Insert some data Querying the database Scripted querying Uploading onto the server

Create the table containing all fields and an index if required

OK we now have all our field names and types and we have our index information, are we ready to create the table - almost, we only need to decide on a name for our table, I am going to call mine `books'. One more thing, the field headers cannot contain any spaces and are case sensitive meaning one is called `My_BOoKs' then it must be reference to as `My_BOoKs' in your MySQL query otherwise you will get an error and no data, this means that we also need to decide on field headers for our fields, I am going to use `bookname',`isbn',`price' and `datepurchased' to represent my fields. To create the table we type the following when in the MySQL client;

	mysql> use databasename
	Database changed
        mysql> create table books (bookname varchar(255) not null,
            -> isbn bigint(10) zerofill not null,
            -> price decimal(3,2) not null,
            -> datepurchased datetime not null,
            -> index books_index (bookname(30),isbn,price,datepurchased));
        Query OK, 0 rows affected (0.01 sec)

You now have a table in your database, which contains all your four fields and contains an index for searching against. So by issuing the following command, you should see the following;

        mysql> show tables;
        +-----------------+
        | Tables_in_books |
        +-----------------+
        | books           |
        +-----------------+
        1 row in set (0.00 sec)

To make sure that the table has been created properly you issue the following command;

        mysql> desc books;
        +---------------+------------------------------+------+-----+---------------------+-------+---------------------------------+
        | Field         | Type                         | Null | Key | Default             | Extra | Privileges                      |
        +---------------+------------------------------+------+-----+---------------------+-------+---------------------------------+
        | bookname      | varchar(255)                 |      | MUL |                     |       | select,insert,update,references |
        | isbn          | bigint(10) unsigned zerofill |      |     | 0000000000          |       | select,insert,update,references |
        | price         | decimal(4,2)                 |      |     | 0.00                |       | select,insert,update,references |
        | datepurchased | datetime                     |      |     | 0000-00-00 00:00:00 |       | select,insert,update,references |
        +---------------+------------------------------+------+-----+---------------------+-------+---------------------------------+
        4 rows in set (0.00 sec)

If you saw that (or something similar) then you have successfully set up the table and are now ready to put data into it. The reason why the `zerofill' option was used in 'ISBN number' is because *all* ISBN numbers contain ten characters meaning that when we retrieve our information we too will get all ten characters.