Basic Walkthrough MySQL: Basic Walkthrough MySQL Part-5

Basic Walkthrough MySQL Part-5

In this part we will study some more useful database objects. For example, VIEWS, TRIGGERS, PROCEDURES and few other.

As we had an introduction to trigger creation in part 2, we will discuss how we run scripts first. MySQL supports different kind of scripts. You can run system shell scripts (.bat, .cmd, .js, .vbs,.sh and many more). Let us try to understand how we can run scripts. Simple SQL script looks like

C:\Documents and Settings\user1>mysql -uuser1 -ppassword1 < script.sql

use sample;

select * from trans;

select count(*) from trans;

C:\Documents and Settings\user1>mysql -uuser1 -ppassword1 < script.sql

Warning: Using a password on the command line interface can be insecure.

ID AMOUNT CODE

1 123.45 in

2 34.40 out

3 100.00 in

4 15.00 out

5 0.75 in

count(*)

This was a simple SQL script. This is to be run in db2 command window. The syntax has been indicated in blue. Being an example script, I just listed few commands. In actual scripts these lines are usually over hundreds.

Similarly, we can use system scripts too for accomplishing same mission. Have a look at following script. This one is equivalent to the previous one.

C:\Documents and Settings\user1>Script.bat

mysql -uuser1 -ppassword1 -e “select id,name,dept,job from staff”

mysql -uuser1 -ppassword1 -e “select count(*) from staff”

This is very straightforward that we use mysql. The scripts are usually written for various tasks including generating daily reports. As a rule of thumb, add all the commands to script file that are to be repeated in future and invoke the script using one of above syntaxes.

Let us create another trigger on TRANS table (created in part 2) to ensure that user uses in or out keywords only in code column. Using anything else will be waste of data. So we will try to reject entry if the code is not set properly.

C:\Documents and Settings\user1>mysql -uuser1 -ppassword1 sample < TriggerCheckCode.sql

DELIMITER //

CREATE TRIGGER Check_Code

BEFORE INSERT ON TRANS

FOR EACH ROW

IF UPPER(NEW.CODE)<>'IN' AND UPPER(NEW.CODE)<>'OUT' THEN

SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Code must be either "in" or "out"';

END IF;

DELIMITER;

This trigger will stop us from entering wrong ‘Code’ in TRANS table. This is to ensure that data entered will be usable by the trigger update_total trigger to update TOTAL tables.

select * from total

+---------+--------+---------+---------------------+

+---------+--------+---------+---------------------+

| 49 | 224.20 | 49.40 | 2018-12-09 19:38:50 |

+---------+--------+---------+---------------------+

1 row in set (0.03 sec)

insert into TRANS values (default, 120.00,'Not')

ERROR 1644 (75000): Code must be either "in" or "out"

insert into TRANS values (default, 120.00,'In')

Query OK, 1 row affected (0.01 sec)

select * from total

+---------+--------+---------+---------------------+

+---------+--------+---------+---------------------+

| 49 | 344.20 | 49.40 | 2018-12-10 07:57:27 |

+---------+--------+---------+---------------------+

1 row in set (0.01 sec)

Cool, the transaction was applied to TOTAL table when we entered the correct code. Remember we had used ‘case free’ iN and OuT key words. It will work with any case.

Let us try to have some flavor of VIEWS. As explained earlier the VIEW is combination of columns from different tables. We will create another table called PERSONS with names of all family members. Then we will create a view joining TRANS and PERSONS tables to save info about who had carried out the transaction. For this purpose, we will add a column PID to TRANS table.

alter table TRANS add column PID CHAR(2);

Query OK, 0 rows affected (0.38 sec)

Records: 0 Duplicates: 0 Warnings: 0

Now create table PERSONS as below.

create table PERSONS (PID CHAR(2) not null primary key, FNAME CHAR(16), LNAME CHAR(12));

Query OK, 0 rows affected (0.16 sec)

Add some data to the table.

insert into PERSONS values (‘00’,’David’,’Boon’);

Query OK, 1 row affected (0.00 sec)

insert into PERSONS values (‘01’,’Marry’,’Boon’);

Query OK, 1 row affected (0.00 sec)

insert into PERSONS values (‘02’,’John’,’Boon’);

Query OK, 1 row affected (0.00 sec)

select * from persons;

+-----+-------+-------+

| PID | FNAME | LNAME |

+-----+-------+-------+

| 00 | David | Boon |

| 01 | Marry | Boon |

| 02 | John | Boon |

+-----+-------+-------+

3 rows in set (0.00 sec)

Now we need to enter these PID in TRANS table to indicate who carried the transactions out. Let’s do that.

select * from TRANS;

+----+--------+------+------+

| ID | AMOUNT | CODE | PID |

+----+--------+------+------+

| 1 | 123.45 | in | NULL |

| 2 | 34.40 | out | NULL |

| 3 | 100.00 | in | NULL |

| 4 | 15.00 | out | NULL |

| 5 | 0.75 | in | NULL |

| 6 | 120.00 | In | NULL |

+----+--------+------+------+

6 rows in set (0.00 sec)

update TRANS set PID='00' where ID=0;