MySQL - creating records and adding columns

Now we have the database and the first table created we can start adding records.

Again, this is very easy via the command line. We'll also add another column to the table, change existing records and delete unwanted records.

As usual, log into MySQL and tell it which database we are going to use:

mysql -u root -p
...
use pickled1;

Insert

Let's insert our first record. It's the email address of Dave, the guy who sits next to me at work:

INSERT INTO `colleagues` (`id` ,`email`)
VALUES
(NULL , 'dave@domain.com');

I find it easier to spread the command (especially as they get longer and more complicated) over several lines. It cleans the code up and makes it easier to review. Remember that MySQL will only execute the code once it reaches the semi-colon at the end.

The syntax order is pretty simple - name the columns and then enter the values you want in the columns. We entered NULL for the id as we set the id column to AUTO_INCREMENT.

Select

Let's see what was actually entered by selecting all entries in the colleagues table:

SELECT * FROM `colleagues`;

The output is exactly as expected - the id has a value of '1' and the email is as we entered.

Alter

Notice anything about the table? Well, I don't think it's too useful yet as I don't know who dave@domain.com is. I might remember now but not after I add the 3 other Dave's that I know.

I think we need to add a column called 'name'. Not only that, I want the column to come straight after the id column. That way, I can read the records like a book: id -> name -> email.

Let's do that:

ALTER TABLE `colleagues` ADD `name` VARCHAR( 45 ) NULL AFTER `id` ;

Now when we do a:

SELECT * FROM `colleagues`;

We see the new column entered but its value is 'NULL'.

Update

Let's insert a name:

UPDATE `colleagues` 
SET 
`name` = 'Dave Farquat' 
WHERE `colleagues`.`id` =1 ;

Again, I don't think the syntax needs too much explaining but do note the 'WHERE' line. Without this, all records would be updated to the name 'Dave Farquat' whereas we only wanted to update the record with the unique id of 1.

Insert

Let's add one more full record:

INSERT INTO `colleagues` 
(`id` , `name` , `email`)
VALUES 
(NULL , 'Paul Tomes', 'paul@domain.com');

Nice.

Delete

Dave spilled coffee on me earlier. Now I want to delete his record from the colleagues table. We do this will a simple:

DELETE FROM `colleagues` WHERE `colleagues`.`id` = 1;

Well, that's him gone.

In the next article, we'll add another user and give them restricted privileges so they can only access the pickled1 database.

PickledOnion

Digg it | del.icio.us | reddit | StumbleUpon