|
||||||||||||||||||
Основы web-программирования для школьного "сайтостроительства". Лекция 5.PHP + MySQLЗдравствуйте, уважаемые коллеги! В пятой лекции нашего курса мы рассмотрим ряд примеров использования СУБД MySQL, которая (и это было показано в первой лекции) на данный момент является основным инструментом web-разработчиков. Эта лекция является весьма сложной и насыщенной, в частности, потому, что объем и тематика нашего курса не позволяют подробно заниматься самой теорией реляционных баз данных, основы которой предполагаются известными. Чтобы освежить в памяти соответствующий материал, я могу порекомендовать, например, главу, посвященную базам данных, из “Энциклопедии учителя информатики” (см. № 16/2007). Учебный план
Зачем нужна СУБД?Короткий ответ: для отделения собственно информации от формы ее представления. Поясню, что имеется в виду. Для этого воспользуюсь аналогией: а зачем нужна технология CSS? Надеюсь, что большинство читателей и слушателей нашего курса знают ответ на этот вопрос — для разделения содержания документов и тонкостей их визуализации. Сейчас идеальным считается такой стиль HTML-верстки, при котором на HTML задается лишь структура документа, — это заголовок такого-то уровня, это список, это таблица и т.д. А то, как именно нужно визуализировать заголовки данного уровня (каким кеглем, цветом, начертанием и т.п.), какой отступ и символ пункта используется в списке, как оформляются таблицы — все эти тонкости визуализации задаются в подключаемых таблицах стилей. Это позволяет не только надежно и просто для разработчика обеспечивать единообразие документов на сайте, но и при необходимости “одним движением” менять тонкие параметры визуализации во множестве документов. И все же описанная методология имеет свои ограничения — ряд важных решений разработчик вынужден принимать на этапе HTML-кодирования, и если в дальнейшем ему захочется что-то изменить, это может быть связано со значительными трудозатратами. Вспомним пример с размещением объявлений на главной странице сайта. Если бы мы располагали только технологиями HTML+CSS, нам пришлось бы принимать решение о способе структуризации объявлений (нумерованный или ненумерованный списки, таблица и т.п.) на этапе кодирования. Использование PHP и хранение объявлений в файлах позволило отделить информацию от способа ее структуризации. “Подкрутив” код на PHP, мы могли структурировать объявления любым удобным нам способом. Однако, храня объявления в файлах, мы все еще остаемся жестко привязаны к способу их физического хранения. И для решения каждой специфической задачи нам приходится придумывать специфическое “хитрое” решение (как, например, дописывание восклицательного знака к объявлениям повышенной важности). Использование СУБД “заканчивает” мысль об отделении информации от способов ее хранения и визуализации. Те же объявления мы можем представлять себе исключительно как порции информации, не думая о том, как именно физически они хранятся, как выбираются объявления, удовлетворяющие данному условию, и т.п. — все это забота СУБД. MySQL в DenwerКак мы уже видели в процессе изучения первой лекции нашего курса, практически на всех хостингах в качестве основной СУБД предлагается MySQL. Почему? Потому что сегодня MySQL — самая мощная, хорошо документированная, “живая” (постоянно поддерживаемая) свободно распространяемая производственная реляционная СУБД. MySQL является серверной СУБД. На первой лекции достаточно подробно рассматривался механизм функционирования web-сервера, как некоторого “кладовщика”, который обслуживает запросы браузеров — предоставляет им файлы. Серверная СУБД — тот же кладовщик. Для всех современных реляционных СУБД языком запросов является SQL, различные диалекты которого, впрочем, могут незначительно отличаться. В состав пакета Denwer входит как сам сервер MySQL, так и один из самых распространенных web-клиентов для взаимодействия с сервером — phpMyAdmin. Давайте, наконец, посмотрим, как все это работает. Пожалуйста, запустите Denwer и зайдите на страницу http://localhost/. Немного прокрутите страницу вниз до раздела “Утилиты” (рис. 1). Нас будет интересовать упомянутый уже phpMyAdmin.
Рис. 1 Кликнем по соответствующей ссылке и окажемся на главной странице (рис. 2).
Рис. 2 Не пугайтесь страшного красного предупреждения — вам пока ничего не угрожает. Установка учебной базы данных “Учет успеваемости”Для экспериментов на этом занятии мы будем использовать небольшую демонстрационную БД sampdb, которую часто используют в учебных курсах по MySQL. Пожалуйста, получите ее в разделе “Учебные материалы” на странице нашего курса. В поле “Создать новую БД” введите название создаваемой БД — sampdb и нажмите кнопку “Создать”. Должна быть создана пустая база данных (рис. 3).
Рис. 3 Теперь нам предстоит заполнить нашу базу данными. Вы уже скачали файл sampdb.sql? Если нет, сделайте это. Затем перейдите на вкладку “SQL” и в поле “Месторасположение текстового файла” укажите файл sampdb.sql (рис. 4). (Отметим, что можно просто скопировать содержимое файла sampdb.sql в буфер обмена и вставить его в поле texarea — результат будет тем же.)
Рис. 4 После нажатия на кнопку с интригующей надписью “Пошел” БД будет создана (рис. 5).
Рис. 5 Далее описана структура нашей учебной базы данных. В процессе знакомства со структурой, пожалуйста, изучите и содержание соответствующих таблиц. Для этого надо выбрать требуемую таблицу и пункт “Обзор” в верхнем меню. Вот, к примеру, несколько первых строк из таблицы student (рис. 6).
Рис. 6 Структура учебной базы данныхБаза данных “Учет успеваемости” содержит четыре таблицы: student, score, event и absence. Опишем структуру и приведем несколько первых строк каждой таблицы (названия ключевых полей выделены жирным шрифтом). Таблица student Таблица student предназначена для хранения списка студентов. Для каждого студента хранятся его имя (name) и пол (sex). Кроме того, каждый студент имеет уникальный идентификатор student_id.
Таблица event В таблице event хранятся даты (date) и типы (type) контрольных мероприятий. В учебном заведении, для которого проектировалась эта база данных, проводятся контрольные мероприятия двух типов: тесты и викторины. В таблице event тесты (test) обозначаются буквой ‘T’, викторины (quiz) — буквой ‘Q’. Каждое мероприятие имеет уникальный идентификатор event_id. Обратите внимание на то, что поле date не могло быть объявлено ключевым, поскольку мыслимой является ситуация, когда в один день проводится несколько контрольных мероприятий. Поскольку в таблице event содержится небольшое количество данных, мы приведем ее полностью.
Таблица score В таблице score хранятся баллы (score), полученные студентами (студент определяется полем student_id) на контрольных мероприятиях (мероприятие определяется полем event_id). (Система оценок на контрольных мероприятиях — стобалльная.) Важная и интересная особенность таблицы score заключается в том, что она имеет составной ключ — пару полей (student_id, event_id). Действительно, и в поле student_id, и в поле event_id могут содержаться повторяющиеся значения (ведь один студент может иметь оценки за несколько контрольных мероприятий, а за одно контрольное мероприятие могут иметь оценки несколько студентов). Но комбинация полей (student_id, event_id) должна быть уникальной: каждый студент может иметь только одну оценку за данное контрольное мероприятие.
Таблица absence Таблица absence предназначена для фиксации дней (дат) отсутствия студентов в учебном заведении. Отметим, что создатели базы данных посчитали, что если уж студент пропускает занятия, то в течение целого дня. На мой взгляд, это не самое удачное решение. Разумнее было бы отмечать пропуски контрольных мероприятий, а не целых дней. Но, что сделано (разработчиками этой БД), то сделано. Поскольку данных в таблице absence не много (удивительно!), приведем ее целиком:
Как и таблица score, таблица absence имеет составной ключ. Связи между таблицами Связи между таблицами student, event, score и absence показаны на рис. 7:
Рис. 7 Соответствие строк показано линиями. Для обозначения связи типа “один ко многим” использованы символы “1” (на стороне “одного”) и “?” (на стороне “многих”). Те, кто имеет опыт работы в MS Access, возможно, узнали знакомую “Схему данных” и знакомые обозначения. Команда SELECTНа этом занятии мы будем использовать только одну команду языка SQL — команду выборки данных SELECT. Команду SELECT часто называют основной командой языка SQL. И по праву. С помощью этой команды организуются все наиболее употребимые запросы. Синтаксис команды SELECT SELECT <запрашиваемая информация> [FROM <источник информации>] [WHERE <условие>] [GROUP BY <список полей через запятую> [HAVING <условие>] ] [ORDER BY <выражение> [ASC|DESC]] [LIMIT [<с какой строки выдать>,] <сколько строк выдавать>] Как обычно, в квадратные скобки заключены необязательные части (отметим также, что рассматриваем не все, а лишь наиболее существенные параметры этой команды). Часть <запрашиваемая информация> Как мы видим, единственной обязательной частью команды SELECT является запрашиваемая информация (это и понятно… кто же, кроме нас, знает, чего мы хотим). Вообще говоря, запрашиваемая информация может не иметь прямого (и вообще никакого) отношения к базе данных. Далее приведен пример такого рода. На рис. 8 приведен запрос, а на рис. 9 — результат его выполнения. Далее, для экономии места, я не буду приводить скриншоты, ограничившись текстовыми блоками. Пример 1
Рис. 8
Рис. 9 Обратите внимание на то, как задается заголовок столбца (мы дали ему имя SUMMA посредством выражения as SUMMA). Сравните: Пример 2
Перейдем к более содержательным примерам запрашиваемой информации. Рассмотрим следующий запрос. Пример 3
Поскольку в таблице student довольно много записей, мы немного забежали вперед и воспользовались ограничителем LIMIT, посредством которого можно задавать количество строк результата запроса, которые должны быть выведены. В примере 5 на месте запрашиваемой информации указан символ “*”. Этот символ означает “все поля”. Если нам требуется вывести лишь некоторые поля, то их надо перечислить явно: Пример 4
В следующем примере запрашивается информация о количестве записей в таблице student: Пример 5
Обратите внимание на то, что в соответствии с концепцией реляционных баз данных MySQL представляет результаты всех запросов в виде таблиц, даже тогда, когда, “вообще говоря”, результатом является просто число. Это, в частности, продемонстрировано в примере 9. COUNT является одной из так называемых “агрегирующих функций”, которые являются мощным инструментом конструирования запросов. Мы еще вернемся к ним при рассмотрении части GROUP BY. Часть FROMВ части FROM перечисляются таблицы, которые требуются для выполнения запроса. Обратите внимание на то, что здесь требуется указывать все таблицы, а не только те, поля которых упоминаются в части <запрашиваемая информация>. Имена таблиц при этом разделяются запятыми. Часть WHEREВ части WHERE записывается условие отбора строк из таблицы результата. Формулирование условий — это, пожалуй, самая интересная составляющая при конструировании запросов. Соответственно, этому вопросу мы уделим достаточное внимание. Пример 6 Получим список всех девушек, информация о которых содержится в таблице student. Для этого требуется, используя простое условие, выбрать данные из одной таблицы:
Пример 7 Если нам требуется получить список всех девушек, которые когда-либо получали оценку, превышающую 90 баллов, то необходимо использовать сложное условие и выбирать данные из двух таблиц: student и score. SELECT name FROM student, score WHERE sex="F" AND score>90 AND student.student_id=score.student_id
На первый взгляд все нормально. Но это только на первый. Почему в полученном списке Abby упоминается дважды? Чтобы это стало более очевидно, выведем все поля. Пример 8 SELECT * FROM student, score WHERE sex="F" AND score>90 AND student.student_id=score.student_id
В результате объединения таблиц student и score получается огромная таблица, содержащая 5363 строки. Как мы это вычислили? Вот так: Пример 9
Затем из этой огромной таблицы выбираются все строки согласно условию, записанному в WHERE. И мы бы, может быть, ничего и не заметили, если бы Abby не получила оценку, превышающую 90, сразу за два контрольных мероприятия, которые имеют идентификаторы event_id 3 и 6. Кстати, поинтересуемся, что это были за мероприятия: Пример 10 SELECT * FROM event WHERE event_id=3 OR event_id=6
Мы видим, что оба контрольных мероприятия были тестами и проводились соответственно 9.9.1999 и 1.10.1999. Так как же быть? Для того чтобы исключить из результата запроса повторяющиеся строки в разделе запрашиваемая информация, следует использовать ключевое слово DISTINCT. (В некоторых диалектах SQL следует использовать слово DISTINCTROW, MySQL понимает оба варианта.) Приведем соответствующий пример: Пример 11 SELECT DISTINCT name FROM student, score WHERE sex="F" AND score>90 AND student.student_id=score.student_id;
Если мы не используем DISTINCT, то “по умолчанию” считается, что используется режим ALL. Слово “ALL” можно задавать и явно, хотя большого смысла в этом нет. Обратите внимание на то, что, поскольку поле student_id имеется в обоих таблицах, необходимо явно указывать, какое именно поле мы имеем в виду (для других полей имя таблицы также можно, но не необходимо указывать явно). Когда MySQL не может понять, какое именно поле ему следует использовать, он выдает следующее сообщение: Пример 12 SELECT student_id FROM student, score; ERROR 1052: Column: 'student_id' in field list is ambiguous В следующем примере мы получим список всех девушек, которые когда-либо получали за тесты оценку, превышающую 80 баллов. Для этого нам потребуются уже три таблицы: из таблицы student мы станем извлекать девушек, из таблицы score — оценки, а из таблицы event — типы контрольных мероприятий (ведь нас интересуют только тесты). Пример 13 SELECT DISTINCT name FROM student, score, event WHERE sex="F" AND score>80 AND type="T" AND student.student_id=score.student_id AND score.event_id=event.event_id
В условиях можно использовать операции, функции и специальные операторы, описание которых содержится в электронной документации к MySQL (“бумажная” версия документации содержит более тысячи страниц убористого текста, поэтому мы не имеем возможности сколько-нибудь подробно описать здесь даже наиболее часто используемые операции и функции). Но один специальный оператор мы упомянем, это оператор LIKE. Предположим, что нам требуется получить список студентов, имена которых начинаются с буквы “L”. Это можно сделать посредством следующего запроса: Пример 14 SELECT name FROM student WHERE name LIKE "L%"
Как видите, таких всего два. Теперь получим список студентов, имена которых состоят ровно из 5 символов: Пример 15 SELECT name FROM student WHERE name LIKE "_____"
В примерах 14 и 15 мы продемонстрировали основные возможности оператора LIKE, который применяется для сопоставления строк с образцом. Образец представляет собой строку, в которой могут использоваться два специальных символа: “%” и “_”. Символ “%” сопоставляется с любой строкой (возможно, пустой). Символ “_” сопоставляется с одним (ровно одним) символом. Любые другие символы, отличные от специальных, могут быть сопоставлены только со специальными символами и сами с собой. Приведем несколько примеров сопоставления строк. Пример 16
Часть GROUP BYВозможность группировки результатов запроса — одна из самых полезных и сложных для понимания. С группировкой непосредственно связаны и агрегирующие функции, хотя, как мы видели выше, в простых случаях их можно использовать и без применения группировки. Рассмотрим примеры. Пример 17 Поинтересуемся количеством юношей и девушек среди студентов: SELECT sex, COUNT(*) FROM student GROUP BY sex
Группировка всегда производится по значениям некоторых полей (в данном случае по значению одного поля sex). Результаты запроса делятся на группы, в каждой из которых значения полей, по которым производится группировка, совпадают, а в любых двух группах они различны. Далее, для каждой группы вычисляются требуемые значения агрегирующих функций. Вообще говоря, использование агрегирующих функций при наличии части GROUP BY не является обязательным. Синтаксис языка SQL этого не требует. Можно, например, выполнить следующий запрос: Пример 18 SELECT name, sex FROM student GROUP BY sex
Смысла в этом запросе, мягко говоря, немного. При его выполнении данные из таблицы student разбиваются на две группы по значению поля sex. Затем из каждой группы берется строка, которая случайно оказалась в данной группе на первом месте, и из нее извлекается поле name. То есть в таблице-результате мы не получим две группы — юношей и девушек! При использовании предложения GROUP BY таблица-результат всегда содержит столько строк, сколько имеется различных наборов значений столбцов, по которым производится группировка. Пример 19 В этом примере мы выясним средние баллы юношей и девушек: SELECT sex, AVG(score) FROM student, score WHERE student.student_id=score.student_id GROUP BY sex
Пример 20 Подсчитаем количество пропусков занятий студентами: SELECT student.student_id, student.name, COUNT(*) FROM student, absence WHERE student.student_id=absence.student_id GROUP BY student.student_id
В следующем запросе мы получим исчерпывающую информацию обо всех контрольных мероприятиях. Пример 21 SELECT date, type, MIN(score), MAX(score), AVG(score), COUNT(score) FROM score, event WHERE score.event_id=event.event_id GROUP BY date
Часть HAVINGПредложение HAVING, в котором задается условие, имеет отношение к GROUP BY и не употребляется без последнего. Принципиальное отличие предложений WHERE и HAVING заключается в том, что первое применяется до группировки, а второе — после. Допустим, нас интересуют студенты, имеющие средний балл выше среднего балла, вычисленного по всей таблице score. Сначала найдем общий средний балл: Пример 22 SELECT AVG(score) FROM score
Теперь получим требуемый список: Пример 23 SELECT student.student_id, name, AVG(score) AS average FROM student, score WHERE student.student_id=score.student_id GROUP BY student.student_id HAVING average>36.8555;
Обратите внимание на то, что нам потребовалось присвоить имя полю результата (AVG(score) AS average). Некоторые СУБД позволяют использовать агрегирующие функции непосредственно в предложении HAVING, но MySQL этого не разрешает. Впрочем, это чисто техническое ограничение. Часть ORDER BYПредложение ORDER BY — очень простое. Оно предназначено для сортировки результатов запросов по возрастанию (ASC, такой режим установлен “по умолчанию”) или убыванию (DESC). Получим тот же список, что и в примере 27, но отсортированный по именам (по возрастанию). Пример 24 SELECT student.student_id, name, AVG(score) AS average FROM student, score WHERE student.student_id=score.student_id GROUP BY student.student_id HAVING average>36.855 ORDER BY name;
Мы закончили рассмотрение команды SELECT. Разумеется, мы рассмотрели далеко не все ее возможности, полному описанию этой самой мощной команды SQL вполне можно посвятить толстую книгу. Но ключевые вопросы были нами рассмотрены. Root я или право имею?Помните то страшное красное
предупреждение на основной странице phpMyAdmin (рис.
2)? Оно имело отношение к вопросу, которым мы
сейчас займемся. Одна из важнейших функций
сервера баз данных — управление пользователями
и их правами. На одном сервере обычно хранятся
десятки, а то и сотни различных баз данных. Далее мы рассмотрим работу с базой данных sampdb из скриптов на PHP. Чтобы не работать из скриптов от имени root’а (так никогда не делают!), создадим пользователя с логином webuser (в отличие от root это не зарезервированное имя, я только что его придумал) и дадим ему право на выполнение операции SELECT в базе данных sampdb. Для этого на главной странице phpMyAdmin (рис. 2) надо выбрать ссылку “Привилегии”. Появится страница, показанная на рис. 10.
Рис. 10 Кликните по ссылке “Добавить нового пользователя” и введите имя (логин) и пароль (я ввел пароль “123456789”, но в “боевых” условиях так делать, конечно, не следует!).
Рис. 11 Теперь мы создали пользователя webuser, но прав он пока ни на что не имеет. Его единственное право — подключаться к серверу. Предоставим ему права на операцию SELECT в базе данных sampdb. Выберем пиктограмму “Редактирование привилегий” (рис. 12).
Рис. 12 На странице редактирования привилегий пользователя webuser выберем базу данных sampdb (рис. 13).
Рис. 13. И, наконец, из всевозможных прав нам требуется отметить только SELECT (рис. 14).
Рис. 14 Подключаемся к cерверу MySQL из PHPПожалуйста, остановите Denwer и создайте каталог /home/sampdb/www/, в котором мы будем размещать скрипты на этом занятии. В первом демонстрационном скрипте я собрал все самые важные функции, которые используются для взаимодействия с сервером MySQL (пояснения по использованию этих функций приведены в таблице). Файл demoAPI.php <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1251"> <title>Демонстрация основных функций API MySQL</title> </head> <body> <?php if (!mysql_connect("localhost", "webuser", "123456789")) die("Не могу подключиться к серверу MySQL"); if (!mysql_select_db("sampdb")) die("Не могу найти БД sampdb"); $query="select student_id,name,sex from student"; $result=mysql_query($query); if (!$result) die("Ошибка в запросе: ".mysql_error()); echo "<table border=1>"; while ($arr=mysql_fetch_assoc($result)) { echo "<tr><td>". $arr['student_id']. "</td><td>". $arr['name']. "</td><td>". $arr['sex']."</td></tr>"; } echo "</table>"; ?> </body> </html> Результат выполнения скрипта demoAPI показан на рис. 15.
Рис. 15
В последнем примере лекции я приведу пример скрипта, в котором запрашиваются и выводятся оценки данного студента. Вот как это выглядит (рис. 16). А вот как это реализовано:
Файл showscore.php <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1251"> <title>Запрос оценок данного студента</title> </head> <body> <?php if (!mysql_connect("localhost", "webuser", "123456789")) die("Не могу подключиться к серверу MySQL"); if (!mysql_select_db("sampdb")) die("Не могу найти БД sampdb"); ?> <h1>Запрос оценок данного студента</h1> <form action="showscore.php"> <p><select name="student_id"> <?php $query="select student_id,name from student"; $result=mysql_query($query); while ($arr=mysql_fetch_assoc($result)) { echo "<option value=".$arr['student_id'].">".$arr['name']."</option>"; } ?> <p><input type="submit" name="showscore" value="Показать оценки"> </form> <hr> <?php if (isset($showscore)) { $query="select name from student where student_id=$student_id"; $result=mysql_query($query); $arr=mysql_fetch_assoc($result); echo "Оценки студента <b>".$arr['name']."</b>"; $query="select score from score where student_id=$student_id"; $result=mysql_query($query); echo "<table border=1><tr>"; while ($arr=mysql_fetch_assoc($result)) echo "<td>".$arr['score']."</td>"; echo "</tr></table>"; } ?> </body> </html> Вопросы и заданияЭта лекция получилась очень тяжелой и насыщенной. Поэтому к ней нет никаких дополнительных вопросов и заданий. Главное — тщательно проработать лекцию и отладить все рассмотренные в ней примеры. Пишите мне, пожалуйста, при возникновении проблем. Се. Ль. Островский | ||||||||||||||||||
|
|
