alan_beaulieu-learning_sql-ru (865932), страница 14
Текст из файла (страница 14)
Поэтому все SQL выражения для работы с данны ми (кроме выражения insert) включают необязательный блок where,где размещаются всевозможные фильтры для ограничения числастрок, подвергаемых воздействию SQL выражения. Кроме того, в вы ражение select входит блок having, в который могут быть включеныусловия фильтрации, относящиеся к группам данных. В этой главеизучаются различные типы условий фильтрации, которые могут при меняться в блоках where выражений select, update и delete.Оценка условияБлок where может содержать одно или более условий, разделенных опе раторами and и or.
При использовании только оператора and строка бу дет включена в результирующий набор в случае истинности (True) всехусловий для нее. Рассмотрим следующий блок where:WHERE title = 'Teller' AND start_date < '20030101'Исходя из этих двух условий, из рассмотрения будет исключен любойсотрудник, не являющийся операционистом или работающий в банке73Оценка условияначиная с 2003 года. В данном примере используется только два усло вия, но если условия в блоке where разделены оператором and, то неза висимо от их количества строка попадет в результирующий набор,только если все условия для нее будут истинны.Если все условия в блоке where разделены оператором or, то чтобы стро ка вошла в результирующий набор, должно выполниться (принять зна чение true) хотя бы одно из них.
Рассмотрим следующие два условия:WHERE title = 'Teller' OR start_date < '20030101'Теперь есть несколько вариантов условий, по которым строка employeeможет быть включена в результирующий набор:• Сотрудник является операционистом и был принят на работу до2003 года.• Сотрудник является операционистом и был принят на работу после1 января 2003.• Сотрудник не является операционистом, но был принят на работудо 2003 года.В табл.
4.1 показаны возможные результаты вычисления блока where,содержащего два условия, разделенных оператором or.Таблица 4.1. Результаты вычисления выражений с двумя условиями,разделенными оператором orПромежуточный результатКонечный результатWHERE true OR trueTrueWHERE true OR falseTrueWHERE false OR trueTrueWHERE false OR falseFalseВ предыдущем примере единственный вариант исключения строки изрезультирующего набора – если сотрудник не является операциони стом и был принят на работу начиная с 1 января 2003 года.СкобкиЕсли блок включает три или больше условий с использованием какоператора and, так и or, следует применять круглые скобки. Это сдела ет намерения запроса понятными и для сервера БД, и для всех, кто бу дет читать код.
Вот блок where, расширяющий предыдущий пример.Он проверяет, работает ли сотрудник в банке до сих пор:WHERE end_date IS NULLAND (title = 'Teller' OR start_date < '20030101')Теперь имеем три условия. Чтобы строка попала в конечный результи рующий набор, первое условие для нее должно быть истинным (true),а также истинным должно быть второе или третье условие (или оба).74Глава 4. ФильтрацияВ табл. 4.2 показаны возможные результаты вычисления этого блокаwhere.Таблица 4.2. Результаты вычисления выражений с тремя условиями,разделенными операторами and и orПромежуточный результатКонечный результатWHERE true AND (true OR true)TrueWHERE true AND (true OR false)TrueWHERE true AND (false OR true)TrueWHERE true AND (false OR false)FalseWHERE false AND (true OR true)FalseWHERE false AND (true OR false)FalseWHERE false AND (false OR true)FalseWHERE false AND (false OR false)FalseКак видите, чем больше условий в блоке where, тем больше комбина ций должен рассмотреть сервер. В данном случае конечный результатtrue обеспечивают только три из восьми комбинаций.Оператор notНадеюсь, предыдущий пример с тремя условиями прост и понятен.Но рассмотрим следующий вариант:WHERE end_date IS NULLAND NOT (title = 'Teller' OR start_date < '20030101')Заметили отличие от предыдущего примера? После оператора and вовторой строке появился оператор not.
Теперь, вместо поиска неуволен ных сотрудников, или являющихся операционистами, или начавшихработать в банке до 2003 года, выбираются неуволенные сотрудники,которые или не являются операционистами, или начали работу в бан ке в 2003 и позже. В табл. 4.3 показаны возможные результаты выпол нения этого примера.Таблица 4.3. Результаты вычисления выражений с тремя условиями,разделенными операторами and, or и notПромежуточный результатКонечный результатWHERE true AND NOT (true OR true)FalseWHERE true AND NOT (true OR false)FalseWHERE true AND NOT (false OR true)FalseWHERE true AND NOT (false OR false)TrueWHERE false AND NOT (true OR true)FalseWHERE false AND NOT (true OR false)False75Создание условияПромежуточный результатКонечный результатWHERE false AND NOT (false OR true)FalseWHERE false AND NOT (false OR false)FalseСервер легко обработает такое выражение, а человеку оценить блок,включающий оператор not, обычно трудно.
Вот почему он использует ся нечасто. В данном случае блок where можно изменить и записать безоператора not следующим образом:WHERE end_date IS NULLAND title != 'Teller' AND start_date >= '20030101'Серверу наверняка все равно, а человеку, пожалуй, проще понять этотвариант блока where.Создание условияТеперь, посмотрев, как сервер вычисляет несколько условий, давайтевернемся назад и посмотрим на то, из чего состоит отдельное условие.Его образуют одно или более выражений, попарно объединенных од ним или более операторами.
Выражением может быть любое из сле дующего:• Число• Столбец таблицы или представления• Строковый литерал, например 'Teller'• Встроенная функция, например CONCAT('Learning', ' ', 'SQL')• Подзапрос• Список выражений, например ('Teller', 'Head Teller', 'OperationsManager')К операторам, используемым в условиях, относятся:• Операторы сравнения, такие как =, !=, <, >, <>, LIKE, IN и BETWEEN• Арифметические операторы, такие как +, , * и /В следующих разделах показано, как путем сочетания этих выраже ний и операторов можно создавать различные типы условий.Типы условийЕсть множество способов отфильтровать ненужные данные. Чтобывключить или исключить те или иные данные, можно вести поиск оп ределенных значений, наборов значений или диапазонов значений.При работе со строковыми данными можно использовать различныеметодики поиска по шаблону для выявления частичного соответствия.Следующие четыре раздела подробно описывают каждый из этих ти пов условий.76Глава 4. ФильтрацияУсловия равенстваМногие из создаваемых или существующих условий фильтрации име ют форму 'столбец = выражение':title = 'Teller'fed_id = '111111111'amount = 375.25dept_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 accountWHERE status = 'CLOSED' AND YEAR(close_date) = 1999;Это выражение включает два условия равенства: одно для выбора толь ко закрытых счетов, а другое – чтобы проверить, были ли эти счета за крыты в 1999 году.Создавая примеры выражений удаления и обновления, я попы таюсь писать каждое выражение таким образом, чтобы ни однастрока не изменялась.
Тогда при их выполнении данные оста нутся не измененными, и получаемый вами результат выраже ний select всегда будет соответствовать приведенному в книге.Поскольку сеансы MySQL по умолчанию находятся в режимеавтоматической фиксации (см. главу 12), нельзя откатить (от менить) изменения, внесенные в данные примера, если одно извыражений изменило их.
Конечно, вы можете делать с данны ми, что угодно, даже полностью очистить их и повторно запус тить предоставленные мною сценарии, но я постараюсь сохра нять их нетронутыми.Условия вхождения в диапазонКроме проверки равенства (или неравенства) одного выражения дру гому, можно создать условия, проверяющие, попадает ли выражениев определенный диапазон. Этот тип условия широко используется приработе с числовыми или временными данными. Рассмотрим следую щий запрос:mysql> SELECT emp_id, fname, lname, start_date> FROM employee> WHERE start_date < '20030101';+++++| emp_id | fname | lname| start_date |+++++|1 | Michael | Smith| 20010622 ||2 | Susan | Barker| 20020912 ||3 | Robert | Tyler| 20000209 ||4 | Susan | Hawthorne | 20020424 |78Глава 4.
Фильтрация|8 | Sarah | Parker| 20021202 ||9 | Jane| Grossman | 20020503 ||10 | Paula | Roberts | 20020727 ||11 | Thomas | Ziegler | 20001023 ||13 | John| Blake| 20000511 ||14 | Cindy | Mason| 20020809 ||16 | Theresa | Markham | 20010315 ||17 | Beth| Fowler| 20020629 ||18 | Rick| Tulman| 20021212 |+++++13 rows in set (0.01 sec)Этот запрос выявляет всех сотрудников, нанятых до 2003 года. Кромеверхней границы даты начала работы, можно задать и нижнюю гра ницу:mysql> SELECT emp_id, fname, lname, start_date> FROM employee> WHERE start_date < '20030101'> AND start_date >= '20010101';+++++| emp_id | fname | lname| start_date |+++++|1 | Michael | Smith| 20010622 ||2 | Susan | Barker| 20020912 ||4 | Susan | Hawthorne | 20020424 ||8 | Sarah | Parker| 20021202 ||9 | Jane| Grossman | 20020503 ||10 | Paula | Roberts | 20020727 ||14 | Cindy | Mason| 20020809 ||16 | Theresa | Markham | 20010315 ||17 | Beth| Fowler| 20020629 ||18 | Rick| Tulman| 20021212 |+++++10 rows in set (0.01 sec)Эта версия запроса выбирает всех сотрудников, нанятых с 2001 по2002 год.Оператор betweenЕсли имеются верхняя и нижняя границы диапазона, вместо двух раз ных условий можно использовать одно, использующее оператор between (между):mysql> SELECT emp_id, fname, lname, start_date> FROM employee> WHERE start_date BETWEEN '20010101' AND '20030101';+++++| emp_id | fname | lname| start_date |+++++|1 | Michael | Smith| 20010622 |Типы условий79|2 | Susan | Barker| 20020912 ||4 | Susan | Hawthorne | 20020424 ||8 | Sarah | Parker| 20021202 ||9 | Jane| Grossman | 20020503 ||10 | Paula | Roberts | 20020727 ||14 | Cindy | Mason| 20020809 ||16 | Theresa | Markham | 20010315 ||17 | Beth| Fowler| 20020629 ||18 | Rick| Tulman| 20021212 |+++++10 rows in set (0.05 sec)При работе с оператором between необходимо помнить пару правил.Первой (после ключевого слова between) всегда должна задаваться ниж няя граница диапазона, а потом (после and) верхняя граница.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
