Basic Walkthrough MySQL Part-3

 

Basic Walkthrough MySQL Part-3

This section will help you walk through development of simplest database program (utility). I will make it to happen so smoothly towards the end of this part you will not even believe that the utility is ready for use.

Of course, intention to keep it simple, will prevent us from having Graphical User Interface (GUI); still the main purpose of looking closely at MySQL will be served and along with the knowledge (know how) there will be another part, which is called amusement while learning. If we have to enjoy the product (MySQL) which is free, we must learn main features in smallest possible time; which is different person to person.

Once we succeed to setup simplest form of database, I promise I will introduce all to remote control unit with no additional expense (in fact REMOTE CONTROL was developed to use existing components). This unit helps to retrieve data from your computer via email commands defined by you. This is advised that the data transferred via email is not encrypted; so please use only in utmost urgency. Still this unit will be nicest tool to learn and experiment with Database and System.

            So, let us start. As we tried few commands in previous part, I will prefer to take you along with me to a nice short development tour. Mostly I will use MYSQL Command Line, which means that for purpose of understanding commands, I will be using MySQL DOS BOX. Don’t worry id you are not familiar with one or other term, because I will be showing snapshots to help you get used to.

First off all I would like to create a table with few columns. The definition is shown below. I will leave it up to you to see and guess which column is used for what data.

CREATE TABLE ADDRESS (                        FNAME CHAR(16) NOT NULL,                        MNAME CHAR(12),                        LNAME CHAR(16) NOT NULL,                        PHONE CHAR(15),                        CELL  CHAR(15),                        ADDR  CHAR(35)                       )

This will create the table in the current database that you are connected to. Should you have connected to your database it should have been as below.

Query OK, 0 rows affected (0.14 sec)

After creating table, I would emphasize on creating a primary key on First Name (FNAME) column. This will help us keep consistent data in our database. The key will be created as combination of two columns, FNAME and LNAME. Which basically help us search through the data when there are really big number of rows and will help us keep distinct data.

ALTER TABLE ADDRESS ADD CONSTRAINT My_FNAME_LNAME_PK PRIMARY KEY( FNAME, LNAME) Query OK, 0 rows affected (0.25 sec) Records: 0  Duplicates: 0  Warnings: 0

So, if we create primary key as a combination of FNAME and LNAME, there can’t be two identical combinations. For example, you can add ‘Tai Tang’ first time, but in subsequent entries ‘Tai Tang’ will not be accepted.

After creating primary key, you will insert some data. You can do this manually because as a sample data of imaginary records, there will be only four rows for our hands on. So, when you will enter data at DB2 CLP, following will be scene.

insert into address values ('Tai','','Tang','647-647-6477','416-614-6144','64 Pluto Way Brampton,ON,CA'); insert into address values ('Sabar','','Raikoti','905-509-5099','419-914-9144','1032 Mavis Rd Mississauga,ON,CA'); insert into address values ('David','M.','Brown','905-264-2644','647-746-7466','1216 Morning Star Drive Miss,ON,CA'); insert into address values ('Dilshaad','Sufi','Akhtar','905-932-9322','416-417-4177','3456 Horner Ave Etobicoke,ON,CA');

            If you want to check how primary key stops wrong data creep in try to enter same row as row #1 again.

 

insert into address values ('Tai','','Tang','647-647-6477','416-614-6144','64 Pluto Way Brampton,ON,CA'); ERROR 1062 (23000): Duplicate entry 'Tai-Tang' for key 'PRIMARY'

 

This will prevent duplicate data and if you really need two similar names exist in the table you need to set primary key more widely. Try eliminating ‘a’ from ‘Tang’.

 

insert into address values ('Tai','','Tng','647-647-6477','416-614-6144','64 Pluto Way Brampton,ON,CA') Query OK, 1 row affected (0.02 sec)

 

Now it allows. The check is done only on participating key columns. Others can have duplicate data.

            For our Address Book utility this table is set up. Further we will see few commands to retrieve data. Try some simple SQL to retrieve data in ADDRESS Table:

Select Fname,Lname,Phone,Cell from address +----------+---------+--------------+--------------+ | Fname    | Lname   | Phone        | Cell         | +----------+---------+--------------+--------------+ | David    | Brown   | 905-264-2644 | 647-746-7466 | | Dilshaad | Akhtar  | 905-932-9322 | 416-417-4177 | | Sabar    | Raikoti | 905-509-5099 | 419-914-9144 | | Tai      | Tang    | 647-647-6477 | 416-614-6144 | | Tai      | Tng     | 647-647-6477 | 416-614-6144 | +----------+---------+--------------+--------------+ 5 rows in set (0.03 sec)

Ops, I wanted only David, he called me last night.

Select Fname,Lname,Phone,Cell from address where fname=’David’;

So, it’s easy to select one out of many. While comparing strings you have to specify the correct case for all letters or you need to do the comparison using some function as below.

select Fname,Lname,Phone,Cell from address where upper(fname)=’DAVID’; +-------+-------+--------------+--------------+ | Fname | Lname | Phone        | Cell         | +-------+-------+--------------+--------------+ | David | Brown | 905-264-2644 | 647-746-7466 | +-------+-------+--------------+--------------+ 1 row in set (0.01 sec)

Another situation, I think the gentleman called me three weeks ago; mm I can’t recall the name but phone number was 416-509…… may be 647-509 . . .. Well I remember middle three digits were 509 ... a kind of… let me try

Select Fname,Lname,Phone,Cell from address where phone like (‘%509%’) +-------+---------+--------------+--------------+ | Fname | Lname   | Phone        | Cell         | +-------+---------+--------------+--------------+ | Sabar | Raikoti | 905-509-5099 | 419-914-9144 | +-------+---------+--------------+--------------+ 1 row in set (0.00 sec)

 

Oh, yeah, he was a nice guy. I gotta help him, let me call him.

There will be many situations; when you may remember one of the properties associated with a contact and then you can get hold of his/her number. Your friend’s friend once met with you and he was talking about some vacancies that are about to open at his workplace. He actually liked chat with you and had promised to recommend your name. They have a reputed setup and just yesterday you heard that S & R Software is hiring.  How will you get to him because your friend has gone back home for a long break?

insert into address values ('John','D','walker','419-932-9322','647-417-4879','promised help with JOB'); Query OK, 1 row affected (0.01 sec)

 

 Oh yes, you could hardly remember that you had entered this fact in the database (Table). Your database was new and you were really interested to enter everything if you could. You only remember ‘promised’ word and nothing else. Let us try.

Select Fname,Lname,Phone,Cell from address where mname like (‘%promised%’) Empty set (0.02 sec)

Well don’t despair. Give another try.

Select Fname,Lname,Phone,Cell from address where addr like (‘%promised%’)   +-------+--------+--------------+--------------+ | Fname | Lname  | Phone        | Cell         | +-------+--------+--------------+--------------+ | John  | walker | 419-932-9322 | 647-417-4879 | +-------+--------+--------------+--------------+ 1 row in set (0.02 sec)

You were lucky, because DB2 had helped? No; lucky, because you had entered something in your table. Now you have his name and go ahead call him, he liked chat with you.

            The best thing about database (or data table) is that once you create it, you can keep adding new contact for ever. There are simple setups to double check that the data that you are entering is valid. I mean no mistake or error. This is done at database level and you do not need to develop the software. Triggers and some constraints will help us do some sanity checks. We will talk about triggers and other constraints in next part.

            Actually, for our walk through the table ADDRESS is ready. But I believe that, because we have a hi-fi database system, why not try some house-keeping kind of things. I mean few features that allow us to maintain our database in top position. We are ok for now and will discuss Triggers and some of constraints in next part.