Oracle DML,DDL,DCL,DQL

2. DML (Data Manipulation language):

   Data Manipulation Language , the language that operates on data in the database, including command Select, UPDATE, INSERT, DELETE, call, EXPLAIN PLAN, LOCK TABLE. Details are as follows:

   1) Select-retrieve data from the A database query that is the query language dql, the basic structure is composed of a SELECT clause, the FROM clause, where clause consists of a block of queries:

   SELECT < field Name table >

   From < table or view name >

   WHERE < query conditions >

   2) Insert-insert data into a table add
   

   3) update-updates existing data within a table update

   4) Delete-deletes all records from a table, the space for the records remain deleted

   5) Call-call a PL/SQL or Java Subprogram

   6) EXPLAIN Plan-explain access path to data

   Each SQL statement executed by an Oracle RDBMS must be evaluated by the Oracle Optimizer. So, understanding how the optimizer chooses (search) the path and how the index is used is a great help in optimizing SQL statements. Explain can be used to quickly and easily find out how the query data in a given SQL statement is obtained, that is, the search path (which we commonly call access path). So that we choose the best query mode to achieve maximum optimization effect.

   7) lock Table-control concurrency lock for control of concurrency

3. DDL (data definition Language):

   data Definition Language , which is used to define and manage all objects in the SQL database, the DDL is mainly used in the initialization of defining or altering tables (table) structures, data types, links between tables and constraints, and most of them use when establishing tables, and the main commands are create, ALTER, Drop,truncate and so on.

   1) create-to create objects in the database
   

   2) Alter-alters The structure of the database modified

   3) Drop-delete objects from the database delete

   4) Truncate-remove all records from a table, including all spaces allocated for the records is removed

   TRUNCATE table [table Name].

   < /span>truncate table name is fast and efficient because:   the
   Truncate table is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources.   The
   DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log.  
   TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement. &NBSP
   for a table referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table, and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.  
   TRUNCATE table cannot be used for tables that participate in an indexed view.  
   5) Comment-add comments to the data dictionary comment  
   6) grant-gives User's access privileges to database Authorization  
   7) Revoke-withdraw access privileges given with the grant command to reclaim the permissions that have been granted  
   

5. DCL (Data Control Language):

   Data Control Language , used to set or change database user or role permissions, and control the time and effect of database manipulation transactions, monitoring the database, and so on. By default, only people such as Sysadmin,dbcreator,db_owner or db_securityadmin have the power to execute the DCL. The main commands are: GRANT, DENY, REVOKE, ROLLBACK, Commit, and so on.

1) commit-save work done submit

When inserting, deleting, and modifying a database, the transaction is completed only when it is committed to the database. Before a transaction is committed, only the person who operates the database has permission to see what is being done, and others can only see it after the final commit is completed. There are three types of submission data: explicit commit, implicit commit, and auto-commit. These three types are described below.

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

2) Savepoint-identify a point in a transaction-which can later roll back SavePoint

3) Rollback-restore database to original since the last COMMIT rollback

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;

4) Set Transaction-change TRANSACTION options like what rollback segment to use sets the characteristics of the current transaction, which has no effect on subsequent transactions.

Oracle DML,DDL,DCL,DQL