Лекции по информатике
  • Регистрация
1 1 1 1 1 1 1 1 1 1 Рейтинг 0.00 (0 Голоса)

Лекция Тема: Запросы. SQL–запросы

8.1. Основы языка Jet SQL.

8.2. Алгоритм создания запроса SQL в СУБД MS Access.

8.3. Запрос на объединение записей.

8.4. Запрос к серверу.

8.4. Управляющий запрос.

8.4. Подчиненный запрос.

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

Существует ряд запросов, которые не поддерживаются в QBE, например: запрос на объединение записей из нескольких таблиц, управляющий запрос, запрос к серверу. Такие запросы можно создать, только написав инструкцию на языке SQL.

В свойствах Источник записей форм и отчетов, Источник строк элементов управления Поле со списком, Список и некоторых других в качестве значения может использоваться инструкция SQL. При программном доступе к данным на языке VBA тоже используются инструкции SQL.

На основании вышеизложенного можно сделать вывод о том, что язык SQL является стандартом для работы с данными.

Язык SQL был разработан сотрудниками фирмы IBM в начале 70-х в рамках работы над проектом системы управления реляционными базами данных. Действующим на данный момент стандартом языка SQL является принятая Американским национальным институтом стандартов (ANSI) версия SQL92.

Язык SQL предназначен для выполнения операций над таблицами (создание, удаление, изменение структуры) и над данными таблиц (выборка, изменение, добавление и удаление), а также администрирования базы данных.

Фирмы разработчики СУБД при реализации языка SQL могут вносить в него расширения, но обязаны реализовывать базовый набор команд ANSI SQL. Различные реализации встроенного SQL могут отличаться друг от друга по составу операторов и синтаксису за счет расширения стандартов.

Язык Access SQL (Jet SQL) в основном соответствует стандарту SQL-89 и существенно отличается от стандарта ANSI SQL.

8.1. Основы языка Jet SQL

Язык Jet SQL состоит из инструкций (команд, операторов).

Инструкции Jet SQL делятся на две группы, два подъязыка:

язык определения данных (Data Definition Language – DDL);

язык манипулирования данными (Data Manipulation Language – DML).

Основные инструкции языка Jet SQL представлены в таблице 8.1.

Таблица 8.1

Основные инструкции Jet SQL

Язык

Инструкция

 

DML

SELECT

 

UPDATE

изменение записей

INSERT

вставка новых записей

DELETE

удаление записей

DDL

CREATE TABLE

 

DROP TABLE

удаление таблицы

ALTER TABLE

изменение структуры таблицы

CREATE INDEX

создание индекса

DROP INDEX

удаление индекса

CREATE VIEW

создание представления (таблицы, формируемой в результате выполнения запроса)

DROP VIEW

удаление представления

GRAND

назначение привилегий

REVOKE

удаление привилегий

Инструкция SELECT

Является ядром (главной командой) языка SQL и используется для отбора строк (записей) и столбцов (полей) из таблиц базы данных.

Синтаксис:

SELECT [ALL | DISTINCT | DISTINCTROW] <список полей>
FROM <список таблиц/запросов>
[WHERE <спецификация выбора строк>]
[GROUP BY <спецификация группировки>]
[HAVING <спецификация выбора групп>]
[ORDER BY <спецификация сортировки>]

Описание ключевых слов (предложений):

SELECT

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

Предикаты:

ALL – вывести все строки (используется по умолчанию)

DISTINCT – вывести строки, у которых значения всех полей, выводящихся в режиме таблицы, являются уникальными

DISTINCTROW – вывести строки с уникальными наборами значений полей

FROM

– определяет таблицы и/или запросы, которые служат источником данных для запроса (обязательный параметр);

WHERE

– задает условие отбора строк;

GROUP BY

– задает поля, которые используются для формирования групп из выбранных строк;

HAVING

– задает условие отбора групп;

ORDER BY

– задает порядок расположения (упорядочивания) строк:

ASC – по возрастанию (используется по умолчанию),

DESC – по убыванию.

Примеры:

Все примеры основаны на учебной базе данных "Борей".

Получить полную информацию обо всех товарах

SELECT * FROM Товары;

* – это сокращение для списка всех имен столбцов в таблице (таблицах) на которую делается ссылка в предложении FROM.

Получить информацию о сотрудниках (фамилия, имя, должность и год рождения), которые были приняты на работу в период с 15 марта 1993 года по 15 марта 1994 года

SELECT Фамилия, Имя, Должность, Year(ДатаРождения) AS Год

FROM Сотрудники

WHERE ДатаНайма Between #15/03/1993# And #15/03/1994#;

Год – это имя вычисляемого поля, содержащего выражение Year(ДатаРождения).

Получить список товаров категорий "Приправы" и "Фрукты" (категория, марка, количество на складе) поставки, которых не прекращены. Список упорядочить по убыванию количества товара на складе в каждой категории.

SELECT Типы. Категория, Товары. Марка, Товары. НаСкладе

FROM Типы INNER JOIN Товары ON Типы. КодТипа = Товары. КодТипа

WHERE (Типы. Категория='Приправы' Or Типы. Категория='Фрукты')

AND Товары. ПоставкиПрекращены = False

ORDER BY Типы. Категория, Товары. НаСкладе DESC;

Получить список стран, в которых есть клиенты, но нет поставщиков.

SELECT DISTINCT Клиенты. Страна

FROM Клиенты LEFT JOIN Поставщики

ON Клиенты. Страна = Поставщики. Страна

WHERE Поставщики. Страна Is Null

ORDER BY Клиенты. Страна;

Получить стоимость каждого заказа с учетом скидки, исполненного в 1998 году и оформленного сотрудницей Белова Мария.

SELECT [Фамилия] & " " & [Имя] AS ФИО, Заказы. КодЗаказа, Sum([Цена]*[Количество]*(1-[Скидка])) AS Стоимость

FROM Сотрудники INNER JOIN (Заказы INNER JOIN Заказано

ON Заказы. КодЗаказа = Заказано. КодЗаказа)

ON Сотрудники. КодСотрудника = Заказы. КодСотрудника

WHERE Year([ДатаИсполнения]) = 1998

GROUP BY Фамилия] & " " & [Имя], Заказы. КодЗаказа

HAVING [Фамилия] & " " & [Имя] = "Белова Мария";

В рамках одной лекции описать все инструкции языка SQL невозможно, поэтому ограничимся представленными примерами и рассмотрим возможности SQL по созданию запросов, которые нельзя создать в QBE. Желающие продолжить изучение SQL могут обратиться к специальной литературе и к разделу Справка Microsoft Access / Работа с запросами / Работа с запросами SQL.

8.2. Алгоритм создания запроса SQL в СУБД MS Access

Для создания запроса SQL необходимо выполнить следующие действия:

1. Создать новый запрос с использованием Конструктора запросов.

2. Закрыть диалоговое окно Добавление таблицы, не добавляя таблиц.

3. Выбрать команду Вид | Режим SQL для вывода окна SQL.

4. Удалить весь текст, который может быть выведен в окне SQL.

5. Ввести в окне инструкцию SQL, используя для ввода новой строки комбинацию клавиш <Ctrl>+<Enter>.

6. Нажать на панели инструментов Конструктор запросов кнопку Запуск, чтобы вывести результирующее множество записей.

7. Для последующего использования сохранить запрос.

8.3. Запрос на объединение записей

Запрос на объединение записей – это запрос, в котором в результирующем наборе записей объединяются строки из нескольких таблиц, запросов и инструкций SELECT по одному или нескольким полям.

Синтаксис:

{TABLE <имя таблицы/запроса> | <инструкция_SELECT>}

UNION

[ALL] {TABLE <имя таблицы/запроса> | <инструкция_SELECT>}

[UNION

[ALL] {TABLE <имя таблицы/запроса> | <инструкция_SELECT>}]

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

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

Пример:

Вывести в одном списке данные о клиентах и поставщиках (страна, город, название, контактное лицо и тип принадлежности: клиент или поставщик).

Рис.8.1. Запрос на объединение в окне Режим SQL.

8.4. Запрос к серверу

Запрос к серверу, в отличие от других запросов, передается на сервер базы данных, к которому выполняется обращение, например MS SQL Server.

Главной особенностью этого запроса состоит в том, что он должен использовать синтаксис языка SQL сервера базы данных (для MS SQL Server – это Transact-SQL).

8.5. Управляющий запрос

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

Инструкции, которые используются для создания управляющих запросов, как отмечалось выше (см. табл.8.1), принадлежат подмножеству языка SQL, которое называется DDL – язык определения данных.

Набор этих инструкций в Jet SQL существенно сокращен по сравнению со стандартом ANSI SQL, т. к. многие операции можно выполнить с помощью других средств (команд меню или окна базы данных).

Инструкция CREATE TABLE

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

Синтаксис:

CREATE TABLE <имя таблицы>

(<имя поля> <тип данных> [(<размер>)] [NOT NULL] [UNIQUE]

[, <имя поля> <тип данных> [(<размер>)] [NOT NULL] [UNIQUE]]

[, CONSTRAINT <имя индекса>

PRIMARY KEY (<имя поля> [, <имя поля>])]);

NOT NULL – запрет на пустое значение в поле (обязательное поле);

UNIQUE – индексированное поле (совпадения в индексе не допускаются);

PRIMARY KEY – определение первичного ключа таблицы.

Пример:

Создать таблицу Должности со структурой представленной в таблице 8.2.

Таблица 8.2

Имя поля

Тип
данных

Размер

Ключевое
поле

Индекс

Обязательное
поле

КодДолжности

числовой

длинное

целое

Да

   

НаимДолжности

текстовый

30

 

Да

Да

CREATE TABLE Должности

(КодДолжности LONG, НаимДолжности TEXT(30) UNIQUE NOT NULL,

CONSTRAINT Индекс1 PRIMARY KEY (КодДолжности));

Инструкция ALTER TABLE

Позволяет изменить структуру таблицы.

Синтаксис:

ALTER TABLE <имя таблицы>

{ADD | DROP} <имя поля> [<тип данных> [(<размер>)]]

[NOT NULL] [UNIQUE]

[{ADD | DROP} <имя поля> [<тип данных> [(<размер>)]]

[NOT NULL] [UNIQUE]];

Примеры:

Добавить в таблицу Должности поле Оклад (тип данных: денежный).

ALTER TABLE Должности ADD Оклад CURENCY;

Удалить из таблицы Должности поле Оклад.

ALTER TABLE Должности DROP Оклад;

Инструкция DROP

Позволяет удалить индекс или таблицу.

Синтаксис:

DROP {TABLE <имя таблицы> |

INDEX <имя индекса> ON <имя таблицы>}

Пример:

Удалить таблицу Должности.

DROP TABLE Должности;

8.6. Подчиненный запрос

Инструкции Jet SQL, такие как SELECT, SELECT … INTO, INSERT … INTO,
DELETE или UPDATE позволяют в предложении WHERE использовать другой запрос. Этот запрос называется подчиненным запросом.

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

– <выражение> [NOT] IN (<инструкция SELECT>);

– <выражение> <оператор сравнения> [ANY | SOME | ALL]
(<инструкция SELECT>).

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

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

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

Примеры:

Удалить данные о клиентах, которые не оформили ни одного заказа.

 


 

 

Рис.8.2. Запрос с подчиненным в окне Конструктор (а) и Режим SQL (б).

Получить список товаров (код, марка, цена) с ценой выше средней.

SELECT КодТовара, Марка, Цена

FROM Товары

WHERE Цена > (SELECT AVG(Цена) FROM Товары)

ORDER BY Цена;

Добавить комментарий


Защитный код
Обновить

По темам:

История Украины

Культурология

Высшая математика

Информатика

Охотоведение

Статистика

География

Военная наука

Английский язык

Генетика

Разное

Технологиеские темы

Украинский язык

Филология

Философия

Химия

Экология

Социология

Физическое воспитание

Растениевосдство

Педагогика

История

Психология

Религиоведение

Плодоводство

Экономические темы

Бухгалтерские темы

Маркетинг

Иностранные языки

Ветеринарная медицина

Технические темы

Землеустройство

Медицинские темы

Творчество

Лесное и парковое хозяйство