Лекция № 4

Тема: «Выборка данных из одной таблицы»

 

            План

    1. Общий вид оператора выбора данных

    2. Параметры команды выбора данных

    3. Список полей в команде выбора данных

    4. Задание условий в запросах

    5. Вычисления в запросах

    6. Итоговые функции в запросах

    7. Группировка в запросах

    8. Сортировка данных

 

1. Общий вид оператора Select

По большому счету вставка данных в таблицу является лишь подготовительным этапом к их использованию. В основном же работа с данными заключается в их изменении и выборке по различным критериям. Быстрая выборка данных по различным критериям является важнейшей задачей, стоящей перед любой сис­темой управления базами данных. Если система не обеспечивает мощных и про­стых механизмов доступа к данным, то все остальные достоинства системы бу­дут сведены на нет. В принципе, данные можно хранить и в текстовом файле, но поиск нужной информации, и ее обработка потребуют очень больших усилий.

Язык SQL предоставляет пользователям гибкие средства доступа к данным. В одном запросе пользователи могут сразу обращаться к множеству разнообразных источников данных, возможно расположенных на разных серверах сети.

Для выборки данных существует команда Select, которая позволяет как делать простую выборку всех данных из одной таблицы текущей базы данных, так и выполнять сложные запросы одновременно к множеству таблиц различных баз данных.

Полный синтаксис команды Select следующий:

 

Select [параметры] список_полей

From имя_таблицы

[Where условия]

[Group by список_полей]

[Having условия]

[Order by список_полей [ASC/DESC]]

 

Как видно, синтаксис команды Select может быть очень сложный. Пожалуй, команда Select является самой сложной и многофункциональной командой. Разработчиками были приложены большие уси­лия, чтобы сделать команду Select максимально наглядной и простой в использовании. В итоге перед пользователями предстала хорошо структурированная команда, разбитая на отдельные разделы. Каждый из разделов выполняет узко­специализированную функцию и является практически независимым от других разделов. Пользователи могут указывать только те разделы, которые им действительно необходимы, и не вникать в логику работы остальных разделов.

Умение создавать правильные и эффективные запросы является важнейшим требованием к профессиональному разработчику.

 

2. Параметры команды выбора данных

С помощью параметров в команде Select можно управлять количест­вом и качеством строк, входящих в результат выборки. Параметры записываются сразу после ключевого слова Select через пробел и могут иметь общий вид

 

Select  [All / Distinct]  

 

Рассмотрим использование параметров раздела.

ALL. Это ключевое слово указывает, что в результат выборки должны быть вклю­чены все строки, возвращаемые запросом. То есть результат выборки может содержать повторяющиеся строки. Параметр ALL используется по умолчанию и его указание необязательно.

Пример. Вывести ФИО всех сотрудников предприятия.

 

Select All fam,

           imya,

           otch

From sotrudniki

 

или

 

Select fam,

       imya,

       otch

From sotrudniki

 

DISTINCT. Применение этого параметра позволяет исключить из возвращаемого резуль­тата повторяющиеся строки. Тем самым можно обеспечить уникальность ка­ждой строки, возвращаемой запросом. Значения NULL считаются эквивалент­ными и включаются в выборку один раз. Если DISTINCT не указывается, то будет использоваться параметр ALL.

Пример. Вывести все фамилии сотрудников в одном экземпляре.

 

Select Distinct fam

From sotrudniki

 

Пример. Вывести все ФИО сотрудников в одном экземпляре.

 

Select Distinct fam,

                imya,

                otch

From sotrudniki

 

3. Список полей в команде выбора данных

После задания параметров команды SELECT формируется список столбцов, ко­торые будут включены в результат выборки, а также значения для этих столбцов. В данном разделе оператора можно использовать следующие параметры:

 

Имя_столбца1 [as 'название'], Имя_столбца2 [as 'название'],. . .

 

Рассмотрим подробно назначение каждого из параметров:

*. Указание этого символа повлечет включение в результат выборки всех столбцов таблицы,  указан­ной в разделе From. Порядок перечисления столбцов в результате вы­борки соответствует физическому порядку столбцов в таблице.

Имя_столбца. Подразумевает указание имени столбца, который должен быть включен в результат выборки. Столбец должен принадлежать таблице, указанной в разделе From. Для выборки нескольких столбцов их имена указываются через запятую. Порядок следования имен столбцов не обязательно должен совпадать с физических порядком столбцов в таблице, и может быть произвольным.

[as 'название']. С помощью этого параметра можно определять названия для столбцов. Использование псевдонимов позволяет изменять имена столбцов, под которыми они будут выведены в результат выборки, по сравнению с их первоначальными именами. С помощью псевдонимов можно лишить столбцы имен или сделать их все одинаковыми. Тем не менее, обычно псевдонимы служат для формирования в результате выборки набора столбцов с разными именами или для присваивания столбцам более понятных названий, возможно на национальном языке (в этом случае название заключается в одинарные кавычки). Также псевдонимы позволяют присваивать имена столбцам, формируемым на основе вычисления выражений. По умолчанию таким столбцам не присвоено никакого имени.

Пример. Отобрать ФИО сотрудников. Поля переименовать.

 

Select fam as 'Фамилия',

       imya as 'Имя',

       otch as 'Отчество'

From sotrudniki

 

4. Задание условий в запросах

Для задания условий в операторе выбора используют раздел Where условия.

Каждое условие записывается в виде

 

Имя_поля Операция Ключ_поиска

 

Операция – это одна из операций отношения:

 

= равно;

<>, != не равно;

> больше;

>=, !< больше или равно;

< меньше;

<=, !> меньше или равно

 

Ключ поиска для текстовых полей берется в одинарные кавычки, для полей типа дата/время ключ берется в кавычки и записывается в виде 'гггг-мм-дд' (MySQL) или 'дд.мм.гггг' (SQL Server) или #дд/мм/гггг# (Microsoft Access).

Пример. Отобрать ФИО сотрудников, оклад которых больше 1500 грн.

 

Select fam,

       imya,

       otch

From sotrudniki

Where oklad > 1500

 

Пример. Отобрать адрес и должность сотрудников, фамилия которых Иванов.

 

Select adres,

       dolgnost

From sotrudniki

Where fam='Иванов'

 

Пример. Отобрать сотрудников, родившихся после июня 1970 года

 

Select *

From sotrudniki

Where datar>='01.07.1970'

 

Если по одному полю указывают несколько условий или условия задаются для нескольких полей, то их объединяют операторами AND или OR. Если оба этих оператора используются в условии, то оператор AND имеет больший приоритет.

Пример. Отобрать ФИО сотрудников, оклад которых находится между 1500 грн и 2000 грн

 

Select fam,

       imya,

       otch

From sotrudniki

Where oklad >= 1500

      and oklad <= 2000

 

Пример. Отобрать адрес и должность сотрудников, фамилия которых Иванов и имя Сергей.

 

Select adres,

       dolgnost

From sotrudniki

Where fam='Иванов'

      and imya = 'Сергей'

 

Пример. Отобрать адрес и должность сотрудников, фамилия которых Иванов или Петров, а имя Сергей.

 

Select adres,

       dolgnost

From sotrudniki

Where (fam='Иванов' or fam='Петров')

       and imya = 'Сергей'

 

Пример. Отобрать сотрудников родившихся в 1978 году

 

Select *

From sotrudniki

Where datar>='01.01.1978'

      and datar<='31.12.1978'

 

Пример. Отобрать фамилию мужчин, у которых оклад больше 2000, и женщин, у которых оклад больше 1500

 

Select fam

From sotrudniki

Where (pol = 'м' and oklad >2000)

       or (pol = 'ж' and oklad >1500)

 

Функции в условиях поиска

 

Кроме операций отношения в условиях можно использовать функции.

 

between начальное_значение and конечное_значение – проверяет на вхождение в диапазон

in (значение1, …, значение n) – проверяет на равенство одному из указанных значений.

not – проверяет на неравенство.

 

Пример. Отобрать ФИО сотрудников, оклад которых находится между 1500 грн и 2000 грн

 

Select fam,

       imya,

       otch

From sotrudniki

Where oklad between 1500 and 2000

 

Пример. Отобрать сотрудников родившихся в 1978 году

 

Select *

From sotrudniki

Where datar between '01.01.1978' and '31.12.1978'

 

Пример. Отобрать сотрудников с должностями Программист, Бухгалтер, Секретарь

 

Select *

From sotrudniki

Where dolgnost in ('Программист', 'Бухгалтер', 'Секретарь')

 

Пример. Отобрать адрес и должность сотрудников, фамилия которых Иванов или Петров, а имя Сергей.

 

Select adres,

       dolgnost

From sotrudniki

Where fam in ('Иванов', 'Петров') and imya = 'Сергей'

 

Пример. Отобрать сотрудников, которые не родились в 1978 году

 

Select *

From sotrudniki

Where datar not between '01.01.1978' and '31.12.1978'

 

Пример. Отобрать сотрудников с должностями кроме Программист, Бухгалтер, Секретарь

 

Select *

From sotrudniki

Where dolgnost not in ('Программист', 'Бухгалтер', 'Секретарь')

 

 

Поиск пустых значений  в поле

Если в поле допускается хранение значений NULL то проверить его на равенство можно c помощью записи:

 

поле is null – поле пустое;

поле is not null – поле не пустое

 

Пример. Вывести список сотрудников, у которых адрес не заполненный.

Поле может быть не заполненным, если его не заполняли вообще (null) или его заполнили, а потом значение удалили ('' - пустой символ)

 

Select *

From sotrudniki

Where adres is null or adres = ''

 

Функция Like

Данная функция позволяет выполнить поиск по текстовым полям используя символ постановки.

 

%  - любая последовательность

_(подчеркивание) - любой символ

\ - указывает, что следующий специальный символ нужно понимать как часть условия поиска

 

Пример. Вывести список сотрудников, у которых фамилия начинается на 'А'

 

Select *

From sotrudniki

Where fam like 'a%'

 

Пример. Вывести список сотрудников, у которых фамилия начинается на 'А', заканчивается на 'ко'.

 

Select *

From sotrudniki

Where fam like 'а%ko'

 

Пример. Вывести список сотрудников, у которых фамилия начинается на 'А', заканчивается на 'ко' и состоит из 6 букв

 

Select *

From sotrudniki

Where fam like 'а_ _ _ko'

 

Пример. Вывести список сотрудников, у которых фамилия начинается на 'А' или 'Р' или 'Т' и заканчивается на 'ко'.

 

Select *

From sotrudniki

Where fam like 'а%ко'

      or fam like 'р%ко' 

      or fam like 'т%ко' 

 

Пример. Вывести список сотрудников, у которых фамилия начинается на 'А' или 'Р' или 'Т' и не заканчивается на 'ко'.

 

Select *

From sotrudniki

Where (fam like 'а%'

       or fam like 'р%' 

       or fam like 'т%')

       and fam not like '%ко'

 

В данном запросе скобки ставить не обязательно, но поставим их для упрощения чтения условия отбора.

 

Пример. Вывести список сотрудников, у которых фамилия начинается на 'А', заканчивается на 'ко', состоит из 6 букв и третья буква или 'к', или 'р', или 'с'

 

Select *

From sotrudniki

Where fam like 'a_k_ko'

      or  fam like 'a_p_ko'

      or  fam like 'a_c_ko'

 

 Пример. Вывести сотрудников, у которых логин содержит символы 'a_a'. Обратите внимание, что условие поиска содержит знак подчеркивания, который является специальным символом. Чтобы этот символ понимался как часть условия, его нужно маскировать символом '\'

 

Select *

From sotrudniki

Where login like '%a\_a%'

 

5. Вычисления в запросах

При создании запроса в списке полей могут указываться формулы.  Формула может содержать знаки операций (+, -, *, /), числа, скобки и имена др. полей.

По умолчанию такое вычисляемое поле будет безымянным, поэтому для него нужно явно указывать имя.

Пример. Вывести ФИО, оклад сотрудников, и премию как 20% от оклада.

 

Select fam as 'Фамилия',

       imya as 'Имя',

       otch as 'Отчество',

       oklad as 'Оклад',

       oklad * 0.2 as 'Премия'

From sotrudniki

 

Пример. Пусть имеются данные о товаре: название и цена в у.е. Вывести название, цену в у.е., цену в гривнах, цену в гривнах с НДС (+20%). Отобразить данные тех записей, у которых цена в у.е. >100

 

Select nazv as 'Наименование товара',

       cena as 'Цена в у.е',

       cena*5.05 as 'Цена в грн',

       cena*5.05 +cena*5.05*0.2 as 'Цена в грн с Ндс'

From tovary

Where cena >100

 

Пример. Пусть имеется таблица students с информацией о студентах. Вывести на экран фамилию, имя и стипендию студентов. Стипендия рассчитывается по формуле: средний балл*100.

 

Select fam,

       imya,

       srbal*100 as stip

From students

 

Функция Case в запросах

Данная функция также используется для создания вычисляемых полей. Она позволяет организовать вывод в поле разных значений в зависимости от условий. Функция имеет вид:

 

Case When условие1  Then вывод, если выполнилось

     When условие2 Then вывод, если выполнилось

     . . .

     When условиеn Then вывод, если выполнилось

     Else вывод, если не выполнилось

End

 

Пример: Для каждого сотрудника рассчитать премию по формуле:

- 20% оклада для женщин;

- 15% оклада для мужчин.

 

Select fam,

       imya,

       case when pol= 'м' then oklad *0.15

            else oklad*0.2 end as prem

From sotrudniki

 

Пусть имеется таблица студентов со средними баллами. Для каждого студента выбрать фамилию, имя и размер стипендии. Стипендия рассчитывается по формуле:

1500 – средний балл 5;

1300 – средний балл от 4 и выше;

0 – средний балл ниже 4.

Стипендию получают только те студенты, у которых форма финансирования = "Бюджет".

 

Select fam,

       imya,

       otch,

       case when srbal=5 and finance='Бюджет' then 1500

            when srbal>=4 and finance='Бюджет' 1300

            else 0 end as stip

From students

 

6. Итоговые функции в запросах

В языке SQL имеются специальные функции позволяющие получать общие показатели по таблице:

 

count(*) – количество записей в таблице

count(поле) – количество не пустых значений (не null) в поле

sum(поле) – сумма

avg(поле) – среднее

minполе) – минимальное

max(поле) – максимальное

 

Запросы с функциями всегда возвращают одну запись, в которой каждая колонка отображает расчитанный результат.

Пример. Определить на предприятии общее число сотрудников и их ср оклад.

 

Select count(*) as 'Всего работников',

       avg(oklad) as 'Средний оклад'

From sotrudniki

 

Пример. Определить средний размер премии на предприятии у сотрудников мужчин.

 

select avg(oklad*0.2) as 'Средняя премия'

From sotrudniki

Where pol='м'

 

В списке полей запроса можно использовать текстовые константы в одинарных кавычках. Это позволяет выдавать результат в виде логически связанных фраз.

 

Select 'Общее число сотрудников: ', count (*),

       '. Их средний оклад: ', avg(oklad)

From sotrudniki

 

Внутри итоговых функций могут  использоваться сложные формулы.

 

Определить среднюю стипендию по учебному заведению (см. запрос расчета стипендии).

 

Select  avg(case when srbal=5 and finance='Бюджет' then 1500

                 when srbal>=4 and finance='Бюджет' 1300

                 else 0 end) as sredstip

From students

 

Определить суммарную стипендию по учебному заведению (см. запрос расчета стипендии).

 

Select  sum(case when srbal=5 and finance='Бюджет' then 1500

                 when srbal>=4 and finance='Бюджет' 1300

                 else 0 end) as sumstip

From students

 

7. Группировка данных

С помощью группировки можно подсчитать итоговые показатели не по всей таблице, а по отдельным группам. Группировка чаще всего используется вместе c агрегирующими функциями.

Для выполнения группировки необходимо:

 

- в списке полей запроса перечисляют те поля, по которым нужно выполнить группировку, а также нужные агрегирующие функции для расчета;

- в разделе Group by запроса указывают все поля из списка полей с соблюдением их порядка следования.

 

Пример. Рассчитать среднюю, максимальную и минимальную стипендию в каждой группе учебного заведения. Предположим, что стипендия расчитывается по формуле: ср.балл * 300, и ее получают студенты со средним баллом от 4 и выше, и обучающиеся на бюджете.

 

Select grupa,

       avg(srbal*300) as 'Ср. стипендия',

       max(srbal*300) as 'Макс. стипендия',

       min(srbal*300) as 'Мин. стипендия'

From students

Where srbal>=4

      and finance='Бюджет'

Group by grupa

 

Пример. Усовершенствуем запрос: подсчитаем эти же показатели по группам и полу в каждой группе.

 

Select grupa,

       pol,

       avg(srbal*100) as 'Ср. стипендия',

       max(srbal*120) as 'Макс. стипендия',

       min(srbal*120) as 'Мин. стипендия'

From students

Where srbal>=4

      and finance='Бюджет'

Group by grupa, pol

 

Пример. Усовершенствуем запрос. Пусть стипендия рассчитывается по формуле:

1500 – средний балл 5;

1300 – средний балл от 4 и выше;

0 – средний балл ниже 4.

Стипендию получают только те студенты, у которых форма финансирования = "Бюджет".

 

Select grupa,

       pol,

       avg(case when srbal=5 and finance='Бюджет' then 1500

                when srbal>=4 and finance='Бюджет' 1300

                else 0 end) as 'Ср. стипендия',

       max(case when srbal=5 and finance='Бюджет' then 1500

                when srbal>=4 and finance='Бюджет' 1300

                else 0 end) as 'Макс. стипендия',

       min(case when srbal=5 and finance='Бюджет' then 1500

                when srbal>=4 and finance='Бюджет' 1300

                else 0 end) as 'Мин. стипендия'

From students

Group by grupa, pol

 

Замечание. В качестве группировки могут использоваться поля, в которых есть повторяющиеся значения. В противном случае группировка не имеет смысла.

 

Отбор по группам

Если на группы или их функции накладываются ограничения, то эти ограничения задаются в специальном разделе Having условия.

 

Пример. Подсчитать количество студентов и среднюю стипендию по каждой из групп: 18, 21, 37. Пусть стипендия рассчитывается по формуле:

1500 – средний балл 5;

1300 – средний балл от 4 и выше;

0 – средний балл ниже 4.

Стипендию получают только те студенты, у которых форма финансирования = "Бюджет".

Отсортировать данные по количеству студентов в порядке убывания.

 

Select grupa,

       count(*) as kolstud,

       avg(case when srbal=5 and finance='Бюджет' then 1500

                when srbal>=4 and finance='Бюджет' 1300

                else 0 end) as sredstip

From students

Group by grupa

Having grupa like '18%'

       or grupa like '21%'

       or grupa like '37%'

Order by kolstud desc

 

В разделе Having можно использовать не только поля, по которым выполняется группировка, но и значения рассчитываемых функций.

 

Пример. Вывести список групп и количество студентов в них. Показать только те группы, в которых найденное количество больше 25.

 

Select grupa,

       count(*) as kolstud

From students

Group by grupa

Having kolstud>25

 

Если в разделе Having используется функция, то ее необязательно указывать в списке вывода запроса. В этом случае функция не будет иметь имени и ее придется записать полностью при задании условия.

 

Пример. Изменим предыдущий запрос: выведем только названия групп с количеством студентов больше 25 (само количество выводить нет будем).

 

Select grupa

From students

Group by grupa

Having count(*)>25

 

Замечания.

Поле, которое указано в разделе Group by, не может использоваться в разделе Where, но может использоваться в разделе Having.

Агрегирующие функции не могут использоваться в разделе Where, но могут использоваться в разделе Having.

 

8. Сортировка данных

Для упорядочивания данных в запросах используют параметр:

 

Order by поле1 [ASC / DESC],  поле2 [ASC / DESC], . . .

 

Допускается указывать несколько полей через запятую. Параметр ASC указывает на то, что данные в поле сортируются по возрастанию. Данный параметр срабатывает по умолчанию и его указывать не обязательно. Для сортировки по убыванию указывают параметр DESC

Пример: Отобрать ФИО сотрудников с должностью Программист и стажем работы больше 5 лет. Данные отсортировать по фамилии.

 

Select fam,

       imya,

       otch

From sotrudniki 

Where dolgnost = 'программист'

      and stag>5

Order by fam

 

Пример: Отобрать ФИО сотрудниц, родившихся в 1970 году. Отсортировать по дате рождения по убыванию и по фамилии по возрастанию.

 

Select fam,

       imya,

       otch

From sotrudniki 

Where pol='ж'

      and datar between '01.01.1970' and '31.12.1970'

Order by datar DESC, fam

 

Пример: Отобрать ФИО сотрудников и сумму их премии (20% от оклада). Отсортировать по фамилии по возрастанию и по премии по убыванию.

 

Select fam,

       imya,

       otch,

       oklad*0.2 as prem

From sotrudniki 

Order by fam, prem DESC

 

Допускается при сортировке указывать не имена полей, а их порядковые номера в списке выбора. Запишем предыдущий запрос сортировкой по номерам полей (обратите внимание, что поле fam имеет порядковый номер 1, а поле prem – номер 4).

 

Select fam,

       imya,

       otch,

       oklad*0.2 as prem

From sotrudniki 

Order by 1, 4 DESC

 

Допускается указывать псевдонимы полей, если они были происвоены в запросе.

 

Select fam as f,

       imya as i,

       otch as o,

       oklad*0.2 as prem

From sotrudniki 

Order by f, prem DESC