Structured Query Language (SQL) is a database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database.
SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, and set permission for users.
Various Sql Commands
- DDL – Data Definition Language
- DML – Data Manipulation Language
- DCL – Data Control Language
- TCL – Transaction Control Language
DDL (Data Definition Language)
DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects 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 (COLUMN_NAME DATATYPES[,....]);Example:
CREATE TABLE employee(Name VARCHAR(20), Email VARCHAR(100), DOB DATE);ALTER
It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.
Syntax:
To add a new column in the table:
ALTER TABLE table_name ADD column_name COLUMN-definition;To modify existing column in the table:
ALTER TABLE MODIFY(COLUMN DEFINITION....);Example:
ALTER TABLE stu_details ADD(ADDRESS VARCHAR2(20));
ALTER TABLE stu_details MODIFY (NAME VARCHAR2(20))DROP
It is used to delete both the structure and record stored in the table.
Syntax:
DROP TABLE ;Example:
DROP TABLE employee;RENAME
Use to rename an object existing in the database.
Syntax:
RENAME old_table _name To new_table_name;Example:
RENAME students TO employee;TRUNCATE
Remove all the records from a table, including all spaces allocated for the records are removed.
Syntax:
TRUNCATE TABLE table_name;Example:
TRUNCATE TABLE employee;COMMENT
Add comments to the data dictionary.
DML (Data Manipulation Language)
DML is short name of 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
This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.
Syntax:
SELECT expressions FROM TABLES WHERE conditions;Example:
SELECT emp_name FROM employee WHERE age > 20INSERT
The INSERT statement is a SQL query. It is used to insert data into the row of a table.
Syntax:
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3, ....valueN);Example:
INSERT INTO student_info (stu_name, stu_result) VALUES (“MEET”, ”PASS”);UPDATE
This command is used to update or modify the value of a column in the table.
Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]Example:
UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = '3'DELETE
It is used to remove one or more row from a table.
Syntax:
DELETE FROM table_name [WHERE condition];Example:
DELETE FROM student_info WHERE stu_name = “MEET”;LOCK TABLE
A lock is a flag associated with a table. MySQL allows a client session to explicitly acquire a table lock for preventing other sessions from accessing the same table during a specific period.
A client session can acquire or release table locks only for itself. And a client session cannot acquire or release table locks for other client sessions.
Syntax:
LOCK TABLES table_name [READ | WRITE]In this syntax, you specify the name of the table that you want to lock after the LOCK TABLES keywords. In addition, you specify the type of lock, either READ or WRITE.
MySQL allows you to lock multiple tables by specifying a list of comma-separated names of tables with lock types that you want to lock after the LOCK TABLES keywords:
Example:
LOCK TABLES table_name1 [READ | WRITE], table_name2 [READ | WRITE], ... ;DCL (Data Control Language)
DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.DCL commands are used to grant and take back authority from any database user.
GRANT
It is used to give user access privileges to database.You can grant users various privileges to tables. These permissions can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.
Syntax:
GRANT privileges ON object TO user;Privileges:
The privileges to assign. It can be any of the following values:
| Privileges | Description |
|---|---|
| SELECT | Ability to perform SELECT statements on the table |
| INSERT | Ability to perform INSERT statements on the table |
| UPDATE | Ability to perform UPDATE statements on the table |
| DELETE | Ability to perform DELETE statements on the table |
| REFERENCES | Ability to create a constraint that refers to the table |
| ALTER | Ability to perform ALTER TABLE statements to change the table definition |
| ALL | ALL does not grant all permissions for the table. Rather, it grants the ANSI-92 permissions which are SELECT, INSERT, UPDATE, DELETE, and REFERENCES |
Object:
The name of the database objects that you are granting permissions for. In the case of granting privileges on a table, this would be the table name.
User:
The name of the user that will be granted these privileges.
Example:
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO meet;REVOKE
It is used to take back permissions from the user.Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.
Syntax:
REVOKE privileges ON object FROM user;Privileges:
The privileges to assign. It can be any of the following values:
| Privileges | Description |
|---|---|
| SELECT | Ability to perform SELECT statements on the table |
| INSERT | Ability to perform INSERT statements on the table |
| UPDATE | Ability to perform UPDATE statements on the table |
| DELETE | Ability to perform DELETE statements on the table |
| REFERENCES | Ability to create a constraint that refers to the table |
| ALTER | Ability to perform ALTER TABLE statements to change the table definition |
| ALL | ALL does not grant all permissions for the table. Rather, it grants the ANSI-92 permissions which are SELECT, INSERT, UPDATE, DELETE, and REFERENCES |
Object:
The name of the database objects that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.
User:
The name of the user that will have these privileges revoked.
Example:
REVOKE DELETE ON employees FROM meet;TCL (Transaction Control Language)
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only. These operations are automatically committed in the database that’s why they cannot be used while creating tables or dropping them.
COMMIT
Commit command is used to save all the transactions to the database.
Syntax:
COMMIT;Example:
DELETE FROM student_info WHERE stu_name = “MEET”;
COMMIT;ROLLBACK
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
Syntax:
ROLLBACK;Example:
DELETE FROM student_info WHERE stu_name = “MEET”;
ROLLBACK;SAVEPOINT
It is used to roll the transaction back to a certain point without rolling back the entire transaction.
Syntax:
SAVEPOINT savepoint_name;This command serves only in the creation of a SAVEPOINT among all the transactional statements. The ROLLBACK command is used to undo a group of transactions.
The syntax for rolling back to a SAVEPOINT is as shown below:
ROLLBACK TO savepoint_name;SET TRANSACTION
The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read only or read write.
Syntax:
SET TRANSACTION [ READ WRITE | READ ONLY ];