Microsoft Jet SQL для Access 2000: средний уровеньOffice 2000
Acey James Bunch Февраль 2000 Относится к: Microsoft® Access 2000 (отрывок) Оригинал Intermediate Microsoft Jet SQL for Access 2000 Перевел Александр Артамонов Аннотация: Вторая в серии из трех статей построена на концепции, освещенной в статье Фундаментальный SQL, и дает более подробную картину того, что можно сделать с помощью Microsoft Jet SQL в Access. Для пользователей и разработчиков, желающих расширить свое понимание Structured Query Language в контексте использования в Microsoft Jet Database Engine эта статья задумана, как следующий логический шаг (38 печатных страниц) Загрузить AcIntSQL.exe. Содержание
ВведениеЭто вторая в серии статей, объясняющих, что такое Microsoft® Jet SQL и как его можно использовать в приложениях, сделанных на Access 2000. Всего есть три статьи: базовый, средний и продвинутый уровень. Статьи задуманы таким образом, чтобы показать синтаксис и методы использования Jet SQL в нарастающем по уровню порядке и продемонстрировать новые особенности Jet SQL, появившиеся в Аксессе 2000. Отсюда и до конца этой статьи, все ссылки на SQL относятся к диалекту SQL, используемому в движке баз данных Microsoft Jet 4.0. Определение SQL среднего уровняОбретя понимание концепций Языка Структурированных Запросов/Structured Query Language - SQL, вы расширите и улучшите способность контролировать структуры и объекты в своей базе данных, а также сможете манипулировать данными, содержащимися в этих структурах с помощью многих интересных и мощных методов. Использованный совместно с такими методами доступа к данным, как DAO и ADO, SQL среднего уровня может значительно улучшить гибкость и производительность вашего приложения. Различия между тематикой Базового, Среднего и Продвинутого SQLТрудно провести грань различия между базовым, средним и продвинутым SQL. Во многих случаях это просто произвольное решение. Но для этой серии статей об использовании SQL в Access 2000 были приняты в расчет следующие аспекты:
Чем SQL среднего уровня может быть полезен?Используя «средний» SQL, вы можете добавить большую мощь и гибкость приложениям Access. Хотя простые инструкции SQL могут выполнять многое, использование более сложных инструкций расширит диапазон способов для доступа и обработки информации в вашей базе данных. Использование SQL среднего уровня также поможет достичь большей степени контроля над использованием и поддержанием базы данных. Улучшения SQLВ Access 2000 было сделано множество улучшений движка данных Microsoft Jet 4.0 для поддержания новых функций Access для более близкого соответствия стандарту ANSI-92, а также для большей совместимости между Access и Microsoft® SQL Server™. Теперь движок баз данных Jet имеет два режима синтаксиса SQL: один, поддерживающий ранее используемый синтаксис SQL, и другой, поддерживающий новый синтаксис SQL. Важно отметить, что некоторые новшества синтаксиса SQL доступны в коде только при использовании ActiveX® Data Objects (ADO) и провайдера данных Jet OLE DB и недоступны в настоящее время в пользовательском интерфейсе «режим SQL» или DAO. В статье будет указываться, когда определенные команды SQL доступны только через провайдера Jet OLE DB и ADO. ADO по сравнению с DAOВ предыдущих версиях Access, Data Access Objects (DAO) был основным методом доступа к данным. Теперь это изменилось. Хотя DAO еще поддерживается, новым способом доступа к данным является ADO. ADO - часть стратегии Майкрософта Universal Data Access, которая основной задачей ставит возможность доступа к любому виду данных, который только может существовать, в базе ли данных или структуре директорий, или в каком-то специальном типе хранения данных. Метод доступа к данным ADO важен в обсуждении Microsoft Jet SQL, поскольку, как упомянуто выше, некоторые из новых инструкций SQL доступны только через использование ADO и поставщик (провайдер) данных Jet OLE DB. В этой статье и в приложенном к ней базе данных - примере весь код написан с использованием ADO. Инструкции SQL, если особо не оговорена доступность только через ADO, могут быть выполнены либо через пользовательский интерфейс "Режим SQL" в Access либо через DAO. Хотя подробное обсуждение ADO выходит за рамки данной статьи, с обзором ADO можно познакомиться по адресу http://www.microsoft.com/data/ado/. Соглашения по коду SQLДанная статья последовательно использует соглашение по коду SQL. Как и в других соглашениях по коду, суть в том, чтобы показать код таким образом, чтобы его было легко читать и понимать. Это достигается использованием сочетания незаполненного пространства, новых строк и ключевых слов, написанных заглавными буквами. В общем, используйте заглавные буквы для всех ключевых слов SQL, и если приходится разбивать строку SQL кода, попытайтесь сделать это на основных разделах SQL-инструкции. Вы лучше поймете о чем идет речь, когда увидите несколько примеров. Плохо отформатированный код SQLCREATE TABLE tblCustomers (CustomerID INTEGER NOT NULL, [Last Name] Хорошо отформатированный код SQLCREATE TABLE tblCustomers ( Язык определения данных (DDL) среднего уровняСтатья "Fundamental Microsoft Jet SQL for Access 2000" показала, как создавать основные объекты, формирующие реляционную базу данных. Следующие разделы этой статьи обсуждают инструкции языка описания данных (DDL - Data Definition Language) среднего уровня, которые позволяют улучшать и/или модифицировать упомянутые базовые структуры . Модификация таблицПосле того, как вы создали и заполнили таблицу, вам может понадобиться изменить ее структуру. Чтобы это сделать, применяйте команду ALTER TABLE. Но имейте в виду, изменение структуры существующей таблицы может повлечь за собой потерю некоторых данных. Например, изменение типа данных поля может вызвать потерю данных или ошибки округления, в зависимости от используемых типов данных. Это также, возможно, нарушит другие части приложения, которые могут ссылаться на измененное поле. Нужно всегда быть особенно внимательным перед модифицированием структуры существующей таблицы. С помощью инструкции ALTER TABLE можно добавлять, удалять или изменять столбец (поле), а также можно добавлять или удалять ограничение. Еще можно объявить значение по умолчанию для поля, однако, изменять можно только одно поле за раз. Давайте предположим, что у нас база данных счетов и мы хотим добавить поле к таблице Customers (Клиенты). Чтобы добавить поле с помощью инструкции ALTER TABLE, используйте предложение ADD COLUMN с именем поля, типом данных и размером данных, если требуется. ALTER TABLE tblCustomers Чтобы изменить тип или размер поля, воспользуйтесь предложением ALTER COLUMN с именем поля, желаемым типом и размером типа данных, если это необходимо. ALTER TABLE tblCustomers Если вы хотите изменить имя поля, вам придется его удалить, а затем воссоздать. Чтобы удалить поле, нужно использовать предложение DROP COLUMN просто с именем поля. ALTER TABLE tblCustomers Обратите внимание, что использование этого метода удалит существующие данные из этого поля. Если вы хотите сохранить существующие данные, нужно менять имя поля с помощью конструктора таблиц в пользовательском интерфейсе Access, либо писать код для сохранения данных во временную таблицу и добавить их обратно в переименованную таблицу. Значение по умолчанию - это значение, сохраняющееся в поле всякий раз при добавлении новой записи, если не было явно указано значения для этого конкретного столбца. Чтобы установить значение по умолчанию для поля, используйте ключевое слово DEFAULT после объявления типа поля либо в предложении ADD COLUMN либо в ALTER COLUMN. ALTER TABLE tblCustomers Обратите внимание, что значение по умолчанию не заключено в одиночные кавычки. Если оно было заключено в кавычки, они были бы вставлены в запись. Ключевое слово DEFAULT может быть также использовано в инструкции CREATE TABLE. CREATE TABLE tblCustomers ( Примечание Инструкция DEFAULT может быть выполнена только через поставщик данных Jet OLE DB и ADO. Она вернет сообщение об ошибке в случае использования через пользовательский интерфейс Access "Режим SQL". Следующий раздел посвящен обсуждению использования ограничений с помощью инструкции ALTER TABLE. Для дополнительной информации об использовании инструкции ALTER TABLE, напечатайте alter table в окошке офисного помощника или в закладке окна помощи Microsoft AccessAnswer Wizard и нажмите Поиск. ОграниченияВ статье "Fundamental Microsoft Jet SQL for Access 2000" ограничения обсуждаютcя как способ установить связи между таблицами. Ограничения могут быть также использованы для определения первичных ключей и ссылочной целостности, а также для *ограничения* значений, которые могут быть вставлены в поле таблицы. В целом ограничения могут быть использованы для поддержки целостности и согласованности данных в вашей базе данных. Существует два типа ограничений: "однопольные" - на уровне поля или на уровне таблицы и "многопольные" - на уровне таблицы. Оба вида ограничений могут быть использованы как в инструкции CREATE TABLE так и в ALTER TABLE. Однопольные ограничения, также известные как ограничения на уровне столбца, объявляются непосредственно с самим столбцом, после объявления поля и типа данных. Давайте возьмем таблицу Customers и создадим первичный ключ на одном поле CustomerID. Чтобы добавить ограничение, используйте ключевое поле CONSTRAINT с именем поля. ALTER TABLE tblCustomers Обратите внимание, что указывается имя ограничения. Можно было бы использовать короткий способ объявления первичного ключа, при котором предложение CONSTRAINT полностью опускается. ALTER TABLE tblCustomers Однако использование короткого способа вынудит Access сгенерировать имя ограничения случайным образом, что в последующем усложнит обращение к ограничению. Всегда полезно давать явные имена ограничениям. Чтобы удалить ограничение, используйте предложение DROP CONSTRAINT в инструкции ALTER TABLE и укажите имя ограничения. ALTER TABLE tblCustomers Ограничения также можно использовать для указания допустимых значений в поле. Можно указать требование к значениям NOT NULL или UNIQUE, также можно определить ограничение CHECK - проверочное ограничение, которое представляет собой тип бизнес правила, которое применяется к полю. Скажем, вы хотите, чтобы значения имени и фамилии было уникальным, то есть чтобы не было одного и того же сочетания имени и фамилии в двух разных записях таблицы. Поскольку это многопольное ограничение, оно объявляется на уровне таблицы, не на уровне поля. Используйте предложение ADD CONSTRAINT и укажите список полей. ALTER TABLE tblCustomers Примечание Вероятно вы не захотите устанавливать ограничение уникальности для имен людей в реальном приложении; здесь мы делаем это только для демонстрации использования ограничений. Ограничение CHECK - новая и мощная возможность SQL, которая позволяет добавлять проверку на валидность данных к таблице путем добавления выражения, которое может относится к одному полю или множеству полей в одной или нескольких таблицах. Предположим, вы хотите убедиться, что сумма в записи счета-фактуры всегда больше $0.00. Чтобы добиться этого, используйте ключевое слово CHECK и выражение проверки на валидность в предложении ADD CONSTRAINT инструкции ALTER TABLE. ALTER TABLE tblInvoices Примечание Инструкция с ограничением CHECK может быть использована исключительно с помощью поставщика данных Jet OLE DB и ADO; она вернет сообщение об ошибке при использовании в пользовательском интерфейсе Access режим SQL. Также обратите внимание, что для того, чтобы удалить ограничение CHECK, нужно выполнить инструкцию DROP CONSTRAINT через поставщик данных Jet OLE DB и ADO. К тому же, если вы определили ограничение CHECK: (1) оно не будет показано как условие на значение в пользовательском интерфейсе Access (ПИ), (2) вы не сможете указать свойство ValidationText, поэтому будет показано стандартное сообщение об ошибке в ПИ Access, и (3) нельзя будет удалить таблицу через ПИ или через код, пока не вы не удалите ограничение с помощью инструкции DROP CONSTRAINT через ADO. Выражение, используемое для определения ограничения CHECK может также ссылаться на более, чем одно поле таблицы, или на поля в других таблицах и может использовать любые операции, допустимые в Microsoft Jet SQL, такие, как инструкции SELECT, математические операторы и статистические (агрегатные) функции. Выражение, которое определяет ограничение CHECK может содержать не более 64 символов. Давайте предположим, что вы хотите установить кредитный лимит каждому клиенту, прежде чем он будет добавлен в таблицу Customers. Используя инструкцию ALTER TABLE с предложениями ADD COLUMN и CONSTRAINT, давайте создадим ограничение, которое возьмет значение из таблицы CreditLimit, чтобы проверить кредитный лимит клиента. Воспользуйтесь следующей SQL-инструкцией, чтобы создать таблицу tblCreditLimit, добавить поле CustomerLimit к таблице tblCustomers, добавить ограничение CHECK к таблице tblCustomers и проверить ограничение CHECK. CREATE TABLE tblCreditLimit ( Обратите внимание, что когда вы будете выполнять инструкцию UPDATE, вы получите сообщение, указывающее на то, что обновления не произошло, так как оно нарушает ограничение CHECK. Если вы измените поле CustomerLimit на значение, которое равно или меньше 100, обновление пройдет успешно. Каскадные обновления и удаленияОграничения также могут использоваться для установки ссылочной целостности между таблицами базы данных. Ссылочная целостность означает помогает держать данные в консистентном и непротиворечивом состоянии. Например, если вы удалите запись о покупателе, а запись об отгрузке этому покупателю останется в базе данных, данные будут в противоречивом состоянии, так как в таблице отгрузок появится висячая запись. Ссылочная целостность устанавливается в момент объявления связей между таблицами. В дополнение к установке ссылочной целостности вы можете также обеспечить синхронизацию записей в таблицах с помощью каскадных обновлений и удалений. Например, когда объявлены каскадные обновления и удаления, при удалении записи о покупателе, соответствующая запись в таблице отгрузок удаляется автоматически. Чтобы обеспечить каскадные обновления и удаления, используйте ключевые слова ON UPDATE CASCADE и/или ON DELETE CASCADE в предложении CONSTRAINT инструкции ALTER TABLE. Обратите внимание, что они должны применятся к внешнему ключу. ALTER TABLE tblShipping Внешние ключиПри применении внешних ключей, может быть полезна идея использовать быстрые внешние ключи. Быстрый внешний ключ - это внешний ключ без индекса. Хотя это может показаться нелогичным, этому есть разумное объяснение. По умолчанию при определении внешнего ключа автоматически создается индекс на столбце/столбцах, составляющих внешний ключ. Во многих случаях это улучшает производительность при выполнении операций, поддерживающих ссылочную целостность. Однака, если в поле внешнего ключа есть много дубликатов, индекс на внешнем ключе отрицательно повлияет на производительность при добавлении и удалении из таблицы. Чтобы воспрепятствовать автоматическому созданию индекса на внешних ключах, используйте ключевые слова NO INDEX в объявлении внешнего ключа. ALTER TABLE tblInvoices Примечание Инструкция для быстрого внешнего ключа может быть выполнена только через провайдер данных Jet OLE DB provider и ADO. Она вернет сообщение об ошибке в случае использования через пользовательский интерфейс Access "Режим SQL". Также обратите внимание, что для удаления быстрого внешнего ключа нужно использовать инструкцию DROP CONSTRAINT через поставщик данных Jet OLE DB и ADO. Другой пример ситуации, в которой быстрый внешний ключ может быть полезен - приложение для базы данных ввода заказов. Предположим, что есть таблица CustomerTypes, описывающая типы покупателей, таблица Customer (Покупатели) и таблица Orders (Заказы). Допустим, что в таблице CustomerTypes есть 10 записей, в таблице Customer - 100 000 записей и 350 000 записей в таблице Orders. Хорошим выбором для таблицы Customers было бы использование быстрого внешнего ключа, ссылающегося на первичный ключ в таблице CustomerTypes - поскольку из 100 000 строк может быть максимально 10 уникальных значений. Для получения данных от индекс здесь мало пользы и он был бы тормозом *on concurrency* и вставку, удаление и обновление в столбце CustmerType. С другой стороны, быстрый внешний ключ, наверное, не был бы полезен, будучи применен к столбцу CustomerID таблицы Orders, так как значения в нем скорее всего будут уникальны, поскольку каждое представляет собой другого покупателя. В подобном случае наличие индексированного внешнего ключа будет весьма выгодным, потому что он используется в соединениях и в критериях поиска. Примечание Хотя в большинстве примеров в этом разделе применялась инструкция ALTER TABLE, все они могли бы быть переписаны с инструкцией CREATE TABLE. Для дальнейшей информации о предложении CONSTRAINT, напечатайте constraints в окошке Помощника MS Office или на закладке *Answer Wizard* в окне помощи Microsoft Access, и нажмите на Поиск. Типы данныхВ стремлении облегчить масштабирование и переход Access-приложений на основе движка баз данных Jet на приложения, основанные на сервере Microsoft SQL Server или MSDE, в движке баз данных Jet были изменены некоторые реализации типов данных и добавлены новые синонимы типов данных. В следующем разделе обсуждаются основные типы данных Jet и их реализацию. Текстовые типы данныхТекстовый тип данных - это тип данных, составляющий поле, которое может хранить текст или сочетание текста и чисел (буквенно-цифровые сочетания), но числа при этом не используются для вычислений. Примерами являются телефонные номера или электронные адреса. Когда вы создаете таблицы через пользовательский интерфейс Access, есть два основных текстовых типа: TEXT и Memo. Но если использовать инструкции SQL, такие как CREATE TABLE или ALTER TABLE, появляется выбор из многих других синонимов типов TEXT и MEMO. В принципе, текстовые поля могут быть длиной до 255 символов, в то время, как MEMO поля - до 65,535 символов, но если MEMO поле не содержит двоичных данных, единственным пределом здесь будет максимальный размер самой базы данных (приблизительно 2.14 Гб или 1,070,000,000 символов Unicode). К тому же под неиспользованные части текста память не выделяется. Таблица внизу перечисляет основные типы данных Jet, различные синонимы и количество байт, выделяемых для каждого типа.
Следующая инструкция CREATE TABLE показывает разнообразие синонимов типов TEXT и MEMO, которые не могут быть использованы для создания таблицы через пользовательский интерфейс Access "Режим SQL". CREATE TABLE tblUITextDataTypes ( Если вы посмотрите на структуру таблицы tblUITextDataTypes с помощью пользовательского интерфейса Access, вы увидите, что синонимы MEMO, LONGTEXT, LONGCHAR, и NOTE в итоге получают тип данных MEMO. Все прочие синонимы дают тип данных TEXT. Для тех текстовых типов данных, у которых не объявлена длина, она составит по умолчанию 255 символов. Хотя вышеуказанные SQL-инструкции могут быть выполнены также через поставщик данных Jet OLE DB и ADO, существуют другие рановидности типов TEXT и MEMO, которые могут быть объявлены только с помощью Jet OLE DB и ADO. CREATE TABLE tblCodeTextDataTypes Если вы посмотрите на структуру таблицы tblUITextDataTypes с помощью пользовательского интерфейса Access, вы увидите, что только тип NCHAR превратился в тип MEMO. Все остальные типы данных стали типом TEXT. Для тех текстовых типов, у которых не указана длина, она составит по умолчанию 255 символов. Примечание Типы данных, перечисленные в предыдущей инструкции SQL могут быть получены только через Jet OLE DB и ADO. Выполнение через пользовательский интерфейс Access "Режим SQL" выдаст сообщение об ошибке. Также обратите внимание, что если вы создаете поле типа TEXT через поставщик Jet OLE DB provider и ADO, вы получите тип MEMO, когда вы будете просматривать структуру таблицы через пользовательский интеорфейс Access. Сжатие Unicode В движке баз данных Microsoft Jet 4.0 все данные текстовых типов теперь хранятся в 2-байтном формате Юникод. Он заменяет формат Multi-byte Character Set (MBCS), который использовался в предыдущих версиях. Хотя Юникод требует больше места для хранения каждого символа, столбцы текстовых типов могут быть объявлены с автоматическим сжатием данных, если это возможно. Когда вы создаете текстовые типы данных с помощью SQL, свойство "Сжатие Юникод" автоматически ставится на "нет". Чтобы установить свойство "Сжатие Юникод" на "Да", используйте ключевые слова WITH COMPRESSION (или WITH COMP) в объявлении на уровне поля. Следующая инструкция CREATE TABLE создает новую таблицу покупателей, на этот раз устанавливая "Сжатие Юникод" на "Да". CREATE TABLE tblCompressedCustomers ( Заметьте, что ключевые слова WITH COMPRESSION и WITH COMP объявляются перед ключевыми словами NOT NULL. Свойство "Сжатие Юникод" можно также изменить у существующего поля инструкцией ALTER TABLE statement таким образом: ALTER TABLE tblCustomers Примечание Ключевые слова WITH COMPRESSION и WITH COMP, упомянутые в предыдущей инструкции SQL могут быть применены через поставщик Jet OLE DB и ADO. Они вызовут сообщение об ошибке при использовании в пользовательском интерфейсе Access "Режим SQL View". Какие из типов данных выбирать при объявлении таблицы, зависит от целей вашего приложения. Если вы знаете, что приложение всегда будет основано на базе данных Jet, используйте наиболее привычные для вас типы данных. Но если приложение может быть перенесено в базу данных, совместимую с ODBC, например, SQL Server или MSDE, используйте типы данных, которые смогут наибольшим образом облегчить переход. Числовые типы данныхЧисловой тип данных составляет поле, которое хранит числа, которые могут быть использованы в вычислениях. Обычно, то, что отличает один числовой тип от другого - это количество байтов, выделенное для хранения данных, что в свою очередь влияет на точность числа, хранимого в этом поле. Многие из типов данных Jet SQL имеют синонимы, которые можно использовать в объявлении типа данных. Какой тип вы выберете, зависит от того, останется ли таблица в базе данных Jet или будет перемещена на сервер баз данных, такой как Microsoft SQL Server. Если она будет перемещена на сервер, следует объявлять тип, который наиболее облегчит переход. Ниже приведена таблица, перечисляющая базовые числовые типы данных Jet, различные синонимы и количество байт, выделеных для каждого типа.
Следующая инструкция CREATE TABLE показывает разнообразие числовых типов данных, которые могут быть использованы для создания таблицы через пользовательский интерфейс Access "Режим SQL". CREATE TABLE tblUINumericDataTypes ( Хотя вышеуказанная инструкция SQL также может быть выполнена через Jet OLE DB и ADO, есть другие разновидности числовых типов, которые могут быть объявлены только с помощью Jet OLE DB и ADO. CREATE TABLE tblCodeNumericDataTypes ( Примечание Типы данных, перечисленные в предыдущей SQL-инструкции могут быть объявлены только через Jet OLE DB provider и ADO. Они вызовут сообщение об ошибке, будучи использованы в режиме SQL пользовательского интерфейса. Также обратите внимание, что если вы создаете поле типа NUMERIC в режиме SQL пользовательского интерфейса Access, то это приведет к типу двойной точности (DOUBLE), когда вы будете просматривать таблицу в конструкторе. Но если вы создадите поле типа NUMERIC с помощью Jet OLE DB provider и ADO, вы получите тип данных DECIMAL при просмотре в конструкторе таблиц Access. Для нового типа данных DECIMAL вы можете установить точность и масштаб числа. Точность - это количество цифр, которое может содержать поле, а масштаб определяет, сколько из этих цифр будет справа от десятичного разделителя. Для точности значение по умолчанию - 18, а |