DDL, DML, DCL and TCL Commands in SQL/MySQL/NoSQL

CodeWorld19
6 Apr, 2020
DDL, DML, DCL and TCL Commands in SQL/MySQL/NoSQL

Topic : DDL, DML, DCL and TCL command's in SQL/MYSQL/NoSQL


Objective

to understand database DDL(Data Definition Language), DML(Data Manipulation Language), DCL (Data-Control Language) and TCL (Transaction Control Language) Commands in SQL/MySQL/NoSQL.

Relevant Theory

SQL :-
IBM developed the original version of SQL, originally called Sequel, as part of the System R project in the early 1970s. The Sequel language has evolved since then, and its name has changed to SQL (Structured Query Language). Many products now support the SQL language. SQL has clearly established itself as the standard relational database language. In 1986, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) published an SQL standard, called SQL-86. ANSI published an extended standard for SQL, SQL-89, in 1989. The next version of the standard was SQL-92 standard, followed by SQL:1999, SQL:2003,
SQL:2006, and most recently SQL:2008. The bibliographic notes provide references to these standards.

Selecting Databases: 
Once you get connection with MySQL server, it is required to select a particular
database to work with. This is because there may be more than one database available with MySQL Server. 
Selecting MySQL Database from Command Prompt: 
This is very simple to select a particular database from mysql> prompt. You can use SQL command use to select a particular database.

The SQL language has several parts:
  1. DDL (Data-definition Language)
    2.
  2. DML (Data-manipulation Language)
    3.
  3. DCL (Data-Control Language)
  4. TCL (Transaction Control Language)
    5.

1. DDL(Data-definition Language) :-

The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.

DDL commands are follows :
  • CREATE
  • DROP
  • ALTER
  • TRUNCATE
  • COMMENT
  • RENAME

1.1 CREATE COMMAND :
is used to create the database or its objects. You would need special privileges to create or to delete a MySQL database. So assuming you have access to root user, you can create any database using MySQL.
Syntax : 
CREATE TABLE table name (column_name1 data type (size), column_name2 data_type(size), 
....... )

1.2 DROP COMMAND :
is used to delete objects from the database.You would need special privileges to create or to delete a MySQL database. SIt is very easy to drop an existing MySQL table, but you need to be very careful while deleting any existing table because data lost will not be recovered after deleting a table.
Syntax :
DROP TABLE table_name;

1.3 ALTER COMMAND :
is used to alter the structure of the database. ALTER command is very useful when you want to change a name of your table, any table field or if you want to add or delete an existing column in a table.
Syntax : 
ALTER TABLE table_name ADD (newcolumn_name1 data_type(size), 
newcolumn_name2 data_type(size), .......)

1.4 TRUNCATE COMMAND :
is used to remove all records from a table, including all spaces allocated for the records are removed.

1.5 COMMENT COMMAND :
is used to add comments to the data dictionary.

1.6 RENAME COMMAND :
is used to rename an object existing in the database.

2. DML(Data-manipulation Language) :-

The SQL DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database. The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.

DML commands are follows :
  • SELECT COMMAND
  • INSERT COMMAND
  • UPDATE COMMAND
  • DELETE COMMAND

2.1 SELECT COMMAND :
is used to retrieve data from the a database. Select is the most commonly used statement in SQL. The SELECT Statement in SQL is used to retrieve or fetch data from a database. We can fetch either the entire table or according to some specified rules. The data returned is stored in a result table. This result table is also called result-set.
in the select command multiple clause are used to display data in different manner. we have see in deep in clause section.

2.2 INSERT COMMAND :
The INSERT command in SQL is used to add records to an existing table. is used to insert data into a table. To insert data into MySQL table, you would need to use SQL INSERT INTO command. You can insert data into MySQL table by using mysql> prompt or by using any script like PHP.

Format 1:-Inserting a single row of data into a table
Syntax :
INSERT INTO table name [(column name, column name)] VALUES (expression, expression);

Format 2: Inserting data into a table from another table
Syntax :
INSERT INTO table name SELECT column name, column name FROM table name.


2.3 UPDATE COMMAND :
The UPDATE command can be used to modify information contained within a table. There may be a requirement where existing data in a MySQL table needs to be modified. You can do so by using SQL UPDATE command. This will modify any field value of any MySQL table.
Syntax :
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]


2.4 DELETE COMMAND :
The DELETE command can be used to delete information contained within a table. If you want to delete a record from any MySQL table, then you can use SQL command DELETE FROM. You can use this command at mysql> prompt as well as in any script like PHP.
Syntax :
DELETE FROM table_name [WHERE Clause]

3. DCL (Data-Control Language) :-

DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system. Data Control Language(DCL) is used to control privileges in Database. To perform any operation in the database, such as for creating tables, sequences or views, a user needs privileges. 
Privileges are of two types :-
  • System: This includes permissions for creating session, table, etc and all types of other system privileges.
  • Object: This includes permissions for any command or query to perform any operation on the database tables
DCL commands are follows : 
  • GRANT COMMAND
  • REVOKE COMMAND 
     
3.1 GRANT COMMAND :
Used to provide any user access privileges or other privileges for the database.

3.2 REVOKE COMMAND :
Used to take back permissions from any user.

4. TCL (Transaction Control Language) :-

Transaction Control Language(TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions.
DCL commands are follows :
  • COMMIT
  • ROLLBACK
  • SAVEPOINT
4.1 COMMIT COMMAND :
COMMIT command saves all the work done. When we use any DML command like INSERT, UPDATE and DELETE the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back.
It ends the current transaction and makes permanent changes during the transaction.
Syntax :
commit ;

4.2 ROLLBACK COMMAND :
ROLLBACK command restores database to original since the last COMMIT. This command restores the database to last commited state. It is also used with savepoint command to jump to a savepoint in an ongoing transaction. It is used to restores the database to last committed state.
Syntax :
ROLLBACK TO SAVEPOINT <savepoint_name>;

4.3 SAVEPOINT COMMAND :
SAVEPOINT command is used for saving all the current point in the processing of a transaction. command is used to temporarily save a transaction so that you can rollback to that point whenever required.
Syntax :
SAVEPOINT savepoint_name;


CodeWorld19

CodeWorld19

Hey, I am Parthe Omkar

Next Post Previous Post
No Comment
Add Comment
comment url