14.2 Выполнение запросов к базе данных

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

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

• create table ИмяТаблицы (ИмяПоля тип,

ИмяПоля тип, ...)

В базе данных создается новая таблица с колонками (полями), определяемыми своими именами (ИмяПоля) и указанными типами.

Типы полей. В квадратные скобки заключены необязательные элементы.

Целые числа. Формат записи:

префиксINT [UNSIGNED]

Необязательный флаг UNSIGNED задает, что будет создано поле для хранения беззнаковых чисел (больших или равных 0). Имена типов:

TINYINT – может хранить числа от –128 до +127

SMALLINT – диапазон от –32 768 до 32 767

MEDIUMINT – диапазон от –8 388 608 до 8 388 607

INT – диапазон от –2 147 483 648 до 2 147 483 647

BIGINT – диапазон от –9 223 372 036 854 775 808 до

9 223 372 036 854 775 807

Дробные числа. Формат записи:

ИмяТипа[(length,decimals)] [UNSIGNED]

Здесь length – ширина поля, decimals – количество знаков после десятичной точки, которые будут учитываться. UNSIGNED задает беззнаковые числа.

FLOAT – число с плавающей точкой небольшой точности

DOUBLE – число с плавающей точкой двойной точности

REAL – синоним для DOUBLE

DECIMAL – дробное число, хранящееся в виде строки

NUMERIC – синоним для DECIMAL

Строковые типы данных. Строки представляют собой массивы символов. Формат записи:

VARCHAR(length) [BINARY]

Тип строки, которая может хранить не более length символов, где length принадлежит диапазону от 1 до 255. При занесении некоторого значения в поле такого типа из него автоматически вырезаются концевые пробелы. Если указан флаг BINARY, то при запросе SELECT строка будет сравниваться с учетом регистра. Тип VARCHAR неудобен тем, что может хранить не более 255 символов.

TINYTEXT – может хранить максимум 255 символов

TEXT – может хранить не более 65 535 символов

MEDIUMTEXT – может хранить максимум 16 777 215 символов

LONGTEXT – может хранить 4 294 967 295 символов

Бинарные данные. Бинарные данные – это почти то же самое, что и данные в формате TEXT, но только при поиске в них учитывается регистр символов. Имеется 4 типа бинарных данных:

TINYBLOB – может хранить максимум 255 символов

BLOB – может хранить не более 65 535 символов

MEDIUMBLOB – может хранить максимум 16 777 215 символов

LONGBLOB – может хранить 4 294 967 295 символов

BLOB-данные не перекодируются автоматически, если при работе с установленным соединением включена возможность перекодирования текста «на лету».

Дата и время.

DATE – дата в формате ГГГГ-ММ-ДД

TIME – время в формате ЧЧ:ММ:СС

DATETIME – дата и время в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС

TIMESTAMP – время и дата в формате timestamp. Оно отображается в виде ГГГГММДДЧЧММСС.

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

ENUM(value1,value2,value3,...)

Множества означают, что в соответствующем поле может содержаться не одно, а сразу несколько значений (value1, value2 и т.д., т. е. – множество значений). Формат задания перечисления:

SET(value1,value2,value3,...)

Значений во множестве может быть не более 64 штук.

Модификаторы и флаги типов. К типу можно также присоединять модификаторы, которые задают его «поведение» и те операции, которые можно (или, наоборот, запрещено) выполнять с соответствующими столбцами. Основные модификаторы MySQL:

not null – означает, что поле не может содержать неопределенное значение – в частности, поле обязательно должно быть инициализировано при вставке новой записи в таблицу (если не задано значение по умолчанию)

primary key – отражает, что поле является первичным ключом, т. е. идентификатором записи, на которой можно ссылаться auto_increment. При вставке новой записи поле получит уникальное значение, так что в таблице никогда не будут существовать два поля с одинаковыми номерами.

Default – задает значение по умолчанию для поля, которое будет использовано, если при вставке записи поле не было проинициализировано явно.

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

• drop table ИмяТаблицы

Удаляет таблицу ИмяТаблицы. Таблица не обязательно должна быть пустой.

Вставка записи:

• insert into ИмяТаблицы(ИмяПоля1 ИмяПоля2 ...) values('зн1','зн2',...)

Добавляет в таблицу ИмяТаблицы запись, у которой поля, обозначенные как ИмяПоляN, установлены в значения, соответственно, знN. Те поля, которые в этой команде не перечислены, получают «неопределенные» значения (неопределенное значение – это не пустая строка, а просто признак, который говорит MySQL, что у данного поля нет никакого значения). Если для не указанного здесь поля при создании таблицы был задан not null, то данная команда закончится неуспешно. Значения полей можно заключать в апострофы и в обычные кавычки. При вставке в таблицу бинарных данных (или текстовых, содержащих апострофы и слэши) некоторые символы \, ' и символ с нулевым кодом должны быть «защищены» обратными слэшами.

Удаление записей:

• delete from ИмяТаблицы where Выражение

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

(id<10) and (name regexp 'a*b') and (age=25).

Поиск записей:

• select * from Таблица where Выражение [order by ИмяПоля [desc]]

Команда предназначена для того, чтобы искать все записи, удовлетворяющие выражению Выражение. Если записей несколько, то при указанном предложении order by они будут отсортированы по тому полю, имя которого записывается правее этого ключевого слова (если задан описатель desc, то упорядочивание происходит в обратном порядке). В предложении order by могут задаваться несколько полей. Символ * предписывает, что из отобранных записей следует извлечь все поля, когда будет выполнена команда получения выборки. Вместо звездочки можно через запятую непосредственно перечислить имена полей, которые требуют извлечения.

Обновление записей:

• update Таблица set (ИмяПоля1='зн1',

ИмяПоля1='зн2', ...) where Выражение

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

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

select count(if(Выражение,1,NULL)) from Таблица

Получение уникальных значений столбцов. При использовании базы данных часто бывает нужным узнать, какие уникальные значения существуют в данном столбце таблицы. Например, если у каждой записи в некоей статистической таблице, содержащей сведения о людях, у нас есть поле Country (страна), в котором указана страна проживания конкретного человека, и мы хотим выяснить, в каких же странах проживают все люди, дожившие до 30 лет, занесенные в таблицу, можно выполнить запрос:

select distinct Country from Таблица where age>=30

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

Получение результата. Результатом выполнения запроса является набор данных и количество вошедших в него записей. Каждая запись – это список значений полей в том же порядке, в котором они были указаны в запросе select ... from Таблица на месте многоточия (если там была звездочка, то все поля). Таким образом, результат – это своеобразный двумерный массив: первый индекс – номер записи и второй – имя поля.

Параметры результата:

• int mysql_num_rows(int $result)

Функция возвращает число записей в результате запроса.

• int mysql_num_fields(int $result)

Функция возвращает число полей в одной строке результата.

Получение поля результата:

• int mysql_result (int $result, int $row, mixed $field)

Функция возвращает значение поля $field в строке результата с номером $row. Параметр $field может задавать не только имя поля, но и его номер – позицию, на которой столбец «стоял» при создании таблицы.

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

• array mysql_fetch_row(int $result)

Функция возвращает массив-список со значениями полей очередной строки результата $result. Если указатель текущей позиции результата был установлен за последней записью, возвращает false. Текущая позиция сдвигается к следующей записи, так что очередной вызов mysql_fetch_row() вернет следующую строку результата. Пример:

$r=mysql_query ("select * frim OurTable where age<30");

while($Row=mysql_fetch_row($r))

{ // обрабатываем строку $Row }

Цикл оборвется, как только строки закончатся, т. е. когда mysql_fetch_row() вернет false.

Работать с числовыми индексами полей не удобно. Лучше использовать для адресации поля внутри результата его имя.

• array mysql_fetch_array(int $result)

Функция возвращает очередную строку результата в виде ассоциативного массива, где каждому полю сопоставлен элемент с ключом, совпадающим с именем поля. Дополнительно в массив записываются элементы с числовыми ключами и значениями, соответствующими величинам полей с этими индексами. В возвращаемом массиве они размещаются сразу за элементами с «обычными» ключами.

• int mysql_data_seek (int $result,

int $row_number)

Функция устанавливает указатель текущей строки в результате $result в позицию $row_number, так что следующий вызов mysql_fetch_row() и mysql_fetch_array() вернет значения полей именно этой строки. Возвращает false в случае ошибки или если строки кончились.

Получение информации о результате:

• string mysql_field_name(int $result,

int $field_index)

Функция возвращает имя поля, которое расположено в результате по смещению $field_index.

• string mysql_field_type(int $result,

int $field_offset)

Функция возвращает тип соответствующей колонки в результате. Им может быть, например, int, double и т. д.

• int mysql_field_len(int $result,

int $field_offset)

Функция возвращает длину поля в результате $result. Поле задается указанием его смещения. Под длиной подразумевается тот размер, который был указан при его создании. Например, если полеимеет тип varchar и было создано (вместе с таблицей) с типом varchar(100), то для него будет возвращено 100.

• string mysql_field_flags(int $result,

int $field_offset)

Функция возвращает флаги, которые были использованы при создании указанного поля в таблице. Возвращаемая строка представляет собой набор слов, разделенных пробелами, которую можно преобразовать в массив при помощи функции explode():

$Flags=explode(" ", mysql_field_flags($r,

$field_offset));

Флаги типов полей:

not_null – поле обязательно должно быть проинициализировано при вставке очередной строки в таблицу.

Primary_key – поле является первичным ключом – т. е. идентификатором строки, который будет использован для ссылок на нее.

Unique_key – поле должно быть уникальным.

Multiple_key – по этому полю построен индекс.

Blob – поле может содержать бинарный блок данных, который никак не интерпретируется.

Unsigned – поле содержит беззнаковые числа.

zerofill – использовать символы с нулевым кодом вместо пробелов.

binary – поле содержит бинарные данные.

enum – поле содержит элемент перечисления, т. е. только один элемент из нескольких возможных.

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

timestamp – в поле динамически проставляется текущее время при добавлении или изменении записи.

• string mysql_field_table (int $result, int

$field_offset)

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

Пример использования функций поддержки MySQL:

<?

// Соединяемся с сервером на локальной машине

mysql_connect("localhost");

// Выбираем текущую базу данных

mysql_select_db("my_database");

// Получаем все данные таблицы

$result = mysql_query("SELECT * FROM tbl");

// Запрашиваем идентификатор данных о полях таблицы

$fields = mysql_num_fields($result);

// Узнаем число записей в таблице

$rows = mysql_num_rows($result);

// Получаем имя таблицы (правда, мы его и так знаем, но все же...)

$table = mysql_field_table($result,0);

echo "Таблица '$table' содержит $fields колонок и $rows строк<BR>";

echo "Таблица содержит следующие поля:<BR>";

// "Проходимся" по всем полям и выводим информацию о них

for($i=0; $i<$fields; $i++) {

$type = mysql_field_type($result, $i);

$name = mysql_field_name($result, $i);

$len = mysql_field_len($result, $i);

$flags = mysql_field_flags($result, $i);

echo "$type $name $len $flags<BR>\n";

}

?>

Уникальные идентификаторы в MySQL. Обычно в таблице содержится много записей с разными значениями полей. Встает проблема выбора одной конкретной записи из этого массива. В рассмотренном примере таблицы с информацией о гражданах, запись можно однозначно идентифицировать по фамилии человека. Ну а если встретятся однофамильцы? Тогда по имени. А если же и имена совпадут? Во избежание недоразумений подобного рода в таблицу вводят еще одно вспомогательное поле (колонку), назвав ее, скажем, id. Этот id уникален у каждой записи, поэтому можно, зная id нужного человека, тут же получить его данные. Кроме того, если понадобится, например, зафиксировать в таблице еще и родственные связи людей (кто является чьим отцом, например), мы можно завести в ней еще одно поле – parent_id, в котором будет храниться id родителя.

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

ИмяПоля int auto_increment primary key

Теперь любая операция INSERT автоматически проставит у добавленной записи поле ИмяПоля так, чтобы оно было уникально во всей таблице – MySQL это гарантирует. В простейшем случае – просто увеличит на 1 некий внутренний счетчик, глобальный для всей таблицы, и занесет его новое значение в нужное поле записи. Причем гарантируется, что никакие проблемы с совместным доступом к таблице просто не могут возникнуть.

Получить только что вставленный идентификатор можно при помощи функции mysql_insert_id().

• int mysql_insert_id([int $link_identifier])

Функция возвращает непосредственно перед ее вызовом сгенерированный идентификатор записи для автоинкрементного поля после выполнения команды insert. Пример:

mysql_query("insert into Таблица (поле1, поле2) values('aa','bb')");

$id=mysql_insert_id();

Теперь к только что вставленной записи можно обратиться, используя идентификатор $id:

$r=mysql_query("select * from Таблица where

id=$id");

$Row=mysql_fetch_array($r);

Функции работа с таблицами в целом:

• int mysql_list_fields(string $dbname, string $tblname [,int $link])

Функция возвращает информацию об указанной таблице $tblname в базе данных $dbname, используя идентификатор соединения $link, если он задан (в противном случае – последнее открытое соединение). Возвращаемое значение – идентификатор результата, который может быть проанализирован обычными средствами, либо при помощи функций mysql_field_flags(), mysql_field_len(), mysql_field_name() и mysql_field_type(). В случае ошибки возвращается –1.

• int mysql_list_tables(string $database

[,int $link_identifier])

Функция возвращает идентификатор результата (одна колонка), в котором содержатся имена всех таблиц, присутствующих в базе данных. Для извлечения этих имен можно использовать функцию mysql_result() с номером колонки, равным 0.