7 семестр / Лабораторная работа_5

КАФЕДРА ТЕХНОЛОГИЙ ПРОГРАММИРОВАНИЯ

Лабораторная работа №5

ТЕМА: Знакомство с основами языка SQL.

ЦЕЛЬ: Научить создавать запросы с использованием основных типов команд языка SQL.

ПЛАН ЗАНЯТИЯ:

I.Общие сведения о языке SQL.

II.Типы команд SQL.

III. Создание БД «фирма» и занесение исходных данных.

IV. Использование инструкции SELECT в языке запросов.

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

VI. Использование в SQL-запросе сложных выражений и функций для вывода определенных записей.

VII. Выбор данных из более чем одной таблицы. VIII. Вывод данных в определенном порядке.

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

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

I.ОБЩИЕ СВЕДЕНИЯ О ЯЗЫКЕ 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). Благодаря влиянию компании IВМ на рынок вычислительных систем 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 не совсем точно отражает суть этого языка, поскольку он давно уже предназначен не только для построения запросов (если, конечно, под «запросом» понимать только извлечение информации из базы данных). Сейчас SQL позволяет реализовать все функции СУБД:

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

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

II.ТИПЫ КОМАНД SQL

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

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

Таблица 1. Команды языка определения данных

2.Команды языка обработки данных (data manipulation language — DML) исполь-

зуются для добавления, корректировки и удаления строк в таблицах и включают команды (см. таблицу 2):

Заполните созданные таблицы данными, приведенными на рис. 2-4

Рис. 2. Содержимое таблицы «Подразделения»

Рис. 3. Содержимое таблицы «Товары

Рис. 4. Содержимое таблицы «Запасы

IV. ИСПОЛЬЗОВАНИЕ ИНСТРУКЦИИ SELECT В ЯЗЫКЕ ЗАПРОСОВ

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

SELECT [ALL | DISTINCT | DISTINCTROW | TOP] { * I table.* I [table.] field1 [AS alias1 [, [table.] field2 [AS alias2] [, ...]]}

FROM table1 [table1Aias] [, table2 [table2Alias]] [, ...] [IN externaldatabase ] [WHERE criteria]

[GROUP BY groupfieldlist] [HAVING groupcriteria]

[ORDER BY field1 [ASC | DESC ][, field2 [ASC I DESC ]][, ...]]]

[WITH OWNERACCESS OPTION]

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

SELECT означает, что из некоторых таблиц базы данных необходимо выбрать набор (таблицу данных).

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

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

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

После слова FROM указываются таблицы, из которых выбираются ранее указанные поля. Здесь tablel1 (,table2) — это имя таблицы (или таблиц), содержащей данные, externaldatabase — имя базы данных, если не используется текущая база.

ПРИМЕЧАНИЕ: Нет никаких правил написания SQL-инструкций относительно их положения в строке. Можно писать инструкцию в одной строке, можно — в нескольких.

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

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

Рис. 5. Режим SQL меню Вид

1 Это не единственный способ создания SQL запроса

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

Рис. 6. Окно для создания SQL-инструкций

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

SELECT * FROM table

Здесь знак * означает выбор значений всех полей, a table — имя таблицы, из которой выбираются значения.

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

SELECT * FROM Товары

Введите эту инструкцию в окно Запрос 1: запрос на выборку, как показано на рис. 7, и щелкните кнопку Запуск на панели Конструктор запросов. Результат выполнения этой очень простой SQL-инструкции приведен на рис. 8.

Рис. 7. Окно для ввода инструкций SQL

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

WHERE Цена1 < 100

VI. ИСПОЛЬЗОВАНИЕ В SQL-ЗАПРОСЕ СЛОЖНЫХ ВЫРАЖЕНИЙ И ФУНКЦИЙ ДЛЯ ВЫВОДА ОПРЕДЕЛЕННЫХ ЗА-

ПИСЕЙ

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

SELECT НаимТовара аs [Наименование товара], Цена1 аs [Цена закупочная] 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)*"

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