Предыдущая лекция
|
Содержание
|
Следующая лекция
Программирование. Лекция №2. Операции выборки данных.
Операции выборки данных. Примеры запросов.
Простая выборка "SELECT *"
Выдать полные характеристики для всех поставщиков:
SELECT *
FROM Поставщики;
Результатом служит копия полной таблицы Поставщики:
| Номер_Поставщика |
Фамилия |
Город |
| 1 |
Смит |
Лондон |
| 2 |
Джонс |
Париж |
| 3 |
Блейк |
Париж |
| 4 |
Кларк |
Лондон |
| 5 |
Адамс |
Атенс |
Здесь звезда или звездочка служит кратким обозначением списка всех имен полей в таблице
(таблицах), указанной(ых) во фразе FROM (из) в том порядке, в котором эти поля определяются
в соответствующем (их) предложении (ях) CREATE TABLE. Таким образом, записанное выше
предложение SELECT эквивалентно следующему:
SELECT НОМЕР_ПОСТАВЩИКА, ФАМИЛИЯ, ГОРОД
FROM Поставщики;
Отметим, наконец, что "*" может уточняться именем соответствующей таблицы. Допустима,
например, следующая форма записи:
SELECT S.*
FROM S;
Выборка с исключением дубликатов
Выдать различные цвета для всех поставляемых деталей:
SELECT DISTINCT ЦВЕТ
FROM Детали;
В этом случае результат таков:
Цвет
зеленый
красный
голубой
Выборка вычисляемых значений
Выбрать названия деталей и вес в килограммах:
SELECT Название, Вес / 1000
FROM Детали;
Получаем результат:
Название Вес
винт 0,017
болт 0,015
гайка 0,012
винт 0,015
шуруп 0,02
Фраза SELECT (и фраза WHERE) может включать арифметические выражения, а также простые имена
полей. Можно, кроме того, осуществлять выборку просто констант. Например:
SELECT Название, 'Вес в граммах = ', Вес / 1000
FROM P;
Получаем результат:
Название Вес
винт Вес в граммах = 0,017
болт Вес в граммах = 0,015
гайка Вес в граммах = 0,012
винт Вес в граммах = 0,015
шуруп Вес в граммах = 0,02
Заметим, что в этом результате три столбца.
В связи с этим примером возникает следующий вопрос: что произойдет, если вес какой-либо
детали имеет неопределенное значение (NULL)? Напомним, что NULL представляет неизвестное
значение. Предположим, например, что вес детали Гайка задан в базе данных как неопределенное
значение вместо значения 12. Каково тогда значение выражения Вес/1000 для этой детали?
Ответ состоит в том, что оно также является неопределенным значением. В общем случае
фактически любое арифметическое выражение считается имеющим неопределенное значение,
если какой-либо из его операндов сам имеет неопределенное значение. Неопределенные
значения показываются на терминале как тире или дефис.
Ограниченная выборка
Выдать поставщиков, которые находятся в Лондоне и имеют номер больше, чем 3:
SELECT НОМЕР_ПОСТАВЩИКА
FROM Поставщики
WHERE ГОРОД = 'Лондон'
AND Номер_Поставщика > 3;
Результат:
Номер_Поставщика Фамилия Город
4 Кларк Лондон
Условие, или предикат, следующий за ключевым словом WHERE, может включать операторы
сравнения =, <> (неравно), >, > =,<, и < = (слайд №13), булевские операторы AND (и),
OR (или) и NOT (нет), а скобки указывают требуемый порядок вычислений. В таком предикате
числа сравниваются алгебраически - отрицательные числа считаются меньшими, чем
положительные, независимо от их абсолютной величины. Строки литер сравниваются в
соответствии с их представлением в коде ASCII. Если нужно сравнить две строки литер,
имеющих разные длины, более короткая строка концептуально дополняется справа пробелами
для того, чтобы обе строки имели одинаковую длину перед тем, как будет осуществляться
их сравнение.
Выборка с упорядочением
Выдать номера, фамилии и города поставщиков, находящихся в Лондоне, в обратном алфавитном
порядке:
SELECT Номер_поставщика, Фамилия, Город,
FROM Поставщики
WHERE Город = 'Лондон'
ORDER BY Фамилия DESC;
Результат:
Номер_Поставщика Фамилия Город
1 Смит Лондон
4 Кларк Лондон
В общем случае не гарантируется, что результирующая таблица будет упорядочена каким-либо
определенным образом. Здесь, однако, пользователь специфицировал, что результат перед тем,
как он будет показан, должен быть организован в определенной последовательности.
Упорядочение может быть:
имя-столбца [упорядочение] [,имя-столбца [упорядочение]]...,
где "упорядочение", как и ранее, это ASC (возрастание) или DECS (убывание), и по умолчанию
принимается ASC. Каждое "имя-столбца" должно идентифицировать некоторый столбец результирующей
таблицы. Поэтому, например, следующее предложение недопустимо:
SELECT НОМЕР_ПОСТАВЩИКА
FROM Поставщики
ORDER BY ГОРОД;
Выборка с использованием BETWEEN (между)
Выбрать все детали, вес которых находится в диапазоне от 10 до 15 включительно.
SELECT Номер_Поставщика, Название, Вес
FROM Детали
WHERE Вес BETWEEN 10 AND 15;
Имеем следующий результат:
Номер_Поставщика Название Вес
1 гайка 12
1 болт 15
4 винт 15
Может быть также специфицировано NOT BETWEEN (не принадлежит диапазону между), например:
SELECT Номер_Поставщика, Название, Вес
FROM Детали
WHERE Вес NOT BETWEEN 10 AND 15;
Получаем тогда:
Номер_Поставщика Название Вес
3 винт 17
4 шуруп 20
Выборка с использованием IN (принадлежит)
Выбрать все детали, вес которых равен 10, 12 или 15.
SELECT Номер_Поставщика, Название, Вес
FROM Детали
Where Вес IN (10, 12, 17);
или
SELECT Номер_Поставщика, Название, Вес
FROM Детали
WHERE Вес = 10
OR Вес = 12
OR Вес = 17;
Результат:
Номер_Поставщика Название Вес
1 гайка 12
3 винт 17
Имеется также в распоряжении предикат NOT IN (не принадлежит), например предложение:
SELECT Номер_Поставщика, Название, Вес
FROM Детали
Where Вес NOT IN (10, 12, 17);
дает результат:
Номер_Поставщика Название Вес Цвет
1 болт 15 зеленый
4 винт 15 красный
4 шуруп 20 голубой
Подобно предикату IN предикат NOT IN может рассматриваться только как сокращенная запись
другого предиката, который не использует NOT IN.
Выборка с использованием предиката LIKE (похоже на)
Обычно предикат LIKE имеет форму:
имя_столбца LIKE литера_строковая_контстанта,
где "имя_столбца" должно обозначать столбец типа CHAR или VARCHAR. Этот предикат принимает
для заданной записи значение истина, если значение в указанном столбце соответствует
образцу, специфицируемому "литерной_строковой_константой". Литеры этой константы
интерпретируются следующим образом:
- Литера "_" (разрыв или подчеркивание) обозначает любую одиночную литеру
- Литера "%" (процент) обозначает любую последовательность из n литер (n может быть нулем)
- Все другие литеры обозначают сами себя
Рассмотрим применение оператора LIKE на трех примерах:
1)
SELECT Название, Цвет
FROM Детали
WHERE Цвет LIKE 'к%' значение Цвет начинается с литеры 'к'
2)
SELECT Название, Цвет
FROM Детали
WHERE Название LIKE '_ _ _ т'; значение Название состоит из 4-х символов, причем последний 'т'
3)
SELECT Название, Цвет
FROM Детали
WHERE Цвет NOT LIKE '%а%'; значение Цвет не содержит литеры 'а'
Выборка, при которой вовлекается NULL (неопределенное значение)
Часто, будут иметься записи в таблице которые не имеют никаких значений для каждого пол,
например потому что информация не завершена, или потому что это поле просто не
заполнялось. SQL учитывает такой вариант, позволяя вам вводить значение NULL(ПУСТОЙ) в
поле, вместо значения. Когда значение пол равно NULL, это означает, что программа базы
данных специально промаркировала это поле как не имеющее никакого значения для этой
строки (или записи). Это отличается от просто назначения полю, значения нуля или пробела,
которые база данных будет обрабатывать также как и любое другое значение. Точно также, как
NULL не является техническим значением, оно не имеет и типа данных. Оно может помещаться в
любой тип пол. Тем не менее, NULL в SQL часто упоминается как нуль.
Неопределенное значение
Фактически любой столбец может содержать неопределенное значение, если в определении этого
столбца в предложении CREATE TABLE явным образом не специфицировано NOT NULL (неопределенное
значение не допускается). Неопределенное значение - это специальное значение, которое
используется для того, чтобы представлять "неизвестное значение" или "неприменимое значение".
Это не то же самое, что пробел или ноль.
Вернемся к предложению CREATE TABLE для базовой таблицы Поставщики. Мы специфицировали NOT
NULL только для столбца НОМЕР_ПОСТАВЩИКА. Результатом этой спецификации является гарантия
того, что запись каждого поставщика в базовой таблице Поставщики всегда будет содержать
какой-либо реальный (отличный от неопределенного значения) номер поставщика. Напротив,
любое из значений ФАМИЛИЯ и ГОРОД или все они могут быть неопределенными в той же самой
записи.
CREATE TABLE Поставщики
(Номер_Поставщика int NOT NULL,
Фамилия char (20),
Город char (15));
Так как NULL указывает на отсутствие значения, вы не можете знать каков будет результат
любого сравнения с использованием NULL. Когда NULL сравнивается с любым значением, даже с
другим таким же NULL, результат будет ни верным ни неверным, он - неизвестен. Неизвестный
Булев, вообще ведет себя также как неверна строка, которая произведя неизвестное значение
в предикате не будет выбрана запросом - имейте ввиду что в то врем как NOT(неверное) -
равняется верно, NOT (неизвестное) - равняется неизвестно. Следовательно, выражение
типа 'Вес = NULL' или 'Вес IN (NULL)' будет неизвестно, независимо от значения Вес.
Часто вы должны делать различи между неверно и неизвестно - между строками содержащими
значения столбцов которые не соответствуют условию предиката и которые содержат NULL
в столбцах. По этой причине, SQL предоставляет специальный оператор IS, который
используется с ключевым словом NULL, для размещения значения NULL. Найдем все записи в
нашей таблице Детали с значениями не равными NULL в столбце Вес:
SELECT Детали.*
FROM Детали
WHERE Вес IS NOT NULL;
Запросы, использующие присоединение.
Способность "соединять" две или более таблицы в одну представляет собой одну из наиболее
мощных возможностей реляционных систем. Фактически наличие операции соединения (join) -
едва ли не самое главное, что отличает реляционные системы от систем других типов.
Итак, что такое соединение? Говоря нестрого, это запрос, в котором выборка данных
осуществляется более чем из одной таблицы. Ниже приводится простой пример.
Простое эквисоединение
Объединение двух таблиц по общему полю "Номер_Поставщика":
SELECT Поставщики.*, Детали.*
FROM Поставщики, Детали
WHERE Поставщики.Номер_Поставщика = Детали.Номер_Поставщика;
Заметим, что здесь ссылки на поля во фразе WHERE должны уточняться именами содержащих их
таблиц.
В результате получим следующую ниже таблицу.
Номер_Поставщика Фамилия Город
1 Смит Лондон
2 Джонс Париж
3 Блейк Париж
4 Кларк Лондон
5 Адамс Атенс
Номер_Поставщика Название Вес Цвет
1 гайка 12 красный
1 болт 15 зеленый
3 винт 17 голубой
4 винт 15 красный
4 шуруп 20 голубой
Номер_Поставщика Фамилия Город Номер_Поставщика Название Вес Цвет
1 Смит Лондон 1 болт 15 зеленый
1 Смит Лондон 1 гайка 12 красный
3 Блейк Париж 3 винт 17 голубой
4 Кларк Лондон 4 винт 15 красный
4 Кларк Лондон 4 шуруп 20 голубой
Результат данного запроса называется соединением таблиц Поставщики и Детали по соответствию
значений Номер_Поставщика. Термин "соединение" используется также для обозначения операции
конструирования такого результата.
Условие Поставщики.Номер_Поставщика = Детали.Номер_Поставщика называется условием соединения
или предикатом соединения. В связи с приведенным примером нужно отметить ряд моментов. Одни
из них имеют большое значение, другие не настолько существенны.
- Необязательно, чтобы поля в предикате соединения имели одинаковые имена, хотя очень
часто это будет именно так
- Нет необходимости в том, чтобы оператор сравнения в предикате соединения обязательно
был равенством, хотя это будет очень часто. В дальнейшем будут приведены примеры такого
рода. В случае оператора равенства соединение называют иногда эквисоединением
- Оба поля в предикате соединения должны быть либо числовыми, либо строками литер.
Не обязательно, чтобы их типы данных были идентичны. Однако, по соображениям
производительности, это было бы, вообще говоря, неплохо.
- Фраза WHERE в SELECT-соединении может включать, помимо самого предиката соединения,
другие условия. Эта возможность иллюстрируется ниже.
- Можно, конечно, предусмотреть в SELECT выборку только специфицированных полей соединения,
а не их всех.
- Выражение
SELECT Поставщики.*,Детали.*
FROM Поставщики, Детали
. . . . . . .;
может быть еще более упрощено:
SELECT *
FROM Поставщики, Детали
. . . . . . .;
С другой стороны, оно может быть записано и в расширенном виде.
- По определению, эквисоединение должно продуцировать результат, содержащий два
идентичных столбца. Если исключить один из этих столбцов, то оставшееся называется
естественным соединением. Для того, чтобы построить естественное соединение таблиц
Поставщики и Детали по городам в SQL, следовало бы записать:
SELECT Поставщики.НОМЕР_ПОСТАВЩИКА, ФАМИЛИЯ, ГОРОД,
Детали.НОМЕР_ПОСТАВЩИКА, НАЗВАНИЕ, ЦВЕТ, ВЕС
FROM Поставщики, Детали
WHERE Поставщики.Номер_Поставщика = Детали.Номер_Поставщика;
Естественное соединение является, вероятно, одной из наиболее полезных форм соединения - в
такой степени, что мы часто используем неуточненный термин "соединение" специально для
обозначения этого случая.Можно образовывать соединения также и трех, четырех, ... или
любого числа таблиц. В примере показано соединение трех таблиц.
Прежде всего построим декартово произведение таблиц, перечисленных во фразе FROM.
Декартово произведение множества, состоящего из n таблиц,- это таблица, содержащая
всевозможные строки r, такие, что r является конкатенацией какой-либо строки из первой
таблицы, строки из второй таблицы, ... и строки из n-й таблицы. Теперь исключим из этого
декартова произведения все такие строки, которые не удовлетворяют предикату соединения.
То, что останется, является требуемым соединением. Между прочим, вполне возможно, хотя,
может быть, и несколько необычным образом, сформулировать в языке SQL запрос, результатом
которого будет декартово произведение.
Например:
SELECT Поставщики.*, Детали.*
FROM Поставщики, Детали;
Соединение с дополнительным условием
Выбрать фамилии поставщиков, находящихся в Лондоне и поставляющих деталь 'винт':
SELECT Фамилия
FROM Поставщики, Детали
WHERE Поставщики.Номер_Поставщика = Детали.Номер_Поставщика
AND Поставщики.Город = 'Лондон'
AND Детали.Название = 'винт';
Результат условия Поставщики.Номер_Поставщика = Детали.Номер_Поставщика
Номер_Поставщика Фамилия Город Номер_Поставщика Название Вес Цвет
1 Смит Лондон 1 болт 15 зеленый
1 Смит Лондон 1 гайка 12 красный
3 Блейк Париж 3 винт 17 голубой
4 Кларк Лондон 4 винт 15 красный
4 Кларк Лондон 4 шуруп 20 голубой
Конечный результат
Фамилия
Кларк
