Introduction to SQL
SQL (Structured Query Language) is a standard language for managing relational databases. It enables operations such as data retrieval, insertion, modification, and deletion.
SQL Syntax Basics
SQL is case-insensitive for keywords. Semicolons are used to separate statements in many database systems.
Essential SQL Commands
| Command | Purpose |
|---|---|
| SELECT | Retrieve data from database |
| UPDATE | Modify existing data |
| DELETE | Remove data records |
| INSERT INTO | Add new data records |
| CREATE DATABASE | Initialize new database |
| ALTER DATABASE | Modify database properties |
| CREATE TABLE | Define new table structure |
| ALTER TABLE | Change table schema |
| DROP TABLE | Remove table entirely |
| CREATE INDEX | Build search optimization index |
| DROP INDEX | Remove existing index |
Core Database Concepts
Database Terminology
- DB: Physical storage of data as files
- DBMS: Software system managing databases
- SQL: Language for DBMS communication
Table Structure
Tables consist of rows (records) and columns (fields with name, type, and constraints).
SQL Language Categories
- DQL: Data query operations (SELECT)
- DML: Data manipulation (INSERT, UPDATE, DELETE)
- DDL: Schema definition (CREATE, ALTER, DROP)
- TPL: Transaction management (COMMIT, ROLLBACK)
- DCL: Access control (GRANT, REVOKE)
Database Connection Example
mysql -h127.0.0.1 -P3306 -uadmin -p
Enter password: ******
Character Set Configurasion
Use SET NAMES to configure client/server character set alignment:
SET NAMES 'utf8mb4';
Database Operations
Creating Databases
CREATE DATABASE inventory
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
Modifying Databases
ALTER DATABASE inventory
CHARACTER SET latin1;
Data Query Language
Basic Retrieval
SELECT product_name, unit_price
FROM products;
Conditional Filtering
SELECT employee_name, department
FROM staff
WHERE salary > 50000
AND hire_date > '2020-01-01';
Result Ordering
SELECT customer_name, total_orders
FROM clients
ORDER BY total_orders DESC;
Single-Row Functions
SELECT UPPER(product_name),
ROUND(unit_price * 1.1, 2)
FROM products;
Aggregate Functions
SELECT department,
AVG(salary),
COUNT(*)
FROM employees
GROUP BY department;
Join Operations
SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id;
Subqueries
SELECT product_name
FROM products
WHERE category_id IN (
SELECT category_id
FROM categories
WHERE active = 1
);
Result Limiting
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 20;
Complete Query Structure
SELECT column1, aggregate(column2)
FROM table1
WHERE condition1
GROUP BY column1
HAVING aggregate(column2) > value
ORDER BY column3
LIMIT 10;