Informix SQL syntax details

1. CREATE DATABASE database_name [WITH LOG IN “pathname”]

Create a database.

database_name: The name of the database.

“pathname”: Transaction log file.

Create a database_name.dbs directory with access rights set by GRANT. You can't use transaction statements such as

BEGIN WORK without a log file (can be changed with the START DATABASE statement).

You can select the log file for the current database.

For example: select dirpath form systables where tabtype = “L”;

Example: create databse customerdb with log in “/usr/john/log/customer.log”;

2. DATABASE databse-name [EXCLUSIVE]

Select the database.

database_name: The name of the database.

EXCLUSIVE: Exclusive status.

Access the database in the current directory and the directory specified in the DBPATH. Do not use this statement during transaction processing.

Example: dtabase customerdb;

3. CLOSE DATABASE

Close the current database.

database_name: The name of the database.

After this statement, only the following statements are legal:

CREATE DATABASE; DATABASE; DROP DATABSE; ROLLFORWARD DATABASE;

This statement must be used before deleting the database.

Example: close database;

4. DROP DATABASE database_name

Delete the specified database.

database_name: The name of the database.

The user is the owner of the DBA or all tables; delete all files, but does not include the database directory; the current database is not allowed to be deleted (the current database must be closed first); this statement cannot be used during transaction processing. ROLLBACK WORK also cannot restore the database.

Example: drop databse customerdb;

5. CREATE [TEMP] TABLE table-name (column_name datatype [NOT NULL], …)

[IN “pathname&rdquo ;]

Create a table or temporary table.

table-name : Table name.

column_name: The name of the field.

data-type: Field data type.

path-name: Specify the storage location of the table

TEMP is used to specify the temporary table; the table name should be unique, the field should be unique; the user with CONNECT permission can create the temporary table; The table defaults to allow CONNECT user access, but not ALTER.

Example: create table user

( c0 serial not null, c1 char (10), c2 char(2), c3 smallint, c4 decimal(6,3),c5 date ) in “usr/john/customer.dbs/user;

6. ALTER TABLE

ALTER TABLE table-name{ADD (newcol_name newcol_type [BEFORE oldcol_name], …)