Агрегатные функции
При статистическом анализе баз данных необходимо получать такую информацию, как общее количество записей, наибольшее и наименьшее значения заданного поля записи, усредненное значение поля. Это делается с помощью запросов, содержащих так называемые агрегатные функции. Агрегатные функции производят одиночное значение для всей группы таблицы. Имеется список этих функций.
count извлекает количество записей данного поля.sum извлекает арифметическую сумму всех выбранных значений данного поля.avg извлекает арифметическое среднее (усреднение) всех выбранных значений данного поля.max извлекает наибольшее из всех выбранных значений данного поля.min извлекает наименьшее из всех выбранных значений данного поля.
Для определения общего числа записей в таблице Products используем запрос
select count (*) from Products;
результатом которого будет следующее (рис. 1.42):
Рис. 1.42. Результат запроса с функцией count
Обратите внимание: на вкладке "Messages" возникает сообщение, что только одна запись извлечена:
(1 row(s) affected)
Это означает, что агрегатные функции возвращают всего одну запись, называемую скалярным значением, в отличие от других запросов, возвращающих наборы записей.
Для определения количества записей поля ProductName таблицы Products используем следующий запрос:
select count (ProductName) from Products;
Таблица Products имеет все заполненные значения полей, поэтому результат этого запроса будет совпадать с результатом извлечения всех записей.
Как быть со значениями полей, которые были незаполненными? Обращение к таким полям осуществляет оператор null. Величина null не означает, что в поле стоит число 0 (нуль) или пустая текстовая строка - " ". Как возникают значения полей null? Существует два способа образования таких значений:
Microsoft SQL Server 2000 автоматически подставляет значение null, если в значение поля не было введено никаких значений и если тип данных для этого поля не препятствует присвоению значения null (если поле не является обязательным для заполнения);если пользователь явным образом вводит значение null (подробнее о создании таблиц см. далее).
Вы можете самостоятельно попробовать определить, содержатся ли в какой-либо таблице базы данных Northwind поля, имеющие значения
select *( или название столбца(ов)) from название_таблицы where название столбца is null;
Для обратной задачи - используем запрос типа
select *( или название столбца(ов)) from название_таблицы where название столбца is not null;
Оператор count учитывает записи со значением поля null.
Синтаксис использования других операторов одинаков - следующие запросы извлекают сумму, арифметическое среднее, наибольшее и наименьшее значения поля UnitPrice таблицы Products:
select sum(UnitPrice) from Products; select avg(UnitPrice) from Products; select max(UnitPrice) from Products; select min(UnitPrice) from Products;
Выполните самостоятельно эти запросы и просмотрите результаты.
Что такое реляционная база данных?
Базы данных - это совокупность сведений (об объектах, процессах, событиях или явлениях), относящихся к определенной теме или задаче. Она организована таким образом, чтобы обеспечить удобное представление этой совокупности, как в целом, так и любой ее части.
Реляционная база данных представляет собой множество взаимосвязанных таблиц, каждая из которых содержит информацию об объектах определенного типа. Каждая строка таблицы содержит данные об одном объекте (например товаре, фирме, клиенте), а столбцы таблицы содержат различные характеристики этих объектов - атрибуты (например, наименования и цены товаров, адреса и телефоны фирм или клиентов). Строки таблицы называются записями; все записи имеют одинаковую структуру - они состоят из полей, в которых хранятся атрибуты объекта (рис. 1.1). Каждое поле записи содержит одну характеристику объекта и имеет строго определенный тип данных (например, текстовая строка, число, дата). Все записи имеют одни и те же поля, только в них содержатся разные значения атрибутов. Для идентификации записей используется первичный ключ. Первичный ключ - это одно или несколько полей (столбцов), комбинация значений которых однозначно определяет каждую запись в таблице.
Рис. 1.1. Названия объектов в таблице
Для работы с данными используются системы управления базами данных (СУБД). Основные функции СУБД - это определение данных (описание структуры баз данных), обработка данных и управление данными.
Любая СУБД позволяет выполнять следующие операции с данными:
добавлять в таблицу одну или несколько записей; удалять из таблицы одну или несколько записей; обновлять значения некоторых полей в одной или нескольких записях; находить одну или несколько записей, удовлетворяющих заданному условию.
Для выполнения этих операций применяется механизм запросов. Результатом выполнения запросов является либо отобранное по определенным критериям множество записей, либо изменения в таблицах. Запросы к базе формируются на специально созданном для этого языке, который так и называется "язык структурированных запросов" (SQL - Structured Query Language).
Важнейшей функцией СУБД является управление данными. Под управлением данными обычно понимают защиту данных от несанкционированного доступа, поддержку многопользовательского режима работы с данными и обеспечение целостности и согласованности данных.
Элементы языка SQL
Термин SQL6) символизирует собой Структурированный Язык Запросов. Это язык, который дает нам возможность работать с данными в реляционных базах данных.
Мир баз данных становится все более и более единым, что привело к необходимости создания стандартного языка, который мог бы использоваться для функционирования в большом количестве различных видов компьютерных сред. Стандартный язык позволит пользователям, знающим один набор команд, применять их, чтобы создавать, отыскивать, изменять и передавать информацию независимо от того, работают ли они на персональном компьютере, сетевой рабочей станции или на сервере.
Элегантность и независимость от специфики компьютерных технологий, а также его поддержка лидерами промышленности в области технологии реляционных баз данных, сделали SQL, и, вероятно, в течение обозримого будущего оставят его, основным стандартным языком. По этой причине любой, кто хочет работать с современными базами данных, должен знать SQL.
Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов), а также ISO (Международной организацией по стандартизации). Однако большинство коммерческих программ баз данных расширяют SQL без уведомления ANSI, добавляя разные особенности в этот язык, которые, как они считают, будут полезны. Иногда они несколько нарушают стандарт языка, хотя хорошие идеи имеют тенденцию развиваться и вскоре становиться стандартами сами по себе в силу полезности своих качеств.
Для обращения к базе данных используются запросы, написанные на языке SQL. Запрос - команда, которую вы даете вашей программе базы данных, и которая сообщает ей, что нужно вывести определенную информацию из таблиц в память. Эта информация обычно посылается непосредственно на экран компьютера или терминала, которым вы пользуетесь, хотя в большинстве случаев ее можно также послать принтеру, сохранить в файле (как объект в памяти компьютера) или представить как вводную информацию для другой команды или процесса.
Несмотря на большое количество разновидностей этого языка, существующих в настоящее время, логика его работы проста.
Достаточно освоить основные команды хотя бы в одной из его версий, чтобы впоследствии без труда разобраться в любой другой его реализации.
Microsoft Query Analyzer, входящий в комплект Microsoft SQL Server 2000, - отличный инструмент для изучения языка SQL. Если у вас установлен этот пакет, то можно приступать к работе. Запускаем Query Analyzer и в появившемся окне указываем следующие параметры (рис. 1.32).
Рис. 1.32. Задание параметров подключения
Мы задействуем подключение к серверу, находящемуся на нашем компьютере, - поэтому указываем имя сервера (local) (можно также использовать знак точки - ( . )). В других случаях - для подключения по локальной сети, например, - используйте кнопку обзора для поиска нужного сервера. Установка галочки "Запускать SQL сервер в случае его остановки (Start SQL Server if it is stopped)" позволяет поддерживать бесперебойную связь с сервером. Параметр "Connect using" задает аутентификацию при подключении к серверу - при выбранном значении "Windows authentification", нет надобности вводить имя пользователя и пароль.
Если вы все сделали правильно, то появляется главное окно программы, содержащее чистый бланк для записи SQL-запросов. Добавим окно, в котором мы будем просматривать содержимое базы данных: пункт меню Tools / Object Browser / (Show/hide) или клавиша F8. Нам также потребуется окно для отображения результатов запросов - Window / /Show Results Pane (Ctrl+R). В результате получим следующее (рис. 1.33).
увеличить изображение
Рис. 1.33. Главное окно программы
Первое, что мы видим в окне Object Browser, - имя компьютера и связанного с ним сервера. На рисунке это 7EA2B2F6068D473. Это имя формируется, когда мы устанавливаем операционную систему Windows. Далее располагается древовидная структура - содержимое пакета SQL Server 2000, состоящего из баз данных master, model, msdb, NorthwindCS и tempdb. В отличие от других баз данных, обеспечивающих работу самой программы, NorthwindCS является специальной учебной базой.
С ней мы и будем работать. На панели инструментов из выпадающего списка выбираем базу, для подключения -
Итак, напишем первый запрос7) и нажмем клавишу F5 (пункт меню Query - Execute):
select * from Customers;
В результате возвращаются все записи из таблицы Customers базы данных NorthwindCS. Для просмотра содержимого базы данных используем Object Browser, щелкая на знак (+) возле соответствующего объекта. Переключившись на вкладку
(91 row(s) affected)
Главное окно программы принимает вид (рис. 1.34)
Рис. 1.34. Запрос извлек таблицу Customers
Вы можете менять вид данных, отображаемых на панели результатов: пункт меню Query / Results in Text (Ctrl+T) - результат в виде текста, Results in Grid (Ctrl+D) - в виде таблицы (по умолчанию), Results to File (Ctrl+Shift+F) - сохранение результата в виде файла в собственном формате программы - *.rpt.
Таблица Customers состоит из следующих полей (рис. 1.35):
Рис. 1.35. Содержание таблицы Customers
Для извлечения не всей таблицы, а столбцов СustomerID и Address, напишем запрос:
select CustomerID, Address from Customers;
Результатом будет (рис. 1.36)
Рис. 1.36. Извлечение столбцов CustomerID и Address
Если мы сделаем ошибку и укажем поле, которого нет в таблице Customers, например AddressID, в окне результатов на вкладке Messages появится соответствующее предупреждение:
Server: Msg 207, Level 16, State 1, Line 3 Invalid column name 'AddressID'.
Для вывода определенного количества записей используем запрос (рис. 1.37)
select top 5 CustomerID from Customers;
Рис. 1.37. Извлечение нескольких записей
Извлекаются первые пять записей поля CustomerID, расположенные в самой таблице Customers в алфавитном порядке, - этот запрос не производит сортировки!
Вводя оператор percent, получаем указанный процент записей от общего числа:
select top 5 percent CustomerID from Customers;
В данном случае результат будет в точности таким же, как и при использовании запроса без оператора percent. В чем же дело? Общее число записей поля CustomerID таблицы Customers - 91 (в этом нетрудно убедиться, введя запрос select * from Customers; и переключившись на вкладку
Для вывода записей, отвечающих заданному условию, используем оператор where:
select * from Products where UnitPrice > 100;
Этот запрос возвращает все записи из таблицы Products в которых Столбец (поле) UnitPrice имеет значение, большее 100 (рис. 1.38):
Рис. 1.38. Отбор записей со всеми полями по заданному значению
Можно группировать операторы так:
select ProductName,UnitPrice from Products where UnitPrice > 100;
Здесь извлекаются поля ProductName и UnitPrice из таблицы Products где поле UnitPrice > 100 (рис. 1.39):
Рис. 1.39. Отбор записей с несколькими полями по заданному значению
Оператор where поддерживает работу со знаками <, >, >=, <=.
Точную выборку осуществляет оператор in, в следующем примере извлекаются лишь те записи, в которых значение поля UnitPrice в точности равно либо 10, либо 15, либо 23 (рис. 1.40):
select ProductName,UnitPrice from Products where UnitPrice in (10,15,23);
Рис. 1.40. Отбор записей по точному совпадению значений поля UnitPrice
Выборка для значений, лежащих в указанном интервале, осуществляется оператором between первое _значение and второе_значение (рис. 1.41):
select ProductName,UnitPrice from Products where UnitPrice between 10 and 13;
Рис. 1.41. Отбор записей по значениям в указанном интервале
Элементы работы с СУБД Microsoft Access
Рассмотрим применение концепции реляционных баз данных на практике. Представим себе деятельность туристической фирмы. Очевидно, что для работы необходимо вести какие-то записи. Их можно производить в обычной бумажной тетради, но со временем поиск нужных записей и финансовая отчетность будет представлять довольно рутинную, длительную работу. Итак, необходимо создать электронную базу данных, которая должна значительно облегчить ведение всей статистики. Запускаем Microsoft Access 2003, в главном меню переходим "Файл - Создать", на появившейся панели "Создание файла" выбираем "Новая база данных". Далее указываем директорию, где будет сохранен файл базы данных, называем его "BDTur_firm.mdb" и нажимаем кнопку "Создать". В отличие от других офисных приложений, мы не можем начать работу с базой данных, предварительно не сохранив ее файл. В результате проделанных действий появляется окно базы данных (рис. 1.2).
Рис. 1.2. Окно базы данных
В заголовке окна базы данных указывается ее формат по умолчанию - Access 2000, обеспечивающий совместимость с программами Microsoft Access 2000 и 97. Если совместимость не важна и требуется получить все преимущества новых версий Access, можно преобразовать базу данных, для чего выбираем пункт меню "Сервис \ Служебные программы \ Преобразовать базу данных \ В формат Access 2002-2003". Мы продолжим работать с форматом по умолчанию.
Если вы закроете приложение, а затем снова продолжите работу, при открытии файла базы данных всякий раз будет появляться предупреждение системы безопасности. Дело в том, что в базе данных Microsoft Access могут храниться макросы, которые при открытии могут выполнить вредоносные действия. Система безопасности защищает, таким образом, от деструктивных действий, возможных при открытии чужого файла базы данных. При работе над своей собственной базой данных эти предупреждения излишни. Для их отключения в главном меню программы выбираем "Сервис \ Макрос \ Безопасность" и на вкладке "Уровни безопасности" отмечаем переключатель "Низкая".
Далее в появившемся диалоговом окне разрешаем выполнение небезопасных выражений. Теперь предупреждения появляться не будут, а для блокирования макросов при открытии чужой базы данных достаточно удерживать клавишу Shift.
Итак, приступим, наконец, к созданию базы данных. Самое сложное при этом - выстроить логическую структуру таблиц, избежать наличия повторяющихся записей, а также обеспечить удобство пользования с учетом специфики работы. В окне базы данных дважды щелкнем на надписи "Создание базы данных в режиме конструктора". В появившемся окне "Таблица1: таблица" предстоит определить названия полей, которые и станут заголовками в этой таблице. Введем следующие названия полей (рис. 1.3).
Рис. 1.3. Заполнение полей таблицы
При вводе названия поля по умолчанию определяется для него тип данных "текстовый". Для изменения типа следует выбрать нужное значение из выпадающего списка (рис. 1.4). Описания возможных типов данных Microsoft Access приводятся в таблице 1.1.
Рис. 1.4. Определение типа данных поля
| Текстовый | Текст или комбинация текста и чисел, например, адреса, а также числа, не требующие вычислений, например, номера телефонов, инвентарные номера или почтовые индексы. Сохраняет до 255 знаков. Свойство "Размер поля" (FieldSize) определяет максимальное количество знаков, которые можно ввести в поле |
| Поле МЕМО | Предназначено для ввода текстовой информации, по объему превышающей 255 символов. Такое поле может содержать до 65 535 символов. Этот тип данных отличается от типа Текстовый (Text) тем, что в таблице даются не сами данные, а ссылки на блоки данных, хранящиеся отдельно. За счет этого ускоряется обработка таблиц (сортировка, поиск и т. п.). Поле типа MEMO не может быть ключевым или проиндексированным |
| Числовой | Данные, используемые для математических вычислений, за исключением финансовых расчетов (для них следует использовать тип "Денежный").Сохраняет 1, 2, 4 или 8 байтов. Конкретный тип числового поля определяется значением свойства Размер поля (FieldSize) |
| Дата/время | Значения дат и времени. Сохраняет 8 байтов |
| Денежный | Используется для денежных значений и для предотвращения округления во время вычислений. Сохраняет 8 байтов |
| Счетчик | Автоматическая вставка уникальных последовательных (увеличивающихся на 1) или случайных чисел при добавлении записи. Сохраняет 4 байта |
| Логический | Данные, принимающие только одно из двух возможных значений, таких, как "Да/Нет", "Истина/Ложь", "Вкл/Выкл". Значения Null не допускаются. Сохраняет 1 бит |
| Поле объекта OLE | Объекты OLE1) (такие, как документы Microsoft Word, электронные таблицы Microsoft Excel, рисунки, звукозапись или другие данные в двоичном формате), созданные в других программах, использующих протокол OLE. Сохраняет до 1 Гигабайта (ограничивается объемом диска) |
| Гиперссылка | Гиперссылки. Гиперссылка может указывать на расположение файла на локальном компьютере либо адреса URL. Сохраняет до 64 000 знаков |
| Мастер подстановок | Создает поле, позволяющее выбрать значение из другой таблицы или из списка значений, используя поле со списком. При выборе данного параметра в списке типов данных запускается мастер для автоматического определения этого поля. Обычно сохраняет 4 байта |
Применение определенного типа данных позволяет избежать ошибок в работе с таблицами - в поле с форматом даты невозможно ввести значение суммы, а в поле с денежным форматом невозможно ввести дату. Кроме того, для различных данных требуется разный объем памяти, и резервирование полей с однородным составом позволяет значительно уменьшить общий размер базы данных.
Около поля "Код туриста" на рис. 1.2 находится изображение ключа. Это означает, что указанное поле будет первичным ключом для записей в таблице. Для того чтобы сделать данное поле ключевым, следует выделить его, щелкнуть правой кнопкой, а затем в появившемся контекстном меню выбрать "Ключевое поле" (рис. 1.5).
Рис. 1.5. Установка первичного ключа
Первая таблица готова. Сохраняем ее, называя "Туристы", и закрываем. Аналогичным образом создаем таблицы "Информация о туристах", "Туры", "Сезоны", "Путевки" и "Оплата" (таблица 1.2).
| 1 | Туристы | Код туриста | Счетчик |  | Содержит основные сведения о туристе |
| Фамилия | Текстовый | ||||
| Имя | Текстовый | ||||
| Отчество | Текстовый | ||||
| 2 | Информация о туристах | Код туриста | Числовой |  | Содержит дополнительные сведения о туристе, которые были вынесены в отдельную таблицу - для избегания повторяющихся записей |
| Серия паспорта | Текстовый | ||||
| Город | Текстовый | ||||
| Страна | Текстовый | ||||
| Телефон | Текстовый | ||||
| Индекс | Числовой | ||||
| 3 | Туры | Код тура | Счетчик |  | Содержит общие сведения о странах для туров |
| Название | Текстовый | ||||
| Цена | Денежный | ||||
| Информация | Поле MEMO | ||||
| 4 | Сезоны | Код сезона | Счетчик |  | Содержит сведения о сезонах - некоторые туры доступны лишь в определенный период |
| Код тура | Числовой | ||||
| Дата начала | Дата/время | ||||
| Дата конца | Дата/время | ||||
| Сезон закрыт | Логический | ||||
| Количество мест | Числовой | ||||
| 5 | Путевки | Код путевки | Числовой |  | Содержит сведения о путевках, реализованных туристам. |
| Код туриста | Числовой | ||||
| Код сезона | Числовой | ||||
| 6 | Оплата | Код оплаты | Счетчик |  | Содержит сведения об оплате за путевки |
| Код путевки | Числовой | ||||
| Дата оплаты | Дата/время | ||||
| Сумма | Денежный |
Теперь в окне базы данных есть несколько таблиц. Обратите внимание на наличие в нескольких таблицах одинаковых полей, например, в таблицах "Туристы" и "Информация о туристах" поле "Код туриста". Их назначение станет ясно немного позже. Приступим к связыванию таблиц. В окне базы данных щелкаем правой кнопкой мыши на чистом месте и в появившемся меню выбираем "Схема данных" (или в главном меню выбираем "Сервис \ Схема данных"). В появившемся окне "Добавление таблицы" выделяем все таблицы и нажимаем кнопки "Добавить" и "Закрыть". В окне "Схема данных" добавленные таблицы можно перетаскивать, располагая удобным способом. Выделив поле "Код туриста" в таблице "Туристы" и не отпуская левой кнопки мыши, перетащим его на поле "Код туриста" таблицы "Информация о туристах" (рис. 1.6).
Рис. 1.6. Создание связи между таблицами
После отпускания кнопки мыши появляется окно "Изменение связей", в котором отмечаем галочки "Обеспечение целостности данных", "Каскадное обновление связанных полей"" и "Каскадное удаление связанных записей", а затем нажимаем кнопку "Создать" (рис. 1.7):
Рис. 1.7. Определение параметров связи
Определение этих параметров позволит при изменении записи в одной таблице автоматически обновлять связанные записи в других таблицах. В окне появилась связь между таблицами, которая была создана Microsoft Access (рис. 1.8):
Рис. 1.8. Связь между таблицами
Эта связь была создана автоматически - так происходит тогда, когда две таблицы имеют одинаковые названия связанных полей и согласованные типы данных, причем хотя бы в одной из таблиц связанное поле является ключевым. Под согласованным типом данных понимается следующее: если ключевое поле имеет тип данных "Счетчик", то соответствующее ему поле в другой таблице должно иметь тип "Числовой". В остальных случаях типы данных должны просто совпадать.
Около полей "Код туриста" обеих таблиц на связи расположено изображение единицы, указывающее на принадлежность связи к отношению "один-к-одному".
Это означает, что одной записи в таблице "Туристы" будет соответствовать одна запись в таблице "Информация о туристах". Существуют также другие типы отношений - "один-ко-многим" и "многие-ко-многим". Отношение "один-ко-многим" далее появится у нас между таблицами "Информация о туристах" и "Путевки" - один турист может приобрести несколько путевок, что и находит логическое отражение в связи между таблицами. Другой возможный тип - "многие-ко-многим", его примером может служить связь между таблицами с преподавателями и предметами: один преподаватель может вести несколько предметов, но и один предмет могут вести несколько преподавателей. Для того чтобы рассмотреть практические примеры типов отношений, полезно открыть учебную базу данных "Борей 2)", входящую в комплект Microsoft Access 2003 (Справка \ Примеры баз данных \ Учебная база данных "Борей"), и отобразить схему базы данных.
В результате определения связей между таблицами должна получиться следующая схема базы данных (рис. 1.9):
увеличить изображение
Рис. 1.9. Схема данных базы BDTur_firm.mdb
Для дальнейшей работы с базой данных заполним ее вымышленными данными - достаточно будет создать по пять записей в каждой таблице. В окне базы данных дважды щелкнем на таблице "Туристы". Щелкая на значок "+" этой записи, можно отобразить и непосредственно вносить изменения в дочерних таблицах. Дочерние таблицы образуют группу, определенную в структуре базы данных. На рис. 1.10 приведена раскрытая группа таблиц - "Туристы" - "Информация о туристах" - "Путевки" - "Оплата" и соответствующая ветвь в структуре базы данных.
увеличить изображение
Рис. 1.10. Вложенная группа таблиц "Туристы" - "Информация о туристах" - "Путевки" - "Оплата" и соответствующая ветвь в структуре базы данных
Таблица "Туры" также содержит вложенную группу дочерних таблиц "Туры" - "Сезоны" - "Путевки" - "Оплата" (рис. 1.11).
увеличить изображение
Рис. 1.11. Вложенная группа таблиц "Туры" - "Сезоны" - "Путевки" - "Оплата" и соответствующая ветвь в структуре базы данных
При взгляде на ветви, приведенные на рис. 1.9 и рис. 1.10, появляется вопрос: почему в таблицах не возникает дочерних групп "Туристы" - "Информация о туристах" - "Путевки" - "Сезоны" - "Туры" (см рис. 1.9) или "Туры" - "Сезоны" - "Путевки" - "Информация о туристах" - "Туристы" (см. рис. 1.10)? Дело в том, что группы образуются при одностороннем переходе от таблицы со связью "один-ко-многим" или "один-к-одному", который условно можно изобразить так (таблица 1.3).
| Можно | 1 | "Туристы"1 - 1"Информация о туристах"1 -  |
| 2 | "Туры" 1 - | |
| Нельзя | 1 | "Туристы" 1 - |
| 2 | "Туры" 1 - | |
| А так можно | 1 | "Туры" 1 - |
| 2 | "Туристы"1 - |
Вносить данные можно сразу в группу таблиц, одновременно просматривая связанные записи в разных таблицах.
Мы создали базу данных Microsoft Access, которая может применяться для управления туристической фирмой. Непосредственное использование таблиц - простое их открытие и внесение данных - встречается крайне редко: отсутствие интерфейса, а главное - отсутствие защиты от случайных ошибок делает всю работу весьма ненадежной. Тем не менее саму базу данных можно считать готовой серверной частью двухуровневого приложения "клиент-сервер". СУБД Microsoft Access содержит все средства для эффективной разработки клиентской части приложения (форм, отчетов, страниц), рассмотрение которых, однако, выходит за рамки этого курса.
В программном обеспечении к курсу вы найдете файл базы данных Microsoft Access BDTur_firm.mdb (Code\Glava1\BDTur_firm.mdb).
Команды изменения языка DML
Значения могут быть помещены и удалены из полей тремя командами языка DML (Язык Манипулирования Данными):
insert (вставить),update (изменить),delete (удалить).
Команда insert имеет свои особенности:
При указании значений конкретных полей вместо использования каких-либо значений можно применить ключевое слово DEFAULT Вставка пустой строки приводит к добавлению пробела ' ', а не значения NULL Строки и даты задаются в апострофах.Не задавайте данные для столбца, имеющего свойство IDENTITYМожно задать NULL явно, можно задать DEFAULT.
Примеры:
insert into ClientInfo (FirstName, LastName, Address, Phone) values('Petr','Petrov','Chehova 13','1234567');
Однократное выполнение этого запроса (нажатие клавиши F5 один раз) приводит к добавлению одной записи. Добавляем еще несколько записей, изменяя значения value:
insert into ClientInfo (FirstName, LastName, Address, Phone) values('Ivan',Ivanov,'Naberejnaya 13','1234568');
insert into ClientInfo (FirstName, LastName, Address, Phone) values(null,'Sidorov','Naberejnaya 25','1234569');
Извлечем все записи созданной таблицы (рис. 1.45).
select * from ClientInfo;
Рис. 1.45. Все записи таблицы ClientInfo
Убедимся в том, что третья запись поля FirstName действительно содержит неопределенное значение null (а не строку NULL), c помощью запроса (рис. 1.46)
select * from ClientInfo where FirstName is null;
Рис. 1.46. Таблица ClientInfo действительно содержит запись со значением поля First Name "NULL"
Команда update позволяет изменять заданные значения записей:
update ClientInfo set FirstName = 'Andrey' where FirstName = 'Petr';
В этом случае в первой записи поля FirstName значение Petr изменится на Andrey (рис. 1.47):
Рис. 1.47. Изменение одной записи
Если не указывать значение, которое необходимо изменить, команда update затронет все записи (рис. 1.48).
update ClientInfo set FirstName = 'Andrey';
Рис. 1.48. Изменение всех записей
Команда delete позволяет изменять заданные значения записей.
delete from ClientInfo where LastName like 'Petrov';
Результатом этого запроса будет удаление первой записи из таблицы ClientInfo.
delete from ClientInfo;
Этот запрос удаляет все записи из таблицы, но не удаляет саму таблицу (рис. 1.49):
Рис. 1.49. Все записи удалены, но сама таблица осталась!
Запросы с командами insert, update и delete могут содержать в себе все прочие конструкции языка SQL.
 |
|
|
OLE - сокращение от Object Linking and Embedding (связывание и внедрение объектов) - применяется для обозначения технологии Microsoft, используемой для создания составных документов внедрением и связыванием.
2)
Слово "Борей" означает "Северный ветер". В комплект поставки Microsoft SQL Server и Microsoft Access традиционно входит база данных "Northwind", предназначенная специально для учебных целей. В переводе с англ. "Northwind" и есть северный ветер.
3)
SQL в оригинале произносится как "seequel". Впрочем, встречается и адаптированное произношение "эс-ку-эл(ь)".
4)
Если вы используете другую версию пакета Microsoft SQL, данные таблицы могут отличаться. Для определения соответствия импортируйте базу данных Types.mdb в свой пакет. База данных Types.mdb находится на диске, прилагаемом к книге (Code\Glava1 Types.mdb).
5)
Транзакция - серия запросов к базе, манипулирующая данными. Транзакцию, в отличие от обычного запроса, можно принять (и тогда вся серия запросов вступит в силу) или отклонить. Далее мы рассмотрим подробно это понятие.
6)
Здесь мы познакомимся только лишь с некоторыми понятиями языка SQL. Для полного изучения этого языка обращайтесь к специализированным руководствам.
7)
Вы можете также писать запросы, используя заглавные буквы, - SQL нечувствителен к регистру.
8)
Перед выполнением этого запроса убедитесь в том, что вы работаете с базой данных NorthwindCS. Или используйте команду use NorthwindCS.
Оператор сравнения like
Оператор сравнения like нужен для поиска записей по заданному шаблону. Это одна из наиболее часто встречаемых задач - например, поиск клиента с известной фамилией в базе данных.
Предположим, что в таблице Customers требуется найти записи клиентов с фамилиями, начинающимися на букву "C" , и содержащие поля CustomerID, ContactName и Address:
select CustomerID, ContactName, Address from Customers where ContactName like 'C%';
Результатом этого запроса будет таблица (рис. 1.43)
Рис. 1.43. Запрос с оператором like
Оператор like содержит шаблоны, позволяющие получать различные результаты (таблица 1.5).
| like '5[%]' | 5% |
| like '[_]n' | _n |
| like '[a-cdf]' | a, b, c, d, или f |
| like '[-acdf]' | -, a, c, d, или f |
| like '[ [ ]' | [ |
| like ']' | ] |
| like 'abc[_]d%' | abc_d и abc_de |
| like 'abc[def]' | abcd, abce, и abcf |
Определение структуры базы данных в SQL Server Enterprise Manager
Для просмотра структуры таблицы выделяем нужную таблицу, щелкаем правой кнопкой мыши и в появившемся контекстном меню выбираем Design Table (рис. 1.19, А) . Аналогично, для просмотра содержимого в контекстном меню выбираем "Open Table \ Return all rows" ("Открыть таблицу \ Извлечь все записи") (рис. 1.19, Б).
увеличить изображение
Рис. 1.19. Просмотр структуры таблицы "Информация о туристах" (А) и ее содержимого (Б)
При просмотре импортированной таблицы в режиме дизайна обратите внимание на преобразование типов данных, - например, тип данных Microsoft Access "Текстовый" (Поле "Город" в таблице "Информация о туристах") был преобразован в тип данных nvarchar. Соотношения между конвертируемыми типами приведены в таблице 1.4:
| 1 | Текстовый | nvarchar | Тип данных для хранения текста до 4000 символов |
| 2 | Поле МЕМО | ntext | Тип данных для хранения символов в кодировке Unicode до 2^30 - 1 (1 073 741 823) символов |
| 3 | Числовой | int | Численные значения (целые) в диапазоне от -2^31 (-2 147 483 648) до 2^31 - 1 (+2 147 483 647) |
| 4 | Дата/время | smalldatetime | Дата и время от 1 января 1900 г. до 6 июня 2079 года с точностью до одной минуты |
| 5 | Денежный | money | Денежный тип данных значения которого лежат в диапазоне от -2^63 (-922 337 203 685 477.5808) до 2^63 - 1 (+922 337 203 685 477.5807), с точностью до одной десятитысячной |
| 6 | Счетчик | int | См. 3 |
| 7 | Логический | bit | Переменная, способная принимать только два значения - 0 или 1 |
| 8 | Поле объекта OLE | image | Переменная для хранения массива байтов от 0 до 231-1 (2 147 483 647) байт |
| 9 | Гиперссылка | ntext | См. 2 |
| 10 | Мастер подстановок | nvarchar | См. 1 |
Сведения о других типах данных Microsoft SQL можно найти в указателе справки SQL Server Enterprise Manager, щелкнув на кнопке
Чтобы установить связи, нам снова предстоит определить первичные ключи для записей в каждой таблице.
Открываем последовательно каждую таблицу в режиме структуры (Design Table), выделяя соответствующие поля, выбираем в контекстном меню Primary Key или нажимаем на кнопку
Рис. 1.20. Определение первичного ключа
Установив первичные ключи для всех таблиц (см. табл. 1.2), переходим к построению схемы базы. Выбираем пункт Diagrams, щелкаем на нем правой кнопкой и выбираем пункт New Database Diagram. В появившемся мастере "Create Database Diagram Wizard" нажимаем кнопку "Далее". В следующем шаге мастера из общего списка, включающего в себя системные таблицы, выбираем пользовательские таблицы (рис. 1.21):
Рис. 1.21. Добавление пользовательских таблиц
Обратите внимание на галочку "Add related tables automatically" ("Добавлять связанные таблицы автоматически") - при выборе этой галочки в выбранные таблицы будут включаться все связанные ранее таблицы. У нас таких таблиц пока нет, поэтому нажимаем далее и завершаем работу мастера. Определение связей практически не отличается от аналогичного действия в Microsoft Access - следует "ухватиться" за одно поле и "перетащить" его на второе. При определении связи появляется окно "Create Relationship" (рис. 1.22), аналогичное окну Microsoft Access "Изменение связей" (сравните рис. 1.7).
Рис. 1.22. Определение связи между таблицами
В окне "Create Relationship" можно задать название связи в поле "Relationship name" (на рис. 1.22 приведено название по умолчанию), а также обеспечить каскадное обновление связанных полей, пометив галочку "Cascade Update Related Fields" и каскадное удаление связанных полей, пометив галочку "Cascade Delete Related Records". В этом окне указывается поле первичного ключа - "Primary key table", - в роли которого по-прежнему выступает столбец "Код туриста" таблицы "Туристы". В таблице "Информация о туристах" поле "Код туриста" выступает в качестве так называемого внешнего или вторичного ключа (Foreign key table).
Внешний ключ - это одно или несколько полей (столбцов) в таблице, содержащих ссылку на поле или поля первичного ключа в другой таблице. Внешний ключ определяет способ объединения таблиц.
После восстановления связей (см. рис. 1.9) схема данных будет выглядеть следующим образом (рис. 1.23):
увеличить изображение
Рис. 1.23. Схема базы данных BDTur_firmSQL
В отличие от схемы данных Microsoft Access, здесь линии, отображающие связи по умолчанию, не привязываются графически к первичным и вторичным полям. Однако при щелчке правой кнопкой на любой связи и последующем выборе пункта контекстного меню "Properties" открывается диалоговое окно, в котором сразу видны все отношения. На панели инструментов расположена кнопка "Show_"
увеличить изображение
Рис. 1.24. Схема базы данных в режиме "Keys"
Завершив работу со схемой данных, сохраняем ее. В окне "Diagrams" может быть создано несколько различных схем одной и той же базы данных (рис. 1.25):
Рис. 1.25. SQL Server Enterprise Manager поддерживает много схем базы данных
Перенос файла Microsoft SQL на другой компьютер
В большинстве случаев вам придется разрабатывать приложения, использующие в качестве базы данных Microsoft SQL Server. Наиболее рациональным решением является разработка базы данных в формате Microsoft SQL на рабочем компьютере с установленной локальной версией Microsoft SQL Server. При сдаче проекта заказчику возникает необходимость переноса базы данных с локального компьютера. Как мы уже отмечали, файлы баз данных по умолчанию размещаются в каталоге C:\Program Files\Microsoft SQL Server\MSSQL\Data. Для переноса на другой компьютер нам потребуется скопировать два файла - саму базу данных BDTur_firmSQL.mdf и файл отчетов о транзакциях5) BDTur_firmSQL.ldf. Однако при попытке их простого копирования появляется ошибка при копировании - подобно си стемным файлам, они недоступны из операционной системы. Можно, конечно, скопировать нужные файлы из-под DOS, но к счастью, программа SQL Server Enterprise Manager предоставляет удобный интерфейс для этой задачи. Выделяем BDTur_firmSQL в окне программы и в контекстном меню выбираем "Все задачи \ Detach Database (Отсоединить базу)" (рис. 1.30).
Рис. 1.30. Отсоединение выбранной базы данных от сервера
Появляется диалоговое окно "Detach Database BDTur_firmSQL" в котором выводится количество текущих соединений к данной базе. Подтверждаем отсоединение, нажимая кнопку "ОК", - и база отсоединена. Теперь можно скопировать нужные файлы непосредственно.
После переноса файлов на другой компьютер желательно их расположить в папке по умолчанию. Для присоединения базы запускаем SQL Server Enterprise Manager на компьютере клиента, выделяем папку "Databases" и в контекстном меню выбираем "Все задачи \ Attach Database (Присоединить базу данных)" (рис. 1.31).
Рис. 1.31. Присоединение базы данных
В появившемся окне указываем расположение файла базы данных BDTur_firmSQL.mdf - файл отчетов присоединится автоматически, если он находится в той же самой директории, - и нажимаем "ОК". Присоединившаяся база данных немедленно отображается в папке "Databases".
В программном обеспечении к курсу вы найдете файлы, готовые для присоединения (Code\Glava1\ BDTur_firmSQL.mdf и BDTur_firmSQL.ldf).
Преобразование базы данных Microsoft Access в формат Microsoft SQL
Программа Microsoft Access с самого начала создавалась как средство управления и проектирования баз данных для офисной работы и задач небольших организаций. Ограничение максимального количества одновременно работающих пользователей (всего 255) делает невозможным использование базы данных даже для управления среднего по размерам Интернет-магазина или форума. Для обслуживания крупных проектов используются более мощные системы, например, Microsoft SQL3) Server. Рассмотрим вопрос преобразования базы данных Miscrosoft Access в формат Microsoft SQL.
После установки пакета Microsoft SQL1 Server 2000 запускаем программу администрирования SQL Server Enterprise Manager ("Пуск \ Все программы \ Microsoft SQL Server \ Enterprise Manager"). Окно консоли управления, в которую загружается программа, разделяется на два окна, подобно проводнику Windows, в которые выводится содержимое сервера (рис. 1.12):
Рис. 1.12. Содержимое локального сервера в программе SQL Server Enterprise Manager
На рис. 1.12 выбрана папка "Databases", и поэтому в правом окне представлено семь баз данных - служебные: master, model, msdb, tempdb и учебные Northwind, NorthwindCS, pubs. Учебные базы данных не являются обязательными для функционирования сервера и предназначены лишь для изучения.
Для определения расположения файла базы данных, например Northwind, дважды щелкаем на нее и в появившемся окне Northwind Properties переходим на вкладку Data Files. По умолчанию при установке пакета определяется каталог C:\Program Files\Microsoft SQL Server\MSSQL\ Data (рис. 1.13):
Рис. 1.13. Расположение файла базы данных Northwind
Приступим к импорту базы данных Microsoft Access BDTur_firm.mdb. Щелкаем правой кнопкой на папке Databases и в появившемся меню выбираем "Все задачи \ Import Data" (рис. 1.14).
Рис. 1.14. Начало импорта базы данных
В появившемся мастере "Data Transformation Services Import/Export Wizard (DTS Import/Export Wizard)" нажимаем кнопку "Далее".
В следующем шаге мастера из выпадающего списка "Data Source" выбираем источник данных - Microsoft Access, затем указываем путь к файлу (нажимаем на кнопку (_)), а поля "Username" и "Password" оставляем пустыми (рис. 1.15). Нажимаем кнопку "Далее".
Рис. 1.15. Определение источника данных - Microsoft Access
В следующем шаге мастера предстоит определить, куда будет осуществляться импорт таблиц. В списке "Database" отображаются все имеющиеся базы данных на сервере - можно выбрать, например, базу данных pubs, однако выберем создание новой базы данных (рис. 1.16):
Рис. 1.16. Новая база данных в списке "Database"
В появившемся окне "Create Database" вводим имя базы данных - BDTur_firmSQL, также можно определить размер базы данных (Data file size) и размер файла отчетов (Log file size). Оставляем значения по умолчанию - размер базы данных BDTur_firm.mdb составляет всего 512 Кб - и нажимаем "OK" (рис. 1.16):
Рис. 1.17. Создание новой базы данных BDTur_firmSQL
Нажимаем кнопку "Далее", в шаге мастера "Specify Table Copy or Query" оставляем значение, предлагаемое по умолчанию, - "Copy table(s) and view(s) from the source databases" (копировать таблицу(ы) и представление(я) из источника данных) и снова нажимаем "Далее". В шаге мастера "Select Sources Tables and Views" выделяем все таблицы, нажав на кнопку "Select All" (рис. 1.18). Здесь же можно просмотреть содержимое выбранной таблицы (первые сто записей), нажав на кнопку "Preview_".
Рис. 1.18. Выбор таблиц для копирования
В шаге мастера "Save, schedule and replicate package" оставляем вариант немедленного запуска (Run immediately) и нажимаем далее. В последнем шаге мастера нажимаем кнопку "Готово". После подтверждения об успешном завершении задачи можно перезапустить SQL Server Enterprise Manager - в списке баз данных появится база данных BDTur_firmSQL, файл которой располагается в общем каталоге C:\Program Files\Microsoft SQL Server\MSSQL\Data.
Преобразование базы данных Microsoft SQL в формат Microsoft Access
Преобразование базы данных Microsoft SQL в один из распространенных форматов представляет собой довольно простую задачу. Преобразуем базу BDTur_firmSQL в формат Microsoft Access. Для этого выделяем ее, щелкаем правой кнопкой мыши и в появившемся меню выбираем Все задачи \ Export Data (см. рис. 1.14). Появляется уже знакомый нам мастер "Data Transformation Services Import/Export Wizard (DTS Import/Export Wizard)", в котором мы снова нажимаем кнопку "Далее". В шаге мастера "Choose a Data Source" ("Выбор источника данных") уже установлены верные значения: источник данных ("Data Source") - Microsoft OLE DB Provider for SQL Server, сервер - локальный ("Server"), а база данных ("Database") - BDTur_firmSQL (рис. 1.26):
Рис. 1.26. Выбор источника при экспорте базы данных
В следующем шаге мастера выбираем тип данных Microsoft Access ("destination") и указываем директорию, где предварительно создан файл Microsoft Access (я назвал его ExportBDTur_firm.mdb) (рис. 1.27).
Рис. 1.27. Определение файла для экспорта данных
В шаге "Specify Table Copy or Query" оставляем предложенное по умолчанию значение "Copy table(s) and view(s) from the source database" и нажимаем кнопку "Далее". В шаге "Select Source Tables and Views" выделяем все таблицы и переходим дальше. Выбираем тип "Запустить сейчас" ("Run immediately") и подтверждаем выполнение действий. Переходим к файлу ExportBDTur_firm.mdb - все таблицы были экспортированы, однако без схемы данных. Ее можно восстановить вручную.
В программном обеспечении к курсу вы найдете файл базы данных Microsoft Access ExportBDTur_firm.mdb (Code\Glava1\ ExportBDTur_firm.mdb).
Создание базы данных в SQL Server Enterprise Manager
Создать новую базу данных можно несколькими способами: нажать кнопку
Рис. 1.28. Свойства создаваемой базы данных
После этого на вкладках "Data Files" и "Transaction Log" доступны определения свойств файла базы данных и файла отчетов о транзакциях. Принимаем значения, предложенные по умолчанию, и нажимаем кнопку "ОК". В списке появляется новая база, раскрываем ее группу, выделяем "Tables" (таблицы) и в контекстном меню выбираем "New Table". Обратите внимание на созданные автоматически 20 таблиц - это системные объекты, необходимые для функционирования данной базы. Создание новой таблицы (рис. 1.29, А) очень похоже на аналогичный процесс в Microsoft Access, поэтому его мы рассматривать не будем. В отдельную таблицу можно также импортировать данные из файла - при выборе пункта меню "Все задачи \ Import Data" появляется уже знакомый нам мастер "Data Transformation Services Import/Export Wizard" (рис. 1.29, Б).
увеличить изображение
Рис. 1.29. Создание новой таблицы (А) или импорт данных (Б) в новой базе данных
При заполнении полей таблицы на первых порах достаточно сложно определить, какой тип данных использовать для заданного поля. Изучение базы данных Northwind, NorthwindCS, pubs а также сведений о самих типах данных, которые можно найти в справке, позволит разобраться с этой задачей.
Создание таблицы с помощью запросов
Вплоть до этого места мы выполняли команды по извлечению таблиц и данных из таблиц, полагая, что сами таблицы были созданы кем-то до нас. Это, действительно, наиболее реальная ситуация, когда небольшое количество людей создает таблицы, которые затем используются другими людьми.
Тем не менее специальная область SQL, называемая DDL (Язык Определения Данных), специально работает с созданием объектов данных.
Таблицы создаются командой create table. Эта команда создает пустую таблицу - таблицу без строк. Команда create table в основном определяет имя таблицы, в виде описания набора имен столбцов, указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь по крайней мере один столбец.
Синтаксис команды create table8):
create table ClientInfo ( FirstName varchar(20), LastName varchar(20), Address varchar(20), Phone varchar(15) );
Тип varchar предназначен для хранения символов не в кодировке Unicode. Число, указываемое в скобках, определяет максимальный размер поля и может принимать значение от 1 до 8000. Если введенное значение поля меньше зарезервированного, при сохранении будет выделяться количество памяти, равное длине значения. После выполнения этого запроса в окне "Messages" появляется сообщение
The command(s) completed successfully.
Перезапустите Query Analyzer. В базе данных NorthwindCS появилась созданная нами таблица (рис. 1.44):
Рис. 1.44. Созданная таблица находится в базе NorthwindCS
Итак, мы создали таблицу, состоящую из четырех полей типа varchar, причем для трех полей была определена максимальная длина 20 байт, а для одного - 15. Значение полей не заполнены - на это указывает величина Null.
Вы можете удалить созданную таблицу непосредственно в интерфейсе Query Analyzer, щелкнув правой кнопкой и выбрав "Delete".
