INFORMIX-4GL utilities (2) (1)

Informix-SQLVersion6.05.UD1 Application

1. Create and delete databases, base tables, and indexes

1. Syntax conventions:
<...> Indicates a required parameter.
{...} Indicates required items.
[...] Indicates the option
(...) Indicates a part of the statement.
| Indicates selecting one or more
Capital [A-Z] indicates reserved words for Informix
Lowercase [a-z] indicates the database name, table name, field name, and variable name of Informix.
2. Database ):
1. Create a database:
CREATEDATABASE [WITHBUFFEREDLOG]
Syntax-keywords → syntax keyword → database name → whether to include database logs
This DB is also enabled when you create a DB.
②. Database naming rules:
(I). The database name cannot exceed 10 characters.
(Ii). It must start with a letter. The rest can be any combination of letters, numbers, and underscores.
(3) The database name must be unique in the same database server.
(Iv) The database name cannot be a reserved word of Informix.
3. Database base Table ):

1. Create a DBTable:
DATABASE → Database Name
CREATETABLE → Database table name
Field Name field type field description
Column0serial (1), # The first value of the ordered Integer type is 1.
Column1integer, # integer between-2147483647 and + 2147483647
Column2smallint, # integer between-32767 and + 32767
Column3floatdefa0.0.00notnull, # double-precision floating point number
Column4smallfloat, # Single-precision floating point number
Column5decimal (m, n), # numbers with customizable precision (m-n digits on the left and n digits on the right)
Column6money (m, n), # same currency type as decimal type)
Column7date, # date type
Column8datetimeyeartosecond, # date and time (1999-09-0709: 52: 52)
Column9char (n), # bytes type n indicates the character Length
Column10varchar (n), # struct (the last field has no comma)
Check (field name in (value 1, value 2, value 3) constraint name,
Primarykey (Field 1, Field 2, field n) constraint name # unique primary key, not empty
);
Data Type Field Length
Length defined in the CHAR statement
Length defined in VARCHAR statements
SMALLINT 2 bytes
INTEGER 4 bytes
SMALLFLOAT 4 bytes
FLOAT 8 bytes
Four SERIAL bytes
DATE 4 bytes
DATETIME 8 bytes (YeartoSecond), the other is 4
DECIMAL (m, n) depends on the precision (m/2 + 1 rounded to the next integer)
MONEY (m, n) depends on precision (m/2 + 1 rounded to the next integer)
②. Base table naming rules:
(I). The table name cannot exceed 18 characters, which is similar to the database naming rules.
(Ii). It must start with a letter. The rest can be any combination of letters, numbers, and underscores.
(3) The table name must be unique in the same database.
(4) The table name cannot be a reserved word of Informix.
(V). The field name in the table cannot exceed 18 characters, and the liketable. * must ensure the uniqueness of the first eight characters.
(6) The Field Names in the table must be unique and case insensitive ).
4. Index ):
①. Index category:
(I) Single-field indexing: Improves query and classification efficiency.
(Ii) Multi-field index: accelerate the processing of several field categories.
Indexing only accelerates data classification.
2. Index rules:
(I). The index is not added until there are hundreds of records in the table.
(Ii). fields that are often used for retrieval and sorting.
(Iii). Used to connect tables and fields with high usage frequency.
(4) do not add indexes to fields that contain a large number of duplicate values. The same value in the index field must not appear more than 65536 times.
(V). You cannot add indexes to groups with more than 8 fields.
(6) You shall not index a field or a group of fields that exceed 120 bytes.
(Vii). The index naming rules are similar to the table name naming rules.
③. Create an index:
CREATE [UNIQUE | DISTINCT] INDEX → Index name
Secondary → unique index ON → Table name
(Column1 [DESC], column2,..., columnn)
Sort → descending Index

5. View ):
①. View definition: A view is a dynamic window on the database rather than a static state, and appears in the database as a database table. The authorization and addition, deletion, and modification operations are consistent with those of the base table.
②. View naming rules:
(I). The table name cannot exceed 18 characters. In addition, it is similar to the naming rules of the database base table.
(Ii). It must start with a letter. The rest can be any combination of letters, numbers, and underscores.
(3) The image name must be unique in the same database.
(Iv) The view name cannot be a reserved word of Informix.
(V). The field name in the view cannot exceed 18 characters. The first eight characters must be unique.
(6) The Field Names in the view must be unique and case-insensitive.
③ View task:
(I). Different data windows are provided for different users in the database. A single view can contain fields from different tables and cannot have the same name ).
(Ii) restrict access to certain sensitive data and only allow users to view aggregated information.
(3) allow users to organize data as they appear in the view when they modify, insert, and delete databases.
4. Differences between a view and a base table:
(I) views cannot be used to replace tables in the format description file.
(Ii). indexes cannot be created on The View.
(Iii). In some conditions, you cannot update or delete the data you see through the view.
(Iv). the table cannot be accessed through ROWID.
⑤ Create and delete views:
CREATEVIEW [Field 1,..., field n)]
View name
ASSELECT <标准select语句> → The UNION operator is not allowed.
[WITHCHECKOPTION] → whether the input and modification meet the SELECT statement Conditions
6. Delete databases, tables, indexes, and views

①. Delete a database: drop
②. Delete the table: droptable
③. Delete the index: dropindex
④. Delete view: dropview
You must disable the database before deleting it. Deleting the database also deletes all tables, indexes, and views of the database.