Основы языка SQL и построение SQL-запросов. Сортировка, поиск, фильтрация данных. Иллюстрированный самоучитель по SQL для начинающих

Введение

SQL -- это язык структурированных запросов (Structured Query Language) к реляционным базам данных. История SQL тесно связана с историей развития реляционных баз данных. В 1974-1975 годах был создан первый прототип реляционной СУБД. Кроме разработки самой СУБД, в рамках проекта System/R в компании IBM проводилась работа над созданием языка запросов к базам данных. Первый язык запросов получил название SEQUEL -- Structured English Query Language. Вторая реализация проекта System/R была установлена на компьютерах нескольких заказчиков IBM с целью опытной эксплуатации в 1978-1979 гг. В этой реализации язык запросов имел уже название SQL, но до сих пор еще можно услышать от пользователей старое название этого языка.

В 1982 году компания IBM начала поставки на рынок коммерческого продукта SQL/Data System, а в 1983 году объявила о создании версии SQL/Data System для операционной системы VM/CMS. В 1983 году IBM выпустила новую реляционную СУБД -- Database 2 (DB2). Благодаря влиянию компании IBM на рынок вычислительных систем SQL DB2 стал фактическим стандартом языка баз данных.

Во второй половине восьмидесятых годов резко увеличилась производительность реляционных СУБД и с развитием реляционных технологий связывались большие надежды. Появились, в частности, новые версии СУБД Ingress и Oracle с производительностью, в два-три раза превышающей производительность предыдущих версий. Росту производительности СУБД способствовало и увеличение общего быстродействия компьютеров.

Опубликование в 1986 году стандарта SQL (ANSI/ISO) официально утвердило SQL как стандартный язык реляционных СУБД. С появлением более мощных персональных компьютеров и объединением их в локальные сети возникла необходимость в новых СУБД. Поставщики таких СУБД снова стали ориентироваться на SQL-технологии. И, наконец, SQL стал ключевой частью архитектуры клиент/сервер.

Первоначально SQL фирмы IBM имел простой синтаксис, но на протяжении нескольких лет производители программного обеспечения в области систем управления базами данных добавляли к своим реализациям новые возможности, многие из которых вошли в стандарты ANSI SQL. Версия языка ANSI SQL была принята организацией International Standards Organization (ISO), отделением ООН со штаб-квартирой в Женеве и комитетом International Electrotechnical Commission (IEC) как продукт ISO/IEC9075:1992 или язык баз данных SQL (SQL-92). Отдельный стандарт ANSI X.3.168-1989 определяет встроенный язык баз данных SQL(SQL-89). Современные СУБД поддерживают SQL-89 и многие дополнения из SQL-92.

Кроме того, большинство СУБД имеют свои ключевые SQL-слова для создания патентованных диалектов SQL, таких как Transact-SQL (сервера SQL Server) или SQL Jet.

Мы будем использовать SQL Jet и Transact-SQL, не уделяя внимания их отличиям от ANSI SQL-92.

Отметим, что историческое название SQL не совсем точно отражает суть этого языка, поскольку он давно уже предназначен не только для построения запросов (если, конечно, под «запросом» понимать только извлечение информации из базы данных). Сейчас SQL позволяет реализовать все функции СУБД: организацию данных, извлечение информации, модификацию данных, управление доступом, совместное использование данных, обеспечение целостности данных.

Типы команд SQL

база данные запрос таблица

В ANSI SQL имеется шесть основных типов команд:

Команды языка определения данных (data definition language -- DDL) позволяют создавать новые таблицы в базе данных, добавлять индексы и т.д.; основными командами языка определения данных являются:

Команды языка обработки данных (data manipulation language -- DML) используются для добавления, корректировки и удаления строк в таблицах и включают команды:

Команда языка запросов данных (data query language -- DQL) (единственная команда) используется для получения данных из таблиц и определения формы представления этих данных:

Команды языка управления данными (data control language -- DCL) определяют доступ отдельных пользователей и групп пользователей к объектам базы данных посредством полномочий, предоставляемых и отменяемых командами

Команды языка обработки транзакций (transaction processing language -- TPL) обеспечивают обновление всех строк, используемых в операторе DML, и включают следующие команды

BEGIN TRANSACTION	Начать транзакцию
COMIT TRANSACTION	Завершить транзакцию
SAVE TRANSACTION	Создать точку сохранения внутри транзакции

Команды языка управления курсором (cursor control language -- CCL) выполняют операции с отдельными строками одной или нескольких таблиц и включают команды: DECLARE CURSOR, FETCH INTO и UPDATE WHERE CURRENT.

Создатели СУБД, в общем-то, не обязаны поддерживать все команды SQL-92, и можно с уверенностью утверждать, что, практически, нет ни одной коммерческой СУБД, в которой реализованы все команды SQL-92. В частности, в SQL Jet не поддерживаются никакие зарезервированные слова команд DCL и CCL.

SQL в MS ACCESS

В MS ACCESS SQL применяется в основном для выполнения запросов, хотя очень удобно также удалять, добавлять и обновлять записи баз данных. Используя SQL-запросы, можно выбирать из таблиц базы данных только необходимые записи. При этом мы можем получить доступ не просто к одной таблице, а к сложной выборке из связанных между собой таблиц или наборов данных.

SQL-запросы можно также применять в приложениях, использующих объектные модели DAO, RDO или ADO. Кроме того, SQL как стандартный способ управления базами данных реализован во многих СУБД, включая Microsoft Access и SQL Server.

Инструкция SELECT

Для работы с базами данных в Microsoft Access применяется язык запросов, включающий единственную инструкцию SELECT. Рассмотрим некоторые вопросы, связанные с ее использованием. Синтаксис (неполный) инструкции SELECT в SQL Jet следующий:

SELECT {* | table.* | [ table. ] field1 [, table. ] field2 [, …] ] }

FROM table1 ] [, …]

[ IN externaldatabase ]

[,…]]]

Инструкция SELECT включает следующие основные элементы:

• ? SELECT означает, что из некоторых таблиц базы данных необходимо выбрать набор (таблицу данных).
• ? Необязательные слова ALL, DISTINCT, DISTINCTROW и ТОР называются предикатами (predicates) и определяют выбор следующим образом:

ALL указывает, что в набор передаются все строки (даже с дублируемыми значениями);

DISTINCT указывает, что в набор передаются только сдублированные строки;

DISTINCTROW указывает, что в результирующий наборбудет включена каждая строка, в которой есть отличие в значении любого из полей исходных таблиц (а не только полей, указанных для отображения в операторе SELECT);

ТОР используется для отображения некоторого количества (точного или в процентном отношении) начальных или конечных записей из результирующего набора.

• ? Список {* | table.* | [ table. ] field1 [, table. ] field2 [, …] ] } (фигурные скобки здесь обозначают список) состоит из имен полей таблиц(ы) запроса. Звездочка (*) означает выбор всех полей таблицы. Если в запросе указывается несколько таблиц, то для определения поля используется наименование таблицы, отделяемое от имени поля точкой (.). Поле может получать «алиасное» имя (псевдоним) при помощи ключевого слова AS.
• ? После слова FROM указываются таблицы, из которых выбираются ранее указанные поля. Здесь table1 (,table2) -- это имя таблицы (или таблиц), содержащей данные, externaldatabase -- имя базы данных, если не используется текущая база.

Неотмеченные элементы инструкции SELECT мы будем рассматривать по мере изучения более сложных запросов к таблицам.

Для примеров использования SELECT будем работать с базой данных Lab 3.mdb, создание и заполнение которой рассматривалось в предыдущей лабораторной работе.

Изучать инструкции SELECT, как и другие инструкции, можно в приложениях Access.

Последнее замечание перед тем, как начать рассмотрение SQL-инструкций, касается способа записи этих инструкций в строке. Нет никаких правил написания SQL-инструкций относительно их положения в строке. Можно писать инструкцию в одной строке, можно -- в нескольких.

Будем следовать следующему правилу: каждое предложение типа SELECT, FROM, WHERE и т.д. будет начинаться с новой строки. Если будет необходимо, на новой строке будет выполнен отступ. Так SQL-инструкции удобнее записывать, так их легче понимать.

Для тестирования SQL-инструкций в среде Access выберите в левой части главного окна Access в меню Объекты опцию Запросы и дважды щелкните команду Создание запроса в режиме конструктора. Появившееся окно Добавление таблицы нам в данном случае не нужно, поэтому его следует закрыть.

Не обращая внимания на рассмотренные в предыдущей лабораторной работе средства Access, предназначенные для создания запроса QBE, выберите команду Режим SQL в меню Вид (рис.1), чтобы, наконец, получить доступ к тому окну, в котором можно будет набирать SQL-инструкции.

Рисунок 1. Выбор команды Режим SQL

Инструкцию SELECT будем изучать по принципу «от простого -- к сложному»: сначала выбросим из полного синтаксиса этой инструкции все необязательные элементы, а затем постепенно будем использовать их, получая более сложные запросы. Если отбросить все необязательные предложения из списка {* | table.* | [ table. ] field1 [, table. ] field2 [, …] ] } и оставить только элемент *, то получим самый простой вариант SELECT-инструкции (SQL-запроса), который будет иметь вид (рис. 2):

SELECT * FROM Продукты;

Рисунок 2. Простейший запрос на SQL

Результат показан на рис. 3.

Рисунок 3. Результат выполнения простейшего запроса

Выбор в SQL-запросе определенных полей

Чаще всего вам не нужно будет получать набор со всеми полями одной (или более) таблицы. Например, поскольку поле КодПрод в таблице

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

Например;

SELECT Продукт, ЕдИзм

FROM Продукты;

Для изменения запроса используем команду Режим SQL в меню Вид (рис. 3.1)

Результат запроса показан на рис 4.

Рисунок 4. Выбор определенных полей

При этом можно изменить заголовки:

FROM Продукты;

Результат см. на рис. 5.

Выбор в SQL-запросе определенных записей

Ключевое слово WHERE в синтаксисе инструкции SELECT позволяет указывать определенные типы записей, которые должны попадать в набор.

Например:

SELECT Продукт AS [Наименование продукта], ЕдИзм AS [Единица измерения]

FROM Продукты

WHERE ЕдИзм = "л";

Результат выборки показан на рис. 6.

В области слова WHERE можно располагать сложное условное выражение с использованием знаков логических операций и функций. Например:

FROM Товары WHERE Цена1 > 50 AND Цена1 < 200;

SELECT НаимТовара as [Наименование товара], Цена1 as [Цена закупочная]

FROM Товары

WHERE Цена1 > 50 AND Len (НаимТовара) < 20;

В первом случае запрашиваются товары, оптовая цена которых находится в диапазоне оптовых цен (55-200), а во втором -- цены которых больше 50 денежных единиц и длина наименования не превышает 20 символов.

Кроме операций < и >, в инструкции SELECT можно использовать операции = (равно), <= (меньше или равно) и >= (больше или равно), а также AND, OR и NOT. Но еще большие возможности по отбору необходимых записей предоставляют такие операторы, как IS NULL, BETWEEN, IN и LIKE.

Оператор IS NULL позволяет найти в таблице записи, в полях которых не указаны данные, например:

SELECT НаимТовара AS [Наименование товара], Цена1 AS [Цена закупочная]

FROM Товары

WHERE Цена2 IS NULL OR Цена1 < 50;

Оператор BETWEEN позволяет указать диапазон, в котором находятся данные некоторого поля, например:

SELECT НаимТовара as [Наименование товара], Цена1 as [Цена закупочная]

FROM Товары

WHERE Цена1 BETWEEN 42 AND 120;

Оператор IN позволяет указать список, в котором находятся данные некоторого поля, например:

SELECT НаимТовара as [Наименование товара], Цена1 as [Цена закупочная]

FROM Товары

WHERE Цена1 IN (42, 105,750)

В SQL-инструкции можно также использовать оператор LIKE, например инструкция:

SELECT НаимТовара as [Наименование товара],Цена1 as [Цена закупочная]

FROM Товары

WHERE НаимТовара LIKE "(VCD)*";

позволяет получить только те записи, у которых в наименовании первые пять символов совпадают со строкой "(VCD)".

Оператор LIKE можно использовать для контекстного поиска, например, если пользователь введет текстовую строку, содержащую часть наименования товара, то эту строку легко использовать в виде шаблона. Следующая инструкция выполняет поиск записей, в которых наименование содержит в качестве подстроки строку "мишень":

SELECT НаимТовара as [Наименование товара], Цена1 as [Цена закупочная]

FROM Товары

WHERE НаимТовара LIKE "*мишень*";

Оператор NOT, который инвертирует логическое выражение, может использоваться с операторами IS NULL, BETWEEN, IN, LIKE. При этом, практически, речь идет об операторах IS NOT NULL, NOT BETWEEN, NOT IN и NOT LIKE, например, как в следующих четырех инструкциях:

SELECT НаимТовара AS [Наименование товара], Цена! AS [Цена закупочная]

FROM Товары

WHERE Цена2 IS NOT NULL OR Цена1 < 50;

SELECT НаимТовара as [Наименование товара], Цена1 as [Цена закупочная]

FROM Товары

WHERE Цена1 NOT BETWEEN 42 AND 120;

SELECT НаименованиеТовара as [Наименование товара], Цена! as [Цена закупочная]

FROM Товары

WHERE Цена1 NOT IN (42, 105,750);

SELECT НаимТовара as [Наименование товара], Цена1 as [Цена закупочная]

FROM Товары

WHERE НаимТовара NOT LIKE "(VCD)*";

Результирующие данные запроса можно форматировать с использованием, например, функции Format. В следующем запросе данные форматируются при помощи строки "### ##0.00$":

SELECT НаимТовара as [Наименование товара],

Format(Цена1*1.2,"### ##0.00р") as [Цена оптовая]

FROM Товары

WHERE Цена1 < 100

Обратите внимание еще и на то, что функция Format в качестве первого аргумента получает произведение Цена1*1.2. Таким образом, мы получаем другую цену из некоторой базовой.

Для форматирования выводимых в запросе данных можно использовать функции преобразования строк. Например, в следующем запросе наименования товаров выводятся символами верхнего регистра, поскольку здесь используется функция StrConv:

SELECT StrConv(Продукт,1) AS [Наименование продукта], ЕдИзм AS [Единица измерения]

FROM Продукты;

Результат такого запроса показан на рис. 7.

Рисунок 7. Результат запроса с указанием формата представления информации

Выбор данных из более чем одной таблицы

Рассмотрим задачу выбора из базы данных товаров некоторого склада. Перед тем как рассмотреть использование слова WHERE для связи таблиц, заметим, что в инструкции SELECT можно перед именем поля указывать имя таблицы, которое отделяется от имени поля точкой:

SELECT { * | field1 [, field2 [, ...]]}

Например (Запрос 6):

SELECT Продукты.Продукт AS [Наименование продукта], Продукты.ЕдИзм AS [Единица измерения]

FROM Продукты;

Или (Запрос 7)

SELECT tab.Продукт AS [Наименование продукта], tab.ЕдИзм AS [Единица измерения]

FROM Продукты tab;

Во второй инструкции использовано альтернативное имя таблицы (локальный псевдоним), но обе инструкции выдают один и тот же результирующий набор.

Следующая инструкция позволяет получить наименования (из таблицы Продукты) и количества (из таблицы Продажи) продуктов (результат выполнения -- на рис. 8):

FROM Продукты, Продажи

WHERE Продукты.КодПрод = Продажи.КодПрод;

Рисунок 8. Выборка из двух таблиц

Обратите внимание на выражение Продукты.КодПрод = Продажи.КодПрод.

Именно оно используется для связи таблиц Продукты и Продажи по ключевому полю КодПрод.

Результат выполнения предыдущей инструкции напоминает инвентаризационную ведомость без указания даты продажи. Следующая инструкция позволяет получить наименования и количества только для тех продуктов, которые проданы после заданной даты (рис. 9):

SELECT Продукты.Продукт AS [Наименование продукта], Продажи.Количество AS [Количество]

FROM Продукты, Продажи

WHERE Продажи.ДатаПродажи > #3/25/2003# AND Продукты.КодПрод = Продажи.КодПрод;

Рисунок 9. Выборка из двух таблиц с ограничением по дате

При помощи локальных псевдонимов можно сократить предыдущую инструкцию (Запрос10).

FROM Продукты a, Продажи b

WHERE b.ДатаПродажи > ;

Вывод выбранных данных в определенном порядке

Для сортировки данных в инструкции SELECT имеются слова ORDER BY:

SELECT { * | field1 [, field2 I, .-.]]}

FROM table1 [, table2 [, ...]

[, field2 ][, ...]]]

Здесь к тем элементам SQL-запроса, которые уже рассмотрены, добавлено необязательное предложение ORDER BY. Как следует из синтаксиса инструкции SELECT, используя слова ASC и DESC, можно изменять «направление» сортировки («по возрастанию» и «по убыванию»). Сортировать можно по нескольким полям (сначала по одному, затем -- по другому, и так далее) и даже по различным элементам одного и того же поля с использованием функций.

В следующей инструкции используется сортировка выводимого набора в порядке убывания количества продаж (Запрос11):

SELECT a.Продукт AS [Наименование продукта], b.Количество AS [Количество]

FROM Продукты a, Продажи b

WHERE b.ДатаПродажи > #3/25/2003# AND a.КодПрод = b.КодПрод

ORDER BY а. Продукт DESC;

Агрегирующие функции в инструкции SELECT

В инструкциях языка ANSI SQL предусмотрены так называемые агрегирующие функции, которые определяют количество записей, вычисляют суммы всех значений полей в наборе, находят минимальные или максимальные, а также средние значения. К агрегирующим функциям относятся функции COUNT, SUM, MAX, MIN и AVG.

Функция COUNT используется для определения количества записей в запросе. Например, инструкция для вычисления количества записей в таблице Продукты может быть записана следующим образом:

SELECT COUNT(Продукт) AS [Всего наименований]

FROM Продукты;

На рис. 10 показан результат выполнения этого запроса.

Рисунок 10. Подсчет количества продуктов

Функция AVG в инструкции SELECT позволяет найти среднее значение для строк, входящих в запрос. Например, следующая инструкция определяет среднюю цену продажи для сделок, совершенных после указанной даты (Запрос13):

SELECT Format(AVG (ЦенаПродажи), "### ##0.00р") AS [Средняя цена]

FROM Продажи

WHERE ДатаПродажи > #3/25/2003#;

Группировка данных в инструкции SELECT

Часто в наборах, получаемых при помощи SELECT-инструкций, встречаются повторяющиеся значения. Для рассматриваемой нами базы данных это могут быть, например, наименования продуктов в запросе (рис. 3.8). Такие повторяющиеся значения можно объединять в группы, используя для групп агрегирующие функции.

В инструкции SELECT для объединения значений в группы используется предложение GROUP BY.

Следующая инструкция имеет результатом список проданных продуктов и их суммарные количества (результат -- на рис. 11):

FROM Продукты a, Продажи b

WHERE b.ДатаПродажи > #3/25/2003# AND a.КодПрод = b.КодПрод

GROUP BY a.Продукт;

Рисунок 11. Группировка объема продаж по наименованиям продуктов

Предположим, что нам нужна информация о суммарном количестве только тех продуктов, для которых количество продаж меньше определенного числа. Чтобы выполнить этот запрос, мы не можем использовать предложение WHERE, так как оно «работает» со значениями данных, расположенными в таблице, а нам нужно наложить некоторые ограничения на вычисляемые данные. Для решения подобной задачи следует использовать предложение HAVING, которое так же связано с предложением GROUP BY, как WHERE с SELECT. Другими словами, предложение HAVING налагает некоторые условия на выбранные посредством предложения GROUP BY данные.

Следующая инструкция позволяет получить список продуктов и суммарное количество продаж. Причем в список включаются только те продукты, для которых суммарное количество продаж меньше чем 20 (результат -- на рис. 12):

SELECT a.Продукт AS [Наименование продукта], SUM(b.Количество) AS [Количество]

FROM Продукты a, Продажи b

WHERE b.ДатаПродажи > #3/25/2003# AND a.КодПрод = b.КодПрод

GROUP BY a.Продукт

HAVING SUM(b.Количество) < 20;

Рисунок 12. Группировка продаж, не превышающих заданной величины

Подзапросы в инструкции SELECT

Подзапрос -- это запрос, который размещается внутри другого запроса, а точнее, является частью предложения WHERE (или HAVING) основного запроса и заключается в круглые скобки. Иногда подзапросы называют вложенными запросами. Запрос, содержащий подзапрос, называют сложным запросом. При выполнении сложного запроса сначала выполняется подзапрос (он для этого и заключается в скобки!), а затем -- основной запрос.

Выходные данные подзапроса обычно используются при оценке выражения в предложении WHERE (или HAVING) основного (по отношению к данному подзапросу) запроса с использованием таких операторов, как =, >, <, о, IN, NOT IN, AND, OR. (Эти операторы можно использовать и внутри подзапроса.)

Очень важно понимать, какие данные возвращает подзапрос, поскольку от этого зависит синтаксис использования предложения WHERE. Необходимо при написании сложных запросов соблюдать следующие правила:

• ? Предложение SELECT подзапроса обычно содержит только один столбец и, следовательно, может иметь результатом массив однородных (только строковые значения, только целые значения и т.д.) значений или только одно значение. Очень редко встречаются случаи использования нескольких столбцов. Если подзапрос возвращает массив значений, соответствующее этому подзапросу предложение WHERE может содержать только многозначные операторы, например, IN.
• ? Предложение ORDER BY следует использовать только в основном запросе. В подзапросе для упорядочения данных необходимо применять предложение GROUP BY.
• ? Недопустимо использование в основном запросе предложения BETWEEN

Простой синтаксис подзапроса для оператора SELECT следующий:

SELECT { * I table.* | field1 [, field2 [, ...]])

FROM table1 [, table2 [, ...]

WHERE field1 operator

(SELECT { * I table.* I field1 [, field2 [, ...]]}

FROM table1 [, table2 [, ...]

WHERE )

Проще всего продемонстрировать использование подзапроса, если он имеет результатом единственное значение. Например, пусть нам необходимо получить список продуктов от поставщика, поставки которого наиболее разнообразны (поставляет наибольшее количество наименований).

Напомним, что схема данных (рис.13) БД Lab 3.mdb та же, что и в Lab 2.mdb.

Рисунок 13. Схема данных Lab 3.mdb

Запрос, связывающий продажи продуктов с поставщиками выглядит следующим образом:

SELECT a.Продукт AS [Наименование продукта], b.Количество AS [Количество], d.КодПост AS [Поставщик]

FROM Продукты a, Продажи b, Поставки c, Поставщики d

WHERE a.КодПрод = b.КодПрод AND b.КодПрод = c.КодПрод AND c.КодПост = d.КодПост;

Его результат показан на рис.14.

Рисунок 14. Продажи, связанные с поставщиками

Для начала нам нужно определить этого поставщика, предоставившего наибольшее количество наименований товара. Создадим запрос, который возвратит код такого поставщика:

SELECT ТОР 1 КодПост

FROM Поставки

GROUP BY КодПост

ORDER BY COUNT(КодПрод) DESC

Предложение TOP 1 указывает на то, что в результирующий набор попадает только одна запись, а поскольку набор сортируется (по убыванию) по суммарным количествам продуктов, то в результате мы получаем код поставщика с наибольшим количеством наименований. Результат такого запроса показан на рис.15.

Рисунок 15. Код поставщика с наибольшим количеством наименований

Остается подготовить запрос, который возвращает список проданных продуктов для указанного кода поставщика, но если ранее мы бы указывали этот код явно, то теперь определяем его в подзапросе (результат -- на рис. 16):

SELECT a.Продукт AS [Наименование продукта], b.Количество AS Количество, d.КодПост AS Поставщик

FROM Продукты AS a, Продажи AS b, Поставки AS c, Поставщики AS d

WHERE a.КодПрод = b.КодПрод AND b.КодПрод = c.КодПрод AND c.КодПост = d.КодПост AND c.КодПост =

(SELECT TOP 1 КодПост

FROM Поставки

GROUP BY КодПост

ORDER BY COUNT(КодПрод) DESC);

Рисунок 16. Продажи продуктов от поставщика с наибольшим количеством наименований

Анализ такого запроса показывает, что количество проданных наименований существенно меньше количества наименований продуктов, предоставленных данным поставщиком.

Заменив в первой строке запроса d.КодПост на d. Поставщик получим результат официальным названием фирмы (рис.17).

Глава 17. Основы языка SQL

В этой главе мы рассмотрим команды языка структурированных запросов (Structured Query Language). Вы узнаете, что такое запрос и как с ним работать.

Основные сведения о языке SQL
Язык SQL существует в нескольких стандартах. Наиболее поддерживаемыми и распространенными являются стандарты SQL-89 и
SQL-92. Последний стандарт также называют стандартом ANSI. Из-за наличия множества стандртов языка существует множество его диалектов. Мы рассмотрим только основные команды SQL для простых операций с таблицами баз данных.
В языке SQL нет многих типов команд, которые присущи большинству языков программирования. Так, в нем нет операторов цикла, перехода и т. д.
SQL-запрос - это команда на языке SQL.
Язык SQL по своей сути ориентирован на доступ к данным, и его обычно включают в состав различных средств разработки. Среда Kylix не осталась в стороне. Более того, все ее компоненты для работы с базами данных поддерживают язык SQL.
Все SQL-запросы можно условно разделить на два вида:

статический SQL-запрос - включается в код приложения во время его разработки и не изменяется во время выполнения приложения. Единственным способом изменить что-либо в запросе такого типа является включение в SQL-запрос параметров и их дальнейшее изменение в ходе работы приложения;

динамический SQL-запрос - создается и изменяется в ходе выполнения приложения. Эти запросы являются наиболее гибкими и могут отвечать конкретным действиям пользователя.
Все операторы и команды языка SQL можно разделить на три группы. Рассмотрим эти группы, а также операторы, входящие в каждую из групп.
Операторы определения данных - предназначены для создания, удаления и изменения структуры данных. Основные из них перечислены в табл. 17.1.

Таблица 17.1. Основные операторы определения данных

Операторы управления данными - предназначены для управления привилегиями доступа к данным. Основные операторы представлены в табл. 17.2.

Таблица 17.2. Основные операторы управления данными

Операторы манипулирования данными - предназначены для работы с записями таблиц. Основные операторы кратко описаны
в табл. 17.3.

Таблица 17.3. Основные операторы манипулирования данными

Использование приведенных в табл. 17.1-17.3 операторов мы рассмотрим далее в этой главе.

Примечание
Обратите внимание на тот факт, что проверка синтаксиса запроса происходит только во время работы приложения. Компилятор Kylix не обрабатывает SQL-запросы во время разработки приложения.

Результатом выполнения запроса обычно является набор данных, который называется результирующим набором данных.
Осталось отметить, что регистр букв команд SQL-запросов не влияет на их выполнение. Мы для наглядности будем применять заглавные буквы для Операторов языка SQL. Точка с запятой также не обязательна в конце каждой SQL-команды. Элементы списков, такие как имена таблиц и полей, обязательно разделяются запятыми. Имена таблиц и полей заключаются в одинарные или двойные кавычки, например "Number", "Table 1". В случае если имя не содержит пробелов и других специальных символов, его можно не заключать в кавычки.

Функции языка SQL
В языке SQL, кроме операторов, имеется несколько функций, основные из которых будут рассмотрены далее.
Статистические функции - функции, предназначенные для статистической обработки данных. Наиболее часто используемые статистические функции Перечислены в табл. 17.4.

Таблица 17.4. Основные статистические функции

Строковые функции - функции, предназначенные для работы со строковыми чачениями. Наиболее часто используемые из них представлены в табл. 17.5.

Таблица 17.5. Основные строковые функции

Функция	Описание
I I	Слияние (конкатенация) двух строк
CAST (<Выражение> AS <Тип>)	Приводит выражение <Выражение> к типу <Тип>
LOWER(S)	Преобразует символы строки S в символы нижнего регистра
SUBSTRING (S FROM Nl то N2)	Выделяет из строки s подстроку, начиная с символа N1 и заканчивая символом N2
TRIM (S)	Удаляет в начале и конце строки s лишние пробелы
UPPER (S)	Преобразует символы строки s в символы верхнего регистра

Операторы определения данных
Давайте рассмотрим более подробно операторы определения данных.
Оператор CREATE TABLE служит для создания новой таблицы базы данных и имеет следующий формат:

CREATE TABLE <Имя таблицы>
<Имя поля> <Тип данных>,
…
<Имя поля> <Тип данных>);

В этом операторе обязательно указание хотя бы одного имени поля и его типа данных.
Приведем пример создания простой таблицы:

CREATE TABLE MyTable
(Number INTEGER, Name CHAR(20),
Surname CHAR(20));

При этом в каталоге текущей базы данных создастся новая таблица муТаblе, состоящая из полей Number, Name и Surname. Первое поле имеет целочисленный тип (INTEGER), остальные поля - символьного типа и ограничены длиной в 20 символов.
Если при выполнении этого запроса выяснится, что таблица с таким именем уже существует, будет сгенерирована исключительная ситуация.
Оператор DROP TABLE <имя таблицы> служит для удаления имеющейся таблицы. Если таблицы с таким именем не существует, будет сгенерирована исключительная ситуация:
DROP TABLE МуТаЫе;

При выполнении этой операции будут удалены все файлы, относящиеся к таблице МуТаЫе.
Оператор ALTER TABLE предназначен для добавления или удаления полей действующей таблицы базы данных. Во время действия этого оператора никакие другие приложения не должны обращаться к таблице. Этот оператор имеет следующий формат:

ALTER TABLE <Имя таблицы>
ADD <Имя поля> <Тип данных>,
DROP <Имя поля>,
…
ADD <Имя поля> <Тип данных>,
DROP <Имя поля>;

При этом операнд ADD добавляет к таблице новое поле, а оператор DROP удаляет из таблицы существующее поле. Операнды могут располагаться внутри оператора ALTER TABLE произвольно:

ALTER TABLE МуТаblе
ADD Telefon INTEGER,
ADD Address CHAR (50),
DROP Number;

При выполнении данного примера в таблицу МуТаblе добавятся два поля: felefon и Address, целочисленного и символьного типа соответственно. Кроме того, будет удалено поле Number.

Операторы манипулирования данными
Главным оператором манипулирования данными является оператор SELECT. ЭТот оператор используется для отбора данных, соответствующих сложным условиям. Оператор SELECT имеет вид:

SELECT
<Список полей> или *
FROM <Список таблиц>

Результатом выполнения данного оператора будет набор записей, удовлетворяющих заданным условиям.
Параметр DISTINCT определяет, будут ли включаться в результирующий набор данных повторяющиеся записи. Если он присутствует в операторе SELECT, то повторяющиеся записи будут исключены из набора данных.
Если в список полей входят поля нескольких таблиц, для указания принадлежности поля к той или иной таблице используют составной оператор, включающий имя таблицы и, через точку, имя поля:

<Имя таблицы>.<Имя поля>

Операнд WHERE определяет критерии, которым должны удовлетворять записи в результирующем наборе данных.
Операнд GROUP BY позволяет группировать записи. Иногда бывает необходимо выполнить какие-либо операции над группой записей.
Операнд HAVING используется вместе с GROUP BY и позволяет выбирать записи внутри групп.
Операнд ORDER BY содержит список полей, определяющих порядок сортировки записей результирующего набора данных. По умолчанию сортировка выполняется в порядке возрастания значений. Для сортировки в порядке убывания значений необходимо после имени поля поставить параметр DESC.
Операнд UNION используется для организации вложенных операторов SELECT. Вложенные операторы SELECT называют иногда подзапросами.
Приведем примеры выбора записей с использованием оператора SELECT.

Пример 17.1. Выбор всех полей таблицы
SELECT * FROM MyTable

В результате выполнения этого запроса будут выбраны все поля и записи из таблицы мутаые. Значок * обозначает, что выбраны все поля таблицы. Вместо него можно просто перечислить через запятую все поля таблицы.

Пример 17.2. Выбор данных из трех полей таблицы
SELECY Number, Surname, Telefon
FROM MyTable

В результате выполнения примера 17.2 будут выбраны все записи из полей
Number, Surname И Telefon таблицы MyTable .

Пример 17.3. Выбор уникальных значений
SELECT DISTINCT Surname
FROM MyTable

В результате мы получим набор данных, который содержит все фамилии (Surname), входящие в таблицу муТаblе. В этот набор данных не будет дважды включена одна и та же фамилия, т. е. будут исключены все однофамильцы.

Пример 17.4. Выбор данных из двух таблиц
SELECT * FROM МуТаЫе!, МуТаЫе2

Выбирает все данные из таблиц МуТаblе1 и МуТаЫе2. Первыми будут располагаться поля таблицы MyTable1, а затем - поля второй таблицы.

Пример 17.5. Выбор записей по значениям числового поля
SELECT Name, Surname
FROM MyTable
WHERE (Number>l) and (Number<100) ;

Результирующим набором данных будут имена и фамилии первых ста человек, занесенных в таблицу МуТаblе.

Пример 17.6. Выбор записей по значению символьного поля
SELECT Name
FROM МуТаЫе
WHERE Surname="Иванов";

Результатом будет набор имен, фамилии которых Иванов.

Пример 17.7. Проверка частичного совпадения по символьному полю
SELECT Name, Surname
FROM MyTable
WHERE Surname LIKE "И";

Будут выбраны имена и фамилии людей, занесенных в таблицу МуТаblе, фамилии которых начинаются на букву И.
В выражениях операции LIKE можно использовать шаблоны. Символы шаблонов:
% - замещает любое количество символов, в том числе и нулевое;
_ - замещает один символ.

Пример 17.8. Проверка частичного совпадения по шаблону
SELECT Name, Surname
FROM MyTable
WHERE Surname LIKE "%" I I "ов" I I "%";

Будут выбраны имена и фамилии людей, в состав фамилий которых входя символы ов.
Операнд ORDER BY служит для упорядочения (сортировки) значений полей.

Пример 17.9. Сортировка записей по полю
SELECT * FROM MyTable
ORDER BY Name
Таким образом, набор данных будет отсортирован в порядке возрастания по полю Name.

Пример 17.10. Сортировка по двум полям
SELECT * FROM MyTable
ORDER BY Name, Surname DESC

Будет произведена сортировка данных сначала по полю Name в порядке возрастания, затем по полю Surname в порядке убывания.
Оператор UPDATE служит для изменения значений полей в группе записей и имеет следующий формат:

UPDATE <Имя таблицы>
SET <Имя поля> = <Выражение>,
…
SET <Имя поля> = <Выражение>
;

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

Пример 17.11. Изменение значений поля
UPDATE MyTable
SET Oklad = Oklad + 1000;
WHERE Cklad < 1000;

Изменит сумму оклада (oklad) сотрудника, если оклад менее 1000 руб. К сумме оклада прибавится еще 1000 руб. Оператор INSERT служит для вставки записей в таблицу и имеет следующий формат:

INSERT INTO <Имя таблицы>
(<Список полей>)
VALUES (<Список значений>);

Таким образом, к таблице <имя таблицы> будет добавлена одна запись.

Пример 17.12. Добавление записи в таблицу
INSERT INTO MyTable
(Name, Surname, Telefon)
VALUES ("Иван", "Иванов", 2341234);
В таблицу MyTable будет добавлена новая запись, содержащая имя и фамилию нового сотрудника, а также номер его телефона.
Оператор DELETE служит для удаления записей из таблицы и имеет следующий формат:

DELETE FROM <Имя таблицы>
;

Из таблицы будут удалены все записи, которые удовлетворяют условию выбора.

Пример 17.13. Удаление записей из таблицы
DELETE FROM MyTable
WHERE Surname = "Иванов";

Удаляет из таблицы MyTable все записи, содержащие фамилию Иванов.
На этом мы заканчиваем обзор основных операторов и функций языка SQL. Дополнительную информацию вы можете почерпнуть из специальной литературы, посвященной данному языку.

SQL - язык структурных запросов, созданный сотрудниками фирмы IBM для реляционных баз данных в начале 1970-х гг., а затем реализованный в рамках реляционных СУБД других фирм-разработчиков. Впоследствии SQL фактически стал стандартом в области языков запросов к реляционным базам данных.

В 1989 г. Американский национальный институт стандартов (American National Standards - ANSI) на основе анализа различных реализаций SQL разработал стандарт SQL-86. В 1992 г. появилась его новая версия - SQL-92, или SQL2. Эта версия реализована практически во всех современных СУБД. В настоящее время разрабатывается версия стандарта SQL3.

SQL представляет собой набор операторов, позволяющих осуществлять выборку (оператор SELECT) данных, их модификацию (оператор UPDATE) и удаление (оператор DELETE), а также ввод новых данных (оператор INSERT). Кроме того, в данный язык входят операторы определения переменных, создания и вызова хранимых процедур, функций и специальных процедур - триггеров. Всего SQL содержит более 100 операторов и позволяет разрабатывать сложные приложения. Одним из основных операторов SQL является SELECT.

Оператор SELECT предназначен для осуществления запросов по выборке данных из БД. Его синтаксис приведен далее:

SELECT Ссписок выбираемых полей>

В данном описании использованы следующие обозначения:

• ? ссписок таблиц> - синтаксическая конструкция, требующая дальнейшего уточнения;
• ? - синтаксическая конструкция внутри квадратных скобок (является необязательной и, в зависимости от ситуации, может использоваться или отсутствовать);
• ? ASC | DESC - наличие символа “|” означает, что может использоваться синтаксическая конструкция, находящаяся слева или справа от этого символа;
• ? DICTINCT - указывает на необходимость исключения из результата одинаковых кортежей (записей);
• ? ссписок выбираемых полей> - список атрибутов (полей), которые входят в состав результирующего отношения. Кроме атрибутов здесь могут быть представлены и выражения;
• ? ссписок таблиц> - список отношений (таблиц), которые используются в запросе;
• ? = [ [сусловие связи>]...]
• ? [АЫБсусловие отбора> [А]>Ш|(Ж]...]
• ? сусловие связи> - используется для связи таблиц, используемых в запросе;
• ? сусловие отбора> - выражение, определяющее, какие кортежи (записи) будут включены в результат. При составлении этого выражения можно использовать арифметические (+, -, х, /) и логические операции (AND, OR, NOT), а также встроенные функции SQL.

Текст запроса заканчивается знаком «;».

Сформулируем запрос: «Вывести следующую информацию о поставках контрагентом с кодом 1: номер накладной, дата поставки».

SELECT №накладной, ДатаПоставки

FROM Поставка

WHERE КодКонтрагента=1;

В этом запросе ссписок выбираемых полей> представлен атрибутами (полями) Мнакладной, ДатаПоставки, ссписок таблиц> - одним отношением (таблицей) Поставка, а сусловие выборки> включает одно сусловие отбора> - КодКонтрагента=1.

В связи с тем, что здесь используется только одна таблица, сусловие связи> отсутствует; сусловие соединения> будет рассмотрено далее.

Рассмотрим пример с использованием нескольких отношений (таблиц). Переформулируем первый запрос: «Вывести следующую информацию о поставках товаров контрагентами: номер накладной, наименование контрагента, дата поставки». В SQL он представляется следующим образом:

SELECT №накладной, ДатаПоставки, НаимКонтрагента FROM Контрагент INNER JOIN Поставка ON Контрагент. КодКонтрагента = Поставка.КодКонтрагента;

Здесь сусловие связи> представлено выражением (Контрагент.Код- Контрагента = Поставка. Код Контрагента). Оно указывает, при помощи каких атрибутов (полей) осуществляется связывание таблиц.

SQL позволяет получать при помощи запросов вычисляемые атрибуты - результаты вычислений над другими атрибутами. Запрос «Вывести следующую информацию о поставках товаров: номер накладной, дата поставки, код товара, сумма позиции» имеет вид:

SELECT №накладной, ДатаПоставки, КодТовара, Цена- Поставщика х Количество AS суммаПозиции FROM Поставка INNER JOIN СпецПоставки ON Поставка. №накладной = СпецПоставки.№накладнойПоставки;

При составлении вычисляемых атрибутов могут использоваться встроенные функции:

COUNT(*) - подсчитывает количество кортежей (записей); MAX(Atr) - выдает наибольшее значение атрибута Atr;

MIN(Atr) - выдает наименьшее значение атрибута Atr. Рассмотрим пример.

Необходимо определить общее количество поставленных товаров:

SELECT КодТовара, Sum(Количество) AS Поставлено FROM СпецПоставки GROUP BY КодТовара;

В таких случаях в СУБД используется так называемая группировка данных. Все кортежи (записи) автоматически делятся на группы по заданным признакам, для каждой группы производятся одни и те же вычисления. Признаки группировки задаются при помощи следующей конструкции:

Кроме того, SQL позволяет упорядочить результаты запросов по возрастанию или убыванию нескольких атрибутов (полей) при помощи конструкции вида

Где - перечень атрибутов (полей), в соответствии с которыми производится сортировка результата запроса; AS С (DESC) - признак упорядочивания по возрастанию (убыванию).

Упорядочим записи результата запроса по убыванию:

SELECT КодТовара, Sum(Количество) AS Поставлено

FROM СпецПоставки

GROUP BY КодТовара

ORDER BY Sum (Количество) DESC;

Конструкция

Join on , где, - связываемые отношения (таблицы);

Определяет внешнее (левое - left и правое - right) или внутреннее (inner) соединение отношений (таблиц).

Внутреннее соединение включает в результирующее отношение (таблицу) только те кортежи (записи) обоих отношений (таблиц), для которых выполняется.

Рассмотрим внутреннее соединение на примере запроса: «Определить количество поставок каждым контрагентом из справочника». С использованием конструкции

SELECT НаимКонтрагента, Count(№накладной) AS Количество

FROM Контрагент LEFT JOIN Поставка ON Контрагент. КодКонтрагента = Поставка.КодКонтрагента GROUP BY Контрагент.НаимКонтрагента;

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

На этом обзор основных конструкций языка SQL завершается. Сложные SQL-запросы, а также ряд других параметров будут рассматриваться далее при решении конкретных задач в п. 3.4.5 и 3.4.6.

2. ОСНОВЫ ЯЗЫКА SQL

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

SQL (Structured Query Language), или Структурированный Язык Запросов,

– это язык, который дает возможность работать с данными в реляционных базах данных. Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов), а также ISO (Международной организацией по стандартизации). Однако большинство коммерческих программ баз данных расширяют SQL, добавляя разные особенности в этот язык, которые, как они считают, будут полезны. Эти дополнения являются не стандартизированными и часто приводят к сложностям при переходе от одного сервера данных к другому.

Для обращения к базе данных используются запросы, написанные на языке SQL. Запросом называется команда, которая передается серверу базы данных, и которая сообщает ему, что нужно вывести определенную информацию из таблиц в память. Эта информация обычно посылается непосредственно на экран компьютера или терминала, хотя в большинстве случаев ее можно также передать на принтер, сохранить в файле (как объект в памяти компьютера) или представить как вводную информацию для другой команды или процесса.

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

Для выполнения SQL-запросов будем использовать SQL Management Studio. При запуске среды Management Studio появляется следующее окно

Рис. 25. Задание параметров подключения

Задействуем подключение к серверу, находящемуся на локальном компьютере. Параметр «Проверка подлинности» задает аутентификацию при подключении к серверу – при выбранном значении «Проверка подлинности Windows» (Windows authentification) в качестве имени пользователя и пароля будут использованы системные параметры.

Если все сделано правильно, то появляется главное окно программы. Для перехода в режим запросов необходимо нажать кнопку «Создать запрос»

Рис. 26. Переход в режим создания запросов

Отметим, что будут создаваться запросы, работающие с выделенной базой данных. После нажатия кнопки «Создать запрос» среда SQL Management Studio принимает вид, как показано на рисунке (рис. 27). Обратите внимание на кнопку «Выполнить», которая выполняет запросы, введенные в правом текстовом поле, и выводит результат их выполнения.

2.1. Базовая конструкция SQL-запроса

Where <условие>

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

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

Итак, напишем первый запрос и нажмем клавишу F5 (пункт меню Запрос

– Выполнить): select * from Туристы;

В результате возвращаются все записи из таблицы «Туристы» базы дан-

ных BDTur_firmSQL.

Главное окно программы принимает вид (рис. 27).

Рис. 27. Извлечение строк из таблицы «Туристы»

Данный запрос извлекал все столбцы таблицы. Если необходимо выбрать только столбец «Фамилия», запрос необходимо модифицировать следующим образом:

select Фамилия from Туристы;

Результат выполнения данного запроса представлен на рисунке 28.

Для вывода определенного количества записей используем следующий запрос (рис. 29):

select top 3 Фамилия from Туристы;

Извлекаются первые три записи поля «Фамилия», расположенные в самой таблице «Туристы». Обратим внимание на то, что фамилии расположены не в алфавитном порядке, а в порядке, в котором они были сохранены в базе данных.

Добиться алфавитного порядка можно с помощью предложения order by , содержащего список атрибутов, после каждого из которых стоит либо ключевое словоasc (сортировка по возрастанию), либо ключевое словоdec (сортировка по убыванию). Теперь предыдущий запрос может выглядеть так:

select top 3 Фамилия from Туристы order by Фамилия asc;

Вводя оператор percent , можем получить указанный процент записей от общего числа:select top 25 percent Фамилия from Туристы;

Для отбора записей, отвечающих заданному условию, используем опера-

тор where :

select * from Туры where Цена > 27000;

Этот запрос возвращает все записи из таблицы «Туры», в которых поле «Цена» имеет значение, большее 27000 (рис. 31).

Рис. 31. Отбор записей со всеми полями по заданному значению

Оператор where поддерживает работу со знаками сравнения <, >, >=, <=.

Точную выборку только из заданного множества значений осуществляет оператор in , в следующем примере извлекаются лишь те записи, в которых значение поля «Цена» в точности равно либо 10 000, либо 20 000, либо 30 000 (рис. 32):

select * from Туры where Цена in (10000, 20000, 30000);

Рис. 32. Отбор записей по точному совпадению значений поля Цена

Выборка значений, лежащих в указанном интервале, осуществляется опе-

ратором between <первое_значение> and <второе_значение>(рис. 33):

Select * from Туры where Цена between 10000 and 30000;

Рис. 33. Отбор записей по значениям в указанном интервале поля Цена

2.2. Агрегирующие функции языка SQL

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

Агрегирующие функции производят вычисление одного «собирающего» значения (суммы, среднего, максимального, минимального значения и т. п.) для заданных групп строк таблицы. Группы строк определяются различными значениями заданного поля (полей) таблицы. Разбиение на группы выполняется с помощью предложенияgroup by .

Рассмотрим перечень агрегирующих функций.

Count определяет количество записей данного поля в группе строк.

sum вычисляет арифметическую сумму всех выбранных значений данного поля.

avg рассчитывает арифметическое среднее (усреднение) всех выбранных значений данного поля.

max находит наибольшее из всех выбранных значений данного поля.

min находит наименьшее из всех выбранных значений данного поля.

Для определения общего числа записей в таблице Туристы используем запрос select count (*) from Туристы;

Результат выполнения запроса представлен на рисунке 34.

Отметим, что результатом запроса является одно число, содержащееся в поле с отсутствующим именем.

Рис. 34. Результат запроса с функцией count

select Фамилия, count (Фамилия) from Туристы group by Фамилия;

Синтаксис использования других операторов одинаков – следующие запросы извлекают сумму, арифметическое среднее, наибольшее и наименьшее значения поля «Цена» таблицы «Туры» (здесь заданной группой записей, как и в первом примере с функцией count , являются все записи таблицы).

select sum(Цена) from Туры select avg(Цена) from Туры select max(Цена) from Туры select min(Цена) from Туры

Если значение поля может быть незаполненным, то для обращения к таким полям необходимо использовать оператор null . Отметим, что величинаnull не означает, что в поле стоит число 0 (нуль) или пустая текстовая строка. Существует два способа образования таких значений:

1) Microsoft SQL Server автоматически подставляет значение null , если в значение поля не было введено никаких значений и тип данных для этого поля не препятствует присвоению значенияnull ;

2) или если пользователь явным образом вводит значение null .

2.3. Оператор сравнения записей like

Оператор сравнения like нужен для поиска записей по заданному шаблону. Эта задача является одной из наиболее часто встречаемых задач – например, поиск клиента с известной фамилией в базе данных.

Предположим, что в таблице «Туристы», содержащей поля «Фамилия», «Имя» и «Отчество», требуется найти записи клиентов

с фамилиями, начинающимися на букву «И».

Рис. 35. Запрос с оператором like select Фамилия, Имя, Отчество from Туристы where Фамилия Like "И%"

Результатом этого запроса будет таблица, представленная на рисунке 35.

Оператор like содержит шаблоны, позволяющие получать различные результаты (таблица 7).

Таблица 7 Шаблоны оператора like

2.4. Команды определения данных языка SQL

Пока мы познакомились только с работой некоторых команд языка SQL по извлечению таблиц и данных из таблиц, предполагая, что сами таблицы были созданы кем-то ранее. Это наиболее реальная ситуация, когда небольшая группа людей (проектировщики баз данных) создает таблицы, которые затем используются другими людьми. Эти команды относятся к области SQL, называемой DML (Data Manipulation Language, или Язык Манипулирования Данными). Тем не менее существует специальная область SQL, называемая DDL (Data Definition Language, или Язык Определения Данных), которая специально работает над созданием объектов данных.

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

Пример команды create table :create table ClientInfo (

FirstName varchar(20), LastName varchar(20), Address varchar(20), Phone varchar(15));

Тип varchar предназначен для хранения символов не в кодировке Unicode. Число, указываемое в скобках, определяет максимальный размер поля и может принимать значение от 1 до 8000. Если введенное значение поля меньше зарезервированного, при сохранении будет выделяться количество памяти, равное длине значения. После выполнения этого запроса в окне «Сообщения» появляется сообщение:

Команды выполнены успешно.

После перезапуска Management Studio в списке таблиц появилась новая таблица (рис. 36).

Итак, была создана таблица, состоящая из четырех полей типа varchar , причем для трех полей была определена максимальная длина 20 байт, а для одного – 15. Значения полей не заполнены – на это указывает величинаNull .

Можно удалить созданную таблицу непосредственно в интер-

фейсе Management Studio, щелкнув правой кнопкой и выбрав «Уда-

лить». Рис. 36. Созданная таблица в базе данных

2.5. Команды изменения данных языка DML

Значения могут быть помещены и удалены из полей тремя командами языка DML (Язык Манипулирования Данными):

insert (вставить),

update (изменить),

delete (удалить).

Команда insert имеет свои особенности.

1) При указании значений конкретных полей вместо использования какихлибо значений можно применить ключевое слово DEFAULT.

2) Вставка пустой строки приводит к добавлению пробела, а не значения

3) Строки и даты задаются в апострофах.

4) Можно задавать NULL явно, а можно задавать DEFAULT.

Например:

insert into ClientInfo (FirstName, LastName, Address, Phone) values("Petr","Petrov","Chehova 13","1234567");

Однократное выполнение этого запроса (нажатие клавиши F5 один раз) приводит к добавлению одной записи. Добавим еще несколько записей, изменяя значения values :

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");

Извлечем все записи созданной таблицы (рис. 37): select * from ClientInfo;

Рис. 37. Все записи таблицы ClientInfo

Отметим, что третья строка содержит значение null , а не текстовую строку

Команда update позволяет изменять заданные значения записей:update ClientInfo set FirstName = "Andrey" where FirstName = "Petr";

В этом случае в первой записи поля FirstName значениеPetr изменится на

Andrey (рис. 38).

Рис. 38. Изменение одной записи

Отметим, что если не указывать условие, определяющее значение, которое необходимо изменить, команда update затронет все записи.

Команда delete удаляет записи из таблицы.delete from ClientInfo where LastName like "Petrov";

Результатом этого запроса будет удаление первой записи из таблицы

Если не задавать условие, определяющее данные, которые необходимо удалить, то будут удалены все данные таблицы.

Запросы с командами insert ,update иdelete могут содержать в себе все прочие конструкции языка SQL.

2.6. Контрольные вопросы и задания к разделу 2

1. Напишите SQL-запросы для вывода на экран содержания всех таблиц БД (для каждой таблицы свой запрос, см. пример из п. 2.1.select * from Туристы; ).

2. Добавьте к соответствующим запросам задания п. 1 сортировку по фамилиям и именам.

3. Что будет выведено на экран в результате выполнения следующего запроса:

select Фамилия, Имя, Отчество from Туристы order by Имя dec, Отчество asc; ?

4. Напишите SQL-запрос, который позволит отобрать всех туристов, проживающих в заданном городе. Используйте сортировку при выводе.

5. Посчитайте туристов с одинаковыми именами.

7. Как определить среднюю цену по турам в Париж, (например, цена может меняться в зависимости от сезона)?

8. Как будет выглядеть таблица «Туристы» после выполнения следующей

SQL-команды: update Туристы set Имя = "Владимир"?

9. Что произойдет с таблицей «Туристы» после выполнения SQL-команды: delete from Туристы where Отчество like "Иван" ?

10.Что произойдет с таблицей «Туры» после выполнения SQL-команды: delete from Туры ?

11.Выясните с помощью SQL-запроса к БД, кто из туристов еще не оплатил свои путевки? Подсчитайте их количество и общую сумму недоплаты.

12.Распечатайте все предлагаемые турфирмой туры в алфавитном порядке.

13.Составьте с помощью оператора update SQL-команду для переименования города Ульяновска в Симбирск в информации о туристах.

14.Распечатайте все предлагаемые турфирмой туры с сезонами.

15.Выведите полную информацию о туристах, выкупивших путевки на какойнибудь определенный тур и сезон.

16.С учетом внесенных изменений в структуру БД по заданию п.12 раздела 1.5. постройте SQL-запрос, выводящий полную контактную информацию о туристах, имеющих долги по оплате своих путевок.

17.С помощью операторов добавления в БД перенесите соответствующую информацию из отношений «Туристы» и «Информация о туристах» в отноше-

ние ClientInfo.

18.Сформулируйте на естественном языке содержание следующих SQLзапросов к БД:

select *from Туристыwhere Имяin ("Владимир", "Иван");select *from Сезоныorder by [Количество мест]dec ;

update ClientInfoset FirstName = "Andrey"where FirstName = NULL;