Detailed SQL four languages: DDL DML DCL Tcl_mysql

See a lot of people talking about SQL is also divided into four types, where the knowledge is popular, and summed up their differences.

1. Ddl–data Definition Language

Database Definition language: Defines the structure of a database.

The main commands are CREATE，ALTER，DROP as follows, with examples. The language does not require a commit, so be cautious.

Create–to create objects in the database creating objects

Cases:

CREATE DATABASE test; To create a database named Test

Alter–alters the structure the database structure

Cases:

ALTER TABLE test ADD birthday date; Modify the test table to add a birthday column of the date type

Drop–delete objects from the database deletes objects

Cases:

Drop database test;//Delete test databases

There are other:

TRUNCATE– Truncate the contents of the table (development period, or quite often)

COMMENT – Add notes for the data dictionary

2. Dml–data Manipulation Language

Database operation language: processing data in a database in SQL

The main commands are INSERT,UPDATE,DELETE as such, these examples are often not introduced. The language requires a commit. There are also commonly used LOCK TABLE.

There are other unfamiliar:

CALL– Invoke a Pl/sql or Java subroutine

EXPLAIN PLAN -Parsing the data access path

3. Dcl–data Control Language

Database Control Language: Authorization, role control, etc.

GRANT– Give the user access rights

REVOKE– Recall Authorization Permissions

4. Tcl–transaction Control Language

Transaction Control Language

COMMIT– Save the work that has been done

SAVEPOINT– Set the save point in a transaction to roll back here

ROLLBACK – Roll Back

SET TRANSACTION– Change transaction options

Example: JDBC in Java encapsulates support for transactions. For example, we first create 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 DEFAULT charset=latin1;
 
SET foreign_key_checks = 1;

JDBC Transaction rollback The first example-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 Database driver Class.forName (DRIVER);
 
      Database Connection conn = Drivermanager.getconnection (URL,USER,PWD);
      Turn off the autocommit transaction mechanism Conn.setautocommit (FALSE);
 
      Set the transaction isolation Level SERIALIZABLE conn.settransactionisolation (connection.transaction_serializable);
      Statement stmt = Conn.createstatement ();
      int rows = Stmt.executeupdate ("INSERT into City VALUES (3, ', ', 1, ' cc ')");
 
      rows = Stmt.executeupdate ("UPDATE City Set country = ' TAIWAN ' WHERE id = 4");
    Submit transaction Conn.commit ();
      catch (Exception e) {e.printstacktrace ();
      ROLLBACK TRANSACTION IF (conn!= null) {conn.rollback ();
        Finally {/** closes the database connection */IF (conn!= null) {try {conn.close (); catch (SqlexCeption e) {e.printstacktrace (); }
      }
    }
  }
}

Line 19th: Set the transaction isolation level for the SERIALIZABLE low-level call is the TCL language set TRANSACTION

Line 22nd: Execute through, insert data

Line 23rd: Execute does not pass, no record with primary key 4, throw exception directly

Line 31st: Transaction rollback, encapsulation is the rollback of the TCL statement

JDBC Transaction rollback The second example-JDBC the database transaction rollback to a specific save point:

/** * Description: JDBC database transaction rollback, rollback to 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);
 
      Database Connection conn = Drivermanager.getconnection (URL,USER,PWD);
      Turn off the autocommit transaction mechanism Conn.setautocommit (FALSE);
 
      Set the transaction isolation Level SERIALIZABLE conn.settransactionisolation (connection.transaction_serializable);
      Statement stmt = Conn.createstatement ();
      int rows = Stmt.executeupdate ("INSERT into City VALUES (3, ', ', 1, ' cc ')");
      Set transaction save Point SVPT = Conn.setsavepoint ();
 
      rows = Stmt.executeupdate ("UPDATE City Set country = ' TAIWAN ' WHERE id = 4");
    Submit transaction Conn.commit ();
      catch (Exception e) {e.printstacktrace ();
      ROLLBACK TRANSACTION IF (conn!= null) {conn.rollback (SVPT); Finally {/** Shutdown database connectionConnect */IF (conn!= null) {try {conn.close ();
        catch (SQLException e) {e.printstacktrace (); }
      }
    }
  }
}

The repetition of the first example is not mentioned.

Line 9th: A save point is declared

Line 24th: Save Point set

Line 33rd: ROLLBACK TRANSACTION to the SavePoint

The code above refers to the savepoint in the Tcl language.

Finally come to the picture summary: (select belongs to Dql ha.) ）

I hope this article will help you learn about SQL.