Detailed description of main SQL statements
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, and grant the access permission.
Unavailable
Begin work and other transaction statements (can be changed by the START database Statement ).
You can select the log files of the current database.
For example, select dirpath form variables Ables where tabtype = "L ";
Example: Create Databse customerdb with log in "/usr/John/log/custo
Mer. Log ";
2. Database Databse-name [exclusive]
Select a database.
Database_name: Database Name.
Exclusive: exclusive.
Access the database under the specified directory in the current directory and dbpath.
Use this statement.
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;
· Drop database database_name
Deletes a specified database.
Database_name: Database Name.
The user is the owner of DBA or all tables. All files are deleted, but the database directory is not included. deletion is not allowed.
Except the current database (the current database must be closed first). This statement cannot be used during transaction processing.
The database cannot be restored after the rollback work is completed.
Example: drop databse customerdb;
· CREATE [TEMP] TABLE table-name (column_name datatype [not null],
...)
[IN "pathname"]
Create a table or 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 temporary table to be created. The table name must be unique and the field must be unique. users with the CONNECT permission
Temporary tables can be created. The created tables can be accessed by CONNECT users by default, but cannot be accessed by 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;
· ALTER TABLE
Alter table table-name
{ADD (newcol_name newcol_type [BEFORE oldcol_name],…) | DROP (oldco
L_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. However, if a word fails
If the original field is NULL, The MODIFY is not null, unless all NULL fields contain
The user of ALTER is the table owner or has the DBA permission or is authorized.
Do not use this statement during processing.
Example: alter table user
Add (c6 char (20) before c5 );
· Rename table oldname TO newname: Modify the TABLE name.
Oldname: original name.
Newname: new name.
The RENAME user is the table owner or has DBA permissions or is authorized.
Use this statement.
Example: rename user to bbb;
· Drop table table-name
Delete a table.
Table-name: The table name.
Deleting a table means deleting all the data in the table, indexing on each field, granting permissions to the table, and views. Users
You cannot delete any system directory tables. The statement user is the table owner or has the DBA permission.
Do not use this statement during the process.
· 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.
Example: rename column user. c6 to c7;
· Create view view-name column-list
Create view view-name column-list AS select_statement [WITH CHECK OPT
ION]
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 will extend the field names of the base TABLE, and use virtual fields such as expressions
The field name must be specified to identify the field name when the field name is repeated between multiple tables. If some fields in the Visual View are named, all words
The fields must be named. The data type in the view is extended by the Data Type in the base table. The virtual field is equivalent to the expression;
You cannot use the order by and UNION clauses. You must have the SELECT permission on all fields in the view. Transactions
This statement is used during processing. Even if the transaction is rolled back, the view is created and cannot be restored.
Example: create view v_user as select * from user where c1 = "B1 ";
· Drop view view-name
Delete a view.
View-name: view name.
Users can delete their own views. The descendant views of the views are also deleted. Do not use them during transaction processing.
This statement.
Example: drop view v_user;
· 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 the statement is executed, the table state is set to EXCLUSIVE. The composite index can contain up to 8 fields, all words
The length and length of the segment cannot exceed 120 bytes. This statement is used during transaction processing, even if the transaction is rolled back
It will be created and cannot be restored.
Example: create cluster index ix_user on user (c5 );
· Alter index index-name TO [NOT] CLUSTER
Modify the index properties.
Index-Name: Index name.
To [not] Cluster: Remove or add the cluster attribute.
When a statement is executed, the table state is set to exclusive. This statement is used during transaction processing, even if
If the transaction is rolled back, the index nature will change and cannot be restored.
For example, alter index ix_user to not cluster;
· Drop Index-name
Delete an index.
Index-Name: Index name.
The statement user is the index owner or has DBA permissions. Do not use this language during transaction processing.
Statement. Otherwise, the transaction cannot be recovered.
Example: drop index ix_user;
· Create synonym for table-name
Create a synonym name.
Synonym: Synonymous name
Table-Name: Table Name
The creator of the database can use the synonymous name. Users without the synonymous name permission cannot use the synonymous name;
The synonymous name cannot be the same as the table name. Do not use this statement during transaction processing. Example: Create synonym user_alias for user;
· Drop synonym Synonym
Delete A synonym name.
Synonym: Synonymous name
You can delete your own synonymous names. Do not use this statement during transaction processing; otherwise, it cannot be restored.
Example: drop synonym user_alias;
· Update statistics [for table table-name]
Updates the database statistics.
Table-name: table name
This statement only applies to the current database; improves query efficiency; only when this statement is executed changes the statistics
Data.
Example: update statistics for table user;
18. GRANT {DBA | RESOURCE | CONNECT} TO {PUBLIC | user-list}
Authorization command.
PUBLIC | user-list: All or specified users.
Only one of the three permissions exists. Do not execute the GRANT statement during transaction processing.
Example: grant resource to pulbic;
GRANT tab-privilege ON table-name TO {PUBLIC | user-list} [WITH GRANT O
PTION]
Grant table-level permissions.
Tab-privilege: Table-level permission.
Table-name: The table name. PUBLIC | user-list: All or specified users.
[With grant option]: indicates whether the authorized user has the permission to perform secondary authorization.
Users can GRANT permissions in self-built expressions or tables authorized by [with grant option ].
The higher the priority of a given permission.
Example: grant update (c1, c6) on user to dick with grant option;
Appendix (INFORMIX permissions)
(1) database permissions (control access to the database and create and Delete tables in the database)
DBA permission: All rights: Modify system tables, create and Delete tables and indexes, and add and restore tables
Data, and grant other user database permissions;
RESOURCE permission: allows you to access data in database tables, create permanent tables, and
Index.
CONNECT permission: only allows access to data in database tables, creating and deleting views and temporary
Table.
(2) Table-level permissions (permissions for table creation, modification, retrieval, and update)
ALTER: Change permissions
DELETE: DELETE permission
INDEX: INDEX permission
INSERT: INSERT permission
SELECT [(cols)]: Specifies the query permission for a field or all fields. If this parameter is not specified, all fields are queried by default.
Field.
UPDATE [(cols)]: Specifies the UPDATE permission for a field or all fields. If this parameter is not specified, all fields are updated by default.
Field.
ALL [PRIVILEGES]: ALL the above table-level Permissions
19. REVOKE {DBA | RESOURCE | CONNECT} FROM {PUBLIC | user-list} command.
PUBLIC | user-list: All or specified users.
Only one of the three permissions exists. Do not execute the GRANT statement during transaction processing.
Example: revoke resource from john;
REVOKE tab-privilege ON table-name FROM {PUBLIC | user-list}
Table-level permissions.
Tab-privilege: Table-level permission.
Table-name: The table name.
PUBLIC | user-list: All or specified users.
[With grant option]: indicates whether the authorized user has the permission to perform secondary authorization.
You can only revoke the table-level access permissions granted to other users by yourself.
When the SELECT and UPDATE operations are canceled, the SELECT and UPDATE operations for all fields in the table are canceled.
Permission.
Example; revoke update on user from dick;
· Lock table table-name IN {SHARE | EXCLUSIVE} MODE
Record-level locking and table-level locking or file locking.
Table-Name: The table name.
Share: allows reading table data, but does not allow any modification
Exclusive: Prohibit any other form of Table Access
The table can only be set once at a time. During transaction processing, lock is executed immediately after begin work.
Table to replace record-level locking. The commit work and rollback work statements are canceled.
All table locks; if no transaction is processed, the lock is kept until the user exits or unlock is executed.
Example: Lock table user in exclusive mode; · unlock table-name
Cancels record-level locks, table-level locks, or file locks.
Table-Name: The table name.
Example: Unlock user;
· Set lock mode to [not] Wait
Changes the lock status.
To [not]: Waiting for unlocking. It may be deadlocked or not waiting, and an error message is prompted, indicating that this record is
Lock, default value.
When you access an exclusive record, an error is returned.
· Start Databse db_name [with log in "pathname"]
Start transaction processing.
"Pathname": the log file for transaction processing.
Before executing this statement, you must close the current database.
Example; clost database;
Start Databse customer with log in "/usr/John/log/customer. log ";
· Begin work
Start the transaction. Example: Begin work;
· Commit work
Commit (ENDS normally) the transaction. Example: commit work;
· Rollback work rollback (abnormal termination) transactions. Example: rollback work;
· Select
Select select_list from tab_name | view_name
Where condition
Group by column_name
Having Condition
Order by column_list
Into temp table_name
Query statement.
Select_list: select a table or *
Tab_name: Table Name
View_name: View name.
Condition: Query condition. BETWEEN, IN, LIKE, is null, LIKE, And MAT can be used.
CHES, NOT,
AND, OR, = ,! = Or <>,>,> =, <=, <, ALL, ANY, SOME
Column_name: group field name
Condition: group aggregation condition
Column_list: List of sorting fields. The default value is ASC. You can specify DSC. When sorting, the NULL value is smaller
Zero value.
Table_name: temporary table name
Example: omitted
Appendix (common functions)
(1) set functions: count (*),
Sum (data item/expression), avg (data item/expression), max (data item/expression), min (data item/expression/
Expression)
Count (distinct data item/expression), sum (distinct data item/expression), avg (distinct data item/
Expression)
(2) algebraic functions and trigonometric Functions
HEX (data item/expression), ROUND (data item/expression), TRUNC (data item/expression ),
TAN (data item/expression), ABS (data item/expression), MOD (divisor, divisor)
(3) statistical functions
Standard deviation, stdev (), variance, variance (), range, rang ()
(4) Time Functions
DAY (date/time expression): returns the number type
MONTH (date/time expression): returns an integer.
WEEKDAY (date/time expression): 0 & #0; & #0; 6, 0 Sunday, 1 Monday; return an integer
YEAR (date/time expression), returns an integer
DATE (non-DATE expression): returns the DATE type.
EXTEND (date/time expression, [first to last]): returns the specified integer
MDY (month, day, year): return date type
CURRENT: return date type
(5) Time Functions
ROUND (), rounding. For example: ROUND (10.95, position) position before rounding
Location
TRUNC (), intercept. For example, TRUNC (10.95, 0) position
INFORMIX temporary tables are automatically canceled in the following circumstances:
A. Exit the database access tool (such as DBACCESS) B. End the SQL call (DISCONNECT)
C. Issue the cancel TABLE statement
D. Exit the program
28. INSERT
Insert into view_name | table_name [(column_list)] VALUES (value_list)
Or insert into view_name | table_name [(column_list)] select_statement
Insert data
View_name | table_name: View name or table name
Column_list: data item list.
Value_list: Value List
Select_statement: query statement.
Example: omitted
29. delete from view_name | table_name WHERE search-conditions
Delete statement.
View_name | table_name: View name or table name
Search-conditions; Delete conditions
Example: omitted
30. UPDATE
UPDATE view_name | table_name SET column_1 = value_1ist WHERE search_c
Onditions
Or UPDATE view_name | table_name SET column_1 | * = value_1ist WHERE sear
Ch_conditions
Update Data statements. View_name | table_name: Table name or view chart name
Value_1ist: Field Value
Search_conditions: Condition for updating data
Example: omitted
31. check table table-name
Check the index statement.
The statement user is the table owner or has DBA permissions. You cannot use this statement for retriable.
Example: omitted
32. repair table table-name
Fix the index.
The statement user is the table owner or has DBA permissions. You cannot use this statement for retriable.
Example: omitted
33. load from "file-name" insert into table_name [(column_name
[,…])]
Add text data to the table.
For example, load form into aa.txt "insert into user;
34. Unload to "pathname"
Unload data from the table as text.
Example: unload to extract aa.txt "select * from user;
35. Info
Query system information.
Info tables: Obtain the name of the table in the current database.
Info Columns for table_name: Specifies the field information of the table.
Info indexes for table_name: Specify the index information of the table. Info [access | privileges] For table_name: Specifies the access permission for the table.
Info status for table_name: Specifies the table status information.
Example: info tables;
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.