Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Advanced operations on MySQL database.

This article will explain you some advanced operations on MySQL like views, triggers, index and stored procedures. It is really important article for those aiming to build a website.

This tutorial will teach you several database operations which will be needed in day to day life. Must be read by all those who want to learn advanced operation on mysql database.

MySql is very popular database which is used as a backend in website development.

Here we are going to discuss:

  1. Views in MySQL
  2. Triggers for automatic events on particular conditions
  3. Indexing to make your searches really fast
  4. Stored Procedure which saves lots of time

First, we need to create a database and table to perform our operations. I have already explained basic sql operations in my previous post. Here I will just give a small description.

Listing 1: Preliminary requirement

Create database article;
use article;
create table article1 (Author varchar(16),id int); 
insert into article1 values ('csanuragjain',1);
insert into article1 values ('anurag',2);

Here:

  1. We have first made a database which is named as article.
  2. Now we need to make operation on this database so we use the command use article which ensures that all database operations are now occurring in this database
  3. We create a new table which is named article1 and define two column for this table which is Author , meant to store Author name and id , meant to store id of the author.
  4. Now we insert two value in this table article1

Now we have completed our initial setup.

We will discuss about views. Views help us to prepare a logical table which can store data from multiple data or some particular data of your own choice. It helps you if you want to obtain some repeated set of data again and again from database table.

Listing 2: Views

create view article1View as
select * from article1
where id=1;

Here:

  1. This will create a new view which will be named as article1View
  2. We select all rows from article1 where id is 1 so according to our case the Author csanuragjain is fulfilling the requirement for id 1.So here the row containing csanuragjain as Author and id as 1 will be selected and will be placed in view article1View which we are creating.
  3. Remember I showed you a very simple scenario. But in real life you may use it to store data from multiple tables so that you don’t have to write complex queries every time.

Now we will discuss about Triggers. Triggers are really useful when you want to perform certain operation after completion of particular activity. For example you want to update a field every time a new row is entered into the table.

Listing 3: Triggers

Delimiter |
CREATE TRIGGER article1qTrigger BEFORE INSERT ON `article1`
FOR EACH ROW
BEGIN
SET NEW.id = 0;
END;
|

First of all we set delimiter to | because the default delimiter in mysql is ;.Now if we don’t change it then mysql will think that statement ends just after SET NEW.id = 0; which is not true because END; statement is also there. That is why we set a new delimiter.

Here:

  1. We created a trigger with name article1Trigger
  2. We set it to activate before any insert on table article1
  3. Before any insertion operation in table article1 we have set the id column value to 0.
  4. Now now id value is taken as 0 irrespective of what value is inserted
  5. This can be useful when you want to set any custom value of a column if a particular condition fulfill. Like you may make a column to have false value if user is not validated by default.

Now we will learn to use Index. Index can be useful to make searches fast. You need to specify a column by which you need to perform indexing. The column chosen for indexing must have unique values

Listing 4: Index

  CREATE INDEX id_index ON article1 (author);

This will create index id_index for table article1.Now we chose column author for indexing

Now we learn to make procedure. We can define some daily routine with stored procedure and we can call them when required

Listing 5: Stored Procedure

delimiter //
CREATE PROCEDURE countproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM article1;
END//
call countproc(@id);
select @id;

Here we created a procedure named countproc. The procedure will accept a column which is stored in param1.Now we use count to calculate the number of rows for that particular column id. After this we used call procedure. We passed id so that we have calculated the number of rows count. Now we show it using select @id

Hope you liked the article. See you next time



My main area of specialization is Java and J2EE. I have worked on many international projects like Recorders,Websites,Crawlers etc.Also i am an Oracle Certified java professional as well as DB2 certified

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login