Книги, научные публикации Pages: | 1 | 2 | 3 | 4 | ... | 11 |

мтт. ...

-- [ Страница 2 ] --

% mysqlshow дает перечень всех баз данных, как SHOW DATABASES % mysqlshow db_name дает перечень всех таблиц в Данной базе данных, как SHOW TABLES % mysqlshow db_name tbl_name дает перечень столбцов в данной таблице, как DESCRIBE tbl name Глава 1. Знакомство с СУБД MySQL и SQL Таблицы проекта "Учет успеваемости" Попробуем посмотреть на примере учета успеваемости в простом журнале, какого рода таблицы нам нужны для реализации проекта контроля успеваемости. На рис. 1.2 изображена страница из обыкновенного журнала. Основным телом журнала является матрица с оценками. Присутствуют и другие данные, необходимые для того, чтобы придать смысл этой информации. Перечисляются имена и идентификационные номера учащихся. (Для простоты ограничимся только четырьмя учащимися.) Вверху записаны даты викторин и тестов. Из рисунка видно, что викторины проводились 3, 6, 16 и 23 сентября, а тесты Ч 9 сентября и 1 октября. Для контроля всей этой информации нам понадобится таблица итоговых оценок score. Что должны хранить записи этой таблицы? В каждой строке указывается имя учащегося, дата викторины или теста и результат. На рис. 1.3 нашло отражение такое представление информации. (Даты представлены в формате "YYYY-MM-DD".) Учащиеся Идентификатор Оценки 1 2 3 Имя 9/3 9/6 9/9 9/169/2310/1... 14 10 73 14 15 67 Billy Missy 17 10 68 17 14 73 Johnny 15 10 78 12 17 82 Jenny 14 13 85 13 19 Q Q Т Q Q Т Рис. 1.2. Пример журнала успеваемости Таблица score name date Billy 1999-09-23 1999-09-23 Missy Johnny 1999-09-23 Jenny 1999-09-23 Billy 1999-10-01 Missy 1999-10-01 Johnny 1999-10-01 Jenny 1999-10-01 score 15 14 17 19 67 73 82 Рис. 1.З. Первоначальная структура таблицы score Однако похоже, что такая структура таблицы может создать определенные проблемы. Кажется, в ней недостает какой-то информации. Например, глядя на записи рис. 1.3, трудно сказать, результатом чего он является. Результатом викторины или результатом теста? Возможно, при определении окончательной оценки потребуется определить типы результатов. Это нужно в том случае, когда результаты викторин и результаты тестов имеют различный приоритет. Конечно, можно определить тип ре Часть I. Использование СУБД MySQL зультата по диапазону результатов, выпадающих на определенную дату (викторины обычно имеют более низкие оценки, чем тесты), но это будет неверно, так как полученный результат будет основываться не на явных данных, а на предположениях. Можно просто добавить столбец, содержащий флаг Т или Q для определения типа оценки. На рис. 1.4 Т Ч означает оценку за тест, Q Ч оценку за викторину. Теперь тип оценки объявлен явным образом. Недостатком является то, что эта информация несколько избыточна. Обратите внимание на то, что даты и типы счетов для нескольких учащихся совпадают. Все результаты за 23 сентября имеют тип Q, а за 1 октября Ч Т. Это бесспорно. При записи результатов тестов и викторин мы несколько раз записываем одну ту же дату и один и тот же тип. Кому нужно вводить эту избыточную информацию? Попробуем другое представление данных. Вместо записи типов результатов в таблицу score мы будем вычислять его из даты. Будем хранить перечень дат и использовать его для определения "типа события": просто найдите в таблице event дату, соответствующую дате в таблице score. Получится тип события. На рис. 1.5 приведена структура этой таблицы и видно, как реагирует эта связь на дату 23 сентября. Подобрав соответствующую запись в таблице event, мы увидим, что этот результат является результатом викторины. Таблица score date name Billy 1999-09-23 Missy 1999-09-23 Johnny 1999-09-23 Jenny 1999-09-23 Billy 1999-10-01 Missy 1999-10-01 Johnny 1999-10-01 Jenny 1999-10-01 score type 15 14 17 19 67 73 82 Q Q Q Q Т Т т т Рис. 1.4. Структура таблицы score, измененная для хранения типа результата Таблица score name date Billy 1999-09-23 Missy 1999-09-231 Johnny 1999-09-23 Jenny 1999-09-23 Billy 1999-10-01 Missy 1999-10-01 Johnny 1999-10-01 Jenny 1999-10-01 Таблица event date 1999-09-03 1999-09-06 ] 1999-09-09 N 1999-09-16 I 19994)9-23 1999-10- score type 15 14 17 19 67 73 82 Q Q Т Q Q Т Рис. 1.5. Таблицы score и event, связанные по дате Глава 1. Знакомство с СУБД MySQL и SQL Это значительно лучше, чем стараться определить тип результата, основываясь на догадках. Вместо этого тип результата определяется прямо из базы данных. Это предпочтительнее хранения результатов в таблице score, так как при этом тип записывается только один раз. Но здесь будет правомочен вопрос о том, что не слишком ли это увеличивает объемы работ, не затрудняет ли это работу? Отчасти этот вопрос резонен. Хранить два списка записей труднее, чем один. Но давайте посмотрим еще раз на журнал, (см. рис 1.2). Разве вы уже не храните два набора записей? Рассмотрим такие факты. Х Результаты отслеживаются в ячейках матрицы результатов. Каждая ячейка отмечена именем учащегося и датой (строки Ч имена, а столбцы Ч даты). Это один набор данных. Его аналог Ч содержимое таблицы score. Х Как можно узнать, какой тип события представляет каждая дата? Ага, над каждой датой надписано маленькое "Т" или "Q"! Таким образом вы отслеживаете взаимосвязь между датой и типом результата. Это второй набор данных. Его аналог Ч содержимое таблицы event. Другими словами, даже невзирая на то, что вы думаете обо все этом, на самом деле вы не делаете ничего, отличного от того, что предлагает автор. Единственным различием является то, что в журнале эти два набора данных не столь явно разделены. Страничка журнала иллюстрирует наше понимание информации и сложность переноса этих данных в базу данных. Обычно мы стараемся объединить различные типы информации и представить все это как единое целое. Базы данных так не работают. Вот почему они кажутся искусственными и неестественными. Природная человеческая потребность унифицировать информацию делает страшной мысль о множестве типов данных вместо одного. Поэтому для читателя может быть проблематично "думать так, как думает база данных". Единственное требование к таблице event: даты должны быть уникальными. Это требование имеет место потому, что по дате производится связывание записей из таблиц score и event. Другими словами, нельзя провести две викторины или викторину и тест в один день. В противном случае, у вас получится два набора записей в таблице score и две записи в таблице event, все с одной и той же датой. И у вас не будет возможности связать записи таблицы score с записями таблицы event. Эта проблема не возникнет, если делать в один день не больше одного экзамена, но насколько вероятно обратное? На первый взгляд, это достаточно правдоподобно;

в конце концов, не садист же вы, чтобы назначать и викторину, и тест на один и тот же день. Но я верю, что читатель простит мой скепсис. Очень часто я сталкивался с такой ситуацией, когда мне говорят, что этого никогда не случится, но это вдруг происходит, и мне приходится переделывать структуру таблиц. 70 Часть I. Использование СУБД MySQL Проблемы, которые могут возникнуть в будущем, надо предвидеть и заранее продумать, как избежать их возникновения. Итак, предположим, что вам может понадобиться записать два результата за день. Что делать в этом случае? Оказывается, эту проблему решить не так трудно. С минимальными изменениями в структуре данных мы разрешаем хранить много событий для одной даты. 1. Добавим столбец в таблицу event и будем в ней хранить номер, уникальный для каждой записи в таблице. Это присвоит каждой записи таблицы свой собственный идентификационный номер. Назовем ее event_id. (Просмотрите журнал на рис. 1.2, если читателю покажется это странным: идентификационный номер события очень напоминает номер столбца на матрице результатов вашего журнала. Номер может быть записан явно, даже назван "идентификатор события", но это именно он.) 2. При записи результатов в таблицу score вводите идентификатор, а не дату события. Результат этих изменений показан на рис. 1.6. Связь между таблицами score и event осуществляется с помощью идентификатора. Теперь в таблице event будет храниться не только тип события, но и дата, когда оно произошло. Кроме того, уникальность должна соблюдаться теперь не для даты события, а для его идентификатора. Это означает, что у вас может быть дюжина тестов и викторин в один и тот же день. (Без сомнения, ваши учащиеся начнут нервничать, узнав это.) К сожалению, с моей точки зрения, структура таблицы на рис. 1.6 кажется менее удовлетворительной, чем предыдущая. Таблица score является более абстрактной, чем ее предыдущие воплощения. Это явилось следствием того, что стало меньше столбцов с явным содержанием. Структура таблицы score, представленная на рис. 1.4, более понятна, так как содержит столбцы с датами и типами результатов. Последняя структура таблицы (см. рис. 1.6) имеет столбцы, назначение которых объяснить уже трудно. Она достаточно далека от понятных категорий. Кому захочется смотреть на таблицу score с "Идентификатором события"? Для непосвященного это ничего не значит. Таблица score event_id name Billy_ 5 Missy Johnny 5 Jenny 5 Billy 6 Missy 6 Johnny 6 Jenny 6 score Таблица event event_id date 1 1999-09-03 2 1999-09-06 3 1999-09-09 4 1999-09-16 1999-09-23.ДV5 1999-10-01 6 type Q Q Т Q 15 14 17 19 67 73 82 Q Т Рис. 1.6. Таблицы score и event, связанные по идентификатору события Глава 1. Знакомство с СУБД MySQL и SQL Теперь мы стоим на распутье. Вероятно, читатель заинтересовался возможностью хранить журнал успеваемости в электронном виде и не связываться с вычислениями вручную при подсчете баллов. Но по мере знакомства с тем, как можно хранить информацию в базе данных, он увидел, как эта информация абстрактна и несвязанна. Возникает естественный вопрос: "Насколько целесообразно использовать базу данных? Может быть, СУБД MySQL не для нас?" Как можно догадаться, мой ответ может быть только отрицательным, иначе эта книга теряет свой смысл. Ведь когда вы обдумываете, как сделать работу, не мешает рассмотреть различные альтернативы, например, выбор какойлибо базы данных (предположим, СУБД MySQL) или что-то наподобие электронных таблиц. Электронная таблица тоже может хранить данные в виде строк и столбцов. Это делает электронную таблицу и журнал успеваемости концептуально и визуально подобными. Х Используя электронную таблицу, можно производить вычисления. Это позволит суммировать оценки учащихся. Задача немного усложняется "присутствием" весового коэффициента. Но и это преодолимо. Однако если читателю захочется посмотреть только на какие-то определенные данные (только на тесты или викторины), провести сравнения оценок девочек с оценками мальчиков, отобразить суммарные данные различными способами Ч это уже другое дело. Электронной таблице с этими задачами будет уже справиться труднее. А СУБД с этим справляются запросто. Абстрактность и несвязанность информации, находящейся в таблицах базы данных, не проблема. Во время планирования и проектирования структуры базы данных необходимо думать о представлении базы данных. Так что во время работы с базой данных ее механизм поможет отображать информацию в понятном виде. Так что не надо смотреть на таблицы базы данных просто как на груду несвязной информации. Например, при выборке результатов из таблицы scores идентификаторы событий читателю не нужны, а нужны даты. Это нетрудно. По идентификатору события база данных просмотрит даты из таблицы event и выдаст их. Может просмотреть, с какими результатами вы имеете дело? С результатами тестов или результатами викторин? Это не проблема. База данных найдет типы результатов аналогичным образом, Ч пользуясь идентификатором события. Помните для чего удобна СУБД, подобная СУБД MySQL: для связывания информации из одного источника с информацией из другого. В случае с данными по успеваемости СУБД MySQL связывает информацию с помощью идентификатора события. Теперь посмотрим, как работает связь одной таблицы с другой в СУБД MySQL. Для этого сделаем запрос. Предположим, нам требуются все результаты за 23 сентября 1999 года. Получится вот такой запрос: т 72 Часть I. Использование СУБД MySQL SELECT score.name, event.date, score.score event.type FROM score, event WHERE event.date = "1999-09-23" AND score.event_id = event.event_id Просто ужас! Посредством объединения (связывания) записей из таблиц score и event этот запрос производит выборку имени учащегося, даты, оценки и типа оценки. Полученный результат выглядит следующим образом.

name 1 date 1 score 1 type 15 14 17 19 1 1 1 1 1 1 I 1 1 Billy Missy Johnny Jenny I 1999-09-23 1 I 1999-09-23 I 1999-09-23 1 I 1999-09-23 Заметили что-то знакомое в формате этой информации? А должны были! Это похоже на структуру таблицы, показанную на рис. 1.4! Нет необходимости знать идентификатор события для того, чтобы получить этот результат. Просто читатель описывает дату, которая его интересует, и предоставляет возможность СУБД MySQL определить, какие оценки принадлежат этой дате. Удивительно куда делась вся абстракция и связанность, присущая базе данных? Конечно, посмотрев на все это, у вас останутся вопросы. А именно: это слишком долго и сложно;

неужели нельзя быстрее узнать оценки для заданной даты? К сожалению, это так. Однако существуют способы избежать ввода нескольких строк на языке SQL каждый раз, когда надо запустить запрос. Обычно такой запрос пишется один раз и потом сохраняется в файле. Впрочем, я, кажется, немного поторопился, показав этот запрос. Это, поверьте мне, немного проще, чем то, что мы будем использовать для получения оценок. Причиной этого является то, что настало время внести еще одну поправку в структуру таблицы. Вместо хранения имени учащегося в таблице score, будем хранить там его идентификационный номер. (Т.е. столбец name будет заменен столбцом ID.) Затем будет создана новая таблица student, содержащая столбцы name и student_id (рис. 1.7). Таблица score Таблица student sex student_id name student_id event_id score M 1 1 Billy 5 15 2 Missy F 2 14 H-H Johnny M 3 3 5 17 4 Jenny F 4 5 19 Таблица event event_id 1 2 3 4 -ЧЩи - date type 1999-09-03 1999-09-06 1999-09-09 1999-09-16 1999-09-23 Q Т 1999-10- 1 2 3 6 6 6 67 73 82 Рис. 1.7. Таблицы score, event и student, связанные по идентификаторам события и учащегося Глава 1. Знакомство с СУБД MySQL и SQL 0- Зачем делать эти изменения? Может существовать двое учащихся с одинаковым именем. Использование уникального идентификатора учащегося позволит вам получить возможность различать их оценки. (Это абсолютно аналогично тому, как мы с помощью уникального идентификатора события сможем разделять оценки, полученные за викторину, и оценки, полученные за тесты.) Повторим запрос после сделанного изменения структуры таблиц.

SELECT student.name, event.date, score.score FROM score, event, student WHERE event.date = "1999-09-23" AND event.event_id = score.event_id AND score.student_id = student.student_id event.type He расстраивайтесь, если вы не поняли смысл этого запроса сразу. Многие не понимают. Мы вернемся к этому запросу повторно при более углубленном изучении. Но разница между сейчас и потом заключается в том, что потом вы его поймете. Конечно, я не шучу. Вероятно, вы заметили, что я что-то изменил в таблице student, чего не было в журнале. Теперь она содержит столбец для хранения информации о поле учащегося. Это позволит производить более простые операции, например, подсчет девочек и мальчиков в классе или более сложные, такие как сравнение суммарных оценок девочек и мальчиков. С проектом учета успеваемости учащихся мы почти закончили. Нам осталась еще одна таблица, предназначенная для фиксации посещаемости. Ее содержимое относительно несложно: идентификационный номер учащегося и дата (рис. 1.8). Каждая строка в таблице свидетельствует о том, что определенный учащийся отсутствовал в определенный день. В конце этой главы мы обратимся к вычислительным возможностям СУБД MySQL для того, чтобы узнать, сколько раз учащийся отсутствовал. Таблица absence student_id date 2 1999-09-02 1999-09-15 4 2 1999-09-20 Рис. 1.8. Таблица absence Теперь мы готовы создать таблицы проекта контроля успеваемости, потому что знаем, что они собой представляют. Оператор CREATE TABLE, предназначенный для создания таблицы student, имеет следующий вид.

CREATE TABLE Student ( name VARCHAR(20) NOT NULL, sex ENUMCF 1, 'M') NOT NULL, Studentid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY Часть I. Использование СУБД MySQL Введите этот оператор, работая в mysql, или запустите из оболочки следующую команду:

% mysql samp_db < create_student.sql После этого оператор CREATE TABLE создаст таблицу student с тремя столбцами: name, sex и student_id. Столбец name является столбцом переменной длины, которая может хранить до 20 символов. Это представление имени проще, чем в случае с таблицами "Исторической Лиги": один столбец используется для хранения имени и фамилии. Это потому, что я знаю наперед, что ни в одном примере запроса для базы данных контроля успеваемости не потребуется раздельная выборка имени и фамилии. Столбец sex отражает пол учащегося. Это столбец перечисляемого типа ENUM, а это значит, что он может принимать одно из двух значений: "F" или "м". Этот тип столбца полезен тогда, когда столбец принимает значения только из ограниченного диапазона. В этом случае можно использовать тип CHAR (1), но ENUM будет явно определять, какие значения может принимать столбец. Оператор DESCRIBE tbl_name для такой таблицы покажет, какие точно значения может принимать этот столбец. Столбец student_id является столбцом целого типа, в котором будет храниться уникальный идентификационный номер учащегося. Обычно идентификационные номера можно получить из какого-то центрального источника, такого как учительская. Объявление типа столбца student_id состоит из нескольких частей. Х INT. Свидетельствует о том, что столбец содержит целые величины (значения не имеют дробной части). Х UNSIGNED. Принимает только положительные значения. Х NOT NULL. Означает, что столбец должен быть всегда заполнен. (Учащийся не может не иметь идентификационного номера.) Х AUTOINCREMENT. Это специальный атрибут СУБД MySQL. Он работает следующим образом. Если во время добавления новой записи значение для столбца student отсутствует (или пустое), СУБД MySQL автоматически создает уникальный номер, который больше максимального значения для этого столбца на единицу. Этот факт будет использован нами при загрузке таблицы student. Будем заполнять только значения для столбцов name и sex. СУБД MySQL сама сгенерирует для нас значения столбца student_id. Х PRIMARY KEY. Означает, что столбец является индексом и каждое значение должно быть уникальным. Он поможет нам избежать повторного использования одного и того же идентификатора по ошибке. (СУБД MySQL требует, чтобы всякий столбец с атрибутом AUTO_INCREMENT имел уникальный индекс.) Проще говоря, можно рассматривать ключи AUTO_INCREMENT и PRIMARY KEY как Глава 1. Знакомство с СУБД MySQL и SQL некое средство, позволяющее нам волшебным образом создавать идентификационные номера для учащихся. Практически не важно, какие значения принимают идентификаторы. Они уникальны. Столбец student_id нужно объявлять аналогичным способом и в случае, если вы собираетесь получить идентификационные номера учащихся от дирекции школы, правда, с одним условием: в этом случае необходимо опустить атрибут AUTO_JNCREMENT.

Внимание!

Таблица event имеет следующий вид.

CREATE TABLE event ( date DATE NOT NULL, type ENUM{'T','Q') NOT NULL, event_ld INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ) Введите этот оператор в mysql, или запустите из оболочки следующую команду:

% mysql samp_db < create_event.sql Все столбцы объявлены здесь как NOT NULL потому, что ни одна из них не может быть пропущена. Столбец date хранит стандартное для СУБД MySQL значение типа DATE в формате "YYYY-MM-DD". Столбец type хранит тип оценки и, как столбец sex в таблице student type, является перечисляемым типом. Допустимые значения здесь Т и Q, что соответствует "тесту" и "викторине" соответственно. Столбец event_id является столбцом типа AUTO_INCREMENT, полностью идентичным столбцу student_id в таблице student. Аналогично столбцу student_id, в таблице student конкретные значения менее важны чем то, что они должны быть уникальными. Таблица score имеет следующий вид.

CREATE TABLE score ( Studentid INT UNSIGNED NOT NULL, event_id INT UNSIGNED NOT NULL, score INT NOT NULL, PRIMARY KEY (event_id, student_id) ) Введите этот оператор, работая в mysql, или запустите из оболочки следующую команду. % nrysql samp_db < create_score. sql Столбец score имеет тип INT, т.е. предполагается, что все значения оценок являются целочисленными. Следовательно, если в оценках необ76 Часть I. Использование СУБД MySQL ходимо использовать числа, подобные 58,5, нужно задать типы данных с плавающей точкой, такие как FLOAT или DECIMAL. Столбцы student_id и event_id являются целыми, показывающими, чья это оценка и какое это событие. Связав по этим полям таблицы student и event, мы сможем узнать имя учащегося и дату события. Комбинация этих столбцов является первичным ключом. Это гарантирует нас от дублирования оценок одного учащегося за один и тот же тест или викторину. Упростится также последующее изменение оценки. Например, если оценка была введена неверно, в дальнейшем будет нетрудно изменить ее с помощью оператора REPLACE. Нет необходимости делать две операции DELETE и INSERT. Обратите внимание на то, что комбинация student_id и event_id является уникальной. В таблице score нет столбца, уникального самого по себе. Для каждого идентификатора event_id может быть несколько записей (по одной на каждого учащегося). Аналогично, для каждого идентификатора student_id может быть несколько записей (по одной на каждый тест или викторину). Таблица absence имеет следующий вид. CREATE TABLE absence ( student_id INT UNSIGNED NOT NULL, date DATE NOT NULL, PRIMARY KEY (event_id, date) ) Введите этот оператор, работая в mysql, или запустите из оболочки следующую команду. % mysql samp_db < create_absence.sql Во избежание потери данных столбцы student_id и date объявлены как NOT NULL. Комбинация этих столбцов объявлена первичным ключом Это воспрепятствует случайному созданию повторяющихся записей. Ведь действительно будет несправедливо посчитать одного учащегося отсутствующим дважды в день Дополнение таблиц Мы достигли цели. Теперь наши таблицы созданы, и в следующем разделе "Выборка информации" читатель увидит, как можно получить информацию из базы данных. Но сначала нужно таблицы заполнить. Можно назвать несколько методов добавления информации в базу данных. Можно добавлять записи в таблицы вручную. Для этой цели служит оператор INSERT. Можно добавлять записи прямо из файла или как исходные данные с помощью команды LOAD DATA;

с помощью утилиты mysql import или в виде предварительно созданных и сохраненных в файле операторов INSERT. Глава 1. Знакомство с СУБД MySQL и SQL В этом разделе демонстрируются все эти методы. Читателю остается только проверить их в работе. Прочитав этот раздел, с помощью команд, приведенных здесь, очистите таблицы и перегрузите их. Сделав это, можно быть уверенным в том, что эти таблицы будут содержать записи, аналогичные тем, с которыми я буду работать в следующем разделе. Это будет надежной гарантией идентичности результатов. Начнем добавление записей с оператора INSERT. Это оператор языка SQL, в котором определяется таблица, куда будет производиться добавление, строка добавляемых данных и значений. Оператор INSERT имеет несколько форм. Х Определение значений всех столбцов. INSERT INTO tbl_name VALUES(valuel, v a l u e 2,... ) Например:

mysql> INSERT INTO student VALUES('Kyle','M',NUbL);

mysql> INSERT INTO event VALUES('1999-9-3','Q',NULL);

Для MySQL 3.22.5 слово INTO является опциональным (это верно и для других форм оператора INSERT.) Список VALUE должен содержать все столбцы, хранящиеся в таблице. (Обычно это порядок следования названий столбцов в операторе CREATE. Для определения этого порядка можно воспользоваться оператором DESCRIBE tbl_name.) Выделять строковые значения или значения типа "дата" можно как одинарными, так и двойными кавычками. Пустые значения здесь будут присвоены столбцам с атрибутом автоинкремента в таблицах student и event. (Ввод недостающего значения приводит к генерированию следующего номера для student_id или event_id.) Начиная с версии СУБД MySQL 3.22.5 можно производить добавление сразу нескольких строк с помощью одного оператора INSERT:

INSERT INTO tbl_name VALUES (...),(...),...

Например:

mysql> INSERT INTO student VALUES('Abby1,'F1,NULL),('Kyle','M1,NULL);

Такой оператор потребует ввода меньшего количества информации, да и сервер сможет эффективнее обработать эту команду. Х Версия СУБД MySQL 3.22.10 позволяет задавать имена столбцов и их значение в форме col_name = value: INSERT INTO tbl_name SET col_namel=valuel,.col_name2=value2,... Например: mysql> INSERT INTO student SET lastname 1 = 'Stein, first_name='Waldo';

78 Часть I. Использование СУБД MySQL Столбцы, не перечисленные в SET, получают значение по умолчанию. Такая форма оператора INSERT неприменима для вставки нескольких строк. 1 Существуют и другие методы загрузки данных в таблицы базы данных прямо из "плоских" файлов. Для этого существует оператор LOAD DATA и утилита mysqlimport. Оператор LOAD DATA действует как массовый загрузчик, считывающий данные из файла. Так она работает из mysql.

mysql> LOAD DATA LOCAL INFILE "member.txt" INTO TABLE member;

Этот оператор считывает данные из файла member.txt, находящегося в текущем каталоге на узле клиента, и загружает их на сервер в таблицу member. Вариант LOAD DATA LOCAL не работает в версиях до 3.22.15, так как' начиная только с этой версии была добавлена возможность чтения данных прямо с компьютера клиента. (Без ключа LOCAL загружаемый файл должен быть расположен прямо на сервере, и поэтому для загрузки такого файла пользователь должен обладать широкими правами доступа к серверу, которых у него обычно нет.) Формат данных в файлах по умолчанию предполагает, что столбцы разделены табуляциями, строки заканчиваются с началом новой строки, значения располагаются в порядке следования столбцов таблицы. Но есть возможность загружать файлы в других форматах или определять другой порядок столбцов. Подробнее с этой проблемой можно ознакомиться в приложении Г, "Синтаксис SQL". Утилиту mysql import можно рассматривать как интерфейс между вводом на уровне оболочки операционной системы и оператором LOAD DATA. % mysql local samp_db member.txt Здесь по сути утилита mysql import генерирует оператор LOAD DATA для загрузки файла member.txt в таблицу member. Такая команда не сработает для версии СУБД MySQL старше 3.22.15, так как для нее потребуется оператор LOAD DATA LOCAL. Здесь все делается так, как это делается в mysql: если нужны параметры для связи, указывайте их перед именем базы данных. Утилита mysqlimport получает имя таблицы, для которой предназначены данные из имени файла. (Для этого используется все, что указывается в имени файла до первой точки.) Например, данные из файла member.txt будут загружены в таблицу member, а из файлаЧ president, txt Ч в таблицу president. Будьте осторожны, если возникла необходимость загрузки таблиц базы данных из нескольких файлов. Так, при использовании имен memberl.txt и member2.txt для корректной работы утилиты mysqlimport должны использоваться таблицы memberl Глава 1. Знакомство с СУБД MySQL и SQL и members. В нашем случае подойдут имена m e m b e r. l. t x t /И member. 2.txt ИЛИ member, txtl И member. txt2. Если чггатель поэкспериментировал с добавлением данных на тестовой базе данных, удалите содержимое таблиц и загрузите данные, необходимые дал ознакомления с материалом следующего раздела. Для этого прямо из сболочки выполните следующие команды. % % % % % % mysql mysql mysql mysql mysql mysql saap_db sanp_db sanp_db sanp_db sanp_db sanp_db < < < insert_president.sql insert_member. sql insert_student. sql insert_score. sql insert_event. sql insert_absence. sql " Кажды( файл содержит оператор DELETE, предназначенный для удаления всех записей, введенных в таблицу, и набор операторов INSERT для инициализации содержимого таблицы. Это ввод можно упростить и ввести команду. % cat insfft_*. sql I mysql samp_db Выборка информации Теперь, когда таблицы созданы и заполнены данными, посмотрим, что можносделать с этими данными. Оператор SELECT позволяет производить выбэрку и отображение информации из таблиц любым способом. Можно сделать выборку всех столбцов и всех строк таблицы:

mysql> SEI2CT * FROM president Или сдшать выборку одного столбца и одной строки таблицы: mysql> SEIJCT birth_date FROM president WHERE last_name = "Eisenhover" Оператф SELECT состоит из нескольких предложений (частей), которые можно сочетать в любом порядке, в зависимости от того, какая информация ребуется для выборки. Любое предложение может быть сложным или гростым, в зависимости от чего весь оператор SELECT может быть сложным или простым. Но я гарантирую, что в этой книге читатель не найдет гапросов размером со страницу, для объяснения которых потребуется еде одна страница. Общий жнтаксис оператора SELECT имеет вид:

SELECT wha to select FROM table or tables WHERE cond.tions that data must satisfy Нужно томнить, что язык SQL является языком со свободным синтаксисом, гоэтому читатель не обязательно должен следовать авторскому стилю нагосания запросов. 80 Часть I. Использование СУБД MySQL Для создания оператора SELECT необходимо определить, что требуется выбрать из таблиц. Для этой цели служат предложения FROM и WHERE, которые используются наиболее часто, и GROUP BY, ORDER BY и LIMIT, которые встречаются реже. Предложение FROM обычно присутствует в операторе SELECT, но в нем нет необходимости, если отображаются данные не из таблиц. Например, этот запрос просто отображает значения выражений, которые могут вычисляться без ссылки на таблицу, так что в предложении FROM нет необходимости.

mysql> SELECT 2-1-2, "Hello, world", VERSION() I 2+2 | Hello, world I VERSION(} I + | 4 | Hello, world | 3.23.0 alpha log | + + + При использовании предложения FROM для определения таблицы, из которой требуется произвести выборку, мы можем получить наиболее "общую" форму запроса. Для этого вместо указания конкретного столбца введем "*", что означает "все". Такой запрос выбирает и отображает все столбцы из таблицы student.

mysql> SELECT * FROM student;

I name I 1 1 I Megan Joseph Kyle Katie 1 I I 1 F M M F sex 1 student id | 1 1 1 1 1 2 3 4 1 | | | Значения всех столбцов возвращаются в том же порядке, в котором они хранятся в таблице. Этот совпадает с порядком, в котором столбцы перечислены оператором DESCRIBE student ('...' в конце распечатки показывает, что запрос возвращает больше строк, чем показано). В операторе SELECT имена столбцов можно указывать явно. Для выборки только имен учащихся нужно сделать следующий запрос.

mysql> SELECT name FROM student;

Л h I н I I I I | + Megan | Joseph | Kyle | Katie I name Для отображения нескольких столбцов в операторе SELECT можно через запятую указать имена столбцов. Этот оператор аналогичен опеГлава 1. Знакомство с СУБД MySQL и SQL ратору SELECT * FROM student, но каждый столбец указывается здесь явным образом:

mysql> SELECT name, sex, student_id FROM student;

SELECT name, student_id FROM student;

SELECT student_id, name FROM student;

Столбцы можно указывать сколько угодно раз. Имена столбцов можно указывать в любом регистре.

SELECT name, student_id FROM student;

SELECT NAME, STUDENT_ID FROM student;

SELECT nAmE, sTuDeNt_Id FROM student;

Имена таблиц и баз данных могут быть чувствительны к регистру. Все зависит от файловой системы ОС, работающей на узле сервера. На сервере, работающем под ОС UNIX, имена таблиц и баз данных чувствительны к регистру, так как имена файлов в ОС UNIX чувствительны к регистру. В ОС Windows имена файлов не чувствительны к регистру, поэтому имена таблиц и баз данных тоже будут не чувствительны к регистру. СУБД MySQL также позволяет производить выборку из нескольких таблиц. Эта тема будет обсуждена в разделе "Выборка данных из нескольких таблиц".

Определение критериев выборки Ограничение набора выбираемых оператором SELECT записей производится с помощью предложения WHERE, которым определяется набор выбираемых строк. В качестве критериев можно задавать цифровые значения.

mysql> SELECT * FROM score WHERE score > 95;

| student id | event id | score 1 1 1 1 1 5I I 11 5| 11 1 16 I Ч 3 3 6 6 6 6 Ч 97 | 1 96 1 1 100 1 1 97 1 1 98 1 1 98 1 1 iЧ Часть I. Использование СУБД MySQL Можно задавать в качестве критериев строковые значения. (Обратите внимание на то, что сравнения строк обычно не чувствительны к регистру.) mysql> SELECT last_name, first_name FROM president ->WHERE last name = "ROOSEVELT";

I last_name I first_name I I Roosevelt I Theodore I I Roosevelt I Franklin D. | + + + mysql> SELECT lastname, first_name FROM president Ч>WHERE last_name = "roosevelt";

H H + I last_name I first_name I -i + + I Roosevelt I Theodore | ] Roosevelt I Franklin D. | -i + + Или производить выборку по дате:

mysql> SELECT last_name, first_name, birth FROM president ->WHERE birth < "1750-1-1";

i last_name I Washington I Adams I Jefferson I first_name I George ] John I Thomas I birth | H H + | 1732-02-22 | | 1735-10-30 I I 1743-04-13 | 1.

Выборку можно производить по комбинации значений.

mysql> SELECT last_name, first_name, birth, state FROM president ->WHERE birth < "1750-1-1" AND (state='VA' OR state='MA');

1 last name 1 first name birth I state | I I I I Washington 1 George I Adams 1 John 1 Jefferson 1 Thomas --1- I 1732-02-22 1 VA I 1735-10-30 1 MA 1 1743-04-13 1 VA Выражения в предложениях WHERE могут содержать арифметические операторы (табл. 1.1), операторы сравнения (табл. 1.2) и логические операторы (табл. 1.3). Выражения группируются с помощью скобок. Операторы могут содержать константы, столбцы таблиц и вызовы функций. Читатель будет иметь возможность познакомиться с применением функций СУБД MySQL в запросах, кроме того, их полный список можно увидеть в приложении В, "Операторы и функции".

Глава 1. Знакомство с СУБД MySQL и SQL Таблица 1.1. Арифметические операторы Оператор Значение Сложение Вычитание Умножение Деление Таблица 1.2. Операторы сравнения Оператор < Ч = != или о >= > Значение Меньше Меньше или равно Равно Не равно Больше или равно Больше Таблица 1.3. Логические операторы Оператор AND OR NOT Значение Логическое "и" Логическое "или" Логическое отрицание При создании запроса, требующего использования логических операторов, необходимо понимать разницу между оператором логического "и" и обычным использованием слова "и" в повседневной жизни. Предположим, что требуется найти "президентов, родившихся в штате Вирджиния, и президентов, родившихся в штате Мэриленд". Обратите внимание на то, как при этом произносится "и". Поставим вопрос следующим образом: "Соответствует ли этому вопросу следующий запрос?" SELECT last_name, first_name, state FROM president WHERE state='VA' AND state='MA';

Нет. Так как запрос трактуется "выбрать президентов, родившихся как в штате Вирджиния, так и в штате Мэриленд", что совершенно бессмысленно. В английском языке этот запрос можно делать с использованием "и", но в языке SQL эти два условия объединяются оператором ок. 84 Часть I. Использование СУБД MySQL mysql> SELECT last_name, first_name, state FROM president ->WHERE state='VA' OR state='MA';

Это то, что требуется понимать в любом случае как тогда, когда человек пишет запрос сам для себя, так и тогда, когда запрос пишется для заказчика. Я советую сначала точно определить, чего они хотят. Например, вышеуказанный запрос проще сформулировать следующим образом: "Выбрать президентов, родившихся в штате Вирджиния или в штате Мэриленд". Значение NULL NULL Ч это особенное значение, потому что оно обозначает ситуацию, когда "нет значения", его нельзя выбрать как обычное значение, его нельзя сравнить с другим значением, так как это можно сделать с обычными значениями. При сравнении пустого значения с помощью обычного арифметического оператора сравнения будет получен неопределенный результат. mysql> SELECT NULL < О, NULL = О, NULL != О, NULL > о + 1 ^ н н | NULL < 0 | NULL = 0 I NULL != 0 | NULL > 0 | + -i 1 + + NULL NULL NULL NULL Более того, нельзя сравнить два значения NULL друг с другом. Это естественно Ч результат сравнения двух неизвестных значений сам не может быть известен.

mysql> SELECT NULL = NULL, NUL | NULL = NULL I NULL i NULL != NULL | NULL | L Глава 1. Знакомство с СУБД MySQL и SQL Для выборки пустых значений требуется особенный синтаксис. Вместо использования символов "=" или "!=" для проверки равенства или неравенства нужно задавать IS NULL или IS NOT NULL. Например, мы присвоили дате смерти живых президентов значение NULL. Поэтому выбрать их будет проще всего, сделав следующий запрос.

mysql> SELECT last_name, first_name FRCM president WHERE death IS NULL;

I last name I I I I I Ford Carter Reagan Bush Clinton 1 first name 1 I 1 1 1 Gerald R. James E. Ronald W. George W. William J.

1 Поиск имен, имеющих суффикс, можно осуществить, пользуясь выражением IS NOT NULL.

mysql> SELECT last_name, first_name, suffix -> FROM president WHERE suffix IS NOT NULL;

I last_name н I Carter -i | first_name I suffix | н и + | James E. | Jr. | 1 1 B СУБД MySQL версии 2.23 есть уникальный оператор сравнения "< = >", работающий даже при сравнении пустых значений. Два предыдущих запроса можно изменить, воспользовавшись этим оператором.

mysql> SELECT last_name, first_name FROM president WHERE death < = > NULL;

I last name | Ford I Carter I Reagan I Bush 1 Clinton 1 first name 1 1 I I i Gerald R. James E. Ronald W. George W. William J.

1 1 mysql> SELECT last_name, first_name, suffix -> FROM president WHERE suffix < = > NULL;

jf (. (. + I last_name + I Carter | first_name I suffix | н 1 ^ I James E. | Jr. I Часть I. Использование СУБД MySQL Сортировка результатов запроса Очень скоро пользователь замечает, что в результате команды SELECT * FROM tbl_name он получает выборку данных из нужной таблицы в том же порядке, в котором она была загружена. Нельзя полностью полагаться, что данные в таблице всегда будут храниться в нужном порядке. В процессе работы с таблицей записи модифицируются, удаляются и добавляются. Удаление записей оставляет "дыры" неиспользуемого пространства, которые СУБД MySQL попытается заполнить позже по мере добавления записей в таблицу. В принципе при выборке данных сервер не гарантирует их порядок. Для их сортировки предназначено предложение ORDER BY.

mysql> SELECT last_name, first_name, suffix FROM president Ч > ORDER BY last_name;

I last_name +I Adams I Adams I Arthur I Buchanan I first_name +I John I John Quincy | Chester A. | James | + | | | | Можно задать порядок сортировки столбца по возрастанию или убыванию с ключевыми словами ASC или DESC. Например, для сортировки имен президентов в обратном (убывающем) порядке можно сделать такой запрос.

mysql> SELECT last_name, first_name, suffix FROM president -> ORDER BY last_name DESC;

last name Wilson Washington Van Buren Tyler first name Woodrow George Martin John По умолчанию принимается порядок сортировки по возрастанию. При сортировке столбца, который может содержать пустые значения, они будут поставлены первыми при сортировке по возрастанию и последними при сортировке по убыванию. Сортировка может задаваться по нескольким столбцам, при этом любой столбец может быть отсортирован независимо от других столбцов как в возрастающем порядке, так и в убывающем. Следующий запрос производит выборку строк из таблицы president, отсортированных в убывающем порядке по названию штата, где родились президенты, и по фамилии в убывающем порядке внутри каждого штата.

Глава 1. Знакомство с СУБД MySQL и SQL mysql> SELECT last_name, irst_name, state FROM president -> ORDER BY state DESC, last name ASC;

state VT VT VA VA VA VA VA VA VA VA TX TX Ограничение количества строк результатов запроса Предложение LIMIT предназначено для ограничения количества строк, выводимых запросом. Этот элемент синтаксиса очень полезен в комбинации с предложением ORDER BY. СУБД MySQL позволяет ограничить вывод первыми п строками. Следующий запрос возвращает пять имен президентов, которым посчастливилось родиться первыми.

mysql> SELECT last_name, first_name, birth FROM president -> ORDER BY birth LIMIT 5;

H + + + I H \ I I I I ч i firstname H Washington | George Adams I John Jefferson I Thomas Madison | James Monroe I James + last_name | birth 1 + ^ 1732-02-22 1 1735-10-30 i 1743-04-13 I 1751-03-16 | 1758-04-28 | Изменив порядок сортировки на сортировку по убыванию ORDER BY b i r t h DESC, пользователь получит список пяти самых молодых президентов. Предложение LIMIT может помочь выбрать записи из середины выборки. Для этого необходимо указать два значения. Первое значение Ч это начальная запись результирующего набора, а второе значение Ч количество строк, которые будут возвращены. (Первая строка имеет номер 0, а не 1.) Этот запрос аналогичен предыдущему, но с той лишь разницей, что он возвращает пять строк начиная с одиннадцатой.

mysql> SELECT last_name, first_name, birth FROM president -> ORDER BY birth LIMIT 10, 5;

I last_name + I Tyler I first_name 1 I John | birth | 1 + | 1790-03-29 I Часть I. Использование СУБД MySQL 1 I 1 I Buchanan Pork Fillmore Pierce I James | James K. I Millard I Franklin 1 1791-04-23 1 1795-11-02 | 1800-01-07 I 1804-11- В версиях СУБД MySQL начиная с версии 3.23.2 появилась возможность задавать порядок сортировки результата в соответствии с формулой. Например, для того, чтобы выбрать произвольную строку из таблицы president, воспользуйтесь ORDER BY RANDO вместе с LIMIT.

mysql> SELECT last_name, first_name FROM president -> ORDER BY RANDO LIMIT 1 ;

+ -1 -+ | last_name | first_name | и- +-t| McKinkey | William | Подсчет и присвоение имен выводимым значениям столбцов таблиц Все предыдущие запросы генерировали результат посредством выборки значений из таблицы. СУБД MySQL также позволяет производить подсчет значений из выводимых столбцов. Последующий запрос оценивает простое выражение (константа) и более сложное выражение, включающее несколько арифметических операций и пару вызовов функций.

mysql> SELECT П, FORMAT (SQRT (3*3*4*4), О) ;

1 17 | FORMAT (SQRT (3*3*4*4),0) | Выражения могут заключать ссылки на столбцы таблиц.

mysql> SELECT CONCAT (f irst_naune, " ",lastname), CONCAT(city, ", ", state) FROM president;

4 1|.

j CONCAT(first_name, " ",last_name) | CONCATfcity, ", ", state) 4 j 1 | George Washington I John Adams I Thomas Jefferson I James Madison I Wakefield, VA I Braintree, MA I Albermarle County, VA I Port Convay, VA I I | I Этот запрос сводит имена и фамилии президентов в одну строку, разделив их пробелом, места рождения объединены в одну строку, разделенную запятой. Выражение, по которому вычисляется результат, отображается в шапке. Отсюда и большая длина столбца (это хорошо видно на предыдущем примере.) Преодолеть это неудобство помогает конструкция AS name, присваивающая столбцу псевдоним. В результате можно получить более понятный результат. Глава 1. Знакомство с СУБД MySQL и SQL mysql> SELECT CONCAT(first_name, " ",last_name) AS Name, -> CONCAT(city, ", ", state) AS Birthplace Ч > FROM president;

mysql> SELECT CONCAT (first_name, " ",last_naioe) AS "President Name", -> CONCAT (city, "/ ", state) AS "Place of Birth" Ч >FROM president;

1 President Name I I I I George Washington John Adams Thomas Jefferson James Madison 1 Place of Birth 1 1 I I Wakefield, VA Braintree, MA Albermarle County, VA Port Convay, VA Работа с датами Принципиальный момент, о котором надо всегда помнить, Ч формат представления дат в СУБД MySQL таков, что год представляется первым. Так, дата 27 июля 1999 года представляется записью "1999Ч07Ч27". Она не может быть представлена записями "07Ч27-1999" или "27Ч07Ч1999". Хотя это может быть более удобно. СУБД MySQL позволяет производить с датами следующие действия. Х Сортировка по дате. (Это мы уже видели.) Х Выбор по определенной дате или диапазону дат. Х Выделение фрагментов даты (год, месяц или день). Х Вычисление разницы между датами. Х Вычисление относительной даты. Вот несколько примеров таких операций. Выборка по определенной дате, по точной дате, по сравнению с определенной датой, сравнение значения столбца типа DATE со значением, которым вы интересуетесь.

mysql> SELECT * FROM event WHERE date = '1999Ч10-01';

I date I 1999-10- I type ! event id | J 1т i -- 6i U Часть I. Использование СУБД MySQL mysql> SELECT last_name, first_name, death Ч>FROM president;

->WHERE death >= "1970-01-01" and death < "1980-01-01";

I last name | Truman | Johnson I first name I Harry S. ! Lyndon B. I death I I 1972-12-26 I | 1973-01-22 | Для выборки по фрагменту даты также можно воспользоваться функциями YEAR (), MONTH (), DAYOFMONTH (). Например, так можно сделать выборку всех президентов, которые родились в один и тот же месяц, что и автор этой книги (март). Для этого произведем выборку всех записей с датами рождения, в которых значится третий месяц.

mysql> SELECT last_name, first_name, birth ->FROM president WHERE MONTH(birth) = 3;

I last name I Madison I Jackson I Tyler | Cleveland 1 first name I I I I James Andrew John Grover I birth I 1751-03-16 | 1767-03-15 I 1790-03-29 I 1837-03- Запрос может также содержать название месяца.

mysql> SELECT last_name, first_name, birth ->FROM president WHERE MONTHNAME(birth) = "march" Чtlast name I first name [ birth I Madison Jackson Tyler Cleveland I Jame s | Andrew I John I Grover I 1751-03-16 I 1767-03-15 | 1790-03-29 I 1837-03- Усложним запрос и сделаем выборку президентов, день рождения которых полностью совпадает с днем рождения автора.

mysql> SELECT last_name, first_name, birth ->FROM president WHERE MONTHNAME(birth) = "march" AND DAYOFMONTH(BIRTH) = 29;

+ н ^ I last_name | first_name | birth I + I Tyler H | John H | 1790-03-29 I + Результат этого запроса напоминает список "Люди, родившиеся сегодня" из раздела "Развлечения" вашей любимой газеты. Для выборки президентов, родившихся сегодня, необходимо сравнить их дни рождения со значением CURRENT_DATE (текущей даты). Глава 1. Знакомство с СУБД MySQL и SQL SELECT last_name, first_name, birth FROM president WHERE MONTH(birth) = MONTH(CURRENT_DftTE) AND DftYOFMONTH(BIRTH) = DAYOFMONTH(CURRENT_DATE) Для получения интервала между датами их можно вычитать одну из другой. Например, для выяснения того, кто из президентов жил дольше всех, необходимо из даты смерти вычесть дату рождения. Для этого с помощью функции TO_DAYS () преобразуем значения death и birth, произведем вычитание и разделим полученный результат на 365. Получим приблизительный возраст в годах.

mysql> SELECT last_name, first_name, birth, death ->FLOOR((TO_DAYS(death) - TO_DAYS(birth))/365) AS age ->FROM president WHERE death IS NOT NULL ->ORDER BY age DESC LIMIT 5;

I last name I Adams I Hoover | Truman I Madison I Jefferson 4 I first name I John I Herbert C. | Harry S. I James I Thomas I birth I death 1826-07-04 1964-10-20 1972-12-26 1836-06-28 1826-07- 1 age | 1 1 1 1 -4 | 1735-10-30 I I 1874-08-10 I I 1884-05-08 I I 1751-03-16 I I 1743-04-13 I 90 I 90 | 88 I 85 I 83 | Ч -t Функция FLOOR (), которой мы воспользовались в этом запросе, отсекает дробную часть от полученного числа лет. Получение разности между датами полезно также для определения, какой период времени разделяет эти две даты. С помощью подобных вычислений можно напоминать членам "Исторической Лиги" о необходимости обновить свое членство. Вычислите разность между датой истечения срока членства и текущей датой. Если она не превышает допустимого значения, скоро понадобится обновление. Вот запрос, делающий выборку членов, которые должны обновить членство в течение 60 дней.

SELECT last_name, first_name, expiration FROM member WHERE (TO_DAYS(expiration) - TO_DAYS(CURRENT_DATE)) < Начиная с версии 3.22 для вычитания одной даты из другой можно использовать функции DATE_ADD () и DATE_SUB (). Эти функции по дате и интервалу возвращают новую дату, например:

mysql> SELECT DATE_ADD("1970-1-1", INTERVAL 10 YEAR) ;

^ у | DATE_ADD("1970-1-1", INTERVAL 10 YEAR) | -I + I 1980-01-01 mysql> SELECT DATE_SUB("1970-1-1", INTERVAL 10 YEAR) ;

H + I DATE_SUB("1970-1-1", INTERVAL 10 YEAR) | н + I 1960-01- I Часть I. Использование СУБД MySQL Запрос, приведенный в этом разделе выше, делал выборку президентов, умерших в семидесятых годах этого столетия. Для задания даты окончания интервала использовались литералы. Перепишем этот запрос по-другому: укажем с помощью литерала начальную дату.

mysql> SELECT last_name, first_name, death ->FROM president ->WHERE death >= "1970-1-1" ->AND death < DATE_ADD ("1970-1-1", INTERVAL 10 YEAR) ;

-I-1- ц I last_name ! Truman I Johnson I first_name | death + I + + | Harry S. | 1972-12-26 | | Lyndon B. | 1973-01-22 | ++ Запрос на обновление членства можно переписать с применением ФУНКЦИИ DATE_ADD ( ). SELECT last_name, first_name, expiration FROM member WHERE expiration < DATEADD (CURRENT_DATE, INTERVAL 60 DAY) Ранее в этой главе был представлен запрос для определения тех пациентов стоматолога, кто забыл пройти регулярный профилактический осмотр.

SELECT last_name, first_name, last_visit FROM patient WHERE last_visit < DATE_SUB (CURRENT_DATE, INTERVAL 6 MONTH) Понятен ли этот запрос теперь?

Соответствие шаблону СУБД MySQL позволяет производить выборку значений, которые соответствуют определенному шаблону. При этом не надо задавать определенное значение. Для выборки можно использовать специальные операторы (LIKE и NOT LIKE) и указать строку, содержащую символызаменители. Символ "_" соответствует значению "любой одиночный символ". Символ "%" соответствует значению "любая последовательность символов" (включая и пустую последовательность). Шаблоны, удовлетворяющие LIKE и NOT LIKE, не чувствительны к регистру. Вот шаблон, соответствующий всем фамилиям, начинающимся с "W" или "w".

mysql> SELECT last_name, first_name FROM president ->WHERE lastname LIKE "W%" I last_name -I Washington I Wilson + -+ I first_name 1 -I George I Woodrow 1 - I + I | + А это ошибочный шаблон: Глава 1. Знакомство с СУБД MySQL и SQL mysql> SELECT last_name, first_name FROM president ->WHERE last_name = "%W%" Empty set (0.00 sec) Это типичная ошибка при работе с шаблонами. Такой запрос даст результат только в том случае, если существует столбец, в действительности содержащий "W%" или "w%". Вот запрос на выборку фамилий, в которых встречается 'W или V.

mysql> SELECT last_name, first_name FROM president ->WHERE lastname LIKE "%W%" 1 last name I first name | I | | I Washington | George I Woodrow 1 Wilson 1 Eisenhover I Dwight D.

Вот запрос на выборку фамилий, которые содержат точно четыре буквы.

mysql> SELECT last_name, first_name FROM president ->WHERE last_name LIKE " _ " СУБД MySQL имеет также и другую форму выборки по шаблону с помощью расширенных регулярных выражений. Регулярные выражения описаны в приложении В, "Операторы и функции".

Получение итоговых результатов Одна из самых полезных возможностей СУБД MySQL (как впрочем и всех других СУБД) Ч обработка огромных объемов данных и суммирование их. По мере ознакомления с СУБД MySQL она становится мощным союзником в деле получения итогов и сумм, особенно принимая во внимание тот факт, что это очень нудная, требующая много времени и являющаяся источником ошибок работа, особенно если делать ее вручную. Простым примером этого может служить необходимость определения значений, которые представлены в таблице. Для удаления повторений воспользуемся ключевым словом DISTINCT. Например, перечень штатов, в которых когда-либо рождались президенты США.

mysql> SELECT DISTINCT state FROM president ORDER BY state;

state | 1AK CA GA IA IL KY MA MO Часть I. Использование СУБД MySQL NC NE NH NJ NY OH PA SC TX VA VT О другом способе получения итогов очень легко догадаться Ч это использование функции COUNT ( ). Выражение COUNT ( * ) выдает количество строк, выбранных запросом. Если запрос не содержит предложения WHERE, выражение COUNT(*) будет подсчитывать количество строк, содержащихся в таблице. Этот запрос покажет, сколько было президентов в истории США. raysql> SELECT COUNT (*) FROM president;

+ -н I COUNT (*) I При наличии предложения WHERE функция COUNT ( * ) покажет, сколько строк удовлетворяет критерию выборки.

mysql> SELECT COUNT (*) FROM event WHERE type = 'Q1 ;

+ -^ I COUNT!*) I 4I Функция COUNT ( * ) подсчитывает каждую выбранную строку. Напротив, COUNT (col_riame) подсчитывает только строки, содержащие ненулевые значения в данном столбце. Этот запрос отчетливо покажет эту разницу.

mysql> SELECT COUNT (*), COUNT (suffix), COUNT (death) FROM president;

+ - нн- + I 41 I +н1| + + 36 I I COUNT (*) I COUNT (suffix) | COUNT (death) | Отсюда видно, что всего в истории США был 41 президент, только один из них имел приставку к своему имени, и большинства из них уже нет в живых. В версиях, выше 3.23.2, уже можно совмещать функцию сошгт(*) с ключевым словом DISTINCT для подсчета количества точных ответов, Глава 1. Знакомство с СУБД MySQL и SQL например, для подсчета количества штатов, в которых когда-либо рождались президенты. raysql> SELECT COUNT(DISTINCT state) FROM president;

н I I COUNT(DISTINCT state) | iI Можно производить подсчеты и по отдельным категориям. Например, общее число учащихся по вашему предмету можно определить таким запросом.

mysql> SELECT COUNT(*) FROM student;

+ ь | COONT(*) | I 1 Но сколько из них мальчиков и девочек?

mysql> SELECT COUNT (*) FROM student WHERE sex j COUNT ( * ) 1 ++ I 15 I 4-h mysql> SELECT COUNT (*) FROM student WHERE sex = ' +4 | COUNT ( * ) | Несмотря на то, что такой метод применим, он не иллюстративен и не очень подходит для случая, когда столбцы имеют несколько различных значений. Предположим, что необходимо определить, сколько президентов родилось в каждом штате. В соответствии с вышеприведенным методом сначала с помощью запроса SELECT DISTINCT state FROM president нужно узнать, сколько таких штатов, а затем сделать запрос SELECT COUNT (*) для каждого полученного штата. Очевидно, что это излишне трудоемкая процедура. К счастью, СУБД MySQL позволяет производить подсчет с помощью одного лишь запроса. Количество девочек и мальчиков можно одновременно подсчитать следующим образом.

mysql> SELECT sex, COUNT(*) FROM student GROUP BY sex;

mysql> SELECT state, COUNT(*) FROM president GROUP BY state;

state | COUNT(*) AK CA GA IA IL KY MA MO NC NE NH NJ NY OH PA SC TX VA VT I | | I | | I | I | | | I | I 1 I I 1 I 4 1 2 1 1 1 4 7 1 1 2 8 i При таком подсчете нам поможет предложение GROUP BY;

оно сообщает базе данных, каким образом группировать значения. Его отсутствие будет расценено СУБД MySQL как ошибка. Использование COUNT (*) в совокупности с GROUP BY для подсчета значений имеет ряд преимуществ при подсчете количества вхождений определенных значений столбцов. Нет необходимости знать все значения, имеющиеся в столбце, значения которого подсчитываются. Х Нет необходимости в серии последовательных запросов, достаточно одного. Х Так как результат получен одним запросом, он вполне поддается сортировке. Первые два преимущества позволяют создавать запросы более простым способом. Третье преимущество также важно, так как оно позволяет гибко манипулировать полученными результатами. При задании предложения GROUP BY результаты группируются по группируемому столбцу. Но это совсем не исключает применения группировки с помощью ORDER BY в другом порядке. Например, нужно подсчитать количество президентов по штату рождения, но отсор гировать их по убыванию, начиная со штата, в котором родилось больше всего президентов США.

mysql> SELECT state, COUNT(*) AS count FROM president ->GROUP BY state ORDER BY count DESC;

Х Глава 1. Знакомство с СУБД MySQL и SQL 4- state | COUNT!*) I VA OH MA NY NC VT TX SC NH PA KY NJ IA MO CA NE GA IL AK 7 4 4 2 2 2 1 1 1 1 1 1 1 1 1 1 1 Столбцу, который нужно отсортировать, но который является результатом вычислений, можно присвоить псевдонимы и в предложении ORDER BY ссылаться прямо на него. Это продемонстрировано в последнем запросе. Столбцу COUNT(*) присвоен псевдоним count. Можно ссылаться на такой столбец и по-другому: указав порядок его следования в выводимых данных. Перепишем этот запрос следующим образом.

SELECT state, COUNT!*) FROM president GROUP BY state ORDER BY 2 DESC Я не нахожу читабельной ссылку на столбец по его позиции. К примеру, при добавлении, удалении или изменении порядка следования столбцов в этом случае нужно помнить о необходимости проверки предложения ORDER BY и изменить номер столбца, если он менялся. Псевдонимы не приводят к таким проблемам. Это справедливо для предложения GROUP BY. При использовании предложения GROUP BY с вычисляемым столбцом ссылка на него может производиться как по его позиции в списке столбцов, так и по его псевдониму. Вот запрос, который определяет, сколько президентов США родилось в каждом месяце.

mysql> SELECT MONTH(birth) as Month, MONTHNAME(birth) as Name, ->COUNT(*) AS count ->FROM president GROUP BY Name ORDER BY Month;

SELECT MONTH (birth), MONTHNAME (birth), COUNT(*) FROM president GROUP BY 2 ORDER BY 1;

Функцию COUNT () можно указать в комбинации с ORDER BY и LIMIT для того, чтобы, например, найти четыре наиболее широко представленных штата из таблицы president.

mysql> SELECT state, COUNT (*) AS count FROM president ->GROUP BY state ORDER BY count DESC LIMIT 4;

+ -\ -+ I state | COUNT!*) | I VA | I OH | I MA | | NY I +--1 8| 7| 4] 4| + Можно поставить ограничение и без применения предложения LIMIT, анализируя только значение, выдаваемое счетчиком COUNT(*). Вот запрос, который выберет штаты, в которых родилось два и более президента США. mysql> SELECT state, COUNT (*) AS count FROM president ->GROUP BY state HAVING COUNT > 1 ORDER BY count DESC ;

| I I I I I 'I + + I state I COUNT)*) | VA OH MA NY NC VT TX -| | | | | | | н + -- h 8| 7| 4| 4| 21 2| 2| ^ - Более общий случай применения этого типа запросов Ч поиск повторений. Предложение HAVING подобно предложению WHERE, но первое применяется, в отличие от второго, тогда, когда результаты запроса уже выбраны, для того, чтобы сузить область результатов, посылаемых сервером клиенту. Глава 1. Знакомство с СУБД MySQL и SQL Кроме COUNT (), существуют и другие агрегирующие функции. Функции MIN (), МАХ (), SUM () и AVG () предназначены для вычисления минимума, максимума, суммы и среднего значений столбца соответственно. Их можно использовать одновременно. Вот запрос, который позволит производить подсчеты различных характеристик для любого теста или викторины. Здесь видно, как много подсчетов можно произвести по любому из значений. (Некоторые учащиеся отсутствуют и их оценки не попадают в подсчеты.) mysql> SELECT Ч>event_id, Ч>MIN(score) AS minimum, Ч>MAX(score) AS maximum, ->MAX(score) Ч MIN(score) + 1 AS range, ->SUM(score) AS total, ->AVG(score) AS average, ->COUNT(score) AS count Ч>FROM score ->GROUP BY event id;

Чi 1 т i I event id I minimum maximum range total 20 12 439 9 1 11 19 12 425 I 21 8 60 97 38 2425 1 3I 14 379 7 20 1 41 20 13 383 1 51 8 62 100 39 2325 1 Чi I average 1 15.1379 I 14.1667 I 78.2258 I 14.0370 I 14.1852 | 80. 1 count 1 29 I 30 1 31 I 27 1 27 1 Конечно, информация может быть более осмысленной, когда известно, откуда получены значения Ч из викторины или из теста. Для получения этой информации необходимо также обратиться к таблице event_table. Мы вернемся к этому запросу в разделе "Выборка данных из нескольких таблиц". Работать с агрегирующими функциями очень просто, так как они достаточно мощны. Но можно легко получить ошибочный результат. Рассмотрим следующий запрос.

mysql> SELECT ->state AS State, ->AVG(TO_DAYS(death) Ч TO_DAYS(birth))/365) ->FROM president WHERE death IS NOT NULL ->GROUP BY state ORDER BY Age;

-, ЧI Чr AS Age, 1 State I I I 1 I I 1 I KY VT NC OH NH NY NJ TX I I I I I I I I Age 56. 208219 58. 852055 60. 141096 62. 866145 64. 917808 69. 342466 71. 315068 71. 1 1 1 1 1 1 1 1 Часть I. Использование СУБД MySQL I MA | VA | PA | SC I CA | MO i IA J | 72.642009 | 72.822945 I 77.158904 I 78.284932 I 81.336986 I 88.693151 I 90. Этот запрос производит выборку умерших президентов, группирует их по дате рождения, определяет их возраст на момент смерти, вычисляет средний возраст по штату, а затем сортирует полученные результаты по среднему возрасту. Другими словами, запрос определяет средний возраст по штатам на момент смерти для всех президентов США, которых уже нет в живых. Что же он показывает? Он показывает только то, что вы умеете писать запросы. И ничего больше. Не все операции, которые можно осуществлять с базами данных, имеют смысл. Однако люди иногда впадают в состояние эйфории, когда вдруг понимают, что они могут "вытворять" с базами данных. В результате этого последние годы особенно бросается в глаза бешеный рост экзотерической (и совершенно бесцельной) спортивной статистики. Спортивные статистики с помощью своих баз данных вытягивают на свет все что нужно и не нужно знать о спортивных клубах. Неужели кого-то действительно может заинтересовать рекорд по перехватам мяча правого полузащитника в момент, когда его клуб вел с разницей в два мяча? Выборка данных из нескольких таблиц Все предыдущие запросы, рассмотренные нами, были получены в результате манипуляций с одной таблицей. А теперь мы приблизились к очень интересному моменту нашего повествования. Автор уже упоминал, что основное преимущество реляционных СУБД заключается в возможности ссылаться на различные объекты базы данных, что позволяет совмещать информацию и отвечать на вопросы, ответ на которые на основании данных из одной таблицы просто невозможен. В этом разделе рассказывается, как писать запросы, которые позволят это сделать. При выборке информации из нескольких таблиц пользователь производит операцию, которая называется объединение (join). Такой термин здесь применяется из-за того, что результат такого запроса получен в результате объединения информации, взятой из одной таблицы, с информацией, взятой из другой таблицы. Рассмотрим пример. Ранее в разделе "Таблицы проекта "Учет успеваемости"" запрос такого рода был представлен без объяснений. Теперь пришло время и для них. В действительности это объединение является трехуровневым объединением. Таким образом, разработаем его в два этапа. На первом этапе создадим запрос выборки результатов тестов для определенной даты: Глава 1. Знакомство с СУБД MySQL и SQL mysql> SELECT student_id, date, score, type Ч>FKOM event, score ->WHERE date = "1999-09-23" Ч >AND event. eventid = score. event i student id | date 1 score I type 15 12 11 13 18 Q Q Q Q 2 3 5 1 | 1999-09-23 1 1999-09-23 1 1999-09-23 ] 1999-09-23 | 1999-09- 23 Q Запрос работает так: сначала делается выборка записи события по заданной дате, а идентификатор события используется для поиска результатов по тому же идентификатору события. В результате работы запроса отображаются: идентификатор учащегося, результат, дата и тип события всех записей с совпадающими записями событий. Этот запрос отличается от всех уже записанных нами двумя важными аспектами. Х Поиск производится из нескольких таблиц и в предложении FROM перечислено две таблицы. FROM event, score В предложении WHERE определено, что таблицы event и score объединяются по значениям event_id. WHERE... event.event_id = score. event_id;

Х Обратите внимание на синтаксис обращения к столбцу вида tbljiame.col_name. В нем обязательно указано имя таблицы. (Столбец event_id присутствует в обеих таблицах, поэтому здесь упоминание имени столбца без имени таблицы приводит к неоднозначности.) Другие столбцы, участвующие в запросе (date, score, type), можно указывать без имени таблицы. Для этого запроса они уникальны и их упоминание не вызовет неоднозначности. Однако я предпочитаю указывать имена таблиц в запросах. Это делает его более прозрачным. В полностью квалифицированном запросе мы получим:

mysql> SELECT score.student_id, event.date, score.score, event.type Ч>FROM event, score ->WHERE event.date = "1999-09-23" Ч>AND event.event_id = score.event_id;

С этого момента будет использоваться полностью квалифицированная форма. Потом доработаем запрос таким образом, чтобы он выбирал имя учащегося из таблицы student. (Результат первого запроса выводит нам только идентификатор учащегося (поле student_id), но имя учащегося более информативно.) Это можно осуществить с помощью столбца 102 Часть I. Использование СУБД MySQL student_id, присутствующего в таблицах score и student. Получим запрос следующего вида. mysql> SELECT student.name, event.date, score.score, event.type ->FROM event, score, student ->WHERE event.date = "1999-09-23" Ч>AND event.event_id = score.event_id;

Ч>AND score.student id = student.student id;

| name | Megan | Joseph I Kyle | Abby | Nathan I date [ score I type | I 1999-09-23 | I 1999-09-23 | I 1999-09-23 | I 1999-09-23 | | 1999-09-23 | 15 12 11 13 IQ 1 i 1 I Q Q Q Q 1 1 1 1 | Этот запрос отличается от предыдущего следующими аспектами. В предложение FROM добавляется таблица student. Она необходима в дополнение к таблицам event и score. Х Теперь столбец student_id без упоминания имени таблицы становится неоднозначным. Необходима нотация score. student_id или student. student_id, чтобы конкретизировать столбец. (Это справедливо, даже если читатель имеет привычку не именовать таблицы в запросах-объединениях.) Х В предложении WHERE добавлено условие соответствия записей таблиц score и student no значениям столбцов student_id: WHERE... score.student_id = student.student_id;

Х Запрос выводит имя учащегося, а не его идентификатор. (Конечно, при желании можно выводить оба поля сразу.) С помощью этого запроса можно запросить любую дату и получить результаты по этим датам. Совсем необязательно знать идентификационные номера учащихся или экзаменов. СУБД MySQL автоматически берет на себя всю заботу об определении соответствующих значений и их использовании для поиска соответствия между строками таблиц. Еще одной задачей проекта учета успеваемости является учет посещаемости. Отсутствие учащихся на занятиях регж грируется по идентификатору учащихся и дате, хранящимся в таблице absence. Для того чтобы получить имена >чащихся (а не только их идентификаторы), нам необходимо объединить таблицы absence и student. Это можно сделать по значению student_id. Вот запрос, выводящий идентификатор учащихся и их имена вместе с суммой занятий, пропущенных утщимися:

mysql> SELECT student.id, student.name, Ч>COUNT(absence.date) as absences Ч>FROM student, absence Ч>WHERE student.student_id = absence.student_id;

Х Глава 1. Знакомство с СУБД MySQL и SQL ->GROUP BY student.student id;

1 student id I name 1 absences | I I I | I 1 1 2 1 1 1 1 1 1 1 1 1 31 51 10 1 17 I 20 Kyle Abby Peter Will Avrey I' Совет if Несмотря на то, что здесь в предложении GROUP BY указано имя таблицы, это совсем не обязательно. Предложение GROUP BY имеет отношение к г;

столбцам, указанным в списке выборки (на первых двух строках запроса). А || там указан только один столбец student_id, поэтому СУБД MySQL "знает", || какая таблица имеется в виду. Это правило справедливо также и для столб\ цов, перечисленных в предложениях ORDER BY. Этот запрос выдает отличный результат тогда, когда мы стремимся узнать имена учащихся, которые имеют пропуски занятий. Но если такой список предоставить педагогическому совету, то может последовать совершенно естественный вопрос: "А как же остальные учащиеся? Нам необходимо оценить результаты и оставшихся учеников". Это совсем другой вопрос. Это значит, что необходимо получить количество пропущенных занятий даже для учащихся, которые пропусков не имеют. Этот запрос отличается от предыдущего. Для того чтобы на него ответить, воспользуемся конструкцией LEFT JOIN в предложении WHERE. Она указывает СУБД MySQL делать выборку всех строк из таблицы, указанной в предложении слева (слева от слов LEFT JOIN). Назвав сначала таблицу student, мы получим перечень всех учащихся, даже тех, кто не представлен в таблице absence. Теперь запрос будет выглядеть следующим образом:

mysql> SELECT student.id, student.name, Ч>COUNT(absence.date) as absences ->FROM student LEFT JOIN absence Ч>WHERE student.student_id = absence.student_id;

->GROUP BY student.student id;

~ " Чг I student 1 1 id 1 name 1 I Megan 2 1 Joseph 3 1 Kyle 4 1 Katie I absences | I | I I 1 1 1 5 1 Abby 6 1 Nathan 7 1 Liesl I | 0 01 1! 01 11 01 Ранее в разделе "Получение итоговых результатов" был показан пример запроса, который выдавал цифровые данные на основе информации, 104 Часть I. Использование СУБД MySQL J содержащейся в таблице score. Результат этого запроса содержал идентификатор события, но не мог содержать дату получения результата теста или его тип. Теперь мы уже знаем, каким образом можно объединить таблицы score и event, чтобы получить даты и типы результатов.

mysql> SELECT Ч> event.date, event.type, Ч>MIN(score.score) AS minimum, Ч>MAX(score.score) AS maximum, Ч>MAX(score.score) Ч MIN(score.score) + 1 AS range, ->SUM(score.score) AS total, Ч>AVG(score.score) AS average, ->COUNT(score.score) AS count Ч>FROM score, event Ч>WHERE score.event = event.event ->GROUP BY event date;

I date I type | minimum j maximum | range | total I average 1 count 1 9 8 60 7 8 62 I 1 | I 1 | 20 I 19 I 97 | 20 | 20 | 100 | 12 I 12 | 38 | 14 | 13 I 39 | 11999-09-16] Q 11999-09-231 Q 11999-10-О - Т 11999-09-031 Q 11999-09-061 Q 11999-09-09] Т 425 2425 379 383 I 15.1379 | I | | I i | | 14.1667 | 78.2258 | 14.0370 | 14.1852 | 80.1724 | 29 30 31 27 27 1 1 1 1 Для получения суммарных значений на основании данных из столбцов различных таблиц можно также воспользоваться агрегатными функциями COUNT () и AVG (). Вот запрос, который определяет число результатов и их среднеарифметическое для всех комбинаций дат событий и пола учащихся. mysql> SELECT event.date, student.sex, Ч>COUNT(score) AS count, AVG(score) AS average, Ч>FROM event, score, student Ч>WHERE event.event_id = score.event ->AND score.student_id = student.student_id ->GROUP BY event_date, student.sex;

н I date -т \ и + Ч, Г I I I I 1 I [ I 1 I -!

1999-09-03 1999-09-03 1999-09-06 1999-09-06 1999-09-09 1999-09-09 1999-09-16 1999-09-16 1999-10-01 1999-09- -A 1 1 1 1 1 1 1 1 1 F M F M F M F M F M I I | | | | | | | I 14 15 14 15 15 16 13 14 14 1 1 1 1 1 1 1 1 1 ~I 14 15 14 13 77 79 15 12 77 6429 6000 7143 6875 4000 0000 3077 8571 7857 \ Аналогичный запрос можно сделать для проекта учета успеваемости. Например, для вычисления суммарного результата каждого учащегося в конце семестра: Глава 1. Знакомство с СУБД MySQL и SQL SELECT student.student_id, student.name, SUM(score.score) AS total, COUNT(score.score) AS n FROM event, score, student WHERE event.event_id = score.event AND score.student_id = student. studentid GROUP BY score.student_id, student. student_id ORDER BY total;

Объединение можно производить не только между различными таблицами, но и таблицы самой с собой. Например, можно определить есть ли президенты, которые родились в одном и том же месте:

mysql> SELECT pi.last_name, pi.first_name, pi.city, pi.state ->FROM president AS pi, president AS p2 ->WHERE pi.city = p2.city AND pi.state = p2. state Ч>AND (pi.last_name != p2.1ast_name AND pi.first_name ' = p2.first_name) ->ORDER BY state, city, last_name;

-I (. 1 -I + I last_name I first_name I city I state | + 1 + 1 H I Adams I John Quincy I Braintree | MA I I Adams I John | Braintree | MA I Этот запрос имеет две особенности. Х Здесь требуется ссылаться на два экземпляра одной и той же таблицы. Для этого создается два экземпляра одной и той же таблицы с различными псевдонимами (pi, p2). Х Запись каждого президента соответствует самой себе. Но они не нужны в отклике запроса. Вторая строка предложения WHERE позволяет избежать этого. Аналогичный запрос делает выборку президентов, родившихся в один и тот же день. При этом нельзя сравнивать даты рождения непосредственно из таблицы. Для сравнения дня и месяца рождения воспользуемся ФУНКЦИЯМИ MONTH() И DAYOFMONTH{). mysql> SELECT pi.last_name, pi.first_name, pi.birth Ч>FROM president AS pi, president AS p2 ->WHERE MONTH (pi.birth) = MONTH (p2.birth) ->AND DAYOFMONTH (pi. birth) = DAYOFMONTH (p2.birth) Ч>AND (pi.last_name != p2.1ast_name Ч>AND pi.first_name != p2.first_name) ->ORDER BY pl.last_name;

1 last name I Harding I Polk 1 first name ! birth Warren G. James K.

I 1 1865-11- 1795-11- Запрос можно упростить, если вместо комбинации функций MONTH () и DAYOFMONTH () воспользоваться функцией DAYOFYEAR (), но это даст 106 Насть I. Использование СУБД MySQL некорректные результаты при сравнении дат високосных и невисокосных годов. Вот таким образом объединение позволяет манипулировать информацией из таблиц, имеющих некую смысловую взаимосвязь, но логику этих связей знает только сам создатель запроса. СУБД MySQL не "знает", есть ли вообще что-то общее между объединенными таблицами. Например, можно объединить таблицы event и president для того, чтобы определить, проводились ли какие-либо викторины или тесты на тему дней рождения президентов.

mysql> SELECT president.last_name, president.first_name, Ч>president.birth, event.type Ч>FROM president, event ->WHERE MONTH(president.birth) = MONTH(event.birth) ->AND DAYOFMONTH(president.birth) = DAYOFMONTH(event.birth);

ХI + + + + I last_name | first_name | birth | type I I Carter + | James E. + | 1924-10-01 | Т + + I + Оказывается, такое возможно. Ну и что? А то, что СУБД MySQL может выдавать результаты независимо от того, имеют они смысл или абсолютно бессмысленны. Результаты запроса необязательно имеют пользу и какой-то смысл только потому, что они получены с помощью компьютера. К счастью, человек все еще должен думать и понимать, что он делает. Удаление и модификация существующих записей Иногда требуется избавиться от определенных записей или изменить их содержимое. Для этой цели существуют операторы DELETE и UPDATE. Оператор DELETE имеет следующий синтаксис:

DELETE FROM tblname WHERE какие записи удалить Предложение WHERE определяет, какая именно запись удаляется. Она может опускаться, но в этом случае будут удалены все записи в таблице! Это значит, что чем проще оператор DELETE, тем он "опаснее": DELETE FROM tbl_name Этот запрос стирает все содержимое таблицы. Будьте бдительны! При удалении определенных записей для указания этих записей воспользуйтесь предложением WHERE. Совсем так как в операторе SELECT. Например, для того, чтобы удалить из таблицы president всех президентов, родившихся в штате Огайо, выведите следующую команду.

mysql> DELETE FROM president WHERE state = OH;

Query OK, 7 rows affected (0.00 sec) Глава 1. Знакомство с СУБД MySQL и SQL Ограничение, накладываемое на предложения WHERE в операторах DELETE, заключается в том, что там можно указывать только столбцы из таблицы, записи которой удаляются. Перед использованием предложения WHERE в операторе DELETE советую проверить его работу в операторе SELECT. He мешает убедиться, какие записи будут удалены оператором DELETE. Предположим, что нужно удалить запись о президенте Теодоре Рузвельте. Выполнит ли следующий запрос эту задачу? mysql> SELECT last_name, first_name, FROM president Ч>WHERE last name = 'Roosevelt'," I last name I Roosevelt I Roosevelt I first name I Theodore 1 | Franklin D. Этот результат показывает, что требуется более серьезная детализация запроса.

mysql> SELECT last_name, first_name, FROM president ->WHERE last_name = 'Roosevelt' AND first_name = 'Theodore';

-I -ц --1 | last_name I first_name | н -- H + I Roosevelt I Theodore I Теперь нам известно правильное предложение WHERE. Таким образом правильный оператор будет иметь вид:

mysql> DELETE FROM president Ч >WHERE last_name = 'Roosevelt' AND first_name = 'Theodore';

Правда, операция удаления записи требует много предварительной работы? Семь раз отмерь, один раз отрежь! (На этом не стоит экономить. Сэкономить можно на копировании, вставке или методах редактирования строк. Более подробно об этом читайте в разделе "Как работать с mysql".) Для модификации уже существующих записей можно воспользоваться оператором UPDATE. Он имеет следующий синтаксис:

UPDATE FROM tbl_name SET какой столбец изменить WHERE какую запись изменить Здесь предложение WHERE ведет себя так же, как предложение WHERE для оператора DELETE. Оно может опускаться, но в этом случае изменения будут сделаны во всех записях таблицы] Вот запрос, который изменит имена всех учащихся из таблицы student на имя "George":

mysql> UPDATE student SET name = "George";

Часть I. Использование СУБД MySQL Очевидно, что необходимо быть достаточно осторожными с такими запросами. К записям, которые подвергаются модификации, необходимо подходить более детально. Предположим, что недавно в "Историческую Лигу" вступил новый член. При этом были заполнены только отдельные столбцы:

mysql> INSERT member (last_name, first_name) -> VALUES('York', 'Jerome');

После этого стало понятно, что была пропущена дата окончания срока членства. Это легко поправимо:

mysql> UPDATE member -> SET expiration='2001-7-20' Ч> WHERE last_name = 'York1 AND first_name = 'Jerome';

Можно модифицировать несколько столбцов одновременно. Этот запрос изменит адреса электронной и обычной почты одновременно:

mysql> UPDATE member Ч> SET email = 'jeromey@aol.com', street = '123 Elm St', city = 1 Any town', -> state = 'NY', zip='01003' Ч> WHERE last_name = 'York' AND first_name = 'Jerome';

Можно присвоить значениям столбцов пустое значение (если столбец их имеет). Допустим, Джером решил заплатить большой членский взнос, что позволяет ему стать "пожизненным" членом. Это можно зафиксировать, установив дату истечения срока полномочий в NULL (никогда не наступает):

mysql> UPDATE member Ч> SET expiration=NULL Ч> WHERE last_name = 'York' AND first_name = 'Jerome';

Для оператора UPDATE так же, как и для оператора DELETE верно утверждение, что нужно проверять правильность предложения WHERE с помощью оператора SELECT. Если ваш критерий выборки слишком широк, то модифицируется слишком много записей, если слишком узок Ч слишком мало. Для тех читателей, которые в процессе ознакомления с этими разделами пробовали все описанные шаги на тестовой базе данных samp_db, перед тем, как приступить к ознакомлению со следующим разделом, рекомендуем отменить все изменения, сделанные в таблицах базы данных. Проще всего это сделать, перезагрузив все таблицы в соответствии с рекомендациями, данными в разделе "Дополнение таблиц". Модификация структуры таблиц Помните, как была создана таблица member базы данных "Историческая Лига"? Там недоставало столбца "членский номер". Автор тогда пообещал воспользоваться оператором ALTER TABLE. С помощью этого опеГлава 1. Знакомство с СУБД MySQL и SQL ратора можно переименовывать, добавлять и удалять таблицы и т. д. Здесь будет показан только один пример того, как можно добавить новый столбец. На что способен в действительности оператор ALTER TABLE, можно прочитать в главе 3, "Синтаксис и использование языка SQL". Основной целью добавления столбца с членским номером в таблицу member была уникальность значений. Здесь будет уместен параметр AUTO_INCREMENT, так как это позволит создавать уникальные номера автоматически. Такая спецификация будет иметь вид:

member_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY Синтаксис оператора ALETR TABLE аналогичен. Запустите запрос:

mysql> ALTER TABLE member ->ADD member_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

Теперь у нас есть новый столбец для хранения членских номеров. Как присваиваются номера существующим записям? СУБД MySQL автоматически присваивает столбцу значения по умолчанию. В случае объявления столбца с параметром AUTO_INCREMENT в каждой новой строке генерируется новый последовательный номер. Как работать с mysql В этом разделе описываются приемы эффективной работы с клиентской программой mysql. Показано, как просто подключиться к серверу и вводить запросы, копируя их из одного черновика. Упрощение процесса подключения Очевидно, что при вызове mysql необходимо указать такие параметры подключения, как имя узла, имя пользователя или пароль. Эта процедура требует длительного ввода и очень быстро может вас утомить. Вот несколько способов упростить эту процедуру. Х Храните параметры подключения в файле конфигурации. Х Воспользуйтесь возможностью повторения команд из журнала регистрации. Х С помощью псевдонима или сценария оболочки задайте ярлык командной строки mysql.

Использование конфигурационных файлов Начиная с версии 3.22 появилась возможность сохранять параметры соединения в конфигурационном файле. В этом случае нет необходимости вводить параметры каждый раз при запуске mysql. Эти параметры используются также и в других клиентских приложениях, таких как, 110 Часть I. Использование СУБД MySQL например, mysqlimport. Конфигурационный файл упростит процесс ввода команд. Создадим файл Ч /. m y. c n f (т.е. файл под именем m y. c n f в вашем корневом каталоге). Этот файл является текстовым файлом, который можно создать с помощью простейшего текстового редактора. Его содержимое должно выглядеть следующим образом: [client] host=serverhost user=yourname password=yourpass Строка [client] идентифицирует начало группы опций данного клиента. Каждая последующая строка считывается СУБД MySQL как значение опции клиентской программы. Завершается информация концом файла или началом группы опций другого клиента. Здесь нужно заменить serverhost, yourname и yourpass именем вашего сервера, вашим именем и паролем. Например, мой.my.cnf выглядит следующим образом:

[client] host=pit.viper.snake.net user=paul password=secret Здесь является обязательной только строка [client]. Строки, определяющие значения параметров, могут быть опущены;

можно указывать только те, которые необходимы. Например, если ваше имя пользователя в базе данных совпадает с именем пользователя ОС UNIX, то его можно не указывать и не включать в строку user. После создания файла my. cnf рекомендуется ограничить к нему доступ: %chmod 600.my.cnf При работе в ОС Windows конфигурационный файл содержит те же команды и имя C : \ m y. c n f, однако запуск команды chmod в этой операционной системе невозможен. Такая возможность в версиях меньше 3.22 отсутствует. В частности, такая возможность отсутствует в бесплатной дистрибуции СУБД MySQL для ОС Windows. Возможность работы с конфигурационными файлами есть в зарегистрированных версиях СУБД MySQL для ОС Windows. Кроме того, с Web-узла MySQL можно загрузить новые версии клиентских программ, работающих с конфигурационными файлами. Более подробную информацию о конфигурационных файлах можно найти в приложении Д, "Программы MySQL".

Использование журнала регистрации Такие оболочки как csh, tcsh и bash запоминают отработанные команды в журнале регистрации. Это позволяет вызывать команды непосредственно из журнала регистрации без их повторного ввода. НаГлава 1. Знакомство с СУБД MySQL и SQL пример, повторить уже один раз введенную команду mysql можно следующим образом: % !ту Символ "!" сообщает оболочке, что необходимо найти последнюю команду, которая начинается с "ту", и повторить ее так, как если бы ее ввели с клавиатуры. Некоторые оболочки позволяют просматривать историю вверх и вниз с помощью клавиш <Т> и <4> (или комбинации и ). Таким образом выбирается нужная команда, после чего для ее активизации нажимается клавиша . Оболочки tcsh и bash имеют такую возможность. Для того чтобы узнать больше о возможностях оболочки, просмотрите документацию и по ней. Псевдонимы и сценарии Присвоение псевдонима позволит вызывать большую командную строку с помощью небольшой команды. Например, создадим в оболочках csh и tcsh псевдоним samp_db, используя следующую командную строку: alias samp_db 'mysql Чh pitЧviper.snake.net Чu paul Чp samp.db' Оболочка bash имеет другой синтаксис для команды создания псевдонима: alias samp_db='mysql Чh pitЧviper.snake.net Чu paul Чp samp.db' Создание псевдонима уравняет в функциональности такие две команды: samp.db mysql Чh pitЧviper.snake.net Чu paul Чp samp.db Совершенно очевидно, что первую командную строку значительно проще напечатать, чем вторую. Для того чтобы псевдоним активизировался при каждой регистрации, добавьте команду alias в ваш стартовый файл (например,.cshrc для оболочки csh или.bash_profile для оболочки bash). Еще одной формой ярлыка является сценарий, который будет выполняться программой mysql. В ОС UNIX файл сценария, являющийся эквивалентом псевдонима samp_db, выглядит следующим образом: #! /bin/sh exec mysql Чh pit-viper.snake.net -u paul Чp samp.db Для того чтобы этот сценарий, назовем его samp_db, заработал, необходимо сделать его исполнимым, используя команду chmod +x samp_db). Теперь можно, напечатав samp_db, запустить клиентское приложение mysql. В ОС Windows для этой цели используются пакетные файлы. Назовем такой файл samp_db.bat и введем туда такую строку:

mysql Чh pitЧviper.snake.net Чu paul Чp samp.db Часть I. Использование СУБД MySQL Этот файл можно запустить из консоли DOS или щелкнув дважды на соответствующей пиктограмме. Чтобы подключиться к нескольким узлам для доступа к нескольким базам данных, можно создать несколько псевдонимов или сценариев, каждый из которых будет активизировать mysql с различными параметрами.

Упрощение процесса создания запросов Программа mysql Ч это очень удобный инструмент взаимодействия с базой данных, но ее интерфейс больше всего подходы,Д,/;

- _ДД.. ротких запросов в одну строку. Программа mysql сама по себе ничего не "знает" о длинных запросах, занимающих несколько строк. Такие запросы очень трудно создавать. Очень огорчительно после длительного ввода запроса обнаружить, что он содержит синтаксическую ошибку и его надо переделывать. Вот несколько приемов, которые позволят избежать ненужного ввода и перепечатывания. Х Для ввода строк в mysql пользуйтесь текстовым редактором. Х Пользуйтесь возможностями копирования и вставки. Х Работайте с mysql в пакетном режиме. Х Избегайте использовать оператор INSERT для создания новых записей. Используйте для этого существующие данные. Для ввода строк в mysql пользуйтесь текстовым редактором Программа mysql имеет встроенную библиотеку для редактирования. В процессе ввода командных строк ими можно манипулировать, вызывать уже введенные строки, повторять их, модифицировать. Это удобно. В случае ошибки при вводе команды, можно вернуться назад и в переделах строки внести исправление. Аналогичным образом можно повторить запрос, который оказался ошибочным. (Эта задача существенно упрощается, если запрос был введен одной строкой.) В табл. 1.4 приведены наиболее часто используемые комбинации клавиш для работы с текстовым редактором mysql. Полный перечень таких комбинаций можно найти в диалоговой версии руководства по использованию редактора Headline. Это руководство включено и в дистрибуцию Headline, доступную на Web-узле GNU проекта h t t p : / / w w w. g n u. o r g /. Вот пример, который отлично иллюстрирует простоту редактирования вводимых строк. Предположим, что была введена следующая ошибочная командная строка:

mysql> SHOW COLUMNS FROM president;

Глава 1. Знакомство с СУБД MySQL и SQL Из этой таблицы видно, что таблицы, созданные в формате ISAM, не могут иметь пустых значений в индексируемом столбце. Кроме того, недопустимо индексирование столбцов типа BLOB и TEXT. Формат MylSAM снимает эти ограничения. Одним из проявлений этих различий для этих двух типов столбцов является то, что в зависимости от версии СУБД MySQL индекс создать будет просто невозможно. Например, в СУБД MySQL до версии 3.23 работает только метод ISAM. Это не позволяет индексировать столбцы, содержащие пустые значения. В версии 3.23 возможен экспорт старых таблиц из формата ISAM в формат My ISAM. Такую возможность предоставляет оператор ALTER TABLE. Это позволит воспользоваться новыми возможностями индексирования.

ALTER TABLE tbl_name TYPE = MYISAM Создание индексов Индексы можно создавать в процессе создания таблицы оператором CREATE TABLE. С ПОМОЩЬЮ операторов CREATE INDEX И ALTER TABLE можно создавать индексы для уже существующих таблиц. Оператор CREATE INDEX был добавлен в версии 3.22, в более ранних версиях для этой цели можно воспользоваться оператором ALTER TABLE. Одной из важнейших характеристик индекса является возможность содержать повторяющиеся значения. Если нет, индекс создается как PRIMARY KEY или UNIQUE. Для индекса, содержащего один столбец, это гарантирует отсутствие повторений значений данного столбца во всей таблице. Для индекса, содержащего несколько столбцов, это гарантирует отсутствие повторения комбинаций значений данных столбцов во всей таблице. Индексы PRIMARY KEY И UNIQUE Подобны. Индекс PRIMARY KEY ЯВляется индексом UNIQUE с именем PRIMARY. Это означает, что таблица может иметь только один индекс PRIMARY KEY, потому что двух индексов одной таблицы с одинаковым именем быть не может. Можно создать несколько индексов UNIQUE. Создать индекс для существующей таблицы можно с помощью операторов ALTER TABLE ИЛИ CREATE INDEX. При ЭТОМ СЛСДуеТ уЧССТЬ, ЧТО возможности оператора ALTER TABLE шире. В СУБД MySQL с его помощью можно создать обычный индекс, индекс UNIQUE или индекс PRIMARY KEY.

ALTER TABLE tbl_name ADD INDEX index_name (column_list) ALTER TABLE tbl~name ADD UNIQUE index_name (column_list) ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) Здесь tbl_name Ч имя индексируемой таблицы, column_list Ч список индексируемых столбцов. Если индекс состоит из нескольких столбцов, они разделяются в списке запятыми. Имя индекса index_name можно не указывать. В случае его отсутствия СУБД MySQL присвоит инГлава 3. Синтаксис и использование языка SQL дексу имя первого индексируемого столбца. Оператор ALTER TABLE позволяет создавать сразу несколько индексов одним оператором. В СУБД MySQL с помощью оператора CREATE INDEX можно создать только обычный индекс и индекс UNIQUE.

CREATE UNIQUE INDEX index_name ON tbl_name (column_list) CREATE INDEX index_name ON tbl_name(column_list) Здесь tbl_name, index_name и column_list имеют значение, аналогичное значению, принятому для оператора ALTER TABLE. Но в этом операторе необходимо указывать имя индекса. С помощью оператора CREATE INDEX индекс PRIMARY KEY создать нельзя. При создании индекса в момент создания таблицы используется синтаксис, аналогичный синтаксису оператора ALTER TABLE, но предложения, отвечающие за создание индекса, указываются в том месте, где объявляются столбцы таблицы:

CREATE TABLE tbl name INDEX index_name (column_list), UNIQUE index_name (column_list) PRIMARY KEY (column list) Как и для оператора ALTER TABLE имя индекса здесь можно не указывать. СУБД MySQL в таком случае присвоит имя сама. Есть одно исключение, позволяющее задавать индекс прямо при объявлении столбца, Ч это можно делать только для индекса PRIMARY KEY, содержащего один столбец:

CREATE TABLE my_tbl ( i INT NOT NULL PRIMARY KEY ) Этот оператор эквивалентен следующему:

CREATE TABLE my_tbl ( i INT NOT NULL PRIMARY KEY (i) ) Все примеры создания таблиц предусматривают указание NOT NULL для индексируемых столбцов. Этого правила необходимо придерживаться для таблиц, созданных методом ISAM, где индексируемые столбцы не могут содержать пустых значений. Для таблиц, созданных методом My ISAM, индексируемые столбцы могут иметь пустые значения. Но это справедливо только тогда, когда индекс не является индексом PRIMARY KEY. Возможно индексирование по префиксу столбца строкового типа (первые п символов строки). При этом спецификация меняется с 200 Часть I. Использование СУБД MySQL column_list на columnlist (n). Например, перрый из операторов внизу использует для создания индекса столбцы полностью. А второй оператор, хоть и аналогичен первому, но использует для создания индекса только часть столбца:

CREATE TABLE my_tbl ( name CHAR (30), address CHAR (60), INDEX (name, address) CREATE TABLE my_tbl ( name CHAR (30), address CHAR(60), INDEX (name (10), address (20)) ) В некоторых случаях индексирование по префиксу может оказаться очень кстати. Длина строки индекса ограничена, поэтому для индексирования по i строке, длина которой выходит за допустимый диапазон, такая возможность будет уместной. Индексирование по префиксу очень кстати при индексировании по столбцу типа BLOB или TEXT в таблицах формата My ISAM. Индексирование по префиксу накладывает определенные ограничения на возможность внесения изменений в длину таких столбцов. Нельзя делать их длину меньше длины индексируемого префикса. Это можно сделать, только удалив предварительно индекс и воссоздав его потом с коротким префиксом.

Удаление индексов Удаление индексов производится операторами DROP INDEX или ALTER TABLE. Аналогично оператору CREATE INDEX, оператор DROP INDEX внутренне обрабатывается как оператор ALTER TABLE. Он был впервые введен в СУБД MySQL версии 3.22. Операторы удаления индексов имеют следующий синтаксис:

DROP INDEX index_name ON tbl_name ALTER TABLE tbl_name DROP INDEX index_name A.LTER TABLE tbl_name DROP PRIMARY KEY Два первых оператора идентичны. Третий используется только для удаления индекса PRIMARY KEY. В последнем случае нужно указывать имя индекса, так как таблица может иметь только один индекс с таким именем. Если такой индекс создан не был, а таблица имеет один или несколько индексов UNIQUE, будет удален первый из них. Удаление столбцов из таблицы влияет на индексы. Удаляя столбец из таблицы, вы тем самым удаляете этот столбец из индекса. Удаляя все индексируемые столбцы из таблицы, вы удаляете весь индекс.

Глава 3. Синтаксис и использование языка SQL Оператор ALTER TABLE Оператор ALTER TABLE в СУБД MySQL обладает достаточно универсальными возможностями. Мы уже демонстрировали несколько вариантов его использования (создание и удаление индексов, преобразование таблицы из одного формата в другой). В этом разделе будут продемонстрированы и другие "способности" этого оператора. Полный синтаксис оператора ALTER TABLE приведен в приложении Г, "Синтаксис SQL". Оператор ALTER TABLE может пригодиться в том случае, когда становится очевидно, что старая структура таблицы уже не соответствует текущим задачам. Появилась какая-то дополнительная информация, или таблица содержит данные, ставшие уже избыточными. Существующие столбцы оказались слишком малы, или опыт работы с таблицей показал, что они непредусмотрительно были объявлены слишком большими и для экономии пространства и оптимизации запросов их требуется уменьшить. Возможно, просто была допущена ошибка в имени столбца при создании таблицы с помощью оператора CREATE TABLE. Вот несколько таких ситуаций. Х Вы ведете на Web-сервере анкету и сохраняете результаты каждого опроса как запись таблицы. Затем вы решили модифицировать анкету и добавить в нее несколько вопросов. Для этого необходимо добавить столбцы в таблицу для того, чтобы сохранять новые вопросы. Х Предположим, что вы участвуете в научном проекте. Каждому эксперименту вы присваиваете номер и сохраняете его в столбце с параметром AUTO_INCREMENT. По всем оценкам, исследования должны длиться достаточно долго. Количество регистрирующих записей должно составлять около 50000. Вы совершенно справедливо приняли решение для хранения номеров опытов выбрать тип UNSIGNED SMALLINT, который может хранить до 65535 значений. Однако по полученным результатам проект был признан успешным и его финансирование возобновилось. По предварительным оценкам может быть сделано еще 50000 записей. Необходимо изменить имеющийся тип столбца на более вместительный. Х Размеры столбцов могут меняться и в другую сторону. Предположим, был создан столбец типа CHAR ( 2 5 5 ), но в процессе работы с таблицей оказалось, что длина значений в этом столбце не превышает 100 символов. Длину столбца можно уменьшить. Синтаксис оператора ALTER TABLE имеет следующий вид:

ALTER TABLE tbl_name action,...

Спецификатором action определяется операция, которая будет произведена над таблицей. Таким образом, в СУБД MySQL был расширен диапазон действий оператора ALTER TABLE. Это удобно и уменьшает ра202 Часть I. Использование СУБД MySQL боту по вводу с клавиатуры, но более веской причиной этого расширения является то, что невозможно поменять таблицу со строками переменной длины на таблицу со строками фиксированной длины, не изменив одновременно типы всех столбцов с типа VARCHAR на тип CHAR. Ниже приведены новые возможности оператора ALTER TABLE. Х Переименование таблицы. Это просто, достаточно указать новое и старое имя: ALTER TABLE tbl_name RENAME AS newtblname Для СУБД MySQL 3.23, в которой впервые появились временные таблицы, переименование временных таблиц в имена, которые уже существуют в базе данных, "прячет" существующие таблицы данных на время существования временных таблиц. Аналогично этому, при создании временной таблицы "прячется" таблица с тем же самым именем. Х Модификация типа столбца. Тип столбца меняется с помощью предложений CHANGE или MODIFY в операторе ALTER TABLE. Предположим, что столбец в таблице my_tbl имеет тип SMALL INT UNSIGNED, и появилась необходимость изменить его на MEDUIM UNSIGNED. Это можно сделать с помощью одной из двух команд:

ALTER TABLE tbl_name MODIFY i MEDIUMINT UNSIGNED ALTER TABLE tbl_name CHANGE i i MEDIUMINT UNSIGNED Почему во втором случае столбец назван дважды? Потому, что операция CHANGE, в отличие от операции MODIFY, не только меняет тип данных, но и переименовывает столбец. По желанию можно не только изменить тип данных столбца, но и изменить имя столбца с i, скажем, на j:

ALTER TABLE tbl_name CHANGE i j MEDIUMINT UNSIGNED Тут важно понять семантику этого оператора. Сначала указывается имя столбца, который будет меняться, а затем дается полностью новое объявление столбца, включая опять же его имя. Имя обязательно указывается, даже если оно является старым именем столбца. Существенной причиной для изменения типа столбца может служить необходимость оптимизировать объединения, сравнивающие столбцы из разных таблиц. Операция сравнения занимает меньше времени, если столбцы однотипные. Предположим, вы запустили запрос:

SELECT... FROM tl, t2 WHERE tl.name = t2.name Очевидно, что если столбец tl.name имеет тип CHAR(10), а столбец t2. name Ч CHAR ( 1 5 ), запрос будет обрабатываться медленнее, чем, если бы оба столбца имели тип CHAR (15). Изменим длину 11. name с помощью одного из следующих операторов:

ALTER TABLE tl MODIFY name CHAR(15) ALTER TABLE tl CHANGE name name CHAR(15) Глава З. Синтаксис и использование языка SQL В версиях до 3.22 были возможны объединения только по столбцам одного и того же типа. В СУБД MySQL версии 3.22 и выше стало возможно делать объединения по столбцам разного типа. Но эффективнее все же работают объединения одинаковых типов данных. Преобразование таблиц из таблиц с переменной длиной строки в таблицы с фиксированной длиной строки. Предположим, что мы имеем таблицу chartbl со столбцами типа VARCHAR. Как ее оптимизировать? (Таблицы со строками фиксированной длины обычно обрабатываются быстрее, чем таблицы со строками переменной длины.) Создадим такую таблицу:

CREATE TABLE chartbl (name VARCHAR(40), address VARCHAR(SO)) Сложность этой процедуры состоит в том, что изменять все столбцы нужно одновременно, используя один оператор ALTER TABLE. Если изменить только один или несколько столбцов, СУБД MySQL "решит", что таблица по-прежнему имеет строки переменной длины и преобразует CHAR-столбцы обратно в VARCHAR для того, чтобы сэкономить пространство. Такое изменение структуры можно осуществить только оператором вида:

ALTER TABLE chartbl MODIFY name CHAR(40), MODIFY address CHAR(80) Теперь запрос DESCRIBE покажет, что таблица содержит только столбцы CHAR. Это наглядно демонстрирует необходимость поддержки множественных операций с помощью оператора ALTER TABLE. Нужно предупредить, что при осуществлении таких операций нужно быть очень осторожным: наличие столбцов типа BLOB или TEXT будет препятствовать преобразованию таблицы в таблицу с фиксированной длиной строки. Эти типы не имеют аналогичного типа фиксированной длины, а наличие хотя бы одного столбца переменной длины приводит к присвоению всем столбцам переменной длины. Преобразование таблиц из таблиц с фиксированной длиной строки в таблицы с переменной длиной строки. Да, все запросы к таблице chartbl работают быстрее, когда она имеет строки фиксированной длины, но при этом занимает больше, чем хотелось бы, места. Итак, мы решили преобразовать ее в первоначальное состояние. Преобразование таблицы в этом направлении значительно проще. Достаточно преобразовать тип только одного столбца в тип VARCHAR, и СУБД MySQL автоматически преобразует тип оставшихся СТОЛбоВ ИЗ CHAR В VARCHAR:

ALTER TABLE chartbl MODIFY name VARCHAR(40) ALTER TABLE chartbl MODIFY address VARCHAR(SO) Часть I. Использование СУБД MySQL ш Преобразование типа таблицы. Предположим, что вы перешли на новую версию СУБД MySQL (с 3.22 на 3.23 или выше). Все таблицы базы данных были созданы в формате ISAM. Их нужно преобразовать в формат My ISAM. Для этого выполните следующее:

ALTER TABLE tbl_name TYPE = MYISAM Зачем это делать? Одна из причин была уже описана в разделе "Создание и удаление индексов", раньше в этой главе. Она заключается в том, что метод My ISAM лучше проявляет себя при индексировании и позволяет индексировать по столбцам, содержащим пустые значения, и по столбцам типа BLOB и TEXT. Другой причиной является то, что таблицы формата MylSAM машинно-независимы, и их можно переносить прямым копированием, даже если машины имеют различную аппаратную конфигурацию. Эта тема будет затронута в главе 11, "Общее администрирование MySQL". Получение информации о базах данных и таблицах Познакомимся с несколькими операторами, позволяющими получить информацию о базах данных и таблицах. Они полезны для отслеживания структуры базы данных, особенно для предварительного просмотра свойств столбцов таблицы перед внесением изменений в их структуру с помощью оператора ALTER TABLE. Для получения самой разноплановой информации о базе данных и таблицах можно воспользоваться оператором SHOW.

SHOW DATABASES SHOW TABLES Перечень баз данных на сервере Перечень таблиц в текущей базе данных Перечень таблиц в указанной базе дан SHOW TABLES FROM db_name SHOW COLUMNS FROM tblname SHOW INDEX FROM tbl_name SHOW TABLE STATUS ных Отображение информации о столбцах в указанной таблице Отображение информации об индексах в указанной таблице Отображение описательной информации о таблицах в текущей базе данных SHOW TABLE STATUS FROM db name Отображение описательной информации о таблицах в указанной базе данных Операторы DESCRIBE tbl_name и EXPLAIN tbl_name являются синонимами оператора SHOW COLUMNS FROM tbl_name. Глава 3. Синтаксис и использование языка SQL Кроме того, с помощью команды mysqlshow можно получать аналогичную информацию о базе данных и таблицах прямо из оболочки UNIX. mysqlshow mysqlshow db_name mysqlshow db name tbl name % mysqlshow tbl_name % mysqlshow keys db_name Перечень баз данных на сервере Перечень таблиц в указанной базе данных Отображение информации о столбцах в указанной таблице Отображение информации об индексах в указанной таблице status db_name Отображение описательной информации о таблицах в указанной базе данных Утилита mysqldump также позволяет просматривать структуру таблиц в формате оператора CREATE TABLE. (При сравнении результатов работы этого оператора и оператора SHOW COLUMNS, автор обнаружил, что листинг программы mysqldump читается легче и более полно отображает состав индекса.) Но при использовании mysqldump вызывайте его с ключом no - data, чтобы случайно не получить дамп всей таблицы.

% mysqldump по Ч data db_name tbl_name Для команд mysqldump и mysqlshow может понадобиться определить обычные опции, такие, например, как - Ч host, для подключения к другому серверу. Выборка записей Зачем же вводить данные в базу, если не для их выборки и последующей обработки? Для этого и предназначен оператор SELECT. Возможно, это самый популярный оператор языка SQL, но верно и другое. Это самый сложный оператор языка SQL. Основной синтаксис оператора SELECT выглядит следующим образом. SELECT selection_list FROM table_list WHERE primary_constraint GROUP BY grouping_columns ORDER BY sorting_columns HAVING secondary_constraint LIMIT count Перечень выбираемых столбцов Откуда производить выборку Каким условиям должен удовлетворять запрос Каким образом результаты группируются Каким образом результаты сортируются Вторичные условия, которым должны удовлетворять строки Диапазон отображаемых строк Часть I. Использование СУБД MySQL В синтаксисе этого оператора является обязательным только слово SELECT и column_list, определяющее перечень выбираемых столбцов. Для некоторых баз данных слово FROM тоже является обязательным. О СУБД MySQL этого сказать нельзя. Это позволяет вычислять выражения, не обращаясь к таблицам: SELECT S Q R T ( P O W ( 3, 2 ) + P O W ( 4, 2 ) ) В главе 1, "Знакомство с СУБД MySQL и SQL", при рассмотрении синтаксиса оператора SELECT упор был сделан в основном на списке выборки, предложениях WHERE, GROUP BY, ORDER BY, HAVING и LIMIT. В этой главе мы сконцентрируем основное внимание на наиболее сложном аспекте оператора SELECT Ч на объединении: т.е. какие типы объединения поддерживает СУБД MySQL, что они делают и как они определяются. Это поможет научиться эффективнее использовать СУБД MySQL потому, что самой серьезной задачей при написании сложного запроса выборки является определение правильного варианта объединения таблиц. Далее в этой главе читатель может просмотреть раздел "Разные решения". Там вы найдете решения некоторых проблем, многие из которых требуют применения всех возможностей оператора SELECT. 1 Основной нюанс, с которым часто сталкиваются при разработке запросов SQL, заключается в том, что при возникновении проблемы не всегда можно легко найти ее решение. Но после того, как выход найден, его можно использовать для решения подобных проблем в будущем. Оператор SELECT как раз и является оператором такого типа. Для эффективной работы с ним накопленный опыт играет существенную роль, просто из-за того, что все пути эффективного использования его вам известны. По мере накопления опыта читателю будет проще находить пути решения новых задач. Вы уже будете ловить себя на мысли, что это просто решается с помощью конструкции LEFT JOIN или это объединение ограничено общей парой ключевых столбцов. (Я, конечно, не беру на себя полной ответственности утверждать, что будет именно так. Но мысль о том, что опыт, накопленный в ходе практической работы, пригодится вам в будущем, будет вам утешительна.) В трех последующих разделах мы рассмотрим использование всех форм объединения, которые поддерживает СУБД MySQL. В примерах будут использованы две небольшие таблицы. table t 1 1 1 i с 2 1с 3|b 4|а Глава 3. Синтаксис и использование языка SQL Тривиальное объединение Самым простым типом объединения является тривиальное объединение, в котором задействована только одна таблица В этом случае строки выбираются из одной таблицы, указанной в предложении FROM. SELECT FROM tl н д.1 Cl Некоторые авторы не считают это объединение объединением вообще и применяют это понятие к выборкам из двух и более таблиц. Я считаю это частным случаем. Полное объединение Объединение будет полным, если в нем задействовано несколько таблиц. Например, при объединении двух таблиц каждая строка из первой таблицы объединяется с каждой строкой из второй таблицы. SELECT t l. *, t 2. * FROM tl, t 1 1 1 1 1 1 1 1 1 1 J ll 1 2 3 1 2 3 1 2 1 1 1 1 1 1 1 1 1 cl a ь с a b с a b с i 2 2 2 3 3 3 4 4 c с с с b b b a a a Полное объединение называют еще и перекрестным объединением. Здесь каждая строка одной таблицы объединяется с каждой строкой другой таблицы, создавая тем самым все возможные комбинации строк обеих таблиц. Есть еще одно название Ч декартово произведение. Такое объединение создает огромное количество строк, потому что результирующее количество строк будет произведением количества строк каждой таблицы, задействованной в объединении. Например, полное объединение трех таблиц, содержащих соответственно 100, 200 и 300 строк, в результате возвращает 100x200x300 = 6 миллионов строк. Несмотря на то, что размеры исходных таблиц невелики, мы получим очень много строк. В таких случаях для уменьшения количества строк до приемлемого количества можно воспользоваться предложением WHERE.

Часть I. Использование СУБД MySQL При добавлении условия в предложении WHERE, требующем соответствия определенных значений определенных столбцов из двух таблиц, мы получим объединение типа equi-join (объединение по равенству).

SELECT tl.*, t2.* FROM tl, t2 WHEPE tl.ll = t2.i2 + + + + I ll cl I c 1 | 2|b 3с 2 1с 3Ib Типы объединений JOIN, CROSS_JOIN и INNER_JOIN эквивалентны оператору объединения ', '. Объединение STRAIGHT_JOIN аналогично полному объединению, но при этом таблицы объединяются в порядке, указанном в предложении FROM. Оптимизатор СУБД MySQL при объединении таблиц сортирует их в абсолютно произвольном порядке для того, чтобы строки выбирались как можно быстрее. Оптимизатор может ошибиться. Это устранимо с помощью ключевого слова STRAIGHT_JOIN. Слово STRAIGHT_JOIN может быть указано в операторе SELECT в двух местах. Его можно указать между словом SELECT и списком выбираемых столбцов, или в предложении FROM. Первый способ окажет глобальное воздействие на все объединения в операторе SELECT:

SELECT STRAIGHT_JOIN... FROM tablel, table2, tableS... SELECT... FROM tablel STRAIGHT_JOIN table2 STRAIGHT_JOIN table3...

* Квалификация ссылок на столбцы I Все ссылки на столбцы в операторе SELECT должны быть однозначно со\. отнесены с таблицами, указанными в предложении FROM Когда в предложении FROM указана одна таблица, неоднозначность исключена Когда в преду ложении FROM указано несколько таблиц, но рассматриваемый столбец есть |j только в одной таблице, неоднозначность также невозможна Но когда в пред'Д* ложении FROM указано несколько таблиц и рассматриваемый столбец присут;

ствует сразу в нескольких таблицах, столбец должен быть квалифицирован 8 только полностью с применением синтаксиса tbl_name. col_name Это однозначно определит, какой таблице принадлежит указанный столбец Пусть таблица my_tbll содержит столбцы а и b, а таблица mytbl2 Ч b и с Ссылки ut %J на столбцы а и с будут однозначными, а ссылки на b должны быть полноL' стью квалифицированы my_tbll.b или my_tbl2.b р SELECT a, my_tbll.b, my_tb!2.b, c F R O M m y _ t b l l, my_tb!2... L I* if II || к W В некоторых ситуациях для удаления неоднозначности квалификатора имени таблицы будет недостаточно Такая ситуация возникает тогда, когда таблица используется в запросе несколько раз В таком случае для полной квалификации столбца может понадобиться псевдоним Присвоим псевдонимы всем экземплярам таблицы, задействованным в данном запросе Теперь ссылка на столбцы таблиц будет иметь вид alias_name.col_name Предлагаю вашему вниманию запрос, объединивший в себе два экземпляра Глава 3. Синтаксис и использование языка SQL одной и той же таблицы Второму экземпляру таблицы присвоен псевдоним, что позволит однозначно ссылаться на столбец из этой таблицы: SELECT my_tbl.coll, m.co!2 FROM my_tbl, my_tbl AS m WHERRE my tbl.coll > m.coll Левое объединение Полное объединение отображает только те строки, значения которых по критерию выборки полностью совпадают. Левое объединение действует аналогичным образом плюс строки в левой таблице, которые не удовлетворяют критерию выборки. Все столбцы из правой таблицы отображаются пустыми значениями. Это объединение работает следующим образом: строки обеих таблиц выбираются в случае, если критерии выборки совпадают, если критерии выборки не совпадают, строка левой таблицы выбирается, а правой присваиваются пустые значения. Другими словами, оператор выборки с левым объединением выбирает все строки левой таблицы, независимо от того, совпадают критерии выборки или нет. Проверка критериев осуществляется в предложениях ON или USING (). Предложение ON используется независимо от того, имеют или нет столбцы, по которым производится объединение, одинаковое имя.

SELECT tl.*, t2.* FROM tl LEFT JOIN t2 ON tl.ll = t2.l 1 1 1 ll cl 1 c | 11 a 21b 31 с I NULL 1 NULL | 1 21С | 1 31b I Предложение USING (} аналогично предложению ON, но имена столбцов, по которым производится объединение, должны совпадать полностью:

SELECT ray_tbll.*, my_tb!2.* FROM my_tbll LEFT JOIN my_tbl!2 USING(b) Объединение LEFT JOIN можно применить для выяснения вопроса, почему по данному критерию выборки не "подтягиваются" строки из правой таблицы. Этого можно добиться, добавив предложение WHERE, которое позволит просматривать только те строки выборки, в которых правая таблица представлена пустыми значениями.

SELECT tl.*, t2.* FROM tl LEFT JOIN t2 ON tl.ll = t2. Д.2 WHERE t2. i2 IS NULL Ч+ I cl Х+ 1 Ia I i2 | c2 | + + + | NULL | NULL | H,Ч+ + Часть I. Использование СУБД MySQL Но в выборке пустых столбцов нет необходимости. Единственное, что действительно может интересовать в выборках такого рода, Ч это строки из левой таблицы, не нашедшие соответствия во второй таблице.

SELECT tl.* FROM tl LEFT JOIN t2 ON tl.ll = t2.i2 WHERE t2.i2 IS NULL I ll I cl I В этой ситуации нужно следить, чтобы столбцы, по которым производится объединение, не были объявлены как NOT NULL, в противном случае может быть получен неадекватный результат. Объединение LEFT JOIN имеет несколько синонимов и вариантов. Один из них Ч объединение LEFT OUTER JOIN. Существует также нотация для ODBC (Open DataBase Connectivity) СУБД MySQL (здесь "oj" обозначает "outer join" Ч "внешнее объединение"):

( О] tbl_name LEFT OUTER JOIN tbl_name ON join_expr } Объединение NATURAL LEFT JOIN тоже ведет себя подобно объединению LEFT JOIN. Оно осуществляет левое объединение, выбирая по критерию совпадения все столбцы, которые имеют одинаковое имя в правой и левой таблицах. Некоторые базы данных имеют соответствующее объединение RIGHT JOIN, но СУБД MySQL объединения такого типа в своем арсенале пока еще не имеет. Комментарии SQL-код можно сопровождать комментариями. Эта возможность может пригодиться при сохранении текстов запросов в файлы. В СУБД MySQL есть два способа комментирования. Комментарием считается любая строка, начинающаяся с символа ' I'. Кроме того, можно производить комментирование в стиле языка программирования С. Это значит, что в качестве комментария рассматриваются все строки, расположенные между символами ' / * ' и ' * / '. Действие комментария такого типа распространяется на несколько строк.

# это однострочный комментарий /* это тоже однострочный комментарий */ /* это многострочный комментарий */ В СУБД MySQL есть интересная возможность "прятать" ключевые слова, присущие только ей, за символы ' / * ! '. СУБД MySQL "заглядывает" за такие ограничители, в то время когда любая другая СУБД их просто проигнорирует как любой комментарий. Это позволяет создавать Глава 3. Синтаксис и использование языка SQL запросы, которые можно применить как для работы с СУБД MySQL, извлекая при этом все преимущества применения дополнительных возможностей MySQL, так и для работы с другими базами данных. Вот пример двух абсолютно идентичных для обычных СУБД запросов. Но MySQL во втором случае выполнит операцию INSERT DELAYED:

INSERT INTO absence (student_id, date) VALUES(13, "1999-09-28" INSERT /*! DELAYED */ INTO absence (student_id, date) VALUES(13, "1999-09-28" Начиная с версии 3.23 комментарий можно начинать с двух тире и пробела между ними;

все символы, что стоят между такими тире и концом строки, рассматриваются как комментарий, т.е. не будут анализироваться интерпретатором. Некоторые другие базы данных тоже считают двойное тире началом комментария СУБД MySQL это тоже позволяет, но для снятия неопределенности требует дополнительного пробела. Операторы, содержащие выражения наподобие 5 7, могут быть распознаны как начало комментария. А выражение наподобие 5 7 вряд ли будет выражением. Но все же лучше будет воспользоваться одним из традиционных комментирующих символов и обращаться к помощи двойного тире только в кодах, заимствованных из других баз данных. Разные решения Этот раздел можно рассматривать как нечто вроде медицинской аптечки первой помощи. Большей частью это решения проблем, которые пришли к автору по почте (благодарю всех тех, кто присылал мне ответы.) Преобразование вложенных выборок в объединения Возможность создавать вложенные выборки появится в СУБД MySQL 3.24. Отсутствие этой возможности в предыдущих версиях вызвало много нареканий в адрес СУБД MySQL. Но запросы, использующие вложенные выборки, можно переписать с применением объединения. Более того, использование объединения всегда эффективнее использования вложенных выборок.

Перезапись вложенных выборок, делающих выборку по совпадению Вот пример запроса, содержащего вложенную выборку. Он выбирает результаты всех тестов из таблицы score (т.е. игнорирует результаты викторин):

SELECT * FROM score WHERE event_id IN (SELECT event_id FROM event WHERE type = "T") Часть I. Использование СУБД MySQL Аналогичный запрос можно написать без вложенного запроса с помощью простого объединения:

SELECT score.* FROM score, event WHERE score.event_id = event.event_id AND type = "T" Вот другой пример. Это выборка результатов, полученных учащимися женского пола:

SELECT * FROM score WHERE student_id IN (SELECT student_id FROM student WHERE sex = "F") Его можно преобразовать в следующее объединение: SELECT score.* FROM score, student WHERE score.student_id = student.student_id AND student.sex = "F" Здесь просматривается простая закономерность. Вложенный запрос типа:

SELECT * FROM table1 WHERE colunral IN (SELECT colunm2 FROM table2a WHERE colunm2b = value) можно заменить объединением:

SELECT tablel.* FROM tablel, table2 WHERE tablel. colunml = table2. colunm2a AND table2. colunm2b = value Перезапись вложенных выборок, делающих выборку по несовпадению Вот пример запроса, содержащего вложенную выборку другого рода. Он выбирает значения из одной таблицы, которые не представлены в другой таблице. Ранее мы уже увидели, что при решении задач типа "поиск отсутствующих значений" подходит объединение типа LEFT JOIN. Вот запрос с вложенным запросом, который проверяет отсутствие значений в таблице absence (учащиеся с отличной посещаемостью):

SELECT * FROM student WHERE student_id NOT IN (SELECT studentid FROM absence) Этот запрос можно переписать, применив конструкцию LEFT JOIN.

SELECT student.* FROM student LEFT JOIN absence ON student.student_id = absence.student_id WHERE absence.student_id IS NULL Здесь просматривается закономерность. Вложенный запрос типа:

SELECT * FROM tablel WHERE colunml NOT EXISTS (SELECT colunm2 FROM table2) можно заменить объединением:

SELECT tablel.* FROM tablel LEFT JOIN table2 ON tablel.columnl = table2.column2 WHERE table2.colunm2 IS NULL Здесь предполагается, что столбец table2.column2 был объявлен Как NOT NULL.

Глава 3. Синтаксис и использование языка SQL Проверка отсутствующих значений в таблице В разделе "Выборка записей" уже было показано, что если необходимо узнать, какие из значений одной таблицы отсутствуют в другой таблице, обе таблицы можно объединить, используя объединение LEFT JOIN, и просмотреть те строки первой таблицы, которым соответствуют пустые значения из второй таблицы. Эта ситуация была показана на примере таких двух таблиц.

Объединение LEFT JOIN для поиска всех t l. i l, отсутствующих в t2. 12, имеет следующий вид:

SELECT tl.* FROM tl LEFT JOIN t2 ON tl.ll = t2. 12 WHERE t2. 12 IS NULL I I Cl | 1 Ia | Теперь рассмотрим усложненный вариант запроса: "Какие значения отсутствуют?". В проекте "Учет успеваемости", впервые упомянутом в главе 1, "Знакомство с СУБД MySQL и SQL", имеется три таблицы. Это таблица student со списком учащихся, таблица event со списком прошедших экзаменов, таблица score с оценками, полученными каждым учащимся по каждому тесту и каждой викторине. Однако если учащийся был в день проведения викторины или теста болен, запись по данному учащемуся по данному тесту или викторине в таблице score будет отсутствовать. Это значит, что ему придется сдавать этот экзамен дополнительно. Как найти недостающие записи и определить, какие учащиеся должны сдавать дополнительные экзамены? Задача стоит таким образом: определить учащихся, которые не имеют оценок по определенным экзаменам. По-другому можно сказать, что нам необходимо определить, какие комбинации учащихся и событий в таблице оценок не представлены. Это наводит на мысль, что нам следует обратиться к объединению типа LEFT JOIN. Уже потому, что нам требуется воспользоваться объединением такого типа для комбинации столбцов можно судить, что это достаточно сложное объединение. Искомые комбинации являются комбинациями "учащийся/событие", которые можно получить пересечением таблиц student и event: FROM student, event 214 Часть I. Использование СУБД MySQL Затем на результат этого объединения наложим объединение LEFT JOIN с таблицей score:

FROM student, event LEFT JOIN score ON student.student_id AND event.event_id = score.event_id = score.student_id Обратите внимание на то, что предложение ON объединяет строки в таблице в полном соответствии со значениями столбцов из различных таблиц. Это ключ к решению задачи. Объединение LEFT JOIN позволяет генерировать строку для каждого пересечения таблиц student и event, даже если в таблице score нет соответствующих им данных. Результирующий набор строк для этих записей с недостающими оценками можно идентифицировать по тому, что эти строки из таблицы score должны быть пустыми. Это определяется в предложении WHERE:

WHERE score.score IS NOLL Результат сортируется предложением ORDER BY. Логичнее всего сортировать по учащемуся и событию. Выберем соответствующий порядок сортировки: ORDER BY student.student_id, event.event_id Теперь остается только указать имена столбцов, которые мы хотим увидеть в результате работы запроса, и дело сделано. Вот как выглядит окончательный запрос.

SELECT student.name, student. student_id, event.date, event.event_id, event.type FROM student, event LEFT JOIN score ON student.student_id = score.student_id AND event.event_id = score.event_id WHERE score.score IS NULL ORDER BY student.student_id, event.event_id Вот результат работы этого запроса.

I name Megan Joseph Katie Devri Devri Will Aver у Gregory Sarah Carter Carter Gabrielle Grace I student id | date score | type | 2 4 13 13 П 20 23 24 27 27 29 J ь 1999-09-16 | 1999-09-03 | 1999-09-23 I 1999-09-03 | 1999-10-01 | 1999-09-16 | 1999-09-06 | 1999-10-01 | 1999-09-23 | 1999-09-16 I 1999-09-23 I 1999-09-16 I 1999-09-23 | 4 1 5 1 6 4 2 6 5 4 5 4 1 1 1 1 1 1 1 1 1 1 1 1 Ч Q Q Q Q Т Q Q Т Q Q Q Q Q Глава З. Синтаксис и использование языка SQL Здесь необходимо обратить внимание на одну особенность. В результате работы оператора получены идентификатор учащегося и идентификатор события. Столбец student_id присутствует как в таблице student, так и в таблице score. Таким образом, можно предположить, что перед нами может стоять выбор: помещать в список выборки столбец student.student_id или столбец score.student_id. Но на самом деле такого выбора у нас нет, так как мы хотим получить из таблицы score только те строки, которые будут иметь значение NULL. Выборка столбца score.student_id в результате даст нам пустой столбец. Аналогичные рассуждения имеют место в случае со столбцом event_id, который присутствует как в таблице event, так и в таблице score. Выполнение операции UNION Запрос по выборке данных из нескольких таблиц, имеющих одинаковую структуру, во многих базах данных осуществляется оператором UNION. В СУБД MySQL такой оператор отсутствует (по крайней мере до версии 3.24). Его отсутствие можно компенсировать по крайней мере двумя способами. Х Выполните несколько запросов SELECT, по одному на каждую таблицу. Если порядок выбираемых строк не имеет никакого значения, то это даст искомый результат. Х Произведите выборку из каждой таблицы во временную таблицу. Затем сделайте выборку из временной таблицы. Это позволит отсортировать строки в нужном порядке. В СУБД MySQL версии 3.23 и выше эта проблема решается довольно просто благодаря тому, что сервер позволяет легко создавать временную таблицу. Она б'/дет автоматически уничтожена после завершения текущей сессии. В примере, представленном ниже, таблица удаляется явным образом, чтобы позволить серверу освободить ресурсы, задействованные им под эту таблицу. Это решение подходит в случае, когда сессия будет продолжаться. Для ускорения быстродействия используется таблица в формате HEAP (хранится в памяти).

CREATE tablel INSERT INSERT TEMPORARY TABLE hold_tbl TYPE=HEAP SELECT... FROM WHERE... INTO hold_tbl SELECT FROM table2 WHERE... INTO hold_tbl SELECT FROM table2 WHERE...

SELECT -FROM hold_tbl ORDER BY DROP TABLE hold_tbl Идея, реализуемая в версиях до 3.23, аналогична. Вся разница заключается в том, что столбцы таблицы hold_tbl необходимо объявлять явным образом, и в конце всей процедуры необходимо за216 Часть I. Использование СУБД MySQL давать оператор DROP TABLE для того, чтобы эта таблица прекратила свое существование после завершения сеанса работы.

CREATE TABLE hoid_tbl (columnl..., column2 TYPE=HEAP SELECT... FROM table1 WHERE... INSERT INTO hold_tbl SELECT FROM tablel WHERE INSERT INTO hold_tbl SELECT FROM table2 WHERE INSERT INTO hold_tbl SELECT FROM tableZ WHERE SELECT FROM hold_tbl ORDER BY DROP TABLE hold_tbl...,............) Добавление столбца с непрерывной последовательностью значений Для того чтобы столбец автоматически заполнялся последовательными номерами, необходимо, используя оператор ALTER TABLE, добавить столбец типа AUTO_INCREMENT. Вот последовательность операторов, демонстрирующих последовательность операций создания таблицы, заполнения ее данными, а затем добавления столбца AOTO_INCREMENT:

mysql> CREATE TABLE t (с CHAR(IO));

mysql> INSERT INTO t VALUES("a"), ( b ) ( c ) "", "";

mysql> SELECT * FROM t;

mysql> ALTER TABLE t ADD i INT AUTO_INCREMENT NOT NULL PRIMARY KEY;

mysql> SELECT * FROM t;

+ + + 1a 1b 1с 111 1 2| 13I Упорядочение или переупорядочение существующих столбцов Всякий цифровой столбец можно упорядочить (или переупорядочить, если он был упорядочен и некоторые строки были удалены):

JJ ALTER TABLE t MODIFY i INT NOLL 'l UPDATE t SET 1 = NULL ALTER TABLE t MODIFY i INT UNSIGNED AUTO INCREMENT NOT NULL PRIMARY KEY Глава З. Синтаксис и использование языка SQL Однако самым простым решением является удаление столбца и повторное добавление его с параметром AUTO_INCREMENT. Оператор ALTER TABLE позволяет производить множественные операции. Таким образом, вся цепочка действий может быть выполнена с помощью одного оператора:

Pages: | 1 | 2 | 3 | 4 | ... | 11 |

Книги, научные публикации

Blog

мтт. ...