Hello Folks😎, this is my first post✌.
Just for fun, I would like to share with you all the SQL command types with their examples.
*## What is SQL? *
1- The SQL language is one of the primary reasons for the success of relational databases in the corporate world.
Because it became a standard for relational
databases for executing queries & fetching data.
2 - SQL uses some of the technical jargon like table, row(tuple), column(attribute) & schema.
3 - A SQL table is the basic element of a relational database (RDS).
*What are SQL commands? *
Structured query language (SQL) commands are specific keywords or SQL statements that developers use to manipulate the data stored in a relational database. We can categorize SQL commands in 5 different ways.
a -> DDL = DDL, which stands for "Data Definition Language", which is generally responsible for the structure of the relation(table), we can also call it a "paper schema".
We can design our database through the following commands in SQL.
-
CREATE -> Used to create the table(schema). Creation of new
table requires you to specify the table name
columns & the data type of each column.Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
-
DROP -> The DROP command can be used to drop named
schema(table) elements, such as tables domains, or
constraints. In layman's terms, it will delete the
whole table along with the data stored inside it.Syntax: DROP TABLE table_name;
-
ALTER -> The ALTER command allows you to add/delete or modify
the columns(attributes) to an existing table in the
database.Syntax: ALTER TABLE table_name ADD column_name COLUMN-definition;
-
TRUNCATE -> The TRUNCATE command simply deletes the whole
contents of the selected existing
table. But it will not delete the table itself,
only the data inside it, will be deleted.Syntax: TRUNCATE TABLE table_name;
-
COMMENT -> The COMMENT command helps the developer to add
extra information about their SQL queries, you can
include a comment in a statement that describes
the purpose of the statement within your
application.
Begin the comment with a slash and an asterisk
(/) write your text or comment. End the comment
with an asterisk and a slash (/).-
RENAME -> There are some situations where we need to change
the name of the table/ columns or rows. The
The "RENAME" command is used to rename the
existing table/columns or the rows of a table.Syntax: RENAME old_table _name To new_table_name;
-
b ->** DQL = DQL, stands for "DATA QUERY LANGUAGE". DQL
consists of instructions, used to fetch data from
the database. The DQL only uses the "SELECT"
command.
- SELECT -> It is the most frequently used SQL command, used
to select the data from a database. The result or
records after executing the query will be stored
in a result-set table.
Syntax: SELECT column1, column2, ...
FROM table_name;
If you want to select all the fields at, once use
(*) asterisk.
Syntax: SELECT * FROM table_name;
c ->** DML = DML, stands for "DATA MANIPULATION LANGUAGE".
DML is used to modify or bring some changes in the
existing database table. We can update the new
information or modify existing database records in
the table.
The changes made here are not automatically
saved, it can be rollback when needed.
We can modify or update our database through the
following commands in SQL.
-
INSERT -> This command is used to insert new records in the
database table.We can insert the data in two ways- a - By specifying column names (You don't need to specify the column name where the data will be inserted, you need only their values.) Syntax: INSERT INTO TABLE_NAME (col1, col2, col3,... col N) VALUES (value1, value2,..valueN); b - Without specifying column names (The second the method needs us to specify both the column name and values that you want to insert.) Syntax: INSERT INTO TABLE_NAME VALUES (value1, value2,...valueN);
-
UPDATE -> This command is used to update or change the row or
column value of the table.Syntax: UPDATE table_name SET [column_name1= value1,column_nameN = valueN] [WHERE CONDITION]
-
DELETE -> This command is used to delete one or more
records(rows) from the database table.Syntax: DELETE FROM table_name [WHERE condition];
-
LOCK -> The command is used to gain Exclusive(X) or
Shared(S) lock on the specified table. This lock
persists until the end of the transaction.Syntax: LOCK TABLE table-Name IN {SHARE | EXCLUSIVE MODE}
-
MERGE -> The "MERGE" command is popularly used to
perform the insert, update & delete operation just
in a single go. No need to write separate logic
for each of the operations.Syntax: MERGE target_table USING source_table ON merge_condition WHEN MATCHED THEN update_statement WHEN NOT MATCHED THEN insert_statement WHEN NOT MATCHED BY SOURCE THEN DELETE;
d ->** DCL = "DCL", stands for "DATA CONTROL LANGUAGE". This
command generally deals with the permission &
rights of a user.
The Database Administrator (DBA) uses the data
control language (DCL) to manage or authorize
the database access for other users in the table.
We can grant or revoke the permissions in the
database through the following commands in SQL.-
GRANT -> "GRANT" command is used to give access &
privilege to the user, by which he/she can make
some changes in the table.Syntax: GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
-
REVOKE -> "REVOKE" command is used to withdraw user access &
the privilege of the user.Syntax: REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
e ->** TCL = "TCL" stands for "TRANSACTION CONTROL LANGUAGE".
Consecutive, Read/Write operations in the
database is called "TRANSACTION".
It is used to manage our transactions so that we
can be sure that it is successfully carried out
& has not violated the integrity of the
database.
The following commands come under the TCL are: -
-
COMMIT -> "COMMIT", command is generally used to save all
the transaction to a database, after performing
all the operations on it.Syntax: COMMIT;
-
ROLLBACK -> "ROLLBACK", command is used to undo all the
transactions(operations) that have not already
been saved(committed) to the database.Syntax: ROLLBACK;
-
SAVEPOINTS -> "SAVEPOINTS", command works like a marker, used
to roll the transaction back to a certain point
without rolling back the entire transaction.Syntax: SAVEPOINT SAVEPOINT_NAME;
-
I hope you've enjoyed😎😎 this simple post on SQL commands.
Thank You! 👍
Top comments (0)