How to use views in MySql with example

 

In this tutorial we will learn what is views in mysql, what is the advantage of view, how to create view in mysql, how to update a view in mysql ,create view with check option and finally how to drop a view from mysql .

 



    What is views in mysql

    Views is an important task for a mysql database administrator. View is a logical table .We can perform insert ,update and delete operation using view. But when we execute one query on view ,the request goes to the physical table and requested operation will be perform there. Then the  acknowledgement sent back to view to see the data of the table. Therefore performance will be degraded by using view in mysql.

    What is the advantage of views in MySql

    There are some advantages of view. These are following.

    1)      A complex query will become simple query using the view:   When create a complex operation like join we can use view statement to do the query simple.

    2)      View provides extra layer of security of database: Suppose we have a table of department which contains many department name .We want to restrict one department data from other department we can do this using View. For this reason it provides extra layer of security.

    3)      We can restrict data for user: Suppose we have a table of department which contains many department name .We want to restrict one department data from other department we can do this using View

    How to create view in MySql

    Suppose we have one table named department and we want to restrict some data. We can create a view to do this task and the syntax is given below.

    create view <view_name>

    as

    [select statement] [condition]

     

    create view in MySql

     

     

    From the above syntax

    1)      create view is the keyword to create the view.

    2)      View_name is name of the view which to be created

    3)      We need to use “as” keyword

    4)      Then we need to enter our statement for which view will be created.

     

    Now we want mechanical department  can view only mechanical department data. We can execute the below query to do this task.

    CREATE view view_mech as select * from dept where deptname=’MECH’;

     

    Now we have created one view named view_mech. To see the view we can execute the below query. We can see only record of mechanical dept after execute the query.

     

    Select * from view_mech;

     

    How to update a view in MySql

    If we need to update our view statement which is created the we need to use the “alter view” keyword. Syntax will be

    alter view <view_name>

    as

    [update statement] [condition]

     

    From the above syntax

    1)      alter view is the keyword to update the view.

    2)      View_name is name of the view which to be updated.

    3)      We need to use “as” keyword

    4)      Then we need to enter our statement for which view will be updated.

     

     

    Create view in MySql  with check option

     We have created one view in the create view section to view,insert,update the record only mechanical department. If we insert one data for other department the data will be inserted.If we use “with check option” keyword then we will get an error. The syntax will be.

    create view <view_name>

    as

    [select statement] [condition] with check option

     

    We must enter “with check option” keyword to prevent the database. You can see demonstration at  the video section .

     

    Drop a view from MySql

    We have created view in MySql . Now it is the time to drop the view from the MySql. Below is the syntax to drop the view.

    Drop  view <view_name>

     

    From the above syntax

    1)      drop view is the keyword to drop the view.

    2)      View_name is name of the view which to be dropped.

     

    Here we are going to drop view_mech using the below query.

     

    drop view view_mech;

     

     


     

    In this tutorial we have  learned what is views in mysql, what is the advantage of view, how to create view in mysql, how to update a view in mysql ,create view with check option and finally how to drop a view from mysql .

    How to use join query in MySql

     

    In this tutorial we will learn what is join , Join types in MySql, Join syntax in MySql,Join query in mysql ,Join tables in MySql.

     



      What is join 

      Join is a query which can fetch or retrieve data from more than two tables. Join in MySql  is very important task for a dba where we need details data from more than two tables. Then we can us join operation to fetch details data as per our requirement.

       

      Join types in MySql


      There are four types of join in MySql.

      1.inner Join: Suppose we have two tables. Using the inner join we can retrieve data from these two tables which data are matched .

      2.Left Join: Suppose we have two tables .Using Left join we can retrieve all data from the left table and matched data from the right table.

      3.Right Join: Suppose we have two tables .Using Right  join we can retrieve all data from the right table and matched data from the left table.

      4.Cross Join: Suppose we have two tables ,one is employee table and other is department table. Department table has five records and employee table has three records. Using cross join Each row of the one table is join to every rows of the other table. After joining two tables we will get 15 records. Normally cross join is not used.

       

      Join syntax in MySql

      1)      In case of inner join we can use the below syntax.

      select * from <first table> inner join <second table> on first table.column name=second table.column name ;

      2)      In case of left join we can use the below syntax.

      select * from <first table>  left join <second table>  on first table.column name=second table.column name ;

        

      3)      In case of right join we can use the below syntax.

      select * from <first table>  right join <second table>  on first table.column name=second table.column name ;

      4)      In case of cross join we can use the below syntax.

      select * from <first table>  cross join <second table> ;

       


      Join tables in MySql

      Suppose we have two tables , ,one is employee table and other is department table. Department table has five records and employee table has three records. We are going to join two tables.

       

      1)      Incase of inner join query MySql we can execute the below join query to retrieve data from two tables.

              select * from dept inner join emp on dept.deptno=emp.empdept ;

      join query in MySql


      2)      Incase of Left join query in MySql we can execute the below join query to retrieve data from two tables.

      select * from dept left join emp on dept.deptno=emp.empdept ;

      left join in MySql


      3)      Incase of Right  join query in MySql we can execute the below join query to retrieve data from two tables.

        select * from dept right join emp on dept.deptno=emp.empdept ;

      right join in MySql


      4)      Incase of cross  join query   in MySql we can execute the below join query to retrieve data from two tables.

          select * from dept cross join emp ;

       

      cross join in mysql

      In this tutorial we have learned what is join , Join types in MySql, Join syntax in MySql, Join tables in MySql.

      Primary key and Foreign key difference with examples


      In this tutorial we will learn about primary key ,example of the primary key, syntax of the primary key,what is foreign key,example of the foreign key,syntax of the foreign key,alter table primary key,alter table foreign key and difference between primary key and foreign key.

       




        What is Primary key

        Primary key is the database constraint must have unique value. Primary key should be not null value. In the table only one column will be the primary key.

        Primary key  Example

        Suppose we talk about employee table, employee no should be the primary key because employee no is the unique no.name may not primary key as name may be duplicate. department may be duplicate value.

        Syntax of the primary key

        Using the above example ,we can create a table with primary key using the below syntax.
        create table emp ( empno INT NOT NULL AUTO_INCREMENT, empname VARCHAR(30) NOT NULL,empdept VARCHAR(30) NOT NULL, PRIMARY KEY(empno));

        Alter table add primary key

        if we did not add primary key during table creation we can add primary key using the alter table command.

        alter table emp 
        add PRIMARY KEY (empno);

        What is Foreign key

        Foreign key is the database constraint which is used to link two or more tables for a relational database.Foreign key in the one table is the primary key of the other table. It should be the duplicate data and it can be null value.

        Foreign key  Example

        Suppose we have two tables ane is employee table and other is the department table .employee table contains employee no ,employee name  and employee department and department table contains department id and department name. Department name is the common for both table. In the department table department no is the primary key and in the employee table employee no is the primary key. In the employee table department name is the foreign key and it should be the primary key of the department table.

        Syntax of the Foreign key

        Using the above example we can create two tables with the primary key for the two tables and foreign key for the employee table using the below syntax.
        create table dept( deptno INT NOT NULL AUTO_INCREMENT,deptname VARCHAR(30) NOT NULL, PRIMARY KEY(deptno));
        create table emp ( empno INT NOT NULL AUTO_INCREMENT, empname VARCHAR(30) NOT NULL,empdept VARCHAR(30) NOT NULL, PRIMARY KEY(empno),FOREIGN KEY(empdept) REFERENCES dept(deptname));

        Alter table add primary key

        if we did not add Foreign key during table creation we can add foreign key using the alter table command.

        alter table emp 
        ADD FOREIGN KEY(empdept) REFERENCES dept(deptname);

        Primary key and Foreign key
        Primary key and Foreign key


        Difference between primary key and foreign key


        Primary key                                                                Foreign key

        1.Primary key should be unique.                             1.Foreign key should not be unique. It can be                                                                                                  duplicate.    
        2.value should not be NULL.                                  2.Foreign key may be null
        3.only one column should be primary key              3.many columns may be foreign key.


        In this tutorial we have learned about primary key ,example of the primary key, syntax of the primary key,what is foreign key,example of the foreign key,syntax of the foreign key,alter table primary key,alter table foreign key and difference between primary key and foreign key.

        Redo logs management in oracle 11g


        In this tutorial we will learn  what is redo logs in oracle,view oracle redo log group and members (redo log files),  add redo log file in redo group,drop redo log file and redo log group in oracle.





          What is redo logs  in oracle

          REDO log file is very essential file for Oracle database which is stored in physical filesystem of hard disk. Normally it is a file which records all changes made in oracle database .
          Suppose a user modified any row using SQL query ,the changed data  will be stored in redo Buffer in SGA components. Then LGWR  process transferred all data from redo Buffer to redo log file under the following conditions.

          1. Redo Buffer one third full
          2. Every 3 seconds
          3. After a successful transaction committed
          4. Before data blocks transfer from db buffer cache to data files.


          View oracle redo log group and members (redo log files)

          We can see Status of redo group and size using the dba directory table  called v$log .here group 2 is current means oracle uses this group to store redo log file and group 1 and group 3 are inactive means it is no longer required to recover oracle database.

          Status of redo group

          We can view oracle redo log group members using one Dba directory called v$logfile. Here under the member we find the location of the redo log file. Here redo01.log file belongs to group 1 , redo02.log file belongs to group 2 and redo03.log file belongs to group 3.
          It is recommended that each group should be at least two redo log file if none redo log file is corrupted then database will be worked using other redo log file.and also two redo log files will be stored on the different disks.if one disk is corrupted then we will get second redo log file in other disk.
          redo logs  in oracle




           Add redo log file in redo group

          We can add redo log file member to redo log group using the command.here redo01a.log redo log file member is added to group 1 and it's status is invalid.
          Add  redo log file


          After switching the log file the status will be valid.
          Add redo log file

          Drop redo log file and redo log group in oracle

          We can drop redo log file using the below command. Here redo01a.log file will be dropped.
          Drop redo log file


          We can drop redo log group sing the below command. Here we delete group 1 .we can not drop the group which status is current.in our case our current group is group 2.
          Drop redo group

          After dropped the group 1 ,redo log files inside the group 1 was not deleted.it is stored on the physical filesystem of the disk.we need delete this log file using the os command.
          Here we did not delete this file and create group 1 using the same file we got this below error.
          Error

          After deleted the Physical redo log file from the disk we can create redo group 1 with the redo log file using the same name.no error came.
          Redo log management oracle 11g


          In this article we have learned
          1.   what is redo log file in oracle
          2. View oracle redo log group and members (redo log files)
          3. Add redo log file in redo group
          4. Drop redo log file and redo log group in oracle
          Stay tune with us.😊


          What is Redo log file in oracle

            

          In this tutorial  we will learn what is redo log files,why we are creating multiple redolog files for Oracle database ,different status of redo log file,purpose of redo log file

          Redo log file in oracle




            What is redo log file in oracle

            REDO log file is very essential file for Oracle database which is stored in physical filesystem of hard disk. Normally it is a file which records all changes made in oracle database .
            Suppose a user modified any row using SQL query ,the changed data  will be stored in redo Buffer in SGA components. Then LGWR  process transferred all data from redo Buffer to redo log file under the following conditions.
            1. Redo Buffer one third full
            2. Every 3 seconds
            3. After a successful transaction committed  
            4. Before data blocks transfer from db buffer cache to data files. 

            Why we are creating multiple redolog files for Oracle database

            It is recommended to create multiple redolog files in oracle database. If there is one redo log file and by accidentally it is corrupted then oracle database is not working properly. So multiples redo files should be created in the oracle database.

            Also we should create redo log files on the separated disk. If we have redo log files on single disk and this disk is corrupted for that we lost all redo log files and database will be not worked. Therefore we should create redo log files on separate disk.
            Redo log files are very essential file to recover oracle database in case oracle instance crashes  by any reason. Now in oracle database many redo log files are created a group.

            Redo group is very important while archiving redo log files.suppose group 2 is current that means oracle uses these redo log files which present in group 2 and archive process then archiving redo log files from other group.for that reason database performance is not degraded.

             Different status of redo log file

            There are three status of redo log files.

            1. Current: oracle database uses this redo files to store the online changes data.
            2. Active: These files are required to recover  oracle database. 
            3. Inactive:These files are no longer required to recover oracle database. 

            Purpose of redo log file

            In case oracle instance crashes then Redo log files are required to recover oracle database.
            In this tutorial we have learned
            1.  what is redo log files
            2. Why we are creating multiple redolog files for Oracle database 
            3. Different status of redo log file
            4. Purpose of redo log file 

            Stay tune with us to know about redo logs management with practical.