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:
- Views in MySQL
- Triggers for automatic events on particular conditions
- Indexing to make your searches really fast
- 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);
- We have first made a database which is named as article.
- 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
- 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.
- 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;
- This will create a new view which will be named as article1View
- 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.
- 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.
- We created a trigger with name article1Trigger
- We set it to activate before any insert on table article1
- Before any insertion operation in table article1 we have set the id column value to 0.
- Now now id value is taken as 0 irrespective of what value is inserted
- 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