IPT - A Virtual Approach - mSQL Data Manipulation

:: Intro :: Datatypes :: DDL :: DML :: Operators :: Built-ins ::

Data Retrieval

The SELECT statement is generally the most used of all SQL statements

     SELECT  [DISTINCT] colName/expression {, colName/expression}
    [FROM tableName {, tableNname}]
    [WHERE  searchCondition/colName IN (selectStatement)]
      {AND/OR  searchCondition/colName IN (selectStatement)}
    [GROUP BY colName {,colName}
      [HAVING  searchCondition]]
    [ORDER BY colName [ASC/DESC] {, colName [ASC/DESC]}]

     selectStatement { UNION selectStatement}

...up to 8 query unions are allowed, so long as the fields being UNIONed are type compatible (ie. are the same data type and width).

Table Join Operators

mSQL for Windows introduces a number of powerful table join operators, allowing users to specify INNER, OUTER and CROSS type joins (with associated LEFT, RIGHT and FULL) constraints, using the following syntax:

     SELECT selectExpression
     FROM tableName [alias]
          [NATURAL][INNER/CROSS/UNION] [LEFT/RIGHT/FULL] [OUTER] JOIN
          tableName [alias]
          [USING (columnList)]
          [ON searchCondition]

Table Population

     INSERT INTO tableName (colName {,colName})
          VALUES (const {, const} ){,(const {, const} )}

where file_name refers to a TEXT FILE in DELIMETED ASCII

     INSERT INTO tableName (colName {,colName})
          selectStatement

Data Modification

     UPDATE tableName
          SET colName = value {, colName =value}
          [WHERE searchCondition]

     UPDATE tableName
          SET colName = selectStatement
          [WHERE searchCondition]

     DELETE FROM tableName
          [WHERE rowCondition]

Table Re-Definition

     ALTER TABLE tableName ADD
          [COLUMN] col_name columnDefinition
          [[checkName] CHECK (searchCondition)]
          [FOREIGN KEY (colList) REFERENCES tableName]
          [PRIMARY KEY (colList)]
          [UNIQUE (colList)]

     ALTER TABLE tableName ALTER [COLUMN] colName
          [DROP DEFAULT]
          [SET DEFAULT literal]
          [(newColumnDefinition)]

     ALTER TABLE tableName DROP CONSTRAINT
          [CHECK checkName|(checkIdentifier)]
          [FOREIGN KEY (colList)]
          [PRIMARY KEY]
          [UNIQUE (colList)]

     ALTER TABLE tableName DROP [COLUMN] colName

I would suggest that you use the ALTER TABLE command very carefully, realising once a column is dropped, the data that was in that column is also irretrievably lost. Similarly, it is logically impossible to add a NOT NULL column to anything but an empty table, unless you specify a DEFAULT value as part of the column definition.

'Purists' would argue that the ALTER TABLE command should not be necessary, as the database design is well thought out in the first place (long before table definition occurs). Realists accept that mistakes happen :)