Basic Walkthrough MySQL

Monday, 24 August 2020

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;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

update TRANS set PID='01' where ID=1

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

update TRANS set PID='00' where ID=2

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

update TRANS set PID='02' where ID=3

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

update TRANS set PID='02' where ID=4

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

update TRANS set PID='00' where ID=6

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

select * from TRANS;

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

| ID | AMOUNT | CODE | PID |

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

| 1 | 123.45 | in | 01 |

| 2 | 34.40 | out | 00 |

| 3 | 100.00 | in | 02 |

| 4 | 15.00 | out | 02 |

| 5 | 0.75 | in | NULL |

| 6 | 120.00 | In | 00 |

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

6 rows in set (0.00 sec)

Now we can create a view name trans_view to reflect name of the person instead of PID. PID is usually handy for use with managing multiple tables within database. Views are very handy and useful for creating end-user reports.

create view trans_view as SELECT t.AMOUNT,t.CODE,p.FNAME,p.LNAME from TRANS t, PERSONS p where p.PID=t.PID;

Query OK, 0 rows affected (0.03 sec)

select * from trans_view;

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

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

| 123.45 | in | Marry | Boon |

| 34.40 | out | David | Boon |

| 100.00 | in | John | Boon |

| 15.00 | out | John | Boon |

| 120.00 | In | David | Boon |

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

5 rows in set (0.00 sec)

So, we had an idea how Views work. In actual practice, the views are rarely so simple. But the logic behind a view is always same. JOIN tables and pretend to be one table. Please note that for simplicity of examples, we did not use TIMESTAMP column in TRANS table, which is mostly used to keep track of transaction along with some other more useful info.

For simplest use of database system, we have already covered most objects. In actual usage I hardly need anything other than what we have covered so far. The other objects are for some more sophisticated actions. For example, PROCEDURES and FUNCTIONS are very useful but for the scope of this tutorial these will be covered in last parts.

Let’s discuss some of useful built-in functions.

CHAR
The CHAR function returns a fixed-length character string representation of the argument.

DATE
The DATE function returns a date derived from a value.

HEX
The HEX function returns a hexadecimal representation of a value.

LOWER
The LOWER function returns a string in which all the characters have been converted to lowercase characters.

UPPER
The UPPER function returns a string in which all the characters have been converted to uppercase characters.

LEFT
The LEFT function returns a string that consists of the specified number of leftmost bytes of the specified string units.

LENGTH
The LENGTH function returns the length of a value.

LOCATE
The LOCATE function returns the position at which the first occurrence of an argument starts within another argument.

LTRIM
The LTRIM function removes blanks or hexadecimal zeros from the beginning of a string expression.

MAX
The MAX scalar function returns the maximum value in a set of values.

MIN
The MIN scalar function returns the minimum value in a set of values.

RIGHT
The RIGHT function returns a string that consists of the specified number of rightmost bytes or specified string unit from a string.

RPAD
The RPAD function returns a string that is padded on the right with blanks or a specified string a specified number of times.

RTRIM
The RTRIM function removes blanks or hexadecimal zeros from the end of a string expression.

SUBSTRING
The SUBSTRING function returns a substring of a string.

TIME
The TIME function returns a time derived from a value.

TIMESTAMP
The TIMESTAMP function returns a timestamp derived from its argument or arguments.

Basic Walkthrough MySQL Part-4

Basic Walkthrough MySQL Part-4

In previous part we had created the database (single table database) and populated the table with some data. The data seemed all right and we had nice time retrieving the data. Except one that (friend of friend)’s last name begins with small ‘w’. We will correct this and move on to creating triggers and other stuff.

Let us try updating the last name with capital ‘W’. We will see contents and update the row based on some unique parameters. Which means that while updating if our predicate selects more than one row, all selected rows will be updated. So be careful while identifying the target row.

Update ADDRESS SET LNAME=’Walker’ Where PHONE=’ 419-932-9322’;

Query OK, 0 rows affected (0.01 sec)

Rows matched: 0 Changed: 0 Warnings: 0

Thank God. The correction was cool.

To have a look at the modified data issue the command: SELECT * FROM ADDRESS. The * basically means all columns.

select * from address;

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

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

| David | M. | Brown | 905-264-2644 | 647-746-7466 | 1216 Morning Star Drive Miss,ON,CA |

| John | D | walker | 419-932-9322 | 647-417-4879 | promised help with JOB |

| Tai | | Tang | 647-647-6477 | 416-614-6144 | 64 Pluto Way Brampton,ON,CA |

| Tai | | Tng | 647-647-6477 | 416-614-6144 | 64 Pluto Way Brampton,ON,CA |

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

6 rows in set (0.02 sec).

When you look at above output you will find last name corrected. Secondly you see word ‘promised’ in ADDR column. That means you are free to insert any text in ADDR column. There is no CONSTRAINT set up. Usually there are no CONSTRAINTS in text fields like address.

There was an extra entry for Tai, with wrong last name (Tng). We did that intentionally to test behavior of primary key. Let us delete it.

Delete from ADDRESS where lname='Tng';

Query OK, 1 row affected (0.02 sec)

Select fname,lname,addr from address;

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

| fname | lname | addr |

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

| David | Brown | 1216 Morning Star Drive Miss,ON,CA |

| Dilshaad | Akhtar | 3456 Horner Ave Etobicoke,ON,CA |

| John | walker | promised help with JOB |

| Sabar | Raikoti | 1032 Mavis Rd Mississauga,ON,CA |

| Tai | Tang | 64 Pluto Way Brampton,ON,CA |

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

5 rows in set (0.00 sec)

So, we have seen how to remove ROWS from a table. A word of caution. Do not use DELETE FROM ADDRESS. This will delete all rows. This command should always have where predicate to limit the rows being deleted.

The most liked part of database is input format routines. The user will enter the data and smart systems accept a wide variety of data in loose format and will try its best to format it strict before inserting into table. This seems that this is part of programming. Well, yes, but basic programming that can be handled in small triggers. So I will help you write trigger to adjust hyphens (‘-‘) in the input data when it is fed to PHONE and CELL columns. Please go over MySQL help pages for triggers’ definition and syntax for more detail.

Next step will be to create a trigger to validate the data being input into the database table.

Create a text file with any extension having following SQL code in it. Do not worry about everything for now. Just create the trigger definition file and get it created. This trigger will check for existence of hyphens in appropriate position in phone numbers.

DELIMITER //

CREATE TRIGGER INS_CELL_PHONE

BEFORE INSERT ON ADDRESS

FOR EACH ROW

IF SUBSTRING(NEW.PHONE,4,1)<>'-' OR SUBSTRING(NEW.PHONE,8,1)<>'-'

OR SUBSTRING(NEW.CELL,4,1)<>'-' OR SUBSTRING(NEW.CELL,8,1)<>'-'

THEN

SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Phone Numbers Expected as 999-999-999';

END IF;

DELIMITER ;

This will create a trigger which will look far hyphens (‘-‘) at specified locations. If any of hyphens are missing, trigger will raise an error with message as specified as MESSAGE_TEXT.

To create the trigger, I suppose you have stored the file as INS_PHONE.TRIG in current folder. See the syntax and note that we had ended the file with //. So calling this SQL file we specify the terminator (@) as below.

C:\Program Files\IBM\SQLLIB\BIN>db2 -td@ -vf ins_phone.trig

DB20000I The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>

Please note that to run SQL files you have to run it from DB2 Command Line and not DB2 CLP These two windows are different although they look the same. If you have started CLP then you may revert back to Command Line by typing QUIT. So now trigger has been created which checks for ‘-‘ at 4^th and 8^th position in PHONE and CELL. If there is any one missing, trigger will force rejection of whole row and nothing will be added.

insert into address values ( 'Gurmit' ,'S', 'Randhawa', '416-742-9242','6477204020','1230 The Walkers

Road');

ERROR 1644 (75000): Phone Numbers Expected as 999-999-999

Let us try little different. This time we won’t provide the CELL number.

insert into address values ( 'Gurmit', 'S', 'Randhawa', '416-742-9242','','1230 The Walkers Road')

ERROR 1644 (75000): Phone Numbers Expected as 999-999-999

Same, because trigger wants two hyphens in both numbers. So now you are forced tom enter the phone numbers exactly as 999-999-9999. By the way, we have designed the trigger just for practice, so we kept it simple; and you can provide any bad data except ‘-‘ on 4^th and 8^th places in phone numbers.

Now try providing the data in strict format.

insert into address values ( 'Gurmit' ,'S' ,'Randhawa' ,'416-742-9242','647-720-4020','1230 The Walkers Road')

Query OK, 1 row affected (0.02 sec)

Cool, This works, and is much better because the chances of omission are ruled out this way.

Triggers are not primarily for this purpose. However being an database object it works faster and ensures data integrity; these triggers can be put to different uses. Normally triggers are used to make calculations based on some columns and populate the other columns with the result. More sophisticated use can be thought of updating other tables based on transaction data entered into main table (usually called TRANSACTION table).

As a last section of this walkthrough part I would like to mention here that entering all data on command line is usually not welcome. But the good thing is that if you do it manually you get more familiar with your database objects; i.e. tables, views, triggers and sequences etc.

Still we do not have to go long way. If you so wish, you can use the batch file listed below. This one will allow you to enter data into your table. I will keep it simple for this tutorial.

@echo off

:another

set /p fname=First Name :

if [%fname%]==[] goto done

set /p mname=Middle Name :

set /p lname=Last Name :

set /p phone=Phone Number :

set /p cell=Cell Number :

set /p addr=Address :

set /p ok= Ok ?

if [%ok%]==[y] goto doit

if [%ok%]==[Y] goto doit

if [%ok%]==[Yes] goto doit

if [%ok%]==[yes] goto doit

if [%ok%]==[YES] goto doit

goto redo

:doit

mysql -uuser1 -ppassword1 sample -e “insert into address values ('%fname%','%mname%','%lname%','%phone%','%cell%','%addr%')”

echo.

:redo

set fname=

set mname=

set lname=

set phone=

set cell=

set addr=

set ok=

goto another

:done

set fname=

set mname=

set lname=

set phone=

set cell=

set addr=

set ok=

Please take care saving this script. This will need .bat or .cmd filename extension. Go ahead and practice with data entry. Remember when you want to finish, enter nothing in First Name :. If you miss it then say no to ‘Ok ?’ prompt and then give it empty First Name again. This batch file will keep looping until you provide empty (NULL) for First Name. For each entry you will be expecting following string from mysql. mysql confirms the successful entry this way. Anything else will signal an error.

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

If you see an error then you need to repeat the entry. Keep in mind that you have a trigger in action to check your phone numbers. That should be good enough for this part of tutorial. In next part, we will see how we can build a simple application to retrieve data from our database remotely.

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

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

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

| 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’;

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

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

| 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%’)

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

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

| 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%’)

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

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

| 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.