Database must be known in SQL four languages: DDL DML DCL TCL

Mason today in the group of discussion, DDL or DML, I this small white or summed up their differences.

1. Ddl-data Definition Language

Database Definition language: Defines the structure of the database. The main orders are Create,alter,drop, and so on, the following examples are detailed. This language does not require a commit, so be cautious. Create-to create objects in the database creating objects

Example: CREATE DATABASE test; To create a database named Test

Alter-alters the structure of the database structure

Example: ALTER TABLE test ADD birthday date; Modify test table to add Birthday column of date type

Drop-delete objects from the database to delete objects from databases

Example: Drop database test;//Delete test databases

There are other: TRUNCATE-TRUNCATE TABLE content (development period, or quite common) COMMENT-add notes to the data dictionary

2. Dml-data Manipulation Language

Database manipulation language: SQL to process data in a database its main commands are select,insert,update,delete, and so on, these examples are often not introduced. The language requires a commit. There are commonly used lock TABLE, remember to write the lock blog-Portal and other unfamiliar: call-invoke a PL/SQL or Java subroutine EXPLAIN PLAN-Parse data access path

3. Dcl-data Control Language

Database Control Language: Authorization, role control, etc. grant-grant user access REVOKE-Revoke authorization permission

4. Tcl-transaction Control Language

Transaction control Language COMMIT-save completed work SavePoint-set the savepoint in the transaction, which can be rolled back here ROLLBACK-rollback set TRANSACTION-Change transaction options Example: JDBC in Java encapsulates support for transactions. For example, we'll start with a new table: Test Test.sql

SET NAMES UTF8; SET foreign_key_checks = 0;--------------------------------Table structure for ' city '------------------------------ DROP TABLE IF EXISTS ' city ';   CREATE TABLE ' city ' (  ' id ' int (one) ' NOT null default ' 0 ' COMMENT ' urban id ', ' name ' varchar () default NULL COMMENT ' name ', ' state ' varchar default null COMMENT ' status ', ' country ' varchar default NULL, PRIMARY KEY (' id ')) engine=innodb D Efault charset=latin1; SET foreign_key_checks = 1;

JDBC Transaction rollback The first example-JDBC a 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 Database driver            class.forname (DRIVER) &NB Sp          //database connection            conn = drivermanager.getconnection (URL , user,pwd);            //close auto-commit transaction mechanism            conn.setautocommit (false) ;            //SET TRANSACTION isolation level SERIALIZABLE            CONN.SETTRANSAC Tionisolation (connection.transaction_serializable);            statement stmt = Conn.createstatement ();            int rows = stmt.executeupdate ("INSERT into City VALUES (3, ' China ', 1, ' cc ')");           &NBsp;rows = Stmt.executeupdate ("UPDATE City Set country = ' TAIWAN ' WHERE id = 4");            //COMMIT transaction            conn.commit ();        } catch (Exception e) {e.printstacktrace ();           &NBS p;//ROLLBACK TRANSACTION            IF (conn! = null) {conn.rollback ();     &NBSP ;      } finally {/** Close database connection */           IF (conn! = Nu  ll) {try {conn.close ();                } catch (SQLException e)        {e.printstacktrace ()                }} }    }}

Line 19th: Set the transaction isolation level for SERIALIZABLE to call the set TRANSACTION 22nd line of the TCL language: Execute pass, insert data 23rd line: Do not pass, no primary key is 4 record, throw exception 31st line: Transaction rollback, encapsulated Is the rollback of TCL statement

rest, one example enough, one more. The code is on the GitHub home page. Https://github.com/JeffLi1993/jee-component-learning

JDBC Transaction rollback The second example-JDBC the database transaction rollback, rolling back to a specific savepoint:

/** * Description: JDBC database transaction rollback, rollback to a specific savepoint * * 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 Database driver            class.forname (DRIVER) &NB Sp          //database connection            conn = drivermanager.getconnection (URL , user,pwd);            //close auto-commit transaction mechanism            conn.setautocommit (false) ;            //SET TRANSACTION isolation level SERIALIZABLE            CONN.SETTRANSAC Tionisolation (connection.transaction_serializable);            statement stmt = Conn.createstatement ();            int rows = stmt.executeupdate ("INSERT into city VALUES (3, ' China ', 1, ' cc ') ");            //set up transaction save points            SVPT = Conn.setsavepoint ();            rows = stmt.executeupdate ("UPDATE City Set country = ' TAIWAN ' WHERE id = 4");            //COMMIT transaction            conn.commit ();        } catch (Exception e) {e.printstacktrace ();           &NBS p;//ROLLBACK TRANSACTION            IF (conn! = null) {conn.rollback (SVPT);     & nbsp      } finally {/** Close database connection */           IF (conn! = Nu  ll) {try {conn.close ();                } catch (SQLException e)        {e.printstacktrace ()                }}}    }} 

And the first example of repetition is not mentioned. Line 9th: Declares a save point, line 24th: Set the Save point 33rd Line: ROLLBACK TRANSACTION to this save point above the code is related to the TCL language in the SavePoint final summary: if the above article or link to help you, do not forget to share the circle of friends, so that more people read Read this post.

Database must be known in SQL four languages: DDL DML DCL TCL