Лекции SQL. Лекции по sql учебное пособие для студентов
 Скачать 0.84 Mb.
|
|
3.4. Объединение запросов. Команда UNION. Прежде всего заметим, что этот параграф не имеет прямого отношения к предыдущему материалу этой главы, и мы разместили его здесь, во-первых, чтобы читатель понял различие между понятиями соединения таблиц и объединения запросов, и, во-вторых, чтобы не создавать новую главу ради одного параграфа. ? Представим, что в БД одна таблица учитывает денежные суммы, которые фирма получила от других фирм в качестве предоплаты, а другая таблица учитывает задолженности других фирм перед нашей. В бухгалтерском учете это называется кредит и дебит. Возникла потребность свести эти денежные суммы в одну таблицу. ! Пример 3.6. Объединение UNION. SELECT klient, data_poluchenie AS date_s, -summa AS summa, FROM kredit UNION SELECT klient, data_otgruzka AS date_s, summa AS summa FROM debit ORDER BY 2,1 √ Фактически каждый запрос выполняется самостоятельно, и только выходные данные объединяются вместе. Чтобы выходные данные двух запросов можно было объединить, они должны иметь одинаковый тип и одинаковое название. Здесь нам пригодился способ переименования выходных полей, который был изучен ранее. Далее, объединенные выходные данные отсортированы, и здесь в ORDER BY удобно использовать не названия полей, а их порядковые номера. √ UNION автоматически удаляет повторяющиеся записи в объединенных выходных данных. Некоторые реализации SQL имеют дополнительный вариант UNION ALL, который как раз не устраняет повторяющиеся записи в выходных данных. 4. Использование вложенных запросов. 4.1.Как выполняются подзапросы. ? Представим себе, что нам нужно выбрать всех студентов физико- математического факультета, но мы не знаем краткого кода этого — 34 — факультета. Один из вариантов действий – это последовательно выполнить два запроса, сначала запрос к таблице fakultet, чтобы выяснить краткий код факультета по его полному названию, а затем, по полученному краткому коду (в нашем случае очевидно, что это будет ФМФ) составить и выполнить второй запрос, который будет выбирать из таблицы spisok студентов, у которых поле kod_fakulteta имеет значение ФМФ. ! Можно обойтись одним запросом, в котором имеется вложенный подзапрос. Пример 4.7. Использование подзапроса. SELECT fam, im, ot, kurs FROM spisok WHERE kod_fakulteta=( SELECT shortname FROM fakultet WHERE fullname=’Физико-математический факультет’) √ Как видите, даже использованием алиасов здесь необязательно – у СУБД не возникло нигде двусмысленности, из какой таблицы выбирать то или иное поле. √ Внимание. Подзапрос нельзя поставить справа от знака сравнения, то есть нельзя записать WHERE (SELECT shortname FROM fakultet WHERE fullname=’Физико-математический факультет’)=kod_fakulteta √ При использовании подзапросов необходимо внимательно следить, чтобы вложенный подзапрос обеспечивал результат, допустимый в предикате, в котором он используется. Запрос SELECT fam, im, ot, kurs FROM spisok WHERE kod_fakulteta=( SELECT shortname FROM fakultet WHERE fullname LIKE ‘Е%’) будет выполнен, потому, что в нашей таблице есть только один факультет с названием, начинающимся на Е, но запрос SELECT fam, im, ot, kurs FROM spisok WHERE kod_fakulteta=( SELECT shortname FROM fakultet WHERE fullname LIKE ‘Ф%’) будет признан ошибочным, поскольку в таблице fakultet есть несколько факультетов с названием, начинающимся на Ф. В то же время всегда правильным будет следующий запрос: — 35 — Пример 4.8. Использование подзапроса и IN . SELECT fam, im, ot, kurs FROM spisok WHERE kod_fakulteta IN ( SELECT shortname FROM fakultet WHERE fullname LIKE ’Ф%’) 4.2. Использование агрегатных функций в подзапросах. ? Требуется выбрать факультеты, на которых обучаются более 200 студентов (наша таблица spisok годится по структуре, но для этого примера должна быть пополнена записями о всех студентах). ! Пример 4.9. Использование агрегатных функций в подзапросах. SELECT f.fullname FROM fakultet f WHERE 200< (SELECT COUNT(id) FROM spisok s WHERE s.kod_fakulteta=f.shortname) √ Обратите внимание на то, что по стандарту SQL считаются ошибочными запросы, в которых SELECT стоит слева от сравнения. Однако в Interbase это как раз разрешается! 4.3. Оператор EXISTS. ? Требуется выбрать факультеты, на которых срок обучения превышает 5 лет. Иначе говоря, это те факультеты, на которых имеются студенты 6-го курса. ! Пример 4.10. Использование агрегатных функций в подзапросах. SELECT f.fullname FROM fakultet f WHERE EXISTS ( SELECT fam,im,ot FROM spisok s WHERE s.kod_fakulteta=f.shortname AND s.kurs=6) √ Оператор EXISTS используется в условиях и принимает значение «истина», если следующий за ним подзапрос имеет хотя бы одну запись выходных данных. Ясно, что вложенный подзапрос не будет давать ни одной строки выходных данных для тех факультетов, где нет студентов 6-го курса. — 36 — √ Без оператор EXISTS можно обойтись, но он отличается улучшенной «читаемостью», его смысл более соответствует написанию, чем другие варианты, обеспечивающие тот же результат. Например, тот же результат можно получить следующим запросом SELECT f.fullname FROM fakultet f WHERE 0<( SELECT COUNT(id) FROM spisok s WHERE s.kod_fakulteta=f.shortname AND s.kurs=6) √ Иногда бывает полезным и предикат NOT EXISTS, который обеспечивает противоположное условие. 5. Команды DML. Добавление записей, удаление и редактирование. Для добавления записей, удаления записей и редактирования записей язык SQL включает специальные инструкции, которые называют языком манипулирования данными (Data Manipulation Language, DML). Таким образом DML – часть SQL. 5.1. Добавление записей. Команда INSERT. ? Прежде чем использовать данные из таблиц, эти данные следует ввести в таблицы БД. ! Пример 5.1 INSERT INTO fakultet (shortname, fullname, dekan) VALUES (‘МПФ’, ‘музыкально-педагогический факультет’, NULL) Такая команда добавит в таблицу fakultet запись, причем поля, которые указаны после имени таблицы, получат значения, которые указаны после VALUES. Обратите внимание, поле dekan получит значение NULL, то есть неопределенное значение. Такое часто приходится делать, если в момент внесения записи значение этого поля еще неизвестно и будет заполнено в дальнейшем. Более точно, будет сделана попытка добавить запись. Кроме технических причин (наличие связи с БД), попытка добавления может закончиться неудачей, если добавляемая запись не удовлетворяет ограничениям на поля таблицы. Например, если поле shortname является первичным ключом, то — 37 — нельзя добавить запись с тем же значением этого поля, которое уже имеется у некоторой записи в таблице. Если поле dekan имеет ограничение NOT NULL, то есть обязательное для заполнения, то это ограничение также не позволит добавить вышеуказанную запись. Если мы попытаемся добавить запись в таблицу spisok, то сервер БД будет следить за тем, чтобы поле kod_fakulteta имело только такое значение, которое имеется в ссылочной таблице fakultet. √ Итак, запись будет добавлена, только если она удовлетворяет всем ограничениям на данные, которые имеются в БД. √ Список полей после названия таблицы может содержать не все поля таблицы, и они могут быть записаны в другом порядке, но список значений должен соответствовать списку полей (первое записанное в VALUES значение предназначено для поля, которое идет первым в списке полей и т.д.). √ Добавляемая запись займет свое место в таблице согласно значению того поля, которое является первичным ключом. 5.2. Добавление множества записей. Вставка результатов запроса. ? Для массового добавления записей можно использовать следующий вариант команды INSERT. ! Пример 5.2 INSERT INTO spisok2 (id, fam, im, ot) SELECT id,fam,im,ot FROM spisok WHERE kurs=5 Эта команда добавит в таблицу spisok2 все записи, которые будут результатом запроса SELECT к таблице spisok. √ Запрос, подготавливающий записи для добавления, не обязательно должен иметь такие же названия полей. Но они должны быть подходящего типа с учетом порядка следования полей. 5.3. Удаление записей. Команда DELETE. ? Иногда требуется удалить некоторые записи из таблицы базы данных. ! Пример 5.3 — 38 — DELETE FROM spisok WHERE kurs=5 Эта команда удалит из таблицы spisok все записи, касающиеся студентов 5-го курса. √ Та часть команды, которая определяет условие отбора записей для удаления, и начинается с WHERE, не является обязательной, но тогда будут удалены все записи из таблицы. √ Более точно говорить о том, что будет предпринята попытка удаления записей. Она может оказаться неудачной, если данные записи связаны ссылочной целостностью с некоторыми записями из другой таблицы. Например, в нашем основном примере, из таблицы fakuktet не удастся удалить записи о тех факультетах, студенты с которых фигурируют в таблице spisok. √ Если требуется удалить только одну запись, нужно обеспечить, чтобы условие после WHERE было истинно только для одной этой записи. Наиболее подходящим является условие вида «поле первичного ключа=..». √ Вообще говоря, следует максимально избегать удаления каких-либо сведений из БД, даже если на первый взгляд кажется, что они в дальнейшем не потребуются. Например, не очень хорошая идея удалять информацию о студентах, окончивших вуз. Правильнее переносить ее в некоторую архивную таблицу, или ввести поле, отмечающее факт окончания вуза. 5.4. Редактирование записей. Команда UPDATE. ? Иногда требуется изменить значения некоторых полей у каких-то записей из таблицы. ! Пример 5.4 UPDATE spisok SET kurs=kurs+1 WHERE dolgi=0 Эта команда удалит из таблицы spisok все записи, касающиеся студентов 5-го курса, не имеющих задолженностей. √ Та часть команды, которая определяет условие отбора записей для редактирования (буквальный перевод UPDATE – «обновление») записей, и — 39 — начинается с WHERE, не является обязательной, но тогда будут изменены все записи из таблицы. √ Новые значения полей должны удовлетворять всем ограничениям на таблицу, иначе они будут отвергнуты сервером БД. √ Если требуется удалить только одну запись, нужно обеспечить, чтобы условие после WHERE было истинно только для одной этой записи. Наиболее подходящим является условие вида «поле первичного ключа=..». √ Возможно одновременное изменение нескольких полей в каждой записи. Пример 5.5 UPDATE spisok SET fam=’Елкин’, fam_rp=’Елкина’, fam_dp=’Елкину’ WHERE fam=’Ёлкин’ Данный запрос изменит написание фамилии так, чтобы не использовать букву ‘Ё’, при этом будут изменены и все варианты фамилии в различных падежах. 6. Команды DDL. Определение структуры БД 6.1. Создание базы данных Для создания и изменения структуры БД в языке SQL имеется специальное подмножество команд – DDL(Data Definition Language, язык определения данных). В данной главе мы описываем его основные возможности. Данная глава в большей степени зависит от конкретной СУБД, однако различия в основном касаются используемых типов данных и не затрагивают концептуальной основы SQL. Мы излагаем тот вариант, который используется в СУБД Interbase. ? Как создать базу данных? ! Пример 6.1. Команда создания базы данных. CREATE DATABASE ‘KINF_2:F:\GDB\employee.gdb’ Пример 6.2. Команда создания базы данных, вариант с большим числом параметров. CREATE DATABASE ‘KINF_2:F:\GDB\employee.gdb’ PAGE_SIZE 2048 DEFAULT CHARACTER SET WIN1251; — 40 — Примечание. KINF_2 - это сетевое имя компьютера, на котором установлен и функционирует сервер СУБД. √ Применительно в Interbase отметим, что компьютер-сервер СУБД может быть невидим в сети средствами файловой системы (проводник Windows), или может быть также недоступен диск или папка, где намечается создать БД. Однако в команде полный путь, который записывается после сетевого имени компьютера, следует записывать так, как если бы вы сидели за тем компьютером. Сервер СУБД и клиент СУБД взаимодействуют между собой через так называемые порты и поэтому отсутствие доступа средствами файловой системы к файлу БД не является препятствием в создании и функционировании БД, более того, обеспечивает ее сохранность от преднамеренного или непреднамеренного уничтожения (повреждения) фала базы данных средствами файловой системы с удаленного компьютера. √ Команда CREATE DATABASE создает базу данных и устанавливает ее характеристики. Синтаксис команды. CREATE DATABASE "<имя файла базы данных>" [USER "<имя пользователя>" [PASSWORD "<пароль>"]] [PAGE_SIZE [=] int] [DEFAULT CHARACTER SET charset] Параметр <имя файла базы данных> включает и имя удаленного сервера. Формат зависит от используемой операционной системы. Рекомендуется в имени файла использовать только латинские буквы, цифры и символ подчеркивания. В качестве расширения имени СУБД InterBase использует .gdb. Параметр <имя пользователя> определяет владельца базы данных. Сразу после инсталляции СУБД InterBase «знает» только одного пользователя, который называется системным администратором, имеет имя SYSDBA и пароль masterkey. Имя обычно не меняют, а пароль, конечно, следует изменить. Системный администратор имеет право доступа ко всем базам данных на сервере. Он может также зарегистрировать других пользователей и пароли для них. Параметр <имя пользователя> должен использовать только одно из таких зарегистрированных имен. В параметре PAGE_SIZE целое число int указывает размер страницы, может быть 1024 (по умолчанию), 2048, 4096 или 8192. Этот параметр касается внутренней структуры файла БД и влияет на производительность, однако можно заметить, что файл базы данных растет путем добавления очередной страницы. Рекомендуется увеличить размер страницы, если база данных содержит таблицы, у которых размер записи велик, и наоборот, если — 41 — таблицы содержат много записей, но размер записи мал, размер страницы должен быть минимальным. Параметр DEFAULT CHARACTER SET определяет текущую кодировку для текстовых данных в таблицах. В принципе можно для некоторых таблиц из этой БД установить в дальнейшем и другую кодировку, однако это делают редко. 6.2.Типы данных Этот параграф не содержит описание некоторой команды SQL, однако он необходим для изучения дальнейшего. Здесь мы перечисляем наиболее популярные типы данных СУБД InterBase. Тип данных Описание BLOB Большой двоичный объект. Сохраняет данные большого объема, такие как графика, текст и цифровой звук. Имеет субтипы, из которых наиболее употребительны 0: данные общего вида; 1: тексты. Cубтип данных BLOB описывается в их контексте. Память под BLOB данные выделяется так называемыми сегментами. При объявлении можно изменять размер сегмента, например BLOB SEGMENT SIZE 1024. По умолчанию размер сегмента 80 байт. CHAR( Строка текста фиксированной длины (от 1 байта до 32767 байт). Альтернативное ключевое слово: CHARACTER VARCHAR( Строка текста переменной длины (от 1 байта до 32767 байт) Альтернативные ключевые слова VARYING CHAR, VARYING CHARACTER DATE Дата (включает также информацию о времени) DECIMAL (<точность>, <масштаб>) Вещественные числа. Параметр <точность> (от 1 до 15) определяет, что сохраняется, по крайней мере столько цифр числа. <Масштаб> (от 1 до 15) определяет число знаков после запятой. Должно быть меньше или равно параметра <точность>. Размер в памяти зависит от указанной точности. NUMERIC Вещественные числа. <Точность> (от 1 до 15) определяет, что сохраняется, по крайней мере столько — 42 — (<точность>, <масштаб>) цифр числа. <Масштаб> (от 1 до 15) определяет число знаков после запятой. Должно быть меньше или равно параметра < Точность >. Размер в памяти зависит от указанной точности. FLOAT Вещественное число. 7 цифр точности. Занимает 4 байта. DOUBLE PRECISION Вещественное число повышенной точности. Для научных вычислений: 15 цифр точности. Занимает всегда 8 байт. INTEGER Длинное целое от -2 147 483 648 до 2 147 483 648. Занимает 4 байта. SMALLINT Короткое целое от -32768 до 32767. Занимает 2 байта. 6.3. Простые варианты команд создания таблиц и изменения их структуры В данном параграфе мы приведем краткое изложение наиболее часто используемых команд DDL. В последующих параграфах будут даны дополнения. ? Как создать таблицу в базе данных? ! Пример 6.3. Команда создания таблицы Fakultet. CREATE TABLE fakultet (shortname VARCHAR(10) NOT NULL, fullname VARCHAR(100), dekan VARCHAR(100), PRIMARY KEY( shortname)) Пример 6.4. Команда создания таблицы Spisok. CREATE TABLE spisok (id INTEGER NOT NULL, fam VARCHAR(30) NOT NULL, im VARCHAR(30) NOT NULL, ot VARCHAR(30), kod_fakulteta VARCHAR(10), kurs INTEGER, date_r DATE, gruppa VARCHAR(20), PRIMARY KEY( id)) Пример 6.5. Изменение структуры таблицы Spisok – добавление вторичного ключа для поля kod_fakulteta. ALTER TABLE spisok ADD CONSTRAINT m18 FOREIGN KEY kod_fakulteta |