Анатолий Мотев СанктПетербург бхвпетербург 2006 удк 681 06 ббк 32. 973. 26018. 2 М85
 Скачать 4.25 Mb.
|
|
. Но последнюю часть шаблона мы поместили в круглые скобки и применили к этому выражению оператор + , обозначающий, что оно может повториться еще несколько раз. То есть после первого слова в названии кни- Óðîê 10. Óäàëåíèå äàííûõ 111 ги должно обязательно следовать еще одно или более слов, разделенных сим- волом пробела. Поэтому в результате запроса в таблице осталась лишь книга с названием 'Буратино' (рис. 10.12). Рис. 10.11. Удаление по шаблону '[0-9]+' Рис. 10.12. Удаление по шаблону '[а-я]+( [а-я]+)+' Полную очистку таблицы можно выполнить с помощью оператора TRUNCATE : TRUNCATE [TABLE] имя_таблицы; Он работает быстрее, нежели построчное удаление таблиц. Ключевое слово TABLE необходимо опускать в MySQL версии до 3.23.33. ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 112 Ëîãè÷åñêèå îïåðàòîðû Если вы хотите указать несколько условий при удалении записей, то необхо- димо перечислить их с помощью логического ИЛИ (OR) или объединить с помощью логического И (AND). Например: mysql> DELETE FROM address WHERE phone IS NULL OR phone NOT LIKE '+7%'; mysql> DELETE FROM book WHERE title='Война и мир' AND year_issue=1993; Первый запрос удаляет из таблицы с адресами те записи, где поле phone со- держит значение NULL или номер, который не начинается с '+7' Второй запрос удаляет записи книг с названием 'Война и мир' и годом вы- пуска 1993 ÓÐÎÊ 11 Èçìåíåíèå äàííûõ Изменение (обновление) содержимого таблиц выполняется при помощи опе- ратора UPDATE : UPDATE имя_таблицы SET имя_поля1=значение1 [, имя_поля2=значение2, ...] [WHERE условие] Параметры: имя_таблицы — имя таблицы, поля которой будут обновляться; имя_поля — обновляемое поле; значение — новое значение поля; условие — условие, описывающее записи, которые требуется обновить. Таким образом, оператор UPDATE обновляет в таблице имя_таблицы поле имя_поля , устанавливая его в значение , для тех записей, которые удовлетво- ряют условию условие Можно обновить сразу несколько полей, перечислив пары имя_поля=значение через запятую. Например, обновим какую-нибудь запись в таблице author (рис. 11.1): mysql> UPDATE author SET author='Тургенев' WHERE id_author=3; Если поле устанавливается в его текущее значение, то MySQL не станет об- новлять запись. А теперь давайте обновим несколько полей в одной таблице (рис. 11.2): mysql> UPDATE address SET address='ул. Мира, 32/15', phone='320-54-54' WHERE id_addr=2; Данный запрос изменяет значения адреса и телефона в записи с номером ад- реса, равным 2. Это, например, может понадобиться, если читатель переехал на другое место жительства. ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 114 Рис. 11.1. Выполнение запроса UPDATE Рис. 11.2. Обновление нескольких полей Можно использовать и такой синтаксис: UPDATE имя_таблицы SET имя_поля=имя_поля+1 WHERE условие; Здесь в поле помещается его текущее значение, увеличенное на единицу. UPDATE имя_таблицы SET имя_поля=имя_поля*2, имя_поля=имя_поля+1 WHERE условие; Такой запрос увеличивает значение поля имя_поля в два раза, а затем к полу- чившемуся значению прибавляется единица. Óðîê 11. Èçìåíåíèå äàííûõ 115 Если предложение WHERE не определено, то изменяются все записи таблицы. Данный оператор возвращает количество обновленных строк (см. рис. 11.2). Rows matched: 1 Changed: 1 Warnings: 0 Если в результате обновления появится дублирующее значение, оператор UPDATE завершится ошибкой. Использование ключевого слова IGNORE приво- дит к тому, что ошибка не возникнет, но и записи обновлены не будут. Можно ограничивать количество обновляемых записей: UPDATE имя_таблицы SET имя_поля=значение WHERE условие LIMIT n; Значение n в предложении LIMIT указывает, сколько записей необходимо об- новить. ÓÐÎÊ 12 Âûáîðêà äàííûõ (îïåðàòîð SELECT) Для получения записей из одной и более таблиц предназначен оператор SELECT . Его можно использовать для составления отчетов или просмотра ре- зультатов работы других операторов. В примерах предыдущих уроков этот оператор уже встречался, а теперь настало время ознакомиться с ним под- робнее. Âûáîðêà âñåõ äàííûõ Общий синтаксис оператора SELECT (мы его уже применяли): SELECT * FROM имя_таблицы; Символ '*' говорит о том, что выбираются все поля таблицы имя_таблицы Данный синтаксис очень удобен, если нужно просмотреть всю таблицу. Так можно отследить ошибки, которые вы, возможно, допустили при вводе данных. Âûáîðêà èç îïðåäåëåííûõ ïîëåé Для выборки данных из определенных полей таблицы достаточно перечис- лить имена этих полей через запятую. Например, чтобы узнать название кни- ги и год ее выпуска, введите запрос: mysql> SELECT title, year_issue FROM book; Значения полей возвращаются в том же порядке, в котором они хранятся в таблице (рис. 12.1). Поля можно перечислять в любом порядке и указывать одно поле любое количество раз. Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 117 Рис. 12.1. Выборка данных из определенных полей Èñêëþ÷åíèå äóáëèêàòîâ Обычный запрос просто выводит все данные из указанных полей. Но многие данные могут повторяться при выводе. Например, может быть довольно мно- го книг с одинаковым названием, но разных годов выпуска. Значит, при вы- борке по названию нам придется наблюдать множество повторяющихся зна- чений. Ограничить вывод можно при помощи ключевого слова DISTINCT mysql> SELECT DISTINCT title FROM book; Этот запрос будет выводить только уникальные записи (рис. 12.2). Рис. 12.2. Удаление дубликатов ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 118 Îãðàíè÷åíèå âûâîäà Для ограничения количества записей, выводимых запросом SELECT , использу- ется предложение LIMIT , принимающее один или два аргумента. Эти аргу- менты должны быть целочисленными константами. Синтаксис: SELECT имя_поля FROM имя_таблицы LIMIT число; Здесь предложение LIMIT указано с одним аргументом: число — число выво- димых записей. Например, чтобы выбрать названия только пяти первых книг, нужно ввести такой запрос (рис. 12.3): mysql> SELECT title FROM book LIMIT 5; Рис. 12.3. Ограничение вывода Также можно указать в запросе номер записи, с которой нужно начать вы- борку. Для этого следует в предложении LIMIT установить два аргумента: SELECT имя_поля FROM имя_таблицы LIMIT смещение, число; Параметры: смещение — номер записи, с которой начинается выборка, причем эта запись в выборку не включается; число — число выбираемых записей. Допусти´м также формат записи LIMIT число,-1 , позволяющий пропустить первые число записей, а затем произвести выборку всех оставшихся незави- симо от их количества. В принципе формат записи LIMIT n эквивалентен формату записи LIMIT 0,n Напишем запрос с двумя аргументами в предложении LIMIT . Следующий за- прос выбирает после второй записи три названия книги: mysql> SELECT title FROM book LIMIT 2,3; Результат выборки представлен на рис. 12.4. Если предложение LIMIT n используется с ключевыми словами ORDER BY , то MySQL закончит сортировку значений, как только найдет первые n строк, вместо того, чтобы сортировать всю таблицу. Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 119 Рис. 12.4. Использование опции LIMIT offset, count Âûáîðêà îïðåäåëåííûõ çàïèñåé Имеется возможность выбирать из таблицы определенные строки, удовле- творяющие каким-либо условиям. Синтаксис: SELECT ... WHERE Например, если понадобится выбрать книги только определенного автора, то можно выполнить следующий запрос (рис. 12.5): mysql> SELECT * FROM book WHERE id_author=1; Рис. 12.5. Выборка книг определенного автора ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 120 При создании условия для выборки можно использовать все, что было описано в уроке 10. В предложении WHERE можно применять арифметические операции, операции сравнения и логические операции. Выражения можно группировать при помощи скобок. При использовании логических операций нужно хорошо понимать разницу между логическим И и логическим ИЛИ. Например, если нужно выбрать книги, вышедшие в 2003 и 1995 году, то можно попробовать выполнить та- кой запрос: mysql> SELECT title, year_issue FROM book WHERE year_issue=2003 AND year_issue=1995; Но этот запрос будет неверным. Здесь мы на самом деле пытаемся выбрать книгу, которая вышла и в 2003, и в 1995 году, но у одной книги не может быть два года выпуска. Правильный запрос должен выглядеть так (рис. 12.6): mysql> SELECT title, year_issue FROM book WHERE year_issue=2003 OR year_issue=1995; Данный запрос выбирает названия и года издания книг, вышедших в 2003 или 1995 году. Рис. 12.6. Выборка с использованием оператора OR Поэтому будьте очень внимательны при использовании в запросах логиче- ских операций. Îïåðàòîð IN Последний запрос из предыдущего раздела можно записать и так (рис. 12.7): mysql> SELECT title, year_issue FROM book WHERE year_issue IN(2003,1995); Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 121 Рис. 12.7. Использование оператора IN Данная форма записи с оператором IN ("принадлежит") является просто крат- кой записью последовательности условий, перечисленных с помощью опера- тора OR (как в предыдущем примере). То есть два последних запроса эквива- лентны. Кроме этого, можно использовать оператор NOT IN ("не принадлежит"): mysql> SELECT title, year_issue FROM book WHERE year_issue NOT IN(2003,1995); В этом случае мы получим книги, которые выпущены в года, отличные от перечисленных в списке. Îïåðàòîð BETWEEN ... AND ... С помощью оператора BETWEEN ... AND ("входит в диапазон от ... до ...") можно выбрать записи, в которых значение какого-либо поля находится в определенном диапазоне. Рис. 12.8. Использование оператора BETWEEN ... AND .. Например, следующий запрос позволяет выбрать книги, вышедшие между 1990 и 2000 годами (рис. 12.8): ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 122 mysql> SELECT title, year_issue FROM book WHERE year_issue BETWEEN 1990 AND 2000; Также можно применять оператор NOT BETWEEN ("не входит в диапазон") (рис. 12.9). Рис. 12.9. Использование оператора NOT BETWEEN Âûáîðêà ñ óïîðÿäî÷åíèåì В процессе работы с БД записи в таблицах модифицируются, удаляются и добавляются. Поэтому довольно часто необходим упорядоченный вывод. Если требуется отсортировать выводимые данные по какому-либо полю, ис- пользуйте синтаксис: SELECT имя_поля1, имя_поля2, ... FROM имя_таблицы ORDER BY имя_поля; В отсортированных данных гораздо легче разобраться. В предложении ORDER BY вы можете указывать не только имя поля, по которому производится сор- тировка, но и порядковый номер данного поля (позицию в таблице). Нумерация позиций полей начинается с 1. Что будет, если поля содержат значения NULL ? Результаты сортировки в этом случае зависят от версии MySQL. В MySQL версии 4.0.2 значения NULL всегда располагаются в начале списка значений. В более ранних версиях они выво- дятся в начале при сортировке по возрастанию и в конце — при сортировке по убыванию. По умолчанию записи будут отсортированы по возрастанию величин в ука- занном поле. Например, сделаем выборку книг, отсортировав записи по году выпуска: mysql> SELECT * FROM book ORDER BY year_issue; или mysql> SELECT * FROM book ORDER BY 3; Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 123 Рис. 12.10. Выборка книг с упорядочением по году выпуска Данный запрос выводит все поля таблицы book , упорядочив (отсортировав) их по году выпуска (рис. 12.10). Конечно же, сортировку можно вести и по текстовым полям (по алфавиту). Например (рис. 12.11): mysql> SELECT title, year_issue FROM book ORDER BY title; Рис. 12.11. Сортировка по названию книги Можно указать в операторе ORDER BY несколько полей: mysql> SELECT title, year_issue FROM book ORDER BY title, year_issue; ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 124 Здесь мы получили сортировку названий книг по возрастанию и сортировку по возрастанию года выпуска для каждого названия (например, для книги "Евгений Онегин" выведен сначала 1985 год, а затем 2001 год) (рис. 12.12). Рис. 12.12. Сортировка по нескольким полям При сортировке полей с символьными значениями не учитывается регистр. Это значит, что порядок расположения значений, совпадающих во всем, кро- ме регистра букв, будет неопределенным. Проводить сортировку с учетом регистра можно при помощи ключевого слова BINARY : SELECT * FROM имя_таблицы ORDER BY BINARY имя_поля; Для сортировки полей в обратном порядке (по убыванию) используйте клю- чевое слово DESC . Например: mysql> SELECT title, year_issue FROM book ORDER BY title DESC; Результат выполнения этого запроса представлен на рис. 12.13. Рис. 12.13. Сортировка по убыванию значений Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 125 Можно сортировать несколько столбцов, при этом каждый из них будет от- сортирован по возрастанию или убыванию независимо от других. Например: mysql> SELECT title, year_issue FROM book ORDER BY title DESC, year_issue ASC; Здесь к полю year_issue добавлено ключевое слово ASC (сортировка поля по возрастанию значений, по умолчанию). Можно выбрать самую новую из имеющихся в библиотеке книг: mysql> SELECT title, year_issue FROM book ORDER BY year_issue DESC LIMIT 1; В случае, если книг окажется несколько, будет выбрана запись только одной из них (первая встреченная). Действие ключевого слова DESC распространяется только на поле, располо- женное непосредственно перед ним. Значения остальных полей сортируются по возрастанию. Ãðóïïèðîâêà Предложение GROUP BY служит для перекомпоновки записей таблицы по группам, в каждой из которых все записи имеют одинаковые значения в поле, указанном в предложении GROUP BY Пример использования предложения GROUP BY для вывода только уникальных названий из поля title таблицы book : mysql> SELECT title FROM book GROUP BY title; Пример использования предложения GROUP BY для вывода только уникальных названий и соответствующих им годов из полей title и year_issue табли- цы book : mysql> SELECT title, year_issue FROM book GROUP BY title; Пример использования предложения GROUP BY для вывода только уникальных годов и названий книг, вышедших в эти года, из полей title и year_issue таблицы book : mysql> SELECT title, year_issue FROM book GROUP BY year_issue; Если есть несколько книг, вышедших в каком-либо году, то выводится пер- вое найденное название, соответствующее этому году. Результаты выполнения этих запросов показаны на рис. 12.14. ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 126 Рис. 12.14. Использование предложения GROUP BY Для исключения некоторых групп из вывода используйте выражение HAVING условие . Предложение HAVING играет такую же роль для групп, как вы- ражение WHERE для строк. Это предложение включается в запрос лишь при наличии оператора GROUP BY . Оно будет обрабатываться последним, непо- средственно перед выводом. Èñïîëüçîâàíèå ôóíêöèé è îïåðàöèé ïðè âûáîðêå äàííûõ В языке SQL операторы SELECT и WHERE могут содержать различные функции и операции. Между именем функции и круглыми скобками, в которые заключены ее пара- метры, не должно быть пробелов. Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 127 В табл. 12.1 описаны не все функции и операции, присутствующие в языке SQL, а только наиболее полезные (по моему мнению) из них. Òàáëèöà 12.1. Îñíîâíûå ôóíêöèè è îïåðàöèè Функция/операция Описание +, -, *, / Арифметические операции сложения, вычитания, умножения и деления % Остаток от деления () Определяют порядок вычислений BETWEEN(A,B,C) Аналогична выражению (A>=B) AND (A<=C) BIT_COUNT() Определяет количество бит ELT(N,a,b,c) Возвращает значение a, если N==1, значение b, если N==2, и т. д. (a, b, с — строки). Например, функция ELT(2,'яблоко','груша','апельсин') возвратит значение 'груша' FIELD(S,a,b,c) Возвращает значение a, если S==a, значение b, если S==b, и т. д. (a, b, с — строки). Например, функция FIELD('яблоко', 'яблоко', 'груша', 'апельсин') возвратит значение 'яблоко' IF(A,B,C) Если выражение A истинно (не равно 0 или NULL), то возвра- щается значение B, иначе возвращается значение C IFNULL(A,B) Если выражение A не равно NULL, возвращается значение A, иначе возвращается значение B ISNULL(A) Возвращает значение 1, если A==NULL NOT, AND, OR Возвращают значение TRUE (1) или FALSE (0) SIGN() Определяет знак аргумента, возвращает значение –1, 0 или 1 =, <>, <=, <, >=, > Возвращают значение TRUE (1) или FALSE (0) LIKE выражение Возвращает значение TRUE (1) или FALSE (0) NOT LIKE выражение Возвращает значение TRUE (1) или FALSE (0) REGEXP выражение Возвращает значение TRUE (1) или FALSE (0) NOT REGEXP выражение Возвращает значение TRUE (1) или FALSE (0) Некоторые из представленных в табл. 12.1 выражений упоминались в этой книге ранее. Кроме того, в языке SQL есть ряд математических функций (табл. 12.2). ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 128 Òàáëèöà 12.2. Ìàòåìàòè÷åñêèå ôóíêöèè Функция Описание ABS(X) Возвращает абсолютное значение (модуль) числа X CEILING(X) Возвращает ближайшее целое число больше X FLOOR(X) Возвращает ближайшее целое число меньше X ROUND(X) Округляет значение X до ближайшего целого EXP(X) Возвращает значение е X (е — основание натурального логарифма) LOG() Возвращает значение натурального логарифма LOG10() Возвращает значение логарифма по основанию 10 MOD() Возвращает остаток от деления POW(X,Y) Возвращает значение X Y SQRT(X) Возвращает квадратный корень из X RAND() Возвращает случайную величину в диапазоне от 0 до 1 PI() Возвращает значение π SIN(X) Возвращает синус X (значение X задается в радианах) COS(X) Возвращает косинус X TAN(X) Возвращает тангенс X COT(X) Возвращает котангенс X RADIANS(X) Возвращает значение Х, преобразованное из градусов в радианы DEGREES(X) Возвращает значение Х, преобразованное из радианов в градусы При выборке данных можно использовать ряд строковых функций (табл. 12.3). Если строковая функция содержит в качестве аргумента строку с двоичными данными, то и результирующая строка также будет строкой с двоичными данными. При этом число, конвертированное в строку, воспри- нимается как строка с двоичными данными (это имеет значение только при выполнении операций сравнения). В табл. 12.4 описаны прочие полезные функции. Òàáëèöà 12.3. Ñòðîêîâûå ôóíêöèè Функция Описание BIN(N) Возвращает строковое представление двоичного зна- чения числа N, где N — целое число большого размера (BIGINT). Если N равно NULL, возвращается значение NULL Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 129 Òàáëèöà 12.3 (îêîí÷àíèå) Функция Описание OCT(N) Возвращает строковое представление восьмеричного значения числа N, где N — целое число большого раз- мера. Если N равно NULL, возвращается значение NULL HEX(N) Если N — число, то возвращается строковое представ- ление шестнадцатеричного числа N, где N — целое число большого размера (BIGINT). Если N — строка, то возвращается шестнадцатеричная строка N, где каждый символ в N конвертируется в 2 шестнадцатеричных числа CONCAT() Выполняет объединение строк INTERVAL(A,a,b,c,d) Возвращает значение 1, если A==a, значение 2, если A==b, и т. д. (A, a, b, c, d — строки). Если совпадений нет, возвращает значение 0 INSERT(str,pos,len,newstr) Возвращает строку str, в которой подстрока длиной len, начинающаяся с позиции pos, замещена подстро- кой newstr LCASE(S) Приводит буквы строки S к нижнему регистру UCASE(S) Приводит буквы строки S к верхнему регистру LEFT(str,len) Возвращает крайние слева len символов из строки str RIGHT(str,len) Возвращает крайние справа len символов из строки str MID(str,pos,len) Возвращает подстроку длиной len символов из строки str, начиная от позиции pos LENGTH(str) Возвращает длину строки str TRIM(str) Возвращает строку str без конечных и начальных про- белов LTRIM(str) Возвращает строку str без начальных пробелов RTRIM(str) Возвращает строку str без конечных пробелов LOCATE(A,B) Возвращает позицию подстроки В в строке А LOCATE(A,B,C) Возвращает позицию подстроки В в строке А, начиная с позиции С SUBSTRING(str,pos,len) Возвращает подстроку длиной len символов из строки str, начиная с позиции pos ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 130 Òàáëèöà 12.4. Ðàçíûå ôóíêöèè Функция Описание CURTIME() Возвращает текущее время в формате HH:MM:SS или HHMMSS. Фор- мат зависит от того, в каком контексте используется функция: в числовом — например, при выполнении запроса SELECT CURTIME()+0; получим 165651 (число); в строковом — например, при выполнении запроса SELECT CURTIME(); получим '16:56:51' (строка) CURDATE() Возвращает текущую дату в формате YYYY-MM-DD или YYYYMMDD DATABASE() Возвращает имя текущей базы данных VERSION() Возвращает строку с номером версии MySQL USER() Возвращает регистрационное имя текущего пользователя, под кото- рым пользователь подключился к БД (login) PASSWORD(str) Создает (шифрует) строку "пароля" из простого текста, заданного в аргументе str Ãðóïïîâûå ôóíêöèè Функции, представленные в табл. 12.5, можно использовать в предложении GROUP Òàáëèöà 12.5. Ãðóïïîâûå ôóíêöèè Функция Описание AVG() Возвращает среднее значение для группы SUM() Возвращает сумму элементов группы COUNT() Возвращает число элементов группы MIN() Возвращает минимальный элемент группы MAX() Возвращает максимальный элемент группы Вызов групповых функций для SQL-запросов, не содержащих предложение GROUP BY , эквивалентен выполнению этих функций над всем набором возвра- щаемых данных. Эти функции нельзя использовать в выражениях, но их ар- гументы могут быть выражениями, например: SUM(value/10) Ïðèìåðû èñïîëüçîâàíèÿ íåêîòîðûõ ôóíêöèé Функция COUNT(выражение) возвращает количество значений (отличных от NULL ) в строках, полученных при использовании оператора SELECT (рис. 12.15). mysql> SELECT COUNT(id_book) FROM book; Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 131 Рис. 12.15. Использование функции COUNT() Функция COUNT(*) возвращает количество извлеченных строк, содержащих значения NULL . Она оптимизирована для быстрого возвращения результата, при этом запрос выборки должен относиться к одной таблице и не содержать выражение WHERE mysql> SELECT COUNT(*) FROM book; При подсчете количества записей можно исключить повторяющиеся значе- ния в поле, для которого применяется функция COUNT() Например, узнаем количество записей в таблице book , исключив записи книг с одинаковыми названиями (рис. 12.16): mysql> SELECT COUNT(DISTINCT title) FROM book; Рис. 12.16. Использование синтаксиса COUNT(DISTINCT выражение) ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 132 А теперь подсчитаем количество книг, сгруппировав их по названиям (рис. 12.17): mysql> SELECT title, COUNT(id_book) FROM book GROUP BY title; Рис. 12.17. Использование функции COUNT() с предложением GROUP BY После выполнения данного запроса мы увидим количество книг с одинако- выми названиями, т. к. произвели группировку по полю title Функции MIN() и MAX() возвращают соответственно минимальное и макси- мальное значения в поле (рис. 12.18). Эти функции могут принимать в каче- стве аргумента как числовые, так и строковые величины. Рис. 12.18. Использование функций MIN() и MAX() Функция CONCAT(строка1,строка2,...) выполняет конкатенацию ("склеива- ние" или сложение) строк и возвращает результат (рис. 12.19). Если одним из Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 133 аргументов функции будет число, то оно автоматически конвертируется в эквивалентную строковую форму. Рис. 12.19. Работа функции CONCAT() В случае, когда один из аргументов имеет значение NULL , результатом выпол- нения функции будет значение NULL Функция CONCAT_WS(разделитель,строка1,строка2,...) аналогична функции CONCAT() , но не "склеивает" строки, а объединяет их посредством разделите- ля, указанного в качестве первого аргумента. Если требуется получить случайное значение, то можно воспользоваться функцией RAND() . Эта функция применяется в двух вариантах — с аргумен- том и без. Если аргумента нет, то функция возвращает значение, находящееся в промежутке между нулем и единицей, а если аргумент указан, то он ис- пользуется как начальное значение возвращаемой величины. Например, вы- полнив запрос mysql> SELECT RAND(); получим значение 0,031077887891864. Выполнив запрос mysql> SELECT RAND(); получим значение 0,004335573971584. Выполнив запрос mysql> SELECT RAND(4); получим значение 0,40613597483014. ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 134 Выполнив запрос mysql> SELECT RAND(4); получим значение 0,40613597483014. Эту функцию также можно применять для отображения случайной выборки при использовании оператора SELECT с предложением ORDER BY . Например, для получения случайного значения из поля title таблицы book можно вы- полнить запрос (рис. 12.20): mysql> SELECT title FROM book ORDER BY RAND() LIMIT 1; Рис. 12.20. Использование функции RAND Рис. 12.21. Пример работы функции REVERSE() Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 135 Если вы используете функцию RAND() в предложении WHERE , то она будет вы- числяться каждый раз заново при выполнении выражения WHERE Функция REVERSE(строка) возвращает "перевернутую" строку (рис. 12.21). Функция REPEAT(строка,число) возвращает символьную строку строка , повто- ренную столько раз, сколько задано параметром число . Например, запрос mysql> SELECT REPEAT('Привет', 3); вернет строку 'ПриветПриветПривет' Îáúåäèíåíèå äàííûõ èç íåñêîëüêèõ òàáëèö Очень часто бывает необходимо сделать выборку данных из нескольких таб- лиц сразу. Это более сложный запрос, в котором нужно четко определить, как следует связать (объединить) таблицы, чтобы получить желаемый результат. Например, если мы хотим получить в результате выборки название книги и ее автора, то необходимо задействовать таблицы book и author . Для указания выбираемого поля нужно использовать синтаксис имя_таблицы.имя_поля во избежание неоднозначности, которая может возникнуть, если в объединяе- мых таблицах есть поля с одинаковыми именами (когда мы выбирали данные из одной таблицы, неоднозначность исключалась). Для полного объединения таблиц table1 и table2 можно выполнить запрос: mysql> SELECT table1.*, table2.* FROM table1, table2; Например, произведем выборку всех полей из таблиц reader и address (рис. 12.22): mysql> SELECT reader.*, address.* FROM reader, address; Рис. 12.22. Полное объединение ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 136 Каждая строка первой таблицы объединяется с каждой строкой второй таб- лицы. Такое объединение называют также перекрестным. На рис. 12.22 вы видите все возможные комбинации строк двух таблиц. Мы получаем доволь- но большое количество записей при выводе, т. к. итог это произведение ко- личеств строк всех таблиц. В наших таблицах было всего по 3 записи. Если каждая таблица будет содержать по 100 записей, то в результате такой вы- борки получится 100 × 100 = 10 000 строк. В этом случае, чтобы уменьшить итоговую выборку, нужно указать с помощью предложения WHERE , как объ- единяемые таблицы связаны друг с другом (рис. 12.23). В итоге мы получим следующий запрос: mysql> SELECT reader.*, address.* FROM reader, address WHERE reader.id_addr=address.id_addr; Рис. 12.23. Использование предложения WHERE При задании условий отбора можно указать только определенные поля. На- пример, вывести названия книг и фамилии их авторов можно с помощью сле- дующего запроса: mysql> SELECT book.title, author.author FROM book, author WHERE book.id_author=author.id_author; После выполнения данного запроса мы увидим названия книг и фамилии их авторов (рис. 12.24). Если, например, в таблице book имеется запись о книге с номером автора (id_author), который отсутствует в таблице author (т. е. в таблицах не соблю- дена ссылочная целостность), то данная книга не отобразится в результате вы- борки. В общем случае для данного запроса не обязательно указывать перед именем выбираемого поля имя таблицы, которой оно принадлежит (рис. 12.25). Делай- Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 137 те это, только если может возникнуть неоднозначность, т. е. если в таблицах присутствуют поля с одинаковыми именами. Рис. 12.24. Выборка из таблиц book и author Рис. 12.25. Выборка из таблиц book и author без указания имен таблиц Рис. 12.26. Выборка из пяти таблиц Попробуем сделать выборку с большим количеством объединяемых таблиц (рис. 12.26). Допустим, нам нужна информация о книгах, которые находятся на руках у читателей. Выберем в БД читателя его адрес, название книги, ко- торую он взял, а также дату выдачи и дату возврата: mysql> SELECT title, reader, address, get_date, exp_date FROM book, reader, abonement, address, unit WHERE book.id_book=unit.id_book ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 138 AND unit.id_unit=abonement.id_unit AND reader.id_reader=abonement.id_reader AND reader.id_addr=address.id_addr; Как вы помните, условия, описанные в предложении WHERE , разделяются ло- гическими операторами OR или AND . В данном случае используется оператор AND , т. к. нам необходимо выполнение всех описанных условий сразу. В предложении FROM указана таблица unit , хотя из нее непосредственно ника- кие поля не выбираются. Вы должны перечислить все таблицы, которые ка- ким-то образом участвуют в запросе. В данном случае таблица unit исполь- зовалась как промежуточная таблица для связки таблицы abonement и табли- цы book , благодаря этому мы смогли по номеру экземпляра, хранящегося в таблице abonement , определить название книги, взяв его из таблицы book Èñïîëüçîâàíèå äðóãèõ îáúåäèíåíèé (JOIN) До настоящего времени для связки нескольких таблиц мы использовали объ- единение по равенству (equi-join). Нужные таблицы мы указывали в предло- жении FROM , объединив их с помощью запятой ( , ). Существуют и другие типы объединений. Объединения JOIN и CROSS JOIN эк- вивалентны оператору объединения ',' mysql> SELECT book.title, author.author FROM book JOIN author WHERE book.id_author=author.id_author; mysql> SELECT book.title, author.author FROM book CROSS JOIN author WHERE book.id_author=author.id_author; После выполнения приведенных запросов мы получим такой же результат, как на рис. 12.25. Объединение STRAIGHT_JOIN работает так же, как уже описанные объедине- ния, но с одним исключением. Здесь таблицы объединяются именно в том порядке, в каком они перечислены в предложении FROM . Ключевое слово STRAIGHT_JOIN можно указать в двух местах оператора SELECT : SELECT STRAIGHT_JOIN ... FROM табл1, табл2,... SELECT ... FROM табл1 STARIGHT_JOIN табл2 STRAIGHT_JOIN табл3 ... Итак, рассмотренные объединения позволяют выбрать строки, значения ко- торых полностью совпадают. Но есть и другие объединения. Правое и левое объединения позволяют также включить в результат выборки строки одной из таблиц, не имеющие соответствий в другой таблице. То есть при левом объединении двух таблиц будут выбраны строки, значения которых совпада- Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 139 ют в обеих таблицах и, кроме этого, будут выбраны строки левой таблицы, значения в которых не совпали со значениями в правой таблице. Таким обра- зом, левое объединение выбирает все строки из левой таблицы вне зависимо- сти от того, совпадают значения в таблицах или нет. Правое объединение ра- ботает аналогично, только таблицы меняются ролями. Рассмотрим работу объединений на примере двух произвольных таблиц с именами table1 и table2 (рис. 12.27). Рис. 12.27. Таблицы table1 и table2 Если сделать полное объединение (рис. 12.28), то мы получим только две записи, т. к. в поле id совпадают два значения: mysql> SELECT table1.*, table2.* FROM table1, table2 WHERE table1.id=table2.id; Рис. 12.28. Полное объединение Теперь попробуем сделать левое объединение этих таблиц. Мы используем объединение LEFT JOIN , а условие на соответствие значений зададим при по- мощи предложения ON (вместо WHERE ). ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 140 Рекомендуется использовать объединение LEFT JOIN вместо RIGHT JOIN для сохранения переносимости кода между различными базами данных. Здесь мы поменяем местами таблицы для удобства выборки (чтобы не ис- пользовать объединение RIGHT JOIN ): mysql> SELECT table2.*, table1.* FROM table2 LEFT JOIN table1 ON table2.id=table1.id; Рис. 12.29. Использование объединения LEFT JOIN В результате (рис. 12.29) видим, что появилась третья строка из таблицы table2 , которая не имеет совпадений в таблице table1 . Поля для правой таб- лицы устанавливаются в значение NULL С помощью левого объединения можно отслеживать те записи в левой таб- лице, которые не находят соответствия в правой. Для этого в запрос нужно добавить предложение WHERE : mysql> SELECT table2.* FROM table2 LEFT JOIN table1 ON table2.id=table1.id WHERE table1.id IS NULL; В результате мы видим одну запись из таблицы table2 (рис. 12.30). Рис. 12.30. Вывод записей с несовпадающими значениями Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 141 Предложение ON позволяет задавать соответствие между полями, имеющими как одинаковые, так и разные имена. Но есть еще один способ задания соот- ветствия между полями таблиц — с помощью предложения USING() . Оно аналогично предложению ON , но имена полей, по которым производится объ- единение, здесь должны полностью совпадать. Поскольку в нашем примере связываемые поля таблиц имеют одинаковые имена, запрос с левым объеди- нением можно написать так (рис. 12.31): mysql> SELECT table2.*, table1.* FROM table2 LEFT JOIN table1 USING(id); Рис. 12.31. Использование предложения USING() Итак, левое объединение очень удобно применять в том случае, когда требу- ется узнать, какие записи в таблицах не имеют соответствия. Обратимся к нашей учебной БД library . При помощи объединения LEFT JOIN , например, можно узнать, книги с какими инвентарными номерами есть в на- личии: mysql> SELECT unit.id_unit FROM unit LEFT JOIN abonement USING(id_unit) WHERE abonement.id_unit IS NULL; Объединение INNER JOIN делает полное объединение таблиц, связывание за- дается при помощи предложения ON . Следующие два запроса эквивалентны. mysql> SELECT table1.*, table2.* FROM table1, table2 WHERE table1.id=table2.id; mysql> SELECT table1.*, table2.* FROM table1 INNER JOIN table2 ON table1.id=table2.id; Èñïîëüçîâàíèå âëîæåííûõ çàïðîñîâ Вложенный запрос (подзапрос) — это запрос, заключенный в круглые скобки и вложенный в предложение WHERE ( HAVING ) оператора SELECT или других опе- раторов, использующих предложение WHERE . Вложенный запрос также может содержать в своем предложении WHERE ( HAVING ) другой вложенный запрос ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 142 и т. д. Нетрудно догадаться, что вложенный запрос служит для того, чтобы при выборке записей таблицы, сформированной основным запросом, можно было использовать данные из других таблиц. Эта возможность появилась в MySQL версии 4.1. В предыдущей версии СУБД (4.0) некоторые вложенные выборки можно было записать как объединения. Об этом будет рассказано чуть позже. Есть несколько способов создания вложенных запросов. Вложенные запросы включаются в предложение WHERE ( HAVING ) с помощью условий IN , EXISTS или одного из условий сравнения ( = , <> , < , <= , > или >= ). Простые вложенные запросы обрабатываются по принципу "снизу вверх". Первым обрабатывается вложенный запрос самого нижнего уровня. Значе- ние, полученное в результате его выполнения, используется при реализации подзапроса более высокого уровня и т. д. Запросы с коррелированными вложенными запросами обрабатываются сис- темой в обратном порядке. Сначала выбирается первая строка рабочей таб- лицы, сформированной основным запросом, и из нее выбираются значения тех столбцов, которые используются во вложенном запросе (вложенных за- просах). Если эти значения удовлетворяют условиям вложенного запроса, то выбранная строка включается в результат. Затем выбирается вторая строка и т. д., пока в результат не будут включены все строки, удовлетворяющие вложенному запросу (последовательности вложенных запросов). Следует отметить, что язык SQL обладает большой избыточностью в том смысле, что он часто предоставляет несколько различных способов формули- ровки одного и того же запроса. Многие из запросов, приведенных в этом уроке, можно было написать и по-другому. Но здесь все же будут приведены их варианты с использованием вложенных запросов. Это связано с необхо- димостью детального знакомства с созданием и принципом выполнения вло- женных запросов, т. к. есть немало задач (особенно на удаление и изменение данных), которые не могут быть реализованы другим способом. Кроме того, разные формулировки одного и того же запроса требуют для своего выпол- нения различных ресурсов памяти и могут значительно отличаться по време- ни реализации в разных СУБД. Ïðîñòûå âëîæåííûå çàïðîñû Здесь внутренний оператор SELECT используется для получения значения, ко- торое будет использовано в операциях сравнения внешнего оператора SELECT Например: mysql> SELECT title FROM book WHERE id_book=(SELECT id_book FROM book WHERE id_author=1 and year_issue=2001); Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 143 Рис. 12.32. Использование вложенного запроса Здесь перед внутренним запросом стоит оператор сравнения, поэтому необ- ходимо, чтобы в результате вложенного запроса было получено не больше одного значения (рис. 12.32). Если получено больше значений, то выполне- ние оператора вызовет ошибку (рис. 12.33). Рис. 12.33. Ошибка при получении более одного значения во вложенном запросе В некоторых ситуациях можно добавить в оператор SELECT предложение LIMIT 1 . Вложенный запрос такого вида можно использовать для вставки в предложение WHERE какой-нибудь функции. Например, чтобы узнать, какая книга самая старая в библиотеке, надо написать: mysql> SELECT * FROM book WHERE year_issue=MIN(year_issue); Рис. 12.34. Выборка самой старой книги в нашей библиотеке ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 144 Но данный запрос неверен и вызовет ошибку, т. к. в предложении WHERE нель- зя использовать групповые функции. Выход можно найти с помощью вло- женного запроса. Предыдущий запрос можно переписать так: mysql> SELECT * FROM book WHERE year_issue=(SELECT MIN(year_issue) FROM book); Результаты обоих запросов представлены на рис. 12.34. Âëîæåííûå çàïðîñû â ïðåäëîæåíèÿõ EXISTS è NOT EXISTS Этот способ выборки работает с помощью передачи значений, полученных из внешнего оператора SELECT , во внутренний для проверки того, соответствуют ли они условиям, описанным во внутреннем запросе. Данную форму запроса можно использовать при поиске в таблице записей, соответствующих или не соответствующих записям другой таблицы. Для примера возьмем таблицы table1 и table2 , которые мы уже использовали ранее (см. рис. 12.27). Напишем запрос, определяющий соответствие наших таблиц: mysql> SELECT id FROM table1 WHERE EXISTS (SELECT * FROM table2 WHERE table1.id=table2.id); Данный запрос произвел выборку по значениям, присутствующим в обеих таблицах. Здесь для обращения к полю таблицы использовался синтаксис имя_таблицы.имя_поля, иначе возникла бы неоднозначность, т. к. имена полей в таблицах совпадают. Результат вложенного запроса в данном случае оценивается как "истина" или "ложь", т. е. возвращает или не возвращает он хоть какие-то строки (не кон- кретное значение, как в запросе первого вида). Соответственно, в подзапросе можно указывать различные имена полей, главное — понимать, что он дол- жен вернуть значение "истина". Поэтому предыдущий запрос можно запи- сать так: mysql> SELECT id FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id=table2.id); Выражение NOT EXISTS определяет несоответствия, т. е. значения, которые присутствуют в одной таблице, но отсутствуют в другой (рис. 12.36): Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 145 mysql> SELECT txt FROM table2 WHERE NOT EXISTS (SELECT * FROM table1 WHERE table1.txt=table2.txt); mysql> SELECT id FROM table2 WHERE NOT EXISTS (SELECT * FROM table1 WHERE table1.id=table2.id); Рис. 12.35. Использование выражения EXISTS Рис. 12.36. Использование выражения NOT EXISTS Âëîæåííûå çàïðîñû â ïðåäëîæåíèÿõ IN è NOT IN Этот вид запросов работает так: вложенный оператор SELECT возвращает зна- чения из одного поля, которые будут оцениваться во внешнем операторе SELECT . Например (рис. 12.37 и 12.38): ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 146 mysql> SELECT txt FROM table2 WHERE txt NOT IN (SELECT txt FROM table1); mysql> SELECT id FROM table2 WHERE id NOT IN (SELECT id FROM table1); mysql> SELECT id FROM table1 WHERE id IN (SELECT id FROM table2); Рис. 12.37. Использование выражения NOT IN Рис. 12.38. Использование выражения IN Последние примеры — это переделка предыдущих запросов выборки, где применялись предложения EXISTS и NOT EXISTS . Запросы, использующие вло- женную выборку, можно переписать с использованием объединений (как и поступали при работе с MySQL версий до 4.1). Например: mysql> SELECT title FROM book WHERE id_book=(SELECT id_book FROM unit WHERE id_unit=20); Подобный запрос можно написать, используя простое объединение: mysql> SELECT title FROM book,unit WHERE book.id_book=unit.id_book and unit.id_unit=20; Если вы работаете с MySQL 4.1 и выше, то это не значит, что все запросы выборки теперь можно писать с использованием вложенных запросов, — Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 147 стоит проверить варианты запросов, использующие объединение, т. к. до- вольно часто они работают эффективнее. Îáúåäèíåíèå UNION Оператор UNION , реализованный в MySQL 4.0.0, предназначен для объедине- ния результатов выполнения нескольких операторов SELECT в один набор ре- зультатов. Эти операторы SELECT являются обычными запросами выборки данных. Оператор UNION имеет следующие особенности. Имена и типы данных полей для результирующего набора устанавливают- ся по имени и типу первого оператора SELECT . Остальные операторы SELECT в объединении UNION должны иметь одинаковое количество полей, но им совсем необязательно иметь одинаковые тип и имя. По умолчанию результирующий набор запроса с оператором UNION не со- держит повторяющихся строк (как при использовании ключевого слова DISTINCT ). Для упорядочения результата необходимо добавить предложение ORDER BY в последний оператор SELECT . Оно будет относиться ко всему результи- рующему набору. Можно осуществлять выборку из одной таблицы различных наборов, удовлетворяющих разным условиям. Для того чтобы создать оператор UNION , нужно написать несколько операто- ров SELECT , вставив между ними ключевое слово UNION : mysql> SELECT title FROM book UNION SELECT author FROM author; В результирующей выборке после выполнения данного запроса мы видим (рис. 12.39) названия всех книг, имеющихся в библиотеке, а также фамилии всех авторов. Во время выполнения запроса поля подбираются по порядку следования, а не по имени. Именно поэтому следующие запросы (рис. 12.40) дают разный ре- зультат: mysql> SELECT id_author, year_issue FROM book UNION SELECT id_author, author FROM author; mysql> SELECT id_author, year_issue FROM book UNION SELECT author, id_author FROM author; И в первом и во втором случае поля, выбранные из таблицы book , задают ти- пы, полученные в результате работы оператора UNION ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 148 Рис. 12.39. Использование оператора UNION Как уже говорилось, при использовании оператора UNION в результирующей выборке исключены повторения. Для того чтобы появились повторения, не- обходимо добавить ключевое слово ALL после первого ключевого слова UNION : mysql> SELECT id_author, year_issue FROM book UNION SELECT author, id_author FROM author; Результат работы запроса с ключевым словом ALL можно увидеть на рис. 12.40. У нас появилось одно повторение — дважды встречается 1 в поле id_author и дважды 2003 год в поле year_issue При сортировке предложение ORDER BY может задаваться для отдельного опе- ратора SELECT , для этого оператор SELECT вместе с ORDER BY необходимо по- местить в скобки. В запросах с использованием оператора UNION можно указывать предложение LIMIT n . Например: mysql> SELECT * FROM table1 UNION SELECT * FROM table2 LIMIT 1; В этом случае он относится ко всему результирующему набору. Если заклю- чить его в скобки вместе с оператором SELECT , то он будет действовать имен- но на этот оператор: mysql> (SELECT * FROM table1 LIMIT 1) UNION (SELECT * FROM table2 LIMIT 1); Как уже говорилось, вы можете производить выборку различных результи- рующих наборов, удовлетворяющих разным условиям, в том числе и из од- ной таблицы. Оператор UNION отсутствовал в MySQL версий до 4.0, поэтому приходилось пользоваться другими методами — сделать выборки во времен- ную таблицу, а затем сделать выборку из временной таблицы. Это выглядело примерно так: Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 149 SELECT TEMPORARY TABLE tmp_table TYPE=HEAP SELECT ... FROM table1 WHERE...; INSERT INTO tmp_table SELECT ... FROM table2 WHERE...; SELECT * FROM tmp_table; Рис. 12.40. Примеры работы оператора UNION ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 150 MySQL автоматически удаляет временные ( TEMPORARY ) таблицы после завер- шения текущего сеанса. Здесь использовалась таблица типа HEAP , она хранит- ся в памяти, что позволит увеличить быстродействие. Хотя вы можете уда- лить таблицу самостоятельно, используя оператор DROP TABLE — это позволит освободить ресурсы, особенно если запросы продолжают выполняться. Это всегда приходилось делать в версиях до 3.23, поскольку не существовало таблиц типа TEMPORARY и отсутствовали таблицы типа HEAP Óäàëåíèå è îáíîâëåíèå íåñêîëüêèõ òàáëèö В MySQL, начиная с версии 4, появилась возможность удаления и обновле- ния нескольких таблиц. mysql> DELETE table1 FROM table1, table2 WHERE table1.id=table2.id; Данный запрос удалит записи из таблицы table1 , имеющие соответствующие значения в таблице table2 . В предложении FROM нужно перечислить таблицы, участвующие в запросе, а в предложении WHERE — задать условия соответст- вия записей. Можно удалить записи одновременно из нескольких таблиц (там, где обна- ружено соответствие): mysql> DELETE table1, table2 FROM table1, table2 WHERE table1.id=table2.id; Для удаления несоответствующих записей можно написать: mysql> DELETE table1 FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; или mysql> DELETE FROM table1 USING table1, table2 WHERE table1.id=table2.id; mysql> DELETE FROM table1, table2 USING table1, table2 WHERE table1.id=table2.id; mysql> DELETE FROM table1 USING table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; Три последние варианта запроса поддерживаются, начиная с версии 4.0.2. Оператор UPDATE имеет те же принципы работы на нескольких таблицах, что и оператор DELETE . Например: mysql> UPDATE table1, table2 SET table1.txt=table2.txt WHERE table1.id=table2,id; Óðîê 12. Âûáîðêà äàííûõ (îïåðàòîð SELECT) 151 Íåñêîëüêî ñëîâ î òðàíçàêöèÿõ Транзакция — это набор операторов SQL, которые будут выполнены как одна операция, не прерываемая другими клиентами. Транзакции обеспечивают неприкосновенность данных во время вашей работы с ними. Это означает, что MySQL блокирует некоторые операторы SQL, чтобы клиенты не смогли повлиять на работу друг друга. Взаимное влияние могут оказывать и операции. Транзакция группирует опе- раторы в единую исполняемую структуру, что позволяет избежать проблем при параллельной работе большого количества клиентов. Транзакции поддерживают операции commit (выполнение) и rollback (откат). То есть если какая-то часть транзакции дала сбой, есть возможность отменить результаты выполнения предыдущих операций. Соответственно БД останется в том виде, который она имела до начала выполнения транзакции. В MySQL транзакции поддерживают таблицы типов BDB и InnoDB ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 152 ЧАСТЬ IV PHP è MySQL Óðîê 13. PHP â HTML Óðîê 14. Îñíîâû ÿçûêà PHP Óðîê 15. Îòîáðàæåíèå è âñòàâêà äàííûõ Óðîê 16. Îáðàáîòêà ðåçóëüòàòîâ çàïðîñà Óðîê 17. Ïîëó÷åíèå äàííûõ èç ôîðìû PHP — это серверный язык создания сценариев, предназначенный специаль- но для генерации web-приложений. Разработка языка была начата в 1995 году Расмусом Лердорфом (Rasmus Lerdorf). Изначально название PHP являлось сокращением от Personal Home Page (персональная начальная страница), но в дальнейшем эта аббревиатура стала означать PHP Hypertext Preprocessor (препроцессор языка PHP). Сейчас язык PHP стал очень популярным благо- даря легкости изучения и простоте встраивания в HTML-код и используется на нескольких миллионах доменах. Язык PHP, как и СУБД MySQL, является продуктом с открытым исходным кодом (open-source). Совместное использование PHP и MySQL позволит вам создавать динамиче- ские сайты с информацией, изменяемой в реальном режиме времени, и пре- доставит большие возможности по настройке структуры проекта. Для ис- пользования PHP вам нужно установить на своем компьютере интерпретатор языка PHP и web-сервер (например, Apache). ÓÐÎÊ 13 PHP â HTML Основная задача языка PHP — интерпретация сценариев (программ) для ге- нерации web-страниц, отсылаемых программе-клиенту (браузеру). Сценарий (скрипт — от англ. script) может содержать как PHP-, так и HTML-код. HTML-код пересылается в литеральном представлении, а PHP-код выполня- ется, и клиенту отсылается результат его работы. Таким образом, пользова- тель никогда не видит PHP-кода. Вы можете использовать PHP для обработки данных, которые пользователь ввел в форму. Для встраивания PHP-кода в HTML должны присутствовать открывающие и закрывающие теги, идентифицирующие PHP-код. Есть несколько способов вставки PHP кода: PHP_код ?> — стандартный способ; PHP_код ?> — использование коротких тегов (этот способ работает только при соответствующей настройке сервера); — аналогично вставке кода на JavaScript. Файл, содержащий PHP-код, должен иметь расширение PHP. Все созданные вами PHP-сценарии будут размещаться в корневом каталоге сервера. В ОС Linux этот каталог называется PUBLIC_HTML. В Windows, если вы исполь- зуете Apache как web-сервер, таким каталогом является HTDOCS. В случае использования IIS как web-сервера это будет каталог WWWROOT. Доступ к корневому каталогу вы можете получить, введя команду localhost в адрес- ной строке браузера. Примеры данной книги были разработаны с использованием сервера Apache. Вы должны настроить конфигурационный файл HTTPD.CONF для того, что- бы сервер Apache распознавал файлы с расширением PHP. Необходимо доба- вить в него следующие строки: ×àñòü IV. PHP è MySQL 156 AddType application/x-httpd-php .php ScriptAlias /__php__/ "c:/php/" Action aplication/x-httpd-php /__php__/php.exe Здесь предполагается, что интерпретатор PHP установлен в корневой каталог диска С:. Для проверки работы PHP откройте любой текстовый редактор и наберите следующий код: ?> Сохраните файл под именем TEST.PHP в корневом каталоге сервера и запус- тите его в браузере. На рис. 13.1 показан результат работы сценария. Рис. 13.1. Если интерпретатор PHP установлен, вы увидите такую страницу ÓÐÎÊ 14 Îñíîâû ÿçûêà PHP Все команды (утверждения) в коде должны заканчиваться точкой с запятой ( ; ). Вы можете размещать несколько команд на одной строке, заканчивая их точкой с запятой. Но для ясности кода программы лучше этого не делать. В PHP также присутствуют управляющие элементы (условные операторы, циклы и т. д.), после которых точка с запятой не требуется. В код программы можно вставлять комментарии, которые будут игнориро- ваться как PHP-интерпретатором, так и HTML-браузером. Использование комментариев помогает программисту вспомнить, о чем он думал в тот мо- мент. Однострочный комментарий предваряется двумя прямыми слэшами ( // ) или символом решетки ( # ), a многострочный заключается между откры- вающей и закрывающей "скобками" ( /* и */ ). Ïåðåìåííûå Переменные в PHP начинаются со знака доллара ( $ ). Имя переменной может включать латинские буквы, цифры и символ подчеркивания ( _ ) и должно на- чинаться с буквы или символа подчеркивания (_). Имя переменной чувстви- тельно к регистру букв, т. е. $a и $A — это разные переменные. При объявле- нии переменной обычно не задается определенный тип, как во многих других языках. Тип переменной (т. е. тип данных, который она будет хранить) опре- деляется по контексту использования. В PHP есть следующие типы переменных: integer floating point string ×àñòü IV. PHP è MySQL 158 object array "Пустая" переменная объявляется при помощи ключевого слова VAR : VAR $some_var; Переменная также может быть объявлена, когда она впервые используется: $some_var="value"; Òèï integer Переменные типа integer могут содержать целое число в пределах от –2 биллионов до +2 биллионов в десятичном, восьмеричном и шестнадцате- ричном исчислении. Например: $var1=100; /* Десятичное значение */ $var2=0144; /* Восьмеричное значение */ $var3=0x64; /* Шестнадцатеричное значение */ Òèï floating point Переменная типа floating point — это число с плавающей точкой (дробь). Например: $var1=2.34; $var2=234e1; Òèï string Строка (переменная типа string ) — это любая комбинация из букв, цифр и специальных символов. При задании строка оформляется в одиночных (апо- строфы) или двойных кавычках. Если строка задана в двойных кавычках, она будет просмотрена на наличие переменных. Если они присутствуют, то вме- сто имен переменных будут подставлены их значения. Примеры строковых переменных: $var="23"; $str1='Переменная содержит значение $var'; $str2="Переменная содержит значение $var"; При выводе наших переменных мы получим соответственно: Переменная содержит значение $var Переменная содержит значение 23 Óðîê 14. Îñíîâû ÿçûêà PHP 159 Возможно, вам понадобится включить в строку специальные символы. Например, вы можете использовать обратный слэш ( \ ) для вывода символа двойной кавычки ( " ). Если написать $str1="фирма "Графика""; это вызовет ошибку, но если использовать обратный слэш ( \ ) $str1="фирма \"Графика\""; то ошибки не будет. При наличии в строке символа обратного слэша (\) следующий за ним символ воспринимается как литерал. В табл. 14.1 описаны Escape-последовательности (обратный слэш и специ- альный символ), применяемые в строковых значениях. Òàáëèöà 14.1. Escape-ïîñëåäîâàòåëüíîñòè, ïðèìåíÿåìûå â ÿçûêå PHP Escape-последовательность Значение \n Начало новой строки \r Перевод каретки \t Символ табуляции \\ Символ ( \ ) \" Символ ( " ) \$ Символ ( $ ) \0 Восьмеричное значение \x Шестнадцатеричное значение Òèï object Объекты (переменные типа object ) являются экземплярами класса. Для соз- дания объекта необходимо прежде создать класс. Класс может содержать на- бор переменных и функций для работы с ними. Более подробную информацию вы найдете в документации по PHP (http://php.net) или в учебниках по языку PHP. ×àñòü IV. PHP è MySQL 160 Òèï array Переменные типа array используются при объявлении массивов. В PHP есть два вида массивов: массив с целочисленными индексами; массив с индексированный строками (хэш). Например, для создания массива, содержащего четыре значения, нужно написать: $myarr=array("value1", "value2" , "value3", "value4"); Каждому элементу, помещенному в массив, присваивается индекс (начиная с 0). Таким образом, $myarr[0] даст нам "value1" , а $myarr[3] — "value4" Конечно, можно добавить в массив элемент: $myarr[]="value5"; Создать хэш можно так: $myhash=array('size'=>'large', 'style'=>'italic', 'family'=>'Arial'); Îïåðàöèè PHP поддерживает несколько видов операций: присваивание; арифметические операции; логические операции; конкатенация; сравнение. Àðèôìåòè÷åñêèå îïåðàöèè В табл. 14.2 кратко описаны арифметические операции. Òàáëèöà 14.2. Àðèôìåòè÷åñêèå îïåðàöèè Оператор Пример операции Описание + $a+$b Вычисление суммы значений переменных $a и $b - $a-$b Вычисление разности значений переменных $a и $b * $a*$b Вычисление произведения значений переменных $a и $b / $a/$b Вычисление частного значений переменных $a и $b % $a%$b Вычисление остатка от деления по модулю значения переменной $a на значение переменной $b Óðîê 14. Îñíîâû ÿçûêà PHP 161 Ëîãè÷åñêèå îïåðàöèè Логическая операция (табл. 14.3) определяет в соответствии с определенны- ми критериями, является ли возвращаемое значение истинным ( true ) или ложным ( false ). Òàáëèöà 14.3. Ëîãè÷åñêèå îïåðàöèè Оператор Пример операции Описание and или && $a and $b $a && $b Возвращает значение true , если истинны значения обеих переменных or или || $a or $b $a || $b Возвращает значение true , если истинно значение хотя бы одной из переменных not или ! not $a !$a Логическое отрицание, инвертирует значение переменной Êîíêàòåíàöèÿ Оператор конкатенации ( ) осуществляет сложение двух строк (две строки объединяются). Например: $a='Оля'; echo 'Привет '.$a; ?> или $h=12; $m=35; $time=$h.':'.$m; echo $time; //получим 12:35 ?> Ñðàâíåíèå Операции сравнения определяют отношение между двумя переменными или выражениями и возвращают значение true или false . Операторы сравнения приведены в табл. 14.4. ×àñòü IV. PHP è MySQL 162 Òàáëèöà 14.4. Îïåðàòîðû ñðàâíåíèÿ Оператор Описание == Равно != Не равно > Больше, чем < Меньше, чем >= Больше или равно <= Меньше или равно Ñòðóêòóðû óïðàâëåíèÿ PHP поддерживает несколько структур управления ходом программы. К ним относятся if , for , while , switch и др. if / elseif Данная структура позволяет реализовать программную логику. Вы можете проверять различные выражения и в зависимости от результата проверки вы- полнять то или иное действие. Базовый синтаксис можно описать так: выражение1) { действия1; } elseif( выражение2) { действия2; } else { действия по умолчанию; } ?> действия1 и действия2 выполняются лишь в том случае, если выражение1 в ус- ловии if или выражение2 в условии elseif оказалось истинным. Если все опи- санные выражения оказались ложными, то выполняются действия по умолча- нию , прописанные в разделе else ("иначе"). Óðîê 14. Îñíîâû ÿçûêà PHP 163 for è foreach Оператор цикла for выполняет заданный блок кода определенное количество раз (итераций). выражение1; выражение2; выражение3) { действия } ?> Параметры: выражение1 — начальное значение счетчика повторений; выраже- ние2 — описание критерия (условия), в соответствии с которым выполнение цикла FOR будет прекращено; выражение3 — изменение значения счетчика (увеличение или уменьшение). Пример ( echo — это конструкция языка, предназначенная для вывода одной или более строк на экран): \n"; for($i=0;$i<10;$i++) { echo "\n"; } echo ""; ?> Результат представлен на рис. 14.1. Структура FOREACH предназначена для перебора элементов числового массива или хэша. { PHP-код } ?> В случае хэша синтаксис следующий: $value) { PHP-код } ?> ×àñòü IV. PHP è MySQL 164 Рис. 14.1. Результат работы примера программы на PHP while Оператор цикла while повторяет блок кода до тех пор, пока выполняется не- которое условие. Как только условие станет ложным, выполнение цикла за- канчивается. условие) { действия } ?> switch Оператор switch очень похож на выполнение серии операторов if с одним выражением. Довольно часто приходится сравнивать значение переменной (или выражения) с разными значениями и выполнять различные блоки кода в зависимости от результата этого сравнения. Синтаксис оператора switch : выражение) { Óðîê 14. Îñíîâû ÿçûêà PHP 165 case значение1: действия1; break; case значение2: действия2; break; default: действия_по_умолчанию; } ?> Данный оператор выполняется построчно. Блок кода действия будет выпол- нен только тогда, когда будет найдено значение , совпадающее со значением выражение в операторе switch . PHP будет продолжать выполнять команды до конца блока switch или до первого оператора break . Если не поставить break в конце оператора case , то выполнятся и все оставшиеся участки кода. Так что не забывайте об этом (хотя, в некоторых ситуациях ставить break и не требуется). действия_по_умолчанию — это код, который выполнится в случае, если ни одно указанное значение не совпало со значением выражения . Оператор default должен находиться в самом конце списка операторов case Пример: { case 1: echo "i равно 1"; break; case 2: echo "i равно 2"; break; case 3: echo "i равно 3"; break; } ?> Ôóíêöèè Функции являются традиционными конструкциями языка. Они выполняют набор определенных действий, используя различные параметры, и возвраща- ют результат своей работы в точку вызова функции. Язык PHP поддерживает два вида функций: функции, объявленные программистом (пользовательские); стандартные (встроенные) функции языка. ×àñòü IV. PHP è MySQL 166 Ïîëüçîâàòåëüñêèå ôóíêöèè Если вам требуется периодически выполнять серию команд, то вы можете поместить эти команды в функцию. Это сделает ваш код более гибким и удо- бочитаемым. Вместо того чтобы постоянно прописывать серию одних и тех же команд, вы можете сделать вызов функции (предварительно объявленной). Это будет намного короче. Кроме этого, если появится необходимость внесения каких- либо изменений, их нужно будет сделать один раз, исправив объявленную вами функцию. Для создания функции в PHP-коде используйте следующий синтаксис: function имя_функции(параметры_функции); { действия; } Параметры: имя_функции — имя функции, задается программистом; параметры_ функции — параметры, используемые функцией (в зависимости от значения этих параметров может изменяться результат работы функции); действия — тело функции (набор команд, которые она должна выполнить). Âñòðîåííûå ôóíêöèè Это функции, описанные разработчиками языка при его создании, которые выполняют часто применяемые операции. Для того чтобы использовать встроенную функцию, вам необходимо знать имя этой функции и список ее параметров. Обращение к функции (вызов) происходит по ее имени. Более подробную информацию вы найдете в документации по PHP (на сайте http://php.net). ÓÐÎÊ 15 Îòîáðàæåíèå è âñòàâêà äàííûõ Настало время посмотреть, как же PHP осуществляет доступ к СУБД MySQL и отображает результаты запросов на web-странице. В листингах PHP-функции, предназначенные для работы с MySQl, выделены полужирным начертанием. В листинге 15.1 приведен пример простого PHP-сценария. Листинг 15.1. Простой PHP-сценарий |