Creating a View

The following walkthrough describes how to create a view. Tables created in the previous parts of walkthrough will be used there.

Prerequisites

In order to complete this walkthrough, you will need access to MySQL server 5.0 or higher.

You also should have the following privileges:

  • CREATE VIEW on the demobase database
  • SELECT privilege on the emp and dept tables

dbForge Fusion for MySQL supports views on MySQL 5.0 and higher.

Creating a View

View is a logical table based on one or more tables or views. A view contains no data itself, but retrieves it from underlying tables and views when requested. Let’s create a view that takes data from two tables (Dept and Emp), joins them and shows the result set.

  1. In Database Explorer, navigate to the Views node in your schema.
  2. Right-click it and choose New View on the shortcut menu.
  3. Type the name of the view (Staff) and click Create.

  4. In the template that appears, paste the following text:

     SELECT demobase.emp.ename AS name, demobase.emp.job, demobase.emp.sal
 AS salary, demobase.dept.dname AS department  FROM demobase.emp, demobase.dept
 WHERE demobase.emp.deptno = demobase.dept.deptno

    It says, take employee’s name, job and salary from the table Emp, and determine from which department the employee is. Show this data for every employee.

  5. Save the document and refresh the Views node. Now, all you have to do is to choose Retrieve Data on the shortcut menu of the view.