CJ Hosting Tutorial Mini SQL Part 1 Language Specifications

Mini SQL 2.0 (Beta) Language Specification

Introduction

The mSQL language offers a significant subset of the features provided by ANSI SQL. It allows a program or user to store, manipulate and retrieve data in table structures. It does not support some relational capabilities such as views and nested queries. Although it does not support all the relational operations defined in the ANSI specification, it does provide the capability of "joins" between multiple tables.

The definitions and examples below depict mSQL key words in upper case, but no such restriction is placed on the actual queries.

The Create Clause

The create clause as supported by mSQL 2 can be used to create tables, indices, and sequences. It cannot be used to create other definitions such as views. The three valid constructs of the create clause are shown below:

CREATE TABLE table_name (: col_name col_type [ not null ]; [ , col_name col_type [ not null ] ]**
)
CREATE [ UNIQUE ] INDEX index_name ON table_name (: field_name; [ , field_name ] **
)
CREATE SEQUENCE ON table_name [ STEP step_val ] [ VALUE initial_val ]

An example of the creation of a table is show below:

CREATE TABLE emp_details (: first_name char(15) not null,; last_name char(15) not null,; comment text(50),; dept char(20),; emp_id int
)

The available types are:-

char (len)	String of characters (or other 8 bit data)
text (len)	Variable length string of chracters (or other 8 bit data) The defined length is used to indicate the expected average length of the data. Any data longer than the specified length will be split between the data table and external overflow buffers. Note : text fields are slower to access than char fields and cannot be used in an index nor in LIKE tests.
int	Signed integer values
real	Decimal or Scientific Notation real values

The table structure shown in the example would benefit greatly from the creation of some indices. It is assumed that the emp_id field would be a unique value that is used to identify an employee. Such a field would normally be defined as the primary key. mSQL 2.0 has removed support for the primary key construct within the table creation syntax although the same result can be achieved with an index. Similarly, a common query may be to access an employee based on the combination of the first and last names. A compound index (i.e. constructed from more than 1 field) would improve performance. We could construct these indices using :

CREATE UNIQUE INDEX idx1 ON emp_details (emp_id)
CREATE INDEX idx2 ON emp_details (first_name, last_name)

These indices will be used automatically whenever a query is sent to the database engine that uses those fields in its WHERE clause. The user is not required to specify any special values in the query to ensure the indices are used to increase performance.

Sequences provide a mechanism via which a sequence value can be maintained by the mSQL server. This allows for atomic operations (such as getting the next sequence value) and removes the concerns associated with performing these operations in client applications. A sequence is associated with a table and a table may contain at most one sequence.

Once a sequence has been created it can be accessed by SELECTing the _seq system variable from the table in which the sequence is defined. For example

CREATE SEQUENCE ON test STEP 1 VALUE 5
SELECT _seq FROM test

The above CREATE operation would define a sequence on the table called test that had an initial value of 5 and would be incremented each time it is accessed (i.e. have a step of 1). The SELECT statement above would return the value 5. If the SELECT was issued again, a value of 6 would be returned. Each time the _seq field is selected from test the current value is returned to the caller and the sequence value itself is incremented.

Using the STEP and VALUE options a sequence can be created that starts at any specified number and is incremented or decremented by any specified value. The value of a sequence would decrease by 5 each time it was accessed if it was defined with a step of -5.

The Drop Clause

The Drop clause is used to remove a definition from the database. It is most commonly used to remove a table from a database but can also be used for removing several other constructs. In 2.0 it can be used to remove the definition of an index, a sequence, or a table. It should be noted that dropping a table or an index removes the data associated with that object as well as the definition.

The syntax of the drop clause as well as examples of its use are given below.

DROP TABLE table_name
DROP INDEX index_name FROM table_name
DROP SEQUENCE FROM table_name

for example

DROP TABLE emp_details
DROP INDEX idx1 FROM emp_details
DROP SEQUENCE FROM emp_details

The Insert Clause

Unlike ANSI SQL, you cannot nest a select within an insert (i.e. you cannot insert the data returned by a select). If you do not specify the field names they will be used in the order they were defined - you must specify a value for every field if you do this.

INSERT INTO table_name [ ( column [ , column ]** ) ]

VALUES (value [, value]** )

for example

INSERT INTO emp_details

(first_name, last_name, dept, salary)

VALUES (`David', `Hughes', `Development','12345')

INSERT INTO emp_details

VALUES (`David', `Hughes', `Development','12345')

The number of values supplied must match the number of columns.

The Select Clause

The SELECT offered by mSQL lacks some of the features provided by the standard SQL specification. Development of mSQL 2 is continuing and some of this missing functionality will be made available in the next beta release. At this point in time, mSQL's select does not provide

Nested selects

Implicit functions (e.g. count(), avg() )

It does however support:

Joins - including table aliases

DISTINCT row selection

ORDER BY clauses

Regular expression matching

Column to Column comparisons in WHERE clauses

Complex conditions

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

SELECT [table.]column [ , [table.]column ]**: 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.
CLIKE - a standard LIKE operator that ignores case.
RLIKE - a complete UNIX regular expression operator.

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

`_'	matches any single character
`%'	matches 0 or more characters of any value
`\'	escapes special characters (e.g. `\%' matches % and `\\' matches \ )
	all other characters match themselves

As an example of the LIKE operator, it is possible to search for anyone in the finance department who's last name consists of any letter followed by `ughes', such as Hughes. The query to perform this operation could look like

SELECT first_name, last_name FROM emp_details

WHERE dept = `finance' and last_name like `_ughes'

The RLIKE operator provides access to the power of the UNIX standard regular expression syntax. The UNIX regular expression syntax provides far greater functionality than SQL's LIKE syntax. The UNIX regex syntax does not use the '_' or '%' characters in the way SQL's regex does (as outlined above). The syntax available in the RLIKE operator is

'.'	matches any single character
'^'	When used as the first charactr in a regex, the caret character forces the match to start at the first character of the string
'$'	When used as the last charactr in a regex, the dollar sign forces the match to end at the last character of the string
'[ ]'	By enclosing a group of single characters withing square brackets, the regex will match a single character from the group of characters. If the ']' character is one of the characters you wish to match you may specifiy it as the first character in the group without closing the group (e.g. '[]abc]' would match any single character that was either ']', 'a', 'b', or 'c'). Ranges of characters can be specified within the group using the 'first-last' syntax (e.g. '[a-z0-9]' would match any lower case letter or a digit). If the first charactr of the group is the '^' character the regex will match any single character that is not contained within the group.
'*'	If any regex element is followed by a '' it will match zero or more* instances of the regular expression.

The power of a relational query language starts to become apparent when you join tables together during a select operation. Lets say you had two tables defined, one containing staff details and another listing the projects being worked on by each staff member, and each staff member has been assigned an employee number that is unique to that person. You could generate a sorted list of who was working on what project with a query like:

SELECT emp_details.first_name, emp_details.last_name, project_details.project

FROM emp_details, project_details

WHERE emp_details.emp_id = project_details.emp_id

ORDER BY emp_details.last_name, emp_details.first_name

mSQL places no restriction on the number of tables "joined" during a query so if there were 15 tables all containing information related to an employee ID in some manner, data from each of those tables could be extracted, by a single query. One key point to note regarding joins is that you must qualify all column names with a table name. mSQL does not support the concept of uniquely named columns spanning multiple tables so you are forced to qualify every column name as soon as you access more than one table in a single select.

mSQL also supports table aliases so that you can perform a join of a table onto itself. This may appear to be an unusual thing to do but it is a very powerful feature if there are rows within a single table relate to each other in some way. An example of such a table could be a list of people including the names of their parents. In such a table there would be multiple rows with a parent/child relationship. Using a table alias you could find out any grandparents contained in the table using something like

SELECT t1.parent, t2.child from parent_data=t1, parent_data=t2: where t1.child = t2.parent

The table aliases t1 and t2 both point to the same table (parent_data in this case) and are treated as two different tables that just happen to contain exactly the same data.

The Delete Clause

The SQL DELETE construct is used to remove one or more entries from a database table. The selection of rows to be removed from the table is based on the same where construct as used by the SELECT clause. The syntax for mSQL's delete clause is

DELETE FROM table_name

WHERE column OPERATOR value

[ AND | OR column OPERATOR value ]**

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

for example

DELETE FROM emp_details WHERE emp_id = 12345

The Update Clause

The SQL update clause is used to modify data that is already in the database. The operation is carried out on one or more rows as specified by the where construct. The value of any number of fields on the rows matching the where construct can be updated. mSQL places a limitation on the operation of the update clause in that it cannot use a column name as an update value (i.e. you cannot set the value of one field to the current value of another field). Only literal values may by used as an update value. The syntax supported by mSQL is

UPDATE table_name SET column=value [ , column=value ]**

WHERE column OPERATOR value

[ AND | OR column OPERATOR value ]**

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

for example

UPDATE emp_details SET salary=30000 WHERE emp_id = 1234

Top of Page