VOICE Home Page: http://www.os2voice.org

April 2004 Newsletter Index < Previous Page | Next Page > Feature Index editor@os2voice.org

In close collaboration: MySQL and OS/2
Part 3: Commands of console and SQL

Today we're starting the third part of my article and this time, we'll take a close look at the console commands and deal with the principles of SQL. To serve as an example, we'll create an adress database.

In order to have a new database named "Adresses" we'll make use of the WPS object Create Database, that we created as part of our previous article.

After having clicked on open, the database will be created. Now open the MySQL console and type the following command:

mysql> show databases;
+------------+
| Database   |
+------------+
| adresses   |
| mysql      |
| test       |
+------------+
3 rows in set (0.00 sec)

As you might notice, creation of the database was successful. Next, we'll select that newly created database named "adresses" by using the "use" console command:

mysql> use adresses;
Database changed

Before going on, we need to do some theory first. Basically, there are two types of commands that we'll deal with:

1. The console commands (listed by the Help command)
2. The SQL commands

Console commands

Let's have a brief discussion of the console commands. To get a list of them, we'll use the "Help" command, as noted above. This will display the follwoing output:

mysql> help
help    (\h)    Display this help.
?       (\?)    Synonym for `help'.
clear   (\c)    Clear command.
connect (\r)    Reconnect to the server. Optional arguments are db and host.
edit    (\e)    Edit command with $EDITOR.
ego     (\G)    Send command to mysql server, display result vertically.
exit    (\q)    Exit mysql. Same as quit.
go      (\g)    Send command to mysql server.
nopager (\n)    Disable pager, print to stdout.
notee   (\t)    Don't write into outfile.
pager   (\P)    Set PAGER [to_pager]. Print the query results via PAGER.
print   (\p)    Print current command.
quit    (\q)    Quit mysql.
rehash  (\#)    Rebuild completion hash.
source  (\.)    Execute a SQL script file. Takes a file name as an argument.
status  (\s)    Get status information from the server.
tee     (\T)    Set outfile [to_outfile]. Append everything into given outfile.
use     (\u)    Use another database. Takes database name as argument.

help, \h, \?

Displays the help topics

clear, \c

Whenever you enter a command (whether console or SQL command) containing the \c sequence, it will make the console ignore the actual command and simply return to the prompt:

mysql> select * from \c
mysql>

The above example demonstrates how the command is ignored.

connect, \r

Re-establishes a connection to a database.

edit, \e

Starts an editor (in my case, it's vi.exe). This enables you to edit several SQL commands or SQL scripts. The environment variable set Editor is used to specify the editor used. For example set editor=d:\os2\e.exe. If you start the console with this environment variable set, the edit command, will invoke the OS/2 system editor.

ego, \G

Sends a SQL command to the database and returns the results line per line.

exit, \q

Terminates the console.

go, \g, ;

Sends a SQL command to the database and returns the results in a table.

tee, \T and notee, \t

This set of commands is used to toggle logging. If enabled, the logging will write all in- and outputs into a specified file, e.g.tee d:\mysql.log.

pager, \P, nopager

This command is not supported on OS/2.

print, \p

I'm a little puzzled about this command. Even after various attempts I have to admit that I have no idea how this command should work on on OS/2.

quit, \q

This is the same as exit: Terminates the console.

rehash, \#

This rebuilds the internal hash table of the database.

source, \.

Executes the specified file. Use your editor to create a file (for example F:\os2_prog\datenbank\mysql\bin\test.sql) which contains the command show databases;. Next, change to your console session and enter the command source test.sql. You'll notice that it will execute the commands contained in the files.

status, \s

This will show various information about database connections.

use, \u

We already used this command several times. It is used to specify the database to work with.

SQL commands

Now let's take a look at the SQL commands. In SQL (and therefore in MySQL too) there's a pocketful of commands available which can be grouped into three categories.

1. DDL (Data Definition Language): These commands are used to create, edit or delete the database structures, for instance:

   create

   This creates a database object (e.g. database, table, index, etc.). Actually we already used create Database in order to create the database "Adresses".

   drop

   Deletes database objects.

   alter

   Changes attributes of database objects.

2. DCL (Data Control Language): This group of commands affects database access control, for instance:

   grant

   Create or edit access to database data.

   revoke

   Remove access rights to database data.

3. The last group of commands is called DML (Data Manipulation Language). These commands are used to read, delete or write records into one or more tables of a database. (In other database systems - like Oracle for instance - this will also apply to so-called views/queries. As of now, we won't deal with them because they are not supported in MySQL for OS/2.) This final group is made up of the following commands:

   select

   Retrieves data from one or more tables according to specified criteria.

   insert

   Creates and stores one or more new records in a table.

   update

   Stores one or more changed/edited records in a table.

   delete

   Deletes one or more records from a table.

A table is part of a database (or, to put it this way: A database contains multiple tables). A table is quite simply a two-dimensional array of data. Tables can handle different types of data (like text, date, graphics, etc.)

Table names are unique within a database. Nevertheless, a specific table name might be used in different databases.

Tables can have multiple indices. An index is used by MySQL to retrieve records within a table. This is quite the same as the index of a book. If you are looking for a specific term in a book, you might lookup the books index first to find the page number. Of course you might also search the entire book from the first page to the last page (or vice versa) in order to find the term. But this will take a while. Try to find a specific term in a book of 500 pages using this method - it'll take you days or even weeks. ;-)

According to my information, MySQL can use up to 32 indices for each single table. An index is build by using one or more fields. The resulting data item (made up of these fields) is also referred to as Key.

There's a special type of index which is called "primary index" (frequently also referred to as "primary key"). There's only one primary index allowed per table and its keys have to be unique, that's to say that the key is allowed to occur only once in the index.

In order to show what this all is about, we'll create a table called "Country". First, one should always try to determine the fields (type and size) required for the table, as well as number of indices and what the primary index should be like. Of course you're free to change all this at a later stage, but this will produce additional load for the database.
Our adress database does only require a simple structure like this:

As you might see in the last column, we used the countrycode field as primary index (PK: "Primary Key"). The reason for this is that there are no two countries in the world that carry the same country code - thus, the country code is unique, just like the primary key needs to be.

The remaining question now is how to actually create such table? First, we have to select the database »Adresses« that we want to use for holding the new table. If you already didn't do it within the example, enter the following command:

mysql> use adresses;
Database changed

For table creation, we'll use the command Create Table. Its syntax basically looks like this:

create table <tablename>
   (<fieldname> <type> (<length>) [null|not null] [defaultvalue] [autovalue],
   (<fieldname> <type> (<length>) [null|not null] [defaultvalue] [autovalue],
   .....
   (<fieldname> <type> (<length>) [null|not null] [defaultvalue] [autovalue],
   primary key (fieldname, fieldname, ....));

This might look a bit complicated, but it ain't. Just take a look at the following code and compare it to the table layout described above.

mysql> create table Country (
    ->   countrycode char(3) not null,
    ->   countryname varchar(45) not null,
    ->   prefixl varchar(10),
    ->   primary key(countrycode));
Query OK, 0 rows affected (0.13 sec)

Now what does each line of code stand for?
The first line starts with create table Country. This line actually tells the server that a new table named »Country« is about to be created. Everything following the »left bracket« is used to define the tables columns (the fields that is).
The next line countrycode char(3) not null, tells the server that the table should provide a column named "countrycode". This column (and the associated data field) will be used to store a text string ("char"). The contents of this field can be up to three characters. The parameter "not null" means that this field is not allowed to be empty.

Almost the same applies to the next line of the command (which refers to the next column or data field of the table). Except for the fact, that the field name is "countryname" and the maximum amount of characters to be stored is 45. Another difference is the data type "Varchar" ("variable"), that is used to define the field type. "Char" and "Varchar" behave almost identical. Both types are used to store alphanumerical data strings (characters and digits) of up to 255 bytes total length. The actual difference between them is, that "Char" makes the field being padded with space characters if its contents are smaller than the field size (this is what e.g. dBase does too). This means that if "AT" is to be stored for instance, it will internally be converted to "AT " (note the trailing space!). If the data type on the other is "Varchar", there'll be no padding with spaces but the contents are rather stored in their actual length. For example "Austria" will be stored as "Austria". (Note that there's no trailing spaces.) With this type of data you'll also need to specify the maximum length of the fields contents - according to our example it's 45. The actual length of the contents is internally managed and determined by the database engine.

You'll always have to figure out first, what data type should be used preferrably for the creation of a field and how the field might be used in the future. My personal way of determining goes like this: Fields that store contents of up to five characters use "Char", those with larger contents are defined as "Varchar".

The next line prefix varchar(10), again is quite similar but this time, we omit "not null". This allows the field to be empty (users might not know the international dial prefix of a country...).

With the last line of code primary key(kurzbez));, things become more interesting, as the line starts with the keyword parameter "primary key" and it tells MySQL that we're about to define the tables primary index. The bracket contains all fields in their required sequence - separated by commas - that are used to build the key. In our case, we'll only use the countrycode field as primary key (primary index).

After pressing the return key, MySQL will either show a confirmation or an error message.

If everything went well, you'll now have a new table called "Country". You might want to check if that is really true by simply using the show tables command we already know:

mysql> show tables;
+--------------------+
| Tables_in_adresses |
+--------------------+
| Country            |
+--------------------+
1 row in set (0.00 sec)

Before concluding this article, let me quickly show you how to insert a data record into the table. This is accomplished by using the command Insert into.

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

To understand what it's about, take another look at the command create table from the example mentioned above. In order to see whether our record actually is contained in the table, enter the following command:

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

In the next part of the article, we'll take a closer look at the command Insert into.

Additional information is available at the following website(s): http://www.mysql.com/doc/de/index.html http://www.rent-a-database.de/mysql/

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

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