There are many versions of the SQL statement classification, 2 main categories are reproduced below:
1.Oracle Classification of SQL :
Data-manipulation language statements [data manipulation LANGUAGE,DML]
- Querying data from one or more tables or views (select); Get operation [Fetch] is scrollable [scrollable] (see "Scrollable cursor")
- Add a new row of data to a table or view (INSERT)
- To modify a column value (UPDATE) for an existing row of data in a table or view
- Insert or UPDATE data rows (MERGE) for tables and views based on judging criteria
- Delete a data row (delete) from a table or view
- Query execution plan for SQL statements [execution Plan] (EXPLAIN plan)
- Lock [lock] on a table or view to temporarily restrict access to this object by other users (lock table)
Data definition language sentence [data definition language,ddl]
- Create, modify, remove schema objects and other database structures, including the database itself and the database user (Create,alter,drop)
- Modify schema Object name (RENAME)
- Removes all data from the schema object, but does not remove the object structure (TRUNCATE)
- Grant or revoke permissions and roles (Grant,revoke)
- Turn auditing options on or off (Audit,noaudit)
- Add a comment to the data dictionary (COMMENT)
Transaction control statements [transaction control statement]
- To permanently save changes to the data in a transaction to the database (COMMIT)
- Restore transaction modifications to data that can be restored to the beginning of a transaction or to any savepoint [savepoint] (ROLLBACK)
- Set the savepoint to identify the rollback position (savepoint)
- Set the properties of a transaction (set TRANSACTION)
Session control statements [session controls statement]
- Perform specific actions to modify the current session, such as enabling or disabling SQL tracing [SQL Trace Facility] (ALTER session)
- Enable or disable roles for the current session [role] (that is, a set of permissions) (set role)
- System control statements [statement]
- The ALTER system is the only control statement.
- Embed SQL statements [Embedded SQL statement]
- Define, assign, and release cursors [cursor] (DECLARE cursor,open,close)
- Select an Oracle database and connect (DECLARE database,connect)
- Assigning variable names (DECLARE STATEMENT)
- Initialization descriptor [Descriptor] (DESCRIBE)
- Setting how to handle errors and warnings (whenever)
- Parsing and executing SQL statements (Prepare,execute,execute IMMEDIATE)
- Retrieving data from the database (FETCH)
Oracle's language is divided into four categories, which are (data Definition language DDL), (Data Manipulation language DML), (Data Control Language DCL), and (Transaction control Language TCL)
Ddl
Data Definition Language (DDL) statements is used to define the database structure or schema. Some Examples:
Create-to create objects in the database
Alter-alters the structure of the database
Drop-delete objects from the database
Truncate-remove all records from a table, including all spaces allocated for the records is removed
Comment-add comments to the data dictionary
Rename-rename an Object
Dml
Data Manipulation Language (DML) statements is used for managing data within schema objects. Some Examples:
Select-retrieve data from the A database
Insert-insert data into a table
Update-updates existing data within a table
Delete-deletes all records from a table, the space for the records remain
Merge-upsert operation (insert or UPDATE)
Call-call a PL/SQL or Java Subprogram
EXPLAIN plan-explain access Path to data
LOCK Table-control concurrency
DCL
Data Control Language (DCL) statements. Some Examples:
Grant-gives user ' s access privileges to database
Revoke-withdraw access privileges given with the GRANT command
Tcl
Transaction Control (TCL) statements is used to manage the changes made by DML statements. It allows statements to being grouped together into logical transactions.
Commit-save work done
Savepoint-identify a point in a transaction to which can later roll back
Rollback-restore database to original since the last COMMIT
SET Transaction-change TRANSACTION options like isolation level and what rollback segment to use
2. Other data mainstreaming methods:
Data Query Language DQL
The DQL basic structure of the data query language is determined by the SELECT clause, the FROM clause, where
clause consists of a query block:
SELECT < field Name table >
From < table or view name >
WHERE < query conditions >
Data Manipulation language
There are three main forms of data manipulation language DML:
1) Inserting: Insert
2) Updates: Update
3) Remove: Delete
Data Definition Language DDL
The data definition language DDL is used to create various objects in the database-----tables, views,
indexes, synonyms, clusters, etc.:
CREATE Table/view/index/syn/cluster
| | | | |
Table View index synonym cluster
Data Control Language DCL
Data Control Language DCL is used to grant or reclaim some kind of privilege to access a database and to control
The time and effect of database manipulation transaction, and the monitoring of database. Such as:
1) Grant: authorization.
2) ROLLBACK [work] to [savepoint]: fallback to a point.
Roll Back---ROLLBACK
The rollback command brings the database state back to the last committed state. The format is:
sql>rollback;
3) Commit [work]: Submit.
When a database is inserted, deleted, and modified, only when the transaction is committed to the data
Only when the library is complete. Before a transaction is committed, only the person who operates the database will be able to see
To do things, others can only be seen after the final submission is completed.
There are three types of submission data: explicit commit, implicit commit, and auto-commit. The following points
Do not describe these three types.
(1) Explicit submission
Commits that are completed directly with the commit command are explicitly committed. The format is:
sql>commit;
(2) Implicit submission
Commits that are indirectly completed with SQL commands are implicitly committed. These commands are:
Alter,audit,comment,connect,create,disconnect,drop,
Exit,grant,noaudit,quit,revoke,rename.
(3) Automatic submission
If the autocommit is set to ON, after the insert, modify, DELETE statement executes,
The system will commit automatically, which is automatic submission. The format is:
Sql>set autocommit on;
SQL Language classification