Explain DDL,DML,DCL in mysql with Examples

By | March 1, 2016

DDL (Data Definition language) statements are used to define the database structure or schema

following are the examples.

1.CREATE- To create objects in the database.
2.ALTER- alter the structure of the database.
3.DROP- delete objects from the database
4.TRUNCATE-remove all records from the table,including all the spaces allocated for the records are removed.
5.COMMENT-add comments to the data dictionary.
6.RENAME- rename an object

DML (Data Manipulation Langugage) -used for managing data in the schema objects.

Some of the examples are

1.SELECT-retrieve data from the database.
2.INSERT-insert data into a table.
3.UPDATE-updates existing data within a table.
4.DELETE-delete all records from the table,the spaces for the records remain
5.MERGE -upsert operation(insert or update)
6.CALL- call a PL/SQL or java subprogram
7.EXPLAIN PLAN-explain access path to data
8.LOCK TABLE-control concurrency

DCL (Data Control Langugage)- This commands are used for rights,permission and other control of the database system

1.GRANT-give users access privileges to the database
2.REVOKE-withdraw access privileges given with the GRANT command

TCL(Transaction Control Language)- This used to manage the changes made by DML statements.
1.COMMIT- save work done
2.SAVEPOINT-identify a point in a transaction to which you can later roll back
3.ROLLBACK-restore database to orriginal since the last COMMIT.
4.SETTRANSACTION-change the transaction options like isolation level and what rollback segment to use.