Учись Учиться

Конспект лекции
Информатика
17 February 2012

Обработка информации средствами структурированного языка запросов SQL

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 
    Tags:
  • Обработка информации
  • средствами структурированного языка запросов SQL
  • Компьютерные технологии
  • подготовки текстовых документов
  • обработки экономической информации на основе табличных процессоров
  • использования систем
  • управления базами данных (СУБД)
  • интегрированных программных пакетов
  • сорок инструкций
  • Запросы на выборку.

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

 

Обработка информации средствами структурированного языка запросов SQL.

 

1. Инструкции SQL. Запросы на выборку. 

1. Инструкции SQL. Запросы на выборку

 

В SQL существует приблизительно сорок инструкций каждая из них «просит» СУБД выполнить определенное действие, например, извлечь данные, создать таблицу или добавить в таблицу новые данные.

Инструкции SQL группируются по выполняемым действиям:

обработка данных:

SELECT – извлекает данные из таблицы;

INSERT – добавляет новые строки в таблицу;

DELETE – удаляет строки из таблицы;

UPDATE – обновляет данные существующие в таблице;

определение данных:

CREATE TABLE – добавляет новую таблицу в БД;

DROP TABLE – удаляет таблицу;

ALTER TABLE – изменяет структуру существующей таблицы;

CREATE VIEW – добавляет новое представление в таблицу;

DROP VIEW – удаляет представление;

CREATE INDEX – создает индекс для столбца;

DROP INDEX – удаляет индекс;

CREATE SCHEMA – добавляет новую схему в БД;

DROP SCHEMA – удаляет схему;

CREATE DOMAIN – добавляет новый домен в БД;

ALTER DOMAIN – изменяет определение домена;

DROP DOMAIN – удаляет домен;

управление доступом:

GRANT – представляет пользователю определенную привилегию доступа;

REVOKE – отменяет указанную привилегию доступа;

управление транзакциями:

COMMIT – завершает текущую транзакцию;

ROLLBACK – отменяет текущую транзакцию;

SET TRANSACTION – определяет режим доступа к данным для текущей транзакции;

программный SQL:

DECLARE – определяет набор записей в который будут возвращены результаты запроса;

OPEN – открывает набор записей;

FETCH – извлекает строку из таблицы результатов запроса;

CLOSE – закрывает набор записей;

PREPARE – подготавливает инструкцию SQL к динамическому выполнению;

EXECUTE – динамически выполняет инструкцию SQL;

DESCRIBE – возвращает описание подготовленного запроса.

Все инструкции SQL имеют одинаковую структуру (рис.1).

 

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

Графически допустимые формы инструкций иллюстрируются с помощью синтаксических диаграмм (рис.2).

Имена в инструкциях SQL указывают, над каким объектом БД инструкция должна выполнит действие. Имена могут содержать от 1 до 128 символов, начинаться с буквы и не содержат пробелов и специальных символов.

Обращение к таблице может производиться в соответствии с именем или по схеме, в которую включена таблица. В полном имени таблицы указывается имя пользователя и имя таблицы (например, Sam.orders). Обращение к таблице, включенной в схему осуществляется с указанием названия схемы и имени таблицы (например, schema1.orders). Имена столбцов задаются с указанием имени таблицы (или без него), имени пользователя, которому принадлежит таблица (или без него), названия схемы, в которую включена таблица (или без него). Например, полное имя столбца может выглядеть следующим образом: orders.customer; schema1.orders.customer; Sam.orders.customer.

Язык SQL предназначен для выполнения запросов к БД. Наиболее мощной инструкцией языка является инструкция SELECT, с помощью которой производится выборка информации из таблиц БД. Например,

 

SELECT city, target, sales

FROM offices

Вывести список офисов с их плановыми и фактическими объемами продаж

 

Синтаксическая диаграмма инструкции SELECT имеет вид (рис.3). Инструкция состоит из шести предложений. Предложения SELECT и FROM являются обязательными, а остальные включаются в инструкцию при необходимости.

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

FROM – указывается список таблиц, которые содержат элементы данных, извлекаемые запросом.

WHERE – показывает, что в результаты запроса следует включать только некоторые строки (условия отбора).

 

GROUP BY – позволяет создать итоговый запрос.

HAVING – показывает, что в результаты запроса следует включать только некоторые из групп, созданных с помощью предложения GROUP BY.

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

Предикат Distinct позволяет исключить повторяющиеся строки.

Результатом выполнения SQL-запроса всегда является таблица, содержащая данные и ничем не отличающаяся от таблицы БД. Если пользователь набирает инструкцию SQL в интерактивном режиме, СУБД выводит результаты на экран в табличной форме. Если программа посылает запрос СУБД с помощью программного SQL, то СУБД возвращает таблицу результатов программе (рис.4).

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

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

SELECT city, region, (sales – target)

FROM offices.

Запросы на выборку с условиями отбора. (Сравнение. Проверка на принадлежность диапазону значений. Проверка на членство в множестве.   Проверка на соответствие шаблону. Составные условия отбора).

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

Условия:

Сравнение – значение одного выражения сравнивается со значение другого выражения.

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

Проверка на соответствие шаблону – проверяется, соответствует ли строковое значение, содержащееся в столбце, определенному шаблону.

Проверка на равенство значению NULL – проверяется, содержится ли в столбце значение NULL.

Синтаксическая диаграмма операции сравнения выглядит следующим образом (рис.5).

Одной из форм проверки на принадлежность диапазону значений является оператор BETWEEN … AND (рис.6).

Проверка на членство в множестве осуществляется с помощью оператора IN (рис.7).

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

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

Подстановочный знак «%» совпадает с любой последовательностью из нуля или более  символов. Например:

SELECT company, credit

FROM customers

WHERE company LIKE ‘Smith% Corp.’, - при этом вместо знака «%» добавятся любые символы, содержащиеся в элементах данных.

Подстановочный знак «_» совпадает с любым отдельным символом. Например:

SELECT company, credit

FROM customers

WHERE company LIKE ‘Smiths_n Corp.’

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

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

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

SELECT order_num, product

FROM orders

WHERE product LIKE ¢ A$%B% ¢ ESCAPE ¢ $ ¢.

Проверка на равенство значению NULL производится с помощью оператора IS NULL (рис.9).

Составные условия отбора – операторы AND, OR, NOT (рис.10).

Оператор OR используется для объединения двух условий отбора, из которых как минимум одно должно быть истинным. Для объединения двух условий отбора, оба из которых должны быть истинными, следует использовать оператор AND. Оператор NOT используется для выбора строк, для которых условие отбора ложно.

С помощью операторов AND, OR, NOT можно создавать сложные условия отбора.

 

Таблица истинности оператора AND

 

AND

TRUE

FALSE

NULL

TRUE

TRUE

FALSE

NULL

FALSE

FALSE

FALSE

FALSE

NULL

NULL

FALSE

NULL

 

Таблица истинности оператора OR

 

OR

TRUE

FALSE

NULL

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

NULL

NULL

TRUE

NULL

NULL

 

Таблица истинности оператора NOT

 

NOT

TRUE

FALSE

NULL

 

FALSE

TRUE

NULL

 

Иногда появляется необходимость объединения результатов двух или более запросов в одну таблицу. SQL поддерживает такую возможность с помощью операции UNION. Вывести список всех товаров, цена которых превышает 2000 или которых было заказано более чем на 300000 за один раз.  

Первой части основного запроса удовлетворяет запрос (рис.11), изображенный в верхней части рисунка:

SELECT mfr_id, product_id

FROM products

WHERE price > 2000.

Подобным образом можно составить запрос на вторую часть исходного запроса:

SELECT DISTINCT mfr, product

FROM orders

WHERE amount > 30000.

Операция UNION создает одну таблицу результатов запроса, в которой содержатся строки результатов как верхнего, так и нижнего запросов:

SELECT mfr_id, product_id

FROM products

WHERE price > 2000

UNION

SELECT DISTINCT mfr, product

FROM orders

WHERE amount > 30000.

Чтобы таблицы результатов можно было объединить с помощью операции UNION они должны соответствовать следующим требованиям:

две таблицы должны содержать одинаковое число столбцов;

тип данных каждого столбца первой таблицы должен совпадать с типом данных соответствующего столбца во второй таблице;

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

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

Запрос на объединение и сортировка – объединенные результаты запросов, возвращенные операцией UNION, можно отсортировать с помощью предложения ORDER BY, следующего за второй инструкцией SELECT.

В SQL существует понятие подчиненного запроса; механизм подчиненных запросов позволяет использовать результаты одного запроса в качестве составной части другого.

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

Вывести список офисов, для которых плановый объем продаж превышает сумму плановых объемов продаж всех служащих.

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

SELECT city

FROM offeces

WHERE target >???

Здесь величина ??? равна сумме плановых объемов продаж всех служащих, работающих в данном офисе.

SELECT SUM(quota)

FROM salesreps

WHERE rep_office = office

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

SELECT city

FROM offeces

WHERE target > (SELECT SUM(quota)

FROM salesreps

WHERE rep_office = office).

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

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

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

·        таблица результатов подчиненного запроса всегда состоит из одного столбца. Это означает, что в предложении SELECT подчиненного запроса всегда указывается один возвращаемый столбец;

·        в подчиненный запрос не может входить предложение ORDER BY. результаты подчиненного запроса используются только внутри главного запроса и для пользователя остаются невидимыми, поэтому нет смысла их сортировать;

·        имена столбцов в подчиненном запросе могут являться ссылками на столбцы таблиц главного запроса;

·        подчиненный запрос не может быть запросом на объединение (UNION) нескольких различных инструкций SELECT; допускается использование только одной инструкции SELECT.

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

§  сравнение с результатом подчиненного запроса (=, , , =). Значение выражения проверяется на равенство, которое возвращается подчиненным запросом. Эта проверка происходит также как простое сравнение. Подчиненный запрос, участвующий в операции сравнения, должен возвращать в качестве результата единичное значение, т.е. одну строку, содержащую один столбец. Если результатом подчиненного запроса являются несколько строк или несколько столбцов, то сравнение не имеет смысла и СУБД выдает сообщение об ошибке. Если в результате выполнения подчиненного запроса не будет получено ни одной строки или будет получено значение NULL, то операция сравнения возвращает значение NULL. В стандарте SQL1 подчиненный запрос  может находиться только справа от оператора сравнения. В стандарте SQL 2 – с любой стороны;

§  проверка на принадлежность результатам подчиненного запроса (предикат IN). Значение выражения проверяется на равенство одному из множества значений, которые возвращаются подчиненным запросом. Эта проверка происходит также как проверка на членство во множестве.  Например:

Вывести список служащих тех офисов, где фактический объем продаж превышает плановый.

SELECT name

FROM salesreps

WHERE rep_office IN (SELECT office

FROM offices

WHERE sales > target);

§  проверка на существование (предикат EXISTS). Проверяется наличие строк в таблице результатов подчиненного запроса. Например:

вывести список всех товаров, на которые получен заказ 25000 или больше

можно перефразировать этот запрос:

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

SELECT description

FROM products

WHERE EXISTS (SELECT order_num

FROM orders

WHERE product = product_id AND mfr = mfr_id

AND amount >= 25000)

Предикат EXISTS не использует результаты подчиненного запроса. Проверяется только наличие результатов. Поэтому в подчиненном запросе допускается использование формы «SELECT *».

§  многократное сравнение (предикаты ANY и ALL). Значение выражения сравнивается с каждым из множества значений, которые возвращаются подчиненным запросом.

При проверке ANY, для того чтобы сравнить проверяемое выражение со столбцом данных, отобранных подчиненным запросом, используется один их шести операторов сравнения (=, , , =). Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если любое из этих сравнений дает результат TRUE, то проверка ANY возвращает значение TRUE. Например:

Вывести список служащих принявших заказ на сумму большую, чем 10% от их плана

SELECT name

FROM salesreps

WHERE (0.1 * quota < ANY (SELECT amount

FROM orders

WHERE rep = empl_num))

Главный запрос проверяет все строки таблицы salesreps. Подчиненный запрос находит все заказы, принятые «текущим» служащим, и возвращает столбец, содержащий стоимости этих заказов. Предложение  WHERE главного запроса вычисляет 10% от плана текущего служащего и использует это число в качестве проверяемого значения, сравнивая его со стоимостью каждого заказа, отобранного подчиненным запросом. Если есть хотя бы один заказ, стоимость которого превышает вычисленное проверяемое значение, то проверка ANY возвращает значение TRUE, а имя служащего заносится в таблицу результатов запроса. Форму «WHERE X < ANY (SELECT Y…)» следует читать «где для некоторого Y Х меньше, чем Y».

В проверке ALL, как и в проверке ANY, используется один из шести операторов (=, , , =) для сравнения проверяемого значение со столбцом данных, отобранных подчиненным запросом. Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если все сравнения дают результат TRUE, то проверка ALL возвращает результат TRUE.

Форму «WHERE X < ALL (SELECT Y…)» следует читать «где для все Y Х меньше, чем Y».

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

Вывести список клиентов, закрепленных за служащими, работающими в офисах восточного региона

SELECT company

FROM customers

WHERE cust_rep IN (SELECT empl_num

FROM salesreps

WHERE rep_office IN (SELECT office

FROM offices

WHERE region = ‘Eastern’

 

Выполняются такие запросы, начиная с самого внутреннего.

 

    Tags:
  • Обработка информации
  • средствами структурированного языка запросов SQL
  • Компьютерные технологии
  • подготовки текстовых документов
  • обработки экономической информации на основе табличных процессоров
  • использования систем
  • управления базами данных (СУБД)
  • интегрированных программных пакетов
  • сорок инструкций
  • Запросы на выборку.
Rating:
( 0 Rating )
  • Дипломная работа
    • Дипломы по физике
    • по психологии
  • Курсовая работа
  • Реферат
  • доклад
  • Статьи
  • Педагогика
  • Конспект лекций
  • Аннотация
  • Лабораторная работа
  • контрольная
  • Иследования
  • конспект урока

Недавно добавили

Тема 10. Самоменеджмент. Система планирования времени. 25 April 2011
Что такое музыка. Как и когда она появилась и почему не потеряла своих позиций в жизни человека? 26 March 2012
Жизненные формы животных 02 April 2012
Отчет по производственной практике 11 January 2011
История отечественного государства и права 24 March 2012
Применение интерактивной доски в процессе образования 12 January 2011
Структура организации, Задача, Технология, Организационная культура, Необходимость управления, Подразделения, Формальная и неформальная организация, Концепция жизненного цикла организации, Компетентность, Уровни управления, 05 March 2011
Экскурсия — еще одна очень интересная форма проведения экологических занятий 04 March 2012
Здравоохранение Японии 03 March 2012
ФИЗИЧЕСКОЕ РАЗВИТИЕ ДЕТЕЙ С ПОЧЕЧНОЙ ПАТОЛОГИЕЙ 29 October 2011

Самое читаемое

  • Методика «Счет по Крепелину»
  • Характеристика На ученика 8а класса
  • Корректурная проба
  • Психолого-педагогическая характеристика на ученика 7«В» класса
  • Психологическая диагностика и коррекция агрессивного поведения детей и подростков
  • Понятие и виды преступности несовершеннолетних.
  • Список литературы по менеджменту
  • Лекция 3. Планирование научного исследования. Формулировка целей и задач.
  • Лекция 11. Научные методы исследования. Правила выбора методов в соответствии с темой и задачами. Виды методов для различных направлений исследования.
  • Отчет по производственной практике

Последние новости

Создание и исследование компьютерного электрокардиографа 02 February 2014
Оценка загрязнения продуктов питания на примере кисло-молочной продукции региональных производителей 02 February 2014
Дистанционная ударноволновая литотрипсия 01 February 2014
Исследование возможностей использования шумомера 01 February 2014
Методы компьютерной обработки визуальной информации УЗ-сканеров 25 January 2014
Правовое регулирование медиации в России 19 January 2014
Психологическая диагностика и коррекция агрессивного поведения детей и подростков 19 January 2014
Итоговое игровое профориентационное занятие «Как устроиться на работу» 19 January 2014
Участие населения в правотворческом процессе муниципальных образований: реальность или фикция? 18 January 2014
Хрематонимы города Кирова: опыт комплексного анализа 18 January 2014

Сейчас читают

CSS 09 May 2011
Политический кризис и реформа в Малайзии 20 February 2012
ОСОБЕННОСТИ ЭКОЛОГИЧЕСКОГО ВОСПИТАНИЯ В НАЧАЛЬНОЙ ШКОЛЕ 04 March 2012
«Межрегиональные и международные торговые контакты Вятского края XIX – начала XXI века: основные тенденции и факторы развития» 29 October 2011
Структура управления развитием организации 11 January 2011
СУЩНОСТЬ, РОЛЬ И ФУНКЦИИ КРЕДИТА В РЫНОЧНОЙ ЭКОНОМИКЕ 04 November 2013
Парламент Малайзии 21 February 2012
Вопрос 3. Пожизненное наследуемое владение земельными участками. Вопрос 4. Право ограниченного пользования чужим земельным участком (сервитут) 02 March 2011
АКТУАЛЬНЫЕ ПРОБЛЕМЫ СОВРЕМЕННОГО ОБЩЕСТВА.2 11 January 2011
ОБ ИСПОЛЬЗОВАНИИ ПРИМИРИТЕЛЬНЫХ ПРОЦЕДУР ПРИ ОСУЩЕСТВЛЕНИИ ПРАВОСУДИЯ ПО СПОРАМ ЧАСТНОПРАВОВОГО ХАРАКТЕРА 25 March 2012
  • Дипломная работа
    • Дипломы по физике
    • по психологии
  • Курсовая работа
  • Реферат
  • доклад
  • Статьи
  • Педагогика
  • Конспект лекций
  • Аннотация
  • Лабораторная работа
  • контрольная
  • Иследования
  • конспект урока