MySQL on Mac Part 2
by Magnus
We're continuing the MySQL series with the steps involved in creating tables and fields. In the first part (see the March issue of shuffle), we looked at logging into a MySQL database server running on a Mac, as well as how to see what databases are available, and how to create a database.
Create and Select Database
You learned this in part one of this series, so I'd like you to go ahead and log in to your MySQL server, and create a new database called "emug", and select it for use.
Tables, Fields and Field types
Each table has a number of fields. Think about a table that should hold information about EMUG members, for example. It should hold information about a member's membership number, their name, their mobile, and their email address. You also need to decide what type of data would be entered into each field. For example, membership number is a number, so only digits. Their name is obviously characters, etc. Finally you need to decide on a primary key. The primary key is a field that makes one record unique from all other records. In our example, membership number is what makes one member unique from all other members, in the database's eyes.
Create Table
Our next step is to create our table, "member", and set what fields goes into the table. When you create a table you also create the fields. You can later add, delete, and edit fields, but it's a good idea to try to get it as correct as possible from the start. We're now going to create a new table called "member", with the fields "membershipnumber", "name", "mobile", and "email" in it.
CREATE TABLE member (membershipnumber INT PRIMARY KEY, name VARCHAR(100), mobile VARCHAR(11), email VARCHAR(50));
This creates a table called member with four fields in it. The "membershipnumber" field can contain just digits ("INT") and it's the primary key. The other fields can contain characters and digits of various lengths ("VARCHAR").
You can now type "SHOW TABLES;" to see that MySQL did indeed create the new table.
Enter Data
So now we have a table, with fields, but we still don't have any data in the table. And the whole idea of a database is to work with data, so how do we get some data into the table? Try this:
INSERT INTO member (membershipnumber, name, mobile, email) VALUES (1, 'Michael Jackson', '050-1231234', 'michael@jackson.com');
INSERT INTO member (membershipnumber, name, mobile, email) VALUES (2, 'John McEnroe', '050-1234321', 'john@mcenroe.com');
This creates two records in the table. Notice how we made the membership numbers different (1 and 2)? That's because the primary key field has to contain a unique number for each record. Entering the same number for both members would not work.
Wrapping Up
That concludes our second part of the three part MySQL series. We covered how to create a table, set what fields should go into the table, and what data should go into each field. We also entered a little bit of data. In the next issue we will see how we can get some data out of a table. Obviously we don't just want to put data into the database, we also want to get data out.
SIDEBAR
MySQL AB recently released the 1.0 version of the GUI (Graphical User Interface) tools (dev.mysql.com/downloads/gui-tools/5.0.html). With them you can administer and work with MySQL databases through a point-and-click interface. We will get back to these tools in a future issue of shuffle.