СУБД (система управления базами данных) - программное обеспечение, с помощью которого пользователи могут определять, создавать и поддерживать базу данных, а также получать к ней контролируемый доступ.
Управление основными потоками информации осуществляется с помощью так называемых систем управления реляционными базами данных, которые берут свое начало в традиционных системах управления базами данных. В реляционной модели объекты реального мира и взаимосвязи между ними представляются с помощью совокупности связанных между собой таблиц (отношений).
Каждая таблица БД представляется как совокупность строк и столбцов, где строки (записи) соответствуют экземпляру объекта, конкретному событию или явлению, а столбцы (поля) - атрибутам (признакам, характеристикам, параметрам) объекта, события, явления.
В каждой таблице БД необходимо наличие первичного ключа - так именуют поле или набор полей, однозначно идентифицирующий каждый экземпляр объекта или запись. Значение первичного ключа в таблице БД должно быть уникальным, т.е. в таблице не допускается наличие двух и более записей с одинаковыми значениями первичного ключа. Он должен быть минимально достаточным, а значит, не содержать полей, удаление которых не отразится на его уникальности.
Между двумя или более таблицами базы данных могут существовать отношения подчиненности, которые определяют, что для каждой записи главной таблицы (называемой еще родительской) возможно наличие одной или нескольких записей в подчиненной таблице (называемой еще дочерней).
Выделяют три разновидности связи между таблицами базы данных:
Основные категории команд языка SQL:
Справочники, инструменты, документацияCREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, ALTER INDEX, DROP INDEX;INSERT, UPDATE, DELETE;SELECT. Эта команда вместе со своими многочисленными опциями и предложениями используется для формирования запросов к реляционной базе данных;GRANT, REVOKE;COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.Идентификаторы языка SQL предназначены для обозначения объектов в базе данных и являются именами таблиц, представлений, столбцов и других объектов базы данных. Символы, которые могут использоваться в создаваемых пользователем идентификаторах языка SQL, должны быть определены как набор символов. Стандарт SQL задает набор символов, который используется по умолчанию, - он включает:
A-Z, a-z);0-9) и символ подчеркивания (_).На формат идентификатора накладываются следующие ограничения:
Язык, в терминах которого дается описание языка SQL, называется метаязыком. Синтаксические определения обычно задают с помощью специальной металингвистической символики, называемой Бэкуса-Науэра формулами (БНФ). Прописные буквы используются для записи зарезервированных слов и должны указываться в операторах точно так, как это будет показано. Строчные буквы употребляются для записи слов, определяемых пользователем.
Применяемые в нотации БНФ символы и их обозначения:
::= - равно по определению;| - необходимость выбора одного из нескольких приведенных значений;<...> - описанная с помощью метаязыка структура языка;{...} - обязательный выбор некоторой конструкции из списка;[...] - необязательный выбор некоторой конструкции из списка;[,...n] - необязательная возможность повторения конструкции от нуля до нескольких раз (n).Данные - это совокупная информация, хранимая в базе данных в виде одного из нескольких различных типов. С помощью типов данных устанавливаются основные правила для данных, содержащихся в конкретном столбце таблицы, в том числе размер выделяемой для них памяти. В языке SQL имеется шесть скалярных типов данных, определенных стандартом:
| Тип | Представление в SQL |
|---|---|
| Символьный | CHAR, VARCHAR |
| Битовый | BIT, BIT VARYING |
| Точные числа | NUMERIC, DECIMAL, INTEGER, SMALLINT |
| Округленные числа | FLOAT, REAL, DOUBLE PRECISION |
| Дата/время | DATE, TIME, TIMESTAMP |
| Интервал | INTERVAL |
Символьные данные состоят из последовательности символов, входящих в определенный создателями СУБД набор символов. Чаще всего используются наборы символов ASCII и EBCDIC. Для определения данных символьного типа используется следующий формат:
<символьный_тип> ::=
{ CHARACTER [VARYING] [<длина>]
| [CHAR | VARCHAR] [<длина>]}Символьная строка может быть определена как имеющая фиксированную или переменную (VARYING) длину. Если строка определена с фиксированной длиной значений, то при вводе в нее меньшего количества символов значение дополняется до указанной длины пробелами, добавляемыми справа.
Битовый тип данных используется для определения битовых строк, т.е. последовательности двоичных цифр (битов), каждая из которых может иметь значение либо 0, либо 1. Данные битового типа определяются при помощи следующего формата:
<битовый_тип> ::= BIT [VARYING] [<длина>]Тип точных числовых данных применяется для определения чисел, которые имеют точное представление, т.е. числа состоят из цифр, необязательной десятичной точки и необязательного символа знака. Данные точного числового типа определяются точностью и длиной дробной части. Точность задает общее количество значащих десятичных цифр числа, в которое входит длина как целой части, так и дробной, но без учета самой десятичной точки. Масштаб указывает количество дробных десятичных разрядов числа.
<фиксированный_тип> ::=
{ NUMERIC [<точность> [,<масштаб>]]
| {DECIMAL | DEC} [<точность> [, <масштаб>]]
| {INTEGER | INT}
| SMALLINT}Типы NUMERIC и DECIMAL предназначены для хранения чисел в десятичном формате. По умолчанию длина дробной части равна нулю, а принимаемая по умолчанию точность зависит от реализации. Тип INTEGER (INT) используется для хранения больших положительных или отрицательных целых чисел. Тип SMALLINT - для хранения небольших положительных или отрицательных целых чисел; в этом случае расход внешней памяти существенно сокращается.
Тип округленных чисел применяется для описания данных, которые нельзя точно представить в компьютере, в частности действительных чисел. Округленные числа или числа с плавающей точкой представляются в научной нотации, при которой число записывается с помощью мантиссы, умноженной на определенную степень десяти (порядок), например: 10Е3, +5.2Е6, -0.2Е-4 . Для определения данных вещественного типа используется формат:
<вещественный_тип> ::=
{ FLOAT [<точность>]
| REAL
| DOUBLE PRECISION}Параметр точность задает количество значащих цифр мантиссы. Точность типов REAL и DOUBLE PRECISION зависит от конкретной реализации.
Тип данных "дата/время" используется для определения моментов времени с некоторой установленной точностью. Стандарт SQL поддерживает следующий формат:
<тип_даты/времени> ::=
{ DATE
| TIME [<точность>] [WITH TIME ZONE]
| TIMESTAMP [<точность>] [WITH TIME ZONE]}Тип данных DATE используется для хранения календарных дат, включающих поля YEAR (год), MONTH (месяц) и DAY (день).
Тип данных TIME - для хранения отметок времени, включающих поля HOUR (часы), MINUTE (минуты) и SECOND (секунды).
Тип данных TIMESTAMP - для совместного хранения даты и времени.
Параметр точность задает количество дробных десятичных знаков, определяющих точность сохранения значения в поле SECOND. Если этот параметр опускается, по умолчанию его значение для столбцов типа TIME принимается равным нулю (т.е. сохраняются целые секунды), тогда как для полей типа TIMESTAMP он принимается равным 6.
Наличие ключевого слова WITH TIME ZONE определяет использование полей TIMEZONE HOUR и TIMEZONE MINUTE, тем самым задаются час и минуты сдвига зонального времени по отношению к универсальному координатному времени (Гринвичскому времени).
Данные типа INTERVAL используются для представления периодов времени.
Величины DATETIME, DATE и TIMESTAMP могут быть заданы любым стандартным набором форматов:
'YYYY-MM-DD HH:MM:SS' или в формате 'YY-MM-DD HH:MM:SS';'YYYY-MM-DD' или в формате 'YY-MM-DD';'YYYYMMDDHHMMSS' или в формате 'YYMMDDHHMMSS', при условии, что строка понимается как дата;'YYYYMMDD' или в формате 'YYMMDD', при условии, что строка интерпретируется как дата;YYYYMMDDHHMMSS или в формате YYMMDDHHMMSS, при условии, что число интерпретируется как дата;YYYYMMDD или в формате YYMMDD, при условии, что число интерпретируется как дата.Домен - это набор допустимых значений для одного или нескольких атрибутов. Домен определяет все потенциальные значения, которые могут быть присвоены атрибуту. Стандарт SQL позволяет определить домен с помощью следующего оператора:
<определение_домена> ::=
CREATE DOMAIN <имя_домена> [AS] <тип_данных>
[DEFAULT <значение>] [CHECK (<допустимые_значения>)]Каждому создаваемому домену присваивается имя, тип данных, значение по умолчанию и набор допустимых значений. Приведенный формат оператора является неполным. При создании таблицы можно указать вместо типа данных имя домена.
Удаление доменов из базы данных выполняется с помощью оператора:
DROP DOMAIN <имя_домена> [RESTRICT | CASCADE]В случае указания ключевого слова CASCADE любые столбцы таблиц, созданные с использованием удаляемого домена, будут автоматически изменены и описаны как содержащие данные того типа, который был указан в определении удаляемого домена.
CAST(<выражение> AS <тип_данных>)CONVERT(<тип_данных> [(<длина>)], <выражение> [, <стиль>])CONVERT и CAST примерно одинаковы и могут быть взаимозаменяемыми.
С помощью аргумента стиль можно управлять стилем представления значений следующих типов данных: дата/время или нецелочисленный.
Выражения представляют собой комбинацию идентификаторов, функций, знаков логических и арифметических операций, констант и других объектов. Выражение может быть использовано в качестве аргумента в командах, хранимых процедурах или запросах. Выражение состоит из операндов (собственно данных) и операторов (знаков операций, производимых над операндами). В качестве операндов могут выступать константы, переменные, имена столбцов, функции, подзапросы.
Операторы - это знаки операций над одним или несколькими выражениями для создания нового выражения. Среди операторов можно выделить унарные операторы, операторы присваивания, арифметические операторы, строковые операторы, операторы сравнения, логические операторы, битовые операторы.
Объявление переменной выполняется командой:
DECLARE {<имя_переменной> <тип_данных>} [,...n]Значения переменной можно присвоить посредством команд SET и SELECT. С помощью команды SELECT переменной можно присвоить не только конкретное значение, но и результат вычисления выражения.
SET <имя_переменной> = <значение>SELECT ... INTO <имя_переменной> ...Группировка двух и более команд в единый блок осуществляется с использованием ключевых слов BEGIN и END:
<блок_операторов> ::=
BEGIN
{<sql_оператор> | <блок_операторов>}
ENDСгруппированные команды воспринимаются интерпретатором SQL как одна команда. Подобная группировка требуется для конструкций поливариантных ветвлений, условных и циклических конструкций. Блоки BEGIN...END могут быть вложенными.
Оператор выбора по сравнению со значению, может использоваться в SELECT:
CASE <значение>
WHEN [<сравниваемое_значение>] THEN <результат>
[WHEN [<сравниваемое_значение2>] THEN <результат> ...]
[ELSE <результат>]
ENDВторой вариант по условию:
CASE
WHEN [<условие>] THEN <значение>
[WHEN [<условие2>] THEN <значение>...]
[ELSE <значение>]
ENDIF (<условие>, <результат_истины>, <результат_лжи>)Логическая структура определяет структуру таблиц, взаимоотношения между ними, список пользователей, хранимые процедуры, правила, умолчания и другие объекты базы данных.
| Структура | Описание |
|---|---|
| Tables | Таблицы базы данных, в которых хранятся собственно данные |
| Views | Просмотры (виртуальные таблицы) для отображения данных из таблиц |
| Stored Procedures | Хранимые процедуры |
| Triggers | Триггеры - специальные хранимые процедуры, вызываемые при изменении данных в таблице |
| User Defined function | Создаваемые пользователем функции |
| Indexes | Индексы - дополнительные структуры, призванные повысить производительность работы с данными |
| User Defined Data Types | Определяемые пользователем типы данных |
| Keys | Ключи - один из видов ограничений целостности данных |
| Constraints | Ограничение целостности - объекты для обеспечения логической целостности данных |
| Users | Пользователи, обладающие доступом к базе данных |
| Roles | Роли, позволяющие объединять пользователей в группы |
| Rules | Правила базы данных, позволяющие контролировать логическую целостность данных |
| Defaults | Умолчания или стандартные установки базы данных |
Все данные в SQL содержатся в объектах - таблицами. Таблицы представляют собой совокупность каких-либо сведений об объектах, явлениях, процессах реального мира. Никакие другие объекты не хранят данные, но они могут обращаться к данным в таблице. Таблицы в SQL имеют такую же структуру, что и таблицы всех других СУБД и содержат:
Представлениями (просмотрами) называют виртуальные таблицы, содержимое которых определяется запросом. Подобно реальным таблицам, представления содержат именованные столбцы и строки с данными. Для конечных пользователей представление выглядит как таблица, но в действительности оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких таблицах. Информация, которую видит пользователь через представление, не сохраняется в базе данных как самостоятельный объект.
Хранимые процедуры представляют собой группу команд SQL, объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое.
Триггерами называется специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении или удалении данных из таблицы.
Функции - это конструкции, содержащие часто исполняемый код. Функция выполняет какие-либо действия над данными и возвращает некоторое значение.
Индекс - структура, связанная с таблицей или представлением и предназначенная для ускорения поиска информации в них. Индекс определяется для одного или нескольких столбцов, называемых индексированными столбцами. Он содержит отсортированные значения индексированного столбца или столбцов со ссылками на соответствующую строку исходной таблицы или представления. Повышение производительности достигается за счет сортировки данных. Использование индексов существенно повышает производительность поиска, однако для хранения индексов необходимо дополнительное пространство в базе данных.
Пользовательские типы данных - это типы данных, которые создает пользователь на основе системных типов данных, когда в нескольких таблицах необходимо хранить однотипные значения; причем нужно гарантировать, что столбцы в таблице будут иметь одинаковый размер, тип данных и чувствительность к значениям NULL.
Ограничения целостности - механизм, обеспечивающий автоматический контроль соответствия данных установленным условиям (или ограничениям). Ограничения целостности имеют приоритет над триггерами, правилами и значениями по умолчанию. К ограничениям целостности относятся: ограничение на значение NULL, проверочные ограничения, ограничение уникальности (уникальный ключ), ограничение первичного ключа и ограничение внешнего ключа. Последние три ограничения тесно связаны с понятием ключей.
Правила используются для ограничения значений, хранимых в столбце таблицы или в пользовательском типе данных. Они существуют как самостоятельные объекты базы данных, которые связываются со столбцами таблиц и пользовательскими типами данных. Контроль значений данных может быть реализован и с помощью ограничений целостности.
Умолчания - самостоятельный объект базы данных, представляющий значение, которое будет присвоено элементу таблицы при вставке строки, если в команде вставки явно не указано значение для этого столбца.
Таблица - основной объект для хранения информации в реляционной базе данных. Она состоит из содержащих данные строк и столбцов, занимает в базе данных физическое пространство и может быть постоянной или временной.
Поле, также называемое в реляционной базе данных столбцом, является частью таблицы, за которой закреплен определенный тип данных. Каждая таблица базы данных должна содержать хотя бы один столбец. Строка данных - это запись в таблице базы данных, она включает поля, содержащие данные из одной записи таблицы.
Базовый синтаксис оператора создания таблицы имеет следующий вид:
<определение_таблицы> ::=
CREATE TABLE <имя_таблицы>
(<имя_столбца> <тип_данных> [NULL | NOT NULL] [UNIQUE]
[DEFAULT <значение>]
[CHECK (<условие_выбора>)]
[,...n])
[CONSTRAINT <имя_ограничения>]
[PRIMARY KEY (<имя_столбца> [,...n])
[FOREIGN KEY (<имя_столбца_внешнего_ключа> [,...n])
REFERENCES <имя_род_таблицы> [(<имя_столбца_род_таблицы> [,...n])] [,...n]
[MATCH {PARTIAL | FULL}]
[ON UPDATE {CASCADE | SET NULL | SET DEFAULT | NO ACTION}]
[ON DELETE {CASCADE | SET NULL | SET DEFAULT | NO ACTION}]Ключевое слово NULL используется для указания того, что в данном столбце могут содержаться значения NULL. Значение NULL отличается от пробела или нуля - к нему прибегают, когда необходимо указать, что данные недоступны, опущены или недопустимы. Если указано ключевое слово NOT NULL, то будут отклонены любые попытки поместить значение NULL в данный столбец.
Фраза DEFAULT, которая предназначена для задания принимаемого по умолчанию значения, когда в операторе INSERT значение в данном столбце будет отсутствовать.
Где дополнительные ограничение можно задать с помощью CHECK, где будет указано условие выбора для столбца, или множества столбцов из таблицы (столбцы используются по своему имени).
Фраза CONSTRAINT позволяет задать имя ограничению, что позволит впоследствии отменить то или иное ограничение с помощью оператора ALTER TABLE.
Первичный ключ таблицы должен иметь уникальное непустое значение в каждой строке, задается с помощью фразы PRIMARY KEY. В пределах таблицы она может указываться только один раз.
Внешние ключи представляют собой столбцы или наборы столбцов, предназначенные для связывания каждой из строк дочерней таблицы, содержащей этот внешний ключ, со строкой родительской таблицы, содержащей соответствующее значение потенциального ключа, задается с помощью предложения FOREIGN KEY, а фраза REFERENCES определяет имя родительской таблицы, т.е. таблицы, где находится соответствующий потенциальный ключ.
Определитель MATCH позволяет уточнить способ обработки значения NULL во внешнем ключе.
Если предпринимается попытка удалить или изменить в родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, то возможно указать стратегию:
CASCADE - выполняется удаление строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы;SET NULL - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL;SET DEFAULT - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;NO ACTION - операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена.CREATE TABLE notes (
id INTEGER PRIMARY KEY NOT NULL,
giud TEXT NOT NULL,
created INTEGER NOT NULL,
updated INTEGER NOT NULL,
name TEXT NOT NULL,
description TEXT NOT NULL,
category TEXT NOT NULL,
user_id NUMBER NOT NULL
)
FOREIGN KEY user_id REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADECREATE TABLE <имя_новой_таблицы> AS <запрос> - создает новую таблицу, на основании результата запроса и заполняет его данными.
Структура существующей таблицы может быть модифицирована с помощью команды ALTER TABLE, упрощенный синтаксис которой представлен ниже:
<изменение_таблицы> ::=
ALTER TABLE <имя_таблицы>
{ ADD [COLUMN] <имя_столбца> <тип_данных> [NULL | NOT NULL] [UNIQUE]
[DEFAULT <значение>]
[CHECK (<условие_выбора>)]
| MODIFY <имя_столбца> <тип_данных> [NULL | NOT NULL] [DEFAULT <значение>]
| DROP [COLUMN] <имя_столбца>
| ADD PRIMARY KEY (<имя_столбца> [,...n])
| DROP PRIMARY KEY
| ADD FOREIGN KEY (<имя_столбца_внешнего_ключа> [,...n])
REFERENCES <имя_род_таблицы> [(<имя_столбца_род_таблицы> [,...n])]
| MATCH {PARTIAL | FULL}
| ON UPDATE {CASCADE | SET NULL | SET DEFAULT | NO ACTION}
| ON DELETE {CASCADE | SET NULL | SET DEFAULT | NO ACTION}} [,...n]Команда позволяет добавлять и удалять столбцы, изменять их определения. Одно из основных правил при добавлении столбцов в существующую таблицу гласит: когда в таблице уже содержатся данные, добавляемый столбец не может быть определен с атрибутом NOT NULL.
С течением времени структура базы данных меняется: создаются новые таблицы, а прежние становятся ненужными и удаляются из базы данных с помощью оператора:
DROP TABLE <имя_таблицы> [RESTRICT | CASCADE]Если в операторе указано ключевое слово RESTRICT, то при наличии в базе данных хотя бы одного объекта, существование которого зависит от удаляемой таблицы, выполнение оператора DROP TABLE будет отменено. Если указано ключевое слово CASCADE, автоматически удаляются и все прочие объекты базы данных, чье существование зависит от удаляемой таблицы, а также другие объекты, зависящие от удаляемых объектов.
Индексы представляют собой структуру, позволяющую выполнять ускоренный доступ к строкам таблицы на основе значений одного или более ее столбцов. Наличие индекса может существенно повысить скорость выполнения некоторых запросов и сократить время поиска необходимых данных за счет физического или логического их упорядочивания. Индекс - это набор ссылок, упорядоченных по определенному столбцу таблицы, который в данном случае будет называться индексированным столбцом.
Физически индекс - всего лишь упорядоченный набор значений из индексированного столбца с указателями на места физического размещения исходных строк в структуре базы данных. Когда пользователь выполняет обращающийся к индексированному столбцу запрос, СУБД автоматически анализирует индекс для поиска требуемых значений. Однако, поскольку индексы должны обновляться системой при каждом внесении изменений в их базовую таблицу, они создают дополнительную нагрузку на систему.
Создание индексов не предусмотрено стандартом SQL, однако большинство диалектов поддерживают как минимум следующий оператор:
CREATE [UNIQUE] INDEX <имя_индекса>
ON <имя_таблицы>(<имя_столбца> [ASC | DESC] [,...n])Указанные в операторе столбцы составляют ключ индекса. Индексы могут создаваться только для базовых таблиц, но не для представлений. Если в операторе указано ключевое слово UNIQUE, уникальность значений ключа индекса будет автоматически поддерживаться системой.
Если созданный индекс впоследствии окажется ненужным, его можно удалить с помощью оператора:
DROP INDEX <имя_индекса>SELECTОператор SELECT - позволяет производить выборки данных из таблиц и преобразовывать к нужному виду полученные результаты. Оператор SELECT - средство, которое полностью абстрагировано от вопросов представления данных, что помогает сконцентрировать внимание на проблемах доступа к данным. Операции над данными производятся в масштабе наборов данных, а не отдельных записей. Оператор SELECT имеет следующий формат:
SELECT [ALL | DISTINCT] {* | [<имя_столбца> [AS <новое_имя> | "<новое_имя>"]]} [,...n]
FROM <имя_таблицы> [[AS] <псевдоним>] [,...n]
[WHERE <условие_поиска>]
[GROUP BY <имя_столбца> [,...n]]
[HAVING <критерии_выбора_групп>]
[ORDER BY <имя_столбца> [,...n]]Оператор SELECT определяет поля (столбцы), которые будут входить в результат выполнения запроса. В списке они разделяются запятыми и приводятся в такой очередности, в какой должны быть представлены в результате запроса. Если используется имя поля, содержащее пробелы или разделители, его следует заключить в квадратные скобки. Символом * можно выбрать все поля, а вместо имени поля применить выражение из нескольких имен.
Если обрабатывается ряд таблиц, то (при наличии одноименных полей в разных таблицах) в списке полей используется полная спецификация поля, т.е. <имя_таблицы>.<имя_поля> или <псевдоним>.<имя_поля>.
Предложение FROM задает имена таблиц и представлений, которые содержат поля, перечисленные в операторе SELECT. Необязательный параметр псевдонима - это сокращение, устанавливаемое для имени таблицы.
Обработка элементов оператора SELECT выполняется в следующей последовательности:
FROM - определяются имена используемых таблиц;WHERE - выполняется фильтрация строк объекта в соответствии с заданными условиями;GROUP BY - образуются группы строк, имеющих одно и то же значение в указанном столбце;HAVING - фильтруются группы строк объекта в соответствии с указанным условием;SELECT - устанавливается, какие столбцы должны присутствовать в выходных данных;ORDER BY - определяется упорядоченность результатов выполнения операторов.Порядок предложений и фраз в операторе SELECT не может быть изменен. SELECT - закрытая операция: результат запроса к таблице представляет собой другую таблицу. Существует множество вариантов записи данного оператора.
Предикат ALL задает включение в выходной набор всех дубликатов, отобранных по критерию WHERE, это значение действует по умолчанию.
Предикат DISTINCT указывает, что требуется отбросить блоки данных, содержащие дублирующие записи в выбранных полях. Значения для каждого из приведенных в инструкции SELECT полей должны быть уникальными, чтобы содержащая их запись смогла войти в выходной набор.
SELECT name, count(*) AS cnt
FROM notes
WHERE name LIKE '%_end'
GROUP BY name
HAVING count(*) > 10
ORDER BY count(*) DESCWHEREС помощью WHERE-параметра определяется, какие блоки данных из приведенных в списке FROM таблиц появятся в результате запроса. За ключевым словом WHERE следует перечень условий поиска, определяющих те строки, которые должны быть выбраны при выполнении запроса. Существует пять основных типов условий поиска (или предикатов).
Сравнение: сравниваются результаты вычисления одного выражения с результатами вычисления другого. Можно использовать следующие операторы сравнения:
= - равенство;< - меньше;> - больше;<= - меньше или равно;>= - больше или равно;<> - не равно.Более сложные предикаты строятся с помощью логических операторов AND, OR или NOT, а также скобок, используемых для определения порядка вычисления выражения. Вычисление выражения в условиях выполняется по следующим правилам:
NOT выполняются до выполнения операторов AND и OR.AND выполняются до выполнения операторов OR.Диапазон - оператор BETWEEN используется для поиска значения внутри некоторого интервала, определяемого своими минимальным и максимальным значениями. При этом указанные значения включаются в условие поиска.
<имя_поля> BETWEEN <мин_значение> AND <макс_значение>При использовании отрицания NOT BETWEEN требуется, чтобы проверяемое значение лежало вне границ заданного диапазона.
Принадлежность множеству - оператор IN используется для сравнения некоторого значения со списком заданных значений, при этом проверяется, соответствует ли результат вычисления выражения одному из значений в предоставленном списке. Оператор IN выполняется быстрее оператора OR.
<имя_поля> IN (<значение1> [,...n])(<имя_поля1> [,...n]) IN ( (<значение1> [,...n]) [,...m])NOT IN используется для отбора любых значений, кроме тех, которые указаны в представленном списке.
Соответствие шаблону - с помощью оператора LIKE выполняется сравнение выражения с заданным шаблоном, в котором допускается использование символов-заменителей:
% - вместо этого символа может быть подставлено любое количество произвольных символов;_ заменяет один символ строки;[] - вместо символа строки будет подставлен один из возможных символов, указанный в этих ограничителях: можно указывать диапазон через - или перечисление через ,;[^] - вместо соответствующего символа строки будут подставлены все символы, кроме указанных в ограничителях.<имя_поля> LIKE '<шаблон>'Значение NULL - оператор IS NULL используется для сравнения текущего значения со значением NULL - специальным значением, указывающим на отсутствие любого значения.
<имя_поля> IS NULLIS NOT NULL используется для проверки присутствия значения в поле.
Позволяет сравнивать блоки данных с использованием AND между ними.
(<имя_поля> [,...n]) = (<значение> [,...n])Сортирует данные выходного набора в заданной последовательности. Сортировка может выполняться по нескольким полям, в этом случае они перечисляются за ключевым словом ORDER BY через запятую. Способ сортировки задается ключевым словом, указываемым в рамках параметра ORDER BY следом за названием поля, по которому выполняется сортировка. По умолчанию реализуется сортировка по возрастанию. Явно она задается ключевым словом ASC. Для выполнения сортировки в обратной последовательности необходимо после имени поля, по которому она выполняется, указать ключевое слово DESC.
ORDER BY <имя_столбца> [ASC | DESC] [,...n]Операция выборки - построение горизонтального подмножества, т.е. подмножества кортежей, обладающих заданными свойствами. Операция выборки работает с одним отношением R и определяет результирующее отношение, которое содержит только те кортежи (строки) отношения R, которые удовлетворяют заданному условию F (предикату).
Операция проекции - построение вертикального подмножества отношения, т.е. подмножества кортежей, получаемого выбором одних и исключением других атрибутов. Операция проекции работает с одним отношением R и определяет новое отношение, которое содержит вертикальное подмножество отношения R, создаваемое посредством извлечения значений указанных атрибутов и исключения из результата строк-дубликатов.
Декартово произведение RxS двух отношений (двух таблиц) определяет новое отношение - результат конкатенации (т.е. сцепления) каждого кортежа (каждой записи) из отношения R с каждым кортежем (каждой записью) из отношения S.
Операция соединения - является производной от операции декартова произведения. Соединение - это процесс, когда две или более таблицы объединяются в одну. В языке SQL для задания типа соединения таблиц в логический набор записей, из которого будет выбираться необходимая информация, используется операция JOIN в предложении FROM. Формат операции:
<операция_соединения> ::=
<имя_таблицы_1> [[AS] <псевдоним>] {INNER | LEFT | RIGHT}
JOIN {<имя_таблицы_2> | <операция_соединения>} [[AS] <псевдоним>]
ON <условие_соединения>В общем виде:
FROM {<имя_таблицы> [[AS] <псевдоним>][,...n] | <операция_соединения> [,...n]}Можно создать вложенные объединения, добавив таблицу к результату объединения других таблиц. Существуют различные типы операций соединения:
Операция тета-соединения в языке SQL называется INNER JOIN (внутреннее соединение) и используется, когда нужно включить все строки из обеих таблиц, удовлетворяющие условию объединения.
естественным соединением называется соединение по эквивалентности двух отношений R и S, выполненное по всем общим атрибутам, из результатов которого исключается по одному экземпляру каждого общего атрибута;
внешнее соединение похоже на внутреннее, но в результирующий набор данных включаются также записи ведущей таблицы соединения, которые объединяются с пустым множеством записей другой таблицы. Какая из таблиц будет ведущей, определяет вид соединения:
LEFT - левое внешнее соединение, ведущей является таблица, расположенная слева от вида соединения - при котором кортежи отношения R, не имеющие совпадающих значений в общих столбцах отношения S, также включаются в результирующее отношение;RIGHT - правое внешнее соединение, ведущая таблица расположена справа от вида соединения - в результирующем отношении содержатся все кортежи правого отношения;NULL.операция полусоединения определяет отношение, содержащее те кортежи отношения R, которые входят в соединение отношений R и S;
операция объединение (UNION) отношений R и S можно получить в результате их конкатенации с образованием одного отношения с исключением кортежей-дубликатов. При этом отношения R и S должны быть совместимы, т.е. иметь одинаковое количество полей с совпадающими типами данных. Иначе говоря, отношения должны быть совместимы по объединению. Объединением двух таблиц R и S является таблица, содержащая все строки, которые имеются в первой таблице R, во второй таблице S или в обеих таблицах сразу;
<SELECT_1>
UNION
<SELECT_2>операция пересечения (INTERSECT) определяет отношение, которое содержит кортежи, присутствующие как в отношении R, так и в отношении S. Отношения R и S должны быть совместимы по объединению. Пересечением двух таблиц R и S является таблица, содержащая все строки, присутствующие в обеих исходных таблицах одновременно;
операция разность (EXCEPT) R-S двух отношений R и S состоит из кортежей, которые имеются в отношении R, но отсутствуют в отношении S. Причем отношения R и S должны быть совместимы по объединению. Разностью двух таблиц R и S является таблица, содержащая все строки, которые присутствуют в таблице R, но отсутствуют в таблице S;
операции деления R:S - набор кортежей отношения R, определенных на множестве атрибутов C, которые соответствуют комбинации всех кортежей отношения S.
Для создания вычисляемого (производного) поля в списке SELECT следует указать некоторое выражение языка SQL. В этих выражениях применяются арифметические операции сложения, вычитания, умножения и деления, а также встроенные функции языка SQL. Можно указать имя любого столбца (поля) таблицы или запроса, но использовать имя столбца только той таблицы или запроса, которые указаны в списке предложения FROM соответствующей инструкции. При построении сложных выражений могут понадобиться скобки.
С помощью итоговых (агрегатных) функций в рамках SQL-запроса можно получить ряд обобщающих статистических сведений о множестве отобранных значений выходного набора.
Доступны следующие основные итоговые функции:
Count(<выражение>) - определяет количество записей в выходном наборе SQL-запроса. При этом COUNT(*) - подсчитывает все строки в результирующей таблице, независимо от того, содержатся там пустые, дублирующиеся или любые другие значения;Min/Max(<выражение>) - определяют наименьшее и наибольшее из множества значений в некотором поле запроса;Avg(<выражение>) - эта функция позволяет рассчитать среднее значение множества значений, хранящихся в определенном поле отобранных запросом записей. Оно является арифметическим средним значением, т.е. суммой значений, деленной на их количество;Sum(<выражение>) - вычисляет сумму множества значений, содержащихся в определенном поле отобранных запросом записей.Если до применения обобщающей функции необходимо исключить дублирующиеся значения, следует перед именем столбца в определении функции поместить ключевое слово DISTINCT. При этом DISTINCT может быть указан в любом запросе не более одного раза.
Итоговые функции могут использоваться только в списке предложения SELECT и в составе предложения HAVING. Если список в предложении SELECT содержит итоговые функции, а в тексте запроса отсутствует фраза GROUP BY, обеспечивающая объединение данных в группы, то ни один из элементов списка предложения SELECT не может включать каких-либо ссылок на поля, за исключением ситуации, когда поля выступают в качестве аргументов итоговых функций.
Запрос, в котором присутствует GROUP BY, называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT, после чего для каждой отдельной группы создается единственная суммарная строка. Стандарт SQL требует, чтобы предложение SELECT и фраза GROUP BY были тесно связаны между собой. При наличии в операторе SELECT фразы GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы. Более того, предложение SELECT может включать только следующие типы элементов: имена полей, итоговые функции, константы и выражения, включающие комбинации перечисленных выше элементов.
Все имена полей, приведенные в списке предложения SELECT, должны присутствовать и во фразе GROUP BY - за исключением случаев, когда имя столбца используется в итоговой функции.
GROUP BY {<имя_поля> | <номер_поля>} [,...n]Стандартом SQL определено, что при проведении группирования все отсутствующие значения, т. е. NULL, рассматриваются как равные.
При помощи HAVING отражаются все предварительно сгруппированные посредством GROUP BY блоки данных, удовлетворяющие заданным в HAVING условиям. Это дополнительная возможность "профильтровать" выходной набор. В условии поиска HAVING можно задавать агрегатные функции.
HAVING {<имя_поля> | <номер_поля>} [,...n]Внутренний подзапрос представляет собой также оператор SELECT, а кодирование его предложений подчиняется тем же правилам, что и основного оператора SELECT. Внешний оператор SELECT использует результат выполнения внутреннего оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут быть помещены непосредственно после оператора сравнения (=, <, >, <=, >=, <>) в предложения WHERE и HAVING внешнего оператора SELECT - они получают название подзапросов или вложенных запросов. Кроме того, внутренние операторы SELECT могут применяться в операторах INSERT, UPDATE и DELETE.
Подзапрос - это инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Текст подзапроса должен быть заключен в скобки. К подзапросам применяются следующие правила и ограничения:
ORDER BY не используется, хотя и может присутствовать во внешнем подзапросе;SELECT состоит из имен отдельных столбцов или составленных из них выражений - за исключением случая, когда в подзапросе присутствует ключевое слово EXISTS;FROM. Однако допускается ссылка и на столбцы таблицы, указанной во фразе FROM внешнего запроса, для чего применяются квалифицированные имена столбцов (т.е. с указанием таблицы);Существует два типа подзапросов:
Вложенные подзапросы генерируют непоименованное промежуточное отношение, временную таблицу. Оно может использоваться только в том месте, где появляется в подзапросе. К такому отношению невозможно обратиться по имени из какого-либо другого места запроса. Применяемые к подзапросу операции основаны на тех операциях, которые, в свою очередь, применяются к множеству, а именно:
{WHERE | HAVING} <выражение> [NOT] IN (<подзапрос>) - проверяется, входит ли значение в предоставленный список или сравниваемое значение не является элементом представленного списка.
{WHERE | HAVING} <выражение> <оператор_сравнения> {ALL | SOME | ANY} (<подзапрос>) - ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими один столбец чисел. Если подзапросу будет предшествовать ключевое слово ALL, условие сравнения считается выполненным, только когда оно выполняется для всех значений в результирующем столбце подзапроса. Если записи подзапроса предшествует ключевое слово ANY, то условие сравнения считается выполненным, когда оно выполняется хотя бы для одного из значений в результирующем столбце подзапроса. Ключевое слово SOME является синонимом слова ANY.
{WHERE | HAVING} [NOT] EXISTS (<подзапрос>) - ключевые слова EXISTS и NOT EXISTS предназначены для использования только совместно с подзапросами. Результат их обработки представляет собой логическое значение TRUE или FALSE. Для ключевого слова EXISTS результат равен TRUE в том и только в том случае, если в возвращаемой подзапросом результирующей таблице присутствует хотя бы одна строка.
(VALUES (<значение_поля>[,...n]) [,...n]) AS <имя_блока>(<имя_поля>[,...n]) - возвращает блок данных (как запрос из таблицы)
Используется для создания временных выборок для последующих запросов, выборки могут использоваться внутри других выборок.
WITH
<ИМЯ_ВЫБОРКИ> AS (<запрос>) [, ...n]
SELECT ...Техническая цель введения аналитических функций - дать лаконичную формулировку и увеличить скорость выполнения "аналитических запросов" к БД, то есть запросов, имеющих смыслом выявление внутренних соотношений и зависимостей в данных. Более точно, пользование аналитическими функциями может дать следующие выгоды перед обычными SQL-операторами:
SELECT <аналитическая_функция> OVER([PARTITION BY <партицирование>...]
ORDER BY (<упорядочивание_выражение2> [, ...] [{ASC | DESC}] [{NULLS FIRST | NULLS LAST}])
{
{ROWS | RANGE} {{UNBOUNDED | <выражение>} PRECEDING | CURRENT ROW}
|
{ROWS | RANGE} BETWEEN
{UNBOUNDED PRECEDING | CURRENT ROW | {UNBOUNDED | <выражение_1>}{PRECEDING | FOLLOWING}}
AND
{UNBOUNDED FOLLOWING | CURRENT ROW | {UNBOUNDED | <выражение_2>}{PRECEDING | FOLLOWING}}
}Где <партицирование> - это некоторое количество записей с общими ключами текущей строки выборки на которую будет распространятся действие Аналитической функции, сортировка внутри <партицирование> осуществляется с помощью order by. Если PARTITION BY не указано, то в качестве единственной группы для вычислений будет взят полный набор строк.
Фразы PRECEDING и FOLLOWING задают верхнюю и нижнюю границы агрегирования (то есть интервал строк, "окно" для агрегирования).
Аналитические функции:
| Имя | Описание |
|---|---|
ROW_NUMBER() |
номер строки в группе |
LAG(<поле>, n, m) |
<поле> имя поля, n предыдущее значение в группе, m - знач по умолчанию |
LEAD(<поле>, n, m) |
<поле> имя поля, n последующее значение в группе, m - знач по умолчанию |
FIRST_VALUE(<поле>) |
<поле> имя поля, первое значение в группе |
LAST_VALUE(<поле>) |
<поле> имя поля, последнее значение в группе |
STD_DEV(<поле>) |
<поле> имя поля, значение стандартного распределения в группе |
SUM(<поле>) |
<поле> имя поля, накопительная сумма по группе |
AVG(<поле>) |
<поле> имя поля, среднее по группе заданной групп |
RANK(<поле>) |
<поле> имя поля, относительный ранг записи в группе |
Функции агрегирования работают по такой логике: если же ORDER BY задан, то подсчет в каждой строке идет от начала партиции до этой строки.
Результат работы оконной функции невозможно отфильтровать в запросе с помощью WHERE, потому что оконные функции выполняются после всей фильтрации и группировки, т.е. с тем, что получилось.
Аналитический SQL используется внутри SELECT в выборке столбцов:
SELECT <поле1>, <аналитический_запрос1> AS <алиас> FROM <таблица>
Оператор INSERT применяется для добавления записей в таблицу. Формат оператора:
<оператор_вставки> ::=
INSERT INTO <имя_таблицы>
[(<имя_столбца> [,...n])]
{ VALUES (<значение> [,...n]) [,...n]
| <SELECT_оператор>}INSERT INTO notes (id, name, category)
VALUES (10, 'the_end', 'music')Предназначена для вставки единственной строки в указанную таблицу. Список столбцов указывает столбцы, которым будут присвоены значения в добавляемых записях. Список может быть опущен, тогда подразумеваются все столбцы таблицы (кроме объявленных как счетчик), причем в определенном порядке, установленном при создании таблицы. Если в операторе INSERT указывается конкретный список имен полей, то любые пропущенные в нем столбцы должны быть объявлены при создании таблицы как допускающие значение NULL, за исключением тех случаев, когда при описании столбца использовался параметр DEFAULT. Список значений должен следующим образом соответствовать списку столбцов:
SELECTINSERT INTO <имя_таблицы> SELECT <имя столбца> [,...n] FROM <имя_др_таблицы> - позволяет скопировать множество строк из одной таблицы в другую. Предложение SELECT может представлять собой любой допустимый оператор SELECT. Вставляемые в указанную таблицу строки в точности должны соответствовать строкам результирующей таблицы, созданной при выполнении вложенного запроса. Все ограничения, указанные выше для первой формы оператора INSERT, применимы и в этом случае.
Поскольку оператор SELECT в общем случае возвращает множество записей, то оператор INSERT в такой форме приводит к добавлению в таблицу аналогичного числа новых записей.
INSERT [INTO] <имя_таблицы> SET {<имя_столбца> = {<выражение> | DEFAULT}} [,...n] - позволяет вставлить новую строку в таблицу с использованием синтаксиса UPDATE
Оператор DELETE предназначен для удаления группы записей из таблицы. Формат оператора:
<оператор_удаления> ::=
DELETE FROM <имя_таблицы>
[WHERE <условие_отбора>]DELETE FROM notes
WHERE id = 10Если предложение WHERE присутствует, удаляются записи из таблицы, удовлетворяющие условию отбора. Если опустить предложение WHERE, из таблицы будут удалены все записи, однако сама таблица сохранится.
Эта операция удаляет и воссоздает таблицу, что намного быстрее, чем поочередное удаление строк.
<оператор_очистки> ::=
TRUNCATE TABLE <имя_таблицы>Оператор UPDATE применяется для изменения значений в группе записей или в одной записи указанной таблицы. Формат оператора:
<оператор_изменения> ::=
UPDATE <имя_таблицы> SET <имя_столбца> = <выражение> [,...n]
[WHERE <условие_отбора>]В предложении SET указываются имена одного и более столбцов, данные в которых необходимо изменить. Если доступ к столбцу из указанного выражения осуществляется по аргументу <имя_столбца>, то команда UPDATE использует для этого столбца его текущее значение. Если столбец устанавливается в его текущее значение, то БД замечает это и не обновляет его.
Предложение WHERE является необязательным. Если оно опущено, значения указанных столбцов будут изменены во всех строках таблицы. Если предложение WHERE присутствует, то обновлены будут только те строки, которые удовлетворяют условию отбора. Выражение представляет собой новое значение соответствующего столбца и должно быть совместимо с ним по типу данных.
Используется для вставки новой записи или обновления тех записей у которых совпадают первичный ключ или уникальное поле при вставке.
INSERT INTO <имя_таблицы>
[(<имя_столбца> [,...n])]
{ VALUES (<значение> [,...n]) [,...n]
| <SELECT_оператор>}
ON DUPLICATE KEY UPDATE <имя_столбца> = <выражение> [,...n]Позволяет дополнять и обновлять данные одной таблицы - данными другой таблицы. При слиянии таблиц проверяется условие, и если оно истинно, то выполняется UPDATE [и DELETE - оценивает уже обновленное состояние], а если нет - INSERT. Причем нельзя изменять поля таблицы в секции UPDATE, по которым идет связывание двух таблиц.
MERGE INTO <имя_таблицы> [<алиас_таблицы>]
USING <выборка> [<алиас_выборки>]
ON <условия>
WHEN MATCHED THEN
UPDATE SET <имя_таблицы>.<колонка1> = <значение> [,...n] [WHERE <условия_включения_в_обновление>]
[DELETE WHERE <условия_удаления_из_таблицы_по_обновленным_строкам>]
WHEN NOT MATCHED THEN
INSERT (<колонка1> [,...n]) VALUES (<значение> [,...n])
[WHERE <условия_включения_в_вставку>]Выполнение операторов модификации данных в таблицах базы данных INSERT, DELETE и UPDATE может привести к нарушению целостности данных и их корректности, т.е. к потере их достоверности и непротиворечивости.
Чтобы информация, хранящаяся в базе данных, была однозначной и непротиворечивой, в реляционной модели устанавливаются некоторые ограничительные условия - правила, определяющие возможные значения данных и обеспечивающие логическую основу для поддержания корректных значений:
NULL;Между двумя или более таблицами базы данных могут существовать отношения подчиненности, которые определяют, что для каждой записи главной таблицы (родительской) может существовать одна или несколько записей в подчиненной таблице (дочерней). Существует три разновидности связи между таблицами базы данных:
Связь между таблицами устанавливается по первичному ключу, т.е. значениям внешнего ключа одной таблицы присваиваются значения первичного ключа другой или может устанавливаться и с помощью вторичных ключей. При установлении связей между таблицами не требуется непременная уникальность ключа, обеспечивающего установление связи. Поля внешнего ключа не обязаны иметь те же имена, что и имена ключей, которым они соответствуют. Внешний ключ может ссылаться на свою собственную таблицу (рекурсивный ключ).
Ссылочная целостность определяет: если в таблице существует внешний ключ, то его значение должно либо соответствовать значению первичного ключа некоторой записи в базовой таблице, либо задаваться определителем NULL.
Ситуации связанные с организацией поддержки ссылочной целостности при выполнении операций модификации данных в базе:
вставка новой строки в дочернюю таблицу. Для обеспечения ссылочной целостности необходимо убедиться, что значение внешнего ключа новой строки дочерней таблицы равно пустому значению либо некоторому конкретному значению, присутствующему в поле первичного ключа одной из строк родительской таблицы;
удаление строки из дочерней таблицы - никаких нарушений ссылочной целостности не происходит. Обновление внешнего ключа в строке дочерней таблицы - этот случай подобен вставке новой строки в дочернюю таблицу;
вставка строки в родительскую таблицу - такая вставка не может вызвать нарушения ссылочной целостности. Добавленная строка просто становится родительским объектом, не имеющим дочерних объектов;
удаление строки из родительской таблицы - ссылочная целостность окажется нарушенной, если в дочерней таблице будут существовать строки, ссылающиеся на удаленную строку родительской таблицы. В этом случае используется одна из следующих стратегий:
NO ACTION - удаление строки из родительской таблицы запрещается, если в дочерней таблице существует хотя бы одна ссылающаяся на нее строка.CASCADE - при удалении строки из родительской таблицы автоматически удаляются все ссылающиеся на нее строки дочерней таблицы.SET NULL - при удалении строки из родительской таблицы во всех ссылающихся на нее строках дочернего отношения в поле внешнего ключа, соответствующего первичному ключу удаленной строки, записывается пустое значение.SET DEFAULT - при удалении строки из родительской таблицы в поле внешнего ключа всех ссылающихся на нее строк дочерней таблицы автоматически помещается значение, указанное для этого поля как значение по умолчанию.NO CHECK - при удалении строки из родительской таблицы никаких действий по сохранению ссылочной целостности данных не предпринимается.обновление первичного ключа в строке родительской таблицы - если значение первичного ключа некоторой строки родительской таблицы будет обновлено, нарушение ссылочной целостности случится при том условии, что в дочернем отношении существуют строки, ссылающиеся на исходное значение первичного ключа. Для сохранения ссылочной целостности может применяться любая из описанных выше стратегий. При использовании стратегии CASCADE обновление значения первичного ключа в строке родительской таблицы будет отображено в любой строке дочерней таблицы, ссылающейся на данную строку.
Существует и другой вид целостности - смысловая (семантическая) целостность базы данных - определяет, что данные в базе данных должны изменяться таким образом, чтобы не нарушалась сложившаяся между ними смысловая связь.
Представления, или просмотры (VIEW), представляют собой временные, производные (иначе - виртуальные) таблицы и являются объектами базы данных, информация в которых не хранится постоянно, как в базовых таблицах, а формируется динамически при обращении к ним. Представление - это предопределенный запрос, хранящийся в базе данных, который выглядит подобно обычной таблице и не требует для своего хранения дисковой памяти. Для хранения представления используется только оперативная память.
<определение_представления> ::=
{CREATE | ALTER} VIEW <имя_представления>
[(<имя_столбца> [,...n])]
[WITH ENCRYPTION]
AS <SELECT_оператор>
[WITH CHECK OPTION]По умолчанию имена столбцов в представлении соответствуют именам столбцов в исходных таблицах. Явное указание имени столбца требуется для вычисляемых столбцов или при объединении нескольких таблиц, имеющих столбцы с одинаковыми именами. Имена столбцов перечисляются через запятую, в соответствии с порядком их следования в представлении.
Параметр WITH ENCRYPTION предписывает серверу шифровать SQL-код запроса, что гарантирует невозможность его несанкционированного просмотра и использования.
Параметр WITH CHECK OPTION предписывает серверу исполнять проверку изменений, производимых через представление, на соответствие критериям, определенным в операторе SELECT. Это означает, что не допускается выполнение изменений, которые приведут к исчезновению строки из представления.
Обращение к представлению осуществляется с помощью оператора SELECT как к обычной таблице.
Представление можно использовать в команде так же, как и любую другую таблицу. К представлению можно строить запрос, модифицировать его (если оно отвечает определенным требованиям), соединять с другими таблицами. Содержание представления не фиксировано и обновляется каждый раз, когда на него ссылаются в команде. Представления значительно расширяют возможности управления данными, что позволяет разрешить доступ к информации в таблице, скрыв часть данных.
Представление может изменяться командами модификации DML, но фактически модификация воздействует не на само представление, а на базовую таблицу.
Представление удаляется командой:
DROP VIEW <имя_представления> [,...n]Модифицируемое представление определяется следующими критериями:
DISTINCT в своем определении (в SELECT);GROUP BY или HAVING в своем определении;NOT NULL;SELECT просмотра не использует агрегирующие (итоговые) функции, соединения таблиц, хранимые процедуры и функции, определенные пользователем;UNION не разрешено.Если просмотр удовлетворяет этим условиям, к нему могут применяться операторы INSERT, UPDATE, DELETE.
Вызываются как: <имя_функции> ([<аргумент1> [,...n]])
| Функция | Описание |
|---|---|
ABS |
вычисляет абсолютное значение числа |
ACOS |
вычисляет арккосинус |
ASIN |
вычисляет арксинус |
ATAN |
вычисляет арктангенс |
ATN2 |
вычисляет арктангенс с учетом квадратов |
CEILING |
выполняет округление вверх |
COS |
вычисляет косинус угла |
COT |
возвращает котангенс угла |
DEGREES |
преобразует значение угла из радиан в градусы |
EXP |
возвращает экспоненту |
FLOOR |
выполняет округление вниз |
LOG |
вычисляет натуральный логарифм |
LOG10 |
вычисляет десятичный логарифм |
PI |
возвращает значение "пи" |
POWER |
возводит число в степень |
RADIANS |
преобразует значение угла из градуса в радианы |
RAND |
возвращает случайное число |
ROUND |
выполняет округление с заданной точностью |
SIGN |
определяет знак числа |
SIN |
вычисляет синус угла |
SQUARE |
выполняет возведение числа в квадрат |
SQRT |
извлекает квадратный корень |
TAN |
возвращает тангенс угла |
| Функция | Описание |
|---|---|
ASCII |
возвращает код ASCII левого символа строки |
CHAR |
по коду ASCII возвращает символ |
CHARINDEX |
определяет порядковый номер символа, с которого начинается вхождение подстроки в строку |
DIFFERENCE |
возвращает показатель совпадения строк |
LEFT |
возвращает указанное число символов с начала строки |
LEN |
возвращает длину строки |
LOWER |
переводит все символы строки в нижний регистр |
LTRIM |
удаляет пробелы в начале строки |
NCHAR |
возвращает по коду символ Unicode |
PATINDEX |
выполняет поиск подстроки в строке по указанному шаблону |
REPLACE |
заменяет вхождения подстроки на указанное значение |
QUOTENAME |
конвертирует строку в формат Unicode |
REPLICATE |
выполняет тиражирование строки определенное число раз |
REVERSE |
возвращает строку, символы которой записаны в обратном порядке |
RIGHT |
возвращает указанное число символов с конца строки |
RTRIM |
удаляет пробелы в конце строки |
SOUNDEX |
возвращает код звучания строки |
SPACE |
возвращает указанное число пробелов |
STR |
выполняет конвертирование значения числового типа в символьный формат |
STUFF |
удаляет указанное число символов, заменяя новой подстрокой |
SUBSTRING |
возвращает для строки подстроку указанной длины с заданного символа |
UNICODE |
возвращает Unicode-код левого символа строки |
UPPER |
переводит все символы строки в верхний регистр |
<строка1>||<строка2>[||...] - (oracle) конкатинирует строки (соединяет).
TO_CHAR(<число>) - (oracle) переводит число в строковой формат.
| Функция | Описание |
|---|---|
DATEADD |
добавляет к дате указанное значение дней, месяцев, часов и т.д. |
DATEDIFF |
возвращает разницу между указанными частями двух дат |
DATENAME |
выделяет из даты указанную часть и возвращает ее в символьном формате |
DATEPART |
выделяет из даты указанную часть и возвращает ее в числовом формате |
DAY |
возвращает число из указанной даты |
GETDATE |
возвращает текущее системное время |
ISDATE |
проверяет правильность выражения на соответствие одному из возможных форматов ввода даты |
MONTH |
возвращает значение месяца из указанной даты |
YEAR |
возвращает значение года из указанной даты |
Хранимые процедуры представляют собой группы связанных между собой операторов SQL. Хранение процедур в том же месте, где они исполняются, обеспечивает уменьшение объема передаваемых по сети данных и повышает общую производительность системы. Применение хранимых процедур упрощает сопровождение программных комплексов и внесение изменений в них.
Хранимые процедуры существуют независимо от таблиц или каких-либо других объектов баз данных. Они вызываются клиентской программой, другой хранимой процедурой или триггером. Разработчик может управлять правами доступа к хранимой процедуре, разрешая или запрещая ее выполнение. Изменять код хранимой процедуры разрешается только ее владельцу или члену фиксированной роли базы данных. При необходимости можно передать права владения ею от одного пользователя к другому.
Триггер - это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных.
Под транзакцией понимается неделимая с точки зрения воздействия на БД последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации), приводящая к одному из двух возможных результатов: либо последовательность выполняется, если все операторы правильные, либо вся транзакция откатывается, если хотя бы один оператор не может быть успешно выполнен. Обработка транзакций гарантирует целостность информации в базе данных. Таким образом, транзакция переводит базу данных из одного целостного состояния в другое.
Характеристики транзакций описываются в терминах ACID (Atomicity, Consistency, Isolation, Durability - неделимость, согласованность, изолированность, устойчивость):
Указанные выше правила выполняет сервер. Программист лишь выбирает нужный уровень изоляции, заботится о соблюдении логической целостности данных и бизнес-правил.
Блокировкой называется временное ограничение на выполнение некоторых операций обработки данных. Блокировка может быть наложена как на отдельную строку таблицы, так и на всю базу данных.
Управлением блокировками на сервере занимается менеджер блокировок, контролирующий их применение и разрешение конфликтов. Транзакции и блокировки тесно связаны друг с другом. Транзакции накладывают блокировки на данные, чтобы обеспечить выполнение требований ACID. Без использования блокировок несколько транзакций могли бы изменять одни и те же данные.
Блокировка представляет собой метод управления параллельными процессами, при котором объект БД не может быть модифицирован без ведома транзакции, т.е. происходит блокирование доступа к объекту со стороны других транзакций, чем исключается непредсказуемое изменение объекта. Различают два вида блокировки:
В СУБД используют протокол доступа к данным, позволяющий избежать проблемы параллелизма. Его суть заключается в следующем:
Решение проблемы параллельной обработки БД заключается в том, что строки таблиц блокируются, а последующие транзакции, модифицирующие эти строки, отвергаются и переводятся в режим ожидания.
Для этого вводят уровни изоляции транзакции, которые определяют, могут ли другие (конкурирующие) транзакции вносить изменения в данные, измененные текущей транзакцией, а также может ли текущая транзакция видеть изменения, произведенные конкурирующими транзакциями, и наоборот. Каждый последующий уровень поддерживает требования предыдущего и налагает дополнительные ограничения:
"Мертвые", или тупиковые, блокировки характерны для многопользовательских систем. "Мертвая" блокировка возникает, когда две транзакции блокируют два блока данных и для завершения любой из них нужен доступ к данным, заблокированным ранее другой транзакцией. Для завершения каждой транзакции необходимо дождаться, пока блокированная другой транзакцией часть данных будет разблокирована. Но это невозможно, так как вторая транзакция ожидает разблокирования ресурсов, используемых первой.
Под управлением транзакциями понимается способность управлять различными операциями над данными, которые выполняются внутри реляционной СУБД. Прежде всего, имеется в виду выполнение операторов INSERT, UPDATE и DELETE.
После успешного выполнения команд, заключенных в тело одной транзакции, немедленного изменения данных не происходит. Для окончательного завершения транзакции существуют так называемые команды управления транзакциями, с помощью которых можно либо сохранить в базе данных все изменения, произошедшие в ходе ее выполнения, либо полностью их отменить.
После завершения транзакции вся информация о произведенных изменениях хранится либо в специально выделенной оперативной памяти, либо во временной области отката в самой базе данных до тех пор, пока не будет выполнена одна из команд управления транзакциями. Затем все изменения или фиксируются в базе данных, или отбрасываются, а временная область отката освобождается.
Существуют три команды, которые используются для управления транзакциями:
команда COMMIT предназначена для сохранения в базе данных всех изменений, произошедших в ходе выполнения транзакции. Она сохраняет результаты всех операций, которые имели место после выполнения последней команды COMMIT или ROLLBACK;
команда ROLLBACK предназначена для отмены транзакций, еще не сохраненных в базе данных. Она отменяет только те транзакции, которые были выполнены с момента выдачи последней команды COMMIT или ROLLBACK;
команда SAVEPOINT (точка сохранения) предназначена для установки в транзакции особых точек, куда в дальнейшем может быть произведен откат (при этом отката всей транзакции не происходит). Команда имеет следующий вид:
SAVEPOINT <имя_точки_сохранения>
Она служит исключительно для создания точек сохранения среди операторов, предназначенных для изменения данных. Имя точки сохранения в связанной с ней группе транзакций должно быть уникальным.
Для отмены действия группы транзакций, ограниченных точками сохранения, используется команда ROLLBACK со следующим синтаксисом:
ROLLBACK TO <имя_точки_сохранения>
Поскольку с помощью команды SAVEPOINT крупное число транзакций может быть разбито на меньшие и поэтому более управляемые группы, ее применение является одним из способов управления транзакциями.
Идентификатором пользователя называется обычный идентификатор языка SQL, применяемый для обозначения некоторого пользователя базы данных. Каждому пользователю должен быть назначен собственный идентификатор, присваиваемый администратором базы данных. Каждый выполняемый СУБД SQL-оператор выполняется от имени какого-либо пользователя. Идентификатор пользователя определяет, на какие объекты базы данных пользователь может ссылаться и какие операции с этими объектами он имеет право выполнять.
Каждый созданный в среде SQL объект имеет своего владельца, который изначально является единственной персоной, знающей о существовании данного объекта и имеет право выполнять с ним любые операции.
Привилегиями, или правами, называются действия, которые пользователь имеет право выполнять в отношении данной таблицы базы данных или представления:
SELECT - право выбирать данные из таблицы;INSERT - право вставлять в таблицу новые строки;UPDATE - право изменять данные в таблице;DELETE - право удалять строки из таблицы;REFERENCES - право ссылаться на столбцы указанной таблицы в описаниях требований поддержки целостности данных;USAGE - право использовать домены, проверки и наборы символов.Привилегии INSERT и UPDATE могут ограничиваться лишь отдельными столбцами таблицы, в этом случае пользователю разрешается модифицировать значения только указанных столбцов. Аналогичным образом привилегия REFERENCES может распространяться исключительно на отдельные столбцы таблицы, что позволит использовать их имена в формулировках требований защиты целостности данных.
Оператор GRANT применяется для предоставления привилегий в отношении поименованных объектов базы данных указанным пользователям. Обычно его использует владелец таблицы с целью предоставления доступа к ней другим пользователям. Оператор GRANT имеет следующий формат:
<предоставление_привилегий> ::=
GRANT {<привилегия> [,...n] | ALL PRIVILEGES}
ON <имя_объекта> TO {<идентификатор_пользователя> [,...n] | PUBLIC}
[WITH GRANT OPTION]Параметр <привилегия> представляет собой:
<привилегия> ::=
{ SELECT
| DELETE
| INSERT [(<имя_столбца> [,...n])]
| UPDATE [(<имя_столбца> [,...n])]}
| REFERENCES [(<имя_столбца> [,...n])]
| USAGE}Из соображений упрощения в операторе GRANT можно указать ключевое слово ALL PRIVILEGES, что позволит предоставить указанному пользователю все существующие привилегии без необходимости их перечисления. Ключевое слово PUBLIC, означающее предоставление доступа указанного типа не только всем существующим пользователям, но также и всем тем, кто будет определен в базе данных впоследствии.
Параметр <имя_объекта> может использоваться как имя таблицы базы данных, представления, домена, набора символов, проверки.
Благодаря параметру WITH GRANT OPTION, указанные в операторе GRANT пользователи имеют право передавать все предоставленные им в отношении указанного объекта привилегии другим пользователям, которые, в свою очередь, будут наделены точно таким же правом передачи своих полномочий. Если данный параметр не будет указан, получатель привилегии не сможет передать свои права другим пользователям. Таким образом, владелец объекта может четко контролировать, кто получил право доступа к объекту и какие полномочия ему предоставлены.
Для отмены привилегий используется оператор REVOKE. С помощью этого оператора могут быть отменены все или некоторые из привилегий, полученных указанным пользователем раньше. Оператор REVOKE имеет следующий формат:
<отмена_привилегий> ::=
REVOKE [GRANT OPTION FOR] {<привилегия> [,...n] | ALL PRIVILEGES}
ON <имя_объекта> FROM {<идентификатор_пользователя> [,...n] | PUBLIC}
[RESTRICT | CASCADE]Ключевое слово ALL PRIVILEGES означает, что для указанного пользователя отменяются все привилегии, предоставленные ему ранее тем пользователем, который ввел данный оператор. Необязательная фраза GRANT OPTION FOR позволяет для всех привилегий, переданных в исходном операторе GRANT фразой WITH GRANT OPTION, отменять возможность их передачи независимо от самих привилегий.
Если в операторе указано ключевое слово RESTRICT, успешное выполнение команды REVOKE возможно лишь в том случае, когда перечисленные в операторе привилегии не могут послужить причиной появления у каких-либо других пользователей так называемых "оставленных" привилегий. С помощью параметра CASCADE удаляются все привилегии, которые иначе могли бы остаться у других пользователей.
"Оставленными" являются привилегии, сохранившиеся у пользователя, которому они в свое время были предоставлены с помощью параметра GRANT OPTION.
Поскольку наличие привилегии необходимо для создания определенных объектов, вместе с ее удалением можно лишиться права, за счет использования которого был образован тот или иной объект (подобные объекты называются "брошенными"). Если в результате выполнения оператора REVOKE могут появиться брошенные объекты (например, представления), право будет отменено при условии, что в нем не указывается ключевое слово CASCADE. Если ключевое слово CASCADE в операторе присутствует, то для любых брошенных объектов, возникающих при выполнении исходного оператора REVOKE, будут автоматически выданы операторы DROP.