Introduction
In this article, we will learn about the important SQL commands which are commonly used in every project, with explanations. Let's start with these commands,
DDL Commands
The various DDL commands are CREATE, ALTER, and DROP. Let us learn about each DDL Command one by one with examples. Please go through it if you are unaware of the various types of SQL DDL Commands.
CREATE TABLE
Using CREATE TABLE you can create a new table in the database. You can set the table name and column name in the table.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
ALTER TABLE
The ALTER TABLE is used to add, delete, or modify columns in a table.
ALTER TABLE table_name
ADD column_name datatype;
DROP Table
The DROP TABLE is used to drop an existing table in a database.
DROP TABLE table_name;
TRUNCATE Table
The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.
TRUNCATE TABLE table_name;
COMMENT
When we do not want our code to be executed we comment on them.
There are three types of comments in SQL,
- Single-line comments.
- Multi-line comments
- Inline comments
Single line comments
-- single line comment example
SELECT * FROM employees;
Multi-line comments
/* multi line comment line 1
line 2 */
SELECT * FROM employees;
Inline comments
SELECT * FROM /* employees; */
DML Commands
DML is an abbreviation of Data Manipulation Language. It clearly shows its functionality by its name, which means what is going to perform in the database. Manipulation simply indicates that it is going to perform some task i.e: Alter, Delete, Edit the data in the database.
Let us learn about each DDL Command one by one with examples. Please go through it if you are unaware of the various types of SQL DML Commands.
SELECT
SELECT statements are used to fetch data from a database. Every query will begin with SELECT.
SELECT column_name
FROM table_name;
INSERT
INSERT statements are used to add a new row to a table.
INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, 'value_2', value_3);
UPDATE
UPDATE statements allow you to edit rows in a table.
UPDATE table_name
SET column_name = new_value
WHERE column_name = old_value;
DELETE
DELETE statements are used to remove rows from a table.
DELETE FROM table_name
WHERE column_name = column_value;
DCL Commands
SQL provides us with 2 DCL Commands - GRANT and REVOKE. Let us learn about each DDL Command one by one with examples. Please go through it if you are unaware of the various types of SQL DCL Commands.
GRANT
GRANT is used to provide access or privileges on the database objects to the users.
GRANT privileges ON object TO user;
REVOKE
REVOKE command removes user access rights or privileges to the database objects.
REVOKE privileges ON object FROM user;
TCL Commands
SQL provides us with 3 TCL Commands - COMMIT, ROLLBACK, and SAVEPOINT.
COMMIT
COMMIT is used to save changes by a transaction to the database.
COMMIT;
ROLLBACK
The ROLLBACK command is used to undo transactions that have not been saved to the database. The command is only be used to undo changes since the last COMMIT.
ROLLBACK;
SAVEPOINT
SAVEPOINT command is used to temporarily save a transaction to a point so that you can roll back to that point whenever required.
SAVEPOINT SAVEPOINT_NAME;
Other Useful Commands
Here are some other commands of SQL with examples.
AND
And is used to combine two conditions. Both conditions must be true to display the record.
SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;
AS
With the help of AS you can rename a column or table using an alias.
SELECT column_name AS 'Alias_Example'
FROM table_name;
AVG()
AVG() function returns the average value of a numeric column.
SELECT AVG(column_name)
FROM table_name;
BETWEEN
BETWEEN the operator selects values (values can be numbers, text, or dates) within a given range.
SELECT column1
FROM table_name
WHERE column1 BETWEEN value1 AND value2;
CASE
In SQL we are using CASE like an if-then-else statement.
SELECT column_name,
CASE
WHEN condition THEN 'Result1'
WHEN condition THEN 'Result2'
ELSE 'Result3'
END
FROM table_name;
COUNT()
The COUNT() function returns the number of rows in a column.
SELECT COUNT(column_name)
FROM table_name;
GROUP BY
The GROUP BY statement groups rows that show identical data into groups.
SELECT column_name1, column_name2
FROM table_name
GROUP BY column_name1;
HAVING
WHERE keyword cannot be used with aggregate functions that's why the HAVING clause was added to SQL.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition;
INNER JOIN
An inner join will combine rows that have matching values in both tables.
SELECT column_name
FROM table1
INNER JOIN table2
On table1.column_name = table2.column_name;
IS NULL / IS NOT NULL
We used IS NULL and IS NOT NULL with the WHERE clause to test if the value is empty or not.
IS NULL Syntax,
SELECT column_name
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL Syntax,
SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;
LIKE
LIKE is a special operator used to search for a specific pattern in a column with the WHERE clause.
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;
MAX()
MAX() is a function that returns the largest value in that column.
SELECT MAX(column_name)
FROM table_name;
MIN()
MIN() is a function that returns the smallest value in that column.
SELECT MIN(column_name)
FROM table_name;
OR
The OR operator displays the result where one of two conditions is true.
SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;
ORDER BY
ORDER BY is used to sort the result in ascending or descending order. By default, it sorts the records in ascending order or descending order, we will use the DESC keyword.
SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;
ROUND()
The ROUND function returns a number rounded to a certain number of decimal places.
SELECT ROUND(column_name, integer)
FROM table_name;
SELECT DISTINCT
The SELECT DISTINCT statement is used to return unique values in the specified column(s).
SELECT DISTINCT column_name
FROM table_name;
SUM
The SUM() function returns the total sum of a column.
SELECT SUM(column_name)
FROM table_name;
WHERE
WHERE clause is used to filter records.
SELECT column1, column2
FROM table_name
WHERE condition;
Conclusion
In this article, we learned about the Most Important SQL Commands. If you want to download the cheatsheet of all these commands please download it from the source code.