Basic SQL Concepts
What is SQL?
SQL (Structured Query Language) is a standard language for managing and manipulating databases. It's used to communicate with a database. Almost all relational database systems (like MySQL, PostgreSQL, Oracle, SQL Server, SQLite, etc.) use SQL as their standard database language.
Core SQL Commands
We'll cover some of the most fundamental SQL commands that form the backbone of database interaction.
SELECT Statement
The SELECT statement is used to query the database and retrieve data that matches criteria you specify. It's the most common SQL command.
Example: Retrieving all columns from a table
SELECT column1, column2, ...
FROM table_name;
To select all columns from a table named Customers:
SELECT *
FROM Customers;
WHERE Clause
The WHERE clause is used to filter records. It extracts only those records that fulfill a specified condition.
Example: Filtering records
Select customers from the 'USA':
SELECT CustomerName, City, Country
FROM Customers
WHERE Country = 'USA';
You can use various comparison operators like =, >, <, >=, <=, <> (or !=), LIKE, IN, BETWEEN.
INSERT INTO Statement
The INSERT INTO statement is used to add new records to a table.
Example: Inserting a new record
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Insert a new customer:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
UPDATE Statement
The UPDATE statement is used to modify existing records in a table.
Example: Updating existing records
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Update the city of the customer with the ID 1:
UPDATE Customers
SET City = 'Berlin'
WHERE CustomerID = 1;
DELETE Statement
The DELETE statement is used to delete existing records from a table.
Example: Deleting records
DELETE FROM table_name
WHERE condition;
Delete the customer with the name 'Alfreds Futterkiste':
DELETE FROM Customers
WHERE CustomerName = 'Alfreds Futterkiste';
Caution: If you omit the WHERE clause, all records in the table will be deleted!
Other Essential Concepts
- Primary Keys: A column (or set of columns) that uniquely identifies each row in a table.
- Foreign Keys: A column (or set of columns) in one table that refers to the primary key in another table, establishing a link between tables.
- JOINs: Used to combine rows from two or more tables based on a related column between them.
In the next tutorial, we'll delve deeper into Data Manipulation Language (DML) operations.