Лекции SQL. Лекции по sql учебное пособие для студентов

• Название: Лекции по sql учебное пособие для студентов
• Анкор: Лекции SQL
• Дата: 16.10.2019
• Размер: 0.84 Mb.
• Имя файла: Lekcii_po_SQL_CHupinNA.pdf
• Тип: Лекции; #90508
• страница: 2 из 6

Таблица Fakultety
Описание: содержит перечень факультетов, ее содержание указано ниже:

— 11 —
Структура таблицы:
Поле
Назначение поля shortname
Краткий код факультета, строковое поле длины не более 10 символов, первичный ключ fullname
Полное название факультета, строковое поле с максимальной длиной строки 100 символов dekan
Фамилия имя отчество декана, строковое поле с максимальной длиной строки 100 символов
Данные в таблице ( пример ): shortname fullname dekan
ЕГФ
Естественно-географический факультет
ФИиП
Факультет истории и права
ФМФ
Физико-математический факультет
Старовикова И.В.
ФФ
Филологический факультет
Таблица Spisok
Описание: содержит список студентов вуза (в учебных целях для пояснения некоторых подробностей SQL в таблице содержится только малая часть реальных данных)
Структура таблицы:
Поле
Назначение поля
Id
Числовой первичный ключ
Fam
Фамилия студента, строковое поле с максимальной длиной строки 30 символов
Im
Имя студента, строковое поле с максимальной длиной строки 30 символов
Ot
Отчество студента, строковое поле с максимальной длиной строки 30 символов

— 12 — kod_fakulteta Краткий код факультета, строковое поле с максимальной длиной строки 10 символов, вторичный ключ, связывающий данную таблицу с таблицей Fakultety
Kurs
Курс (год обучения), числовое поле date_r
Дата рождения, поле типа ДАТА
Подразумевается, что таблицы связаны между собой через поля связи kod_fakulteta (таблица Spisok) и shortname (таблица Fakultety). Например, чтобы узнать полное название факультета, на котором учится студент, нужно прочитать в таблице Spisok в поле kod_fakulteta краткий код факультета, а затем искать в таблице Fakultet такую запись, где поле shortname заполнено таким же значением. Обычно в СУБД устанавливают связь «явно», добавляя в таблицу spisok ограничение типа «вторичный ключ» (FOREIGN KEY), однако для изучения основ SQL наличие вторичного ключа не является необходимым.
Данные в таблице Spisok:
Так как табличная форма представления данных из таблицы Spisok была бы слишком громоздка, мы укажем данные в специальном виде, в котором, впрочем, легко разобраться. Как будет объяснено позже, такой вид имеют команды SQL для вставки записей в таблицу.
INSERT INTO spisok (id, fam, im, ot, date_r, kod_fakulteta) VALUES (1001,
'Петров', 'Петр', 'Петрович', '1990-01-01', 'ФМФ');
INSERT INTO spisok (id, fam, im, ot, date_r, kod_fakulteta) VALUES (1002,
'Сергеев', 'Сергей', 'Сергеевич', '1990-02-02', 'ЕГФ');
INSERT INTO spisok (id, fam, im, ot, date_r, kod_fakulteta) VALUES (1003,
'Викторов', 'Виктор', 'Викторович', '1990-03-03', 'ФМФ');
Иногда, когда требуется что-то особенное, мы будем упоминать и другие таблицы, структура их полей и заполнение их данными будут очевидны из текста. Кроме того, иногда мы будем использовать какие-то поля в таблице
Spisok, которых нет в базовом примере, или какой-то особенный вариант заполнения таблицы данными, отличный от основного примера. Каждый раз из самого примера и его описания будет очевидно, что мы имеем в виду.
Примечание. При разработке этого учебного пособия мы первоначально решили избегать чисто английских терминов, заменив их так называемым транслитом – записью русских слов латиницей (Например, kod_fakulteta).
Казалось, что этим решается проблема недостаточного знания английского языка у многих студентов, а также реализуется стремление избежать ситуаций, когда английское слово совпадает со служебным словом SQL или техническим термином, используемым в СУБД. Однако выяснилось, что

— 13 — такой вариант не всегда оптимален. Поэтому в итоге получилась смесь двух подходов – использовать упомянутый транслит в сочетании с русскими терминами и использовать английские термины (например, shortname – краткий код). Здесь же попутно заметим, что разработчики обычно одинаковые по смыслу поля называются одинаково, но мы из методических соображений, чтобы подчеркнуть, что совпадение названий не является обязательным требованием, а также для удобства обсуждения некоторых проблем, назвали их по-разному: в таблице Fakultety поле названо shortname, а в таблице Spisok такое же по смыслу поле названо kod_fakulteta.
1. Запросы на выборку
данных из таблицы
1.0. Введение
?
Перед пользователем, работающим с базой данных, часто встает задача выбрать информацию из некоторой таблицы базы данных. В настоящее время практически все реляционные базы данных «понимают» язык запросов
SQL. От пользователя требуется уметь записать и запустить на выполнение в некоторой программе, которая умеет взаимодействовать с СУБД на языке
SQL, соответствующую SQL-инструкцию(команду).
1.1. Простейший вариант запроса
!
Простейший для понимания вариант запроса на языке SQL для выборки данных из одной таблицы выглядит так:
SELECT <список полей> FROM <имя таблицы>
Пример 1.1.
SELECT fam,im,ot,kurs FROM spisok
Данный запрос выбирает фамилии, имена, отчества и курс из таблицы spisok, в которой, как было указано во введении, хранится информация о студентах.
Напоминаем, что SQL игнорирует лишние пробелы и команды перехода на новую строку в тексте и поэтому этот запрос можно записывать и в несколько строк:
SELECT fam, im, ot, kurs

— 14 —
FROM spisok
Первый вариант обеспечивает экономию бумаги, второй вариант дает лучшую «читаемость».
Пример 1.2
SELECT shortname FROM fakultety
Данный запрос выбирает из таблицы fakultety только краткие названия факультетов.
Правила:
√ После SELECT можно указать одно из полей таблицы, несколько полей или все поля, которые имеются в таблице. Имеется возможность выбирать только часть полей, имеющихся в таблице базы данных. Это уменьшает сетевой трафик, то есть объем информации, пересылаемый по сети, и поэтому обеспечивает большую скорость при работе с удаленными базами данных, что особенно важно в низкоскоростных сетях.
√ Поля могут перечисляться не в том порядке, в котором они перечислены в таблице.
Пример 1.3
Два запроса
SELECT kurs, fam, im, ot FROM spisok
и
SELECT fam, im, ot, kurs FROM spisok
отличаются только тем, что в выходных данных будет другой порядок следования полей.
√ Вместо списка полей можно указать символ *, который означает, что в выходных данных следует вывести все поля, которые имеются в таблице.
При этом порядок следования полей будет тот же, что и в таблице. Это удобно, если вы не знаете, какие именно поля имеются в таблице, или пишете такой запрос, который бы пригодился и в дальнейшем, когда количество полей в таблице будет изменено в ходе модернизации базы данных.
Пример 1.4
SELECT *
FROM spisok

— 15 —
Данный запрос выведет все поля из таблицы spisok. Фактически он выведет всю информацию, которая имеется в этой таблице.
1.2. Условия отбора записей WHERE
?
Часто пользователю требуется вывести только часть записей, а именно те записи, которые удовлетворяют некоторому условию.
!
Использование условий выбора легко понять из следующих примеров:
Пример 1.5
SELECT fam, im, ot, kurs
FROM spisok
WHERE kurs=2
Данный запрос выведет фамилии, имена, отчества и курс только студентов второго курса.
Пример 1.6
SELECT fam, im, ot, kurs
FROM spisok
WHERE kod_fakultet=’ФМФ’
Данный запрос выведет фамилии, имена, отчества и курс студентов только физико-математического факультета.
Как легко понять, для текстов (а также для даты и времени) используются апострофы, для чисел они не нужны. В некоторых SQL-системах используются не апострофы, а двойные кавычки, или допустимо и то и другое.
В качестве знаков сравнения можно использовать =, <, >, <= (меньше или равно), >= (больше или равно), <> (не равно).
В условиях возможен случай, когда и слева и справа от знака сравнения фигурируют поля таблицы.
Пример 1.7
SELECT fam, im, ot
FROM spisok
WHERE sum_vneseno

— 16 —
Такой запрос выбирает клиентов, которые внесли сумму оплаты не полностью. В данном примере упоминаются поля sum_vneseno (сумма внесенная) и sum_oplata(сумма, требуемая к оплате), которые мы не описывали в базовом примере, но их смысл вполне ясен.
√ Условие отбора записей WHERE не является обязательным в SQL-запросе.
Если оно используется, то должно располагаться после FROM.
√ Условие отбора записей WHERE должно оцениваться в пределах одной записи. В дальнейшем мы увидим и более сложные примеры запросов, где условие оценивается в терминах группы записей или всей таблицы, но для этого используется не WHERE.
√ Использование условий в командах выборки данных на практике является почти обязательным при работе с удаленными базами данных. Это резко сокращает объем информации, передаваемой по сети от сервера клиенту.
Всю вычислительную работу по отбору части данных берет на себя серверная часть СУБД. Поскольку обычно сервер СУБД – это достаточно мощная машина, это вполне допустимо и даже желательно.
1.3. Логические связки AND OR NOT в WHERE
?
Иногда может потребоваться более сложное условие отбора записей.
!
Пример 1.8
SELECT fam, im, ot, kurs, kod_fakulteta
FROM spisok
WHERE kod_fakulteta=’ФМФ’ OR kod_fakulteta=’ЕГФ’
Такой запрос выбирает студентов двух факультетов.
√ Логические связки AND OR NOT используются для формирования сложных условий из простых.
√ Использование в условиях логических связок AND OR и NOT достаточно традиционно, поэтому нет смысла описывать их подробно. Понятно также, что для указания приоритета можно использовать круглые скобки.
√ Стоит заметить небольшое различие в использовании скобок в SQL и
Паскалем. В SQL знаки сравнения =, >, < и другие имеют больший приоритет в сравнении с логическими связками, поэтому при записи условий в SQL удается обходиться меньшим количеством круглых скобок. Например, в
Паскале условие, которое мы видим в примере, обязательно пришлось бы записывать со скобками (kod_fakulteta=’ФМФ’) OR (kod_fakulteta=’ЕГФ’).
Конечно, лишние скобки не изменят результат запроса. Многие программы

— 17 — автоматического построения SQL-запросов как раз грешат тем, что «не экономят» скобки.
√ Следует иметь в виду особенность русского разговорного языка.
Например, если неискушенный студент будет ориентироваться буквально на слова «выбрать студентов физико-математического и естественно- географического факультета» и напишет запрос
SELECT fam, im, ot, kurs, kod_fakulteta
FROM spisok
WHERE kod_fakulteta=’ФМФ’ AND kod_fakulteta=’ЕГФ’
он будет удивлен, что запрос не выдал ни одной записи в ответе. С другой стороны, логическая связка AND в русском языке часто прячется за служебным словом «а», например, «найти студента, имя которого Степан, а отчество Сергеевич» на языке SQL запишется так:
SELECT fam, im, ot, kurs, kod_fakulteta
FROM spisok
WHERE im=’Степан’ AND ot=’Сергеевич’
1.4. Оператор IN в WHERE
В SQL имеются нетрадиционные (в сравнении с другими языками, например, с Паскалем) операторы, применяемые в условиях WHERE.
?
Имеется средство для более краткой записи нескольких возможных значений некоторого поля таблицы.
!
Запишем два запроса, имеющие один и тот же смысл.
Пример 1.9
SELECT fam, im, ot
FROM spisok
WHERE kod_fakulteta=’ФМФ’ OR kod_fakulteta=’ЕГФ’
Пример 1.10
SELECT fam, im, ot
FROM spisok
WHERE kod_fakulteta IN (’ФМФ’,’ЕГФ’)
√ Оператор IN проверяет вхождение значения поля, записанного слева от него в список, который записывается в круглых скобках через запятую. В

— 18 — данном случае вряд ли обеспечивается какой-то выигрыш, однако позже мы увидим варианты использования IN, где без него просто не обойтись.
1.5. Оператор BETWEEN в WHERE
!
Приведем пример еще одного нетрадиционного оператора, применяемого в условиях. Запишем два запроса, имеющие один и тот же смысл.
Пример 1.11
SELECT fam, im, ot
FROM spisok
WHERE kurs>=1 AND kurs<=3
Пример 1.12
SELECT fam, im, ot
FROM spisok
WHERE kurs BETWEEN 1 AND 3
√ Оператор BETWEEN проверяет принадлежность значения поля некоторому диапазону.
√ На взгляд автора ценность оператора BETWEEN незначительна, вместо него вполне можно обойтись первым вариантом.
1.6. Оператор LIKE в WHERE
?
При работе с текстовой информацией часто возникает потребность найти значение, «похожее» на некоторый образец. Например, часто путаница происходит с женскими именами Наталия и Наталья, которые, по сути, есть одно и то же, но различие в их написании часто создает проблемы при оформлении документов.
!
Пример 1.13
SELECT fam, im, ot
FROM spisok
WHERE im LIKE ‘Натал_я’
В выходные данные попадут все записи о студентах с именем Наталья,
Наталия и, вообще, с любой предпоследней буквой или даже символом в этой позиции.

— 19 —
Пример 1.14
SELECT fam, im, ot
FROM spisok
WHERE fam LIKE ‘%щ%’ OR fam LIKE ‘%Щ%’
Будут выбраны все записи о студентах с фамилиями, в которых имеется заглавная или строчная буква Щ.
√ Оператор LIKE очень полезен при работе с полями текстового типа. Он позволяет проверить соответствие значения поля некоторому шаблону. В шаблоне разрешается использовать обычные символы и два специальных символа: символ подчеркивания _ обозначает, что в этой позиции может находиться любой символ, но обязательно ровно один символ. Символ процента
%
обозначает, что в этой позиции может находиться любое количество символов (в том числе нуль символов). Все другие символы обозначают «сами себя». Два этих специальных символа можно комбинировать и повторять в шаблоне несколько раз наряду с обычными символами.
1.7. Значение NULL в WHERE
?
Требуется выбрать студентов, у которых еще не заполнена дата рождения.
!
Пример 1.15
SELECT fam, im, ot
FROM spisok
WHERE date_r IS NULL
√ NULL означает неопределенное значение. Другими словами, NULL обозначает, что никакая информация в это поле еще не внесена. Хотя можно заметить, что отсутствие информации это тоже информация. Например, если у студента поле ДАТА ОТЧИСЛЕНИЯ не заполнено …
√ Противоположный вариант можно записать date_r IS NOT NULL или NOT
date_r IS NULL или date_r NOT IS NULL. Очевидно, такое разнообразие вариантов сделано для того, чтобы рядовой пользователь, на которого первоначально был рассчитан SQL, не утруждал себя подробностями правил синтаксиса языка.
√ Некоторая неприятность состоит в том, что SQL различает в условиях два случая – значение NULL и значение «пустая строка». Второй вариант проверяется примерно так: ot=’’ (поясняем – записано два апострофа,

— 20 — стоящие рядом, без пробела между ними). Этот вариант часто появляется, когда текстовое значение сначала было внесено, а затем удалено. Это следует иметь в виду разработчикам информационных систем.
1.8. Оператор DISTINCT в SELECT
?
Требуется выбрать те факультеты, для которых в таблице spisok имеются студенты.
Неправильный вариант запроса:
SELECT shortname FROM fakultet
Он отобразит все факультеты, в том числе и те, для которых в таблице spisok студентов нет.
Не очень хороший вариант:
SELECT kod_fakulteta FROM spisok
Он выдаст нужные коды факультетов, но они будут многократно повторяться, каждый код факультета будет повторяться столько раз, сколько студентов учится на данном факультете.
!
Пример 1.16
SELECT DISTINCT kod_fakulteta FROM spisok
√ Служебное слово DISTINCT подавляет повторяющиеся значения, так что каждое из них будет повторяться только один раз.
√ Если запрос выбирает несколько полей, то это служебное слово не повторяется перед каждым полем, а записывается только один раз после
SELECT.Более точно говоря, DISTINCT подавляет повторяющиеся значения всей записи, то есть запрос
SELECT DISTINCT kod_fakulteta, kurs FROM spisok
будет выдавать примерно такой ответ:
kod_fakulteta kurs
-----------------------------
ЕГФ 1
ЕГФ 2
ЕГФ 3

— 21 —
ЕГФ 4
ФМФ 1
ФМФ 2
ФМФ 3
ФМФ 4
1.9. Переименование полей
?
Часто требуется назвать выводимое поле не так, как оно названо в таблице.
!
Пример 1.17
SELECT shortname AS kod_fakulteta
FROM fakultet
В выводимых данных поле КРАТКИЙ КОД ФАКУЛЬТЕТА, которое в таблице имеет имя short name, будет фигурировать под именем kod_fakulteta.
1.10. Выражения в SELECT
?
Представим себе, что в таблице Spisok есть два поля: oklad и doplata, смысл которых ясен из названия. Требуется, чтобы запрос выводил бы общую денежную сумму, причитающуюся работнику.
!
Пример 1.18
SELECT fam,im,ot, oklad+doplata FROM spisok
√ В запросах можно указывать не только настоящее поле из таблицы, но и выражение из полей данной записи и констант. Естественно, выражение должно быть построено синтаксически правильно, например, oklad+fam явно не годится, поскольку смешаны числовое и строковое поля. С числовыми выражениями ситуация достаточно традиционна и вряд ли требует дополнительно объяснения. Однако выражения для текстовых полей строятся в различных СУБД по-разному. Тем более в разных СУБД сильно различаются перечни встроенных функций, которые также можно использовать в выражениях. Это следует иметь в виду при переносе информационной системы в другую СУБД.

— 22 —