Four SQL languages: DDL DML DCL TCL and dmldcl

Four SQL languages: DDL DML DCL TCL and dmldcl

There are also four types of SQL discussed by many people. Here, I will popularize the knowledge and summarize their differences.

1. DDL-Data Definition Language

Database Definition Language: defines the structure of a database.

Its main Commands include:CREATE，ALTER，DROP. This language does not require commit, so be careful.

CREATE-to create objects in the database to CREATE objects in the database

Example:

Create database test; // CREATE a DATABASE named test

ALTER-alters the structure of the database modify the database structure

Example:

Alter table test ADD birthday date; // modify the test TABLE and ADD a date-type birthday column.

DROP-delete objects from the database

Example:

Drop database test; // Delete the test DATABASE

There are other:

TRUNCATE-Truncation of table content (usually used during development)

COMMENT -Add remarks for the data dictionary

2. DML-Data Manipulation Language

Database Operation Language: SQL is used to process data in the database.

Its main Commands include:INSERT,UPDATE,DELETE. The language requires commit. There are also common LOCK tables.

There are other unfamiliar ones:

CALL-Call a PL/SQL or Java subroutine

EXPLAIN PLAN -Parse and analyze the data access path

3. DCL-Data Control Language

Database Control Language: authorization, role control, etc.

GRANT-Grant access permissions to users

REVOKE-Revoke authorization Permissions

4. TCL-Transaction Control Language

Transaction Control Language

COMMIT-Save the completed work

SAVEPOINT-Set the save point in the transaction. You can roll back here.

ROLLBACK -Rollback

SET TRANSACTION-Change transaction options

Example: JDBC in Java encapsulates transaction support. For example, first create a new table: test

Test. SQL

Set names utf8; SET FOREIGN_KEY_CHECKS = 0; -- Modify Table structure for 'city' -- -------------------------- drop table if exists 'city'; create table 'city' ('id' int (11) not null default '0' COMMENT 'city id', 'name' varchar (20) default null comment 'name', 'state' varchar (20) default null comment' status ', 'country' varchar (20) default null, primary key ('id') ENGINE = InnoDB default charset = latin1; SET FOREIGN_KEY_CHECKS = 1;

First example of JDBC transaction rollback-JDBC database transaction rollback:

/*** Description: JDBC database transaction rollback ** Created by bysocket on 16/6/6. */public class TransactionRollBack extends BaseJDBC {public static void main (String [] args) throws SQLException {Connection conn = null; try {// load the database Driver Class. forName (DRIVER); // database connection conn = DriverManager. getConnection (URL, USER, PWD); // disable the transaction mechanism conn. setAutoCommit (false); // sets the transaction isolation level SERIALIZABLE conn. setTransactionIsolation (Connection. TRANSACTION_SERIALIZABLE); Statement stmt = conn. createStatement (); int rows = stmt.exe cuteUpdate ("insert into city VALUES (3, 'China', 1, 'cc ')"); rows = stmt.exe cuteUpdate ("UPDATE city set country = 'Taiwan 'WHERE id = 4"); // submit the transaction conn. commit ();} catch (Exception e) {e. printStackTrace (); // roll back the transaction if (conn! = Null) {conn. rollback () ;}} finally {/** close database connection */if (conn! = Null) {try {conn. close () ;}catch (SQLException e) {e. printStackTrace ();}}}}}

Row 19th: SET the TRANSACTION isolation level to SERIALIZABLE. The underlying call is the set transaction of the TCL language.

Row 22nd: The execution passes and data is inserted.

Row 23rd: The execution fails and no record with the primary key 4 exists. An exception is thrown directly.

Row 3: Transaction ROLLBACK, which encapsulates the ROLLBACK of the TCL statement

The second example of JDBC transaction rollback-The JDBC database transaction rollback to a specific storage point:

/*** Description: JDBC database transaction rollback to a specific storage point ** Created by bysocket on 16/6/6. */public class TransactionRollBack2 extends BaseJDBC {public static void main (String [] args) throws SQLException {Connection conn = null; Savepoint svpt = null; try {// load the database Driver Class. forName (DRIVER); // database connection conn = DriverManager. getConnection (URL, USER, PWD); // disable the transaction mechanism conn. setAutoCommit (false); // sets the transaction isolation level SERIALIZABLE conn. s EtTransactionIsolation (Connection. TRANSACTION_SERIALIZABLE); Statement stmt = conn. createStatement (); int rows = stmt.exe cuteUpdate ("insert into city VALUES (3, 'China', 1, 'cc ')"); // set the transaction retention point svpt = conn. setSavepoint (); rows = stmt.exe cuteUpdate ("UPDATE city set country = 'Taiwan 'WHERE id = 4"); // submit the transaction conn. commit ();} catch (Exception e) {e. printStackTrace (); // roll back the transaction if (conn! = Null) {conn. rollback (svpt) ;}} finally {/** close database connection */if (conn! = Null) {try {conn. close () ;}catch (SQLException e) {e. printStackTrace ();}}}}}

If it is repeated in the first example, it will not be mentioned.

Row 3: declares a save point.

Row 24th: Set the save point.

Row 33rd: roll back the transaction to the Save point

The above Code involves the SAVEPOINT In the TCL language.

Finally, let's take a look at the following figure: (SELECT belongs to DQL .)

I hope this article will help you learn SQL.