4. Базы данных и основы SQL

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

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

Таблица 8.2. Реестр экзаменационных оценок

 

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

Каждый отдельный элемент данных в таблице, например, оценка студента, является полем таблицы. Объединение полей (в рассматриваемом примере это имя и оценка) представляет собой запись.

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

В простейшей форме база данных представля­ет собой набор из одной или нескольких таблиц. Для доступа к данным в табли­цах обычно используется язык SQL.

Для работы с базами данных обычно используется язык SQL (Structured Query Language), и PHP не является исключением. Рассмотрим базовые конструкции SQL, которые требуются для выполнения основ­ных действий с базами данных в РНР. Предположим, существует таблица базы данных с именем fruit, которая содержит данные о складе фруктов. Таблица содержит два поля: name, содержащее название фрукта, и number, содержащее количество данного фрукта на складе. Чтобы считать данные из этой таб­лицы для последующей обработки в скрипте, необходимо выполнить следую­щий оператор языка SQL (запрос):

SELECT * FROM fruit

Если в качестве сервера базы данных используется MySQL, то для выполне­ния запроса используется функция mysql_query, например:

$query = «SELECT * FROM fruit»;

$result = mysql_query ($query)

or die («Ошибка при выполнении запроса: «.mysql_error());

Оператор SELECT используется для выборки данных из заданной таблицы. После ключевого слова SELECT указывается перечень полей, которые возвраща­ются в качестве результата выполнения запроса. Выше использован символ «*», который означает, что вернуть следует все поля таблицы, указанной после клю­чевого слова FROM Можно также перечислить все поля в явной форме (в данном случае эти конструкции эквивалентны):

SELECT name, number FROM fruit

Запросы, приведенные выше, возвращают все строки таблицы. Имеется воз­можность выбрать только те записи таблицы, которые удовлетворяют заданно­му условию. Для этого используется ключевое слово WHERE. Например, для выборки всех записей, у которых в поле «name» указано «яблоки», используется следующий запрос:

SELECT * FROM fruit WHERE name = «яблоки»

Помимо сравнения, в запросах можно использовать следующие операторы:

1) < (меньше);

2) <= (меньше или равно);

3) > (больше);

4) >= (больше или равно).

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

SELECT * FROM fruit WHERE name IN («яблоки», «апельсины»)

Для построения условия можно использовать также и логические операторы. Например, требуется выбрать все записи из таблицы, удовлетворяющие следующим двум условиям: в поле «name» указано «яблоки» или «апельсины», а поле «number» содержит какое-либо значение (считается, что поле, которое не содержит никакого значения, содержит специальное значение NULL):

SELECT * FROM fruit WHERE name IN («яблоки», «апельсины») AND number IS NOT NULL

Для построения сложных условий используются три логических оператора — AND, OR и NOT. Если два простых условия объединены при помощи AND, то результирующее условие выполняется только в том случае, когда оба простых условия выполняются. Если же два простых условия объединены при помощи OR, то результат является истинным, если хотя бы одно из простых условий истинно. Оператор NOT производит логическую инверсию условия — истинное становится ложным и наоборот.

Результат, возвращаемый оператором SELECT, может быть отсортирован по заданному критерию. Например, для сортировки записей по алфавиту по на­именованию фрукта может быть использован следующий запрос:

SELECT * FROM fruit ORDER BY name

Этот оператор производит упорядочение по возрастанию. Для сортировки по убыванию используется ключевое слово DESC:

SELECT * FROM fruit ORDER BY name DESC

Для удаления записей используется оператор DELETE. Ниже приведен запрос, который удаляет все записи из таблицы fruit, которые не относятся к яблокам или апельсинам:

DELETE FROM fruit WHERE name NOT IN («яблоки», «апельсины»)

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

UPDATE fruit SET number = «2008» WHERE name = «яблоки»

Наконец, для добавления новых записей в таблицу используется оператор INSERT. К примеру, следующий запрос служит для добавления в таблицу fruit сведений об абрикосах:

INSERT INTO fruit (name, number) VALUES (‘абрикосы’, ’203′)

4.1 Доступ к базе MySQL

После предварительной подготовки можно переходить к использованию воз­можностей MySQL при помощи РНР. Для этого необходимо иметь запущенный сервер базы данных. Если сервер не запущен как системный сервис, его можно запустить при помощи команды my sqld, расположенной в каталоге mysq1/bin:

%mysqld —console

Для остановки сервера может быть использована следующая команда:

%mysqladmin -u root —р shutdown

Для установления соединения с сервером базы данных из скрипта РНР ис­пользуется функция mysql_connect, которой на вход передаются имя узла, на котором запущен сервер, имя пользователя и пароль:

$connection = mysql_connect («localhost», «user», «password») or die («Ошибка соединения с сервером»);

После успешного установления соединения для выбора текущей базы ис­пользуется функция mysql_select_db:

$db = mysql_select_db («db», $connection) or die («Ошибка при выборе базы данных»);

Наконец, для выбора всех строк из таблицы fruit можно выполнить SQL-запрос при помощи функции mysql_query. В случае возникновения ошиб­ки функция mysql_error возвращает ее р азвернутое текстовое описание:

$query = «SELECT * FROМ fruit»; $result = mysql_query( $query)

or die(«Query failed: «.mysql_error());

Помимо этих функций для работы с MySQL предназначен целый набор различных средств. Ниже приведён перечень наиболее часто употребительных функций:

mysql_affected_rows. Количество строк, затронутых последним запросом INSERT, UPDATE ИЛИ DELETE (но не SELECT).

mysgl_change_user. Регистрация от имени другого пользователя.

mysql_client_encoding. Текущая кодировка, используемая на клиенте.

mysql_close. Завершение соединения с сервером базы данных.

mysql_connect. ‘Установление соединения с сервером базы данных.

mysql_ create_db. Создание базы данных.

mysq_ldata_seek. Перемещение указателя в наборе данных результата запроса SELECT.

mysql_db_name. Имя текущей базы данных.

mys ql_db_query. Выполнение произвольного SQL- запроса к заданной базе данных.

mysql_drop_db. Удаление базы данных.

mysql_error. Текст сообщения об ошибке, возникшей в результате преды­дущей операции.

mysql_fetch_array. Формирование строки из набора данных результата запроса SELECT, в виде массива, проиндексированного номерами полей, именами полей или одновременно обоими индексами.

mysq1_fetch_assoc. Формирование строки из набора данных результата запроса SELECT, в виде массива, проиндексированного именами полей.

mysql_fetch_ row. Формирование строки из набора данных результата за­проса SELECT, в виде массива, проиндексированного номерами полей.

mysql_field_len. Длина заданного поля результата запроса SELECT.

mysql_field_name. Имя заданного поля результата запроса SELECT.

mysql_field_seek. Перемещение указателя на заданное поле результата запроса SELECT.

mysql_field_table. Имя таблицы заданного поля результата запроса SELECT.

mysql_field_type. Тип заданного поля результата запроса SELECT.

mysql_get_server_info. Информация о сервере базы данных.

mysql_info. Информация о последнем выполненном запросе.

mysql_list_dbs. Список баз данных MySQL-сервера.

mysql_list_fields. Список полей заданной таблицы.

mysql_list_tables. Список таблиц заданной базы данных.

mysql_num_fields. Количество полей в результате запроса SELECT.

mysql_ num_rows.Количество строк в результате запроса SELEСТ.

mysql_pconnect. ‘Установление постоянного соединения с сервером базы данных.

mysql_query. Выполнение произвольного SQL-запроса к текущей базе данных.

mysql_result. Результат запроса SELECT.

mysql_select_db. Выбор текущей базы данных.

mysql_tablename. Имя таблицы, содержащей указанное поле.

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

Первым шагом при работе с SQL-сервером является создание базы данных. Для этого необходимо установить соединение с MySQL, что осуществляется при помощи функции mysql_connect (при необходимости следует задать другие имя и/или пароль пользователя):

$connection = mysql_connect («localhost», «root», «password») or die («Ошибка соединения с сервером»);

Далее при помощи функции mysql_query выполняется SQL-запрос, кото­рый создает базу данных.

$query = «CREATE DATABASE IF NOT EXISTS db»;

$result = mysgl_guery ($query)

or die ( «Ошибка при выполнении запроса: » .mysql_error());

Наконец, происходит выбор только что созданной базы данных при помощи функции mysql_select_db. После завершения работы с базой данных необхо­димо разорвать соединение с SQL-сервером, для чего предназначена функция mysql_close. Порядок создания базы данных продемонстрирован на приме­ре 8.1, а результат его выполнения представлен на рис. 8.1. После того как база данных будет успешно создана, можно переходить к созданию таблиц.

Рис. 8.1. База данных успешно создана

Пример 8.1. Создание базы данных, phpdatacreatedb. php

<HTML>

<HEAD>

<TITLE>

Создание базы данных </TITLE> </HEAD> <BODY>

<CENTER>

<Н1>Создание базы данных</Н1> <?php

$connection = mysql_connect («localhost», «user», «password»)

or die («Ошибка соединения с сервером»);

$query = «CREATE DATABASE IF NOT EXISTS db»; $result = mysql_query ($query)

or die(«Ошибка при выполнении запроса:   «.mysql_error());

$db = mysql_select_db («db», $connection) or die («Ошибка при выборе базы данных»);

echo «База данных ‘db’ успешно создана»;

mysql_close ($connection);

?>

</CENTER> </BODY> </HTML>

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

Для создания таблицы требуется установить соединение с SQL-сервером и выбрать базу данных, в которой она будет размещена. Эти операции уже были рассмотрены выше. После этого необходимо сформировать SQL-запрос, кото­рый описывает структуру создаваемой таблицы, и исполнить его при помощи функции  mysql_query:

$query = «CREATE TABLE fruit (name VARCHAR(20), number INT)»;

$result = mysql_query ($query) or die («Ошибка при выполнении запроса: «.mysql_error());

Полностью скрипт для создания таблицы приведен в примере 8.2.

Рис. 8.2. Таблица успешно создана

Создание новой таблицы, рhрdataсгеаtе.рhр

<HTML>

<HEAD>

<TITLE>

Создание таблицы </TITLE> </HEAD>

<BODY><CENTER><H1> Создание таблицы </Н1>

<?php

$connection = mysql_connect («loсalhost», «user», «password» ) or die («Ошибка соединения с сервером»);

$db = mysql_select_db («db», $connection) or die («Ошибка при выборе базы данных»);

$query = «CREATE TABLE fruit (name VARCHAR(20), number INT)» ; $resuit = mysql_query ($query) or die («Ошибка при выполнении запроса: «.mysql_error()) ;

echo «Таблица ‘fruit’ успешно создана»; mysql_close ($connection) ;

?>

</CENTER> </BODY> </HTML>

4.4 Добавление данных

Созданная в предыдущем разделе таблица пуста. Чтобы наполнить ее данны­ми, можно воспользоваться SQL-запросом INSERT. После установления соеди­нения с SQL-сервером и выбора базы данных формируется запрос на добавление строки в таблицу, который исполняется при помощи уже знакомой функции

mysql_query:

$query = «INSERT INTO fruit (name, number) VALUES (‘яблоки’, ’203′)»;

$result = mysql_query ($query)

or die («Ошибка при выполнении запроса: » .mysql_error());

В примере 8.3 в таблицу fruit добавляются три строки, содержащие сведе­ния про яблоки, груши и персики. На рис. 8.3 демонстрируется успешное завер­шение этих операций.

Пример 8. 3. Добавление данных в таблицу fruit,

phpdatainsert.php

<HTML> <HEAD>

<TITLE>

Добавление данных </TITLE> </HEAD> <B0DY>

<CENTER>

<Н1>Добавление данных</Н1> <?php

$connection = mysql_connect (» localhost», «user», «password»)

or die («Ошибка соединения с  сервером»);

$db = mysql_select_db («db», $connection)

or die («Ошибка при выборе базы данных»);

$query = «INSERT INTO fruit (name, number) VALUES (‘яблоки’, ’203′)»;

$result = mysql_query ($query)

or die («Ошибка при выполнении запроса: » .mysql_error()) ;

$query = «INSERT INTO fruit (name, number) VALUES (‘груши’, ’431′)»;

$result = mysql_query ($query)

or die («Ошибка при выполнении запроса: «.mysql_error();

$query = «INSERT INTO fruit (name, number) VALUES( ‘персики’, ’961′)»;

$result = mysql_query ($query)

or die («Ошибка при выполнении запроса: «.mysql_error());

echo «Данные добавлены успешно»;

mysql_close ($connection);

?>

</CENTER> </BODY> </HTML>

Рис. 8.3. Добавление данных в таблицу

4.5 Отображение данных

В предыдущих разделах была создана база данных и таблица fruit, содер­жащая три строки. Чтобы отобразить все строки этой таблицы в виде HTML-до­кумента, используется SQL-запрос SELECT. В результате выполнения этого за­проса формируется набор данных, содержащий все строки требуемой таблицы.

$query = «SELECT * FROM fruit»;

$result = mysql_query($query)

or die («Ошибка при выполнении запроса: «.mysql_error ());

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

while ($row = mysql_fetch_array ($result))

{

echo «<TR>»;

echo «<TD>», Srow ['name'], «</TDxTD>» , Srow ['number'], «</TD>»;

echo «</TR>»;

}

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

Рис. 8.4. Отображение таблицы fruit

<HTML> <HEAD>

<TITLE>

Отображение данных </TITLE> </HEAD> <BODY>

<CENTER>

<Н1>0тображение данных</Н1> < ?php

Sconnection = mysql_connect («localhost», «user», «password») or die («Ошибка  соединения  с  сервером»);

$db = mysql_select_db («db», $сonnection) or die («Ошибка при выборе базы данных»);

$query = «SELECT * FROM fruit»; $result = mysql_query ($ query)

or die («Ошибка при выполнении запроса: «.mysql_error());

echo «<TABLE BORDER=’l'>»; echo «<TR>»;

echo «<ТН>Наименование</ТНхТН>Количество</ТН>» ; echo «</TR>»;

while ($row = mysql_fetch_array ($result)) {

echo «<TR>»;

echo «<TD>» , $row [ 'name' ], «</TDxTD>», $row ['number'], «</TD>»;

echo «</TR>»;

}

echo «</TABLE>»; mysql_close ($connection);

?>

</CENTER> </BODY> </HTML>

4.6 Изменение данных

Данные, которые нельзя скорректировать, будут лежать мертвым ненужным грузом. Для изменения данных требуется всего лишь написать соответствую­щий SQL-запрос. Например, требуется изменить количество яблок в таблице fruit. Для начала следует установить соединение с базой данных аналогично тому, как это сделано в предыдущих разделах. Потом выполняется SQL-запрос, изменяющий заданную строку в таблице:

$query = «UPDATE fruit SET number = 234 WHERE name = ‘яблоки’»;

$result = mysql_query ($query)

or die («Ошибка при выполнении запроса: » .mysql_error ());

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

Пример 8.5. Изменение данных, phpdataupdate.php

Рис. 8.5. Изменение количества яблок в таблице fruit

4.7 Сортировка данных

В примере 8.4 данные выводятся в том порядке, в каком они были добавлены в таблицу. Но гораздо удобнее видеть данные, упорядоченные по какому-либо критерию. Для этого в запросе SELECT используется конструкция ORDER BY. В ней задается поле, перечень полей или выражение, которое используется для сортировки данных. Необязательный параметр DESC задает сортировку в обрат­ном порядке. Ниже приведен фрагмент кода, который возвращает список фрук­тов, отсортированный по их наименованию:

$query = «SELECT * FROM fruit ORDER BY name»; $result = mysql_query ( $query)

or die («Ошибка при выполнении запроса: » .mysql_error()) ;

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

Рис. 8.6. Отображение отсортированных данных

Пример 8.6. Copтировка данных, phpdatasort.php

<HTML> <HEAD>

<TITLE>

Сортировка данных </TITLE> </HEAD> <BODY>

<CENTER>

<H1>Cортировка данных</Hi> < ?php

$connection = mysgl_connect («localhost», «user», «password») or die («Ошибка соединения с сервером» ) ;

$ db = mysql_se1ect_db («db», $connection) or die («Ошибка при выборе базы данных» ) ;

$query = «SELECT * FROM fruit ORDER BY name»; $result = mysql_query ( $query )

or die («Ошибка при выполнении запроса: » .mysql_error()) ;

echo » <TABLE BORDER= ’1′ ‘>»;

echo «<TR>»;

eсho » <ТН>Наименование </ТНхТН>Количество </TH> » ; echo   «</TR>»;

while   ($row = mysql_fetch_array   ($result)) {

echo «<TR>»;

echo «<TD>» ,  $row ['name'],   » </TDxTD>» ,  $row ['number'],  «</TD>»;

echo «</TR>»;

}

echo «</TABLE>»; mysql_close ($connection);

?>

</CENTER> </B0DY> </HTML>

4.8 Удаление данных

Для удаления данных из таблицы используется SQL-запрос DELETE. В запро­се обычно присутствует условие WHERE, идентифицирующее те данные, которые следует удалить. Если по ошибке опустить условие, то будут удалены все записи из таблицы, так что в этом случае следует быть особенно внимательным. Напри­мер, удаление из таблицы fruit заданного фрукта естественным образом произ­водится по его названию, как показано ниже:

$query = «DELETE FROM fruit WHERE name = ‘яблоки’»; $result = mysql_query ($query)

or die («Ошибка при выполнении запроса: » .mysql_error()) ;

Пример 8.7 демонстрирует удаление одной строки в таблице fruit и отобра­жение всех оставшихся в ней записей, а на рис. 8.7 представлен результат вы­полнения этого примера. Следует обратить внимание на то, что если условию от­бора строк для удаления не удовлетворила ни одна запись из таблицы, это не яв­ляется ошибкой, а такую ситуацию следует диагностировать отдельно при помощи функции mysql_af fected_rows.

Рис. 8.7. Удаление строки таблицы

Пример 8.7. Удаление данных, phpdatadelete.php

<HTML> <HEAD>

<TITLE>

Удаление данных </TITLE> </HEAD> <BODY>

<CENTER>

< H1 >Удаление данных</Н1 > <?php

$connection = mysql_connect («localhost», «user»,

«password»)

or die («Ошибка соединения с сервером»);

$db = mysql_select_db («db», $connection) or die   («Ошибка при выборе базы данных») ;

$query = «DELETE FROM fruit WHERE name = ‘яблоки’»; $result = mysql_query ($ query)

or die («Ошибка при выполнении запроса: «.mysql_error());

$query = «SELECT * FROM fruit»;

$result = mysql_query ($ query)

or die («Ошибка при выполнении запроса: » .mysql_error());

echo «<TABLE B0RDER=’1′>»;

echo «<TR>»;

echo «<ТН>Наименование</ТНхТН>Количество</ТН>»;

echo «</TR>»;

while ($row = mysql_fetch_array ($result) )

{

echo <TR>»;

echo «<TD>» , $row [ 'name' ], «</TDxTD>», $row ['number’], «</TD>»;

echo «</TR>»;

}

echo «</TABLE>»; mysql_close ($connection);

?>

</CENTER> </BODY></HTML>

  4 Базы данных и основы SQL (256,0 Кб, 3 скачиваний)
You do not have permission to download this file.