close

Вход

Забыли?

вход по аккаунту

?

язык манипулирования для реляционных дан

код для вставкиСкачать
Спецкурс (10 семестр)
Специальность «Экономическая кибернетика»
1 из 10
Лекция 3. SQL Data Manipulation Language – язык манипулирования
для реляционных данных.
3.1.
SQL: операции над отдельными кортежами – строками реляционных таблиц...........1
3.1.1 Добавление записей ..........................................................................................................1
3.1.2 Удаление записей..............................................................................................................2
3.1.3 Обновление записей .........................................................................................................3
3.1. SQL: операции над отдельными кортежами – строками реляционных
таблиц
Запросы на изменение позволяют изменять состояние базы данных, меняя значения
отдельных элементов данных.
3.1.1 Добавление записей
Инструкция SQL на добавление одной записи имеет вид:
INSERT INTO назначение [(поле_1[, поле_2[, ...]])]
VALUES (значение_1[, значение_2[, ...])
где:
назначение – имя таблицы или запроса, в который добавляются записи;
поле_i – имя поля в таблице; можно указывать только те поля, в которые будут
внесены значения, но обязательно указать все ключевые поля;
значение_i – значение, взятое из допустимых значения для поля_i
Для добавления нескольких записей:
INSERT INTO назначение
[(поле_1[, поле_2[, ...]])]
SELECT [источник.]поле_1[, поле_2[, ...]
FROM выражение
[WHERE…]
где:
назначение – имя таблицы или запроса, в который добавляются записи;
источник – имя таблицы или запроса, откуда копируются записи;
поле_i –
имена полей для добавления данных, если они следуют за аргументом
назначение; имена полей, из которых берутся данные, если они следуют за
аргументом источник;
выражение – имена таблицы или таблиц, откуда вставляются данные;
значение_i – значения, добавляемые в указанные поля новой записи. Каждое
значение будет вставлено в поле, занимающее то же положение в списке:
значение_1 вставляется в поле_1 в новой записи, значение_2 в поле_2 и т.д.
Каждое значение текстового поля следует заключать в кавычки ' '; для
разделения значений используйте запятые.
Пример 1.
Добавить в таблицу Star информацию об Уильяме Смите (все, кроме его фотографии).
INSERT INTO Star (name, gender, birthday, address)
VALUES ('Уильям Смит', 'м', '19.09.1969', 'Калифорния');
Спецкурс (10 семестр)
Специальность «Экономическая кибернетика»
2 из 10
Инструкция SELECT в запросах на изменение данных может содержать как имена полей, так
и фиксированные значения.
Пример 2.
Добавить в таблицу StarsIn информацию о том, что все кинозвезды, снявшиеся в фильме
«Терминатор», снялись также и в фильме «Терминатор-2»
INSERT INTO starsIn (starName, movieTitle, movieYear)
SELECT DISTINCT starName, 'Терминатор-2', 1997
FROM StarsIn
WHERE starName IN (
SELECT a.starName FROM starsIn a WHERE a.movieTitle =
'Терминатор');
Существует особая инструкция SQL на создание таблицы добавлением в нее записей:
SELECT поле_1[, поле_2[, ...]]
INTO новаяТаблица
FROM источник
[WHERE…]
где:
поле_i –
имена полей, которые следует скопировать в новую таблицу;
новаяТаблица – имя создаваемой таблицы. Это имя должно удовлетворять
стандартным правилам именования. Если новаяТаблица совпадает с именем
существующей таблицы, возникает перехватываемая ошибка;
источник
– имя существующей таблицы, из которой отбираются записи. Это
может быть одна таблица, несколько таблиц или запрос.
Пример 3.
Создать таблицу “Самые длинные фильмы на каждой студии”, в которой для каждой студии
отобрать самые длинные по продолжительности фильмы.
SELECT Movie.title, Movie.year, Movie.length, Movie.studioName
FROM Movie
INTO [Самые длинные фильмы на каждой студии]
WHERE Movie.length = (
SELECT MAX(a.length)
FROM Movie AS a
WHERE a.studioName = Movie.studioName
);
Здесь вложенный запрос является связанным и выполняется для каждой записи таблицы
Movie, указанной во внешнем запросе.
3.1.2 Удаление записей
Инструкция SQL на удаление записей выглядит так:
Спецкурс (10 семестр)
Специальность «Экономическая кибернетика»
3 из 10
DELETE [таблица.*]
FROM таблица
WHERE условиеОтбора
где:
таблица – имя таблицы, из которой удаляются записи;
условиеОтбора – выражение, определяющее удаляемые записи.
Пример 4.
Удалить из таблицы Movie все фильмы, выпущенные до 1930 года.
DELETE FROM Movie
WHERE year <=1930;
Пример 5.
Удалить из таблицы «Кинофильмы» все фильмы продолжительностью, большем чем
продолжительность фильма «Титаник».
DELETE *
FROM Movie
WHERE length > (
SELECT a.length FROM Movie a
WHERE a.title = 'Титаник');
3.1.3 Обновление записей
Инструкция SQL на обновление записей в таблице выглядит так:
UPDATE таблица
SET новоеЗначение
WHERE условиеОтбора;
где:
таблица – имя таблицы, данные в которой следует изменить;
новоеЗначение – выражение, определяющее значение, которое должно быть
вставлено в указанное поле обновленных записей.
условиеОтбора – выражение, отбирающее записи, которые должны быть
изменены. При выполнении этой инструкции будут изменены только записи,
удовлетворяющие указанному условию.
Пример 6.
Обновить таблицу Studio так, чтобы у студии «New Line Cinema» был президентом Уильям
Смит.
UPDATE Studio
SET Studio.presidentName = ‘Уильям Смит’
WHERE (((Studio.studioName) = ’New Line Cinema’ ));
Спецкурс (10 семестр)
Специальность «Экономическая кибернетика»
4 из 10
3.2 SQL: операция выборки данных по условиям
Выборка предполагает отбор строк, удовлетворяющих набору условий, из одной или
нескольких таблиц
Инструкция SQL на выборку из таблиц выглядит так:
SELECT [предикат] { * | таблица.* | [таблица.]поле_1
[AS псевдоним_1] [, [таблица.]поле_2 [AS псевдоним_2] [, ...]]}
FROM выражение [, ...] [IN внешняяБазаДанных]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
где:
предикат – ALL | DISTINCT[ROW] | [TOP n [PERCENT]]
ALL – выбираются все (в том числе повторяющиеся) строки;
DISTINCT[ROW] – выбираются только различные строки;
TOP n [PERCENT] – первые n записей;
TOP n PERCENT – первые n процентов записей;
таблица – имя таблицы, из которой должны быть отобраны записи;
поле_i –
имена полей, из которых должны быть отобраны данные. Если
включить несколько полей, они будут извлекаться в указанном порядке. В
качестве полей также могут служит и поля, отбираемые самостоятельным
запросом на выборку, и выражения, построенные на основании результатов
самостоятельных запросов;
псевдоним_i – имена, которые станут заголовками столбцов вместо исходных
названий столбцов в таблице;
выражение – имена одной или нескольких таблиц, которые содержат отбираемые
данные, или выражение, использующее операторы INNER JOIN, LEFT JOIN,
RIGHT JOIN.
Предложение FROM должно присутствовать в каждой инструкции SELECT. Порядок
следования имен таблиц в выражении не существенен.
Предложение WHERE определяет, какие записи из таблиц, перечисленных в предложении
FROM, следует включить в результат выполнения инструкции SELECT.
Предложение GROUP BY объединяет записи с одинаковыми значениями в указанном
списке полей в одну группу. Если инструкция SELECT содержит статистическую функцию
SQL, например, SUM() или COUNT(), то для каждой группы будет вычислено итоговое
значение.
Предложение HAVING определяет, какие сгруппированные записи отображаются при
использовании инструкции SELECT с предложением GROUP BY. После того как записи
будут сгруппированы с помощью предложения GROUP BY, предложение HAVING отберет
те группы записей, которые удовлетворяют условиям отбора, указанным в предложении
HAVING.
Предложение ORDER BY сортирует записи, полученные в результате запроса, в порядке
возрастания (ASC) или убывания (DESC) на основе значений указанного поля или полей.
Спецкурс (10 семестр)
Специальность «Экономическая кибернетика»
5 из 10
Покажем на примере базы данных «Кинофильмы» (см. схему на рис.4.1) различные запросы
на выборку.
Star (name, gender, birthday, address, photo)
Movie (title, year, length, inColor, studioName, producerName)
Studio (studioName, address, phone, presidentName, webSite)
StarsIn (movieTitle, movieYear, starName)
Рисунок 3.1 – Реляционная схема БД «Кинофильмы»
Пример 1 (Простая выборка из одной таблицы).
Выбрать из таблицы «Movie» все цветные фильмы, созданные до 1941 года, и упорядочить
их по алфавиту.
SELECT *
FROM Movie
WHERE year <=1941 AND inColor =’цветной’
ORDER BY title;
Пример 2 (Простая выборка из нескольких связанных таблиц).
Показать все различные имена кинозвезд, снимавшихся хотя бы в одном фильме киностудии
«Paramount Pictures», начиная с 1990 года.
SELECT DISTINCT Star.name
FROM Star, Studio, StarsIn, Movie
WHERE StarsIn.movieYear >=1990
AND Movie.studioName = “Paramount Pictures”
AND StarsIn.starName=Star.name
AND StarsIn.movieTitle = Movie.title
AND StarsIn.movieYear = Movie.year
AND Studio.studioName = Movie.studioName;
Обратите внимание на то, сколько дополнительных условий накладывается на значения из
связанных таблиц, а также на ключевое слово DISTINCT, заставляющее отбирать только
различные записи.
В предложении FROM можно указывать как список таблиц, так и выражение, описывающее
связи между таблицами из списка, содержащее INNER (LEFT, RIGHT) JOIN.
Пример 3 (Использование выражения INNER JOIN).
Показать дни рождения кинозвезд, которые снимались в фильмах до 1998 года.
SELECT Star.birthday
FROM Star INNER JOIN starsIn ON starsIn.starName = Star.name
WHERE starsIn.movieYear<=1998;
Спецкурс (10 семестр)
Специальность «Экономическая кибернетика»
6 из 10
Эквивалентный запрос без INNER JOIN будет таким:
SELECT Star.birthday
FROM Star, starsIn
WHERE starsIn.movieYear<=1998
AND starsIn.starName = Star.name;
Использование LEFT (RIGHT) JOIN уместно, когда необходимо, чтобы записям левой
(правой) таблицы, которым не нашлось соответствующих записей в правой (левой) таблице,
ставились в соответствие пустые записи.
Пример 4.
Показать информацию о звездах и названиях фильмах, в которых они снимались. Если
звезда не снималась ни в одном фильме, то сопоставить ей пустую запись.
SELECT name, birthday, starsIn.movieTitle, starsIn.movieYear
FROM Star LEFT JOIN starsIn ON name =starsIn.starName;
Результат запроса показан на рис.3.2:
Рисунок 3.2 – Результат работы LEFT JOIN. Для Уильяма Смита в базе данных нет ни
одного фильма
Агрегированное значение – это числовое значение, вычисляемое для всей таблицы
(или для групп внутри таблицы).
Пример 5 (Вычисление агрегированного значения по таблице).
Найти наибольшую продолжительность среди кинофильмов.
SELECT MAX(Movie.length)
FROM Movie;
Подобным же образом можно находить среднее значение – AVG(), минимум – MIN(),
количество записей в выборке – COUNT(), сумму значений в столбце, допускающем
суммирование – SUM(). Подобные функции носят название агрегированных.
Пример 6 (Вычисление количества записей, удовлетворяющих условию).
Показать, сколько кинозвезд снималось в фильме «Чародеи»
Спецкурс (10 семестр)
Специальность «Экономическая кибернетика»
7 из 10
SELECT COUNT (StarsIn.starName)
FROM StarsIn
WHERE StarsIn.movieTitle = “Чародеи”;
Пример 7 (Вычисление агрегированных функций по группам записей с одинаковым значением
в одном или нескольких столбцах).
Показать, сколько кинофильмов было снято каждой киностудией.
SELECT COUNT (Movie.title)
FROM Movie
GROUP BY Movie.studioName;
Пример 8 (Группировка с отбором групп по условию).
Показать те студии, на которых было снято больше 10 кинофильмов.
SELECT studioName, COUNT(title)
FROM Movie
GROUP BY studioName
HAVING COUNT (title) > 10
Заметим, что предложение HAVING может использовать только функции агрегирования из
предложения SELECT или поля из предложения GROUP BY.
Поскольку результатом выборки в общем случае является множество строк, в SQL
допускаются запросы на объединение (UNION), пересечение (INTERSECT) и разность
(MINUS, EXCEPT) двух и более множеств строк. MS Access допускает только запросы на
объединение, причем записать их можно только в режиме SQL.
Пример 9.
Выбрать кинозвезд, живущих в Калифорнии, и присоединить к результату тех кинозвезд,
которые снялись в фильме «Терминатор-2»
SELECT Star.name
FROM Star
WHERE Star.address Like “Калифорния*”
UNION
SELECT StarsIn.starName
FROM StarsIn
WHERE StarsIn.movieTitle = «Терминатор-2»;
Вложенные запросы – это запросы, в которых один запрос помещается внутри
предложения WHERE другого запроса. При этом изменяется порядок вычисления
результата запроса: сначала вычисляется результат внутреннего запроса
(подзапроса), а затем данные, полученные в подзапросе, используют для оценки
Спецкурс (10 семестр)
Специальность «Экономическая кибернетика»
8 из 10
истинности условия внешнего запроса. Подзапрос должен выбирать единственный
столбец, а тип данных этого столбца должен соответствовать типу значения,
указанного в предложении WHERE внешнего запроса.
Пример 10 (Запрос с подзапросом, возвращающим одно значение).
Выбрать все фильмы, в которых снималось кинозвезд больше, чем в фильме “Бриллиантовая
рука”.
SELECT a.movieTitle, count (a.starName)
FROM StarsIn a
GROUP BY a.movieTitle
HAVING COUNT(a.starName) >
(SELECT COUNT(b.starName)
FROM StarsIn b
WHERE b.movieTitle = “Бриллиантовая рука”);
В случае использования в HAVING подзапроса, этот подзапрос будет выполняться один раз
для каждой группы из внешнего запроса, а не для отдельной строки.
Пример 11 (Запрос с подзапросом, возвращающим несколько значений).
Выбрать все фильмы, в которых участвовали кинозвезды, снимавшиеся в фильме
“Бриллиантовая рука”.
SELECT a.movieTitle
FROM StarsIn a
WHERE a.starName IN (SELECT b.starName
FROM StarsIn b
WHERE b.movieTitle=”Бриллиантовая рука”);
Такой запрос выполнится быстрее, чем эквивалентный запрос без подзапроса:
SELECT DISTINCT a.movieTitle
FROM StarsIn a, StarsIn b
WHERE b.movieTitle<>a.movieTitle
AND b.movieTitle = “ Бриллиантовая рука ”
AND b.starName = a.starName;
Во вложенных запросах в подзапросе можно ссылаться на таблицу, имя которой указано в
предложении FROM внешнего запроса. Такие подзапросы называются связанными
подзапросами. Связанный подзапрос выполняется по одному разу для каждой строки
таблицы из внешнего запроса.
Пример 12 (Выборка из таблиц с помощью связанного подзапроса).
Выбрать для каждой студии фильм максимальной длины и показать всю информацию об
этих фильмах.
Спецкурс (10 семестр)
Специальность «Экономическая кибернетика»
9 из 10
SELECT m.title, m.year, m.length, m.studioName
FROM Movie AS m
WHERE m.length=(SELECT MAX(a.length)
FROM Movie a
WHERE a.studioName = m.studioName);
Иногда нужно просто оценить, возвращает ли подзапрос непустой набор строк или нет. Для
этого используется один из операторов EXISTS, ALL, ANY.
Пример 13.
Если в базе данных есть информация о кинофильмах, снятых на киностудии «Paramount
Pictures», то выдать всю информацию о снимавшихся в этих фильмах кинозвездах.
SELECT DISTINCT Star.name, birthday, gender, address
FROM Star, starsIn
WHERE EXISTS ( SELECT Movie.title FROM Movie, starsIn AS a
WHERE Movie.studioName = 'Paramount Pictures'
AND Movie.title = a.movieTitle
AND Movie.year = a.movieYear
AND a.starName = Star.name
);
Операторы EXISTS, ANY, ALL, подобно булевым операторам AND, OR, NOT, возвращают
значения «истина» или «ложь». Это значит, что такие операторы можно комбинировать.
Пример 14.
Если в базе данных не существует информации о кинофильмах, снятых ранее 1930 года, то
выдать всю информацию о самом раннем зарегистрированном в базе данных фильме, и о
кинозвездах, снявшихся в нем.
SELECT TOP 1 Movie.*, Star.name, birthday, gender, address
FROM Movie, Star, starsIn
WHERE NOT EXISTS (SELECT * FROM Movie AS a
WHERE a.year <=1920)
AND Movie.title = starsIn.movieTitle
AND Movie.year = starsIn.movieYear
AND starsIn.starName = Star.name
ORDER BY Movie.year ASC;
В этом примере если хотя бы из четырех условий в предложении WHERE внешнего запроса
окажется ложным, запрос вернет пустое множество строк.
Спецкурс (10 семестр)
Специальность «Экономическая кибернетика»
10 из 10
Оператор ALL используется для отбора в главном запросе только тех записей, которые
удовлетворяют сравнению со всеми записями, отобранными в подчиненном запросе.
Оператор ANY более «мягкий», т.к. используется для отбора в главном запросе только тех
записей, которые удовлетворяют сравнению с любой записью, отобранной в подчиненном
запросе.
Пример 15.
Выдать информацию о кинозвездах, которые родились раньше любой кинозвезды,
снявшейся в фильмах до 1998 года включительно.
SELECT Star.name, birthday
FROM Star
WHERE birthday < ALL (SELECT a.birthday FROM Star AS a, starsIn
WHERE starsIn.movieYear<=1998
AND starsIn.starName = a.name);
В запросах на выборку в предложении SELECT можно использовать самостоятельные
запросы на выборку, облегчающие получение нужных полей из других таблиц.
Пример 16.(Дополнительные запросы в предложении SELECT)
Показать наибольшую продолжительность среди кинофильмов, и для каждого фильма
показать, на сколько этот фильм короче самого длинного фильма.
SELECT (SELECT Max(movie.length) FROM Movie) AS
[Наибольшая продолжительность фильма],
[Наибольшая продолжительность фильма] – movie.length AS [на сколько минут
дольше],
movie.title AS [Название], movie.studioName AS [Студия]
FROM movie;
Результат запроса показан на рис.3.3:
Рисунок 3.3 – Сравнение продолжительностей кинофильмов.
Документ
Категория
Техническая литература
Просмотров
27
Размер файла
382 Кб
Теги
1/--страниц
Пожаловаться на содержимое документа