Adding, modifying, selecting, deleting MySQL data
Well, now it’s time to learn the most important SQL commands, also called queries. To perform the SQL queries, you can enter MySQL through telnet, if your host configured a MySQL account for you, or use your own computer if you already downloaded and installed MySQL on your own computer. Or use these queries in your PHP or Perl scripts. First you have to connect to your database and than you can perform any of the following SQL queries:
After connecting to our database, first we must create a table where our data can be stored. Remember the table in the beginning of MySQL tutorial:
| FIRSTNAME | LASTNAME | AGE | SEX | ID |
| John | Smith | 24 | M | 19754 |
| Angela | Power | 29 | F | 765 |
Here’s how we created it:
CREATE TABLE members (firstname VARCHAR (25), lastname VARCHAR (25), age INT, sex VARCHAR (10), ID INT NOT NULL AUTO_INCREMENT);
We created the first table in our database called members.
Well, now let’s learn how to insert, modify, select or delete data from our table. The SQL queries to do that are:
INSERT, UPDATE, SELECT and DELETE.
Here are examples:
INSERT INTO members (‘John’, ‘Smith’, 24, ‘M’, ‘19754’);
And some important notes:
1. All datatypes of type STRING (i.e. char, varchar, text, blob, etc.) must be surrounded in single quotes, except type INT, or an error will occur.
2. The same number of values must be inserted as fields are contained within a record, i.e. five in our above example.
UPDATE members SET ID = ID+24 WHERE (lastname = ‘Smith’);
This query increments ID of all the Smith by 24.
SELECT age FROM members WHERE (firstname = ‘John’);
This query locates age (24) of all records with the first names John.
We can also delete records from the table:
DELETE FROM members WHERE (age = 29);
Delete Angela Power from the table members.
There are bunch of other SQL commands, like: AND (&&), OR (||), NOT (!), ALTER, ADD, DROP, RENAME, LIKE, ORDER BY, and so forth.
Well, congratulations! you are now a SQL programmer. For the details, go to any bookstore and pick up a SQL quick reference.