SQL Commands

In SQL, commands are grouped into four main categories based on their functionality: DML, DCL, TCL, and DDL.

Data Manipulation Language (DML)

These commands are used for managing data within schema objects.

SELECT

Retrieves data from a database.

INSERT

Adds new data to a table.

Syntax

-- Inserting data into all columns
INSERT INTO table_name VALUES (value1, value2, value3, ...);

-- Inserting data into specific columns
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example

-- Assuming a table 'users' with columns id, name, and email
INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', 'john.doe@example.com');

UPDATE

Modifies existing data within a table. You can update one or more columns for all rows or specific rows that meet a condition.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

-- Update the email of the user with id 1
UPDATE users
SET email = 'john.new@example.com'
WHERE id = 1;

DELETE

Removes data from a database. Be cautious when using DELETE without a WHERE clause, as it will remove all rows from the table.

Syntax

DELETE FROM table_name WHERE condition;

Example

-- Delete the user with id 1
DELETE FROM users WHERE id = 1;

Data Definition Language (DDL)

These commands are used to define and modify the database structure.

• CREATE Creates a new table, view, index, or other database object.

• ALTER Modifies an existing database object.

• DROP Deletes an existing database object.

• TRUNCATE Removes all records from a table, including all spaces allocated for the records.

Data Control Language (DCL)

These commands are used to control access to data within the database.

• GRANT Gives user's access privileges to the database.

• REVOKE Takes back permissions granted to a user.

Transaction Control Language (TCL)

These commands are used to manage transactions in the database.

• COMMIT Saves all transactions to the database.

• ROLLBACK Undoes transactions that have not yet been saved to the database.

• SAVEPOINT Sets a point within a transaction to which you can later roll back.