Home / Documentation / FAQ / Support / Download SQL Syntax

### SQL Syntax Index

> 1. SQL Syntax Introduction
> 2. CREATE TABLE syntax
> 3. ALTER TABLE syntax
> 4. DROP TABLE syntax
> 5. CREATE VIEW syntax
> 6. DROP VIEW syntax
> 7. COMPACT TABLE syntax
> 8. CREATE SCHEMA syntax
> 9. DROP SCHEMA syntax
> 10. INSERT syntax
> 11. DELETE syntax
> 12. UPDATE syntax
> 13. SELECT syntax
> 14. COMMIT and ROLLBACK syntax
> 15. CREATE USER, ALTER USER and DROP USER syntax
> 16. GRANT/REVOKE syntax
> 17. SET syntax
> 18. DESCRIBE syntax
> 19. SHOW syntax
> 20. SHUTDOWN syntax

1. SQL Syntax Introduction

> This section is a reference for the SQL grammar that Mckoi SQL Database supports. Mckoi SQL Database supports a subset of entry level ANSI SQL-92. This section is not intended to be a tutorial for learning SQL. For SQL books and online tutorials see the links section on the home page.

2. CREATE TABLE syntax

>     CREATE TABLE [ IF NOT EXISTS ] table_name
>       ( column_declare1, column_declare2, constraint_declare1, ... )

>     column_declare ::= column_name type [ DEFAULT expression ]
>       [ NULL | NOT NULL ] [ INDEX_BLIST | INDEX_NONE ]

>     type ::= BIT | REAL | CHAR | TEXT | DATE | TIME |
>       FLOAT | BIGINT | DOUBLE | STRING | BINARY | NUMERIC |
>       DECIMAL | BOOLEAN | TINYINT | INTEGER | VARCHAR |
>       SMALLINT | VARBINARY | TIMESTAMP | LONGVARCHAR |
>       JAVA_OBJECT | LONGVARBINARY

>     constraint_declare :: = [ CONSTRAINT constraint_name ]
>       PRIMARY KEY ( col1, col2, ... ) |
>       FOREIGN KEY ( col1, col2, ... ) REFERENCES f_table [ ( col1, col2, ... ) ]
>                  [ ON UPDATE triggered_action ] [ ON DELETE triggered_action ] |
>       UNIQUE ( col1, col2, ... ) |
>       CHECK ( expression )
>       [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
>       [ NOT DEFERRABLE | DEFERRABLE ]

>     triggered_action :: =
>       NO ACTION | SET NULL | SET DEFAULT | CASCADE

> When declaring string or binary column types the maximum size must be specified. The following example declares a string column that can grow to a maximum of 100 characters,

>     CREATE TABLE Table ( str_col VARCHAR(100) )

> When handling strings the database will only allocate as much storage space as the string uses up. If a 10 character string is stored in `str_col` then only space for 10 characters will be allocated in the database. So if you need a column that can store a string of any size, use an arbitrarily large number when declaring the column. Mckoi SQL Database does not use a fixed size storage mechanism when storing variable length column data.

> `JAVA_OBJECT` is a column type that can contain serializable Java objects. The `JAVA_OBJECT` type has an optional Java class definition that is used for runtime class constraint checking. The following example demonstrates creating a `JAVA_OBJECT` column.

>     CREATE TABLE ObjectTable (
>             obj_id NUMERIC, obj JAVA_OBJECT(java.awt.Point))

> If the Java class is not specified the column defaults to `java.lang.Object` which effectively means any type of serializable Java object can be kept in the column.

> String types may have a `COLLATE` clause that changes the collation ordering of the string based on another language. For example, the folling statement creates a string that can store and order Japanese text;

>     CREATE TABLE InternationalTable (
>             japanese_text VARCHAR(4000) COLLATE 'jaJP')

> The 'jaJP' is an ISO localization code for the Japanese language in Japan. Other locale codes can be found in the documentation to `java.text.Collate`.

> Unique, primary/foreign key and check integrity constraints can be defined in the `CREATE TABLE` statement. The following is an example of defining a table with integrity constraints.

>     CREATE TABLE Customer (
>       number VARCHAR(40)  NOT NULL,
>       name   VARCHAR(100) NOT NULL,
>       ssn    VARCHAR(50)  NOT NULL,
>       age    INTEGER      NOT NULL,

>       CONSTRAINT cust_pk PRIMARY KEY (number),
>       UNIQUE ( ssn ),              // (An anonymous constraint)
>       CONSTRAINT age_check CHECK (age >= 0 AND age < 200)
>     )

3. ALTER TABLE syntax

>     ALTER TABLE table_name ADD [COLUMN] column_declare
>     ALTER TABLE table_name ADD constraint_declare
>     ALTER TABLE table_name DROP [COLUMN] column_name
>     ALTER TABLE table_name DROP CONSTRAINT constraint_name
>     ALTER TABLE table_name DROP PRIMARY KEY
>     ALTER TABLE table_name ALTER [COLUMN] column_name SET default_expr
>     ALTER TABLE table_name ALTER [COLUMN] column_name DROP DEFAULT

>     ALTER CREATE TABLE ....

> `ALTER` is used to add / remove / modify the columns and integrity constraints of a table. The `ADD [COLUMN]` form adds a new column definition to the table (using the same column declaration syntax in the `CREATE` command). The `DROP [COLUMN]` form drops the column with the name from the table. `ALTER [COLUMN] column_name SET default_expr` alters the default value for the column. `ALTER [COLUMN] column_name DROP DEFAULT` removes the default value set for the column.

> The following example adds a new column to a table;

> `ALTER TABLE Order ADD notes VARCHAR(60000) DEFAULT 'n/a'`

> `ADD constraint_declare` is used to define a new integrity constraint on a table (using the same constraint declaration syntax in the `CREATE` command). `DROP CONSTRAINT` is used to drop a named constraint from a table.

> The other form of this statement is `ALTER CREATE TABLE ...`. This alters the table to the specification of the given `CREATE` statement. Any columns that are in the original table are not lost provided the column name is in the new table specification. Any columns that were not in the original table are set to the default value.

> The following example demonstrates this form of `ALTER` statement;

>     ALTER CREATE TABLE table
>       ( col1 INTEGER NOT NULL UNIQUE,
>         col2 NUMERIC,
>         col3 VARCHAR(90000) )

> The `ALTER CREATE TABLE ...` syntax is an extension to the SQL-92 standard.

4. DROP TABLE syntax

>     DROP TABLE [ IF EXISTS ] table_name1, table_name2, ....

> Removes the table(s) from the database. The `IF EXISTS` clause will drop the table only if it exists. If this clause is not present an error is generated if the table does not exist. Any data that was in a dropped table is lost so use with care.

5. CREATE VIEW syntax

>     CREATE VIEW table_name [ ( column_name1, column_name2, ... ) ]
>       AS SELECT ...

> Creates a new view. A view is a virtual table based on the result of a `SELECT` query. The content of a view may reference any number of other tables and views.

> A simple example of a view follows;

>     CREATE VIEW ViewOfTableA AS SELECT col1 FROM TableA

> A view acts like a regular table and can be queried as you would a table made with the `CREATE TABLE` statement. Views are read-only.

6. DROP VIEW syntax

>     DROP VIEW table_name

> Removes a view from the database. A view can be changed by dropping and recreating it.

7. COMPACT TABLE syntax

>     COMPACT TABLE table_name

> Compacts the table data file in the file system. This removes all unused space from the table file and may rearrange the structure of the table to a form that better fits the characteristics of the data being stored.

8. CREATE SCHEMA syntax

>     CREATE SCHEMA schema_name

> Creates a schema with the given name. By default a database has three schema initially defined, `SYS_INFO`, `SYS_JDBC` and `APP`. The `SYS_INFO` and `SYS_JDBC` schema contain a number of important system tables and the `APP` schema is the default user schema.

> The following is an example of creating a new schema and changing to it;

>     CREATE SCHEMA my_schema;
>     SET SCHEMA my_schema;

9. DROP SCHEMA syntax

>     DROP SCHEMA schema_name

> Drops the schema with the given name. A schema may only be dropped if it contains no tables. The `SYS_INFO` and `APP` schema may not be dropped.

10. INSERT syntax

>     INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
>       VALUES ( expression1_1, expression1_2, .... ),
>              ( expression2_1, expression2_2, .... ), ....

>     INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
>       SELECT ...

>     INSERT INTO table_name
>       SET col_name1 = expression1, col_name2 = expression2, ....

> This is the SQL command to insert records into a table in the database. This statement comes in three forms. The first inserts data from a `VALUES` clause;

>     INSERT INTO table ( col1, col2, col3 )
>       VALUES ( 10, 4 + 3, CONCAT('1', '1', 'c') ),
>              ( 11, (28 / 2) - 7, CONCAT(col1, 'c') )

> The second form is used to copy information from a `SELECT` query into the table specified in the `INSERT` statement. For example;

>     INSERT INTO table ( col1, col2, col3 )
>       SELECT id, num, description
>         FROM table2
>        WHERE description LIKE '11%'

> The third form uses a list of column `SET` assignments. For example;

>     INSERT INTO table
>       SET col1 = 10, col2 = 4 + 3, col3 = CONCAT(col1, 'c')

> If a column of the table is not specified in an `INSERT` the default value declared for the column is used. If no default value was declared a NULL value is inserted in the column. If the column is declared as `NOT NULL` the insert operation fails.

11. DELETE syntax

>     DELETE FROM table_name
>       [ WHERE expression ]
>       [ LIMIT limit_amount ]

> Deletes all the rows from the table that match the `WHERE` clause. An optional `LIMIT` clause specifies the maximum number of matched rows to be removed. An example of using the `DELETE` statement;

>     DELETE FROM table
>       WHERE col3 LIKE '11%' AND col1 < 1000 LIMIT 200

12. UPDATE syntax

>     UPDATE table_name
>       SET col_name1 = expression1, col_name2 = expression2, ....
>       [ WHERE expression ]
>       [ LIMIT limit_amount ]

> Updates information in a table. The `SET` clause is a list of assignments that describe how the columns of the data matched by the `WHERE` clause are to be updated. Any columns not assigned in the `SET` clause are left unchanged. Examples of using `UPDATE`;

>     UPDATE Employee
>       SET salary = salary * 1.25
>       WHERE name = 'Bob'

>     UPDATE Order
>       SET id = id + 3, part = CONCAT(part, '-00')
>       WHERE part LIKE 'PO-%'
>       LIMIT 10

13. SELECT syntax

>     SELECT [ DISTINCT | ALL ]
>       column_expression1, column_expression2, ....
>       [ FROM from_clause ]
>       [ WHERE where_expression ]
>       [ GROUP BY expression1, expression2, .... ]
>       [ HAVING having_expression ]
>       [ ORDER BY order_column_expr1, order_column_expr2, .... ]

>     column_expression ::= expression [ AS ] [ column_alias ]

>     from_clause ::= select_table1, select_table2, ...
>     from_clause ::= select_table1 LEFT [OUTER] JOIN select_table2 ON expr  ...
>     from_clause ::= select_table1 RIGHT [OUTER] JOIN select_table2 ON expr  ...
>     from_clause ::= select_table1 [INNER] JOIN select_table2  ...

>     select_table ::= table_name [ AS ] [ table_alias ]
>     select_table ::= ( sub_select_statement ) [ AS ] [ table_alias ]

>     order_column_expr ::= expression [ ASC | DESC ]

> The `SELECT` statement is used to form queries for extracting information out of the database. The following example query will return the number, quantity and price of all orders for more than 5 items sorted in descending order by order number. In addition it rounds the order price to two decimal places and applies a dollar ($) sign to the output.

>       SELECT number, quantity, CONCAT('$', ROUND(price, 2))
>         FROM Order
>        WHERE quantity > 5
>     ORDER BY number DESC

> The `ORDER BY` and `GROUP BY` clause may refer to a column, a column alias, or an expression. The `HAVING` clause is evaluated after the grouping and aggregate columns have been resolved.

> For examples of using `SELECT` with aggregate functions see the 'Internal SQL Functions' section.

14. COMMIT and ROLLBACK syntax

>     COMMIT
>     ROLLBACK

> Transactional operations for closing a transaction and either committing all the changes made or rolling back and disposing all changes. `COMMIT` may cause a concurrent transaction conflict exception to be thrown. If a conflict is detected the transaction is automatically rolled back. See the 'Transactions' section of the documentation for further details of how Mckoi handles transactions.

15. CREATE USER, ALTER USER and DROP USER syntax

>     CREATE USER username SET PASSWORD 'password'
>     [ SET GROUPS groups_list ]
>     [ SET ACCOUNT ( LOCK | UNLOCK ) ]

>     ALTER USER username SET PASSWORD 'password'
>     [ SET GROUPS groups_list ]
>     [ SET ACCOUNT ( LOCK | UNLOCK ) ]

>     DROP USER username

> These are user management commands for creating/altering and dropping users in the system. Only members of the 'secure access' group are permitted to perform these operations, which includes the administrator user that is setup when the Mckoi database is created.

> The following example creates a user called 'harry' with the password 'cat';

>       CREATE USER harry SET PASSWORD 'cat'

> See the 'JDBC Driver' section for documentation on connecting to a database using a username and password to connect to a database.

16. GRANT/REVOKE syntax

>     GRANT privileges ON database_object TO ( PUBLIC | user_list )
>     [ WITH GRANT OPTION ]

>     REVOKE [ GRANT OPTION FOR ] privileges ON database_object
>     FROM ( PUBLIC | user_list )

>     privileges ::= priv_item1, priv_item2, ...

>     priv_item ::= ALL [ PRIVILEGES ] | SELECT | INSERT | UPDATE |
>                   DELETE | REFERENCES | USAGE

>     database_object ::= [ TABLE ] table_name | SCHEMA schema_name

>     user_list ::= PUBLIC | username1, username2, ...

> Grants or revokes types of access on a table or view to a user. When a table or view is created the system gives full grant options to the user that created the object. The user is given the option to grant other users selective access to the object through the `GRANT` and `REVOKE` syntax. For example, the follow statement shows how a user would grant user 'toby' permission to `SELECT` from a table called `MyTable`;

>       GRANT SELECT ON TABLE MyTable TO toby

> The `GRANT` command allows granting all users access to an object. The following statement makes `MyTable` globally readable;

>       GRANT SELECT ON TABLE MyTable TO PUBLIC

> If you wish to give a user the option of granting a privilege to another user, add `WITH GRANT OPTION` to the `GRANT` statement.

17. SET syntax

>     SET variable = expression
>     SET AUTO COMMIT ( ON | OFF )
>     SET TRANSACTION ISOLATION LEVEL ( SERIALIZABLE )
>     SET SCHEMA schema_name

> Makes a change to the state of the connection. `SET AUTO COMMIT` is used to switch transaction 'auto commit mode' on or off. When auto commit mode is on the engine commits after every statement. By default, a connection starts with auto commit mode switched on. `SET TRANSACTION ISOLATION LEVEL` currently only supports the `SERIALIZABLE` isolation level. See the 'Transactions' section of the documentation for details of how Mckoi handles transactions.

> `SET SCHEMA` is used to change the default schema of a connection.

18. DESCRIBE syntax

>     DESCRIBE table_name

> This command provides information about the columns of the table. It shows the column names, the type / size and scale (if applicable) and other useful information.

19. SHOW syntax

>     SHOW engine_variable

>     engine_variable ::= TABLES | SCHEMA | STATUS | CONNECTIONS

> Shows internal information about the database system. `SHOW TABLES` returns a list of tables in the database. `SHOW STATUS` returns debugging and statistical information about the internal state of the database engine. `SHOW CONNECTIONS` returns a snapshot of the current connections on the database. `SHOW SCHEMA` lists all the schema defined.

20. SHUTDOWN syntax

>     SHUTDOWN

> Shuts down the database. If the database is running as a server the database shuts down cleanly and the process is stopped. If the database is embedded in a Java application it is cleanly put into a shut down state.

> Only a user with the correct grants may successfully execute this command.

Last Updated: Mon Nov 18 14:36:45 PST 2002 Mckoi SQL Database Copyright © 2000, 2001, 2002 Diehl and Associates, Inc. All rights reserved.