изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
Создание условия Теперь, посмотрев, как сервер вычисляет несколько условий, давайте вернемся назад и посмотрим на то, из чего состоит отдельное условие. Его образуют одно или более выражений, попарно объединенных од ним или более операторами. Выражением может быть любое из сле дующего: • Число • Столбец таблицы или представления • Строковый литерал, например 'Teller' • Встроенная функция, например CONCAT('Learning', ' ', 'SQL') • Подзапрос • Список выражений, например ('Teller', 'Head Teller', 'Operations Manager') К операторам, используемым в условиях, относятся: • Операторы сравнения, такие как =, !=, <, >, <>, LIKE, IN и BETWEEN • Арифметические операторы, такие как +, , * и / В следующих разделах показано, как путем сочетания этих выраже ний и операторов можно создавать различные типы условий. Типы условий Есть множество способов отфильтровать ненужные данные. Чтобы включить или исключить те или иные данные, можно вести поиск оп ределенных значений, наборов значений или диапазонов значений. При работе со строковыми данными можно использовать различные методики поиска по шаблону для выявления частичного соответствия. Следующие четыре раздела подробно описывают каждый из этих ти пов условий. WHERE false AND NOT (false OR true) False WHERE false AND NOT (false OR false) False Промежуточный результат Конечный результат 76 Глава 4. Фильтрация Условия равенства Многие из создаваемых или существующих условий фильтрации име ют форму 'столбец = выражение': title = 'Teller' fed_id = '111 11 1111' amount = 375.25 dept_id = (SELECT dept_id FROM department WHERE name = 'Loans') Такие условия называются условиями равенства, потому что они про веряют равенство одного выражения другому. В первых трех приме рах столбец сравнивается с литералом (две строки и число), а в четвер том столбец сравнивается со значением, возвращаемым подзапросом. Следующий запрос использует два условия равенства, одно в блоке on (условие соединения) и второе в блоке where (условие фильтрации): mysql> SELECT pt.name product_type, p.name product > FROM product p INNER JOIN product_type pt > ON p.product_type_cd = pt.product_type_cd > WHERE pt.name = 'Customer Accounts'; + + + | product_type | product | + + + | Customer Accounts | certificate of deposit | | Customer Accounts | checking account | | Customer Accounts | money market account | | Customer Accounts | savings account | + + + 4 rows in set (0.08 sec) По этому запросу выбираются все счета, являющиеся лицевыми счета ми (customer accounts). Условия неравенства Другой достаточно распространенный тип условия – условие неравен ства , которое определяет, что два выражения не равны. Вот предыду щий запрос, в котором условием фильтрации в блоке where является условие неравенства: mysql> SELECT pt.name product_type, p.name product > FROM product p INNER JOIN product_type pt > ON p.product_type_cd = pt.product_type_cd > WHERE pt.name != 'Customer Accounts'; + + + | product_type | product | + + + | Individual and Business Loans | auto loan | | Individual and Business Loans | business line of credit | | Individual and Business Loans | home mortgage | | Individual and Business Loans | small business loan | Типы условий 77 + + + 4 rows in set (0.00 sec) В результате этого запроса выводятся все счета, не являющиеся лице выми счетами. В условиях неравенства можно использовать оператор != или <>. Изменение данных с помощью условий равенства Условия равенства/неравенства обычно используются при изменении данных. Например, в банке принято уничтожать строки старых счетов раз в год. Задача состоит в удалении из таблицы account строк с данны ми о счетах, закрытых в 1999 году. Вот одно из возможных решений: DELETE FROM account WHERE status = 'CLOSED' AND YEAR(close_date) = 1999; Это выражение включает два условия равенства: одно для выбора толь ко закрытых счетов, а другое – чтобы проверить, были ли эти счета за крыты в 1999 году. Создавая примеры выражений удаления и обновления, я попы таюсь писать каждое выражение таким образом, чтобы ни одна строка не изменялась. Тогда при их выполнении данные оста нутся не измененными, и получаемый вами результат выраже ний select всегда будет соответствовать приведенному в книге. Поскольку сеансы MySQL по умолчанию находятся в режиме автоматической фиксации (см. главу 12), нельзя откатить (от менить) изменения, внесенные в данные примера, если одно из выражений изменило их. Конечно, вы можете делать с данны ми, что угодно, даже полностью очистить их и повторно запус тить предоставленные мною сценарии, но я постараюсь сохра нять их нетронутыми. Условия вхождения в диапазон Кроме проверки равенства (или неравенства) одного выражения дру гому, можно создать условия, проверяющие, попадает ли выражение в определенный диапазон. Этот тип условия широко используется при работе с числовыми или временными данными. Рассмотрим следую щий запрос: mysql> SELECT emp_id, fname, lname, start_date > FROM employee > WHERE start_date < '2003 01 01'; + + + + + | emp_id | fname | lname | start_date | + + + + + | 1 | Michael | Smith | 2001 06 22 | | 2 | Susan | Barker | 2002 09 12 | | 3 | Robert | Tyler | 2000 02 09 | | 4 | Susan | Hawthorne | 2002 04 24 | 78 Глава 4. Фильтрация | 8 | Sarah | Parker | 2002 12 02 | | 9 | Jane | Grossman | 2002 05 03 | | 10 | Paula | Roberts | 2002 07 27 | | 11 | Thomas | Ziegler | 2000 10 23 | | 13 | John | Blake | 2000 05 11 | | 14 | Cindy | Mason | 2002 08 09 | | 16 | Theresa | Markham | 2001 03 15 | | 17 | Beth | Fowler | 2002 06 29 | | 18 | Rick | Tulman | 2002 12 12 | + + + + + 13 rows in set (0.01 sec) Этот запрос выявляет всех сотрудников, нанятых до 2003 года. Кроме верхней границы даты начала работы, можно задать и нижнюю гра ницу: mysql> SELECT emp_id, fname, lname, start_date > FROM employee > WHERE start_date < '2003 01 01' > AND start_date >= '2001 01 01'; + + + + + | emp_id | fname | lname | start_date | + + + + + | 1 | Michael | Smith | 2001 06 22 | | 2 | Susan | Barker | 2002 09 12 | | 4 | Susan | Hawthorne | 2002 04 24 | | 8 | Sarah | Parker | 2002 12 02 | | 9 | Jane | Grossman | 2002 05 03 | | 10 | Paula | Roberts | 2002 07 27 | | 14 | Cindy | Mason | 2002 08 09 | | 16 | Theresa | Markham | 2001 03 15 | | 17 | Beth | Fowler | 2002 06 29 | | 18 | Rick | Tulman | 2002 12 12 | + + + + + 10 rows in set (0.01 sec) Эта версия запроса выбирает всех сотрудников, нанятых с 2001 по 2002 год. Оператор between Если имеются верхняя и нижняя границы диапазона, вместо двух раз ных условий можно использовать одно, использующее оператор be tween (между): mysql> SELECT emp_id, fname, lname, start_date > FROM employee > WHERE start_date BETWEEN '2001 01 01' AND '2003 01 01'; + + + + + | emp_id | fname | lname | start_date | + + + + + | 1 | Michael | Smith | 2001 06 22 | Типы условий 79 | 2 | Susan | Barker | 2002 09 12 | | 4 | Susan | Hawthorne | 2002 04 24 | | 8 | Sarah | Parker | 2002 12 02 | | 9 | Jane | Grossman | 2002 05 03 | | 10 | Paula | Roberts | 2002 07 27 | | 14 | Cindy | Mason | 2002 08 09 | | 16 | Theresa | Markham | 2001 03 15 | | 17 | Beth | Fowler | 2002 06 29 | | 18 | Rick | Tulman | 2002 12 12 | + + + + + 10 rows in set (0.05 sec) При работе с оператором between необходимо помнить пару правил. Первой (после ключевого слова between) всегда должна задаваться ниж няя граница диапазона, а потом (после and) верхняя граница. Вот что происходит, если задать первой верхнюю границу: mysql> SELECT emp_id, fname, lname, start_date > FROM employee > WHERE start_date BETWEEN '2003 01 01' AND '2001 01 01'; Empty set (0.00 sec) Как видите, не возвращено никаких данных. Это объясняется тем, что фактически из одного заданного условия сервер генерирует два, ис пользуя операторы <= и >=: mysql> SELECT emp_id, fname, lname, start_date > FROM employee > WHERE start_date >= '2003 01 01' > AND start_date <= '2001 01 01'; Empty set (0.00 sec) Поскольку такая дата – одновременно позднее 1 января 2003 года и раньше 1 января 2001 года – не существует, по запросу возвращается пустой набор. Далее следует отметить второй подводный камень при использовании оператора between: необходимо помнить, что верхняя и нижняя границы включаются в диапазон. В данном случае я хотел, чтобы нижней границей была дата 2001 01 01, а верхней – 2002 12 31, а не 2003 01 01. Даже несмотря на то, что, скорее всего, никто из со трудников банка не начал работать в первый же день Нового 2003 го да, лучше задавать именно то, что требуется. Как и для дат, можно создавать условия, определяющие диапазон для чисел. Числовые диапазоны довольно просты для понимания, как вид но из следующего примера: mysql> SELECT account_id, product_cd, cust_id, avail_balance > FROM account > WHERE avail_balance BETWEEN 3000 AND 5000; + + + + + | account_id | product_cd | cust_id | avail_balance | + + + + + 80 Глава 4. Фильтрация | 3 | CD | 1 | 3000.00 | | 14 | CD | 7 | 5000.00 | | 15 | CHK | 8 | 3487.19 | + + + + + 3 rows in set (0.03 sec) Выбираются все счета, доступный остаток которых составляет от 3000 до 5000 долларов. Еще раз подчеркну, что первой задается нижняя граница. Строковые диапазоны Диапазоны дат и чисел легко представить, но можно также создавать условия для поиска диапазона строк, проиллюстрировать которые чуть сложнее. Например, требуется найти клиентов, для которых в оп ределенный диапазон попадает номер социальной страховки. Формат номера социальной страховки – 'XXX XX XXXX', где X – число от 0 до 9. Требуется найти всех клиентов, номер социальной страховки которых находится между '500 00 0000' и '999 99 9999'. Вот как может выгля деть такое выражение: mysql> SELECT cust_id, fed_id > FROM customer > WHERE cust_type_cd = 'I' > AND fed_id BETWEEN '500 00 0000' AND '999 99 9999'; + + + | cust_id | fed_id | + + + | 5 | 555 55 5555 | | 6 | 666 66 6666 | | 7 | 777 77 7777 | | 8 | 888 88 8888 | | 9 | 999 99 9999 | + + + 5 rows in set (0.01 sec) Для работы со строковыми диапазонами необходимо знать порядок символов в наборе символов (порядок, в котором сортируются симво лы в наборе символов, называется сопоставлением (collation)). Условия членства В некоторых случаях выражение ограничивается не одним значением или диапазоном значений, а конечным набором (set) значений. Напри мер, требуется выбрать все счета, кодом типа которых является 'CHK', 'SAV' , 'CD' или 'MM': mysql> SELECT account_id, product_cd, cust_id, avail_balance > FROM account > WHERE product_cd = 'CHK' OR product_cd = 'SAV' > OR product_cd = 'CD' OR product_cd = 'MM'; Типы условий 81 + + + + + | account_id | product_cd | cust_id | avail_balance | + + + + + | 1 | CHK | 1 | 1057.75 | | 2 | SAV | 1 | 500.00 | | 3 | CD | 1 | 3000.00 | | 4 | CHK | 2 | 2258.02 | | 5 | SAV | 2 | 200.00 | | 6 | CHK | 3 | 1057.75 | | 7 | MM | 3 | 2212.50 | | 8 | CHK | 4 | 534.12 | | 9 | SAV | 4 | 767.77 | | 10 | MM | 4 | 5487.09 | | 11 | CHK | 5 | 2237.97 | | 12 | CHK | 6 | 122.37 | | 13 | CD | 6 | 10000.00 | | 14 | CD | 7 | 5000.00 | | 15 | CHK | 8 | 3487.19 | | 16 | SAV | 8 | 387.99 | | 17 | CHK | 9 | 125.67 | | 18 | MM | 9 | 9345.55 | | 19 | CD | 9 | 1500.00 | | 20 | CHK | 10 | 23575.12 | | 23 | CHK | 12 | 38552.05 | + + + + + 21 rows in set (0.02 sec) На создание этого блока where (всего четыре условия, разделенных опе раторами or) ушло не слишком много сил и времени. А представьте, ес ли бы набор выражений содержал 10 или 20 элементов? В таких ситуа циях можно использовать оператор in: SELECT account_id, product_cd, cust_id, avail_balance FROM account WHERE product_cd IN ('CHK','SAV','CD','MM'); При использовании оператора in записывается единственное условие, сколько бы у вас ни было выражений. Подзапросы Можно самостоятельно создать набор выражений, например ('CHK', 'SAV','CD','MM') , но сделать это можно и с помощью подзапроса. На пример, у всех четырех типов счетов, используемых в предыдущем за просе, столбец product_type_cd имеет значение 'ACCOUNT'. В следующей версии запроса для извлечения четырех кодов типов счетов вместо яв ного указания их имен используется подзапрос к таблице product: mysql> SELECT account_id, product_cd, cust_id, avail_balance > FROM account > WHERE product_cd IN (SELECT product_cd FROM product > WHERE product_type_cd = 'ACCOUNT'); 82 Глава 4. Фильтрация + + + + + | account_id | product_cd | cust_id | avail_balance | + + + + + | 1 | CHK | 1 | 1057.75 | | 2 | SAV | 1 | 500.00 | | 3 | CD | 1 | 3000.00 | | 4 | CHK | 2 | 2258.02 | | 5 | SAV | 2 | 200.00 | | 6 | CHK | 3 | 1057.75 | | 7 | MM | 3 | 2212.50 | | 8 | CHK | 4 | 534.12 | | 9 | SAV | 4 | 767.77 | | 10 | MM | 4 | 5487.09 | | 11 | CHK | 5 | 2237.97 | | 12 | CHK | 6 | 122.37 | | 13 | CD | 6 | 10000.00 | | 14 | CD | 7 | 5000.00 | | 15 | CHK | 8 | 3487.19 | | 16 | SAV | 8 | 387.99 | | 17 | CHK | 9 | 125.67 | | 18 | MM | 9 | 9345.55 | | 19 | CD | 9 | 1500.00 | | 20 | CHK | 10 | 23575.12 | | 23 | CHK | 12 | 38552.05 | + + + + + 21 rows in set (0.03 sec) Подзапрос возвращает набор из четырех значений, а основной запрос проверяет, соответствует ли значение столбца product_cd значениям, возвращенным подзапросом. Оператор not in Иногда требуется проверить, присутствует ли определенное выраже ние в наборе выражений, а иногда нужно удостовериться в его отсут ствии . В таких ситуациях можно использовать оператор not in (нет в): mysql> SELECT account_id, product_cd, cust_id, avail_balance > FROM account > WHERE product_cd NOT IN ('CHK','SAV','CD','MM'); + + + + + | account_id | product_cd | cust_id | avail_balance | + + + + + | 21 | BUS | 10 | 0.00 | | 22 | BUS | 11 | 9345.55 | | 24 | SBL | 13 | 50000.00 | + + + + + 3 rows in set (0.02 sec) Этот запрос находит все счета, не являющиеся текущими, депозитны ми, депозитными сертификатами или депозитными счетами денежно го рынка. Типы условий 83 Условия соответствия До сих пор были рассмотрены условия, выделяющие определенную строку, диапазон строк или набор строк. Еще один тип условий касает ся частичного соответствия строк. Например, требуется найти всех со трудников, фамилия которых начинается с «T». Получить первую бук ву значения столбца lname можно с помощью встроенной функции: mysql> SELECT emp_id, fname, lname > FROM employee > WHERE LEFT(lname, 1) = 'T'; + + + + | emp_id | fname | lname | + + + + | 3 | Robert | Tyler | | 7 | Chris | Tucker | | 18 | Rick | Tulman | + + + + 3 rows in set (0.01 sec) Хотя встроенная функция left() выполняет то, что требуется, она не обеспечивает особой гибкости. Вместо нее можно в выражениях поис ка можно использовать символы маски, как показано в следующем разделе. Символы маски При поиске частичных соответствий строк интерес могут представлять: • Строки, начинающиеся/заканчивающиеся определенным символом • Строки, начинающиеся/заканчивающиеся подстрокой • Строки, содержащие определенный символ в любом месте строки • Строки, содержащие подстроку в любом месте строки • Строки определенного формата, независимо от входящих в них от дельных символов С помощью символов маски, представленных в табл. 4.4, можно по строить выражения для поиска этих и многих других частичных стро ковых соответствий. Таблица 4.4. Символы маски Символ подчеркивания замещает один символ, а символ процента мо жет замещать разное количество символов. При построении условий, использующих выражения поиска, применяется оператор like (как): mysql> SELECT lname Символ маски Соответствие _ Точно один символ % Любое число символов (в том числе ни одного) 84 Глава 4. Фильтрация > FROM employee > WHERE lname LIKE '_a%e%'; + + | lname | + + | Barker | | Hawthorne | | Parker | | Jameson | + + 4 rows in set (0.00 sec) Выражение поиска в предыдущем примере определяет строки, содер жащие «а» во второй позиции, за которым следует «е» в любом другом месте строки (включая последний символ). В табл. 4.5 показано еще несколько выражений поиска и их интерпретации. Таблица 4.5. Примеры выражений поиска Последний пример из табл. 4.5 можно использовать для поиска клиен тов, федеральный ID которых соответствует формату, используемому для номеров социальной страховки: mysql> SELECT cust_id, fed_id > FROM customer > WHERE fed_id LIKE '_ _ _ _ _ _ _ _ _'; + + + | cust_id | fed_id | + + + | 1 | 111 11 1111 | | 2 | 222 22 2222 | | 3 | 333 33 3333 | | 4 | 444 44 4444 | | 5 | 555 55 5555 | | 6 | 666 66 6666 | | 7 | 777 77 7777 | | 8 | 888 88 8888 | | 9 | 999 99 9999 | + + + 9 rows in set (0.02 sec) |