MySQL Crash Course #14 # Chapter 22. Using views

Index

1. What's the view?
2. Why do I need a view
3. Rules for working with views
4. How to use views
5. View Application Instance
6. Don't update your data with views!

What's the view?

The best way to understand a view is to look at the following example.

SELECT Cust_name, Cust_contact  from customers, orders, OrderItems WHERE = orders.cust_id    and = Orders.order_num    and = ' TNT2 ';

The above request is used to retrieve information about a customer who has purchased a particular product , and anyone who wants to retrieve the data above must understand the structure of the table and the relationships between the multiple tables . If you want to obtain the same information for another product, you must also modify the conditions that follow the WHERE.

If the entire request can be encapsulated into a productcustomers table , then only the following statement is sufficient to retrieve the required data:

SELECT Cust_name, Cust_contact  from productcustomers WHERE = ' TNT2 ';

Productcustomers is a view that contains only the request, which itself does not have any fields and data, and when the view is used, the view dynamically retrieves the data that is needed.

Why do I need a view

You ' ve already seen one with for views. Here is some other common uses:

• to reuse SQL statements.

• to simplify complex SQL operations. After the query was written, it can be reused easily, without have to know the details of the underlying query itself.

• to expose parts of a table instead of complete tables .

• to secure data . Users can be given access to specific subsets of tables instead from to entire tables.

• to change data formatting and representation. Views can return data formatted and presented differently from their underlying tables.

For the more part, after views is created, they can be used in the same as tables. You can perform SELECT operations, filter and sort data, join views to other views or tables, and possibly even a DD and update data. (There is some restrictions on the last item. More on this in a moment.)

The important thing to remember was views was just that and views into data stored elsewhere. Views contain no data themselves, so the data they return was retrieved from other tables. When data was added or changed in those tables, the views would return that changed data.

Rules for working with views

Here is some of the most common rules and restrictions governing view creation and usage:

• Like tables, must is uniquely named. (they cannot is named with the name of any other table or view).

• There is no limit to the number of views that can created.

• To create views, you must hAve security Access. This was usually granted by the database administrator.

• Views can be nested; That's, a view may built using a query that retrieves data from another view.

• order by was used in a view, but it would be overridden if ORDER by was also used in the SELECT That's retrieves data from the view.

• Views cannot is indexed, nor can they has triggers or default values associated with them.

• Views can is used in conjunction with tables, For example, to create a SELECT statement which joins a TA Ble and a view.

How to use views

So now this you know what is (and the rules and restrictions that govern them), let's look at View creation:

• Views is created using the CREATE VIEW statement.

• To view the statement used to create a view, use SHOW create view viewname;.

• To remove a view, the DROP statement is used. The syntax is simply DROP VIEW viewname;.

• To update a view the DROP statement and then the CREATE statement again, or just us E Create OR replace VIEW, which would create it if it does not exist and replace it if it does.

View Application Instance

Format the retrieved data:

CREATE VIEW  as SELECT Concat (RTrim'  ('RTrim')')         as Vend_title  from Vendors ORDER  by Vend_name;

To filter unwanted data:

CREATE VIEW  as SELECT cust_id, Cust_name, Cust_email  from Customers WHERE  is  not NULL;

Simplify calculated fields:

CREATE VIEW  as SELECT Order_num,       prod_id,       quantity,       item_price,       quantity* as Expanded_price from OrderItems;

Don't update data with views

Yes, but not recommended!

MySQL Crash Course #14 # Chapter 22. Using views