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

You might find some helpful information on what views are and how they're used in MySQL here.

Answer 2

Have a look at:

MySQL Views Tutorial
Views and More in MySQL 5.0

Answer 3

They're a shorthand for common filters.

Say you have a table of records with a deleted column. Normally you wouldn't be interested in deleted records and hence you could make a view called Records that filters out deleted records from the AllRecords table.

This will make your code cleaning since you don't have to append/prepend deleted != 1 to every statement.

SELECT * FROM Records

would return all not-deleted records.

Answer 4

In simple words

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

Source

In addtion to this, you can insert rows in an underlying table from a view provided that the only one table is referred by the view and columns which are not referred in the view allow nulls.

Answer 5

In mysql a view is a stored select statement

Look to this answer: MySQL Views - When to use & when not to

Answer 6

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 7

A view is a way of pre-defining certain queries. It can be queried just like a table, is defined by a query rather than a set of on-disk data. Querying it allows you to query the table's results.

In most cases, querying a view can be seen as equivalent to using the view's defining query as a subquery in your main query. Views allow queries to be shorter and more modular (as the common parts are defined separately in the view). They also provide opportunity for optimization (although not all databases do so; I am not sure if MySQL provides any optimizations to make views faster or not).

If you update the underlying tables, queries against the view will automatically reflect those changes.

Answer 8

It's simple: views are virtual tables.

Views are based on SELECT-queries on "real" tables, but the difference is that views do not store the information unlike real tables. A view only references to the tables and combines them the way SELECT says them to. This makes often used queries a lot more simplified.

Here's a simple example for you. Lets suppose you have a table of employees and a table of departments, and you'd like to see their salaries. First you can create a view for the salaries.

CREATE VIEW SALARIES
AS  
SELECT e.name,
       e.salary,
       d.name
FROM employees AS e, deparments as d
WHERE e.depid = d.depid
ORDER BY e.salary DESC

This query lists the name of the employee, his/her salary and department and orders them by their salaries in descending order. When you've done this you can use queries such as:

 SELECT * from SALARIES

On a larger scale you could make a view that calculates the average salary of the employees and lists who has a salary that's less than the average salary. In real life such queries are much more complex.

Answer 9

You can think of the view as a on-the-fly generated table. In your queries it behaves like an ordinary table but instead of being stored on the disk, it is created on the fly when it is necessary from a SQL statement that is defined when creating a view.

To create a view, use:

CREATE VIEW first_names AS SELECT first_name FROM people WHERE surname='Smith'

You can then use this view just as an ordinary table. The trick is when you update the table people the first_names view will be updated as well because it is just a result from the SELECT statement.

This query:

SELECT * FROM first_names

will return all the first names of people named Smith in the table people. If you update the people table and re-run the query, you will see the updated results.

Basically, you could replace views with nested SELECT statements. However, views have some advantages:

• Shorter queries - nested SELECT statements make the query longer
• Improved readability - if the view has a sensible name, the query is much easier to understand
• Better speed - the view's SELECT statement is stored in the database engine and is pre-parsed, therefore it doesn't need to be transferred from the client and parsed over and over again
• Caching and optimizations - the database engine can cache the view and perform other optimizations

Answer 10

This small article explains views in MySQL quite well: Views and More in MySQL 5.0