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

 


 

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

7334. Магнитное поле в вакууме. Закон Био-Савара-Лапласа 124 KB
  Тема: Магнитное поле в вакууме. Закон Био-Савара-Лапласа Магнитное поле тока. Закон Био-Савара-Лапласа. Принцип суперпозиции магнитных полей. 2. Применение закона Био-Савара-Лапласа к расчету магнитного поля: Магнитное по...
7335. Закон полного тока для магнитного поля в вакууме 85.5 KB
  Тема: Закон полного тока для магнитного поля в вакууме Вихревой характер магнитного поля. Циркуляция вектора магнитной индукции. Закон полного тока для магнитного поля в вакууме Применение закона полного тока к расчету магнитного ...
7336. Закон Ома в дифференциальной форме 91.5 KB
  Тема: Закон Ома в дифференциальной форме Постоянный электрический ток, его характеристики и условия существования. Классическая электронная теория электропроводности (КЭТ) металлов и ее опытное обоснование. Плотность тока по КЭТ. 3. Вывод за...
7337. Энергия электрического поля 73 KB
  Тема: Энергия электрического поля 1. Энергия уединенного заряженного проводника и системы заряженных проводников 2. Энергия заряженного конденсатора 3. Энергия электростатического поля. Объемная плотность энергии. 4. Пондеромоторные силы. Применение...
7338. Выявление и оценка тенденций развития финансовых процессов на предприятии 155 KB
  Введение: На современном этапе развития нашей экономики вопрос анализа финансового состояния предприятия является очень актуальным. От финансового состояния предприятия зависит во многом успех его деятельности. Поэтому анализу финансового состояния ...
7339. Теоретические основы легирования 1.15 MB
  Теоретические основы легирования Конспект лекций составлен в соответствии с программой и учебным планом по дисциплине Теоретические основы легирования и содержит материалы по основным разделам дисциплины. СОДЕРЖАНИЕ Стр. Легирующие элементы и кл...
7340. Разработка сбалансированной системы показателей 2.01 MB
  Разработка сбалансированной системы показателей Введение Настоящий документ содержит описание методических принципов и решений, используемых при построении сбалансированной системы показателей с помощью программного продукта Business Studio. Идею ис...
7341. Акустическое поле. Полная система уравнений акустического поля. Волновое уравнение 53 KB
  Акустическое поле. Полная система уравнений акустического поля. Волновое уравнение. Поле, особая форма материи физическая система, обладающая бесконечно большим числом степеней свободы. Примерами поля могут служить электромагнитное и гравитационное...
7342. Изучение микроконтроллера MC68HC908GP32, методики его программирования и отладки 397 KB
  Изучение микроконтроллера MC68HC908GP32, методики его программирования и отладки Цель работы: Изучение состава стенда, назначения отдельных узлов стенда. Изучение технических характеристик и состава микропроцессора MC68HC908...