MySQL – DDL, DML, DCL and TCL Commands

DDL:

DDL is stands for DATA DEFINITION LANGUAGE, which deals with database schemas and descriptions, of how the data should reside in the database.

• CREATE – To create a database and its objects like (table, index, views, store procedure, function, and triggers)

  //Syntax
  CREATE TABLE table_name (
        column1 datatype,
        column2 datatype,
      ....
   );
  
  //Ex:
  CREATE TABLE Tbl_Employee (
  EmployeeID int,
  LastName varchar(255),
  FirstName varchar(255),
  Gender varchar(255),
  City varchar(255) 
  );
  
  Try It Now
  

• ALTER – Alters the structure of the existing database. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

  //Syntax
  ALTER TABLE table_name ADD column_name COLUMN DEFINITION; 
  //to add new column
  ALTER TABLE table_name MODIFY(COLUMN DEFINITION....); 
  //to modify existing coulmn
  
  //Ex:
  ALTER TABLE Tbl_Employee ADD Email varchar(255);
  ALTER TABLE Tbl_Employee MODIFY Email varchar(50);
  
  Try It Now

• DROP – Delete objects from the database. It is used to delete both the structure and record stored in the table.

  //Syntax:
  DROP TABLE table_name;
  
  //Ex:
  DROP TABLE Tbl_Employee;

• TRUNCATE – Remove all records from a table, including all spaces allocated for the records are removed. In this case primary key will get reset to 1.

  //Syntax:
  TRUNCATE TABLE table_name;
  
  //Ex:
  TRUNCATE TABLE Tbl_Employee;

• RENAME – Rename an object. It might be Table name

  //Syntax:
  RENAME tablename to newtablename;
  
  //Ex: RENAME Tbl_Employee TO Tbl_Employee_New; 
  //rename table name

DML:

DML is stands for DATA MANIPULATION LANGUAGE which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc. and it is used to store, modify, retrieve, delete and update data in a database.

• SELECT– Retrieve data from a database.

  //Syntax:
  SELECT column1, column2, ...
  FROM table_name;
  
  //Ex:
  SELECT * FROM Tbl_Employee;
  
  Try It Now

• INSERT– Insert data into a table.

  //Syntax:
  INSERT INTO table_name (column1, column2, column3, ...)
  VALUES (value1, value2, value3, ...);
  OR
  INSERT INTO table_name
  VALUES (value1, value2, value3, ...); 
  
  //Ex:
  INSERT INTO Tbl_Employee 
  VALUES (1,'Smith','James','Male','Mumbai');
  
  Try It Now

• UPDATE– Updates existing data within a table.

  //Syntax:
  UPDATE table_name
    SET column1 = value1, column2 = value2, ...
    WHERE condition;
  
  //Ex:
  UPDATE Tbl_Employee SET City = 'Delhi' 
  WHERE EmployeeID = 1;
  
  Try It Now

• DELETE– The DELETE statement is used to delete existing records in a table.

  //Syntax:
  DELETE FROM table_name WHERE condition;
  
  //Ex:
  UPDATE FROM Tbl_Employee WHERE EmployeeID = 1;
  
  Try It Now

DCL:

DCL is stands for DATA CONTROL LANGUAGE which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.

• GRANT – Allow users access privileges to the database.

  //Syntax:
  GRANT SELECT, UPDATE ON MY_TABLE 
  TO SOME_USER, ANOTHER_USER;

• REVOKE – Withdraw users access privileges given by using the GRANT command.

  //Syntax:
  REVOKE SELECT, UPDATE ON MY_TABLE 
  FROM SOME_USER, ANOTHER_USER;

TCL:

TCL is stands for TRANSACTION CONTROL LANGUAGE which deals with a transaction within a database.

• COMMIT – Commits a Transaction.When a successful transaction is completed, the COMMIT command should be issued so that the changes to all involved tables will take effect.

  //Syntax:
  COMMIT; //Execute after any mysql execution
  
  //Ex:
  DELETE FROM Tbl_Employee  WHERE EmployeeID = 1; 
  COMMIT; ##transaction has been complete
  
  Try It Now

• ROLLBACK – Rollback a transaction in case of any error occurs.

  //Syntax:
  ROLLBACK; //Execute after any mysql execution 
  
  //Ex: 
  DELETE FROM Tbl_Employee WHERE EmployeeID = 1;
  COMMIT; ##rollback previous transaction
  
  Try It Now

• SAVEPOINT – To rollback the transaction making points within groups.

  //Syntax:
  Mysql query statment1;
  SAVEPOINT tran1; //Belongs to statment1
  
  Mysql query statment2;
  SAVEPOINT tran2; //Belongs to statment2
  
  ROLLBACK TO tran1; 
  //only output of statement1 will remain 
  and output of statemnt2 will rollback.
  
  //Ex:
  DELETE FROM Tbl_Employee  WHERE EmployeeID = 1; 
  SAVEPOINT tran1;
  
  DELETE FROM Tbl_Employee  WHERE EmployeeID = 2; 
  SAVEPOINT tran2;
  
  ROLLBACK TO tran1; ##roll back trans1
  
  Try It Now

• SET TRANSACTION – Specify characteristics of the transaction.