Кренделева Н.Ю. Конспект лекций по SQL92 с примерами
Подождите немного. Документ загружается.
11
Использование текстовых сообщений в запросах с группировкой
Использование текстовых сообщений в запросах с группировкой
Запрос может содержать не только перечень полей для вывода информации, но и текстовые
константы. Обычно это делается для удобного оформления результата запроса.
Задача: подсчитать сколько студентов получают стипендию.
Без использования текстовой константы результатом запроса будет только одно число.
Введём текстовые константы в запрос:
SELECT count (СтБил),
"студент(а) получают стипендию: ", стипендия, "грн."
FROM студенты
GROUP BY стипендия;
Подчёркиванием выделены текстовые константы
Результатом такого запроса будет таблица вида:
Сортировка данных в запросах
Сортировка данных в запросах
Для упорядочения вывода полей таблиц используется команда ORDER BY. Она
позволяет сортировать вывод запроса, согласно значениям в столбцах. Можно указать
сортировку по нескольким столбцам сразу. Сортировка по возрастанию–ASC. Сортировка по
убывани.–DESC. По умолчанию стоит сортировка по возрастанию.
SELECT * FROM студенты
ORDER BY фамилия ASC;
Сортировка по двум полям:
SELECT * FROM студенты
ORDER BY стипендия DESC, фамилия ASC;
Запрещается сортировка по полям не выбранным в запросе!
Вместо имён столбцов в запросе можно использовать их порядковые номера в списке
вывода полей запроса. Если поле в запросе стоит первым, то оно будет иметь номер 1
независимо от того, каким оно стоит реально в таблице.
SELECT фамилия, имя, отчество
FROM студенты
ORDER BY 1 ASC;
(поле «фамилия» в запросе указано первым, хотя в реальной таблице это второе поле )
12
Команда ORDER BY может использоваться одновременно с командой GROUP BY,
причём команда ORDER BY должна быть последней.
SELECT count([СтБил]), "студент(а) получают стипендию: ", [стипендия], "грн."
FROM студенты
GROUP BY [стипендия]
ORDER BY стипендия DESC;
Результат будет таким:
Избежать некорректности при выводе текстового сообщения обычно не удастся.
Примеры сортировок:
SELECT *
from студенты
order by имя desc; (по убыванию имён)
SELECT *
from студенты
order by имя, стипендия ;
SELECT фамилия
From студенты
group by фамилия ; (выведет неповторяющиеся фамилии)
Многотабличные запросы
Многотабличные запросы
Многотабличные запросы предназначены для объединения в одном запросе данных из
нескольких таблиц. Для этого необходимо указать существующие связи между таблицами.
См. } рисунок1 . Выполнить это можно разными способами, один из которых показан ниже:
SELECT учителя.фамилия, предметы.название, предметы.курс
FROM учителя, предметы
WHERE учителя.код_учит=предметы.код_преп
ORDER BY 3;
Сортировка по третьему полю, указанному в запросе.
13
Вложенные запросы
Вложенные запросы
Базовая конструкция организации вложенного запроса:
Базовая конструкция организации вложенного запроса:
SELECT [поле1] [поле2], ..,[полеМ],
FROM [таблица];
WHERE [полеN]=
(вложенный запрос);
Рассмотренные нами примеры будут основываться на БД, схема которой
приведена на рисунке 1.
Выборка информации из таблицы УСПЕВАЕМОСТЬ по конкретной фамилии студента,
хранящейся в таблице СТУДЕНТЫ.
SELECT *
FROM Успеваемость
WHERE СтБил=
(select СтБил from Студенты
where фамилия = [Введите фамилию студента]);
Аналогично выборка информации о преподавателях.
SELECT *
from Предметы
where код_преп=
(select код_учит from Учителя
where фамилия = [Введите фамилию преподавателя]);
SELECT *
from Успеваемость
where оценка>=
(select avg(оценка) from Успеваемость);
Двухуровневый подзапрос
Двухуровневый подзапрос
на определение и вывод фамилии, имени, отчества конкретного преподавателя, у которого есть
принятые зачеты. Известен код сдачи зачёта.
SELECT фамилия, Имя, Отчество FROM учителя
WHERE код_учит=
(select код_преп from Предметы
where номер_П=
(select кодПредмета from Успеваемость
where кодсдачи =[введите код сдачи дисциплины]));
14
Определение названия предмета и курса, на котором он сдан, для конкретного студента.
SELECT название, курс from Предметы
where номер_п=
(SELECT кодПредмета from Успеваемость
WHERE стбил=
(SELECT стбил from Студенты
WHERE фамилия = [ введите фамилию] and имя=[ введите имя]));
Если студент сдал более одного запроса, будет выдано сообщение о невозможности
выполнения данного запроса.
15
Объединение таблиц
Объединение таблиц
Переименование полей в запросе на объединение
Переименование полей в запросе на объединение
В следующем запросе на объединение поле «Название» получает при выводе
результатов имя «Поставщик/Клиент»:
SELECT [Название] AS [Поставщик/Клиент], [Город]
FROM [Поставщики]
UNION SELECT [Название] AS [Поставщик/Клиент], [Город]
FROM [Клиенты];
Возвращение повторяющихся записей
Возвращение повторяющихся записей
в запросе на объединение
в запросе на объединение
В следующем запросе на объединение инструкция UNION ALL обеспечивает
возвращение всех записей, в том числе повторяющихся:
SELECT [Название], [Город]
FROM [Поставщики]
UNION ALL SELECT [Название], [Город]
FROM [Клиенты];
16
Внесение изменений в БД
Внесение изменений в БД
При работе с запросами SQL предоставляется возможность управлять значениями в
таблицах БД.
Для этого используются три команды языка:
INSERT — вставить,
DELETE — удалить,
UPDATE — модифицировать
INSERT
INSERT
Новую запись в таблицу можно добавить при помощи конструкции:
INSERT INTO ИмяТаблицы
VALUES (значение поля1, значение поля2, ..., значение поляN);
Такая запись используется когда, последовательность вводимых значений совпадает с
последовательностью полей в данной таблице. Если удобнее ввести значения в другой
последовательности, указывают очерёдность полей в круглых скобках.
INSERT INTO ИмяТаблицы (НазваниеПоля2, НазваниеПоля3, НазваниеПоля1)
VALUES (значение поля2, значение поля3, значение поля1);
Например,
INSERT INTO Студенты (Фамилия, Имя, Группа)
VALUES (“Иванов”, “Иван”, “ПЕОТ-08 1/9”);
Необходимо, чтобы выполнялось условие совпадения типов полей, значения которых
указываются в конструкции.
Команда INSERT не производит никакого вывода на экран.
Если конкретное значение какого-то поля неизвестно, допустимо задать значение NULL.
INSERT INTO Студенты (Фамилия, Имя, Группа)
VALUES (“Иванов”, “Иван”, NULL);
Если значение какого-то поля НЕ указать, то будет установлено значение «по умолчанию»,
если такого не указано при создании таблиц, установится NULL.
Команда вставки используется и для выборки данных из одной таблицы и расположения их
в другой. Тогда оператор VALUES заменяется на оператор SELECT.
INSERT INTO Студенты
SELECT *
From АБИТУРИЕНТЫ
Where ПроходнойБалл=[Введите проходной балл];
Обратите внимание! В предложении VALUES нельзя использовать
вычисляемые выражения.
17
DELETE
DELETE
Удалить запись из таблицы можно добавить при помощи конструкции вида:
DELETE FROM ИмяТаблицы
WHERE условие;
DELETE FROM БИБЛИОТЕКА_Техникума
WHERE годПоступления=[вв.год поступления];
Для удаления всехзаписей можно использовать конструкцию:
DELETE FROM ИмяТаблицы;
Данная команда не может удалить некоторые поля из таблиц, а только всю запись,
поэтому в данной конструкции НЕ используется параметр ИмяПоля.
Допускается использование подзапроса в качестве условия для удаления записей.
DELETE FROM Студенты
WHERE СтБил=
(select СтБил
from Успеваемость
where оценка <=3);
см. схему данных
Удаляет все записи из таблицы Студенты, для студентов имеющих
оценки 3 и ниже в таблице Успеваемость.
UPDATE
UPDATE
Изменить данные в таблицах БД можно при помощи конструкции вида:
UPDATE ИмяТаблицы
SET ВносимыеИзменения;
Изменения могут вноситься сразу для всех значений определённого поля.
Например, UPDATE Студенты
SET стипендия= 140;
См. схему данных
При выполнении данного запроса размер стипендии всем студентам будет
установлен = 140. Но такие запросы на практике используются редко. Чаще
применяют запросы на изменение по условию.
Например,
UPDATE Студенты
SET стипендия= 140
WHERE стипендия > 0;
18
При выполнении данного запроса размер стипендии будет увеличен только
тем студентам, которые имели какую-либо стипендию.
Приведём пример с использованием нулевых значений полей:
UPDATE успеваемость
SET оценка=null
WHERE кодпредмета=2001;
В данном запросе обнуляются все оценки по определённому предмету.
Запрос может содержать составной критерий выполнения:
UPDATE успеваемость
SET оценка=null
WHERE кодпредмета=2001 and стбил=3412;
В данном запросе обнуляется оценка по определённому предмету для
единственного студента с номером ст. билета 3412.
Данная команда в одной конструкции позволяет изменить содержимое
нескольких полей сразу. Например,
UPDATE студенты
SET фамилия= «Полякова», стБил=3422
WHERE стБил=3420;
В данном запросе выполняется изменение фамилии студентки и смена её
номера студенческого билета.
В предложении SET можно использовать вычисляемые выражения.
Например, UPDATE Студенты
SET стипендия= стипендия*1,2
WHERE стипендия > 0;
В данном запросе размер стипендии увеличивается на 20%
Ошибочной будет попытка выполнить в рамках одного запроса изменения
значения одного поля по разным критериям:
UPDATE студенты
SET стипендия=стипендия+1 WHERE стипендия>100
and SET стипендия=стипендия+2 WHERE стипендия <=100 ;
В команде UPDATE можно использовать подзапросы.
Нужно помнить, что в предложении FROM любого подзапроса нельзя
модифицировать таблицу, к которой обращается основная команда UPDATE.
При использовании подзапросов в данной команде может возникнуть ситуация,
когда результатом выполнения подзапроса будет не одна величина, а несколько.
Тогда возникает ситуация ошибки.
19
UPDATE студенты
SET стипендия=стипендия*1.1
WHERE стбил IN
(SELECT стбил FROM успеваемость
WHERE оценка=5)
В данном запросе предпринимается попытка повысить размер стипендии на
10% тем студентам, у которых есть оценка 5 по какому-либо экзамену.
Следующий пример увеличивает в три раза размер стипендии тем студентам,
у которых имеются оценки как минимум по двум предметам:
UPDATE студенты
SET стипендия=стипендия*3
WHERE 2<=
(SELECT COUNT(стбил)
FROM успеваемость
WHERE студенты.стбил=успеваемость.стбил);