Home > Database > Mysql Tutorial > SQL Quick Reference: Simplifying Database Management

SQL Quick Reference: Simplifying Database Management

DDD
Release: 2024-12-24 10:23:15
Original
738 people have browsed it

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;
Copy after login
Copy after login
Copy after login

Commenting in SQL

  • Single-line comment: -- This is a comment
  • Multi-line comment:
  /* This is a 
     multi-line comment */
Copy after login
Copy after login
Copy after login

Data Definition Language (DDL)

Creating a Table

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

Example:

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

Altering a Table

Adding a Column

ALTER TABLE table_name
ADD column_name datatype;
Copy after login
Copy after login

Dropping a Column

ALTER TABLE table_name
DROP COLUMN column_name;
Copy after login
Copy after login

Modifying a Column

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Copy after login
Copy after login

Renaming a Table

ALTER TABLE old_table_name
RENAME TO new_table_name;
Copy after login
Copy after login

Dropping a Table

DROP TABLE table_name;
Copy after login
Copy after login

Creating an Index

CREATE INDEX index_name
ON table_name (column_name);
Copy after login
Copy after login

Dropping an Index

DROP INDEX index_name;
Copy after login
Copy after login

Data Manipulation Language (DML)

Inserting Data into a Table

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

Example:

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

Updating Data in a Table

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

Example:

UPDATE employees
SET age = 31
WHERE id = 1;
Copy after login
Copy after login

Deleting Data from a Table

DELETE FROM table_name
WHERE condition;
Copy after login
Copy after login

Example:

DELETE FROM employees
WHERE id = 1;
Copy after login
Copy after login

Data Query Language (DQL)

Selecting Data from a Table

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

Example:

SELECT * FROM employees;
SELECT name, age FROM employees WHERE age > 30;
Copy after login
Copy after login

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%';
Copy after login
Copy after login

Data Control Language (DCL)

Granting Permissions

GRANT permission ON object TO user;
Copy after login

Example:

GRANT SELECT, INSERT ON employees TO 'user1';
Copy after login

Revoking Permissions

REVOKE permission ON object FROM user;
Copy after login

Example:

REVOKE SELECT ON employees FROM 'user1';
Copy after login

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;
Copy after login
Copy after login
Copy after login

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 */
Copy after login
Copy after login
Copy after login

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],
    ...
);
Copy after login
Copy after login
Copy after login

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
);
Copy after login
Copy after login

Subqueries

Subquery in SELECT

ALTER TABLE table_name
ADD column_name datatype;
Copy after login
Copy after login

Subquery in WHERE

ALTER TABLE table_name
DROP COLUMN column_name;
Copy after login
Copy after login

Subquery in FROM

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Copy after login
Copy after login

Indexes

Creating an Index

ALTER TABLE old_table_name
RENAME TO new_table_name;
Copy after login
Copy after login

Dropping an Index

DROP TABLE table_name;
Copy after login
Copy after login

Unique Index

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

CREATE INDEX index_name
ON table_name (column_name);
Copy after login
Copy after login

Aggregation Functions

COUNT

Counts the number of rows that match a specific condition.

DROP INDEX index_name;
Copy after login
Copy after login

SUM

Returns the sum of values in a column.

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

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');
Copy after login
Copy after login

MIN and MAX

Returns the minimum and maximum values in a column.

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

Grouping and Sorting

GROUP BY

Groups rows that have the same values into summary rows.

UPDATE employees
SET age = 31
WHERE id = 1;
Copy after login
Copy after login

HAVING

Filters groups after applying GROUP BY.

DELETE FROM table_name
WHERE condition;
Copy after login
Copy after login

ORDER BY

Sorts the result set in ascending or descending order.

DELETE FROM employees
WHERE id = 1;
Copy after login
Copy after login

Transactions

Starting a Transaction

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

Committing a Transaction

SELECT * FROM employees;
SELECT name, age FROM employees WHERE age > 30;
Copy after login
Copy after login

Rolling Back a Transaction

SELECT * FROM employees WHERE name LIKE 'J%';
Copy after login
Copy after login

Advanced SQL

CASE WHEN

Conditional logic inside a query.

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

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 */
Copy after login
Copy after login
Copy after login

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],
    ...
);
Copy after login
Copy after login
Copy after login

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!

source:dev.to
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template