SQL statement details

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 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 ";

For example, create Databse customerdb with log in "/usr/John/log/customer. Log ";
 

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. 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; database; drop Databse; rollforward database;

You must use this statement before deleting a database.

For example, close database;
 

4. 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. The current database cannot be deleted (the current database must be closed first ); 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-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 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

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

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-name

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-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-name

Delete a 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 );
 

13. Alter 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 the transaction processing. Even if the transaction is rolled back, the index nature changes and cannot be restored.

For example, alter index ix_user to not cluster;
 

14. 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 statement during transaction processing; otherwise, the transaction cannot be recovered.

Example: drop index ix_user;
 

15. 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;

16. 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;

17. update statistics [for 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.

Example: update statistics for table user;