Что значит структурированный язык запросов

Структурированный язык запросов SQL

Материал из ПИЭ.Wiki

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

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

SQL основывается на реляционной алгебре.

Содержание

Краткая история развития SQL

SQL ведет свою историю с начала 1970-х годов, когда в исследовательской лаборатории компании IBM в штате Калифорния была разработана его первая версия. Первая публикация описания языка относится к 1974 году. В то время его назвали SEQUEL (Structured English Query Language — структурированный английский язык запросов). Сначала он был реализован в экспериментальной реляционной СУБД System/R, проект которой инициировала в середине 70-х годов компания IBM в исследовательской лаборатории в Сан-Хосе. При реализации последующей версии System/R язык был переименован в SQL. Исследовательский проект System/R был завершен в 1979 году. Он подтвердил возможность создания эффективных промышленных реляционных СУБД.

В 1977 году небольшая, только что организованная фирма Relational Software приступила к созданию промышленной реляционной СУБД на основе SQL. Поставки этой СУБД, получившей название Oracle, начались в 1979 году. Вскоре и сама фирма была переименована в Oracle. С тех пор она является крупнейшим поставщиком реляционных СУБД на базе SQL.

В середине 70-х годов в исследовательской лаборатории Калифорнийского университета в Беркли был открыт проект по созданию реляционной СУБД. В этой лаборатории, как и в компании IBM, создали экспериментальную СУБД, получившую название Ingress, на которой отрабатывались результаты научных исследований в области реляционных баз данных. В 1980 году часть сотрудников этой лаборатории организовали фирму Relational Technology, которая в 1981 году выпустила промышленную СУБД Ingress. Первоначально эта СУБД использовала реляционный язык QUEL, однако в 1986 году была переведена на SQL.

В 1980 году компания IBM на основании опыта, полученного при разработке экспериментальной System/R, приступила к созданию собственной промышленной СУБД реляционного типа, которая начала поставляться в 1982 году под названием SQL/DS. Затем в компании был разработан более совершенный продукт — DB2, поставки которого начались в 1985 году. Эта СУБД стала стратегическим программным продуктом компании IBM.

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

Стандартизация SQL

К началу 80-х годов в связи с широким распространением реляционных СУБД появилась необходимость анализа возможной стандартизации языка для управления реляционными базами данных и разработки такого стандарта, если это будет признано целесообразным. В связи с этим в 1982 году Американский национальный институт стандартов (American National Standards Institute — ANSI) создал комитет ХЗН2, перед которым была поставлена эта задача. Комитет принял к рассмотрению различные реляционные языки, которые были описаны и реализованы к тому времени. Однако, учитывая широкую распространенность SQL в промышленных СУБД и тот факт, что он фактически уже стал стандартом к тому времени, комитет остановился на этом языке. Взяв за основу его диалект, реализованный в СУБД DB2, комитет постарался его обобщить, учитывая реализованные в других реляционных СУБД возможности. После четырех лет работы, в 1986 году предложенный комитетом вариант SQL был официально утвержден как стандарт ANSI, а в 1987 году он был принят в качестве стандарта Международной организацией стандартов (International Standards Organization — ISO). Затем стандарт ANSI/ISO приняло правительство США как федеральный стандарт в области обработки информации (Federal Information Processing Standard — FIPS). В 1989 году стандарт был незначительно изменен и получил название SQL-89 (или SQL1).

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

Последующая деятельность этого комитета, который со временем получил название ANSI ТС NCITS H2, была направлена на устранение недостатков стандарта. В 1992 году ANSI принял новый стандарт, который был назван SQL-92 (или SQL2). В этом стандарте комитет не пошел на поводу производителей СУБД и предусмотрел возможности, которые выходили за рамки имеющихся в промышленных СУБД, — были расширены способы ограничения целостности, введена поддержка различных языков программирования, предусмотрена обработка транзакций и многое другое. В связи с существенным расширением языка было введено три уровня совместимости стандартов. Нижний уровень (Entry Level) предполагает минимальные дополнения к стандарту SQL-89. Промежуточный уровень (Intermediate Level) существенно расширяет SQL-89, но не затрагивает сложных элементов языка. Третий уровень (Full Level) представляет полную спецификацию нового стандарта.

В 1992 году комитет приступил к решительным изменениям в SQL, которые отразились в стандарте SQL-99 (или SQL3). Первое принципиальное изменение заключается в том, что SQL стал поддерживать модель данных, выходящую за рамки реляционной. В соответствии с SQL3, ячейки таблиц могут быть многозначными, что позволяет представлять иерархическую и сетевую модели. Кроме того, язык расширен до возможности представления объектной модели данных и манипулирования ею. Во-вторых, он был структурирован — стал состоять из отдельных частей (parts), составляющих его основу (foundation), которая дополняется независимо определенными модулями (packages).

Наконец, в 2003 году был опубликован последний стандарт — SQL-2003. Ничего революционного, как это было в SQL-99, в нем нет. Помимо последовательного расширения уже существующих возможностей, введена новая часть языка — SQL/XML, позволяющая интегрировать две популярные технологии структурирования данных.

Возможности SQL

В начале 70-х годов SQL являлся лишь языком запросов (ЯЗ). Он, по сути, содержал только предложение SELECT, которое позволяло формулировать запросы для выборки данных из базы. Затем язык был дополнен двумя другими компонентами, необходимыми для работы с базами данных. Первый из них — средства для определения структуры базы данных, которые в терминологии теории баз данных называются языком определения данных (ЯОД). Второй — средства, позволяющие заполнять базу данными, изменять их и удалять. Этот компонент в теории баз данных называется языком манипулирования данными (ЯМД). Также было принято решение, что весь интерфейс с базами данных должен обеспечиваться одним языком, вследствие чего SQL оброс множеством функций, необходимых для управления базами данных. Приведем некоторые из них:

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

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

Типы команд SQL

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

Такими категориями являются:

• DDL (Data Definition Language — язык определения данных);
• DML (Data Manipulation Language — язык манипуляций данными);
• DQL (Data Query Language — язык запросов к данным);
• DCL (Data Control Language — язык управления данными);
• команды администрирования данных;
• команды управления транзакциями.

Определение структур базы данных (DDL)

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

Среди основных команд DDL:

• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• CREATE INDEX
• ALTER INDEX
• DROP INDEX

Команда создания таблицы CREATE

Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу — таблицу без строк. Значения вводятся с помощью DML команды INSERT. Команда CREATE TABLE в основном определяет имя таблицы, в виде описания набора имен столбцов указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь, по крайней мере, один столбец. Синтаксис команды CREATE TABLE:

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

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

Индексы

Индекс — это упорядоченный (буквенный или числовой) список столбцов или групп столбцов в таблице. Таблицы могут иметь большое количество строк, а, так как строки не находятся в каком-нибудь определенном порядке, их поиск по указанному значению может потребовать значительного времени. Когда вы создаете индекс в поле, ваша база данных запоминает соответствующий порядок всех значений этого поля в области памяти. Предположим, что наша таблица Заказчиков имеет тысячи входов, а вы хотите найти заказчика с номером cnum=299. Так как строки не упорядочены, ваша программа будет просматривать всю таблицу, строку за строкой, проверяя каждый раз значение поля cnum на равенство значению 299. Однако если бы имелся индекс в поле cnum, то программа могла бы выйти на номер 299 прямо по индексу и дать информацию о том, как найти правильную строку таблицы. В то время как индекс значительно улучшает эффективность запросов, использование индекса несколько замедляет операции модификации DML INSERT, UPDATE и DELETE, что вполне понятно, поскольку при модификации таблицы должен модифицироваться и индекс, а сам индекс занимает объем памяти. Следовательно, каждый раз, когда вы создаете таблицу, Вы должны принять решение, индексировать ее или нет. Синтаксис для создания индекса — обычно следующий (помните, что это не ANSI стандарт):

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

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

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

Удаление индекса не воздействует на содержание полей.

Изменение существующей таблицы ALTER TABLE

Команда ALTER TABLE не часть стандарта ANSI; но это — широко доступная, и довольно содержательная форма, хотя ее возможности несколько ограничены. Она используется, чтобы изменить определение существующей таблицы. Обычно, она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также в некоторых программах добавлять или удалять ограничения. Типичный синтаксис, чтобы добавить столбец к таблице:

Удаление таблиц DROP

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

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

реализациях SQL, поддерживаема и полезна. К счастью, она более проста, и, следовательно, более непротиворечива, чем ALTER TABLE. ANSI просто не имеет способа для определения разрушенных или неправильных таблиц. Примечание. Не все SQL-серверы требуют очистки таблицы перед ее удалением. Здесь нужно обратиться к документации по Вашей системе.

Язык манипулирования данными (DML)

DML — Data Manipulation Language. Язык манипулирования данными. Используется для работы с информацией, хранимой в базе данных.

Основными командами этой группы являются:

• Select — вычитка информации.
• Insert — добавление информации.
• Update — обновление информации.
• Delete — удаление информации.

Добавить новую запись в таблицу INSERT

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

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

Модификация записей UPDATE

Если задано ключевое слово WHERE и условие, то команда UPDATE применяется только к тем записям, для которых оно выполняется. Если условие не задано, UPDATE применяется ко всем записям. Пример:

В качестве условия используются логические выражения над константами и полями. В условиях допускаются: операции сравнения: > , = , , != . В SQL эти операции могут применяться не только к числовым значениям, но и к строкам ( » » позже в алфавитном порядке) и датам ( » » позже в хронологическом порядке).

• оперции проверки поля на значение NULL: IS NULL, IS NOT NULL
• операции проверки на вхождение в диапазон: BETWEEN и NOT BETWEEN.
• операции проверки на вхождение в список: IN и NOT IN
• операции проверки на вхождение подстроки: LIKE и NOT LIKE
• отдельные операции соединяются связями AND, OR, NOT и группируются с помощью скобок.

Эта команда находит в таблице publishers все неопределенные значения столбца url и заменяет их строкой «url not defined».

Удаление записей DELETE

Удаляются все записи, удовлетворяющие указанному условию. Если ключевое слово WHERE и условие отстутствуют, из таблицы удаляются все записи. Пример:

Эта команда удаляет запись об издательстве Super Computer Publishing.

Выборка данных SELECT

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

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

Пример: получить список всех авторов

получить список всех полей таблицы authors:

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

Допустим теперь, что нам надо найти все публикации за интервал 1995 — 1997 гг. Это условие можно записать в виде:

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

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

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

Некоторые задачи нельзя решить с использованием только операторов сравнения. Например, мы хоти найти web-site издательтва «Wiley», но не знаем его точного наименования. Для решения этой задачи предназначено ключевое слово LIKE, его синтаксис имеет вид:

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

• % (знак процента) — заменяет любое количество символов
• _ (подчеркивание) — заменяет одиночный символ.

Попробуем найти искомый web-site:

В соотвествии с шаблоном СУБД найдет все строки включающие в себя подстроку «Wiley». Другой пример: найти все книги, название которых начинается со слова «SQL»:

В том случае, когда надо найти значение, которое само содержит один из символов шаблона, используют ключевое слово ESCAPE и . Литерал, следующий в шаблоне после ключевого символа, рассматривается как обычный символ, все последующие символы имеют обычное значение. Например, нам надо найти ссылку на web-страницу, о которой известно, что в ее url содержится подстрока «my_works»:

Выборка из нескольких таблиц.

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

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

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

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

Вычисления внутри SELECT.

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

В арифметических вражения допускаются операции сложения (+), вычитания (-), деления (/), умножения (*), а также различные функции (COS, SIN, ABS — абсолютное значение и т.д.). Также в запрос можно добавить строковую константу:

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

• AVG( ) — среднее по всем значениям данного поля
• COUNT( ) или COUNT (*) — число записей
• MAX( ) — максимальное из всех значений данного поля
• MIN( ) — минимальное из всех значений данного поля
• SUM( ) — сумма всех значений данного поля

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

подсчитать количество книг в нашей базе данных:

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

Групировка данных.

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

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

Kлючевое слово HAVING работает следующим образом: сначала GROUP BY разбивает строки на группы, затем на полученные наборы накладываются условия HAVING. Например, устраним из предыдущего запроса те издательства, которые имеют только одну книгу:

Cортировка данных.

Для сортировки данных, получаемых при помощи оператора SELECT служит ключевое слово ORDER BY. С его помощью можно сортировать результаты по любому столбцу или выражению, указанному в . Данные могут быть упорядочены как по возрастанию, так и по убыванию. Пример: сортировать список авторов по алфавиту: SELECT author FROM authors ORDER BY author; Более сложный пример: получить список авторов, отсортированный по алфавиту, и список их публикаций, причем для каждого автора список книг сортируется по времени издания в обратном порядке (т.е. сначала более «свежие» книги, затем все более «древние»):

Ключевое слово DESC задает здесь обратный порядок сортировки по полю yearpub, ключевое слов ASC (его можно опускать) — прямой порядок сортировки по полю author.

Источник