Методические указания для студентов специальности 2205, 0755 «Проектирование и технология эвс», «Комплексная информационная безопасность автоматизированных систем» (стр. 11 из 21)
- выражение, которое может включать пользовательскую функцию;
- параметр FROM задается всегда. Определяет имена всех баз данных, которые принимают участие в запросе;
- <выражение> может быть полем, имя может быть составным т.е. из нескольких констант.
Необязательная фраза AS <колонка> специфицирует заголовок колонки в выводе результата запроса. Это полезно, когда имеются <выражения> или содержатся функции работы с полями и требуется присвоить колонке в результате соответствующее имя.
<колонка> может быть выражением.
Если в разных базах одни и те же имена и они одновременно выводятся на экран, то колонки получат одинаковое название и букву алфавита fam; fam a, fam b ...
Если не устраивают имена колонок данных по умолчанию, можно их обозначить по-своему, используя выражение:
[AS <Новое наименование колонки>]
Псевдоним БД, откуда берется нужное поле может, быть назначен в команде SELECT. Вне команды действия не имеет. Рассмотрим примеры. Будем использовать спроектированную учебную базу BOOK , структура которой приведена ниже.
База спроектирована для компании занимающейся выпуском книг и содержащей несколько издательств.
Руководство компанией интересует информация о книгах, выпускаемых в издательствах, об авторах книг и редакторах, а также о финансовом положении компании. При этом в базе данных должно было быть учтено, что:
- автор может написать несколько книг;
- книга может быть написана несколькими авторами;
- порядок следования авторов в заглавии книги влияет на гонорар;
- редактор может работать с несколькими книгами и несколько редакторов с одной;
- в заказе на покупку может быть перечислено несколько книг.
Исходя из вышесказанного, были выделены объекты и определены их характеристики.
Объект Autors – авторы, книги которых опубликованы компанией, имеют следующие атрибуты: имя автора, полный адрес, где он проживает, телефон.
Объект Titles – книги, имеют атрибуты: название книги, ее стоимость, дату выпуска.
Объект Editors - редакторы, работающие на компанию, имеют атрибуты аналогичные объекту авторы: имя, адрес, телефон.
Объект Publishers – имеет атрибуты: наименование издательства, которыми владеет компания, его адрес, и телефон. В результате проектирования получим совокупность таблиц приведенную выше.
Допустим, необходимо вывести значения всех столбцов. Оператор SELECT в этом случае запишется:
SELECT *
FROM PUBLISERS
| Pub_id | Pub_name | Address | City | State | Zip |
Тот же самый результат можно получить, перечислив в списке выбора все поля таблицы.
SELECT PUB_ID, PUB_NAME, ADDRESS, СITY, STATE, ZIP
FROM PUBLISHERS
Если необходимо осуществить выбор отдельных столбцов, то в списке выбора перечисляют необходимые столбцы в порядке их отображения на экране.
SELECT PUB_ID, PUB_NAME
FROM PUBLISHERS
С помощью оператора возможно переименование столбцов и задание имен выражениям. При выводе результатов запроса каждый столбец по умолчанию получает заголовок, совпадающий с его именем в БД. Поскольку столбцы в БД обычно имеют сокращенные имена, желательно им присвоить полные. Выполняется это с помощью предложения AS. В списке выбора пишут:
имя столбца AS имя заголовка
SELECT PUB_NAME AS PUBLISHERS, PUB_ID
FROM PUBLISHERS
Таблица с поименованными столбцами приведена ниже.
| Publishers | pub_id |
В большинстве систем ширина отображаемых столбцов устанавливается по максимальной длине заголовка (но не уже находящихся в них данных). Как правило, в заголовках столбцов нельзя использовать кавычки и пробелы, поэтому нет возможности применять русские названия столбцов. Если в списке выбора задано выражение, то для него тоже можно создать заголовок.
Например, если мы хотим посмотреть, как изменится стоимость книг, если мы увеличим их стоимость на 10% , то запрос может выглядеть так:
SELECT TITLE, PRICE AS OLD_PRICE, PRICE * 1.1 AS NEW_PRICE
FROM TITLE
| TITLE | OLD_PRICE | NEW_PRICE |
Пояснения в строках
Например:
SELECT 'СТОИМОСТЬ КНИГИ', TITLES, 'СТАЛА', PRICE*1.1 AS NEW_PRICE
FROM TITLE
В списках с числовыми данными и константами можно выполнять следующие арифметические действия: +, -, *, /. (в некоторых системах введена операция остаток от деления - %)
Таблицам можно присвоить имена- псевдонимы, что бывает полезно для осуществления соединения таблицы с самой собой или для доступа из вложенного подзапроса к текущей записи внешнего запроса.
Например:
SELECT Р. PUB_ID, Р.PUB_NAME
FROM PUBLISHERS Р
р – здесь псевдоним, p.pub_id – полное наименование поля.
Выполнение запроса
SELECT * FROM AUTORS, TITLES
Соответствует декартову произведению таблиц AUTORS, TITLES.
5.1.1. Определение критерия отбора данных.
Отбор данных в выборке производится посредством ключевого слова WHERE:
WHERE <условие связи> [AND <условие связи>...]
[ARD/OR <условие отбора> [AND/OR < >...]]
<условие связи> применяется, если выборка делается более чем из одной базы, и указывает критерий, которому должны удовлетворять поля из разных баз. Здесь используются операции отношений =, #, >, <, >=, <=, допускается применение нескольких критериев, соединенных знаком AND.
Если включить в запрос две базы данных и не специфицировать условие связи, то каждая запись из первой базы данных будет соединяться с каждой записью из второй базы данных при выполнении условия фильтрации.
<условие отбора> специфицирует критерий, которому должны удовлетворять записи, чтобы попасть в результат действия запроса. Условия фильтрации можно соединять операторами AND и OR. Можно также использовать оператор NOT для реверса значения логического выражения.
Операторы условия =, >, <, >=, <=, !=, < > применяются к данным имеющим тип: числовой, символьный, дата.
Например, необходимо найти все книги стоимостью более 250 рублей.
SELECT TITLE, PRICE
FROM TITLE
WHERE PRICE > 250
Запрос, в котором необходимо найти книги, выпущенные издательством до 1999г., выглядит следующим образом:
SELECT TITLE, TITLE_ID, PRICE
FROM TITLE
WHERE PUB_DATE < {01. 01. 1999}
Допустим, необходимо найти авторов живущих не в Томске и их телефоны:
SELECT AU_ID, PHONE
FROM AUTORS
WHERE CITY != 'ТОМСК'
Возможно совместное использование условных и логических операторов
Пусть нужно найти книги по Windows стоимостью выше 250 руб. и затратами на выпуск ниже 2 000 000.
SELECT TITLE, TYPE, PRICE, ADVANCE
FROM TITLE
WHERE TYPE = 'WINDOWS' AND PRICE > 250 AND ADVANCE < 2000000
При составлении логических выражений нужно быть внимательным. Рассмотрим два примера. В первом примере в выборку попадут все книги по бизнесу независимо от затрат на их производство и книги по искусству, затраты на выпуск которых превысили сумму в 5 500 000 рублей.
SELECT TITLE_ID, TYPE, PRICE, ADVANCE
FROM TITLE
WHERE TYPE ‘БИЗНЕС’
OR TYPE = ‘ИСКУССТВО’
AND ADVANCE > 5 500 000
Во втором случае за счет расставленных скобок в выборку попадут все книги по искусству и бизнесу затраты на выпуск которых превысили 5500000.
SELECT TITLE_ID, TYPE, ADVANCE
FROM TITLE
WHERE (TYPE = 'БИЗНЕС ' OR TYPE = 'ИСКУССТВО')
AND ADVANCE > 5 500 000
Еще один пример. Необходимо найти книги, затраты на выпуск которых не окупились.
Доход от продажи определяется как ytd_sales * price, advance - сумма затрат. Книги выпущены два года назад.
SELECT TITLE_ID, TYRE, PRICE, ADVANCE, YTD_SALES
FROM TITLE
WHERE PRICE * YTD_SALES < ADVANCE
AND PUB_DATE < {10.1. 2003}
Определение диапазона реализации можно проводить с помощью знаков > < ,а также с помощью ключа BETWEEN (NOT BETWEEN), который проверяет, находится ли (или нет) выражение в заданном диапазоне.
<выражение> BETWEEN <нижнее значение> AND <верхнее значение>
Допустим, необходимо найти книги, количество проданных экземпляров у которых между 4095 и 12000 включительно.
SELECT TITLE_ID, YTD_SALES
FROM TITLE
WHERE YTD_SALES BETWEEN 4095 AND 12000
Запрос, в котором число проданных экземпляров меньше 4095 или больше чем 12000, запишется как
SELECT TITLE_ID, YTD_SALES
FROM TITLE
WHERE YTD_SALES NOT BETWEEN 4095 AND 1200
То же самое получается при следующем запросе:
SELECT TITLE_ID, YTD_SALES
FROM TITLE
WHERE YTD_SALES < 4095 OR YTD_SALES
Списки IN, not IN позволяют выбрать значения из заданного списка.
<выражение> IN (<выражение>, <выражение>,...)
Выбрать авторов, проживающих в г.г. Томск и Сургут.
SELECT A_LNAME, STSTE
FROM AUTORS
WHERE СYTI IN ('Томск', 'Сургут')
Найти имена авторов, которые получают меньше 50% от суммарного гонорара за книги, написанные в соавторстве. Здесь приведен пример запроса с подзапросом.
SELECT AU_LNAME, AU_FNAME
FROM AUTORS
WHERE AU_ID IN
(SELECT AU_ID FROM TITLEAUTORS
WHERE ROYALTY_PER < 0.5)
В предложении WHERE для поиска нужных подстрок удобно применять шаблоны.
<выражение> LIKE <шаблон>
В качестве части этого выражения можно использовать специальные символы % (процент) и _<(подчеркивание)>. Подчеркивание _ означает подстановку одного любого символа, а процент % - произвольную последовательность таких символов.
Например, выбрать книги, в названии которых есть слово "компьютер".
SELECT TITLE_ID, NAME, PRICE
FROM TITLE
WHERE NAME LIKE ‘%компьютер%’
5.1.2. Сортировка результатов запроса
Сортировка результатов запроса производится с использованием ключевого слова ORDER BY.
ORDER BY <колонка> [ASC/DESC][,<колонка>,...] - задает упорядочение по колонке, колонкам.
Фраза DESC вызывает упорядочение по убыванию значения соответствующей <колонки>. Фраза ASC специфицирует упорядочение по возрастанию и принимается по умолчанию.
Пример сортировки по цене (список цен идентификационных номеров издателей):
SELECT PRICE, TITLE_ID, PUB_ID
FROM TITLE
ORDER BY PRICE
Хорошо бы книги в каждой ценовой категории, выпущенные одним издательством, в списке были рядом. Для этого в список ORDER BY добавим столбец pub_id.
SELECT PRICE, TITLE_ID, PUB_ID
FROM TITLE
ORDER BY PRICE , PUB_ID
Количество уровней сортировки может быть любым. Порядок сортировки задается в предложении ОRDER BY. Порядок следования столбцов при выводе задается списком выбора. В операторе SELECT список выбора может не совпадать со списком сортировки.