Database views allow you to easily reduce the complexity of the end user experience and limit their ability to access data contained in database tables by limiting the data presented to the end user. Essentially, a view uses the results of a database query to dynamically populate the contents of an artificial database table.
Why Use Views?
There are two primary reasons to provide users with access to data through views rather than providing them with direct access to database tables:
- Views provide simple, granular security. You can use a view to limit the data that a user is allowed to see in a table. For example, if you have an employees table and wish to provide some users with access to the records of full-time employees, you can create a view that contains only those records. This is much easier than the alternative (creating and maintaining a shadow table) and ensures the integrity of the data.
- Views simplify the user experience. Views hide complex details of your database tables from end users who do not need to see them. If a user dumps the contents of a view, they won’t see the table columns that aren’t selected by the view and they might not understand. This protects them from the confusion caused by poorly named columns, unique identifiers and table keys.
Creating a View
Creating a view is quite straightforward: you simply need to create a query that contains the restrictions you wish to enforce and place it inside the CREATE VIEW command. Here’s the syntax:
CREATE VIEW viewname AS
For example, if you wish to create the full-time employees view I discussed in the previous section, you would issue the following command:
CREATE VIEW fulltime AS
SELECT first_name, last_name, employee_id
Modifying a View
Changing the contents of a view uses the exact same syntax as the creation of a view, but you use the ALTER VIEW command instead of the CREATE VIEW command. For example, if you wanted to add a restriction to the fulltime view that adds the employee’s telephone number to the results, you would issue the following command:
ALTER VIEW fulltime AS
SELECT first_name, last_name, employee_id, telephone
Deleting a View
It’s simple to remove a view from a database using the DROP VIEW command. For example, if you wish to delete the full-time employees view, you would use the following command:
DROP VIEW fulltime