Windows. Железо. Интернет. Безопасность. Оптимизация
Вы здесь: » »

Что такое SQL. Назначение и основа

В предыдущих двух статьях данного цикла, опубликованных в № 6 и 7 нашего журнала, мы рассмотрели различные механизмы доступа к данным, включая ADO, BDE и их альтернативы. Теперь мы знаем, как выбрать технологию доступа к данным для той или иной пары «СУБД - средство разработки».

Располагая технологией доступа к данным, можно наконец подумать и о том, каким образом следует манипулировать самими данными и метаданными. Способы манипуляции могут быть специфичными для данной СУБД (например, использование объектов клиентской части этой СУБД для доступа к объектам баз данных) или для данного механизма доступа к данным. Тем не менее существует более или менее универсальный способ манипуляции данными, поддерживаемый почти всеми серверными реляционными СУБД и большинством универсальных механизмов доступа к данным (в том числе при использовании их совместно с настольными СУБД). Этот способ - применение языка SQL (Structured Query Language - язык структурированных запросов). Ниже мы рассмотрим назначение и особенности этого языка, а также изучим, как с его помощью извлекать и суммировать данные, добавлять, удалять и модифицировать записи, защищать данные от несанкционированного доступа, создавать базы данных. Для более подробного изучения SQL мы можем порекомендовать книги Мартина Грабера «Введение в SQL» (М., Лори, 1996) и «SQL. Справочное руководство» (М., Лори, 1997).

Введение

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

Язык SQL был создан в начале 70-х годов в результате исследовательского проекта IBM, целью которого было создание языка манипуляции реляционными данными. Первоначально он назывался SEQUEL (Structured English Query Language), затем - SEQUEL/2, а затем - просто SQL. Официальный стандарт SQL был опубликован ANSI (American National Standards Institute - Национальный институт стандартизации, США) в 1986 году (это наиболее часто используемая ныне реализация SQL). Данный стандарт был расширен в 1989 и 1992 годах, поэтому последний стандарт SQL носит название SQL92. В настоящее время ведется работа над стандартом SQL3, содержащим некоторые объектно-ориентированные расширения.

Существует три уровня соответствия стандарту ANSI - начальный, промежуточный и полный. Многие производители серверных СУБД, такие как IBM, Informix, Microsoft, Oracle и Sybase, применяют собственные реализации SQL, основанные на стандарте ANSI (отвечающие как минимум начальному уровню соответствия стандарту) и содержащие некоторые расширения, специфические для данной СУБД.

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

Как работает SQL

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

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

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

Data Definition Language (DDL)

Data Definition Language содержит операторы, позволяющие создавать, изменять и уничтожать базы данных и объекты внутри них (таблицы, представления и др.). Эти операторы перечислены в табл. 1.

Таблица 1

Оператор

Описание

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

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

Применяется для изменения структуры имеющейся таблицы

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

Применяется для удаления представления из базы данных

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

Применяется для удаления существующего индекса

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

Применяется для удаления схемы из базы данных

Применяется для создания нового домена

Применяется для переопределения домена

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

Data Manipulation Language (DML)

Data Manipulation Language содержит операторы, позволяющие выбирать, добавлять, удалять и модифицировать данные. Обратите внимание на то, что эти операторы не обязаны завершать транзакцию, внутри которой они вызваны. Операторы DML представлены в табл. 2.

Таблица 2

Иногда оператор SELECT относят к отдельной категории, называемой Data Query Language (DQL).

Cursor Control Language (CCL)

Операторы Cursor Control Language используются для определения курсора, подготовки SQL-предложений для выполнения, а также для некоторых других операторов. Операторы CCL представлены в табл. 5.

Таблица 5

Оператор

Описание

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

Применяется для описания плана запроса. Этот оператор представляет собой расширение SQL для Microsoft SQL Server 7.0. Он не обязан выполняться в других СУБД. Например, в случае Oracle следует использовать оператор EXPLAIN PLAN

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

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

Применяется для закрытия курсора

Применяется для подготовки оператора SQL для выполнения

Применяется для выполнения оператора SQL

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

Все операторы SQL имеют вид, показанный на рис. 2 .

Каждый оператор SQL начинается с глагола, представляющего собой ключевое слово, определяющее, что именно делает этот оператор (SELECT, INSERT, DELETE...). В операторе содержатся также предложения, содержащие сведения о том, над какими данными производятся операции. Каждое предложение начинается с ключевого слова, такого как FROM, WHERE и др. Структура предложения зависит от его типа - ряд предложений содержит имена таблиц или полей, некоторые могут содержать дополнительные ключевые слова, константы или выражения.

С помощью чего можно выполнить SQL-операторы

Все современные серверные СУБД (а также многие популярные настольные СУБД) содержат в своем составе утилиты, позволяющие выполнить SQL-предложение и ознакомиться с его результатом. В частности, клиентская часть Oracle содержит в своем составе утилиту SQL Plus, а Microsoft SQL Server - утилиту SQL Query Analyzer. Именно этой утилитой мы воспользуемся для демонстрации возможностей SQL, а в качестве базы данных, над которой мы будем «экспериментировать», возьмем базу данных NorthWind, входящую в комплект поставки Microsoft SQL Server 7.0. В принципе, можно использовать другую базу данных и любую другую утилиту, способную выполнять в этой базе данных SQL-предложения и отображать результаты (или даже написать свою, используя какое-либо средство разработки - Visual Basic, Delphi, C++Builder и др.). Однако на всякий случай рекомендуется сделать резервную копию этой базы данных.

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

1. Что такое База Данных

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

2. Что такое SQL

Итак, переходим к SQL .

SQL - простой язык программирования, который имеет немного команд и которой может научиться любой желающий. Расшифровывается как Structured Query Language - язык структурированных запросов, который был разработан для работы с БД , а именно, чтобы получать /добавлять /изменять данные, иметь возможность обрабатывать большие массивы информации и быстро получать структурированную и сгруппированную информацию. Есть много вариантов языка SQL , но у них всех основные команды почти одинаковы. Также существует и много СУБД , но основными из них являются: Microsoft Access, Microsoft SQL Server, MySQL, Oracle SQL, IBM DB2 SQL, PostgreSQL та Sybase Adaptive Server SQL . Чтобы работать с SQL кодом, нам понадобится одна из вышеперечисленных СУБД . Для обучения мы будем использовать СУБД Microsoft Access .

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

Доступный в Интернете словарь Merriam-Webster определяет базу данных как большой набор данных , организованный специальным образом для обеспечения быстрого поиска и извлечения данных (например, с помощью компьютера).

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

За многие годы для решения различных видов проблем хранения данных было создано множество СУБД.

Типы баз данных

В 1960-70-х годах разрабатывались базы данных, которые тем или иным способом решали проблему повторяющихся групп. Эти методы привели к созданию моделей систем управления базами данных. Основой для таких моделей, используемых и по сей день, послужили исследования, проводимые в компании IBM.

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

База данных с сетевой структурой

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

Рис. 1. Структура записей конвертера валют

Данные загружаются и получается связанный (отсюда и название модели – сетевая) список для языков (рис. 2):

Рис. 2. Связанный список

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

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

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

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

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

Иерархическая модель базы данных

В конце 1960-х годов IBM использовала в СУБД IMS иерархическую модель построения базы. В этой модели проблема повторяющихся групп решалась за счет представления одних записей как состоящих из множеств других.

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

Иерархическая модель базы данных применяется до сих пор. Иерархическая СУБД способна оптимизировать хранение данных в том, что касается некоторых отдельных вопросов, например можно без труда определить, в каком автомобиле используется какая-то конкретная деталь.

Реляционная модель базы данных

Огромный скачок в развитии теории систем управления базами данных произошел в 1970 году, когда был опубликован доклад Е. Ф. Код- да (E. F. Codd) «Реляционная модель для больших разделяемых банков данных» («A Relational Model of Data for Large Shared Data Banks»), см. эту ссылку. В этом поистине революционном труде вводилось понятие отношений и было показано, как использовать таблицы для представления фактов, которые устанавливают отношения с объектами «реального мира» и, следовательно, хранят данные о них.

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

Реляционную систему управления базами данных определяет набор правил. Во-первых, запись таблицы носит название «кортеж», и именно этот термин используется в части документации на PostgreSQL. Кортеж - это упорядоченная группа компонентов (или атрибутов), каждый из которых принадлежит определенному типу. Все кортежи построены по одному шаблону, во всех одинаковое количество компонентов одинаковых типов. Приведем пример набора кортежей:

{"France", "FRF", 6.56} {"Belgium", "BEF", 40.1}

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

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

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

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

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

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

Последнее правило, определяющее структуру реляционной базы данных, - это ссылочная целостность. Такое требование объясняется тем, что в любой момент времени все записи базы данных должны иметь смысл. Разработчик приложения, взаимодействующего с базой данных, должен быть внимателен, он обязан убедиться, что его код не нарушает целостности базы. Представьте, что происходит при удалении клиента. Если клиент удаляется из отношения CUSTOMER, необходимо удалить и все его заказы из таблицы ORDERS. В противном случае останутся записи о заказах, которым не сопоставлен клиент.

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

Языки запросов SQL и друие

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

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

Одним из первых был реализован язык запросов QUEL, он использовался в созданной в конце 1970х годов базе данных Ingres. Еще один язык запросов, в котором применялся другой метод, назывался QBE (Query By Example - запрос по примеру). Приблизительно в то же самое время группа, работающая в исследовательском центре IBM, разработала язык структурированных запросов SQL (Structured Query Language), это название обычно произносится как «сиквел».

SQL - это стандартный язык запросов , наиболее распространенным его определением является стандарт ISO/IEC 9075:1992, «Information Techno­logy - Database Languages - SQL» (или, проще говоря, SQL92) и его американский аналог ANSI X3.135-1992, отличающийся от первого лишь несколькими страницами обложки. Эти стандарты заменили ранее существовавший SQL89. На самом деле есть и более поздний стандарт, SQL99, но он еще не получил распространения, к тому же большая часть обновлений не затрагивает ядро языка SQL.

Существуют три уровня соответствия SQL92: Entry SQL, Intermediate SQL и Full SQL. Самым распространенным является уровень «Entry», и PostgreSQL очень близок к такому соответствию, хотя есть и небольшие различия. Разработчики занимаются исправлением незначительных упущений, и с каждой новой версией PostgreSQL становится все ближе к стандарту.

В языке SQL три типа команд:

  • Data Manipulation Language (DML) - язык манипулирования данными. Это та часть SQL, которая используется в 90% случаев. Она состоит из команд добавления, удаления, обновления и, что важнее всего, выборки данных из базы данных.
  • Data Definition Language (DDL) - язык определения данных. Это команды для создания таблиц и управления другими аспектами базы данных, структурированными на более высоком уровне, чем относящиеся к ним данные.
  • Data Control Language (DCL) - язык управления данными

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

SQL

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

Однако под давлением рынка производители баз данных вынуждены создавать отличающиеся друг от друга продукты. Так появилось несколько диалектов SQL, чему способствовало и то, что в стандарте, описывающем язык, не определены команды для многих задач администрирования базы данных, которые представляют собой необходимую и очень важную составляющую при использовании базы в реальном мире. Поэтому существуют различия между диалектами SQL, принятыми (например) в Oracle, SQL Server и PostgreSQL.

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

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

CREATE TABLE item (item_id serial, description char(64) not null, cost_price numeric(7,2), sell_price numeric(7,2));

Здесь мы определили, что таблице необходим идентификатор, который бы действовал как первичный ключ, и что он должен автоматически генерироваться системой управления базой данных. Идентификатор имеет тип serial, а это означает, что каждый раз при добавлении нового элемента item в последовательности будет создан новый, уникальный item_id. Описание (description) - это текстовый атрибут, состоящий из 64 символов. Себестоимость (cost_price) и цена продажи (sell_price) определяются как числа с плавающей точкой, с двумя знаками после запятой.

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

INSERT INTO item(description, cost_price, sell_price) values("Fan Small", 9.23, 15.75); INSERT INTO item(description, cost_price, sell_price) values("Fan Large", 13.36, 19.95); INSERT INTO item(description, cost_price, sell_price) values("Toothbrush", 0.75, 1.45);

Основа SQL - это оператор SELECT . Он применяется для создания результирующих множеств - групп записей (или атрибутов записей), которые соответствуют некоторому критерию. Эти критерии могут быть достаточно сложными. Результирующие множества могут использоваться в качестве целевых объектов для изменений, осуществляемых оператором UPDATE , или удалений, выполняемых DELETE .

Вот несколько примеров использования оператора SELECT:

SELECT * FROM customer, orderinfo WHERE orderinfo.customer_id = customer.customer_id GROUP BY customer_id SELECT customer.title, customer.fname, customer.lname, COUNT(orderinfo.orderinfo_id) AS "Number of orders" FROM customer, orderinfo WHERE customer.customer_id = orderinfo.customer_id GROUP BY customer.title, customer.fname, customer.lname

Эти операторы SELECT перечисляют все заказы клиентов в указанном порядке и подсчитывают количество заказов, сделанных каждым клиентом.

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

  • Использовать консольное приложение для выполнения операторов SQL
  • Непосредственно встроить SQL в приложение
  • Использовать вызовы функций API (Application Programming In­terfaces, интерфейсов прикладного программирования) для подготовки и выполнения операторов SQL, просмотра результирующих множеств и обновления данных из множества различных языков программирования
  • Прибегнуть к опосредованному доступу к данным базы PostgreSQL с применением драйвера ODBC (Open Database Connection - открытого интерфейса доступа к базам данных) или JDBC (Java Database Connectivity - интерфейса доступа Java-приложений к базам данных) или стандартной библиотеки, такой как DBI для языка Perl

Системы управления базами данных

СУБД , как уже говорилось ранее, - это набор программ, делающих возможным построение баз данных и их использование. В обязанности СУБД входит:

  • Создание базы данных. Некоторые системы управляют одним большим файлом и создают одну или несколько баз данных внутри него, другие могут задействовать несколько файлов операционной системы или же непосредственно реализовывать низкоуровневый доступ к разделам диска. Пользователи и разработчики не должны заботиться о низкоуровневой структуре таких файлов, т. к. весь необходимый доступ обеспечивает СУБД.
  • Предоставление средств для выполнения запросов и обновлений. СУБД должна обеспечивать возможность запроса данных, удовлетворяющих некоторому критерию, например возможность выбора всех заказов, сделанных некоторым клиентом, но еще не доставленных. До того как SQL получил широкое распространение в качестве стандартного языка, способы выражения таких запросов менялись от системы к системе.
  • Многозадачность. Если с базой данных работают несколько приложений или к ней одновременно осуществляют доступ несколько пользователей, то СУБД должна гарантировать, что обработка запроса каждого пользователя не влияет на работу остальных. То есть пользователям приходится ждать, только если кто-то другой записывает данные именно тогда, когда им нужно прочитать (или записать) данные в какой-то элемент. Одновременно может происходить несколько считываний данных. На поверку оказывается, что разные базы данных поддерживают разные уровни многозадачности и что эти уровни даже могут быть настраиваемыми.
  • Ведение журнала. СУБД должна вести журнал всех изменений данных за некоторый период времени. Он может использоваться для отслеживания ошибок, а также (может быть, это даже важнее) для восстановления данных в случае сбоя системы, например внепланового выключения питания. Обычно производится резервное копирование данных и ведется журнал транзакций, т. к. резервная копия может быть полезна для восстановления базы данных в случае повреждения диска.
  • Обеспечение безопасности базы данных. СУБД должна обеспечивать контроль над доступом, чтобы только зарегистрированные пользователи могли манипулировать данными, хранящимися в базе, и самой структурой базы данных (атри­бутами, таблицами и индексами). Обычно для каждой базы определяется иерархия пользователей, во главе этой структуры стоит «суперпользователь», который может изменять все что угодно, дальше идут пользователи, которые могут добавлять и удалять данные, а в самом низу находятся те, кто имеет право только на чтение. СУБД должна иметь средства, позволяющие добавлять и удалять пользователей, а также указывать, к каким возможностям базы данных они могут получить доступ.
  • Поддержание ссылочной целостности. Многие СУБД имеют свойства, способствующие поддержанию ссылочной целостности, то есть корректности данных. Обычно, если запрос или обновление нарушает правила реляционной модели, СУБД выдает сообщение об ошибке.

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

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 ;

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

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

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

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

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

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

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

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

Рубрика: Интернет
Поделиться