 | Appendix D: The Fifteen-Minute SQL Tutorial |  |
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.
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
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.
|
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.
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.
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.
|  | |
| D.2. Inserting Data into a Table |  | D.4. Changing Table Information |
Copyright © 2001 O'Reilly & Associates. All rights reserved.