14.3.1.1 Querying

select

Function

select &rest selections
 &key all
 set-operation
 distinct
 from
 where
 flatp 

       group-by
 having
 order-by
 database

Selects data from database given the constraints specified. Returns a list of lists of record values as specified by args . By default, the records are each represented as lists of attribute values. The selections argument may be either db-identifiers or literal strings.

Database identifiers used in select are conveniently specified using the symbolic SQL [] syntax. This syntax is enabled by calling enable-sql-reader-syntax , and is only available in packages which inherit from the SQL package.

You should therefore create a new package which includes the sql package, before using the bracket syntax.

For a description of the symbolic SQL syntax see Symbolic SQL syntax. For example, the following is a potential query and result:

(select [person_id] [person surname] :from [person])

--> ((111 "Brown") (112 "Jones") (113 "Smith"))

In this example, [person_id] , [person surname ] and [person ] are database-identifiers and evaluate to literal SQL. The result is a list of lists of attribute values. Conversely, consider

(select [surname] :from [person] :flatp t)

--> ("Brown" "Jones" "Smith")

In this case the result is a simple list of surname values because of the use of the flatp keyword. The flatp keyword only works when there is one column of data to return.

In this final example the :where keyword is used to specify a condition for returning selected values from the database.

(select [surname] :from [person] :where [= [person_id] 112])

-> (("Jones"))

print-query

Function

Prints a tabulated version of the records resulting from query-exp .

titles

A list of strings for using as column headings -- nil means no column headings are used. It is nil by default.

formats

A list of format strings used to print each attribute -- t means use ~A , or ~VA if sizes are provided or computed. It is t by default.

sizes

A list of field sizes for printing the attributes -- t means compute minimum sizes. It is t by default.

stream

An output stream -- t means use *standard-output* . It is t by default.

The following call prints two even columns of names and salaries:

(print-query [select [surname] [income] :from [person]]

             :titles '("NAME" "SALARY"))

NAME   SALARY

Brown  22000

Jones  45000

Smith  35000