SQL команды – всё что нужно знать: основные задачи и примеры

SQL команды – база, которую необходимо знать при работе с языком SQL. Язык SQL или Structured Query Language (язык структурированных запросов) предназначен для управления данными в системе реляционных баз данных (RDBMS). В этой статье будет рассказано о часто используемых командах SQL, с которыми должен быть знаком каждый программист.

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

Фактически, SQL является набором стандартов, для написания запросов к БД. Последняя действующая редакция стандартов языка SQL – ISO/IEC 9075:2016.

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

Варианты спецификаций SQL разрабатываются компаниями и сообществами и служат, соответственно, для работы с разными СУБД (Системами Управления Базами Данных) – системами программ, заточенных под работу с продуктами из своей инфраструктуры.

Наиболее применяемые на сегодня СУБД, использующие свои стандарты (расширения) SQL:

• MySQL – СУБД, принадлежащая компании Oracle;
• PostgreSQL – свободная СУБД, поддерживаемая и развиваемая сообществом;
• Microsoft SQL Server – СУБД, принадлежащая компании Microsoft. Применяет диалект Transact-SQL (T-SQL).

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

Читайте так же: “ТОП 5 языков программирования для новичков”

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

SQL запосы

Коснемся классификации SQL запросов.

Выделяют такие виды SQL запросов:

• DDL (Data Definition Language) – язык определения данных. Задачей DDL запросов является создание БД и описание ее структуры. Запросами такого вида устанавливаются правила того, в каком виде различные данные будут размещаться в БД.
• DML (Data Manipulation Language) – язык манипулирования данными. В число запросов этого типа входят различные команды, используя которые непосредственно производятся некоторые манипуляции с данными. DML-запросы нужны для добавления изменений в уже внесенные данные, для получения данных из БД, для их сохранения, для обновления различных записей и для их удаления из БД. В число элементов DML-обращений входит основная часть SQL операторов.
• DCL (Data Control Language) – язык управления данными. Включает в себя запросы и команды, касающиеся разрешений, прав и других настроек СУБД.
• TCL (Transaction Control Language) – язык управления транзакциями. Конструкции такого типа применяют чтобы управлять изменениями, которые производятся с использованием DML запросов. Конструкции TCL позволяют нам производить объединение DML запросов в наборы транзакций.

Основные типы SQL запросов по их видам

Создание и настройка базы данных

Нам нужна будет для примеров БД MS SQL Server 2017 и MS SQL Server Management Studio 2017.

Рассмотрим последовательность действий того, как создать SQL запрос. Воспользовавшись Management Studio, для начала создадим новый редактор скриптов. Чтобы это сделать, на стандартной панели инструментов выберем «Создать запрос». Или воспользуемся клавиатурной комбинацией Ctrl+N.

Нажимая кнопку «Создать запрос» в Management Studio, мы открываем тестовый редактор, используя который можно производить написание SQL запросов, сохранять их и запускать.

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

Создадим новую БД с именем «b_library» для библиотеки книг. Чтобы это делать наберем в редакторе такой SQL запрос:

Далее выделим введенный текст и нажмем F5 или кнопку «Выполнить». У нас создастся БД «b_library».

Все дальнейшие манипуляции мы можем провести с этой созданной нами БД. Для этого сначала подключимся к этой базе:

В БД «b_library» создадим таблицу авторов «tAuthors» с такими столбцами: AuthorId, AuthorFirstName, AuthorLastName, AuthorAge:

Заполним нашу таблицу таким авторами: Александр Пушкин, Сергей Есенин, Джек Лондон, Шота Руставели и Рабиндранат Тагор. Для этого используем такой SQL запрос:

Мы можем посмотреть в «tAuthors» записи, путем отправления в СУБД простого SQL запроса:

В нашей БД «b_library» мы создали первую таблицу «tAuthors», заполнили «tAuthors» авторами книг и теперь можем рассмотреть различные примеры SQL запросов, которыми мы сможем взаимодействовать с БД.

Примеры простых запросов SQL к базам данных.

Рассмотрим основные запросы SQL.

SELECT

1) Выведем все имеющиеся у нас БД:

2) Выведем все таблицы в созданной нами ранее БД «b_library»:

3) Выводим еще раз имеющиеся у нас записи по авторам книг из созданной выше «tAuthors»:

4) Выведем информацию о том, сколько у нас имеется записей строк в «tAuthors»:

5) Выведем из «tAuthors» две записи, начиная с четвертой. Используя ключевое слово OFFSET, пропустим первые три записи, а благодаря использованию ключевого слова FETCH – обозначим выборку только следующих 2 строк (ONLY):

6) Выведем из «tAuthors» все записи с сортировкой в алфавитном порядке по первой букве имени автора:

7) Выведем из «tAuthors» данные, предварительно по AuthorId отсортировав их по убыванию:

8) Выберем записи из «tAuthors», значение AuthorFirstName у которых соответствует имени «Александр»:

9) Выберем из «tAuthors» записи, где имя автора AuthorFirstName начинается с «се»:

10) Выберем из «tAuthors» записи, в которых имя автора (AuthorFirstName) заканчивается на «ат»:

11) Сделаем выборку всех строк из «tAuthors», значение AuthorId в которых равняется 2 или 4:

12) Выберем в «tAuthors» такую запись AuthorAge, значение которой – наибольшее:

13) Проведем выборку из «tAuthors» по столбцам AuthorFirstName и AuthorLastName:

14) Получим из «tAuthors» все строки, у которых AuthorId не равняется трем:

INSERT

INSERT – это вид запроса SQL, при применении которого СУБД выполняет добавление новых записей в БД.

Добавим в «tAuthors» нового автора – Уильяма Шекспира, 51 год. Соответственно в поле AuthorFirstName добавится Уильям, в AuthorLastName добавится Шекспир, в AuthorAge – 51. В AuthorId, в нашем случае, автоматически добавится значение, инкрементированное от предыдущего на 1.

Проверим:

UPDATE

UPDATE – SQL запрос, позволяющий внести изменения или дописывать новую информацию в те записи, которые уже существуют.

Внесем корректировки в шестую запись (AuthorId = 6). Значения изменим для полей имени, фамилии и возраста автора.

Затем, обратимся к БД, чтобы вывести все имеющиеся записи:

Мы видим изменения информации в записи автора под номером 6.

DELETE

DELETE – SQL запрос, выполняя который в СУБД производится операция удаления определенной строки из таблицы в БД.

Обратимся к «tAuthors» с командой на удаление строки, где AuthorId = 5:

Чтобы увидеть изменения, снова обратимся к базе для вывода всех записей:

Мы видим, что запись автора под номером 5 теперь отсутствует в «tAuthors» и, соответственно, не выводится с другими записями.

DROP

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

После рассмотрения ряда простых запросов к БД мы можем полностью удалить нашу таблицу «tAuthors» целиком, выполнив простой SQL запрос:

Далее рассмотрим сложные запросы SQL.

SQL команды

Выделяют следующие группы команд SQL:

Команды языка определения данных

Команды языка определения данных DDL (Data Definition Language, язык определения данных) — это подмножество SQL, используемое для определения и модификации различных структур данных.
К данной группе относятся команды предназначенные для создания, изменения и удаления различных объектов базы данных. Команды CREATE (создание), ALTER (модификация) и DROP (удаление) имеют большинство типов объектов баз данных (таблиц, представлений, процедур, триггеров, табличных областей, пользователей и др.). Т.е. существует множество команд DDL, например, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, CREATE USER, CREATE ROLE и т.д.

Некоторым кажется, что применение DDL является прерогативой администраторов базы данных, а операторы DML должны писать разработчики, но эти два языка не так-то просто разделить. Сложно организовать аффективный доступ к данным и их обработку, не понимая, какие структуры доступны и как они связаны. Также сложно проектировать соответствующие структуры, не зная, как они будут обрабатываться.

Команды языка управления данными

С помощью команд языка управления данными ( DCL (Data Control Language) ) можно управлять доступом пользователей к базе данных. Операторы управления данными включают в себя применяемые для предоставления и отмены полномочий команды GRANT и REVOKE, а также команду SET ROLE, которая разрешает или запрещает роли для текущего сеанса.

Команды языка управления транзакциями

Команды языка управления транзакциями ( TCL (Тгаnsасtiоn Соntrol Language) ) команды позволяют определить исход транзакции.
Команды управления транзакциями управляют изменениями в базе данных, которые осуществляются командами манипулирования данными.
Транзакция (или логическая единица работы) – неделимая с точки зрения воздействия на базу данных последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации) такая, что либо результаты всех операторов, входящих в транзакцию, отображаются в БД, либо воздействие всех этих операторов полностью отсутствует.
COMMIT — заканчивает («подтверждает») текущую транзакцию и делает постоянными (сохраняет в базе данных) изменения, осуществленные этой транзакцией. Также стирает точки сохранения этой транзакции и освобождает ее блокировки. Можно также использовать эту команду для того, чтобы вручную подтвердить сомнительную распределенную транзакцию.
ROLLBACK — выполняет откат транзакции, т.е. отменяет все изменения, сделанные в текущей транзакции. Можно также использовать эту команду для того, чтобы вручную отменить работу, проделанную сомнительной распределенной транзакцией.
Понятие транзакции имеет непосредственную связь с понятием целостности базы данных. Очень часто база данных может обладать такими ограничениями целостности, которые просто невозможно не нарушить, выполняя только один оператор изменения БД. Например, невозможно принять сотрудника в отдел, название и код которого отсутствует в базе данных.
В системах с развитыми средствами ограничения и контроля целостности каждая транзакция начинается при целостном состоянии базы данных и должна оставить это состояние целостными после своего завершения. Несоблюдение этого условия приводит к тому, что вместо фиксации результатов транзакции происходит ее откат (т.е. вместо оператора COMMIT выполняется оператор ROLLBACK), и база данных остается в таком состоянии, в котором находилась к моменту начала транзакции, т.е. в целостном состоянии.
В связи со свойством сохранения целостности БД транзакции являются подходящими единицами изолированности пользователей, т.е., если с каждым сеансом работы с базой данных ассоциируется транзакция, то каждый пользователь начинает работу с согласованным состоянием базы данных, т.е. с таким состоянием, в котором база данных могла бы находиться, даже если бы пользователь работал с ней в одиночку.

Команды языка манипулирования данными

Команды языка манипулирования данными DML (Data Manipulation Language) позволяют пользователю перемещать данные в базу данных и из нее:

• INSERT — осуществляет вставку строк в таблицу.
• DELETE — осуществляет удаление строк из таблицы.
• UPDATE — осуществляет модификацию данных в таблице.
• SELECT — осуществляет выборку данных из таблиц по запросу.

Каждый, кто работает с SQL в среде Oracle, должен вооружиться книгами: справочником по языку SQL, таким как «Oracle SQL: The Essential Reference? (O’Reilly), руководством по оптимизации производительности, например «Oracle SQL Tuning Pocket Reference» (O’Reilly).

Для простоты ниже мы приведем несколько основных команд SQL.

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

Настройка базы данных для примеров

Создайте базу данных для демонстрации работы команд. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql. После этого откройте терминал и войдите в консоль MySQL с помощью следующей команды (статья предполагает, что MySQL уже установлен в системе):

mysql -u root -p

Затем введите пароль.

Выполните следующую команду. Назовём базу данных «university»:

CREATE DATABASE university;
USE university;
SOURCE <path_of_DLL.sql_file>;
SOURCE <path_of_InsertStatements.sql_file>;

Команды SQL для работы с базами данных

Изучить все тонкости работы с SQL можно в он – лайн университете skillbox

1. SQL команды – просмотр доступных баз данных

SHOW DATABASES;

2. SQL команды – создание новой базы данных

CREATE DATABASE;

3. Выбор базы данных для использования

USE <database_name>; 

4. Импорт SQL-команд из файла .sql

SOURCE <path_of_.sql_file>; 

5. Удаление базы данных

DROP DATABASE <database_name>; 

SQL команды – работа с таблицами

6. Просмотр таблиц, доступных в базе данных

SHOW TABLES; 

7. Создание новой таблицы

CREATE TABLE <table_name1> (
  <col_name1> <col_type1>,
  <col_name2> <col_type2>,
  <col_name3> <col_type3>
  PRIMARY KEY (<col_name1>),
  FOREIGN KEY (<col_name2>) REFERENCES <table_name2>(<col_name2>)
); 

Ограничения целостности при использовании CREATE TABLE

Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:

• ячейка таблицы не может иметь значение NULL;
• первичный ключ — PRIMARY KEY (col_name1, col_name2, …);
• внешний ключ — FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn).

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

Пример

Создайте таблицу «instructor»:

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)
); 

8. Сведения о таблице

Можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы следующей командой:

DESCRIBE <table_name>; 

9. Добавление данных в таблицу

INSERT INTO <table_name> (<col_name1>, <col_name2>, <col_name3>, …)
  VALUES (<value1>, <value2>, <value3>, …); 

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

INSERT INTO <table_name>
  VALUES (<value1>, <value2>, <value3>, …); 

10. Обновление данных таблицы

UPDATE <table_name>
  SET <col_name1> = <value1>, <col_name2> = <value2>, ...
  WHERE <condition>; 

11. Удаление всех данных из таблицы

DELETE FROM <table_name>; 

12. Удаление таблицы

DROP TABLE <table_name>; 

SQL команды – команды для создания запросов

13. SELECT

SELECT используется для получения данных из определённой таблицы:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>; 

Следующей командой можно вывести все данные из таблицы:

SELECT * FROM <table_name>; 

14. SELECT DISTINCT

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

SELECT DISTINCT <col_name1>, <col_name2>, …
  FROM <table_name>; 

15. WHERE

Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <condition>; 

В запросе можно задавать следующие условия:

• сравнение текста;
• сравнение численных значений;
• логические операции AND (и), OR (или) и NOT (отрицание).

Пример

Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в 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; 

16. GROUP BY

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

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  GROUP BY <col_namex>; 

Пример

Выведем количество курсов для каждого факультета:

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

17. HAVING

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

SELECT <col_name1>, <col_name2>, ...
  FROM <table_name>
  GROUP BY <column_namex>
  HAVING <condition> 

Пример

Выведем список факультетов, у которых более одного курса:

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

18. ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC.

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  ORDER BY <col_name1>, <col_name2>, … ASC|DESC; 

Пример

Выведем список курсов по возрастанию и убыванию количества кредитов:

SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC; 

19. BETWEEN

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

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namex> BETWEEN <value1> AND <value2>; 

Пример

Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

SELECT * FROM instructor
  WHERE salary BETWEEN 50000 AND 100000; 

20. LIKE

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

Есть два свободных оператора, которые используются в LIKE:

• % (ни одного, один или несколько символов);
• _ (один символ).

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namex> LIKE <pattern>; 

Пример

Выведем список курсов, в имени которых содержится «to», и список курсов, название которых начинается с «CS-»:

SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE 'CS-___'; 

21. IN

С помощью IN можно указать несколько значений для оператора WHERE:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namen> IN (<value1>, <value2>, …); 

Пример

Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:

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

22. JOIN

JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name1>
  JOIN <table_name2>
  ON <table_name1.col_namex> = <table2.col_namex>; 

Пример 1

Выведем список всех курсов и соответствующую информацию о факультетах:

SELECT * FROM course 
    JOIN department 
    ON course.dept_name=department.dept_name;

Пример 2

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

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

Пример 3

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

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

23. View

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

Создание

CREATE VIEW <view_name> AS
  SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <condition>; 

Удаление

DROP VIEW <view_name>; 

Пример

Создадим view, состоящую из курсов с 3 кредитами:

24. SQL команды – агрегатные функции

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

• COUNT (col_name) — возвращает количество строк;
• SUM (col_name) — возвращает сумму значений в данном столбце;
• AVG (col_name) — возвращает среднее значение данного столбца;
• MIN (col_name) — возвращает наименьшее значение данного столбца;
• MAX (col_name) — возвращает наибольшее значение данного столбца.

25. SQL команды – вложенные подзапросы

Вложенные подзапросы — это SQL-запросы, которые включают выражения SELECT, FROM и WHERE, вложенные в другой запрос.

Пример

Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:

SELECT DISTINCT course_id
  FROM section
  WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (
    SELECT course_id
    FROM section
    WHERE semester = ‘Spring’ AND year= 2010
  );