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», иначе – «эконом». Упорядочить по убыванию процента наценки.

 


 

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

34089. Основания прекращения прав на земельные участки 38.5 KB
  Основания добровольного прекращения: ликвидация юридического лица; смерть гражданина и отсутствие соответствующих наследников; отчуждение земельного участка другим лицам в порядке установленном гражданским законодательством; добровольный отказ от прав на земельный участок. Процедура различна для собственников и лиц не являющихся собственниками Собственник земельного участка может отказаться от своего права путем подачи заявления в орган осуществляющий государственную регистрацию прав на недвижимое имущество и сделок с ним....
34090. Сделки с земельными участками: понятие и виды сделок 53.5 KB
  Особенности оборота земельных участков находят отражение в содержании соответствующих сделок с землей. Так гражданскоправовой институт наследования имущества применяется и в земельном праве однако в целях предотвращения дробления наследуемого земельного участка между многими наследниками земельным законодательством могут быть установлены особые правила наследования земельных участков. Земельное законодательство предусматривает особенности совершения например сделок куплипродажи земельных участков обусловленные необходимостью обеспечения...
34091. Договор купли-продажи земельных участков 38 KB
  По договору куплипродажи земельного участка продавец обязуется передать в собственность покупателя земельный участок а покупатель обязуется принять земельный участок и уплатить за него определенную денежную сумму.Стороны договора не вправе изменять целевое назначение земельного участка являющегося предметом договора. Также не подлежат самовольному изменению такие условия использования земельного участка как охранные зоны и обременения участка. Продавец при заключении договора обязан предоставить покупателю имеющуюся у него информацию об...
34092. Залог земельных участков 37.5 KB
  Нормативной базой ипотеки является 3 главы 23 ГК РФ и ФЗ N 102ФЗ Об ипотеке залогенедвижимости от 16 июля 1998 г. далее Закон об ипотеке. 1 Закона об ипотеке по договору о залоге недвижимого имущества договору об ипотеке одна сторона залогодержатель являющийся кредитором по обязательству обеспеченному ипотекой имеет право получить удовлетворение своих денежных требований к должнику по этому обязательству из стоимости заложенного недвижимого имущества другой стороны залогодателя преимущественно перед другими...
34093. Договор аренды земельных участков 34 KB
  Договор аренды земельных участков. В основе аренды лежит договор. По договору аренды одна сторона арендодатель обязуется передать другой стороне арендатору земельный участок во временное владение или и пользование за плату. Заключается договор аренды в простой письменной форме.
34094. Договор дарения земельного участка. Наследование земельных участков 32 KB
  Договор дарения земельного участка. Право дарения земельного участка имеют собст ДАРЕНИЕ венники земли граждане и юридические лица ЗЕМЕЛЬНОГО на основании Указа Президента РФ О регули УЧАСТКА ровании земельных отношений и развитии аг рарной реформы в России п. Для обеспечения заключения договоров дарения так же как и договоров мены Комитетом РФ по земельным ресурсам и землеустройству была разработана форма договора дарения земельного участка1. Договор дарения земельного участка должен быть оформлен...
34095. Общая долевая собственность на земельные участки 63.5 KB
  Право общей долевой собственности на земельный участок 1 возникает на основе юридических фактов обусловливающих право собственности граждан и юридических лиц. Право собственности на земельные доли предметом рассмотрения в данной статье не является. Право частной собственности на землю. N 13051 О собственности в СССР но которые не были надлежаще оформлены и зарегистрированы имеют право бесплатно приобрести право собственности на указанные земельные участки в соответствии с правилами установленными ст.
34096. Общая совместная собственность на земельные участки 26.5 KB
  Общая совместная собственность на земельные участки. Общая собственность на земельные участки Существует 2 вида: долевая; общая совместная. Общая долевая собственность возникает при попадании двум и более лицам неделимого участка в случаях предусмотренных в законе или договоре. Совместная собственность возникает в следующих случаях прямо предусмотренных законами: общая собственность супругов если брачным контрактом не предусмотрено иное; возникает собственность крестьянскофермерских хозяйств если соглашением между членами...
34097. Изъятие (выкуп) земельных участков для государственных и муниципальных нужд: основания, порядок 62.5 KB
  Следует иметь в виду что предполагаемое назначение объекта должно соответствовать полномочиям органа принимающего решение об изъятии земельного участка. Например решение об изъятии земельного участка для целей связанных с защитой Государственной границы России может быть принято только органами государственной власти Российской Федерации согласно п. Данное обстоятельство по сути означает что в случае возникновения судебного спора орган принявший решение об изъятии земельного участка должен будет представить суду убедительные...