Занятие 5 (12 мая)
Часть 1 (методическая). Основные инструкции SQL в MS ACCESS. Синтаксис
Инструкции языка DDL
Создание таблицы – CREATE TABLE
Изменение структуры таблицы – ALTER TABLE
Удаление таблицы – DROP TABLE
Работа с индексами
Создание индекса в новой таблице – предложение CONTRAINT
Добавление индекса – CREATE INDEX
Удаление индекса – DROP INDEX
Инструкции языка DML
Ввод данных в таблицу – INSERT INTO, SELECT INTO
Удаление данных из таблицы – DELETE
Обновление данных в таблице – UPDATE
Связывание таблиц
Синтаксис предложения FROM
Виды соединений
Инструкции языка DQL
-
Синтаксис инструкции SELECT
Псевдонимы
Предикаты
Сортировка
Вычисляемые поля
Условия
-
Предложение WHERE
Логические операторы
Групповые функции
Подчиненные запросы
Часть 3 (практикум).
Упраженения.
Контрольные задания.
=====================================================================
Упражнение 1. Изменение структуры существующей таблицы.
В режиме конструктора ввести управляющий запрос SQL, который должен добавить в таблицу новая новое поле – "образование" (тип – текстовый, длина – 10 символов).
ALTER TABLE новая
ADD COLUMN [фракция] TEXT(10)
Инструкция ALTER TABLE выполняет реструктуризацию таблицы:
удаляет существующие поля,
добавляет новые поля,
добавляет или удаляет индексы.
ALTER TABLE <�таблица>
{ADD {[COLUMN] <�поле> <�тип>[(<�размер>)] [CONSTRAINT <�индекс>] |
CONSTRAINT <�составной_индекс>} |
DROP {[COLUMN] <�поле> | CONSTRAINT <�имя_индекса>}}
Опция ADD обеспечивает добавление поля,
опция DROP – удаление поля таблицы,
добавление опции CONSTRAINT означает подобные действия для индексов таблицы.
Для удаления таблицы (одновременно и структуры, и данных) используется команда:
DROP TABLE <�имя_таблицы>
Упражнение 2 (подобное упраждение выполнялось на занятии 3 – см. 4.3).
Сконструировать запрос на добавление записей в таблицу "новая" из таблицы "депутаты": добавить сведения о депутатах с номерами 21–25.
Изучить построенную СУБД Access инструкцию SQL, соответствующую этому запросу (запросу на добавление):
Инструкция INSERT INTO вводит новые записи в таблицу.
1. Для ввода одной записи используется синтаксис:
INSERT INTO <�таблица_куда> [(<�поле1>[, <�поле2>[, ...]])]
VALUES (<�значение1>[, <�значение2>[, ...]);
Указывается имя таблицы, в которую добавляют запись, и состав полей, для которых вводятся значения. Каждое значение добавляется в поле, занимающее то же положение в списке: <�значение1> будет вставлено в <�поле1>, <�значение2> – в <�поле2> и т.п.
Замечание. Если вводятся значения всех полей, имена полей можно не указывать, однако число значений и их порядок должно соответствовать числу и порядку полей.
2. Для ввода нескольких записей (группового ввода), которые выбираются из других таблиц БД, используется синтаксис:
INSERT INTO <�таблица_куда> [(<�поле1>[, <�поле2>[, …]])]
SELECT [<�источник.>]<�поле1>[, <�поле2>[, ...]
FROM <�выражение>
WHERE <�условие>
Предложение SELECT задает выполнение подчиненного запроса, который формирует выборку для вставки в <�таблицу_куда>:
определяет структуру данных источника передаваемых записей – имена таблицы и полей, содержащих исходные данные для вставки.
Предложение FROM указывает имена исходных таблиц, участвующих в формировании выборки, а предложение WHERE задает условия выполнения запроса.
Замечания. Если структуры таблиц совпадают и используются все поля таблицы-источника данных, их можно не перечислять, а просто поставить после имени таблицы точку и звездочку.
Добавление записей возможно и из внешней базы данных, для которой в таком случае указывается полностью специфицированное имя (диск, каталог, имя, расширение), которое заключается в кавычки:
INSERT INTO <�таблица_куда> [(<�поле1>[, <�поле2>[, …]])]
SELECT [<�источник>]<�поле1>[, <�поле2>[, ...]
FROM <�выражение> [IN <�внешняя_база_данных>]
WHERE <�условие>
Упражнение 3. Добавление группы записей в таблицу новая с помощью запроса SQL.
В режиме конструктора ввести управляющий запрос SQL, который должен добавить в таблицу новая те записи из таблицы депутаты, которых значение поля новый номер больше 450, причем будет использована только информация из полей [новый номер], фамилия, [имя, отчество].
INSERT INTO новая
SELECT депутаты.[новый номер], депутаты.фамилия, депутаты.[имя, отчество]
FROM депутаты
WHERE депутаты.[новый номер]>450;
Упражнение 4. Создание соединения.
Создать запрос с помощью конструктора запросов на основе таблиц новая и образование с выводом полей номер, фамилия, [имя, отчество] и образование из первой таблицы и полей учреждение и специальность – из второй таблицы.
Изучить построенную СУБД инструкцию SQL, соответствующую этому запросу:
Операция INNER JOIN создает симметричное соединение – наиболее частую разновидность внутреннего объединения записей из двух таблиц. Симметричным называется соединение, в котором участвуют только те записи обеих таблиц, в которых совпадают значения ключей (связующих полей).
INNER JOIN может использоваться в любом предложении FROM. Синтаксис:
FROM <�таблица1> INNER JOIN <�таблица2> ON <�таблица1>.<�поле1> =
<�таблица2>.<�поле2>
Возможные варианты операции:
LEFT JOIN (левостороннее) соединение – выбираются все записи "левой" таблицы и только те записи "правой" таблицы, которые содержат соответствующие ключи связи.
RIGHT JOIN (правостороннее) соединение – выбираются все записи "правой" таблицы и только те записи "левой" таблицы, которые содержат соответствующие ключи связи.
Упражнение 5. (выполнялось на занятии 3 – см. упражнение 4.4).
С помощью конструктора запросов создать запрос на удаление записей из таблицы "новая": удалить запись с номером 8.
Изучить построенную СУБД инструкцию SQL, соответствующую этому запросу:
Инструкция DELETE – удаление записей в таблице.
Синтаксис:
DELETE [<�таблица>.*]
FROM <�таблица>
WHERE <�условия_отбора>
где
<�таблица> – необязательное имя таблицы, из которой удаляются записи;
<�таблица> – имя таблицы, из которой удаляются записи;
<�условия_отбора> – выражение, определяющее удаляемые записи.
Инструкция DELETE * FROM <�таблица> удаляет все записи из таблицы.
Замечания:
при удалении записей структура таблицы сохраняется;
индексы в индексированной таблице автоматически корректируются;
восстановить записи, удаленные с помощью запроса на удаление записей, средствами MS Access невозможно.
Упражнение 6. (выполнялось на занятии 3 – см. упражнение 4.2).
Сконструировать запрос на обновление таблицы "новая": для депутата с номером 1 обновить поле "имя" (заменить прежнее значение на "Иванов").
Изучить построенную СУБД инструкцию SQL, соответствующую этому запросу:
Инструкция UPDATE – обновление значений полей таблицы для записей, удовлетворяющих условию отбора.
Синтаксис:
UPDATE <�таблица>
SET <�имя поля> = <�новое_значение>
WHERE <�условие_отбора>
Упражнение 7. Стандартный запрос на выборку.
Отобрать из таблицы новая все записи в составе всех полей
Изучить построенную СУБД инструкцию SQL, соответствующую этому запросу:
Инструкция SELECT – запрос на выборку
Выборка с помощью оператора SELECT – наиболее частая команда при работе с реляционной базой данных. SELECT обладает большими возможностями по заданию структуры выходной информации, указанию источников входной информации, способами упорядочения выходной информации, формированию новых значений и т.п. (табл. 1).
При выполнении выборки могут формироваться и новые данные, так называемые вычисляемые поля, являющиеся результатом обработки исходных данных. Возможно упорядочение выводимых данных, формирование групп записей, подсчет групповых итогов, формирование подмножеств данных (записей), являющихся основой для формирования условий по обработке следующего этапа – вложенных запросов.
Синтаксис:
SELECT [<�предикат>] {* | <�таблица>.* | [<�таблица>.]<�поле1>[AS<�псевдоним1>]
[, [<�таблица>.]<�поле2>[AS<�псевдоним2>[, ...]]}
FROM <�выражение> [, ...] [IN <�внешняя_база_данных>]
[WHERE...]
[GROUP BY...]
[HAVING...]
[ORDER BY...]
[WITH OWNERACCESS OPTION]
Замечания:
для изменения заголовка столбца с результатами выборки используется служебное слово AS (псевдоним);
группа псевдонимов может следовать после группы имен полей;
если используются одноименные поля из нескольких таблиц, включенных в предложение FROM, следует указывать перед именем такого поля имя таблицы через точку (.): [Студент заочник].[Группа] и [Студент].[Группа] – два одноименных поля из разных таблиц.
Аргументы оператора SELECT:
<�Предикат> – используется для указания числа возвращаемых записей:
ALL – все записи;
DISTINCT – записи, различающиеся в указанных полях;
DISTINCTROW – полностью различающиеся записи по всем полям;
ТОР – возврат заданного числа или процента записей в диапазоне, соответствующем фразе ORDER BY
<�Таблица> – имя таблицы, из которой выбираются записи
<�Поле1>, <�поле2> – имена полей, из которых извлекаются данные
<�Псевдоним1>, <�Псевдоним2> – новые заголовки столбцов в выборке вместо исходных (часто используется с вычисляемыми полями)
<�Внешняя база данных> – имя внешней базы – источника данных для выборки
Предложение FROM – определяет <�выражение>, используемое для указания источника формирования выборки (присутствует обязательно)
Предложение WHERE… – определяет условия отбора записей
Предложение GROUP BY... – указывает поля для группировки и вычисления групповых итогов
Предложение HAVING... – задает условия отбора для сгруппированных данных
Предложение ORDER BY... – задает поля, по которым выполняется сортировка (по возрастанию (ASC) или убыванию (DESC) значения выбранного поля)
Предложение WITH OWNERACCES OPTION – при работе в сети возможность просматривать результат запроса или выполнять запрос.
Упражнение 8. Запрос SQL на выборку с использованием предикатов и псевдонимов.
Отобрать из таблицы новая первые семь записей. Вывести вычисляемое поле как "сумму" полей фамилия и [Имя, отчество] депутатов. Назвать это поле ФИО.
SELECT top 7 Фамилия&" "&[Имя, Отчество] as ФИО
FROM новая;
Упражнение 9. Запрос на выборку из двух таблиц с использованием условия.
Получить список депутатов (таблицы депутаты) из фракции яблоко, имеющих специальность экономист (таблица образование).
Изучить построенную СУБД инструкцию SQL, соответствующую этому запросу:
В предложении WHERE чаще всего используются логические операторы AND (логическое И), OR (логическое ИЛИ), NOT (логическое НЕ), a также XOR (логическое исключающее ИЛИ), IMP (импликация) и EQV (эквиваленция).
Кроме того, могут использоваться специальные операторы: LIKE (сравнение строковых значений), BETWEEN...AND (сравнение с диапазоном значений), IN (сравнение со списком значений), IS (проверка значения на NULL – пусто).
Упражнение 10. Запрос с групповыми операциями.
По таблице депутаты подсчитать количество депутатов в каждой фракции
Изучить построенную СУБД инструкцию SQL, соответствующую этому запросу:
Групповые функции:
Avg – среднее арифметическое значений;
Count – количество записей;
Min, Max –минимальное и максимальное значения;
StDev, StDevPs – среднее квадратическое отклонение генеральной совокупности и выборки;
Sum – сумма значений;
Var, VarPs – дисперсия для генеральной совокупности и выборки.
Для определения полей группирования указывается ключевое слово GROUP BY. Ключевое слово HAVING задает условия для групповых значений.
Подчиненные запросы
Упражнение 11.
Из таблицы депутаты выбрать только тех депутатов, у которых число детей (поле дети) больше максимального для какой-либо фракции.
А) Рассмотреть вариант построения обычного запроса.
В) Вариант SQL-запроса с подчиненным:
SELECT депутаты.Фамилия, депутаты.Дети, депутаты.Фракция
FROM депутаты
WHERE (((депутаты.Дети)>Any (select max(дети) from депутаты group by фракция)));
Первый тип – сравнение выражения с результатом подчиненного запроса.
Ключевые слова:
ANY – какой-либо (сравнение с каждым элементом подчиненной выборки).
ALL – все (сравнение со всеми элементами подчиненной выборки).
SOME – некоторые (сравнение с некоторыми элементами подчиненной выборки).
Упражнение 12.
Найти депутатов, которые более одного раза встречаются в таблице образование. Вывести поля новый номер, учреждение и специальность.
В) Вариант запроса на повторяющиеся записи
С) Вариант SQL-запроса с подчиненным:
SELECT Образование.[Новый номер], Образование.Учреждение, Образование.Специальность
FROM Образование
WHERE (((Образование.[Новый номер]) In (SELECT [Новый номер] FROM [Образование] As Tmp GROUP BY [Новый номер] HAVING Count(*)>1 )))
ORDER BY Образование.[Новый номер];
Второй тип – поиск выражения, которое должно быть найдено в результатах (IN) выполнения подчиненного запроса. Пример – запрос на повторяющиеся записи.
Упражнение 13.
Получить фамилии депутатов (таблица депутаты), по которым нет сведений в таблице ученая степень/звание
А) Рассмотреть вариант обычного запроса
В) Вариант запроса "записи без подчиненных"
С) Вариант SQL-запроса с подчиненным:
SELECT фамилия
FROM депутаты
WHERE (((Exists (SELECT [новый номер] FROM [Ученая степень/звание] WHERE депутаты.[новый номер] = [Ученая степень/звание].[новый номер]))=Yes));
Третий тип – проверка наличия (отсутствия) соответствующих записей в подчиненном запросе. Пример – записи с подчиненными (без подчиненных).
Задание 1. Создать базу данных из трех таблиц
-
Таблица "студент" (ключ – номер студента)
номер студента
|
имя
|
дата рождения
|
1
|
Иванов
|
01.01.1987
|
2
|
Петров
|
02.02.1988
|
3
|
Сидоров
|
03.03.1989
|
4
|
Зайцев
|
04.04.1990
|
-
Таблица "оценка"
номер студента
|
номер дисциплины
|
результат
|
1
|
1
|
2
|
1
|
1
|
3
|
1
|
2
|
4
|
2
|
1
|
5
|
2
|
2
|
5
|
3
|
1
|
5
|
3
|
1
|
3
|
Таблица "дисциплина"
-
номер дисциплины
|
название
|
1
|
история
|
2
|
математика
|
3
|
право
|
Образец: SQL-инструкция создания таблицы "студент"
create table студент([номер студента] integer, имя text(15), [дата рождения] datetime, constraint индекс1 primary key([номер студента]));
Задание 2. Ввести данные в таблицу.
Добавить в таблицу студент новую запись о студенте с номером 5, именем Волков и датой рождения 05.05.1991:
Образец: SQL-инструкция добавления записи о студенте с номером 10 и именем Мамонтов
insert into cтудент ([номер студента], имя) values (1, "Мамонтов");
Задание 3. Изменить данные в таблице.
В таблице студент заменить фамилию "Волков" на "Медведев".
Образец: SQL-инструкция обновления записи о студенте с номером 10 – вводится информация о дате рождения 06.06.1992
update cтудент set [дата рождения]="06.06.1992" where ([номер студента]=10);
Задание 4. Запросы на выборку из одной или нескольких таблиц
Получить список студентов, дата рождения которых – позже 1 января 1987 года
Получить список студентов, имеющих двойки
Образец. SQL-инструкция однотабличного запроса по дате рождения
select студент.* from студент
where студент.[дата рождения]=#2/2/1988#));
Образец. SQL-инструкция двухтабличного запроса по оценке и дисциплине
select оценка.результат
from оценка inner join дисциплина on оценка.[номер дисциплины]=дисциплина.[номер дисциплины]
where название="история";
Задание 5. Запросы с групповыми операциями
Получить средний балл по каждому студенту
Получить средний балл по тем дисциплинам, для которых этот средний балл <4.
Образец. SQL-инструкция запроса на вычисление средних баллов по дисциплинам
select [номер дисциплины], avg(результат) as [средний балл] from оценка
group by [номер дисциплины]
having avg(результат)>=4.5;
Задания 6–8. Подчиненные запросы
Получить записи таблицы "оценка", в которых значение результата равно максимуму по какой-либо (any) дисциплине
Образец. SQL-инструкция запроса на вывод только тех записей таблицы "оценка", в которых значение результата меньше средних баллов по всем дисциплинам
select * from оценка
where результат
(select avg(результат) from оценка
group by [номер дисциплины]);
Получить записи таблицы "студент" для тех студентов, у которых результат в таблице "оценка" больше или равен 4
Образец. SQL-инструкция запроса на вывод только тех записей таблицы "студенты", которые соответствуют студентам, имеющим в таблице "оценка" результат 3 или ниже
select * from студент
where [номер студента] in
(select [номер студента] from оценка
where [результат]<=3);
Получить записи таблицы "студент", по которым есть результаты в таблице "оценка"
Образец. SQL-инструкция запроса на вывод только тех записей таблицы "студент", по которым нет результатов в таблице "оценка"
select * from студент
where not exists
(select * from оценка where студент.[номер студента] = оценка.[номер студента]);
|
