Learning SQL Using phpMyAdmin

Data modification

In this section, we will learn the basic syntax for the INSERT, UPDATE, DELETE, and SELECT statements.

Adding Data with INSERT

Let's first examine the INSERT statement, by looking at the code phpMyAdmin generates when we do an Insert operation. We bring up the Insert sub-page, in Table view for the countries table, and we enter data about a country:

When we click Go, the data is inserted and phpMyAdmin shows us the INSERT statement used:

INSERT INTO `countries` ( `country_code` , `country_name` ) 

VALUES ('ca', 'Canada');

After the INSERT INTO part, we have the table name. In MySQL, we can enclose table names and column names within backticks, in case there are special characters in them, like reserved words or accented characters. Then we open a first set of brackets, listing the columns in which we want to insert, separated by commas. The reserved word VALUES follows, then the last set of brackets enclosing the values, in the same order as the columns list. If the values have a character data type, we have to enclose them within quotes.

We can now insert a city

INSERT INTO `cities` ( `id` , `city_name` , `latitude` , `longitude` , `population` , `country_code` ) 

VALUES ('', 'Sherbrooke', '45 23 59.00', '-71 46 11.00', 125000, 'ca');

Here, we put an empty value for id, because this column's auto-increment attribute will provide a value. We also see that the population value, being numeric, does not need to be surrounded by quotes.

Let's end this section by inserting some data for another country and city, which we will need later.

INSERT INTO `countries` ( `country_code` , `country_name` ) 

VALUES ('zh', 'China');

INSERT INTO `cities` ( `id` , `city_name` , `latitude` , `longitude` , `population` , `country_code` ) 

VALUES ('', 'Shanghai', '31 13 58.00', '121 26 59.99', 11000000, 'zh');