Back to Blog
Mysql workbench view table5/29/2023 ![]() ![]() SELECT * FROM `general_v_movie_rentals` Įxecuting the above script in MySQL workbench against the myflixdb gives us the following results shown below. Let’s now select all the fields from a table named general_v_movie_rentals. 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` Įxecuting the above scripts creates the view named general_v_movie_rentals in our myflixdb Below is the script that helps us to achieve that. We will package the JOIN created that gets information from three (3) tables namely members, movies and movie rentals. Let’s now look at a fairly complex example which involves multiple tables and uses joins. SHOW CREATE VIEW `accounts_v_members` Įxecuting the above script gives you the view name and the SQL SELECT statements used to create the view. If we want to see the SQL statements that make up a particular view, we can use the script shown below to do that. Other details found in the members table have been hidden. Only the authorized columns for accounts department have been returned. SELECT * FROM `accounts_v_members` Įxecuting the above script in MySQL workbench against myflixdb gives us the following results shown below. Let’s now execute a SELECT statement that selects all the fields from the view as shown in the below MySQL create view example. Note the accounts_v_members object is now visible in the database views objects. CREATE VIEW `accounts_v_members` AS SELECT `membership_number`,`full_names`,`gender` FROM `members` Įxecuting the above script in MySQL workbench against the myflixdb and expanding the views node in the database explorer gives us the following results. ![]()
0 Comments
Read More
Leave a Reply. |