VOICE Home Page: http://www.os2voice.org
|By Wolfgang Draxler © April 2004, Translation: Thomas Klein|
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:
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.
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.
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.
DDL (Data Definition Language): These commands are used to create, edit or delete the database structures, for instance:
DCL (Data Control Language): This group of commands affects database access control, for instance:
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:
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:
|1||countrycode||alphanumeric||3||AT, DE, ...||yes, PK|
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/
< Previous Page | Newsletter Index | Next Page >
VOICE Home Page: http://www.os2voice.org