Different types of commands in SQL :
1)DDL - Data Definition Language
2)DML - Data Manipulation Language
3)DCL - Data Control Language
4)TCL - Transaction Control Language
5)DQL - Data Query Language
1)DDL - Data Definition Language
DDL Command used to perform various operations like create table, delete table
and alter table
Following are the different commands of
CREATE , ALTER , DROP , TRUNCATE
a) CREATE : Use of create command is to create table in
database.
Synatx of Create Command is :
create table table_name(column_name1 datatype,column_name2 datatype,.....);
Example :
create table Student_info(Stud_id int(5), Name varchar(20));
b) ALTER : Below mentioned are the different uses of alter command
Modify the characteristics of an existing attribute in a table
Add a new attribute into the table
Synatx of alter Command is :
alter table table_name add column_name column_definition;
Example :
alter table Student_info add(age int(5));
c) DROP : Below mentioned are the different uses of drop command
Delete structure of table , Delete record stored in table
Synatx of drop Command is :
drop table table_name;
Example :
drop table Student_info;
d) TRUNCATE: Use of truncate command is to delete all the rows from the
table
Synatx of alter truncate is :
truncate table table_name;
Example :
truncate table Student_info;
2) DML - Data Manipulation Language
Commands of DML are used to modify the database
Below mentioned are the different commands of DML
INSERT , UPDATE , DELETE
a) INSERT : This command is used to insert record values
Syntax :
insert into table_name (Column_name 1, Column_name 2, Column_name 3,.....) values (value 1, value 2, value 3,.....);
Example :
insert into Student_info(Stud_id,Stud_name) values(1,”ABC”);
b) UPDATE : This command is used to update record values in a table.
Syntax :
update table_name set [column_name1=value1,.....] where CONDITION
Example :
update Student_info set Stud_name=”xyz” where Stud_id=1;
c) DELETE : This command is used to remove one or more record/records from
table.
Syntax :
delete from table_name where CONDITION
Example :
delete from Student_info where Stud_id=1;
3) DCL - Data Control Language
Below mentioned are the commands in DCL
GRANT : This command is used To provides the user's access privileges to the database.
To allows a user in the current database to work with data
REVOKE : revoke (back permission)
This Command is used to revoke the privileges from MySQL accounts.
4) TCL - Transaction Control Language
- Below mentioned are the different TCL commands
COMMIT: This command is used to save all the transactions into the
database and changes made in the current transaction are made permanent
ROLLBACK: This command is used to undo transactions that have not
already been saved to the database.
SAVEPOINT: This command is used to break the transaction into multiple
units so that the user has can rollback upto specified point.
Note : - These commands are used with DML commands
a) COMMIT : This command is used to save all the transactions into the
database and changes made in the current transaction are made
permanent
Example :
delete from
Student_info
where city=”Pune”
COMMIT;
b)ROLLBACK : This command is used to undo transactions that have not
already been saved to the database.
Example :
delete from student_info where city=“Pune”
ROLLBACK;
c) SAVEPOINT : This command is used to break the transaction into multiple
units so that the user can rollback upto specified point.
Example : Consider table is created with name Student_info having attributes
id and name.
Start Transaction;
SAVEPOINT savepoint1;
insert into Student_info values(1,”ABC”);
SAVEPOINT savepoint2;
insert into Student_info values(2,”XYZ”);
5) DQL : Data Query Language
- This command is used to fetch the data from the database.
SELECT is DQL command
SELECT: This command is used to select the attribute based on the condition
described by WHERE clause.
Example: Consider table is created with name Student_info having
attributes id and name.
Select name from Student_info where id=”1”;