15 основных запросов SQL для управления базой данных

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

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

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

Что такое реляционная база данных?

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

SQL — это язык, используемый для взаимодействия с реляционными базами данных. Команды SQL используются для выполнения основных операций с базой данных, таких как создание, чтение, обновление и удаление (CRUD) всего, что связано с базой данных.

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

Подмножества SQL

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

Эти команды будут разделены на четыре категории:

• Команды языка обработки данных (DML)
• Команды языка определения данных (DDL)
• Команды языка управления данными (DCL)
• Операторы управления транзакциями (TCS)

Команды DML используются для манипулирования и выполнения операций с данными в базе данных. Примеры команд DML включают SELECT, INSERTи UPDATE.

Команды DDL используются для определения структуры базы данных. Вы можете изменить схему базы данных, создав новые таблицы и объекты или изменив их атрибуты (например, тип данных, имя таблицы и т. д.). Примеры команд DDL включают CREATEи ALTER.

Команды DCL используются для управления разрешениями пользователей и доступом к базе данных. Примеры команд DCL включают GRANTи REVOKE.

Команды TCS используются для управления транзакциями в базе данных. Транзакции — это единицы работы, которые можно либо зафиксировать, либо отменить. Примеры команд TCS включают COMMITи ROLLBACK.

Теперь, когда мы рассмотрели различные типы команд SQL, давайте подробнее рассмотрим пример типичного запроса SQL.

Анатомия типичного SQL-запроса

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

Хотя команды SQL не чувствительны к регистру, считается хорошей практикой писать их в верхнем регистре.

Большинство SQL-запросов имеют форму оператора DML [1], и основной синтаксис для этого выглядит следующим образом:

SELECT column_name AS alias_name

   FROM table_name

   WHERE condition

   GROUP BY column_name

   HAVING condition

   ORDER BY column_name DESC;

Разберем каждую часть этого синтаксиса:

• SELECT: имя команды SQL, которую вы хотите выполнить. Для запросов DML эта команда может быть SELECT или UPDATE.
• column_name: имя столбца, который вы хотите запросить.
  • Вы можете дать столбцу временный псевдоним, используя ASключевое слово и указав имя псевдонима.
• FROM: Здесь вы указываете запрос из определенной таблицы, в данном случае table_name.
• WHERE: это предложение используется для фильтрации результатов запроса, соответствующих определенному условию.
  • Это WHEREпредложение можно использовать вместе с AND, OR, BETWEEN, IN, LIKEдля создания запросов.
• GROUP BY: Предложение, которое группирует строки с одинаковыми значениями в итоговые строки.
• HAVING: это предложение фильтрует результаты запроса (аналогично WHEREпредложению), но его можно использовать с агрегатными функциями.
• ORDER BY: необязательное предложение, используемое для сортировки результатов запроса в порядке возрастания или убывания.
• DESC: порядок набора результатов ASCпо умолчанию установлен в порядке возрастания ( ). DESCможно использовать для установки убывающего порядка.

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

Лучшие запросы SQL для изучения

1. CREATE DATABASE и ALTER DATABASE

Команда CREATE DATABASEсоздает новую базу данных. База данных должна быть создана для хранения любых таблиц или данных.

Синтаксис:

CREATE DATABASE database_name;

Пример:

CREATE DATABASE fruit_database;

Команда ALTER DATABASEизменяет существующую базу данных. Например, ALTER DATABASEкоманда может добавлять или удалять файлы из базы данных.

Синтаксис:

ALTER DATABASE database_name action;

Пример:

ALTER DATABASE fruit_database ADD FILE 'mango.txt';

2. USE

USE выбирает базу данных. Эта команда часто используется для начала работы с только что созданной базой данных.

Синтаксис:

USE database_name;

Пример:

USE fruit_database; 

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

Имейте в виду, что USEкоманда может выбирать только уже созданные базы данных.

Если база данных с указанным именем не существует, то будет возвращена ошибка.

3. CREATE TABLE, ALTER TABLE и DROP TABLE

Команда CREATE TABLEсоздает новую таблицу в базе данных. Таблицу необходимо создать до того, как в нее можно будет вставить какие-либо данные.

Синтаксис:

CREATE TABLE table_name (
    column_name data_type,
    column_name data_type,
    ...
);

Пример:

CREATE TABLE people_table (
    id INTEGER,
    name VARCHAR(255),
    age INTEGER
);

В этом примере мы создаем таблицу people_tableс тремя столбцами: id, nameи age.

Тип данных для каждого столбца должен быть указан. Некоторые распространенные типы данных включают INTEGER, VARCHARи DATE.

Команда ALTER TABLEизменяет существующую таблицу. Например, ALTER TABLEкоманду можно использовать для добавления или удаления столбцов из таблицы.

Синтаксис:

ALTER TABLE table_name action;

Пример:

ALTER TABLE people_table 
ADD email VARCHAR(255);

В этом примере мы добавляем emailв people_tableтаблицу новый столбец с именем. Необходимо указать тип данных для нового столбца.

Также можно использовать команду для изменения типа данныхALTER TABLE существующего столбца.

Синтаксис:

ALTER TABLE table_name 
MODIFY COLUMN column_name data_type;

Пример:

ALTER TABLE people_table 
MODIFY COLUMN last_name 
VARCHAR(128);

В этом примере мы изменяем last_nameстолбец, чтобы он имел тип данных VARCHAR(128).

Обратите внимание, что вы не можете использовать эту ALTER TABLEкоманду для изменения типа данных столбца, если в этом столбце хранятся какие-либо данные.

Чтобы изменить тип данных столбца, вы должны сначала удалить все данные из этого столбца.

Синтаксис:

ALTER TABLE table_name 
DROP COLUMN column_name;

Пример:

ALTER TABLE people_table 
DROP COLUMN email;

В этом примере мы удаляем emailстолбец из файла people_table. Обратите внимание, что эта команда безвозвратно удалит все данные, хранящиеся в этом столбце.

Команда DROP TABLEудаляет всю таблицу из базы данных. Эта команда безвозвратно удалит все данные, хранящиеся в таблице.

Синтаксис:

DROP TABLE table_name;

Пример:

DROP TABLE people_table;

В этом примере мы удаляем people_tableтаблицу из базы данных.

Важно быть осторожным при использовании DROP TABLEкоманды, так как ее нельзя отменить! После удаления таблицы все данные, хранящиеся в этой таблице, безвозвратно теряются.

Альтернативой DROP TABLEявляется использование TRUNCATE TABLEвместо этого. Эта команда удалит все данные из таблицы, но не саму таблицу.

Синтаксис:

TRUNCATE TABLE table_name;

Пример:

TRUNCATE TABLE people_table;

В этом примере мы удаляем все данные из people_tableтаблицы. Сама таблица не удаляется, поэтому все сведения о столбцах сохраняются.

4. INSERT INTO

Команда INSERT INTOвставляет данные в таблицу.

Синтаксис:

INSERT INTO table_name (column_name, column_name, ...)
VALUES (value, value, ...);

Пример:

INSERT INTO people_table (id, name, age)
VALUES (NULL, 'Crystal', 64);

В этом примере мы вставляем новую строку в people_table. Первый столбец в таблице id. Мы указали, что для этого столбца должно быть установлено значение NULL, что означает, что база данных автоматически сгенерирует уникальный идентификатор для этой строки.

Второй и третий столбцы таблицы — это nameи ageсоответственно. Мы указали, что эти столбцы должны быть установлены для этой строки ’Crystal’и 64для этой строки.

5. UPDATE

Команда UPDATEизменяет данные, уже сохраненные в таблице.

Синтаксис:

UPDATE table_name
SET column_name = value, column_name = value, ...
WHERE condition;

Пример:

UPDATE people_table
SET name = 'Crystal Sequel', age = 65
WHERE id = 100;

В этом примере мы обновляем строку id = 100в people_tableтаблице. Мы устанавливаем nameстолбец в ’Crystal Sequel’и ageстолбец в 65.

Важно : Предложение WHEREобязательно при использовании UPDATEкоманды. Без WHEREпредложения все строки в таблице будут обновлены!

6. DELETE

Команда DELETEудаляет данные из таблицы.

Синтаксис:

DELETE FROM table_name
WHERE condition;

Пример:

DELETE FROM people_table
WHERE id = 100;

В этом примере мы удаляем из people_tableтаблицы строку с id=100.

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

7. SELECT и FROM

Команда SELECTзапрашивает данные FROMиз таблицы.

Синтаксис:

SELECT column_name, column_name, ...
FROM table_name
WHERE condition;

Пример:

SELECT name, age
FROM people_table
WHERE id = 100;

В этом примере мы запрашиваем people_tableимя и возраст строки, где id=100.

Команды SELECTи FROMявляются двумя наиболее важными командами SQL, поскольку они позволяют указывать и извлекать данные из базы данных.

8. ORDER BY

Команда ORDER BYсортирует результаты запроса.

Синтаксис:

SELECT column_name, column_name, ...
   FROM table_name
   WHERE condition
   ORDER BY column_name [ASC | DESC];

Пример:

SELECT name, age
   FROM people_table
   WHERE id = 100
   ORDER BY age DESC;

В этом примере мы запрашиваем people_tableимя и возраст строки с помощью id=100. Затем мы сортируем результаты по возрасту в порядке убывания.

Команда ORDER BYчасто используется вместе с SELECTкомандой для извлечения данных из таблицы в определенном порядке.

Важно отметить, что ORDER BYкоманда работает не только с числовыми данными — ее также можно использовать для сортировки текстовых данных по алфавиту!

ASC: По умолчанию порядок восходящий (A, B, C,… Z)

DESC: в порядке убывания (Z, Y, X,… A)

9. GROUP BY

Команда GROUP BYгруппирует результаты запроса по одному или нескольким столбцам.

Синтаксис:

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

Пример:

SELECT name, count(*)
   FROM people_table
   WHERE country='US'
   GROUP BY names;

В этом примере мы запрашиваем people_tableвсе уникальные имена в таблице. Затем мы используем COUNT()функцию, чтобы подсчитать, сколько раз встречается каждое имя.

Команда GROUP BYчасто используется с агрегатными функциями (такими как COUNT(), MIN(), MAX(), SUM()и т. д.) для группировки данных и расчета сводного значения.

Столбцы, указанные в GROUP BYпредложении, также должны быть включены в SELECTпредложение.

10. HAVING

Команда HAVINGфильтрует результаты запроса на основе одной или нескольких агрегатных функций.

Синтаксис:

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

Пример:

SELECT name, count(*)
   FROM people_table
   WHERE country='US'
   GROUP BY names
   HAVING count(*) > 0;

В этом примере мы запрашиваем people_tableвсе уникальные имена в таблице. Затем мы используем COUNT()функцию, чтобы подсчитать, сколько раз встречается каждое имя.

Наконец, мы используем HAVINGпредложение, чтобы отфильтровать любые имена, которые не встречаются в таблице хотя бы раз.

Подобно GROUP BYпредложению, мы также можем использовать его HAVINGвместе с агрегатными функциями для фильтрации результатов запроса.

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

• COUNT(): подсчитывает количество строк в таблице
• MIN(): находит минимальное значение в столбце
• MAX(): находит максимальное значение в столбце
• SUM(): вычисляет сумму значений в столбце
• AVG(): вычисляет среднее значение значений в столбце

Столбцы, указанные в GROUP BYпредложении, также должны быть включены в SELECTпредложение.

HAVING очень похож на WHERE, но есть несколько важных отличий:

• WHEREиспользуется для фильтрации данных перед агрегированием, а HAVINGиспользуется для фильтрации данных после агрегирования.
• WHEREможет использоваться с агрегатными функциями, но HAVINGможет использоваться только со столбцами, включенными в GROUP BYпредложение.
• WHEREприменяется к отдельным строкам, а HAVINGприменяется к группам строк.

11. UNION и UNION ALL

Команда UNIONобъединяет результаты двух или более запросов в один набор данных. Он часто используется для объединения данных из нескольких таблиц в один набор данных.

Синтаксис:

SELECT column_name FROM table_name1
UNION
SELECT column_name FROM table_name2;

Пример:

SELECT names FROM employee_table
UNION
SELECT email FROM people_table;

В этом примере мы используем SELECTи UNIONдля запроса имен из, employee_tableа затем объединяем их с электронными письмами из people_tableв один набор результатов.

Количество и порядок столбцов должны быть одинаковыми во всех операторах SELECT, объединенных с UNION. Кроме того, все столбцы должны быть одного типа данных.

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

Синтаксис:

SELECT column_name FROM table_name_one
UNION ALL
SELECT column_name FROM table_name_two;

Пример:

SELECT names FROM people_table
UNION ALL ALL
SELECT email FROM people_table;

В этом примере мы запрашиваем people_tableвсе уникальные имена в таблице. Затем мы используем UNION ALLкоманду для объединения этого набора данных с другим набором данных, содержащим все уникальные адреса электронной почты в таблице.

12. JOIN

A JOIN— это способ объединения данных из двух или более таблиц в одну новую таблицу. Соединяемые таблицы называются левой и правой таблицами.

Наиболее распространенным типом соединения является INNER JOIN. Внутреннее соединение будет объединять только те строки из левой таблицы, которые совпадают в правой таблице.

Синтаксис:

SELECT column_name FROM left_table
INNER JOIN right_table 
ON left_table.column_name = right_table.column_name;

Пример:

SELECT name, email FROM people_table
INNER JOIN employee_table 
ON people_table.id = yourtable.id;

В этом примере мы используем INNER JOINдля объединения данных из файлов people_tableи employee_table. Мы присоединяемся к таблицам с помощью idстолбца.

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

LEFT OUTER JOIN: левое соединение объединит все строки из левой таблицы, даже если в правой таблице нет совпадений.

Синтаксис:

SELECT column_name(s) FROM left_table
LEFT OUTER JOIN right_table 
ON left_table.column_name = right_table.column_name;

RIGHT OUTER JOIN: Правое соединение объединит все строки из правой таблицы, даже если в левой таблице нет совпадений.

Синтаксис:

SELECT column_name(s) FROM left_table
RIGHT OUTER JOIN right_table ON left_table.column_name = right_table.column_name;

FULL OUTER JOIN: полное внешнее соединение объединит все строки из обеих таблиц, даже если ни в одной из них нет совпадений.

Синтаксис:

SELECT column_name(s) FROM left_table
FULL OUTER JOIN right_table ON left_table.column_name = right_table.column_name;

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

13. CREATE INDEX и DROP INDEX

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

Синтаксис:

CREATE INDEX index_name ON table_name (column_name);

Пример:

CREATE INDEX people ON employee_table (names);

После создания индекса база данных может использовать его для ускорения выполнения запросов SQL. Индексы — это важный инструмент, о котором должны знать администраторы баз данных, и они могут быть полезны для повышения производительности SQL-запросов.

Синтаксис:

DROP INDEX index_name ON table_name;

Пример:

DROP INDEX people ON employee_table;

После удаления индекса база данных больше не может использовать его для ускорения выполнения SQL-запросов.

14. GRANT и REVOKE

Команды GRANTи REVOKEуправляют разрешениями в базе данных.

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

Синтаксис:

GRANT permission_type ON object_name TO user;

Пример:

GRANT CREATE TABLE ON important_database TO bob;

Команда REVOKEудаляет разрешение пользователя на выполнение действий.

Синтаксис:

REVOKE permission_type ON object_name FROM user;

Пример:

REVOKE CREATE TABLE ON important_database FROM bob;

Управление разрешениями в базе данных является важной задачей для администраторов баз данных. Команды GRANTи REVOKE— две наиболее важные команды для управления разрешениями.

15. LIKE

Оператор LIKE используется для поиска данных, соответствующих определенному значению.

Синтаксис:

SELECT column_name(s) 
FROM table_name 
WHERE column_name LIKE pattern;

Пример:

SELECT first_name 
FROM class_roster 
WHERE first_name LIKE '%a';

В приведенном выше примере запрос вернет все записи из class_rosterтаблицы, в которой first_nameстолбец содержит значение, оканчивающееся на букву a.

Размещение по модулю %после буквы aвернет все записи, в которых столбец first_name содержит значение, начинающееся с буквы a.

Помещение по модулю %до и после буквы «а» вернет все записи, в которых столбец first_name содержит значение, содержащее букву «а».

Заключение

Изучение SQL — ценный навык для всех, кто работает с данными. В этой статье мы рассмотрели некоторые из наиболее важных команд SQL, которые необходимо знать для управления базами данных.

Если вы хотите узнать больше о SQL, мы рекомендуем вам продолжать практиковаться с реальными наборами данных. Чем больше вы используете SQL, тем лучше вы будете писать SQL-запросы!