Язык запросов 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