Preparation

To get the most out of this article I recommend following the previous one (linked above) and setting up the ‘mytestdb’ database and the ‘customers’ table.

In short, follow the article but don’t drop the table at the end.

Log in

Again, working with MySQL requires us to log in:

mysql -u root -p

You also can save user and password of mysql for logged in user edit and put in your root dir file with name “.my.cnf” and content :
[client]
user=USER
pass=PASS

Remember we need to specify which database we are working with:

use mytestdb;

Add a column

When we created the customers table we had two columns: id and email.

I don’t know about you, but I reckon a ‘name’ column may help as people don’t like being called by their email address!

Let’s do that now:

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

The syntax is fairly straightforward and inserts a ‘name’ column just after the ‘id’ column. The ‘name’ can be left blank (i.e. it is not required) and is, like the email, a normal text entry of up to 45 characters.

Of course, you may decide you want the name to be compulsory and so on – simply change the parameters to suit your needs.

Anyway, a quick check shows the new column:

mysql> SHOW columns FROM customers;

3 rows in set (0.01 sec)

Inserting a record

Our business is now picking up nicely and we have our first customer!

Let’s put their details into the table:

INSERT INTO `customers` (`id` ,`name`,`email`)
VALUES
(NULL , “David Davies”, ‘[email protected]’);

Again, once you start to use the syntax, you can see it makes logical sense. We named the columns we want to populate and then gave the details (note the order of the data must match the order of the named columns).
Selecting a record

Now we can select the record from the table:

SELECT * FROM `customers`;

Which gives us:

mysql> SELECT * FROM `customers`;

1 row in set (0.00 sec)

Cool.
Update a record

In the excitement of entering our first customer’s details, we got their name wrong. Ooops.

No problem, updating a record is very easy. This is where the unique id comes into play as we identify the record from the id:

UPDATE `customers`
SET
`name` = ‘Dave Davison’
WHERE `customers`.`id` =1 ;

a quick check shows the new record:

mysql> SELECT * FROM `customers`;

1 row in set (0.00 sec)

The record has been updated correctly.

Of course, you don’t need to check the records after every change, but it’s nice to confirm an action, especially when it is a new talent.
Delete

Unfortunately, Mr Davidson was not happy about being called the wrong name and is no longer our customer.

Let’s delete him from the database:

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

Once done, selecting all the records will return an empty set.
Summary

Manually administering databases can save a lot of time, especially when one column needs renaming or a few records need to be found.

As with the other MySQL commands, the syntax is straightforward and simple to use – checking the results is just as easy.

Categorised as: Linux