Views


Of the three new features in MySQL 5 that I discuss in this chapter, the view is the easiest to understand and implement. A view is simply a special kind of table, often based upon the structure and data of standard tables. For example, looking at the accounting database, you might often need to see how much in total dollars each client has been invoiced. You can establish a view that reflects just that information.

You might think that the view would then be a static look at some data: a snapshot taken at the time the view was created. But no! If you define a view based upon selecting data from other tables, when those tables are updated, so is the data in the view.

Views are available as of MySQL 5.0.1. If you upgraded to this version or later, make sure that you upgrade the grant tables so that the right permissions are enabled (see Chapter 1, "Installing MySQL"). Views, like stored routines and triggers, are specific to a database.

I'll show you how to create, use, modify, and drop views over the next several pages.

Creating a view

You make a view using the CREATE VIEW command. The basic syntax is

CREATE VIEW view_name AS select_query


The select_query is the important part: it dictates the view's structure and populates it. The SELECT query can be more or less anything, including joins, unions, and subqueries. There are a few limitations: a view cannot:

  • Contain a subquery in its FROM clause

  • Use system or user variables

  • Use prepared statements

The results of the SELECT query, which looks like a table (Figure 11.30), will be the view.

Figure 11.30. If you think about how SELECT query results look like tables, you'll understand what a view conceptually is.


In order to create a view, you will need to have CREATE VIEW privileges within the database as well as SELECT privileges on the tables involved.

By default, the new view will be created using column names that match those retrieved by the SELECT statement. Say I define a view as

CREATE VIEW emp_directory AS SELECT first_name, last_name, phone FROM employees


Then the emp_directory view has three columns, called first_name, last_name, and phone. If you use an alias, that will be a column's name, just as it acts as the header in the returned results.

You can define your own column names by listing them:

CREATE VIEW view_name (col1, col2, col3...) AS select_query


If you choose to do this, the number of columns must match the number returned by the query. Notice that you don't specify a data type for each column, as that will automatically be dictated by the defining SELECT statement.

To create a view:

1.

Log in to the mysql client as a user with permission to create views on the accounting database.

2.

Select the accounting database.

USE accounting;


3.

Show the total amount of expenses by category (Figure 11.31).

SELECT expense_category AS category, SUM(expense_amount) AS total FROM expense_categories AS ec LEFT JOIN expenses USING (expense_category_id) GROUP BY ec.expense_category_id


Before creating a view, you may want to run the SELECT query first to confirm that it works and has the results you desire. This is a join on two tables that also uses one of the aggregate functions.

Figure 11.31. The SELECT query previews the view it will be used to create.


4.

Create a view that lists the total expenses in each category (Figure 11.32).

CREATE VIEW total_expenses AS SELECT expense_category AS category, SUM(expense_amount) AS total FROM expense_categories AS ec LEFT JOIN expenses USING (expense_category_id) GROUP BY ec.expense_category_id;


By slapping CREATE VIEW total_expenses before the query in Step 3, I can create a view whose contents will be the results from that query.

Figure 11.32. Creating a view.


5.

Create a view that lists the number and total amount of outstanding invoices for each client (Figure 11.33).

CREATE VIEW outstanding_invoices AS SELECT client_name AS client, SUM(invoice_amount) AS total, COUNT(invoice_id) AS invoices FROM invoices JOIN clients USING (client_id) WHERE date_invoice_paid IS NULL AND is_overdue(invoice_date) GROUP BY invoices.client_id;


This, as complicated as it may seem, works nicely. It returns three values: the client's name, the total amount of money that is past due, and the total number of invoices this involves. A join is used on two tables, and the stored function created earlier in the chapter determines which invoices are considered overdue.

Figure 11.33. Creating another view.


6.

View the database's tables (Figure 11.34).

SHOW TABLES;


Figure 11.34. Views appear in the list of tables along with standard tables.


Tips

  • You can see how a view was originally created by using SHOW CREATE VIEW view_name.

  • Triggers cannot be associated with views. They can only be associated with tables.

  • There are a couple of other clauses that can be added to your view definition. Two of these are related to security and who has permission to access a view. Another option is to dictate the algorithm used for managing the view. All of these are detailed in the MySQL manual but aren't needed for most views, particularly as you're just learning the concept.


Altering or Dropping a View

Once you've created a view, there are two ways to alter its definition. The first method is to use the CREATE VIEW syntax, with the added OR REPLACE clause:

CREATE OR REPLACE VIEW view_name AS select_query


You must have DROP VIEW privileges to execute this, though (as it drops the view and then creates the new one with the same name).

The second method is to use an ALTER VIEW statement. It looks a lot like a CREATE VIEW:

ALTER VIEW view_name AS select_query


Once you no longer need a view, you can delete it using

DROP VIEW view_name


To be extra cautious, you can add the IF EXISTS clause:

DROP VIEW IF EXISTS view_name



Using a view

As I already said, once you've created a view, you can use it like any other table. To really show what this means, let's try a few queries out.

To use a view:

1.

Log in to the mysql client as a user with permission to use views on the accounting database.

2.

Select the accounting database.

USE accounting;


3.

Show every expense category that has not yet been used (Figure 11.35).

SELECT category FROM total_expenses WHERE total IS NULL;


Because the view itself is based upon a join, I can now use a simple query to whittle down my results.

Figure 11.35. This simple query returns one column's values from a view.


4.

Reveal the entire contents of the outstanding_invoices view (Figure 11.36).

SELECT * FROM outstanding_invoices;


You can see both the column names and the row values in this view. For the column names, the original SELECT query's aliases are used.

Figure 11.36. SELECT * queries also work on views.


Updating View Data

Just because a view acts like a table, that does not mean that you should always treat it like one. While it's safe to run SELECT queries on a view, I would argue against running any other, like INSERT, UPDATE, or DELETE. As the data in most views is based upon data stored in actual tables, you should, when necessary, alter the data in the underlying tables, not in the view.

As a matter of fact, the data in certain views cannot be modified regardless. This includes some views based upon multiple tables as well as ones whose SELECT query includes GROUP BY, DISTINCT, LIMIT, UNION, HAVING, or any of the aggregate functions.


5.

Mark some of the invoices as paid (Figure 11.37).

SELECT invoice_id, invoice_amount, client_name FROM clients JOIN invoices USING (client_id) WHERE is_overdue(invoice_date); UPDATE invoices SET date_invoice_paid=NOW() WHERE invoice_id IN (11, 15);


The first query returns the invoice information along with the client's name for each overdue invoice. Now I can decide which of these invoices have been paid (assuming they actually have been) and mark them as such using the second query.

Figure 11.37. The information for the outstanding invoices is first retrieved. This is then used to mark two of the invoices as paid.


6.

Reveal the entire contents of the outstanding_invoices view again (Figure 11.38).

SELECT * FROM outstanding_invoices;


You'll see how the view has been updated so that only one outstanding invoice remains.

Figure 11.38. Changes to the tables on which the view is defined immediately affect the contents of the view. Compare this with Figure 11.36.


Tip

  • The queries I'm using in this views section are based upon sample data that I have. You may need to change them so that they apply better to the data you have. Also, as joins and GROUP BY queries can be complex, often not giving the result expected, rest assured that a fair amount of experimentation went into making them correct. This is my way of reminding you not to sweat it if some queries take you a while to get right.





MySQL Visual QuickStart Guide Serie  .Covers My SQL 4 and 5
MySQL, Second Edition
ISBN: 0321375734
EAN: 2147483647
Year: 2006
Pages: 162
Authors: Larry Ullman

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net