Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
4 Лабораторная работа № 4 Создание сложных запросов 4.1 Объединение таблиц в запросах До сих пор все наши запросы обращались к одной таблице. Однако SQL позволяет в одном запросе обращаться к нескольким таблицам. Именно это свойство и сделало язык SQL столь популярным. В таких запросах вместе с именами каждого из стобцов указываются имена соответствующих таблиц. Это называется подробным определением столбца в запросе. Подробные определения требуются только для тех столбцов, которые присутствуют в нескольких из указанных в запросе таблиц. Но в операторе обычно указываются подробные определения для всех столбцов: SELECT Teacher.Teach_FAM, Chair.Chair_NAME FROM Teacher, Chair WHERE Teacher.Chair_ID = Chair.Chair_ID
Этот оператор возвращает фамилии преподавателей и названия кафедр, на которых они работают. Номер преподавателя присутствует в обеих таблицах, имена двух столбцов таблиц рассматриваются как эквивалентные (что задается равенством).
В следующем запросе столбец Chair_ID имеется в обеих таблицах, но извлекается только из таблицы Teacher, так как во второй таблице соответствующий столбец содержит точно такую же информацию.
SELECT Teacher.*, Chair.Chair_NAME
FROM Teacher, Chair
WHERE Teacher.Chair_ID = Chair.Chair_ID
Использование псевдонимов для имен таблиц. Псевдонимы назначаются таблицам с целью сокращения объема печатания, а также для использования при рекурсивном связывании таблиц. Пример: SELECT A.Teach_ID, B.Chair_NAME
FROM Teacher A,
Chair B
WHERE A.Chair_ID = B.Chair_ID
Рекурсивное связывание удобно использовать, когда все необходимые данные размещаются в одной таблице, но требуется каким-то образом сравнить одни записи таблицы с другими. В нашей базе в таблице Students хранятся фамилии студентов и номера старост этих студентов. Получим список студентов с фамилиями их старост:
SELECT A.Stud_FAM, B.Stud_FAM FROM Students A, Students B WHERE A.Stud_Star = B.Stud_ID
Связывание по нескольким ключам. Предположим вы хотите увидеть, кто из студентов, по каким предметам получили оценки: SELECT Students. Stud_FAM, Subject.Subj_NAME, Progress.OCENKA FROM Students, Subject, Progress WHERE Students.Stud_ID = Progress.Stud_ID AND Subject.Subj_ID = Progress.Subj_ID
SQL Server проверяет каждую комбинацию строк двух таблиц и проверяет их на условие, указанное в предложении WHERE. Если эта комбинация удовлетворяет ему, то она выводится. Предположим, что мы хотим найти всех студентов, сдавших хорошо преподавателю Серикову. Для этого требуется связать четыре таблицы: SELECT Students.Stud_FAM, 'по предмету ', Subject.Subj_NAME, 'получили оценку ', Progress.OCENKA, 'у преподавателя ', Teacher.Teach_FAM FROM Students, Subject, Progress, Teacher WHERE Students.Stud_ID = Progress. Stud_ID AND
Teacher.Teach_ID = Progress.Teach_ID AND
Subject.Subj_ID = Progress.Subj_ID AND
Progress.Ocenka <>2 AND
Teacher.Teach_FAM = 'Сериков '
4.2 Использование подзапросов
Запросы могут управлять другими запросами. Это делается путем помещения запроса внутрь условия другого запроса и использования вывода внутреннего запроса в верном или неверном условии.
Обычно внутренний запрос генерирует значение, которое проверяется в условии внешнего запроса, определяющего верно оно или нет. Например, мы знаем фамилию студента - Сидоров, но не знаем его кода (Stud_ID), и хотим получить все его оценки из таблицы Progress:
SELECT *
FROM Progress
WHERE Stud_ID = (
SELECT Stud_ID
FROM Students
WHERE Stud_FAM = 'Сидоров' )
Чтобы выполнить внешний (основной запрос), сначала выполняется внутренний запрос (подзапрос) внутри предложения WHERE. При выполнении подзапроса просматривается таблица Students, в которой выбираются строки, где поле Stud_FAM равно 'Сидоров', затем извлекается значение поля Stud_ID. Пусть единственной строкой будет Stud_ID = 30104. Далее полученное значение помещается в условие основного запроса, вместо самого подзапроса, так что условие примет вид:
WHERE Stud_ID = 301004
При использовании подзапросов в условиях, основанных на операциях сравнения (больше, меньше, равно, не равно и т.д.), необходимо убедиться, что подзапрос будет возвращать одно и только одно значение. Если ваш подзапрос не вернет никакого значения, то основной запрос не выведет никаких значений.
Если Вы хотите использовать подзапрос, который возвращает несколько строк, то необходимо использовать оператор IN. Например, если в базе несколько студентов с фамилией Сидоров и все имеют оценки:
SELECT *
FROM Progress
WHERE Stud_ID IN (
SELECT Stud_ID
FROM Students
WHERE Stud_FAM = 'Сидоров' ) Найдем все оценки для предмета Информатика:
SELECT *
FROM Progress
WHERE Subj_ID IN (
SELECT Subj_ID
FROM Subject
WHERE Subj_NAME = 'Информатика' )
Этот результат можно получить и с помощью объединения:
SELECT Progress.* FROM Subject, Progress WHERE Subject.Subj_ID = Progress.Subj_ID AND Subject.Subj_NAME = 'Информатика' Хотя этот запрос эквивалентен предыдущему, SQL Server должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их соответствие условию.
Все вышеприведенные подзапросы объединяет то, что все они выбирают один единственный столбец. Это обязательно, поскольку их результат сравнивается с одиночным значением. Команды типа SELECT * запрещены в подзапросах.
Подзапросы можно также использовать в предложении HAVING. Эти подзапросы могут использовать собственные предложения GROUP BY или HAVING. Следующий запрос является тому примером:
SELECT Ocenka, COUNT (DISTINCT Stud_ID )
FROM Progress
GROUP BY Ocenka
HAVING Ocenka > (
SELECT AVG(ocenka )
FROM Progress
WHERE Pr_DATE >04/06/02)
Эта команда подсчитывает студентов с оценкой выше средней, сдавших экзамен после 04.06.02.
Связанные подзапросы. При использовании подзапросов можно обратиться во вложенном подзапросе к таблицам из внешнего подзапроса. Например, как найти всех студентов, сдавших экзамены 3 июня :
SELECT *
FROM Students C
WHERE '2004-06-03' IN (
SELECT Pr_DATE
FROM Progress O
WHERE O.Stud_ID = C.Stud_ID )
Можно было бы использовать объединение следующего вида:
SELECT C.*
FROM Students C, Progress O
WHERE C.Stud_ID = O.Stud_ID AND
O. Pr_DATE = '2004.06.03'
Связанные запросы можно использовать для сравнения таблицы с собой. Например, можно найти всех студентов с баллом выше среднего:
SELECT *
FROM Progress O
WHERE ocenka > (
SELECT AVG(ocenka )
FROM Progress O1
WHERE O1.Stud_ID = O.Stud_ID )
Использование оператора EXISTS. Оператор EXISTS берет подзапрос, как аргумент, и оценивает его как верный, если подзапрос возвращает какие-либо записи и неверный, если тот не делает этого. Вот как выполняется проверка наличия студентов со стипендией = 1200:
SELECT Stud_ID , Stud_FAM, Stud_STIP
FROM Students
WHERE EXISTS (
SELECT *
FROM Students
WHERE Stud_STIP = 2000 )
Внутренний подзапрос выбирает все данные для всех студентов, у которых стипендия = 1200. Оператор EXISTS во внешнем условии отмечает, что подзапрос вернул некоторые данные, следовательно, условие верное. Подзапрос будет выполнен один раз для всего внешнего запроса и имеет одно значение во всех случаях. Поэтому EXISTS, когда используется таким образом, делает условие верным или неверным для всех строк сразу.
Использование операторов ANY, ALL. Рассмотрим новый способ поиска студента, сдавшего экзамен: SELECT * FROM Students
WHERE Stud_ID = ANY (
SELECT Stud_ID
FROM Progress )
Оператор ANY берет все значения, выведенные подзапросом, и оценивает их как верные, если любое из них равняется значению в текущей строке внешнего запроса. Это означает, что подзапрос должен выбирать значения такого же типа как и те, которые сравниваются в основном условии.
В приведенном выше запросе можно было бы использовать оператор IN. Однако оператор ANY можно применять не только с оператором равенства.
Оператор ALL считает условие верным, если каждое значение, выбранное подзапросом, удовлетворяет условию внешнего запроса. Выберем тех студентов, у которых стипендия выше 1500:
SELECT *
FROM Students
WHERE Stud_STIP > ALL(
SELECT Stud_stip
FROM Students
WHERE Stud_STIP = 1500 )
Использование команды UNION. Команда UNION объединяет вывод нескольких запросов с исключением повторяющихся строк. Например, приведенный ниже запрос выводит всех студентов и преподавателей, чьи фамилии размещены между буквами К и С: SELECT Stud_FAM FROM Students WHERE Stud_FAM BETWEEN 'К' AND 'С' UNION SELECT Teach_FAM FROM Teacher WHERE Teach_FAM BETWEEN 'К' AND 'С' Для применения команды UNION существует 2 правила:
число и порядок следования колонок должны быть одинаковы во всех запросах
типы данных должны быть совместимы
UNION автоматически исключает дубликаты строк из вывода. Если вы хотите, чтобы все строки из запросов попали в результат, используйте UNION ALL.:
SELECT Stud_FAM
FROM Students
UNION ALL
SELECT Teach_FAM FROM Teacher
4.3 Создание и использование представлений
Представление – это заранее определенный запрос, сохраненный в базе данных, имеющий вид обычной таблицы и проявляющий себя подобно таблице, но не требующий дополнительного места для хранения. Представление используется точно так же, как и обычная таблица. Данными представления тоже можно манипулировать, хотя и с некоторыми ограничениями. Если используемая в представлении таблица удаляется, представление становится недоступным.
Представление можно использовать для защиты данных, то есть для ограничения доступа пользователей к определенным столбцам и строкам таблиц, в зависимости от условий, задаваемых выражением ключевого слова WHERE в определении представления. Например, чтобы не все пользователи базы данных видели оклады сотрудников из соответствующей таблицы, можно создать для этой таблицы представление, содержащее только требуемую информацию (без поля, содержащего оклады сотрудников).
Представление является удобным средством для создания итоговых отчетов на основе данных таблиц, которые обновляются очень часто.
Представление создается с помощью команды CREATE VIEW. Представление можно создать на базе данных одной или нескольких таблиц, а также других представлений. Например, создадим представление StudAddress из таблицы Students:
CREATE VIEW StudAddress AS
SELECT Stud_ID, Stud_FAM, Stud_IMA,Stud_ADDRESS
FROM Students
Для удаления представлений из базы данных используется команда DROP VIEW. У этой команды есть две опции – RESTRICT и CASCADE. Если используется RESTRICT и в условиях имеются зависимые представления, то оператор DROP VIEW возвращает ошибку. При использовании опции CASCADE и наличии зависимых представлений оператор DROP VIEW завершается успешно и все зависимые представления тоже удаляются.
4.4 Задание на лабораторную работу Вначале выполните все примеры, приведенные в п.п.4.1-4.3.
Получите следующую информацию из базы данных: 4.4.1 Список преподавателей, ведущих дисциплины «Информатика» и «Физика». 4.4.2 Список студентов, имеющих неудовлетворительные оценки. 4.4.3 Список студентов, не сдавших экзамен по высшей математике. 4.4.4 Список преподавателей кафедры «Информатика». 4.4.5 Список кафедр с указанием фамилий заведующих кафедр. 4.4.6 Список названий групп с указанием фамилий старост этих групп. 4.4.7 Списки студентов каждой группы с их оценками по всем предметам. 4.4.8 В каких группах проводятся занятия по предмету «Информатика»? 4.4.9 Какие виды занятий по Информатике проводятся в первой группе? 4.4.10 Сколько часов занятий по каждому предмету в каждой группе проводится в семестре? 4.4.11 Создайте представление STUDOСENKA из таблиц Students, Subject
и Progress, в котором отражается список студентов с их оценками по предметам. 4.5 Контрольные вопросы 4.5.1 Можно ли иметь несколько ключевых слов AND в выражении, заданном ключевым словом WHERE? 4.5.2 Что такое рекурсивное связывание? 4.5.3 При связывании таблиц должны ли они связываться в том же порядке, в каком они указаны в выражении ключевого слова FROM? 4.5.4 При использовании в операторе запроса таблицы-связки, обязательно ли выбирать в запросе ее столбцы? 4.5.5 Можно ли связывать в запросе не один, а несколько столбцов таблиц? 4.5.6 Какая часть оператора SQL задает условия связывания таблиц? 4.5.7 Что будет, если в запросе указать выборку из двух таблиц, но не связать их? 4.5.8 Для чего используются псевдонимы таблиц? 4.5.9 Что такое подзапрос? Как он работает? 4.5.10 Что случится, если таблица, на основе которой строится представление, будет удалена?
4.5.11 Как представление можно использовать для защиты данных?
СПИСОК ЛИТЕРАТУРЫ 1. Хансен Г., Хансен Д. Базы данных: разработка и управление. – М.: ЗАО «Издательство БИНОМ», 1999. 2. Тихомиров Ю. Microsoft SQL Server 7.0 – СПб.: Издательство «Питер», 1999. 3. Плю Р., Стефенс Р., Райан К. Освой самостоятельно SQL за 24 часа. – М.: Издательский дом «Вильямс», 2000. 4. Кандзюба С.П., Громов В.Н. Delphi 6/7. Базы данных и приложения. – СПб: ООО «ДиаСофт», 2002. 5. Бобровский С. Delphi 5: учебный курс. – СПб: Издательство «Питер», 2000.