69906

Простая выборка данных

Доклад

Информатика, кибернетика и программирование

Пусть реляционная база данных, состоящая из одной или нескольких таблиц, создана, и произведено подключение к ней. В этом случае типичной практической задачей является получение (извлечение) нужных данных. Например, может потребоваться просто просмотреть все содержимое...

Русский

2014-10-12

99 KB

0 чел.

1. Простая выборка данных

Пусть реляционная база данных, состоящая из одной или нескольких таблиц, создана, и произведено подключение к ней. В этом случае типичной практической задачей является получение (извлечение) нужных данных. Например, может потребоваться просто просмотреть все содержимое какой-либо таблицы из базы данных или некоторых ее полей. При этом, возможно, потребуются не все записи, а лишь те, которые удовлетворяют заданным условиям. Однако чаще возникает более сложная задача извлечения данных сразу из нескольких таблиц. Данные из двух и более таблиц необходимо скомпоновать в одну таблицу, чтобы представить ее для обозрения, анализа или последующей обработки. Язык SQL предоставляет для этого широкие возможности.

В результате выполнения выражения на языке SQL создается таблица (итоговый набор записей), которая либо содержит запрошенные данные, либо пуста, если данных, соответствующих запросу, не нашлось. Эта таблица, называемая результатной, существует только во время сеанса работы с базой данных и не присоединяется к числу таблиц, входящих в базу данных. Она не хранится на жестком диске компьютера подобно исходным таблицам базы данных, и поэтому ее еще называют виртуальной.

Выборка данных из нескольких таблиц, их обработка, а также использование подзапросов (запросов, которые нужны в качестве промежуточных для получения окончательного результата) относятся к сложным запросам. Под простым запросом будем подразумевать задачи выборки данных из одной таблицы при относительно простых условиях отбора, группировки и сортировки записей. Тем не менее, операторы SQL, применяемые в простых запросах на выборку данных, используются и в сложных запросах, направленных не только на получение, но и на изменение данных.

1.1. Структура оператора SELECT

Основное SQL-выражение для выборки данных имеет вид:

SELECT списокСтолбцов FROM таблица;

Такой запрос возвращает таблицу, полученную из указанной в операторе FROM путем выделения в ней только тех столбцов, которые определены в операторе SELECT. Для выделения требуемых записей (строк) исходной таблицы используется выражение, следующее за ключевым словом (оператором) WHERE. Оператор WHERE является наиболее часто используемым, хотя и необязательным в SQL-выражениях. Кроме WHERE, в SQL-выражениях используются и другие операторы, позволяющие уточнить запрос.

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

WHERE (где) – указывает записи, которые должны войти в результатную таблицу (фильтр записей);

GROUP BY (группировать по) – группирует записи по значениям определенных столбцов;

HAVING (имеющие, при условии) – указывает группы записей, которые должны войти в результатную таблицу (фильтр групп);

ORDER BY (сортировать по) – сортирует (упорядочивает) записи.

Эти операторы не являются обязательными. Их можно совсем не использовать, или использовать лишь некоторые из них, или все сразу. Если применяются несколько операторов, то в SQL-выражении они используются в указанном в списке порядке.

Таким образом, запрос данных из таблицы с применением всех перечисленных операторов уточнения запроса имеет следующий вид:

SELECT списокСтолбцов

FROM имяТаблиы

WHERE условиеПоиска

GROUP BY столбецГруппировки

HAVING условиеПоиска

ORDER BY условиеСортировки;

Порядок перечисления операторов в SQL-выражении не совпадает с порядком их выполнения. Однако знание порядка выполнения операторов поможет избежать многих недоразумений. Итак, перечисленные операторы SQL-выражения выполняются в следующем порядке, передавая друг другу результат в виде таблицы:

1. FROM – выбирает таблицу из базы данных; если указано несколько таблиц, то выполняется их декартово произведение, и результирующая таблица передается для обработки следующему оператору.

2. WHERE  – из таблицы выбираются записи, отвечающие условию поиска, и отбрасываются все остальные.

3. GROUP BY – создаются группы записей, отобранных с помощью оператора WHERE (если он присутствует в SQL-выражении); каждая группа соответствует какому-нибудь значению столбца группирования. Столбец группирования может быть любым столбцом таблицы, заданной в операторе FROM, а не только тем, который указан в SELECT.

4. HAVING – обрабатывает каждую из созданных групп записей, оставляя только те из них, которые удовлетворяют условию поиска; этот оператор используется только вместе с оператором GROUP BY.

5. SELECT – выбирает из таблицы, полученной в результате применения перечисленных операторов, только указанные столбцы.

6. ORDER BY – сортирует записи таблицы.

1.2. Форма оператора SELECT для простой выборки данных

Тривиальный запрос, возвращающий все данные (все столбцы и все записи) из одной таблицы, формулируется так:

 SELECT *

 FROM имяТаблицы;

Например, получим все сведения обо всех авторах:

SELECT *

FROM authors;

Может потребоваться выбрать не все столбцы таблицы, а только некоторые. Тогда SQL-запрос будет выглядеть так:

 SELECT списокСтолбцов

 FROM имяТаблицы;

Список столбцов – это перечень имен столбцов, разделенных запятой, как они определены в  таблице, указанной в выражении FROM. Можно указать все или только некоторые столбцы в любом порядке. Возможно также указать один и тот же столбец несколько раз.

Например, получим фамилии и имена всех авторов:

SELECT last_name, first_name

FROM authors;

Сразу за оператором SELECT до списка столбцов можно применять ключевые слова ALL (все) и DISTINCT (отличающиеся), которые указывают, какие записи представить в результатной таблице. Если эти ключевые слова не используются, то подразумевается, что следует выбрать все записи, что также соответствует применению ключевого слова ALL. В случае использования DISTINCT в результатной таблице представляются только уникальные записи. При этом если в исходной таблице находятся несколько идентичных записей, то из них выбирается только первая.

Например, получим все имена, которые встречаются среди авторов:

SELECT DISTINCT first_name

FROM authors;

Существует и еще одна форма оператора DISTINCT с явным перечислением полей, проверяемых на наличие дубликатов. В этом случае за словом DISTINCT следует секция ON (списокСтолбцов).

Например, получим имена, которые встречаются среди авторов, с указанием одной из фамилий, соответствующих каждому имени:

SELECT DISTINCT ON (first_name)

first_name, last_name

FROM authors;

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

AS заголовокСтолбца

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

Например,

SELECT last_name AS Фамилия, first_name AS Имя

FROM authors;

Псевдонимы также можно задать и для каждой таблицы после ключевого слова FROM. Для этого достаточно указать псевдоним через пробел сразу (или с использованием слова AS) после имени соответствующей таблицы. Псевдонимы таблиц, более короткие, чем их имена, удобно использовать в сложных запросах.

Например,

SELECT *

FROM authors a;

или

SELECT *

FROM authors As a;

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

Например, получим в одном столбце и фамилию, и имя автора, используя оператор конкатенации (слияния) строк:

SELECT last_name || ' ' || first_name AS name

FROM authors;

Команда SELECT также может использоваться для простого вычисления и вывода результатов выражений и констант. В этом случае она не содержит секции FROM.

Например,

SELECT 2+2 AS "2 plus 2",

   pi() AS "the pi function",

  'PostgreSQL is more than a calculator!' AS    comment;

Перечень операторов и функций, доступных в СУБД PostgreSQL, см. в приложении (П.5. Операторы, П.6. Функции).

1.3. Сортировка записей

Записи хранятся в таблицах в произвольном порядке. Более того, даже повторное выполнение запроса никоим образом не гарантирует одинакового порядка следования возвращаемых записей. Однако упорядочение данных играет важную роль при выборке, поэтому в SQL поддерживается конструкция ORDER BY, являющаяся гибким средством сортировки итогового набора.

Секции ORDER BY передается список полей, разделенный запятыми (или выражений, в которых используются поля). Переданный список задает критерий сортировки. Для каждого критерия сортировки могут дополнительно указываться ключевые слова ASC и DESC, управляющие типом сортировки.

ASC. Записи сортируются по возрастанию заданного критерия (то есть числа сортируются от меньших к большим, даты – от ранних к поздним, а текст – по алфавиту). По умолчанию выбирается именно этот способ сортировки, поэтому ASC используется лишь для наглядности.

DESC. Записи сортируются по убыванию заданного критерия (то есть числа сортируются от больших к меньшим, даты – от поздних к ранним, а текст – в порядке, обратном алфавитному).

Например, получим фамилии и имена всех авторов с сортировкой по фамилии:

SELECT last_name, first_name

FROM authors

ORDER BY last_name;

При сортировке допускается использование полей, отсутствующих в целевом списке оператора SELECT. Более того, если запрос связан с агрегированием, секция ORDER BY может содержать вызовы агрегатных функций и выражения.

Например, получим фамилии и имена всех авторов с сортировкой по идентификационному номеру:

SELECT last_name, first_name

FROM authors

ORDER BY id;

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

Например, получим фамилии и имена всех авторов с сортировкой по фамилии, а если фамилии совпадают, то и по именам:

SELECT last_name, first_name

FROM authors

ORDER BY last_name, first_name;

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

Например, получим номера изданий и даты изданий так, чтобы номера изданий были упорядочены по возрастанию, а среди них сначала шли бы те, которые изданы недавно:

SELECT edition, publication

FROM editions

ORDER BY edition ASC, publication DESC;

Секция ORDER BY обрабатывается перед удалением дубликатов ключевым словом DISTINCT. Этот факт позволяет с помощью сортировки составить запрос, аналогичный по результату использованию агрегатных функций max() или min().

Например, получим даты последних изданий для каждого номера издания:

SELECT DISTINCT ON (edition) edition, publication

FROM editions

ORDER BY edition ASC, publication DESC;

1.4. Выбор интервалов записей

Количество записей, выбираемых запросом SQL, не ограничивается. Обработка запроса, возвращающего несколько миллионов записей, займет много времени, но сервер не остановится, пока не вернет весь итоговый набор (или процесс не будет прерван извне).

В SQL предусмотрены ключевые слова LIMIT и OFFSET, упрощающие выборку заданной части итогового набора.

Секция LIMIT ограничивает максимальное количество записей в итоговом наборе (хотя размер итогового набора может быть меньше заданной величины).

Например, получим последние пять номеров isbn книг, которые были изданы:

SELECT isbn, publication

FROM editions

ORDER BY publication DESC

LIMIT 5;

При наличии секции OFFSET в итоговом наборе пропускается количество записей, заданное параметром секции.

Например, получим isbn и даты изданий всех книг, кроме последних пяти изданных:

SELECT isbn, publication

FROM editions

ORDER BY publication DESC

OFFSET 5;

Если заданы оба ключевых слова, и LIMIT, и OFFSET, то отсчет ограничения, указанного в секции LIMIT, начинается после пропуска записей в соответствии с секцией OFFSET.

Например, получим предпоследние (т.е. не учитывая самую последнюю книгу) пять номеров isbn книг, которые были изданы:

SELECT isbn, publication

FROM editions

ORDER BY publication DESC

LIMIT 5

OFFSET 1;

1.5. Условные выражения с оператором CASE

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

1.5.1. Оператор CASE со значениями

Оператор CASE со значениями имеет следующий синтаксис:

CASE проверяемое_значение

 WHEN значение1 THEN результат1

 WHEN значение2 THEN результат2

...

 WHEN значениеN THEN результатN

ELSE результатX

END

В случае, когда проверяемое_значение равно значение1, оператор CASE возвращает значение результат1, указанное после ключевого слова THEN. В противном случае проверяемое_значение сравнивается с значение2, и если они равны, то возвращается значение результат2. В противном случае проверяемое_значение сравнивается со следующим значением, указанным после ключевого слова WHEN и т.д. Если проверяемое_значение не равно ни одному из таких значений, то возвращается значение результатХ, указанное после ключевого слова ELSE.

Ключевое слово ELSE не является обязательным. Если оно отсутствует и ни одно из значений, подлежащих сравнению, не равно проверяемому значению, то оператор CASE возвращает NULL.

Например, вместо номера издания в виде числа выведем номер издания в виде текстовой строки:

SELECT isbn,

CASE edition

 WHEN 1 THEN 'first'

 WHEN 2 THEN 'second'

 WHEN 3 THEN 'third'

 ELSE 'large then 3'

END AS edition_num

FROM editions;

1.5.2. Оператор CASE с условием поиска

Вторая форма оператора CASE предполагает его использование при поиске в таблице тех записей, которые удовлетворяют определенному условию:

CASE 

 WHEN условие1 THEN результат1

 WHEN условие2 THEN результат2

...

 WHEN условиеN THEN результатN

ELSE результатX

END

Оператор CASE проверяет, истинно ли условие1 для первой записи в таблице. Если да, то CASE возвращает значение результат1. В противном случае для данной записи проверяется условие2. Если оно истинно, то возвращается значение результат2 и т.д. Если ни одно из условий не выполняется, то возвращается значение результатХ, указанное после ключевого слова ELSE.

Ключевое слово ELSE не является обязательным. Если оно отсутствует и ни одно из условий не выполняется, оператор CASE возвращает NULL. После того как оператор, содержащий CASE, выполнится для первой записи, происходит переход к следующей записи. Так продолжается до тех пор, пока не будет обработан весь набор записей.

Например, вместо номера издания в виде числа выведем номер издания в виде текстовой строки:

SELECT isbn,

CASE

 WHEN edition = 1 THEN 'first'

 WHEN edition = 2 THEN 'second'

 WHEN edition = 3 THEN 'third'

 ELSE 'large then 3'

END AS edition_num

FROM editions;


Лабораторная работа
1

Подключиться к базе данных booktown и выполнить следующие запросы:

  1.  Все сведения о книгах.
  2.  Все сведения об изданиях книг.
  3.  Код и название всех книг.
  4.  ISBN, номер издания, дата издания и тип обложки для всех изданий.
  5.  Неповторяющиеся коды издательств, выпускавших книги.
  6.  Неповторяющиеся коды тем, по которым написаны книги.
  7.  Для каждого кода ISBN получить прибыль магазина в случае продажи всех имеющихся в наличии экземпляров.
  8.  Названия всех книг, упорядоченные по алфавиту.
  9.  Фамилии и имена всех покупателей, отсортированные по алфавиту (если есть однофамильцы, то они должны быть отсортированы по именам).
  10.  Список всех ISBN с указанием розничной цены, упорядоченный от самых дорогих к самым дешевым.
  11.  Код ISBN и затраты магазина на закупку соответствующих имеющихся в наличии книг. Упорядочить по стоимости закупки (всех имеющихся экземпляров).
  12.  Код ISBN, номер издания и дата издания для всех книг в таком порядке, чтобы сначала шли книги, издававшиеся наибольшее количество раз. Книги с одним и тем же количеством изданий упорядочить по дате издания.
  13.  Код ISBN и имеющееся количество для десяти книг, в наибольшем количестве имеющихся на складе.
  14.  Все данные о последних пяти покупках.
  15.  Все данные о первых семи покупках, не считая трех самых первых (т.е.  следующие семь после первых трех).
  16.  Самая дорогая цена (розничная) книги.
  17.  Самая малая разница между ценой закупки и ценой продажи.
  18.  Код ISBN и название типа обложки («твердая» / «мягкая») для всех изданий. Упорядочить по названию типа обложки.
  19.  Код ISBN, цена закупки, цена продажи, величина наценки, процент наценки и класс всех книг. Если цена продажи более, чем на 10 % превышает цену закупки, то класс – «VIP», иначе – «эконом». Упорядочить по убыванию процента наценки.

 


 

А также другие работы, которые могут Вас заинтересовать

67695. Передатчик судовой подвижной службы, предназначенный для связи береговых служб с морскими судами и между судами 301.27 KB
  Рассчитать передатчик судовой подвижной службы, рассчитанный для связи береговых служб с морскими судами и между судами. Максимальная мощность сигнала в антенне (Pa) – 1500 Вт. Диапазон рабочих частот 0.4-5 МГц Нагрузка – несимметричный фидер с волновым сопротивлением 75 Ом.
67696. Скремблеры 195.23 KB
  Главной целью при разработке систем передачи речи является сохранение тех ее характеристик которые наиболее важны для восприятия слушателем. Безопасность связи при передаче речевых сообщений основывается на использовании большого количества различных методов закрытия сообщений меняющих...
67697. Карибська криза 1962 року 145.5 KB
  Кубинська революція відбулася незалежно від будьяких значних починань радянської політики і поступово створила в зоні американського впливу соціалістичну державу відносини з якою у США складалися чим далі тим гірше. Обидва полюси СРСР і США були непримиренними ворогами.
67698. ИЗУЧЕНИЕ КИНЕТИКИ ЭЛЕКТРОДНЫХ ПРОЦЕССОВ ПРИ ЗАЩИТЕ СТАЛИ ФОСФАТНЫМИ ПЛЁНКАМИ 127.09 KB
  Причиной возникновения и протекания процессов коррозии является термодинамическая неустойчивость материалов к определенным компонентам, находящихся в окружающей их среде. Результатом коррозии являются продукты коррозии (например, ржавчина), испорченное оборудование, разрушение конструкций.
67699. Вторая квадратичная форма. Тип точки на поверхности 1.04 MB
  Цель данной работы: изучить понятие второй квадратичной формы, кривизны на поверхности, соприкасающегося параболоида поверхности, научиться определять типы точек на поверхности. Дифференциальная геометрия изучает свойства кривых и поверхностей методами математического анализа.
67700. Проект универсальной раздвижной рамы и выбор пневмоцилиндра 663.16 KB
  В зависимости от серии используемой краски условий сушки и типа запечатываемой поверхности время высыхания трафаретной краски может составлять от нескольких минут до нескольких суток. В процессе изготовления печатной продукции оно может выполнять две функции: промежуточной подсушки цвета многокрасочного...
67701. Управление финансово-хозяйственной деятельностью ЧУП «Калинковичский молочный комбинат» 57.53 KB
  В настоящее время немногие российские организации имеют должным образом организованный менеджмент и поставленную систему бухгалтерского учёта, чтобы представляемая информация была пригодна для оперативного управления, анализа, была бы объективной, достоверной, своевременной и точной.
67702. Управление формированием операционной прибылью 1.32 MB
  Целью курсовой работы является изучение теоретических вопросов, раскрывающих понятие операционной прибыли, а также расчет показателей, связанных с данным понятием. Основными задачами является: раскрытие понятия операционной прибыли, изучение операционного левериджа, исследование способа управления формированием операционной прибыли.
67703. Формирование аристократической модели государственной службы в России в XV-XVII вв 46.54 KB
  Актуальность выбора темы определяется тем что для успешного реформирования института государственной службы в РФ понимание истории и традиций государственной службы России является не менее важным чем изучение опыта иностранных государств членов ЕС США и других развитых стран мира в сфере построения государственного управления.