изучаем SQL. Она позволяет решать многошаговые задачи одним выражением

Название	Она позволяет решать многошаговые задачи одним выражением
Дата	09.02.2018
Размер	1.6 Mb.
Формат файла
Имя файла	изучаем SQL.pdf
Тип	Документы #36127
страница	7 из 31

1 2 3 4 5 6 7 8 9 10 ... 31

mysql> SELECT name
> FROM department;

Блок select
55
+
+
| name |
+
+
| Operations |
| Loans |
| Administration |
+
+
3 rows in set (0.00 sec)
Таким образом, задача блока select заключается в следующем:
Блок select определяет, какие из всех возможных столбцов должны быть включены в результирующий набор запроса.
Если бы приходилось выбирать столбцы только из таблицы или таб лиц, указанных в блоке from, было бы скучновато. Хорошо, что можно добавить остроты, включив в блок select такие вещи, как:
•
Литералы, например числа или строки
•
Выражения, например transaction.amount * 1
•
Вызовы встроенных функций, например ROUND(transaction.amount, 2)
Следующий запрос демонстрирует использование столбца таблицы, ли терала, выражения и вызова встроенной функции в одном запросе к таб лице employee:
mysql> SELECT emp_id,
> 'ACTIVE',
> emp_id * 3.14159,
> UPPER(lname)
> FROM employee;
+
+
+
+
+
| emp_id | ACTIVE | emp_id * 3.14159 | UPPER(lname) |
+
+
+
+
+
| 1 | ACTIVE | 3.14159 | SMITH |
| 2 | ACTIVE | 6.28318 | BARKER |
| 3 | ACTIVE | 9.42477 | TYLER |
| 4 | ACTIVE | 12.56636 | HAWTHORNE |
| 5 | ACTIVE | 15.70795 | GOODING |
| 6 | ACTIVE | 18.84954 | FLEMING |
| 7 | ACTIVE | 21.99113 | TUCKER |
| 8 | ACTIVE | 25.13272 | PARKER |
| 9 | ACTIVE | 28.27431 | GROSSMAN |
| 10 | ACTIVE | 31.41590 | ROBERTS |
| 11 | ACTIVE | 34.55749 | ZIEGLER |
| 12 | ACTIVE | 37.69908 | JAMESON |
| 13 | ACTIVE | 40.84067 | BLAKE |
| 14 | ACTIVE | 43.98226 | MASON |
| 15 | ACTIVE | 47.12385 | PORTMAN |
| 16 | ACTIVE | 50.26544 | MARKHAM |
| 17 | ACTIVE | 53.40703 | FOWLER |
| 18 | ACTIVE | 56.54862 | TULMAN |

56
Глава 3. Азбука запросов
+
+
+
+
+
18 rows in set (0.05 sec)
Выражения и встроенные функции будут подробно рассмотрены позже,
но я хотел дать представление о том, что может быть включено в блок select
. Если требуется только выполнить встроенную функцию или вы числить простое выражение, можно вообще обойтись без блока from.
Вот пример:
mysql> SELECT VERSION( ),
> USER( ),
> DATABASE( );
+
+
+
+
| VERSION() | USER() | DATABASE( )|
+
+
+
+
| 4.1.11 nt | lrngsql@localhost | bank |
+
+
+
+
1 row in set (0.02 sec)
Поскольку данный запрос просто вызывает три встроенные функции и не извлекает данные из таблиц, блок from здесь не нужен.
Псевдонимы столбцов
Хотя инструмент mysql и генерирует имена для столбцов, возвращае мых в результате запроса, вы можете задавать эти имена самостоя тельно. Кроме того, что при желании можно дать другое имя столбцу из таблицы (если у него «плохое» или неоднозначное имя), практиче ски наверняка вы захотите по своему назвать те столбцы результиру ющего набора, которые будут сформированы в результате выполнения выражения или встроенной функции. Сделать это можно добавлением
псевдонима столбца
после каждого элемента блока select. Вот преды дущий запрос к таблице employee, в котором для трех столбцов указаны псевдонимы:
mysql> SELECT emp_id,
> 'ACTIVE' status,
> emp_id * 3.14159 empid_x_pi,
> UPPER(lname) last_name_upper
> FROM employee;
+
+
+
+
+
| emp_id | status | empid_x_pi | last_name_upper |
+
+
+
+
+
| 1 | ACTIVE | 3.14159 | SMITH |
| 2 | ACTIVE | 6.28318 | BARKER |
| 3 | ACTIVE | 9.42477 | TYLER |
| 4 | ACTIVE | 12.56636 | HAWTHORNE |
| 5 | ACTIVE | 15.70795 | GOODING |
| 6 | ACTIVE | 18.84954 | FLEMING |
| 7 | ACTIVE | 21.99113 | TUCKER |
| 8 | ACTIVE | 25.13272 | PARKER |

Блок select
57
| 9 | ACTIVE | 28.27431 | GROSSMAN |
| 10 | ACTIVE | 31.41590 | ROBERTS |
| 11 | ACTIVE | 34.55749 | ZIEGLER |
| 12 | ACTIVE | 37.69908 | JAMESON |
| 13 | ACTIVE | 40.84067 | BLAKE |
| 14 | ACTIVE | 43.98226 | MASON |
| 15 | ACTIVE | 47.12385 | PORTMAN |
| 16 | ACTIVE | 50.26544 | MARKHAM |
| 17 | ACTIVE | 53.40703 | FOWLER |
| 18 | ACTIVE | 56.54862 | TULMAN |
+
+
+
+
+
18 rows in set (0.00 sec)
Как видно из заголовков столбцов, второй, третий и четвертый столб цы теперь имеют осмысленные имена, а не обозначены просто функци ей или выражением, сформировавшим этот столбец. Если посмотреть на блок select, можно увидеть, что псевдонимы status, empid_x_pi и last_
name_upper добавлены после второго, третьего и четвертого столбцов.
Думаю, все согласятся с тем, что с присвоенными псевдонимами столб цов выходные данные стали понятнее; кроме того, с ними легче рабо тать программно, если запросы формируются из Java или C#, а не ин терактивно посредством инструмента командной строки mysql.
Уничтожение дубликатов
В некоторых случаях запрос может возвратить дублирующие строки данных. Например, при выборе ID всех клиентов, имеющих счета, бы ло бы представлено следующее:
mysql> SELECT cust_id
> FROM account;
+
+
| cust_id |
+
+
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
| 4 |
| 5 |
| 6 |
| 6 |
| 7 |
| 8 |
| 8 |
| 9 |

58
Глава 3. Азбука запросов
| 9 |
| 9 |
| 10 |
| 10 |
| 11 |
| 12 |
| 13 |
+
+
24 rows in set (0.00 sec)
Поскольку у некоторых клиентов по нескольку счетов, один и тот же
ID клиента будет выведен столько раз, сколько счетов имеет клиент.
Но, очевидно, целью данного запроса является выбор клиентов, имею щих счета, а не получение ID клиента для каждой строки таблицы ac count
. Добиться этого можно, поместив ключевое слово distinct (от личный) непосредственно после ключевого слова select, как в следую щем примере:
mysql> SELECT DISTINCT cust_id
> FROM account;
+
+
| cust_id |
+
+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
+
+
13 rows in set (0.01 sec)
Теперь в результирующем наборе 13 строк, по одной для каждого кли ента, а не 24 строки, по одной для каждого счета.
Если не требуется, чтобы сервер удалял дублирующие данные, или вы уверены, что в результирующем наборе их не будет, вместо DISTINCT
можно указать ключевое слово ALL (все). Однако ключевое слово ALL
применяется по умолчанию и в явном указании не нуждается, поэтому большинство программистов не включает ALL в запросы.
Запомните, что формирование набора уникальных значений требует сортировки данных, что в случае больших результи рующих наборов может занять много времени. Не поддавайтесь

Блок from
59
соблазну использовать DISTINCT только для того, чтобы гаранти ровать отсутствие дублирования; лучше потратьте некоторое время на осмысление данных, с которыми работаете, чтобы уже наверняка знать, где дублирование возможно.
Блок from
До сих пор мы рассматривали запросы, в блоках from которых была указана только одна таблица. Хотя большинство книг по SQL опреде ляют блок from просто как список из одной или более таблиц, мне бы хотелось расширить это определение следующим образом:
Блок from определяет таблицы, используемые запросом, а также средства связывания таблиц.
Это определение включает две разные, но взаимосвязанные концеп ции, которые будут изучены в следующих разделах.
Таблицы
При встрече с термином table большинство людей представляют себе набор взаимосвязанных строк, хранящихся в базе данных. Хотя один из типов таблиц действительно описывается именно так, мне бы хоте лось использовать это слово в более общем значении – избавиться от любого упоминания о способах хранения данных, сосредоточившись только на наборе взаимосвязанных строк. Этому свободному определе нию соответствуют три разных типа таблиц:
•
Постоянные таблицы (т. е. созданные с помощью выражения create table
)
•
Временные таблицы (т. е. строки, возвращенные подзапросом)
•
Виртуальные таблицы (представления) (т. е. созданные с помощью выражения create view)
Каждый из этих типов таблиц может быть включен в блок from запро са. На данный момент вы уже вполне освоили включение постоянных таблиц, поэтому далее кратко описаны другие типы таблиц, которые могут использоваться в блоке from.
Таблицы, формируемые подзапросом
Подзапрос (subquery) – это запрос, содержащийся в другом запросе.
Подзапросы заключаются в круглые скобки и могут располагаться в различных частях выражения select. Однако в рамках блока from под запрос выполняет функцию формирования временной таблицы, види мой для всех остальных блоков запроса и способной взаимодействовать с другими таблицами, указанными в блоке from. Вот простой пример:
mysql> SELECT e.emp_id, e.fname, e.lname
> FROM (SELECT emp_id, fname, lname, start_date, title

Блок from
61
использования. Теперь, когда представление существует, можно де лать запросы к нему:
SELECT emp_id, start_year
FROM employee_vw;
Emp_id start_year
1 2001 2 2002 3 2000 4 2002 5 2003 6 2004 7 2004 8 2002 9 2002 10 2002 11 2000 12 2003 13 2000 14 2002 15 2003 16 2001 17 2002 18 2002
Представления создаются по разным причинам, в том числе с целью скрыть столбцы от пользователей и упростить сложно устроенные БД.
MySQL до версии 5.0.1 не поддерживает представления. Однако они широко используются другими серверами БД, поэтому тот,
кто планирует работать с MySQL, должен помнить о них.
Поскольку MySQL версии 4.1.11 не включает представления,
в предыдущем запросе намеренно не показано приглашение
mysql>
и обычное форматирование результирующего набора.
Этот же прием применяется в других главах книги при описа нии возможности SQL, еще не реализованной в MySQL.
Связи таблиц
Второе отступление от определения простого блока from: если в блоке from присутствует более одной таблицы, обязательно должны быть включены и условия, используемые для связывания (link) таблиц. Это не требование MySQL или какого то другого сервера БД, а утвержден ный ANSI метод соединения нескольких таблиц, и это способ, наибо лее переносимый между серверами БД. Соединение нескольких таб лиц будет подробно рассматриваться в главах 5 и 10; здесь приведен лишь простой пример для утоления любопытства:
mysql> SELECT employee.emp_id, employee.fname,
> employee.lname, department.name dept_name

Блок where
63
SELECT e.emp_id, e.fname, e.lname,
d.name dept_name
FROM employee e INNER JOIN department d
ON e.dept_id = d.dept_id;
Если внимательнее посмотреть на блок from, видно, что таблица emp loyee получила псевдоним e, а таблица department – псевдоним d. Затем эти псевдонимы используются в блоке on при описании условия соеди нения, а также в блоке select при задании столбцов, которые должны быть включены в результирующий набор. Надеюсь, все согласятся,
что использование псевдонимов делает выражение более компактным,
не приводя к путанице (при условии разумного выбора псевдонимов).
Блок where
До сих пор запросы, приводимые в данной главе, осуществляли выбор всех строк из таблиц employee, department или account (кроме примера с ключевым словом distinct). Однако чаще всего извлекать все строки таблицы не требуется, и нужен способ, позволяющий отфильтровы вать строки, не представляющие интереса. Это работа для блока where.
Блок where – это механизм отсеивания нежелательных строк из результирующего набора.
Например, требуется извлечь из таблицы employee данные, но только для сотрудников, нанятых в качестве старших операционистов (head tellers). В следующем запросе блок where служит для извлечения толь
ко
четырех старших операционистов:
mysql> SELECT emp_id, fname, lname, start_date, title
> FROM employee
> WHERE title = 'Head Teller';
+
+
+
+
+
+
| emp_id | fname | lname | start_date | title |
+
+
+
+
+
+
| 6 | Helen | Fleming | 2004 03 17 | Head Teller |
| 10 | Paula | Roberts | 2002 07 27 | Head Teller |
| 13 | John | Blake | 2000 05 11 | Head Teller |
| 16 | Theresa | Markham | 2001 03 15 | Head Teller |
+
+
+
+
+
+
4 rows in set (0.00 sec)
В данном случае блоком where были отсеяны 14 из 18 строк. Этот блок where содержит всего одно условие фильтрации (filter condition), но этих условий может быть столько, сколько потребуется. Условия разделяют ся с помощью таких операторов, как and, or и not (подробно блок where и условия фильтрации обсуждаются в главе 4). Вот расширенный вари ант предыдущего запроса со вторым условием – должны быть включе ны только сотрудники, принятые на работу после 1 января 2002 года:

Блоки group by и having

1 2 3 4 5 6 7 8 9 10 ... 31