SQL Server commands - DML, DDL, DCL, TCL
For those who are in first touch with SQL and it's standard commands... SQL commands in simple words are commands or instructions which we are using with queries to communicate with our database.
With these commands, we can do administration for our database as well as use/read data, edit it or copy and delete etc...
It is not uncommon to create our database, tables an relationship between them using some of these commands which we will cover in later part of this article.
SQL Server commands are grouped in these four main logical groups, and they are:
- Data Manipulation Language (DML)
- Data Definition Language (DDL)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
Using these commands we can define structure of our database, do the insert or update to the data, we can control the access or privileges over our database.
First, we will go with Data Manipulation Language commands group.
Data Manipulation Language - DML
DML commands are mainly used for manipulation with the records in our table, so with them, we can select/read data with some criteria or not, we can insert new data or edit existing ones... and of course we can delete records if we don't need them anymore.
DML commands are:
SELECT - select/read records from table in our database,
INSERT - we can insert new records in our table,
UPDATE - edit/update existing records,
DELETE - delete existing records in our table
DML Commands are mainly used in similar way and by their name we can realize for what they are mainly used in practise, on next image we will show example of all four commands in real examples:
Short explanation about these commands:
From SELECT part we can see very simple syntax, after we write keyword SELECT with asterisk sign (which replace ALL columns) so with this command in our output we will get all columns from table employee, in case that we don't need or won't all the columns we can list columns that we want to see in output... also in our SELECT command after FROM part we could use WHERE clause to filter output records with some condition.
**INSERT **command is also very simple, after keyword INSERT we select table name (INTO keyword is optional) after name of table in brackets we can specify which columns we will use in this insert, after that we put keyword VALUES and than in bracket we are typing values that we want to insert. Arrangement of columns in insert part must be the same as in VALUES part so that we insert correct values in correct columns.
UPDATE is very useful when we want to change some data in our record in table, this command has very simple syntax as we can se in image above... after keyword UPDATE we list name of table after that with keyword SET and column that we want to change and than assignment sign with new values (we can combine more than one column)... after that the most important thing is to use WHERE clause to specify criteria for the record that we want to change that can be for example an ID of Person in table.
DELETE is very powerful and simple command, after keyword DELETE we list table name from which one we want to delete the record and after that the most important thing is to use WHERE clause to specify criteria for the record that we want to change that can be for example an ID of Person in table (Same as in UPDATE).
Warning: UPDATE and DELETE without WHERE clause will set same value from update/delete all the records from the table!
Data Definition Language - DDL
DDL commands we use for definition and creation objects in database (Table, Procedure, Views...). These commands are mainly used for design and definition the structure of our database.
DDL commands are:
CREATE - we can create a new table, database, procedure, view, trigger...
ALTER - usually we use for editing database objects (table, procedure, view...) for example, add or delete column from table
DROP - we use for deleting database objects
Rename- is used to rename an object existing in database.
Usage for these commands is showed this image:
As for the previous image commands we will wrote some short explanation for DDL commands.
CREATE TABLE will obviously create a new table after two keywords CREATE and TABLE we pick a name for the new table and in the body of this command we type the columns/attributes for new table... very similar syntax is also for creating new Views, Procedures or Triggers.
ALTER we can use to edit our object, for this example on the last image we have added new column/attribute BirthDate in our table Person.
DROP is used to delete objects from a database, we use DROP with a keyword for an object that we want to create and a name for that object.
Data Control Language - DCL
DCL commands are used for access control and permission management for users in our database. With them we can easily allow or deny some actions for users on the tables or records (row level security).
DCL commands are:
GRANT - we can give certain permissions on the table (and other objects) for certain users of database,
DENY - bans certain permissions from users.
REVOKE - with this command we can take back permission from users.
Example of usage of these commands are in this image here:
GRANT in first case we gave privileges to user Almir to do SELECT, INSERT, UPDATE and DELETE on the table called employees.
REVOKE with this command we can take back privilege to default one... in this case, we take back command INSERT on the table employees for user Almir.
**DENY **is a specific command. We can conclude that every user has a list of privilege which is denied or granted so command DENY is there to explicitly ban you some privileges on the database objects.
Transaction Control Language - TCL
With TCL commands we can mange and control T-SQL transactions so we can be sure that our transaction is successfully done and that integrity of our database is not violated.
TCL commands are:
BEGIN TRAN - begin of transaction
**COMMIT TRAN - **commit for completed transaction
ROLLBACK - go back to beginning if something was not right in transaction.
On this image we have simple example of these three commands combined in one transaction. With BEGIN TRANSACTION obviously we will begin our transaction, as we can see we will update some author with some value of ID... query is more or less clear and the important part is IF-ELSE part of query. If we affect five rows with our first query than we will COMMIT this transaction. Obviously we will not affect five rows, so we will affect one row for this reason we will not go into the IF part but we will go into the ELSE part so our transaction will be returned to beginning state because it will trigger ROLLBACK command and everything that happened in UPDATE it will be undone.
SAVEPOINT:
Suppose there are set of update, delete transactions performed on the tables. But there are some transactions which we are very sure about correctness. After that set of transactions we are uncertain about the correctness. So what we can do here is we can set a SAVEPOINT at the correct transaction telling the database that, in case of rollback, rollback till the savepoint marked. Hence the changes done till savepoint will be unchanged and all the transactions after that will be rolled back.
In the case of multiple transactions, savepoint can be given after each transactions and transaction can be rolled back to any of the transactions.
01.TRANSACTION T1; -- Transaction can be insert, update or delete
02.
03.SAVEPOINT S1;
04.TRANSACTION T2;
05.SAVEPOINT S2;
06.TRANSACTION T3;
07.SAVEPOINT S3;
08.TRANSACTION T4;
09.ROLLBACK TO S1; -- This will rollback all the changes by T1 and T2 and will have only the changes done on T1
**AUTOCOMMIT : **
**
AUTOCOMMIT command automatically commits each transaction after its execution. If this command is set, then no need to explicitly issue commit. We cannot rollback our transactions, if AUTOCOMMIT is on. This needs to be set /unset before we begin any transactions.
**