The 15-Minute SQL Tutorial - Automating System Administration with Perl

To get at all of the data in our hosts table, we can use this SQL code:

USE sysadm
SELECT * FROM hosts

This returns all of the rows and columns, in the column order in which our table was created:

name      ipaddr        aliases                    owner           dept
bldg  room  manuf  model
--------- ------------- -------------------------- --------------- ---------
----- ----- ------ -------------
shimmer   192.168.1.11  shim shimmy shimmydoodles  David Davis     Software
Main  309   Sun    M4000
bendir    192.168.1.3   ben bendoodles             Cindy Coltrane  IT
West  143   Apple  Mac Pro
sander    192.168.1.55  sandy micky mickydoo       Alex Rollins    IT
Main  1101  Dell   Optiplex 740
sulawesi  192.168.1.12  sula su-lee                Ellen Monk      Design
Main  1116  Apple  Mac Pro

If we want to see specific columns, we just need to specify them by name:

USE sysadm
SELECT name,ipaddr FROM hosts

When we specify the columns by name they are returned in the order we specify them, independent of the order used when the table was created. For instance, to see IP addresses per building, we could use this query:

USE sysadm
SELECT bldg,ipaddr FROM hosts

This returns:

bldg       ipaddr
---------- ---------------
Main       192.168.1.11
West       192.168.1.3
Main       192.168.1.55
Main       192.168.1.12

Databases wouldn’t be very interesting if you couldn’t retrieve a subset of your data. In SQL, we use the SELECT command and add a WHERE clause containing a conditional:

USE sysadm
SELECT * FROM hosts WHERE bldg='Main'

This shows:

name      ipaddr        aliases                    owner         dept      bldg
room  manuf  model
--------- ------------- -------------------------- ------------- --------- -----
----- ------ -------------
shimmer   192.168.1.11  shim shimmy shimmydoodles  David Davis   Software  Main
309   Sun    M4000
sander    192.168.1.55  sandy micky mickydoo       Alex Rollins  IT        Main
1101  Dell   Optiplex 740
sulawesi  192.168.1.12  sula su-lee                Ellen Monk    Design    Main
1116  Apple  Mac Pro

The set of available conditional operators for WHERE clauses contains the standard programming fare:

=      >      >=      <      <=      <>

Unlike Perl, SQL does not have separate string and numeric comparison operators.

Conditional operators can be combined with AND/OR and negated with NOT. We can test for an empty column using IS NULL, or for a non-empty column with IS NOT NULL. For instance, this SQL code will show all of the machines without owners listed in our table:

USE sysadm
SELECT name FROM hosts WHERE owner IS NULL

If you want to find all of the rows that have a column whose content is one of several specified values, you can use the IN operator to specify a list of values:

USE sysadm
SELECT name FROM hosts WHERE dept IN ('IT', 'Software')

This shows all of the machines in use in either the IT or software departments. SQL will also allow you to return rows that match a certain range of values (most useful with numeric or date values) with the BETWEEN operator. Here’s an example that shows all of the machines in the main building between the 10th and 19th floors (presuming you use a simple convention for room numbers):

USE sysadm
SELECT name FROM hosts
  WHERE (bldg = 'Main') AND
        (room BETWEEN '1000' AND '1999')

Finally, the WHERE clause can be used with LIKE to choose rows using weak pattern matching (weak, that is, in comparison to Perl’s regular expressions). For instance, this will select all of the machines that have the string “doodles” somewhere in their aliases:

USE sysadm
SELECT name FROM hosts WHERE aliases LIKE '%doodles%'

Table D.2, “LIKE wildcards” lists the supported LIKE wildcards.

Some database servers have added extensions to SQL to allow for regular expression use in SELECTs. For instance, MySQL offers the REGEXP operator for use with SELECT. REGEXP doesn’t have all the power of Perl’s regular expression engine, but it offers a substantial increase in flexibility over the standard SQL wildcards.

Three useful clauses for a SELECT statement are COUNT, DISTINCT, and ORDER BY. The first returns the number of rows retrieved:

USE sysadm
SELECT COUNT(*) FROM hosts

The second allows us to eliminate duplicate records returned by a query. If we want a list of all of the distinct manufacturers represented in our hosts table, we can use DISTINCT:

USE sysadm
SELECT DISTINCT manuf FROM hosts

The third clause allows us to specify the order of the returned results. If we want to see our data returned in a sorted order, we can use ORDER BY:

USE sysadm
SELECT name,ipaddr,dept,owner FROM hosts ORDER BY dept

Experienced database users often habitually add ORDER BY clauses to queries that return multiple rows because it makes dealing with the returned information easier.

SQL has several operators that can be used to modify the output returned by a query. They allow you to change column names, do summary and intra/intercolumn calculations, reformat how fields are displayed, perform subqueries, and a whole host of other things. Please see a dedicated SQL book for more detail on SELECT’s many clause operators.

A new table containing the results of a query can be created on the fly by using an INTO clause on some SQL servers:

USE sysadm
SELECT name,ipaddr INTO itmachines FROM hosts WHERE dept = 'IT'

This statement works just like those we’ve seen previously, except that the results of the query are added to another table called itmachines. With some servers, this table is created on the fly if it does not exist. You can think of this operator clause as the equivalent of the > operator in most Unix- and Windows-based operating system command-line shells.

CREATE TABLE COPY AS SELECT name,ipaddr FROM hosts WHERE dept = 'IT'

Most 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 specification of your query sticks around. Each time you access anything from the view, the original query is run to provide that information. 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 in a spreadsheet. Views are also useful for more mundane purposes, such as query simplification (e.g., you may be able to SELECT fewer columns) and data restructuring (e.g., table users see 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 of this query is:

name                           ipaddr
------------------------------ ---------------
shimmer                        192.168.1.11
bendir                         192.168.1.3
sander                         192.168.1.55
sulawesi                       192.168.1.12

Like tables, views are dropped using a form of the DROP command:

USE sysadm
DROP VIEW ipaddr_view

Dropping the view has no effect on the underlying data tables.

In all the queries we’ve seen thus far, we’ve asked the server to hand us back all of the results once the query has completed. But sometimes it is preferable to receive the answer to a query one line at a time. This is most often the case when embedding SQL queries in other programs. If your query returns tens of thousands of lines, chances are pretty good that you’ll want to process the results one line at a time, rather than storing them all in memory for later use. SQL programming in Perl often uses this line-at-a-time method. Here’s a small native-SQL program that demonstrates cursor use on a Sybase or Microsoft SQL Server:

USE sysadm
-- declare our variables
DECLARE @hostname varchar(30)
DECLARE @ip varchar(15)

-- declare our cursor
DECLARE hosts_curs CURSOR FOR SELECT name,ipaddr FROM hosts

-- open this cursor
OPEN hosts_curs

-- iterate over the table, fetching rows one at a time,
-- until we receive an error
FETCH hosts_curs INTO @hostname,@ip
WHILE (@@fetch_status = 0)
  BEGIN
     PRINT "----"
     PRINT @hostname
     PRINT @ip
     FETCH hosts_curs INTO @hostname,@ip
  END

-- close the cursor (not strictly necessary when followed
-- by a DEALLOCATE)
CLOSE hosts_curs

-- undefine cursor def
DEALLOCATE hosts_curs

This produces the following output:

----
shimmer
192.168.1.11
----
bendir
192.168.1.3
----
sander
192.168.1.55
----
sulawesi
192.168.1.12

Most database systems allow you to upload SQL code to the server, where it is stored in an optimized, post-parsed form for faster execution. Such uploads are known as stored procedures. Stored procedures are often a critical component of SQL for administrators, because large parts of server administration for some servers rely on them. For example, to change the owner of the sysadm database in Sybase, you might do this:

USE sysadm
sp_changedbowner "jay"

Some databases also support something called “triggers.” Triggers are stored procedures that automatically fire when some event takes place in the database (e.g., when a row gets INSERTed). Each database vendor implements triggers slightly differently, so check the documentation of the database you are using for the details on how to use CREATE TRIGGER and DROP TRIGGER.

Now that you’ve seen the basics of SQL, you’re ready to tackle Chapter 7, SQL Database Administration.