View in MySQL

What is View in MySQL?

When we want to write a complex query that we are using multiple times in the code, instead of writing this complex query numerous times we can write them inside view in mysql and call that view from the code.

The syntax for creating a view:

CREATE VIEW view_name AS
      SELECT column1, column2, …
      FROM schema.table_name
      WHERE condition;

We have the following two tables with values.

To create a view right click on the ‘views’ and select the ‘create view’ option as shown in the following image.

Now we will create a view for the query to get the data from students and the student_report table where students’ marks are greater than 200 as shown in the below image.

Create View Query:

CREATE VIEW `students_details` AS
      SELECT
            std.*
      FROM
            students AS std
            INNER JOIN student_report AS rpt
            ON std.roll_num = rpt.roll_num
            WHERE std.marks > 200
            ORDER BY std.roll_num;

Once view is successfully created we can select from the view, to Select from the view following is the syntax.

SELECT
*
FROM
schema_name.view_name;

Let’s select all column data from the above-created view.

Query:

SELECT
*
FROM
test_schema1.students_details;

We can also select required columns from the view as shown below query.

SELECT
roll_num, name, marks
FROM
test_schema1.students_details;

Like table select statements we can apply where condition, order by, and group by clauses in view as shown below.

SELECT
*
FROM
test_schema1.students_details
WHERE marks = 255;

Like this, we can create and use multiple views in the code. These views can treat them as tables and apply all the conditions or queries like joins etc. we use on the table.

Let’s connect one more view and apply union on both of them.

In the below view, we will select the students’ details for which marks are less than 190.

Create View Query:

SELECT
            std.*
      FROM
            students AS std
            INNER JOIN student_report AS rpt
            ON std.roll_num = rpt.roll_num
            WHERE std.marks < 190
            ORDER BY std.roll_num;

If we select from the above view we will get the following data

Query:

SELECT
*
FROM
test_schema1.students_details_2;