Lab_MYSQL_УИ. Лабораторная работа Базы данных Mysql в рнр на примере телефонного справочника
 Скачать 479.8 Kb.
|
| ||||||||||||||||||||
Лабораторная работа Базы данных MySQL в РНР | Уровень | Соответствие терминов | | |
| Предметный | Сущность | Экземпляр | Свойство |
| Математический | Отношение | Кортеж | Атрибут |
| Базы данных | Таблица | Строка | Столбец |
| Физический | Файл | Запись | Поле |
Модели клиент-сервер – это технология взаимодействия в информационной сети. Сервер обладает правом управления тем или иным ресурсом, а клиент – пользования им. Каждый конкретный сервер определяется видом того ресурса, которым он владеет. Например, назначением сервера баз данных является обслуживание запросов клиентов, связанных с обработкой данных; файловый сервер, или файл-сервер, распоряжается файловой системой.
Выделяются четыре подхода, реализованные в следующих моделях:
Модель файлового сервера (File Server - FS);
Модель доступа к удаленным данным (Remote Data Access - RDA);
Модель сервера баз данных (Data Base Server - DBS);
Модель сервера приложений (Application Server - AS).
Базы данных состоят из таблиц, таблицы состоят из записей (строк), записи состоят из полей (Таблица 1).
Ключ - совокупность атрибутов, однозначно идентифицирующую строки.
Индекс – некоторая хранимая нумерация записей по заданным условиям сортировки записей таблицы. Различают простые и составные индексы.
Нормализация отношений – это процесс пошаговой декомпозиции (разложение) исходных отношений на более простые, использующие далее неделимые данные, с целью устранения нежелательных зависимостей атрибутов, а вместе с тем избыточности данных. Такой процесс обозначается как приведение данных к нормальной форме (НФ).
1 НФ - основывается на неделимости исходных данных. Все атрибуты зависят от ключа, но степень зависимости разная. При составном ключе атрибут может зависеть от части ключа. Возникает частичная зависимость.
Пример 1
Отношение С (Поставщик, Товар, Город, Факс, Телефон, Стоимость
трансп.,
Объем поставки.
Составной ключ: Поставщик + Товар Зависимость атрибутов:
Город, Факс, Телефон, Стоимость транспортировки.
Зависит от части Поставщик, что ведет к дублированию данных, если поставщик поставляет не один товар.
Делим на две таблицы
Поставщик, Товар, Объем поставки
Поставщик, Город, Факс, Телефон, Стоимость транспортировки.
НФ – преобразование, исключающую частичную зависимость атрибутов. Отсутствуют неполные зависимости от ключа. Частичные зависимости выделяются в отдельные таблицы.
В отношение Е атрибут Стоимость транспортировки зависит от ключа не прямо, а через зависимость от атрибута Город, т.е. транзитивно.
НФ – преобразование, исключающее все транзитивные зависимости не ключевых атрибутов.
При выполнении лабораторных работ, необходимо, не только более подробно рассмотреть первые три нормальные формы, но и изучить другие нормальные формы более высокого порядка.
В настоящее время язык SQL из языка структурированных запросов для получения некоторых видов отображения данных преобразован в полноценные серверные системы управления данными (например, MySQL) и стал обязательной частью большинства современных СУБД.
Начиная изучение SQL, необходимо обратить внимание на две главные конструкции SQL [1, 2]. Это общий формат оператора выборки и
многотабличные запросы
Общий формат оператора выборки SELECT:
SELECT [DISTINCT | ALL] {* | [column [AS new_column_name]] [, …]}
FROM table [alias] [,…]
[WHERE condition]
[GROUP BY list [HAVING condition]]
[ORDER BY list]
Здесь использованы следующие обозначения:
column – имя столбца (или константа, или выражение);
DISTINCT – результат не будет содержать строк-дубликатов;
ALL – результат может содержать дублирующие строки (по умолчанию);
* – все столбцы;
table - имя таблицы;
alias – сокращение для имени таблицы;
condition – условие фильтрации строк данных;
list – список столбцов;
Порядок секций ( предложений) в операторе SELECT должен строго соблюдаться (например, GROUP BY должно всегда предшествовать ORDER BY), иначе это приведет к появлению ошибок. Обязательными секциями оператора являются первые две: SELECT и FROM.
В предложении SELECT, с которого начинаются все инструкции SELECT, необходимо указать элементы данных, которые будут возвращены
в результате запроса. Эти элементы задаются в виде списка возвращаемых столбцов (column), разделенных запятыми. Для каждого элемента из этого списка в таблице результатов запроса будет создан один столбец. Столбцы в таблице результатов будут расположены в том же порядке, что и элементы
списка возвращаемых столбцов. Возвращаемый столбец может представлять собой:
имя столбца – один из столбцов, содержащихся в исходных таблицах; в результат подставляются значения из указанного столбца;
константу – в каждой строке результатов запроса должно содержаться одно и то же константное значение;
выражение – СУБД будет вычислять значение, помещаемое в таблицу результатов запроса, по формуле, заданной выражением, которые могут состоять из имен столбцов, констант, скобок ('(', ')'), скалярных операторов ( например: +, -, *, /); при попытке выполнения арифметических операций над столбцами, содержащие данные неподходящих типов ( например, текст), будет выдано сообщение об ошибке.
Предложение FROM состоит из ключевого слова FROM, за которым следует список таблиц, разделенных запятыми. Для каждой таблицы в списке FROM можно задать псевдоним (сокращение), для облегчения описания ссылок на таблицы.
Многотабличные запросы:
Для выборки значений из нескольких таблиц БД используются многотабличные запросы. В многотабличных запросах используются операции соединения таблиц и, соответственно, в основе всех многотабличных запросов лежит операция декартово произведение.
Процедура выполнения многотабличного запроса состоит в следующем:
выполняется секция FROM (формируется декартово произведение таблиц и выполняется соответствующее соединение ( если это задано синтаксисом секции FROM));
выполняется секция WHERE;
выполняется секция GROUP BY … HAVING;
выполняется секция SELECT (формируются результирующие строки); 5) выполняется секция ORDER BY.
Виды соединений таблиц ( соответствуют видам соединений из реляционной алгебры):
Декартово произведение двух таблиц:
SELECT t1.*, t2.* FROM t1, t2;
Тета-соединение таблиц (используются знаки сравнения, на практике используется редко, так как трудно найти смысл соединения): SELECT * FROM t1, t2 WHERE (t1.number > t2.number);
Экви-соединение таблиц (выполняется по равенству значений общего атрибута, например значений первичного и внешнего ключа): SELECT t1.*, t2.* FROM t1, t2 WHERE (t1.number = t2.number); или эквивалентный вариант соединения (inner или natural join):
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON (t1.number = t2.number);
Внешние соединения таблиц (левое, правое и полное, соответственно):
SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.number = t2.number;
SELECT t1.*, t2.* FROM t1 RIGHT JOIN t2 ON t1.number = t2.number;
SELECT t1.*, t2.* FROM t1 FULL JOIN t2 ON t1.number = t2.number;
Кроме приведенных выше соединений, есть различные нестандартные применения многотабличных запросов, например найти все пары студентов имеющих один и тот же рейтинг (запрос формируется для одной таблицы t1):
SELECT A.name, B.name, A.rating FROM t1 A, t1 B WHERE A.rating =
B.rating;
Здесь псевдоним существует только на время выполнения SELECT. C увеличением количества таблиц в запросе резко возрастает объем работы, необходимой для выполнения запроса. Хотя ограничений на количество объединяемых таблиц нет, на практике высокие затраты на обработку многотабличных запросов во многих приложениях накладывают серьёзные ограничения на использование многотабличных запросов. В запросах, для которых время выполнения критично, число используемых таблиц обычно не превышает трех.
Операции UNION (объединение), INTERSECT (пересечение) и EXCEPT (разность) представляют собой операции над множествами элементов, которые рассмотрены в реляционной алгебре. Данные операции позволяют комбинировать результаты двух и более запросов в единую результирующую таблицу. Формат записи операторов следующий:
(SELECT …)
operator [ALL] [CORRESPONDING [BY {column1 [, …]}]]
(SELECT …)
Здесь:
(SELECT …) – подзапросы, возвращающие данные для выполнения оператора; таблицы сформированные этими подзапросами должны быть совместимы по соединению – т.е. они должны иметь одну и ту же структуру (смысл данных должен поддерживаться пользователем);
operator – один из поддерживаемых (UNION, INTERSECT, EXCEPT (MINUS));
ALL – при указании в результирующей таблице будут оставлены дубликаты строк (по умолчанию дубликаты строк удаляются);
CORRESPONDING – операция выполняется для общих столбцов таблиц (если указан список столбцов, то операция выполняется для указанных столбцов);
Например:
(SELECT * FROM student1)
UNION CORRESPONDING name
(SELECT * FROM student2);
Другие синтаксические конструкции запросов SQL следует изучить по учебникам баз данных [1, 2] и экспериментально проверить в упражнениях лабораторных работ. Понадобятся знания HTML для разметки Web-страницы, использования различных объектов (полей ввода-вывода, кнопок управления, переключателей и др.), функций PHP для выполнения запросов SQL [3, 4].
Общая информация о СУБД MySQL
MySQL − это программное обеспечение (ПО) с открытым кодом, применять и модифицировать его может любой пользователь. Использование программного обеспечения MySQL регламентируется лицензией GNU General Public License.
СУБД MySQL5 является частью широко известного пакета denwer. В документации пакета, denwer определяется каr джентльменский набор Webразработчика ("Д.н.w.р", читается "Денвер" — почти как название города) — самый известный проект Лаборатории dk (http://dklab.ru), набор дистрибутивов (Apache+SSL, PHP5 в виде модуля, MySQL5, phpMyAdmin и т.д.) и программная оболочка, используемые Web-разработчиками (программистами и дизайнерами) для отладки сайтов на "домашней" (локальной) Windows-машине без необходимости выхода в Интернет. Денвер работает в ОС Windows XP/Vista/7/8/10.
В качестве альтернативной набору программных средств среды Денвер возможно использование известного пакета open_server_5_2_2_ultimate.exe.
Ключевая особенность Денвера - поддержка работы сразу с несколькими проектами, каждый из которых располагается на отдельном виртуальном хосте. Виртуальные хосты для проектов создаются автоматически: например, вам достаточно скопировать файлы проекта в /home/ИмяПроекта/www, и он тут же станет доступен по адресу http://ИмяПроекта. Все компоненты Денвера уже настроены и готовы для работы (в частности, корректно настроена русскоязычная кодировка MySQL, SSL и т.д.). Схема именования директорий может быть легко настроена персонально на ваш хостинг в шаблоне виртуальных хостов (обязательно прочитайте документацию denwer).
Упражнение 1. Установка программного обеспечения для работы с базами данных MySQL
Начинаем с установки пакета denwer. Выполняем запуск установочного файла. Например:
Denwer3_Base_2012-09-16_a2.2.22_p5.3.13_m5.5.25_pma3.5.1_xdebug.exe.
Следуя инструкциям, довести установку до получения окна: Денвер установлен успешно!.htm. Перейти по ссылке http://localhost/ к окну: Ура, заработало!.htm. Сохранить страницу и в ней проверить основные службы локального Web-сервера. Нас будет интересовать прежде всего служба:
«Проверка MySQL и phpMyAdmin» (Рис. 1) для работы с базами данных.
Внимательно изучите все возможности администрирования базами данных.
Рис.1. Панель администратора локального хостинга.
Вызов панели администратора выполняется на странице http://localhost переходом по ссылке http://localhost/Tools/phpMyAdminдля проверки MySQL и phpMyAdmin. Используя команды Создать новую БД, Создать новую таблицу в БД, Вставить (новые записи) создать базу данных phone и таблицу Tb1 с полями Nom и Fam (Рис. 2), используя инструменты панели администратора в Denwer.
Команды Обзор позволяет просматривать, добавлять и редактировать записи, а команда Структура - добавлять, настраивать и удалять поля таблиц с данными.
Рис. 3. Таблица базы данных MySQL
Затем переходим к установке удобного программного обеспечения для работы со сценариями управления базами данных.
Упражнение 2. Установка Web-редакторов для удобной работы со сценариями РНР
Обязательным шагом к управлению базами данных с помощью сценариев РНР является установка двух Web-редакторов (Рис.4).
Первый Web-редактор (Рис.4) устанавливается из инсталляционного файла pspad457inst_en.exe. Установщик распространяется бесплатно по лицензии GNU General Public License и скачивается с официального сайта http://www.pspad.com. Имеет хороший справочник на английском языке, но это не проблема при наличие Google-переводчика или других систем переводов с иностранных языков.
Этот Web-редактор отличается хорошим набором синтаксических конструкций для разных языков программирования, подсветка синтаксиса, парных скобок, наличием различных удобных инструментов для работы со сценариями (Рис.2). Особенно удобной для отладки сценариев является нумерация строк, а яркое цветовое решение меньше утомляет зрение
Рис. 4 Web-редактор PSPad.
Второй Web-редактор устанавливается из инсталляционного файла npp.6.7.5.Installer.exe. Установщик распространяется бесплатно по лицензии GNU General Public License и скачивается с официального сайта http://notepadplus-plus.org.
Web-редактор обладает хорошими средствами отладки и отличается очень удобными и достоверными инструментами в верхнем меню
(Кодировать, Преобразовать) для работы с кодировками (Рис.5). Обязательно поэкспериментируйте и используйте для правильного сохранения сценариев.
Справочник команд загружается с официального сайта.
Упражнение 3. Создание базы и таблиц MySQL в сценариях РНР
Переходим к к управлению данными с помощью запросов MySQL, выполняемых в сценариях РНР.
В папке C:/ WebServers/home/localhost /www/my_phone создать файл create.php:
Кафедра УИ
Лабораторная работа
$key1 =0;
echo " Создание базы данных на сервере MySQL
"."
"."
";
$sdb_name = "localhost";
$user_name="root";
$user_pass = "";
$db_name = "Phone";
// Соединение с сервером
$link=mysql_connect($sdb_name,$user_name,$user_pass); echo "Report MySQL-server"."
"; if (!$link)
{ echo "Нет соединения с MySQL-server
";
exit();}
echo "Есть соединение с MySQL-server
";
// Создание базы данных
$str_sql_query = "CREATE DATABASE $db_name";
echo " Message: ".$str_sql_query;
if (!mysql_query($str_sql_query, $link))
{ echo "
Не создали новую базу (уже существует)
";}
// Выбор базы данных
if (!mysql_select_db($db_name,$link))
{echo " Не выбрали базу данных - не нашли."."
";
exit();}
echo "
Выбрали и открыли базу данных
";
// Создание таблицы
mysql_query("CREATE TABLE Tb1(nom TEXT(20), fam TEXT(50))") or die ("
Ошибка создания таблицы
".mysql_error());
mysql_close($link);
?>
_ Кафедра УИ
Лабораторная работа
$key1 =0;
echo " Работа с данными на сервере MySQL
"."
"."
";
$sdb_name = "localhost";
$user_name="root";
$user_pass = "";
$db_name = "Phone";
// Соединение с сервером $link=mysql_connect($sdb_name,$user_name,$user_pass);
echo "Report MySQL-server"."
"."
";
if (!$link) { echo "Not connect MySQL-server"."
"; exit();} echo "Connect MySQL-server - Yes"."
";
// Выбор базы данных Nom Fam if (!mysql_select_db($db_name,$link))
{echo "Not find for use data base"."
"; exit();
}
echo "Open data base - Yes"."
";
// Запрос к таблице
$str_sql_query= "SELECT * FROM Tb1";
// Запрос к таблице
if (!$result=mysql_query($str_sql_query,$link))
{ echo "Not RUN query Tb1"."
"; exit();
} echo "Query Table data base - Yes"."
";
//ДОБАВЛЕНИЕ – ОБНОВЛЕНИЕ - УДАЛЕНИЕ ЗАПИСЕЙ (снимаем
комментарий только для одного варианта)
// переменные для обновления полей в записи
$newnom='203040';
$newfam='FAMILY5';
// ДОБАВЛЕНИЕ ЗАПИСЕЙ
// Вставляем строку новой записи 1 вариант
//
$str_sql_query="INSERT
INTO Tb1 SET Nom='Новый номер', Fam='Новая Фамилия'";
//
$str_sql_query="INSERT INTO Tb1 SET Nom='$newnom',
Fam='$newfam'";
// Вставляем строку новой записи 2 вариант
// $str_sql_query="INSERT INTO Tb1(nom, fam) VALUES
('$newnom','$newfam')";
// ОБНОВЛЕНИЕ ЗАПИСЕЙ - замена фамилии по номеру телефона
// $str_sql_query="UPDATE Tb1 SET fam = 'Это замена' WHERE nom='55555555' ";
// УДАЛЕНИЕ ЗАПИСЕЙ с указанным номером
// $str_sql_query="DELETE FROM Tb1 WHERE nom='55555555' ";
// Запрос к таблице для добавления, обновления и удаления записей
// Выполняем запрос для обновления записей if (!mysql_query($str_sql_query,$link))
{ echo "Не могу выполнить запрос к Tb1 на добавление записи"."
"; exit();
} echo "Запрос выполнен успешно!"."
";
// ДОБАВЛЕНИЕ – ОБНОВЛЕНИЕ - УДАЛЕНИЕ ЗАПИСЕЙ (конец блока)
$vsego=mysql_num_rows($result);
print "Всего выбрано записей: ".$vsego;
while ($mas=mysql_fetch_row($result)):
{ foreach ($mas as $field)
{ $key1 = $key1+1; $array_name[$key1] = $field;
}
};
endwhile;
echo "
Вывод из таблицы Tb1 базы данных
Phone
"; echo "
ALIGN=CENTER>\n";
echo "
//$vsego
//for ($i=1; $i<=10; $i++) for ($i=1; $i<=$vsego; $i++)
// Формирование строк
{ echo "
for ($j=1; $j<=2; $j++)
// Формирование столбцов
{ $key1 =$key1+1;
print "
echo "
"; }
echo "
?>
_ Кафедра УИ
Лабораторная работа
"."
"."
";
$sdb_name = "localhost";
$user_name="root";
$user_pass = "";
$db_name = "Phone";
// Соединение с сервером
$link=mysql_connect($sdb_name,$user_name,$user_pass); echo "Report MySQL-server"."
"."
";
if (!$link) { echo "Not connect MySQL-server"."
";
exit();}
echo "Connect MySQL-server - Yes"."
";
// Выбор базы данных
if (!mysql_select_db($db_name,$link))
{echo "Not find for use data base"."
"; exit();}
echo "Open data base - Yes"."
";
// Запрос к таблице
$str_sql_query= "SELECT * FROM Tb1";
if (!$result=mysql_query($str_sql_query,$link))
{echo "Not RUN query"."
"; exit();}
echo "Query Table data base - Yes"."
";
while ($mas=mysql_fetch_row($result)):
{ foreach ($mas as $field) { $key1 = $key1+1;
$array_name[$key1] = $field;} };
endwhile;
echo "
Вывод из таблицы Tb1 базы данных Phone
"; echo "
| Телефон | Фамилия |
| ".$array_name[$key1]." | "; }
mysql_close($link);
?>
Кафедра УИ
Лабораторная работа
$key1 =0;
echo " Создание базы данных на сервере MySQL "."
"."
";
$sdb_name = "localhost";
$user_name="root";
$user_pass = "";
$db_name = "phone";
// Соединение с сервером
$link=mysql_connect($sdb_name,$user_name,$user_pass); echo "Report MySQL-server"."
";
if (!$link) { echo "Нет соединения с MySQL-server
";
exit();} echo "Есть соединение с MySQL-server
";
// Создание базы данных
$str_sql_query = "CREATE DATABASE $db_name";
echo " Message: ".$str_sql_query;
if (!mysql_query($str_sql_query, $link))
{ echo "
Не создали новую базу (уже существует)
";}
// Выбор базы данных
if (!mysql_select_db($db_name,$link))
{echo " Не выбрали базу данных - не нашли."."
"; exit();}
echo "
Выбрали и открыли базу данных
";
// Создание таблицы
mysql_query("CREATE TABLE Tb2(nom TEXT(20), sum TEXT(30))") or die ("
Ошибка создания таблицы
".mysql_error()); mysql_close($link);
?>
Кафедра УИ
Лабораторная работа
$key1 =0;
echo " Просмотр данных одной таблицы на сервере MySQL
"."
"."
";
$sdb_name = "localhost";
$user_name="root";
$user_pass = "";
$db_name = "Tabl1";
// Соединение с сервером $link=mysql_connect($sdb_name,$user_name,$user_pass);
echo "Report MySQL-server"."
"."
";
if (!$link) { echo "Not connect MySQL-server"."
";
exit();} echo "Connect MySQL-server - Yes"."
";
// Выбор базы данных Nom Fam
if (!mysql_select_db($db_name,$link))
{echo "Not find for use data base"."
"; exit();
}
echo "Open data base - Yes"."
";
// Запрос к таблице
$str_sql_query= "SELECT * FROM Tb2";
// Запрос к таблице
if (!$result=mysql_query($str_sql_query,$link))
{ echo "Not RUN query Tb1"."
"; exit();
} echo "Query Table data base - Yes"."
";
// ОБНОВЛЕНИЕ ЗАПИСЕЙ * снимаем комментарий только для одного варианта
// переменные для обновления полей в записи
//$newnom='203040';
//$newsum='100';// ДОБАВЛЕНИЕ ЗАПИСЕЙ
// Вставляем строку новой записи 1 вариант
// $str_sql_query="INSERT INTO Tb1 SET Nom='Новый номер', sum='Новая сумма";
// $str_sql_query="INSERT INTO Tb1 SET Nom='$newnom',
sum='$newsum'";
// Вставляем строку новой записи 2 вариант
// $str_sql_query="INSERT INTO Tb1(nom, sum) VALUES
('$newnom','$newsum')";
// ОБНОВЛЕНИЕ ЗАПИСЕЙ - замена фамилии по номеру телефона
// $str_sql_query="UPDATE Tb1 SET sum = '1500' WHERE
nom='55555555' ";
// УДАЛЕНИЕ ЗАПИСЕЙ с указанным номером
// $str_sql_query="DELETE FROM Tb1 WHERE nom='55555555' ";
// Запрос к таблице для добавления, обновления и удаления записей
// Выполняем запрос для обновления записей
if (!mysql_query($str_sql_query,$link))
{ echo "Не могу выполнить запрос к Tb1 на обновление"."
"; exit();
} echo "Запрос выполнен успешно!"."
";
// ОБНОВЛЕНИЕ ЗАПИСЕЙ * конец блока
$vsego=mysql_num_rows($result);
print "Всего выбрано записей: ".$vsego;
while ($mas=mysql_fetch_row($result)):
{ foreach ($mas as $field)
{ $key1 = $key1+1;
$array_name[$key1] = $field;
}
};
endwhile;
echo "
Вывод из таблицы Tb2 базы данных
Phone ";
echo "
ALIGN=CENTER>\n";
echo "
$key1 =0;
//$vsego
//for ($i=1; $i<=10; $i++)
for ($i=1; $i<=$vsego; $i++)
{ echo "
for ($j=1; $j<=2; $j++)
{ $key1 =$key1+1; print "
echo "
"; }
echo "
';