Informix SQL statement (1)

1. create database database_name [with log in "pathname"] CREATE a DATABASE.

Database_name: Database Name.

"Pathname": the log file for transaction processing.

Create a database_name.dbs directory. The access permission is set by GRANT and cannot be used without a log file.

Begin work and other transaction statements can be changed using the start database Statement ).

You can select the log files of the current database.

For example, select dirpath form variables Ables where tabtype = "L ";

For example, create databse customerdb with log in "/usr/john/log/customer. log ";

2. Select DATABASE databse-name [EXCLUSIVE.

Database_name: Database Name.

EXCLUSIVE: EXCLUSIVE.

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

For example, dtabase customerdb;

3. CLOSE DATABASE

Close the current database.

Database_name: Database Name.

After this statement, only the following statements are valid:

Create database; drop databse; rollforward database;

You must use this statement before deleting a database.

For example, close database;

4. drop database database_name to delete the specified DATABASE.

Database_name: Database Name.

The user is the owner of DBA or all tables. Deleting all files, excluding the database directory, does not allow deleting the current database. You must first disable the current database ); this statement cannot be used during transaction processing, nor can the database be restored through rollback work.

Example: drop databse customerdb;

5. CREATE [TEMP] TABLE table-name (column_name datatype [not null],…) [IN "pathname"] creates a table or a temporary table.

Table-name: The table name.

Column_name: field name.

Data-type: field data type.

Path-name: Specifies the table storage location

TEMP is used to specify the creation of a temporary table. The table name must be unique and the field must be unique. users with the CONNECT permission can create a temporary table. The created table can be accessed by the CONNECT user by default, but cannot be 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 table-name

{ADD (newcol_name newcol_type [BEFORE oldcol_name],…) | DROP (oldcol_name ,...) | MODIFY (oldcol_name newcol_type [not null],… )},... Modify the table structure.

Table-name: The table name.

Newcol_name: new field name

Newcol_type: New Field Type

Oldcol_name: old field name

You can use one or more ADD clauses, DROP clauses, and MODIFY clauses. If a Word fails, the operation is aborted. The original field is NULL and the MODIFY is not null, unless all NULL fields are not empty, the statement can be used. The ALTER user is the table owner or has DBA permission or is authorized. Do not use this statement during transaction processing.

Example: alter table user

Add (c6 char (20) before c5 );

7. rename table oldname TO newname TO modify the TABLE name.

Oldname: original name.

Newname: new name.

The RENAME user is the table owner or has DBA permissions or is authorized. Do not use this statement during transaction processing.

Example: rename user to bbb;

8. drop table table-name to delete a TABLE.

Table-name: The table name.

Deleting a table means to delete all data in the table, indexes on various fields, table permissions, views, etc. You cannot delete any system directory tables. The statement user is the table owner or has DBA permissions, do not use this statement during transaction processing.

9. rename column table. oldcolumn, TO newcolumn modify the field name.

Table. oldcolumn: table Name and original field name

Newcolumn: name of the new field.

The statement user is the table owner or a user with DBA or ALTER permissions. Do not use this statement during transaction processing.

Example: rename column user. c6 to c7;

10. create view view-name column-list create view-name column-list AS select_statement [with check option] CREATE a view.

View-name: view name.

Column-list: Field list.

Select_statement: SELECT statement.

The following statements do not use views: alter table, drop index, alter index, lock table, create index, and rename table. The view uses the field name of the base TABLE, duplicate names of fields such as expressions and multi-table fields must specify the names of their fields. If some fields in the visual graph are named, all fields must be named. The data type in the view is extended by the Data Type in the base table, virtual fields refer to expressions. order by and UNION clauses cannot be used. SELECT permission is required for all fields in the view. This statement is used during transaction processing, even if the transaction is rolled back, the view will also be created and cannot be restored.

Example: create view v_user as select * from user where c1 = "B1 ";

11. drop view view-name Delete VIEW.

View-name: view name.

You can delete the created view. The descendant view of the view is also deleted. Do not use this statement during transaction processing.

Example: drop view v_user;

12. create index create [UNIQUE/DISTINCT] [CLUSTER] INDEX index_name ON table_name

([Column_name ASC/DESC],…) Create an index.

Index_name: Index name.

Table_name: Table name.

Column_name: field name.

UNIQUE/DISTINCT: UNIQUE index.

CLUSTER: sort the table's physical storage order by index.

ASC/DESC: ascending or descending by default.

When a statement is executed, the table state is set to EXCLUSIVE. A composite index can contain up to 8 fields. The length of all fields and must not exceed 120 bytes. This statement is used during transaction processing, even if the transaction is rolled back, the index is created and cannot be restored.

Example: create cluster index ix_user on user (c5 );