Essential SQL Commands: A Quick Reference Guide

I recently took some time to recap the SQL commands I use the most, and I decided to put together a few notes while I was at it. Nothing fancy—just a quick, practical list of the commands that come up often in my day-to-day work. Sharing them here in case they’re useful to anyone else who wants a simple reference or a refresher.

Tip: If you're looking to dive deep into MySQL, I highly recommend the MySQL Crash Course by O'Reilly — it’s the one I took and found incredibly helpful.

Query Filtering & Sorting

Commands for filtering and sorting the results of a SELECT query.

The * wildcard in SQL selects all columns from a table without listing them individually.

Returns all columns from the users table (e.g., id, name, email, age, etc.).

SELECT * FROM users;

In SQL, the LIKE operator is commonly used to search for a specified pattern in a column. It's especially handy when you want to filter results based on partial matches.

Comparison Operators

Used in WHERE clauses to compare values or test specific conditions in SQL queries.

MySQL Data Types (Quick Reference)

A concise guide to the different data types supported in MySQL, including numeric, string, date/time, and special types.

This example demonstrates how various MySQL data types can be used when creating a user_profile table:

CREATE TABLE user_profile (

    user_id         INT AUTO_INCREMENT PRIMARY KEY,

    username        VARCHAR(50) NOT NULL,

    email           VARCHAR(100) UNIQUE NOT NULL,

    account_status  ENUM('active', 'inactive', 'suspended') DEFAULT 'active',

    preferences     JSON,

    is_verified     BOOLEAN DEFAULT FALSE,

    signup_date     DATETIME DEFAULT CURRENT_TIMESTAMP

);

Joins

Commands to select data from multiple tables.

Example: Given two tables, one containing city names with their country codes and the other containing country names with their country codes, we can use SQL JOIN operations to select and combine data from both tables.

Cities table:

Countries table:

Examples Using Different Joins:

SELECT c.city_name, co.country_name

FROM cities AS c

INNER JOIN countries AS co

ON c.country_code = co.country_code;

This uses c as the alias for cities and co for countries.

We can replace INNER JOIN in the query above with LEFT JOIN, RIGHT JOIN, or use a FULL OUTER JOIN (with a workaround in MySQL) to see different results based on the type of join.

• INNER JOIN: Returns only the cities with a matching country_code in both tables.
  
  Result: Tokyo/Japan, and Sydney/Australia and Dublin/Ireland.




• LEFT JOIN: Returns all the rows from the left table (cities), and the matching rows from the right table (countries). If there is no match in the right table, the query will return NULL for the columns from the right table.
  
  Result: All cities, but New York, Paris, and Los Angeles with NULL in the country_namecolumn.




• RIGHT JOIN: Returns all rows from the right table (countries) and the matching rows from the left table (cities).
  
  Result: Dublin, Tokyo and Sydney with their country names and NULL as the city_name for Italy.

Database Management Commands

These commands are used for creating, managing, and deleting databases and tables.

Data Manipulation Commands

Commands to insert, update, delete, and select data in tables.

Inserting multiple rows at once:

INSERT INTO users (name, age)

VALUES

  ('John', 25),

  ('Jane', 30),

  ('Bob', 22),

  ('Alice', 28);

Aggregate Functions

Functions that return aggregated values such as sums, averages, counts, etc.

Transactions

Commands to manage database transactions and maintain data integrity.

Here’s a simple example demonstrating how transactions work:

START TRANSACTION;



UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;



-- If everything is fine, then run:

-- COMMIT;



-- If something goes wrong, then run:

-- ROLLBACK;

Explanation: This example simulates a money transfer between two users. The START TRANSACTION begins a transaction block. If both UPDATE statements succeed, COMMIT saves the changes. If an error occurs (e.g., user_id 2 doesn’t exist), ROLLBACK undoes both updates to maintain data integrity.

Conclusion

SQL is an incredibly powerful tool for interacting with databases, and understanding the essential commands is key to writing efficient and effective queries. By revisiting and practicing these commonly used commands, such as those for filtering, sorting, joining tables, and managing transactions, we can significantly improve our workflow and avoid common pitfalls. 😊