Элементы работы с базами данных

Агрегатные функции

При статистическом анализе баз данных необходимо получать такую информацию, как общее количество записей, наибольшее и наименьшее значения заданного поля записи, усредненное значение поля. Это делается с помощью запросов, содержащих так называемые агрегатные функции. Агрегатные функции производят одиночное значение для всей группы таблицы. Имеется список этих функций.

• 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;

Выполните самостоятельно эти запросы и просмотрите результаты.

Оператор сравнения 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).

Создание таблицы с помощью запросов

Вплоть до этого места мы выполняли команды по извлечению таблиц и данных из таблиц, полагая, что сами таблицы были созданы кем-то до нас. Это, действительно, наиболее реальная ситуация, когда небольшое количество людей создает таблицы, которые затем используются другими людьми.

Тем не менее специальная область SQL, называемая DDL (Язык Определения Данных), специально работает с созданием объектов данных.

Таблицы создаются командой create table. Эта команда создает пустую таблицу - таблицу без строк. Команда create table в основном определяет имя таблицы, в виде описания набора имен столбцов, указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь по крайней мере один столбец.

Синтаксис команды create table^{8Перед выполнением этого запроса убедитесь в том, что вы работаете с базой данных NorthwindCS. Или используйте команду use NorthwindCS.}:

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".

Команды изменения языка 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.