Querying Information (Perl for System Administration)

D.3. Querying Information

As an administrator, the SQL command you'll probably use the most often is SELECT. SELECT is used to query information from a server. Before we talk about this command, a quick disclaimer: SELECT is a gateway into a whole wing of the SQL language. We're only going to demonstrate some of its simpler forms. There is an art to constructing good queries (and designing databases so they can be queried well), but more in-depth coverage like this is best found in books entirely devoted to SQL and databases.

The simplest SELECT form is used mostly for retrieving server and connection-specific information. With this form, you do not specify a data source. Here are two examples:

-- both of these are database vendor specific
SELECT @@SERVERNAME

SELECT VERSION(  );

The first statement returns the name of the server from a Sybase or MS-SQL server; the second returns the current version number of a MySQL server.

D.3.1. Retrieving All of the Rows in a Table

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

USE sysadm
SELECT * FROM hosts

This returns all of the rows and columns in the same column order as 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        Ultra60    
bendir    192.168.1.3   ben bendoodles              Cindy Coltrane IT        
West  143  Apple      7500/100   
sander    192.168.1.55  sandy micky mickydoo        Alex Rollins IT        
Main  1101 Intergraph TD-325     
sulawesi  192.168.1.12  sula su-lee                 Ellen Monk
Design   
 Main  1116 Apple      G3

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 creating the table. For instance, to see IP addresses per building:

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

D.3.2. Retrieving a Subset of the Rows in a Table

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        Ultra60    
sander    192.168.1.55  sandy micky mickydoo        Alex Rollins      IT        Main  1101 Intergraph TD-325     
sulawesi  192.168.1.12  sula su-lee                 Ellen Monk        Design    Main  1116 Apple      G3

The set of available conditional operators for WHERE clauses are 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 non-empty 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 contents is one of several specified values, you can use the IN operator to specify a list:

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 on the tenth floor:

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 (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 lists the supported LIKE wildcards.

Table D-2. LIKE Wildcards

Wildcard	Meaning	Closest Perl Regexp Equivalent
%	Zero or more characters	.*
_	A single character	.
[ ]	A single character that is one of a specified set or range	[ ]

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.

D.3.3. Simple Manipulation of Data Returned by Queries

Two useful clauses for a SELECT statement are DISTINCT and ORDER BY. The first 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 could use DISTINCT:

USE sysadm
SELECT DISTINCT manuf FROM hosts

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

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 an SQL book for more detail on SELECT's many clause operators.

D.3.4. Adding the Query Results to Another Table

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 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 NT command-line shells.

TIP

Some database servers (like MySQL) do not support SELECTINTO; they require the use of an INSERT command to perform this action. Other servers such as MS-SQL and Sybase require a special flag be set on a database before SELECTINTO can be used within that database, or the command will fail.