msql(5)

NAME
mSQL - Mini SQL Specification

DESCRIPTION
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 relational capabilities such as table joins, views or 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. Although the definitions and examples below depict mSQL key words in upper case, no such restriction is placed on the actual queries.

  The Create Clause
The create clause as supported by mSQL can only be used to create a table. It cannot be used to create other definitions such as views. It should also be noted that there can only be one primary key field defined for a table. Defining a field as a key generates and implicit ``not null'' attribute for the field.
    CREATE TABLE table_name (
    	col_name	col_type	[ not null | primary key ]
    	[ , col_name	col_type	[ not null | primary key ] ]**
    )
for example:
    CREATE TABLE emp_details(
    	first_name		char(15) not null,
    	last_name		char(15) not null,
    	dept		char(20),
    	emp_id		int primary key,
    	salary		int
    )
The available types are:
    o char (len)
    o int
    o real

  The Drop Clause
Drop is used to remove a table definition from the database:
    DROP TABLE table_name
for example:
    DROP TABLE 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). Currently, you must also specify the names of the fields into which the data is to be inserted. You cannot specify the values without the field name and expect the server to insert the data into the correct fields by default.
    INSERT INTO table_name ( column [ , column ]** )
    VALUES (value [, value]** )
for example:
    INSERT INTO emp_details ( first_name, last_name, dept, salary)
    VALUES (`David', `Hughes', `I.T.S.','12345')
The number of values supplied must match the number of columns.

  The Delete 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 or ILIKE
for example:
    DELETE FROM emp_details WHERE emp_id = 12345

  The Select Clause
The select offered by mSQL lacks some of the features provided by the SQL spec:
    o No nested selects
    o No implicit functions (e.g. count(), avg() ) It does however support:
    o Joins
    o DISTINCT row selection
    o ORDER BY clauses
    o Regular expression matching
    o Column to Column comparisons in WHERE clauses So, the formal syntax for mSQL's select is:
    SELECT [table.]column [ , [table.]column ]**
    FROM table [ , table]**
    [ WHERE [table.] column OPERATOR VALUE
    	[ AND | OR [table.]column OPERATOR VALUE]** ]
    [ ORDER BY [table.]column [DESC] [, [table.]column [DESC] ]
    OPERATOR can be <, >, =, >=, <=, <>, LIKE or ILIKE
    VALUE can be a literal value or a column name
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, 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
The regular expression syntax supported by LIKE and ILIKE clauses is that of standard SQL:
    o `_' matches any single character
    o `%' matches 0 or more characters of any value
    o `' escapes special characters (e.g. `' matches % and `\' matches )
    o all other characters match themselves So, to search for anyone in finance who's last name consists of a letter followed by `ughes', such as Hughes, the query could look like this:
    SELECT first_name, last_name FROM emp_details
    WHERE dept = `finance' and last_name like `_ughes'
The power of a relational query language starts to become apparent when you start joining tables together during a select. 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, albeit slowly, 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.

  The Update Clause
The mSQL update clause cannot use a column name as a value. Only literal values may by used as an update value
    UPDATE table_name SET column=value [ , column=value ]**
    WHERE column OPERATOR value
    	[ AND | OR column OPERATOR value ]**
    OPERATOR can be <, >, =, <=, >=, <>, LIKE or ILIKE
for example:
    UPDATE emp_details SET salary=30000 WHERE emp_id = 1234

SEE ALSO
libmsql(3), msqld(5).