Sql Syntex

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. CREATE SEQUENCE syntax
8. DROP SEQUENCE syntax
9. COMPACT TABLE syntax
10. CREATE SCHEMA syntax
11. DROP SCHEMA syntax
12. INSERT syntax
13. DELETE syntax
14. UPDATE syntax
15. SELECT syntax
16. COMMIT and ROLLBACK syntax
17. CREATE USER, ALTER USER and DROP USER syntax
18. GRANT/REVOKE syntax
19. SET syntax
20. DESCRIBE syntax
21. SHOW syntax
22. 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 a 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 < col_name1 =" expression1," col_name2 =" expression2," col1 =" 10," col2 =" 4" col3 =" CONCAT(col1," col_name1 =" expression1," col_name2 =" expression2," salary =" salary" name =" 'Bob'" id =" id" part =" CONCAT(part,"> 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.



16. 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.



17. 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.



18. 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.



19. 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.



20. 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.



21. 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.



22. 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.