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

Лекция 1. Язык запросов SQL В настоящее время все большее распространение получают информационные системы с архитектурой клиент-сервер. Основой таких систем являются SQL-ориентированные СУБД, выполняемые на выделенных серверах (SQL-серверах). Причинами роста популярности клиент-серверных систем являются успехи в области телекоммуникаций, высокая производительность систем данного класса, их большая информационная емкость, высокая надежность, безопасность, возможность одновременной работы с одним и тем же источником данных нескольких пользователей, легкость реконфигурации системы, позволяющая возможность поэтапного наращивания ресурсов базы данных и подключения новых пользователей, возможность распределенной обработки и работа с разнородными источниками данных. Последнее свойство определяется использованием в системе единого языкового стандарта для работы с базами данных – языка SQL. Многие компьютерные фирмы производят программное обеспечение, “превращающее” обычный компьютер в SQL-сервер. К наиболее известным и распространенным SQL-серверам относятся: Microsoft SQL Server, Oracle Server, Sybase SQL Server, Informix Online, Watcom SQL Network Server, XDB-Enterprise, Gupta SQLBase Server, DB2, Borland InterBase Workgroup Server, Progress и другие. Стандарт SQL определяет подъязык данных, который используется в контексте другого языка, называемого включающим, для того, чтобы выполнять специализированные задачи управления базами данных. Такая взаимосвязь между включающим языком и подъязыком данных SQL называется стилем связывания. SQL-92 определяет три типа связывания: встроенный SQL, модульный язык и непосредственный вызов. В 1995 году американские и международные комитеты, ответственные за стандарт языка 1 SQL, завершили работу над спецификацией нового стиля связывания, названного Call Level Interface (SQL/CLI – интерфейс уровня вызовов) [3,16]. SQL/CLI был одобрен как международный стандарт официально названный “ISO/IEC 9075-3:1995, Information Technology – Database Languages – SQL – Part 3, Call Level Interface (SQL/CLI)”. 1. История SQL Первый международный стандарт языка SQL был принят в 1989 г. (SQL/89). В конце 1992 г. Был принят новый международный стандарт SQL/92. “Родным” языком Microsoft SQL Server является язык TransactSQL (TSQL), являющийся диалектом стандартного языка SQL. T-SQL поддерживает большинство возможностей языков SQL/89 и SQL/92, а также ряд расширений, увеличивающих возможность программирования и гибкость языка. В частности, в язык T-SQL добавлены конструкции для задания последовательности операций управления в программе (например, if и while), локальных переменных и других конструкций, позволяющих писать более сложные запросы и строить программные объекты, хранящиеся на сервере, в том числе процедуры и триггеры. Язык SQL включает следующие языки:  язык определения данных (Data Definition Language или DDL), предназначенный для добавления, модификации и удаления данных в таблицах;  язык модификации данных (Data Modification Language или DML), предназначенный для добавления, модификации и удаления данных в таблицах. В синтаксических использоваться конструкциях следующие при соглашения. описании языка Нетерминальные будут элементы заключаются в угловые скобки <>. Необязательная конструкция заключается в квадратные скобки []. Запись вида {A}… означает повторение конструкции 2 А произвольное число раз (включая нулевое). Вертикальные разделители | читаются как “ИЛИ” и служат для выбора одной из конструкций, заключенных в скобки. Этапы развития SQL 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[6] Уровни соответствия: Впервые уровни соответствия стандарту были предложены в SQL92. Были определены три категории: Entry, Intermediate и Full (основной (начальный), средний и полный). Чтобы производитель мог утверждать, что 3 его продукт соответствует стандарту ANSI SQL, соответствие должно быть не ниже уровня Entry. Национальный институт стандартов и технологий США (NIST) позже предложил еще один уровень – Transitional (Промежуточный), расположенный между уровнями Entry и Intermediate. Так что уровни соответствия по NIST были следующие: Entry, Transitional, Intermediate и Full, а по ANSI - только Entry, Intermediate и Full. Каждый более высокий уровень являлся расширением предыдущего уровня. Это означает, что каждый более высокий уровень соответствия стандарту включал в себя все свойства более низкого уровня. В стандарте SQL99 определено обязательное функциональное ядро (Core) и набор уровней расширенного соответствия. Уровни расширенного соответствия не являются обязательными для реализации в СУБД, претендующей на поддержку стандарта SQL99. СУБД может не поддерживать ни одного уровня расширенного соответствия или поддерживать любые из них. Каждый уровень описывает набор возможностей языка SQL, которые должны поддерживать реализации СУБД, претендующие на данный уровень соответствия. Каждая из реализаций языка SQL в конкретной СУБД называется диалектом. Функции, которые добавляются к стандарту языка разработчиками, принято называть расширениями. Перечень процедурных расширений для самых популярных СУБД приведён в следующей таблице: СУБД InterBase/ Firebird IBM DB2 Краткое название Расшифровка PSQL Procedural SQL SQL SQL Procedural Language (расширяет SQL/PSM); PL (англ.) также в DB2 хранимые процедуры могут 4 СУБД Краткое название Расшифровка писаться на обычных языках программирования: Си, Java и т. д. MS SQL Server/ Sybase ASE TransactSQL Transact-SQL SQL/Persistent Stored Module (соответствует MySQL SQL/PSM Oracle PL/SQL Procedural Language/SQL (основан на языке Ada) PL/pgSQ Procedural Language/PostgreSQL Structured Query L Language (очень похож на Oracle PL/SQL) PostgreSQL стандарту SQL:2003) В широко распространенных в настоящее время СУБД используются следующие диалекты языка SQL: PL/SQL – в СУБД Oracle; Transact-SQL – в СУБД Microsoft SQL; Jet SQL – Microsoft Access. Классификация интерфейсов SQL: - Интерактивный (или автономный) SQL дает возможность пользователям непосредственно извлекать информацию из базы или записывать в нее данные. - Статический SQL позволяет записать фиксированный исполняемый код SQL, он обычно используется в приложениях на процедурных языках. Есть две разновидности: встроенный и модульный. Встроенный SQL определен как код SQL, который включен в исходный текст программы, написанной на другом языке программирования. В модульном варианте операторы SQL записаны в отдельных модулях, которые компонуются с модулями основного языка. 5 - Динамический SQL дает возможность генерировать код SQL во время исполнения приложения и используется вместо статического SQL в тех случаях, когда при разработке приложения необходимый код SQL еще не может быть определен или зависит от того, какой выбор сделает пользователь. Операторы Динамического SQL обычно применяются в диалоговых средах для построения запросов и в графических средствах разработки приложений БД. 2. Типы данных SQL 1. Простые: 1.1. Символьные типы 1) Строки постоянной длины CHAR[ACTER](<длина>) – строка текста в формате, определенном разработчиком. Натуральное число задает <длину> строки. На практике максимальное число символов бывает в диапазоне от 256 в MS SQL Server до 32767 в InterBase. CHAR трактуется как CHAR(1) 2) Строки переменной длины VARCHAR|CHAR[ACTER] VARYING [(<длина>)] – строка текста переменной длины в формате, определенном разработчиком. Натуральное число задает максимальную <длину> строки, но в таблице отводится место только под реальную длину строки. 1.2. Числовые типы 1) Целые типы данных 6 INT[EGER] – число без десятичной точки. Размер зависит от конкретного варианта реализации. Часто это 4 байта. SMALLINT – совпадает с INT, но обычно меньше по размеру. Часто 2 байта. BIGINT – совпадает с INT, но обычно больше по размеру. Это 4 или более байта. 2) Вещественные числа с фиксированной точкой DEC[IMAL]|[NUMERIC[(<точность>[,<масштаб>])] – десятичное число с фиксированной точкой. Число имеет: <точность> — общее число значащих десятичных разрядов, <масштаб> — максимальное количество разрядов справа от десятичной точки. 3) Вещественные числа с плавающей точкой FLOAT – число с плавающей точкой, представленное в экспоненциальной форме по основанию 10. Задается максимальная точность. REAL – совпадает с FLOAT, но точность зависит от варианта реализации. DOUBE[PRECISION] – совпадает с REAL, но точность может быть больше в конкретной реализации. 1.3. Типы даты и типы времени DATE – дата в формате yyyy-mm-dd (ISO), mm/dd/yyyy (ANSI). TIME – время в формате hh.mm.ss (ISO), hh:mm am/pm (ANSI). INTERVAL – дата и время в формате yyyy-mm-dd-hh.mm.ss.nnnnn (ISO). (часто TIMESTAMP). Тип данных SQL datetime smalldatetime date time от до Jan 1, 1753 Dec 31, 9999 Jan 1, 1900 Jun 6, 2079 Сохраняет дату как June 30, 1991 Сохраняет время как 12:30 P.M. 7 1.4. Логический тип BOOLEAN – логическое значение (TRUE, FALSE, UNKNOWN). Для правильного понимания таблицы истинности в трехзначной логике (3VL) можно условно считать, что FALSE — 0, TRUE -1, а UNKNOWN – 0.5. Тогда: — Оператор AND возвращает наименьшее. — Оператор OR – наибольшее из исходных значений. — NOT UNKNOWN = UNKNOWN. 2. Коллекции Коллекции PL/SQL. Коллекция(collection) – это составная структура данных, которая ведёт себя как список или одномерный массив. В PL/SQL существует три типа коллекций: асcоциативные (индексные) массивы (associative arrays), вложенные таблицы (nested tables) и массивы переменной размерности (variable arrays) – VARRAY. Коллекции реализуются как типы (TYPE). Как и для других типов, определяемых пользователем, необходимо сначала определить тип, а затем объявить экземпляр такого типа. Определение типа может быть сохранено в БД или помещено в программу PL/SQL. Каждый экземпляр такого типа является коллекцией. CREATE [OR REPLACE] TYPE имя_типа IS TABLE OF тип_элемента [NOT NULL] 2.1. Массив <имя поля> <тип данных>[(<длина>)] ARRAY[<число элементов>] – набор однотипных значений. Пример: Так, определение WeekDays Varchar(10) ARRAY[7] позволяет хранить название всех семи дней недели в одном поле. Ряд СУБД допускают даже многомерные массивы. 2.2. Мультимножество 8 <имя поля> <тип данных>[(<длина>)] MULTISET – неограниченный набор однотипных значений, допускающий дубликаты. 2.3. Анонимный строковый тип <имя поля> ROW (<имя поля> <тип данных> [(<длина>)] [<опции>], …) – набор разнотипных значений, включая вложенные. Опции могут задавать порядок сортировки полей строкового типа и ряд других установок. Пример: Так, определив Address ROW(State Char(6), City Varchar(30), Street Varchar(50)) позволяет хранить подробный адрес в одном поле. 3. Типы LOB CLOB (Character Large Object) – ведут себя во многом подобно символьным строкам, но их запрещено использовать: — В ограничениях Primary Key, Unique, Foreign Key. — В сравнениях, отличных от чистых равенств или неравенств, в разделах Order By и Group By. BLOB (Binary Large Object) – поток байт в формате, в котором пользователь сможет их записать в колонку БД. LOB и проблемы их использования LOB (Тип данных больших объектов) - используется для хранения объектов данных размером до 2 Гбайт. Такие объекты обычно применяются для хранения больших объемов текстовых данных и для загрузки подключаемых модулей и аудио- и видеофайлов. В языке Transact-SQL поддерживаются следующие типы данных LOB:  VARCHAR(MAX);  NVARCHAR(MAX);  VARBINARY(MAX). Проблемы использования LOB 1) Хранение LOB прямо в таблицах вместе с другими данными нарушает работу оптимизатора, основанную на использовании страниц данных, размер 9 которых соответствует размеру дисковых страниц. Поэтому LOB хранятся в отдельных областях (сегментах) дисковой памяти. 2) Проблемы обновления Поскольку размер LOB объектов может достигать десятков и сотен мегабайт, то их невозможно хранить в буферах целиком. Поэтому данные типа LOB обрабатываются по частям, например, группами страниц. В операторах INSERT и UPDATE для обработки по частям используются специальные технологии, позволяющие многократно вызывать одну и ту же API-функцию для одного поля. Аналогично и при считывании данных операторами SELECT и FETCH. 3) Проблемы выполнения транзакций Для поддержки транзакций большинство СУБД ведет журнал транзакций, в котором записываются копии данных до и после модификаций. Однако из-за больших размеров LOB не записываются в журнал. 4) Проблемы пересылки по сети Часто клиент и сервер работают на разных компьютерах, и пересылка LOB по сети может прервать работу всех, кто пользуется сетью в данных момент Приоритеты типов В SQL Server используется следующий приоритет типов данных: 1) определяемые пользователем типы данных (высший приоритет); 2) sql_variant 3) xml 4) datetime 5) date 6) time 7) float 8) real 9) decimal 10) money 10 11) bigint 12) int 13) bit 14) text 15) image 16) nvarchar 17) nchar 18) varchar 19) char 20) binary (низший приоритет) 11 3. Оператор SELECT Оператор SELECT используется для запросов к базе данных и выборки результатов. Синтаксис оператора SELECT следующий: <оператор SELECT>::= SELECT [ALL | DISTINCT] <список выборки> <табличное выражение> ORDER BY <спецификация сортировки>] <табличное выражение>::= FROM <имя таблицы>[{,<имя таблицы>}…] [WHERE <условие поиска>] [GROUP BY <имя столбца> [{,<имя столбца>}…] [HAVING <условие поиска>] Если задано ключевое слово DISTINCT, то из результирующей таблицы удаляются повторяющиеся строки. Список выборки определяет, какие столбцы должны быть возвращены в результирующую таблицу. Данный список представляет список арифметических выражений над значениями столбцов таблиц из раздела FROM и констант. В простейшем случае он может быть, например, списком имен некоторых столбцов таблиц из раздела FROM. В случае, если вместо списка выборки стоит звездочка (*), то выбираются все столбцы таблиц из раздела FROM. В разделе FROM определяются таблицы, из которых будут извлекаться данные. Следует отметить, что рядом с именем таблицы можно указывать еще одно имя - синоним имени таблицы, который можно использовать в других разделах табличного выражения. Раздел WHERE служит своего рода фильтром при отборе данных. 12 Выполнение раздела GROUP BY оператора выборки сводится к разбиению результирующей таблицы на множество групп строк, которое состоит из минимального числа таких групп, в которых для каждого столбца из списка столбцов раздела GROUP BY во всех строках каждой группы, включающей более одной строки, значения этого столбца совпадают. Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска является истинным. Условие поиска раздела HAVING задает условие на целую группу, а не на индивидуальные строки, поэтому в данном случае прямо можно использовать только столбцы, указанные в качестве столбцов группирования в разделе GROUP BY. Раздел ORDER BY позволяет установить желаемый порядок просмотра результирующей таблицы. Спецификация сортировки имеет следующий синтаксис: <спецификация сортировки>::= {<целое без знака> | <имя столбца>} [ASC | DESC] Как видно, фактически задается список столбцов, и для каждого столбца указывается порядок просмотра строк результирующей таблицы в зависимости от значений этого столбца (ASC - по возрастанию (умолчание), DESC - по убыванию). Указывать сортируемый столбец можно по имени или по порядковому номеру в результирующей таблице. Предикаты условия поиска В условии поиска могут использоваться следующие предикаты: предикат сравнения, предикат BETWEEN, предикат IN, предикат LIKE, предикат NULL, предикат с квантором и предикат EXISTS. Предикат IN определяется следующим образом: 13 <предикат IN>::= <выражение> [NOT] IN (<значение> [,<значение>...] | .<подзапрос>) Значение предиката является истинным, когда значение левого операнда совпадает хотя бы с одним значением списка правого операнда. Использование ключевого слова NOT осуществляет отрицание результата. Подзапрос – это запрос, используемый в предикате условия поиска. Результатом выполнения подзапроса является единственный столбец. Предикат BETWEEN определяется следующим образом: <предикат BETWEEN>::= <выражение> [NOT] BETWEEN <выражение> AND <выражение> По определению результат x BETWEEN y AND z тот же самый, что результат логического выражения x>=y AND x<=z. Предикат LIKE имеет следующий синтаксис: <предикат LIKE>::= <имя столбца> [NOT] LIKE <шаблон>[ESCAPE ] Значение предиката LIKE является истинным, если шаблон является подстрокой заданного столбца. При этом, если раздел ESCAPE отсутствует, то при составлении шаблона со строкой производится специальная интерпретация символов-заместителей шаблона: символ подчеркивания (“_”) обозначает любой одиночный символ, символ процента (“%”) обозначает последовательность произвольных символов произвольной длины (может быть нулевой), парные квадратные скобки представляют любой символ, записанный в скобках. Если же раздел ESCAPE присутствует и специфицирует некоторый одиночный символ x, то пары символов “x_” и “x%” представляют одиночные символы “_” и “%” соответственно. Предикат NULL описывается синтаксическим правилом: 14 <предикат NULL>::= <имя столбца> IS [NOT] NULL Значение “x IS NULL” является истинным, когда значение x неопределено. Предикат EXISTS имеет следующий синтаксис: <предикат EXISTS>::= EXISTS <подзапрос> Значение предиката является истинным, когда результат вычисления подзапроса не пуст. Агрегатные функции Агрегатные функции (функции множества) в запросе предназначены для вычисления некоторого значения для заданного множества строк. Таким множеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или вся таблица. В языке SQL определены следующие агрегатные функции:  AVG - функция определения среднего значения;  MAX - функция определения максимального значения;  MIN - функция определения минимального значения;  SUM - функция суммирования значений;  COUNT - функция для подсчета числа строк или значений. Грамматика агрегатных функций следующая: <агрегатная функция>::= COUNT(*) | | ::= {AVG | COUNT | MAX | MIN | SUM} (DISTINCT <имя столбца>) ::= {AVG | MAX | MIN | SUM} ([ALL]<выражение>) 15 Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном множестве. Функция типа distinct выполняет вычисления только над одним столбцом, а в вычислениях используются только уникальные значения столбца. При использовании функции типа all список значений формируется из значений арифметического выражения, вычисляемого для каждой строки заданного множества. Операции реляционной алгебры Большинство SQL-запросов требует одновременного обращения к нескольким таблицам. Часто такого рода запросы основываются на операциях реляционной алгебры, в частности, соединения, декартова произведения, объединения, пересечения и разности. При соединении двух таблиц по некоторому условию образуется результирующая таблица, строки которой являются конкатенацией (сцеплением) строк первой и второй таблиц и удовлетворяют этому условию. Операцию соединения можно реализовать с использованием обычного SQL-запроса типа SELECT-FROM-WHERE. По стандарту ANSI операция соединения таблиц может указываться явно в разделе FROM. Синтаксис раздела FROM в этом случае следующий: <раздел FROM>::= FROM <имя таблицы> [JOIN <имя таблицы> ON <условие соединения> ...] При выполнении декартова произведения двух таблиц производится таблица, строки которой являются конкатенацией строк первой и второй таблиц. Операцию декартова произведения можно реализовать с использованием SQL-запроса типа SELECT-FROM. По стандарту ANSI операция декартова произведения может указываться явно в разделе FROM с использованием ключевой фразы CROSS JOIN. При выполнении операции объединения двух таблиц производится таблица, включающая все строки, входящие хотя бы в одну из таблиц операндов. При этом число столбцов и типы данных этих столбцов должны 16 быть одинаковыми для всех операндов. Для объединения результирующих таблиц операторов SELECT используется ключевое слово UNION. Операция пересечения двух таблиц производит таблицу, включающую все строки, входящие в обе исходные таблицы. Таблица, являющаяся разностью двух таблиц, включает все строки, входящие в таблицу - первый операнд, такие, что ни одна из них не входит в таблицу, являющуюся вторым операндом. Подробнее познакомиться с синтаксисом оператора SELECT в SQL Server, назначением ключевых слов и примерами можно на сайте MSDN Microsoft: https://msdn.microsoft.com/ru-ru/library/ms189499.aspx. 17