MySQL - Creating/Editing Columns and Records


This article will walk you through creating/editing columns in tables. It will also walk you through adding rows to your tables.

Contents

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

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;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment | 
| name  | varchar(45) | YES  |     | NULL    |                | 
| email | varchar(45) | YES  |     | NULL    |                | 
+-------+-------------+------+-----+---------+----------------+

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", 'dave@domain.com');

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`;

+----+--------------+-----------------+
| id | name         | email           |
+----+--------------+-----------------+
|  1 | David Davies | dave@domain.com | 
+----+--------------+-----------------+

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`;

+----+--------------+-----------------+
| id | name         | email           |
+----+--------------+-----------------+
|  1 | Dave Davison | dave@domain.com | 
+----+--------------+-----------------+

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.



Was this content helpful?




© 2011-2013 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License


See license specifics and DISCLAIMER