Арифметические функции
SQL поддерживает полный набор арифметических операций и математических функций для построения арифметических выражений над колонками базы данных (+, -, *, /, ABS, LN, SQRT и т.д.). Список основных встроенных математических функций дан ниже в таблице 8.2.
| ABS(X) | Возвращает абсолютное значение числа Х |
| ACOS(X) | Возвращает арккосинус числа Х |
| ASIN(X) | Возвращает арксинус числа Х |
| ATAN(X) | Возвращает арктангенс числа Х |
| COS(X) | Возвращает косинус числа Х |
| EXP(X) | Возвращает экспоненту числа Х |
| SIGN(X) | Возвращает -1, если Х<0,0, если Х=0, +1, если Х>0 |
| LN(X) | Возвращает натуральный логарифм числа Х |
| MOD(X,Y) | Возвращает остаток от деления Х на Y |
| CEIL(X) | Возвращает наименьшее целое, большее или равное Х |
| ROUND(X,n) | Округляет число Х до числа с n знаками после десятичной точки |
| SIN(X) | Возвращает синус числа Х |
| SQRT(X) | Возвращает квадратный корень числа Х |
| TAN(X) | Возвращает тангенс числа Х |
| FLOOR(X) | Возвращает наибольшее целоеб меньшее или равное Х |
| LOG(a,X) | Возвращает логарифм числа Х по основанию А |
| SINH(X) | Возвращает гиперболический синус числа Х |
| COSH(X) | Возвращает гиперболический косинус числа Х |
| TANH(X) | Возвращает гиперболический тангенс числа Х |
| TRANC(X,n) | Усекает число Х до числа с n знаками после десятичной точки |
| POWER(A,X) | Возвращает значение А, возведенное в степень Х |
Набор встроенных функций может изменяться в зависимости от версии СУБД одного производителя и также в СУБД различных производителей. Так, например, в СУБД SQLBase, Centure Inc. есть функция @ATAN2(X,Y), которая возвращает арктангенс Y/X, но отсутствует функция SIGN(X).
Арифметические выражения необходимы для получения данных, которые непосредственно не сохраняются в колонках таблиц базы данных, но значения которых необходимы пользователю. Допустим, что вам необходим список служащих, показывающий выплату, которую получил каждый служащий с учетом премий и штрафов.
SELECT ENAME, SAL, COMM, FINE, SAL + COMM - FINE FROM EMPLOYEE ORDER BY DEPNO;
Арифметическое выражение SAL + COMM - FINE выводится как новая колонка в результирующей таблице, которая вычисляется в результате выполнения запроса. Такие колонки называют еще производными (вычисляемыми) атрибутами или полями.
Домены и допустимые типы данных
В информационной модели, создаваемой на этапе анализа, среда реализации не учитывается. Аналитик просто определяет атрибуты как строку, число или дату, в идеале он также назначает атрибуту домен. В контексте аналитика домен - это просто тип атрибута, например деньги или рабочий день. Аналитик может включить ряд проверок допустимости или правил обработки, например требование, что значение должно быть положительным, ненулевым и иметь максимум два десятичных разряда (это полезно для сумм долларовых трат, выставляемых банком на другой банк). Использование доменов аналитиком упрощает задачу обеспечения непротиворечивости. При переходе к проектированию физической модели проектировщику необходимо знать возможности выбранной СУБД по назначению типов данных колонок. В логической модели данных значения, которые может принимать атрибут отношения, также задаются доменом, который наследуется из информационной модели. В физической модели базы данных требуется, чтобы каждый атрибут отношения в базе данных обладал рядом свойств, которые диктуют, что в нем может храниться и что не может. Этими свойствами являются тип, размер и ограничения, которые могут еще более ограничивать допустимый набор значений столбца. Задача состоит в преобразовании домена в подходящий тип данных, поддерживаемый СУБД. Таким образом, проектировщик базы данных должен знать, какими типами данных он располагает при решении вышеуказанной задачи.
В контексте проектирования физической модели реляционной базы данных домен - это выражение, определяющее разрешенные значения для колонок (атрибутов) отношения. При описании таблицы реляционной базы данных каждой колонке назначается определенный тип данных. Практически основу определения домена составляет тип данных, содержащихся в колонке, поскольку большинство встроенных типов задают разрешенный интервал значений данных.
Пример. Колонку в базе данных можно описать следующим образом:
amount NUMBER (8,2) NOT NULL CONSTRAINT cc_limit_amnt CHECK (amount > 0)
В этой колонке можно размещать только числовые данные; она должна быть заполнена для каждой таблицы; ее значение должно быть положительным; точность этого значения - два значащих десятичных разряда. Максимальное значение, которое может храниться в этом столбце, - 999999.99. В этом простом определении колонки мы фактически определили ряд неявных правил, проверку которых Oracle принудительно включает при вводе данных в базу данных.
Как видно, дальнейшее определение домена колонки (после присвоения ей типа) выполняется проектировщиком с помощью уточнений правил изменения значений. Такие уточнения поддерживаются в SQL с помощью механизма ограничений в спецификации колонки в таблице (см. далее). В этом разделе мы рассмотрим связь между понятием домена и допустимыми в СУБД типами данных.
В стандарт SQL-92 введено понятие доменов, определенных пользователем. Определение таких доменов базируется на встроенных типах данных СУБД.
Допустимые типы данных
Все допустимые типы данных описаны в стандарте SQL-92, но в большинстве диалектов поддерживается расширенный список типов данных. Однако любой диалект SQL поддерживают три общих типа данных: строковые, числовые и тип для представления даты и времени. Задание типа данных определяет значения и длину данных, а также формат их представления при визуализации.
Для всех типов данных определено так называемое нуль-значение, которое указывает на отсутствие данных в колонке указанного типа, т.е. то обстоятельство, что значение данных в текущий момент времени неизвестно.
Описание типов, данное в таблице ниже, относится к диалекту SQL для СУБД SQLBase, которое имеет существенные отличия от предписаний стандарта SQL. В комментарии уточняются сведения о типах данных, принятые в реализации СУБД Oracle. Жирным шрифтом выделена часть зарезервированного слова для определения типа, которую можно использовать как аббревиатуру при определении типа в спецификации колонки.
Данные строкового типа представляют собой последовательность строк символов. Строковые данные могут быть заданы как с предопределенной длиной (ключевые слова char или varchar (длина строки)), так и без указания длины (ключевое слово long varchar) для представления строк произвольной длины. Тип данных varchar2 определяет строку символов переменной длины, имеющую максимальный размер size. В отличие от строкового типа с предопределенной длиной, со строками long varchar не допускаются операции сравнения, и они не могут быть использованы в выражениях и как аргументы большинства встроенных функций. В Oracle этот тип не может быть использован в определении последовательности. Строки последнего типа могут применяться для сохранения битовых образов. Стандарт SQL-92 не имеет типа long varchar и varchar.
Обратим внимание на тип данных varchar2. Он, так же как и тип данных char, предназначен для представления алфавитно-цифровых данных. Но он имеет формат переменной длины. Последнее означает, что длина колонки такого типа равна числу символов в ней, в то время как колонка типа char использует все определенное для нее пространство.
Сравним две колонки с содержанием 'abc', но с типами varchar2(5) и char(5). Первая занимает действительно 3 байта, а вторая - 5 байт. Оставшиеся два байта заполняются символом "white space", который аналогичен пропуску, возникающему при нажатии на клавиатуре клавиши space bar. Несмотря на то, что колонки содержат одинаковые строки, они не равны, так как первая в 4-й и 5-й позициях содержат null-значение, а вторая в тех же позициях содержит white space. Это может привести к проблемам при соединении таблиц по таким колонкам. Обычно колонки типа varchar2 не планируются для использования в процедурах поиска данных в базе данных. В них хранят текст.
Существует два типа числовых данных.
Целые и вещественные значения (например, сальдо банковского счета или ставка процента). Они являются объектом математической обработки.Строковые числовые данные, в которых единственно допустимыми символами являются цифры (например, номера банковских счетов).
Числовые типы данных предназначены для представления целых чисел, чисел с десятичной точкой и чисел с плавающей точкой. Любое представление чисел задается своей точностью и масштабом. Точность определяет допустимое представлением количество значащих цифр числа, а масштаб - количество значащих цифр после десятичной точки.
Для представления целых чисел используются типы interger (точность 10 значащих цифр) и smallint (точность 5 значащих цифр).
Для представления чисел с фиксированной десятичной точкой используются типы number (точность, масштаб) (для чисел с точностью до 15 значащих цифр) и decimal (точность, масштаб) (для чисел заданной точности до 15 значащих цифр). Если указать для колонки тип number без задания масштаба, максимальное число значащих цифр для Oracle будет 105. Вместо задания точности и масштаба может быть указан символ *. Это будет эквивалентно заданию просто типа number. Различие между этими типами данных состоит в том, что для типа number нет необходимости следить за точностью при выполнении операций.
При выполнении операций с числами этих типов действуют следующие формулы для определения точности и масштаба результата (p - точность, s - масштаб):
сложение/вычитание точность=max{min[15, max(p1-s1, p2-s2)+max(s1, s2)+1]} масштаб=max[s1, s2] деление точность=15 масштаб=15-p1+s1-s2 умножение точность=min{15, p1+p2} масштаб=min{15, s1+s2}
Для представления чисел с плавающей точкой в SQL предусмотрены следующие типы данных:
Double Precision - для чисел с точностью от 22 до 53 значащих цифр;Float (точность) - для представления чисел с точностью от 1 до 21 значащей цифры;Real - для чисел с точностью по умолчанию (зависит от конкретной реализации).
Тип данных для представления даты и времени отсутствует в стандарте SQL. Обычно в конкретных диалектах SQL используются три типа для представления таких данных:
datestamp (timestamp) - для представления даты и времени;date - для представления даты;time - для представления времени.
В СУБД Oracle тип date принимает допустимые значения от 1 января 4712 ВС до 31 декабря 4712 АD. Формат по умолчанию - "ДД-МММ-ГГ".
В СУБД Oracle представлен набор типов данных для хранения объектов большого размера: Long Raw для хранения очень больших по размеру данных цифровой природы и raw для хранения битовых строк сравнительно небольшого размера.
В Oracle есть еще два типа данных для представления метки безопасности операционной системы (secure operating system label): msllabel в виде четырех последовательных байт и raw msllabel - в двоичном формате.
Правила преобразования типов представлены в таблице 8.9 ниже.
| Строковый | Числовой | Значение исходного типа должно быть в форме допустимой для числовых значений |
| Числовой | Строковой | Нет необходимости в одинарных кавычках |
| Дата/время | Числовой | |
| Числовой | Дата/время | |
| Дата/время | Строковой | Нет необходимости в одинарных кавычках |
| Строкой | Дата/время | Значение исходного типа должно быть в форме допустимой для значений даты и время |
Функции для обработки даты
В диалекте SQL СУБД Oracle имеется небольшой набор функций для манипулирования колонками с типом date. Список основных функций обработки даты и времени приведен в таблице 8.6.
| SYSDATE | Возвращает текущую дату и время |
| ROUND(D[,F]) | Округляет значение даты D согласно заданному шаблону |
| TRANC(D[,F]) | Усекает значение даты D согласно заданному шаблону |
| NEXT_DAY(D,S) | Возвращает дату дня, который является первым днем, более поздним, чем текущая дата с названием S |
Если вам потребовался список новых служащих, поступивших за последний квартал в организацию, то вы можете написать запрос в следующем виде:
SELECT ENAME, HIREDATE, HIREDATE + 92 DAYS FROM EMPLOYEE WHERE HIREDATE + 92 DAYS > SYSDATE AND DEPNO=30;
Ключевое слово SYSDATE всегда возвращает текущую дату. В этом примере также показано, как используется арифметический оператор сложения с переменными типа "дата". К переменной типа "дата" можно прибавлять и вычитать из него целое число дней, месяцев, лет, часов, минут, секунд, микросекунд. Для этого используются соответствующие ключевые слова (DAY, MONTH и т.д.), следующие за целой константой (дробная часть игнорируется, если вы указываете число с десятичной точкой). Имеется ограничение на использование скобок в таких выражениях (так, заключение в скобки выражения 1 DAYS + 1 YEARS приведет к ошибке).
Функции обработки строк
SQL предоставляет вам широкий набор функций для манипулирования со строковыми данными (конкатенация строк, CHR, LENGTH, INSTR и другие). Список основных функций для обработки строковых данных приведен в таблице 8.3.
| CHR(N) | Возвращает символ ASCII кода для десятичного кода N |
| ASCII(S) | Возвращает десятичный ASCII код первого символа строки |
| INSTR(S2.S1.pos[,N] | Возвращает позицию строки S1 в строке S2 большую или равную pos.N - число вхождений |
| LENGHT(S) | Возвращает длину строки |
| LOWER(S) | Заменяет все символы строки на прописные символы |
| INITCAP(S) | Устанавливает первый символ каждого слова в строке на заглавный, а остальные символы каждого слова - на прописные |
| SUBSTR(S,pos,[,len]) | Выделяетв строке S подстроку длиной len, начиная с позиции pos |
| UPPER(S) | Преобразует прописные букцвы в строке на заглавные буквы |
| LPAD(S,N[,A]) | Возвращает строку S, дополненную слева симолами A до числа символов N. Символ - наполнитель по умолчанию - пробел |
| Rpad(S,N[,A]) | Возвращает строку S, дополненную справа симолами A до числа символов N. Символ - наполнитель по умолчанию - пробел |
| LTRIM(S,[S1]) | Возвращает усеченную слева строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел) |
| RTRIM(S,[S1]) | Возвращает усеченную справа строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел |
| TRANSLATE(S,S1,S2) | Возвращает строку S, в которой все вхождения строки S1 замещены строкой S2. Если S1 <> S2, то символы, которым нет соответствия, исключаются из результирующей строки |
| REPLACE(S,S1,[,S2]) | Возвращает строку S, для которой все вхождения строки S1 замещены на подстроку S2. Если S2 не указано, то все вхождения подстроки S1 удаляются из результирующей строки |
| NVL(X,Y) | Если Х есть NULL, то возвращает в Y либо строку, либо число, либо дату в зависимости от исходного типа Y |
Названия одних и тех же функций могут отличаться в различных СУБД.
Так, например, функция СУБД Oracle SUBSTR(S, pos, [, len]) в СУБД SQLBase называется @SUBSTRING(S, pos, len). В СУБД SQLBase имеются функции, которых нет в СУБД Oracle (см. таблицу ниже, где приведен список таких функций).
| @EXACT(S1,S2) | Возвращает результат сравнения двух строк |
| @LEFT(S,len) | Возвращает левую подстроку длиной len |
| LENGTH(S) | Возвращает длину строки |
| @MID(S, pos, len) | Возвращает подстроку указанной длины, начиная с позиции pos |
| @REPEAT(S,n) | Повторяет строку S n раз |
| @REPLACE(S1,pos,len,S2) | Замещает с позиции pos len символов в строке S2 символами строки S1 |
| RIGHT(S,len) | Возвращает правую подстроку S длиной len |
| @SCAN(S,pat) | Возвращает позицию подстроки pat в строке S |
| @STRING(X,scale) | Возвращает символьное представление числа с указанным масштабом scale |
| @TRIM(S) | Удаляет пробелы в строке справа и слева |
| @VALUE(S) | Преобразует символьное представление числа в числовое значение |
SELECT INITCAP(ENAME) FROM EMPLOYEE ORDER BY DEPNO;
Иерархия объектов реляционной базы данных
Одной из главных задач, которые обязан решить проектировщик на стадии проектирования физической модели реляционной базы данных, является задача превращения объектов логической модели реляционной базы данных в объекты реляционной базы данных. Для решения этой задачи проектировщику базы данных необходимо знать: а) какими объектами располагает реляционная база данных в принципе; б) какие объекты поддерживает конкретная СУБД, которая выбрана для реализации базы данных.
Таким образом, мы предполагаем, что решение о выборе СУБД уже принято руководителем ИТ-проекта, и согласовано с заказчиком базы данных, т.е. СУБД задана. Проектировщик базы данных должен ознакомиться с документацией, в которой описан диалект SQL, поддерживаемый выбранной СУБД. В настоящей лекции предполагается, что была выбрана СУБД Oracle 9i, хотя подавляющая часть материала охватывает объекты в любой промышленной реляционной СУБД.
Замечание. О выборе СУБД. Выбор СУБД относится к многокритериальной задаче выбора и в настоящем курсе не рассматривается. Следует помнить о том, что СУБД обычно поддерживает только одну модель данных: реляционную, иерархическую, сетевую, многомерную, объектно-ориентированную, объектно-реляционную. Исключение составляют небольшое число СУБД. Например, ADABAS, Software AG (сетевая и реляционная модели), или Oracle 9i, Oracle Inc. (реляционная и объектно-реляционная модели). Обычно при выборе СУБД при всех прочих равных возможностях стараются создать базу данных на СУБД, претендующей на промышленный стандарт.
Иерархия объектов реляционной базы данных прописана в стандартах по SQL, в частности, в стандарте SQL-92, на который мы будем ориентироваться при изложении материала настоящей лекции. Этот стандарт поддерживается практически всеми современными СУБД, вплоть до настольных. Иерархия объектов реляционной базы данных показана на рисунке ниже.
На самом нижнем уровне находятся наименьшие объекты, с которыми работает реляционная база данных, - столбцы (колонки) и строки. Они, в свою очередь, группируются в таблицы и представления.
Замечание. В контексте лекции атрибуты, колонки, столбцы и поля считаются синонимами. То же относится и к терминам "строка", "запись" и "кортеж".
Таблицы и представления, которые представляют физическое отражение логической структуры базы данных, собираются в схему. Несколько схем собираются в каталоги, которые затем могут быть сгруппированы в кластеры. Следует отметить, что ни одна из групп объектов стандарта SQL-92 не связана со структурами физического хранения информации в памяти компьютеров.
Рис. 8.1. Иерархия объектов реляционной базы данных, соответствующая стандарту SQL-92
Помимо указанных на рисунке объектов, в реляционной базе данных могут быть созданы индексы, триггеры, события, хранимые команды, хранимые процедуры и ряд других. Теперь перейдем к определению объектов реляционной базы данных.
Использование агрегатных функций в запросах
Агрегатные функции в SQL позволяют выбирать обобщающую информацию из группы строк и проводить систематизацию данных. Список агрегатных функций приведен в таблице 8.7. Агрегатные функции почти во всех реализациях SQL носят одинаковые имена. Различие в наименование для Oracle дано через косую черту.
| AVG(X) = AVG(ALL X) AVG(DISTINCT X) | Вычисляет среднее значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты |
| COUNT(*) COUNT(X) = COUNT(ALL X) COUNT(DISTINCT X) | Вычисляет числа итемов. При указании * всегда возвращается число строк в таблице. Указание DISTINCT подавляет дуюликаты |
| MAX(X) = MAX(ALL X) MAX (DISTINCT X) | Вычисляет максимальное значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты |
| MIN(X) = MIN(ALL X) MIN (DISTINCT X) | Вычисляет минимальное значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты |
| SUM(X) = SUM(ALL X) SUM (DISTINCT X) | Вычисляет сумму значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты |
| STDDEV([DISTINCT|ALL]X) | Вычисляет стандартное отклонение на множестве значений аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты |
| VARIANCE([DISTINCT|ALL]) | Вычисляет квадрат дисперсии |
Использование функций агрегирования позволяет вам находить суммарные значения колонок и разброс данных в колонке. Так, после выполнения запроса
SELECT SUM(SAL) FROM EMPLOYEE;
вы узнаете итоговую сумму зарплаты по организации, а из запроса
SELECT AVG(SAL), STDDEV(SAL) FROM EMPLOYEE;
- среднюю зарплату по организации и ее разброс (дисперсию).
Однако наиболее часто требуется подобная итоговая информация не для таблицы в целом, а для определенных наборов (групп) строк таблицы.
Для того чтобы группировать строки таблицы по какому-либо признаку, в команде SELECT существует специальное предложение GROUP BY, которое задает колонку (или колонки) для проведения группировки. Это предложение группирует строки таблицы по значениям колонок группировки с последующим подавлением дублирующих значений в колонках группировки, т.е. позволяет определять подмножество значений некоторой колонки в терминах другой колонки и применять к полученным подмножествам функции агрегирования.
Предположим, что вы хотите найти минимальные и максимальные оклады служащих в подразделениях, тогда вы можете написать
SELECT DEPNO, MIN(SAL), MAX(SAL) FROM EMPLOYEE GROUP BY DEPNO;
Предложение GROUP BY должно следовать после предложения WHERE, если последнее присутствует в команде SELECT. Каждая строка результирующей таблицы относится к одной группе строк. Число групп определяется числом различных значений в колонке группировки (в данном случае DEPNO). Агрегатные функции применяются к каждой группе как к отдельному множеству.
Агрегатные функции можно использовать при соединении таблиц. Допустим, что вам нужно знать, сколько служащих работает на каждой должности в каждом подразделении, какова сумма зарплаты на подразделение и средняя зарплата. Тогда вам нужен запрос
SELECT DNAME, JOB, SUM(SAL), COUNT(*), AVG(SAL) FROM EMPLOYEE, DEPARTAMENT WHERE EMPLOYEE.DEPNO=DEPARTAMENT.DEPNO GROUP BY DNAME, JOB;
Функции SUM( ), COUNT( ), AVG( ) вычисляют суммы, число строк в группе и среднее значение в группе строк.
В SQL можно задавать условия поиска для группы строк. Для этого в команде SELECT существует предложение HAVING, которое должно следовать за предложением GROUP BY. HAVING задает условие поиска для группы строк.
Допустим, что вам необходимо получить ответ на тот же вопрос, что и в предыдущем примере, но при этом каждая группа должна состоять не менее чем из двух сотрудников.
SELECT DNAME, JOB, SUM(SAL), AVG(SAL) FROM EMPLOYEE, DEPARTAMENT WHERE EMPLOYEE.DEPNO=DEPARTAMENT.DEPNO GROUP BY DNAME, JOB HAVING COUNT(*)>=2;
Условие поиска в предложении HAVING исключает из результирующей таблицы группы, содержащие менее двух работников.
Таким образом, вы познакомились с различными вариантами использования команды SQL SELECT.
Константы, выражения, системные переменные
Константы обычно специфицируют единственное значение и, в соответствии с типом представляемых данных, могут быть строковыми, числовыми и представлять дату/время. Строковые константы должны быть заключены в одинарные кавычки.
В SQL существует ряд предопределенных системных переменных, которые можно использовать в выражениях вместо имен колонок и констант. К таким переменным относятся следующие:
NULL - для представления неопределенных значений;ROWID - (в SQLBase) внутренний системный номер строки в таблице;USER - имя пользователя, активного в данный момент;SYSDATETIME - системное текущее время и дата;SYSDATE - системная текущая дата;SYSTIME - системное текущее время;SYSTIMEZONE - временной пояс, установленный в системе.
Выражением в SQL является итем или комбинация итемов с допустимыми для них операциями, которая дает единственное значение. В качестве итемов могут выступать имена колонок, константы, связанные переменные, результаты вычислений функций, системные переменные и другие выражения. При этом если один из итемов имеет нуль-значение, то результат выражения также имеет нуль-значение.
В этом разделе вы узнали, какие вcтроенные типы данных предоставляются проектировщику баз данных в диалектах SQL доменов в физической модели реляционной базы данных. Заметим, что наиболее распространены три из них - varchar2, number и date. Наличие такого небольшого набора типов данных может показаться недостатком, однако это не так. В Oracle типы, определенные в других СУБД и диалектах SQL, можно создать, используя определенный пользователем тип данных. Например, тип money - это тип number с двумя десятичными разрядами, а тип positive integer - тип number без десятичных разрядов и с ограничением на ввод отрицательных значений. По крайней мере, при таком положении дел вам не приходится беспокоиться об ограничениях на внутреннюю память, решая, как хранить вещественное число - с использованием типа float или типа double.
Самое главное при выборе типов данных - обеспечение непротиворечивости. Если вы определите номер шасси автомобиля в одной таблице как number(11), а в другой таблице - как varchar(15), то, когда дело дойдет до соединения этих таблиц, неприятности вам обеспечены. Напишите-ка SQL-предложение для сравнения 918273645 и "918-27-36/4/5". Да так, чтобы оно эффективно работало в предложении, выполняющем соединение!
Описание основных операторов SQL
SQL состоит из набора команд манипулирования данными в реляционной базе данных, которые позволяют создавать объекты реляционной базы данных, модифицировать данные в таблицах (вставлять, удалять, исправлять), изменять схемы отношений базы данных, выполнять вычисления над данными, делать выборки из базы данных, поддерживать безопасность и целостность данных.
Весь набор команд SQL можно разбить на следующие группы:
команды определения данных (DDL - Data Defininion Language);команды манипулирования данными (DML - Data Manipulation Language);команды выборки данных (DQL - Data Query Language);команды управления транзакциями;команды управления данными.
При выполнении каждая команда SQL проходит четыре фазы обработки:
фаза синтаксического разбора, которая включает проверку синтаксиса команды, проверку имен таблиц и колонок в базе данных, а также подготовку исходных данных для оптимизатора;фаза оптимизации, которая включает подстановку действительных имен таблиц и колонок базы данных в представление, идентификацию возможных вариантов выполнения команды, определение стоимости выполнения каждого варианта, выбор наилучшего варианта на основе внутренней статистики;фаза генерации исполняемого кода, которая включает построение выполняемого кода команды;фаза выполнения команды, которая включает выполнение кода команды.
В настоящее время оптимизатор является составной частью любой промышленной реализации SQL. Работа оптимизатора основана на сборе статистики о выполняемых командах и выполнении эквивалентных алгебраических преобразований с отношениями базы данных. Такая статистика сохраняется в системном каталоге базы данных. Системный каталог является словарем данных для каждой базы данных и содержит информацию о таблицах, представлениях, индексах, колонках, пользователях и их привилегиях доступа. Каждая база данных имеет свой системный каталог, который представляет совокупность предопределенных таблиц базы данных.
Таблица 8.1 содержит список команд SQL в соответствии с принятым стандартом, за исключением некоторых практически не используемых в диалектах команд.
Жирным шрифтом выделены команды диалекта SQL СУБД Oracle.
| Команды определения данных объектов | |
| ALTER TABLE | Изменяет описание таблицы (схему отношения) |
| CREATE EVENT | Создает событие таймера в базе данных |
| CREATE INDEX | Создает индекс для таблицы |
| CREATE SEQUENCE | Создает последовательность |
| CREATE TABLE | Определяет таблицу |
| CREATE TABLESPACE | Создает табличное пространство |
| CREATE TRIGGER | Создает триггер в базе данных |
| CREATE VIEW | Определяет представление на таблицах |
| DROP INDEX | Физически удаляет индекс из баз данных |
| DROP SEQUENCE | Удаляет последовательность |
| DROP TABLE | Физически удаляет таблицу из базы данных |
| DROP TABLESPACE | Удаляет табличное пространство |
| DROP VIEW | Удаляет представление |
| Команды манипулирвания данными | |
| DELETE | Удаляет одну или более строк из таблицы базы данных |
| INSERT | Вставляет одну или более строк в таблицу баззы данных |
| UPDATE | Обновляет значения колонок в таблице базыы данных |
| Команды выборки данных | |
| SELECT | Выполняет запрос на выборку данных из таблиц и представлений |
| UNION | Объединяет в одной выборке результаты выполнения двух или более команд SELECT |
| Команды управления транзакциями | |
| COMMIT | Завершает транзакцию и физически актуалищирует состояние базы данных |
| ROLLBACK | Завершает транзакцию и возвращает текущее состояние базы данных на момент последней завершенной транзакции и контрольной точки |
| SAVEPOINT | Назначает контрольную точку внутри транзакции |
| Команды управления данными | |
| ALTER DATABASE | Изменяет группы хранения или журналы транзакций |
| ALTER DBAREA | Изменяет размер областей хранения базы данных |
| ALTER PASSWORD | Изменяет пароль для доступа к базе данных |
| ALTER STOGROUP | Изменяет состав областей хранения в группе хранения |
| CHECK DATABASE | Проверяет целостность базы данных |
| CHECK INDEX | Проверяет целостность индекса |
| CHECK TABLE | Проверяет целостность таблицы и индекса |
| CREATE DATABASE | Физически создает базу данных |
| CREATE DBAREA | Создает область хранения базы данных |
| CREATE STOGROUP | Создает группу хранения |
| CREATE SYSNONYM | Создает синоним для таблицы или представления |
| DEINSTALL DATABASE | Делает базу данныхх недоступной пользователям вфычислительной сети |
| DROP DATABASE | Физически удаляет базы данных |
| DROP DBAREA | Физически удаляет область хранения данных |
| DROP STOGROUP | Удаляет группу хранения |
| GRANT | Определяет привелеги пользователей и разграничение доступа к базе данных |
| INSTALL DATABASE | Делает базу данных доступной пользователям вычислительной сети |
| LOCK DATABASE | Блокирует текущую активную базу данных |
| REVOKE | Отменяет привелегии пользователей и разграничения доступа к базе данных |
| SET DEFAULT STOGROUP | Определяет группу хранения по умолчанию |
| UNLOCK DATABASE | Деблокирует текущую активную базу данных |
| UPDATE STATISTIK | Обновляет статистику для базы данных |
| Другие команды | |
| COMMENT ON | Размещает в системном каталоге комментарии к описанию объектов БД |
| CREATE SYNONYM | Определяет в системном каталоге альтернативные имена для таблиц и представлений БД |
| DROP SYNONYM | Удаляет из системного каталого альтернативные именя для таблиц и представлений БД |
| LABEL | Изменяет метки системных описаний |
| ROWCOUNT | Вычисляет число строк в таблице БД |
Набор команд SQL, перечисленный в таблице, не является полным. Этот список приведен, чтобы вы составили впечатление о возможностях SQL в целом. Для получения полного списка команд следует обратиться к соответствующему руководству для конкретной СУБД. Следует помнить, что SQL является единственным средством общения всех категорий пользователей с реляционными базами данных.
Основные объекты реляционной базы данных
Кластеры, каталоги и схемы не являются обязательными элементами стандарта и, следовательно, программной среды реляционных баз данных.
Под кластером понимается группа каталогов, к которым можно обращаться через одно соединение с сервером базы данных (программная компонента СУБД).
На практике процедура создания каталога определяется реализацией СУБД на конкретной операционной платформе. Под каталогом понимается группа схем. На практике каталог часто ассоциируется с физической базой данных как набором физических файлов операционной системы, которые идентифицируются ее именем.
Для проектировщика базы данных схема - это общее логическое представление отношений законченной базы данных. С точки зрения SQL, схема - это контейнер для таблиц, представлений и других структурных элементов реляционной базы данных. Принцип размещения элементов базы данных в каждой схеме полностью определяется проектировщиком базы данных.
Для создания таблиц и представлений наличие схемы не обязательно. Если у вас планируется инсталляция только одной логической базы данных, то ясно, что можно обойтись и без схемы. Но если планируется, что одна и та же СУБД будет использоваться для поддержки нескольких баз данных, то надлежащая организация объектов баз данных в схемы может значительно облегчить сопровождение этих баз данных. На практике схема часто ассоциируется с объектами определенного пользователя физической базы данных.
Далее объекты реляционной базы данных будут вводиться в контексте реляционной СУБД Oracle 9i. Такой подход принят потому, что проектирование физической модели реляционной базы данных выполняется для конкретной среды ее реализации.
В Oracle 9i термин схема (Schema) используется для описания всех объектов базы данных, которые созданы некоторым пользователем. Для каждого нового пользователя автоматически создается новая схема.
К числу основных объектов реляционных баз данных относятся таблица, представление и пользователь.
Таблица (Table) является базовой структурой реляционной базы данных.
Она представляет собой единицу хранения данных - отношение. Таблица идентифицируется в базе данных своим уникальным именем, которое включает в себя идентификацию пользователя. Таблица может быть пустой или состоять из набора строк.
Представление (View) - это поименованная динамически поддерживаемая СУБД выборка из одной или нескольких таблиц базы данных. Оператор выборки ограничивает видимые пользователем данные. Обычно СУБД гарантирует актуальность представления - его формирование производится каждый раз, когда представление используется. Иногда представления называют виртуальными таблицами.
Пользователь (User) - это объект, обладающий возможностью создавать или использовать другие объекты базы данных и запрашивать выполнение функций СУБД, таких как организация сеанса работы, изменение состояние базы данных и т. д.
Для упрощения идентификации и именования объектов в базе данных поддерживается такие объекты, как синоним, последовательность и определенные пользователем типы данных.
Синоним (Synonym) - это альтернативное имя объекта (псевдоним) реляционной базы данных, которое позволяет иметь доступ к данному объекту. Синоним может быть общим и частным. Общий синоним позволяет всем пользователям базы данных обращаться к соответствующему объекту по его псевдониму. Синоним позволяет скрыть от конечных пользователей полную квалификацию объекта в базе данных.
Последовательность (Sequence) - это объект базы данных, который позволяет генерировать последовательность уникальных чисел (номеров) в условиях многопользовательского асинхронного доступа. Обычно элементы последовательности используются для уникальной нумерации элементов таблиц (строк) в операциях модификации данных.
Определенные пользователем типы данных (User-defined data types) представляют собой определенные пользователем типы атрибутов (домены), которые отличаются от поддерживаемых (встроенных) СУБД типов. Они определяются на основе встроенных типов. Определенные пользователем типы данных образуют ту часть среды СУБД, которая организована в соответствии с объектно-ориентированной парадигмой.
Для обеспечения эффективного доступа к данным в реляционных СУБД поддерживаются ряд других объектов: индекс, табличная область, кластер, секция.
Индекс (Index) - это объект базы данных, создаваемый для повышения производительности выборки данных и контроля уникальности первичного ключа (если он задан для таблицы). Полностью индексные таблицы (index-organized tables) исполняют роль таблицы и индекса одновременно.
Табличное пространство или область (Tablespace) - это именованная часть базы данных, используемая для распределения памяти для таблиц и индексов. В Oracle 9i - это логическое имя физических файлов операционной системы. Все объекты базы данных, в которых хранятся данные, соответствуют некоторым табличным пространствам. Большинство объектов базы данных, в которых данные не хранятся, находятся в словаре данных, расположенном в табличном пространстве SYSTEM.
Кластер (Cluster) - это объект, задающий способ совместного хранения данных в нескольких или одной таблице. Одним из критериев использования кластера является наличие общих ключевых полей в нескольких таблицах, которые используются в одной и той же команде SQL. Обычно кластеризованные столбцы или таблицы хранятся в базе данных в виде таблиц хэширования (т.е. специальным образом).
Секция (Partition) - это объект базы данных, который позволяет представить объект с данными в виде совокупности подобъектов, отнесенных к различным табличным пространствам. Таким образом, секционирование позволяет распределять очень большие таблицы на нескольких жестких дисках.
Для обработки данных специальным образом или для реализации поддержки ссылочной целостности базы данных используются объекты: хранимая процедура, функция, команда, триггер, таймер и пакет (Oracle). С помощью этих объектов базы данных можно выполнять так называемую построчную обработку (record processing) данных. С точки зрения приложений баз данных построчная обработка - это последовательная выборка данных по одной строке, ее обработка и переход к обработке следующей строки.
Данные объекты реляционной базы данных представляют собой программы, т.е. исполняемый код. Этого код обычно называют серверным кодом (server-side code), поскольку он выполняется компьютером, на котором установлено ядро реляционной СУБД. Планирование и разработка такого кода является одной из задач проектировщика реляционной базы данных.
Хранимая процедура (Stored procedure) - это объект базы данных, представляющий поименованный набор команд SQL и/или операторов специализированных языков обработки программирования базы данных (например, SQLWindows или PL/SQL).
Функция (Function) - это объект базы данных, представляющий поименованный набор команд SQL и/или операторов специализированных языков обработки программирования базы данных, который при выполнении возвращает значение - результат вычислений.
Команда (Command) - это поименованный оператор SQL, который заранее откомпилирован и сохраняется в базе данных. Скорость обработки команды выше, чем у соответствующего ему оператора SQL, т.к. при этом не выполняются фазы синтаксического разбора и компиляции.
Триггер (Trigger) - это объект базы данных, который представляет собой специальную хранимую процедуру. Эта процедура запускается автоматически, когда происходит связанное с триггером событие (например, до вставки строки в таблицу).
Таймер (Timer) отличается от триггера тем, что запускающим событием для хранимой процедуры является событие таймера.
Пакет (Package) - это объект базы данных, который состоит из поименованного структурированного набора переменных, процедур и функций.
В распределенных реляционных СУБД имеются специальные объекты: снимок и связь базы данных.
Снимок (Snapshop) - локальная копия таблицы удаленной базы данных, которая используется для тиражирования (репликации) таблицы или результата запроса. Снимки могут быть модифицируемыми или предназначенными только для чтения.
Связь базы данных (Database Link) или связь с удаленной базой данных - это объект базы данных, который позволяет обратиться к объектам удаленной базы данных.Имя связи базы данных, грубо говоря, можно представить как ссылку на параметры доступа к удаленной базы данных.
Для эффективного управления разграничением доступа к данным в Oracle поддерживает объект роль.
Роль (Role) - объект базы данных, представляющий собой поименованную совокупность привилегий, которые могут назначаться пользователям, категориям пользователей или другим ролям.
Правила определения имен
Как и в любом языке, имена используются для идентификации элементов и объектов языка. В этом отношении имя есть идентификатор объекта SQL. Имена бывают длинными (до 18 символов) и короткими (до 8 символов). Также различают обыкновенный идентификатор, который начинается с буквы или символов #, @, $ и состоит из букв, цифр и символа _, и идентификатор в апострофах (Delimited Identifier), который состоит из произвольных символов, заключенных в двойные кавычки.
Объекты SQL именуются в соответствии со своей иерархией, и могут иметь квалифицируемые имена, когда имя объекта квалифицируется именем охватывающего объекта, присоединенного к имени вложенного объекта через точку. По стандарту SQL охватывающим является имя схемы, которое есть практически во всех реализациях реляционных СУБД, в том числе и в Oracle. Нижеследующие объекты SQL должны иметь уникальное имя.
Имя пользователя (Authorization ID), для идентификации которого используется короткий идентификатор, обозначающий пользователя базы данных.Колонки таблицы или представления базы данных, для идентификации которых используется, возможно, квалифицируемый длинный идентификатор. Имя колонки квалифицируется посредством либо имени таблицы, либо имени представления, либо алиасным (корреляционным) именем таблицы, назначенным в команде SQL.База данных, для идентификации которой используется короткий идентификатор, обозначающий базу данных. Имя базы данных может начинаться только с буквы и состоять из букв и цифр.Индексы таблиц, для идентификации которых используются, возможно, квалифицируемый длинный идентификатор. Имя индекса квалифицируется именем пользователя, который выдает команду, использующую данный индекс.Пароль авторизации доступа, для идентификации которого используется короткий идентификатор.Внутренние (связанные с командой SQL) переменные (Bind Variable), для идентификации которых используются обыкновенные идентификаторы или цифры с предшествующим им двоеточием.Команды SQL, для идентификации которых используются длинные идентификаторы.
Имя команды определяется пользователем.Синонимы таблиц и представлений, для идентификации которых используются длинные идентификаторы. Синонимы сохраняются в системном каталоге и используются в качестве альтернативных имен таблиц и представлений.Таблицы базы данных, для идентификации которых используются, возможно, квалифицируемые длинные идентификаторы. В качестве квалификаторов применяются имена пользователей.Представления (виртуальные таблицы) базы данных, для идентификации которых используются, возможно, квалифицируемые длинные идентификаторы. В качестве квалификаторов применяются имена пользователей.События таймера, для идентификации которых используются, возможно, квалифицируемые длинные идентификаторы. В качестве квалификаторов применяются имена пользователей. Хранимые процедуры, для идентификации которых используются, возможно, квалифицируемые длинные идентификаторы. В качестве квалификаторов применяются имена пользователей.Триггеры, для идентификации которых используются, возможно, квалифицируемые длинные идентификаторы. В качестве квалификаторов применяются имена пользователей.
Таким образом, способ именования и идентификации объектов реляционной базы данных задается отчасти их иерархией и подчиняется следующим общим правилам:
имена столбцов должны быть уникальны в таблице;имена таблиц должны быть уникальны в схеме;имена схем должны быть уникальны в каталоге (базе данных);для доступа к объекту базы данных используется квалификация имени;для идентификаторов объектов используются буквы, цифры и символы подчеркивания.
Для предметной области базы данных
Для предметной области базы данных для иллюстративных примеров можно выделить следующие основные классы объектов: подразделения (схема структурной организации фирмы), сотрудники (штатное расписание) и подчиненные им объекты (дети, родственники, например), проекты, выполняемые данной организацией.
Сотрудник как объект определяется карточкой личного учета, которая имеет весьма сложную внутреннюю структуру и которую целесообразно как теоретически, так и практически представлять совокупностью объектов, связанных в целое через ее номер (чаще всего искусственно придуманный табельный номер служащего). Аналогично, подразделение как объект определяется структурой организации и его функциями в ней. Проекты (другими словами, работы) являются объектами, которые отражают состояние деятельности организации в получении прибыли. Логическая схема базы данных приведена на рис. 8.2. Для простоты изложения большая часть атрибутов и объектов опущено - пример преследует иллюстрированные и учебные цели и не претендует на полноту представления данных.
Рис. 8.2. Логическая структура учебной базы данных
Определение таблиц данных приведено ниже. Таблица DEPARTAMENT содержит информацию о подразделениях организации, таблица EMPLOYEE - о служащих данной организации, таблица PROJECT - информацию о проектах, выполняемых в организации.
| Номер подразделения | DEPNO (PK) | Integer |
| Наименование | DNAME | char(20) |
| Размещение | LOC | char(20) |
| Руководитель | MANAGER | char(25) |
| Телефон | PHONE | CHAR(15) |
| Номер личной карточки |
| Номер личной карточки | EMPNO (PK) | Integer |
| Фамилия | ENAME | char(25) |
| Имя | LNAME | char(20) |
| Страховка | SSECNO | char(10) |
| Номер подразделения | DEPNO | Integer |
| Должность | JOB | char(25) |
| Возраст | AGE | Integer |
| Стаж | HIREDATE | Data |
| Доплаты | COMM | dec(9,2) |
| Зарплата | SAL | dec(9,2) |
| Штрафы | FINE | dec(9,2 |
| Шифр проекта | PROJNO | char(8) |
| Наименование | PNAME | char(25) |
| Стоимость | BUDGET | Number(9,2) |
Специальные функции
SQL обеспечивает набор специальных функций для преобразований значений колонок. Список таких функций приведен в таблице 8.5.
| DECODE(E,S1,R1,S2,R2,…,[def]) | Если E соответствует Si, то возвращается Ri, в противном случае - def или NULL, если умолчание не задано |
| TO_NUMBER(S) | Возвращает результат преобразования строки S в аргумент типа NUMBER |
| TO_CHAR(X[,F]) | Возвращает результат преобразования строки S в аргумент типа DATE согласно заданному формату даты F |
| TO_DATE(S[,F]) | Возвращает результат преобразования значения параметра S символьного типа в тип DATE |
В таблице EMPLOYEE для каждого служащего можно ввести признак пола - добавить колонку SEX типа CHAR(1) (0 - мужской, 1 - женский). Допустим, что вам нужен список служащих, в котором требуется разделение их по признаку пола с указанием его в числовом формате; тогда можно задать такую команду:
SELECT ENAME, LNAME, AGE, 'Пол:', TO_NUMBER(SEX) FROM EMPLOYEE ORDER BY 5;
В качестве примера использования функции DECODE приведем запрос, вычисляющий список служащих с указанием их руководителя. Если руководитель неизвестен, то выводится по умолчанию "не имеет".
SELECT ENAME, DECODE(DEPNO, 10, 'Дрягин', 20,'Жиляева', 30,' Коротков', 'не имеет') FROM EMPLOYEE ORDER BY ENAME;
Предположим, что руководитель организации имеет неопределенное значение колонки DEPNO и, следовательно, для него будет работать умолчание, предусмотренное в DECODE.
SQL и его история
Единственным средством общения и администраторов баз данных, и проектировщиков, и разработчиков, и пользователей с реляционной базой данных является структурированный язык запрос SQL (Structured Query Language). SQL есть полнофункциональный язык манипулирования данными в реляционных базах данных. В настоящее время он является общепризнанным, стандартным интерфейсом для реляционных баз данных, таких как Oracle, Informix, Sybase, DB/2, MS SQL Server и ряда других (стандарты ANSI и ISO). SQL - непроцедурный язык, который предназначен для обработки множеств, состоящих из строк и колонок таблиц реляционной базы данных. Хотя существуют его расширения, допускающие процедурную обработку. Проектировщики баз данных используют SQL для создания всех физических объектов реляционной базы данных.
Теоретические основы SQL были заложены в известной статье [Кодд], положившей начало развитию теории реляционных БД. Первая практическая реализации была выполнена в исследовательских лабораториях фирмы IBM Chamberlin D.D. и Royce R.F. Промышленное применение SQL было впервые реализовано в СУБД Ingres. Одной из первых промышленных реляционных СУБД является Oracle. По сути дела, реляционная СУБД - это программное обеспечение, которое управляет работой реляционной базы данных.
Первый международный стандарт языка SQL был принят в 1989 г. (SQL-89). В конце 1992 г. был принят новый международный стандарт SQL-92. В настоящее время большинство производителей реляционных СУБД используют его в качестве базового. Однако работы по стандартизации языка SQL далеки от завершения и уже разработан проект стандарта SQL-99, который вводит в обиход языка понятие объекта и разрешает на него ссылаться в операторах SQL. В исходном варианте SQL не было команд управления потоком данных, они появились в недавно принятом стандарте ISO/IEC 9075-5: 1996 дополнительной части SQL.
Каждой конкретной СУБД соответствует своя собственная реализация SQL, в целом поддерживающая определенный стандарт, но имеющая свои особенности. Эти реализации называются диалектами. Так, стандарт ISO/IEC 9075-5 предусматривает объекты, называемые постоянно хранимыми модулями или PSM-модулями (Persistent Stored Modules). В СУБД Oracle расширение PL/SQL является аналогом указанного выше расширения стандарта 1.
