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

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

1 ... 4 5 6 7 8 9 10 11 ... 31

65
А как быть, если вам нужно задействовать в блоке where оба оператора –
and и or? Рад, что спросили. Необходимо сгруппировать условия с по мощью круглых скобок. Следующий запрос составлен так, что в ре зультирующий набор должны попасть только те сотрудники, которые являются старшими операционистами (Head Teller) и начали работать в компании позже 1 января 2002 года, или простые операционисты (Tel ler), начавшие работать после 1 января 2003 года:
mysql> SELECT emp_id, fname, lname, start_date, title
> FROM employee
> WHERE (title = 'Head Teller' AND start_date > '2002 01 01')
> OR (title = 'Teller' AND start_date > '2003 01 01');
+
+
+
+
+
+
| emp_id | fname | lname | start_date | title |
+
+
+
+
+
+
| 6 | Helen | Fleming | 2004 03 17 | Head Teller |
| 7 | Chris | Tucker | 2004 09 15 | Teller |
| 10 | Paula | Roberts | 2002 07 27 | Head Teller |
| 12 | Samantha | Jameson | 2003 01 08 | Teller |
| 15 | Frank | Portman | 2003 04 01 | Teller |
+
+
+
+
+
+
5 rows in set (0.00 sec)
Для разделения групп условий при использовании различных опера торов всегда следует применять круглые скобки, чтобы автор запроса,
сервер БД и любой специалист, который позже будет работать с этим кодом, понимали, что происходит.
Блоки group by и having
Все рассмотренные до сих пор запросы извлекали необработанные стро ки данных, не выполняя над ними никаких действий. Однако иногда вам захочется выявить в данных общие направления, для чего серверу
БД придется немного поколдовать над ними, прежде чем предоставить вам результирующий набор. Одним из средств для этого является блок group by, предназначенный для группировки данных по значениям столбцов. Например, вместо списка сотрудников и отделов, в которых они числятся, нужен список отделов с числом сотрудников, работаю щих в каждом из них. С блоком group by также можно использовать блок having, позволяющий фильтровать данные групп аналогично бло ку where, позволяющему фильтровать необработанные данные.
Я хотел лишь слегка коснуться этих двух блоков, чтобы в дальнейшем они не были неожиданностью для читателей, но они немного сложнее,
чем другие четыре блока выражения select. Поэтому прошу дождать ся главы 8, где полностью описано, как и когда использовать group by и having.

66
Глава 3. Азбука запросов
Блок order by
В общем случае строки результирующего набора запроса возвращают ся в произвольном порядке. Если требуется упорядочить результи рующий набор определенным образом, необходимо предписать серве ру сортировать результаты с помощью блока order by:
Блок order by – это механизм сортировки результирующего набора на осно ве данных столбцов, или выражений, использующих данные столбцов.
Вот, к примеру, еще один взгляд на приведенный ранее запрос к табли це account:
mysql> SELECT open_emp_id, product_cd
> FROM account;
+
+
+
| open_emp_id | product_cd |
+
+
+
| 10 | CHK |
| 10 | SAV |
| 10 | CD |
| 10 | CHK |
| 10 | SAV |
| 13 | CHK |
| 13 | MM |
| 1 | CHK |
| 1 | SAV |
| 1 | MM |
| 16 | CHK |
| 1 | CHK |
| 1 | CD |
| 10 | CD |
| 16 | CHK |
| 16 | SAV |
| 1 | CHK |
| 1 | MM |
| 1 | CD |
| 16 | CHK |
| 16 | BUS |
| 10 | BUS |
| 16 | CHK |
| 13 | SBL |
+
+
+
24 rows in set (0.00 sec)
Если требуется проанализировать данные каждого сотрудника, полез но было бы отсортировать результаты по столбцу open_emp_id. Для это го просто добавляем этот столбец в блок order by:
mysql> SELECT open_emp_id, product_cd
> FROM account
> ORDER BY open_emp_id;

Блок order by
67
+
+
+
| open_emp_id | product_cd |
+
+
+
| 1 | CHK |
| 1 | SAV |
| 1 | MM |
| 1 | CHK |
| 1 | CD |
| 1 | CHK |
| 1 | MM |
| 1 | CD |
| 10 | CHK |
| 10 | SAV |
| 10 | CD |
| 10 | CHK |
| 10 | SAV |
| 10 | CD |
| 10 | BUS |
| 13 | CHK |
| 13 | MM |
| 13 | SBL |
| 16 | CHK |
| 16 | CHK |
| 16 | SAV |
| 16 | CHK |
| 16 | BUS |
| 16 | CHK |
+
+
+
24 rows in set (0.00 sec)
Теперь легче увидеть, какие типы счетов были открыты каждым со трудником. Однако было бы гораздо лучше, если бы типы счетов для каждого отдельного сотрудника выводились в определенном порядке;
это осуществляется путем добавления в блок order by столбца product_cd после open_emp_id:
mysql> SELECT open_emp_id, product_cd
> FROM account
> ORDER BY open_emp_id, product_cd;
+
+
+
| open_emp_id | product_cd |
+
+
+
| 1 | CD |
| 1 | CD |
| 1 | CHK |
| 1 | CHK |
| 1 | CHK |
| 1 | MM |
| 1 | MM |
| 1 | SAV |
| 10 | BUS |

68
Глава 3. Азбука запросов
| 10 | CD |
| 10 | CD |
| 10 | CHK |
| 10 | CHK |
| 10 | SAV |
| 10 | SAV |
| 13 | CHK |
| 13 | MM |
| 13 | SBL |
| 16 | BUS |
| 16 | CHK |
| 16 | CHK |
| 16 | CHK |
| 16 | CHK |
| 16 | SAV |
+
+
+
24 rows in set (0.00 sec)
Теперь результирующий набор отсортирован сначала по ID сотрудни ков, а затем по типу счета. Учитывается порядок размещения столб цов в блоке order by.
Сортировка по возрастанию и убыванию
При сортировке можно задать порядок по возрастанию (ascending)
или по убыванию (descending) с помощью ключевых слов asc и desc.
По умолчанию выполняется сортировка по возрастанию, поэтому до бавлять придется только ключевое слово desc – если требуется сорти ровка по убыванию. Например, по следующему запросу выводится список всех счетов, отсортированный по доступному остатку, начиная с самого большого:
mysql> SELECT account_id, product_cd, open_date, avail_balance
> FROM account
> ORDER BY avail_balance DESC;
+
+
+
+
+
| account_id | product_cd | open_date | avail_balance |
+
+
+
+
+
| 24 | SBL | 2004 02 22 | 50000.00 |
| 23 | CHK | 2003 07 30 | 38552.05 |
| 20 | CHK | 2002 09 30 | 23575.12 |
| 13 | CD | 2004 12 28 | 10000.00 |
| 22 | BUS | 2004 03 22 | 9345.55 |
| 18 | MM | 2004 10 28 | 9345.55 |
| 10 | MM | 2004 09 30 | 5487.09 |
| 14 | CD | 2004 01 12 | 5000.00 |
| 15 | CHK | 2001 05 23 | 3487.19 |
| 3 | CD | 2004 06 30 | 3000.00 |
| 4 | CHK | 2001 03 12 | 2258.02 |
| 11 | CHK | 2004 01 27 | 2237.97 |
| 7 | MM | 2002 12 15 | 2212.50 |
| 19 | CD | 2004 06 30 | 1500.00 |

Блок order by
69
| 1 | CHK | 2000 01 15 | 1057.75 |
| 6 | CHK | 2002 11 23 | 1057.75 |
| 9 | SAV | 2000 01 15 | 767.77 |
| 8 | CHK | 2003 09 12 | 534.12 |
| 2 | SAV | 2000 01 15 | 500.00 |
| 16 | SAV | 2001 05 23 | 387.99 |
| 5 | SAV | 2001 03 12 | 200.00 |
| 17 | CHK | 2003 07 30 | 125.67 |
| 12 | CHK | 2002 08 24 | 122.37 |
| 21 | BUS | 2002 10 01 | 0.00 |
+
+
+
+
+
24 rows in set (0.01 sec)
Сортировка по убыванию обычно применяется в ранжирующих запро сах вроде «покажи мне пять самых больших доступных остатков».
MySQL включает блок limit (предел), позволяющий сортировать дан ные и затем отбрасывать все, кроме первых X строк. Блок limit обсуж дается в приложении В вместе с другими расширениями, не входящи ми в стандарт ANSI.
Сортировка с помощью выражений
Сортировать результаты по данным столбца легко и приятно, но ино гда может потребоваться сортировка по какому то признаку, который не хранится в БД и, возможно, отсутствует в запросе. Чтобы справить ся с этой ситуацией, можно добавить в блок order by выражение. На пример, требуется сортировать данные клиентов по последним трем разрядам их федерального ID (это либо номер социальной страховки для физических лиц, либо корпоративный ID для юридических лиц):
mysql> SELECT cust_id, cust_type_cd, city, state, fed_id
> FROM customer
> ORDER BY RIGHT(fed_id, 3);
+
+
+
+
+
+
| cust_id | cust_type_cd | city | state | fed_id |
+
+
+
+
+
+
| 1 | I | Lynnfield | MA | 111 11 1111 |
| 10 | B | Salem | NH | 04 1111111 |
| 2 | I | Woburn | MA | 222 22 2222 |
| 11 | B | Wilmington | MA | 04 2222222 |
| 3 | I | Quincy | MA | 333 33 3333 |
| 12 | B | Salem | NH | 04 3333333 |
| 13 | B | Quincy | MA | 04 4444444 |
| 4 | I | Waltham | MA | 444 44 4444 |
| 5 | I | Salem | NH | 555 55 5555 |
| 6 | I | Waltham | MA | 666 66 6666 |
| 7 | I | Wilmington | MA | 777 77 7777 |
| 8 | I | Salem | NH | 888 88 8888 |
| 9 | I | Newton | MA | 999 99 9999 |
+
+
+
+
+
+
13 rows in set (0.01 sec)

Упражнения
71
3.2
Извлеките ID счета, ID клиента и доступный остаток всех счетов, имею щих статус 'ACTIVE' (активный) и доступный остаток более 2500 долла ров.
3.3
Напишите запрос к таблице account, возвращающий ID сотрудников, от рывших счета (используйте столбец account.open_emp_id). Результирую щий набор должен включать по одной строке для каждого сотрудника.
3.4
В этом запросе к нескольким наборам данных заполните пробелы (обо значенные как <число>) так, чтобы получить результат, приведенный ниже:
mysql> SELECT p.product_cd, a.cust_id, a.avail_balance
> FROM product p INNER JOIN account <1>
> ON p.product_cd = <2>
> WHERE p.<3> = 'ACCOUNT';
+
+
+
+
| product_cd | cust_id | avail_balance |
+
+
+
+
| CD | 1 | 3000.00 |
| CD | 6 | 10000.00 |
| CD | 7 | 5000.00 |
| CD | 9 | 1500.00 |
| CHK | 1 | 1057.75 |
| CHK | 2 | 2258.02 |
| CHK | 3 | 1057.75 |
| CHK | 4 | 534.12 |
| CHK | 5 | 2237.97 |
| CHK | 6 | 122.37 |
| CHK | 8 | 3487.19 |
| CHK | 9 | 125.67 |
| CHK | 10 | 23575.12 |
| CHK | 12 | 38552.05 |
| MM | 3 | 2212.50 |
| MM | 4 | 5487.09 |
| MM | 9 | 9345.55 |
| SAV | 1 | 500.00 |
| SAV | 2 | 200.00 |
| SAV | 4 | 767.77 |
| SAV | 8 | 387.99 |
+
+
+
+
21 rows in set (0.02 sec)

Фильтрация
Бывают случаи, когда требуется работать со всеми строками таблицы,
например:
•
Удаление всех данных таблицы для того, чтобы загрузить новые данные из другого источника.
•
Изменение всех строк таблицы после добавления нового столбца.
•
Извлечение всех строк из таблицы очереди сообщений.
В подобных случаях SQL выражениям не нужен блок where, поскольку нет необходимости исключать из рассмотрения какие либо строки.
Однако чаще всего требуется сужать фокус и работать с подмножест вом строк таблицы. Поэтому все SQL выражения для работы с данны ми (кроме выражения insert) включают необязательный блок where,
где размещаются всевозможные фильтры для ограничения числа строк, подвергаемых воздействию SQL выражения. Кроме того, в вы ражение select входит блок having, в который могут быть включены условия фильтрации, относящиеся к группам данных. В этой главе изучаются различные типы условий фильтрации, которые могут при меняться в блоках where выражений select, update и delete.
Оценка условия
Блок where может содержать одно или более условий, разделенных опе раторами and и or. При использовании только оператора and строка бу дет включена в результирующий набор в случае истинности (True) всех условий для нее. Рассмотрим следующий блок where:
WHERE title = 'Teller' AND start_date < '2003 01 01'
Исходя из этих двух условий, из рассмотрения будет исключен любой сотрудник, не являющийся операционистом или работающий в банке

Оценка условия
73
начиная с 2003 года. В данном примере используется только два усло вия, но если условия в блоке where разделены оператором and, то неза висимо от их количества строка попадет в результирующий набор,
только если все условия для нее будут истинны.
Если все условия в блоке where разделены оператором or, то чтобы стро ка вошла в результирующий набор, должно выполниться (принять зна чение true) хотя бы одно из них. Рассмотрим следующие два условия:
WHERE title = 'Teller' OR start_date < '2003 01 01'
Теперь есть несколько вариантов условий, по которым строка employee может быть включена в результирующий набор:
•
Сотрудник является операционистом и был принят на работу до
2003 года.
•
Сотрудник является операционистом и был принят на работу после
1 января 2003.
•
Сотрудник не является операционистом, но был принят на работу до 2003 года.
В табл. 4.1 показаны возможные результаты вычисления блока where,
содержащего два условия, разделенных оператором or.
Таблица 4.1. Результаты вычисления выражений с двумя условиями,
разделенными оператором or
В предыдущем примере единственный вариант исключения строки из результирующего набора – если сотрудник не является операциони стом и был принят на работу начиная с 1 января 2003 года.
Скобки
Если блок включает три или больше условий с использованием как оператора and, так и or, следует применять круглые скобки. Это сдела ет намерения запроса понятными и для сервера БД, и для всех, кто бу дет читать код. Вот блок where, расширяющий предыдущий пример.
Он проверяет, работает ли сотрудник в банке до сих пор:
WHERE end_date IS NULL
AND (title = 'Teller' OR start_date < '2003 01 01')
Теперь имеем три условия. Чтобы строка попала в конечный результи рующий набор, первое условие для нее должно быть истинным (true),
а также истинным должно быть второе или третье условие (или оба).
Промежуточный результат
Конечный результат
WHERE true OR true
True
WHERE true OR false
True
WHERE false OR true
True
WHERE false OR false
False

74
Глава 4. Фильтрация
В табл. 4.2 показаны возможные результаты вычисления этого блока where
Таблица 4.2. Результаты вычисления выражений с тремя условиями,
разделенными операторами and и or
Как видите, чем больше условий в блоке where, тем больше комбина ций должен рассмотреть сервер. В данном случае конечный результат true обеспечивают только три из восьми комбинаций.
Оператор not
Надеюсь, предыдущий пример с тремя условиями прост и понятен.
Но рассмотрим следующий вариант:
WHERE end_date IS NULL
AND NOT (title = 'Teller' OR start_date < '2003 01 01')
Заметили отличие от предыдущего примера? После оператора and во второй строке появился оператор not. Теперь, вместо поиска неуволен ных сотрудников, или являющихся операционистами, или начавших работать в банке до 2003 года, выбираются неуволенные сотрудники,
которые или не являются операционистами, или начали работу в бан ке в 2003 и позже. В табл. 4.3 показаны возможные результаты выпол нения этого примера.
Таблица 4.3. Результаты вычисления выражений с тремя условиями,
разделенными операторами and, or и not
Промежуточный результат
Конечный результат
WHERE true AND (true OR true)
True
WHERE true AND (true OR false)
True
WHERE true AND (false OR true)
True
WHERE true AND (false OR false)
False
WHERE false AND (true OR true)
False
WHERE false AND (true OR false)
False
WHERE false AND (false OR true)
False
WHERE false AND (false OR false)
False
Промежуточный результат
Конечный результат
WHERE true AND NOT (true OR true)
False
WHERE true AND NOT (true OR false)
False
WHERE true AND NOT (false OR true)
False
WHERE true AND NOT (false OR false)
True
WHERE false AND NOT (true OR true)
False
WHERE false AND NOT (true OR false)
False

Создание условия
75
Сервер легко обработает такое выражение, а человеку оценить блок,
включающий оператор not, обычно трудно. Вот почему он использует ся нечасто. В данном случае блок where можно изменить и записать без оператора not следующим образом:
WHERE end_date IS NULL
AND title != 'Teller' AND start_date >= '2003 01 01'
Серверу наверняка все равно, а человеку, пожалуй, проще понять этот вариант блока where.

1 ... 4 5 6 7 8 9 10 11 ... 31