8 февраля [Лекции]

Операторы определения данных в SQL – CREATE, DROP

Базы данных содержат различные объекты – таблицы (Table), представления (View), индексы (Index) и другие. Определять данные объекты с точки зрения разработки всей системы лучше всего на этапе проектирования базы данных, однако язык SQL позволят оперировать ими и в оперативном режиме. Для этого в язык SQL включен ряд команд (язык DDL), которые позволяют создавать, изменять, удалять и переименовывать структуры объектов, предназначенных для хранения данных.

Всего таких команд 6 – CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME, но наибольшую важность представляют две из них: CREATE и DROP.

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

Создание таблицы производится следующим образом:

CREATE TABLE имя_таблицы

{

имя_столбца тип_данных [значение_по_умолчанию] [ограничение_столбца],

имя_столбца тип_данных [значение_по_умолчанию] [ограничение_столбца],

…,

[ограничения_таблицы]}

Квадратные скобки [] указывают на необязательность тех или иных параметров команды CREATE, «ограничение_столбца» и «ограничения_таблицы» опеределяют ограничения целостности для столбцов и таблицы соответственно, «значение_по_умолчанию» будет записано в столбец в случае отсутствия конкретного значения при добавлении нового кортежа. Тип данных определяет как тип данных столбца, так и размер области для хранения данных (длина строки, количество цифр числа и так далее). В простейшем случае создание таблицы для отношения Студент производится командой:

CREATE TABLE student

{

STUDENT_ID INTEGER,

ID_CARD_NUM VARCHAR(32),

LAST_NAME VARCHAR(70),

FIRST_NAME VARCHAR(40),

MID_NAME VARCHAR(40),

BIRTH_DATE DATE,

YEAR_IN INTEGER

}

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

- NOT NULL – обязательность значений для столбца (задается только на уровне столбца);

- UNIQUE – уникальность значений в столбце (при задании на уровне столбца допускает наличие неопределенных значений, автоматически создает уникальный индекс);

- PRIMARY KEY – первичный ключ (создает первичный ключ с параметрами UNIQUE и NOT NULL, автоматически создает уникальный индекс);

- FOREIGN KEY – внешний ключ (возможно использование также ключевых слов REFERENCES – указывает таблицу и поле, на которое ссылается, - и ON DELETE CASCADE – разрешает удаление строк в зависимой таблице при удалении строки в родительской);

- CHECK – дополнительные ограничения на вид значений столбца.

Синтаксис использования ограничения на уровне столбца:

имя_столбца [CONSTRAINT имя_ограничения] тип_ограничения,

на уровне таблицы:

[CONSTRAINT имя_ограничения] тип_ограничения

С учетом ограничений создание таблицы для отношения Студент с первичным ключом STUDENT_ID, уникальным непустым полем ID_CARD_NUM (номер студенческого билета), непустыми полями LAST_NAME, FIRST_NAME, BIRTH_DATE и YEAR_IN (фамилия, имя, дата рождения и год поступления) будет выглядеть так:

CREATE TABLE student

{

STUDENT_ID INTEGER PRIMARY KEY,

ID_CARD_NUM VARCHAR(32) UNIQUE NOT NULL,

LAST_NAME VARCHAR(70) NOT NULL,

FIRST_NAME VARCHAR(40) NOT NULL,

MID_NAME VARCHAR(40),

BIRTH_DATE DATE NOT NULL,

YEAR_IN INTEGER NOT NULL

}

Ограничение CHECK задает условия, которым должны удовлетворять значения столбца или группы столбцов в каждой строке. Примером такого ограничения может быть CHECK (YEAR_IN BETWEEN 1999 AND 2007).

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

CREATE TABLE имя_таблицы [(имя_столбца, имя_столбца, …)] AS запрос

Например, создать таблицу сущности Студент можно из начальной таблицы Персоны, в которой перечислены все персоны с указанием их статуса (студент, преподаватель, работник и другие):

CREATE TABLE student AS

SELECT LAST_NAME, FIRST_NAME, MID_NAME, BIRTH_DATE FROM persons WHERE person_type=’student’

Удаление таблиц происходит следующим образом:

DROP TABLE имя_таблицы [CASCADE CONSTRAINTS]

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

DROP TABLE students

С помощью команд CREATE и DROP можно также создавать и удалять индексы, повышающую скорость работы с таблицей.

Создание индекса производится следующим образом:

CREATE INDEX имя_индекса ON имя_таблицы (имя_столбца [, имя_столбца, …])

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

Например, создание индекса для столбца ID_CARD_NUM в таблице STUDENT выполняется следующей командой:

CREATE INDEX id_card_index ON students (id_card_num)

Удаление индекса производится следующим образом:

DELETE INDEX имя_индекса

При этом указание имени индекса обязательно, а само удаление не изменяет значение поля (столбца), индекс для которого удаляется. Для удаления созданного в предыдущем примере индекса необходимо выполнить команду:

DELETE INDEX id_card_index

8 февраля [Лекции]

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

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

Запрос является инструкцией, с помощью которой SQL обращается к базе данных. Запрос состоит из одной или более логических частей, которые называются предложениями. Предложения начинаются ключевым словом и состоят из ключевых слов и аргументов. Ключевое слово – используемые в выражениях языка SQL слова, имеющие специальное значение (например, конкретные команды языка – CREATE, DROP, SELECT). Ключевые слова не могут быть использованы ни для каких других целей, например, в качестве имени объекта базы данных.

В языке SQL можно выделить следующие основные ключевые слова:

Операторы определения данных DDL

CREATE Создает структуры для хранения данных

ALTER Изменяет структуры данных или ограничения

DROP Удаляет структуры данных

TRUNCATE Очищает объекты, удаляя все записи и оставляя структуру неизменной

COMMENT Добавляет комментарии к объекту

RENAME Переименовывает объект

Операторы манипулирования данными DML

SELECT Выбирает данные из базы данных

INSERT Вставляет данные в базу данных

DELETE Удаляет данные из базы данных

UPDATE Изменяет данные в базе данных

Средства управления транзакциями

COMMIT Завершает транзакцию

ROLLBACK Отменяет (откатывает) транзакцию

SAVEPOINT Сохраняет промежуточное состояние базы данных

Средства администрирования данных

GRANT Предоставить права

REVOKE Лишить прав

Программный SQL

DECLARE Определяет курсор (имя и связанный с ним запрос), соответствующий виртуальному набору данных

OPEN Открывает курсор, формируя виртуальный набор данных, соответствующий описанию указанного курсора и текущему состоянию базы данных

FETCH Считывает очередную строку из множества, определенного курсором

CLOSE Закрывает курсор и прекращает доступ к этому виртуальному набору данных

PREPARE Подготавливает оператор SQL к динамическому выполнению

EXECUTE Выполняет оператор SQL, ранее подготовленный к выполнению

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

SQL поддерживает следующие основные типы данных:

CHAR(n) или CHARACTER(n) Символьные строки постоянной длины n. Для значения этого типа всегда отводится n символов, даже если фактически оно короче, тогда недостающие символы автоматически заменяются пробелами

NUMERIC[(n,m)] Точные сила, n – общее число цифр в числе, m – число цифр справа от запятой (то есть в десятичной числа)

DECIMAL[(n,m)] или DEC[(n,m)] Точные сила, n – общее число цифр в числе, m – число цифр справа от запятой (то есть в десятичной числа)

INTEGER или INT Целые числа

SMALLINT Целые числа меньшего диапазона значений

FLOAT[(n)] Числа с плавающей запятой большой точности, n – число байт, отводимое для хранения одного числа, диапазон значений определяется конкретной реализацией

REAL Числа с плавающей запятой меньшей точности, чем FLOAT

DOUBLE PRECISION Числа с плавающей запятой большей точности, чем та, что определена в данной реализации для типа REAL

Типы данных языка SQL/89

Необходимо обратить внимание, что в стандарте SQL1 не определялось точно, что подразумевается под типами INT и SMALLINT, а было указано только соотношение между ними. В большинстве реализаций тип данных INTEGER соответствует целым значениям, хранимым в 4 байтах, а SMALLINT – целым значениям, хранимым в 2 байтах. Выбор того или иного типа определяется, естественно, размером числа. Аналогично, типы REAL и DOUBLE PRECISION не указывались точно, а лишь связывались между собой.

VARCHAR(n) Строки символов переменной длины

NCHAR(n) Строки локализованных символов постоянной длины

NCHAR VARYING(n) Строки локализованных символов переменной длины

BIT(n) Битовая последовательность постоянной длины

BIT VARYING(n) Битовая последовательность переменной длины

DATE Календарная дата

TIMESTAMP(точность) Дата и время

INTERVAL Временной интервал

Типы данных, добавленные в языке SQL/92

Многие производители коммерческих СУБД поддерживают и дополнительные типы данных, не включенные в спецификацию SQL, например, тип данных для представления неструктурированного текста большого объема, аналог MEMO настольных СУБД. Однако, следует отметить, что специфика реализации даже отдельных основных типов данных серьезным образом влияет на результаты выполнения запросов к базе данных. Особенно это касается типов данных, связанных со временем и датой, - DATE и TIMESTAMP. Одна и та же дата, например 12 марта 2007 года, может быть записана в MS SQL Server как March 12, 2007, а в ORACLE – 12-MAR-07, что может отрицательно сказаться на переносимости приложения с одной базы данных на другую.

В свою очередь объекты базы данных также должны называться согласно определенным правилам:

- должны начинаться с буквы;

- могут быть не длиннее 30 символов;

- могут содержать только символы A-Z, a-z, 0-9, _ (подчеркивание), $ и #;

- не могут совпадать с именем другого объекта;

- не могут совпадать с зарезервированными словами сервера базы данных.

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

+ Сложение

- Вычитание

* Умножение

/ Деление

|| Конкатенация (сложение строк)

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

= Равно

<> Не равно

> Больше

< Меньше

>= Больше или равно

<= Меньше или равно

Однако, есть в языке SQL и четыре специальных оператор сравнения SQL:

BETWEEN … AND … Между двумя значениями (включительно)

IN (список) Совпадает с одним из значений в списке

LIKE Соответствует символьному шаблону

IS NULL Является неопределенным значением

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

NOT BETWEEN … AND … НЕ между двумя значениями (включительно)

NOT IN (список) НЕ совпадает с одним из значений в списке

NOT LIKE НЕ соответствует символьному шаблону

IS NOT NULL НЕ является неопределенным значением

Список логических операторов языка SQL ограничивается тремя стандартными операторами:

AND Логическое И

IN (список) Логическое ИЛИ

LIKE Логическое НЕ

8 февраля [Лекции]

История появления и развития SQL

Язык SQL появился после создания реляционной алгебры. Его прототип был разработан в 1974 году компанией IBM Research. Он был реализован в первой реляционной СУБД фирмы IBM System R. В дальнейшем этот язык распространился и применялся во многих коммерческих СУБД (SQL/DS в 1981 году и DB2 в 1983 году – обе СУБД разработаны также IBM) и в результате в силу своего широкого распространения стал стандартом «де-факто» для языков манипулирования данными в реляционных СУБД.

Первый международный стандарт был принят в 1989 году, его часто называют SQL/89 или SQL1. Иногда SQL1 также называют ANSI/ISO. Большинство современных доступных на рынке СУБД поддерживают этот стандарт полностью. Но как это часто бывает, развитие информационных технологий в области баз данных и необходимость реализации переносимости приложений потребовали доработки и расширения первого стандарта SQL.

В конце 1992 года был принят новый международный стандарт языка SQL, который по аналогии получил названия SQL/92 или SQL2. Он также не лишен недостатков, но вместе с тем является гораздо более полным и точным, чем SQL/98. В настоящий момент большинство производителей СУБД уже внесли в свои продукты изменения, позволяющие им в большей степени удовлетворять стандарту SQL2.

Еще спустя 7 лет в 1999 году появился очередной международный стандарт языка SQL3. И если в основном отличия стандартов SQL1 и SQL2 можно назвать «количественными», то стандарт SQL3 уже олицетворял серьезные качественные изменения. В SQL3 были введены новые типы данных, а также возможность создания сложных структурированных типов данных, которые в большей мере соответствуют объектно-ориентированному подходу. Кроме того, были добавлены разделы со стандартами событий и триггеров, которые ранее не затрагивались вообще, хотя и применялись широко и давно в коммерческих СУБД.

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

8 февраля [Лекции]

Язык баз данных SQL

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

Различают два вида языка SQL: интерактивный и встроенный SQL. Интерактивный SQL используется для задания SQL-запросов пользователем и получения результатов в интерактивном режиме. Встроенный SQL состоит из команд языка, встроенных в тело программ, написанных на других языках программирования, например, С++. Однако, при этому требуются дополнительные средства для связи SQL с языком, в который он встраивается.

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

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

DML (Data Manipulation Language) – язык обработки данных, позволяющий работать непосредственно с информацией, хранимой в базе данных (извлекать, изменять, удалять и так далее).

Необходимо понимать, что это не два разных языка, а две составные части одного. Стоит также отметить, что в некоторых источниках оператор SELECT языка SQL выделяют в качестве оператора еще одной составляющей языка – DQL (Data Query Language) – языка запросов.

8 февраля [Лекции]

Введение

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

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