Урок 1 из 0
В процессе

Язык запросов SQL

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

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

Итак, SQL является прежде всего информационно-логическим языком, предназначенным для описания, изменения и извлечения данных, хранимых в реляционных базах данных. Также он является непроцедурным языком. Чтобы с его помощью решить задачу, сообщите SQL, что именно вам нужно. И при этом не надо говорить, каким образом получить для вас то, что вы хотите. Система управления базами данных (СУБД) сама решит, как лучше всего выполнить ваш запрос.

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

Типы данных в SQL

  • INT или INTEGER – целое число (до 10 значащих цифр)
  • SMALLINT – короткое целое число (до 5 значащих цифр)
  • FLOAT – число с плавающей точкой
  • REAL – число с плавающей точкой и нулевое значение
  • CHAR(n) – символьная строка фиксированной длинны в n символов
  • VARCHAR(n) – символьная строка переменной длины, не больше n символов
  • NCHAR(n) – тоже самое, что CHAR(n), только в кодировке unicode
  • NVARCHAR(n) – тоже самое, что VARCHAR(n), только в кодировке unicode
  • DATE – дата, в определенном формате (по умолчанию ГГГГ-ММ-ДД)
  • DATETIME – дата и время в 24-м формате (по умолчанию ГГГГ-ММ-ДД ЧЧ:ММ:СС )
  • TIME – время в 24-м формате (по умолчанию ЧЧ:ММ:СС)

Каждый столбец должен поддерживать определенные заранее форматы данных.

Синтаксис SQL запроса

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

В наиболее общем виде он имеет следующий формат:

SELECT [DISTINCT или ALL] <имена полей возвращаемых запросом>

FROM <имена таблиц используемых в запросе>

WHERE <условия отбора записей>

GROUP BY <имена группируемых полей>

HAVING <условия отбора>

ORDER BY <список полей сортировки>  [прядок сортировки DESC/ASC]

SELECT – ключевое слово, которое сообщает СУБД, что эта команда – запрос. Все запросы начинаются этим служебным словом с последующим пробелом. За ним может следовать способ выборки – с удалением дубликатов записей (DISTINCT) или без удаления (ALL, по умолчанию).

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

При необходимости поля таблиц можно переименовать, для этого используется оператор AS.

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

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

Все последующие разделы оператора SELECT являются необязательными.

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

В выражении условий раздела WHERE могут быть использованы обычные операторы сравнения (=, <>, >, <, >=, <= ), а также различные логические операторы языка SQL о которых поговорим чуть позже. Также можно проверить поле на наличие или отсутствие значения с помощью операторов IS NULL и IS NOT NULL.

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

Рассмотрим пример, изображенный на рисунке ниже

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

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

Раздел ORDER BY определяет список полей сортировки. Последовательность перечисления полей имеет важное значение и определяет порядок сортировки в результирующем отношении.

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

Операторы языка SQL

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

SELECT Определяет какие данные должны отобразиться в результате работы запроса.
INSERT Определяет какие данные и в какую таблицу будут добавлены.
UPDATE Обновляет уже существующие записи.
DELETE Удаляет записи.

Из этих четырех операторов только SELECT выводит данные по заданным критериям, остальные их так или иначе изменяют.

Далее рассмотрим основные логические операторы

AND TRUE, если оба выражения логического типа равны TRUE.
OR TRUE, если одно из выражений логического типа равно TRUE.
BETWEEN TRUE, если операнд находится в указанном диапазоне. Может работать с шаблонами применимыми к оператору LIKE.
LIKE TRUE, если оператор соответствует шаблону.
IN TRUE, если операнд содержится в заданном списке выражений.
NOT Меняет значение оператора логического типа на противоположное.

 

Примеры использования логических операторов

AND:

Выбрать в таблице Orders заказы, которые были доставлены до 1 января 1997 года (колонка ShippedDate) и которые оформил продавец с ID 5.

Запрос:

Результат:

OR:

Выбрать в таблице Employers продавцов, которые живут в Сиэтле или в Лондоне.

Запрос:

 

Результат:

BETWEEN:

     Выбрать в таблице Orders заказы, дата заказа которых между 1 июля 1996 и 15 июля 1996. Важно: при использовании оператора BETWEEN начальная граница включается в выборку, а конечная нет.

Запрос:

Результат:

LIKE:

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

Запрос:

Результат:

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

  • % – Знак процента представляет нулевой, один, несколько символов;
  • _ – Нижнее подчеркивание представляет собой один символ.
  • [a-h] – любой одиночный символ, содержащийся в диапазоне [a-h]
  • [^a-s] – любой символ, не содержащийся в диапазоне [^ a- s]

 

IN:

Выбрать в таблице Employers работников с ID 1, 2 и 3.

Запрос:

Результат:

 

NOT:

Вывести всех работников живущих не в Лондоне

Запрос:

Результат:

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

Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение. Агрегатные функции, за исключением COUNT(*), не учитывают значения NULL.

COUNT Количество строк или непустых значений полей, которые выбрал запрос
SUM Сумма всех выбранных значений данного поля
AVG Среднеарифметическое значение всех выбранных значений данного поля
MIN Наименьшее из всех выбранных значений данного поля
MAX

Наибольшее из всех выбранных значений данного поля

 

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

Столбец результатов агрегатной функции не имеет своего названия, поэтому на практике для его названия используется оператор AS.