alan_beaulieu-learning_sql-ru (865932), страница 48
Текст из файла (страница 48)
Лучше было бы отсортироватьстроки в убывающем порядке, указать серверу пропустить пер вые 50 строк и затем удалить оставшиеся строки:DELETE FROM login_historyORDER BY login_date DESCLIMIT 49, 9999999;Однако MySQL не обеспечивает возможности применения вто рого необязательного параметра при использовании блока limitв выражениях delete или update.С помощью блоков limit и order by можно не только удалять, но и об новлять данные. Например, если банк решает для удержания лояль ных клиентов добавить по 100 долларов на каждый из десяти самыхстарых счетов, можно сделать следующее:mysql> UPDATE account> SET avail_balance = avail_balance + 100> WHERE product_cd IN ('CHK', 'SAV', 'MM')> ORDER BY open_date ASC> LIMIT 10;Query OK, 10 rows affected (0.06 sec)Rows matched: 10 Changed: 10 Warnings: 0Это выражение сортирует счета в возрастающем порядке по дате от крытия и затем изменяет первые десять записей, которыми в данномслучае являются десять самых старых счетов.MySQLCрасширения языка SQL269Многотабличные обновления и удаленияВ определенных ситуациях для выполнения поставленной задачи мо жет понадобиться изменить или удалить данные из нескольких раз ных таблиц.
Например, если обнаруживается, что в БД банка есть фик тивный клиент, выявленный в процессе аудита системы, вероятно, по надобится удалить данные из таблиц account, customer и individual.Для этого раздела я создам набор клонов таблиц account, customer и individual, назвав их account2, customer2 и individual2. Этопозволит как защитить используемые в примере данные от из менений, так и избежать проблем с ограничениями внешнегоключа между таблицами (более подробно об этом в данном разде ле позже).
Вот выражения create table для формирования трехтаблиц клонов:CREATE TABLE individual2 ASSELECT * FROM individual;CREATE TABLE customer2 ASSELECT * FROM customer;CREATE TABLE account2 ASSELECT * FROM account;Если бы ID фиктивного клиента был равен 1, можно было бы сгенери ровать три разных выражения delete для каждой из трех таблиц:DELETE FROM account2WHERE cust_id = 1;DELETE FROM customer2WHERE cust_id = 1;DELETE FROM individual2WHERE cust_id = 1;Но в MySQL можно не писать отдельные выражения delete, а создатьодно многотабличное выражение delete, которое в данном случае вы глядит так:mysql> DELETE account2, customer2, individual2> FROM account2 INNER JOIN customer2> ON account2.cust_id = customer2.cust_id> INNER JOIN individual2> ON customer2.cust_id = individual2.cust_id> WHERE individual2.cust_id = 1;Query OK, 5 rows affected (0.02 sec)Это выражение удаляет все пять строк, по одной из таблиц individual2и customer2 и три из таблицы account2 (у клиента с ID = 1 три счета).В этом выражении три отдельных блока:deleteУказывает таблицы, строки которых предназначенны для удаления.270Приложение BfromУказывает таблицы, позволяющие идентифицировать строки, ко торые должны быть удалены.
Этот блок по форме и выполняемымфункциям аналогичен блоку from в выражении select; в блок deleteнеобязательно включать все перечисленные здесь таблицы.whereСодержит условия фильтрации, используемые для идентификациистрок, которые должны быть удалены.Многотабличное выражение delete очень похоже на выражение select,но с блоком delete вместо блока select. При удалении строк из однойтаблицы с помощью многотабличного delete разница еще менее замет на. Например, вот выражение select, выбирающее ID всех счетов, при надлежащих Джону Хейварду (John Hayward):mysql> SELECT account2.account_id> FROM account2 INNER JOIN customer2> ON account2.cust_id = customer2.cust_id> INNER JOIN individual2> ON individual2.cust_id = customer2.cust_id> WHERE individual2.fname = 'John'> AND individual2.lname = 'Hayward';++| account_id |++|8 ||9 ||10 |++3 rows in set (0.01 sec)Если просмотрев результаты, вы решите удалить из таблицы account2все три счета Джона, потребуется только заменить в предыдущем за просе блок select блоком delete с указанием таблицы account2:mysql> DELETE account2> FROM account2 INNER JOIN customer2> ON account2.cust_id = customer2.cust_id> INNER JOIN individual2> ON customer2.cust_id = individual2.cust_id> WHERE individual2.fname = 'John'> AND individual2.lname = 'Hayward';Query OK, 3 rows affected (0.01 sec)Надеюсь, это помогло лучше понять назначение блоков delete и fromв многотабличном выражении delete.
Оно функционально идентичноследующему однотабличному выражению delete, определяющему IDклиента Джона Хейварда с помощью подзапроса:DELETE FROM account2WHERE cust_id =MySQLCрасширения языка SQL271(SELECT cust_idFROM individual2WHERE fname = 'John' AND lname = 'Hayward';Применяя многотабличное выражение delete для удаления строк изодной таблицы, вы просто выбираете подобный запросу формат с со единением таблиц, а не традиционное выражение delete с подзапроса ми. Реальная мощь многотабличных выражений delete заключаетсяв возможности удаления данных из нескольких таблиц одним выра жением, как показано в первом выражении этого раздела.Кроме удаления строк из нескольких таблиц, MySQL также предостав ляет возможность изменять строки в нескольких таблицах с помощьюмноготабличного обновления (multitable update). Скажем, происходитслияние двух банков.
В базах данных обоих банков есть перекрываю щиеся ID клиентов. Руководство одного из банков решает уладить про блему путем добавления 10 000 к каждому ID клиента своего банка,чтобы можно было безопасно импортировать данные второго банка.Следующий пример показывает, как с помощью одного выражения из менить ID клиента с ID 3 в таблицах individual2, customer2 и account2:mysql> UPDATE individual2 INNER JOIN customer2> ON individual2.cust_id = customer2.cust_id> INNER JOIN account2> ON customer2.cust_id = account2.cust_id> SET individual2.cust_id = individual2.cust_id + 10000,> customer2.cust_id = customer2.cust_id + 10000,> account2.cust_id = account2.cust_id + 10000> WHERE individual2.cust_id = 3;Query OK, 4 rows affected (0.01 sec)Rows matched: 5 Changed: 4 Warnings: 0Это выражение изменяет четыре строки – по одной в таблицах individual2 и customer2 и две в таблице account2.
Синтаксис многотабличноговыражения update очень похож на синтаксис однотабличного выраже ния update, за исключением того, что в блоке update указываются не сколько таблиц и соответствующие им условия соединения, а не про сто одна таблица. Как и однотабличное выражение update, многотаб личная версия включает блок set.
Разница в том, что все упомянутыев блоке update таблицы можно изменить посредством блока set.При использовании механизма хранения InnoDB, если задейст вованные таблицы имеют ограничения внешнего ключа, приме нять многотабличные выражения delete и update скорее всего неполучится. Причина в том, что этот механизм не гарантируетпроведение изменений в порядке, не нарушающем ограниче ния. Поэтому в такой ситуации следует использовать несколькооднотабличных выражений в соответствующем порядке, такчтобы не нарушались ограничения внешнего ключа.Решения к упражнениямГлава 33.1Извлеките ID, имя и фамилию всех банковских сотрудников.
Выпол ните сортировку по фамилии и затем по имени.mysql> SELECT emp_id, fname, lname> FROM employee> ORDER BY lname, fname;++++| emp_id | fname| lname|++++|2 | Susan| Barker||13 | John| Blake||6 | Helen| Fleming ||17 | Beth| Fowler||5 | John| Gooding ||9 | Jane| Grossman ||4 | Susan| Hawthorne ||12 | Samantha | Jameson ||16 | Theresa | Markham ||14 | Cindy| Mason||8 | Sarah| Parker||15 | Frank| Portman ||10 | Paula| Roberts ||1 | Michael | Smith||7 | Chris| Tucker||18 | Rick| Tulman||3 | Robert | Tyler||11 | Thomas | Ziegler |++++18 rows in set (0.01 sec)Решения к упражнениям2733.2Извлеките ID счета, ID клиента и доступный остаток всех счетов,имеющих статус 'ACTIVE' (активный) и доступный остаток больше2500 долларов.mysql> SELECT account_id, cust_id, avail_balance> FROM account> WHERE status = 'ACTIVE'> AND avail_balance > 2500;++++| account_id | cust_id | avail_balance |++++|3 |1 |3000.00 ||10 |4 |5487.09 ||13 |6 |10000.00 ||14 |7 |5000.00 ||15 |8 |3487.19 ||18 |9 |9345.55 ||20 |10 |23575.12 ||22 |11 |9345.55 ||23 |12 |38552.05 ||24 |13 |50000.00 |++++10 rows in set (0.00 sec)3.3Напишите запрос к таблице account, возвращающий ID сотрудников, от крывших счета (используйте столбец account.open_emp_id).
Результирую щий набор должен включать по одной строке для каждого сотрудника.mysql> SELECT DISTINCT open_emp_id> FROM account;++| open_emp_id |++|1 ||10 ||13 ||16 |++4 rows in set (0.00 sec)3.4В этом запросе к нескольким наборам данных заполните пробелы (обо значенные как <число>) так, чтобы получить результат, приведенныйниже:mysql> SELECT p.product_cd, a.cust_id, a.avail_balance> FROM product p INNER JOIN account <1>274Приложение C> 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)Верные значения для <1>, <2> и <3>:1.
a2. a.product_cd3. product_type_cdГлава 44.1Какие ID транзакций возвращают следующие условия фильтрации?txn_date < '20050226' AND (txn_type_cd = 'DBT' OR amount > 100)ID транзакций 1, 2, 3, 5, 6 и 7.4.2Какие ID транзакций возвращают следующие условия фильтрации?account_id IN (101,103) AND NOT (txn_type_cd = 'DBT' OR amount > 100)ID транзакций 4 и 9.Решения к упражнениям2754.3Создайте запрос, выбирающий все счета, открытые в 2002 году.mysql> SELECT account_id, open_date> FROM account> WHERE open_date BETWEEN '20020101' AND '20021231';+++| account_id | open_date |+++|6 | 20021123 ||7 | 20021215 ||12 | 20020824 ||20 | 20020930 ||21 | 20021001 |+++5 rows in set (0.01 sec)4.4Создайте запрос, выбирающий всех клиентов физических лиц, второйбуквой фамилии которых является буква 'a' и есть 'e' в любой пози ции после 'a'.mysql> SELECT cust_id, lname, fname> FROM individual> WHERE lname LIKE '_a%e%';++++| cust_id | lname | fname |++++|1 | Hadley | James ||9 | Farley | Richard |++++2 rows in set (0.02 sec)Глава 55.1Заполните в следующем запросе пробелы (обозначенные как <число>),чтобы получить такие результаты:mysql> SELECT e.emp_id, e.fname, e.lname, b.name> FROM employee e INNER JOIN <1> b> ON e.assigned_branch_id = b.<2>;+++++| emp_id | fname| lname| name|+++++|1 | Michael | Smith| Headquarters ||2 | Susan| Barker| Headquarters ||3 | Robert | Tyler| Headquarters |276Приложение C|4 | Susan| Hawthorne | Headquarters ||5 | John| Gooding | Headquarters ||6 | Helen| Fleming | Headquarters ||7 | Chris| Tucker| Headquarters ||8 | Sarah| Parker| Headquarters ||9 | Jane| Grossman | Headquarters ||10 | Paula| Roberts | Woburn Branch ||11 | Thomas | Ziegler | Woburn Branch ||12 | Samantha | Jameson | Woburn Branch ||13 | John| Blake| Quincy Branch ||14 | Cindy| Mason| Quincy Branch ||15 | Frank| Portman | Quincy Branch ||16 | Theresa | Markham | So.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
