SQL (Structured Query Language)

Материал из Национальной библиотеки им. Н. Э. Баумана
Последнее изменение этой страницы: 23:25, 14 декабря 2017.
SQL
SQL.jpg
Парадигма Мультипарадигмальный
Спроектировано Дональд Чемберлин
Рэймонд Бойс
Разработчики ISO/IEC
Первый   появившийся 1974
Стабильная версия SQL:2008 / 2008
Печать дисциплины Статическая, строгая
OS Кроссплатформенный
Расширение файла .sql
Главная реализация
Много
Диалект
SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2008
Под влиянием
Datalog
Влияние
Agena, CQL, LINQ, Windows PowerShell[1]
SQL (Structured Query Language)
Разработчик ISO/IEC
Начальная версия 1986
Последний релиз
SQL:2008
(2008)
Тип формата Базы данных
Стандарт ISO/IEC 9075
Открытый формат? Да

SQL - (Structured Query Language, произносится ˈstrʌkʧəd ˈkwɪəri ˈlæŋgwɪʤ) - язык программирования специального назначения, разработанный для управления данными в реляционных СУБД. В круг ответственности SQL входит добавлене данных, извлечение по запросу, обновление и их удаление, а также создание и изменение схемы реляционной БД, контроль прав доступа к данным. Несмотря на то, что в основном SQL описывают как декларативный язык, в него включены и процедурные элементы. Все крупнейшие реляционные СУБД поддерживают SQL в той или иной форме. В дополнение к этому, все программы, написанные на SQL, портируются между всеми реализациями SQL, подчиняющимися стандарту, либо с изменениями на любые другие реализации.

История

Первые разработки

В начале 1970-х годов в одной из исследовательских лабораторий компании IBM была разработана экспериментальная реляционная СУБД IBM System R, для которой затем был создан специальный язык SEQUEL, позволявший относительно просто управлять данными в этой СУБД. Аббревиатура SEQUEL расшифровывалась как Structured English QUEry Language — «структурированный английский язык запросов». Позже по юридическим соображениям[2] язык SEQUEL был переименован в SQL. Когда в 1986 году первый стандарт языка SQL был принят ANSI, официальным произношением стало [,es kju:' el] — эс-кью-эл. Несмотря на это, даже англоязычные специалисты зачастую продолжают читать SQL как сиквел (по-русски часто говорят «эс-ку-эль»).

C одной стороны, SQL был ориентирован на удобную и понятную пользователям формулировку запросов к реляционным БД. С другой стороны, практически с самого начала он был так называемым «полным языком БД». Это означает, что SQL включал:

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

Целью разработки было создание простого непроцедурного языка, которым мог воспользоваться любой пользователь, даже не имеющий навыков программирования. Собственно разработкой языка запросов занимались Дональд Чэмбэрлин и Рэй Бойс (|Ray Boyce). Пэт Селинджер (Pat Selinger) занималась разработкой стоимостного оптимизатора (Шаблон:Lang-en2), Рэймонд Лори (Raymond Lorie) занимался компилятором запросов.

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

Первыми СУБД, поддерживающими новый язык, стали в 1979 году Oracle V2 для машин VAX от компании Relational Software Inc. (впоследствии ставшей компанией Oracle) и System/38 от IBM, основанная на System/R.

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

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

В 1983 году Международная организация по стандартизации (ISO) и Американский национальный институт стандартов (ANSI) приступили к разработке стандарта языка SQL. По прошествии множества консультаций и отклонения нескольких предварительных вариантов, в 1986 году ANSI представил свою первую версию стандарта, описанную в документе ANSI X3.135-1986 под названием «Database Language SQL» (Язык баз данных SQL). Неофициально этот стандарт SQL-86 получил название SQL1. Год спустя была завершена работа над версией стандарта ISO 9075-1987 под тем же названием. Разработка этого стандарта велась под патронажем Технического Комитета TC97 (Technical Committee TC97), областью деятельности которого являлись процессы вычисления и обработки информации (Computing and Information Processing). Именно его подразделение, именуемое как Подкомитет SC21 (Subcommittee SC21), курировало разработку стандарта, что стало залогом идентичности стандартов ISO и ANSI для SQL1 (SQL-86).

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

Со временем к стандарту накопилось несколько замечаний и пожеланий, особенно с точки зрения обеспечения целостности и корректности данных, в результате чего в 1989 году данный стандарт был расширен, получив название SQL89. В частности, в него была добавлена концепция первичного и внешнего ключей. ISO-версия документа получила название ISO 9075:1989 «Database Language SQL with Integrity Enhancements» (Язык баз данных SQL с добавлением контроля целостности). Параллельно была закончена и ANSI-версия.

Сразу после завершения работы над стандартом SQL1 в 1987 году была начата работа над новой версией стандарта, который должен был заменить стандарт SQL89, получив название SQL2, поскольку дата принятия документа на тот момент была неизвестна. Таким образом, фактически SQL89 и SQL2 разрабатывались параллельно. Новая версия стандарта была принята в 1992 году, заменив стандарт SQL89. Новый стандарт, озаглавленный как SQL92, представлял собой по сути расширение стандарта SQL1, включив в себя множество дополнений, имевшихся в предыдущих версиях инструкций.

Как и SQL1, SQL92 также был разделён на несколько уровней, однако, во-первых, число уровней было увеличено с двух до трёх, а во-вторых, они получили названия вместо порядковых цифр: начальный (entry), средний (intermediate), полный (full). Уровень «полный», как и Уровень 2 в SQL1 подразумевал весь стандарт целиком. Уровень «начальный» представлял собой подмножество уровня «средний», в свою очередь, представлявшего собой подмножество уровня «полный». Уровень «начальный» был сравним с Уровнем 2 стандарта SQL1, но спецификации этого уровня были несколько расширены. Таким образом, цепочка включений уровней стандартов выглядела примерно следующим образом: SQL1 Уровень 1 → SQL1 Уровень 2 → SQL92 «Начальный» → SQL92 «Средний» → SQL92 «Полный».

После принятия стандарта SQL92 к нему были добавлены ещё несколько документов, расширявших функциональность языка. Так, в 1995 году был принят стандарт SQL/CLI (Call Level Interface, интерфейс уровня вызовов), впоследствии переименованный в CLI95. На следующий год был принят стандарт SQL/PSM (Persistent Stored Modules, постоянно хранимые модули), получивший название PSM-96.[3]

Следующим стандартом стал SQL:1999 (SQL3). В настоящее время действует стандарт, принятый в 2003 году (SQL:2003) с небольшими модификациями, внесёнными позже (SQL:2008). История версий стандарта:

Год Название Иное название Изменения
1986 SQL-86 SQL-87 Первый вариант стандарта, принятый институтом ANSI и одобренный ISO в 1987 году.
1989 SQL-89 FIPS 127-1 Немного доработанный вариант предыдущего стандарта.
1992 SQL-92 SQL2, FIPS 127-2 Значительные изменения (ISO 9075); уровень Entry Level стандарта SQL-92 был принят как стандарт FIPS 127-2.
1999 SQL:1999 SQL3 Добавлена поддержка регулярных выражений, рекурсивных запросов, поддержка триггеров, базовые процедурные расширения, нескалярные типы данных и некоторые объектно-ориентированные возможности.
2003 SQL:2003 Введены расширения для работы с XML-данными, оконные функции (применяемые для работы с OLAP-базами данных), генераторы последовательностей и основанные на них типы данных.
2006 SQL:2006 Функциональность работы с XML-данными значительно расширена. Появилась возможность совместно использовать в запросах SQL и XQuery.
2008 SQL:2008 Улучшены возможности оконных функций, устранены некоторые неоднозначности стандарта SQL:2003[4]

Для поставщиков СУБД стандарт — это путеводная звезда, которая гарантирует правильное направление работ. А вот эффективность реализации стандарта — это гарантия успеха. SQL нельзя в полной мере отнести к традиционным языкам программирования, он не содержит традиционные операторы, управляющие ходом выполнения программы, операторы описания типов и многое другое, он содержит только набор стандартных операторов доступа к данным, хранящимся в базе данных. Операторы SQL встраиваются в базовый язык программирования, которым может быть любой стандартный язык типа C++, PL, COBOL и т. д. Кроме того, операторы SQL могут выполняться непосредственно в интерактивном режиме.

Альтернатива

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

  • .QL: Объектно-ориентированный Datalog
  • 4D Query Language (4D QL)
  • BQL: Надмножество, которое сводится к SQL
  • Datalog: Критики предполагают, что Datalog имеет два преимущества перед SQL: он имеет более понятную семантику, что облегчает понимание и обслуживание программ, и это более выразительно, в частности, для рекурсивных запросов.
  • HTSQL: Метод запросов на основе URL
  • IBM Business System 12 (IBM BS12): Одна из первых полностью реляционных систем управления базами данных, введенная в 1982 году
  • ISBL
  • jOOQ: SQL, реализованный на Java как внутренний язык, специфичный для домена
  • Java Persistence Query Language (JPQL): Язык запросов, используемый API Java Persistence и библиотеку персистентности вспящем режиме
  • LINQ: Запускает операторы SQL, написанные как языковые конструкции, для запроса коллекций непосредственно изнутри кода .Net.
  • Object Query Language
  • QBE (Query By Example), созданный Moshè Zloof, IBM в 1977
  • Quel Введенный в 1974 U.C. Berkeley.
  • Tutorial D
  • XQuery

Структура

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

SQL включает в себя выражения, решающие широкий круг задач:

  • Запросы к БД.
  • Вставка, обновление, удаление строк из таблиц.
  • Создание, замена, изменение и удаление таблиц и других объектов.
  • Управление доступом пользователей к объектам и услугам СУБД.
  • Средства гарантии целостности реляционной БД.

Команды языка SQL чатсо разделяют на наиболее крупные сегменты:

  • Data Definition Language - синтаксис объявления схем реляционных баз данных.
  • Data Manipulation Language - синтаксис запросов, команд добавления, изменения и удаления.
  • Data Control Language - команды управления доступом к данным и операциям над ними различных учётных записей.

Синтаксис

Структура программы

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

Data definition language

Data Definition Language используется для модификации схемы реляционной базы данных. Этот раздел языка состоит из четырёх типов утверждений: CREATE, ALTER, DROP, RENAME.

Create

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

CREATE TABLE employees (
    id            INTEGER      PRIMARY KEY,
    first_name    VARCHAR(50) not null,
    last_name     VARCHAR(75) not null,
    fname         VARCHAR(50) not null,
    dateofbirth   DATE         not null
);

ALTER

Команда ALTER используется для модификации уже существующего в БД объекта.

ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE office ADD CONSTRAINT unique_name_and_description UNIQUE (name, description);
ALTER TABLE office DROP CONSTRAINT unique_name_and_description;

DROP

Команда DROP уничтожает существующий объект (будь то база данных, таблица или иной объект).

DROP TABLE employees;

RENAME

Команда RENAME используется для переименования таблицы.

RENAME TABLE old_name TO new_name;

Data manipulation language

Data Manipulation Language используется для составления запросов к СУБД или модификации её содержимого. Раздел языка состоит из четырёх типов утверждений: SELECT, INSERT, UPDATE и DELETE.

SELECT

SELECT извлекает 0 или более строк из различных таблиц или отображений.

SELECT *
FROM reunion
WHERE (priority = 'B' AND NOT duration <= 60) OR date = '2008-05-12';

Декларативное утверждение SELECT формулирует запрос с помощью условий FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER_BY, DISTINCT и др. Возможны вложенные запросы, хотя их производительность обычно уступает классическому подходу с применением JOIN. Вложенный запрос также называют подзапросом.

INSERT

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

INSERT INTO reunion (id_reunion, name, description, priority, planned, date, hour, duration, id_office)

UPDATE

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

UPDATE reunion
SET description = 'Meeting with Ms. JOHNSON', date = '2010-02-11', hour = '08:00:00'
WHERE id_reunion = '14';

DELETE

DELETE удаляет заданный условием набор строк.

DELETE FROM reunion
WHERE duration = 120;

MERGE

MERGE объединяет элементы нескольких таблиц.

MERGE INTO table_name USING table_reference ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 ]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 ]) VALUES (value1 [, value2 ]);

Data control language

Синтаксис Data Control Language используется для ограничения прав пользователей базы данных. Содержит два основных утверждения: GRANT и REVOKE.

GRANT

GRANT предоставляет привилегии пользователю. Все команды SQL выполняются от имени определённого пользователя.

GRANT INSERT, UPDATE, DELETE ON office TO DEVELOPER_2 DEVELOPER_3;

REVOKE

REVOKE снимает привилегии с пользователя. Для полного снятия привилегии необходимо её снятие с понижаемого в полномочиях пользователя всеми пользователями, её давшими.

REVOKE INSERT, DELETE ON TABLE office FROM DEVELOPER_2, DEVELOPER_3;

Управление транзакциями

START TRANSACTION, COMMIT, SAVE TRANSACTION, ROLLBACK - набор команд, использующихся для организации транзакций и обеспечивающих надёжность и целостность реляционной БД.

START TRANSACTION;
 UPDATE Account SET amount=amount-200 WHERE account_number=1234;
 UPDATE Account SET amount=amount+200 WHERE account_number=2345;
IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;

Преимущества и недостатки

Преимущества

Независимость от конкретной СУБД

Несмотря на наличие диалектов и различий в синтаксисе, в большинстве своём тексты SQL-запросов, содержащие DDL и DML, могут быть достаточно легко перенесены из одной СУБД в другую. Существуют системы, разработчики которых изначально ориентировались на применение по меньшей мере нескольких СУБД (например: система электронного документооборота Documentum может работать как с Oracle Database, так и с Microsoft SQL Server и DB2). Естественно, что при применении некоторых специфичных для реализации возможностей такой переносимости добиться уже очень трудно.

Наличие стандартов

Наличие стандартов и набора тестов для выявления совместимости и соответствия конкретной реализации SQL общепринятому стандарту только способствует «стабилизации» языка. Правда, стоит обратить внимание, что сам по себе стандарт местами чересчур формализован и раздут в размерах (например, базовая часть стандарта SQL:2003 состоит из более чем 1300 страниц текста).

Декларативность

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

Недостатки

Несоответствие реляционной модели данных

Создатели реляционной модели данных Эдгар Кодд, Кристофер Дейт и их сторонники указывают на то, что SQL не является истинно реляционным языком. В частности, они указывают на следующие дефекты SQL с точки зрения реляционной теории[5]:

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

В опубликованном Кристофером Дейтом и Хью Дарвеном Третьем манифесте[6] они излагают принципы СУБД следующего поколения и предлагают язык Tutorial D, который является подлинно реляционным.

Сложность

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

Отступления от стандартов

Несмотря на наличие международного стандарта ANSI SQL-92, многие разработчики СУБД вносят изменения в язык SQL, применяемый в разрабатываемой СУБД, тем самым отступая от стандарта. Таким образом появляются специфичные для каждой конкретной СУБД диалекты языка SQL.

Сложность работы с иерархическими структурами

Ранее диалекты SQL большинства СУБД не предлагали способа манипуляции древовидными структурами. Некоторые поставщики СУБД предлагали свои решения (например, в Oracle Database используется выражение CONNECT BY). В настоящее время в ANSI стандартизована рекурсивная конструкция WITH из диалекта SQL DB2. В Microsoft SQL Server рекурсивные запросы (Recursive Common Table Expressions) появились лишь в версии 2005.

Распределенная обработка SQL

Архитектура распределенной реляционной базы данных (DRDA) была разработана рабочей группой в IBM в период с 1988 по 1994 год. DRDA позволяет связанным с сетью реляционным базам данных взаимодействовать для выполнения запросов SQL. Интерактивный пользователь или программа может выдавать SQL-запросы локальному RDB и получать таблицы данных и индикаторы состояния в ответ от удаленных RDB. Операторы SQL также могут быть скомпилированы и сохранены в удаленных RDB как пакеты, а затем вызваны именем этого пакета. Это важно для эффективной работы прикладных программ, которые вызывают сложные высокочастотные запросы. Это особенно важно, когда доступ к таблицам находится в удаленных системах. Сообщения, протоколы и структурные компоненты DRDA определяются архитектурой распределенного управления данными.

См. также

Ссылки

Источники

  1. Ryan Paul. "A guided tour of the Microsoft Command Shell". Ars Technica. Archived from the original on 2012-02-03. 
  2. "Databases Demystified". McGraw-Hill Osborne Media. San Francisco, CA: Andy Oppel. pp. 90—91. ISBN 0-07-146960-5.  
  3. "Standardization of SQL". 
  4. "Window functions patch v04 for the September commit fest". 
  5. O’Reilly Network An Interview with Chris Date
  6. The Third Manifesto