SQL Stragglers
Before we close this tutorial section, there are a few more advanced SQL topics you may encounter in your travels.
Views
Some SQL servers allow you to create
different views of a table. Views are like magic
permanent SELECT
queries. Once you create a view
using a special SELECT
query, the results of your
query stick around and behave like their own table. Views can be
queried like any other table. Modifications to a view, with a few
restrictions, are propagated back to the original table or tables.
Note I said tables. Here’s where the magic of views comes in: a view on a table can be created that consists of a join between that table and another. This view behaves as one large virtual table. Changes to this view are propagated back to the original tables that are part of the join that created the view.
A view can also be created with a new column consisting of calculations performed between other columns in that table, almost like a spreadsheet. Views are useful for more mundane purposes also, like query simplification (i.e., may be able to select fewer columns) and data restructuring (i.e., table users sees a view of the data that doesn’t change, even if other columns in the underlying table structure are modified).
Here’s a view creation example that demonstrates query simplification:
USE sysadm CREATE VIEW ipaddr_view AS SELECT name, ipaddr FROM hosts
Now we can use a very simple query to get back just the information we need:
USE sysadm SELECT * FROM ipaddr_view
The result ...
Get Perl for System Administration now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.