MSDN Documentation

Introduction to SQL Statements

SQL (Structured Query Language) statements are commands used to communicate with and manipulate a database. They form the backbone of database interaction, allowing you to perform a wide range of operations, from retrieving data to modifying its structure.

SQL statements can be broadly categorized into several types:

• Data Definition Language (DDL): Used to define and manage database structures.
• Data Manipulation Language (DML): Used to manage data within schema objects.
• Data Control Language (DCL): Used to control access to data.
• Transaction Control Language (TCL): Used to manage transactions.

Data Definition Language (DDL)

DDL statements are used to create, alter, and drop database objects. These statements affect the schema of the database.

• CREATE: Used to create new database objects like tables, views, indexes, and stored procedures.
• ALTER: Used to modify existing database objects.
• DROP: Used to delete database objects.
• TRUNCATE: Used to remove all records from a table quickly.
• RENAME: Used to rename an object.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP
);
                        

Data Manipulation Language (DML)

DML statements are used to retrieve, insert, update, and delete data in database tables.

• SELECT: Used to retrieve data from one or more tables.
• INSERT: Used to add new rows of data into a table.
• UPDATE: Used to modify existing data in a table.
• DELETE: Used to remove rows of data from a table.

-- Insert a new customer
INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('John', 'Doe', 'john.doe@example.com');

-- Select all customers
SELECT CustomerID, FirstName, LastName, Email
FROM Customers;
                        

-- Update a customer's email
UPDATE Customers
SET Email = 'jane.doe.updated@example.com'
WHERE CustomerID = 1;

-- Delete a customer
DELETE FROM Customers
WHERE CustomerID = 2;
                        

Data Control Language (DCL)

DCL statements are used to grant and revoke permissions from database users.

• GRANT: Used to give specific privileges to users.
• REVOKE: Used to remove specific privileges from users.

GRANT SELECT, INSERT ON Customers TO 'readonly_user'@'localhost';
REVOKE DELETE ON Customers FROM 'readonly_user'@'localhost';
                        

Transaction Control Language (TCL)

TCL statements manage the changes made during a transaction, ensuring data integrity.

• COMMIT: Saves all the transactions to the database.
• ROLLBACK: Undoes all the transactions since the last COMMIT or ROLLBACK.
• SAVEPOINT: Sets a point within a transaction to which you can later roll back.

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT; -- Or ROLLBACK if an error occurred
                        

Key Concepts

Understanding the syntax and purpose of these statements is crucial for effective database management. Always refer to the specific SQL dialect documentation (e.g., T-SQL for SQL Server, PL/SQL for Oracle, standard SQL) for precise syntax and available options.