DISTINCT row selection

G   

ORDER BY clauses

G   

Regular expression matching

G   

Column to Column comparisons in WHERE clauses

G   

Complex conditions

G   

The formal definition of the syntax for mSQL s select clause is

SELECT [table.]column [ , [table.]column ]**

G   

FROM table [ = alias] [ , table [ = alias] ]**

[ WHERE [table.] column OPERATOR VALUE

[ AND | OR [table.]column OPERATOR VALUE]** ]

[ ORDER BY [table.]column [DESC] [, [table.]column [DESC] ]

OPERATOR can be <,> , =, <=, =, <>, LIKE, RLIKE or CLIKE

VALUE can be a literal value or a column name

Where clauses may contain  (   )  to nest conditions e.g. "where (age <20 or age>30) and

sex =  male " .

A simple select may be

SELECT first_name, last_name FROM emp_details

WHERE dept =  finance 

To sort the returned data in ascending order by last_name and descending order by first_name the

query would look like this

SELECT first_name, last_name FROM emp_details

WHERE dept =  finance 

ORDER BY last_name, first_name DESC

And to remove any duplicate rows from the result of the select, the DISTINCT operator could be used:

SELECT DISTINCT first_name, last_name FROM emp_details

WHERE dept =  finance 

ORDER BY last_name, first_name DESC

mSQL provides three regular expression operators for use in 

where

 comparisons. The standard SQL

syntax provides a very simplistic regular expression capability that does not provide the power nor the

flexibility UNIX programmers or users will be accustomed to. mSQL supports the "standard" SQL

regular expression syntax, via the LIKE operator, but also provide further functionality if it is

required. The available regular expression operators are:

LIKE   the standard SQL regular expression operator.

G   

CLIKE   a standard LIKE operator that ignores case.

G   

RLIKE   a complete UNIX regular expression operator.

G   

Note

 : CLIKE and RLIKE are not standard SQL and may not be available in other

implementations of the language if you decide to port your application. They are however very

convenient and powerful features of mSQL.

The regular expression syntax supported by the LIKE and CLIKE operators is that of standard

SQL and is outlined below