Benefits of Using Views

Download

HTML can be useful for transferring content to other online publishing platforms, typically by copy/pasting to the new platform.

<div class="callout good" data-callout-custom-class="good">
<h3 id="h3_TSTT" name="h3_TSTT">Benefits of Using Views</h3>
<ul id="ul_TcuR" name="ul_TcuR">
<li id="li_SLDQ" name="li_SLDQ">Design Flexibility: By using a view instead of a query in an application, it is easier to make changes to the underlying table structure.</li>
<li id="li_RGnP" name="li_RGnP">Improved Security: By using a view to return data from tables instead of a SELECT, you can hide the WHERE clause or other columns to which you do not want the user to have access.</li>
<li id="li_rRwW" name="li_rRwW">Query Simplification: You can write simple select statements against views, which handle complex queries and joins.</li>
</ul>
</div>Code Sample:<code>USE WORLD; CREATE VIEW city_country AS SELECT ci.name AS city_name, co.name AS country_name FROM city ci     JOIN country co     ON ci.CountryCode = co.Code;</code>Results by selecting from the city_country view:<img src="https://edtechbooks.org/content_images/learning_mysql/v_01.png" class="img-fluid" alt="v_01.png" id="img_TTSp" name="img_TTSp">CREATE VIEW city_country AS<ul id="ul_Hcrk" name="ul_Hcrk">
<li id="li_LdCC" name="li_LdCC">Create a new VIEW object and give it the name city_country</li>
<li id="li_SnvB" name="li_SnvB">The AS statement precedes the query that will be assigned to the VIEW</li>
</ul> SELECT ci.name AS city_name, co.name AS country_name<ul id="ul_SrPB" name="ul_SrPB">
<li id="li_LdtV" name="li_LdtV">Only the columns defined in the SELECT statement will be available to the VIEW</li>
<li id="li_zbct" name="li_zbct">It is a good idea to provide a column alias in the select because the VIEW will not have access to the underlying table structure.</li>
</ul>FROM city ci     JOIN country co    ON ci.CountryCode = co.Code;<ul id="ul_Zram" name="ul_Zram">
<li id="li_gCIf" name="li_gCIf">The JOIN statement of the SELECT.</li>
<li id="li_tYDj" name="li_tYDj">Once you have created a VIEW, you can run SQL statements using the VIEW as if it were a table.</li>
<li id="li_XYNF" name="li_XYNF">By creating a VIEW, we can run selects that retrieve data from multiple tables without having to re-code a join.</li>
<li id="li_CSCH" name="li_CSCH">Notice how the SELECT * retrieves only the rows defined in the SELECT statement used in the VIEW creation.</li>
<li id="li_XuXX" name="li_XuXX">If you want to drop a VIEW, we can run the DROP VIEW statement</li>
<li id="li_famm" name="li_famm">If you want to modify an existing view you can use the statement CREATE OR REPLACE VIEW. That way you do not have to run a DROP VIEW statement and then a CREATE VIEW statement.</li>
</ul>