SQL Quick Reference: Simplifying Database Management

SQL Cheatsheet

This blog comprehensively guides the most important SQL commands and operations. It covers basic queries, joins, subqueries, indexes, and more advanced concepts.

Table of Contents

1. SQL Basics
2. Data Definition Language (DDL)
3. Data Manipulation Language (DML)
4. Data Query Language (DQL)
5. Data Control Language (DCL)
6. Joins
7. Subqueries
8. Indexes
9. Aggregation Functions
10. Grouping and Sorting
11. Transactions
12. Advanced SQL
13. Best Practices

---

SQL Basics

Structure of a SQL Query

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

Commenting in SQL

• Single-line comment: -- This is a comment
• Multi-line comment:

  /* This is a 
     multi-line comment */

---

Data Definition Language (DDL)

Creating a Table

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    hire_date DATE
);

Altering a Table

Adding a Column

ALTER TABLE table_name
ADD column_name datatype;

Dropping a Column

ALTER TABLE table_name
DROP COLUMN column_name;

Modifying a Column

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Renaming a Table

ALTER TABLE old_table_name
RENAME TO new_table_name;

Dropping a Table

DROP TABLE table_name;

Creating an Index

CREATE INDEX index_name
ON table_name (column_name);

Dropping an Index

DROP INDEX index_name;

---

Data Manipulation Language (DML)

Inserting Data into a Table

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

Example:

INSERT INTO employees (id, name, age, hire_date)
VALUES (1, 'John Doe', 30, '2022-01-01');

Updating Data in a Table

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

Example:

UPDATE employees
SET age = 31
WHERE id = 1;

Deleting Data from a Table

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM employees
WHERE id = 1;

---

Data Query Language (DQL)

Selecting Data from a Table

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

Example:

SELECT * FROM employees;
SELECT name, age FROM employees WHERE age > 30;

Wildcards

• *: Select all columns
• %: Wildcard for zero or more characters (in LIKE clause)
• _: Wildcard for exactly one character (in LIKE clause)

Example:

SELECT * FROM employees WHERE name LIKE 'J%';

---

Data Control Language (DCL)

Granting Permissions

GRANT permission ON object TO user;

Example:

GRANT SELECT, INSERT ON employees TO 'user1';

Revoking Permissions

REVOKE permission ON object FROM user;

Example:

REVOKE SELECT ON employees FROM 'user1';

---

Joins

INNER JOIN

Returns rows when there is a match in both tables.

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table, and matched rows from the right table. If no match, NULL values will appear for columns from the right table.

  /* This is a 
     multi-line comment */

RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table, and matched rows from the left table. If no match, NULL values will appear for columns from the left table.

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

FULL OUTER JOIN

Returns rows when there is a match in one of the tables.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    hire_date DATE
);

---

Subqueries

Subquery in SELECT

ALTER TABLE table_name
ADD column_name datatype;

Subquery in WHERE

ALTER TABLE table_name
DROP COLUMN column_name;

Subquery in FROM

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

---

Indexes

Creating an Index

ALTER TABLE old_table_name
RENAME TO new_table_name;

Dropping an Index

DROP TABLE table_name;

Unique Index

Ensures that all values in a column (or group of columns) are unique.

CREATE INDEX index_name
ON table_name (column_name);

---

Aggregation Functions

COUNT

Counts the number of rows that match a specific condition.

DROP INDEX index_name;

SUM

Returns the sum of values in a column.

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

AVG

Returns the average of values in a column.

INSERT INTO employees (id, name, age, hire_date)
VALUES (1, 'John Doe', 30, '2022-01-01');

MIN and MAX

Returns the minimum and maximum values in a column.

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

---

Grouping and Sorting

GROUP BY

Groups rows that have the same values into summary rows.

UPDATE employees
SET age = 31
WHERE id = 1;

HAVING

Filters groups after applying GROUP BY.

DELETE FROM table_name
WHERE condition;

ORDER BY

Sorts the result set in ascending or descending order.

DELETE FROM employees
WHERE id = 1;

---

Transactions

Starting a Transaction

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

Committing a Transaction

SELECT * FROM employees;
SELECT name, age FROM employees WHERE age > 30;

Rolling Back a Transaction

SELECT * FROM employees WHERE name LIKE 'J%';

---

Advanced SQL

CASE WHEN

Conditional logic inside a query.

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

UNION and UNION ALL

• UNION: Combines the result sets of two or more queries (removes duplicates).
• UNION ALL: Combines result sets (keeps duplicates).

  /* This is a 
     multi-line comment */

---

Best Practices

• Use JOIN instead of subqueries when possible for better performance.
• Index frequently searched columns to speed up queries.
• Avoid SELECT * and specify only the columns you need.
• Use LIMIT for large result sets to restrict the number of rows returned.
• Normalize your data to avoid redundancy and improve consistency.
• Use WHERE clauses instead of HAVING to filter data before aggregation.
• Test queries for performance, especially for large datasets.
• Use transactions to ensure data consistency, especially for operations that involve multiple DML statements.

---

Conclusion

This SQL cheatsheet covers all the essential SQL commands and techniques you’ll need for working with relational databases. Whether you are querying, inserting, updating, or joining data, this guide will help you work more effectively with SQL.

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

The above is the detailed content of SQL Quick Reference: Simplifying Database Management. For more information, please follow other related articles on the PHP Chinese website!