Cheatsheets: HTML, CSS, JavaScript, PHP, SQL, Git and RegExp

Select All

The SELECT statement is used to retrieve data from one or more tables in a database.

Retrieve all information about customers.

SELECT * FROM customers;

Retrieve the names and emails of all customers.

SELECT name, email FROM customers;

INSERT Statements

The `INSERT` statement is used to add new rows to a table.

Add a new customer to the database.

INSERT INTO customers (name, email, date_of_birth) 
VALUES ('John Doe', 'john.doe@example.com', '1980-05-15');

UPDATE Statements

The `UPDATE` statement is used to modify existing records in a table.

Update the email address of a customer.

UPDATE customers 
SET email = 'john.newemail@example.com' 
WHERE name = 'John Doe';

DELETE Statements

The `DELETE` statement is used to remove one or more rows from a table.

Remove a customer from the database.

DELETE FROM customers 
WHERE name = 'John Doe';

Nested SELECT Queries

Nested `SELECT` queries, also known as subqueries, are used to perform operations that require querying another result set.

Find customers who have the same email domain as 'example.com' and add best friends name.

SELECT 
  ( SELECT best_friend.firstname
    WHERE best_friend.id = customers.best_friend_id
    LIMIT 1
  ) as best_friend_name,
  email 
FROM customers 
WHERE SUBSTRING_INDEX(email, '@', -1) = (SELECT SUBSTRING_INDEX('example.com', '@', -1));

Working with JSON Data

In modern databases, you can store and query JSON data directly within a table. Assume we have a `customer_details` column in JSON format in the `customers` table.

Retrieve the city from the JSON data.

SELECT JSON_EXTRACT(customer_details, '$.address.city') AS city 
FROM customers;

Advanced Search Techniques

Advanced search techniques can help you perform more complex queries.

Find customers whose names start with 'J' and are older than 30 years.

SELECT name, email 
FROM customers 
WHERE name LIKE 'J%' 
  AND date_of_birth < DATE_SUB(CURDATE(), INTERVAL 30 YEAR);

INSERT Statements

The INSERT statement is used to add new rows to a table.

Add a new customer to the database.

INSERT INTO customers (name, email, date_of_birth) 
VALUES ('John Doe', 'john.doe@example.com', '1980-05-15');

Introduction to Joins

Joins are used to combine rows from two or more tables based on a related column between them.

Join the `customers` and `orders` tables to get a list of customers and their orders.

SELECT customers.name, orders.order_id, orders.order_date 
FROM customers 
JOIN orders ON customers.customer_id = orders.customer_id;

INNER JOIN

The `INNER JOIN` returns records that have matching values in both tables.

Retrieve customers who have placed orders.

SELECT customers.name, orders.order_id 
FROM customers 
INNER JOIN orders ON customers.customer_id = orders.customer_id;

LEFT JOIN

The `LEFT JOIN` returns all records from the left table and the matched records from the right table. The result is NULL from the right side if there is no match.

Retrieve all customers, and if they have placed orders, include the order details.

SELECT customers.name, orders.order_id 
FROM customers 
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

RIGHT JOIN

The `RIGHT JOIN` returns all records from the right table and the matched records from the left table. The result is NULL from the left side if there is no match.

Retrieve all orders, and if there is a customer associated, include the customer details.

SELECT customers.name, orders.order_id 
FROM customers 
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

FULL JOIN

The `FULL JOIN` returns all records when there is a match in either left or right table records.

Retrieve all customers and all orders, matching where possible.

SELECT customers.name, orders.order_id 
FROM customers 
FULL JOIN orders ON customers.customer_id = orders.customer_id;

CROSS JOIN

The `CROSS JOIN` returns the Cartesian product of the two tables. It combines each row of the first table with each row of the second table.

Generate a list of all possible combinations of customers and orders.

SELECT customers.name, orders.order_id 
FROM customers 
CROSS JOIN orders;

Self Joins

A self join is a regular join but the table is joined with itself.

Find customers who share the same city.

SELECT a.name AS Customer1, b.name AS Customer2 
FROM customers a 
JOIN customers b ON a.city = b.city AND a.customer_id <> b.customer_id;

Handling NULL Values

NULL values represent missing or unknown data. Proper handling of NULL values is essential for accurate queries.

Find customers who have not placed any orders.

SELECT customers.name 
FROM customers 
LEFT JOIN orders ON customers.customer_id = orders.customer_id 
WHERE orders.order_id IS NULL;

Using IS NULL and IS NOT NULL

The `IS NULL` and `IS NOT NULL` operators are used to test for NULL values.

Find all orders that do not have an associated customer (hypothetically).

SELECT order_id 
FROM orders 
WHERE customer_id IS NULL;

Find all customers who have provided their email addresses.

SELECT name 
FROM customers 
WHERE email IS NOT NULL;

Temporary Tables

Temporary tables are used to store intermediate results temporarily during a session.

Create a temporary table to store the results of a complex query.

CREATE TEMPORARY TABLE temp_sales AS
SELECT customer_id, SUM(order_amount) AS total_sales
FROM orders
GROUP BY customer_id;

Common Table Expressions (CTE)

CTEs create temporary result sets that can be referenced within a `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement.

Use a CTE to simplify a complex query.

WITH SalesCTE AS (
    SELECT customer_id, SUM(order_amount) AS total_sales
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_sales
FROM SalesCTE
WHERE total_sales > 1000;

Recursive CTE

Recursive CTEs are used to work with hierarchical data, such as organizational charts or folder structures.

Use a recursive CTE to list all subordinates of a manager.

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, employee_name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Window Functions

Window functions perform calculations across a set of table rows related to the current row.

Calculate a running total of sales for each customer.

SELECT customer_id, order_date, order_amount,
       SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

Stored Procedures

Stored procedures encapsulate SQL code for reuse and can include logic and control-of-flow constructs.

Create a stored procedure to add a new customer and return the customer ID.

DELIMITER //

CREATE PROCEDURE AddCustomer (IN name VARCHAR(50), IN email VARCHAR(50), OUT customer_id INT)
BEGIN
    INSERT INTO customers (name, email) VALUES (name, email);
    SET customer_id = LAST_INSERT_ID();
END //

DELIMITER ;

Triggers

Triggers automatically execute SQL code in response to certain events on a table.

Create a trigger to log changes to the orders table.

CREATE TRIGGER log_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, old_status, new_status, changed_at)
    VALUES (OLD.order_id, OLD.status, NEW.status, NOW());
END;

User-Defined Functions (UDF)

UDFs allow you to create custom functions to perform operations not covered by built-in functions.

Create a UDF to calculate the discount based on the total order amount.

CREATE FUNCTION CalculateDiscount (total_amount DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE discount DECIMAL(10, 2);
    IF total_amount > 500 THEN
        SET discount = total_amount * 0.1;
    ELSE
        SET discount = 0;
    END IF;
    RETURN discount;
END;

Event Scheduler

The event scheduler allows you to automate tasks to run at specified times or intervals.

Create an event to archive old orders monthly.

CREATE EVENT archive_old_orders
ON SCHEDULE EVERY 1 MONTH
DO
    INSERT INTO archived_orders
    SELECT * FROM orders WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);
    DELETE FROM orders WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

Partitioning Tables

Partitioning tables can improve performance and manageability by dividing a large table into smaller, more manageable pieces.

Partition an orders table by year.

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_amount DECIMAL(10, 2),
    order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

Query Hints

Query hints provide instructions to the optimizer to influence the execution plan of a query.

Use a query hint to limit the execution time of a query.

SELECT /*+ MAX_EXECUTION_TIME(1000) */ *
FROM orders
WHERE order_amount > 100;

Virtual Columns

Virtual columns are computed based on other columns and do not store the data physically.

Create a virtual column for the total price with tax.

CREATE TABLE products (
    product_id INT,
    price DECIMAL(10, 2),
    tax_rate DECIMAL(5, 2),
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

Full-Text Search

Full-text search enables efficient text searching within MySQL.

Create a full-text index and search for a term in product descriptions.

CREATE FULLTEXT INDEX idx_description ON products(description);

SELECT * FROM products
WHERE MATCH(description) AGAINST('search term');

Spatial Data Types and Functions

MySQL supports spatial data types for geographic information systems (GIS).

Store and retrieve spatial data.

CREATE TABLE locations (
    id INT,
    name VARCHAR(50),
    coordinates POINT
);

INSERT INTO locations (name, coordinates)
VALUES ('Location A', ST_GeomFromText('POINT(1 1)'));

SELECT id, name, ST_AsText(coordinates) FROM locations;

Indexing

Indexes improve the speed of data retrieval operations by providing a quick way to look up rows.

Create an index on the `email` column in the `customers` table to speed up search queries.

CREATE INDEX idx_email ON customers(email);

Query Optimization

Optimizing queries involves using best practices and tools to make SQL queries run faster.

Use the `EXPLAIN` statement to analyze a query execution plan.

EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01';

Caching

Caching stores frequently accessed data in memory for quicker retrieval.

Enable query caching in MySQL.

SET GLOBAL query_cache_size = 1048576;
SET GLOBAL query_cache_type = 1;

Database Normalization

Normalization organizes data to reduce redundancy and improve data integrity.

Normalize a customer orders database to third normal form (3NF).

-- Create separate tables for customers and orders
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_amount DECIMAL(10, 2),
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Data Security

Securing data involves protecting it from unauthorized access and ensuring privacy.

Grant specific privileges to a user for enhanced security.

GRANT SELECT, INSERT ON database_name.* TO 'username'@'host' IDENTIFIED BY 'password';

Data Encryption

Data encryption ensures that data is protected during transmission and storage.

Enable SSL for MySQL connections.

-- Configuration settings in MySQL my.cnf
[mysqld]
ssl-ca=/path/to/ca.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

Backup and Recovery

Regular backups ensure data can be recovered in case of a failure.

Perform a backup using the `mysqldump` tool.

mysqldump -u username -p database_name > backup_file.sql

Monitoring and Profiling

Monitoring and profiling tools help track database performance and identify bottlenecks.

Use the `slow_query_log` to identify slow-running queries.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;