Virtual OS/2 International Consumer Education
VOICE Home Page: http://www.os2voice.org 		May 2004

Newsletter Index
< Previous Page | Next Page >
Feature Index

editor@os2voice.org

In close collaboration: MySQL and OS/2
Part 4: Insert and Select commands

By Wolfgang Draxler © May 2004, Translation: Jürgen Gaida

In the last article I touched on the insert command. Today we take a closer look at this command. As mentioned last time insert creates and stores one or more new records.

The basic syntax is:

Insert into <Table> [(Field, Field, ...)]
   values (value [, value, value, ...), (value [, value, value, ...), ...]

As you can see from above, multiple records can be inserted. But before we start with more complex things, we first take a look at a command from last month.

mysql> insert into country
    ->   values ('A', 'Austria', '+43');
Query OK, 1 row affected (0.03 sec)

This command inserts a record with the values for Austria. You may have noticed there were no fields listed. The field list is optional if you want to store data in all the fields. The sequence for the fields is the same as stated in the command Create Table. In this case the command could have been written like this:

mysql> insert into country (countrycode, countryname, prefix)
    ->   values ('A', 'Austria', '+43');

If you execute this command, you will probably get an error message stating the record already exists. The reason is quite simple: While creating the table definition, we assigned countrycode as the primary key and therefore the value "A" cannot be inserted a second time. (You need to delete the existing record before inserting it but this is explained at a future date.) You could of course use other values, Germany, for example:

mysql> insert into country (countrycode, countryname, prefix)
    ->   values ('D', 'Germany', '+49');
Query OK, 1 row affected (0.03 sec)

To see the records stored so far, key in the following command:

mysql> select * from country;
+-------------+-------------+---------+
| countrycode | countryname |  prefix |
+-------------+-------------+---------+
| A           | Austria     | +43     |
| D           | Germany     | +49     |
+-------------+-------------+---------+
2 rows in set (0.02 sec)

(If a record is missing, do an insert.)

Insert can store several records in one run:

mysql> insert into country (countrycode, countryname, prefix) values
    ->   ('CH','Switzerland','+41'),
    ->   ('GB','GreatBritain','+44'),
    ->   ('USA','United States of America','+1');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicate: 0  Warnings: 0

Of course you may omit the field list here as well but only if you know the sequence of fields. For example:

mysql> insert into country values
    ->   ('I','Italy','+39'),
    ->   ('H','Hungary','+36');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicate: 0  Warnings: 0

Now we insert records without the prefix value:

mysql> insert into country (countrycode, countryname) values
    ->   ('L','Luxemburg'),
    ->   ('NL','Netherlands'),
    ->   ('B','Belgium');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicate: 0  Warnings: 0

Now that we have stored some records with Insert, we may view these records as well as run some other queries against the database. This is done with the pussiant command Select. We have used this command previously to view other information.

The syntax for Select is like this:

Select <Field> [, <Field>, ...] || *
   from <Table> [, <Table>, ...]
   [where {condition}]
   [order by [<Field> [,<Field>, ...]]]

Looks quite simple, doesn't it? ;-)

However, take a look at the next example:

mysql> select countrycode, countryname, prefix from country;
+-------------+-------------------------+---------+
| countrycode | countryname             | prefix  |
+-------------+-------------------------+---------+
| A           | Austria                 | +43     |
| D           | Germany                 | +49     |
| CH          | Switzerland             | +41     |
| GB          | GreatBritain            | +44     |
| USA         | United States of America| +1      |
| I           | Italy                   | +39     |
| H           | Hungary                 | +36     |
| NL          | Netherlands             | NULL    |
| L           | Luxemburg               | NULL    |
| B           | Belgium                 | NULL    |
+-------------+-------------------------+---------+
10 rows in set (0.08 sec)

Notice that all records and all fields we stored so far have been shown. To access all fields you can use the wildcard "*" (asterisk). By replacing the field list with "*" the statement becomes:

mysql> select * from country;
+-------------+-------------------------+---------+
| countrycode | countryname             | prefix  |
+-------------+-------------------------+---------+
| A           | Austria                 | +43     |
| D           | Germany                 | +49     |
| CH          | Switzerland             | +41     |
| GB          | GreatBritain            | +44     |
| USA         | United States of America| +1      |
| I           | Italy                   | +39     |
| H           | Hungary                 | +36     |
| NL          | Netherlands             | NULL    |
| L           | Luxemburg               | NULL    |
| B           | Belgium                 | NULL    |
+-------------+-------------------------+---------+
10 rows in set (0.00 sec)

Of course you can skip one or more fields. Just give it a try! Nothing bad can happen except that MySQL may present an error message.

Next we include the Where clause in our query. In a Where clause the MySQL server checks if the condition is "true." If so, the server returns the record, otherwise it is ignored and the next record is processed. Such a statement looks like the following example:

mysql> select * from country where countrycode='A';
+-------------+-------------+---------+
| countrycode | countryname | prefix  |
+-------------+-------------+---------+
| A           | Austria     | +43     |
+-------------+-------------+---------+
1 row in set (0.04 sec)

The countrycode with value "A" is displayed, as you see. As there is only one country with countrycode "A," Austria is displayed. The reason for this is the term where countrycode='A'. The equal sign is called a comparison operator. There are lots of such comparison operators (and those operators are valid on almost all SQL databases):

comparison
operator

description

example
=

Compares both terms for equality. Upper and lower case is considered.

countrycode='A'
<

The term to the left must be less than the term on the right.

countrycode<'D'
<=

The term to the left must be less than or equal to the term on the right.

countrycode<='D'
>

The term to the left must be greater than the term on the right.

countrycode>'D'
>=

The term to the left must be greater than or equal to the term on the right.

countrycode>='D'
!= or <>

Compares both terms for inequality. Case is considered.

countrycode<>'D'
like

The right term can contain the wildcard "_" (underscore) to match any one character, or "%" (percent) to match zero or more characters. If the remaining characters are equal, the record is returned. Those wildcards could be used repeatedly. If you use "not" in front of "like" the reverse happens.

countryname like '%l%'

countryname like '%land_'

countryname not like '%land_'

prefix like '+_9'

prefix not like '+_9'
in

This operator does the same as the "=" operator but with "in" the right term is a list of values to match. Similar to "like" you can use the operator "not".

countrycode in ("A", "D")

countrycode not in ("A", "D")
is null

is not null

With this operator MySQL searches for records where the term is empty or not empty.

prefix is null

prefix is not null
Between <value1> and <value2>

Entries are searched where the term is between <value1> and <value2>. Works with "not" as well.

countrycode between 'A' and 'D'

countrycode not between 'A' and 'D'

There are logical comparison operators to combine one or several conditions.

operator

description
and

This operator is true if both conditions are true.
or

This operators is true if one or both conditions are true.
not

the Not operator reverses the result (thus "true" or "false") of the condition. This means, if the condition returned the result "true", then "Not" will give the result "false".

Now we take a look at some examples.

First we display all countries where the countrycode is alphabetically less than "GB".

mysql> select * from country where countrycode < 'GB';
+-------------+-------------+---------+
| countrycode | countryname | prefix  |
+-------------+-------------+---------+
| A           | Austria     | +43     |
| D           | Germany     | +49     |
| CH          | Switzerland | +41     |
| B           | Belgium     | NULL    |
+-------------+-------------+---------+
4 rows in set (0.05 sec)

Now we want to display all countries where the countrycode is alphabetically greater than "GB".

mysql> select * from country where countrycode > 'GB';
+-------------+-------------------------+---------+
| countrycode | countryname             | prefix  |
+-------------+-------------------------+---------+
| USA         | United States of America| +1      |
| I           | Italy                   | +39     |
| H           | Hungary                 | +36     |
| NL          | Netherlands             | NULL    |
| L           | Luxemburg               | NULL    |
+-------------+-------------------------+---------+
5 rows in set (0.00 sec)

Now we combine the two conditions. We have two options to do this . . .

mysql> select * from country where countrycode < 'GB' or countrycode > 'GB';
+-------------+-------------------------+---------+
| countrycode | countryname             | prefix  |
+-------------+-------------------------+---------+
| A           | Austria                 | +43     |
| D           | Germany                 | +49     |
| CH          | Switzerland             | +41     |
| USA         | United States of America| +1      |
| I           | Italy                   | +39     |
| H           | Hungary                 | +36     |
| NL          | Netherlands             | NULL    |
| L           | Luxemburg               | NULL    |
| B           | Belgium                 | NULL    |
+-------------+-------------------------+---------+
9 rows in set (0.01 sec)

. . . or the nifty method: 

mysql> select * from country where countrycode <> 'GB';
+-------------+-------------------------+---------+
| countrycode | countryname             | prefix  |
+-------------+-------------------------+---------+
| A           | Austria                 | +43     |
| D           | Germany                 | +49     |
| CH          | Switzerland             | +41     |
| USA         | United States of America| +1      |
| I           | Italy                   | +39     |
| H           | Hungary                 | +36     |
| NL          | Netherlands             | NULL    |
| L           | Luxemburg               | NULL    |
| B           | Belgium                 | NULL    |
+-------------+-------------------------+---------+
9 rows in set (0.00 sec)

Here are examples with operator Like:

mysql> select * from country where countryname like '%land%';
+-------------+--------------+---------+
| countrycode | countryname  | prefix  |
+-------------+--------------+---------+
| CH          | Switzerland  | +41     |
| NL          | Netherlands  | NULL    |
+-------------+--------------+---------+
2 rows in set (0.00 sec)

Or all other countries:

mysql> select * from country where countryname not like '%land%';
+-------------+-------------------------+---------+
| countrycode | countryname             | prefix  |
+-------------+-------------------------+---------+
| A           | Austria                 | +43     |
| D           | Germany                 | +49     |
| GB          | GreatBritain            | +44     |
| USA         | United States of America| +1      |
| I           | Italy                   | +39     |
| H           | Hungary                 | +36     |
| L           | Luxemburg               | NULL    |
| B           | Belgium                 | NULL    |
+-------------+-------------------------+---------+
8 rows in set (0.00 sec)

One example for operator in:

mysql> select * from country where countrycode in ('A','D','GB');
+-------------+-----------------+---------+
| countrycode | countryname     | prefix  |
+-------------+-----------------+---------+
| A           | Austria         | +43     |
| D           | Germany         | +49     |
| GB          | GreatBritain    | +44     |
+-------------+-----------------+---------+
3 rows in set (0.03 sec)

Now we display all countries where we do not know the prefix:

mysql> select * from country where prefix is null;
+-------------+-------------+---------+
| countrycode | countryname | prefix  |
+-------------+-------------+---------+
| NL          | Netherlands | NULL    |
| L           | Luxemburg   | NULL    |
| B           | Belgium     | NULL    |
+-------------+-------------+---------+
3 rows in set (0.02 sec)

. . . and finally, all countries which we stored between GreatBritain and Luxemburg. Again, we have two options here:

mysql> select * from country where countrycode >= 'GB' and countrycode <= 'L';
+-------------+-----------------+---------+
| countrycode | countryname     | prefix  |
+-------------+-----------------+---------+
| GB          | GreatBritain    | +44     |
| H           | Hungary         | +36     |
| I           | Italy           | +39     |
| L           | Luxemburg       | NULL    |
+-------------+-----------------+---------+
4 rows in set (0.00 sec)

. . . or:

mysql> select * from country where countrycode between 'GB' and 'L';
+-------------+--------------+---------+
| countrycode | countryname  | prefix  |
+-------------+--------------+---------+
| GB          | GreatBritain | +44     |
| H           | Hungary      | +36     |
| I           | Italy        | +39     |
| L           | Luxemburg    | NULL    |
+-------------+--------------+---------+
4 rows in set (0.00 sec)

As stated before, experiment. For example, search for all countries between "C" and "Z" with no prefix.

This has covered the most important aspects of conditions and how to combine them. In the next article we discuss how to sort a result using order by. After that we tackle the commands update and delete.

References:

MySQL: http://www.mysql.de
MySQL for OS/2 (Yuri Dario): http://www.os2power.com/yuri/
MySQL for OS/2 (Netlabs): http://mysql.netlabs.org
MySQL documentation: http://www.mysql.com/doc/de/index.html
Further MySQL documentation: http://www.rent-a-database.de/mysql/

Wolfgang Draxler lives in Vienna with his wife Yvonne. He works for a software development and consulting company doing organisation and database programming. In his spare time, amongst other things, he is involved in the ongoing development of Sibyl for OS/2, a Delphi-like programming language.

Feature Index
editor@os2voice.org
< Previous Page | Newsletter Index | Next Page >
VOICE Home Page: http://www.os2voice.org