Последнее обновление: 24.06.2017

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

SQL Server был создан компанией Microsoft. Первая версия вышла в 1987 году. А текущей версией является версия 16, которая вышла в 2016 году и которая будет использоваться в текущем руководстве.

SQL Server долгое время был исключительно системой управления базами данных для Windows, однако начиная с версии 16 эта система доступна и на Linux.

SQL Server характеризуется такими особенностями как:

Производительность. SQL Server работает очень быстро.

Надежность и безопасность. SQL Server предоставляет шифрование данных.

Простота. С данной СУБД относительно легко работать и вести администрирование.

Центральным аспектом в MS SQL Server, как и в любой СУБД, является база данных. База данных представляет хранилище данных, организованных определенным способом. Нередко физически база данных представляет файл на жестком диске, хотя такое соответствие необязательно. Для хранения и администрирования баз данных применяются системы управления базами данных (database management system) или СУБД (DBMS). И как раз MS SQL Server является одной из такой СУБД.

Для организации баз данных MS SQL Server использует реляционную модель. Эта модель баз данных была разработана еще в 1970 году Эдгаром Коддом. А на сегодняшний день она фактически является стандартом для организации баз данных.

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

Для идентификации каждой строки в рамках таблицы применяется первичный ключ (primary key). В качестве первичного ключа может выступать один или несколько столбцов. Используя первичный ключ, мы можем ссылаться на определенную строку в таблице. Соответственно две строки не могут иметь один и тот же первичный ключ.

Через ключи одна таблица может быть связана с другой, то есть между двумя таблицами могут быть организованы связи. А сама таблица может быть представлена в виде отношения ("relation").

Для взаимодействия с базой данных применяется язык SQL (Structured Query Language). Клиент (например, внешняя программа) отправляет запрос на языке SQL посредством специального API. СУБД должным образом интерпретирует и выполняет запрос, а затем посылает клиенту результат выполнения.

Изначально язык SQL был разработан в компании IBM для системы баз данных, которая называлась System/R. При этом сам язык назывался SEQUEL (Structured English Query Language). Хотя в итоге ни база данных, ни сам язык не были впоследствии официально опубликованы, по традиции сам термин SQL нередко произносят как "сиквел".

В 1979 году компания Relational Software Inc. разработала первую систему управления баз данных, которая называлась Oracle и которая использовала язык SQL. В связи с успехом данного продукта компания была переименована в Oracle.

Впоследствии стали появляться другие системы баз данных, которые использовали SQL. В итоге в 1989 году Американский Национальный Институт Стандартов (ANSI) кодифицировал язык и опубликовал его первый стандарт. После этого стандарт периодически обновлялся и дополнялся. Последнее его обновление состоялось в 2011 году. Но несмотря на наличие стандарта нередко производители СУБД используют свои собственные реализации языка SQL, которые немного отличаются друг от друга.

Выделяются две разновидности языка SQL: PL-SQL и T-SQL. PL-SQL используется в таких СУБД как Oracle и MySQL. T-SQL (Transact-SQL) применяется в SQL Server. Собственно поэтому в рамках текущего руководства будет рассматриваться именно T-SQL.

В зависимости от задачи, которую выполняет команда T-SQL, он может принадлежать к одному из следующих типов:

DDL (Data Definition Language / Язык определения данных). К этому типу относятся различные команды, которые создают базу данных, таблицы, индексы, хранимые процедуры и т.д. В общем определяют данные.

В частности, к этому типу мы можем отнести следующие команды:

• CREATE : создает объекты базы данных (саму базу даных, таблицы, индексы и т.д.)

  ALTER : изменяет объекты базы данных

  DROP : удаляет объекты базы данных

  TRUNCATE : удаляет все данные из таблиц

DML (Data Manipulation Language / Язык манипуляции данными). К этому типу относят команды на выбору данных, их обновление, добавление, удаление - в общем все те команды, с помощью которыми мы можем управлять данными.

К этому типу относятся следующие команды:

• SELECT : извлекает данные из БД

  UPDATE : обновляет данные

  INSERT : добавляет новые данные

  DELETE : удаляет данные

DCL (Data Control Language / Язык управления доступа к данным). К этому типу относят команды, которые управляют правами по доступу к данным. В частности, это следующие команды:

• GRANT : предоставляет права для доступа к данным

  REVOKE : отзывает права на доступ к данным

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

Логическая обработка запросов

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

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

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

На следующем рисунке представлена схема логической обработки запроса в SQL Server 2012:

Заметьте, что при написании запроса предложение SELECT всегда пишется первым, но в процессе логической обработки оно находится практически в самом конце - непосредственно перед обработкой предложения ORDER BY.

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

1. Вычисление выражений

   Удаление дубликатов

OFFSET-FETCH/TOP

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

Предложения, поддерживающие оконные функции

Как видно из предыдущего рисунка, напрямую оконные функции поддерживают только предложения SELECT и ORDER BY. Причина ограничения заключается в том, чтобы в начале работы с окном избежать неоднозначности при работе с (почти) финальным результирующим набором запроса. Если разрешить оконные функции на этапах, предшествующих этапу SELECT, начальные окна этих этапов могут отличаться от окна этапа SELECT и, поэтому, в некоторых формах запроса будет очень сложно определить правильный результат.

Я попытаюсь продемонстрировать эту неоднозначность на примере. Сначала выполните следующий код, чтобы создать таблицу T1 и наполнить ее данными:

SET NOCOUNT ON; USE TSQL2012; IF OBJECT_ID("dbo.T1", "U") IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 (col1 VARCHAR(10) NOT NULL CONSTRAINT PK_T1 PRIMARY KEY); INSERT INTO dbo.T1(col1) VALUES("A"),("B"),("C"),("D"),("E"),("F"); GO

Допустим, что оконные функции разрешены на этапах, предшествующих SELECT, например на этапе WHERE. Посмотрите на следующий запрос и попытайтесь определить, какие значения col1 должны содержаться в результате:

Прежде чем говорить, что это очевидно, что это должны быть значения C, D и Е, вспомните о принципе «все сразу» в SQL. Этот принцип подразумевает, что с точки зрения концепции все выражения одного логического этапа выполняются одновременно. Это значит, что порядок следования выражений не должен влиять на результат. Если так, то следующий запрос должен быть семантически эквивалентен такому:

Сможете ли вы на этот раз определить, какое выражение правильное? Это C, D и Е или только C?

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

При анализе блок-схемы на рисунке выше вы могли заметить, что на этапе SELECT оконные функции поддерживает шаг 5-1 (Вычисление выражений) и он выполняет перед шагом 5-2 (Удаление дубликатов). Если вы спросите, почему так важно знать такие детали, я продемонстрирую, зачем это нужно.

Вот вопрос, возвращающий атрибуты empid и country всех сотрудников из таблицы сотрудников Employees:

SELECT empid, country FROM HR.Employees;

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

SELECT DISTINCT country, ROW_NUMBER() OVER(ORDER BY country) AS rownum FROM HR.Employees;

Некоторые будут ожидать такой результат:

Но на самом деле вы получите это:

А теперь вспомните, что в этом запросе функция ROW_NUMBER вычисляется на шаге 5-1, на котором вычисляются выражения списка SELECT - до удаления дубликатов на шаге 5-2. Функция ROW_NUMBER назначает девять уникальных номеров строк, содержащих информацию о сотрудниках, поэтому предложению DISTINCT нечего удалять.

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

WITH EmpCountries AS (SELECT DISTINCT country FROM HR.Employees) SELECT country, ROW_NUMBER() OVER(ORDER BY country) AS rownum FROM EmpCountries;

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

Тот факт, что оконные функции оцениваются на этапе SELECT или ORDER BY означает, что окно, определенное для вычисления, - до применения последующих ограничений - является промежуточной формой строк, полученной после всех предшествующих фаз, то есть после применения FROM со всеми табличными операторами (например, соединениями), а также фильтрации с применением WHERE, группировки и фильтрации групп. Такой запрос можно считать примером:

Сначала вычисляется предложение FROM, после чего выполняется соединение. Затем фильтр оставляет только строки, относящиеся к 2007 году. После этого оставшиеся строки группируются по идентификатору сотрудника. Только после этого вычисляются выражения в списке SELECT, в числе которых функция RANK, которая вычисляется с использование упорядочения по убыванию общего количества. Если бы в списке SELECT были другие оконные функции, в них в качестве исходной точки использовался этот же набор результатов.

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

В обход ограничений

Я объяснил, почему запретили использование оконных функций на этапах логической обработки запроса, предшествующих предложению SELECT. Но что, если нужно выполнять фильтрацию или группировку на основе вычислений, выполненных в оконных функциях? Решение заключается в использовании табличного выражения, такого как CTE или производная таблица. Заставьте запрос вызывать оконную функцию в его списке SELECT, назначив выражению псевдоним. Определите на основе этого запроса табличное выражение, после чего сошлитесь на него в запросе по псевдониму.

Вот пример, демонстрирующий, как можно фильтровать на основе результатов оконной функции с использованием CTE:

В инструкциях, изменяющих данные, оконные функции полностью запрещены, потому что в этих инструкциях не поддерживаются предложения SELECT и ORDER BY. Но есть случаи, когда оконные функции нужны в изменяющих данные инструкциях. Табличные выражения позволяют решить и эту проблему, потому что T-SQL позволяет менять данные через табличные выражения. Продемонстрирую это поведение на примере UPDATE. Сначала выполните следующий код, чтобы создать таблицу T1 со столбцами col1 и col2 и наполнить ее данными:

SET NOCOUNT ON; USE TSQL2012; IF OBJECT_ID("dbo.T1", "U") IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 (col1 INT NULL, col2 VARCHAR(10) NOT NULL); INSERT INTO dbo.T1(col2) VALUES("C"),("A"),("B"),("A"),("C"),("B"); GO

Значения столбца col2 определены явно, a col1 был заполнен значениями NULL.

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

UPDATE dbo.T1 SET col1 = ROW_NUMBER() OVER(ORDER BY col2);

Но, как вы помните, в такой инструкции это запрещено. Обходной способ заключается в создании запроса по отношению к T1, который возвращает col1, и выражения, основанного на функции ROW_NUMBER (назовем ее rownum); определите табличное выражение, основанное на этом запросе, и, наконец, примените инструкцию UPDATE к CTE для присвоения значения rownum столбцу col1:

WITH C AS (SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col2) AS rownum FROM dbo.T1) UPDATE C SET col1 = rownum; SELECT col1, col2 FROM dbo.T1;

Получите данные из T1 - вы увидите, что все строки получили уникальное значение в столбце col1:

Возможность создания дополнительных фильтров

Я показал, как в T-SQL можно прибегнуть к обходному решению и косвенным образом использовать оконные функции в элементах, которые не поддерживают их напрямую. Это обходное решение основано на применении табличного выражения в форме CTE или производной таблицы. Приятно иметь дополнительный вариант, но в табличном выражении используется дополнительный уровень запроса и все немного усложняется. Приведенные мной примеры просты, но как насчет длинных и сложных запросов. Возможно ли более простое решение без этого дополнительного уровня?

Если говорить об оконных функциях, то в SQL Server на текущий момент нет другого решения. Вместе с тем, интересно посмотреть, как другие справляются с этой проблемой. Например, в Teradata создали фильтрующее предложение, которое называется QUALIFY и принципиально вычисляется после предложения SELECT. Это означает, что в нем можно напрямую обращаться к оконным функциям, как в следующем примере:

Не работает в SQL Server 2012 SELECT orderid, orderdate, val FROM Sales.OrderValues QUALIFY RANK() OVER(ORDER BY val DESC)

Более того, можно ссылаться на псевдонимы столбцов, определенных в списке SELECT, так:

Не работает в SQL Server 2012 SELECT orderid, orderdate, val, RANK() OVER(ORDER BY val DESC) AS rnk FROM Sales.OrderValues QUALIFY rnk

Предложения QUALIFY нет в стандартном SQL - оно поддерживается только в продуктах Teradata. Но оно кажется очень интересным решением, и было бы неплохо, если бы и стандарт, и в SQL Server удовлетворили такую потребность.

Повторное использование определений окон

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

SELECT empid, ordermonth, qty, SUM(qty) OVER (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_sum_qty, AVG(qty) OVER (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_avg_qty, MIN(qty) OVER (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_min_qty, MAX(qty) OVER (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_max_qty FROM Sales.EmpOrders;

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

SQL Server пока не поддерживает предложение WINDOW . В стандартном SQL можно сократить предыдущий запрос с использованием предложения WINDOW так:

Не работает в SQL Server 2012 SELECT empid, ordermonth, qty, SUM(qty) OVER W1 AS run_sum_qty, AVG(qty) OVER W1 AS run_avg_qty, MIN(qty) OVER W1 AS run_min_qty, MAX(qty) OVER W1 AS run_max_qty FROM Sales.EmpOrders WINDOW W1 AS (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

Как видите, разница ощутима. В данном случае предложение WINDOW присваивает имя W1 полному определению окна с параметрами секционирования, упорядочения и кадрирования. После этого W1 используется в качестве определения окна во всех четырех функциях. Предложение WINDOW довольно сложное. Как уже говорилось, не обязательно присваивать имя полному определению окна - можно назначать имя только части определения. В таком случае определение окна содержит смесь именованных частей и явно заданных параметров. Кстати сказать, описание предложения WINDOW в стандарте SQL занимает целых десять страниц! И разобраться в них не так-то просто.

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

sql часто называют языком эсперанто для систем управления базами данных (СУБД). Действительно, в мире нет другого языка для работы с базами данных (БД), который бы настолько широко использовался в программах. Первый стандарт sol появился в 1986 г. и к настоящему времени завоевал всеобщее признание. Его можно использовать даже при работе с нереляционными СУБД. В отличие от других программных средств, таких, как языки Си и Кобол, являющихся прерогативой программистов-профессионалов, sql применяется специалистами из самых разных областей. Программисты, администраторы СУБД, бизнес-аналитики — все они с успехом обрабатывают данные с помощью sql. Знание этого языка полезно всем, кому приходится иметь дело с БД.

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

Трудно ли изучить sql? Это зависит от того, насколько глубоко вы собираетесь вникать в суть. Для того чтобы стать профессионалом, придется изучить очень многое. Язык sql появился в 1974 г. как предмет небольшой исследовательской работы, состоявшей из 23 страниц, и с тех пор прошел долгий путь развития. Текст действующего ныне стандарта — официального документа "the international standard database language sql" (обычно называемого sql-92) — содержит свыше шести сотен страниц, однако в нем ничего не говорится о конкретных особенностях версий sol, реализованных в СУБД фирм microsoft, oracle, sybase и др. Язык настолько развит и разнообразен, что лишь простое перечисление его возможностей потребует нескольких журнальных статей, а если собрать все, что написано на тему sol, то получится многотомная библиотека.

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

Что такое sql?

sql — это специализированный непроцедурный язык, позволяющий описывать данные, осуществлять выборку и обработку информации из реляционных СУБД. Специализированность означает, что sol предназначен лишь для работы с БД; нельзя создать полноценную прикладную систему только средствами этого языка — для этого потребуется использовать другие языки, в которые можно встраивать sql-команды. Поэтому sql еще называют вспомогательным языковым средством для обработки данных. Вспомогательный язык используется только в комплексе с другими языками.

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

Наиболее существенным свойством sql является возможность доступа к реляционным БД. Многие даже считают, что выражения "БД, обрабатываемая средствами sql" и "реляционная БД" — синонимы. Однако скоро вы убедитесь, что между ними имеется разница. В стандарте sql-92 даже нет термина отношение (relation).

Что такое реляционная СУБД?

Если не вдаваться в подробности, то реляционная СУБД — это система, основанная на реляционной модели управления данными.

Понятие реляционной модели было впервые предложено в работе д-ра Е. Ф. Кодда, опубликованной в 1970 г. В ней был описан математический аппарат для структуризации данных и управления ими, а также предложена абстрактная модель для представления любой реальной информации. До этого при использовании БД требовалось учитывать конкретные особенности хранения в ней информации. Если внутренняя структура БД изменялась (например, с целью повышения быстродействия), приходилось перерабатывать прикладные программы, даже если на логическом уровне никаких изменений не происходило. Реляционная модель позволила отделить частные особенности хранения данных от уровня прикладной программы. В самом деле, модель никак не описывает способы хранения информации и доступа к ней. Учитывается лишь то, как эта информация воспринимается пользователем. Благодаря появлению реляционной модели качественно изменился подход к управлению данными: из искусства оно превратилось в науку, что привело к революционному развитию отрасли.

Основные понятия реляционной модели

Согласно реляционной модели, отношение (relation) — это некоторая таблица с данными. Отношение может иметь один или несколько атрибутов (признаков), соответствующих столбцам этой таблицы, и некоторое множество (возможно, пустое) данных, представляющих собой наборы этих атрибутов (их называют n-арными кортежами, или записями) и соответствующих строкам таблицы.

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

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

Обратите внимание: атрибут обязательно должен иметь одно из допустимых значений. Задание сразу нескольких значений запрещено. Таким образом, помимо требования принадлежности значений атрибута некоторому домену, должно соблюдаться условие его атомарности. Это означает, что для этих значений недопустима декомпозиция, т. е. нельзя разбить их на более мелкие части, не потеряв основного смысла. Например, если бы значение атрибута одновременно содержало Понедельник и Вторник, то можно было бы выделить две части, сохранив первоначальный смысл — ДеньНедели; следовательно, это значение атрибута не является атомарным. Однако если попробовать разбить значение "Понедельник" на части, то получится набор из отдельных букв — от "П" до "К"; исходный смысл утерян, поэтому значение "Понедельник" является атомарным.

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

В своей исходной работе д-р Кодд определил набор из восьми операторов, получивший название реляционной алгебры. Четыре оператора — объединение, логическое умножение, разность и Декартово произведение — были перенесены из традиционной теории множеств; остальные операторы были созданы специально для обработки отношений. В последующих работах д-ра Кодда, Криса Дейта и других исследователей были предложены дополнительные операторы. Далее в этой статье будут рассмотрены три реляционных оператора — продукция (project), ограничения (select, или restrict) и слияние (join).

sql и реляционная модель

Теперь, когда вы познакомились с реляционной моделью, давайте забудем о ней. Конечно, не навсегда, а лишь для того, чтобы объяснить следующее: хотя именно предложенная д-ром Коддом реляционная модель была использована при разработке sql, между ними нет полного или буквального соответствия (это одна из причин, почему в стандарте sql-92 отсутствует термин отношение). Например, понятия таблица sql и отношение не являются равнозначными, потому что в таблицах может быть сразу несколько одинаковых строк, тогда как в отношениях появление идентичных кортежей не разрешено. К тому же в sql не предусмотрено использование реляционных доменов, хотя в некоторой степени их роль играют типы данных (некоторые влиятельные сторонники реляционной модели предпринимают сейчас попытку добиться включения в будущий стандарт sql реляционных доменов).

К сожалению, несоответствие между sql и реляционной моделью породило множество недоразумений и споров за прошедшие годы. Но так как основная тема статьи — изучение sql, а не реляционной модели, эти проблемы здесь не рассматриваются. Просто следует запомнить, что между терминами, применяемыми в sql и в реляционной модели, имеются различия. Далее в статье будут использоваться только термины, принятые в sql. Вместо отношений, атрибутов и кортежей будем применять их sql-аналоги: таблицы, столбцы и строки.

Статический и динамический sql

Возможно, вам уже знакомы такие термины, как статический и динамический sql. sql-запрос является статическим, если он компилируется и оптимизируется на стадии, предшествующей выполнению программы. Мы уже упоминали одну из форм статического sql, когда говорили о встраивании sql-команд в программы на Си или Коболе (для таких выражений существует еще другое название — встроенный sql). Как вы, наверное, догадываетесь, динамический sql-запрос компилируется и оптимизируется в ходе исполнения программы. Как правило, обычные пользователи применяют именно динамический sql, позволяющий создавать запросы в соответствии с сиюминутными нуждами. Один из вариантов изпользования динамических sql-запросов — их интерактивный или непосредственный вызов (существует даже специальный термин — directsql), когда отправляемые на обработку запросы вводятся в интерактивном режиме с терминала. Между статическим и динамическим sql имеются определенные различия в синтаксисе применяемых конструкций и особенностях исполнения, однако эти вопросы выходят за рамки статьи. Отметим лишь, что для ясности понимания примеры даются в форме direct sql-запросов, поскольку это позволяет научиться использовать sql не только программистам, но и большинству конечных пользователей.

Как изучать sql

Теперь вы готовы к написанию своих первых sql-запросов. Если у вас имеется доступ к БД через sql и вы захотите воспользоваться нашими примерами на практике, то учтите следующее: вы должны входить в систему как пользователь с неограниченными полномочиями и вам потребуются программные средства интерактивной обработки sql-запросов (если речь идет о сетевой БД, следует переговорить с администратором БД о предоставлении вам соответствующих прав). Если доступа к БД через sql нет — не огорчайтесь: все примеры очень простые и в них можно разобраться "всухую", без выхода на машину.

Для того чтобы выполнить какие-либо действия в sql, следует выполнить выражение на языке sql. Встречается несколько типов выражений, однако среди них можно выделить три основные группы: ddl-команды (data definition language — язык описания данных), dml-команды (data manipulation language — язык манипуляций с данными) и средства контроля за данными. Таким образом, в sql в каком-то смысле объединены три различных языка.

Команды языка описания данных

Начнем с одной из основных ddl-команд — create table (Создать таблицу). В sql бывают таблицы нескольких типов, основными являются два типа: базовые (base) и выборочные (views). Базовыми являются таблицы, относящиеся к реально существующим данным; выборочные — это "виртуальные" таблицы, которые создаются на основе информации, получаемой из базовых таблиц; но для пользователей формы выглядят как обычные таблицы. Команда create table предназначена для создания базовых таблиц.

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

create table ИмяТаблицы (Столбец ТипДанных) ;

create и table — это ключевые слова sql; ИмяТаблицы, Столбец и ТипДанных — это формальные параметры, вместо которых пользователь каждый раз вводит фактические значения. Параметры Столбец и ТипДанных заключены в круглые скобки. В sql круглые скобки обычно используются для группировки отдельных элементов. В данном случае они позволяют объединить определения для столбца. Стоящий в конце знак "точка с запятой" является разделителем команд. Он должен завершать любое выражение на языке sql.

Рассмотрим пример. Пусть нужно создать таблицу для хранения данных обо всех встречах (appointments). Для этого в sql следует ввести команду:

create table appointments (appointment_date date) ;

После выполнения этой команды будет создана таблица с именем appointments, где имеется один столбец appointment_date, в котором могут записываться данные типа date. Поскольку на текущий момент данные еще не вводились, количество строк в таблице равно нулю (с помощью команды create table только дается определение таблицы; реальные значения вводятся командой insert, которая рассматривается далее).

Параметры appointments и appointment_date называются идентификаторами, поскольку они задают имена для конкретных объектов БД, в данном случае — имена для таблицы и столбца соответственно. В sql встречаются идентификаторы двух типов: обычные (regular) и выделенные (delimited). Выделенные идентификаторы заключаются в двойные кавычки, и в них учитывается регистр используемых символов. Обычные идентификаторы не выделяются никакими ограниченными символами, в их написании регистр не учитывается. В этой статье применяются только обычные идентификаторы.

Символы, используемые для построения идентификаторов, должны удовлетворять определенным правилам. В обычных идентификаторах могут использоваться только буквы (не обязательно латинские, но и других алфавитов), цифры и символ подчеркивания. Идентификатор не должен содержать знаков пунктуации, пробелов или специальных символов (#, @, % или!); кроме того, он не может начинаться с цифры или знака подчеркивания. Для идентификаторов можно использовать отдельные ключевые слова sql, но делать это не рекомендуется. Идентификатор предназначен для обозначения некоторого объекта, поэтому у него должно быть уникальное (в рамках определенного контекста) имя: нельзя создать таблицу с именем, которое уже встречается в БД; в одной таблице нельзя иметь столбцы с одинаковыми именами. Кстати, имейте в виду, что appointments и appointments — это одинаковые имена для sql. Одним лишь изменением регистра букв создать новый идентификатор нельзя.

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

create table ИмяТаблицы (Столбец ТипДанных [ { , Столбец ТипДанных } ]) ;

Квадратные скобки использованы для обозначения необязательных элементов, фигурные содержат элементы, которые могут представлять собой перечень однопутных конструкций (при вводе реальной sql-команды ни те ни другие скобки не ставятся). Такой синтаксис позволяет задать любое число столбцов. Обратите внимание, что перед вторым элементом стоит запятая. Если в списке имеется несколько параметров, то они отделяются друг от друга запятыми.

create table appointments2 (appointment_date date , appointment_time time , description varchar (256)) ;

Данная команда создает таблицу appointments2 (новая таблица должна иметь иное имя, так как таблица appointments уже присутствует в БД). Как и в первой таблице, в ней имеется столбец appointment_date для записи даты встреч; кроме того, появился столбец appointment_time для записи времени этих встреч. Параметр description (описание) является текстовой строкой, где может содержаться до 256 символов. Для этого параметра указан тип varchar (сокращение от character varying), поскольку заранее не известно, сколько места потребуется для записи, но ясно, что описание займет не более 256 символов. При описании параметро в типа символьная строка (и некоторых других типов) указывается длина параметра. Ее значение задается в круглых скобках справа от названия типа.

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

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

create table employees (last_name character (13) not null, first_name character (10) not null, hire_date date , branch_office character (15) , grade_level smallint , salary decimal (9 , 2)) ;

В команде встречаются несколько новых элементов. Прежде всего, это выражение not null, стоящее в конце определения столбцов last_name и first_name. С помощью подобных конструкций задаются требования, подлежащие обязательному соблюдению. В данном случае указано, что поля last_name и first_name должны обязательно заполняться при вводе; оставлять эти столбцы пустыми нельзя (это вполне логично: как можно идентифицировать сотрудника, не зная его имени?).

Кроме того, в примере присутствуют три новых типа данных: character, smallint и decimal. До сих пор мы почти не говорили о типах. Хотя в sql нет реляционных доменов, однако имеется набор основных типов данных. Эта информация используется при выделении памяти и сравнении величин; в определенной степени сужает список возможных значений при вводе, однако контроль типов в sql менее строгий, чем в других языках.

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

Кстати, если вы подумали, что датовремя — это опечатка, то ошиблись. К данной группе (datetime) относится большинство используемых в sql типов данных, связанных со временем (такие параметры, как временные интервалы, выделены в отдельную группу). В предыдущем примере уже встречались два типа данных из группы датовремя — date и time.

Следующий тип данных, с которым вы уже знакомы, — character varying (или просто varchar); он относится к группе символьных строк. Если varchar служит для хранения строк переменной длины, то встретившийся в третьем примере тип char предназначен для записи строк, имеющих фиксированное число символов. Например, в столбце last_name будут записываться строки из 13 символов вне зависимости от реально вводимых фамилий, будь то poe или penworth-chickering (в случае с poe оставшиеся 10 символов заполнятся пробелами).

С точки зрения пользователя, varchar и char имеют одинаковый смысл. Зачем нужно было вводить два типа? Дело в том, что на практике обычно приходится искать компромисс между быстродействием и экономией пространства на диске. Как правило, применение строк с фиксированной длиной дает некоторый выигрыш в скорости доступа, однако при слишком большой длине строк пространство на диске расходуется неэкономно. Если в appointments2 для каждой строки комментария резервировать по 256 символов, то это может оказаться нерационально; чаще всего строки будут значительно короче. С другой стороны, фамилии также имеют разную длину, но для них, как правило, требуется около 13 символов; в этом случае потери будут минимальными. Существует хорошее правило: если известно, что длина строки меняется незначительно либо она сравнительно невелика, то используйте char; в остальных случаях — varchar.

Следующие два новых типа данных — smallint и decimal — относятся к группе точных числовых значений. smallint — это сокращенное название от small integer (малое целое). В sql также предусмотрен тип данных integer. Наличие двух схожих типов и в этом случае объясняется соображением экономии пространства. В нашем примере значения параметра grade_level могут быть представлены с помощью двузначного числа, поэтому использован тип smallint; однако на практике не всегда известно, какие максимальные значения могут быть у параметров. Если такой информации нет, то применяйте integer. Реальный объем, выделяемый для хранения параметров типа smallint и integer, и соответствующий диапазон значений для этих параметров индивидуальны для каждой платформы.

Тип данных decimal, обычно используемый для учета финансовых показателей, позволяет задать шаблон с требуемым числом десятичных знаков. Поскольку этот тип служит для точной числовой записи, он гарантирует точность при выполнении математических операций над десятичными данными. Если для десятичных значений использовать типы данных из группы приближенной числовой записи, например float (floating point number — число с плавающей точкой), это приведет к погрешностям округления, поэтому для финансовых расчетов этот вариант не подходит. Для определения параметров типа decimal используется следующая форма записи:

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

Во врезке "Создание таблицы" показан полный вариант обобщенной записи команды create table. В нем присутствуют новые элементы и показан формат для всех рассмотренных типов данных (В принципе встречаются и другие типы данных, но пока мы их не рассматриваем).

На первых порах может показаться, что синтаксис sql-команд слишком сложен. Но вы легко в нем разберетесь, если внимательно изучили приведенные выше примеры. На схеме появился дополнительный элемент — вертикальная черта; он служит для разграничения альтернативных конструкций. Другими словами, при определении каждого столбца нужно выбрать подходящий тип данных (как вы помните, в квадратные скобки заключаются необязательные параметры, а в фигурные скобки — конструкции, которые могут повторяться многократно; в реальных sql-командах эти специальные символы не пишутся). В первой части схемы приведены полные названия для типов данных, во второй — их сокращенные названия; на практике можно использовать любые из них.

Первая часть статьи завершена. Вторая будет посвящена изучению dml-команд insert, select, update и delete. Также будут рассмотрены условия выборки данных, операторы сравнения и логические операторы, использование null-значений и троичная логика.

Создание таблицы. Синтаксис команды create table: в квадратных скобках указаны необязательные параметры, в фигурных — повторяющиеся конструкции.

create table table (column character (length) [ constraint ] | character varying (length) [ constraint ] | date [ constraint ] | time [ constraint ] | integer [ constraint ] | smallint [ constraint ] | decimal (precision, decimal places) [ constraint ] | float (precision) [ constraint ] [{ , column char (length) [ constraint ] | varchar (length) [ constraint ] | date [ constraint ] | time [ constraint ] | int [ constraint ] | smallint [ constraint ] | dec (precision, decimal places) [ constraint ] | float (precision) [ constraint ] }]) ;

Секрет названия sql

В начале 1970-х гг. в ibm приступили к практическому воплощению модели реляционных БД, предложенной д-ром Коддом. Дональд Чамберлин и группа других сотрудников подразделения перспективных исследований создали прототип языка, получивший название structured english query language (язык структурированных англоязычных запросов), или просто sequel. В дальнейшем он был расширен и подвергнут доработке. Новый вариант, предложенный ibm, получил название sequel/2. Его использовали как программный интерфейс (api) для проектирования первой реляционной системы БД фирмы ibm — system/r. Из соображений, связанных с правовыми нюансами, в ibm решили изменить название: вместо sequel/2 использовать sql (structured query language). Эту аббревиатуру часто произносят как "си-ку-эл".

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

Краткое описание операторов языка SQL

Работа с операторами SQL

Выбор данных

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

SELECT column-list FROM table-list

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

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

Для выбора одной колонки применяется следующий синтаксис:

SELECT CompanyName Пример выбора нескольких колонок имеет вид:

SELECT CompanyName, ContactName, ContactTitle

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

SELECT Customers.CompanyName, Shippers.CompanyName

Предложение FROM

Для указания имен таблиц, из которых выбираются записи, применяется ключевое слово FROM, например:

SELECT * FROM Customers

Этот запрос возвратит все поля из таблицы Customers.

Если в результирующем наборе данных нужны только поля CompanyName и ContactName, мы можем ввести следующее предложение SELECT:

SELECT CompanyName, ContactName FROM Customers

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

SELECT Customers.CompanyName, Shippers.CompanyName FROM Customers, Shippers

Предложение WHERE

Для фильтрации результатов, возвращаемых оператором SELECT, можно использовать предложение WHERE, синтаксис которого имеет вид:

WHERE expression1 [{AND | OR} expression2 […]]

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

SELECT * FROM Products WHERE CategoryID = 4

В предложении WHERE можно использовать различные выражения, например:

SELECT * FROM Products WHERE CategoryID = 2 AND SupplierID > 10

SELECT ProductName, UnitPrice FROM Products WHERE CategoryID = 3 OR UnitPrice < 50

SELECT ProductName, UnitPrice FROM Products WHERE Discontinued IS NOT NULL

Выражение ‘IS NOT NULL’ означает, что соответствующая колонка результирующего набора данных не может содержать пустых значений. В предложении WHERE можно использовать один из шести операторов отношений, определенных в SQL. Эти операторы приведены в табл. 1.

Таблица 1 Оператор Описание

< Меньше

<= Меньше или равно

<> Не равно

> Больше

>= Больше или равно

Помимо перечисленных выше простых операторов сравнения, можно использовать и специальные операторы сравнения, приведенные в табл. 2.

Таблица 2 Оператор Описание

ALL Применяется совместно с операторами сравнения при сравнении со списком значений

ANY Применяется совместно с операторами сравнения при сравнении со списком значений

BETWEEN Применяется при проверке нахождения значения внутри заданного интервала (включая его границы)

IN Применяется для проверки наличия значения в списке

LIKE Применяется при проверке соответствия значения заданной маске

Приведем несколько примеров применения этих операторов. Для сопоставления данных с маской применяется ключевое слово LIKE:

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘M%’

В данной маске символ ‘%’ (процент) заменяет любую последовательность символов, а символ ‘_’ (подчеркивание) - один любой символ. Тот же самый результат может быть получен следующим способом:

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName BETWEEN ‘M’ AND ‘N’

В последнем примере мы можем расширить область поиска. В частности, при поиске компаний с именами, начинающимися с букв от A до C, можно выполнить следующий оператор SELECT:

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName BETWEEN ‘A’ AND ‘D’

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

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘%bl%’

Маска ‘%bl%’ показывает, что до и после искомой подстроки может быть любое количество произвольных символов.

Используя оператор IN, можно задать список значений, в котором должно содержаться значение поля:

SELECT CompanyName, ContactName FROM Customers WHERE CustomerID IN (‘ALFKI’, ‘BERGS’, ‘VINET’)

Операторы AND, OR и NOT

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

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘S%’ AND Country = ‘USA’

Результатом выполнения этого запроса будет список заказчиков, находящихся в США, название которых начинается с буквы S.

Оператор OR позволяет выбрать записи, удовлетворяющие хотя бы одному из перечисленных условий, в то время как оператор NOT используется для исключения из набора данных записей, удовлетворяющих данному условию. Например, можно применить оператор OR для поиска всех заказчиков, либо находящихся в Калифорнии, либо имеющих название, начинающееся с буквы S (и при этом находящихся где угодно):

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘S%’ OR Region=’CA’

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

Теперь рассмотрим пример применения оператора NOT. Для исключения некоторых заказчиков из результирующего набора данных можно использовать запрос вида:

SELECT CompanyName, ContactName FROM Customers WHERE Country NOT IN (‘USA’, ‘UK’)

В результате выполнения этого запроса мы получим список заказчиков из всех стран, кроме США и Великобритании.

Предложение ORDER BY

Предложение ORDER BY (необязательное) применяется для сортировки результирующего набора данных по одной или нескольким колонкам. Для определения порядка сортировки используются ключевые слова ASC (по возрастанию) или DESC (по убыванию). По умолчанию данные сортируются по возрастанию. Синтаксис предложения ORDER BY имеет вид:

ORDER BY column1 [{ASC | DESC}] [, column2 [{ASC | DESC}] [,…]

Например, для сортировки сотрудников по фамилии и затем по имени следует использовать следующий SQL-запрос:

SELECT LastName, FirstName, Title FROM Employees ORDER BY LastName, FirstName

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

SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC

Связывание таблиц

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

SELECT ProductName, CategoryName FROM Products, Categories

в то время как запрос, показанный ниже, приводит к отображению списка продуктов с указанием, к какой категории принадлежит данный продукт:

SELECT ProductName, CategoryName FROM Products, Categories WHERE Products.CategoryID = Categories.CategoryID

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

SELECT column-list FROM table1, table2 WHERE table1.column1=table2.column2

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

Существует несколько типов связывания таблиц. Например, следующий оператор SQL осуществляет так называемое внутреннее соединение таблиц (inner join) - в этом случае в результирующем наборе данных содержатся записи, в которых значения в связанных полях совпадают:

SELECT ProductName, CategoryName FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID

Так называемые внешние соединения (outer joins) позволяют нам включить в результат запроса все строки из одной таблицы и соответствующие им строки из другой таблицы. Например:

SELECT ProductName, CategoryName FROM Products LEFT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID

Это было так называемое левое внешнее соединение (left outer join). Существуют также правые внешние соединения (right outer join), возвращающие все строки из второй (то есть правой) таблицы и соответствующие им строки из другой таблицы:

SELECT ProductName, CategoryName FROM Products RIGHT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID

Комбинируя левое и правое внешние соединения, можно получить полное внешнее соединение, возвращающее все данные из обеих таблиц:

SELECT ProductName, CategoryName FROM Products FULL OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID

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

SELECT ProductName, CategoryName FROM Products CROSS JOIN Categories

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

Предложение GROUP BY

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

GROUP BY {column1} [, …]

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

SELECT Customers.CustomerID, COUNT (Orders.OrderID) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID

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

Предложение HAVING

Предложение HAVING имеет назначение, сходное с предложением WHERE, но используется с агрегатными данными. Например:

SELECT Customers.CustomerID, COUNT (Orders.OrderID) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID HAVING COUNT(Orders.OrderID) >= 10

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

Ключевые слова ALL и DISTINCT

До этого момента мы рассматривали, как извлечь все или заданные колонки из одной или нескольких таблиц. Для управления выводом дублирующихся строк результирующего набора данных можно использовать ключевые слова ALL или DISTINCT в предложении SELECT. Ключевое слово DISTINCT указывает, что строки результирующего набора данных должны быть уникальны, тогда как ключевое слово ALL указывает, что возвращать следует все строки. Например, для извлечения названий стран, в которых имеются заказчики, можно использовать следующий запрос:

SELECT DISTINCT Country FROM Customers

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

Ключевое слово TOP

Ключевое слово TOP может быть использовано для возврата первых n строк или первых n процентов таблицы. Например, запрос:

SELECT TOP 10 * FROM PRODUCTS ORDER BY ProductName

возвращает первые 10 продуктов из таблицы, тогда как запрос:

SELECT TOP 25 PERCENT * FROM PRODUCTS ORDER BY ProductName

вернет первую четверть записей таблицы.

Модификация данных

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

Оператор UPDATE

Для изменения значений в одной или нескольких колонках таблицы применяется оператор UPDATE. Синтакcис этого оператора имеет вид:

UPDATE tableSET column1 = expression1 [, column2 = expression2] [,…]

Выражение в предложении SET может быть константой или результатом вычислений. Например, для повышения цен всех продуктов, стоящих меньше 10 долл., можно выполнить следующий запрос:

UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE UnitPrice < 10

Оператор DELETE

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

DELETE FROM table

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

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

DELETE FROM Products WHERE Discontinued = 1

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

SELECT ProductName FROM Products WHERE Discontinued = 1

Можно использовать в предложении WHERE более сложный критерий для определения того, какие записи должны быть удалены. Предположим, нам нужно удалить из списка клиентов тех из них, кто не имел заказов до определенной даты. Сначала для этого следует выполнить следующий SELECT, чтобы определить, что именно мы удаляем:

SELECT CompanyName FROM Customers WHERE Customers.CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE OrderDate > 01/01/96)

а затем заменить оператор SELECT на оператор DELETE:

DELETE FROM Customers WHERE Customers.CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE OrderDate > 01/01/96)

Замечание. При использовании в операторах SQL даты или времени, а также полей, содержащих такие данные, следует уточнить синтаксис таких предложений в документации из комплекта поставки используемой СУБД.

Оператор INSERT

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

INSERT table ( { VALUES ({ DEFAULT | NULL | expression } } [, …])

Например, для добавления нового клиента в таблицу Customers можно использовать следующий запрос:

INSERT INTO Customers (CustomerID, CompanyName) VALUES (‘XYZFO’, ‘XYZ Deli’)

Модификация метаданных

Существует несколько операторов SQL для управления метаданными, используемых для создания, изменения или удаления баз данных и содержащихся в них объектов (таблиц, представлений и др.). Мы рассмотрим некоторые из них: CREATE TABLE, ALTER TABLE и DROP.

Оператор CREATE TABLE

Для создания новой таблицы необходимо использовать оператор CREATE TABLE, синтаксис которого имеет вид:

CREATE TABLE table (column1 type1 [(size1)] [, column2 type2 [(size2)] [, ...]] ]]);

В этом операторе следует указать имя поля, тип данных для него (тип данных должен поддерживаться данной СУБД), длину (для некоторых типов полей) и, если нужно, серверные ограничения (с применением ключевого слова CONSTRAINT). Например, следующий запрос создает таблицу с именем Simple с четырьмя колонками - LastName, FirstName, EMail и HomePage:

CREATE TABLE Simple (FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255))

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

CREATE TABLE Simple (PersonID Integer NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255))

и указать, что комбинация полей LastName и FirstName должна быть уникальна:

CREATE TABLE Simple (PersonID Integer NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255), CONSTRAINT SimpleConstraint UNIQUE (FirstName, LastName))

Используя предложение SELECT и ключевое слово INTO, мы можем создавать новые таблицы, основанные на условии, указанном в предложении WHERE. Например:

SELECT * INTO NewOrders FROM Orders WHERE OrderDate > 1/1/97 Этот запрос создаст новую таблицу NewOrders и заполнит ее данными о заказах начиная с 1 января 1997 года.

Оператор ALTER TABLE

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

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

ALTER TABLE table ADD column datatype [(size)]

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

ALTER TABLE Simple ADD Phone varchar(30)

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

ALTER TABLE table ADD CONSTRAINT constraint

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

Третья разновидность предложения ALTER TABLE применяется для удаления поля из таблицы:

ALTER TABLE table DROP column

Ключевое слово COLUMN использовать не обязательно. Например:

ALTER TABLE Simple DROP Phone

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

ALTER TABLE table DROP CONSTRAINT index

Ниже приведен пример такого запроса:

ALTER TABLE Simple DROP CONSTRAINT PrimaryKey

Оператор DROP

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

DROP TABLE table

Вторая разновидность используется для удаления индекса.

Язык программирования

SQL (Structured Query Language — Структурированный язык запросов) — язык управления базами данных для реляционных баз данных. Сам по себе SQL не является Тьюринг-полным языком программирования, но его стандарт позволяет создавать для него процедурные расширения, которые расширяют его функциональность до полноценного языка программирования.

Язык был создан в 1970х годах под названием “SEQUEL” для системы управления базами данных (СУБД) System R. Позднее он был переименован в “SQL” во избежание конфликта торговых марок. В 1979 году SQL был впервые опубликован в виде коммерческого продукта Oracle V2.

Первый официальный стандарт языка был принят ANSI в 1986 году и ISO — в 1987. С тех пор были созданы еще несколько версий стандарта, некоторые из них повторяли предыдущие с незначительными вариациями, другие принимали новые существенные черты.

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

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

SQL состоит из четырех отдельных частей:

1. язык определения данных (DDL) используется для определения структур данных, хранящихся в базе данных. Операторы DDL позволяют создавать, изменять и удалять отдельные объекты в БД. Допустимые типы объектов зависят от используемой СУБД и обычно включают базы данных, пользователей, таблицы и ряд более мелких вспомогательных объектов, например, роли и индексы.
2. язык манипуляции данными (DML) используется для извлечения и изменения данных в БД. Операторы DML позволяют извлекать, вставлять, изменять и удалять данные в таблицах. Иногда операторы select извлечения данных не рассматриваются как часть DML, поскольку они не изменяют состояние данных. Все операторы DML носят декларативный характер.
3. язык определения доступа к данным (DCL) используется для контроля доступа к данным в БД. Операторы DCL применяются к привилегиям и позволяют выдавать и отбирать права на применение определенных операторов DDL и DML к определенным объектам БД.
4. язык управления транзакциями (TCL) используется для контроля обработки транзакций в БД. Обычно операторы TCL включают commit для подтверждения изменений, сделанных в ходе транзакции, rollback для их отмены и savepoint для разбиения транзакции на несколько меньших частей.

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

Примеры:

Hello, World!:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Строка ‘Hello, World!’ выбирается из встроенной таблицы dual , используемой для запросов, не требующих обращения к настоящим таблицам.

select "Hello, World!" from dual ;

Факториал:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

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

• псевдостолбец level для создания псевдотаблиц t1 и t2 , содержащих числа от 1 до 16,
• агрегатную функцию sum , позволяющую суммировать элементы множества без явного использования цикла,
• и математические функции ln и exp , позволяющие заменить произведение (необходимое для вычисления факториала) на сумму (предоставляемую SQL).

Строка “0! = 1” не войдет в набор строк, полученный в результате, т.к. попытка вычислить ln(0) приводит к исключению.

Числа Фибоначчи:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

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

• формулу Бине и математические функции ROUND , POWER и SQRT для вычисления n-ого числа Фибоначчи;
• псевдостолбец level для создания псевдотаблицы t1, содержащей числа от 1 до 16;
• встроенную функцию SYS_CONNECT_BY_PATH для упорядоченной конкатенации полученных чисел.

SELECT REPLACE (MAX (SYS_CONNECT_BY_PATH (fib || ", " , "/" )), "/" , "" ) || "..." fiblist FROM ( SELECT n , fib , ROW_NUMBER () OVER (ORDER BY n ) r FROM (select n , round ((power ((1 + sqrt (5 )) * 0 . 5 , n ) - power ((1 - sqrt (5 )) * 0 . 5 , n )) / sqrt (5 )) fib from (select level n from dual connect by level <= 16 ) t1 ) t2 ) START WITH r = 1 CONNECT BY PRIOR r = r - 1 ;

Hello, World!:

Пример для версий Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012 , MySQL 5 , PostgreSQL 8.4 , PostgreSQL 9.1 , sqlite 3.7.3

select "Hello, World!" ;

Факториал:

Пример для версий Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012

Используется рекурсивное определение факториала, реализованное через рекурсивный запрос. Каждая строка запроса содержит два числовых поля — n и n!, и каждая следующая строка вычисляется с использованием данных из предыдущей.

Можно вычислить целочисленные факториалы только до 20!. При попытке вычислить 21! возникает ошибка “Arithmetic overflow error”, т.е. происходит переполнение разрядной сетки.

Для вещественных чисел вычисляется факториал 100! (Для этого в примере необходимо заменить bigint на float в 3-ей строке)

Числа Фибоначчи:

Пример для версий Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012

Используется итеративное определение чисел Фибоначчи, реализованное через рекурсивный запрос. Каждая строка запроса содержит два соседних числа последовательности, и следующая строка вычисляется как (последнее число, сумма чисел) предыдущей строки. Таким образом все числа, кроме первого и последнего, встречаются дважды, поэтому в результат входят только первые числа каждой строки.

Факториал:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Этот пример демонстрирует использование оператора model , доступного начиная с версии Oracle 10g и позволяющего обработку строк запроса как элементов массива. Каждая строка содержит два поля — номер строки n и его факториал f.

select n || "! = " || f factorial from dual model return all rows dimension by ( 0 d ) measures ( 0 f , 1 n ) rules iterate (17 ) ( f [ iteration_number ] = decode (iteration_number , 0 , 1 , f [ iteration_number - 1 ] * iteration_number ), n [ iteration_number ] = iteration_number );

Числа Фибоначчи:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Этот пример демонстрирует использование оператора model , доступного начиная с версии Oracle 10g и позволяющего обработку строк запроса как элементов массива. Каждая строка содержит два поля — само число Фибоначчи и конкатенация всех чисел, меньше или равных ему. Итеративная конкатенация чисел в том же запросе, в котором они генерируются, выполняется проще и быстрее, чем агрегация как отдельное действие.

select max (s ) || ", ..." from (select s from dual model return all rows dimension by ( 0 d ) measures ( cast (" " as varchar2 (200 )) s , 0 f ) rules iterate (16 ) ( f [ iteration_number ] = decode (iteration_number , 0 , 1 , 1 , 1 , f [ iteration_number - 1 ] + f [ iteration_number - 2 ]), s [ iteration_number ] = decode (iteration_number , 0 , to_char (f [ iteration_number ]), s [ iteration_number - 1 ] || ", " || to_char (f [ iteration_number ])) ) );

Факториал:

Пример для версий MySQL 5

select concat (cast (t2 . n as char ), "! = " , cast (exp (sum (log (t1 . n ))) as char )) from ( select @ i : = @ i + 1 AS n from TABLE , (select @ i : = 0 ) as sel1 limit 16 ) t1 , ( select @ j : = @ j + 1 AS n from TABLE , (select @ j : = 0 ) as sel1 limit 16 ) t2 where t1 . n <= t2 . n group by t2 . n

Числа Фибоначчи:

Пример для версий MySQL 5

Замените TABLE на любую таблицу, к которой есть доступ, например, mysql.help_topic .

select concat (group_concat (f separator ", " ), ", ..." ) from (select @ f : = @ i + @ j as f , @ i : = @ j , @ j : = @ f from TABLE , (select @ i : = 1 , @ j : = 0 ) sel1 limit 16 ) t

Hello, World!:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

В этом примере используется анонимный блок PL/SQL, который выводит сообщение в стандартный поток вывода с помощью пакета dbms_output .

begin dbms_output . put_line ("Hello, World!" ); end ;

Факториал:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Этот пример демонстрирует итеративное вычисление факториала средствами PL/SQL.

declare n number : = 0 ; f number : = 1 ; begin while (n <= 16 ) loop dbms_output . put_line (n || "! = " || f ); n : = n + 1 ; f : = f * n ; end loop ; end ;

Числа Фибоначчи:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Этот пример использует итеративное определение чисел Фибоначчи. Уже вычисленные числа хранятся в структуре данных varray — аналоге массива.

declare type vector is varray (16 ) of number ; fib vector : = vector (); i number ; s varchar2 (100 ); begin fib . extend (16 ); fib (1 ) : = 1 ; fib (2 ) : = 1 ; s : = fib (1 ) || ", " || fib (2 ) || ", " ; for i in 3 .. 16 loop fib (i ) : = fib (i - 1 ) + fib (i - 2 ); s : = s || fib (i ) || ", " ; end loop ; dbms_output . put_line (s || "..." ); end ;

Квадратное уравнение:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Этот пример тестировался в SQL*Plus, TOAD и PL/SQL Developer.

Чистый SQL позволяет вводить переменные в процессе исполнения запроса в виде заменяемых переменных. Для определения такой переменной ее имя (в данном случае A, B и C) следует использовать с амперсандом & перед ним каждый раз, когда нужно сослаться на эту переменную. Когда запрос выполняется, пользователь получает запрос на ввод значений всех заменяемых переменных, использованных в запросе. После ввода значений каждая ссылка на такую переменную заменяется на ее значение, и полученный запрос выполняется.

Существует несколько способов ввести значения для заменяемых переменных. В данном примере первая ссылка на каждую переменную предваряется не одинарным, а двойным амперсандом && . Таким образом значение для каждой переменной вводится только один раз, а все последующие ссылки на нее будут заменены тем же самым значением (при использовании одиночного амперсанда в SQL*Plus значение для каждой ссылки на одну и ту же переменную приходится вводить отдельно). В PL/SQL Developer ссылки на все переменные должны предваряться одиночным знаком & , иначе будет возникать ошибка ORA-01008 “Not all variables bound”.

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

Сам запрос состоит из четырех разных запросов. Каждый запрос возвращает строку, содержащую результат вычислений, в одном из случаев (A=0, D=0, D>0 и D<0) и ничего — в трех остальных случаях. Результаты всех четырех запросов объединяются, чтобы получить окончательный результат.

alter session set NLS_NUMERIC_CHARACTERS = ". " ; select "Not a quadratic equation." ans from dual where && A = 0 union select "x = " || to_char (-&& B / 2 /& A ) from dual where & A != 0 and & B *& B - 4 *& A *&& C = 0 union select "x1 = " || to_char ((-& B + sqrt (& B *& B - 4 *& A *& C )) / 2 /& A ) || ", x2 = " || to_char (-& B - sqrt (& B *& B - 4 *& A *& C )) / 2 /& A from dual where & A != 0 and & B *& B - 4 *& A *& C > 0 union select "x1 = (" || to_char (-& B / 2 /& A ) || "," || to_char (sqrt (-& B *& B + 4 *& A *& C ) / 2 /& A ) || "), " || "x2 = (" || to_char (-& B / 2 /& A ) || "," || to_char (- sqrt (-& B *& B + 4 *& A *& C ) / 2 /& A ) || ")" from dual where & A != 0 and & B *& B - 4 *& A *& C < 0 ;