Основные команды SQL

SQL – или Structured Query Language – это фундаментальный инструмент для взаимодействия с реляционными базами данных. Этот язык специализирован на управлении информацией, которая хранится в таблицах. Он отличается простотой и лаконичностью синтаксиса, которая делает его доступным даже для начинающих разработчиков, при этом обеспечивая мощные инструменты для решения сложных задач обработки данных.

В этой статье мы рассмотрим ключевые команды SQL, которые необходимы для эффективной работы с табличными данными.

Настройка базы данных

Для работы с SQL необходима система управления базами данных (СУБД), такая как MySQL. Это программное обеспечение обрабатывает SQL-запросы и управляет базами данных. Чтобы начать его использовать, нужно установить сервер MySQL и клиентское ПО, которое позволяет с ним взаимодействовать.

Подключение к серверу MySQL осуществляется через командную строку следующим образом:

mysql -u root -p

Эта команда инициирует подключение к серверу MySQL с использованием имени пользователя root. После ввода команды вам будет предложено ввести пароль.

После успешного подключения к серверу MySQL вы можете создать новую базу данных:

CREATE DATABASE ( );
USE ( );
SOURCE ;
SOURCE ;

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

Команда SHOW DATABASES

SHOW DATABASES; – это одна из основных команд SQL, которая позволяет просмотреть список всех баз данных на сервере. Она не вносит изменений в данные, но предоставляет обзор доступных баз, что может быть полезно для администрирования.

Пример использования команды:

SHOW DATABASES;

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

Команда CREATE DATABASE

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

Пример использования команды:

CREATE DATABASE имя_базы_данных;

Здесь имя_базы_данных – это уникальное имя новой базы данных. 

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

Например:

• таблицы – CREATE TABLE;
• индексы – CREATE INDEX;
• триггеры – CREATE TRIGGER. 

Таким образом команда CREATE DATABASE задает начало для создания новой среды хранения данных. А после ее выполнения вы можете создавать необходимые объекты внутри новой базы данных.

Команда USE

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

Пример использования команды:

USE имя_базы_данных;

Здесь имя_базы_данных – это имя базы данных, к которой вы хотите получить доступ. 

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

Команда SOURCE

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

Пример использования команды:

SOURCE путь/к/файлу.sql;

Здесь путь/к/файлу.sql указывает на расположение файла, который может содержать любые SQL-команды. Например, CREATE TABLE, INSERT, UPDATE и т.д. 

Таким образом команда SOURCE позволяет эффективно управлять базами данных без необходимости ввода каждой команды вручную.

Команда DROP DATABASE

Команда DROP DATABASE предназначена для полного удаления указанной базы данных и всех ассоциированных с ней объектов. Например, таблиц, индексов, представлений и хранимых процедур. 

Пример использования команды:

DROP DATABASE имя_базы_данных;

Здесь имя_базы_данных – это название базы данных, которую вы хотите удалить. 

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

Команда SHOW TABLES

Команда SHOW TABLES предоставляет обзор таблиц, содержащихся в активной базе данных. При этом саму базу данных нужно указать, используя команду USE имя_базы_данных;. 

Пример использования команд:

USE имя_базы_данных;
SHOW TABLES;

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

Команда CREATE TABLE

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

Пример создания таблицы:

CREATE TABLE instructor (
    ID CHAR(5),
    name VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    salary NUMERIC(8,2),
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);

В этом примере:

• ID, name, dept_name и salary являются именами столбцов таблицы instructor. 
• ID определен как первичный ключ (PRIMARY KEY), что означает, что каждое значение в этом столбце должно быть уникальным и не может быть NULL. 
• FOREIGN KEY (dept_name) REFERENCES department(dept_name) устанавливает внешний ключ на столбец dept_name, который ссылается на столбец dept_name в таблице department. Это ограничение поддерживает целостность данных между таблицами.
• NOT NULL указывает, что столбец не может содержать NULL значения, то есть каждая запись в этом столбце должна иметь конкретное значение.

Типы данных, такие как CHAR(5), VARCHAR(20) и NUMERIC(8,2), определяют формат данных, которые могут быть сохранены в каждом столбце: символьные строки фиксированной и переменной длины, а также числовые значения с точностью до двух знаков после запятой соответственно.

Команда DESCRIBE

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

Пример использования команды:

DESCRIBE имя_таблицы;

Здесь имя_таблицы – это имя таблицы, структуру которой вы хотите просмотреть. 

В результате выполнения этой команды вы получите список столбцов в таблице, с указанием следующей информации:

• типа данных каждого столбца;
• может ли столбец содержать значения NULL;
• первичные ключи (PRIMARY KEY);
• внешние ключи (FOREIGN KEY).

Использование DESCRIBE особенно полезно при работе с большими таблицами. 

Команда INSERT

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

Пример использования команды с указанием столбцов:

INSERT INTO имя_таблицы (столбец1, столбец2, столбец3)
VALUES (значение1, значение2, значение3);

В этом примере значения «значение1, значение2 и значение3» вставляются в столбцы «столбец1, столбец2 и столбец3» соответственно.

Пример использования команды без явного указания столбцов:

INSERT INTO имя_таблицы
VALUES (значение1, значение2, значение3, ...);

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

INSERT может также использоваться для добавления нескольких строк за один раз:

INSERT INTO имя_таблицы (столбец1, столбец2)
VALUES (значение1_1, значение2_1),
       (значение1_2, значение2_2),
       ...;

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

Команда UPDATE

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

Пример использования команды:

UPDATE имя_таблицы
SET столбец1 = новое_значение1, столбец2 = новое_значение2
WHERE условие;

В этом примере:

• имя_таблицы – это таблица, в которой будут обновлены данные;
• SET указывает столбцы и значения, которые нужно обновить;
• WHERE определяет условие, по которому выбираются строки для обновления. 

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

Команда DELETE

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

Пример использования команды с предложением WHERE:

DELETE FROM имя_таблицы WHERE условие;

Здесь имя_таблицы – это таблица, из которой будут удалены данные, а условие определяет, какие именно строки следует удалить. 

Если предложение WHERE опущено, команда удалит все строки из таблицы, что приведет к ее полной очистке:

DELETE FROM имя_таблицы;

Такое действие нельзя отменить, и все данные будут потеряны. Поэтому DELETE нужно использовать с осторожностью. 

Команда DROP TABLE

Команда DROP TABLE предназначена для полного удаления таблицы из базы данных. Это включает в себя удаление всех данных, структуры таблицы, а также связанных с ней объектов, таких как индексы и ограничения. 

Пример использования команды:

DROP TABLE имя_таблицы;

Здесь имя_таблицы – это название удаляемой таблицы. После выполнения этой команды таблица будет полностью удалена из базы данных, освобождая пространство и удаляя все связанные с ней данные.

Команда SELECT

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

Пример использования команды для выбора конкретных столбцов:

SELECT столбец1, столбец2, столбец3
FROM имя_таблицы;

В этом примере столбец1, столбец2, и столбец3 – это имена столбцов, которые вы хотите извлечь из имя_таблицы.

Для выбора всех столбцов из таблицы используется символ *:

SELECT * FROM имя_таблицы;

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

SELECT также может быть расширен с использованием дополнительных команд:

• условий (WHERE);
• методов сортировки (ORDER BY);
• группировки (GROUP BY);
• агрегатных функций (COUNT, SUM, AVG и т.д.). 

Таким образом можно выполнять более сложные и целенаправленные запросы данных.

Команда SELECT DISTINCT

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

Пример использования команды для выбора уникальных значений из одного столбца:

SELECT DISTINCT столбец1
FROM имя_таблицы;

В этом запросе SELECT DISTINCT извлекает все уникальные значения из столбец1 в имя_таблицы, игнорируя любые дубликаты.

Для выбора уникальных комбинаций значений из нескольких столбцов:

SELECT DISTINCT столбец1, столбец2
FROM имя_таблицы;

Этот запрос вернет уникальные пары значений из столбец1 и столбец2, опять же исключая дубликаты. 

Оператор WHERE

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

Примеры использования оператора WHERE:

SELECT * FROM course WHERE dept_name='Comp. Sci.';
SELECT * FROM course WHERE credits > 3;
SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits > 3;

Здесь:

• В первом примере из таблицы course выбираются все строки, где столбец dept_name равен 'Comp. Sci.'. 
• Во втором примере выбираются курсы с количеством кредитов больше 3. 
• Третий пример комбинирует оба условия, выбирая курсы отделения компьютерных наук с количеством кредитов больше 3.

Оператор WHERE может использоваться с:

• различными условиями и операторами сравнения (=, >, <, >=, <=, <>);
• логическими операторами (AND, OR, NOT). 

Это позволяет формировать сложные запросы для точного отбора данных. Для наглядности SQL сразу выводит промежуточные результаты на экран.

Команда GROUP BY

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

Пример использования команды:

SELECT dept_name, COUNT(course_id) AS course_count
FROM course
GROUP BY dept_name;

В этом запросе:

• строки таблицы course группируются по столбцу dept_name;
• с помощью функции COUNT подсчитывается количество курсов в каждом факультете. 

Результатом будет список факультетов с количеством курсов в каждом из них.

Здесь часто применяются простенькие встроенные функции подсчета данных:

• MIN и MAX возвращают минимальное и максимальное значения в группе соответственно.
• COUNT подсчитывает количество строк в группе.
• AVG вычисляет среднее значение числовых столбцов в группе.
• SUM суммирует значения числовых столбцов в группе.

Таким образом GROUP BY обогащает аналитические возможности.

Оператор HAVING

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

Пример использования HAVING:

SELECT dept_name, COUNT(course_id) AS course_count
FROM course
GROUP BY dept_name
HAVING COUNT(course_id) > 1;

В этом запросе:

• данные группируются по факультетам с помощью GROUP BY dept_name;
• с использованием HAVING, из результата выбираются только те группы, в которых количество курсов (COUNT(course_id)) превышает 1.

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

HAVING можно использовать с различными агрегатными функциями, такими как COUNT, SUM, AVG, MAX, и MIN. Это позволяет выполнить сложные аналитические запросы, основанные на суммированных или вычисленных данных.

Оператор ORDER BY

Оператор ORDER BY используется для сортировки результатов запроса по одному или нескольким столбцам в порядке возрастания (ASC) или убывания (DESC). Это позволяет организовывать данные в удобном для анализа порядке.

Примеры использования ORDER BY:

SELECT * FROM course ORDER BY credits;

В этом запросе курсы сортируются по количеству кредитов в порядке возрастания, что является настройкой по умолчанию для ORDER BY.

SELECT * FROM course ORDER BY credits DESC;

Здесь курсы сортируются по количеству кредитов, но уже в порядке убывания, благодаря указанию DESC.

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

SELECT * FROM course ORDER BY dept_name ASC, credits DESC;

В этом примере курсы сначала сортируются по названию факультета в алфавитном порядке, а затем – по убыванию количества кредитов внутри каждого факультета.

Оператор BETWEEN

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

Пример использования BETWEEN:

SELECT * FROM instructor
WHERE salary BETWEEN 50000 AND 100000;

В этом запросе выбираются все записи из таблицы instructor, где значение столбца salary находится в диапазоне от 50000 до 100000, включая обе эти суммы.

BETWEEN не ограничен использованием с числовыми значениями и может применяться также к датам и другим типам данных:

​​​​​​​SELECT * FROM event
WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31';

В этом примере выбираются события, запланированные на 2024 год. Оператор BETWEEN позволяет задать диапазон дат, облегчая работу с временными интервалами.

Оператор LIKE

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

Примеры использования LIKE:

SELECT * FROM course WHERE title LIKE '%to%';

Этот запрос выберет все курсы, название (title) которых содержит подстроку «to» в любом месте. Символ % обозначает любую последовательность символов, включая отсутствие символов.

SELECT * FROM course WHERE course_id LIKE 'CS-___';

Здесь выбираются курсы, идентификаторы (course_id) которых начинаются на «CS-», за которыми следуют ровно три любых символа. Символ _ обозначает ровно один любой символ.

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

Оператор IN

Оператор IN позволяет эффективно выполнять запросы с условием, которое должно соответствовать любому из нескольких указанных значений. Это особенно удобно при использовании с оператором WHERE для фильтрации данных на основе списка значений.

Пример использования IN:

​​​​​​​SELECT * FROM student
WHERE dept_name IN ('Comp. Sci.', 'Physics', 'Elec. Eng.');

В этом запросе выбираются все записи из таблицы student, где столбец dept_name соответствует одному из перечисленных названий департаментов: 

• 'Comp. Sci.';
• 'Physics';
• 'Elec. Eng.'. 

IN позволяет избежать множественного использования OR для каждого значения, упрощая запрос и повышая его читаемость.

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

Оператор JOIN

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

Пример использования JOIN:

​​​​​​​SELECT prereq.course_id, title, dept_name, credits, prereq_id
FROM prereq
LEFT OUTER JOIN course ON prereq.course_id = course.course_id;

В этом запросе LEFT OUTER JOIN объединяет таблицы prereq и course, выбирая все строки из prereq и соответствующие строки из course. Если соответствия в course нет, в результате для столбцов из course будут показаны значения NULL.

Существует несколько типов JOIN:

• INNER JOIN – возвращает строки, когда есть хотя бы одно совпадение в обеих таблицах.
• LEFT (OUTER) JOIN – возвращает все строки из левой таблицы и совпадающие строки из правой таблицы. Для строк без совпадений в правой таблице возвращается NULL.
• RIGHT (OUTER) JOIN – возвращает все строки из правой таблицы и совпадающие строки из левой таблицы. Для строк без совпадений в левой таблице возвращается NULL.
• FULL (OUTER) JOIN – возвращает строки, когда есть хотя бы одно совпадение в любой из таблиц. Для строк без совпадений возвращается NULL.

Использование JOIN упрощает доступ и анализ связанных данных из различных таблиц.

VIEW 

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

Создание VIEW:

​​​​​​​CREATE VIEW название_вью AS
SELECT столбец1, столбец2
FROM имя_таблицы
WHERE условие;

Этот пример создает VIEW, которая представляет собой результат выполнения указанного запроса SELECT. 

Например, создадим VIEW для отображения всех сотрудников отдела продаж:

​​​​​​​CREATE VIEW sales_view AS
SELECT employee_id, name, position
FROM employees
WHERE department = 'Sales';

После создания VIEW к ней можно обращаться так же, как к обычной таблице:

SELECT * FROM sales_view;

Этот запрос вернет всех сотрудников отдела продаж, используя определение sales_view.

Удаление VIEW:

DROP VIEW название_вью;

Для удаления VIEW и освобождения ресурсов используется команда DROP VIEW. 

Например, удаление VIEW для сотрудников отдела продаж:

DROP VIEW sales_view;

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

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

Основные агрегатные функции:

• COUNT() – подсчитывает количество элементов в наборе данных. Может использоваться для подсчета всех строк (COUNT(*)) или строк с конкретными значениями (COUNT(column_name)).
• MIN() и MAX() – возвращают минимальное и максимальное значения в наборе данных. Могут применяться к числовым и строковым типам данных.
• SUM() – суммирует значения по указанному числовому столбцу.
• AVG() – вычисляет среднее значение по указанному числовому столбцу.

Пример использования агрегатных функций:

SELECT COUNT(employee_id), MAX(salary), AVG(salary)
FROM employees
WHERE department = 'Sales';

В этом запросе:

• COUNT(employee_id) подсчитывает количество сотрудников в отделе продаж;
• MAX(salary) определяет максимальную зарплату в этом отделе;
• AVG(salary) вычисляет среднюю зарплату сотрудников отдела.

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

Вложенные подзапросы

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

Пример использования вложенного подзапроса:

SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year = 2022 AND course_id IN (
  SELECT course_id
  FROM section
  WHERE semester = 'Spring' AND year = 2023
);

В этом запросе внутренний подзапрос выбирает course_id курсов, проведенных весной 2023 года. Затем внешний запрос использует этот список для выбора уникальных course_id курсов, проведенных осенью 2022 года, которые также проводились весной 2023.

Вложенные подзапросы могут быть использованы в различных частях основного запроса, включая SELECT, FROM, WHERE, и даже HAVING, обеспечивая гибкость в построении запросов.

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

Заключение

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

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