Informix SQL Introduction

Author: springwind I. Introduction: 1), INFORMIX main products are divided into three parts:;? Database server (database core); application development tools; network database interconnect products 2), the database server has two, the role is?? Provide data operation and management:?; SE: completely based unix operating system To develop applications necessary environmental and tools, there are two series: • 4GL: Informix Traditional base identical image development tool, five main products, they are I-SQL, 4GL RDS, 4GL C Compiler, 4GL ID and ESQL / C;?; Newra: Informix's newly available development tool with event-driven capabilities, object-oriented basic graphics interfaces. 4), INFORMIX network database interconnect products: Provide users with a variety of industrial standards for users, through which they can follow other databases that comply with these industrial standards.

Second, I-SQL Menu Description: Form can perform, create, modify, compile screen format, or delete existing screen format RUN Perform a existing screen format modify Modifying the specified screen format generate creates a specified table A default screen format compile Compile a set screen format New Creating a specific user screen format DROP Delete a already existing format file EXIT Exit FORM menu REPORT can be executed, create, modify, compile, or delete report Run execute a report Modify Modifying the specified report generate creates a default report New to create a user-specified report Compile to compile the specified report DROP Delete a specified existing report EXIT Exit Report menu Query-language See Utilities Description: DBACCESS User-Menu can execute, establish or modify the user-established menu RUN to execute the current database User menu modify allows the user to establish or modify the user menu EXIT Exit User-Menu menu Database Detact Description: DBACCESS TABLE See Utilities Description: DBACCESS EXIT Exit Utilities ISQLRF (or ISQL) three, common operation:?; create database isql == "Database ==" create == "Enter new database name?; create table isql ==" Table == "CREATE == "Enter the table name to build ==" add == "Enter the field name and type of the desired field, etc. ==" exit == "build-new-table?; Generate the default table format ISQL ==" form == " Generate = "Select Database ==" Enter Table Format Name == "Select Table ==" Table-Selection-Complete ---- "Compile Success ==" RUN 4. Write SQL statements using RDSQL (interactive query language) Method: ISQL == "Query Langavage ==" New == "Run?; Building a Database Create Database Database Name?; Construction Table (Format and Optional Data Type) Basic Format: CREATE TABLE Table (Field Name Type, Field Name Type, --- field name type); Detailed definition: create Table Name (Col1 Integer Not Null, Col2 Char (2) Check (col2 in ("? ? "," ? "), Col3 date default, --- field name type) Fragment by Round Robin (by Expression) in DB1, DB2 EXTENT SIZE 1000 Next Size 600 with no log (log; buffered log; log mode ANSI

LOCK MODE ROW (PAGE); Example 1: Create Table Orders (Order_num Integer Unique Constraint ORDER_NUM_UQ, Order_Date Date Not Null Constraint Order_Date_nn Default Today

; Example 2: CREATE TABLE CUSTOMER (CUSTOMER_NUM SERIAL, FNAME CHAR (20), PRIMARY KEY (CUSTOMER_NUM) CONSTRAINT PK_CNUM); CREATE TABLE ORDERS (ORDER_NUM SERIAL, CUSTOMER_NUM INTEGER, FOREIGN KEY (CUSTOMER_NUM) REFERENCES CUSTOMER CONSTRAINT FK_CNUM); Data Type: Character (CHAR (20)), VARCHAR (MINSI, MAXSIZE) value (six, Decimal (16, 2), smallint, integer, smallfloat, float, serial) Date type (Date, default format: mm / dd / Yyyy) ** To modify the default format of the Date type, just do the following description in the user's home directory: dBdate = y4md / export dbdate ** monetary (Money (8, 2)) other (eg: Interval, DateTime, etc.); establish an index (must establish a naming index) Create [unique] index index name ON table name (field name) Create [unique] index index name ON table name (field name) Disabled; set indexes index name enabled DROP INDEX index name?; Database Rights Connect: Access Database Resource: Allows users to establish or clear tables and index DBAs in the database: Database administrator, all permissions, general meter is DBA GRANT permission to Revoke Permissions from ?; Retrieval Database Database Database Name Select * From Name SELECT Field Name, Field Name FROM Table SELECT Field Name, Field Name - * / New Field Name from Name Sekect AVG (Field ), Min (), max (), sum (), count () from () from the name where clause = <>! = <=>> = Matches * 0 multiple? Single [] single character or a range / escape next character LIKE%: 0-- multiple _ single / escape next special character ORDER BY (DESC) Time Function: DAY MDY MONTH WEEKDAY YEAR DATE CURRENT Today: Length User Current Today is looking for in multiple tables, the SELECT nested field name can be written directly, if there is the same field name in different tables, the prefix table name SELECT table . Field name from FROM table 1, inserted data INSERT INTO table name in Table INTO [Column 1, Column 2. . . . ] VALUES [] Modify Update Table Name SET Field Name = WHER Delete Delete From Table Name WHERE Modified Structure: Example 1: ALTER TABLE Name DROP COL2, Modify Col1 Integer Not Null, Add Col4 Integer, Add Col3 Char (20) Before Col4; Example 2: Alter Table ORDERS Modify Next Size 300; Rename: Rename Column Table.