SQL (Structured Query Language) is the standard programming language used for managing and manipulating Relational Database Management Systems (RDBMS), such as PostgreSQL, MySQL, SQL Server, and Oracle.
It is the language that allows you to store, retrieve, update, and delete the structured data your application needs.
1. Core Database Concepts
Before diving into the commands, it’s important to understand the structure of a relational database:
- Database: A container holding related data, organized into tables.
- Table (Relation): A collection of data organized into rows and columns, similar to a spreadsheet.
- Column (Field/Attribute): Represents a specific type of data (e.g.,
user_name,product_price). It defines the data type for the values it holds. - Row (Record/Tuple): Represents a single, complete entry in the table (e.g., one user, one product).
2. The Four Main SQL Categories
SQL commands are typically grouped into four main sub-languages, defining the type of action you want to perform:
A. Data Definition Language (DDL)
Used to define the database structure or schema.
| Command | Purpose | Example |
|---|---|---|
CREATE | Creates new database objects (tables, indexes, etc.). | CREATE TABLE Customers (...) |
ALTER | Modifies an existing database object. | ALTER TABLE Customers ADD email VARCHAR(100) |
DROP | Deletes an entire database object. | DROP TABLE Customers |
B. Data Manipulation Language (DML)
Used to manage data within schema objects. These are the commands you will use most often in your application logic.
| Command | Purpose |
|---|---|
SELECT | Retrieves data from the database. (The most common command) |
INSERT | Adds new rows of data to a table. |
UPDATE | Modifies existing data within a table. |
DELETE | Removes rows from a table. |
C. Data Control Language (DCL)
Used to control access to the data.
| Command | Purpose |
|---|---|
GRANT | Gives user access privileges to database objects. |
REVOKE | Removes user access privileges. |
D. Transaction Control Language (TCL)
Used to manage transactions, ensuring data integrity.
| Command | Purpose |
|---|---|
COMMIT | Saves all work done in a transaction permanently. |
ROLLBACK | Undoes changes since the last COMMIT. |
3. Essential DML Commands (The CRUD Operations)
Let’s assume we have a table called Products defined like this:
| Column | Data Type | Constraint |
|---|---|---|
| id | INTEGER | PRIMARY KEY |
| name | VARCHAR(255) | NOT NULL |
| price | DECIMAL | NOT NULL |
| stock | INTEGER |
1. INSERT (Create) – Adding Data
This command adds new rows (records) to the table.
INSERT INTO Products (id, name, price, stock)
VALUES (101, 'Wireless Mouse', 24.99, 150);
INSERT INTO Products (id, name, price, stock)
VALUES (102, 'Mechanical Keyboard', 79.99, 80);
2. SELECT (Read) – Retrieving Data
This is the most powerful and flexible command, used to fetch data based on specific criteria.
| Query | Description |
|---|---|
SELECT * FROM Products; | Fetches all columns (*) and all rows from the Products table. |
SELECT name, price FROM Products WHERE price > 50; | Fetches only the name and price columns for products where the price is greater than 50. |
SELECT name FROM Products ORDER BY price DESC; | Fetches the name, ordered from most expensive to least expensive. |
3. UPDATE – Modifying Data
This command changes the values in existing rows. The WHERE clause is crucial to specify which rows to change.
-- Increase the stock of the Wireless Mouse (id 101) by 50 units
UPDATE Products
SET stock = stock + 50
WHERE id = 101;
-- Change the price of all items over $70 to $69.99
UPDATE Products
SET price = 69.99
WHERE price > 70;
4. DELETE – Removing Data
This command removes entire rows from a table. Be careful! If you omit the WHERE clause, you will delete all rows in the table.
-- Delete the Mechanical Keyboard (id 102)
DELETE FROM Products
WHERE id = 102;
-- DANGER: Deletes ALL products if run without a WHERE clause
-- DELETE FROM Products;
The DML commands (SELECT, INSERT, UPDATE, DELETE) directly map to the CRUD (Create, Read, Update, Delete) operations that form the backbone of almost every web application you will build.
Now that you have the basic structure of SQL, the next step is typically learning how to connect your Spring Boot application to a database using technologies like JPA (Java Persistence API) and Hibernate.
