Views in sql

• "CREATE VIEW` view_name`" tells the MySQL server to create a view object in the database with the name `view_name`
• "AS SELECT statement" are SQL statements that are to be packaged into views. This can be a SELECT statement that can contain data from one or more tables.

Let's now create our first view using "myflixdb", we will create a simple view that limits the columns visible in the members table.

Let's assume that the authorization requirements indicate that the accounting department can only see the member number, name, and gender from the member table. To do this, you can create a VIEW −

CREATE VIEW `accounts_v_members` AS SELECT `membership_number`,`full_names`,`gender` FROM `members`;

Running the above script in MySQL Workbench for myflixdb and expanding the view node in the database explorer gives us the following results.

Note that the account_v_members object is now visible in the database view objects. Let's now execute a SELECT statement that selects all fields from the view.

SELECT * FROM `accounts_v_members`;

Running the above script in MySQL Workbench against myflixdb gives us the following results shown below.

Only authorized columns for accounting were returned. Other details found in the members table have been hidden.

If we want to see the SQL statements that make up a particular view, we can use the script shown below to do so.

SHOW CREATE VIEW `accounts_v_members`;

Running the script above gives you the name of the view and the SQL SELECT statements used to create the view.

Connections and Views

Let's now look at a fairly complex example that includes multiple tables and uses joins.

We will package the generated JOIN that gets information from three (3) tables, namely members, movies, and movie rentals. Below is a script that helps us achieve this.

CREATE VIEW `general_v_movie_rentals` AS SELECT mb.`membership_number`,mb.`full_names`,mo.`title`,mr.`transaction_date`,mr.`return_date` FROM `movierentals` AS mr INNER JOIN `members` AS mb ON mr.`membership_number` = mb.`membership_number` INNER JOIN `movies` AS mo ON mr.`movie_id` = mo.`movie_id`;

Running the above scripts creates a view called general_v_movie_rentals in our myflixdb

Now let's select all the fields from the table named general_v_movie_rentals.

SELECT * FROM `general_v_movie_rentals`;

DROP VIEW ` general_v_movie_rentals `;