SSQL is the $PRODUCT_NAME version of SQL. SQL the Structured Query Language, a standard language for querying databases. SSQL is a subset of SQL which is used by $PRODUCT_NAME to query its own internal database, and to query internal tables.

SSQL is not a full implementation of SQL. It provides only a subset of full SQL functionality.

This chapter does not attempt to describe the full syntax of SQL; the purpose of this chapter is to show which portions of SQL are supported by SSQL. See an online SQL reference for full details of what these options mean, and how they work.

Query Types

The following query types are supported:

• SELECT
• DELETE FROM
• CREATE TABLE
• INSERT
• DROP TABLE
• SHOW TABLES
• CREATE INDEX
• USE
• ALTER TABLE

SELECT

Syntax:

  SELECT column1, columnN
  FROM tablename
  [ (LEFT|RIGHT|INNER) JOIN jointablename ON tablecolumn = jointablename [ AND condition ] ]
  [ WHERE condition ]
  [ GROUP BY column1, ..., columnN ]
  [ ORDER BY column, ..., columnN ]
  [ LIMIT startrow, numrows ]

This selects data from one or more tables.

The columnN values in the SELECT clause may be column names, or may use the aggregation operators SUM, MAX, MIN, AVG, COUNT (including COUNT(DISTINCT F)), or SET.

The SET operator is a SSQL extension which creates a "set" column, where each cell contains a set of integer values. Sets can be of arbitrary size; duplicate values are removed; and are merged if a SET column is grouped using a SET aggregation operator. The integer value of a SET column is the number of items in the set. COUNT(DISTINCT) is implemented using SET.

There may be any number of JOIN clauses.

WHERE conditions, and JOIN conditions, support the following SQL operatoer: AND, OR, NOT, =, and !=.

CREATE TABLE

Syntax:

  CREATE TABLE tablename (fieldname1 fieldtype1, ... fieldnameN fieldtypeN)
  [ SELECT ... ]

This creates a table, optionally from the result of a query.

If a SELECT clause is present, it can use any syntax of a normal SELECT clause; the result of the SELECT is inserted into the new table.

INSERT

Syntax:

  INSERT INTO tablename
  [ SELECT ... ]

This inserts the results of a query into tablename.

The SELECT portion can use any syntax of a normal SELECT clause; the result of the SELECT is inserted at the end of tablename.

DELETE FROM

Syntax:

  DELETE FROM tablename WHERE condition

This delete rows from tablename which match condition. See SELECT for more information about WHERE conditions.

DROP TABLE

Syntax:

  DROP TABLE tablename

This drops the table tablename from the database.

SHOW TABLES

Syntax:

  SHOW TABLES LIKE expression

This shows all tables in the database whose names match the wildcard expression expression.

ALTER TABLE

Syntax:

  ALTER TABLE tablename ADD columnname columntype

  ALTER TABLE tablename DROP COLUMN columnname

The first adds a column to the tablename table; the new column will be named columnname and will have type columntype.

The second drops a column from the tablename table.

CREATE INDEX

Syntax:

  CREATE [ UNIQUE ] INDEX indexname ON tablename (fieldname)

This creates an index on the field fieldname in the table tablename. Indices can make queries and joins faster, if the conditions of the queries or the columns of the joins match the indices available in the table.

USE

Syntax:

  USE DATABASE databasename

This has no effect; it is allowed for compatibility reasons only.