Лабораторная работа 4. Взаимодействие сайтов с базами данных
 Скачать 240.39 Kb.
|
|
Основы WEB-программирования Лабораторная работа №4. «Взаимодействие сайтов с базами данных» Цель: формирование практических навыковвзаимодействия веб-страницы с базой данных. Задачи: связать выполненный в предыдущих лабораторных работах веб-сайт с базой данных. Теоретические сведения. Основная информация о MySQL. MySQL - это популярная система управления базами данных (СУБД), очень часто применяемая в сочетании с PHP. База данных представляет собой структурированную совокупность данных. Эти данные могут быть любыми - от простого списка предстоящих покупок до перечня экспонатов картинной галереи или огромного количества информации в корпоративной сети. Для записи, выборки и обработки данных, хранящихся в компьютерной базе данных, необходима система управления базой данных, каковой и является ПО MySQL. Поскольку компьютеры замечательно справляются с обработкой больших объемов данных, управление базами данных играет центральную роль в вычислениях. Реализовано такое управление может быть по-разному - как
MySQL - это система управления реляционными базами данных. В реляционной базе данных данные хранятся не все скопом, а в отдельных таблицах, благодаря чему достигается выигрыш в скорости и гибкости. Таблицы связываются между собой при помощи отношений, благодаря чему обеспечивается возможность объединять при выполнении запроса данные из нескольких таблиц. SQL как часть системы MySQL можно охарактеризовать как язык структурированных запросов плюс наиболее распространенный стандартный язык, используемый для доступа к базам данных. MySQL - это ПО с открытым кодом. Применять его и модифицировать может любой желающий. Такое ПО можно получать по Internet и использовать бесплатно. При этом каждый пользователь может изучить исходный код и изменить его в соответствии со своими потребностями. Использование программного обеспечения MySQL регламентируется лицензией GPL (GNU General Public License), http://www.gnu.org/licenses/, в которой указано, что можно и чего нельзя делать с этим программным обеспечением в различных ситуациях. Почему веб-программисты отдают предпочтение СУБД MySQL? MySQL является очень быстрым, надежным и легким в использовании. Если вам требуются именно эти качества, попробуйте поработать с данным сервером. MySQL обладает также рядом удобных возможностей, разработанных в тесном контакте с пользователями. Первоначально сервер MySQL разрабатывался для управления большими базами данных с целью обеспечить более высокую скорость работы по сравнению с существующими на тот момент аналогами. И вот уже в течение нескольких лет данный сервер успешно используется в условиях промышленной эксплуатации с высокими требованиями. Несмотря на то что MySQL постоянно совершенствуется, он уже сегодня обеспечивает широкий спектр полезных функций. Благодаря своей доступности, скорости и безопасности MySQL очень хорошо подходит для доступа к базам данных по Internet. Технические возможности СУБД MySQL. MySQL является системой клиент-сервер, которая содержит многопоточный SQL-сервер, обеспечивающий поддержку различных вычислительных машин баз данных, а также несколько различных клиентских программ и библиотек, средства администрирования и широкий спектр программных интерфейсов (API). Мы также поставляем сервер MySQL в виде многопоточной библиотеки, которую можно подключить к пользовательскому приложению и получить компактный, более быстрый и легкий в управлении продукт. Доступно также большое количество программного обеспечения для MySQL, в большей части - бесплатного. Устройство MySQL MySQL состоит из двух частей: серверной и клиентской. Сервер MySQL постоянно работает на компьютере. Клиентские программы (например, скрипты PHP) посылают серверу MySQL SQL-запросы через механизм сокетов (то есть при помощи сетевых средств), сервер их обрабатывает и запоминает результат. То есть скрипт (клиент) указывает, какую информацио он хочет получить от сервера баз данных. Затем сервер баз данных посылает ответ (результат) клиенту (скрипту). Почему всегда передается не весь результат? Очень просто: дело в том, что размер результирующего набора данных может быть слишком большим, и на его передачу по сети уйдет чересчур много времени. Да и редко когда бывает нужно получать сразу весь вывод запроса (то есть все записи, удовлетворяющие выражению запроса). Например, нам может потребоваться лишь подсчитать, сколько записей удовлетворяет тому или иному условию, или же выбрать из данных только первые 10 записей. Механизм использования сокетов подразумевает технологию клиент-сервер, а это означает, что в системе должна быть запущена специальная программа — MySQL-сервер, которая принимает и обрабатывает запросы от программ. Так как вся работа происходит в действительности на одной машине, накладные расходы по работе с сетевыми средствами незначительны (установка и поддержание соединения с MySQL-сервером обходится довольно дешево). Cтруктура MySQL трехуровневая: базы данных — таблицы — записи. Базы данных и таблицы MySQL физически представляются файлами с расширениями frm, MYD, MYI. Логически - таблица представляет собой совокупность записей. А записи - это совокупность полей разного типа. Имя базы данных MySQL уникально в пределах системы, а таблицы - в пределах базы данных, поля - в пределах таблицы. Один сервер MySQL может поддерживать сразу несколько баз данных, доступ к которым может разграничиваться логином и паролем. Зная эти логин и пароль, можно работать с конкретной базой данных. Например, можно создать или удалить в ней таблицу, добавить записи и т. д. Обычно имя-идентификатор
Поля и их типы в MySQL База данных с точки зрения MySQL (и некоторых других СУБД) - это обыкновенный каталог, содержащий двоичные файлы определенного формата - таблицы. Таблицы состоят из записей, а записи, в свою очередь, состоят из полей. Поле имеет два атрибута - имя и тип. Тип поля может быть:
Возможные типы данных, диапазоны и описания представлены в последующих таблицах: Целочисленные типы данных  Вещественные числа Вещественные типы записываются в виде: ТИП (ДЛИНА, ЗНАКИ) [UNSIGNED] Длина - это колличество знакомест, в которых будет размещено все число при его передаче, а ЗНАКИ - это колличество знаков после десятичной точки, которые будут учитываться. Если указан модификатор UNSIGNED, знак числа учитываться не будет.  Строки Любая строка - это массив символов. При поиске с помощью оператора SELECT (мы рассмотрим его далее) не учитывается регистр символов: строки "HELLO" и "Hello" считаются одинаковыми. Можно настроить MySQL на автоматическое перекодирование символов - в этом случае в базе данных строки будут храниться в одной кодировке, а выводиться - в другой.
VARCHAR(48); CHAR(73); Если 255 символов для вашей задачи недостаточно, можно использовать другие типы, например, TEXT.  Бинарные типы данных Бинарные типы данных также можно использовать для хранения текста, но при поиске будет учитываться регистр символов. К тому же, любой текстовый тип можно преобразовать в бинарный, указав модификатор BINARY: VARCHAR(30) BINARY;  Примечание: Бинарные данные не перекодируются "на лету", если установлена перекодировка символов. Дата и время  Другие типы данных MySQL рассматривать бессмысленно, поскольку применение их в PHP нецелесообразно. Операторы и команды MySQL Структурированный язык запросов SQL позволяет производить различные операции с базами данных: создавать таблицы, помещать, обновлять и удалять из них данные, производить запросы из таблиц и.т.д. Далее мы последовательно рассмотрим все эти операторы. Несмотря на то, что последний стандарт SQL принят в 1992 году, на сегодняшний день нет ни одной СУБД, где бы он полностью выполнялся. Более того, в различных базах данных часть операций осуществляется по-разному. Мы будем придерживаться диалекта SQL характерного для СУБД MySQL поэтому не все запросы могут выполняться для других баз данных. Примечание: Команды SQL не чувствительны к регистру, но традиционно они набираются прописными буквами. Создание таблиц. Оператор CREATE Создать таблицу через SQL-запрос позволяет оператор CREATE. Его синтаксис: CREATE TABLE <Имя_таблицы> (  Имя_поля1 Тип Модификатор ...  Имя_поляN Тип Модификатор [первичный ключ] [внешний ключ] )  Вообще, с помощью оператра CREATE можно создавать и другие объекты, но мы их рассматривать не будем, поскольку их применение весьма ограничено.
 Теперь создадим таблицы - "Товар", "Клиенты", "Заказы":  CREATE TABLE CLIENTS
Таблица CLIENTS содержит поля C_NO (номер клиента), FIO (Фамилия, Имя, Отчество), ADDR (Адрес), CITY (Город) и PHONE (Телефон). Все эти поля не могут содержать пустого значения (NOT NULL).
Данная таблица будет содержать данные о товарах. Тип double(9,2) означает, что 9 знаков относим под целую чать, и два - под дробную. QTY - это колличество товара на складе.
AMOUNT double(9,2) NOT NULL  );  Эта таблица содержит сведения о заказах - номер заказа (O_NO), дату заказа (DATE), номер клиента (C_NO), номер товара (T_NO), колличество (QUANTITY) и сумму всего заказа AMOUNT (то есть AMOUNT = T_NO * TOOL_PRICE). Добавление данных в таблицу. Оператор INSERT Для добавления записей используется оператор INSERT: INSERT INTO Имя_таблицы [(Список полей)] VALUES (Список констант); После выполнения оператора INSERT будет создана новая запись, в качестве значений полей будут использованы соответствующие константы, указанные в списке VALUES. Теперь добавим данные в наши таблице. Добавить данные можно с помощью оператора INSERT. Рассмотрим пример использование оператора INSERT: INSERT INTO CLIENTS VALUES (1, 'Иванов И.И.', 'Вокзальная 3', 'Москва', '09599911100'); Добавляемые значения должны соответствовать тому порядку, в котором поля перечислены в операторе CREATE. Если вы хотите добавлять информацию в другом порядке, то вы должны указать этот порядок в операторе INSERT, например: INSERT INTO CLIENTS (FIO, ADDRESS, C_NO, PHONE, CITY) VALUES ('Петров', 'Мира 29', 2, '-', 'Екатеринбург'); и помощью INSERT мы можем добавлять данные и в определенные поля, например, C_NO и FIO: INSERT INTO CLIENTS (C_NO, FIO) VALUES (1, 'Иванов'); Однако, в нашем случае сервер MySQL не выполнит такой запрос, поскольку все остальные поля равны NULL (пустое значение), а наша таблица не принимает пустые значения. Аналогично можно добавить данные в другие таблицы. В качестве примера, добавим данные в таблицу TOOLS: INSERT INTO TOOLS VALUES (1, 'Клавиатура ABC', 340.98, 5); Обратите внимание, что мы пока не указали первичные ключи таблицы, поэтому нам никто не мешает добавить в таблицу одинаковые записи. Добавить дату в поле DATE можно с помощью функции TO_DATE: INSERT INTO ORDERS VALUES (1, TO_DATE('01/03/05, 'DD/MM/YY'), 1,1,1,340.98); Данная запись означает, что первого марта 2005 года Иванов И.И. (C_NO=1) заказал одну (QUANTITY=1) клавиатуру ABC (T_NO=1). Обновление записей. Оператор UPDATE Синтаксис оператора UPDATE, который используется для обновления записей, выглядит так: UPDATE Имя_таблицы SET Поле1 = Значение1, ... , ПолеN = ЗначениеN [WHERE Условие]; Если не задано условие WHERE, будет модифицирована вся таблица, а это может повлечь за собой непредсказуемые последствия, поскольку для всех записей будут установлены одинаковые значения полей, поэтому всегда указывайте условие WHERE. Предположим, нам необходимо обновить запись, если, например, клиент Иванов переехал в другой город и нам нужно отметить это событие в базе данных. Сделаем следующее: UPDATE CLIENTS SET CITY = 'Псков' WHERE C_NO = 1; Данный запрос нужно понимать так: найти запись, поле C_NO которой = 1 (это код клиента Иванова), и установить значение CITY равным «Псков". Удаление записей. Оператор DELETE Если нам необходимо удалить всех клиентов, номера которых превышают 5, то мы поступим следующим образом: DELETE FROM CLIENTS WHERE C_NO > 5; В помощью оператора DELETE можно удалить все записи таблицы, указав условие, которое подойдет для всех записей, например: DELETE FROM CLIENTS; Если вторая часть оператора DELETE-WHERE не указана, занчит, действие оператора распространяется на все записи сразу. Выбор записей. Оператор SELECT Добавление, изменение и удаление записей - это, конечно, очень важные команды, но вы часто будете использовать оператор SELECT, который выбирает данные из таблицы. Синтаксис этого оператора более сложен: SELECT [DISTINCT|ALL] {*| [поле1 AS псевдоним] [,..., полеN AS псевдоним]} FROM Имя_таблицы1 [,..., Имя_таблицыN] [WHERE условие]  [GROUP BY список полей] [HAVING условие] [ORDER BY список полей] Мы полностью не будем рассматривать оператор SELECT, лучше это делать на конкретном примере. Сейчас мы рассмотрим оператор SELECT в общих чертах. Например, для вывода всех записей из таблицы CLIENTS сделайте следующее: SELECT * FROM CLIENTS; В результате вы получите следующий ответ сервера:  Обратите внимание на первые две записи - они одинаковые. Теоретически, добавление одинаковых записей возможно - ведь мы не указали первичный ключ таблицы. Если вы хотите исключить одинаковые записи из ответа сервера (но не из таблицы), используйте запрос: SELECT DISTINCT * FROM CLIENTS; Предположим, вы хотите вывести только фамилию и номер телефона клиента, тогда используйте следующий запрос: SELECT DISTINCT FIO, PHONE FROM CLIENTS; Если вам нужно вывести все товары, цена на которые превышает 800, то воспользуйтесь таким запросом: SELECT * FROM TOOLS WHERE PRICE > 800; Вы можете использовать следующие операторы отношений: <, >, =, <>, <=, >=. Если в вашей таблице присутствуют несколько однофамильцев, то для вывода информации обо всех из них, используйте модификатор LIKE, например: SELECT * FROM CLIENTS WHERE FIO LIKE '%Иванов%'; Приведенный запрос можно причитать так: вывести информацию о клиентах, фамилия которых похожа на 'Иванов'. Если вам необходимо выбрать данные из разных таблиц, то перед именем поля нужно указывать имя таблицы. Вот запрос, который позволяет вывести имена всех клиентов, которые хотя бы один раз покупали товар: SELECT DISTINCT CLIENTS.FIO FROM CLIENTS, ORDERS WHERE CLIENTS.C_NO = ORDERS.C_NO; Оператор SELECT позволяет ипользовать вложенные запросы, однако MySQL их не поддерживает. Внутренние функции MIN, MAX, AVG, SUM При работе с оператором SELECT вам доступны несколько очень полезных внутренних функций MySQL, вычисляющих колличество элементов (COUNT), сумму элементов (SUM), максимальное и минимальное значения (MAX и MIN), а также среднее значение (AVG). Следующие операторы выведут, соответственно, колличество записей в таблице CLIENTS, самый дорогой товар и сумму цен всех товаров: SELECT COUNT(*) FROM CLIENTS; SELECT MAX(PRICE) FROM TOOLS; SELECT SUM(PRICE) FROM TOOLS; Группировка записей Оператор SELECT позволяет группировать возвращаемые значения. Например, клиент Иванов (C_NO=1) несколько раз заказывал какой-то товар. Значит, его номер встречается в таблице ORDERS несколько раз. Другой клиент также мог сделать несколько заказов. Мы можем сгруппировать все записи по полю C_NO (номер клиента), а затем вывести сумму заказа каждого клиента. SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM FROM CLIENTS, ORDERS WHERE CLIENTS.C_NO = ORDERS.C_NO GROUP BY ORDERS.C_NO; Группировку выполняет оператор GROUP BY, который является частью оператора SELECT. Оператор GROUP BY можно ограничить с помощью HAVING. Этот оператор используется для отбора строк, возвращаемых GROUP BY. HAVING можно считать аналогом WHERE, но только для GROUP BY: HAVING <условие> Например, нас интересуют только клиенты, которые заказали товаров на общую сумму, превышающую 1500: SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM FROM CLIENTS, ORDERS WHERE CLIENTS.C_NO = ORDERS.C_NO GROUP BY ORDERS.C_NO HAVING TOTALSUM > 1500; В этом запросе мы использовали псевдоним столбца TOTALSUM. В некоторых серверах SQL для определения псевдонима не нужно писать служебное слово AS, а некоторые требуют применения знака равенства: SUM(ORDERS.AMOUNT) TOTALSUM или TOTALSUM = SUM(ORDERS.AMOUNT) Сортировка записей Пока мы не установили первичный ключ, сортировка таблицы не выполняется. Данные будут отображены в порядке их занесения в таблицу. Для сортировки по полю C_NO результата вывода таблицы CLIENTS ипользуется следующий оператор (сама таблица при этом не сортируется): SELECT * FROM CLIENTS ORDER BY C_NO; Ключи Предположим, что кто-то добавил в таблицу CLIENTS запись: 1 Сидоров Свободы 7 Калининград 0113452103 В то же время, до этого номер 1 был закреплен за Ивановым. У нас получилось, что один и тот же номер сопоставлен разным клиентам. Чтобы избежать такой путаницы, необходимо использовать первичные ключи: ALTER TABLE CUSTOMER ADD PRIMARY KEY (C_NO); После этого запроса поле C_NO может содержать только уникальные значения. В качестве первичного ключа нельзя использовать поле, доспускающее значение NULL. Создать певичный ключ можно и проще - при создании таблицы следующим образом: CREATE TABLE CLIENTS
 PRIMARY KEY (C_NO); ); Таблица ORDERS содержит сведения о заказах. По полю C_NO этой таблице идентифицируется заказчик. Предположим, что в таблицу ORDERS кто-то ввел значение, которого нет в таблице CLIENTS. Кто заказал товар? Нам нужно не допустить подобной ситуации, поэтому следует использовать подобный запрос: ALTER TABLE ORDERS ADD FOREIGN KEY(C_NO) REFERENCES CLIENTS; Введенные в таблицу ORDERS номера клиентов C_NO должны существовать в таблице CLIENTS. Аналогично нужно добавить внешний ключ по полю T_NO. Эта возможность называется декларативной целостностью. Команда ALTER используется не только для добавления ключей. Она предназначена для реорганизации таблицы в целом. Вы хотите добавить еще одно поле? Или установить список допустимых значений для каждого из полей. Все это можно сделать с помощью команды ALTER: ALTER TABLE CLIENTS ADD ZIP char(7) NULL; Этот оператор добавляет в таблицу CLIENTS новое поле ZIP типа char. Обратите внимание, что вы не можете добавить новое поле со значением NOT NULL в таблицу, в которой уже есть данные. Например, если компания работает только с клиентами Москвы и Екатеринбурга, то целесообразно ввести список допустимых значений для таблицы CLIENTS: ALTER TABLE CLIENTS ADD CONSTRAINT INVALID_STATE SHECK (CITY IN ('Москва', 'Екатеринбург')); Использование внешних ключей Теперь углубимся в изучение SQL. Вы уже знаете, как добавлять первичный ключ, теперь добавим внешний ключ при создании таблицы. Внешние ключи используются для связи одной таблицы с другой. Например, в таблице CLIENTS у нас есть два клиента - Иванов (C_NO=1) и Петров (C_NO=2). Оператор в магазине при оформлении заказа ошибся и указал несуществующий номер, например, C_NO=3. Как мы потом сможем идентифицировать клиента? Для решения такой проблемы и существуют внешние ключи: CREATE TABLE T ( /* Описание полей таблицы */ FOREING KEY KEY_NAME (LIST) REFERENCES ANOTHER_TABLE [(LIST2)] [ON DELETE OPTION] [ON UPDATE OPTION] ); Здесь:
Предположим, что в первой таблице у нас есть поля - NO и NAME - целого
FOREIGN KEY KEY_NAME (NO, NAME) REFERENCES ANOTHER_TABLE (NAME, NO) Это определение некорректно, потому что типы полей NO и NAME не совпадают. Нужно использовать такое определение: FOREIGN KEY KEY_NAME (NO, NAME) REFERENCES ANOTHER_TABLE (NO, NAME) [ON DELETE | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||