Getting
to Grips with mySQL - Page 4
by
Granville Barnett aka G | 13 July 2005
Step 7
Now we are going to update a record, here is where our id
column comes in very handy!! In the mySQL terminal type in
the command to view the records so that all columns appear
then find a user to which you want to modify their details –
look at their corresponding id number!! then type in the
following command:
UPDATE users SET
name=”Kirupa.com”,
location=”Spain”,
date=CURDATE()
WHERE id=4;
You should see:

See how I update record with id 4 (WHERE id=4) I changed the
name of the user and location of the user to Kirupa.com and
Spain respectively.
Hopefully by now the power of SQL is sinking in, and I've
barely touched the surface!! Now let me explain one last key
function that you will no doubt need – the DELETE statement.
Step 8
Once again display the contents of the users' table and find
someone you want to delete then make a note of their
corresponding id number. Type in the following:
DELETE FROM users WHERE id=4;
In the above
statement I deleted this row:

Sorry Kirupa. Now lets view the contents of our table again
and see if anything has changed.

See how the kirupa record has gone?? well I hope you do!!
 |
Note |
Use the DELETE
statement with great care as unlike some
applications etc (mySQL is not really an
application but rather a database
server) you cannot retrieve a previously
deleted record. It is possible to make
incremental backups of your database and
restore data that way – again this is
out of the scope of this tutorial. |
|
Conclusion
What we have just done is covered the following:
- Creating a database
- Creating a table and specifying the tables column
names and their acceptable values.
- Inserting data into our table
- Selecting our data in a specific way that we see
only the information we asked for and in a specific
order
- Updating a record in our table
- Deleting a record in our table
Hopefully by now you will know these statements well!
Don't forget that in languages such as PHP you can quote the
exact same query statements covered in this tutorial.
If you have any questions, please contact me or post on
the forums.
 |
page 3
of 3 |
|
|