Sql запросы firebird c. Выборка SELECT из базы данных FIREBIRD. Утилиты администрирования Firebird
Рассмотрены вопросы, необходимые разработчику для создания клиент-серверных приложений с использованием СУБД Firebird, явившейся развитием СУБД Borland Interbase 6. Содержится обзор концепций и моделей архитектуры клиент/сервер, а также практические рекомендации по работе с клиентскими библиотеками Firebird. Детально описаны особенности типов данных SQL, язык манипулирования данными (Data Manipulation Language, DML), а также синтаксис и операторы языка определения данных (Data Definition Language, DDL). Большое внимание уделено описанию транзакций и приведены советы по их использованию при разработке приложений. Описано программирование на стороне клиента и сервера написание триггеров и хранимых процедур, создание и использование событий базы данных, обработка ошибок в коде на сервере и многое другое. Материал сопровождается многочисленными примерами, советами и практическими рекомендациями.
Для разработчиков баз данных
Простейший запрос
Этот запрос просто отыскивает все строки из таблицы соответствия в произвольном порядке. Хотя оптимизатор определяет наличие индекса (индекс первичного ключа), он его не использует для поиска:
SQL>SET PLANONLY ON;
SQL> SELECT * FROM COUNTRY;
Оптимизатор выбирает индекс правой стороны, потому что правый поток будет того же размера, что и левый или (потенциально) большего размера. Опять же, оптимизатор не может сказать, что это отношение является отношением один к одному. Столбец PROJ_NAME, задающий порядок выходного набора, имеет уникальный индекс, созданный ограничением UNIQUE, чтобы использовать для сортировки, и оптимизатор выбирает этот индекс. Индекс сортировки появляется в плане первым, указывая серверу на необходимость сортировки левого потока перед тем, как он будет отыскивать соответствие ключа соединения в правом потоке.
Соединение равенства при отсутствии доступных индексов
Таблицы в следующем запросе являются неиндексированными копиями таблиц PROJECT и EMPLOYEE (см. сноску 4 ранее в этой главе):
SQL> SELECT PI.*, EL. FULL_NAME FROM PROJECT1 PI JOIN EMPLOYEEL EL ON EL.EMP_NO = PL.TEAM_LEADER ORDER BY PI. PROJ_NAME;
PLAN SORT (MERGE (SORT (EL NATURAL) , SORT (PI NATURAL)))
Потоки с обеих сторон будут сортироваться, а затем сливаться, полученная река снова будет сортироваться, потому что ни один из потоков не имеет требуемого порядка сортировки.
Трехстороннее соединение с индексированными равенствами
Рассмотрим тройное эквисоединение в следующем примере:
SQL> SELECT P.PROJ_NAME, D.DEPARTMENT, PDB. PROJECTED_BUDGET FROM PROJECT P
JOIN PROJ_DEPT_BODGET PDB ON P.PROJ_ID = PDB.PROJ_ID JOIN DEPARTMENT D ON PDB.DEPT_NO = D.DEPT_NO;
PLAN JOIN (D NATURAL, PDB INDEX (RDB$FOREIGN18), P INDEX (RDB$PRIMARY12))
Поскольку доступно множество подходящих индексов, оптимизатор выбирает метод доступа JOIN. Индекс, связывающий поток PDB с таблицей DEPARTMENT, будет использован для выбора потока DEPARTMENT. При обработке результирующей реки и потока PROJECT эквисоединение между первичным ключом таблицы PROJECT и большей по размеру (потенциально) реки дает возможность формировать реку с использованием индекса первичного ключа PROJECT для выборки данных из реки.
Трехстороннее соединение
только с одним индексированным равенством
Для этого примера мы используем неиндексированные копии таблиц PROJECT и EMPLOYEE для демонстрации того, как оптимизатор будет использовать доступный индекс, когда он может применять лучший вариант условий неиндексированного эквисоединения:
SQL> SELECT PI.PROJ_NAME, DL.DEPARTMENT, PDB.PROJECTED_BUDGET FROM PROJECT1 PI
JOIN PROJ_DEPT_BUDGET PDB ON PI . PROJ_ID = PDB.PROJ_ID
JOIN DEPARTMENT1 Dl ON PDB. DEPT_NO = Dl. DEPT_NO;
PLAN MERGE (SORT
(PI NATURAL), SORT (JOIN (Dl NATURAL, PDB INDEX (RDB$FOREIGN18))))
В самом внутреннем цикле выбран индекс внешнего ключа для эквисоединения потока PDB и выбираемых подходящих строк потока DEPARTMENT. Заметьте, что выбор такого индекса ничего не выполняет с внешним ключом, для поддержки которого был создан индекс, поскольку таблица, на которую ссылается внешний ключ, даже не присутствует в операторе.
После этого результирующая река и поток PROJECT сортируются. В завершение (в самом внешнем цикле) два сортированных потока объединяются в один.
Запросы с множеством планов
Когда в запросе задаются подзапросы и объединения, используется несколько операторов SELECT. Оптимизатор конструирует независимый план для каждого оператора SELECT. Возьмем следующий пример:
(SELECT E.FULL_NAME FROM EMPLOYEE E
WHERE P.PRODUCT = "software"
PLAN (Е INDEX (RDB$PRIMARY7))
PLAN (Р INDEX (PRODTYPEX))
Первый план выбирает индекс первичного ключа таблицы EMPLOYEE для просмотра кодов TEAM_LEADER в первичной таблице подзапроса. Индекс PRODTYPEX для таблицы PROJECT используется для фильтрации строк в таблице PRODUCT, поскольку первым элементом ключа в этом индексе является столбец PRODUCT.
Интересно то, что если изменить тот же запрос, включив предложение упорядочения, то оптимизатор изменит свой выбор индекса для фильтрации и выберет уникальный индекс по PROJ_NAME для навигации по упорядочиваемому столбцу:
(SELECT E.EULL_NAME FROM EMPLOYEE E
WHERE P.TEAM_LEADER = E.EMP_NO) AS LEADER_NAME
WHERE P.PRODUCT = "software" ORDER BY 1;
PLAN (E INDEX (RDB$PRIMARY7))
PLAN (P ORDER RDB$11)
Задание вашего собственного плана
Синтаксис выражений, который использует оптимизатор для создания плана и передачи его серверу Firebird доступен в SQL в предложении PLAN. Это позволяет вам определять ваш собственный план, ограничивая оптимизатор в его выборе.
Предложение PLAN может быть задано почти в любом операторе SELECT, включая операторы, используемые в создании просмотров, в хранимых процедурах и подзапросах. Firebird версии 1.5 и выше также допускает предложения PLAN и в триггерах. Множество планов может быть указано независимо для запроса и любого подзапроса. При этом нет требования "все или ничего" - любое предложение плана является необязательным.
Предложение PLAN генерируется для оператора SELECT В хранимой процедуре выбора. Поскольку выходом хранимой процедуры выбора является виртуальный набор, любые условия будут основываться на доступе NATURAL. При этом любой оператор SELECT в хранимой процедуре будет оптимизирован, и для него можно применять пользовательский план.
ПРИМЕЧАНИЕ. Конструирование пользовательского плана для оператора SELECT в просмотре создает собственные проблемы для разработчика. Более подробную информацию см. в разд. "Использование планов запросов для просмотров" главы 24.
Вы должны задать имена и порядок использования для всех индексов, которые будут использованы.
Оптимизатор всегда создает план, даже если задан пользовательский план. Хотя оптимизатор не мешает созданному пользователем плану, он проверяет, какие индексы подходят для данного контекста. Альтернативные пути отбрасываются, но во всем остальном дела идут своим чередом. Таким образом, наличие пользовательского плана не отключает оптимизатор, и он все равно делает свою оценку и генерацию своего плана в тех аспектах, которые не были указаны в предложении PLAN.
Неверный индекс приведет к тому, что запрос не будет выполнен. Если любой предикат или условие соединения остаются после того, как все индексы, указанные в плане выражения, будут использованы, оптимизатор просто оценит потоки в соответствии с естественным порядком и порядком сортировки по умолчанию.
Представляя ваше собственное выражение плана, вы можете получить небольшое увеличение скорости за счет обхода работы оптимизатора. При этом разработка вашего собственного плана, основанная на структурных правилах управления вашими данными, может не дать ожидаемых вами удовлетворительных результатов, особенно если ваш план наследован от другой СУБД, которая использует структурные правила для оптимизации запросов.
Оптимизатор Firebird основан на стоимости (cost-based) и обычно создает лучший план, если ваша база данных хорошо поддерживается сервисными средствами. Поскольку геометрия индексов и данных может изменяться в процессе выполнения операторов - особенно, если изменяется или удаляется большое количество строк- никакой сгенерированный оптимизатором план не может оставаться статичным от одной подготовки запроса к другой. Если вы создаете статичное выражение PLAN, то ухудшение эффективности может стать результатом снижения производительности, что уберет все преимущества отмены работы оптимизатора.
Firebird SQL Server — сервер баз данных основанный на открытом исходном коде InterBase 6.0. Дистрибутивы Firebird существуют под Windows, Linux, Unix, Solaris, MacOS, под 32-х и 64-х битную архитектуру. Firebird SQL Server распространяется бесплатно и не имеет лицензионных ограничений.
Какую версию Firebird выбрать?
На сегодняшний момент новейшая версия — Firebird 3.0. Стабильная версия Firebird 2.5.
Установка Firebird — выбор типа сервера
Сервер Firebird SQL доступен в 3-х вариантах:
- Firebird Super Server
- Firebird Classic Server
- Firebird Embedded
Firebird Super Server
Firebird Super Server — все клиентские подключения обслуживаются одним серверным процессом, имеется общий клиентский кеш. За счет этого достигается меньший расход ресурсов на обслуживание клиентов. Недостатком Firebird Super Server является отсутствие возможности задействовать несколько процессоров для работы Firebird сервера.
Firebird Classic Server
Firebird Classic Server — на каждое клиентское подключение создает отдельный серверный процесс. Архитектура Firebird Classic Server более надежна, т.к. сбой одного серверного процесса не вызывает отказ в обслуживании всех клиентов. Кроме того, Firebird Classic позволяет задействовать многопроцессорную архитектуру. При установке Firebird вы можете выбрать между версиями Firebird Super Server или Firebird Classic Server .
Firebird Embedded
Firebird Embedded Server — предназначен для встраиваемых баз данных. Состоит из одной dll — fbembed.dll , которая включает в себя клиент и сервер Firebird Super Server. Firebird Embedded не требует установки на клиентской рабочей станции. Достаточно просто скопировать fbembed.dll и еще несколько файлов клиенту на компьютер.
Недостаток Firebird Embedded — это невозможность одновременного подключения к одной БД нескольких клиентов. После успешного подключения Firebird Embedded блокирует файл базы данных для монопольного доступа.
Firebird Embedded Server распространяется отдельным дистрибутивом.
Если затрудняетесь с выбором
Если вы не уверены в выборе типа установки Firebird , то используйте Firebird Super Server. В дальнейшем вы без труда сможете изменить архитектуру сервера.
Начало работы с Firebird
Для знакомства с Firebird можно использовать тестовую базу данных employee.fdb. Она входит в установочный комплект Firebird и расположена в папке Program Files\Firebird\Firebird\examples\empbuild\ .
Создание новой базы Firebird
Для создания пустой базы Firebird:
- запустите утилиту C:\Program Files\Firebird\Firebird\bin\isql.exe
- выполните инструкцию создания базы данных:
В ISQL каждое выражение завершается точкой с запятой.
Проверить подключение можно, выполнив запрос:
SQL> select MON$DATABASE_NAME from MON$DATABASE;
Если все сделано правильно, SQL запрос вернет путь к подключенной базе данных:
========================================================================== D:\TEMP\TEST.FDBБезопасность Firebird
- Firebird хранит всех пользователей в одной служебной базе данных security.fdb.
- Начиная с Firebird 2.1 реализована поддержка SSPI аутентификации средствами Windows. Администраторы домена на уровне БД определяются как суперпользователи SYSDBA.
- В Firebird 3.0 планируется реализовать возможность хранения пользователей в клиентской базе данных.
Пользователь SYSDBA. Смена пароля по умолчанию
SYSDBA — это административный пользователь Firebird с исключительными правами. Пароль по умолчанию: masterkey . Для того чтобы сменить пароль, используйте утилиту gsec из состава Firebird:
C:\Program Files\Firebird\bin>gsec GSEC> modify SYSDBA -pw NEW_PASS
При помощи утилиты gsec можно создавать, удалять, модифицировать и просматривать пользователей. Полный список команд можно получить, набрав help.
Подключение к Firebird из клиентского приложения
Для начала скачайте и установите пакет IBProvider Professional Edition .
IBProvider Professional Edition — это набор COM-компонент, которые позволяют работать с любыми версиями Firebird и InterBase. Компоненты поддерживаются большинством средств разработки: Delphi, C++ Builder, Visual C++, .Net Framework, C#, Visual Basic, VBScript, VBA и др.
Для проверки подключения к Firebird напишем простой сценарий на языке VBScript . Создайте пустой файл с расширением vbs и вставьте в него следующий код, указав правильный путь к базе данных:
Dim cn, cmd, rs, i Set cn = CreateObject ("ADODB.Connection") cn.Open "Provider=LCPI.IBProvider.3;" & _ "Data Source=localhost:d:\temp\test.fdb; " & _ "User Id=SYSDBA;" & _ "password=masterkey;" & _ "ctype=win1251;" & _ "auto_commit=true" set rs = cn.execute("select * from MON$ATTACHMENTS") do while not rs.EOF for i=0 to rs.Fields.Count - 1 wscript.echo rs(i).Name & "=" & rs(i).Value next rs.MoveNext loop rs.close cn.close
Запустите скрипт на выполнение в режиме командной строки, и вы увидите список активных подключений к БД.
Firebird и VBScript, Visual Basic, VBA
Для доступа к Firebird и InterBase из VBScript , VBA , Visual Basic используется библиотека ADO (ActiveX Data Objects). Множество примеров работы с этой библиотекой вы найдете в документации: примеры InterBase, Firebird VBScript, VBA, Visual Basic .
Firebird и Delphi
IBProvider предлагает несколько способов работы с InterBase и Firebird из Delphi:
- dbGo (ADO Express) компоненты, работающие через библиотеку ADO.
- прямой доступ к COM-интерфейсам ADO, минуя компоненты dbGo.
- прямой доступ к COM-интерфейсам OLE DB при помощи сторонних VCL-компонент (OLE DB Direct/OLE DB Express).
Firebird и.Net
Для доступа к Firebird из.Net используется библиотека ADO.NET. На сайте IBProvider опубликовано большое пошаговое руководство, посвященное работе с Firebird в Visual Studio .Net (ADO.NET) .
Дополнительные материалы по теме:
Firebird и C++
В состав IBProvider Professional Edition входит C++ библиотека, которая предоставляет самый быстрый способ работы с OLE DB провайдерами из Visual C++ 2005-2008, а так же из C++ Builder.
Редактирование базы Firebird
В пустой базе данных необходимо создать таблицы, связи между таблицами, первичные ключи, индексы, хранимые процедуры, генераторы и другие объекты. Для редактирования баз данных Firebird вы можете использовать утилиту isql.exe.
Создаем таблицу:
SQL> CREATE TABLE cross_rate CON> (CON> from_currency VARCHAR(10) NOT NULL, CON> to_currency VARCHAR(10) NOT NULL, CON> conv_rate FLOAT NOT NULL, CON> update_date DATE, CON> CON> PRIMARY KEY (from_currency, to_currency) CON>);
Вставляем одну запись и делаем выборку из таблицы:
SQL> INSERT INTO cross_rate VALUES (‘Dollar’, ‘CdnDlr’, 1.3273, ’11/22/93′); SQL> SELECT * from cross_rate; FROM_CURRENCY TO_CURRENCY CONV_RATE UPDATE_DATE ============= =========== ============== =========== Dollar CdnDlr 1.3273000 1993-11-22
Помимо isql существует различные графические утилиты администрирования Firebird .
Утилиты администрирования Firebird
FlameRobin
FlameRobin — поддерживает Firebird. Кросс-платформенная архитектура. Есть редактор SQL, DDL, управление пользователями.Лицензия: open source, распространяется бесплатно.
Сайт:
IBExpert
IBExpert — Поддерживает Firebird, InterBase, Yaffil. Редакторы DDL и DML. Визуальный построитель запросов. Автозавершение кода, Metadata Extractor, а так же множество других возможностей.Лицензия: Бесплатный для exUSSR, для остальных: от 179 евро.
Этот документ сформирован по предложениям в конференции fido7.su.dbms.interbase. Здесь дан список того, что не надо (или категорически нельзя) делать при работе с Interbase/Firebird/Yaffil.
- Не надо логиниться к одной базе с разными путями
В этом случае очень вероятно повреждение базы вплоть до ее полного уничтожения. Т.е. не надо использовать link-и на файлы и каталоги БД в unix, и не надо ошибаться и под win писать путь коннекта как c:dir\data.gdb вместо правильного c:\dir\data.gdb.
этот совет не относится к разным именам одного и того же сервера в строке коннекта. - Не надо выбирать на клиента большие обьемы данных
Человек все равно не в состоянии просмотреть 100 000 записей:-) - Не надо получать новый номер путем SELECT MAX(ID)+1
при многопользовательской работе это 100%-ный источник конфликтов. Используйте генераторы. - Не надо устанавливать значения генераторов (например в 0) внутри процедур и тем более триггеров
опять же, при многопользовательской работе будут конфликты. - Не надо делать большие расчеты в одной транзакции
При больших операциях в одной транзакции сильно увеличивается объем памяти, используемой IB, что приводит к конкретным тормозам при нехватке оной.
Если нет необходимости делать большой расчет (с большими изменениями данных) именно в одной транзакции, то лучше его разбить на части и провести в разных транзакциях. Это приведет к меньшему использованию памяти и ускорению расчета. - Не надо делать PageSize = 1024 или 2048
Устанавливайте при создании (или restore) для баз данных размер страницы 4 или 8 килобайт. При этом нужно чтобы логический диск был отформатирован с размером кластера идентичным размеру страницы. В Firebird и Yaffil можно использовать размер страницы 16К. - Не надо делать коммит после каждой записи, если это не требуется по смыслу
Это излишне нагружает сервер. - Не надо делать commit после вставки каждой записи, если вы их вставляете больше 10 за один раз
Interbase плохо работает в этом режиме, т.е. вставка будет медленной. Лучше оформить вставки "в пакет" и обрамить их StartTransaction/Commit. - Не надо увлекаться ссылочной целостностью больше чем это требуется
Не рекомендуется делать FK от больших таблиц на короткие справочники, в которых никогда не выполняются update и delete. Рекомендуется замещать такие FK контролем на триггерах и явным запретом модификации справочника в его триггерах.
Кроме того, излишнее увлечение каскадным удалением, в совокупности с удалением через триггеры, может сильно запутать логику или привести к непредсказуемым удалениям или ошибкам нарушения целостности. - Не создавайте ненужные индексы
Помните, каждый индекс ускоряя выборку данных, замедляет добавление/изменение/удаление записей. - Не стройте индексы по полям, содержащим наименьшее количество разных значений
(например только 0 и 1, пол мужской/женский и т.п.)
Если Вам все же надо такой индекс, то добавьте к нему еще одно поле, которое сделает его уникальным (такое поле должно идти перед наименее уникальным столбцом в списке полей индекса).
Такие индексы заставляют сервер при поиске или выборках перебирать большое количество записей. Особенно это сказывается при чистке мусора после больших удалений. - Не стройте индексы по полям PK, FK, UNIQUE
IB автоматически создает соответствующие индексы при создании этих constraints. - Не рекомендуется увлекаться outer join
есть мнение, что null при наличии вторичных ключей это плохо, а кроме того оптимизатор при явных join действует совершенно определенным образом. Короче, не уверен - не используй. - Не рекомендуется использовать связку "таблица+ХП"
, т.е. явный или неявный join таблицы с хранимой процедурой. В некоторых ситуациях наблюдалось неправильное выполнение запроса (Например, от пеpемены мест слагаемых, "сумма" иногда меняется.). Также ситуации сильно зависят от версий IB (4.x, 5.x, 6.x) - в одной из версий это может привести к падению сервера, в другой не выполнится, а в третьей - пройдет. - Не рекомендуется употреблять подзапросы в списочном контексте
, то есть in (...), =all(...), =some(...).
оптимизатор раскладывает FIELD IN (X, Y) в FIELD = X or FIELD = Y, соответственно чем больше элементов в IN, тем больше проверок приходится делать, и тем медленнее все это выполняется. Подумайте, может быть удастся заменить IN явным или неявным JOIN. - Не надо увлекаться компонентом TTable и аналогичными
TTable может выбрать все записи из большой таблицы, а также вы будете плохо знать SQL. Используйте TQuery и подобные. - Нельзя создавать триггеры с именами вроде CHECK_xxx
Префикс CHECK_ используется для "системных" триггеров, которые автоматически создаются при создании полей таблиц, имеющих check constraint. Возможно что в последующих версиях создание триггеров с такими именами будет вообще запрещено. - Не рекомендуется создавать свои объекты с префиксом RDB$
особенно триггеры на системные таблицы. После backup/restore такие объекты могут пропасть, или вести себя очень странно (нельзя удалить, нельзя изменить и т.п.) в зависимости от версии Interbase/Firebird/Yaffil. - Не рекомендуется создавать роль (role) с именем любого из существующих пользователей
если вам удастся это сделать (если еще не даны гранты для этого пользователя), то вы заблокируете доступ этого пользователя к базе данных. К тому же, впоследствии поведение может быть изменено, и создавать роль с именем пользователя (или наоборот) может быть запрещено. - Не надо использовать тип FLOAT
этот тип данных имеет длину 4 байта и точность всего 7 цифр. Эквивалентом в Delphi является single. Если хотите использовать вещественные числа, то сначала попробуйте перемножить и поделить два таких числа прямо в Delphi - так вы увидите точность вычислений, что исключит впоследствии проблемы с хранением и обработкой таких данных в базе. - Не используйте маски (*.gdb) для gbak!!!
Использование для gbak масок на Unix или "парных" параметров на Windows вроде
gbak -b /db/*.gdb /bk/*.gbk
gbak -b a.gdb a.gbk b.gdb b.gbk
может привести к удалению ВСЕХ баз данных в указанном каталоге и бэкапов после бэкапа первого обработанного файла. - Не создавайте столбцы с одинаковыми именами но в разном регистре в третьем диалекте
В третьем диалекте (IB6.0 и выше) есть возможность создать таблицу вида
create table X ("Id" integer, "iD" integer); (см. faq по диалектам). При этом сервер будет работать с такими столбцами нормально, однако не все клиентские компоненты смогут нормально обрабатывать такие имена как минимум в FieldByName.
Собственно, именование объектов в двойных кавычках необязательно для третьего диалекта. - Не надо копировать базу обычной файловой командой (операцией) copy
в то время, когда сервер работает с базой.
Файл БД это файл произвольного доступа. А копирование (не gbak) осуществляется поблочно. Поэтому пока часть базы "скопируется", она уже может успеть измениться несколько раз. Как результат, копию БД можно будет сразу отправить в "корзину". - Не надо делать restore (gbak -r) поверх существующей базы данных
.
Если вдруг restore не пройдет - вы окажетесь без рабочей базы данных, и без восстанавливаемого бэкапа. - Ни в коем случае не меняйте во время работы доменное имя сервера.
IB/FB/YA используют имя сервера для именования lock-файла, поэтому в Classic это может привести к порче базы данных (два lck-файла с разными именами для разных процессов Classic). - Нельзя в одной транзакции менять медаданные и тут же выполнять запросы, которые используют эти метаданные
Изменение метаданных зачастую это операция, которая реально применяется только по commit. При этом для текущей транзакции видны изменения этих самых метаданных. Поэтому ни в коем случае нельзя делать нечто вроде
alter table add field1 int
update table set field1 = 10
ISQL и ряд других инструментов при выполнении операторов DDL опознают это и действуют в режиме autocommit. В IB API ограничения на выполнение DDL и DML нет, т.е. разработчик сам должен знать о таком поведении сервера. - Не нужно смешивать в одном запросе синтаксис явных и неявных JOIN
При этом сервер или не выполнит запрос (ошибка), или вернет результат, который вы вовсе не ожидаете. В запросе целиком нужно использовать или только явные JOIN, или только неявные.
Выборка из базы данных - это основная часть нашей работы. Без запросов в базу данных нельзя сделать полноценный динамический сайт. Запрос к одной таблице - это простая задача, а вот запрос к нескольким таблицам - сложная задача. Тут легко запутаться. Очень важно научиться вытаскивать массив значений из базы данных Firebird по определённому запросу .
#Поиск записей о продажах, для которых не установлена дата отгрузки: SELECT * FROM SALES WHERE SHIP_DATE IS NULL;
Как это не странно, но NULL - тоже значение. Вы можете использовать поиск по нему. При записи в базу данных важно учитывать какое поле может быть пустым, а какое должно быть с определённым значением.
#Найти тех сотрудников, у которых нет проектов SELECT * FROM employee WHERE NOT EXISTS(SELECT * FROM employee_project ep WHERE ep.emp_no = employee.emp_no)
Очень удобно использовать подзапрос IN. Вы могли бы через запятую перечислить значения, но это приведёт к тому, что запрос будет статичным и вам каждый раз придётся его менять. К тому же число значений в подзапросе может быть достаточно большим. В этом случае длина SQL-запроса может быть довольно большой. Если вам нужно передать массив PHP в подзапрос IN, то перейдите на страницу Как превратить массив в строку для оператора IN() PHP . Массив будет превращён в строку. Например, строка с id: "1,2,3,4,5,6,7"
#IN(подзапрос) SELECT * FROM SHOP WHERE SHOP_ID IN(SELECT SHOP_ID FROM SHOP WHERE SHOP_ID >1 AND SHOP_ID 1 AND SHOP_ID
Подзапросы хороши тем, что вам не нужно делать несколько запросов, а потом работать с массивами. Вы облегчите себе труд. Будет минимум писанины.
#EXISTS(подзапрос) SELECT * FROM SHOP WHERE EXISTS(SELECT SHOP_ID FROM SHOP WHERE SHOP_ID >1 AND SHOP_ID ALL (SELECT rating FROM Customers WHERE city = "Paris")
В Firebird нет Limit как в MySQL. Тут нужно использовать FIRST.
#FIRST первые 2 SELECT FIRST 2 SHOP_ID FROM SHOP #SKIP - последующие SELECT SKIP 2 SHOP_ID FROM SHOP
При удалении записи в базе дынных будьте особенно осторожными . Многие программисты лишились работы из-за удаления базы. Данные базы чаще всего представляют коммерческую ценность и вы не имеете право полностью удалять данные таблицы. При удалении записей обязательно должно быть условие и желательно не одно, иначе вы очистите всю таблицу.
#FIRST (SELECT только в скобках) DELETE FROM MYTABLE WHERE ID IN (SELECT FIRST 10 ID FROM MYTABLE) #последние 2 записи SELECT skip ((select count(*) - 2 from SHOP)) SHOP_NAME, SHOP_ID FROM SHOP #Этот запрос вернет строки 81-100 из таблицы PEOPLE: select first 20 skip 80 id, name from People order by name asc
Очень часто нужно увидеть на экране уникальные значения. Тут без DISTINCT не обойтись. DISTINCT является неким фильтром, который удаляет повторяющиеся значения.
#без повторов SELECT DISTINCT SHOP_NAME FROM SHOP #группировка SELECT SHOP_NAME FROM SHOP GROUP BY SHOP_NAME #магазин + группировка + Большие буквы SELECT "магазин " || upper(SHOP_NAME) FROM SHOP GROUP BY SHOP_NAME
Удобно использовать маленькое начертание букв при помощи lower. Вы можете использовать php-функцию mb_strtolower(). Её часто придется применять, что не очень удобно.
#условие + приведение к маленькому регистру select case lower(SHOP_NAME) when "майкоп" then "Майкоп::2015" when "ростов" then "Ростов::2015" else "-нет-" end as title, SHOP_ID from SHOP # left join select * from A left join B on A.id = B.code
Вязание таблиц между собой - это самый сложный этап работы. Я часто ломаю голову и редко пишу сложный SQL-запрос без ошибок. Тут вам придется собраться и набраться терпения. Ошибка в один символ приведёт к ошибке в запросе. Вы должны будите привязать таблицы по параметрам. Чаще всего вяжут таблицы по ID.
#выборка select * from flotsam f join jetsam j on f.sea = j.sea and f.ship = j.ship #или select * from flotsam join jetsam using (sea, ship) # Перестроим наши ранние примеры. Мы можем использовать предложение # HAVING для исключения малых групп студентов: select class, count(*) as num_boys, avg(age) as boys_avg_age from students where sex = "M" group by class having count(*) >= 5 #Выберем только группы, которые имеют минимальный разброс по возрасту #1,2 года select class, count(*) as num_boys, avg(age) as boys_avg_age from students where sex = "M" group by class having max(age) - min(age) > 1.2
Часто приходится выводить на странице наиболее характерные значения. Например самый большой заказ в рублях. Если вы будите использовать php, вы должны использовать специальную встроенную функцию. Это лишняя нагрузка на сервер.
Транскрипт
1 (Охватывает Firebird до версии включительно) v Спонсоры документации: Platinum Sponsor Gold Sponsor
2 Оглавление Введение Что содержит данный документ Авторство Спонсоры Благодарности Лицензионные замечания Обновления Подмножества SQL Действия при ошибках Диалекты SQL Типы и подтипы данных Целочисленные данные SMALLINT INTEGER BIGINT Типы данных с плавающей точкой FLOAT DOUBLE PRECISION Типы данных с фиксированной точкой NUMERIC DECIMAL Типы данных для работы с датой и временем DATE TIME TIMESTAMP Символьные типы данных CHAR VARCHAR NCHAR Бинарные типы данных Массивы Специальные типы данных Тип данных SQL_NULL Преобразование типов данных Явное преобразование типов данных Неявное преобразование типов данных Сокращѐнное приведение типов даты и времени (datetime) Работа с доменами Создание доменов Изменение доменов Удаление доменов Операторы DDL DATABASE
3 CREATE DATABASE CONNECT ALTER DATABASE DROP DATABASE SHADOW CREATE SHADOW DROP SHADOW DOMAIN CREATE DOMAIN ALTER DOMAIN DROP DOMAIN TABLE CREATE TABLE ALTER TABLE DROP TABLE RECREATE TABLE INDEX CREATE INDEX ALTER INDEX DROP INDEX SET STATISTICS VIEW CREATE VIEW ALTER VIEW CREATE OR ALTER VIEW DROP VIEW RECREATE VIEW TRIGGER CREATE TRIGGER ALTER TRIGGER CREATE OR ALTER TRIGGER DROP TRIGGER RECREATE TRIGGER PROCEDURE CREATE PROCEDURE ALTER PROCEDURE CREATE OR ALTER PROCEDURE DROP PROCEDURE RECREATE PROCEDURE EXTERNAL FUNCTION DECLARE EXTERNAL FUNCTION ALTER EXTERNAL FUNCTION DROP EXTERNAL FUNCTION FILTER DECLARE FILTER DROP FILTER SEQUENCE (GENERATOR) CREATE SEQUENCE (GENERATOR)
4 ALTER SEQUENCE SET GENERATOR DROP SEQUENCE (GENERATOR) EXCEPTION CREATE EXCEPTION ALTER EXCEPTION CREATE OR ALTER EXCEPTION DROP EXCEPTION RECREATE EXCEPTION COLLATION CREATE COLLATION DROP COLLATION CHARACTER SET ALTER CHARACTER SET Операторы DML SELECT FIRST, SKIP и ROWS Особенности использования Примеры FIRST и SKIP ROWS Примеры с ROWS Список полей SELECT Примеры операторов SELECT с различными типами полей Выборка в переменные с помощью INTO Выражение FROM Выборка из селективной хранимой процедуры Выборка из производной таблицы (derived table) Выборка из общих табличных выражений (CTE) Соединения (JOINS) Внутренние (INNER) и внешние (OUTER) соединения Обычные соединения Соединения с явными условиями Соединения именованными столбцами Естественные соединения (Natural Joins) Замечание о равенстве Перекрестное соединение (CROSS JOIN) Неоднозначные имена полей в соединениях Соединения с хранимыми процедурами Предложение WHERE Предложение GROUP BY HAVING Предложение PLAN Простые планы Составные планы UNION Предложение ORDER BY WITH LOCK
5
Как сервер работает с WITH LOCK Опциональное предложение OF
6 LEAVE EXIT SUSPEND EXECUTE STATEMENT FOR SELECT FOR EXECUTE STATEMENT OPEN FETCH CLOSE IN AUTONOMOUS TRANSACTION EXCEPTION WHEN DO POST_EVENT Встроенные функции и переменные Контекстные переменные CURRENT_CONNECTION CURRENT_DATE CURRENT_ROLE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TRANSACTION CURRENT_USER DELETING GDSCODE INSERTING NEW NOW OLD ROW_COUNT SQLCODE SQLSTATE TODAY TOMORROW UPDATING YESTERDAY USER Функции для работы с контекстными переменными RDB$GET_CONTEXT () RDB$SET_CONTEXT() Скалярные функции ABS () ACOS () ASCII_CHAR () ASCII_VAL () ASIN () ATAN () ATAN2 ()
7 BIN_AND () BIN_OR () BIN_SHL () BIN_SHR () BIN_XOR () BIT_LENGTH () CAST () CEIL (), CEILING () CHAR_LENGTH (), CHARACTER_LENGTH () CHAR_TO_UUID () COALESCE () COS () COSH () COT () DATEADD () DATEDIFF () DECODE () EXP () EXTRACT () FLOOR () GEN_ID () GEN_UUID () HASH () IIF () LEFT () LN () LOG () LOG10 () LOWER () LPAD () MAXVALUE () MINVALUE () MOD () NULLIF () OCTET_LENGTH () OVERLAY () PI () POSITION () POWER () RAND () REPLACE () REVERSE () RIGHT () ROUND () RPAD () SIGN () SIN () SINH ()
8 SQRT () SUBSTRING () TAN () TANH () TRIM () TRUNC () UPPER () UUID_TO_CHAR () Агрегатные функции AVG () COUNT() LIST () MAX () MIN () SUM () Управление транзакциями RELEASE SAVEPOINT ROLLBACK ROLLBACK RETAIN ROLLBACK TO SAVEPOINT SAVEPOINT Внутренние точки сохранения Точки сохранения и PSQL SET TRANSACTION IGNORE LIMBO LOCK TIMEOUT NO AUTO UNDO Безопасность Операторы управления пользователями CREATE USER ALTER USER DROP USER Операторы управления ролями CREATE ROLE ALTER ROLE DROP ROLE Операторы назначения привилегий GRANT REVOKE Роль RDB$ADMIN Предоставление роли RDB$ADMIN в обычной базе данных Использование роли RDB$ADMIN в обычной базе данных Предоставление роли RDB$ADMIN в базе данных пользователей Использование роли RDB$ADMIN в базе данных пользователей AUTO ADMIN MAPPING Включение и выключение флага в обычной базе данных
9 Включение и выключение флага в базе данных пользователей Приложения Приложение 1. Поле RDB$VALID_BLR Приложение 2. Обработка ошибок, коды и сообщения Типы исключений Системные исключения Пользовательские исключения. Создание Пользовательские исключения. Изменение и удаление Коды ошибок SQLSTATE и их описание Коды ошибок GDSCODE их описание, и SQLCODE
10 Глава 1 Введение Это руководство описывает язык SQL, поддерживаемый СУБД Firebird 2.5. Известно, что СУБД Firebird начала создаваться на основе открытого кода СУБД InterBase 6.0. В период с 2000 по 2014 год было выпущено 5 основных релизов Firebird: 1.0.х.х; 1.5.х; 2.0.х; 2.1.х; 2.5.х., релиз 3.0 запланирован на 2015 год. Однако, за всѐ время развития проекта Firebird, а это более 10 лет, на русском языке до сих пор не было создано единой документации. Несколько переведѐнных материалов по отдельным вопросам можно было найти на официальном сайте (Но их, вопервых, очень мало, а во-вторых зачастую они уже неактуальны. Наибольшее количество русскоязычной информации о Firebird находится на сайте за что огромное спасибо Дмитрию Кузьменко (IBSurgeon/iBase.ru). Но единой русскоязычной документации о Firebird до сих пор не существовало. Данное «Руководство по языку SQL СУБД Firebird» это первый русскоязычный документ, полностью освещающий все аспекты и особенности работы с языком SQL Firebird для текущей (актуальной) на сегодняшний день версии СУБД Firebird 2.5. В руководстве также приводятся практические примеры использования SQL, многие из которых взяты из реальной практики. Что содержит данный документ Данный документ содержит описание языка SQL Firebird. Он охватывает следующие основные области: Основные положения; Зарезервированные и ключевые слова; Типы и подтипы данных; Операторы DDL (Data Definition Language - язык создания данных); Операторы DML (Data Manipulation Language - язык обращения с данными); Операторы управления транзакциями; Обработка исключений; Операторы PSQL (Procedural SQL процедурный SQL, используется в хранимых процедурах, триггерах и выполнимых блоках); Безопасность и операторы управления доступом; Контекстные переменные; 10
11 Руководство по языку SQL СУБД Firebird Операторы и предикаты (утверждения); Агрегатные функции; Встроенные функции; Коды ошибок и обработка исключительных ситуаций; UDF (User Defined Functions функции, определѐнные пользователем. Также известные как внешние функции). Вопросы, не связанные с SQL в данном документе не рассматриваются. Авторство В работе над руководством принимали участие: Симонов Денис; Пол Винкеуг; Филиппов Дмитрий; Еманов Дмитрий; Томас Воинк; Карпейкин Александр; Ковязин Алексей; Кузьменко Дмитрий; Редакторы Александр Карпейкин, Дмитрий Кузьменко, Алексей Ковязин, Денис Симонов Спонсоры Платиновым спонсором создания «Руководства по языку СУБД Firebird» является Московская Биржа (Московская Биржа крупнейший в России и Восточной Европе биржевой холдинг, образованный 19 декабря 2011 года в результате слияния биржевых групп ММВБ (основана в 1992) и РТС (основана в 1995). Московская Биржа входит в двадцатку ведущих мировых площадок по объему торгов ценными бумагами, суммарной капитализации торгуемых акций и в десятку крупнейших бирж производных финансовых инструментов. Золотым спонсором «Руководства по языку СУБД Firebird» является IBSurgeon (ibase.ru) (техническая поддержка и инструменты разработчика и администратора для СУБД Firebird. Благодарности Благодарим Влада Хорсуна, Александра Пешкова, Павла Зотова за помощь в 11
12 создании этого документа. Лицензионные замечания Руководство по языку SQL СУБД Firebird Содержание данного Документа распространяется на условиях лицензии «Public Documentation License Version 1.0» (далее «Лицензия»); Вы можете использовать этот Документ, только если согласны с условиями Лицензии. Копии текста Лицензии доступны по адресам (PDF) и (HTML). Оригинальное название документа «Руководство по языку SQL Firebird». Copyright (C) Все права защищены. Адрес электронной почты для контакта: Далее представлен оригинальный текст раздела, так как его перевод не имеет равноценной юридической силы. The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the License); you may only use this Documentation if you comply with the terms of this License. Copies of the License are available at (PDF) and (HTML). Обновления Так как СУБД Firebird постоянно развивается, то изменяется и улучшается его документация. Вы можете получить обновленные версии этого документа и других частей документации по Firebird на русском языке на сайте проекта документации: 12
13 Глава 2 Подмножества SQL SQL имеет четыре подмножества SQL, используемых в различных областях применения: Динамический SQL (DSQL, Dynamic SQL) Процедурный SQL (PSQL, Procedural SQL) Встроенный SQL (ESQL, Embedded SQL) Интерактивный SQL (ISQL, Interactive SQL) Динамический SQL является основной частью языка, которая соответствует Части 2 (SQL/Foundation SQL/Основы) спецификации SQL. DSQL представляет собой конструкции, которые передаются клиентскими приложениями с помощью Firebird API и обрабатываются сервером базы данных. Процедурный SQL является расширением Динамического SQL, в котором дополнительно присутствуют составные операторы, содержащие локальные переменные, присваивание, циклы и другие процедурные конструкции. PSQL относится к Части 4 (SQL/PSM) спецификации SQL. Изначально расширение PSQL было доступно только лишь в постоянно хранимых в базе модулях (процедурах и триггерах), но сравнительно недавно они стали также доступны в Динамическом SQL (смотри EXECUTE BLOCK). Встроенный SQL определяет DSQL подмножество, поддерживаемое средством Firebird GPRE приложение, которое позволяет вам внедрять SQL конструкции в ваш непосредственный язык программирования (C, C++, Pascal, Cobol и так далее) и производить обработку этих внедренных конструкций в правильный вызов Firebird API. Обратите внимание, что ESQL поддерживает только часть конструкций и выражений DSQL. Интерактивный SQL подразумевает собой язык, который может быть использован для работы с приложением командной строки Firebird ISQL для интерактивного доступа к базам данных. ISQL является обычным клиентским приложением. Для него обычный язык это язык DSQL. Однако приложение поддерживает несколько дополнительных команд. Оба языковых подмножества, как DSQL, так и PSQL полностью представлены в данном руководстве. Из набора инструментария ни ESQL, ни ISQL не описаны здесь отдельно, за исключением тех мест, где это не указано явно. Действия при ошибках Обработка любого оператора либо успешно завершается, либо прерывается из-за вызванной определѐнными условиями ошибки. Обработку 13
14 ошибок можно проводить как на клиентском приложении, так и на стороне сервера средствами SQL. Подробнее смотрите в Приложение 2. Обработка ошибок, коды и сообщения. 14
15 Глава 3 Диалекты SQL SQL диалект это термин, определяющий специфические особенности языка SQL, которые доступны во время доступа с его помощью к базе данных. SQL диалект может быть определен как на уровне базы данных, так и на уровне соединения с базой данных. В настоящее время доступны три диалекта: В 1-м диалекте дата и время хранятся в поле с типом данных DATE и имеется тип данных TIMESTAMP, который идентичен DATE. Двойные кавычки используются для разграничения строковых данных. Точность типов данных NUMERIC и DECIMAL меньше, чем в 3-м диалекте и в случае, если значение точности более 9, Firebird хранит такие значения как длинные значения с плавающей точкой. BIGINT не является доступным типом данных. Идентификаторы являются регистро не зависимыми. Значение генераторов хранится как 64 битное целое, а при выдаче значения урезается до 32 битного целого. Диалект 2 доступен только в клиентском соединении к Firebird и не может быть применен к базе данных. Он предназначен для того, чтобы помочь в отладке в случае возможных проблем с целостностью данных при проведении миграции с диалекта 1 на 3. Диалект 3 базы данных позволяет хранить числа (типы данных DECIMAL и NUMERIC) в базе данных как длинные значения с фиксированной точкой (масштабируемые целые числа) в случае если точность числа меньше чем 9. Тип данных TIME доступен и используется для хранения значения только времени. Тип данных DATE хранит информацию о дате. Тип данных BIGINT доступен в качестве целого 64-х битного типа данных. Двойные кавычки могут использоваться, но только для идентификаторов, которые являются зависимыми от регистра, а не для строковых данных, для которых используют одинарные кавычки. Значения генераторов хранятся как 64-ти битные целые значения. Целью 1-го диалекта является обеспечение поддержки для унаследованных (пре-версия IB6) Interbase приложений для работы с Firebird. Диалект 2 используется как промежуточный и предназначен для разрешения проблем при миграции с 1-го в 3-й диалект. Для вновь разрабатываемых баз данных и приложений настоятельно рекомендуется использовать 3-й диалект. Диалект при соединении с базой данных должен быть таким же, как и базы данных. Исключением является случай миграции с 1-го в 3-й диалект, когда в строке соединения с базой данных используется 2-й диалект. По умолчанию это руководство описывает семантику SQL третьего диалекта, если только в тексте явно не указывается диалект. 15
16 Глава 4 Типы и подтипы данных Типы данных используются в случае: определения столбца в таблице базы данных в операторе CREATE TABLE или для его изменения с использованием ALTER TABLE; при объявлении и редактировании домена оператором CREATE DOMAIN/ALTER DOMAIN; при объявлении локальных переменных в хранимых процедурах, PSQLблоках и триггерах, при указании аргументов хранимых процедур; при описании внешних функций (UDF функций, определенных пользователем) для указания аргументов и возвращаемых значений. при явном преобразовании типов данных в качестве аргумента для функции CAST. Данные о типах данных приведены в таблице 4.1. Таблица 4.1. Типы данных Firebird Точность и Название Размер диапазон Описание BIGINT 64 бита От Тип данных доступен только в 3 диалекте. BLOB Переменный Нет. Размер сегмента Тип данных с динамически BLOB изменяемым размером для ограничивается 64К. хранения больших данных, Максимальный таких как графика, тексты, размер поля BLOB 4 оцифрованные звуки. Базовая Гб. структурная единица сегмент. Подтип Blob CHAR(n) CHARACTER(n) n символов (размер в байтах зависит от кодировки, кол-ва байт на символ) от 1 до байтов (объявленный размер может быть до включительно) описывает содержимое. Символьный тип данных фиксированной длины. При отображении данных, строка дополняется пробелами справа до указанной длины. Конечные пробелы не хранятся в базе данных, а восстанавливаются при отображении такого столбца. Восстановление пробельных символов до максимальной длины происходит на клиенте, а не на сервере, при передаче данных по локальной сети пробелы не передаются, что позволяет уменьшить сетевой трафик. Если количество символов n не указано, то по умалчанию принимается 1. DATE 32 бита От н.э. до ISC_DATE 16
17 Название DECIMAL (precision, scale) Размер Переменный (16, 32 или 64 бита) Точность и диапазон Описание н.э. precision = от 1 до Scale должно быть меньше или 18, указывает, по равно precision. Число с меньшей мере, десятичной точкой, имеющей количество цифр для после точки scale разрядов. хранения; Пример: DECIMAL(10,3) scale = от 0 до 18. содержит число точно в Задает количество следующем формате: знаков после ppppppp.sss. разделителя DOUBLE PRECISION 64 бита 2,225 x IEEE двойной точности, до 1,797 x цифр, размер зависит от платформы FLOAT 32 бита 1,175 x до IEEE одинарной точности, 3,402 x цифр INTEGER INT NUMERIC(precision, scale) 32 бита до Переменный (16, 32 или 64 бита) precision = от 1 до 18; точное количество цифр для хранения. scale = от 0 до 18; задает количество знаков после точки. Должно быть меньше или равно precision. signed long Scale должно быть меньше или равно precision. Число с десятичной точкой, имеющей после точки scale разрядов. Пример: NUMERIC(10,3) содержит число точно в следующем формате: ppppppp.sss. SMALLINT 16 бит до signed short (word) TIME 32 бита 0:00 до 23:59: ISC_TIME TIMESTAMP 64 бита От н.э. до Включает информацию и о н.э. времени VARCHAR(n) от 1 до CHAR VARYING байтов CHARACTER VARYING n символов (размер в байтах зависит от кодировки, кол-ва байт на символ) Размер символов в байтах с учетом их кодировки не может быть больше 32К. Начальные и конечные пробелы хранятся и не обрезаются, за исключением тех хвостовых пробелов, которые не умещаются в заявленную длину. Для этого типа данных, в отличие от CHAR (где по умолчанию предполагается количество символов 1), количество символов n обязательно должно быть указано. Замечание о датах: Следует иметь в виду, что временной ряд из дат прошлых веков рассматривается без учета реальных исторических фактов и так, как будто бы во всѐм этом диапазоне ВСЕГДА действовал только Григорианский 17
18 календарь. Целочисленные данные Для целых чисел используют целочисленные типы данных SMALLINT, INTEGER и BIGINT (в 3 диалекте). Firebird не поддерживает беззнаковый целочисленный тип данных. SMALLINT Тип данных SMALLINT представляет собой целочисленное компактное хранилище данных и применяется в случае, когда не требуется широкий диапазон возможных значений для хранения данных. Примеры: CREATE DOMAIN DFLAG AS SMALLINT DEFAULT 0 NOT NULL CHECK (VALUE=-1 OR VALUE=0 OR VALUE=1); CREATE DOMAIN RGB_VALUE AS SMALLINT; INTEGER Тип данных INTEGER представляет собой 4-байтовое целое. Сокращенный вариант записи типа данных INT. BIGINT BIGINT это SQL-99-совместимый 64 битный целочисленный тип данных. Он доступен только в 3-м диалекте. При использовании клиентом диалекта 1, передаваемое сервером значение генератора усекается до 32-х битного целого (INTEGER). При подключении в 3-м диалекте значение генератора имеет тип BIGINT. Числа типа BIGINT находятся в диапазоне, или Начиная с Firebird 2.5 числа типа BIGINT могут быть заданы в шестнадцатеричном виде с 9 16 шестнадцатеричными цифрами. Более короткие шестнадцатеричные числа интерпретируются как тип данных INTEGER. Пример: CREATE TABLE WHOLELOTTARECORDS (ID BIGINT NOT NULL PRIMARY KEY, DESCRIPTION VARCHAR(32)); 18
19 INSERT INTO MYBIGINTS VALUES (, 22, 0X6F55A09D42, X7FFFFFFFFFFFFFFF, XFFFFFFFFFFFFFFFF, X , т.е. INTEGER 0X , т.е. BIGINT 0XFFFFFFFF, -- -1, т.е. INTEGER 0X0FFFFFFFF , т.е. BIGINT); Шестнадцатеричный INTEGER автоматически приводится к типу BIGINT перед вставкой в таблицу. Однако это происходит после установки численного значения, так 0x (8 цифр) и 0x (9 цифр) будут сохранены в разных форматах. Ззначение 0x (8 цифр) будет сохранено в формате INTEGER, а 0x (9 цифр) как BIGINT. Типы данных с плавающей точкой Типы данных с плавающей точкой являются примерами данных, которые хранятся в СУБД с точностью, подходящей масштабу числа. Примерами таких данных являются FLOAT и DOUBLE PRECISION (DOUBLE). Учитывая особенности хранения чисел с плавающей точкой в базе данных, данный тип данных не рекомендуется использовать для хранения денежных данных. По тем же причинам не рекомендуется использовать столбцы с данными такого типа в качестве ключей и применять к ним ограничения уникальности. При проверке данных столбцов с типами данных с плавающей точкой рекомендуется вместо точного равенства использовать выражения проверки вхождения в диапазон, например BETWEEN. При использовании таких типов данных в выражениях рекомендуется крайне внимательно и серьезно подойти к вопросу округления результатов расчетов. FLOAT Данный тип данных обладает приблизительной точностью 7 цифр после запятой. Для обеспечения надежности хранения полагайтесь на 6 цифр. 19
20 DOUBLE PRECISION Руководство по языку SQL СУБД Firebird При хранении данных, предполагается приблизительная точность 15 цифр. Типы данных с фиксированной точкой Данные типы данных позволяют применять их для хранения денежных значений и обеспечивают предсказуемость операций умножения и деления. Firebird предлагает два типа данных с фиксированной точкой: NUMERIC и DECIMAL. В соответствии со стандартом оба типа ограничивают хранимое число объявленным масштабом (количеством чисел после запятой). При этом подход к тому, как ограничивается точность для типов разный: для столбцов NUMERIC точность является такой, «как объявлено», в то время как DECIMAL столбцы могут получать числа, чья точность, по меньшей мере, равна тому, что было объявлено. Например, NUMERIC(4, 2) описывает число, состоящее в общей сложности из четырех цифр, включая 2 цифры после запятой; итого 2 цифры до запятой, 2 после. При записи в столбец с этим типом данных значений 3,1415 в столбце NUMERIC(4, 2) будет сохранено значение 3,14. Для данных с фиксированной точкой общим является форма декларации, например NUMERIC(p, s). Здесь важно понять, что в этой записи s - это масштаб, а не интуитивно предсказываемое «количество знаков после запятой». Для «визуализации» механизма хранения данных запомните для себя процедуру: При сохранении в базу данных число умножается на 10 в степени s, превращаясь в целое; При чтении данных происходит обратное преобразование числа. Способ физического хранения данных в СУБД зависит от нескольких факторов: декларируемой точности, диалекта базы данных, типа объявления (Таблица 4.2). Таблица 4.2. Способ физического хранения вещественных чисел Точность Тип данных Диалект 1 Диалект NUMERIC SMALLINT SMALLINT DECIMAL INTEGER INTEGER 5 9 NUMERIC и DECIMAL INTEGER INTEGER NUMERIC и DECIMAL DOUBLE PRECISION BIGINT NUMERIC Формат объявления данных: 20
21 NUMERIC(p, s) Руководство по языку SQL СУБД Firebird В зависимости от точности (p) и масштаба (s) СУБД хранит данные по разному (см. таблицу 4.2). Приведем примеры того, как СУБД хранит данные в зависимости от формы их объявления: NUMERIC(4) NUMERIC(4,2) NUMERIC(10,4) SMALLINT SMALLINT DOUBLE PRECISION (для 1-го диалекта или BIGINT (для 3-го диалекта) Примечание Всегда надо помнить, что формат хранения данных зависит от точности. Например, вы задали тип столбца NUMERIC(2,2), предполагая, что диапазон значений в нѐм будет Однако в действительности диапазон значений в столбце будет, что объясняется хранением типа данных NUMERIC(2,2) в формате SMALLINT. Фактически типы данных NUMERIC(4,2), NUMERIC(3,2) и NUMERIC(2,2) являются одинаковыми. Т.е. Для реального хранения данных в столбце с типом данных NUMERIC(2,2) в диапазоне для него надо создавать ограничение. DECIMAL Формат объявления данных: DECIMAL(p, s) Формат хранения данных в базе во многом аналогичен NUMERIC, хотя существуют некоторые особенности. Приведем примеры того, как СУБД хранит данные в зависимости от формы их объявления: DECIMAL(4) DECIMAL(4,2) DECIMAL(10,4) INTEGER INTEGER DOUBLE PRECISION (для 1-го диалекта) или BIGINT (для 3-го диалекта) Типы данных для работы с датой и временем В СУБД Firebird для работы с данными, содержащими дату и время, используются типы данных DATE, TIME, TIMESTAMP. В 3-м диалекте присутствуют все три вышеназванных типа данных, а в 1-м для операций с датой и временем доступен только тип данных DATE, который не тождественен типу данных DATE 3-го диалекта, а напоминает тип данных TIMESTAMP из 3-го 21
22 диалекта. Доли секунды, если хранятся в типах данных даты и времени, являются десятитысячными долями секунды. DATE В 3-м диалекте тип данных DATE, как это и следует предположить из названия, хранит только одну дату без времени. В 1-м диалекте нет типа данных «только дата». В случае необходимости сохранять, например, только значения даты, без времени, при записи в таблицу передавайте время к значению даты в виде литерала 00:00: Допустимый диапазон хранения от 01 января 100 г. н.э. до 31 декабря 9999 года. TIME Этот тип данных доступен только в 3-м диалекте. Позволяет хранить время дня в диапазоне от 00:00: до 23:59: При необходимости получения времени из DATE. в 1-м диалекте можно использовать функцию EXTRACT. Примеры: EXTRACT (HOUR FROM DATE_FIELD) EXTRACT (MINUTE FROM DATE_FIELD) EXTRACT (SECOND FROM DATE_FIELD) TIMESTAMP Этот тип данных доступен только в 3-м диалекте, состоит из двух 32- битных слов и хранит дату со временем. Такое хранение эквивалентно типу DATE 1-го диалекта. Символьные типы данных В СУБД Firebird для работы с символьными данными есть тип данных фиксированной длины CHAR и строковый тип данных VARCHAR переменной длины. Максимальный размер текстовых данных, хранящийся в этих типах данных, составляет байт. Максимальное количество символов, которое поместится в этот объем, зависит от используемого набора символов CHARACTER SET и/или заданного порядка сортировки, который для символьных данных задается предложением COLLATE. В случае отсутствия явного указания набора символов при описании 22
23 текстового объекта базы данных будет использоваться набор символов по умолчанию, заданный при создании базы данных. При отсутствии явного указания набора символов, а также отсутствия набора символов по умолчанию в базе данных, поле получает набор символов CHARACTER SET NONE. Если база данных будет содержать строки только с русским алфавитом, то для нее рекомендуется к использованию кодировка WIN1251. При ее использовании на один символ расходуется 1 байт, соответственно максимальный размер текстовых полей для данной кодировки будет символов. Для стандартных операций сортировки при работе с WIN1251 не требуется задавать порядок сортировки (COLLATE). В настоящее время все современные средства разработки поддерживают Unicode. При возникновении необходимости использования западноевропейских текстов в строковых полях базы данных или для более экзотических алфавитов, рекомендуется работать с набором символов UTF8. При этом следует иметь в виду, что на один символ в данном наборе приходится до 4 байт. Следовательно, максимальный размер символов в символьных полях составит 32676/4 (8192) байта на символ. При этом следует обратить внимание, что фактически значение параметра «байт на символ» зависит от диапазона, к которому принадлежит символ: английские буквы занимают 1 байт, русские буквы кодировки WIN байта, остальные символы могут занимать до 4- х байт. Набор символов UTF8 поддерживает последнюю версию стандарта Unicode, до 4 байт на символ, поэтому для интернациональных баз рекомендуется использовать именно эту реализацию поддержки Unicode в Firebird. При работе со строками необходимо помнить и о параметре соединения клиентской программы к базе данных. В нем также задается набор символов. В случае различия набора символов, при выдаче результата для строковых столбцов происходит автоматическая перекодировка как при передаче данных с клиента на сервер, так и в обратном направлении с сервера на клиент. То есть, совершенно нормальной является ситуация, когда база создана в кодировке WIN1251, а в настройках клиента в параметрах соединения стоит KOI8R или UTF8. Упомянутый выше набор символов NONE относится к специальным наборам символов. Его можно охарактеризовать тем, что каждый байт является частью строки, но в системе хранится без указаний, к какому фактическому набору символов они относятся. Разбираться с такими данными должно клиентское приложение, на него возлагается ответственность в правильной трактовке символов из таких полей. Также к специальным наборам символов относится OCTETS. В этом случае данные рассматриваются как байты, которые могут в принципе не интерпретироваться как символы. OCTETS позволяет хранить бинарные данные и/или результаты работы некоторых функций Firebird. Правильное отображение данных пользователю, хранящихся в полях с CHARACTER SET OCTETS, также становится заботой клиентской стороны. При 23
24 работе с подобными данными следует также помнить, что СУБД не контролирует их содержимое и возможно возникновение исключения при работе кода, когда идет попытка отображения бинарных данных в желаемой кодировке. Каждый текстовый набор символов (CHARACTER SET) имеет последовательность сортировки (COLLATE) по умолчанию, задающий порядок сортировки и упорядочивания. Если необходимо нестандартное поведение строк при указанных выше действиях, то в описании строкового столбца может быть указан параметр COLLATE, который его опишет. Помимо описания объявления столбца, выражение COLLATE может быть добавлено в предложениях SELECT в секции WHERE, когда происходят операции сравнения больше меньше, в секции ORDER BY при сортировке по символьному полю, а также при операциях группировки для указания специальной последовательности сортировки при выводе в предложении GROUP BY. Для регистронезависимого поиска можно воспользоваться функцией UPPER: where upper(name) = upper(:flt_name) Для строк с набором символов WIN1251 можно для этих же целей воспользоваться предложением COLLATE PXW_CYRL. Пример: WHERE FIRST_NAME COLLATE PXW_CYRL >= :FLT_NAME Пример сортировки независимой от регистра символов: ORDER BY NAME COLLATE PXW_CYRL См. также CONTAINING Ниже приведена таблица возможных последовательностей сортировки для набора символов UTF8 (таблица 4.3). Таблица 4.3. Последовательности сортировки для UTF8 COLLATION UCS_BASIC UNICODE Комментарии Сортировка работает в соответствии с положением символа в таблице (бинарная): Пример: A, B, a, b, á... Добавлена в Firebird 2.0 Сортировка работает в соответствии с алгоритмом UCA (Unicode Collation Algorithm)(алфавитная). Пример: a, A, á, b, B... Добавлена в Firebird
25 UTF-8 UNICODE_CI_AI Сортировка происходит без учета регистра символа. Добавлена в Firebird 2.1 Сортировка происходит без учета регистра символа, в алфавитном порядке. Добавлена в Firebird 2.5 Пример сортировки строк для набора символов UTF8 без учета регистра символов (эквивалент COLLATE PXW_CYRL) ORDER BY NAME COLLATE UNICODE_CI_AI Для символьных полей, использующих нестандартный порядок сортировки, может возникнуть проблема при построении индекса по данному полю: имеется ограничение на длину поля при построении индекса 84 байта при указании COLLATE, 252 байта без COLLATE. Это актуально для версий Firebird меньше 2.0. Теперь ситуация изменилась. Максимальная используемая длина ключа индекса, ранее установленная в 252 байта, теперь равна 1/4 размера страницы, т.е. от 256 до 4096 байтов. Максимальная длина индексируемой строки на 9 байтов меньше, чем максимальная длина ключа. В таблице 4.4 приведены данные для максимальной длины индексируемой строки (в символах) в зависимости от размера страницы и набора символов. Таблица 4.4. Максимальная длина индексируемого поля (VAR)CHAR Размер страницы Максимальная длина индексируемой строки для набора символов 1 байт/символ 2 байта/символ 3 байта/символ 4 байта/символ Замечание: В кодировках, нечувствительных к регистру ("_CI"), один символ в *индексе* будет занимать не 4, а 6 (шесть) байт, поэтому максимальная длина ключа для страницы, скажем, 4096, составит 169 символов. См. также: CREATE DATABASE, CONNECT, COLLATION, CREATE INDEX, SELECT, Предложение WHERE, Предложение ORDER BY, Предложение GROUP BY. 25
26 Полный список доступных наборов символов и нестандартных порядков сортировки доступен в приложении 4. CHAR CHAR является типом данных фиксированной длины. Если введенное количество символом меньше объявленной длины, то поле дополнится концевыми пробелами. В общем случае символ заполнитель может и не являться пробелом, он зависит от набора символов, так например для набора символов OCTETS - это ноль. Полное название типа данных CHARACTER, но при работе нет требования применять полные наименования; инструменты по работе с базой прекрасно понимают и короткие имена символьных типов данных. Синтаксис: CHAR [(длина)] В случае если не указана длина, то считается, что она равна единице. Данный тип символьных данных можно использовать для хранения в справочниках кодов, длина которых стандартна и определенной «ширины». Примером такого может служить почтовый индекс в России 6 символов. VARCHAR Является базовым строковым типом для хранения текстов переменной длины, поэтому реальный размер хранимой структуры равен фактическому размеру данных плюс 2 байта, в которых задана длина поля. Все символы, которые передаются с клиентского приложения в базу данных, считаются как значимые, включая начальные и конечные пробельные символы. Полное название CHARACTER VARYING. Имеется и сокращенный вариант записи CHAR VARYING. Синтаксис: VARCHAR (длина) NCHAR Представляет собой символьный тип данных фиксированной длины с предопределенным набором символов ISO8859_1. 26
27 Синтаксис: NCHAR (длина) Синонимом является написание NATIONAL CHAR. Аналогично доступно для строковых типов переменной длины: NATIONAL CHARACTER VARYING (NVARCHAR). Бинарные типы данных BLOB (Binary Large Objects, большие двоичные объекты) представляют собой сложные структуры, предназначенные для хранения текстовых и двоичных данных неопределенной длины, зачастую очень большого объема. Синтаксис: BLOB Сокращенный синтаксис: BLOB (<размер сегмента>) BLOB (<размер сегмента>, <подтип>) BLOB (, <подтип>) В случае если подтип не указан, он считается равным 0, читается как BLOB SUBTYPE 0. Общее название этого подтипа «нетипизированный двоичный BLOB». Подтип 1 имеет алиас TEXT. Например, BLOB SUBTYPE TEXT. Это специализированный подтип, который используется для хранения текстовых данных большого объема. Для текстового подтипа BLOB может быть указан набор символов, аналогично символьному полю. Начиная с версии Firebird 2.0 для текстовых BLOB полей можно указывать порядок сортировки COLLATE. Указание дискового сегмента BLOB является некоторым атавизмом, оно идет с тех времен, когда приложения для работы с данными BLOB писались на C (Embedded SQL) при помощи GPRE. В настоящий момент размер сегмента при работе с данными BLOB определяется клиентской частью, причем размер сегмента может превышать размер страницы данных. Подтип BLOB отражает природу данных, записанную в столбце. Могут быть также добавлены пользовательские подтипы данных, для них зарезервирован интервал от -1 до Максимальный размер поля BLOB ограничен 4Гб и не зависит от варианта 27
28 сервера, 32 битный или 64 битный (во внутренних структурах, связанных с BLOB присутствуют 4-х байтные счетчики). Для размера страницы 4096 максимальный размер BLOB поля несколько ниже 2 Гб. Текстовые BLOB любой длины и с любым набором символов (включая multi-byte) теперь могут быть использованы практически c любыми встроенными функциями и операторами (начиная с Firebird 2.1): Полная поддержка для операторов: - = (присвоение); - =, <>, <, <=, >, >= (сравнение); - (конкатенация); - BETWEEN, IS DISTINCT FROM, IN, ANY SOME и ALL. Частичная поддержка для STARTING , LIKE и CONTAINING. (возникает ошибка, в случае если второй аргумент больше или равен 32 Кб). SELECT DISTINCT, ORDER BY и GROUP BY в своей работе используют BLOB ID, а не содержимое самого поля. Это одновременно и хорошо и плохо, кроме того, SELECT DISTINCT ошибочно выдает несколько значений NULL, если они присутствуют. GROUP BY ведет себя странно в том, что он объединяет одинаковые строки, если они находятся рядом, но не делает этого, если они располагаются вдали друг от друга. По умолчанию, для каждого BLOB создается обычная запись, хранящаяся на какой-то выделенной для этого странице данных (data page). Если весь BLOB на эту страницу поместится, его называют BLOB уровня 0. Номер этой специальной записи хранится в записи таблицы и занимает 8 байт. Если BLOB не помещается на одну страницу данных (data page), то его содержимое размещается на отдельных страницах, целиком выделенных для него (blob page), а в записи о BLOB помещают номера этих страниц. Это BLOB уровня 1. Если массив номеров страниц с данными BLOB не помещается на страницу данных (data page), то его (массив) размещают на отдельных страницах (blob page), а в запись о BLOB помещают уже номера этих страниц. Это BLOB уровня 2. Уровни выше 2 не поддерживаются. См. также: FILTER, DECLARE FILTER. Массивы Поддержка массивов в СУБД Firebird является расширением традиционной реляционной модели. Поддержка в СУБД такого инструмента позволяет проще решать некоторые задачи по обработке однотипных данных. Массивы в Firebird реализованы на базе полей типа BLOB. Массивы могут быть одномерными и многомерными. 28
29 Пример: CREATE TABLE SAMPLE_ARR (ID INTEGER NOT NULL PRIMARY KEY, ARR_INT INTEGER ); Так будет создана таблица с полем типа массива из четырех целых. Индексы данного массива от 1 до 4. Для определения верхней и нижней границы значений индекса следует воспользоваться следующим синтаксисом: [<нижняя>:<верхняя>] Добавление новой размерности в синтаксисе идет через запятую. Пример создания таблицы с массивом размерности два, в котором нижняя граница значений начинается с нуля: CREATE TABLE SAMPLE_ARR2 (ID INTEGER NOT NULL PRIMARY KEY, ARR_INT INTEGER ); СУБД не предоставляет большого набора инструментов для работы с содержимым массивов. База данных employee.fdb, которая находится в дистрибутиве Firebird, содержит пример хранимой процедуры, показывающей возможности работы с массивами. Ниже приведѐн еѐ текст: CREATE OR ALTER PROCEDURE SHOW_LANGS (CODE VARCHAR(5), GRADE SMALLINT, CTY VARCHAR(15)) RETURNS (LANGUAGES VARCHAR(15)) AS DECLARE VARIABLE I INTEGER; BEGIN I = 1; WHILE (I <= 5) DO BEGIN SELECT LANGUAGE_REQ[:I] FROM JOB WHERE (JOB_CODE = :CODE) AND (JOB_GRADE = :GRADE) AND (JOB_COUNTRY = :CTY) AND (LANGUAGE_REQ IS NOT NULL)) INTO:LANGUAGES; IF (:LANGUAGES = "") THEN /* PRINTS "NULL" INSTEAD OF BLANKS */ 29
30 LANGUAGES = "NULL"; I = I +1; SUSPEND; END END Руководство по языку SQL СУБД Firebird Если приведенных выше возможностей достаточно для ваших задач, то вы вполне можете применять массивы для своих проектов. В настоящее время совершенствования механизмов обработки массивов средствами СУБД не производится. Специальные типы данных Тип данных SQL_NULL Данный тип данных содержит не данные, а только состояние: NULL или NOT NULL. Также, этот тип данных не может быть использован при объявлении полей таблицы, переменных PSQL, использован в описании параметров. Этот тип данных добавлен для улучшения поддержки нетипизированных параметров в предикате IS NULL. Такая проблема возникает при использовании «отключаемых фильтров» при написании запросов следующего типа: WHERE col1 = :param1 OR:param1 IS NULL после обработки, на уровне API запрос будет выглядеть как WHERE col1 =? OR? IS NULL В данном случае получается ситуация, когда разработчик при написании SQL запрос рассматривает:param1 как одну переменную, которую использует два раза, а на уровне API запрос содержит два отдельных и независимых параметра. Вдобавок к этому, сервер не может определить тип второго параметра, поскольку он идет в паре с IS NULL. Именно для решения проблемы «? IS NULL» и был добавлен этот специальный тип данных SQL_NULL. После введения данного специального типа данных при передаче запроса и его параметров на сервер будет работать такая схема: приложение передает параметризованные запросы на сервер в виде «?». Это делает невозможным слияние пары «одинаковых» параметров в один. Так, например, для двух фильтров (двух именованных параметров) необходимо передать четыре позиционных параметра (далее предполагается, что читатель имеет некоторое знакомство с Firebird API): SELECT SH.SIZE, SH.COLOUR, SH.PRICE 30
31 FROM SHIRTS SH WHERE (SH.SIZE =? OR? IS NULL) AND (SH.COLOUR =? OR? IS NULL) После выполнения isc_dsql_describe_bind() sqltype 2-го и 4-го параметров устанавливается в SQL_NULL. Как уже говорилось выше, сервер Firebird не имеет никакой информации об их связи с 1-м и 3-м параметрами - это полностью прерогатива программиста. Как только значения для 1-го и 3-го параметров были установлены (или заданы как NULL) и запрос подготовлен, каждая пара XSQLVARs должна быть заполнена следующим образом: Пользователь задал параметры Первый параметр (сравнение значений): set *sqldata в переданное значение и *sqlind в 0 (для NOT NULL); Второй параметр (проверка на NULL): set *sqldata в NULL (не SQL_NULL) и *sqlind в 0 (для NOT NULL). Пользователь не задал параметры (NULL) Оба параметра (проверка на NULL): set *sqldata в NULL (не SQL_NULL) и *sqlind в -1 (индикация NULL). Другими словами: значение параметра сравнения всегда устанавливается как обычно. SQL_NULL параметр устанавливается также, за исключением случая, когда sqldata передаѐтся как NULL. Преобразование типов данных При написании выражения или при задании, например, условий сравнения, нужно стараться использовать совместимые типы данных. В случае необходимости использования смешанных данных различных типов, желательно первоначально выполнить преобразования типов, а уже потом выполнять операции. При рассмотрении вопроса преобразования типов в Firebird большое внимание стоит уделить тому, в каком диалекте база данных. Явное преобразование типов данных В тех случаях, когда требуется выполнить явное преобразование одного типа в другой, используют функцию CAST. Синтаксис: CAST (<значение> NULL AS <тип данных>), где <тип данных>::= sql_datatype 31
32 <домен> TYPE OF COLUMN relname.colname При преобразовании к домену учитываются объявленные для него ограничения, например, NOT NULL или описанные в CHECK и если <значение> не пройдет проверку, то преобразование не удастся. В случае если дополнительно указывается TYPE OF (преобразование к базовому типу), при преобразовании игнорируются любые ограничения домена. При использовании TYPE OF с типом (VAR)CHAR набор символов и сортировка сохраняются. При преобразовании к типу столбца допускается использовать указание столбца таблицы или представления. Используется только сам тип столбца; в случае строковых типов это включает набор символов и сортировку. Ограничения и значения по умолчанию исходного столбца не применяются. Пример: CREATE TABLE TTT (S VARCHAR (40) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI); COMMIT; /* У меня много друзей (шведский)*/ SELECT CAST ("Jag har mänga vänner" AS TYPE OF COLUMN TTT.S) FROM RDB$DATABASE; В таблице 4.5 представлены допустимые преобразования для функции CAST. Таблица 4.5. Допустимые преобразования для функции CAST Числовые типы CHAR BLOB DATE TIME TIMESTAMP Из типа Числовые типы CHAR BLOB CHAR BLOB Числовые типы DATE TIME TIMESTAMP CHAR BLOB TIMESTAMP CHAR В тип 32
33 BLOB DATE TIME При проведении следует помнить о возможности частичной потери данных, например, при преобразовании типа данных TIMESTAMP в DATE. Неявное преобразование типов данных В 3-м диалекте невозможно неявное преобразование данных, здесь требуется указывать функцию CAST для явной трансляции одного типа в другой. Однако это неотносится к операции конкатенации, при которой все другие типы данных будут неявно преобразованы к символьному. При использовании 1-го диалекта во многих выражениях выполняется неявная трансляция одних типов в другой без применение функции CAST. Например, в выражении отбора в диалекте 1 можно записать: WHERE DOC_DATE < " " и преобразование строки в дату пройдет неявно. В 1-м диалекте можно смешивать целые данные и числовые строки, строки неявно преобразуются в целое, если это будет возможно, например: 2 + "1" корректно выполнится. В 3-м диалекте подобное выражение вызовет ошибку, в нем потребуется запись следующего вида: 2 + CAST("1" AS SMALLINT) Таблица 4.6. Литералы с предопределенными значениями даты и времени Литерал "NOW" "TODAY" "TOMORROW" "YESTERDAY" Значение Текущая дата и время Тип данных для диалекта 1 DATE DATE (c нулевым Текущая дата временем) Текущая дата + DATE (c нулевым 1 временем) DATE (c нулевым Текущая дата - 1 временем) Тип данных для диалекта 3 TIMESTAMP DATE (только дата) DATE DATE 33
34 Сокращѐнное приведение типов даты и времени (datetime) При преобразовании строки в тип DATE, TIME или TIMESTAMP, Firebird позволяет использовать сокращѐнное C-style приведение типов. Синтаксис: datatype "date/timestring" Пример: UPDATE PEOPLE SET AGECAT = "Old" WHERE BIRTHDATE < DATE "1-Jan-1943"; INSERT INTO APPOINTMENTS (EMPLOYEE_ID, CLIENT_ID, APP_DATE, APP_TIME) VALUES (973, 8804, DATE"today" + 2, TIME "16:00"); NEW.LASTMOD = TIMESTAMP"now"; Обратите внимание, что эти сокращѐнные выражения вычисляются сразу же во время синтаксического анализа, то есть как будто оператор уже подготовлен к выполнению. Таким образом, даже если запрос выполняется несколько раз, значение, например, для timestamp "now" не изменится, независимо от того, сколько времени проходит. Если вам нужно получать нарастающее значение времени (т.е. оно должно быть оценено при каждом вызове), используйте полный синтаксис CAST. Пример использования в триггере такого выражения: NEW.CHANGE_DATE = CAST("now" AS TIMESTANP); См. также: Явное преобразование типов данных. Работа с доменами Домены в СУБД Firebird реализуют широко известный по многим языкам программирования инструмент «типы данных, определенные пользователем». Когда несколько таблиц в базе данных содержат поля с характеристиками одинаковыми или практически одинаковыми, то есть целесообразность сделать домен, в котором описать набор свойств поля и использовать такой набор свойств, описанный один раз, в нескольких объектах базы данных. Домены могут использоваться помимо описания полей таблиц и представлений (VIEW) и при объявлении входных и выходных параметров, а также при объявлении переменных в коде PSQL. Определение домена содержит обязательные и необязательные атрибуты. К обязательному атрибуту относится тип данных. К необязательным относятся: 34
35 значение по умолчанию; возможности использования NULL для домена; ограничения CHECK для данных домена; набор символов (для символьных типов данных и BLOB полей); порядок сортировки (для символьных типов данных). Пример описания домена: CREATE DOMAIN BOOL3 AS SMALLINT CHECK (VALUE IS NULL OR VALUE IN (0, 1)); См. также: Явное преобразование типов данных, где описаны отличия работы механизма преобразования данных при указании доменов для опций TYPE OF и TYPE OF COLUMN. При описании таблиц базы данных некоторые свойства столбцов, базирующихся на доменах, могут быть переопределены. Возможности переопределения атрибутов столбцов на базе доменов приведены в таблице 4.7 Таблица 4.7. Возможности переопределения атрибутов столбцов на базе доменов Атрибут тип данных значение по умолчанию текстовый набор символов текстовый порядок сортировки условия проверки CHECK NOT NULL Может переопределяться нет да да да нет нет 35 Примечания также может использоваться, чтобы восстановить для столбца значения по умолчанию для базы данных для добавления в проверку новых условий, можно использовать в операторах CREATE и ALTER на уровне таблицы соответствующие предложения CHECK. во многих случаях лучше оставить при описании домена возможность значения NULL, а контроль его допустимости осуществлять в описании полей на уровне таблицы.
Схема базы данных БД и схема БД В чем разница? Схема структура БД Схема- объект БД 2 3 Метаданные Все объекты, определенные в базе данных называются ее схемой или метаданными СУБД сохраняют метаданные
Лекция 8 Язык SQL. Язык SQL (Structured Query Language) состоит из 3-х частей. На самом деле в SQL гораздо больше разделов, но эти три самые важные, основные, с которыми вам придётся познакомиться непосредственно,
Лекция #7 Базыданных Михаил Моисеев Procedural SQL Представления Представление именованный SELECT запрос, хранимый в БД. CREATE VIEW [(,...)] AS CREATE VIEW studs
Язык SQL Язык SQL SEQUEL (Structured English Query Language, структурированный английский язык для запросов) SQL (Structured Query Language, структурированный язык запросов) Стандарты языка SQL 1986 г.
СМОЛЕНСКОЕ ОБЛАСТНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ СРЕДНЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ «ВЯЗЕМСКИЙ ПОЛИТЕХНИЧЕСКИЙ ТЕХНИКУМ» ОДОБРЕНО УТВЕРЖДЕНО Протоколом Методического совета
Язык SQL Язык SQL SEQUEL (Structured English Query Language, структурированный английский язык для запросов) SQL (Structured Query Language, структурированный язык запросов) 2 Стандарты языка SQL 1986
Введение Стр. 1 Введение Оглавление Оглавление... 2 Введение... 8 1. SQL Ред База Данных. Общие сведения... 10 1.1. Объекты базы данных... 10 1.2. Структура языка... 11 1.3. Базовые языковые конструкции...
Лекция #5 Базыданных Михаил Моисеев SQL-DML Определения В SQL определены два подмножества языка: SQL-DDL (Data Definition Language) -язык определения структур и ограничений целостности баз данных. Сюда
Общие сведения о Transact-SQL Лекция 3 План лекции Типы данных Переменные в Transact-SQL Управляющие конструкции Transact-SQL Функции Transact-SQL Server 2008. Лекция 3 2 Типы данных Server 2008. Лекция
Введение в базу данных Oracle 12c Oracle 12c: введение в SQL 1: Обзор основных возможностей БД Oracle 12c Обсуждение основных концепций, а также теоретических и физических аспектов реляционной базы данных
Простейшие конструкции языка SQL Дмитрий Барашев 13 сентября 2016 г. Computer Science Center Этот материал распространяется под лицензией Creative Commons Attribution - Share Alike 3.0 http://creativecommons.org/licenses/by-sa/3.0/us/deed.ru
Базы данных и Информационные системы 9/15 DataManupulationLanguage Кузиков Б.О. Сумы, СумГУ 2013 Задачи Сегодня мы должны узнать: Как вставить данные в таблицу Как обновить данные в таблице Как удалить
Лекция 3. Язык SQL. Средства манипулирования данными 3.1. Структура запросов Для того, чтобы можно было более или менее точно рассказать про структуру запросов в стандарте SQL/89, необходимо начать со
SQL Structured Query Language информационно-логический язык для описания, изменения и извлечения данных, хранимых в реляционных базах данных. Категории команд: DDL (Data Definition Language) язык определения
SQL-ПОДОБНЫЙ ДЕКЛАРАТИВНЫЙ ЯЗЫК ДЛЯ N-МОДЕЛИ ДАННЫХ. Ольховик Олег Владимирович Белых Александр Валерьевич Введение. В работе предлагается SQL-подобный язык N-Declarative Language (NDL) для N-модели данных,
20761B: Создание запросов данных при помощи Transact-SQL 20761B: Querying Data with Transact-SQL Официальная программа курса на английском языке Содержание Краткая информация... 2 Обзор... 2 О курсе...
Безопасность Firebird: 2.1, 2.5, 3.0 Александр Пешков Firebird Foundation IbPhoenix Начальные этапы развития InterBase Чтобы понять проблемы с безопасностью в firebird, необходимо понять когда и как они
ЯЗЫК VBA Язык VBA является объектно-ориентированным. Это значит, что многие его команды имеют особенный формат. Типичная команда VBA имеет вид: ..< >.
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ Федеральное государственное бюджетное образовательное учреждение высшего образования «Саратовский государственный технический университет имени Гагарина
Дж.Боуман, С.Эмерсон, М.Дарновски ПРАКТИЧЕСКОЕ РУКОВОДСТВО ПО SQL Эта книга поможет даже в тех случаях, когда бесполезно самое лучшее руководство пользователя. Здесь раскрываются темы, которые часто пропускаются
MySQL Основы реляционных баз банных База данных (БД) совокупность данных, систематизированных таким образом, чтобы эти материалы могли быть сохранены, найдены и обработаны с помощью ЭВМ. Система управления
1 Violet Tape MS SQL 2011 With Result Set MS SQL 2011 Denali With Result Set Модификация возвращаемого набора данных NEW В оригинальном звучании и в жизни эта возможность звучит как With Result Set. Эта
Язык запросов SQL - Structured Query Language Типы запросов данных: SELECT выбрать строки из таблиц; INSERT добавить строки в таблицу; UPDATE изменить строки в таблице; DELETE удалить строки в таблице.
Security Базы данных, как и данные, хранимые в файлах базы данных, должны быть защищены. Firebird обеспечивает двухуровневую защиту данных аутентификация пользователя на уровне сервера и привилегии на
MS-10774 Создание запросов в Microsoft SQL Server 2012 Прод олжит ельн о сть ку рса: 40 академических часов Аттестация: удостоверение о повышении квалификации установленного образца (или сертификат ТПУ)
Базы данных Проектирование баз данных "Сложная система, спроектированная наспех, никогда не работает, и исправить её, чтобы заставить работать, невозможно". Законы Мерфи. 16-й закон системантики Этапы
Структура следования. Использование структуры следования, общий вид и структура следования. Блок схема операторов следования. CASE (Transact-SQL) Оценка списка условий и возвращение одного из нескольких
Глава 5 Операции и выражения В этой главе Выражения в языке VBA Совместимость типов данных Оператор присваивания Арифметические операторы Логические операторы Операторы сравнения Строковые операторы Приоритеты
ТЕСТЫ ПО ДИСЦИПЛИНЕ «ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ» Тема: Структурированный язык запросов SQL.. Количество вопросов: 30 Вопрос 1 Для чего предназначен язык SQL? 1 Для написания программных продуктов. 2 Для
Глава 11 Управление правами доступа У базы данных может быть несколько пользователей с различными правами доступа к ее объектам. Так, одним пользователям разрешено только просматривать некоторые таблицы,
NovaInfo.Ru - 17, 2013 г. Технические науки 1 СТРУКТУРИРОВАННЫЙ ЯЗЫК ЗАПРОСОВ SQL В PHPMYADMIN Соломатова Евгения Ивановна phpmyadmin это программа написанная на PHP и предназначенная для управления сервером
SQL DML and DDL SQL можно разделить на две части: язык манипулирования данными (Data Manipulation Language DML). язык определения данных (Data Definition Language DDL). DML Commands form the DML part of
Подзапросы и предикаты 1 Проблема Требование реляционной модели суперпозиция реляционных операций SELECT обеспечивает ее лиши частично 2 Решение Раннее решение использовать представления CREATE
Триггеры ТРИГГЕРЫ являются особым видом процедур, каждый триггер связывается с одной из таблиц базы данных и одной или несколькими операциями модификации этой таблицы (INSERT, UPDATE, DELETE). Говорят,
Лекция 5 Внешние источники данных в 1С Предприятии 8.2.14 Титов А.В. Тула 2013 г. ODBC интерфейс доступа к SQL совместимым базам данных. Клиент ODBC Диспетчер драйверов ODCB Драйвер БД БД Приложение может
Модели данных Старший преподаватель Каф. Процессов управления и информационной безопасности Пермского государственного университета Неверов А.В. Понятие модели данных Модель данных это абстрактное, самодостаточное,
Введение 1 Кому предназначена книга 1 О чем эта книга 1 Что необходимо для чтения книги 3 Соглашения 4 Загрузка исходных текстов из Web 5 Нам интересно мнение читателей 5 Обнаруженные опечатки и их исправление
УДК 681.066 ББК 22.18 С89 Астахова И. Ф., Мельников В. М., Толстобров А. П., Фертиков В.В. СУБД: язык SQL в примерах и задачах. М.:ФИЗМАТЛИТ, 2009. 168 с. ISBN 978-5-9221-0816-4. Учебное пособие содержит
Разработка информационной системы "Отдел кадров магазина" с использованием клиент-серверной технологи Черкесова К.И. Национальный Исследовательский Университет «Белгородский Государственный Университет»
1 из 9 Лекция 2. Язык SQL язык определения и манипулирования для реляционных данных. 2.1. SQL: история языка...1 2.1.1 Стандарт SQL ANSI/ISO...1 2.2. SQL Data Definition Language язык определения реляционных
Прикладной системный анализ Лабораторная работа 3 Создание хранилища данных в реляционной СУБД Создать и заполнить хранилище данных для своего варианта задания из Лабораторной работы 1. Написать SQL-запросы
PGConf 2015 Синие против красных Сергей Мелехин Менеджер проекта emply.ru [email protected] Содержание доклада Различия SQL Различия PL/*SQL Репликация и высокая доступность Способы обхода различий Различия
Firebird 2.5 Обновление справочника языка SQL Всё новое в языке SQL Firebird начиная с InterBase 6 Paul Vinkenoog 8 октября 2011 года, версия 1.1 охватывает Firebird 2.5 и 2.5.1 (Примечание переводчика:
Лекция 6. Всё, что вы хотели знать про SQL, но боялись спросить Что это? SQL (Structured Query Language, язык структурированных запросов) язык запросов к базам данных. Базы данных нужны для того, чтобы
Организация баз данных 2004. 1(7) УДК 004.65 2004 г. А.Д. Плутенко, канд. техн. наук, А.А. Ситников (Амурский государственный университет, Благовещенск) МОДЕЛИРОВАНИЕ ПРОЦЕССА РЕПЛИКАЦИИ ДАННЫХ В СУБД
Урок 2 Курс: «Разработка приложений под мобильные устройства на основе Android» Тема: Переменные, типы данных, операторы План 1. Типы данных 2. Переменные. Константы и литералы 3. Операторы 1. Понятие
Лабораторная работа 5. Хранимые процедуры Цель работы: научиться создавать хранимые процедуры в среде SQL Server Management Studio. Теоретические сведения Хранимая процедура SQL-запрос, который имеет параметры,
PostgreSQL технологическая платформа сервиса Movebo.ru Денис Милованов movebo.ru О сервисе Сервис занимается продвижением сайтов в поисковых системах за счет улучшения поведенческих факторов. Десятки тысяч
ПРОГРАММА ПОВЫШЕНИЯ КВАЛИФИКАЦИИ PHP ПРОГРАММИСТ Модуль 1. PHP программирование - уровень 1 Занятие 1-2. Сервер Apache. Установка PHP Как работает HTML Как работает PHP Установка сервера Apache Служба