Summary of Oracle DDL, DML, DCL, and TCL

Source: Internet
Author: User
Tags field table savepoint

Oracle DDL, DML, DCL, and TCL summarize the impetuousness of some time ago. I always think this will happen too. But when I wake up, I find that I don't know anything, and I don't even know the basic concepts, let's start from scratch and study for a period of time. Oracle learning can be implemented and maintained independently. Of course, optimization is a long time-consuming experience and requires more experience. contains some online materials. In general, SQL statements are commonly used for addition, deletion, modification, query, and alter. in oracle, commit and revoke are commonly used. These are very confusing for me and I hope I can continue to work hard. DDL Data Definition Language (DDL) statements are used to define the database structure or schema. some examples: CREATE-to create objects in the databaseALTER-alters the structure of the databaseDROP-delete objects from the databaseTRUNCATE-remove all records from a table, including all spaces allocated for the records are removedCOMMENT-add comments to the data dictionaryRENAME-rename An objectDML Data Manipulation Language (DML) statements are used for managing data within schema objects. some examples: SELECT-retrieve data from the databaseINSERT-insert data into a tableUPDATE-updates existing data within a tableDELETE-deletes all records from a table, the space for the records remainMERGE-UPSERT operation (insert or update) CALL-call a PL/SQL or Jav A subprogramEXPLAIN PLAN-explain access path to dataLOCK TABLE-control concurrencyDCL Data Control Language (DCL) statements. some examples: GRANT-gives user's access privileges to databaserevke-withdraw access privileges given with the GRANT commandTCLTransaction Control (TCL) statements are used to manage the changes made by DML statements. it allows statements to be grouped together int O logical transactions. COMMIT-save work doneSAVEPOINT-identify a point in a transaction to which you can later roll backROLLBACK-restore database to original since the last commitset transaction-Change transaction options like isolation level and what rollback segment to use, the following is an easy-to-understand Method for most people. For the SQL language, there are two components: DML (data manipulation language): They are SELECT, UPDATE, INSERT, and DELETE, just like their names, these four commands are used to operate the data in the database. DDL (data definition language): DDL is more than DML. The main Commands include CREATE, ALTER, and DROP. DDL is mainly used to define or change the database structure and data type, most of the initialization tasks such as links and constraints between tables are used when creating tables. There are four types of SQL languages: DQL, DML, DDL, and DCL. 1. Data Query Language DQL the basic structure of DQL is a query block consisting of the SELECT clause, FROM clause, and WHERE clause: SELECT <field table> FROM <Table or view Name> WHERE <query condition> 2 data manipulation language DML has three forms: 1) insert: INSERT2) Update: UPDATE3) DELETE: DELETE 3 Data Definition Language DDL is used to create various objects in the database-tables, views, indexes, synonyms, clustering, etc: create table/VIEW/INDEX/SYN/CLUSTER 4 Data Control Language DCL is used to grant or revoke some privileges to access the database, and control the time and Effect of database operations and transactions, monitors databases. Example: 1) GRANT: authorization. 2) ROLLBACK [WORK] TO [SAVEPOINT]: roll back TO a certain point. ROLLBACK-the ROLLBACK command returns the database status to the last submitted status. The format is SQL> ROLLBACK; 3) COMMIT [WORK]: Submit. During database insertion, deletion, and modification, transactions are only completed when they are committed to the database. Before a transaction is committed, only the person who operates the database can view what is done, and others can only see it after the last commit. You can submit data explicitly, implicitly, or automatically. The three types are described below. (1) The COMMIT directly completed by using the COMMIT command is an explicit COMMIT. The format is: SQL> COMMIT; (2) Implicit COMMIT: Indirect COMMIT using SQL commands is implicit COMMIT. These commands are: ALTER, AUDIT, COMMENT, CONNECT, CREATE, DISCONNECT, DROP, EXIT, GRANT, NOAUDIT, QUIT, REVOKE, RENAME. (3) automatic submission if AUTOCOMMIT is set to ON, the system automatically submits the insert, modify, and delete statements after they are executed. The format is: SQL> SET AUTOCOMMIT ON; DDL, DML, DCL. What are the differences between the difference between DDL, DML and DCL commands? What are the differences between DDL, DML, and DCL commands? (DDL is a Data Definition Language, for example, :) DDL is Data Definition Language statements. some examples: CREATE-to create objects in the database (CREATE an object in the database) ALTER-alters the structure of the database (modify the database structure) DROP-delete objects from the database (delete objects from the database)
] TRUNCATE-remove all records from a table, including all spaces allocated for the records are removed (remove all records from a table, including all space allocated for records) COMMENT-add comments to the data dictionary (add remarks to the data dictionary) GRANT-gives user's access privileges to database (GRANT access permissions to Users) REVOKE-withdraw access privileges given with the GRANT command (revoking the access permission granted by the GRANT command) DML is Data Manipulation Language statements. some examples :( DML is the data operation language, for example: SELECT-retrieve data from the database (return data that meets certain requirements from the specified database) INSERT-insert data into a table (INSERT data into a table) UPDATE-updates existing data within a table (UPDATE existing data in the table) DELETE-deletes all records from a table, the space for the records remain (delete all records in the table, but keep the space occupied by the records) CALL-call a PL/SQL or Java subprogram (CALL a PL/SQL or JAVA subroutine) explain plan-explain access path to data (the access path for interpreting analysis Data) lock table-control concurrency (Control concurrency) DCL is data control Language statements. some examples :( DCL is the data control language, for example: COMMIT-save work done (save completed work) SAVEPOINT-identify a point in a transaction to which you can later roll back (mark a point in the transaction so that you can roll back here later) ROLLBACK-restore database to original since the last COMMIT (re-Save the database after the last COMMIT) set transaction-Change transaction options like what rollback segment to use (Change TRANSACTION options) Author yujin2010good

Related Article

Contact Us

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: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.