|
|
Sofar, when you've wanted to restructure data with calculated columns or extract data from multiple tables based on comparisons between column values, you've had to resort to relatively complex SQL statements. Typically, though, you'll want to obtain data in this modified form more than once. Certain tasks, such as getting the name of the customer associated with an order, will be performed so often that it can seem awkward to have to query multiple tables all the time.
To get around this, SQL allows you to create views in a database, which provide (semi)permanent reshaping of data from tables in a database that you can access with queries as an alternative to accessing tables directly. This has the advantage of providing an additional layer of abstraction to the user, which can mean that you can format data in a more consistent way and even enhance security by providing access only to views, not to the underlying data. Views are sometimes referred to as virtual tables.
A view is basically the set of rows you'd receive should you execute a given query. This set of rows looks exactly like any other table to a user of the database although all the data it contains is in fact stored in other tables.
This can be extremely useful for restricting access to certain data. Instead of giving users access to the actual tables in the database, you can instead give them access to a view of the table, which might not contain all of the available data. In addition, modifying data through a view is usually not possible, so you can provide a simple, read-only version of the data that's accessible to low-privilege users.
Another great thing about views is that they're updated whenever their underlying data changes, without you having to do anything!
| Note |
At the time of writing, views have not yet been implemented in MySQL or Microsoft Access, but they're available in SQL Server, Oracle, and DB2. |
|
|