What are views in MySQL?

Question

What are views in MySQL? What's the point of views and how often are they used in the real world.

I'm after a layman explanation please with a example if possible to aid my understanding!

Thank you in advance ;-)

Answer 1

Normal Views are nothing more then queryable queries.

Example:

You have two tables, orders and customers, orders has the fields id, customer_id, performance_date and customers has id, first_name, last_name.

Now lets say you want to show the order id, performance date and customer name together instead of issuing this query:

SELECT    o.id as order_id, c.first_name + ' ' + c.last_name as customer_name,
          o.performance_date 
FROM      orders o inner join customers c

you could create that query as a view and name it orders_with_customers, in your application you can now issue the query

SELECT   *
FROM     orders_with_customer

One benefit is abstraction, you could alter the way you store the customers name, like inlcuding a middle name, and just change the views query. All applications that used the view continue to do so but include the middle name now.

Answer 2

Have a look at:

MySQL Views Tutorial
Views and More in MySQL 5.0