Virtual Workshops

Part of keithjbrown.co.uk

Main Menu

• Server Environment
• MySQL
• PHP Databases
• Web Design
• Excel

Using MySQL - Manipulating Structures

Workshop Requirements

You should have completed Parts One, Two and Three of this series.

You should also have access to the MySQL command line client software.

You should also have full permissions on a database.

Introduction

By this point you should be very familiar with SQL statements and manipulating data within database TABLEs. In this part of the Using MySQL series we will look at manipulating database tables rather than just the data within. Before beginning, ensure that you have opened your own MySQL database where you entered all the CD data.

ADDing a Column

When we created the 'cds' TABLE, based on the Data Collection Form, we omitted the 'tracks' field. It is time to correct this by adding a 'tracks' COLUMN to our 'cds' TABLE. This is done by using the ALTER statement. With the ALTER statement you can add, remove or change a table's columns with a general syntax of:

ALTER TABLE <table_name> 
ADD/DROP <column_name> [datatype];

As we want to add a new COLUMN called 'tracks' to our 'cds' table we should enter this:

mysql> ALTER TABLE cds 
    -> ADD tracks INT(2);

Using the SELECT command we can confirm that there is now an empty or NULL 'tracks' column in every row.

mysql> SELECT cdID, artist, title, tracks
    -> FROM cds;      
+------+------------+----------------+--------+
| cdID | artist     | title          | tracks |
+------+------------+----------------+--------+
|    2 | jamiroquai | A Funk Odyssey |  NULL  |

Mini Exercise

Using the UPDATE command learned in Part3 you can now add all the 'tracks' data to your 'cds' table.

MODIFYing a TABLE

As well as ADDing new COLUMNs we could also change an existing one by using MODIFY in the statement.

ALTER TABLE <table> 
MODIFY <column> <new_definition>

So to change the newly added track COLUMN to hold 3 figure values we would enter:

mysql> ALTER TABLE cds 
    -> MODIFY tracks INT(3); 

And to change this back to hold 2 figure values:

mysql> ALTER TABLE cds 
    -> MODIFY tracks INT(2);

Copying Tables

Before we look at the other table manipulation commands it will be useful to make a copy of our 'cds' table (in fact we'll make several to illsutrate the process) so that we can mess about and still have all the data we have input. Copying tables in MySQL can sometimes be done in one statement, but some column attributes cannot be copied so it is usually necessary to use at least two statements. To gain a good understanding of what is going on let's break this process down into its parts.

The Long Way

The really long 'manual' way to copy a table is to create a new table indentical to the first, THEN copy all our inputted data into the new table and finally modify the new table to create the 'auto increment' and 'primary key' attributes on the 'cdID' field.

So first we should double check the structure of the 'cds' table using the describe command.

mysql> DESCRIBE cds;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| cdID   | int(3)      |      | PRI | NULL    | auto_increment |
| artist | varchar(20) | YES  |     | NULL    |                |
| title  | varchar(30) | YES  |     | NULL    |                |
| year   | int(4)      | YES  |     | NULL    |                |
| label  | varchar(20) | YES  |     | NULL    |                |
| bought | date        | YES  |     | NULL    |                |
| tracks | int(2)      | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)	

We now need to make the new 'cds2' table that matches exactly.

mysql> CREATE TABLE cds2 (
    -> cdID INT(3) auto_increment primary key,
    -> artist VARCHAR(20),
    -> title VARCHAR(30),
    -> year INT(4),
    -> label VARCHAR(20),
    -> bought DATE,
    -> tracks INT(2));
Query OK, 0 rows affected (0.00 sec)

Use the DESCRIBE command again, this time to check that the 'cds2' table matches the 'cds' table. Once that is complete, we now have to copy the data from one table to the other. This is done by mixing the INSERT and SELECT statements so that you are inserting into one table the selection from the other.

mysql> INSERT INTO cds2 
    -> SELECT * FROM cds;

Check that this has worked by issuing a select command on the 'cds2' table .

mysql> SELECT cds2.cdID, cds2.artist, cds2.title 
    -> FROM cds2;
+------+------------+----------------+
| cdID | artist     | title          |
+------+------------+----------------+
|    2 | jamiroquai | A Funk Odyssey |

There is a problem however. As can be seen in the DESCRIBE statement, the 'cdID' COLUMN in the new TABLE has been created without the auto_increment and primary key properties. We can use the ALTER TABLE.....MODIFY statement we learned above to add the auto_increment and primary key properties:

mysql> ALTER TABLE cds2 
    -> MODIFY cdID INT(3) auto_increment primary key;

The Short Way

There is another way to achieve the same result. Rather than creating the new table then inserting the data from the original we can CREATE another table called 'cds3' with a SELECT statement:

mysql> CREATE TABLE cds3 
    -> SELECT * from cds;

A SELECT statement will confirm that this has worked.

mysql> SELECT cds3.cdID, cds3.artist, cds3.title 
    -> FROM cds3;
+------+------------+----------------+
| cdID | artist     | title          |
+------+------------+----------------+
|    2 | jamiroquai | A Funk Odyssey |

We still have to add the structural constraints.

mysql> ALTER TABLE cds3 
    -> MODIFY cdID INT(3) auto_increment primary key;

All in one statement

We can make copy of the table in one statement if we first create the cdID field (with auto_increment and primary key) then insert the other fields as specified in the INSERT statement, which is a combination of boths methods.

mysql> CREATE TABLE cds4 (cdID INT(3) auto_increment primary key) 
    -> SELECT 
    -> cds.artist, 
    -> cds.title, 
    -> cds.year, 
    -> cds.label, 
    -> cds.bought, 
    -> cds.tracks
    -> FROM cds;

DROP-ing Columns and Tables

As we saw in a previous Virtual Workshop we can delete data from a table by using the DELETE command. This will leave the table structure intact and simply remove the data. If we want to delete a structural element of the database (a column or table) we have to use the DROP command.

DROP <element> <name> 

So to remove a column from our 'cds2' table - lets say the newly added 'tracks' column - we would use the DROP and ALTER TABLE commands together (as we are altering a table).

mysql> ALTER TABLE cds2 
    -> DROP tracks;

Similarly if we wanted to remove the whole table we would issue the following:

mysql> DROP TABLE cds2;

It is important to be sure that you know exactly what want to do before you issue a DROP Command. If you created another 'cds3' table in the previous section remove it as well. You can check that a table has in fact gone by asking MySQL to display all the tables.

mysql> show tables;
+------------------+
| Tables_in_kbrown |
+------------------+
| cds              |
| cds3             |
| cds4             |

In order to tidy things up - you should also delete tables 'cds3' and 'cds4'.

mysql> DROP TABLE cds3;

mysql> DROP TABLE cds4;

Exporting and Importing Data

The final thing that we will look at in this workshop is the exporting and importing of data. You may want to export data so that it can be imported into another package, or similarly we may wish to import data from a different package.

IMPORTANT: In newer versions of MySQL these methods may be disabled. You should be able to turn this feature on by issuing the command:

mysql> --local-infile=1

Otherwise contact your systems administrator for advice. The version of MySQL that comes with the FoxSev distribution discussed in Setting Up a Server does allow this.

Exporting Data

When we are exporting files from MySQL we need an area that the 'MySQL' package can write to AND that you can access to get your data. You may have to contact your systems administrator for advice on a directory. For the purpose of this section we will use Unix commands and the example directory '/tmp/vworks'. If you are a Windows users you will have to substitute Windows commands (i.e. 'dir' for 'ls' and 'type' for 'cat') and directory strucures (e.g. 'C:\temp\vworks' for '/tmp/vworks').

The command to export data to a text file is similar to the standard SELECT command except instead of displaying the data on screen it goes into a file.

SELECT <columns> 
FROM <table_name> 
INTO OUTFILE <file_name>

IMPORTANT: We do not want to output the 'cdID' column as this is only useful within the database and so we need to specify the other columns explicitly.

mysql> SELECT 
    -> cds.artist, 
    -> cds.title, 
    -> cds.year, 
    -> cds.label, 
    -> cds.bought, 
    -> cds.tracks
    -> FROM cds INTO OUTFILE '/tmp/vworks/cds.txt';

In order to check that this has worked we need to exit MySQL to your system's command line (or you could open another 'shell' window). First Change Directory to '/tmp/vworks''.

$ cd /tmp/vworks

Then issue a 'list short' command.

$ ls

And you should see that your file is there, so far so good. Next we want to view the contents of the file and to do this we will use a 'cat' command.

$ cat cds.txt

You should see something like this..... (Thanks to Mark for these).

Jamiroquai A Funk Odyssey 2001 Sony soho2 2001-09-01 11
Abba Abbas Greatest Hits 1998 demon 2001-09-10 23
Various Now 49 2001 virgin 2001-09-10 40
westlife westlife 2000 jive 2000-06-09 13
Various Eurovision Song contest 2001 2001 EMI 2000-09-08 20

The space between each column is a 'tab'. This is because MySQL creates a 'tab delimited' file by default. It is important to know this if you are going to import the file to another database as it will likely ask for the delimited format to determine the differences between fields.

Importing data

Importing data is a very simliar process to exporting. The syntax for importing a file is:

LOAD DATA INFILE 'file_name.txt' 
INTO TABLE tbl_name (field1, field2...etc)

For a quick example we will import our exported data back into the cds TABLE. It is important to specify the field names as we are not importing data into every column - the first column 'cdID' is automatically numbered by MySQL. So to do it for real:

mysql> LOAD DATA INFILE '/tmp/vworks/cds.txt' 
    -> INTO TABLE cds (artist, title, year, label, bought, tracks);

You can now check that this has worked by issuing a SELECT command, where you should see duplicate entries in your table.

mysql> SELECT * 
    -> FROM cds;