Essential SQL Commands for Every Developer

In today’s data-driven world, SQL stands as a cornerstone for modern development. With over 7 million developers worldwide leveraging SQL, its importance cannot be overstated. Whether you’re building applications or analyzing data, SQL provides the essential tools to manage and manipulate databases efficiently. In fact, 89% of developers use SQL at work, underscoring its ubiquity and necessity. This guide emphasizes practical application and best practices, ensuring you can harness the full power of SQL in your projects. For those seeking a quick reference, our SQL cheat sheet will be invaluable.

Basic SQL Commands

SELECT Statement

The SELECT statement is the cornerstone of SQL, enabling you to retrieve data from your database. Mastering this command is essential for any developer working with SQL.

Basic Syntax

The basic syntax of a SELECT statement is straightforward:

SELECT column1, column2, ...
FROM table_name;

For instance, to fetch all columns from a table named employees, you would use:

SELECT * FROM employees;

This command retrieves every row and column from the employees table, making it a powerful tool for data exploration.

Filtering Results with WHERE

To narrow down your results, the WHERE clause is indispensable. It allows you to specify conditions that the data must meet to be included in the result set.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

For example, if you want to find employees who work in the ‘Sales’ department, you would write:

SELECT * FROM employees
WHERE department = 'Sales';

This command filters the rows to include only those where the department is ‘Sales’.

Sorting Results with ORDER BY

The ORDER BY clause lets you sort the result set by one or more columns, either in ascending (ASC) or descending (DESC) order.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

To sort employees by their hire date in descending order, you would use:

SELECT * FROM employees
ORDER BY hire_date DESC;

This ensures that the most recently hired employees appear first in the result set.

INSERT Statement

The INSERT statement is used to add new rows to a table. This command is fundamental for populating your database with data.

Basic Syntax

The basic syntax for inserting a single row is:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

For example, to add a new employee to the employees table, you might write:

INSERT INTO employees (name, department, hire_date)
VALUES ('John Doe', 'Engineering', '2023-01-15');

This command inserts a new row with the specified values into the employees table.

Inserting Multiple Rows

You can also insert multiple rows in a single INSERT statement, which can be more efficient than inserting each row individually.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...), (value3, value4, ...), ...;

For instance, to add multiple employees at once:

INSERT INTO employees (name, department, hire_date)
VALUES 
  ('Alice Smith', 'Marketing', '2023-02-01'),
  ('Bob Johnson', 'Sales', '2023-02-05');

This command adds two new rows to the employees table in a single operation.

UPDATE Statement

The UPDATE statement is used to modify existing records in a table. This command is crucial for keeping your data up-to-date.

Basic Syntax

The basic syntax for updating a single row is:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

For example, to update the department of an employee with ID 1, you would write:

UPDATE employees
SET department = 'HR'
WHERE id = 1;

This command changes the department of the employee with ID 1 to ‘HR’.

Updating Multiple Rows

You can also update multiple rows that meet a specific condition. This is useful for making bulk changes to your data.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

For instance, to give all employees in the ‘Sales’ department a new title, you might write:

UPDATE employees
SET title = 'Senior Sales Associate'
WHERE department = 'Sales';

This command updates the title for all employees in the ‘Sales’ department.

By mastering these basic SQL commands, you’ll be well-equipped to handle a wide range of data manipulation tasks in your TiDB database. Whether you’re retrieving, inserting, or updating data, these commands form the foundation of your SQL toolkit.

DELETE Statement

The DELETE statement is a powerful command used to remove rows from a table. This operation is essential for maintaining the accuracy and relevance of your data by eliminating obsolete or incorrect records.

Basic Syntax

The basic syntax for a DELETE statement is straightforward:

DELETE FROM table_name
WHERE condition;

For example, to delete an employee record with a specific ID from the employees table, you would use:

DELETE FROM employees
WHERE id = 10;

This command removes the row where the id is 10 from the employees table. It’s crucial to include a WHERE clause to specify which rows should be deleted; otherwise, all rows in the table will be removed.

Conditional Deletion with WHERE

Using the WHERE clause, you can perform conditional deletions, targeting only the rows that meet specific criteria. This is particularly useful for cleaning up data without affecting the entire table.

DELETE FROM table_name
WHERE condition;

For instance, if you want to delete all employees who were hired before a certain date, you might write:

DELETE FROM employees
WHERE hire_date < '2020-01-01';

This command deletes all rows where the hire_date is earlier than January 1, 2020, ensuring that only outdated records are removed.

By mastering the DELETE statement, you can efficiently manage and maintain the integrity of your data within the TiDB database. Proper use of this command helps ensure that your database remains clean, accurate, and up-to-date, which is vital for any data-driven application.

Intermediate SQL Commands

As you advance in your SQL journey, mastering intermediate commands will significantly enhance your ability to manipulate and analyze data. These commands build on the basics and introduce more complex operations that are essential for robust database management.

JOIN Operations

JOIN operations are fundamental when working with relational databases, enabling you to combine rows from two or more tables based on a related column between them. Understanding different types of joins is crucial for effective data retrieval and manipulation.

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. This is the most common type of join.

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_field = b.common_field;

For example, to retrieve employees and their corresponding department names:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

This query returns only the employees who have a matching department.

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.

SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_field = b.common_field;

To get all employees and their departments, even if some employees are not assigned to any department:

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

This ensures that employees without a department are still included in the results.

RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side if there is no match.

SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_field = b.common_field;

For instance, to list all departments and their employees, including departments without employees:

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

This query ensures that all departments are listed, even those without employees.

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records. It combines the results of both LEFT JOIN and RIGHT JOIN.

SELECT a.column1, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.common_field = b.common_field;

To get a complete list of employees and departments, including those without matches in either table:

SELECT employees.name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.id;

This query includes all employees and all departments, regardless of whether they have matches in the other table.

Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. They are essential for summarizing data.

COUNT

The COUNT function returns the number of rows that match a specified condition.

SELECT COUNT(*)
FROM table_name
WHERE condition;

To count the number of employees in the ‘Sales’ department:

SELECT COUNT(*)
FROM employees
WHERE department = 'Sales';

SUM

The SUM function returns the total sum of a numeric column.

SELECT SUM(column_name)
FROM table_name
WHERE condition;

To calculate the total salaries of all employees:

SELECT SUM(salary)
FROM employees;

AVG

The AVG function returns the average value of a numeric column.

SELECT AVG(column_name)
FROM table_name
WHERE condition;

To find the average salary of employees in the ‘Engineering’ department:

SELECT AVG(salary)
FROM employees
WHERE department = 'Engineering';

MIN and MAX

The MIN and MAX functions return the smallest and largest values in a column, respectively.

SELECT MIN(column_name), MAX(column_name)
FROM table_name
WHERE condition;

To find the minimum and maximum hire dates of employees:

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

GROUP BY and HAVING Clauses

The GROUP BY clause groups rows that have the same values into summary rows, like “find the number of employees in each department.” The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Grouping Data

The GROUP BY statement is used to arrange identical data into groups.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

To group employees by department and count the number of employees in each:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Filtering Groups with HAVING

The HAVING clause allows you to filter groups based on a condition.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
HAVING aggregate_function(column_name) condition;

To find departments with more than 10 employees:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

By mastering these intermediate SQL commands, you can perform more sophisticated data analysis and manipulation within the TiDB database. These skills are essential for any developer looking to leverage the full power of SQL in their projects.

Advanced SQL Commands

As you delve deeper into SQL, mastering advanced commands will significantly enhance your ability to perform complex data manipulations and analyses. These commands build on the basics and introduce more sophisticated operations essential for robust database management.

Subqueries

Subqueries, also known as nested queries, are queries within another SQL query. They allow you to perform more complex operations by breaking them down into simpler, manageable parts.

Inline Subqueries

Inline subqueries are used within the SELECT, FROM, or WHERE clauses of an SQL statement. They are executed once for each row processed by the outer query.

SELECT name, (SELECT department_name FROM departments WHERE departments.id = employees.department_id) AS department
FROM employees;

In this example, the inline subquery retrieves the department name for each employee, allowing you to display both the employee’s name and their department in the result set.

Correlated Subqueries

Correlated subqueries reference columns from the outer query, making them dependent on the outer query for their values. They are executed once for each row considered by the outer query.

SELECT name
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

This query finds employees whose salary is above the average salary of their respective departments. The subquery calculates the average salary for each department, correlated with the outer query’s current row.

Window Functions

Window functions perform calculations across a set of table rows related to the current row. Unlike aggregate functions, window functions do not collapse rows into a single result but instead provide a result for each row.

ROW_NUMBER

The ROW_NUMBER function assigns a unique sequential integer to rows within a partition of a result set.

SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS row_num
FROM employees;

This query assigns a unique row number to each employee within their department, ordered by their hire date.

RANK and DENSE_RANK

The RANK and DENSE_RANK functions assign ranks to rows within a partition of a result set, with RANK allowing gaps in ranking and DENSE_RANK providing consecutive ranks.

SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

This query ranks employees within their departments based on their salaries, with the highest salary receiving the top rank.

LEAD and LAG

The LEAD and LAG functions access data from subsequent or preceding rows in the result set without using a self-join.

SELECT name, salary, LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

This query retrieves the salary of the next employee based on the hire date, allowing you to compare salaries between consecutive employees.

Common Table Expressions (CTEs)

Common Table Expressions (CTEs) provide a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They improve readability and maintainability of complex queries.

Basic Syntax

CTEs are defined using the WITH clause, followed by the CTE name and the query that defines the CTE.

WITH DepartmentCTE AS (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, c.employee_count
FROM departments d
JOIN DepartmentCTE c ON d.id = c.department_id;

This query creates a CTE to count the number of employees in each department and then joins it with the departments table to display the department names along with their employee counts.

Recursive CTEs

Recursive CTEs are used to perform hierarchical or recursive queries, such as traversing organizational charts or tree structures.

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

This query creates a recursive CTE to build an employee hierarchy, starting from the top-level managers and recursively including their subordinates.

By mastering these advanced SQL commands, you can perform intricate data manipulations and analyses within the TiDB database. These skills are indispensable for any developer aiming to leverage the full potential of SQL in their projects.

TiDB SQL Cheat Sheet

Navigating the intricacies of the TiDB database can be simplified with a handy SQL cheat sheet. This section provides essential commands and best practices for managing databases, tables, indexes, data manipulation, and user permissions in TiDB.

Essential TiDB Commands

Creating and Managing Databases

Creating and managing databases in TiDB is straightforward and follows standard SQL syntax with some TiDB-specific enhancements.

• Show Databases:

  SHOW DATABASES;
  

  Lists all databases in the TiDB instance.

• Create Database:

  CREATE DATABASE IF NOT EXISTS sample_db;
  

  Creates a new database named sample_db. The IF NOT EXISTS clause prevents an error if the database already exists.

• Drop Database:

  DROP DATABASE sample_db;
  

  Deletes the database named sample_db.

Creating and Managing Tables

Tables are the backbone of any database. Here’s how to create and manage them in TiDB:

• Create Table:

  CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    department VARCHAR(255),
    hire_date DATE
  );
  

  Creates a table named employees with columns for ID, name, department, and hire date.

• Show Create Table:

  SHOW CREATE TABLE employees;
  

  Displays the CREATE TABLE statement used to create the employees table.

• Drop Table:

  DROP TABLE employees;
  

  Deletes the employees table.

Creating and Managing Indexes

Indexes enhance query performance by allowing faster data retrieval.

• Create Index:

  CREATE INDEX idx_department ON employees (department);
  

  Creates an index on the department column of the employees table.

• Create Unique Index:

  CREATE UNIQUE INDEX idx_unique_id ON employees (id);
  

  Creates a unique index on the id column of the employees table.

• Show Indexes:

  SHOW INDEX FROM employees;
  

  Lists all indexes on the employees table.

• Drop Index:

  DROP INDEX idx_department ON employees;
  

  Deletes the index named idx_department from the employees table.

Data Manipulation in TiDB

Data manipulation involves inserting, updating, and deleting data, as well as querying it efficiently.

Inserting, Updating, and Deleting Data

• Insert Data:

  INSERT INTO employees (id, name, department, hire_date)
  VALUES (1, 'John Doe', 'Engineering', '2023-01-15');
  

  Adds a new row to the employees table.

• Update Data:

  UPDATE employees
  SET department = 'HR'
  WHERE id = 1;
  

  Updates the department of the employee with ID 1 to ‘HR’.

• Delete Data:

  DELETE FROM employees
  WHERE id = 1;
  

  Deletes the row where the id is 1 from the employees table.

Querying Data

Efficient querying is crucial for data analysis and reporting.

• Select All Data:

  SELECT * FROM employees;
  

  Retrieves all rows from the employees table.

• Select Specific Columns:

  SELECT name, department FROM employees;
  

  Retrieves the name and department columns from the employees table.

• Conditional Query:

  SELECT * FROM employees
  WHERE department = 'Sales';
  

  Retrieves rows where the department is ‘Sales’.

User and Permission Management in TiDB

Managing users and their permissions ensures secure and controlled access to the database.

Creating Users

• Create User:

  CREATE USER 'tiuser'@'localhost' IDENTIFIED BY 'password123';
  

  Creates a new user named tiuser with the password password123.

Granting Privileges

• Grant Privileges:

  GRANT SELECT, INSERT ON sample_db.* TO 'tiuser'@'localhost';
  

  Grants SELECT and INSERT privileges on the sample_db database to tiuser.

Dropping Users

• Drop User:

  DROP USER 'tiuser'@'localhost';
  

  Deletes the user named tiuser.

This SQL cheat sheet for TiDB covers the fundamental commands necessary for efficient database management. By mastering these commands, you can ensure your TiDB environment is well-organized, secure, and optimized for performance.

---

Mastering SQL commands is crucial for any developer aiming to efficiently manage and manipulate data. By practicing and applying these commands in real-world scenarios, you can significantly enhance your database management skills. Remember, continuous learning is key. Explore additional resources, such as the TiDB Documentation, to further deepen your understanding and stay updated with the latest advancements in SQL and the TiDB database.

See Also

Streamlining SQL Code Organization with TiDB as SQL Sandbox

Become a SQL Join Expert: The Ultimate Handbook

Transforming MySQL Data Handling with Text-to-SQL and LLMs

Getting Started with SQL Query Performance Enhancement

Maximizing Query Performance with SQL EXPLAIN Tool