SQL (Structured Query Language) Overview:
SQL is a language used to manage data stored in relational databases.
SQL allows us to perform CRUD operations:
CREATE: Inserts new data (tuples) into a table.
READ: Retrieves data using SELECT.
UPDATE: Modifies existing data.
DELETE: Deletes specific data or rows from the table.
Example:
-- Create a table
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(100), age INT);
-- Insert data
INSERT INTO students (id, name, age) VALUES (1, 'John', 20);
-- Read data
SELECT * FROM students;
-- Update data
UPDATE students SET age = 21 WHERE id = 1;
-- Delete data
DELETE FROM students WHERE id = 1;
SQL vs MySQL:
SQL is the query language used to interact with databases.
MySQL is a type of RDBMS (Relational Database Management System) that uses SQL to
store and manage data.
MySQL uses a client-server model where the client (could be a frontend or command
line) interacts with the MySQL server to manage databases.
Example:
SQL is the language:
SELECT * FROM users; -- This is SQL syntax
MySQL is the RDBMS where the SQL query is run.
RDBMS (Relational Database Management System):
RDBMS is software that helps manage databases based on a relational model (tables
with rows and columns).
Examples: MySQL, MS SQL, Oracle, IBM DB2.
Example:
-- Using MySQL to create and manage tables
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), department
VARCHAR(50));
SQL Data Types:
SQL uses different data types to define the type of data stored in columns.
Common Data Types:
INT: Integer numbers.
VARCHAR(size): Variable-length string (up to a defined size).
DATE: Stores date values in YYYY-MM-DD format.
FLOAT/DOUBLE: For decimal numbers with precision.
Example:
CREATE TABLE products (
id INT,
name VARCHAR(100),
price DOUBLE,
created_at DATE
);
Types of SQL Commands:
SQL commands are divided into various categories:
1. DDL (Data Definition Language):
Used to define and modify the structure of database objects (tables, databases).
Examples: CREATE, ALTER, DROP, TRUNCATE.
-- Create a table
CREATE TABLE orders (id INT PRIMARY KEY, product_name VARCHAR(100));
-- Modify table structure
ALTER TABLE orders ADD column order_date DATE;
-- Drop the table
DROP TABLE orders;
2. DML (Data Manipulation Language):
Deals with data modification in the database.
Examples: INSERT, UPDATE, DELETE.
-- Insert data
INSERT INTO employees (id, name, department) VALUES (1, 'Alice', 'IT');
-- Update data
UPDATE employees SET name = 'Alicia' WHERE id = 1;
-- Delete data
DELETE FROM employees WHERE id = 1;
3. DQL/DRL (Data Query Language):
Retrieves data from the database.
Example: SELECT.
SELECT * FROM employees WHERE department = 'IT';
4. DCL (Data Control Language):
Controls access to data.
Examples: GRANT, REVOKE.
-- Grant access to a user
GRANT SELECT, UPDATE ON employees TO 'username';
-- Revoke access
REVOKE SELECT, UPDATE ON employees FROM 'username';
5. TCL (Transaction Control Language):
Manages transactions in the database.
Examples: COMMIT, ROLLBACK, SAVEPOINT.
START TRANSACTION;
-- Perform some operations
INSERT INTO employees VALUES (2, 'Bob', 'HR');
-- Commit the changes
COMMIT;
SQL Constraints:
Constraints are used to enforce rules at the table level.
Common Constraints:
Primary Key: Uniquely identifies each row.
Foreign Key: Links data between two tables.
UNIQUE: Ensures all values in a column are unique.
CHECK: Ensures a condition is met for each row.
DEFAULT: Provides a default value for a column if no value is specified.
Example:
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount DOUBLE CHECK (amount > 0),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
SQL Joins:
Joins are used to combine rows from two or more tables based on a related column.
Types of Joins:
INNER JOIN: Returns rows with matching values in both tables.
LEFT JOIN: Returns all rows from the left table and matching rows from the right
table.
RIGHT JOIN: Returns all rows from the right table and matching rows from the left
table.
FULL JOIN: Returns rows when there is a match in either the left or right table.
CROSS JOIN: Returns the Cartesian product of both tables (every combination).
Example:
-- Inner Join
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
-- Left Join
SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
Pattern Searching (LIKE, Wildcards):
LIKE and wildcards are used for pattern matching in SQL.
%: Represents zero or more characters.
_: Represents a single character.
Example:
-- Select names starting with 'J'
SELECT * FROM employees WHERE name LIKE 'J%';
-- Select names with 5 characters
SELECT * FROM employees WHERE name LIKE '_____';
Grouping and Aggregation:
GROUP BY is used to group rows that share the same values in specified columns.
Aggregation functions like COUNT(), SUM(), AVG(), MIN(), MAX() are often used with
GROUP BY.
Example:
-- Group employees by department and count them
SELECT department, COUNT(*) FROM employees GROUP BY department;
SQL Data Retrieval (SELECT):
Retrieves data from the table.
Key Clauses:
WHERE: Filters rows based on a condition.
ORDER BY: Sorts result in ascending or descending order.
DISTINCT: Returns unique values.
Example:
-- Select distinct departments
SELECT DISTINCT(department) FROM employees;
-- Select employees, sorted by name
SELECT * FROM employees ORDER BY name ASC;
