Basic Walkthrough MYSQL Part-2

 

Basic Walkthrough MYSQL Part-2

This part will provide simple introduction to MYSQL. Some might have some experience with relational database systems, while others might not have even thought about it. In simplest form RDBS (relational data base systems) is a database package that manages relational data. Now relational data is all about properties of an object that needs to be related via any other properties. Suppose we talk about a person. A person has name, last name, phones and may have vehicles. The person may have an income. The person may have expenses. The person may have choices and options. So, all the properties are linked to each other via a person. This way or that way, whatever is linked via another data is usually called relational data. The database that provides the relational data interlink (interface) is usually called relational database.

            So MYSQL is a Relational Database. Like other databases it also provides few database objects that help us save, organize and retrieve data. Other databases provide some other features and may to some length, while MYSQL has a reputation of providing a reliable database system that can easily be handled. The best part is that now DB2 Express-C server is free to use an is highly suitable for new learners and matured developers. Let us discuss about some of MYSQL objects.

Tables

            Tables are the first one that I can think of. And mostly used right from simplest database to most complicated system. I our walkthrough we will be dealing with one simple table that will be very very simple to understand and you will never need to ask the question “what is a table??” Definition wise a table is a simple collection of data in rows and columns. The first table I ever came across was a dBase table that the dBase guys used to call ‘database’ instead of ‘table’.

Views

            Second object is usually views. The views are actually a logical combination of columns from different tables. These columns usually linked via a link id. Link id is normally a common column among related tables to indicate the object that these columns should relate. From user’s perspective the views are same as tables (not in dBase). You use same command to retrieve data from views as from tables. While we will be using simplest form of DB application; with one table only; we won’t be dealing with views a lot except simple usage example towards the end of series.

Triggers

            Triggers are another object that play a vital role in database life. As the name the triggers get triggered when an event is sensed. Database manager will execute the code in trigger body to perform some actions described in the trigger when an event described in trigger header occurs. Normally triggers are called when insert, update or delete is performed on a table. So, the triggers are created on the tables.

Procedures

            Procedures are another part that plays an important role in data manipulation.  These are similar to procedures, sub routines and functions. These procedures are written and when we create these; database compiles and stores on server machine. These are later called to do actions as preplanned in the Procedure Body.

Packages

            Packages are little bigger than procedures. These are almost same as procedures. These packages are also compiled and later bound to the database. Any errors are usually phased out in compile time.

Sequences

            Sequences are some numerical series created by database based on criteria provided by the creator. This help numbering the data being entered into the database automatically.

            In fact, for introductory walkthrough, you do not need to remember all these. More you learn, more will you discover the objects. All objects are for some purpose; and if your requirements are so simple, you may not need to learn all. But they are there, just in case you need these any time.

Let us start exploring some of these. We will create few basic tables to study behavior of some of objects. Tables will be created quickly and we will study other objects briefly.

MySQL Database

To start with let us take a look at ways we can issue commands to MySQL. First method is to use mysql command window. Go to Start>All Programs>Accessories>Command Prompt. Run mysql.exe in the command window.

 

 

 

 

 

Well for now let us use mysql command window for creating tables, view and triggers. So, open ‘cmd’ widow to execute following commands one by one.

 

Use sample; CREATE TABLE TRANS(     ID      SMALLINT NOT NULL AUTO_INCREMENT,     AMOUNT  DECIMAL(6,2) NOT NULL,     CODE    CHAR(4) NOT NULL,     CONSTRAINT PK_EXAMPLE PRIMARY KEY (ID) );   CREATE TABLE TOTAL (  WEEKNUM    SMALLINT  NOT NULL  DEFAULT 0,  INCOME     DECIMAL (6, 2)  NOT NULL  DEFAULT 0,  EXPENSE    DECIMAL (6, 2)  NOT NULL  DEFAULT 0,  TIMEUPDATE TIMESTAMP  NOT NULL  );

 

After creating these two tables, you will create a trigger that will update your TOTAL table. See first line for syntax to create trigger when you name the code file as trig1.sql.

 

 

db2 -td@ -f trig1.sql

delimiter // drop trigger update_total// create trigger update_total after insert on trans for each row BEGIN IF NEW.amount<>0 THEN   if exists (select 1 from total where weeknum=week(current_timestamp))   then       if lower(NEW.code)='in' then update total set income= income + NEW.amount, timeupdate=current_timestamp where weeknum=week(current_timestamp) ; end if;       if lower(NEW.code)='out' then update total set expense= expense + NEW.amount, timeupdate=current_timestamp where weeknum=week(current_timestamp);end if ;   else       if lower(NEW.code)='in' then insert into total values(week(current_timestamp),NEW.amount,0,current_timestamp);end if ;       if lower(NEW.code)='out' then insert into total values(week(current_timestamp),0,NEW.amount,current_timestamp);end if ;   end if; END IF; END;// delimiter ;

Only thing to note here is that the triggers can be created in Command Window not CLP. You can create it in Command Editor, but for that you will have to change terminator character to ‘@’ instead of ‘;’ (look bottom left of CE).

            After creating these three objects try entering data into TRANS table as below. You will see that TOTAL table will be populated automatically. This is feature of the database; not the program. The TOTAL table will create one row for each week. When week changes it will generate another row. This table will reflect weekly income and expenses. On regular basis you will enter all transactions into TRANS table as below. Remember to enter code as either ‘in’ for income or ‘out’ for expenses. Any other code will be accepted but will be useless.

insert into TRANS (amount,code) values (123.45,’in’); insert into TRANS (amount,code) values (34.40,’out’); insert into TRANS (amount,code) values (100,’in’); insert into TRANS (amount,code) values (15,’out’); insert into TRANS (amount,code) values (.75,’in’);

            And lastly check contents of TOTAL table

 

select * from total;

Cool, get the calculator and see if the calculation is correct. Last update stamp only shows the last time when the data was entered.

            In fact, we have created a very useful utility. This can further be customized as required. You will see how triggers can help us validate data while data entry in following sections. Till then happy MySQLing.