Tux Training

In this tutorial we’re going to cover how to Alter Records, Update Records, and how to Delete Records.

Please see these prerequisites:

1. Basics of MySql on the Commandline #1
2. Basics of MySql on the Commandline #2

Alter tables

mysql -u root -p test_database1then press ENTER. Then type your password and press Enter.

This command string establishes a connection to the MySQL server, specifically the database test_database1ALTER TABLE name ADD COLUMN flavor CHAR(25);

then press ENTER.

This command string will add a field, or column, to the table name. MySQL refers to table fields as columns.

These commands read pretty much like a sentence in English:

ALTER the TABLE name by ADDing a COLUMN called flavor. Then make party a column that contains a maximum of 25 characters.

Now the table name is organized like this, with a new field called flavor:

Column	Datatype	Properties
id	INT	primary key, not null, auto increment
first	CHAR(25)
last	CHAR(25)
flavor	CHAR(25)

 

Type:

SELECT * FROM name;

then press ENTER.

Now you should see the ID column, the first name, last name and flavor column. While data will be there for the first three columns, all the data in flavor will read “null”.

Update Records
Type:

UPDATE name SET party=’Kubuntu’ WHERE (last=’Torvalds’ OR last=’Shuttleworth’);

then press ENTER.

The UPDATE command fills in the blank entries in the name table that were created when you added the party field.

This string of commands reads like this:

UPDATE the table name. SET the party field to “Kubuntu” WHERE the last name of the president is either “Torvalds” OR “Shuttleworth.”

Type:

SELECT * FROM name;

then press ENTER.

Now you should see the “flavor” column has data for Linus Torvalds and Mark Shuttleworth, showing Kubuntu as their flavor of choice.Type:

UPDATE name SET party=’Redhat’ WHERE (last=’Cox’ OR last=’Stallman’);

then press ENTER.

This updates the party affiliations for Cox and Stallman.

If you Type:

SELECT * FROM name;

then press ENTER again, you’ll see that now Alan Cox and Richard Stallman’s flavor field has been updated with ‘Redhat’

Delete Records

Type:

DELETE FROM name WHERE id>2;

then press ENTER. The DELETE command deletes records that match the criteria you set. In this case, you told MySQL to DELETE from the table name any records WHERE the value for id is greater than 2.

Type: SELECT * FROM name; then press ENTER.

The table should now hold only the top 2 records.

You can now close the mysql session and exit your terminal for this tutorial.