Памятка по SQL: краткое руководство по основным командам

Как программист или аналитик данных, вы часто будете работать с большими объемами данных. Одним из самых мощных инструментов в вашем распоряжении является SQL (язык структурированных запросов).

Эта шпаргалка по SQL содержит наиболее распространенные объекты и команды SQL, включая типы данных, операторы DML и DDL, агрегатные функции, функции работы со строками и датами, а также подзапросы.

Он разработан, чтобы быть быстрым и удобным справочником, к которому вы можете обращаться всякий раз, когда взаимодействуете с реляционной базой данных.

Если вы похожи на нас и хотите скачать и распечатать или сохранить шпаргалку на своем компьютере, вы можете скачать ее ниже.

Скачать памятку по SQL

Оглавление

• Типы данных SQL
  • 1. Числовые типы
  • 2. Типы даты и времени
  • 3. Типы строк
• Оператор ВЫБЕРИТЕ
• ГДЕ Предложение
• ЗАКАЗАТЬ
• Объединение нескольких таблиц в SQL
  • 1. ВНУТРЕННЕЕ СОЕДИНЕНИЕ
  • 2. ЛЕВОЕ СОЕДИНЕНИЕ
  • 3. ПРАВОЕ СОЕДИНЕНИЕ
  • 4. ПОЛНОЕ СОЕДИНЕНИЕ
• Агрегатные функции SQL
  • 1. Функция СЧЁТ
  • 2. Функция СУММ
  • 3. СРЕДНЯЯ функция
  • 4. Функция МАКС.
  • 5. Функция МИН.
• Общие строковые функции
• Общие числовые функции
• Общие функции даты
• СГРУППИРОВАТЬ И ПОЛУЧИТЬ
  • 1. Сгруппировать по пункту
  • 2. Наличие пункта
  • Быстрые советы
• Подзапросы
  • 1. Оператор IN
  • 2. Оператор СУЩЕСТВУЕТ
  • 3. ЛЮБОЙ оператор
  • 4. ВСЕ Оператор
• Манипуляции с данными (DML)
  • 1. Оператор INSERT
  • 2. Заявление об ОБНОВЛЕНИИ
  • 3. Заявление УДАЛИТЬ
• Управление базой данных с помощью DDL
  • 1. Оператор СОЗДАТЬ
  • 2. Оператор ALTER
    • ДОБАВИТЬ НОВУЮ СТОЛБЦУ
    • Изменить тип данных столбца
    • Удалить столбец
    • Добавьте уникальное ограничение
    • Добавить внешний ключ между таблицами
  • 3. Оператор DROP
• Транзакции
  • 1. НАЧАЛО
  • 2. СОВЕРШИТЬ
  • 3. ОТКАТ
• Последние мысли

Типы данных SQL

Типы данных для конкретных систем управления базами данных (СУБД) могут различаться (например, Microsoft SQL Server и MySQL ). Тем не менее, есть несколько, которые можно найти в большинстве систем. Вы можете разделить их на три категории:

1. Числовой

2. Дата и время

3. Нить

1. Числовые типы

Это наиболее распространенные числовые типы:

• INTEGER : Целое число без десятичной точки.

• SMALLINT : Меньший диапазон целых чисел

• BIGINT : больший диапазон целых чисел.

• DECIMAL(p, s) или NUMERIC(p, s) : Например, десятичное число (5,2) соответствует 123,45.

• REAL : число с плавающей запятой с точностью не менее 6 десятичных цифр.

• FLOAT(n) : число с плавающей запятой с точностью не менее n цифр.

2. Типы даты и времени

• DATE : значение даты, обычно в формате «ГГГГ-ММ-ДД».

• ВРЕМЯ : значение времени, обычно в формате «ЧЧ:ММ:СС».

• DATETIME или TIMESTAMP : комбинация значений даты и времени.

3. Типы строк

• CHAR(n) : строка фиксированной длины из n символов.

• VARCHAR(n) или CHARACTER VARYING(n) : строка переменной длины.

• ТЕКСТ : строка переменной длины, максимальная длина которой определяется СУБД.

Оператор ВЫБЕРИТЕ

Оператор SELECT используется для извлечения данных из одной или нескольких таблиц. Вы можете указать столбцы, которые вы хотите получить, и из какой таблицы. Базовый оператор SELECT выглядит следующим образом:

SELECT column1, column2
FROM table;

Чтобы получить все записи из столбцов «name» и «country_id» из таблицы «city», ваш SQL-запрос выглядит следующим образом:

SELECT name, country_id
FROM city;

ГДЕ Предложение

Предложение WHERE позволяет фильтровать результаты оператора SELECT на основе определенных условий.

SELECT column1, column2
FROM table
WHERE condition;

Чтобы получить записи из таблицы «город», где «население» превышает 1 000 000 человек, ваш запрос выглядит следующим образом:

SELECT name, population
FROM city
WHERE population > 1000000;

ЗАКАЗАТЬ

Предложение ORDER BY позволяет сортировать результаты оператора SELECT по одному или нескольким столбцам. Вы можете отсортировать результаты по возрастанию (ASC) или по убыванию (DESC):

SELECT column1, column2
FROM table
ORDER BY column1 ASC, column2 DESC;

Например, чтобы получить записи из таблицы «город», отсортированные по «населению» в порядке убывания, ваш запрос будет выглядеть следующим образом:

SELECT name, population
FROM city
ORDER BY population DESC;

Объединение нескольких таблиц в SQL

В SQL обычно используются четыре соединения:

1. ВНУТРЕННЕЕ СОЕДИНЕНИЕ

2. ЛЕВОЕ СОЕДИНЕНИЕ

3. ПРАВОЕ ПРИСОЕДИНЕНИЕ

4. ПОЛНОЕ СОЕДИНЕНИЕ

1. ВНУТРЕННЕЕ СОЕДИНЕНИЕ

INNER JOIN извлекает записи, которые имеют совпадающие значения в обеих таблицах.

Возьмем в качестве примера базу данных исполнителей и альбомов, и вы хотите найти все комбинации исполнителей и альбомов. Это ВНУТРЕННЕЕ СОЕДИНЕНИЕ:

SELECT *
FROM artists AS a
INNER JOIN albums AS b
ON a.artist_id = b.artist_id;

При использовании INNER JOIN в результатах будут возвращены только строки с совпадающими значениями в указанных полях.

2. ЛЕВОЕ СОЕДИНЕНИЕ

ЛЕВОЕ СОЕДИНЕНИЕ также известно как ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ. Он возвращает все записи из левой таблицы и соответствующие записи из правой таблицы. Если в правильной таблице совпадений нет, результат будет содержать значения NULL.

Например, чтобы получить список всех исполнителей и их соответствующих альбомов (если они есть), вы можете использовать ЛЕВОЕ СОЕДИНЕНИЕ:

SELECT *
FROM artists AS a
LEFT JOIN albums AS b
ON a.artist_id = b.artist_id;

Этот запрос вернет всех исполнителей, даже если у них нет альбомов, связанных с ними в таблице альбомов.

3. ПРАВОЕ СОЕДИНЕНИЕ

ПРАВОЕ СОЕДИНЕНИЕ также известно как ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ. Он возвращает все записи из правой таблицы и соответствующие записи из левой таблицы. Если в левой таблице совпадений нет, результат будет содержать значения NULL.

Например, чтобы получить информацию обо всех альбомах и связанных с ними исполнителях (если они существуют), вы должны использовать ПРАВОЕ СОЕДИНЕНИЕ:

SELECT *
FROM artists AS a
RIGHT JOIN albums AS b
ON a.artist_id = b.artist_id;

Этот запрос вернет все альбомы, даже если у них нет связанных исполнителей в таблице исполнителей.

4. ПОЛНОЕ СОЕДИНЕНИЕ

ПОЛНОЕ СОЕДИНЕНИЕ также известно как ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ. Он объединяет результаты соединений LEFT и RIGHT. Другими словами, он возвращает все строки из левой и правой таблиц и заполняет пропущенные значения пустыми значениями, если совпадений нет.

Вот пример использования таблиц исполнителей и альбомов:

SELECT *
FROM artists AS a
FULL JOIN albums AS b
ON a.artist_id = b.artist_id;

Этот запрос возвращает все строки из обеих таблиц, заполняя значениями NULL там, где нет совпадений ни в одной из таблиц.

Агрегатные функции SQL

Агрегатные функции используются для вычисления одного результата из набора входных значений. Их называют «агрегатными», потому что они принимают несколько входных данных и возвращают один выходной. Наиболее распространенными являются:

1. СЧИТАТЬ

2. СУММА

3. СРЕДНИЙ

4. МАКС

5. МИН

1. Функция СЧЁТ

Функция COUNT позволяет подсчитать количество строк в результате запроса. Эту агрегатную функцию можно использовать для определения общего количества записей в таблице или количества записей, соответствующих определенным критериям.

Вот пример:

SELECT COUNT(*) FROM employees;

Этот запрос вернет общее количество сотрудников в таблице «сотрудники». Имейте в виду, что добавление предложения WHERE может улучшить ваши результаты:

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

2. Функция СУММ

The SUM function calculates the total sum of a numeric column. It’s useful when you need to calculate the total value of a particular numeric field. For example, this query returns the total sum of all employee salaries:

SELECT SUM(salary) FROM employees;

3. AVG Function

The AVG function computes the average value of a numeric column. This function is helpful when you want to find the average of a particular numeric field. For instance, this query returns the average salary of all employees:

SELECT AVG(salary) FROM employees;

4. MAX Function

The MAX function finds the maximum value of a column. This is often used to find the highest value in a numeric field or the most recent date in a datetime field. For example, this query returns the highest salary:

SELECT MAX(salary) FROM employees;

5. MIN Function

Lastly, the MIN function helps you find the minimum value of a column. For example, this query returns the lowest salary:

SELECT MIN(salary) FROM employees;

Remember, you can use WHERE clauses in these queries and JOIN with multiple tables.

Common String Functions

Here are the most common string functions that are found in most SQL dialects (the exact syntax can vary):

1. LEN or LENGTH(string): Returns the length of a string.

2. UPPER(string): Converts a string to upper case.

3. LOWER(string): Converts a string to lower case.

4. SUBSTR or SUBSTRING(string, start, length): Extracts a portion from a string.

5. TRIM(string): Removes leading and trailing spaces from a string.

6. LTRIM(string): Removes leading spaces from a string.

7. RTRIM(string): Removes trailing spaces from a string.

Common Numeric Functions

Here are the most common numeric functions that are found in most SQL dialects (the exact syntax can vary):

1. ABS(number): Returns the absolute value of a number.

2. ROUND(number, decimal_places): Rounds a number to a certain number of decimal places.

3. FLOOR(number): Rounds down the number to the nearest integer.

4. CEIL or CEILING(number): Rounds up the number to the nearest integer.

5. RAND(): Returns a random float value from 0 to 1.

6. MOD(n, m): Returns the remainder of n divided by m.

7. POWER(base, exponent): Raises a number to the power of another number.

8. LOG(number): Returns the natural logarithm of a number.

Common Date Functions

Here are the most common date functions that are found in most SQL dialects (the exact syntax can vary):

1. NOW(): Returns the current date and time.

2. DATE(datetime): Extracts the date part of a date or datetime expression.

3. TIME(datetime): Extracts the time part of a date or datetime expression.

4. YEAR(date): Returns the year part.

5. MONTH(date): Returns the month part.

6. DAY(date): Returns the day of the month part.

7. HOUR(time): Returns the hour part from a time.

8. MINUTE(time): Returns the minute part from a time.

9. SECOND(time): Returns the second part from a time.

GROUP BY And HAVING

When working with SQL queries, you may want to further summarize and filter your aggregated data. The GROUP BY and HAVING clauses provide this functionality.

1. Group By Clause

The GROUP BY clause allows you to group rows that share the same values in specified columns. It is commonly used with aggregate functions. This is the syntax:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2;

For example, if you want to calculate the total sales amount for each product category, this is the query:

SELECT product_category, SUM(sales_amount)
FROM sales_data
GROUP BY product_category;

TIP: Combining GROUP BY and COUNT is a good way of finding duplicate values.

2. Having Clause

If you want to filter the aggregated results further, you can use the HAVING clause. The syntax is:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition;

If you want to find product categories with total sales of more than $1,000,000, you would write:

SELECT product_category, SUM(sales_amount)
FROM sales_data
GROUP BY product_category
HAVING SUM(sales_amount) > 1000000;

Quick Tips

• Always use the GROUP BY clause before the HAVING clause.

• The SELECT statement can only contain specified column names, aggregate functions, constants, and expressions.

• When using the HAVING clause, filter conditions should be applied to the aggregate functions rather than directly to the grouped columns.

By understanding and properly applying the GROUP BY and HAVING clauses, you can better organize and analyze your data using SQL.

Subqueries

A subquery is also known as an inner or nested query. This is a query embedded within another SQL statement (such as a SELECT statement) or even inside another subquery.

Subqueries allow you to retrieve data based on the output of another query. The most common operators used with subqueries are:

1. IN

2. EXISTS

3. ANY

4. ALL

1. IN Operator

The IN operator tests if a value is within a set of values generated by the inner query. The syntax for using the IN operator with a subquery is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM other_table);

This returns rows from the outer query where the specified column value matches any of the values provided by the subquery.

Suppose you have an employee table and a departments table. You want to find employees who work in departments based at the head office. Here is a sample query

SELECT first_name, last_name
FROM employee
WHERE department IN (SELECT department FROM departments
                     WHERE location = "HQ");

For a more in-depth look, check out our article on the SQL WHERE IN syntax.

2. EXISTS Operator

The EXISTS operator checks if there is at least one row resulting from the subquery. You can use the EXISTS operator to filter rows based on the existence of related data in another table. This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM other_table WHERE condition);

When the subquery returns at least one row, the EXISTS operator returns true, and the relevant rows from the outer query are included in the result.

3. ANY Operator

The ANY operator is used to compare a value to any value in a set of values provided by a subquery. It’s commonly used with comparison operators like =, <, >, <=, or >=.

This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY (SELECT column_name FROM other_table WHERE condition);

This will return rows from the outer query where the specified column value meets the condition against any value from the subquery.

4. ALL Operator

The ALL operator compares a value to all values within a set of values provided by a subquery. The conditions must be true for every value in the subquery’s result. This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL (SELECT column_name FROM other_table WHERE condition);

This returns rows from the outer query only if the specified column value satisfies the condition against all values in the subquery’s output.

Data Manipulation (DML)

Data Manipulation Language (DML) is a sub-language within SQL for managing and updating data. The most common statements are:

1. INSERT

2. UPDATE

3. DELETE

1. INSERT Statement

The INSERT statement allows you to insert rows into a table. Here’s the basic syntax:

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

For example, if you want to insert a new row into a ‘users’ table with columns ‘id’, ‘name’, and ’email’, you would use the following query:

INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', '[email protected]');

2. UPDATE Statement

The UPDATE statement allows you to modify existing row data in a table. This is the syntax:

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

For example, if you want to update the email address of a user with the id ‘1’ in the ‘users’ table, your query would look like this:

UPDATE users
SET email = '[email protected]'
WHERE id = 1;

TIP: remember to include a WHERE clause to avoid updating all rows in the table by mistake.

3. DELETE Statement

The DELETE statement allows you to remove rows from a table. Here’s the syntax:

DELETE FROM table_name
WHERE condition;

For example, if you want to delete a user with the id ‘1’ from the ‘users’ table, your query would look like this:

DELETE FROM users
WHERE id = 1;

TIP: always include a WHERE clause to specify which rows to delete and to avoid deleting all the rows in the table.

Database Management With DDL

Data Definition Language (DDL) is the SQL sub-language used for creating and altering tables and the database itself. The most common DDL statements are:

1. CREATE

2. ALTER

3. DROP

1. CREATE Statement

The CREATE statement allows you to create new database objects, such as new tables, views, or indexes. When creating a new table, you need to define the columns, their data types, and any constraints.

Here’s an example of creating an orders table:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    product VARCHAR(255) NOT NULL,
    customer_id INT NOT NULL
);

TIP: choose appropriate data types and constraints to ensure data integrity in your tables.

Для более подробного ознакомления ознакомьтесь с нашей статьей об основных операциях с таблицами SQL .

2. Оператор ALTER

Оператор ALTER помогает вам изменять существующие объекты базы данных. Обычное использование включает:

• добавление, изменение или удаление столбцов.

• добавление или удаление ограничений из существующей таблицы.

• добавление первичных и внешних ключей.

ДОБАВИТЬ НОВУЮ СТОЛБЦУ

ALTER TABLE users ADD COLUMN age INTEGER;

Изменить тип данных столбца

ALTER TABLE users ALTER COLUMN age TYPE FLOAT;

Удалить столбец

ALTER TABLE users DROP COLUMN age;

Добавьте уникальное ограничение

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE(email);

Добавить внешний ключ между таблицами

ALTER TABLE users ADD FOREIGN KEY (country_id) REFERENCES Country(country_id);

3. Оператор DROP

Оператор DROP позволяет удалять объекты базы данных, такие как таблицы, представления или индексы. Используйте его с осторожностью, так как он безвозвратно удалит указанный объект и все его данные.

Вот пример:

DROP TABLE users;

СОВЕТ . Перед выполнением инструкции DROP убедитесь, что у вас есть надлежащие резервные копии.

Если вы хотите узнать больше о моделировании данных, посмотрите это видео:

Транзакции

Транзакции играют решающую роль в поддержании целостности базы данных , особенно когда несколько связанных операций выполняются одновременно. При обработке транзакций есть три основных операции:

1. НАЧИНАТЬ

2. СОВЕРШИТЬ

3. ОТКАТ

1. НАЧАЛО

Оператор BEGIN означает начало транзакции. Выполняя эту команду, вы устанавливаете отправную точку для своего набора операторов SQL.

BEGIN;

2. СОВЕРШИТЬ

Чтобы завершить изменения и сохранить их в базе данных, используйте оператор COMMIT. Это гарантирует, что все операции внутри транзакции выполняются успешно и постоянно.

COMMIT;

Вот пример полной транзакции на классическом примере перевода средств между счетами:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

3. ОТКАТ

При работе с транзакциями также важно знать, как отменить изменения в случае возникновения ошибки. Оператор ROLLBACK отменяет все изменения, сделанные с момента начала транзакции:

ROLLBACK;

Вот пример транзакции с обработкой ошибок с помощью ROLLBACK:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
IF @@ERROR <> 0
    ROLLBACK;
ELSE
    COMMIT;

Последние мысли

Как вы видели в этой шпаргалке по SQL, SQL предлагает множество команд и функций, которые позволяют создавать, обрабатывать и запрашивать данные в реляционной базе данных.

От базовых команд, таких как SELECT, INSERT, UPDATE и DELETE, до более сложных конструкций, таких как JOIN и подзапросы, до рассмотренных нами агрегатных функций, SQL обеспечивает гибкость для обработки широкого спектра задач с данными.

Продолжайте возвращаться к этой памятке, чтобы ускорить свой путь к изучению SQL !