Разработка Web-приложений

5. Насколько, по вашему мнению, динамическое изменение структуры таблиц с помощью SQL эффективнее традиционного подхода?

6. Вывести номера домашних телефонов всех директоров.

7. Вывести номера сотовых телефонов всех директоров.

8. Вывести номера домашних телефонов всех факультетов.

9. Вывести название веб-сайтов высших учебных заведений.

10. Составить список владельцев всех 3-комнатных квартир.

11. Подсчитать количество сотрудников в каждом из отделений.

12. Создать представление сотрудников и объектов, которые они предлагают в аренду.



Практическое занятие №15-17.

Тема: Основы разработки интерактивного web- приложения в РНР+МYSQL

Цель работы: научиться работать с базой базой данных и соединять ее к браузеру.

Указание к работе: применение на практике и показать их на примере.

Основные характеристики MySQL

База данных -- это структурированная коллекция записей или данных, хранящихся в компьютерной системе и организованных так, что можно осуществлять быстрый поиск и извлечение нужной информации.

В названии MySQL составляющая SQL означает Structured Query Language -- язык структурированных запросов. Если характеризовать его в общих чертах, то это язык, основанный на словах английского языка и используемый также в других системах управления базами данных, например Oracle и Microsoft SQL Server. Он разработан для предоставления возможности создания простых запросов к базе данных посредством команд следующего вида:

SELECT title FROM publications WHERE author = 'Charles Dickens';

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



Таблица 1. Пример простой базы данных

Author (автор)	Title (название)	Type (категория)	Year (год)
Mark Twain (Марк Твен)	The Adventures of Tom Sawyer («Приключения Тома Сойера»)	Fiction ( Художественная )	1876
Jane Austen (Джейн Остен)	Pride and Prejudice («Гордость и предубеждение»)	Fiction ( Художественная )	1811
Charles Darwin (Чарльз Дарвин)	The Origin of Species («Происхождение видов»)	Non-Fiction ( Научная )	1856
Charles Dickens (Чарльз Диккенс)	The Old Curiosity Shop («Лавка древностей»)	Fiction ( Художественная )	1841
William Shakespeare (Вильям Шекспир)	Romeo and Juliet («Ромео и Джульетта»)	Play ( Пьеса )	1594

Каждая строка таблицы подобна строке в таблице MySQL, и каждый элемент в этой строке подобен полю MySQL.

Чтобы однозначно идентифицировать эту базу данных, в последующих примерах я буду ссылаться на нее как на базу данных publications (издания). Как вы уже заметили, все эти издания относятся к классической литературе, поэтому таблицу в базе данных, содержащую сведения о них, я буду называть classics.

Сводка понятий, используемых в базах данных

Основными понятиями, с которыми следует ознакомиться на данном этапе, являются:

Ѓ база данных -- контейнер для всей коллекции данных MySQL;

Ѓ таблица -- вложенный в базу данных контейнер, в котором хранятся сами данные;

Ѓ строка -- отдельная запись, в которой могут содержаться несколько полей;

Ѓ столбец -- имя поля внутри строки.

Следует заметить, что я не пытаюсь воспроизвести точную терминологию, используемую в учебной литературе по реляционным базам данных, а хочу лишь дать простые, обычные определения, помогающие быстро усвоить основные понятия и приступить к работе с базой данных.

Доступ к MySQL из командной строки

Работать с MySQL можно тремя основными способами: используя командную строку, применяя веб-интерфейс наподобие phpMyAdmin и задействуя такой язык программирования, как PHP. Третий из перечисленных способов будет рассмотрен в главе 10 , а сейчас изучим первые два способа.

Начало работы с интерфейсом командной строки

В следующих разделах даны соответствующие инструкции для Windows, Mac OS X и Linux.

Для пользователей Windows

Если у вас в соответствии с инструкциями, изложенными в главе 2, установлена программа Zend Server Free Edition WAMP, то доступ к исполняемой программе MySQL можно получить из следующих каталогов (первый из которых относится к 32-разрядным, а второй -- к 64-разрядным компьютерам):

C:\Program Files\Zend\MySQL55\bin

C:\Program Files (x86)\Zend\MySQL55\bin

По умолчанию начальным для MySQL будет пользователь по имени root, у которого не установлен пароль. Учитывая то, что это разработочный сервер, доступ к которому можете получить только вы, мы не станем устанавливать пароль.

Чтобы войти в интерфейс командной строки MySQL, следует выбрать команду Пуск?Выполнить и в окне запуска ввести команду CMD, после чего нажать клавишу _Enter. В результате будет вызвано командное окно Windows. Находясь в этом окне, нужно ввести следующую команду (внося в нее соответствующие коррективы):

"C:\Program Files\Zend\MySQL55\bin\mysql" -u root

"C:\Program Files (x86)\Zend\MySQL55\bin\mysql" -u root

Эта команда предписывает MySQL зарегистрировать вас как пользователя root без пароля. Теперь вы должны оказаться в среде MySQL и сможете приступить к вводу команд. Чтобы убедиться в том, что все работает должным образом, введите следующую команду, результат выполнения которой должен быть похож на показанный на рис. 1:

SHOW databases;

Рис. 1. Доступ к MySQL из командной строки Windows

Использование интерфейса командной строки

Для всего, что изложено далее в тексте главы, нет никакой разницы, из какой именно системы -- Windows, Mac OS X или Linux -- вы получаете непосредственный доступ к MySQL, поскольку все используемые команды (и сообщения об ошибках, которые могут быть получены) абсолютно одинаковы.



Точка с запятой

Начнем с самого простого. Набирая команду, вы наверняка заметили точку с запятой (;) в конце SHOW databases;? Этот символ используется в MySQL для завершения команд или отделения их друг от друга. Если забыть поставить этот символ, MySQL выдаст приглашение и будет ожидать от вас его ввода.

Запрашиваемая точка с запятой стала частью синтаксиса, позволяющего вводить длинные команды, разбивая их на несколько строк. Она также позволяет вводить сразу несколько команд, после каждой из которых стоит точка с запятой. После нажатия клавиши _Enter интерпретатор получит все эти команды в едином пакете и выполнит их в порядке следования.

На экране могут появляться шесть разных приглашений MySQL (табл. 2), позволяющих определить, на каком именно этапе многострочного ввода вы находитесь.

Таблица 8.2. Шесть приглашений к вводу команды MySQL

Приглашение MySQL	Значение
mysql>	Готова к работе и ждет ввода команды
->	Ожидание следующей строки команды
'>	Ожидание следующей строки строкового значения, которое начиналось с одинарной кавычки
">	Ожидание следующей строки строкового значения, которое начиналось с двойной кавычки
`>	Ожидание следующей строки строкового значения, которое начиналось с символа засечки (`)
/*>	Ожидание следующей строки комментария, который начинался с символов /*



Отмена команды

Если, набрав часть команды, вы решили, что ее вообще не следует выполнять, то ни в коем случае не пользуйтесь сочетанием _Ctr+C! Оно закроет программу.

Вместо нее можно ввести символы \c и нажать клавишу Enter. Порядок использования этой пары символов показан в примере 1.

Пример 1. Отмена ввода строки бессмысленная для mysql строка \c

При наборе этой строки MySQL проигнорирует все ранее введенные символы и выдаст новое приглашение. Без _\c программа выведет сообщение об ошибке.

Но этой парой символов нужно пользоваться с оглядкой: если у вас уже есть открытая строка или комментарий, то прежде чем применить _\c, вам придется их закрыть, иначе MySQL примет _\c за часть строки. В примере 2 показано, как в таком случае следует задействовать _\c.

Пример 2. Отмена ввода из строки это "бессмысленная для mysql строка" \c

Следует также заметить, что комбинация _\c после точки с запятой работать не будет, поскольку это уже будет новая инструкция.

Команды MySQL

Нам уже приходилось встречаться с командой _SHOW, которая выводит список таблиц, баз данных и многих других элементов. В табл. 3 приведен перечень наиболее востребованных команд.

Таблица 3. Подборка наиболее востребованных команд MySQL

Команда	Действие
ALTER	Внесение изменений в базу данных или таблицу
BACKUP	Создание резервной копии таблицы
\c	Отмена ввода
CREATE	Создание базы данных
DELETE	Удаление строки из таблицы
DESCRIBE	Описание столбцов таблиц
DROP	Удаление базы данных или таблицы
EXIT (Ctrl+C)	Выход
GRANT	Изменение привилегий пользователя
HELP (\h, \?)	Отображение подсказки
INSERT	Вставка данных
LOCK	Блокировка таблицы (таблиц)
QUIT (\q)	То же самое, что и EXIT
RENAME	Переименование таблицы
SHOW	Список сведений об объектах
SOURCE	Выполнение команд из файла
STATUS (\s)	Отображение текущего состояния
TRUNCATE	Опустошение таблицы
UNLOCK	Снятие блокировки таблицы (таблиц)
UPDATE	Обновление существующей записи
USE	Использование базы данных

Многие из этих команд будут рассмотрены по мере изучения этой главы, но сначала следует запомнить два важных положения, касающихся команд MySQL.

Команды и ключевые слова SQL нечувствительны к регистру. Все три команды -- CREATE, create и CrEaTe -- абсолютно идентичны по смыслу. Но чтобы было понятнее, для команд рекомендуется использовать буквы верхнего регистра.

Имена таблиц нечувствительны к регистру в Windows, но чувствительны к регистру в Linux и Mac OS X. Поэтому из соображений переносимости нужно всегда выбирать буквы одного из регистров и пользоваться только ими.

Для имен таблиц рекомендуется использовать буквы нижнего регистра или комбинацию из букв верхнего и нижнего регистра.



Создание базы данных

Если вы работаете на удаленном сервере, у вас только одна учетная запись пользователя и вы имеете допуск только к одной созданной для вас базе данных, то можете перейти к изучению пункта «Создание таблицы» далее. А если это не так, то продолжим, введя следующую команду для создания новой базы данных по имени publications:

CREATE DATABASE publications;

При успешном выполнении команды будет выведено сообщение, пока не имеющее для нас особого смысла, -- _{Query OK, 1 row affected (0.38 sec)} (Запрос выполнен, обработана 1 строка за 0,38 с), но вскоре все станет на свои места. После создания базы данных с ней нужно будет работать, поэтому даем следующую команду:

USE publications;

Теперь должно быть выведено сообщение об изменении текущей базы данных

(Database changed), и после этого база будет готова к продолжению работы со следующими примерами.

Организация доступа пользователей

Теперь, когда вы уже убедились в том, насколько просто пользоваться MySQL, и создали свою первую базу данных, настало время посмотреть на то, как происходит организация доступа пользователей, поскольку, вполне вероятно, вам не захочется предоставлять PHP-сценариям привилегированный доступ (_root) к MySQL, что грозит большими неприятностями в том случае, если кому-то вздумается взломать ваш сайт.

Для создания нового пользователя выдается команда предоставления прав -- GRANT, которая принимает следующую форму (не вздумайте все это набирать, поскольку это еще не команда):

GRANT ПРАВА ON база_данных.объект TO 'имя_пользователя@имя_хоста'

IDENTIFIED BY 'пароль';

Эта форма не должна вызвать каких-либо затруднений, быть может, за исключением фрагмента база_данных.объект. Это ссылка на саму базу данных и на содержащиеся в ней объекты, например на таблицы (табл. 4).

Таблица 4. Примерные параметры для команды GRANT

Параметр	Значение
*.*	Все базы данных и все их объекты
база_данных.*	Только база данных с именем база_данных и все ее объекты
база_данных.объект	Только база данных с именем база_данных и ее объект с именем объект

Итак, создадим пользователя, который получит доступ только к новой базе данных publications и ко всем ее объектам, и введем для этого следующую команду (заменив в ней имя пользователя jim и пароль mypasswd выбранными вами именем и паролем):

GRANT ALL ON publications.* TO 'jim'@'localhost'

IDENTIFIED BY 'mypasswd';

Эта команда предоставляет пользователю jim@localhost полный доступ к базе данных publications при использовании пароля mypasswd. Работоспособность этой установки можно проверить, если ввести команду _quit для выхода из системы, а затем перезапустить MySQL, воспользовавшись прежним способом запуска, но вместо -u root -p набрав -u jim -p или применив в этой строке созданное вами имя пользователя. В табл. 5 показаны команды, соответствующие используемой вами операционной системе, но если в вашей системе MySQL-клиент установлен в другой каталог, то в команду следует внести соответствующие коррективы.



Таблица 5. Запуск MySQL и вход в систему под именем jim@localhost

Операционная система	Пример команды
Windows	"C:\Program Files\Zend\MySQL55\bin\mysql" -u jim -p
Mac OS X	/Applications/MAMP/Library/bin/mysql -u jim -p
Linux	mysql -u jim -p

Теперь, как только появится приглашение, нужно лишь ввести свой пароль, и вход в систему будет открыт. Кстати, при желании можете поместить пароль сразу же после ключа _-p (не используя никаких пробелов). Тем самым вы избежите его ввода после появления приглашения. Но такой подход не приветствуется, поскольку если в вашей системе зарегистрировались и другие пользователи, они могут подсмотреть вводимую вами команду и получить доступ к вашему паролю.

Создание таблицы

В данный момент вы должны находиться в системе MySQL, обладать всеми (_ALL) правами, выделенными для базы данных publications (или той базы данных, которая была для вас создана), и быть готовыми к созданию своей первой таблицы. Поэтому нужно включить базу данных в работу, набрав следующую команду (и заменив publications именем своей базы данных, если оно у нее другое):

USE publications;

Теперь наберите построчно команды, которые приведены в примере 3.

Пример 3. Создание таблицы с названием classics

CREATE TABLE classics ( author VARCHAR(128), title VARCHAR(128), type VARCHAR(16), year CHAR(4)) ENGINE MyISAM;

После ввода команды MySQL должна выдать ответ: Query OK, 0 rows affected, а также показать время, затраченное на выполнение команды. Если вместо этого появится сообщение об ошибке, внимательно проверьте синтаксис команды. Должны быть на месте все скобки и запятые, а может быть, допущена какая-нибудь опечатка. Команда ENGINE MyISAM, которая своим присутствием в примере могла вызвать у вас недоумение, указывает MySQL тип механизма управления базой данных, применяемого к этой таблице.

Чтобы проверить факт создания новой таблицы, наберите команду:

DESCRIBE classics;

Если все в порядке, то вы увидите последовательность команд и ответов, показанных в примере 4, в которой особое внимание следует обратить на отображение формата таблицы.

Пример 4. Сеанс работы с MySQL: создание и проверка формата новой таблицы mysql> USE publications;

Database changed mysql> CREATE TABLE classics ( -> author VARCHAR(128),

-> title VARCHAR(128),

-> type VARCHAR(16),

-> year CHAR(4)) ENGINE MyISAM; Query OK, 0 rows affected (0.03 sec) mysql> DESCRIBE classics; +--------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+--------------+------+-----+---------+-------+

| author | varchar(128) | YES | | NULL | |

| title | varchar(128) | YES | | NULL | |

| type | varchar(16) | YES | | NULL | |

| year | char(4) | YES | | NULL | |

+--------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec )

Команда DESCRIBE является неоценимым средством отладки, когда нужно убедиться в успешном создании таблицы MySQL. Этой командой можно воспользоваться также для того, чтобы просмотреть имена полей или столбцов таблицы и типы данных в каждом из них. Рассмотрим подробнее все заголовки:

Ѓ Field -- имя каждого из полей или столбцов таблицы;

Ѓ Type -- тип данных, сохраняемых в поле;

Ѓ Null -- заголовок, который показывает, может ли поле содержать значение NULL;

Ѓ Key -- MySQL поддерживает ключи, или индексы, позволяющие ускорить просмотр и поиск данных. Под заголовком _Key показан тип применяемого ключа ( если таковой имеется);

Ѓ Default -- исходное значение, присваиваемое полю, если при создании новой строки не указано никакого значения;

Ѓ Extra -- дополнительная информация, например, о настройке поля на автоматическое приращение его значения.

Типы данных

В примере 3 можно было заметить, что для трех полей таблицы объявлены типы данных VARCHAR, а для одного -- тип данных CHAR. Термин VARCHAR означает VARiable length CHARacter string -- строка символов переменной длины, а команда воспринимает числовое значение, указывающее MySQL максимальную длину, разрешенную для строки, хранящейся в этом поле.

Этот тип данных очень удобен, поскольку позволяет MySQL планировать размер базы данных и эффективнее осуществлять просмотр и поиск данных. Но есть у него и недостаток: если присвоить строковое значение длиннее позволенного, оно будет усечено до максимальной длины, объявленной в определении таблицы.

Но у поля year (год) более предсказуемые значения, поэтому вместо VARCHAR для него используется более подходящий тип данных -- CHAR(4). Параметр 4 позволяет выделить под него 4 байта данных, поддерживающих все года от ?999 и до 9999; байт содержит 8 бит и может иметь значения от 00000000 до 11111111, что в десятичном представлении означает от 0 до 255.

Можно было бы, конечно, сохранять год и в значении, состоящем из двух цифр, но если данные не утратят своей актуальности и в следующем столетии или показатель лет каким-то образом опять вернется к нулевому значению, то эту проблему нужно решать в первоочередном порядке, поскольку она очень похожа на «проблему 2000 года», из-за которой даты начиная с 1 января 2000 года во многих крупных компьютерных системах могли быть отнесены к 1900 году.

Оба типа данных (и CHAR, и VARCHAR) принимают строки текста, ограничивая их длину размером поля. Разница между ними состоит в том, что каждая строка в поле CHAR имеет указанный размер. Если поместить в него строку меньшего размера, она будет дополнена пробелами. В поле VARCHAR дополнения текста не происходит; его размер может изменяться таким образом, чтобы в него помещался вставленный текст. Но при использовании поля VARCHAR требуется идти на небольшие издержки, чтобы отслеживать размер каждого значения. Поэтому _CHAR больше подходит для тех случаев, когда данные во всех записях имеют одинаковый размер, а VARCHAR эффективнее применять, когда размеры могут сильно отличаться друг от друга и возрастать. Но за это приходится расплачиваться тем, что доступ к данным типа VARCHAR осуществляется несколько медленнее, чем к данным типа CHAR.

Тип данных CHAR

В табл. 6 перечислены все типы символьных данных _CHAR. Все они предлагают указать параметр, устанавливающий максимальную (или точную) длину строки, которая может быть помещена в поле. Из таблицы следует, что у каждого типа есть присущее ему максимальное значение длины. Для данных типа VARCHAR длиной от 0 до 255 байт требуется еще один байт в хранилище, а для данных длиной более 256 байт требуется еще два байта.

Таблица 6. Типы данных CHAR, используемые в MySQL

Тип данных	Количество байт	Примеры
CHAR(n)	В точности равное n (< 256)	CHAR(5) Hello использует 5 байт CHAR(57) Goodbye использует 57 байт
VARCHAR(n)	Вплоть до n (< 65 536)	VARCHAR(7) Morning использует 7 байт VARCHAR(100) Night использует 5 байт

Тип данных BINARY

Тип данных _BINARY применяется для хранения строк, заполненных байтами, не имеющими никакой связи с таблицей символов (табл. 7). Например, тип данных _BINARY можно использовать для хранения изображения в формате GIF.

Таблица 7. Типы данных BINARY, используемые в MySQL

Тип данных	Количество байт	Примеры
BINARY(n) или BYTE(n)	В точности равное n (< 256)	Похож на CHAR, но содержит двоичные данные
VARBINARY(n)	Вплоть до n (? 65 536)	Похож на VARCHAR, но содержит двоичные данные

Типы данных TEXT и VARCHAR

Типы данных TEXT и VARCHAR имеют незначительные отличия друг от друга.

Ѓ До выхода версии 5.0.3 MySQL удалял из полей VARCHAR все начальные и замыкающие пробелы.

Ѓ В полях типа TEXT не может быть исходных значений.

Ѓ В столбце TEXT MySQL индексирует только первые n символов (n задается при создании индекса).

Это означает, что VARCHAR является более приемлемым и быстрее обрабатываемым типом данных, если нужно вести поиск по всему содержимому поля. Если поиск никогда не будет вестись более чем в конкретном количестве начальных символов хранящегося в поле значения, то, наверное, нужно остановить свой выбор на типе данных TEXT (табл. 8).

Таблица 8. Типы данных TEXT, используемые в MySQL

Тип данных	Количество байт	Особенности
TINYTEXT(n)	Вплоть до n (< 256)	Считается строкой с набором символов
TEXT(n)	Вплоть до n (< 65 536)	Считается строкой с набором символов
MEDIUMTEXT(n)	Вплоть до n (< 1,67e+7)	Считается строкой с набором символов
LONGTEXT(n)	Вплоть до n (< 4,29e+9)	Считается строкой с набором символов

Тип данных BLOB

Термин BLOB означает Binary Large Object -- большой двоичный объект, и поэтому, как и можно было предположить, тип данных _BLOB больше всего подходит для хранения двоичных данных, превышающих по объему 65 536 байт. Другим основным отличием BLOB от типа данных BINARY является то, что для столбцов типа BLOB нельзя задавать исходные значения (табл. 9).

Таблица 9. Типы данных BLOB, используемые в MySQL

Тип данных	Количество байт	Особенности
TINYBLOB(n)	Вплоть до n (< 256)	Считается не набором символов, а двоичными данными
BLOB(n)	Вплоть до n (< 65 536)	Считается не набором символов, а двоичными данными
MEDIUMBLOB(n)	Вплоть до n (1,67e+7)	Считается не набором символов, а двоичными данными
LONGBLOB(n)	Вплоть до n < 4,29e+9)	Считается не набором символов, а двоичными данными



Числовые типы данных

В MySQL поддерживаются различные числовые типы данных -- от одиночного байта до чисел с плавающей точкой с удвоенной точностью. Хотя для числового поля можно использовать до 8 байт, лучше все же выбрать поле с самым скромным типом данных, в котором способно уместиться наибольшее из ожидаемых вами значений.

Тогда ваша база данных будет небольшой по объему и быстрой по доступу.

В табл. 10 перечислены числовые типы данных, поддерживаемые MySQL, и диапазоны значений, которые могут содержаться в их полях. Если вы не знакомы с терминологией, поясню, что число со знаком имеет диапазон возможных значений от отрицательного до нуля и от нуля до положительного значения, а число без знака может быть в диапазоне от нуля до положительного значения. Оба они могут иметь одинаковую величину, нужно лишь представить число со знаком, сдвинутым наполовину влево, с одной половиной в отрицательном, а с другой -- в положительном диапазоне. Следует заметить, что значения с плавающей точкой (любой точности) могут быть только числами со знаком.

Таблица 10. Числовые типы данных, используемые в MySQL

Тип данных	Количество байт	Минимальное значение	Максимальное значение
		Со знаком	Без знака	Со знаком	Без знака
TINYINT	1	-128	0	127	255
SMALLINT	2	-32 768	0	32 767	65 535
MEDIUMINT	3	-8,38e+6	0	8,38e+6	1 ,67e +7
INT или INTEGER	4	?2,15e+9	0	2,15e+9	4 ,29e +9
BIGINT	8	?9,22e+18	0	9,22e+18	1 , 84e+19
FLOAT	4	?3,40e+38	Не бывает	3,40e+38	Не бывает
DOUBLE или REAL	8	?1,80e+308	Не бывает	1,80e+308	Не бывает



Чтобы указать, какой именно тип данных используется, со знаком или без знака, применяется спецификатор UNSIGNED. В следующем примере создается таблица по имени tablename, содержащая поле fieldname с типом данных UNSIGNED INTEGER:

CREATE TABLE tablename (fieldname INT UNSIGNED);

При создании числового поля можно также передать в качестве параметра необязательное число:

CREATE TABLE tablename (fieldname INT(4));

Но при этом следует помнить, что, в отличие от типов данных BINARY и CHAR, этот параметр не показывает количество байтов, выделяемых под хранение.

Может быть, это противоречит интуитивному восприятию, но на самом деле это число обозначает отображаемую ширину данных в поле при его извлечении. Оно часто используется вместе со спецификатором ZEROFILL:

CREATE TABLE tablename (fieldname INT(4) ZEROFILL);

Этот спецификатор указывает на то, что все числа шириной меньше четырех символов дополняются одним или несколькими нулями, для того чтобы ширина отображаемого поля составляла четыре символа.

Если поле уже занимает четыре и более символа, дополнение не производится.

Типы данных DATE и TIME

В табл. 11 показана еще одна важная категория типов данных, поддерживаемая MySQL, которая относится к дате и времени.

Таблица 11. Типы данных DATE и TIME, используемые в MySQL

Тип данных	Формат времени-даты
DATETIME	'0000-00-00 00:00:00'
DATE	'0000-00-00'
TIMESTAMP	'0000-00-00 00:00:00'
TIME	'00:00:00'
YEAR	0000 ( только годы 0000 и 1901-2155)

Значения, имеющие типы данных DATETIME и TIMESTAMP, отображаются одинаково. Основное различие в том, что у TIMESTAMP слишком узкий диапазон (от 1970 до 2037 года), а в DATETIME может храниться практически любая нужная дата, если только вы не интересуетесь античной историей или научной фантастикой.

Но TIMESTAMP также полезен, потому что, используя его, можно позволить MySQL установить для вас нужное значение. Если при добавлении строки не задавать значение для поля с этим типом данных, то в него автоматически будет вставлено текущее время. Можно также заставить MySQL обновлять столбец с типом данных TIMESTAMP при каждом изменении строки.

Добавление данных к таблице

Для добавления данных к таблице предназначена команда _INSERT. Рассмотрим ее в действии, заполнив таблицу classics данными из табл. 1, многократно используя одну и ту же форму команды INSERT (пример 8).

Пример 8. Заполнение таблицы classics

INSERT INTO classics(author, title, type, year)

VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');

INSERT INTO classics(author, title, type, year)

VALUES('Jane Austen','Pride and Prejudice','Fiction','1811');

INSERT INTO classics(author, title, type, year)

VALUES('Charles Darwin','The Origin of Species','Non-Fiction','1856');

INSERT INTO classics(author, title, type, year)

VALUES('Charles Dickens','The Old Curiosity Shop','Fiction','1841');

INSERT INTO classics(author, title, type, year)

VALUES('William Shakespeare','Romeo and Juliet','Play','1594');

После каждой второй строки вы должны увидеть сообщение об успешной обработке запроса -- Query OK. Как только будут введены все строки, наберите следующую команду, которая отобразит содержимое таблицы:

SELECT * FROM classics;

Результат должен быть похож на тот, что показан на рис. 4.

Рис. 4. Заполнение таблицы classics и просмотр ее содержимого

Сейчас не стоит обращать внимания на команду SELECT, ее очередь наступит в разделе «Запросы к базе данных MySQL с помощью PHP». Достаточно сказать, что в таком виде она отображает все только что введенные данные.

Теперь вернемся назад и посмотрим, как используется команда INSERT. Ее первая часть, INSERT INTO classics, сообщает MySQL, куда нужно вставлять следующие за ней данные. Затем в круглых скобках перечисляются четыре имени столбцов: _author, title, type и year, которые отделяются друг от друга запятыми.

Таким образом MySQL сообщается, что именно в эти четыре поля будут вставляться данные.

Во второй строке каждой команды INSERT содержится ключевое слово VALUES, за которым следуют четыре строковых значения, взятых в кавычки и отделенных друг от друга запятыми. Они обеспечивают MySQL теми четырьмя значениями, которые будут вставлены в четыре ранее указанных столбца. (Как и во всех остальных примерах, разбиение команды на строки было моим собственным решением, придерживаться которого не обязательно.)

Каждый элемент данных будет вставлен по порядку в соответствующие столбцы. Если порядок перечисления столбцов и данных будет случайно перепутан, данные попадут не в те столбцы. А количество указанных столбцов должно соответствовать количеству элементов данных.

Переименование таблиц

Переименование таблиц, как и любые другие изменения ее структуры или метаданных, осуществляются посредством команды _ALTER. Поэтому, чтобы, к примеру, изменить имя таблицы classics на pre1900, воспользуйтесь следующей командой:

ALTER TABLE classics RENAME pre1900;

Если применить эту команду, то потом, чтобы без изменений работали все последующие примеры текущей главы, вам придется вернуть таблице ее прежнее имя, для чего нужно будет ввести следующую команду:

ALTER TABLE pre1900 RENAME classics;

Изменение типа данных столбца

Для изменения типа данных столбца также используется команда _ALTER, но в этом случае вместе с ней применяется ключевое слово _MODIFY. Поэтому для изменения типа данных столбца year с CHAR(4) на SMALLINT (для которого потребуется только 2 байта памяти, что способствует экономии дискового пространства) нужно ввести следующую команду:

ALTER TABLE classics MODIFY year SMALLINT;

После этого, если для MySQL есть смысл конвертировать тип данных, система автоматически изменит данные, сохраняя их значение. В данном случае она заменит каждое строковое значение сопоставимым с ним целым числом, пока строку можно будет распознать как отображение целого числа.

Добавление нового столбца

Предположим, что таблица создана и заполнена большим объемом данных и тут выяснилось, что нужен еще один столбец. Не стоит расстраиваться.

Рис. 5. Добавление нового столбца pages и просмотр таблицы

Посмотрите, как можно добавить к таблице новый столбец _pages, который будет использоваться для хранения количества страниц, имеющихся в книге:

ALTER TABLE classics ADD pages SMALLINT UNSIGNED;

Эта команда добавляет новый столбец по имени _pages, в котором используется тип данных UNSIGNED SMALLINT, подходящий для хранения значений вплоть до 65 535. Этого наверняка более чем достаточно для любой когда-либо изданной книги!

И если запросить у MySQL описание обновленной таблицы, воспользовавшись показанной далее командой DESCRIBE, то можно будет увидеть внесенные в нее изменения (рис. 5): DESCRIBE classics;

Переименование столбца

Посмотрев еще раз на рис. 5, можно заметить, что наличие в таблице столбца type может привести к путанице, поскольку такое же имя используется MySQL для идентификации типа данных. Но это не проблема -- изменим имя этого столбца на category:

ALTER TABLE classics CHANGE type category VARCHAR(16);

Обратите внимание на добавление VARCHAR(16) в конце этой команды.

Это связано с тем, что ключевое слово _CHANGE требует указания типа данных даже в том случае, если вы не собираетесь его изменять, и VARCHAR(16) -- тот самый тип данных, который был указан при создании столбца type.

Удаление столбца

Поразмыслив, можно прийти к выводу, что столбец _pages, в котором хранится количество страниц, не представляет для этой базы данных особой ценности, поэтому его можно удалить, используя ключевое слово _DROP:

ALTER TABLE classics DROP pages;



Удаление таблицы

Удалить таблицу очень просто. Но, поскольку я не хочу заставлять вас заново вводить все данные в таблицу classics, мы ее удалять не станем.

Вместо этого просто создадим новую таблицу, проверим факт ее существования, а затем удалим ее, набрав команду, приведенную в примере 9. Результат выполнения всех четырех команд показан на рис. 6.

Пример 9. Создание, просмотр и удаление таблицы

CREATE TABLE disposable(trash INT);

DESCRIBE disposable;

DROP TABLE disposable;

SHOW tables;

Рис. 6. Создание, просмотр и удаление таблицы

Работа с MySQL через phpMyAdmin

Для работы с MySQL, безусловно, важно изучить все представленные здесь основные команды и особенности их работы, но после того, как они уже изучены, для управления базами данных и таблицами будет намного проще и быстрее использовать программу phpMyAdmin.

Но перед тем, как пользоваться phpMyAdmin, это средство нужно установить. Для этого вызовите Zend UI путем набора в адресной строке браузера следующей строки и регистрации (рис. 18): http://localhost:10081/ZendServer/

Рис. 18. Инструментальная панель Zend

Теперь щелкайте на левой и правой стрелках справа от области DEPLOY SAMPLE APPS (Развертывание типовых приложений), пока не увидите логотип phpMyAdmin. Щелкните на этом логотипе, чтобы инициировать загрузку; затем, по ее окончании, нажмите кнопку _Next (Далее). Нажмите еще раз такую же кнопку после просмотра информации README (Прочти меня), чтобы вызвать экран Application Details (Подробности приложения) (рис. 19).

Здесь, вероятно, нужно будет принять предлагаемые по умолчанию Display Name (Отображаемое имя) и Virtual Host (Виртуальный хост), но при этом следует указать имя каталога для phpMyAdmin, чтобы держать это средство в стороне от корневых файлов документа.

Я ввел имя phpmyadmin (полностью буквами в нижнем регистре, чтобы не приходилось вводить заглавные буквы для вызова этого средства путем набора URL-адреса).

Затем нужно продолжить настройку, нажимая кнопку Next (Далее) и принимая лицензионные соглашения до тех пор, пока не появится экран, показанный на рис. 20.

Здесь нужно установить флажок Use HTTP (Apache) Basic Authentication? (Использовать основную аутентификацию HTTP (Apache)?) и предоставить логин и пароль.

В качестве логина по умолчанию предлагается DBadmin, но я выбрал просто _admin; вы же можете выбрать логин и пароль по своему усмотрению.

Если настройки менять не нужно, можно оставить поля IP, Port (Порт), Database User (Пользователь базы данных) и Password (Пароль) в том виде, в котором они отображаются на экране.

Работа с MySQL через phpMyAdmin

Рис. 19. Настройка phpMyAdmin для Zend



Рис. 20. Ввод параметров пользователя phpMyAdmin

Теперь нажмите кнопку _Next (Далее), просмотрите показанную сводку и по готовности нажмите кнопку Deploy (Развернуть). Через несколько секунд вы увидите, что приложение было успешно развернуто, и с этого момента вы можете обратиться к phpMyAdmin путем ввода в сроку браузера следующего адреса: http://localhost/phpmyadmin

Рис. 21. Регистрация в phpMyAdmin



Это приведет к появлению диалогового окна, показанного на рис. 21, в котором перед тем, как нажать кнопку Log In (Зарегистрироваться), нужно будет ввести ваше имя пользователя и пароль. В браузере должно появиться изображение, показанное на рис. 22, и теперь вместо командной строки MySQL можно будет воспользоваться средством phpMyAdmin.

Использование phpMyAdmin

На левой панели главной страницы phpMyAdmin щелкните на раскрывающемся меню Databases (Базы данных) для выбора базы данных, с которой будете работать. Откроется база данных, и появятся ее таблицы. Отсюда можно осуществлять все основные операции над базами данных, включая их создание, добавление таблиц, индексов и многое другое. Документация по работе с программой phpMyAdmin выложена по адресу https:// docs.phpmyadmin.net.

Рис. 22. Основной экран phpMyAdmin

Если вы прорабатывали вместе со мной все примеры этой главы, то я вас поздравляю с окончанием этого длинного путешествия. Мы прошли большой путь создания базы данных MySQL до отправки сложных запросов, сочетающих несколько таблиц, использующих логические операторы и усиленных различными спецификаторами MySQL. В следующей главе мы приступим к изучению подходов к проектированию эффективных баз данных, освоим современные SQL-технологии, а также функции и транзакции MySQL. компьютерный гипертекстовый клининг браузер тег

Задания к работе

1. Создать процедуру, выполняющую подсчет количества клиентов, обратившихся в каждый из офисов. Для вывода информации воспользоваться модулем DBMS_OUTPUT для отладки программ.

2. Создать функцию, которая возвращала бы среднее количество сотрудников, работающих в каждом из отделений фирмы.

3. Создать триггер, настроенный на ввод новых строк в таблицу с описанием сотрудников. Реакцией на добавление должна быть проверка количества работающих сотрудников в заданном отделении и занесение (путем разового обновления данных соответствующего столбца ) этого количества с учетом нового сотрудника во вспомогательную таблицу. В случае превышения вычисленного значения 5 в строку с данным отделением во вспомогательной таблице следует добавить некоторое информирующее сообщение.

4. Создать динамическую процедуру, изменяющую значения заработной платы сотрудников на указанный в аргументе процент, адрес офиса с сотрудниками получающими надбавку; указывать в качестве второго входного аргумента.

5. Создать базу данных СамТУИТ

6. CGI. Проверка знания таблицы умножения и текстовый счетчик посещения страницы.

7. PHP. Выбор учебных курсов из предлагаемого перечня и формирование расписания занятий. Первоначально предлагается перечень всех курсов и все свободные дни недели. Затем выбранный курс из списка удаляется, и день недели помечается как занятый выбранным курсом. Скрипт прекращает работу, когда все дни недели заняты и расписание сохраняется в базу данных.

8. CGI. Любой психологический тест и текстовый счетчик посещения страницы.

9. PHP. Проверка таблицы умножения с ограничением по времени на каждый ответ. Вывод итогового результата для разных пользователей в базу данных.

10. CGI. Проверка знания языка HTML и текстовый счетчик посещения страницы.

11. PHP. Вывод отдельных слов иностранного языка, из которых нужно построить фразу по правилам. Рядом с каждым словом поле, в котором можно определить порядковый номер слова во фразе. Предусмотреть кнопку помощи для решения задачи. Предусмотреть вывод в базу данных.

12. CGI. Проверка знания правил дорожного движения и текстовый счетчик посещения страницы.



Практическое занятие №18

Тема: Фреймворк направленный на объект (yii, codilgniter) возможность обучения и разработка web приложений

Цель работы: научиться работать с фреймворком и применять их на практике.

Указание к работе: применить на практике использование фреймворков и показать на примере.

Запросы к базе данных MySQL с помощью mysqli

Здесь будут повторены несколько предыдущих примеров, но переписанных с использованием _mysqli. Это послужит отличной демонстрацией превращения любого встретившегося устаревшего кода во вполне современный код.

Создание файла входа в систему

Создание файла входа в систему при использовании _mysqli ничем не отличается от предыдущего варианта, поэтому файл будет выглядеть, как показано в примере 1.

Пример 1. Файл login.php

< ?php // login.php

$db_hostname = 'localhost';

$db_database = 'publications';

$db_username = 'username';

$db_password = 'password';

?>

Как и в предыдущей главе, будет использоваться база данных под названием publications, а переменным $db_username и $db_password будут присвоены имя пользователя и пароль, применяемые с MySQL.

Подключение к MySQL

После сохранения файла login.php для обращения к базе данных используется инструкция require_once, а подключение к серверу осуществляется в порядке, показанном в примере 2.

Пример 2. Подключение к серверу MySQL с помощью mysqli

< ?php require_once 'login.php';

$connection = new mysqli($db_hostname, $db_username, $db_password, $db_ database); if ($connection->connect_error) die($connection->connect_error) ?>

В данном примере путем вызова метода mysqli со всеми значениями, извлеченными из файла login.php, создается новый объект по имени $connection.

Обратите внимание на усовершенствованную проверку возникновения ошибок, достигаемую за счет ссылки на свойство $connection->connect_error. Если оно имеет значение TRUE, вызывается функция die и выводятся подробности, объясняющие характер ошибки. Свойство connect_error объекта $connection содержит строку, детализирующую ошибку подкючения.

Объект $connection будет использован для обращения к базе данных MySQL в следующих примерах.

Построение и выполнение запроса

Отправка запроса к MySQL из PHP с помощью _mysqli не сложнее вызова, используемого в расширении метода _query. Как это делается, показано в примере 3.

Пример 3. Запрос к базе данных с помощью mysqli

< ?php

$query = "SELECT * FROM classics";

$result = $connection->query($query); if (!$result) die($connection->error); ?>

Как и в главе 10, переменной $query присвоено значение, соответствующее выполняемому запросу, но в данном случае это значение передается методу объекта $connection, который возвращает результат, помещаемый в объект $result. Все, что нужно, было сделано с применением $connection и перешло в $result, чтобы можно было воспользоваться тем, что вернулось из подключения.

При ошибке значением $result окажется FALSE; в противном случае это будет объект, к которому можно будет обратиться. Свойство error объекта $connection содержит строку с подробными сведениями об ошибке.

Извлечение результата

Возвращенным $result объектом можно воспользоваться для поэлементного извлечения требуемых данных, применяя для этого метод объекта fetch_assoc. В примере 4 предыдущие примеры объединены и расширены в программу, которую можно набрать и запустить самостоятельно для получения результатов, показанных на рис. 1. Я предлагаю сохранить этот сценарий под именем query-mysqli.php.

Запросы к базе данных MySQL с помощью mysqli

Пример 4. Поэлементное извлечение результатов с помощью mysqli

< ?php // query-mysqli.php require_once 'login.php';

$connection =

new mysqli($db_hostname, $db_username, $db_password, $db_database); if ($connection->connect_error) die($connection->connect_error)

$query = "SELECT * FROM classics";

$result = $connection->query($query); if (!$result) die($connection->error); $rows = $result->num_rows;

for ($j = 0 ; $j < $rows ; ++$j)

{

$result->data_seek($j);

echo 'Author: ' . $result->fetch_assoc()['author'] . '<br>';

$result->data_seek($j);

echo 'Title: ' . $result->fetch_assoc()['title'] . '<br>';

$result->data_seek($j);

echo 'Category: ' . $result->fetch_assoc()['category'] . '<br>';

$result->data_seek($j);

echo 'Year: ' . $result->fetch_assoc()['year'] . '<br>';

$result->data_seek($j);

echo 'ISBN: ' . $result->fetch_assoc()['isbn'] . '<br><br>'; }

$result->close();

$connection->close();

?>

В этом коде перед извлечением каждого элемента данных для поиска нужной строки при очередном проходе цикла вызывается метод data_seek объекта $result.

Затем для извлечения значения, сохраненного в каждой ячейке, вызывается метод fetch_assoc, а для вывода результата используются команды echo.

Процедурное использование mysql

Вы, наверное, согласитесь с тем, что весь этот поиск данных выглядит слишком громоздким и должен быть какой-то более эффективный метод достижения того же результата. Разумеется, такой метод, осуществляющий построчное извлечение, существует.

Извлечение строки

Для построчного извлечения данных нужно заменить цикл из примера 4 кодом, выделенным в примере 5 полужирным шрифтом, и обнаружится, что будет получен точно такой же результат, который был показан на рис. 1.

Переделанный файл можно сохранить под именем fetchrow-mysqli.php.



Рис. 1. Результат запуска на выполнение кода примера 4

Пример 5. Построчное извлечение результатов с помощью mysqli

< ?php //fetchrow-mysqli.php require_once 'login.php';

$connection =

new mysqli($db_hostname, $db_username, $db_password, $db_database); if ($connection->connect_error) die($connection->connect_error);

$query = "SELECT * FROM classics";

$result = $connection->query($query); if (!$result) die($connection->error); $rows = $result->num_rows;

for ($j = 0 ; $j < $rows ; ++$j)

{

$result->data_seek($j);

$row = $result->fetch_array(MYSQLI_ASSOC);

echo 'Author: ' . $row['author'] . '<br>'; echo 'Title: ' . $row['title'] . '<br>'; echo 'Category: ' . $row['category'] . '<br>'; echo 'Year: ' . $row['year'] . '<br>';

Запросы к базе данных MySQL с помощью mysqli

echo 'ISBN: ' . $row['isbn'] . '<br><br>'; }

$result->close();

$connection->close();

?>

В этом измененном коде к объекту $result делается только одна пятая часть обращений, а при каждом проходе цикла в объекте осуществляется только один поиск, поскольку посредством метода fetch_array каждая строка извлекается целиком. В результате одна строка данных возвращается в виде массива, который затем присваивается массиву $row.

Метод fetch_array в зависимости от переданного ему значения может возвращать три типа массивов.

Ѓ MYSQLI_NUM -- числовой массив. Каждый столбец появляется в массиве в порядке его определения при создании (или изменении) в таблице. В нашем случае нулевой элемент массива содержит столбец Author, первый элемент содержит Title и т. д.

Ѓ MYSQLI_ASSOC -- ассоциативный массив. Каждый ключ является названием столбца. Поскольку ссылка на элементы данных производится по названию столбца (а не по номеру индекса), используйте этот вариант в своем коде везде, где это возможно, чтобы упростить отладку и помочь другим программистам управиться с вашим кодом.

Ѓ MYSQLI_BOTH -- ассоциативный и числовой массив.

Ассоциативные массивы обычно полезнее числовых, поскольку на каждый столбец можно ссылаться по названию, например $row['author'], вместо того чтобы пытаться вспомнить, где он находится в порядке следования столбцов. Поэтому в данном сценарии используется ассоциативный массив, что заставляет нас передать MYSQLI_ASSOC.

Закрытие подключения

Со временем, после завершения работы сценария, PHP вернет память, распределенную под объекты, поэтому при небольших по размеру сценариях обычно не приходится волноваться об освобождении памяти своими силами. Но если распределено большое количество объектов с результатами обращения к базе или извлечен большой объем данных, было бы неплохо освободить задействованную память, чтобы избежать возможных будущих проблем в своем сценарии.

В частности, важность такого освобождения возрастает для страниц с высоким трафиком, поскольку объем памяти, затрачиваемый в сеансе, может быстро возрастать. Поэтому в предыдущих сценариях не забывайте вызывать методы close объектов $result и $connection, как только минует надобность в каждом из объектов.

В идеале нужно закрывать каждый объект с результатами по окончании его использования, а затем, когда ваш сценарий больше не будет обращаться к MySQL, закрывать и объект подключения к базе данных.

Такой подход обеспечит возвращение ресурсов в систему как можно скорее, чтобы сохранить оптимальный режим работы MySQL, и устранит сомнения насчет возвращения со стороны PHP неиспользуемой памяти ко времени возникновения новой потребности в ней.

Практический пример

Теперь перепишем процедурную программу sqltest.php из предыдущей главы с использованием _mysqli. Переделка не представляет особого труда, в чем можно убедиться, просмотрев код примера 6 (который нужно сохранить в файле mysqlitest.php, если вы собираетесь его протестировать, поскольку он постоянно вызывает сам себя).

Пример 6. Вставка и удаление с помощью mysqlitest.php

< ?php // mysqlitest.php require_once 'login.php';

$connection =

new mysqli($db_hostname, $db_username, $db_password, $db_database); if ($connection->connect_error) die($connection->connect_error);

if (isset($_POST['delete']) && isset($_POST['isbn']))

{

$isbn = get_post($connection, 'isbn');

$query = "DELETE FROM classics WHERE isbn='$isbn'";

$result = $connection->query($query);

if (!$result) echo "Сбой при удалении данных: $query<br>" .

$connection->error . "<br><br>";

}

if ( isset($_POST['author']) && isset($_POST['title']) && isset($_POST['category']) && isset($_POST['year']) && isset($_POST['isbn']))

{

$author = get_post($connection, 'author');

$title = get_post($connection, 'title');

$category = get_post($connection, 'category');

$year = get_post($connection, 'year');

$isbn = get_post($connection, 'isbn'); $query = "INSERT INTO classics VALUES" .

"('$author', '$title', '$category', '$year', '$isbn')";

$result = $connection->query($query);

if (!$result) echo "Сбой при вставке данных: $query<br>" .

$connection->error . "<br><br>";

} echo <<<_END

Практический пример

<form action="mysqlitest.php" method="post"><pre>

Author <input type="text" name="author">

Title <input type="text" name="title">

Category <input type="text" name="category">

Year <input type="text" name="year">

ISBN <input type="text" name="isbn">

<input type="submit" value="ADD RECORD">

</pre></form>

_END;

$query = "SELECT * FROM classics";

$result = $connection->query($query); if (!$result) die ("Сбой при доступе к базе данных: " . $connection->error); $rows = $result->num_rows;

for ($j = 0 ; $j < $rows ; ++$j)

{

$result->data_seek($j);

$row = $result->fetch_array(MYSQLI_NUM);

echo <<<_END

<pre>

Author $row[0]

Title $row[1]

Category $row[2]

Year $row[3]

ISBN $row[4]

</pre>

<form action="mysqlitest.php" method="post">

<input type="hidden" name="delete" value="yes">

<input type="hidden" name="isbn" value="$row[4]">

<input type="submit" value="DELETE RECORD"></form>

_END;

}

$result->close();

$connection->close();

function get_post($connection, $var)

{

return $connection->real_escape_string($_POST[$var]);

}

?>

В первых двух строках внедряется код из login.php и создается объект $connection для получения доступа к базе данных. Затем идет код для удаления записи, в котором просто выдается команда DELETE для объекта $connection с использованием метода _query, и, в случае возникновения проблемы, возвращается сообщение об ошибке.

Затем, если новые данные отправляются программе, выдается команда INSERT и опять для объекта $connection используется метод query. В обоих экземплярах кода результат данной операции получает объект $result, значением которого может быть либо TRUE, либо FALSE.